Skip to content

Latest commit

 

History

History
561 lines (413 loc) · 13.7 KB

ETAF-EORM.md

File metadata and controls

561 lines (413 loc) · 13.7 KB

ETAF-EORM Documentation

ETAF-EORM is an ORM (Object-Relational Mapping) library for Emacs, inspired by Diesel, the popular Rust database ORM. It provides a type-safe, composable query builder interface for SQLite databases, deeply integrated with the ETAF framework.

Table of Contents

Features

  • Schema Definition: Define table schemas with type safety
  • Composable Query Builder: Build complex queries with a chainable API
  • CRUD Operations: Simple and safe Create, Read, Update, Delete operations
  • Migration System: Version-controlled schema management
  • Transaction Support: ACID-compliant transaction handling
  • Type Safety: Automatic type conversion between Emacs Lisp and SQLite
  • Reactive Integration: Automatic UI updates when data changes
  • Query Logging: Optional SQL query logging for debugging
  • Protection: Prevents accidental mass updates/deletes

Requirements

  • Emacs 29.1 or later with SQLite support
  • ETAF framework (for reactive features)

Check if your Emacs has SQLite support:

(and (fboundp 'sqlite-available-p)
     (sqlite-available-p))

Installation

Add ETAF-EORM to your load path and require it:

(add-to-list 'load-path "/path/to/etaf")
(require 'etaf-eorm)

Enable query logging (optional):

(setq etaf-eorm-enable-logging t)

Quick Start

Here's a complete example showing basic usage:

;; 1. Define a schema
(etaf-eorm-define-table users
  (id integer :primary-key t :autoincrement t)
  (name text :not-null t)
  (email text :unique t)
  (age integer)
  (created-at datetime :default current-timestamp))

;; 2. Connect to database
(setq db (etaf-eorm-connect "~/my-app.db"))

;; 3. Create tables
(etaf-eorm-migrate db)

;; 4. Insert data
(etaf-eorm-insert db 'users
  :name "Alice"
  :email "alice@example.com"
  :age 30)

;; 5. Query data
(etaf-eorm-select db 'users
  :where '(> age 25)
  :order-by 'name)

;; 6. Update data
(etaf-eorm-update db 'users
  :set '(:age 31)
  :where '(= name "Alice"))

;; 7. Delete data
(etaf-eorm-delete db 'users
  :where '(= email "alice@example.com"))

;; 8. Disconnect
(etaf-eorm-disconnect db)

Schema Definition

Defining Tables

Use etaf-eorm-define-table to define your database schema:

(etaf-eorm-define-table table-name
  (column-name type &rest options)
  ...)

Supported Types

  • integer - Integer numbers
  • text - Text strings
  • real - Floating point numbers
  • blob - Binary data
  • datetime - Date and time strings
  • boolean - Boolean values (stored as INTEGER)

Column Options

  • :primary-key t - Mark as primary key
  • :autoincrement t - Auto-increment (for INTEGER PRIMARY KEY only)
  • :not-null t - NOT NULL constraint
  • :unique t - UNIQUE constraint
  • :default value - Default value (use current-timestamp for timestamps)
  • :references (table column) - Foreign key reference

Example

(etaf-eorm-define-table blog-posts
  (id integer :primary-key t :autoincrement t)
  (user-id integer :not-null t :references (users id))
  (title text :not-null t)
  (content text)
  (published boolean :default nil)
  (views integer :default 0)
  (created-at datetime :default current-timestamp)
  (updated-at datetime))

CRUD Operations

Create (Insert)

Insert a new row:

(etaf-eorm-insert db 'users
  :name "Bob"
  :email "bob@example.com"
  :age 25)
;; Returns the ID of the inserted row

Read (Select)

Select all rows:

(etaf-eorm-select db 'users)

Select specific columns:

(etaf-eorm-select db 'users
  :columns '(name email))

Select with conditions:

(etaf-eorm-select db 'users
  :where '(and (> age 25) (= active t))
  :order-by '((name asc))
  :limit 10
  :offset 0)

Update

Update rows (requires WHERE clause):

(etaf-eorm-update db 'users
  :set '(:age 31 :email "new@example.com")
  :where '(= name "Alice"))
;; Returns the number of affected rows

Delete

Delete rows (requires WHERE clause):

(etaf-eorm-delete db 'users
  :where '(= email "alice@example.com"))
;; Returns the number of deleted rows

Query Builder

The query builder provides a chainable interface for building queries:

;; Basic query
(etaf-eorm-query-get
  (etaf-eorm-query db 'users))

;; Complex query
(etaf-eorm-query-get
  (etaf-eorm-query-order-by
    (etaf-eorm-query-where
      (etaf-eorm-query-select
        (etaf-eorm-query db 'users)
        'name 'email)
      '(> age 25))
    '((name asc) (age desc))))

;; Get first result
(etaf-eorm-query-first
  (etaf-eorm-query-where
    (etaf-eorm-query db 'users)
    '(= email "alice@example.com")))

WHERE Clause Syntax

ETAF-EORM supports a rich WHERE clause syntax:

Comparison Operators

'(= column value)      ; Equal
'(!= column value)     ; Not equal
'(> column value)      ; Greater than
'(< column value)      ; Less than
'(>= column value)     ; Greater than or equal
'(<= column value)     ; Less than or equal

Pattern Matching

'(like column pattern)     ; LIKE pattern matching
'(in column (val1 val2))   ; IN list

NULL Checks

'(is-null column)          ; IS NULL
'(is-not-null column)      ; IS NOT NULL

Logical Operators

'(and expr1 expr2 ...)     ; Logical AND
'(or expr1 expr2 ...)      ; Logical OR
'(not expr)                ; Logical NOT

Complex Examples

;; Multiple conditions with AND
'(and (> age 25) (= active t) (like email "%@gmail.com"))

;; OR conditions
'(or (= role "admin") (= role "moderator"))

;; Nested conditions
'(and (> age 18)
      (or (= country "US") (= country "CA"))
      (not (= banned t)))

Transactions

Execute multiple operations atomically:

;; Using macro
(etaf-eorm-with-transaction db
  (etaf-eorm-insert db 'accounts :name "Alice" :balance 1000)
  (etaf-eorm-insert db 'accounts :name "Bob" :balance 500))

;; Using function
(etaf-eorm-transaction db
  (lambda ()
    (etaf-eorm-update db 'accounts
      :set '(:balance 900)
      :where '(= name "Alice"))
    (etaf-eorm-update db 'accounts
      :set '(:balance 600)
      :where '(= name "Bob"))))

If any operation fails, the entire transaction is rolled back.

Integration with ETAF

Reactive Queries

Create queries that automatically update when data changes:

(require 'etaf-component)

;; Create a reactive query
(let ((users-ref (etaf-eorm-reactive-query
                   db 'users
                   (lambda (db table)
                     (etaf-eorm-select db table
                       :where '(= active t)
                       :order-by 'name)))))
  
  ;; Watch for changes
  (etaf-watch users-ref
    (lambda (new-val old-val)
      (message "Users updated: %d active users" (length new-val))))
  
  ;; Any insert/update/delete will trigger the watch
  (etaf-eorm-insert db 'users
    :name "Charlie" :active t))

Display Data in ETAF UI

(let* ((users (etaf-eorm-select db 'users :order-by 'name))
       (user-rows
        (mapcar
         (lambda (user)
           `(tr
             (td ,(plist-get user :name))
             (td ,(plist-get user :email))))
         users)))
  
  (etaf-paint-to-buffer "*Users*"
    `(table
      (thead (tr (th "Name") (th "Email")))
      (tbody ,@user-rows))))

Utility Functions

Count

Count rows matching conditions:

(etaf-eorm-count db 'users)
;; => 10

(etaf-eorm-count db 'users :where '(> age 25))
;; => 5

Exists

Check if any rows match conditions:

(etaf-eorm-exists-p db 'users :where '(= email "alice@example.com"))
;; => t or nil

Find by ID

Find a single row by ID:

(etaf-eorm-find-by-id db 'users 42)
;; => plist of the user with id=42

;; Custom ID column
(etaf-eorm-find-by-id db 'posts 123 'post-id)

Migration System

Create tables based on defined schemas:

;; Define schemas first
(etaf-eorm-define-table users
  (id integer :primary-key t :autoincrement t)
  (name text :not-null t))

(etaf-eorm-define-table posts
  (id integer :primary-key t :autoincrement t)
  (title text :not-null t))

;; Create a single table
(etaf-eorm-create-table db 'users)

;; Drop a table
(etaf-eorm-drop-table db 'users)

;; Check if table exists
(etaf-eorm-table-exists-p db 'users)
;; => t or nil

;; Migrate specific tables (recommended for multiple databases)
(etaf-eorm-migrate db1 'users)              ; only users in db1
(etaf-eorm-migrate db2 '(users posts))      ; users and posts in db2

;; Migrate all defined tables (legacy, not recommended for multiple DBs)
(etaf-eorm-migrate db)

Best Practice for Multiple Databases:

When working with multiple databases, always specify which tables to migrate to avoid accidentally creating all tables in all databases:

;; Define all schemas
(etaf-eorm-define-table users ...)
(etaf-eorm-define-table posts ...)
(etaf-eorm-define-table comments ...)

;; Connect to different databases
(setq user-db (etaf-eorm-connect "~/users.db"))
(setq content-db (etaf-eorm-connect "~/content.db"))

;; Migrate specific tables to specific databases
(etaf-eorm-migrate user-db 'users)                    ; only users
(etaf-eorm-migrate content-db '(posts comments))      ; posts and comments

API Reference

Connection Management

  • (etaf-eorm-connect db-path) - Connect to database
  • (etaf-eorm-disconnect db) - Disconnect from database
  • (etaf-eorm-get-connection db-path) - Get or create connection

Schema Definition

  • (etaf-eorm-define-table name &rest columns) - Define table schema
  • (etaf-eorm-get-schema table-name) - Get schema definition

Migrations

  • (etaf-eorm-create-table db table-name) - Create table
  • (etaf-eorm-drop-table db table-name) - Drop table
  • (etaf-eorm-table-exists-p db table-name) - Check if table exists
  • (etaf-eorm-migrate db &optional table-names) - Run migrations
    • table-names can be nil (all tables), a symbol (single table), or a list of symbols (multiple tables)
  • (etaf-eorm-drop-table db table-name) - Drop table
  • (etaf-eorm-table-exists-p db table-name) - Check if table exists
  • (etaf-eorm-migrate db) - Run migrations

CRUD Operations

  • (etaf-eorm-insert db table-name &rest values) - Insert row
  • (etaf-eorm-select db table-name &rest args) - Select rows
  • (etaf-eorm-update db table-name &rest args) - Update rows
  • (etaf-eorm-delete db table-name &rest args) - Delete rows

Query Builder

  • (etaf-eorm-query db table-name) - Create query builder
  • (etaf-eorm-query-select query &rest columns) - Add SELECT
  • (etaf-eorm-query-where query expr) - Add WHERE
  • (etaf-eorm-query-order-by query &rest spec) - Add ORDER BY
  • (etaf-eorm-query-limit query n) - Add LIMIT
  • (etaf-eorm-query-offset query n) - Add OFFSET
  • (etaf-eorm-query-get query) - Execute query
  • (etaf-eorm-query-first query) - Get first result

Transactions

  • (etaf-eorm-transaction db func) - Execute in transaction
  • (etaf-eorm-with-transaction db &rest body) - Transaction macro

Utilities

  • (etaf-eorm-count db table-name &rest args) - Count rows
  • (etaf-eorm-exists-p db table-name &rest args) - Check existence
  • (etaf-eorm-find-by-id db table-name id) - Find by ID

Reactive Integration

  • (etaf-eorm-reactive-query db table-name query-fn) - Create reactive query

Examples

See examples/etaf-eorm-example.el for comprehensive examples including:

  1. Basic CRUD operations
  2. Query builder usage
  3. Transaction handling
  4. Reactive queries
  5. Integration with ETAF UI

Run all examples:

(load-file "examples/etaf-eorm-example.el")
(etaf-eorm-run-all-examples)

Best Practices

  1. Always use WHERE clauses with UPDATE and DELETE to prevent accidental mass operations
  2. Use transactions for operations that need to be atomic
  3. Define schemas before creating tables
  4. Enable logging during development for debugging
  5. Use the query builder for complex queries
  6. Leverage reactive queries for real-time UI updates
  7. Close connections when done to free resources

Inspiration from Diesel

ETAF-EORM takes inspiration from Diesel's design philosophy:

  • Type Safety: Column and table names are checked against schemas
  • Composable Queries: Query builder allows building queries step by step
  • Safe API: Prevents common mistakes like mass updates/deletes
  • Migration System: Schema versioning and management
  • Performance: Efficient query generation and execution

Comparison with Diesel

Feature Diesel (Rust) ETAF-EORM (Emacs Lisp)
Schema Definition Rust structs + macros Emacs Lisp macros
Type Safety Compile-time Runtime
Query Builder Yes Yes
Transactions Yes Yes
Migrations CLI tool Programmatic
Reactive Integration No Yes (with ETAF)
Database Support PostgreSQL, MySQL, SQLite SQLite (via Emacs)

License

GNU General Public License v3.0 or later.

Contributing

Contributions are welcome! Please ensure:

  • Code follows Emacs Lisp conventions
  • Tests are included for new features
  • Documentation is updated
  • Commit messages are clear and descriptive

See Also