From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp11.migadu.com ([2001:41d0:2:4a6f::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms5.migadu.com with LMTPS id WJxYDTAJRWNBGAEAbAwnHQ (envelope-from ) for ; Tue, 11 Oct 2022 08:12:00 +0200 Received: from aspmx1.migadu.com ([2001:41d0:2:4a6f::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp11.migadu.com with LMTPS id GB5TDTAJRWN0FgEA9RJhRA (envelope-from ) for ; Tue, 11 Oct 2022 08:12:00 +0200 Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by aspmx1.migadu.com (Postfix) with ESMTPS id 872AE2852D for ; Tue, 11 Oct 2022 08:11:59 +0200 (CEST) Received: from localhost ([::1]:38798 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1oi8Uf-0000tq-44 for larch@yhetil.org; Tue, 11 Oct 2022 02:11:57 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:47482) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1oi8Ex-0004h6-N3 for emacs-orgmode@gnu.org; Tue, 11 Oct 2022 01:55:43 -0400 Received: from smtp-out-2.univ-rouen.fr ([193.52.152.97]:42334 helo=mailhoc.univ-rouen.fr) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1oi8Et-0003Xy-TQ for emacs-orgmode@gnu.org; Tue, 11 Oct 2022 01:55:43 -0400 Received: from localhost (unknown [80.214.217.147]) (using TLSv1.2 with cipher AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by mailhoc.univ-rouen.fr (Postfix) with ESMTPSA id 5A96B1C43; Tue, 11 Oct 2022 07:55:34 +0200 (CEST) From: =?utf-8?Q?S=C3=A9bastien?= Rey-Coyrehourcq To: Jean Louis Cc: Alan Schmitt , emacs-orgmode Subject: Re: SQLite for contacts and relations to Org - Re: contact management in emacs Date: Tue, 11 Oct 2022 07:54:16 +0200 References: <87lfb9bwff.fsf@m4x.org> <090c6ca8-62e1-edcd-d348-688281c4840d@univ-rouen.fr> User-agent: mu4e 1.8.9; emacs 28.1.91 In-reply-to: Message-ID: <87mta26h0s.fsf@univ-rouen.fr> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="===-=-="; micalg=pgp-sha256; protocol="application/pgp-signature" X-Spamd-Bar: -- X-Rspamd-Server: mailhoc X-Rspamd-Queue-Id: 5A96B1C43 X-Spamd-Result: default: False [-2.20 / 15.00]; ARC_NA(0.00)[]; FROM_HAS_DN(0.00)[]; RCPT_COUNT_THREE(0.00)[3]; TO_MATCH_ENVRCPT_ALL(0.00)[]; MIME_GOOD(-0.20)[multipart/signed,multipart/mixed,text/plain]; TO_DN_ALL(0.00)[]; NEURAL_HAM(-0.00)[-0.967,0]; SIGNED_PGP(-2.00)[]; RCVD_COUNT_ZERO(0.00)[0]; FROM_EQ_ENVFROM(0.00)[]; MIME_TRACE(0.00)[0:+,1:+,2:+,3:+,4:~]; ASN(0.00)[asn:5410, ipnet:80.214.192.0/19, country:FR]; MID_RHS_MATCH_FROM(0.00)[] Received-SPF: pass client-ip=193.52.152.97; envelope-from=sebastien.rey-coyrehourcq@univ-rouen.fr; helo=mailhoc.univ-rouen.fr X-Spam_score_int: -41 X-Spam_score: -4.2 X-Spam_bar: ---- X-Spam_report: (-4.2 / 5.0 requ) BAYES_00=-1.9, RCVD_IN_DNSWL_MED=-2.3, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001, T_FILL_THIS_FORM_SHORT=0.01 autolearn=ham autolearn_force=no X-Spam_action: no action X-BeenThere: emacs-orgmode@gnu.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+larch=yhetil.org@gnu.org Sender: "Emacs-orgmode" X-Migadu-Flow: FLOW_IN X-Migadu-Country: US ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=yhetil.org; s=key1; t=1665468719; h=from:from:sender:sender:reply-to:subject:subject:date:date: message-id:message-id:to:to:cc:cc:mime-version:mime-version: content-type:content-type:in-reply-to:in-reply-to: references:references:list-id:list-help:list-unsubscribe: list-subscribe:list-post; bh=OEuO6j5kjAPBP06ur3cmbNH/wS5l0Zfc3GAIJPMdm8A=; b=avM1TpDzQTzAhrF1nabbR/TJQQSQiSG9VK0OHK3XDa887ZAML4y0Lqj4aNRDiJeS1Bi3K9 mXuoVF+hKzyRkMDrb7XZQeLjCp8V2r3TxxPu1qTo+4s/9gYuu2jm4/JAlN2i0PxPljJVhN E5/VjjpbxBsqTcsUjUqIbDO2XFrmOEaNOCOc9z+ub3gdxfpphqUHolxfghmDX1JVdVqCG8 WsiklaZ+eJdtBFaZ+3+kjnDg76pduhGVoFPSuw1I6Gg0iZiXOePGxe/BYD/oIbvLswCrWm vlG0gTfZvxZ21aEpFezjQyv8BaouxBX7ERmyD2pN077+JHYhmLmc5Y6VJXJXLw== ARC-Seal: i=1; s=key1; d=yhetil.org; t=1665468719; a=rsa-sha256; cv=none; b=PGCF1WhS5Io/GxpKR4Zv6Im1JxvVAmE5z609LNYbsS4JNkLw4KKqIyjBKutI/iQ6beAZsr DdZjNpXEOtj3LvFQUuYRyJdWdIru+KWLexREmFPN3EZr1WT43UesoOCUBSesBqngQhicwS PMc7OrG9KVy0I+SYkeLz0w0sDkbtB98jTFBRb5aXHTt0NV7M2WfIs37xEPVRzOlL/Bzb4r RkU6UaLrGi6rwJINmV//V1Gpog/iZ24R2j6EAmck5AyyzaXamIPfAGDyfDtZRMP9XWFlau YC45Grzr/WSbuavKaRX0mggGyNsr6+Uevq6x1x3c+juwJLmrXUhJXg315zBVpw== ARC-Authentication-Results: i=1; aspmx1.migadu.com; dkim=none; dmarc=none; spf=pass (aspmx1.migadu.com: domain of "emacs-orgmode-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="emacs-orgmode-bounces+larch=yhetil.org@gnu.org" X-Migadu-Spam-Score: -5.79 Authentication-Results: aspmx1.migadu.com; dkim=none; dmarc=none; spf=pass (aspmx1.migadu.com: domain of "emacs-orgmode-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="emacs-orgmode-bounces+larch=yhetil.org@gnu.org" X-Migadu-Queue-Id: 872AE2852D X-Spam-Score: -5.79 X-Migadu-Scanner: scn0.migadu.com X-TUID: 1gY0cz4/eP9B --===-=-= Content-Type: multipart/mixed; boundary="=-=-=" --=-=-= Content-Type: multipart/mixed; boundary="==-=-=" --==-=-= Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Hi Jean Louis, First i want to thank you for this very detailled explanation, this is very= valuable for someone that start in elisp like me. Secondly, i found the idea of using sqlite for contacts very interesting, a= nd i think a lot on this solution on my side the last weeks. With the new e= macs interface to sql, it was easy i suppose to create a =E2=80=9Cboth-way= =E2=80=9D syncing tool between org-contact (to easily maintain info, linkin= g to notes, agenda, etc.) and a database that anyone could reuse, move, sen= d also using mobile phone. I use org-roam that use both of the two world, database and org, in the lig= ht of recent discussion, why we cannot do the same things for org-contact ?= Linking / syncing properties with unique hash/id also stored in a database= . If you have 150000 contact, with 150000 unique ID, and if you want to cre= ate an org-contact file/propertie into an org document, that=E2=80=99s prob= ably easy to inject and maintain some sort of syncing (like org-roam do) be= tween the info in database and the info into some properties block no ? Best regards, SR Jean Louis writes: > * S=C3=A9bastien Rey-Coyrehourcq [2022-09-09 ven. 19:12]: >> Hi, >> >> After some search today on the web, like Alan i try to compare all >> possibility to manage my contact + mail using emacs, mu4e, and org. > > I will describe you how to manage contacts with Emacs in the reverse > example reverse of what org-contacts tries to do for you. > > Org contacts try to keep people in Org file. Emacs 29 has a built-in > SQLite database which allows every Emacs user to keep information in > ordered, structured manner in the relational database. > > For people who do not use latest development Emacs version, there are > SQLite Emacs packages that allow immediate usage of file based > single-user access database, see > > In Emacs 29, here is how I create the SQLite database, or open the existi= ng one: > > (setq my-db (sqlite-open =E2=80=9C~/my-people=E2=80=9D)) > > This is database handle: > > my-db =E2=87=92 # > > This is how to create a table of `people=E2=80=99 in the database. The > structure of the table `people=E2=80=99 is thus pretty much individual, > one can record any kind of entries and be sure if the the entry > is there or not. It does not allow vague guessing with text files > like in Org or Hyrolo in Hyperbole. > > This is example table, one can build upon it. This one contains emails on= ly. > > (sqlite-execute my-db =E2=80=9CCREATE TABLE people1 (people_id INTEGER PR= IMARY KEY, > people_firstname TEXT, > people_middlenames TEXT, > people_name TEXT NOT NULL DEFA= ULT =E2=80=99>>>UNKNOWN<<<=E2=80=98, > people_email TEXT, > people_description TEXT)=E2=80= =9D) > > This is how to get all queries from the table: > > (sqlite-select my-db =E2=80=9CSELECT * FROM people=E2=80=9D) =E2=87=92 nil > > This is how to insert new entry programmatically: > > (sqlite-execute my-db =E2=80=9CINSERT INTO people (people_firstname, peop= le_name, people_email) > VALUES (?, ?, ?) > RETURNING people_id=E2=80=9D > =E2=80=99(=E2=80=9CRobert=E2=80=9D =E2=80=9CWeiner=E2=80=9D =E2=80=9Crs= w@gnu.org=E2=80=9D)) > > Get information back from database, programmatically. Do with information= what you wish. > > (sqlite-select my-db =E2=80=9CSELECT * FROM people=E2=80=9D) =E2=87=92 ((= 1 =E2=80=9CRobert=E2=80=9D nil =E2=80=9CWeiner=E2=80=9D =E2=80=9Crsw@gnu.or= g=E2=80=9D nil) (2 =E2=80=9CRobert=E2=80=9D nil =E2=80=9CWeiner=E2=80=9D = =E2=80=9Crsw@gnu.org=E2=80=9D nil)) > > Delete everything in the database. > > (sqlite-execute my-db =E2=80=9CDELETE FROM people=E2=80=9D) =E2=87=92 0 > > ;; Backup? > > Backup database by simple copy of the file =E2=80=9C~/my-people=E2=80=9D > > (sqlite-select my-db =E2=80=9CSELECT * FROM people=E2=80=9D) =E2=87=92 nil > > Make nicer function to add new people: > > (defun my-people-add () > (interactive) > (let* ((first-name (read-string =E2=80=9CFirst name: =E2=80=9D)) > (middle-names (read-string =E2=80=9CMiddles names: =E2=80=9D)) > (name-or-last-name (let ((result)) > (while (not result) > (setq result (apply =E2=80=99read-string =E2=80=99(=E2=80=9CLast name= : =E2=80=9D)))) > result)) > (email (read-string =E2=80=9CEmail: =E2=80=9D)) > (description (read-string =E2=80=9CDescription: =E2=80=9D))) > (sqlite-execute my-db =E2=80=9CINSERT INTO people (people_firstname, = people_middlenames, people_name, people_email, people_description) > VALUES (?, ?, ?, ?, ?) RETURNING people_id=E2= =80=9D > (list first-name middle-names name-or-last-name description)))) > > Run the function by using (my-people-add) or M-x my-people-add > > Verify that person was added: > > (sqlite-select my-db =E2=80=9CSELECT * FROM people=E2=80=9D) =E2=87=92 ((= 1 =E2=80=9CJoe=E2=80=9D =E2=80=9C=E2=80=9D =E2=80=9CDoe=E2=80=9D nil nil)) > > Entries are returned as list, get the first list entry: > > (caar (sqlite-select my-db =E2=80=9CSELECT people_id FROM people WHERE pe= ople_id =3D 1=E2=80=9D)) =E2=87=92 1 > > My database structure is ALWAYS as so that for TABLE I always > have primary key named TABLE_id. That principle allows to > simplify things. > > Then I can make generic function to fetch from database DB, from > TABLE and COLUMN by using ID: > > (defun sqlite-db-get-entry (table column id db) > =E2=80=9CReturn value for the TABLE, COLUMN, ID from the database PG.= =E2=80=9D > (let* ((sql (format =E2=80=9CSELECT %s FROM %s WHERE %s_id =3D %s=E2=80= =9D column table table id)) > (value (caar (sqlite-select db sql)))) > value)) > > (sqlite-db-get-entry =E2=80=9Cpeople=E2=80=9D =E2=80=9Cpeople_id=E2=80=9D= 1 my-db) =E2=87=92 1 > > (sqlite-db-get-entry =E2=80=9Cpeople=E2=80=9D =E2=80=9Cpeople_name=E2=80= =9D 1 my-db) =E2=87=92 =E2=80=9CDoe=E2=80=9D > > (sqlite-db-get-entry =E2=80=9Cpeople=E2=80=9D =E2=80=9Cpeople_email=E2=80= =9D 1 my-db) =E2=87=92 =E2=80=9Cjoe@example.com=E2=80=9D > > Make it exportable to Org: > > (defun my-people-to-org-single (id) > (let* ((first-name (sqlite-db-get-entry =E2=80=9Cpeople=E2=80=9D =E2=80= =9Cpeople_firstname=E2=80=9D id my-db)) > (middle-names (sqlite-db-get-entry =E2=80=9Cpeople=E2=80=9D =E2=80=9Cpe= ople_middlenames=E2=80=9D id my-db)) > (last-name (sqlite-db-get-entry =E2=80=9Cpeople=E2=80=9D =E2=80=9Cpeopl= e_lastname=E2=80=9D id my-db)) > (email (sqlite-db-get-entry =E2=80=9Cpeople=E2=80=9D =E2=80=9Cpeople_em= ail=E2=80=9D id my-db)) > (description (sqlite-db-get-entry =E2=80=9Cpeople=E2=80=9D =E2=80=9Cpeo= ple_description=E2=80=9D id my-db)) > (heading (list first-name middle-names last-name)) > (heading (mapcar (lambda (e) (cond ((seq-empty-p e) nil) > (t e))) > heading)) > (heading (delq nil heading)) > (heading (concat =E2=80=9C* =E2=80=9D (string-join heading =E2=80=9C = =E2=80=9D) =E2=80=9C\n\n=E2=80=9D)) > (text (with-temp-buffer > (when (and email (> (length email) 0)) > (insert (concat =E2=80=9CE-mail: =E2=80=9D email =E2=80=9C\n\n=E2=80= =9D))) > (when (and description (> (length description) 0)) > (insert description)) > (buffer-string)))) > (concat heading text =E2=80=9C\n\n=E2=80=9D))) > > (my-people-to-org-single 1) =E2=87=92 =E2=80=9C* Joseph Joe > > E-mail: joe@example.com > > This is just a sample description > > =E2=80=9D > > What if we have multiple people like =E2=80=9CJoe Doe=E2=80=9D? Then each= will > have its unique ID by which we have to be able to choose them. > > One approach is to add on the name a bracketed ID number, like: > > =E2=80=9CJoe Doe [1]=E2=80=9D > =E2=80=9CJoe Doe [2]=E2=80=9D > > Another approach is to add various other attributes to make the > entry distinguishable in completions: > > =E2=80=9CJoe Doe, Ukraine [1]=E2=80=9D > =E2=80=9CJoe Doe, Russia [2]=E2=80=9D > =E2=80=9CJoe Doe, USA [3]=E2=80=9D > =E2=80=9CJoe Doe, Emacs Users [4]=E2=80=9D > > To cut the ID from the bracket on the end, following function does it: > > (defun rcd-get-bracketed-id-end (s) > =E2=80=9CReturn the ID number in string S from within first brackets on= its > end. For example it would return 123 from `Some string [123]=E2=80=99=E2= =80=9D > (let* ((match (string-match =E2=80=9C\\[\\([[:digit:]]*\\)\\][[$=E2=80= =9D s))) > (when match > (string-to-number > (substring-no-properties s (match-beginning 1) (match-end 1)))))) > > Let us make generic function that expects bracketed ID and > completes by SQL. This function could be better of course, this > is example: > > (defun my-completing-id-by-sql-end (prompt sql db) > =E2=80=9CReturn the ID as by finding [] on the end. > Argument PROMPT will be displayed to user. > Argument SQL is supplied query. > Argument PG is database handle.=E2=80=9D > (let* ((selection (sqlite-select db sql)) > (choice (completing-read prompt selection))) > (rcd-get-bracketed-id-end choice))) > > Let us now use that generic function to get a completion list: > > (defun my-people-completion-list () > (let ((sql =E2=80=9CSELECT coalesce(CASE WHEN people_firstname IS NOT N= ULL > AND length(people_firstname) > 0 > THEN people_firstname || =E2=80=98, =E2=80=99 > END, =E2=80=98=E2=80=99) || > coalesce(CASE WHEN people_middlenames IS NOT NULL > AND length(people_middlenames) > 0 > THEN people_middlenames || =E2=80=98, =E2=80=99 > END, =E2=80=98=E2=80=99) || > people_name || =E2=80=99 [=E2=80=99 || people_id ||= =E2=80=99]=E2=80=99 FROM people=E2=80=9D)) > (sqlite-select my-db sql))) > > And here is how completion list looks like: > > (my-people-completion-list) =E2=87=92 ((=E2=80=9CJoe, Doe [1]=E2=80=9D)) > > Here is the function to choose person among people and return it=E2=80=99= s ID: > > (defun my-people-complete () > =E2=80=9CReturn person=E2=80=99s ID.=E2=80=9D > (let ((my-people (my-people-completion-list))) > (cond (my-people (rcd-get-bracketed-id-end (completing-read =E2=80=9C= Choose person: =E2=80=9D my-people))) > (t (error =E2=80=9CNo people in database=E2=80=9D))))) > > (my-people-complete) =E2=87=92 1 > > Let us get list of all columns in SQLite table: > > (defun my-sqlite-table-column-list (table) > =E2=80=9CFrom: =E2=80=9D > ;; (sqlite-select my-db =E2=80=9CSELECT name FROM PRAGMA_TABLE_INFO(?)= =E2=80=9D (list table))) > (mapcar =E2=80=99cadr > (sqlite-select my-db =E2=80=9C > SELECT > m.name as tableName, > p.name as columnName > FROM > sqlite_master m > LEFT OUTER JOIN pragma_table_info((m.name)) p ON m.name <> p.name > WHERE > m.type IN (=E2=80=98table=E2=80=99, =E2=80=98view=E2=80=99) > AND m.name NOT LIKE =E2=80=98sqlite_%=E2=80=99 > AND m.name =3D ? > ORDER BY > tableName, > columnName=E2=80=9D (list table)))) > > (my-sqlite-table-column-list =E2=80=9Cpeople=E2=80=9D) =E2=87=92 (=E2=80= =9Cpeople_description=E2=80=9D =E2=80=9Cpeople_email=E2=80=9D =E2=80=9Cpeop= le_firstname=E2=80=9D =E2=80=9Cpeople_id=E2=80=9D =E2=80=9Cpeople_middlenam= es=E2=80=9D =E2=80=9Cpeople_name=E2=80=9D) > > Let us make function to get full person=E2=80=99s name. > > - people_firstname can be empty, what if it is empty, it should not be di= splaed > > - people_middlename should be in the middle, but could be empty > > - people_name represents either last name or company name or > group name, business name, list of people, etc. It must be > there. > > (defun my-people-name (id) > (caar (sqlite-select my-db =E2=80=9CSELECT coalesce(CASE WHEN people_fi= rstname IS NOT NULL > AND length(people_firstname) > 0 > THEN people_firstname || =E2=80=99 =E2=80=99 > END, =E2=80=98=E2=80=99) || > coalesce(CASE WHEN people_middlenames IS NOT NULL > AND length(people_middlenames) > 0 > THEN people_middlenames || =E2=80=99 =E2=80=99 > END, =E2=80=98=E2=80=99) || > people_name > FROM people > WHERE people_id =3D ?=E2=80=9D > (list id)))) > > (my-people-name 1) =E2=87=92 =E2=80=9CJoseph Joe Doe=E2=80=9D > > Now function to edit people by their ID programmatically or > interactively by choice: > > (defun my-people-edit (&optional id) > (interactive) > (let ((id (or id (my-people-complete))) > (columns (my-sqlite-table-column-list =E2=80=9Cpeople=E2=80=9D))) > (cond (id (let ((column-to-edit (completing-read =E2=80=9CWhich colum= n to edit? =E2=80=9D columns nil t))) > (cond ((member column-to-edit columns) (let* ((initial-input (sqlite-db= -get-entry =E2=80=9Cpeople=E2=80=9D column-to-edit id my-db)) > (edited-entry (read-string (format =E2=80=9CEdit `%s=E2=80= =99: =E2=80=9D column-to-edit) initial-input nil initial-input))) > (unless (string=3D initial-input edited-entry) > (sqlite-execute my-db (format =E2=80=9CUPDATE people SET %s =3D= ? WHERE people_id =3D ?=E2=80=9D column-to-edit) > (list edited-entry id))) > (my-people-edit id))) > (t (message =E2=80=9CNo column choosen=E2=80=9D))))) > (t (message =E2=80=9CNo person selected for editing=E2=80=9D))))) > > (sqlite-select my-db =E2=80=9CSELECT * FROM people=E2=80=9D) =E2=87=92 ((= 1 =E2=80=9CJoseph=E2=80=9D =E2=80=9CJoe=E2=80=9D =E2=80=9CDoe Junior=E2=80= =9D =E2=80=9Cjoe@example.com=E2=80=9D =E2=80=9CThis is just a sample descri= ption=E2=80=9D)) > > ;; Delete entry > > Now let us make possibility to delete single entries: > > (defun my-people-delete (&optional id) > (interactive) > (let* ((id (or id (my-people-complete))) > (name (my-people-name id))) > (when (y-or-n-p (format =E2=80=9CDo you wish to delete entry `%s=E2= =80=99? =E2=80=9D name)) > (sqlite-execute my-db =E2=80=9CDELETE FROM people WHERE people_id = =3D ?=E2=80=9D (list id)) > (message =E2=80=9CDeleted entry `%s=E2=80=99=E2=80=9D name)))) > > (sqlite-select my-db =E2=80=9CSELECT * FROM people=E2=80=9D) =E2=87=92 nil > > Add again: > > (my-people-add) > > Select from database again: > > (sqlite-select my-db =E2=80=9CSELECT * FROM people=E2=80=9D) =E2=87=92 ((= 1 =E2=80=9CJoseph=E2=80=9D =E2=80=9Cjoe=E2=80=9D =E2=80=9CDoe=E2=80=9D nil = nil)) > > Edit the entry > (my-people-edit) or M-x my-people-edit > > (sqlite-select my-db =E2=80=9CSELECT * FROM people=E2=80=9D) =E2=87=92 ((= 1 =E2=80=9CJoseph=E2=80=9D =E2=80=9CJoe=E2=80=9D =E2=80=9CDoe=E2=80=9D =E2= =80=9Cjoe@example.com=E2=80=9D =E2=80=9CThis is just a sample description= =E2=80=9D)) > > now we come to export single person to Org entry: > > (my-people-to-org 1) =E2=87=92 =E2=80=9C* Joseph Joe > E-mail: joe@example.com > > This is just a sample description=E2=80=9D > > Now we can make full export to Org of all people in the database: > > (defun my-people-to-org () > (let ((people (flatten-list (sqlite-select my-db =E2=80=9CSELECT people= _id FROM people=E2=80=9D)))) > (with-temp-buffer > (while people > (insert (my-people-to-org-single (pop people)))) > (buffer-string)))) > > (my-people-to-org) =E2=87=92 =E2=80=9C* Joseph Joe > > E-mail: joe@example.com > > This is just a sample description > > * Jane > > > > =E2=80=9D > > Then we can export everything to Org file: > > (defun my-people-export-to-org-file () > (interactive) > (let ((file (read-file-name =E2=80=9CExport people to which Org file? = =E2=80=9D))) > (with-temp-file file > (insert (my-people-to-org))))) > > M-x my-people-export-to-org-file > > now when one is aware that people may be managed very easy in the > SQLite database, one shall examine the modes of SQLite: > > .mode MODE ?TABLE? Set output mode where MODE is one of: > csv Comma-separated values > column Left-aligned columns. (See .wid= th) > html HTML code > insert SQL insert statements for TABLE > line One value per line > list Values delimited by .separator s= tring > tabs Tab-separated values > tcl TCL list elements > > It also means that importing CSV files into SQLite is easy. > Exporting from SQLite to Org file is also easy. > > Exporting SQL is easy: > > .mode insert > sqlite> select * from people; > INSERT INTO =E2=80=9Ctable=E2=80=9D VALUES(1,=E2=80=99Joseph=E2=80=99,=E2= =80=99Joe=E2=80=99,=E2=80=99Doe=E2=80=99,=E2=80=99joe@example.com=E2=80=99,= =E2=80=99This is just a sample description=E2=80=99); > INSERT INTO =E2=80=9Ctable=E2=80=9D VALUES(2,=E2=80=99Jane=E2=80=99,=E2= =80=99=E2=80=99,=E2=80=99Dine=E2=80=99,=E2=80=99=E2=80=99,NULL); > INSERT INTO =E2=80=9Ctable=E2=80=9D VALUES(3,=E2=80=99Robert=E2=80=99,NUL= L,=E2=80=99Weiner=E2=80=99,=E2=80=99rsw@gnu.org=E2=80=99,NULL); > > Or one value per line: > > .mode line > select * from people; > > people_id =3D 1 > people_firstname =3D Joseph > people_middlenames =3D Joe > people_name =3D Doe > people_email =3D joe@example.com > people_description =3D This is just a sample description > > people_id =3D 2 > people_firstname =3D Jane > people_middlenames =3D > people_name =3D Dine > people_email =3D > people_description =3D > > people_id =3D 3 > people_firstname =3D Robert > people_middlenames =3D > people_name =3D Weiner > people_email =3D rsw@gnu.org > people_description =3D > > Now we come to relation of Org to people ID. > > How about this: > > * My heading > :PROPERTIES: > :PEOPLE-ID-RELATED: 1 > :END: > > Now you know that ID is in the file my-people.sqlite and that ID > is 1. That is to stay so, immutable. > > Let us make the report for person: > > (define-derived-mode my-people-view-mode org-mode =E2=80=9CMy People View= Mode=E2=80=9D > =E2=80=9CThis is read only view mode for people=E2=80=9D) > > (defun my-people-org-report (id) > (let* ((name (my-people-name id)) > (buffer (get-buffer-create name)) > (org (my-people-to-org-single id))) > (pop-to-buffer (get-buffer-create =E2=80=9CMy people report=E2=80=9D)) > (read-only-mode 0) > (erase-buffer) > (insert org) > (goto-char (point-min)) > (my-people-view-mode) > (keymap-set my-people-view-mode-map =E2=80=9Cq=E2=80=9D #=E2=80=99quit-= window) > (keymap-set my-people-view-mode-map =E2=80=9Ce=E2=80=9D `(lambda () (in= teractive) (my-people-edit ,id))) > (read-only-mode 1))) > > The above allows to open the new Org buffer where entry from database is = shown: > > (my-people-org-report 1) > > Then I can see this: > > * Joseph Joe > > E-mail: joe@example.com > > This is just a sample description > > now back to heading in your real Org file: > > * My heading > :PROPERTIES: > :PEOPLE-ID-RELATED: 1 > :END: > > If person ID 1 is related to this heading, then I can make > function to quickly see the entry of the person: > > (defun my-people-org-set-people-id-name (id) > (let ((name (my-people-name id))) > (org-set-property =E2=80=9CPEOPLE-ID-NAME=E2=80=9D name))) > > (defun my-people-org-related-view () > (interactive) > (let ((people-id-related (cdr (assoc =E2=80=9CPEOPLE-ID-RELATED=E2=80= =9D (org-entry-properties))))) > (cond (people-id-related (progn (my-people-org-set-people-id-name peopl= e-id-related) > (my-people-org-report people-id-related))) > (t (message =E2=80=9CNo related contact found in this Org heading=E2= =80=9D))))) > > When there is related contact one jumps into generated Org file by C-c r > > and then with =E2=80=9Ce=E2=80=9D one can already edit the contact in gen= eral way by using Emacs minibuffer. > > And what if we jump to related contact, maybe we still need contact > name in properties, so it is generated automatically and directly from > the database. > > * My heading > :PROPERTIES: > :PEOPLE-ID-RELATED: 1 > :PEOPLE-ID-NAME: Joseph Joe Doe > :PEOPLE-ID-EMAIL: joe@example.com > :END: > > What if we need email in properties? > > (defun my-people-org-set-people-id-email (id) > (let ((email (sqlite-db-get-entry =E2=80=9Cpeople=E2=80=9D =E2=80=9Cpeo= ple_email=E2=80=9D id my-db))) > (org-set-property =E2=80=9CPEOPLE-ID-EMAIL=E2=80=9D email))) > > > then > > (defun my-people-org-related-view () > (interactive) > (let ((people-id-related (cdr (assoc =E2=80=9CPEOPLE-ID-RELATED=E2=80= =9D (org-entry-properties))))) > (cond (people-id-related (progn (my-people-org-set-people-id-email peop= le-id-related) > (my-people-org-set-people-id-name people-id-related) > (my-people-org-report people-id-related))) > (t (message =E2=80=9CNo related contact found in this Org heading=E2= =80=9D))))) > > And how to add related person straight from database? > > (defun my-people-org-related-person (&optional id) > (interactive) > (let ((id (or id (my-people-complete)))) > (when id > (org-set-property =E2=80=9CPEOPLE-ID-RELATED=E2=80=9D (format =E2= =80=9C%s=E2=80=9D id))))) > > This all assumes that it is just one person related to heading. > We could make any number of people related to heading, but let > us keep it to this example. > > At this moment maybe you are writing Org headline that is related > to SCHOOL ABC: > > Here is the flow: > > 1. Add new entry in people table by using M-x my-people-add > > 2. Make new headline in Org: > > ** Cleaning project > > 3. Run function M-x my-people-org-related-person > > ** Cleaning project > :PROPERTIES: > :PEOPLE-ID-RELATED: 4 > :PEOPLE-ID-EMAIL: myschool@example.com > :PEOPLE-ID-NAME: SCHOOL ABC > :END: > > At this point you may examine the entry for the ABC SCHOOL by clicking > C-c r and it will update Org properties. > > By using this principle it could update anything in Org but also in > any other type of file. > > By using SQLite or PostgreSQL you can use plethora of OTHER PROGRAMS > to manage entries in your database. > > For example in sqlitebrowser: > > > Now your contacts are separated from Org. Only by using SQLite browser > you may freely enter new contacts in the database and edit them by > your wish. There are many various tools to manage databases. > > I am editing contacts by using tabulated list mode in Emacs: > > ID 6 > Date created =E2=80=9C2021-06-25 15:50:35.1712+03=E2= =80=9D > Date modified =E2=80=9C2022-10-06 12:37:31.877754+03= =E2=80=9D > Prefix nil > First name =E2=80=9CJoe=E2=80=9D > Middle names nil > Last name (People List Name) =E2=80=9CDoe=E2=80=9D > Suffix nil > Hyperdocument Set #1 nil > Profile Image nil > ID Document nil > Lead source =E2=80=9Cthispersondoesnotexist.com/=E2= =80=9D > Title nil > Department nil > Date of birth or Begin Date nil > End date nil > Don=E2=80=99t contact nil > Description nil > Modified by username =E2=80=9Cmaddox=E2=80=9D > Created by username =E2=80=9Cmaddox=E2=80=9D > Introduced by =E2=80=9CVicente Ramirez=E2=80=9D > Other names nil > Tokens =E2=80=9C=E2=80=98data=E2=80=99:2 =E2= =80=98enter=E2=80=99:1 =E2=80=98lastnam=E2=80=99:5 =E2=80=98people.people= =E2=80=99:4=E2=80=9D > Rank 0 > Assigned to nil > People type =E2=80=9CIndividual Person=E2=80=9D > Reports to nil > Communication Language nil > SIC Code nil > Industry nil > Tag #1 nil > Tag #2 nil > Tag #3 nil > Active =E2=80=9CActive=E2=80=9D > FROM Identity nil > Internal information nil > > But I can as well edit my contacts by using external tools and > exchange information with other software. > > Do you wish to share your contacts? That is easy, one can simple send > the full file to somebody. > > By followed the thought patterns described one shall understand that > contacts may be separated from single mode (Org) and separated from > single software (Emacs) and that it will not minimize or limit you but > rather widen the capacities and usefulness for human. --==-=-=-- --=-=-=-- --===-=-= Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQJcBAEBCABGFiEEQdBRWS1ZqcB6tN8l3FXLa3BDQW4FAmNFBVMoHHNlYmFzdGll bi5yZXktY295cmVob3VyY3FAdW5pdi1yb3Vlbi5mcgAKCRDcVctrcENBbiKzD/4t 8b97BjWxxmm5m7TaqSfLwkkDdGwrceGQLycMT5PX37U0qqm+Y1Lj7motB/903hiT VhORTSpb/MeA9vxNAUeLEl5ojN8bCjnn2kALf/qHaPnMoJZ2ZIYi79xDqO7Hqccw vwYHClHiPP/jPjTfSGbV2zKVFl+Sd3yoZu/Z27P2JMYdQ/ySui6k3wrykEg2LLVC SvaPVJw1sbLdcpjtie0NJuUumLj7I6JQIkZHO+miG5dBsOcWY1UJyTS+0Yv1A50R HnRBUiyTkBJpZmPJLSABNagcCCF6/1SYVgtIHywK/Ge1bawp8WDfakP2vBiQcYf8 pQyH7Le5Wk8Xq4lORGIAnRZacBN6yiqiX3o3g1Rrb2BbPOnl+F/k+yO/0C5Jwhnj mwAiFLMZT2S9apU+jL0oCwVWeQWBysCcOMYuxC7RxRprg24Aqn2pYTcYwyZA418o nuiPXcD7SjGxPN/ZDHj62S8+TK47Hb6w6xkWaIcnwEkHOHq+tCMmvwngxCE2x0DH SaULhHWJR9W80JMznEyyOk5Xkj8HAIwTL0yMrwVYyJ72c9X8BqeeNcT9oYI/0mu6 tjIiHMImFlKHFhVHYCgaZ84uZ/jqsJB4swh3DEa0daBOj9HcMClX713DvHeDPS56 3RHWLYj+QYJrqkuvPlMzQ/ijiZWpGHJjJEBexI/lbg== =BOLD -----END PGP SIGNATURE----- --===-=-=--