Interacting with PostgreSQL

Interacting with PostgreSQL

[Tim Landscheidt]

Hi,

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:

   | (define-key sql-mode-map
   |   [f12]
   |   (lambda nil
   |     (interactive)
   |     (shell-command-on-region
   |      (point-min)
   |      (point-max)
   |      "/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.

   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.

   But even if that stuff would work, it would still not
   provide the clean slate I get with my approach: These are
   the steps to execute on a known state of the database;
   and if they do not "work", the next iteration will start
   again at the known state, not some in-between where some
   functions may have been created/data has been changed
   while others have not.

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.

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.

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

As an alternative to 2. and 3., I'm aware of three approach-
es:

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.

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

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

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

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

Tim

Re: Interacting with PostgreSQL

[Jean Louis]

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

Re: Interacting with PostgreSQL

[Marcin Borkowski]

Hi there,

not sure if this will help, but I sometimes use Org-mode to interact
with Postgres:
http://mbork.pl/2020-03-09_Using_Org-mode_as_a_PostgreSQL_client

Best,
Marcin

On 2020-11-22, at 00:51, Tim Landscheidt <tim@tim-landscheidt.de> wrote:

> Hi,
>
> 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:
>
>    | (define-key sql-mode-map
>    |   [f12]
>    |   (lambda nil
>    |     (interactive)
>    |     (shell-command-on-region
>    |      (point-min)
>    |      (point-max)
>    |      "/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.
>
>    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.
>
>    But even if that stuff would work, it would still not
>    provide the clean slate I get with my approach: These are
>    the steps to execute on a known state of the database;
>    and if they do not "work", the next iteration will start
>    again at the known state, not some in-between where some
>    functions may have been created/data has been changed
>    while others have not.
>
> 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.
>
> 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.
>
>    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).
>
> As an alternative to 2. and 3., I'm aware of three approach-
> es:
>
> 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.
>
> 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).
>
> 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).
>
> None of these feel superior enough to my inline Perl scripts
> to warrant the effort of a rewrite.
>
> What do other users use to work on PostgreSQL databases from
> Emacs?
>
> Tim


-- 
Marcin Borkowski
http://mbork.pl

Re: Interacting with PostgreSQL

[Tim Landscheidt]

Marcin Borkowski <mbork@mbork.pl> wrote:

> not sure if this will help, but I sometimes use Org-mode to interact
> with Postgres:
> http://mbork.pl/2020-03-09_Using_Org-mode_as_a_PostgreSQL_client

> […]

I sometimes do as well, mainly for read-only queries, and it
has the very incentive benefit that one can add a bunch of
DONE tasks to the daily agenda view :-).  But for data mung-
ing I have two reservations:

- AFAICT, there is no way to execute the code block while
  editing.  So I have to either C-c ', edit, C-c ', C-c C-c,
  check the result, lather, rinse, repeat, finally uncomment
  COMMIT and C-c C-c, or edit the query "inline" which can
  be dangerous when pasting bulk data or replacing text be-
  cause it is easy to miss where the query ends.

- For non-idempotent queries (say, incrementing a column
  once for all rows), there are no safeguards against acci-
  dentally re-executing the query in the future.  So I would
  have to edit the query and evaluate the code block for
  testing, then uncomment the COMMIT, evaluate the code
  block, then comment the COMMIT again to be on the safe
  side.

Tim

P. S.: Just noticed that apparently :prologue and :epilogue
       are ignored in ob-sql.  I'll file a report for that.

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

Re: Interacting with PostgreSQL

[Jean Louis]

* Tim Landscheidt <tim@tim-landscheidt.de> [2020-11-25 02:15]:
> 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.

Thank you.

I am probably not getting it right. I got impression that when I set
triggers to work they are set to be forgotten and I do not need to
think of it any more.

In general I am developing SQL in the SQL file, so anything I do I
write first in the file. Rarely I will go to sql-postgres to change
some view or similar.

Better workflow is always writing it first in the file and then
executing chunks from file. It is just fine for testing. Benefit is
that I can recreate new database from the file that has much better
readability then the SQL dump.

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

OK, I do not know about that. And I am not using auto commit mode, did
not know that it exists and will not switch to it.

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

Quite understandable. I did not encounter similar problems in last so
many years.

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

I said "did not break yet" as the function is escaping the quotes,
maybe backslashes, and I do not remember now if there is anything else
to be escaped really. That function did not break ever is also the
case. When it is there you can inspect it and look into specification
and then make test cases and see if it works well or not.

I am sure that Perl has also somewhere SQL escaping functions and it
also has bugs, so there is not much difference. PostgreSQL has also
bugs, programmers are not perfect.

I have used this function in Perl.

sub sql_escape ($) {
	my ($self, $string) = @_;
	return unless $string;
	$string =~ s/'/''/g;
	$string =~ s/\\/\\\\/g;
	return $string;
}

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

Hm, yes and no. Good when you are familiar with it. My side I like to
interact with Emacs Lisp directly to be kind of free of Perl.

I am interested in your work.

What do you store in the database?

Do you interact through Emacs with it?

Re: Interacting with PostgreSQL

[Tim Landscheidt]

Jean Louis <bugs@gnu.support> wrote:

> […]

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

> OK, I do not know about that. And I am not using auto commit mode, did
> not know that it exists and will not switch to it.

The problem is that you do not need to consciously use auto-
commit mode, but that psql automatically reverts to it when
you rollback or commit a transaction:

| tim=> BEGIN WORK;
| BEGIN
| tim=> INSERT INTO t (ID) VALUES (1);
| INSERT 0 1
| tim=> ROLLBACK WORK;
| ROLLBACK
| tim=> INSERT INTO t (ID) VALUES (1);
| INSERT 0 1
| tim=> -- The row has been committed.

You can set the AUTOCOMMIT variable to off, but that does
not turn auto-commit off, but instead silently inserts BEGIN
statements where psql thinks they are useful; it can also
interfere with scripts that except auto-commit to be on.

> […]

> I am interested in your work.

> What do you store in the database?

> Do you interact through Emacs with it?

Yes, that was the point of my question :-).  I use Postgre-
SQL to store most data that fits a relational model and (for
a huge part) use Emacs to interact with it.  I also use a
number of Perl scripts for daily consistency checks and
other Perl/Python scripts where either the input/output will
be part of a pipeline or there is a modal UI that I do not
(yet) have the motivation to rewrite in Emacs Lisp (e. g.,
playing a podcast in the background and "afterwards" asking
the user whether the podcast should be marked as "heard" and
then update the database accordingly before archiving/delet-
ing the podcast file).

Tim

Re: Interacting with PostgreSQL

[Jean Louis]

* Tim Landscheidt <tim@tim-landscheidt.de> [2020-11-25 13:38]:
> The problem is that you do not need to consciously use auto-
> commit mode, but that psql automatically reverts to it when
> you rollback or commit a transaction:
> 
> | tim=> BEGIN WORK;
> | BEGIN
> | tim=> INSERT INTO t (ID) VALUES (1);
> | INSERT 0 1
> | tim=> ROLLBACK WORK;
> | ROLLBACK
> | tim=> INSERT INTO t (ID) VALUES (1);
> | INSERT 0 1
> | tim=> -- The row has been committed.

I understand. I always used it manually and will rather continue. Just
observing how you do it.

> > I am interested in your work.
> 
> > What do you store in the database?
> 
> > Do you interact through Emacs with it?
> 
> Yes, that was the point of my question :-).  I use Postgre-
> SQL to store most data that fits a relational model and (for
> a huge part) use Emacs to interact with it.  I also use a
> number of Perl scripts for daily consistency checks and
> other Perl/Python scripts where either the input/output will
> be part of a pipeline or there is a modal UI that I do not
> (yet) have the motivation to rewrite in Emacs Lisp (e. g.,
> playing a podcast in the background and "afterwards" asking
> the user whether the podcast should be marked as "heard" and
> then update the database accordingly before archiving/delet-
> ing the podcast file).

