[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Interacting with PostgreSQL

From: Jean Louis
Subject: Re: Interacting with PostgreSQL
Date: Sun, 22 Nov 2020 04:33:06 +0300
User-agent: Mutt/2.0 (3d08634) (2020-11-07)

Hello Tim,

At least somebody there who works with PostgreSQL.

I was using pg.el before until PostgreSQL upgrade arrived where nobody
could repair or do something, so pg.el stopped working and then I've
discovered `emacs-libpq` and since then use dynamic module. There is
nothing complicated with it. I am attaching my basic file, few of
generic functions I am using mostly with it. And I recommend you using

* Tim Landscheidt <> [2020-11-22 02:52]:
> I've been using Emacs with PostgreSQL for a bit more than
> 20 years now, and every now and then I look at Emacs's SQL
> "stuff" and think that I'm doing something absurdly wrong,
> but can never find a better solution.  So I'd like to pre-
> sent you with three scenarios, my tried and tested workflow
> for each of them and ask for your input on solving them more
> Emacsily.
> 1. Mass data and structure changes: These are changes where
>    I'd like to see the result or test triggers & Co. before
>    committing them.  For that, I have a function to create a
>    scratch buffer for SQL commands in a transaction:
>    | (defun tl-sql-scratch nil
>    |   "Create a scratch buffer for PostgreSQL."
>    |   (interactive)
>    |   (switch-to-buffer (generate-new-buffer "scratch.sql"))
>    |   (sql-mode)
>    |   (sql-highlight-postgres-keywords)
>    |   (insert "BEGIN WORK;\n-- COMMIT WORK;\n")
>    |   (forward-line -1))
>    and set [f12] to pipe the buffer's contents to psql:

When developing SQL tables or wish to do some direct maintenance with
the database, I do {M-x sql-postgres RET} and I am inside.

When developing SQL tables, I open my SQL file like `something.sql`
then I set SQLi buffer from menu I can send paragraphs or regions to
PostgreSQL from there, it is similar like Lisp interactivity.

>    |      "/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

>    The closest replacement for that that I have come across
>    is sql-postgres which essentially opens a glorified psql
>    session, but does not even support editing the input in
>    any "advanced" or just meaningful way; for example, if I
>    yank "SELECT 1 + \n2;\n" into the buffer, the "2;" does
>    not get prefixed with the continuation prompt, I then
>    have to press RET twice to get the result, and the prompt
>    is totally gone.

That may be. I am not worried about it. 

> 2. Reading data from the database: These are queries where,
>    for example, I'd like to read data from the database to
>    set up mail abbreviations.  For simple data, I execute
>    "psql -0Atc" and split-string, for more complex struc-
>    tures I build a JSON object.  If the query results are
>    dependent on some parameter, as there is no
>    sql-postgres-quote function and psql does not allow to
>    refer in "-c" statements to parameters set with "-v", I
>    add an inline Perl script that uses DBI to connect to and
>    pass @ARGV to the server and return the results.

Since I have switched from Perl to Emacs Lisp I do everything in Emacs
Lisp. I have not get same use case as you. I do functions in Emacs
Lisp and database editing within Emacs as interface.

> 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.

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)))

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

>    For very simple queries it feels possible to create a new
>    sql-postgres connection, send the query with
>    sql-send-string and then check whether the "last" answer
>    was "INSERT 0 1" or something like that, but even so the
>    buffer looks like a mess because there is no context for
>    the answer as the sent query is not printed (yes, there
>    is "\set ECHO queries", but it does not necessarily in-
>    spire confidence).

That means it is done. I like using "RETURNING table_id" to get the
value of the new entry immediately and act upon it. Very little stuff
I do directly in psql. That is when I need to design tables so I write
it in the file and send to sql-postgres buffer inside of Emacs.

All other database editing, adding entries, deleting is done through
Emacs, for example listing entries I do in tabulated-list-mode, or I
am using helm-mode or ivy-mode to find entry and edit in Emacs

