Re: Interacting with PostgreSQL

[Jean Louis <bugs@gnu.support>]

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