### Install Mito using Quicklisp

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Provides the Quicklisp command to load the Mito library. This is the standard method for installing and using Mito in a Common Lisp environment.

```common-lisp
(ql:quickload :mito)
```

--------------------------------

### Install Mito using Roswell

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Shows the Roswell command for installing the Mito library. Roswell is a popular Lisp implementation and tool manager.

```shell
ros install mito
```

--------------------------------

### Custom SQL Queries with Mito (Auto Conversion to Objects)

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

This example demonstrates using Mito's `select-by-sql` to execute custom queries and automatically convert the results into DAO instances. This simplifies data retrieval by handling object instantiation and population.

```common-lisp
(mito:select-by-sql 'user
  (select (:user.*)
    (from :users)
    (where (:in :user.name
                (select (:poster)
                  (from :tweets)
                  (where (:> :tweets.likes 1000))
                  (returning :poster))))))
```

--------------------------------

### Database Migrations with Mito ORM (Common Lisp)

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Provides examples of using Mito for database migrations, including ensuring tables exist, generating migration expressions, and applying migrations. It also touches on SQLite3 specific migration behavior and auto-migration modes.

```common-lisp
(ensure-table-exists 'user)
;-> ;; CREATE TABLE IF NOT EXISTS "user" (
;       "id" BIGSERIAL NOT NULL PRIMARY KEY,
;       "name" VARCHAR(64) NOT NULL,
;       "email" VARCHAR(128),
;       "created_at" TIMESTAMP,
;       "updated_at" TIMESTAMP
;   ) () [0 rows] | MITO.DAO:ENSURE-TABLE-EXISTS

;; No changes
(mito:migration-expressions 'user)
;=> NIL

(mito:deftable user ()
  ((name :col-type (:varchar 64))
   (email :col-type (:varchar 128)))
  (:unique-keys email))

(mito:migration-expressions 'user)
;=> (#<SXQL-STATEMENT: ALTER TABLE user ALTER COLUMN email TYPE character varying(128), ALTER COLUMN email SET NOT NULL>
;    #<SXQL-STATEMENT: CREATE UNIQUE INDEX unique_user_email ON user (email)>)

(mito:migrate-table 'user)
;-> ;; ALTER TABLE "user" ALTER COLUMN "email" TYPE character varying(128), ALTER COLUMN "email" SET NOT NULL () [0 rows] | MITO.MIGRATION.TABLE:MIGRATE-TABLE
;   ;; CREATE UNIQUE INDEX "unique_user_email" ON "user" ("email") () [0 rows] | MITO.MIGRATION.TABLE:MIGRATE-TABLE
;-> (#<SXQL-STATEMENT: ALTER TABLE user ALTER COLUMN email TYPE character varying(128), ALTER COLUMN email SET NOT NULL>
;    #<SXQL-STATEMENT: CREATE UNIQUE INDEX unique_user_email ON user (email)>)

;; SQLite3 migration creates temporary tables with pre-migration data. To delete them after migration is complete set
;; `mito:*migration-keep-temp-tables*` to `nil`. It has no effect on other drivers.

;; Auto migrations
;; If `mito:*auto-migration-mode*` is set to `t`, and you are connected to a database, Mito will run migrations after
;; each change to model definitions.

```

--------------------------------

### Implement Before Trigger for DAO Insertion

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Shows how to define a ':before' method for the 'insert-dao' generic function to execute custom logic before a new DAO object is inserted into the database. This example prints a message indicating the user being added.

```common-lisp
(defmethod mito:insert-dao :before ((object user))
  (format t "~&Adding ~S...~%" (user-name object)))

(mito:create-dao 'user :name "Eitaro Fukamachi" :email "e.arrows@gmail.com")
```

--------------------------------

### Raw SQL Queries in Common Lisp

Source: https://context7.com/fukamachi/mito/llms.txt

Details how to execute raw SQL queries using `execute-sql` and `retrieve-by-sql`. This is useful for operations not directly supported by the ORM. Examples cover executing updates with and without parameters, retrieving results in different formats (plist, alist, values), and converting raw results to DAO objects.

```common-lisp
;; Execute raw SQL (returns row count)
(mito:execute-sql "UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01'")
;=> 42

;; Execute with parameters
(mito:execute-sql "UPDATE users SET name = ? WHERE id = ?" '("New Name" 1))

;; Retrieve results as plist (default)
(mito:retrieve-by-sql "SELECT * FROM users WHERE status = ?" :binds '("active"))
;=> ((:id 1 :name "Fukamachi" :email "e.arrows@gmail.com" :status "active")
;    (:id 2 :name "Shinmera" :email "shinmera@tymoon.eu" :status "active"))

;; Retrieve as alist
(mito:retrieve-by-sql "SELECT id, name FROM users" :format :alist)
;=> ((("id" . 1) ("name" . "Fukamachi"))
;    (("id" . 2) ("name" . "Shinmera")))

;; Retrieve as values (list of lists)
(mito:retrieve-by-sql "SELECT id, name FROM users" :format :values)
;=> ((1 "Fukamachi") (2 "Shinmera"))

;; Using SxQL for query building
(mito:retrieve-by-sql
  (sxql:select (:user.*)
    (sxql:from :users)
    (sxql:where (:in :user.name
                     (sxql:select (:poster)
                       (sxql:from :tweets)
                       (sxql:where (:> :tweets.likes 1000)))))))
;=> ((:name "Shinmera" :email "shinmera@tymoon.eu") ...)

;; Convert raw results to DAO objects
(mito:select-by-sql 'user
  (sxql:select (:*)
    (sxql:from :user)
    (sxql:where (:> :login-count 100))))
;=> (#<USER {1003E769E3}> #<USER {10040637A3}>)
```

