* 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 -- Jean Take action in Free Software Foundation campaigns: https://www.fsf.org/campaigns In support of Richard M. Stallman https://stallmansupport.org/