> a) Speaking natively with the PostgreSQL server from Emacs
>    Lisp ( This does not
>    work with PostgreSQL versions currently supported.

Exactly, forget it. I will never use it again.

> b) Wrapping libpq in an Emacs module
>    ( This looks (very)
>    promising, but requires to compile and install yet anoth-
>    er binary (and keep it working with Emacs and PostgreSQL
>    versions increasing).

That is not hard to do and when compiled it simply works. That is my
best choice. Just use this and forget other stuff. I am using Emacs
development version and I do not think of re-compiling that
module. Developers will soon include it in GNU ELPA which is great
thing. It will help so many people to manage information.

> c) EmacsSQL ( This is
>    probably the most interesting approach, however instead
>    of using standard SQL which I have mastered reasonably
>    well and can also reuse in Perl, Python and whatever, it
>    comes up with a similar, but different syntax, and more
>    complex queries (checking the number of affected rows,
>    etc.) do not seem be supported (CMIIW).

It may look nice to you, to me it looks dependable:

> (emacsql db [:create-table people ([name id salary])])

> ;; Or optionally provide column constraints.
> (emacsql db [:create-table people
>              ([name (id integer :primary-key) (salary float)])])

I don't like that as it is not SQL to me. So I have no freedom
there. It looks as hard coded idiosyncratic structure to which I do
not wish to depend for future.

I like writing functions myself and understanding it.

(defun rcd-db-column-comment (table column pg)
  (let ((sql (format "SELECT pgd.description FROM 
pg_catalog.pg_statio_all_tables AS st INNER JOIN pg_catalog.pg_description pgd 
ON (pgd.objoid=st.relid) INNER JOIN information_schema.columns c ON 
(pgd.objsubid=c.ordinal_position AND c.table_schema=st.schemaname AND 
c.table_name=st.relname AND c.table_name = '%s' AND c.table_schema = 'public' 
AND c.column_name = '%s')" table column)))
    (rcd-sql-first sql pg)))

I would spend way too much time understanding how to translate already
to me complex SQL to that structure. Unless I am ready to submit to
mortification of flesh I will never use that EmacsSQL.

Ah just do it yourself how you think is better.

My tables are made so that each table has its _id as unique id. This
makes it then handy to construct such function:

(defun rcd-db-get-entry (table column id pg)
  "Returns value for the column from the RCD Database by using the entry ID"
  (let* ((sql (format "SELECT %s FROM %s WHERE %s_id = %s" column table table 
         (value (rcd-sql-first sql pg)))

You could call it `db-get`

(db-get "contacts" "get_contacts_name(contacts_id)" 123 *pg-handle*)


(db-get "countrie" "countries_phoneprefix" 217 *pg-handle*)

or like this:

(defun db-delete-entry (table id pg)
  "Delets entry by its ID without cascading"
  (let ((sql (format "DELETE FROM %s WHERE %s_id = %s" table table id)))
    (rcd-sql sql pg)))

Let us say you find contact Tim in your index, and you wish to delete
it, invoke db-delete-entry (db-delete-entry "contacts" 123 *handle*)

Of course those are underlying functions, your Emacs should ask you if
to delete or not. I am deleting everything relating to some contacts.

I have bunch of functions and I think there is nothing that cannot be
done with Emacs Lisp directly.

> None of these feel superior enough to my inline Perl scripts to
> warrant the effort of a rewrite.

Well... what to say. I also have Perl scripts from before and I use it
with other programs without thinking about it.

For example I press F9 in Mutt and it will check if contact is in the
database or not, then I choose where to sort the contact.

> What do other users use to work on PostgreSQL databases from Emacs?

`emacs-libpq` is best choice and will remain so for long time. It
offers all liberties and is faster.

Let me know more what you do with PostgreSQL.


Attachment: rcd-db-init.el
Description: Text document

reply via email to

[Prev in Thread] Current Thread [Next in Thread]