--------------------------------

### Mito CLI for Database Schema Management (Bash)

Source: https://context7.com/fukamachi/mito/llms.txt

Utilizes the Mito command-line tool for database schema management and migration generation. This facilitates database version control and deployment. Requires Mito CLI to be installed.

```bash
# Install mito CLI via Roswell
ros install mito

# View available commands
mito
# Usage: mito command [option...]
# Commands:
#     generate-migrations
#     migrate
#     migration-status

# Generate migrations for PostgreSQL
mito generate-migrations \
  -t postgres \
  -d myapp \
  -u fukamachi \
  -p secret \
  -s my-app-system

# Run migrations
mito migrate \
  -t postgres \
  -d myapp \
  -u fukamachi \
  -p secret

# Check migration status
mito migration-status \
  -t postgres \
  -d myapp \
  -u fukamachi \
  -p secret

# Dry run (show SQL without executing)
mito migrate \
  -t postgres \
  -d myapp \
  --dry-run

# Specify custom migration directory
mito generate-migrations \
  -t mysql \
  -d myapp \
  -D /path/to/migrations/
```

--------------------------------

### Get Table Definition SQL with Mito

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Generates the SQL statement for creating a defined table using Mito. This is useful for inspecting the schema or for manual database management.

```common-lisp
(mito:table-definition 'user)
;=> (#<SXQL-STATEMENT: CREATE TABLE user (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, email VARCHAR(128))>) 
```

```common-lisp
(mito:table-definition 'tweet)
;=> (#<SXQL-STATEMENT: CREATE TABLE tweet (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, status TEXT NOT NULL, user_id BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP, updated_at TIMESTAMP)>)
```

--------------------------------

### JOIN Queries in Common Lisp

Source: https://context7.com/fukamachi/mito/llms.txt

Explains how to use `joins` to filter results based on related table columns. It can be combined with `includes` for both filtering and eager loading. Examples show `INNER JOIN` for filtering by active users and `LEFT JOIN` to include tweets without users.

```common-lisp
;; Filter tweets by active users using INNER JOIN
(mito:select-dao 'tweet
  (joins 'user)
  (sxql:where (:= :user.status "active")))
;-> ;; SELECT tweet.* FROM tweet INNER JOIN user ON tweet.user_id = user.id
;      WHERE (user.status = ?)

;; LEFT JOIN to include tweets without users
(mito:select-dao 'tweet
  (joins 'user :type :left)
  (sxql:where (:is-null :user.id)))
;-> ;; SELECT tweet.* FROM tweet LEFT JOIN user ON tweet.user_id = user.id
;      WHERE (user.id IS NULL)

;; Filter AND load foreign objects (combine joins + includes)
(mito:select-dao 'tweet
  (joins 'user)
  (includes 'user)  ; Also populate the ghost slot
  (sxql:where (:= :user.status "active")))
```

--------------------------------

### Column Type Options in Mito `deftable`

Source: https://context7.com/fukamachi/mito/llms.txt

Defines columns within `deftable` using the `:col-type` option, supporting various standard SQL and database-specific types. Includes examples for common types, nullable columns, numeric types with qualifiers, and primary key definitions.

```common-lisp
;; Common column types
(mito:deftable example ()
  ((int-col :col-type :integer)
   (bigint-col :col-type :bigint)
   (text-col :col-type :text)
   (varchar-col :col-type (:varchar 255))
   (timestamp-col :col-type :timestamp)
   (timestamptz-col :col-type :timestamptz)
   (binary-col :col-type :binary)
   (bytea-col :col-type :bytea)))  ; PostgreSQL

;; Nullable columns
(mito:deftable user ()
  ((name :col-type (:varchar 64))
   (nickname :col-type (or (:varchar 64) :null))))  ; nullable

;; Numeric types with multiple qualifiers (use string format)
(mito:deftable product ()
  ((price :col-type "numeric(10,2)")
   (quantity :col-type :integer)))

;; Primary key definition
(mito:deftable session ()
  ((token :col-type (:varchar 64)
          :primary-key t)
   (user-id :col-type :bigint)))
```

--------------------------------

### Create DB Tables with Mito

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

This snippet demonstrates how to create database tables using Mito. It utilizes `mito:execute-sql` with table definitions and `mito:ensure-table-exists` to manage table creation.

```common-lisp
(mapc #'mito:execute-sql (mito:table-definition 'user))

(mito:ensure-table-exists 'user)
```

--------------------------------

### Create and Manage Database Tables with Mito

Source: https://context7.com/fukamachi/mito/llms.txt

Demonstrates how to create, ensure existence, and recreate database tables using Mito's `ensure-table-exists` and `recreate-table` functions. It also shows how to manually execute table definition SQL.

