On Sun, 8 Jan 2023 at 18:36, Jean Louis <bugs@gnu.support> wrote: > > * Eduardo Ochs <eduardoo...@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 =)
