From mboxrd@z Thu Jan 1 00:00:00 1970 Path: news.gmane.io!.POSTED.blaine.gmane.org!not-for-mail From: Jean Louis Newsgroups: gmane.emacs.help Subject: Re: Interacting with PostgreSQL Date: Wed, 25 Nov 2020 17:25:56 +0300 Message-ID: References: <87r1oms1z3.fsf@passepartout.tim-landscheidt.de> <87sg8yuz33.fsf@passepartout.tim-landscheidt.de> <87blflvi1j.fsf@passepartout.tim-landscheidt.de> Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit Injection-Info: ciao.gmane.io; posting-host="blaine.gmane.org:116.202.254.214"; logging-data="9397"; mail-complaints-to="usenet@ciao.gmane.io" User-Agent: Mutt/2.0 (3d08634) (2020-11-07) Cc: help-gnu-emacs@gnu.org To: Tim Landscheidt Original-X-From: help-gnu-emacs-bounces+geh-help-gnu-emacs=m.gmane-mx.org@gnu.org Wed Nov 25 15:32:40 2020 Return-path: Envelope-to: geh-help-gnu-emacs@m.gmane-mx.org Original-Received: from lists.gnu.org ([209.51.188.17]) by ciao.gmane.io with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1khvqZ-0002Mi-HU for geh-help-gnu-emacs@m.gmane-mx.org; Wed, 25 Nov 2020 15:32:39 +0100 Original-Received: from localhost ([::1]:58666 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1khvqY-0007UC-IF for geh-help-gnu-emacs@m.gmane-mx.org; Wed, 25 Nov 2020 09:32:38 -0500 Original-Received: from eggs.gnu.org ([2001:470:142:3::10]:58104) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1khvo9-0005ZO-5w for help-gnu-emacs@gnu.org; Wed, 25 Nov 2020 09:30:09 -0500 Original-Received: from static.rcdrun.com ([95.85.24.50]:34247) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1khvo7-0006Oy-84 for help-gnu-emacs@gnu.org; Wed, 25 Nov 2020 09:30:08 -0500 Original-Received: from localhost ([::ffff:41.202.241.56]) (AUTH: PLAIN admin, TLS: TLS1.2,256bits,ECDHE_RSA_AES_256_GCM_SHA384) by static.rcdrun.com with ESMTPSA id 00000000002C1AE4.000000005FBE6A6D.00000A9A; Wed, 25 Nov 2020 14:30:04 +0000 Content-Disposition: inline In-Reply-To: <87blflvi1j.fsf@passepartout.tim-landscheidt.de> Received-SPF: pass client-ip=95.85.24.50; envelope-from=bugs@gnu.support; helo=static.rcdrun.com X-Spam_score_int: -18 X-Spam_score: -1.9 X-Spam_bar: - X-Spam_report: (-1.9 / 5.0 requ) BAYES_00=-1.9, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001 autolearn=ham autolearn_force=no X-Spam_action: no action X-BeenThere: help-gnu-emacs@gnu.org X-Mailman-Version: 2.1.23 Precedence: list List-Id: Users list for the GNU Emacs text editor List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: help-gnu-emacs-bounces+geh-help-gnu-emacs=m.gmane-mx.org@gnu.org Original-Sender: "help-gnu-emacs" Xref: news.gmane.io gmane.emacs.help:125557 Archived-At: * Tim Landscheidt [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 );