```common-lisp
;; Create table if it doesn't exist
(mito:ensure-table-exists 'user)
;-> ;; CREATE TABLE IF NOT EXISTS "user" (
;       "id" BIGSERIAL NOT NULL PRIMARY KEY,
;       "name" VARCHAR(64) NOT NULL,
;       "email" VARCHAR(128),
;       "created_at" TIMESTAMP,
;       "updated_at" TIMESTAMP
;   ) () [0 rows]

;; Execute table definition SQL manually
(mapc #'mito:execute-sql (mito:table-definition 'user))

;; Drop and recreate table
(mito:recreate-table 'user)
```

--------------------------------

### Build Custom Queries with Mito and SxQL

Source: https://context7.com/fukamachi/mito/llms.txt

Explains how to construct complex database queries using Mito's `select-dao` in conjunction with SxQL clauses for filtering, ordering, limiting, and pagination. Supports various conditions like LIKE, AND, OR, and IN.

```common-lisp
;; Basic select with WHERE clause
(mito:select-dao 'tweet
  (sxql:where (:like :status "%Japan%")))
;=> (#<TWEET {1003513EC3}> #<TWEET {1007BABEF3}>)

;; Multiple conditions
(mito:select-dao 'user
  (sxql:where (:and (:= :status "active")
                    (:> :created-at "2024-01-01"))))

;; Ordering results
(mito:select-dao 'user
  (sxql:where (:= :status "active"))
  (sxql:order-by (:desc :created-at)))

;; Limiting results
(mito:select-dao 'user
  (sxql:order-by (:desc :id))
  (sxql:limit 10))

;; Pagination with offset
(mito:select-dao 'user
  (sxql:order-by :id)
  (sxql:limit 10)
  (sxql:offset 20))

;; Using IN clause
(mito:select-dao 'user
  (sxql:where (:in :id '(1 2 3 4 5))))

;; Complex nested conditions
(mito:select-dao 'user
  (sxql:where (:or (:= :role "admin")
                   (:and (:= :status "active")
                         (:> :login-count 100)))))

```

--------------------------------

### Mito CLI for Schema Versioning

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Details the command-line interface for Mito, used for managing database schema versions. It lists available commands like `generate-migrations` and `migrate`, along with common options for specifying database connection details and directories.

```bash
$ ros install mito
$ mito
Usage: mito command [option...]

Commands:
    generate-migrations
    migrate
    migration-status

Options:
    -t, --type DRIVER-TYPE          DBI driver type (one of "mysql", "postgres" or "sqlite3")
    -d, --database DATABASE-NAME    Database name to use
    -u, --username USERNAME         Username for RDBMS
    -p, --password PASSWORD         Password for RDBMS
    -s, --system SYSTEM             ASDF system to load (several -s's allowed)
    -D, --directory DIRECTORY       Directory path to keep migration SQL files (default: "/Users/nitro_idiot/Programs/lib/mito/db/")
    --dry-run                       List SQL expressions to migrate
    -f, --force                     Create a new empty migration file even when it's unnecessary.

#### Example

```
mito --database postgres --username fukamachi --pasword c0mmon-l1sp
```
```

--------------------------------

### Define and Inherit Tables with Mito

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Demonstrates how to define a base table 'user' and then create a 'temporary-user' table that inherits from 'user', adding a new column 'registered-at'. It also shows how to use DAO-TABLE-MIXIN to create reusable table structures like 'has-email'.

```common-lisp
(mito:deftable user (
   (name :col-type (:varchar 64))
   (email :col-type (:varchar 128)))
  (:unique-keys email))

(mito:deftable temporary-user (user)
  ((registered-at :col-type :timestamp)))

(mito:table-definition 'temporary-user)
```

```common-lisp
(defclass has-email ()
  ((email :col-type (:varchar 128)
          :accessor object-email))
  (:metaclass mito:dao-table-mixin)
  (:unique-keys email))

(mito:deftable user (has-email)
  ((name :col-type (:varchar 64))))

(mito:table-definition 'user)
```

--------------------------------

### Generate SQL CREATE TABLE Statement in Common Lisp

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Demonstrates generating the SQL CREATE TABLE statement for a Mito-defined class using 'mito:table-definition'. The output can then be yielded into a string representation using 'sxql:yield'.

```common-lisp
(mito:table-definition 'user)
;=> (#<SXQL-STATEMENT: CREATE TABLE user (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, email VARCHAR(128), created_at TIMESTAMP, updated_at TIMESTAMP)>)

(sxql:yield *)
;=> "CREATE TABLE user (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, email VARCHAR(128), created_at TIMESTAMP, updated_at TIMESTAMP)"
;   NIL
```

--------------------------------

### Integrate Mito with Clack Web Server

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Shows how to integrate Mito with the Clack web server using a middleware. This allows Mito to manage database connections within the context of a web application.

```common-lisp
(clack:clackup
  (lack:builder
    (:mito '(:sqlite3 :database-name #P"/tmp/myapp.db"))
    ...
    *app*))
```

--------------------------------

### Report Environment Information in Common Lisp

