Re: On "write function for each reference"

[Eduardo Ochs]

On Sun, 8 Jan 2023 at 18:36, Jean Louis <bugs@gnu.support> wrote:
>
> * Eduardo Ochs <eduardoochs@gmail.com> [2023-01-08 17:32]:
> >   https://lists.gnu.org/archive/html/help-gnu-emacs/2022-10/msg00807.html
> >
> > I know how to run SQLite in a shell prompt, but I've played very
> > little with M-x sql-sqlite... I stumbled on this,
> >
> >               (find-es "sqlite"      "bypass-prompt")
> >   http://angg.twu.net/e/sqlite.e.html#bypass-prompt
> >
> > asked that question, got no answers, and decided to play with other
> > things instead. Can you help me to write a series of examples of how
> > to use SQLite and PostGres starting from examples that are "simple" in
> > the sense below?
>
> When you get confronted with a lot of information at once, as user you
> may get confused.
>
> Using Emacs based M-x sql-sqlite or M-x sql-postgres is more complex
> and harder to understand then using terminal and commands like:
>
> $ sqlite3
>
> or
>
> $ psql
>
> so that would be first to explore.
>
> Here is how to start:
>
> ;; Provide customizable location of SQLite database
>
> (defcustom rcd-people-sqlite-database (concat (expand-file-name 
> user-emacs-directory) "rcd-people.sqlite")
>   "SQLite database file for people management."
>   :type 'file
>   :group 'rcd-people-sqlite)
>
> ;; let us bypass and just do:
>
> (setq rcd-people-sqlite-database "~/my-test.sqlite")
>
> ;; Provide connection handle, as there could be multiple accesses,
>    each handle is entry to database in its own
>
> (defvar rcd-people-sqlite-db nil
>   "Database handle for RCD People Management.")
>
> ;; Connect to database
>
> (defun rcd-people-sqlite-connect ()
>   "Connect to RCD People Management by using SQLite."
>   (setq rcd-people-sqlite-db (sqlite-open
>   rcd-people-sqlite-database)))
>
> (rcd-people-sqlite-connect) ➜ #<sqlite db=0x561e49445cc0 
> name=/home/data1/protected/my-test.sqlite>
>
> ;; Some helper functions
>
> (defun rcd-sqlite-select-p (sql)
>   "Return TRUE if SQL starts with \"SELECT\"."
>   (string-match (rx line-start (zero-or-more blank) "SELECT") sql))
>
> (defun rcd-sqlite (sql db &rest parameters)
>   "Sends SQL queries to SQLIte database and return results.
> db is database handle."
>   (prog1
>       (condition-case err
>           (cond ((rcd-sqlite-select-p sql) (apply 'sqlite-select db sql 
> parameters))
>                 (t (apply 'sqlite-execute db sql parameters)))
>         (error err
>                (message "Signal error: %s" err)
>                ))
>          ;; (if (string-match "error" (cdr err))
>          ;;     (progn
>          ;;       (message "%s"(cdr err))))
>
>     ;; ))
>            ;; re-throw
>            ;;(signal (car err) (cdr err))))
>     (when rcd-sqlite-logging
>       (funcall rcd-sqlite-message-function "%s"
>                (replace-regexp-in-string  (rx (one-or-more (or "\n" (any 
> whitespace)))) " " (string-trim sql))))))
>
> (defvar rcd-sqlite-message-function 'rcd-message
>   "The RCD SQLite message function, like the function `message-box'.")
>
> ;; Create first table of information
>
> (rcd-sqlite "CREATE TABLE books (
>             books_id INTEGER NOT NULL PRIMARY KEY,
>             books_datecreated TEXT NOT NULL DEFAULT (datetime()),
>             books_name TEXT NOT NULL,
>             books_author TEXT,
>             books_location TEXT) STRICT"
>             rcd-people-sqlite-db)
>
> ;; What is inside of table?
>
> (rcd-sqlite "SELECT * FROM books" rcd-people-sqlite-db) ➜ nil
>
> ;; Create insert function
>
> (defun new-book ()
>   (let* ((name (read-string "Book name: "))
>          (location (read-string "Location: "))
>          (author (read-string "Author: ")))
>     (rcd-sqlite "INSERT INTO books
>                              (books_name, books_author, books_location)
>                       VALUES (?, ?, ?)"
>                 rcd-people-sqlite-db (list name author location))))
>
> (new-book) ➜ 1
>
> ;; What is inside of table?
>
> (rcd-sqlite "SELECT * FROM books" rcd-people-sqlite-db) ➜ ((1 "2023-01-08 
> 21:27:49" "My book ABC" "Joe Doe" "On the shelf"))
>
> ;; Delete entry
>
> (rcd-sqlite "DELETE FROM books WHERE books_name = 'My book ABC'" 
> rcd-people-sqlite-db) ➜ 1
>
> ;; What is inside of table?
>
> (rcd-sqlite "SELECT * FROM books" rcd-people-sqlite-db) ➜ nil


Worked perfectly!
Many thanks! =) =) =)
  Eduardo =)