Re: Interacting with PostgreSQL

[Jean Louis <bugs@gnu.support>]

* 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