Source: https://github.com/fukamachi/mito/blob/master/CONTRIBUTING.md

This Common Lisp code snippet helps users gather and report essential environment details for bug reporting. It displays information about the operating system, Lisp implementation, ASDF version, and Quicklisp distribution status. This is crucial for developers to reproduce and diagnose issues.

```common-lisp
(flet ((put (k v &rest vs) (format t "~&~A: ~A~{ ~A~}~%" k v vs))) (put "Machine" (software-type) (software-version)) (put "Lisp" (lisp-implementation-type) (lisp-implementation-version) #+(and sbcl (not sb-thread)) "(without threads)") (put "ASDF" (asdf:asdf-version)) (let ((qlversion (ql:dist-version "quicklisp"))) (put "Quicklisp" qlversion (if (string= (car (first (ql:available-dist-versions "quicklisp"))) qlversion) "(latest)" "(update available)"))))
```

--------------------------------

### Connect to Database with Mito

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Establishes a connection to a relational database using Mito. It supports various database drivers like MySQL, PostgreSQL, and SQLite3. The function takes connection details such as database name, username, and password as arguments.

```common-lisp
(mito:connect-toplevel :mysql :database-name "myapp" :username "fukamachi" :password "c0mon-1isp")
;=> #<DBD.MYSQL:<DBD-MYSQL-CONNECTION> {100691BFF3}>
```

```common-lisp
(mito:connect-toplevel :sqlite3
                       :database-name #P":memory:")
```

--------------------------------

### Custom SQL Queries with Mito (Raw Results)

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

This snippet shows how to execute custom SQL queries using Mito's `retrieve-by-sql` function. It returns results as a list of property lists, allowing for flexible data retrieval and manipulation.

```common-lisp
(mito:retrieve-by-sql
  (select (:user.*)
    (from :users)
    ;; Using a subquery to avoid a join and distinct
    ;; Make sure you actually test performance before doing this in production
    (where (:in :user.name
                (select (:poster)
                  (from :tweets)
                  (where (:> :tweets.likes 1000))
                  (returning :poster))))))
```

--------------------------------

### Manage Database Connections with DBI

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Demonstrates managing database connections using `dbi:connect` and `dbi:disconnect`, including using cached connections with `dbi:connect-cached`. This approach allows for lexical binding of connections and ensures proper cleanup.

```common-lisp
(let ((mito:*connection* (dbi:connect :sqlite3 :database-name #P"/tmp/myapp.db")))
  (unwind-protect (progn ...)
    ;; Ensure that the connection is closed.
    (dbi:disconnect mito:*connection*)))
```

```common-lisp
(let ((mito:*connection* (dbi:connect-cached :sqlite3 :database-name #P"/tmp/myapp.db")))
  (unwind-protect (progn ...)
    ;; Ensure that the connection is closed.
    ))
```

--------------------------------

### Define Relationships and Foreign Keys in Mito

Source: https://context7.com/fukamachi/mito/llms.txt

Details how to define relationships between database tables using Mito's `deftable`. Covers setting custom primary keys, defining foreign keys with `:references`, and using `:col-type` with another class name for ghost slots.

```common-lisp
;; Define user table with custom primary key
(mito:deftable user ()
  ((id :col-type (:varchar 36)
       :primary-key t)
   (name :col-type (:varchar 64))
   (email :col-type (or (:varchar 128) :null))))

;; Define tweet with foreign key reference
(mito:deftable tweet ()
  ((status :col-type :text)
   (user-id :references (user id))))  ; References user.id

(mito:table-definition (find-class 'tweet))
;=> (#<SXQL-STATEMENT: CREATE TABLE tweet (
;       id BIGSERIAL NOT NULL PRIMARY KEY,
;       status TEXT NOT NULL,
;       user_id VARCHAR(36) NOT NULL,
;       created_at TIMESTAMPTZ,
;       updated_at TIMESTAMPTZ
;   )>)

;; Alternative: Use col-type with class name (creates ghost slot)
(mito:deftable tweet ()
  ((status :col-type :text)
   (user :col-type user)))  ; Ghost slot - auto resolves to user object

;; Create tweet with user object
(defvar *user* (mito:create-dao 'user :name "Eitaro Fukamachi"))
(mito:create-dao 'tweet :user *user* :status "Hello World!")

;; Find tweet by user object
(mito:find-dao 'tweet :user *user*)
;=> #<TWEET {100835FB33}>
```

--------------------------------

### Configure Inflation and Deflation for Timestamps in Mito

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Configures inflation and deflation for the 'reported-at' timestamp column in the 'user-report' table. It uses `local-time:universal-to-timestamp` for inflation (RDBMS to Mito) and `local-time:timestamp-to-universal` for deflation (Mito to RDBMS), ensuring correct data type conversion.

```common-lisp
(mito:deftable user-report ()
  ((title :col-type (:varchar 100))
   (body :col-type :text
         :initform "")
   (reported-at :col-type :timestamp
                :initform (local-time:now)
                :inflate #'local-time:universal-to-timestamp
                :deflate #'local-time:timestamp-to-universal))
  (:conc-name report-))
```

--------------------------------

### Integrate Mito with Clack/Lack Web Frameworks (Common Lisp)