That is some type of logging or recording, I understand.

Any time I need some structured and relational data I use
PostgreSQL. For example to insert geographic locations of interests
that are helpful to other people or coordinates of lands. When
coordinates are in the database then I can invoke map downloads with
nicely shown lands on the geographic map.

In general I use database for everything including for sorting files
by using database where program decides where files are sorted not me,
for quick retrieval of files where program decides it, for SMS record,
phone calls, notes, project planning, Website Revision System,
variables for the WRS.

It helps in preparing translations, I can just write some words into
database then I run program that fetches automatic translations and
translates it before human makes the review of it.

Also for website forms, like all those small pieces of HTML like text
inputs, check boxes and similar. When there is a lot of work that
becomes simple reuse without much copy/paste or looking into files.

Then for invoices, for reporting and records of all kinds, reminders,
real estate agency, opportunities, market pricing, statistics, just
for anything that requires structure and relations.

Do you use any kind of revision system?

I have made recently my own revision system, you can see the simple
table below, and `hyperscope-vc' that fetches values and inserts into
the "version control" table.

Then function like `hlink-edit-org' with `hyperscope-vc' before the
update makes sure that information first get recorded in the version
control table. I could as well record all updates that I do through
Emacs and it could be possible to make triggers as well, but I like it
this way now.

If you know other ways of version control, let me know.

