### PostgreSQL Connection with pg.el Source: https://context7.com/magit/emacsql/llms.txt Details how to establish a connection to a PostgreSQL database using the `pg.el` library, requiring Emacs 28.1 or later. Demonstrates connection setup, table creation, insertion, and querying, using the same syntax as SQLite. ```elisp ;; Connect to PostgreSQL database (defvar pg-db (emacsql-pg "mydb" "username" :host "localhost" :password "secret" :port 5432)) ;; Use same query syntax as SQLite (emacsql pg-db [:create-table users ([(id integer :primary-key) username email])]) (emacsql pg-db [:insert :into users :values ([1 "alice" "alice@example.com"])]) (emacsql pg-db [:select * :from users]) ;; => ((1 "alice" "alice@example.com")) (emacsql-close pg-db) ``` -------------------------------- ### MySQL Connection with mysql command Source: https://context7.com/magit/emacsql/llms.txt Details how to connect to a MySQL database using the standard `mysql` command-line program. Shows connection setup, table creation, data insertion, and confirms that the same query syntax works across different back-ends. ```elisp ;; Connect to MySQL database (defvar mysql-db (emacsql-mysql "mydb" :user "root" :password "secret" :host "localhost" :port 3306)) ;; Same query syntax works across all back-ends (emacsql mysql-db [:create-table products ([(id integer :primary-key) name (price float)])]) (emacsql mysql-db [:insert :into products :values ([1 "Widget" 9.99] [2 "Gadget" 19.99])]) (emacsql-close mysql-db) ``` -------------------------------- ### Cloning EmacSQL Test Dependencies Source: https://github.com/magit/emacsql/blob/main/README.md Provides shell commands to clone the necessary `pg` and `sqlite3` packages into sibling directories for running the EmacSQL test suite. This setup is required for the Makefile to locate the dependencies. ```shell git clone https://github.com/emarsden/pg-el ../pg git clone https://github.com/pekingduck/emacs-sqlite3-api ../sqlite3 ``` -------------------------------- ### Basic Prepared Statement Structure in EmacSQL Source: https://github.com/magit/emacsql/blob/main/README.md Provides examples of the fundamental structure of prepared EmacSQL s-expression statements, which are vectors starting with a keyword followed by other keywords and values. This structure is used for most query types, including sub-queries. ```elisp [:select ... :from ...] [:select tag :from tags :where (in tag [:select ...])] ``` -------------------------------- ### EmacSQL SQLite Database Utilities Source: https://context7.com/magit/emacsql/llms.txt Provides functions for listing user tables, getting and setting database versions, dumping and restoring databases, and configuring busy timeouts for SQLite connections. ```elisp ;; List all user tables (excluding sqlite_ internal tables) (emacsql-sqlite-list-tables db) ;; => (people departments config) ;; Get database version (emacsql db [:pragma user-version]) ;; => ((0)) ;; Set database version (emacsql db [:pragma (= user-version 1)]) ;; Dump database to SQL file (emacsql-sqlite-dump-database db) ; Creates database.sql (emacsql-sqlite-dump-database db t) ; With version and timestamp ;; Restore database from dump (emacsql-sqlite-restore-database "~/new.db" "~/backup.sql") ;; Configure busy timeout for concurrent access (setq emacsql-sqlite-busy-timeout 30) ; Wait 30 seconds for locks ``` -------------------------------- ### Parameterized Queries in EmacSQL Source: https://context7.com/magit/emacsql/llms.txt Explains how to use parameterized queries for secure and efficient SQL construction in EmacSQL. It details the different parameter types: $i (identifier), $s (scalar), $v (vector), $r (raw string), and $S (schema), providing examples for each. ```elisp ;; $i for identifier (table/column names) (emacsql db [:select * :from $i1] 'people) ;; $s for scalar values (numbers, strings, symbols) (emacsql db [:select * :from people :where (> salary $s1)] 60000) ;; Multiple parameters (emacsql db [:select * :from people :where (and (> salary $s1) (< salary $s2))] 50000 80000) ;; $v for vector values (rows) (emacsql db [:insert :into people :values $v1] '([("Grace" 7 72000.0)])) ;; $r for raw strings (useful for LIKE patterns) (emacsql db [:select * :from people :where (like name $r1)] "%a%") ;; $S for schema definitions (emacsql db [:create-table $i1 $S2] 'dynamic-table '([(id integer :primary-key) data])) ``` -------------------------------- ### EmacSQL Table Schema Definitions Source: https://github.com/magit/emacsql/blob/main/README.md Illustrates how to define table schemas in EmacSQL, including simple schemas with column names, schemas with column constraints, and schemas with table constraints. It also provides examples of foreign key constraints. ```emacs-lisp ;; No constraints schema with four columns: ([name id building room]) ;; Add some column constraints: ([(name :unique) (id integer :primary-key) building room]) ;; Add some table constraints: ([(name :unique) (id integer :primary-key) building room] (:unique [building room]) (:check (> id 0))) ``` ```emacs-lisp ;; "subjects" table schema ([(id integer :primary-key) subject]) ;; "tag" table references subjects ([(subject-id integer) tag] (:foreign-key [subject-id] :references subjects [id] :on-delete :cascade)) ``` -------------------------------- ### Transactions with EmacSQL Source: https://context7.com/magit/emacsql/llms.txt Details how to manage database transactions using EmacSQL, including automatic rollback on errors and support for nested transactions. It provides examples for basic transactions, transactions with error handling, and threading multiple statements within a transaction. ```elisp ;; Basic transaction (emacsql-with-transaction db (emacsql db [:insert :into accounts :values (["checking" 1000])]) (emacsql db [:insert :into accounts :values (["savings" 5000])])) ;; Transaction with rollback on error (condition-case err (emacsql-with-transaction db (emacsql db [:update accounts :set (= balance 500) :where (= name "checking")]) (emacsql db [:update accounts :set (= balance 5500) :where (= name "savings")]) ;; If any operation fails, both updates are rolled back (error "Simulated failure")) (error (message "Transaction failed: %s" err))) ;; Thread multiple statements through a transaction (emacsql-thread db [:delete :from temp-data] [:insert :into temp-data :values ([1 "data1"])] [:insert :into temp-data :values ([2 "data2"])])) ``` -------------------------------- ### PostgreSQL Connection with psql command Source: https://context7.com/magit/emacsql/llms.txt Explains how to connect to a PostgreSQL database by utilizing the standard `psql` command-line tool. Includes checking for `psql` availability and performing standard database operations. ```elisp ;; Connect using psql command (defvar psql-db (emacsql-psql "mydb" :username "postgres" :hostname "localhost" :port "5432")) ;; Check if psql is available (when (null (emacsql-psql-unavailable-p)) (message "psql is available")) ;; Standard operations work the same (emacsql psql-db [:select * :from information-schema:tables :limit 5]) (emacsql-close psql-db) ``` -------------------------------- ### Configuring EmacSQL Test Load Path with Make Source: https://github.com/magit/emacsql/blob/main/README.md Demonstrates how to configure the Emacs load path for EmacSQL tests using the `make` command with the `LOAD_PATH` variable. This is an alternative to cloning dependencies into sibling directories. ```shell make LOAD_PATH='-L path/to/pg -L path/to/sqlite3' ``` -------------------------------- ### Running EmacSQL Tests Source: https://github.com/magit/emacsql/blob/main/README.md The command to execute the EmacSQL test suite after setting up the necessary dependencies. This command assumes the Makefile is present and configured correctly. ```shell make test ``` -------------------------------- ### Connection Management and Cleanup in EmacSQL Source: https://context7.com/magit/emacsql/llms.txt Illustrates connection management using `emacsql-with-connection` for automatic resource cleanup, preventing leaks. It also shows the singleton pattern with `emacsql-sqlite-connection` for managing a single database connection and initializing tables. ```elisp ;; Automatic cleanup with emacsql-with-connection (emacsql-with-connection (db (emacsql-sqlite-open "~/temp.db")) (emacsql db [:create-table test ([id value])]) (emacsql db [:insert :into test :values ([1 "hello"])]) (emacsql db [:select * :from test])) ;; Connection automatically closed here ;; Using emacsql-sqlite-connection for singleton pattern (defvar my-app-db-connection nil) (defun my-app-db () "Return the application database connection, opening if needed." (emacsql-sqlite-connection 'my-app-db-connection "~/.my-app/database.db" #'my-app-init-tables)) (defun my-app-init-tables (db) "Initialize database tables." (emacsql db [:create-table :if-not-exists users ([(id integer :primary-key) (username :unique) email created-at])])) ``` -------------------------------- ### Querying Data with EmacSQL Source: https://context7.com/magit/emacsql/llms.txt Demonstrates how to retrieve data from tables using SELECT statements with various clauses like WHERE, ORDER BY, and aggregate functions. It covers selecting all or specific columns, filtering, pattern matching with LIKE, ordering results, and using aggregate functions like COUNT, MAX, and AVG. It also shows how to use the BETWEEN operator. ```elisp ;; Select all columns from a table (emacsql db [:select * :from people]) ;; => (("Alice" 1 75000.0) ("Bob" 2 68000.0) ...) ;; Select specific columns (emacsql db [:select [name salary] :from people]) ;; => (("Alice" 75000.0) ("Bob" 68000.0) ...) ;; Filter with WHERE clause (emacsql db [:select [name id] :from people :where (> salary 70000)]) ;; => (("Alice" 1) ("Carol" 3) ("Eve" 5)) ;; Using LIKE operator (matches printed representation) (emacsql db [:select * :from people :where (like name '"%ob%"')]) ;; => (("Bob" 2 68000.0)) ;; Order results (emacsql db [:select [name salary] :from people :order-by [(desc salary)]]) ;; Using aggregate functions with funcall (emacsql db [:select (funcall count *) :from people]) ;; => ((6)) (emacsql db [:select (funcall max salary) :from people]) ;; => ((90000.0)) (emacsql db [:select (funcall avg salary) :from people]) ;; => ((69166.67)) ;; BETWEEN using <= with 3 arguments (emacsql db [:select * :from people :where (<= 50000 salary 80000)]) ``` -------------------------------- ### EmacSQL SQLite Database Operations Source: https://github.com/magit/emacsql/blob/main/README.md Demonstrates basic EmacSQL operations for SQLite, including opening a database, creating tables with and without constraints, inserting data, and querying the database. It also shows how to use query templates with placeholders. ```emacs-lisp (defvar db (emacsql-sqlite-open "~/company.db")) ;; Create a table. Table and column identifiers are symbols. (emacsql db [:create-table people ([name id salary])]) ;; Or optionally provide column constraints. (emacsql db [:create-table people ([name (id integer :primary-key) (salary float)])]) ;; Insert some data: (emacsql db [:insert :into people :values (["Jeff" 1000 60000.0] ["Susan" 1001 64000.0])]) ;; Query the database for results: (emacsql db [:select [name id] :from people :where (> salary 62000)]) ;; => (("Susan" 1001)) ;; Queries can be templates, using $1, $2, etc.: (emacsql db [:select [name id] :from people :where (> salary $s1)] 50000) ;; => (("Jeff" 1000) ("Susan" 1001)) ``` -------------------------------- ### Subqueries and Joins in EmacSQL Source: https://context7.com/magit/emacsql/llms.txt Illustrates how to perform advanced queries involving subqueries and JOIN operations for relational data retrieval. Supports subqueries in WHERE clauses, standard JOIN, LEFT JOIN, and using table aliases. ```elisp ;; Subquery in WHERE clause (emacsql db [:select * :from people :where (in id [:select person-id :from managers])]) ;; JOIN operation (emacsql db [:select [p:name d:department-name] :from (as people p) :join (as departments d) :on (= p:department-id d:id)]) ;; LEFT JOIN (emacsql db [:select [p:name d:department-name] :from (as people p) :left-join (as departments d) :on (= p:department-id d:id)]) ;; Using table aliases with colon notation (p:name becomes p.name) (emacsql db [:select [e:name m:name] :from (as employees e) :left-join (as employees m) :on (= e:manager-id m:id)]) ``` -------------------------------- ### Result Binding with emacsql-with-bind Source: https://context7.com/magit/emacsql/llms.txt Shows how to iterate over query results and bind column names to local variables for more readable code. Supports WHERE clauses with parameters and returning the last evaluated body value. ```elisp ;; Bind columns to variables (emacsql-with-bind db [:select [name salary] :from people] (message "Employee: %s earns $%.2f" name salary)) ;; With WHERE clause using parameters (emacsql-with-bind db ([:select [name salary] :from people :where (> salary $s1)] 50000) (insert (format "* %s: $%.0f\n" name salary))) ;; Returns the last evaluated body value (setq high-earners (let (names) (emacsql-with-bind db [:select [name] :from people :where (> salary 70000)] (push name names)) names)) ``` -------------------------------- ### EmacSQL SQL Compilation and Debugging Source: https://context7.com/magit/emacsql/llms.txt Demonstrates how to compile Emacs Lisp s-expressions into SQL strings for development and debugging, and how to enable and view debug logs for a database connection. ```elisp ;; Compile s-expression to SQL string (emacsql-compile nil [:select * :from people :where (> age 21)]) ;; => "SELECT * FROM people WHERE age > 21;" ;; Interactive command to show SQL for expression before point ;; M-x emacsql-show-last-sql ;; Enable debug logging for a connection (emacsql-enable-debugging db) ;; View the log buffer (switch-to-buffer (oref db log-buffer)) ;; Fix vector indentation in emacs-lisp-mode (emacsql-fix-vector-indentation) ;; Now SQL vectors indent properly: ;; (emacsql db [:select * ;; :from people ;; :where (> age 60)]) ``` -------------------------------- ### Using Raw Strings for Special Cases in EmacSQL Source: https://github.com/magit/emacsql/blob/main/README.md Shows how to quote strings in EmacSQL to treat them as 'raw strings'. These are not printed when assembling queries and are intended for specific uses like filenames or pattern matching with LIKE. ```elisp (emacsql db [... :where (like name '"%foo%"')]) (emacsql db [:attach '"/path/to/foo.db"' :as foo]) ``` -------------------------------- ### Create Tables with Schema Definitions (Emacs Lisp) Source: https://context7.com/magit/emacsql/llms.txt Creates database tables using s-expression schema definitions. Supports column constraints (unique, primary-key, type), table constraints (foreign-key, CHECK), and conditional table creation (if-not-exists). Dashes in identifiers are converted to underscores. ```Emacs Lisp ;; Simple table with no constraints (emacsql db [:create-table items ([name id category])]) ;; Table with column type constraints (emacsql db [:create-table people ([(name :unique) (id integer :primary-key) (salary float)])]) ;; Table with foreign key constraints (emacsql db [:create-table subjects ([(id integer :primary-key) subject])]) (emacsql db [:create-table tags ([(subject-id integer) tag] (:foreign-key [subject-id] :references subjects [id] :on-delete :cascade))]) ;; Table with CHECK constraint (emacsql db [:create-table employees ([(id integer :primary-key) (age integer) name] (:check (> age 0)) (:unique [name]))]) ;; Create table only if it doesn't exist (emacsql db [:create-table :if-not-exists config ([key value])]) ``` -------------------------------- ### List Expressions and Operator Conversion in EmacSQL Source: https://github.com/magit/emacsql/blob/main/README.md Illustrates how lists in EmacSQL are treated as expressions, even within row vectors. It also shows how certain traditional keywords are converted into operators like AS, ASC, and DESC. ```elisp [... :where (= name "Bob")] [:select [(/ seconds 60) count] :from ...] [... :order-by [(asc b), (desc a)]] ; "ORDER BY b ASC, a DESC" [:select p:name :from (as people p)] ; "SELECT p.name FROM people AS p" ``` -------------------------------- ### Update and Delete Data in EmacSQL Source: https://context7.com/magit/emacsql/llms.txt Demonstrates how to modify and remove records from a database table using EmacSQL's UPDATE and DELETE statements. Supports single/multiple field updates, calculations, specific row deletion, complex conditions, and table truncation. ```elisp ;; Update single field (emacsql db [:update people :set (= salary 80000) :where (= name "Bob")]) ;; Update multiple fields (emacsql db [:update people :set [(= salary 85000) (= id 20)] :where (= name "Carol")]) ;; Update with calculation (emacsql db [:update people :set (= salary (* salary 1.1)) :where (< salary 60000)]) ;; Delete specific rows (emacsql db [:delete :from people :where (= name "Dave")]) ;; Delete with complex condition (emacsql db [:delete :from people :where (or (< salary 50000) (> id 100))]) ;; Delete all rows (truncate) (emacsql db [:delete :from temp-data]) ``` -------------------------------- ### EmacSQL Identifier Handling Source: https://github.com/magit/emacsql/blob/main/README.md Demonstrates how standalone symbols in EmacSQL are treated as identifiers. To use a symbol as a value, it must be quoted. ```elisp [:insert-into people :values ...] ``` -------------------------------- ### EmacSQL Database Error Handling Source: https://context7.com/magit/emacsql/llms.txt Illustrates how to catch and handle various EmacSQL database errors using Emacs Lisp's condition system, including constraint violations, locked databases, and general errors. ```elisp ;; Catch specific error types (condition-case err (emacsql db [:insert :into people :values ([1 "duplicate" 50000])]) (emacsql-constraint (message "Constraint violation: %s" (cadr err))) (emacsql-locked (message "Database is locked, retrying...") (sleep-for 1) (emacsql db [:insert :into people :values ([1 "duplicate" 50000])])) (emacsql-error (message "Database error: %s" (cadr err)))) ;; Error types available: ;; - emacsql-error: Parent condition for all errors ;; - emacsql-syntax: Invalid SQL statement ;; - emacsql-constraint: Constraint violation ;; - emacsql-locked: Database locked ;; - emacsql-access: Permission or I/O error ;; - emacsql-corruption: Database corruption ;; - emacsql-timeout: Query timeout ;; - emacsql-memory: Out of memory ``` -------------------------------- ### EmacSQL Statement Templates with Parameter Types Source: https://github.com/magit/emacsql/blob/main/README.md Demonstrates using `$tn` templates in EmacSQL for faster statement compilation. 'i' for identifier, 's' for scalar, 'v' for vector, 'r' for raw string, and 'S' for schema. The number indicates the argument position. ```elisp (emacsql db [:select * :from $i1 :where (> salary $s2)] 'employees 50000) (emacsql db [:select * :from employees :where (like name $r1)] "%Smith%") ``` -------------------------------- ### Open SQLite Database Connection (Emacs Lisp) Source: https://context7.com/magit/emacsql/llms.txt Opens a connection to a SQLite database file, creating the directory structure if needed. It supports file-based and in-memory databases, debug logging, and provides functions to check connection status and close the connection. ```Emacs Lisp ;; Open a connection to a file-based database (defvar my-db (emacsql-sqlite-open "~/data/my-app.db")) ;; Open an in-memory database (useful for testing) (defvar temp-db (emacsql-sqlite-open nil)) ;; Open with debug logging enabled (defvar debug-db (emacsql-sqlite-open "~/data/debug.db" t)) ;; Ensure connection is open (when (emacsql-live-p my-db) (message "Database connection is active")) ;; Close connection when done (emacsql-close my-db) ``` -------------------------------- ### Using funcall Operator in EmacSQL Source: https://github.com/magit/emacsql/blob/main/README.md Demonstrates the use of the 'funcall' operator in EmacSQL for function-like SQL operations such as 'count' and 'max'. This allows calling SQL functions within EmacSQL expressions. ```elisp [:select (funcall max age) :from people] ``` -------------------------------- ### EmacSQL Default Indentation vs. Fixed Indentation Source: https://github.com/magit/emacsql/blob/main/README.md Illustrates the default, often 'ugly', indentation of vectors in Emacs Lisp mode when using EmacSQL, and shows the improved indentation after calling `emacsql-fix-vector-indentation`. ```elisp ;; Ugly indentation! (emacsql db [:select * :from people :where (> age 60)]) ;; Such indent! (emacsql db [:select * :from people :where (> age 60)]) ``` -------------------------------- ### EmacSQL Insert Statement with Vector Values Source: https://github.com/magit/emacsql/blob/main/README.md Shows how to use the `$v1` template for inserting multiple rows using the `:values` clause in EmacSQL. Rows must be vectors, not lists, to accommodate this. ```elisp (emacsql db [:insert-into favorite-characters :values $v1] '([0 "Calvin"] [1 "Hobbes"] [3 "Susie"])) ``` -------------------------------- ### Quoting Symbol Literals in EmacSQL Source: https://github.com/magit/emacsql/blob/main/README.md Illustrates how to quote symbol literals in EmacSQL to distinguish them from column references. This is necessary when referring to the symbol itself rather than a column named after the symbol. ```elisp (emacsql db [... :where (= category 'hiking)]) ``` -------------------------------- ### Insert Data into Tables (Emacs Lisp) Source: https://context7.com/magit/emacsql/llms.txt Inserts rows into database tables. Supports inserting single or multiple rows at once, using template parameters for safe query construction, and inserting various Lisp objects which are stored as their printed s-expression representation. ```Emacs Lisp ;; Insert a single row (emacsql db [:insert :into people :values (["Alice" 1 75000.0])]) ;; Insert multiple rows at once (emacsql db [:insert :into people :values (["Bob" 2 68000.0] ["Carol" 3 82000.0] ["Dave" 4 55000.0])]) ;; Insert using template parameters (emacsql db [:insert :into people :values $v1] '(["Eve" 5 90000.0] ["Frank" 6 45000.0])) ;; Insert lisp objects (stored as printed s-expressions) (emacsql db [:insert :into config :values $v1] '(["settings" (theme dark font-size 14)] ["colors" ("#ff0000" "#00ff00" "#0000ff")])) ``` -------------------------------- ### Table Schema Definition in EmacSQL Source: https://github.com/magit/emacsql/blob/main/README.md Shows the specific format for defining a table schema in EmacSQL, which is a list whose first element is a vector. This distinguishes schemata from other data structures. ```elisp [:create-table people ([(id :primary-key) name])] ``` -------------------------------- ### Row-Oriented Data Representation in EmacSQL Source: https://github.com/magit/emacsql/blob/main/README.md Explains that row-oriented information, such as rows for insertion or column sets in a query, are represented as vectors in EmacSQL. This includes nested vectors for multiple rows. ```elisp [:select [id name] :from people] [:select * :from ...] [:values [1 2 3]] [:values ([1 2 3] [4 5 6])] ``` === COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.