Source: https://context7.com/fukamachi/mito/llms.txt

Integrates Mito with Clack/Lack web frameworks using provided middleware for automatic connection management. This simplifies database connection handling within web applications. It requires Mito and Clack/Lack libraries.

```common-lisp
;; Using Lack middleware with Clackup
(clack:clackup
  (lack:builder
    (:mito '(:sqlite3 :database-name #P"/tmp/myapp.db"))
    *app*))

;; With MySQL
(clack:clackup
  (lack:builder
    (:mito '(:mysql :database-name "myapp" :username "user" :password "pass"))
    *app*))

;; With PostgreSQL
(clack:clackup
  (lack:builder
    (:mito '(:postgres :database-name "myapp" :username "postgres" :password "secret"))
    *app*))

;; Disable connection caching (useful for SQLite3 or testing)
(clack:clackup
  (lack:builder
    (:mito '(:mysql :database-name "myapp") :no-cache t)
    *app*))
```

--------------------------------

### Iterate Over Select Results with do-select

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Illustrates the use of the 'do-select' macro for iterating over query results one by one, utilizing cursors for PostgreSQL to manage memory efficiently. It contrasts this with a standard 'cl:loop' approach for the same query.

```common-lisp
(do-select (dao (select-dao 'user
                  (where (:< "2024-07-01" :created_at))))
  (when (equal (user-name dao) "Eitaro")
    (return dao)))
```

```common-lisp
(loop for dao in (select-dao 'user
                   (where (:< "2024-07-01" :created_at)))
      when (equal (user-name dao) "Eitaro")
      do (return dao))
```

--------------------------------

### Eager Loading with includes in Common Lisp

Source: https://context7.com/fukamachi/mito/llms.txt

Demonstrates how to use `includes` to eagerly load related objects, preventing N+1 query problems. This method loads all related objects in a single query, improving performance. It's useful when you need to access related data without triggering additional database calls.

```common-lisp
;; BAD: N+1 query problem
(let ((tweets (mito:select-dao 'tweet
                (sxql:where (:like :status "%Japan%")))))
  ;; Each iteration triggers a new SELECT query for user
  (mapcar (lambda (tweet)
            (user-name (tweet-user tweet)))
          tweets))

;; GOOD: Eager loading with includes
(let ((tweets (mito:select-dao 'tweet
                (includes 'user)  ; Load all users in one query
                (sxql:where (:like :status "%Japan%")))))
  ;;-> ;; SELECT * FROM `tweet` WHERE (`status` LIKE ?) ("%Japan%")
  ;;-> ;; SELECT * FROM `user` WHERE (`id` IN (?, ?, ?)) (1, 3, 12)

  ;; No additional SQL queries - users already loaded
  (mapcar (lambda (tweet)
            (user-name (tweet-user tweet)))
          tweets))
;=> ("Fukamachi" "Shinmera" "User3")
```

--------------------------------

### Perform CRUD Operations with Mito DAO

Source: https://context7.com/fukamachi/mito/llms.txt

Illustrates Create, Read, Update, and Delete operations using Mito's Data Access Object (DAO) functions. Covers inserting, finding, retrieving, counting, updating, and deleting records, including variations like shorthand creation and deletion by values.

```common-lisp
;; CREATE - Insert new record
(defvar me
  (make-instance 'user :name "Eitaro Fukamachi" :email "e.arrows@gmail.com"))
(mito:insert-dao me)
;-> ;; INSERT INTO `user` (`name`, `email`, `created_at`, `updated_at`) VALUES (?, ?, ?, ?)
;=> #<USER {10053C4453}>

;; CREATE - Shorthand using create-dao
(mito:create-dao 'user :name "Eitaro Fukamachi" :email "e.arrows@gmail.com")
;=> #<USER {10053C4453}>

;; READ - Find single record by primary key
(mito:find-dao 'user :id 1)
;-> ;; SELECT * FROM `user` WHERE (`id` = ?) LIMIT 1 (1)
;=> #<USER {10077C6073}>

;; READ - Find by any field
(mito:find-dao 'user :email "e.arrows@gmail.com")
;=> #<USER {10077C6073}>

;; READ - Retrieve all records
(mito:retrieve-dao 'user)
;=> (#<USER {10077C6073}> #<USER {10077C6074}>)

;; READ - Retrieve with conditions
(mito:retrieve-dao 'user :name "Eitaro Fukamachi")
;=> (#<USER {10077C6073}>)

;; READ - Count records
(mito:count-dao 'user)
;=> 2

;; READ - Count with conditions
(mito:count-dao 'user :name "Eitaro Fukamachi")
;=> 1

;; UPDATE - Modify and save
(setf (slot-value me 'name) "nitro_idiot")
(mito:save-dao me)
;-> ;; UPDATE `user` SET `name` = ? WHERE (`id` = ?)

;; UPDATE - Using update-dao directly
(mito:update-dao me)

;; UPDATE - Update specific columns only
(mito:update-dao me :columns '(name email))

;; DELETE - Remove record
(mito:delete-dao me)
;-> ;; DELETE FROM `user` WHERE (`id` = ?)

;; DELETE - Delete by values without loading object
(mito:delete-by-values 'user :id 1)
;-> ;; DELETE FROM `user` WHERE (`id` = ?)

;; Get primary key value
(mito:object-id me)
;=> 1
```

