Re: Interacting with PostgreSQL

[Jean Louis <bugs@gnu.support>]

[-- Attachment #1: Type: text/plain, Size: 9732 bytes --]

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
`emacs-libpq`.

* Tim Landscheidt <tim@tim-landscheidt.de> [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
Emacs.

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

> a) Speaking natively with the PostgreSQL server from Emacs
>    Lisp (https://github.com/cbbrowne/pg.el): 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
>    (https://github.com/anse1/emacs-libpq): 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 (https://github.com/skeeto/emacsql): 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 id))
	 (value (rcd-sql-first sql pg)))
    value))

You could call it `db-get`

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

or

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

Jean

[-- Attachment #2: rcd-db-init.el --]
[-- Type: text/plain, Size: 6220 bytes --]

;;; rcd-db-init.el --- RCD Database Initialization and basic SQL query functions

;; Copyright (C) 2016-2020 by Jean Louis

;; Author: Jean Louis <bugs@gnu.support>
;; Version: 1.7
;; Package-Requires: 
;; Keywords: applications
;; URL: https://gnu.support/gnu-emacs/packages/rcd-db-init-el.html

;; This file is not part of GNU Emacs.

;; This program is free software: you can redistribute it and/or
;; modify it under the terms of the GNU General Public License as
;; published by the Free Software Foundation, either version 3 of the
;; License, or (at your option) any later version.
;;
;; This program is distributed in the hope that it will be useful, but
;; WITHOUT ANY WARRANTY; without even the implied warranty of
;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
;; General Public License for more details.
;;
;; You should have received a copy of the GNU General Public License
;; along with this program. If not, see <http://www.gnu.org/licenses/>.

;;; Commentary:

;; RCD Database Initialization and basic SQL query functions uses
;; PostgreSQL database backend. This may change in future to use any
;; other type of a database.
;;
;; RCD is acronym for Reach, Connect, Deliver, my personal
;; principle and formula for Wealth.

;;; Change Log:

;;; Code:

;; TODO remove the following line when emacs-libpq becomes part of GNU ELPA
(add-to-list 'load-path "~/Programming/git/emacs-libpq/")

(require 'pq)

(defun rcd-db-connect (database &optional port host username password)
  "Connects to PostgreSQL database with specified parameters, by
using the GNU Emacs module emasc-libpq"
  (let* ((port (if port port 5432))
	 (connection (format "dbname=%s port=%s" database port))
	 (connection (if host
			 (concat connection (format " host=%s" host))
		       connection))
	 (connection (if username
			 (concat connection (format " user=%s" username))
		       connection))
	 (connection (if password
			 (concat connection (format " password=%s" password))
		       connection))
	 (connection (or (getenv "PG_CONNINFO") connection)))
    (pq:connectdb connection)))

(defun rcd-sql (sql pg)
  "Sends SQL queries to PostgreSQL database and returns results"
    (condition-case err
	(pq:query pg sql)
      (error
       (if (string-match "^ERROR:  syntax error" (cdr err))
	   (progn
	     (if (fboundp 'speak) (speak (cdr err)))
	     (message (cdr err)))
	 ;; re-throw
	 (signal (car err) (cdr err))))))

(defun rcd-sql-list (sql pg)
  "Returns list of lists instead of vectors"
  (let ((list '()))
    (dolist (i (apply 'rcd-sql (list sql pg)) (reverse list))
      (cond ((eq (type-of i) 'vector) (push (append i '()) list))
	    (t (push i list))))))

(defun rcd-sql-first (sql pg)
  "Returns first entry from SQL query"
  (car (apply 'rcd-sql (list sql pg))))

(defun rcd-sql-list-first (sql pg)
  "Returns first item of the list of lists instead of vectors"
  ;; TODO this is not verified that it works well as intended
  (let ((list (rcd-sql-first sql pg)))
    (append list '())))

(define-skeleton cf-sql-table
    "Prepare the SQL table for Central Files database design"
  nil
  "
-- ------------------------------------------
-- ------------ Table " (setq table (skeleton-read "Table name: ")) "
-- ------------------------------------------
DROP SEQUENCE " table "_id_seq;

CREATE TABLE " table " (
" table "_id SERIAL NOT NULL PRIMARY KEY,
" table "_datecreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
" table "_datemodified TIMESTAMP,
" table "_usercreated TEXT NOT NULL DEFAULT current_user,
" table "_usermodified TEXT NOT NULL DEFAULT current_user,
" table "_name TEXT,
" table "_title TEXT,
" table "_description TEXT,
" table "_ TEXT
);
GRANT ALL ON " table " TO PUBLIC;
DROP VIEW " table "_combo;
CREATE OR REPLACE VIEW " table "_combo AS
SELECT " table "_id AS id,
" table "_name AS TEXT
FROM " table ";
GRANT SELECT ON " table "_combo TO PUBLIC;

DROP VIEW " table "_rcd;
CREATE OR REPLACE VIEW " table "_rcd AS
SELECT concat(" table "_id, ' '," table "_name) AS id
FROM " table ";
GRANT SELECT ON " table "_rcd TO PUBLIC;

COMMENT ON TABLE " table " IS '" (capitalize table) "';
COMMENT ON COLUMN " table "." table "_id IS 'ID';
COMMENT ON COLUMN " table "." table "_datecreated IS 'Date created';
COMMENT ON COLUMN " table "." table "_datemodified IS 'Date modified';
COMMENT ON COLUMN " table "." table "_usercreated IS 'User created';
COMMENT ON COLUMN " table "." table "_usermodified IS 'User modified';
COMMENT ON COLUMN " table "." table "_hid IS 'HID';
COMMENT ON COLUMN " table "." table "_name IS 'Name';
COMMENT ON COLUMN " table "." table "_title IS 'Title';
COMMENT ON COLUMN " table "." table "_description IS 'Description';
COMMENT ON COLUMN " table "." table "_IS '';

CREATE UNIQUE INDEX " table "_index ON " table " ( " table "_weekend );

INSERT INTO meta_fields VALUES ('" table "','" table "_description','widget','area(rows=10,cols=60)');
INSERT INTO meta_fields VALUES ('" table "','" table "_datecreated','widget','readonly');
INSERT INTO meta_fields VALUES ('" table "','" table "_datemodified','widget','readonly');
INSERT INTO meta_fields VALUES ('" table "','" table "_usercreated','widget','readonly');
INSERT INTO meta_fields VALUES ('" table "','" table "_usermodified','widget','readonly');
INSERT INTO meta_fields VALUES ('" table "','" table "_','hide_list','1');
-- INSERT INTO " table " (" table "_name) VALUES ('');
-- INSERT INTO meta_tables VALUES ('" table "', 'hide', '1');

-- Triggers
-- For Date Modified
CREATE TRIGGER " table "_moddatetime
BEFORE UPDATE ON " table "
FOR EACH ROW
EXECUTE PROCEDURE moddatetime(" table "_datemodified);

-- For User Modified
CREATE TRIGGER insert_username_" table "
BEFORE INSERT OR UPDATE ON " table "
FOR EACH ROW
EXECUTE PROCEDURE insert_username(" table "_usermodified);


-- List view
/*
DROP VIEW " table "_list;
CREATE OR REPLACE VIEW " table "_list AS
SELECT " table "_id, " table "_name
FROM " table " ORDER BY " table "_id DESC;
COMMENT ON VIEW " table "_list IS '" (capitalize table) "';
COMMENT ON COLUMN " table "_list." table "_id IS 'ID';
COMMENT ON COLUMN " table "_list." table "_name IS 'Name'; 
*/
}
);")

(provide 'rcd-db-init)

;;; rcd-db-init.el ends here