Re: On "write function for each reference"

[Jean Louis]

* 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



-- 
Jean

Take action in Free Software Foundation campaigns:
https://www.fsf.org/campaigns

In support of Richard M. Stallman
https://stallmansupport.org/