--------------------------------

### Inflation and Deflation in Common Lisp

Source: https://context7.com/fukamachi/mito/llms.txt

Explains how to use `:inflate` and `:deflate` functions to convert values between Lisp objects and their database representations. This is particularly useful for handling specific data types like timestamps, ensuring they are correctly converted when reading from or writing to the database.

```common-lisp
;; Define table with inflation/deflation for timestamps
(mito:deftable user-report ()
  ((title :col-type (:varchar 100))
   (body :col-type :text
         :initform "")
   (reported-at :col-type :timestamp
                :initform (local-time:now)
                :inflate #'local-time:universal-to-timestamp
                :deflate #'local-time:timestamp-to-universal))
  (:conc-name report-))

;; Values are automatically converted when reading/writing
(let ((report (mito:create-dao 'user-report
                :title "Bug Report"
                :reported-at (local-time:now))))
  ;; reported-at is stored as universal time in DB
  ;; but accessed as local-time:timestamp in Lisp
  (report-reported-at report))
;=> @2024-01-15T10:30:00.000000Z
```

--------------------------------

### Generate and Execute Database Migrations with Mito

Source: https://context7.com/fukamachi/mito/llms.txt

Automatically generate and execute database migrations when table definitions change. This involves checking for migration expressions, defining or modifying table schemas, and then applying these changes to the database. Mito also provides options to control auto-migration mode and temporary table behavior.

```common-lisp
;; Check what migrations are needed
(mito:migration-expressions 'user)
;=> NIL  ; No changes needed

;; Modify the table definition
(mito:deftable user ()
  ((name :col-type (:varchar 64))
   (email :col-type (:varchar 128)))  ; Changed from nullable
  (:unique-keys email))               ; Added unique constraint

;; View migration expressions
(mito:migration-expressions 'user)
;=> (#<SXQL-STATEMENT: ALTER TABLE user ALTER COLUMN email TYPE character varying(128),
;                       ALTER COLUMN email SET NOT NULL>
;    #<SXQL-STATEMENT: CREATE UNIQUE INDEX unique_user_email ON user (email)>)

;; Execute migrations
(mito:migrate-table 'user)
;-> ;; ALTER TABLE "user" ALTER COLUMN "email" TYPE character varying(128),
;       ALTER COLUMN "email" SET NOT NULL
;-> ;; CREATE UNIQUE INDEX "unique_user_email" ON "user" ("email")

;; Enable auto-migration mode (migrations run automatically on class changes)
(setf mito:*auto-migration-mode* t)

;; SQLite3: Control whether temp tables are kept after migration
(setf mito:*migration-keep-temp-tables* nil)  ; Delete temp tables (default)
```

--------------------------------

### Implement Triggers and Callbacks with Mito Method Combinations

Source: https://context7.com/fukamachi/mito/llms.txt

Define custom logic for DAO operations (insert, update, delete) using method combinations like :before, :after, and :around. This allows implementing triggers and callbacks that execute at specific points during database interactions.

```common-lisp
;; Before insert trigger
(defmethod mito:insert-dao :before ((object user))
  (format t "~&Adding ~S...~%" (user-name object)))

(mito:create-dao 'user :name "Eitaro Fukamachi" :email "e.arrows@gmail.com")
;-> Adding "Eitaro Fukamachi"...
;-> ;; INSERT INTO "user" ...
;=> #<USER {100835FB33}>

;; After insert trigger
(defmethod mito:insert-dao :after ((object user))
  (format t "~&User ~A created with ID ~A~%"
          (user-name object)
          (mito:object-id object)))

;; Around method for transaction wrapping
(defmethod mito:delete-dao :around ((object user))
  (format t "~&About to delete user ~A~%" (user-name object))
  (call-next-method)
  (format t "~&User deleted~%"))
```

--------------------------------

### Mito Table Class Definition Syntax

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Provides the general syntax for defining a Mito table class in Common Lisp. It outlines the structure for class name, column definitions, metaclass, and optional class-level configurations.

```common-lisp
(defclass {class-name} ()
  ({column-definition}*)
  (:metaclass mito:dao-table-class)
  [[class-option]])

column-definition ::= (slot-name [[column-option]])
column-option ::= {:col-type col-type} |
                  {:primary-key boolean} |
                  {:inflate inflation-function} |
                  {:deflate deflation-function} |
                  {:references {class-name | (class-name slot-name)}} |
                  {:ghost boolean}
col-type ::= { keyword |
              (keyword . args) |
              (or keyword :null) |
              (or :null keyword) }
class-option ::= {:primary-key symbol*} |
                 {:unique-keys {symbol | (symbol*)}*} |
                 {:keys {symbol | (symbol*)}*} |
                 {:table-name table-name} |
                 {:auto-pk auto-pk-mixin-class-name} |
                 {:record-timestamps boolean} |
                 {:conc-name conc-name}
auto-pk-mixin-class-name ::= {:serial | :uuid}
conc-name ::= {null | string-designator}
```

--------------------------------

### CRUD Operations with Mito

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