(defun hlink-edit-org (id)
  (let* ((blob (hlink-description-value id))
	 (blob (if blob blob  ""))
	 (buffer-name (format "HyperScope Editing ID: %d" id))
	 (new-blob (read-from-buffer blob buffer-name 'org-mode)))
    (hyperscope-vc "hlinks" "hlinks_description" id)
    (rcd-db-update-entry "hlinks" "hlinks_description" "text" id new-blob *hs*)))

(defun hyperscope-vc (table column id &optional description)
  "Simple version system."
  (let* ((value (rcd-db-get-entry table column id *hs*))
	 (value (sql-escape-string value)) ;; TODO only text supported for now
	 (description (if description (sql-escape-string description) "NULL"))
	 (sql (format "INSERT INTO vc (vc_table, 
                                      vc_column, 
                                      vc_tableid, 
                                      vc_value, 
                                      vc_description) VALUES ('%s', '%s', %s, %s, %s)
                         RETURNING vc_id"
		      table column id value description))
	 (id (rcd-sql sql *hs*)))
    (if id id nil)))

CREATE TABLE vc (
vc_id SERIAL NOT NULL PRIMARY KEY,
vc_datecreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
vc_datemodified TIMESTAMP,
vc_usercreated TEXT NOT NULL DEFAULT current_user,
vc_usermodified TEXT NOT NULL DEFAULT current_user,
vc_table TEXT NOT NULL,
vc_column TEXT NOT NULL,
vc_tableid INT4 NOT NULL,
vc_value TEXT 
);

Re: Interacting with PostgreSQL

[Tim Landscheidt]

Jean Louis <bugs@gnu.support> wrote:

>> The problem is that you do not need to consciously use auto-
>> commit mode, but that psql automatically reverts to it when
>> you rollback or commit a transaction:

>> | tim=> BEGIN WORK;
>> | BEGIN
>> | tim=> INSERT INTO t (ID) VALUES (1);
>> | INSERT 0 1
>> | tim=> ROLLBACK WORK;
>> | ROLLBACK
>> | tim=> INSERT INTO t (ID) VALUES (1);
>> | INSERT 0 1
>> | tim=> -- The row has been committed.

> I understand. I always used it manually and will rather continue. Just
> observing how you do it.

Eh, that /is/ the behaviour using it manually, either on the
command line or via sql-postgres.

> […]

> Do you use any kind of revision system?

> I have made recently my own revision system, you can see the simple
> table below, and `hyperscope-vc' that fetches values and inserts into
> the "version control" table.

> […]

> If you know other ways of version control, let me know.

> […]

If I were to choose a system for storing structured informa-
tion, I would almost certainly not use a system that accord-
ing to https://github.com/BradNeuberg/hyperscope (last up-
dated six years ago) "is not currently maintained.  It is
present here for archival purposes."

There are org-mode, many markdown variants, plain HTML, and
apparently even a dedicated GNU project called Hyperbole
(https://www.gnu.org/software/hyperbole/).  There are a my-
riad of version control systems, with Git at the forefront.
Most of them are natively supported by Emacs, right out of
the box.

I find that there is a lot of strength in numbers and thus
I like to use software that is used by at least a sizable,
critical mass of other users.  This gives me confidence that
if I encounter a problem, either a bug or just a question of
how to achieve something in the most effective or efficient
way, others will have treaded that path before me and
hopefully left some breadcrumbs.  And if I am genuinely the
first one to tackle an issue, my solution to it will not
only benefit myself, but also others who may face the same
questions.

Tim

Re: Interacting with PostgreSQL

[Jean Louis]

* Tim Landscheidt <tim@tim-landscheidt.de> [2020-11-25 20:09]:
> Jean Louis <bugs@gnu.support> wrote:
> 
> >> The problem is that you do not need to consciously use auto-
> >> commit mode, but that psql automatically reverts to it when
> >> you rollback or commit a transaction:
> 
> >> | tim=> BEGIN WORK;
> >> | BEGIN
> >> | tim=> INSERT INTO t (ID) VALUES (1);
> >> | INSERT 0 1
> >> | tim=> ROLLBACK WORK;
> >> | ROLLBACK
> >> | tim=> INSERT INTO t (ID) VALUES (1);
> >> | INSERT 0 1
> >> | tim=> -- The row has been committed.
> 
> > I understand. I always used it manually and will rather continue. Just
> > observing how you do it.
> 
> Eh, that /is/ the behaviour using it manually, either on the
> command line or via sql-postgres.

I understand what you mean. Without BEGIN I am in autocommit
mode. That is standard. But why is it problem for you?

> > Do you use any kind of revision system?
> 
> > I have made recently my own revision system, you can see the simple
> > table below, and `hyperscope-vc' that fetches values and inserts into
> > the "version control" table.
> 
> > […]
> 
> > If you know other ways of version control, let me know.
> 
> > […]
> 
> If I were to choose a system for storing structured informa-
> tion, I would almost certainly not use a system that accord-
> ing to https://github.com/BradNeuberg/hyperscope (last up-
> dated six years ago) "is not currently maintained.  It is
> present here for archival purposes."

I do not think that is related to PostgreSQL, maybe, but I have no
idea. It is not software I am making for Emacs. 

> There are org-mode, many markdown variants, plain HTML, and
> apparently even a dedicated GNU project called Hyperbole
> (https://www.gnu.org/software/hyperbole/).

Definitely yes. But those you mentioned are related only by
having hyperlink features. Org mode is outline mode, markdown is not
quite outline neither hierarchical, it is pre-processor for HTML. GNU
Hyperbole I am using daily and started using it back in time, maybe 2
decades with pauses and never fully used all of the options it
has. Maybe you mean Koutliner in GNU Hyperbole as that is outline
somewhat similar to Org but better structured.

Emacs HyperScope is dynamic knowledge repository that augments
knowledge, relates it together and serves as dynamic knowledge
repository that follows the technology template project for open
hyperdocument systems by Doug Engelbart, definitely similar in its
nature to first Javascript based HyperScope. This one is for Emacs.

About Dynamic Knowledge Repositories (DKR)
https://www.dougengelbart.org/content/view/190/163/

TECHNOLOGY TEMPLATE PROJECT OHS Framework 
https://www.dougengelbart.org/content/view/110/460/

> There are a my- riad of version control systems, with Git at the
> forefront.  Most of them are natively supported by Emacs, right out
> of the box.

That is right. And I need to think about such. For example I would
need to check in, check out, and I do that for the files.

When editing database entries, those are not files on file
system. Data comes from the database. That data is itself stored in
some files that PostgreSQL database manages is irrelevant as user has
no access to source files normally, neither is allowed to do some
versioning with such. Interaction goes over TCP or sockets and not
from file access. In that sense when I am editing for example Org
based data it is not file from file system but Org mode formatted data
then there is currently none known versioning system that I know that
is generic for databases.

This can be easily helped with simple procedure that function that is
about to edit the data simply fetch the edited data and stores it in
the version control table before any editing. Version control table
remembers the table, column ID, column, type and value. Right now I am
storing those as text, but not numbers as it need some type casting to
text that I can implement later. Major problem is with larger text
that requires sometimes longer editing.

Because such editing is without files I would like to know if I could
temporarily assign a disconnected file to buffer so that file get
saved from buffer, but when I finish recursive editing that
buffer-string gets returned to my function that stores it into
database. That is yet unsolved problem and could be solution to safety
of data being edited longer time.

Two safety problems are with PostgreSQL data entry editing, one is to
save the previouse entries or historical and that I have solved in
very simple manner. Other problem is to solve the currently edited
text that is nowhere saved. For that reason I wish to find way to
automatically save the buffers somewhere but not that buffer is
connected to the file being saved.

Does anybody have pointers how to do that?

> I find that there is a lot of strength in numbers and thus
> I like to use software that is used by at least a sizable,
> critical mass of other users.

Well, I program first for me to organize things, then it has
tremendous value for others in my group, and then there is maybe small
value in public when software is published. Without programming I
would never get what I really want as software for what I need does
not exists normally.

If you refer to version control systems, in the above example with
versioning table there is nothing to think of. Once you make function
to fetch data from edited table and save it in versioning table your
function works always. Since I made it before maybe 2-3 days there are
168 entries of all kinds of data. It just works and can work for
years.

> This gives me confidence that if I encounter a problem, either a bug
> or just a question of how to achieve something in the most effective
> or efficient way, others will have treaded that path before me and
> hopefully left some breadcrumbs.  And if I am genuinely the first
> one to tackle an issue, my solution to it will not only benefit
> myself, but also others who may face the same questions.

Yes and no, I do not know how it applies here. PostgreSQL is designed
in such manner that it is left to users how to design their tables and
database. It involves and demands planning, development,
implementation. All what one need is specification of PostgreSQL and
one can build parts one by one until system works well
integrated. Almost any programming language can be used.

In decades I have not encountered any serious problem. If there is
single mistake in the SQL it will simply not work. Isn't that
simplicity very useful and helpful?

The escaping function works with any data. If it does not work I would
get error and see why it does not work, but data would not be affected
as if things have to be escaped before entering the database those
things cannot be entered into the database unescaped.

Jean

Re: Interacting with PostgreSQL

[Jean Louis]

* Marcin Borkowski <mbork@mbork.pl> [2020-11-23 00:34]:
> Hi there,
> 
> not sure if this will help, but I sometimes use Org-mode to interact
> with Postgres:
> http://mbork.pl/2020-03-09_Using_Org-mode_as_a_PostgreSQL_client

Databases are so useful for Org mode reporting in form of tables. That
way data remains stable and centralized and can be reused dynamically
in Org files.

Re: Interacting with PostgreSQL

[Tim Landscheidt]

Jean Louis <bugs@gnu.support> wrote:

>> >> The problem is that you do not need to consciously use auto-
>> >> commit mode, but that psql automatically reverts to it when
>> >> you rollback or commit a transaction:

>> >> | tim=> BEGIN WORK;
>> >> | BEGIN
>> >> | tim=> INSERT INTO t (ID) VALUES (1);
>> >> | INSERT 0 1
>> >> | tim=> ROLLBACK WORK;
>> >> | ROLLBACK
>> >> | tim=> INSERT INTO t (ID) VALUES (1);
>> >> | INSERT 0 1
>> >> | tim=> -- The row has been committed.

>> > I understand. I always used it manually and will rather continue. Just
>> > observing how you do it.

>> Eh, that /is/ the behaviour using it manually, either on the
>> command line or via sql-postgres.

> I understand what you mean. Without BEGIN I am in autocommit
> mode. That is standard. But why is it problem for you?

Because if I make a mistake in auto-commit mode, potentially
all data can be changed or lost.  Therefore I want to use a
transaction wherever possible so that I can verify the ef-
fects of a query before committing it.

> […]

>> There are org-mode, many markdown variants, plain HTML, and
>> apparently even a dedicated GNU project called Hyperbole
>> (https://www.gnu.org/software/hyperbole/).

> Definitely yes. But those you mentioned are related only by
> having hyperlink features. Org mode is outline mode, markdown is not
> quite outline neither hierarchical, it is pre-processor for HTML. GNU
> Hyperbole I am using daily and started using it back in time, maybe 2
> decades with pauses and never fully used all of the options it
> has. Maybe you mean Koutliner in GNU Hyperbole as that is outline
> somewhat similar to Org but better structured.

> Emacs HyperScope is dynamic knowledge repository that augments
> knowledge, relates it together and serves as dynamic knowledge
> repository that follows the technology template project for open
> hyperdocument systems by Doug Engelbart, definitely similar in its
> nature to first Javascript based HyperScope. This one is for Emacs.

> About Dynamic Knowledge Repositories (DKR)
> https://www.dougengelbart.org/content/view/190/163/

> TECHNOLOGY TEMPLATE PROJECT OHS Framework
> https://www.dougengelbart.org/content/view/110/460/

I know.  So why use that and not Org mode?

>> There are a my- riad of version control systems, with Git at the
>> forefront.  Most of them are natively supported by Emacs, right out
>> of the box.

> That is right. And I need to think about such. For example I would
> need to check in, check out, and I do that for the files.

> When editing database entries, those are not files on file
> system. Data comes from the database. That data is itself stored in
> some files that PostgreSQL database manages is irrelevant as user has
> no access to source files normally, neither is allowed to do some
> versioning with such. Interaction goes over TCP or sockets and not
> from file access. In that sense when I am editing for example Org
> based data it is not file from file system but Org mode formatted data
> then there is currently none known versioning system that I know that
> is generic for databases.

> This can be easily helped with simple procedure that function that is
> about to edit the data simply fetch the edited data and stores it in
> the version control table before any editing. Version control table
> remembers the table, column ID, column, type and value. Right now I am
> storing those as text, but not numbers as it need some type casting to
> text that I can implement later. Major problem is with larger text
> that requires sometimes longer editing.

> Because such editing is without files I would like to know if I could
> temporarily assign a disconnected file to buffer so that file get
> saved from buffer, but when I finish recursive editing that
> buffer-string gets returned to my function that stores it into
> database. That is yet unsolved problem and could be solution to safety
> of data being edited longer time.

> Two safety problems are with PostgreSQL data entry editing, one is to
> save the previouse entries or historical and that I have solved in
> very simple manner. Other problem is to solve the currently edited
> text that is nowhere saved. For that reason I wish to find way to
> automatically save the buffers somewhere but not that buffer is
> connected to the file being saved.

> Does anybody have pointers how to do that?

> […]

I have absolutely no idea /why/ someone would store Org mode
data in a database and then wonder how to implement a form
of version control for it.  Emacs is very good at editing
files, Git is very good at versioning them, it has plenty of
commands to create branches and worktrees and everything
else one of the millions of projects using it has ever need-
ed, and Emacs Lisp is more than versatile enough to code
every imaginable workflow.

Some of the brightest minds have worked on them extensively,
either from a formally educated perspective or with the ex-
perience of blood and tears.  These giants are inviting
everybody to stand on their shoulders, and neither would I
ignore them nor would I recommend others to do so.

Tim

Re: Interacting with PostgreSQL

[Jean Louis]

* Tim Landscheidt <tim@tim-landscheidt.de> [2020-11-30 06:18]:
> Jean Louis <bugs@gnu.support> wrote:
> 
> >> >> The problem is that you do not need to consciously use auto-
> >> >> commit mode, but that psql automatically reverts to it when
> >> >> you rollback or commit a transaction:
> 
> >> >> | tim=> BEGIN WORK;
> >> >> | BEGIN
> >> >> | tim=> INSERT INTO t (ID) VALUES (1);
> >> >> | INSERT 0 1
> >> >> | tim=> ROLLBACK WORK;
> >> >> | ROLLBACK
> >> >> | tim=> INSERT INTO t (ID) VALUES (1);
> >> >> | INSERT 0 1
> >> >> | tim=> -- The row has been committed.
> 
> >> > I understand. I always used it manually and will rather continue. Just
> >> > observing how you do it.
> 
> >> Eh, that /is/ the behaviour using it manually, either on the
> >> command line or via sql-postgres.
> 
> > I understand what you mean. Without BEGIN I am in autocommit
> > mode. That is standard. But why is it problem for you?
> 
> Because if I make a mistake in auto-commit mode, potentially
> all data can be changed or lost.  Therefore I want to use a
> transaction wherever possible so that I can verify the ef-
> fects of a query before committing it.

That is very understandable. And I guess PostgreSQL users will use
BEGIN; and COMMIT; at all sensitive transactions.

My side here there are cases where BEGIN; COMMIT; are not needed and
this may be due to my work flow:

- all SQL is prepared in the file.

- I invoke SQL that has been written in the file, not one written with
  psql command line tool on command line, which I use for other
  purposes

- I am normally creating tables, views and triggers

- If something is wrong with table, it is not created

- If table is created but I forgot proper column name, I can still
  correct it with alter

- If anything major is wrong with table or view, it is new at creation
  time, so I do not mind and I can as well DROP TABLE and start over
  again

- Once satisfied table is working for years, sometimes it gets new
  columns

Within `psql' on command line I am normally inspecting table and
sometimes entering some values. My workflow differs.

When working within buffer with Emacs Lisp, I have something like:

(rcd-sql-begin)
(rcd-sql-do-some-deletion)

and after I am verifying the tables and when satisifed:

(rcd-sql-commit)

Working within Emacs Lisp buffer with SQL is somehow better as I can
work on database while program is being executed. I can revert with
(rcd-sql-commit) stuff related to local buffer.

> > Emacs HyperScope is dynamic knowledge repository that augments
> > knowledge, relates it together and serves as dynamic knowledge
> > repository that follows the technology template project for open
> > hyperdocument systems by Doug Engelbart, definitely similar in its
> > nature to first Javascript based HyperScope. This one is for Emacs.
> 
> > About Dynamic Knowledge Repositories (DKR)
> > https://www.dougengelbart.org/content/view/190/163/
> 
> > TECHNOLOGY TEMPLATE PROJECT OHS Framework
> > https://www.dougengelbart.org/content/view/110/460/
> 
> I know.  So why use that and not Org mode?

One good example is that Org files were meant to be plain text but
today they are not any more, they are text with a lot of structured
information and Org Emacs Lisp programs try to solve the problem by
parsing text.

https://www.dougengelbart.org/content/view/110/460/#2a1a

,----
| Elementary Objects
| 
| Objects are basic content packets of an arbitrary, user and developer
| extensible nature. Types of elementary objects could contain text,
| graphics, equations, tables, spreadsheets, canned-images, video,
| sound, code elements, etc.
`----

I am editing hyperdocuments on a meta level that then later may become
Org documents. Org allows me to insert let us say image, but there is
harder work and more effort to assign that image specific hyperlink.

Org type of a node is normally heading and such have its ambiguous
identifier such as heading name and then it requires users to decide
to make it non-ambiguous by providing either the Org ID or CUSTOM_ID
or some other identifier. In the end I have 50 duplicates minimum on
my system as headings need to be copied and the built-in copy function
C-c C-x M-w does not have and clue what is unique ID, or Org ID or
CUSTOM_ID in other words does not grant any of them their true meaning
and copies the ID even though it is specialized Org based function and
not just Emacs kill.

With few Org files it is manageable, with growing complexity of data
is becomes time wasting machine.

When using Org I can have elementary objects as heading, but in vague
manner. If I change the heading but do not have unique ID it becomes
something else, the reference to heading is lost and hyperlink may not
work. If I use some hyperlink to unique ID, then duplicates are
disturbing.

Org file does not have headings or paragraphs or list items as
elementary objects.

Example of elementary object is this hyperlink below that gives
reference to specific paragraph 2a1a:

https://www.dougengelbart.org/content/view/110/460/#2a1a

Database backed object may have by decision its unique ID about which
user never need to think later and there are no duplicates for
decades. As PostgreSQL user you know what I mean.

- Org file may include graphics but again graphics object is not
  uniquely identified and user has to think of it.

  If I wish to include graphics object I can, and it is uniquely
  identified even if I rename it or displace it, or put somewhere else
  in hierarchy or make symlink to graphics object, it remains there
  for ever for referencing

Same for graphics, equations, tables, spreadsheets, canned-images,
video, sound, code elements, etc.

In a hyperdocument I can put Gnumeric spreadsheet. In Org file I can
only hyperlink to external Gnumeric spreadsheet or use the built-in
Org table features.

Hyperdocument creation does not let user think about it, it should be
maybe one key press to create hyperdocument.

Org mode assumes that users are there to collect their pieces of
hyperlinks and hyperdocuments together. That degrades sharing
capability. 

https://www.dougengelbart.org/content/view/110/460/#2a1a

,----
|  Mixed-Object Documents
|  
|  Documents are a coherent entity made up of an arbitrary mix of
|  elementary objects bundled within a common "envelope" to be stored,
|  transmitted, read, printed, or otherwise be operated on. 2a1b1
| 
|  the MIME specification is an example of a document definition based
|  on a higher level collection of elementary objects.  2a1b2
`----

When elementary objects have been defined then a mixed-object document
can be defined. It may consist of anything. Libreoffice Spreadsheets
and spreadsheets in general, then also ODT files and packages such as
TAR, or email files may be similar to mixed-object document. Yet they
need not offer clear overview of what is inside and no meanings or
relations connecting the dots.

Org file is not mixed-object document, it is rather in itself
elementary object.

Imagine collection of PDF files, and there are 10000 references to
specific articles in PDF files tagged with specific human objects such
as cup, flower, silk and similar. Finding set of articles relating to
flower becomes tedious task. It is useful in creation of art. As that
is exactly how I have found need to create Hyperscope as dynamic
knowledge repository.

The joy of having quick access to PDF specific articles that may be
just 1/3 large on a page is great. No time spending.

One good benefit streamlines quicker location of any node or
elementary object and speed of access to such elementary object.

Enter object once. Never again construct Org hyperlinks by hand. That
principle is used by some features of Org mode but is not well
integrated yet. So people construct hyperlinks by tedious {C-c C-l}
repetition and that is not scalable.

Let us say I wish to insert collection of references, notes, tasks,
images. I can then insert such into Org file without thinking on each
of them, without constructing hyperlinks by hand or doing tedious copy
and paste. General benefit for me is speed and ease.

https://www.dougengelbart.org/content/view/110/460/#2a1c

,----
|     Shared Objects
| 
| Objects and the documents made out of them are shareable among members
| of a networked community: they may be simultaneously viewable,
| manipulable, and changeable according to rules appropriate to their
| nature and supported in access permissions and restrictions contained
| in their definitions.  2a1c1
`----

We all share hyperlinks and documents. But how do I share specific
hyperlink from Org file to specific person or group?

- Copy hyperlink
- Open chat, insert hyperlink
- maybe write description

or

- Copy hyperlink 
- Open up mail client
- Insert hyperlink
- Describe hyperlink
- Insert email address
- maybe designate my own email address

How about:

- choose person to receive this hyperlink
- press ENTER

What about hyperdocuments related to groups and people who should know
about them:

Maybe task have been assigned to group of 3 people. Am I to repeat the
actions of opening files, locating tasks for people, copying task,
opening email client, inserting task, finding email address for person
1, sending email, opening new email, yanking text inside again,
finding email address for person 2, sending email, opening new email,
yanking text, finding email for person 3, sending email.

Then I have 365 such tasks assigned to people, so let me rather kick
the wall with my head.

> Objects and the documents made out of them are shareable among members
> of a networked community: they may be simultaneously viewable

Staff member may use Emacs, but also other interface such as web
browser, or email to access document, receive it, or maybe edit on the
fly by using tramp access.

| manipulable, and changeable according to rules appropriate to their
| nature and supported in access permissions and restrictions contained
| in their definitions.

Fine access permissions and restrictions may be solved with
PostgreSQL.

Row Security Policies
https://www.postgresql.org/docs/13/ddl-rowsecurity.html

My projects are well written in logical order and consists of
strategic plan, tactical plans and multiple plans where some steps of
plans are broken down into projects and each project could be broken
down into specific atomic tasks easily doable or executable by
literate person without higher education.

One set of those tasks in a project may not be for the eyes of people
also participating in the same project. One group of people gets to do
specific tasks, other group of people do other tasks all related to
one project but not all people have permission to get insights into
security concerns. That is all easily solved on project planning with
such attributes such as access permissions and restrictions.

Defining a security policy matter of minutes and later defining group
access or individual access will give permissions accordingly.

https://www.dougengelbart.org/content/view/110/460/#2a1d

,----
|     Object ID-Time Stamps
| 
| Each creation or modification of an object automatically results in
| the creation of a stamp containing information concerning the date,
| time, date, and user identification associated with that modification.
| 2a1d1
| 
| Users can filter and control the portrayal of this information and
| their associated content objects.
`----

Org file has its objects which is handling internally such as
headings, properties, tags, body of a heading. But it is not multi
user environment. It does not have feature to have multiple users to
edit tasks, assign tasks in the same time, report on tasks with
concurrency support, where we may all know WHO edited the task and
WHEN and WHAT was previous version of task.

> > Two safety problems are with PostgreSQL data entry editing, one is to
> > save the previouse entries or historical and that I have solved in
> > very simple manner. Other problem is to solve the currently edited
> > text that is nowhere saved. For that reason I wish to find way to
> > automatically save the buffers somewhere but not that buffer is
> > connected to the file being saved.
> 
> > Does anybody have pointers how to do that?

I still need to find way how to open up buffer, edit string from
database so that the intermediate buffer editing still gets its
automatic saving but that buffer is not really connected to
file.

Maybe I could run of the timer a function in the buffer that is saving
it appropriately even inserting into database the temporary
version. So I guess that may be the simplest solution for that case.

> I have absolutely no idea /why/ someone would store Org mode
> data in a database

Org file and parts of Org files are elementary objects. Maybe because
they are part of one overall hyperdocument as mixed-object. Org file
could be one object, text other, video other object, tasks different
objects, specific paragraphs different, specific parts of Org file
different as by their access permissions. Mixed object may contain
directory subtree with bunch of files belonging to such. Including
bunch of files in Org is not integrated or available feature.

Org does not offer finely grained referencing. For example I cannot
specifically reference to report list under some heading unless I
place <<target>> but then again <<targe>> will work only for
HTML. With finely grained based elementary objects I can reference
such objects from other text parts, objects, files, including Org
files.

Today I was sending again the local village miner daily report to a
supervisor of village miners. When I was using org file the key
sequence would be about 100 chars including spaces, find the file if
not bound in bookmark or register, find specific heading, export
heading to ASCII, copy ASCII, open email, insert into email, send
email.

How about: press key to send it by email, choose person, ENTER and
email is sent.

Trying to do everything with Org is limiting my work and I have to
adapt way too many things. Then it is better working on a meta level
and creating useful functions that will relate to Org but not be
dependant of Org stuff and lack of structure.

> Emacs is very good at editing files, Git is very good at versioning
> them, it has plenty of commands to create branches and worktrees and
> everything else one of the millions of projects using it has ever
> need- ed, and Emacs Lisp is more than versatile enough to code every
> imaginable workflow.

One could say that for any mean of communication but general
availability of a tool does not make it proper for every use
case. People have been collaborating before computers as well and
before Git as well. Tools helps us to streamline our workflows, to
minimize our work and efforts, to lessen those repetitive small tasks.

Let us mention Emacs version control and Git version control. Those
are great tools definitely. But that is not everything there is as we
may indefinitely enhance our human processes. What version control
does is great.

But when looking better into it, one can see that version control
could be as well automatic. Programming RCS simpler and well working
revision control system took some time and effort to do it.

But then creating PostgreSQL table and few triggers to provide version
control for editing of any other table takes much less effort.

Then again, what if version control is simply built into file system?
Would not that alone be better?

Look here:

HAMMER is a file system written for DragonFlyBSD
https://www.dragonflybsd.org/hammer/

A convenient undo command is provided for single-file history, diffs,
and extractions. Snapshots may be used to access entire directory
trees.

That may not be "version control" as it does not solve problems that
Git is solving. But something similar could as well solving the
problems for a user transparently without users ever thinking about
version control commands, tools, how it works.

While Git has solved many problems it also created many problems due
to its complex nature. Just look on Internet how many questions are
there related to Git.

Anyway, solving rudimentary version control with PostgreSQL was few
minutes of work. I have made a table where column values from other
tables will be saved and inserted, and before editing those values,
save them in the table.

(defun hlink-edit-description (id) ;; 
  (let* ((description (hlink-description-value id)) ;; fetch description from the hyperdocument ID
	 (description (if description description "")) 
	 (buffer-name (format "HyperScope Editing ID: %d" id)) ;; prepare buffer name
	 (new-description (read-from-buffer description buffer-name))) ;; edit description
    (hyperscope-vc "hlinks" "hlinks_description" id) ;; fetch previous description and store it in version control
    (rcd-db-update-entry "hlinks" "hlinks_description" "text" id new-description *hs*))) ;; insert new description

Simple.

Triggers can be added to each database to store the whole column
values BEFORE UPDATE OR INSERT in a version control table. See:
https://www.postgresql.org/docs/13/sql-createtrigger.html

My version control for database entries relate to ANY tables and any
columns that I specifically decide to keep their revisions in the
version table. Version table is centralized.

And yet version control table could be as well automatically
constructed for each table so not to be centralized and to be
specifically related to the table for which one need version
control. Integration can be automatic:

1. Invoke function
2. Choose table A
3. Automatically construct version control table for table A
4. Automatically add triggers

Finished there. After that, no more thinking about version control for
decades. No programming, developing, files, etc. It does not solve
same problems as Git solves it but it gives oppportunity to diff
versions and see what was changed and when in a simple manner and to
request back some specific changes or whole versions from past.

When editing hyperdocuments or editing database entries, one cannot be
satisfied with Git as Git is not made for that case. Hyperdocument may
have a whole bunch of files included, let us say 50 PDF documents, and
they are not really edited rather stored and annotated in a
database. There are user permissions to be changed, so Git is not
finding itself in that sector. It does not have structured version
control, it is file system based control.

When I say structured version control I mean columns in PostgreSQL
database. If I change author's name that column has its identifier
such as hlinks_authorname and Git does not keep identifiers for finely
grained objects. It keeps it for files and finds what modifications
have been made. But I cannot search those modifications like "Tell me
who changed author's name during February 2020". It is more general
system for file versioning, not specific system for structured
objects.

Anyway it is trivial to solve the problem in a database itself so that
user need not think about that ever again. There is not even a need to
checkout, or invoke commands. PostgreSQL handles also replication, so
once connection is configured there is no need to think how it will be
replicated on other servers.

> Some of the brightest minds have worked on them extensively, either
> from a formally educated perspective or with the ex- perience of
> blood and tears.  These giants are inviting everybody to stand on
> their shoulders, and neither would I ignore them nor would I
> recommend others to do so.

Does that mean authority and well known giant minds should influence
programmers not to program but rather trust giant minds to know it
better and only follow their principles or tools or ways and methods of work?

If that is so, let us define well a list of giant minds, and let us
stop enhancing and programming forever.

Those principles would lead to nowhere. Would there be no rudimentary
version control systems, there would be no Git. Git did not come out
of nothing. Would there be no BSD or Unix system, probably would not
be there Minix and without Minix and GNU there would be no Linux
kernel as of today. Collaborating and contributing to each other is
what develops civilization. We cannot stay in place thinking that
everything one needs is already there produced by some giant minds.

And how much effort somebody put into some feature can be great and
awarding for that person and people dependable on that feature.

But it can also be nonsense and of little value for somebody else who
simply thinks different and can find ways in streamlining processes.

Let us say Org mode versus SMOS that I recently found:

SMOS - A Comprehensive Self-Management System by Tom Sydney Kerckhove
https://smos.cs-syd.eu/features

He could recognize that Org mode is not a plaint text and it is
history. Org file is free structure in the eyes of a user that Org
programs try to manage and structure to be useful for users. The
approach to manage in structural way something that was not meant to
be structured leads to complexities.

As an Org mode user he has created SMOS to streamline task
management. This makes repetition redundant and provides more
reliability. Files maybe exported as Yaml and still imported into
normal Org files (trivial to do). No wonder he has logo eating Org
mode in a sandwich. https://github.com/NorfairKing/smos

SMOS have been built as inspired by Org task management. Org have been
built as expired by Outline mode and plethora of other references.

I could say why Org mode was not built on a database backend? Because
author started in plain text. Would database like GDBM or similar
already be included in Emacs maybe it would have until today many
database backed features. Emacs does everything possible to keep
things in a database.

init file is sample of a database that lacks concurrency
support. Users are allowed to edit various variables and configure
them and they are colliding with the custom system or
custom-set-variables.

Old and new users need to cope with same problem of lack of
concurrency since decades.

My database approach to configuration would be simple:

- variable
- value
- boolean toggle if variable is locked or not.

Locked variable would not get overwritten by other variable,
defcustom, you name it. Once user makes decision this would not get
overwritten by anything. Finally init.el or .emacs file belongs to
user, and not to programmer and user should have full control over it.

Jean