Re: Interacting with PostgreSQL

[Tim Landscheidt]

Jean Louis <bugs@gnu.support> wrote:

> […]

>>    |      "/usr/bin/psql")))

>>    I then add UPDATEs, CREATE FUNCTIONs, test cases, etc.,
>>    press [f12] until I am satisfied with the results, remove
>>    the test cases, uncomment the COMMIT and then press [f12]
>>    for the last time before killing the buffer.

> Sounds complicated to me. If I want direct interactivity then I just
> do {M-x sql-postgres RET} and from inside I use \e or \ev or \ef to
> create some functions or change views, etc.

> emacsclient is opening within Emacs, when I kill it, SQL is
> executed. I do not use any self made functions to interact with
> Emacs.

As a mere mortal :-), when for example I add triggers to
five tables with three test cases (INSERT, UPDATE, DELETE)
each, I will not remember which of the triggers or test
cases have been set/inserted/updated/deleted.  Also, my mind
will be focussed on fixing the functions/etc.  Worst case is
that I miss that psql has reverted to auto-commit mode and
my not-yet-working trigger/query overwrites good data with
garbage.

So I definitely want to be sure that my changes are (easily)
revertable until they are working properly.

> […]

>> 3. Writing data to the database: These are queries where I
>>    want to store data, either single tuples or complex
>>    structures.  Here again I use an inline Perl script that
>>    uses DBI to connect to the database and do all the neces-
>>    sary transaction handling, escaping and sanity checks.

> Ha, alright, but not quite. Why use two programming languages when one
> is enough. Somehow complex. Some people like it.

For the same reason why I use PostgreSQL to store data and
not Emacs :-).  It is highly unlikely that call-process and
Perl's DBI change in a fundamental way anytime soon, and if
they did, there would probably be millions of users explain-
ing how to migrate to a new system.

> What escaping you need?

> I am using this escaping function:

> (defun sql-escape-string (str)
>   "Returnes escaped string for PostgreSQL. If string is `NULL' returns `NULL'"
>   (if (null str)
>       (setq str "NULL")
>     (when (or (string-match "\\\\" str)
>               (string-match "'" str))
>       (setq str (replace-regexp-in-string "\\\\" "\\\\\\\\" str))
>       (setq str (replace-regexp-in-string "'" "''" str))))
>   (unless (string= "NULL" str)
>     (setq str (format "E'%s'" str)))
>   str)

> Maybe I am missing something but so far in last year I did not have
> any incident for using this escaping.

> […]

I prefer to use shell-quote-argument and other prebaked
functions because "did not break yet" means that there is a
chance that I will discover at 3 AM that my data has been
garbled, and now I would have to find the error in my quote
function, fix all the data that has been trashed, and do all
that under time pressure because I have a deadline at 4 AM.
So if I have a working solution with call-process and DBI
that is partially tested every day by millions of users, I
rather not replace just for the sake of replacing it.

Tim