This section details the Create, Read, Update, and Delete (CRUD) operations for data objects using Mito. It covers inserting, retrieving, updating, and deleting records, as well as counting them.

```common-lisp
(defvar me
  (make-instance 'user :name "Eitaro Fukamachi" :email "e.arrows@gmail.com"))

(mito:insert-dao me)

;; Same as above
(mito:create-dao 'user :name "Eitaro Fukamachi" :email "e.arrows@gmail.com")

;; Getting the primary key value
(mito:object-id me)

;; Retrieving from the DB
(mito:find-dao 'user :id 1)

(mito:retrieve-dao 'user)

;; Updating
(setf (slot-value me 'name) "nitro_idiot")

(mito:save-dao me)

;; Deleting
(mito:delete-dao me)
(mito:delete-by-values 'user :id 1)

;; Counting
(mito:count-dao 'user)
```

--------------------------------

### Database Connection Management in Mito

Source: https://context7.com/fukamachi/mito/llms.txt

Establishes and manages database connections using `connect-toplevel` and `disconnect-toplevel`. Connections are stored in the `*connection*` special variable. Supports MySQL, PostgreSQL, and SQLite3, including in-memory databases and lexical binding with cached connections.

```common-lisp
;; Connect to MySQL
(mito:connect-toplevel :mysql
                       :database-name "myapp"
                       :username "fukamachi"
                       :password "c0mon-1isp")
;=> #<DBD.MYSQL:<DBD-MYSQL-CONNECTION> {100691BFF3}>

;; Connect to PostgreSQL
(mito:connect-toplevel :postgres
                       :database-name "myapp"
                       :username "postgres"
                       :password "secret")

;; Connect to SQLite3
(mito:connect-toplevel :sqlite3
                       :database-name #P"/tmp/myapp.db")

;; Connect to SQLite3 in-memory database
(mito:connect-toplevel :sqlite3
                       :database-name #P":memory:")

;; Get current database name
(mito:connection-database-name)
;=> "myapp"

;; Use lexical connection binding
(let ((mito:*connection* (dbi:connect :sqlite3 :database-name #P"/tmp/myapp.db")))
  (unwind-protect (progn
                    ;; Your database operations here
                    )
    (dbi:disconnect mito:*connection*)))

;; Using cached connections (recommended for multi-threaded apps)
(let ((mito:*connection* (dbi:connect-cached :sqlite3 :database-name #P"/tmp/myapp.db")))
  ;; Operations here
  )

;; Disconnect
(mito:disconnect-toplevel)
```

--------------------------------

### Retrieve Class Definition in Common Lisp

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Shows how to retrieve the class object for a defined Mito table using 'find-class'. It also illustrates how to inspect the direct superclasses of the retrieved class, which typically includes 'mito:dao-class'.

```common-lisp
(find-class 'user)
;=> #<MITO.DAO.TABLE:DAO-TABLE-CLASS COMMON-LISP-USER::USER>

(c2mop:class-direct-superclasses *)
;=> (#<STANDARD-CLASS MITO.DAO.TABLE:DAO-CLASS>)
```

--------------------------------

### High-Level Custom Queries with Mito (select-dao)

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

This snippet illustrates the highest-level API for custom queries using Mito's `select-dao`. It allows for complex queries with features like the `includes` clause for handling joins, automatically converting results to DAO objects.

```common-lisp
(mito:select-dao 'user
  (where (:in :user.name
              (select (:poster)
                (from :tweets)
                (where (:> :tweets.likes 1000))
                (returning :poster))))))
```

--------------------------------

### Inspect Table Column Slots in Common Lisp

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Demonstrates how to retrieve and inspect the table column slots associated with a Mito class using the 'mito.class:table-column-slots' function. This is useful for understanding the structure of the mapped database table.

```common-lisp
(mito.class:table-column-slots (find-class 'user))
```

--------------------------------

### Defining Database Tables with `deftable` in Mito

Source: https://context7.com/fukamachi/mito/llms.txt

Defines database table classes using the `deftable` macro. Tables automatically include `id`, `created_at`, and `updated_at` columns by default, which can be customized or disabled. Supports custom accessor prefixes, unique keys, and alternative primary key definitions (UUID, custom).

```common-lisp
;; Basic table definition
(mito:deftable user ()
  ((name :col-type (:varchar 64))
   (email :col-type (or (:varchar 128) :null))))
;=> #<MITO.DAO.TABLE:DAO-TABLE-CLASS COMMON-LISP-USER::USER>

;; Table with custom accessor prefix
(mito:deftable user ()
  ((name :col-type (:varchar 64))
   (email :col-type (or (:varchar 128) :null)))
  (:conc-name my-))

(my-name (make-instance 'user :name "fukamachi"))
;=> "fukamachi"

;; Table with unique keys
(mito:deftable user ()
  ((name :col-type (:varchar 64))
   (email :col-type (:varchar 128)))
  (:unique-keys email))

;; Table without auto-pk (define your own primary key)
(mito:deftable user ()
  ((id :col-type (:varchar 36)
       :primary-key t)
   (name :col-type (:varchar 64))))

;; Table with UUID primary key instead of serial
(mito:deftable user ()
  ((name :col-type (:varchar 64)))
  (:auto-pk :uuid))

;; Table without automatic timestamps
(mito:deftable user ()
  ((name :col-type (:varchar 64)))
  (:record-timestamps nil))

;; View generated SQL
(mito:table-definition 'user)
;=> (#<SXQL-STATEMENT: CREATE TABLE user (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, email VARCHAR(128), created_at TIMESTAMP, updated_at TIMESTAMP)>)
```

--------------------------------

### Define Table Schema with Mito's deftable

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Defines a database table schema using Mito's `deftable` macro. This macro simplifies the process of defining table classes, automatically handling metaclasses and accessors. It allows specifying column types and nullability.

```common-lisp
(mito:deftable user ()
  ((name :col-type (:varchar 64))
   (email :col-type (or (:varchar 128) :null))))
;=> #<MITO.DAO.TABLE:DAO-TABLE-CLASS COMMON-LISP-USER::USER>
```

```common-lisp
(mito:deftable tweet ()
  ((status :col-type :text)
   (user :col-type user)))
;=> #<MITO.DAO.TABLE:DAO-TABLE-CLASS COMMON-LISP-USER::TWEET>
```

```common-lisp
(mito:deftable user ()
  ((name :col-type (:varchar 64))
   (email :col-type (or (:varchar 128) :null)))
  (:conc-name my-))

(my-name (make-instance 'user :name "fukamachi"))
;=> "fukamachi"
```

--------------------------------

### Common Lisp :col-type with Multiple Qualifiers (Workaround)

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Explains a workaround for specifying :col-type with multiple qualifiers (e.g., NUMERIC(10,2)) in Mito. Since direct list notation doesn't work, the entire type definition must be provided as a string.

```common-lisp
(price :col-type "numeric(10,2)")
```

--------------------------------

### Table Inheritance and Mixins in Mito

Source: https://context7.com/fukamachi/mito/llms.txt

Define reusable table structures using inheritance and mixins. Inheritance allows creating new tables based on existing ones, adding or modifying columns. Mixins provide a way to include common column patterns and constraints in multiple tables without direct inheritance.

```common-lisp
;; Define base user table
(mito:deftable user ()
  ((name :col-type (:varchar 64))
   (email :col-type (:varchar 128)))
  (:unique-keys email))

;; Inherit from user and add columns
(mito:deftable temporary-user (user)
  ((registered-at :col-type :timestamp)))

(mito:table-definition 'temporary-user)
;=> (#<SXQL-STATEMENT: CREATE TABLE temporary_user (
;        id BIGSERIAL NOT NULL PRIMARY KEY,
;        name VARCHAR(64) NOT NULL,
;        email VARCHAR(128) NOT NULL,
;        registered_at TIMESTAMP NOT NULL,
;        created_at TIMESTAMP,
;        updated_at TIMESTAMP,
;        UNIQUE (email)
;    )>)

;; Define a mixin (not tied to a specific table)
(defclass has-email ()
  ((email :col-type (:varchar 128)
          :accessor object-email))
  (:metaclass mito:dao-table-mixin)
  (:unique-keys email))

;; Use mixin in table definition
(mito:deftable user (has-email)
  ((name :col-type (:varchar 64))))

(mito:table-definition 'user)
;=> (#<SXQL-STATEMENT: CREATE TABLE user (
;       id BIGSERIAL NOT NULL PRIMARY KEY,
;       name VARCHAR(64) NOT NULL,
;       email VARCHAR(128) NOT NULL,
;       created_at TIMESTAMP,
;       updated_at TIMESTAMP,
;       UNIQUE (email)
;   )>)
```

--------------------------------

### Common Lisp :col-type with Single Qualifier

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Illustrates how to specify a :col-type with a single qualifier in Mito class definitions, such as VARCHAR with a length. This is the standard and recommended way for types with one parameter.

```common-lisp
(name :col-type (:varchar 64))
```

--------------------------------

### Efficiently Iterate Large Result Sets with Mito's do-select

Source: https://context7.com/fukamachi/mito/llms.txt

Iterate over large result sets efficiently using `do-select`, which leverages database cursors (e.g., for PostgreSQL) to minimize memory consumption. This function allows processing data row by row without loading the entire result set into memory.

```common-lisp
;; Iterate over results one by one
(mito:do-select (dao (mito:select-dao 'user
                       (sxql:where (:< "2024-07-01" :created_at))))
  (when (equal (user-name dao) "Eitaro")
    (return dao)))

;; With index tracking
(mito:do-select (dao (mito:select-dao 'user) index)
  (format t "~&Processing user ~A at index ~A~%" (user-name dao) index))

;; Process large datasets without loading all into memory (PostgreSQL)
(mito:do-select (user (mito:select-dao 'user))
  (process-user user))
```

--------------------------------

### Common Lisp :col-type Keywords

Source: https://github.com/fukamachi/mito/blob/master/README.markdown

Lists common keywords recognized for the :col-type option in Mito class definitions. These keywords map to standard SQL data types. Note that some types are database-specific.

```common-lisp
:serial
:bigserial
:timestamptz
:integer
:bytea
:timestamp
:bigint
:unsigned
:int
:binary
:datetime
```