<commit_before># SQLite.swift Documentation

  - [Installation](#installation)
  - [Getting Started](#getting-started)
    - [Connecting to a Database](#connecting-to-a-database)
      - [Read-Write Databases](#read-write-databases)
      - [Read-Only Databases](#read-only-databases)
      - [In-Memory Databases](#in-memory-databases)
    - [A Note on Thread-Safety](#a-note-on-thread-safety)
  - [Building Type-Safe SQL](#building-type-safe-sql)
    - [Expressions](#expressions)
      - [Compound Expressions](#compound-expressions)
    - [Queries](#queries)
  - [Creating a Table](#creating-a-table)
    - [Create Table Options](#create-table-options)
    - [Column Constraints](#column-constraints)
    - [Table Constraints](#table-constraints)
  - [Inserting Rows](#inserting-rows)
    - [Setters](#setters)
  - [Selecting Rows](#selecting-rows)
    - [Iterating and Accessing Values](#iterating-and-accessing-values)
    - [Plucking Rows](#plucking-rows)
    - [Building Complex Queries](#building-complex-queries)
      - [Selecting Columns](#selecting-columns)
      - [Joining Other Tables](#joining-other-tables)
        - [Column Namespacing](#column-namespacing)
        - [Table Aliasing](#table-aliasing)
      - [Filtering Rows](#filtering-rows)
        - [Filter Operators and Functions](#filter-operators-and-functions)
      - [Sorting Rows](#sorting-rows)
      - [Limiting and Paging Results](#limiting-and-paging-results)
      - [Aggregation](#aggregation)
  - [Updating Rows](#updating-rows)
  - [Deleting Rows](#deleting-rows)
  - [Transactions and Savepoints](#transactions-and-savepoints)
  - [Altering the Schema](#altering-the-schema)
    - [Renaming Tables](#renaming-tables)
    - [Adding Columns](#adding-columns)
      - [Added Column Constraints](#added-column-constraints)
    - [Indexes](#indexes)
      - [Creating Indexes](#creating-indexes)
      - [Dropping Indexes](#dropping-indexes)
    - [Dropping Tables](#dropping-tables)
    - [Migrations and Schema Versioning](#migrations-and-schema-versioning)
  - [Custom Types](#custom-types)
    - [Date-Time Values](#date-time-values)
    - [Binary Data](#binary-data)
    - [Custom Type Caveats](#custom-type-caveats)
  - [Other Operators](#other-operators)
  - [Core SQLite Functions](#core-sqlite-functions)
  - [Aggregate SQLite Functions](#aggregate-sqlite-functions)
  - [Executing Arbitrary SQL](#executing-arbitrary-sql)
  - [Logging](#logging)


[↩]: #sqliteswift-documentation


## Installation

> _Note:_ SQLite.swift requires Swift 1.1 (and [Xcode 6.1](https://developer.apple.com/xcode/downloads/)) or greater.

To install SQLite.swift as an Xcode sub-project:

 1. Drag the **SQLite.xcodeproj** file into your own project. ([Submodule](http://git-scm.com/book/en/Git-Tools-Submodules), clone, or [download](https://github.com/stephencelis/SQLite.swift/archive/master.zip) the project first.)

    ![Installation](Resources/installation@2x.png)

 2. In your target’s **Build Phases**, add **SQLite iOS** (or **SQLite Mac**) to the **Target Dependencies** build phase.

 3. Add the appropriate **SQLite.framework** product to the **Link Binary With Libraries** build phase.

 4. Add the same **SQLite.framework** to a **Copy Files** build phase with a **Frameworks** destination. (Add a new build phase if need be.)

You should now be able to `import SQLite` from any of your target’s source files and begin using SQLite.swift.


## Getting Started

To use SQLite.swift classes or structures in your target’s source file, first import the `SQLite` module.

``` swift
import SQLite
``` 


### Connecting to a Database

Database connections are established using the `Database` class. A database is initialized with a path. SQLite will attempt to create the database file if it does not already exist.

``` swift
let db = Database("path/to/db.sqlite3")
```


#### Read-Write Databases

On iOS, you can create a writable database in your app’s **Documents** directory.

``` swift
let path = NSSearchPathForDirectoriesInDomains(
    .DocumentDirectory, .UserDomainMask, true
).first as String

let db = Database("\(path)/db.sqlite3")
```

On OS X, you can use your app’s **Application Support** directory:

``` swift
var path = NSSearchPathForDirectoriesInDomains(
    .ApplicationSupportDirectory, .UserDomainMask, true
).first as String + NSBundle.mainBundle().bundleIdentifier!

// create parent directory iff it doesn't exist
NSFileManager.defaultManager().createDirectoryAtPath(
    path, withIntermediateDirectories: true, attributes: nil, error: nil
)

let db = Database("\(path)/db.sqlite3")
```


#### Read-Only Databases

If you bundle a database with your app, you can establish a _read-only_ connection to it.

``` swift
let path = NSBundle.mainBundle().pathForResource("db", ofType: "sqlite3")!

let db = Database(path, readonly: true)
```

> _Note_: Signed applications cannot modify their bundle resources. If you bundle a database file with your app for the purpose of bootstrapping, copy it to a writable location _before_ establishing a connection (see [Read-Write Databases](#read-write-databases), above, for typical, writable locations).


#### In-Memory Databases

If you omit the path, SQLite.swift will provision an [in-memory database](https://www.sqlite.org/inmemorydb.html).

``` swift
let db = Database() // equivalent to `Database(":memory:")`
```

To create a temporary, disk-backed database, pass an empty file name.

``` swift
let db = Database("")
```

In-memory databases are automatically deleted when the database connection is closed.


### A Note on Thread-Safety

> _Note:_ Every database comes equipped with its own serial queue for statement execution and can be safely accessed across threads. Threads that open transactions and savepoints, however, do not block other threads from executing statements within the transaction.


## Building Type-Safe SQL

SQLite.swift comes with a typed expression layer that directly maps [Swift types](https://developer.apple.com/library/prerelease/ios/documentation/General/Reference/SwiftStandardLibraryReference/) to their [SQLite counterparts](https://www.sqlite.org/datatype3.html).

| Swift Type      | SQLite Type |
| --------------- | ----------- |
| `Int`           | `INTEGER`   |
| `Double`        | `REAL`      |
| `String`        | `TEXT`      |
| `Bool`          | `BOOLEAN`   |
| `nil`           | `NULL`      |
| `SQLite.Blob`*  | `BLOB`      |

> *SQLite.swift defines its own `Blob` structure, which safely wraps the underlying bytes.
>
> See [Custom Types](#custom-types) for more information about extending other classes and structures to work with SQLite.swift.

These expressions (in the form of the structure, [`Expression`](#expressions)) build on one another and, with a query ([`Query`](#queries)), can create and execute SQL statements.


### Expressions

Expressions are generic structures associated with a type ([built-in](#building-type-safe-sql) or [custom](#custom-types)), raw SQL, and (optionally) values to bind to that SQL. Typically, you will only explicitly create expressions to describe your columns, and typically only once per column.

``` swift
let id = Expression<Int>("id")
let email = Expression<String>("email")
let balance = Expression<Double>("balance")
let verified = Expression<Bool>("verified")
```

Use optional generics for expressions that can evaluate to `NULL`.

``` swift
let name = Expression<String?>("name")
```

> _Note:_ The default `Expression` initializer is for [quoted identifiers](https://www.sqlite.org/lang_keywords.html) (_i.e._, column names). To build a literal SQL expression, use `init(literal:)`.


### Compound Expressions

Expressions can be combined with other expressions and types using [filters](#filter-operators-and-functions), and [other operators](#other-operators) and [functions](#core-sqlite-functions). These building blocks can create complex SQLite statements.


### Queries

Queries are structures that reference a database and table name, and can be used to build a variety of statements using expressions. We can create a `Query` by subscripting a database connection with a table name.

``` swift
let users = db["users"]
```

Assuming [the table exists](#creating-a-table), we can immediately [insert](#inserting-rows), [select](#selecting-rows), [update](#updating-rows), and [delete](#deleting-rows) rows.


## Creating a Table

We can run [`CREATE TABLE` statements](https://www.sqlite.org/lang_createtable.html) by calling the `create(table:)` function on a database connection. The following is a basic example of SQLite.swift code (using the [expressions](#expressions) and [query](#queries) above) and the corresponding SQL it generates.

``` swift
db.create(table: users) { t in     // CREATE TABLE "users" (
    t.column(id, primaryKey: true) //     "id" INTEGER PRIMARY KEY NOT NULL,
    t.column(email, unique: true)  //     "email" TEXT UNIQUE NOT NULL,
    t.column(name)                 //     "name" TEXT
}                                  // )
```

> _Note:_ `Expression<T>` structures (in this case, the `id` and `email` columns), generate `NOT NULL` constraints automatically, while `Expression<T?>` structures (`name`) do not.


### Create Table Options

The `create(table:)` function has several default parameters we can override.

  - `temporary` adds a `TEMPORARY` clause to the `CREATE TABLE` statement (to create a temporary table that will automatically drop when the database connection closes). Default: `false`.

    ``` swift
    db.create(table: users, temporary: true) { t in /* ... */ }
    // CREATE TEMPORARY TABLE "users" -- ...
    ```
 
  - `ifNotExists` adds an `IF NOT EXISTS` clause to the `CREATE TABLE` statement (which will bail out gracefully if the table already exists). Default: `false`.

    ``` swift
    db.create(table: users, ifNotExists: true) { t in /* ... */ }
    // CREATE TABLE "users" IF NOT EXISTS -- ...
    ```

### Column Constraints

The `column` function is used for a single column definition. It takes an [expression](#expressions) describing the column name and type, and accepts several parameters that map to various column constraints and clauses.

  - `primaryKey` adds an `INTEGER PRIMARY KEY` constraint to a single column. (See the `primaryKey` function under [Table Constraints](#table-constraints) for non-integer primary keys).

    ``` swift
    t.column(id, primaryKey: true)
    // "id" INTEGER PRIMARY KEY NOT NULL
    ```

    > _Note:_ The `primaryKey` parameter cannot be used alongside `defaultValue` or `references`. If you need to create a column that has a default value and is also a primary and/or foreign key, use the `primaryKey` and `foreignKey` functions mentioned under [Table Constraints](#table-constraints).
    >
    > Primary keys cannot be optional (`Expression<Int?>`).

  - `unique` adds a `UNIQUE` constraint to the column. (See the `unique` function under [Table Constraints](#table-constraints) for uniqueness over multiple columns).

    ``` swift
    t.column(email, unique: true)
    // "email" TEXT UNIQUE NOT NULL
    ```
 
  - `check` attaches a `CHECK` constraint to a column definition in the form of a boolean expression (`Expression<Bool>`). Boolean expressions can be easily built using [filter operators and functions](#filter-operators-and-functions). (See also the `check` function under [Table Constraints](#table-constraints).)

    ``` swift
    t.column(email, check: like("%@%", email))
    // "email" TEXT NOT NULL CHECK ("email" LIKE '%@%')
    ```
 
  - `defaultValue` adds a `DEFAULT` clause to a column definition and _only_ accepts a value (or expression) matching the column’s type. This value is used if none is explicitly provided during [an `INSERT`](#inserting-rows).

    ``` swift
    t.column(name, defaultValue: "Anonymous")
    // "name" TEXT DEFAULT 'Anonymous'
    ```
 
    > _Note:_ The `defaultValue` parameter cannot be used alongside `primaryKey` and `references`. If you need to create a column that has a default value and is also a primary and/or foreign key, use the `primaryKey` and `foreignKey` functions mentioned under [Table Constraints](#table-constraints).

  - `collate` adds a `COLLATE` clause to `Expression<String>` (and `Expression<String?>`) column definitions with [a collating sequence](https://www.sqlite.org/datatype3.html#collation) defined in the `Collation` enumeration.

    ``` swift
    t.column(email, collate: .NoCase)
    // "email" TEXT NOT NULL COLLATE NOCASE
    ```
 
  - `references` adds a `REFERENCES` clause to `Expression<Int>` (and `Expression<Int?>`) column definitions and accepts a table (`Query`) or namespaced column expression. (See the `foreignKey` function under [Table Constraints](#table-constraints) for non-integer foreign key support.)

    ``` swift
    t.column(user_id, references: users[id])
    // "user_id" INTEGER REFERENCES "users"("id")

    t.column(user_id, references: users)
    // "user_id" INTEGER REFERENCES "users"
    // -- assumes "users" has a PRIMARY KEY
    ```

    > _Note:_ The `references` parameter cannot be used alongside `primaryKey` and `defaultValue`. If you need to create a column that has a default value and is also a primary and/or foreign key, use the `primaryKey` and `foreignKey` functions mentioned under [Table Constraints](#table-constraints).


### Table Constraints

Additional constraints may be provided outside the scope of a single column using the following functions.

  - `primaryKey` adds a `PRIMARY KEY` constraint to the table. Unlike [the column constraint, above](#column-constraints), it supports all SQLite types, [ascending and descending orders](#sorting-rows), and composite (multiple column) keys.

    ``` swift
    t.primaryKey(email.asc, name)
    // PRIMARY KEY("email" ASC, "name")
    ```
 
  - `unique` adds a `UNIQUE` constraint to the table. Unlike [the column constraint, above](#column-constraints), it supports composite (multiple column) constraints.

    ``` swift
    t.unique(local, domain)
    // UNIQUE("local", "domain")
    ```
 
  - `check` adds a `CHECK` constraint to the table in the form of a boolean expression (`Expression<Bool>`). Boolean expressions can be easily built using [filter operators and functions](#filter-operators-and-functions). (See also the `check` parameter under [Column Constraints](#column-constraints).)

    ``` swift
    t.check(balance >= 0)
    // CHECK ("balance" >= 0.0)
    ```

  - `foreignKey` adds a `FOREIGN KEY` constraint to the table. Unlike [the `references` constraint, above](#column-constraints), it supports all SQLite types, and both [`ON UPDATE` and `ON DELETE` actions](https://www.sqlite.org/foreignkeys.html#fk_actions).

    ``` swift
    t.foreignKey(user_id, on: users[id], delete: .SetNull)
    // FOREIGN KEY("user_id") REFERENCES "users"("id") ON DELETE SET NULL
    ```

    > _Note:_ Composite foreign keys are not supported at this time. If you add support, please [submit a pull request](https://github.com/stephencelis/SQLite.swift/fork).

<!-- TODO
### Creating a Table from a Select Statement
-->


## Inserting Rows

We can insert rows into a table by calling a [query’s](#queries) `insert` function with a list of [setters](#setters), typically [typed column expressions](#expressions) and values (which can also be expressions), each joined by the `<-` operator.

``` swift
users.insert(email <- "<EMAIL>", name <- "Alice")?
// INSERT INTO "users" ("email", "name") VALUES ('<EMAIL>', 'Alice')
```

The `insert` function can return several different types that are useful in different contexts.

  - An `Int?` representing the inserted row’s [`ROWID`][ROWID] (or `nil` on failure), for simplicity.

    ``` swift
    if let insertID = users.insert(email <- "<EMAIL>") {
       println("inserted id: \(insertID)")
    }
    ```

    We can use the optional nature of the value to disambiguate with a simple `?` or `!`.

    ``` swift
    // ignore failure
    users.insert(email <- "<EMAIL>")?

    // assertion on failure
    users.insert(email <- "<EMAIL>")!
    ```

  - A `Statement`, for [the transaction and savepoint helpers](#transactions-and-savepoints) that take a list of statements.

    ``` swift
    db.transaction(
        users.insert(email <- "<EMAIL>"),
        users.insert(email <- "<EMAIL>")
    )
    // BEGIN DEFERRED TRANSACTION;
    // INSERT INTO "users" ("email") VALUES ('<EMAIL>');
    // INSERT INTO "users" ("email") VALUES ('<EMAIL>');
    // COMMIT TRANSACTION;
    ``` 

  - A tuple of the above [`ROWID`][ROWID] and statement: `(ID: Int?, Statement)`, for flexibility.

    ``` swift
    let (ID, statement) = users.insert(email <- "<EMAIL>")
    if let ID = ID {
       println("inserted id: \(ID)")
    } else if statement.failed {
       println("insertion failed: \(statement.reason)")
    }
    ```

The [`update`](#updating-rows) and [`delete`](#deleting-rows) functions follow similar patterns.

> _Note:_ If `insert` is called without any arguments, the statement will run with a `DEFAULT VALUES` clause. The table must not have any constraints that aren’t fulfilled by default values.
> 
> ``` swift
> timestamps.insert()!
> // INSERT INTO "timestamps" DEFAULT VALUES
> ```


### Setters

SQLite.swift typically uses the `<-` operator to set values during [inserts](#inserting-rows) and [updates](#updating-rows).

``` swift
views.update(count <- 0)
// UPDATE "views" SET "count" = 0 WHERE ("id" = 1)
```

There are also a number of convenience setters that take the existing value into account using native Swift operators.

For example, to atomically increment a column, we can use `++`:

``` swift
views.update(count++) // equivalent to `views.update(count -> count + 1)`
// UPDATE "views" SET "count" = "count" + 1 WHERE ("id" = 1)
```

To take an amount and “move” it via transaction, we can use `-=` and `+=`:

``` swift
let amount = 100.0
db.transaction(
    alice.update(balance -= amount),
    betty.update(balance += amount)
)
// BEGIN DEFERRED TRANSACTION;
// UPDATE "users" SET "balance" = "balance" - 100.0 WHERE ("id" = 1);
// UPDATE "users" SET "balance" = "balance" + 100.0 WHERE ("id" = 2);
// COMMIT TRANSACTION;
```


###### Infix Setters

| Operator | Types              |
| -------- | ------------------ |
| `<-`     | `Value -> Value`   |
| `+=`     | `Number -> Number` |
| `-=`     | `Number -> Number` |
| `*=`     | `Number -> Number` |
| `/=`     | `Number -> Number` |
| `%=`     | `Int -> Int`       |
| `<<=`    | `Int -> Int`       |
| `>>=`    | `Int -> Int`       |
| `&=`     | `Int -> Int`       |
| `||=`    | `Int -> Int`       |
| `^=`     | `Int -> Int`       |
| `+=`     | `String -> String` |


###### Postfix Setters

| Operator | Types        |
| -------- | ------------ |
| `++`     | `Int -> Int` |
| `--`     | `Int -> Int` |


## Selecting Rows

[`Query` structures](#queries) are `SELECT` statements waiting to happen. They execute via [iteration](#iterating-and-accessing-values) and [other means](#plucking-values) of sequence access.


### Iterating and Accessing Values

[Queries](#queries) execute lazily upon iteration. Each row is returned as a `Row` object, which can be subscripted with a [column expression](#expressions) matching one of the columns returned.

``` swift
for user in users {
    println("id: \(user[id]), email: \(user[email]), name: \(user[name])")
    // id: 1, email: <EMAIL>, name: Optional("Alice")
}
// SELECT * FROM "users"
```

`Expression<T>` column values are _automatically unwrapped_ (we’ve made a promise to the compiler that they’ll never be `NULL`), while `Expression<T?>` values remain wrapped.


### Plucking Rows

We can pluck the first row by calling the `first` computed property on [`Query`](#queries).

``` swift
if let user = users.first { /* ... */ } // Row
// SELECT * FROM "users" LIMIT 1
```

To collect all rows into an array, we can simply wrap the sequence (though this is not always the most memory-efficient idea).

``` swift
let all = Array(users)
// SELECT * FROM "users"
```


### Building Complex Queries

[`Query`](#queries) structures have a number of chainable functions that can be used (with [expressions](#expressions)) to add and modify [a number of clauses](https://www.sqlite.org/lang_select.html) to the underlying statement.

``` swift
let query = users.select(email)           // SELECT "email" FROM "users"
                 .filter(name != nil)     // WHERE "name" IS NOT NULL
                 .order(email.desc, name) // ORDER BY "email" DESC, "name"
                 .limit(5, offset: 1)     // LIMIT 5 OFFSET 1
```


#### Selecting Columns

By default, [`Query`](#queries) objects select every column of the result set (using `SELECT *`). We can use the `select` function with a list of [expressions](#expressions) to return specific columns instead.

``` swift
let query = users.select(id, email)
// SELECT "id", "email" FROM "users"
```

<!-- TODO
We can select aggregate values using [aggregate functions](#aggregate-sqlite-functions).

``` swift
let query = users.select(count(*))
// SELECT count(*) FROM "users"
```
-->


#### Joining Other Tables

We can join tables using a [query’s](#queries) `join` function.

``` swift
users.join(posts, on: user_id == users[id])
// SELECT * FROM "users" INNER JOIN "posts" ON ("user_id" = "users"."id")
```

The `join` function takes a [query](#queries) object (for the table being joined on), a join condition (`on`), and is prefixed with an optional join type (default: `.Inner`). Join conditions can be built using [filter operators and functions](#filter-operators-and-functions), generally require [namespacing](#column-namespacing), and sometimes require [aliasing](#table-aliasing).


##### Column Namespacing

When joining tables, column names can become ambiguous. _E.g._, both tables may have an `id` column.

``` swift
let query = users.join(posts, on: user_id == id)
// assertion failure: ambiguous column 'id'
```

We can disambiguate by namespacing `id`.

``` swift
let query = users.join(posts, on: user_id == users[id])
// SELECT * FROM "users" INNER JOIN "posts" ON ("user_id" = "users"."id")
```

Namespacing is achieved by subscripting a [query](#queries) with a [column expression](#expressions) (_e.g._, `users[id]` above becomes `users.id`).

> _Note:_ We can namespace all of a table’s columns using `*`.
>
> ``` swift
> let query = users.select(users[*])
> // SELECT "users".* FROM "users"
> ```


##### Table Aliasing

Occasionally, we need to join a table to itself, in which case we must alias the table with another name. We can achieve this using the [query’s](#queries) `alias` function.

``` swift
let managers = users.alias("managers")

let query = users.join(managers, on: managers[id] == users[manager_id])
// SELECT * FROM "users"
// INNER JOIN "users" AS "managers" ON ("managers"."id" = "users"."manager_id")
```

If query results can have ambiguous column names, row values should be accessed with namespaced [column expressions](#expressions). In the above case, `SELECT *` immediately namespaces all columns of the result set.

``` swift
let user = query.first!
user[id]           // fatal error: ambiguous column 'id'
                   // (please disambiguate: ["users"."id", "managers"."id"])

user[users[id]]    // returns "users"."id"
user[managers[id]] // returns "managers"."id"
```


#### Filtering Rows

SQLite.swift filters rows using a [query’s](#queries) `filter` function with a boolean [expression](#expressions) (`Expression<Bool>`).

``` swift
users.filter(id == 1)
// SELECT * FROM "users" WHERE ("id" = 1)

users.filter(contains([1, 2, 3, 4, 5], id))
// SELECT * FROM "users" WHERE ("id" IN (1, 2, 3, 4, 5))

users.filter(like("<EMAIL>", email))
// SELECT * FROM "users" WHERE ("email" LIKE <EMAIL>')

users.filter(verified && lower(name) == "alice")
// SELECT * FROM "users" WHERE ("verified" AND (lower("name") == 'alice'))

users.filter(verified || balance >= 10_000)
// SELECT * FROM "users" WHERE ("verified" OR ("balance" >= 10000.0))
```

You can build your own boolean expressions by using one of the many [filter operators and functions](#filter-operators-and-functions).

> _Note:_ SQLite.swift defines `filter` instead of `where` because `where` is [a reserved keyword](https://developer.apple.com/library/ios/documentation/swift/conceptual/Swift_Programming_Language/LexicalStructure.html#//apple_ref/doc/uid/TP40014097-CH30-XID_906).


##### Filter Operators and Functions

SQLite.swift defines a number of operators for building filtering predicates. Operators and functions work together in a type-safe manner, so attempting to equate or compare different types will prevent compilation.


###### Infix Filter Operators

| Swift | Types                            | SQLite    |
| ----- | -------------------------------- | --------- |
| `==`  | `Equatable -> Bool`              | `=`       |
| `!=`  | `Equatable -> Bool`              | `!=`      |
| `>`   | `Comparable -> Bool`             | `>`       |
| `>=`  | `Comparable -> Bool`             | `>=`      |
| `<`   | `Comparable -> Bool`             | `<`       |
| `<=`  | `Comparable -> Bool`             | `<=`      |
| `~=`  | `(Interval, Comparable) -> Bool` | `BETWEEN` |
| `&&`  | `Bool -> Bool`                   | `AND`     |
| `||`  | `Bool -> Bool`                   | `OR`      |


###### Prefix Filter Operators

| Swift | Types              | SQLite |
| ----- | ------------------ | ------ |
| `!`   | `Bool -> Bool`     | `NOT`  |


###### Filtering Functions

| Swift      | Types                   | SQLite  |
| ---------- | ----------------------- | ------- |
| `like`     | `String -> Bool`        | `LIKE`  |
| `glob`     | `String -> Bool`        | `GLOB`  |
| `match`    | `String -> Bool`        | `MATCH` |
| `contains` | `(Array<T>, T) -> Bool` | `IN`    |


<!-- TODO
#### Grouping Results
-->


#### Sorting Rows

We can pre-sort returned rows using the [query’s](#queries) `order` function.

_E.g._, to return users sorted by `email`, then `name`, in ascending order:

``` swift
users.order(email, name)
// SELECT * FROM "users" ORDER BY "email", "name"
```

The `order` function takes a list of [column expressions](#expressions).

`Expression` objects have two computed properties to assist sorting: `asc` and `desc`. These properties append the expression with `ASC` and `DESC` to mark ascending and descending order respectively.

``` swift
users.order(email.desc, name.asc)
// SELECT * FROM "users" ORDER BY "email" DESC, "name" ASC
```


#### Limiting and Paging Results

We can limit and skip returned rows using a [query’s](#queries) `limit` function (and its optional `offset` parameter).

``` swift
users.limit(5)
// SELECT * FROM "users" LIMIT 5

users.limit(5, offset: 5)
// SELECT * FROM "users" LIMIT 5 OFFSET 5
```


#### Aggregation

[`Query`](#queries) structures come with a number of functions that quickly return aggregate values from the table. These mirror the [core aggregate functions](#aggregate-sqlite-functions) and are executed immediately against the query.

``` swift
users.count
// SELECT count(*) FROM "users"
```

Filtered queries will appropriately filter aggregate values.

``` swift
users.filter(name != nil).count
// SELECT count(*) FROM "users" WHERE "name" IS NOT NULL
```

  - `count` as a computed property (see examples above) returns the total number of rows matching the query.

    `count` as a function takes a [column name](#expressions) and returns the total number of rows where that column is not `NULL`.

    ``` swift
    users.count(name) // -> Int
    // SELECT count("name") FROM "users"
    ```

  - `max` takes a comparable column expression and returns the largest value if any exists.

    ``` swift
    users.max(id) // -> Int?
    // SELECT max("id") FROM "users"
    ```

  - `min` takes a comparable column expression and returns the smallest value if any exists.

    ``` swift
    users.min(id) // -> Int?
    // SELECT min("id") FROM "users"
    ```

  - `average` takes a numeric column expression and returns the average row value (as a `Double`) if any exists.

    ``` swift
    users.average(balance) // -> Double?
    // SELECT avg("balance") FROM "users"
    ```

  - `sum` takes a numeric column expression and returns the sum total of all rows if any exist.

    ``` swift
    users.sum(balance) // -> Double?
    // SELECT sum("balance") FROM "users"
    ```

  - `total`, like `sum`, takes a numeric column expression and returns the sum total of all rows, but in this case always returns a `Double`, and returns `0.0` for an empty query.

    ``` swift
    users.total(balance) // -> Double
    // SELECT total("balance") FROM "users"
    ```

> _Note:_ Most of the above aggregate functions (except `max` and `min`) can be called with a `distinct` parameter to aggregate `DISTINCT` values only.
>
> ``` swift
> users.count(distinct: name)
> // SELECT count(DISTINCT "name") FROM "users"
> ```


## Updating Rows

We can update a table’s rows by calling a [query’s](#queries) `update` function with a list of [setters](#setters), typically [typed column expressions](#expressions) and values (which can also be expressions), each joined by the `<-` operator.

When an unscoped query calls `update`, it will update _every_ row in the table.

``` swift
users.update(email <- "<EMAIL>")?
// UPDATE "users" SET "email" = '<EMAIL>'
```

Be sure to scope `UPDATE` statements beforehand using [the `filter` function](#filtering-rows).

``` swift
let alice = users.filter(id == 1)
alice.update(email <- "<EMAIL>")?
// UPDATE "users" SET "email" = '<EMAIL>' WHERE ("id" = 1)
```

Like [`insert`](#inserting-rows) (and [`delete`](#updating-rows)), `update` can return several different types that are useful in different contexts.

  - An `Int?` representing the number of updated rows (or `nil` on failure), for simplicity.

    ``` swift
    if alice.update(email <- "<EMAIL>") > 0 {
       println("updated Alice")
    }
    ```

    We can use the optional nature of the value to disambiguate with a simple `?` or `!`.

    ``` swift
    // ignore failure
    alice.update(email <- "<EMAIL>")?

    // assertion on failure
    alice.update(email <- "<EMAIL>")!
    ```

  - A `Statement`, for [the transaction and savepoint helpers](#transactions-and-savepoints) that take a list of statements.

  - A tuple of the above number of updated rows and statement: `(changes: Int?, Statement)`, for flexibility.


## Deleting Rows

We can delete rows from a table by calling a [query’s](#queries) `delete` function.

When an unscoped query calls `delete`, it will delete _every_ row in the table.

``` swift
users.delete()?
// DELETE FROM "users"
```

Be sure to scope `DELETE` statements beforehand using [the `filter` function](#filtering-rows).

``` swift
let alice = users.filter(id == 1)
alice.delete()?
// DELETE FROM "users" WHERE ("id" = 1)
```

Like [`insert`](#inserting-rows) and [`update`](#updating-rows), `delete` can return several different types that are useful in different contexts.

  - An `Int?` representing the number of deleted rows (or `nil` on failure), for simplicity.

    ``` swift
    if alice.delete() > 0 {
       println("deleted Alice")
    }
    ```

    We can use the optional nature of the value to disambiguate with a simple `?` or `!`.

    ``` swift
    // ignore failure
    alice.delete()?

    // assertion on failure
    alice.delete()!
    ```

  - A `Statement`, for [the transaction and savepoint helpers](#transactions-and-savepoints) that take a list of statements.

  - A tuple of the above number of deleted rows and statement: `(changes: Int?, Statement)`, for flexibility.


## Transactions and Savepoints

Using the `transaction` and `savepoint` functions, we can run a series of statements, commiting the changes to the database if they all succeed. If a single statement fails, we bail out early and roll back.

``` swift
db.transaction(
    users.insert(email <- "<EMAIL>"),
    users.insert(email <- "<EMAIL>", manager_id <- db.lastID)
)
```

> _Note:_ Each statement is captured in an auto-closure and won’t execute till the preceding statement succeeds. This means we can use the `lastID` property on `Database` to reference the previous statement’s insert [`ROWID`][ROWID].


## Altering the Schema

SQLite.swift comes with several functions (in addition to `create(table:)`) for altering a database schema in a type-safe manner.


### Renaming Tables

We can rename a table by calling the `rename(table:to:)` function on a database connection.

``` swift
db.rename(users, to: "users_old")
// ALTER TABLE "users" RENAME TO "users_old"
```


### Adding Columns

We can add columns to a table by calling `alter` function on a database connection. SQLite.swift enforces [the same limited subset](https://www.sqlite.org/lang_altertable.html) of `ALTER TABLE` that SQLite supports.

``` swift
db.alter(table: users, add: suffix)
// ALTER TABLE "users" ADD COLUMN "suffix" TEXT
```


#### Added Column Constraints

The `alter` function shares several of the same [`column` function parameters](#column-constraints) used when [creating tables](#creating-a-table).
 
  - `check` attaches a `CHECK` constraint to a column definition in the form of a boolean expression (`Expression<Bool>`). (See also the `check` function under [Table Constraints](#table-constraints).)

    ``` swift
    let check = contains(["JR", "SR"], suffix)
    db.alter(table: users, add: suffix, check: check)
    // ALTER TABLE "users"
    // ADD COLUMN "suffix" TEXT CHECK ("suffix" IN ('JR', 'SR'))
    ```
 
  - `defaultValue` adds a `DEFAULT` clause to a column definition and _only_ accepts a value matching the column’s type. This value is used if none is explicitly provided during [an `INSERT`](#inserting-rows).

    ``` swift
    db.alter(table: users, add: suffix, defaultValue: "SR")
    // ALTER TABLE "users" ADD COLUMN "suffix" TEXT DEFAULT 'SR'
    ```
 
    > _Note:_ Unlike the [`CREATE TABLE` constraint](#table-constraints), default values may not be expression structures (including `CURRENT_TIME`, `CURRENT_DATE`, or `CURRENT_TIMESTAMP`).

<!-- FIXME
  - `collate` adds a `COLLATE` clause to `Expression<String>` (and `Expression<String?>`) column definitions with [a collating sequence](https://www.sqlite.org/datatype3.html#collation) defined in the `Collation` enumeration.

    ``` swift
    t.column(email, collate: .NoCase)
    // email TEXT NOT NULL COLLATE NOCASE
    ```
-->

  - `references` adds a `REFERENCES` clause to `Int` (and `Int?`) column definitions and accepts a table or namespaced column expression. (See the `foreignKey` function under [Table Constraints](#table-constraints) for non-integer foreign key support.)

    ``` swift
    db.alter(table: posts, add: user_id, references: users[id])
    // ALTER TABLE "posts" ADD COLUMN "user_id" INTEGER REFERENCES "users"("id")

    db.alter(table: posts, add: user_id, references: users)
    // ALTER TABLE "posts" ADD COLUMN "user_id" INTEGER REFERENCES "users"
    // -- assumes "users" has a PRIMARY KEY
    ```


### Indexes 


#### Creating Indexes

We can run [`CREATE INDEX` statements](https://www.sqlite.org/lang_createindex.html) by calling the `create(index:)` function on a database connection.

``` swift
db.create(index: users, on: email)
// CREATE INDEX "index_users_on_email" ON "users" ("email")
```

The index name is generated automatically based on the table and column names.

The `create(index:)` function has a couple default parameters we can override.

  - `unique` adds a `UNIQUE` constraint to the index. Default: `false`.
<commit_msg>Fix whitespace issues in documentation<commit_after># SQLite.swift Documentation

  - [Installation](#installation)
  - [Getting Started](#getting-started)
    - [Connecting to a Database](#connecting-to-a-database)
      - [Read-Write Databases](#read-write-databases)
      - [Read-Only Databases](#read-only-databases)
      - [In-Memory Databases](#in-memory-databases)
    - [A Note on Thread-Safety](#a-note-on-thread-safety)
  - [Building Type-Safe SQL](#building-type-safe-sql)
    - [Expressions](#expressions)
      - [Compound Expressions](#compound-expressions)
    - [Queries](#queries)
  - [Creating a Table](#creating-a-table)
    - [Create Table Options](#create-table-options)
    - [Column Constraints](#column-constraints)
    - [Table Constraints](#table-constraints)
  - [Inserting Rows](#inserting-rows)
    - [Setters](#setters)
  - [Selecting Rows](#selecting-rows)
    - [Iterating and Accessing Values](#iterating-and-accessing-values)
    - [Plucking Rows](#plucking-rows)
    - [Building Complex Queries](#building-complex-queries)
      - [Selecting Columns](#selecting-columns)
      - [Joining Other Tables](#joining-other-tables)
        - [Column Namespacing](#column-namespacing)
        - [Table Aliasing](#table-aliasing)
      - [Filtering Rows](#filtering-rows)
        - [Filter Operators and Functions](#filter-operators-and-functions)
      - [Sorting Rows](#sorting-rows)
      - [Limiting and Paging Results](#limiting-and-paging-results)
      - [Aggregation](#aggregation)
  - [Updating Rows](#updating-rows)
  - [Deleting Rows](#deleting-rows)
  - [Transactions and Savepoints](#transactions-and-savepoints)
  - [Altering the Schema](#altering-the-schema)
    - [Renaming Tables](#renaming-tables)
    - [Adding Columns](#adding-columns)
      - [Added Column Constraints](#added-column-constraints)
    - [Indexes](#indexes)
      - [Creating Indexes](#creating-indexes)
      - [Dropping Indexes](#dropping-indexes)
    - [Dropping Tables](#dropping-tables)
    - [Migrations and Schema Versioning](#migrations-and-schema-versioning)
  - [Custom Types](#custom-types)
    - [Date-Time Values](#date-time-values)
    - [Binary Data](#binary-data)
    - [Custom Type Caveats](#custom-type-caveats)
  - [Other Operators](#other-operators)
  - [Core SQLite Functions](#core-sqlite-functions)
  - [Aggregate SQLite Functions](#aggregate-sqlite-functions)
  - [Executing Arbitrary SQL](#executing-arbitrary-sql)
  - [Logging](#logging)


[↩]: #sqliteswift-documentation


## Installation

> _Note:_ SQLite.swift requires Swift 1.1 (and [Xcode 6.1](https://developer.apple.com/xcode/downloads/)) or greater.

To install SQLite.swift as an Xcode sub-project:

 1. Drag the **SQLite.xcodeproj** file into your own project. ([Submodule](http://git-scm.com/book/en/Git-Tools-Submodules), clone, or [download](https://github.com/stephencelis/SQLite.swift/archive/master.zip) the project first.)

    ![Installation](Resources/installation@2x.png)

 2. In your target’s **Build Phases**, add **SQLite iOS** (or **SQLite Mac**) to the **Target Dependencies** build phase.

 3. Add the appropriate **SQLite.framework** product to the **Link Binary With Libraries** build phase.

 4. Add the same **SQLite.framework** to a **Copy Files** build phase with a **Frameworks** destination. (Add a new build phase if need be.)

You should now be able to `import SQLite` from any of your target’s source files and begin using SQLite.swift.


## Getting Started

To use SQLite.swift classes or structures in your target’s source file, first import the `SQLite` module.

``` swift
import SQLite
```


### Connecting to a Database

Database connections are established using the `Database` class. A database is initialized with a path. SQLite will attempt to create the database file if it does not already exist.

``` swift
let db = Database("path/to/db.sqlite3")
```


#### Read-Write Databases

On iOS, you can create a writable database in your app’s **Documents** directory.

``` swift
let path = NSSearchPathForDirectoriesInDomains(
    .DocumentDirectory, .UserDomainMask, true
).first as String

let db = Database("\(path)/db.sqlite3")
```

On OS X, you can use your app’s **Application Support** directory:

``` swift
var path = NSSearchPathForDirectoriesInDomains(
    .ApplicationSupportDirectory, .UserDomainMask, true
).first as String + NSBundle.mainBundle().bundleIdentifier!

// create parent directory iff it doesn't exist
NSFileManager.defaultManager().createDirectoryAtPath(
    path, withIntermediateDirectories: true, attributes: nil, error: nil
)

let db = Database("\(path)/db.sqlite3")
```


#### Read-Only Databases

If you bundle a database with your app, you can establish a _read-only_ connection to it.

``` swift
let path = NSBundle.mainBundle().pathForResource("db", ofType: "sqlite3")!

let db = Database(path, readonly: true)
```

> _Note_: Signed applications cannot modify their bundle resources. If you bundle a database file with your app for the purpose of bootstrapping, copy it to a writable location _before_ establishing a connection (see [Read-Write Databases](#read-write-databases), above, for typical, writable locations).


#### In-Memory Databases

If you omit the path, SQLite.swift will provision an [in-memory database](https://www.sqlite.org/inmemorydb.html).

``` swift
let db = Database() // equivalent to `Database(":memory:")`
```

To create a temporary, disk-backed database, pass an empty file name.

``` swift
let db = Database("")
```

In-memory databases are automatically deleted when the database connection is closed.


### A Note on Thread-Safety

> _Note:_ Every database comes equipped with its own serial queue for statement execution and can be safely accessed across threads. Threads that open transactions and savepoints, however, do not block other threads from executing statements within the transaction.


## Building Type-Safe SQL

SQLite.swift comes with a typed expression layer that directly maps [Swift types](https://developer.apple.com/library/prerelease/ios/documentation/General/Reference/SwiftStandardLibraryReference/) to their [SQLite counterparts](https://www.sqlite.org/datatype3.html).

| Swift Type      | SQLite Type |
| --------------- | ----------- |
| `Int`           | `INTEGER`   |
| `Double`        | `REAL`      |
| `String`        | `TEXT`      |
| `Bool`          | `BOOLEAN`   |
| `nil`           | `NULL`      |
| `SQLite.Blob`*  | `BLOB`      |

> *SQLite.swift defines its own `Blob` structure, which safely wraps the underlying bytes.
>
> See [Custom Types](#custom-types) for more information about extending other classes and structures to work with SQLite.swift.

These expressions (in the form of the structure, [`Expression`](#expressions)) build on one another and, with a query ([`Query`](#queries)), can create and execute SQL statements.


### Expressions

Expressions are generic structures associated with a type ([built-in](#building-type-safe-sql) or [custom](#custom-types)), raw SQL, and (optionally) values to bind to that SQL. Typically, you will only explicitly create expressions to describe your columns, and typically only once per column.

``` swift
let id = Expression<Int>("id")
let email = Expression<String>("email")
let balance = Expression<Double>("balance")
let verified = Expression<Bool>("verified")
```

Use optional generics for expressions that can evaluate to `NULL`.

``` swift
let name = Expression<String?>("name")
```

> _Note:_ The default `Expression` initializer is for [quoted identifiers](https://www.sqlite.org/lang_keywords.html) (_i.e._, column names). To build a literal SQL expression, use `init(literal:)`.


### Compound Expressions

Expressions can be combined with other expressions and types using [filters](#filter-operators-and-functions), and [other operators](#other-operators) and [functions](#core-sqlite-functions). These building blocks can create complex SQLite statements.


### Queries

Queries are structures that reference a database and table name, and can be used to build a variety of statements using expressions. We can create a `Query` by subscripting a database connection with a table name.

``` swift
let users = db["users"]
```

Assuming [the table exists](#creating-a-table), we can immediately [insert](#inserting-rows), [select](#selecting-rows), [update](#updating-rows), and [delete](#deleting-rows) rows.


## Creating a Table

We can run [`CREATE TABLE` statements](https://www.sqlite.org/lang_createtable.html) by calling the `create(table:)` function on a database connection. The following is a basic example of SQLite.swift code (using the [expressions](#expressions) and [query](#queries) above) and the corresponding SQL it generates.

``` swift
db.create(table: users) { t in     // CREATE TABLE "users" (
    t.column(id, primaryKey: true) //     "id" INTEGER PRIMARY KEY NOT NULL,
    t.column(email, unique: true)  //     "email" TEXT UNIQUE NOT NULL,
    t.column(name)                 //     "name" TEXT
}                                  // )
```

> _Note:_ `Expression<T>` structures (in this case, the `id` and `email` columns), generate `NOT NULL` constraints automatically, while `Expression<T?>` structures (`name`) do not.


### Create Table Options

The `create(table:)` function has several default parameters we can override.

  - `temporary` adds a `TEMPORARY` clause to the `CREATE TABLE` statement (to create a temporary table that will automatically drop when the database connection closes). Default: `false`.

    ``` swift
    db.create(table: users, temporary: true) { t in /* ... */ }
    // CREATE TEMPORARY TABLE "users" -- ...
    ```

  - `ifNotExists` adds an `IF NOT EXISTS` clause to the `CREATE TABLE` statement (which will bail out gracefully if the table already exists). Default: `false`.

    ``` swift
    db.create(table: users, ifNotExists: true) { t in /* ... */ }
    // CREATE TABLE "users" IF NOT EXISTS -- ...
    ```

### Column Constraints

The `column` function is used for a single column definition. It takes an [expression](#expressions) describing the column name and type, and accepts several parameters that map to various column constraints and clauses.

  - `primaryKey` adds an `INTEGER PRIMARY KEY` constraint to a single column. (See the `primaryKey` function under [Table Constraints](#table-constraints) for non-integer primary keys).

    ``` swift
    t.column(id, primaryKey: true)
    // "id" INTEGER PRIMARY KEY NOT NULL
    ```

    > _Note:_ The `primaryKey` parameter cannot be used alongside `defaultValue` or `references`. If you need to create a column that has a default value and is also a primary and/or foreign key, use the `primaryKey` and `foreignKey` functions mentioned under [Table Constraints](#table-constraints).
    >
    > Primary keys cannot be optional (`Expression<Int?>`).

  - `unique` adds a `UNIQUE` constraint to the column. (See the `unique` function under [Table Constraints](#table-constraints) for uniqueness over multiple columns).

    ``` swift
    t.column(email, unique: true)
    // "email" TEXT UNIQUE NOT NULL
    ```

  - `check` attaches a `CHECK` constraint to a column definition in the form of a boolean expression (`Expression<Bool>`). Boolean expressions can be easily built using [filter operators and functions](#filter-operators-and-functions). (See also the `check` function under [Table Constraints](#table-constraints).)

    ``` swift
    t.column(email, check: like("%@%", email))
    // "email" TEXT NOT NULL CHECK ("email" LIKE '%@%')
    ```

  - `defaultValue` adds a `DEFAULT` clause to a column definition and _only_ accepts a value (or expression) matching the column’s type. This value is used if none is explicitly provided during [an `INSERT`](#inserting-rows).

    ``` swift
    t.column(name, defaultValue: "Anonymous")
    // "name" TEXT DEFAULT 'Anonymous'
    ```

    > _Note:_ The `defaultValue` parameter cannot be used alongside `primaryKey` and `references`. If you need to create a column that has a default value and is also a primary and/or foreign key, use the `primaryKey` and `foreignKey` functions mentioned under [Table Constraints](#table-constraints).

  - `collate` adds a `COLLATE` clause to `Expression<String>` (and `Expression<String?>`) column definitions with [a collating sequence](https://www.sqlite.org/datatype3.html#collation) defined in the `Collation` enumeration.

    ``` swift
    t.column(email, collate: .NoCase)
    // "email" TEXT NOT NULL COLLATE NOCASE
    ```

  - `references` adds a `REFERENCES` clause to `Expression<Int>` (and `Expression<Int?>`) column definitions and accepts a table (`Query`) or namespaced column expression. (See the `foreignKey` function under [Table Constraints](#table-constraints) for non-integer foreign key support.)

    ``` swift
    t.column(user_id, references: users[id])
    // "user_id" INTEGER REFERENCES "users"("id")

    t.column(user_id, references: users)
    // "user_id" INTEGER REFERENCES "users"
    // -- assumes "users" has a PRIMARY KEY
    ```

    > _Note:_ The `references` parameter cannot be used alongside `primaryKey` and `defaultValue`. If you need to create a column that has a default value and is also a primary and/or foreign key, use the `primaryKey` and `foreignKey` functions mentioned under [Table Constraints](#table-constraints).


### Table Constraints

Additional constraints may be provided outside the scope of a single column using the following functions.

  - `primaryKey` adds a `PRIMARY KEY` constraint to the table. Unlike [the column constraint, above](#column-constraints), it supports all SQLite types, [ascending and descending orders](#sorting-rows), and composite (multiple column) keys.

    ``` swift
    t.primaryKey(email.asc, name)
    // PRIMARY KEY("email" ASC, "name")
    ```

  - `unique` adds a `UNIQUE` constraint to the table. Unlike [the column constraint, above](#column-constraints), it supports composite (multiple column) constraints.

    ``` swift
    t.unique(local, domain)
    // UNIQUE("local", "domain")
    ```

  - `check` adds a `CHECK` constraint to the table in the form of a boolean expression (`Expression<Bool>`). Boolean expressions can be easily built using [filter operators and functions](#filter-operators-and-functions). (See also the `check` parameter under [Column Constraints](#column-constraints).)

    ``` swift
    t.check(balance >= 0)
    // CHECK ("balance" >= 0.0)
    ```

  - `foreignKey` adds a `FOREIGN KEY` constraint to the table. Unlike [the `references` constraint, above](#column-constraints), it supports all SQLite types, and both [`ON UPDATE` and `ON DELETE` actions](https://www.sqlite.org/foreignkeys.html#fk_actions).

    ``` swift
    t.foreignKey(user_id, on: users[id], delete: .SetNull)
    // FOREIGN KEY("user_id") REFERENCES "users"("id") ON DELETE SET NULL
    ```

    > _Note:_ Composite foreign keys are not supported at this time. If you add support, please [submit a pull request](https://github.com/stephencelis/SQLite.swift/fork).

<!-- TODO
### Creating a Table from a Select Statement
-->


## Inserting Rows

We can insert rows into a table by calling a [query’s](#queries) `insert` function with a list of [setters](#setters), typically [typed column expressions](#expressions) and values (which can also be expressions), each joined by the `<-` operator.

``` swift
users.insert(email <- "<EMAIL>", name <- "Alice")?
// INSERT INTO "users" ("email", "name") VALUES ('<EMAIL>', 'Alice')
```

The `insert` function can return several different types that are useful in different contexts.

  - An `Int?` representing the inserted row’s [`ROWID`][ROWID] (or `nil` on failure), for simplicity.

    ``` swift
    if let insertID = users.insert(email <- "<EMAIL>") {
        println("inserted id: \(insertID)")
    }
    ```

    We can use the optional nature of the value to disambiguate with a simple `?` or `!`.

    ``` swift
    // ignore failure
    users.insert(email <- "<EMAIL>")?

    // assertion on failure
    users.insert(email <- "<EMAIL>")!
    ```

  - A `Statement`, for [the transaction and savepoint helpers](#transactions-and-savepoints) that take a list of statements.

    ``` swift
    db.transaction(
        users.insert(email <- "<EMAIL>"),
        users.insert(email <- "<EMAIL>")
    )
    // BEGIN DEFERRED TRANSACTION;
    // INSERT INTO "users" ("email") VALUES ('<EMAIL>');
    // INSERT INTO "users" ("email") VALUES ('<EMAIL>');
    // COMMIT TRANSACTION;
    ```

  - A tuple of the above [`ROWID`][ROWID] and statement: `(ID: Int?, Statement)`, for flexibility.

    ``` swift
    let (ID, statement) = users.insert(email <- "<EMAIL>")
    if let ID = ID {
        println("inserted id: \(ID)")
    } else if statement.failed {
        println("insertion failed: \(statement.reason)")
    }
    ```

The [`update`](#updating-rows) and [`delete`](#deleting-rows) functions follow similar patterns.

> _Note:_ If `insert` is called without any arguments, the statement will run with a `DEFAULT VALUES` clause. The table must not have any constraints that aren’t fulfilled by default values.
>
> ``` swift
> timestamps.insert()!
> // INSERT INTO "timestamps" DEFAULT VALUES
> ```


### Setters

SQLite.swift typically uses the `<-` operator to set values during [inserts](#inserting-rows) and [updates](#updating-rows).

``` swift
views.update(count <- 0)
// UPDATE "views" SET "count" = 0 WHERE ("id" = 1)
```

There are also a number of convenience setters that take the existing value into account using native Swift operators.

For example, to atomically increment a column, we can use `++`:

``` swift
views.update(count++) // equivalent to `views.update(count -> count + 1)`
// UPDATE "views" SET "count" = "count" + 1 WHERE ("id" = 1)
```

To take an amount and “move” it via transaction, we can use `-=` and `+=`:

``` swift
let amount = 100.0
db.transaction(
    alice.update(balance -= amount),
    betty.update(balance += amount)
)
// BEGIN DEFERRED TRANSACTION;
// UPDATE "users" SET "balance" = "balance" - 100.0 WHERE ("id" = 1);
// UPDATE "users" SET "balance" = "balance" + 100.0 WHERE ("id" = 2);
// COMMIT TRANSACTION;
```


###### Infix Setters

| Operator | Types              |
| -------- | ------------------ |
| `<-`     | `Value -> Value`   |
| `+=`     | `Number -> Number` |
| `-=`     | `Number -> Number` |
| `*=`     | `Number -> Number` |
| `/=`     | `Number -> Number` |
| `%=`     | `Int -> Int`       |
| `<<=`    | `Int -> Int`       |
| `>>=`    | `Int -> Int`       |
| `&=`     | `Int -> Int`       |
| `||=`    | `Int -> Int`       |
| `^=`     | `Int -> Int`       |
| `+=`     | `String -> String` |


###### Postfix Setters

| Operator | Types        |
| -------- | ------------ |
| `++`     | `Int -> Int` |
| `--`     | `Int -> Int` |


## Selecting Rows

[`Query` structures](#queries) are `SELECT` statements waiting to happen. They execute via [iteration](#iterating-and-accessing-values) and [other means](#plucking-values) of sequence access.


### Iterating and Accessing Values

[Queries](#queries) execute lazily upon iteration. Each row is returned as a `Row` object, which can be subscripted with a [column expression](#expressions) matching one of the columns returned.

``` swift
for user in users {
    println("id: \(user[id]), email: \(user[email]), name: \(user[name])")
    // id: 1, email: <EMAIL>, name: Optional("Alice")
}
// SELECT * FROM "users"
```

`Expression<T>` column values are _automatically unwrapped_ (we’ve made a promise to the compiler that they’ll never be `NULL`), while `Expression<T?>` values remain wrapped.


### Plucking Rows

We can pluck the first row by calling the `first` computed property on [`Query`](#queries).

``` swift
if let user = users.first { /* ... */ } // Row
// SELECT * FROM "users" LIMIT 1
```

To collect all rows into an array, we can simply wrap the sequence (though this is not always the most memory-efficient idea).

``` swift
let all = Array(users)
// SELECT * FROM "users"
```


### Building Complex Queries

[`Query`](#queries) structures have a number of chainable functions that can be used (with [expressions](#expressions)) to add and modify [a number of clauses](https://www.sqlite.org/lang_select.html) to the underlying statement.

``` swift
let query = users.select(email)           // SELECT "email" FROM "users"
                 .filter(name != nil)     // WHERE "name" IS NOT NULL
                 .order(email.desc, name) // ORDER BY "email" DESC, "name"
                 .limit(5, offset: 1)     // LIMIT 5 OFFSET 1
```


#### Selecting Columns

By default, [`Query`](#queries) objects select every column of the result set (using `SELECT *`). We can use the `select` function with a list of [expressions](#expressions) to return specific columns instead.

``` swift
let query = users.select(id, email)
// SELECT "id", "email" FROM "users"
```

<!-- TODO
We can select aggregate values using [aggregate functions](#aggregate-sqlite-functions).

``` swift
let query = users.select(count(*))
// SELECT count(*) FROM "users"
```
-->


#### Joining Other Tables

We can join tables using a [query’s](#queries) `join` function.

``` swift
users.join(posts, on: user_id == users[id])
// SELECT * FROM "users" INNER JOIN "posts" ON ("user_id" = "users"."id")
```

The `join` function takes a [query](#queries) object (for the table being joined on), a join condition (`on`), and is prefixed with an optional join type (default: `.Inner`). Join conditions can be built using [filter operators and functions](#filter-operators-and-functions), generally require [namespacing](#column-namespacing), and sometimes require [aliasing](#table-aliasing).


##### Column Namespacing

When joining tables, column names can become ambiguous. _E.g._, both tables may have an `id` column.

``` swift
let query = users.join(posts, on: user_id == id)
// assertion failure: ambiguous column 'id'
```

We can disambiguate by namespacing `id`.

``` swift
let query = users.join(posts, on: user_id == users[id])
// SELECT * FROM "users" INNER JOIN "posts" ON ("user_id" = "users"."id")
```

Namespacing is achieved by subscripting a [query](#queries) with a [column expression](#expressions) (_e.g._, `users[id]` above becomes `users.id`).

> _Note:_ We can namespace all of a table’s columns using `*`.
>
> ``` swift
> let query = users.select(users[*])
> // SELECT "users".* FROM "users"
> ```


##### Table Aliasing

Occasionally, we need to join a table to itself, in which case we must alias the table with another name. We can achieve this using the [query’s](#queries) `alias` function.

``` swift
let managers = users.alias("managers")

let query = users.join(managers, on: managers[id] == users[manager_id])
// SELECT * FROM "users"
// INNER JOIN "users" AS "managers" ON ("managers"."id" = "users"."manager_id")
```

If query results can have ambiguous column names, row values should be accessed with namespaced [column expressions](#expressions). In the above case, `SELECT *` immediately namespaces all columns of the result set.

``` swift
let user = query.first!
user[id]           // fatal error: ambiguous column 'id'
                   // (please disambiguate: ["users"."id", "managers"."id"])

user[users[id]]    // returns "users"."id"
user[managers[id]] // returns "managers"."id"
```


#### Filtering Rows

SQLite.swift filters rows using a [query’s](#queries) `filter` function with a boolean [expression](#expressions) (`Expression<Bool>`).

``` swift
users.filter(id == 1)
// SELECT * FROM "users" WHERE ("id" = 1)

users.filter(contains([1, 2, 3, 4, 5], id))
// SELECT * FROM "users" WHERE ("id" IN (1, 2, 3, 4, 5))

users.filter(like("<EMAIL>", email))
// SELECT * FROM "users" WHERE ("email" LIKE <EMAIL>')

users.filter(verified && lower(name) == "alice")
// SELECT * FROM "users" WHERE ("verified" AND (lower("name") == 'alice'))

users.filter(verified || balance >= 10_000)
// SELECT * FROM "users" WHERE ("verified" OR ("balance" >= 10000.0))
```

You can build your own boolean expressions by using one of the many [filter operators and functions](#filter-operators-and-functions).

> _Note:_ SQLite.swift defines `filter` instead of `where` because `where` is [a reserved keyword](https://developer.apple.com/library/ios/documentation/swift/conceptual/Swift_Programming_Language/LexicalStructure.html#//apple_ref/doc/uid/TP40014097-CH30-XID_906).


##### Filter Operators and Functions

SQLite.swift defines a number of operators for building filtering predicates. Operators and functions work together in a type-safe manner, so attempting to equate or compare different types will prevent compilation.


###### Infix Filter Operators

| Swift | Types                            | SQLite    |
| ----- | -------------------------------- | --------- |
| `==`  | `Equatable -> Bool`              | `=`       |
| `!=`  | `Equatable -> Bool`              | `!=`      |
| `>`   | `Comparable -> Bool`             | `>`       |
| `>=`  | `Comparable -> Bool`             | `>=`      |
| `<`   | `Comparable -> Bool`             | `<`       |
| `<=`  | `Comparable -> Bool`             | `<=`      |
| `~=`  | `(Interval, Comparable) -> Bool` | `BETWEEN` |
| `&&`  | `Bool -> Bool`                   | `AND`     |
| `||`  | `Bool -> Bool`                   | `OR`      |


###### Prefix Filter Operators

| Swift | Types              | SQLite |
| ----- | ------------------ | ------ |
| `!`   | `Bool -> Bool`     | `NOT`  |


###### Filtering Functions

| Swift      | Types                   | SQLite  |
| ---------- | ----------------------- | ------- |
| `like`     | `String -> Bool`        | `LIKE`  |
| `glob`     | `String -> Bool`        | `GLOB`  |
| `match`    | `String -> Bool`        | `MATCH` |
| `contains` | `(Array<T>, T) -> Bool` | `IN`    |


<!-- TODO
#### Grouping Results
-->


#### Sorting Rows

We can pre-sort returned rows using the [query’s](#queries) `order` function.

_E.g._, to return users sorted by `email`, then `name`, in ascending order:

``` swift
users.order(email, name)
// SELECT * FROM "users" ORDER BY "email", "name"
```

The `order` function takes a list of [column expressions](#expressions).

`Expression` objects have two computed properties to assist sorting: `asc` and `desc`. These properties append the expression with `ASC` and `DESC` to mark ascending and descending order respectively.

``` swift
users.order(email.desc, name.asc)
// SELECT * FROM "users" ORDER BY "email" DESC, "name" ASC
```


#### Limiting and Paging Results

We can limit and skip returned rows using a [query’s](#queries) `limit` function (and its optional `offset` parameter).

``` swift
users.limit(5)
// SELECT * FROM "users" LIMIT 5

users.limit(5, offset: 5)
// SELECT * FROM "users" LIMIT 5 OFFSET 5
```


#### Aggregation

[`Query`](#queries) structures come with a number of functions that quickly return aggregate values from the table. These mirror the [core aggregate functions](#aggregate-sqlite-functions) and are executed immediately against the query.

``` swift
users.count
// SELECT count(*) FROM "users"
```

Filtered queries will appropriately filter aggregate values.

``` swift
users.filter(name != nil).count
// SELECT count(*) FROM "users" WHERE "name" IS NOT NULL
```

  - `count` as a computed property (see examples above) returns the total number of rows matching the query.

    `count` as a function takes a [column name](#expressions) and returns the total number of rows where that column is not `NULL`.

    ``` swift
    users.count(name) // -> Int
    // SELECT count("name") FROM "users"
    ```

  - `max` takes a comparable column expression and returns the largest value if any exists.

    ``` swift
    users.max(id) // -> Int?
    // SELECT max("id") FROM "users"
    ```

  - `min` takes a comparable column expression and returns the smallest value if any exists.

    ``` swift
    users.min(id) // -> Int?
    // SELECT min("id") FROM "users"
    ```

  - `average` takes a numeric column expression and returns the average row value (as a `Double`) if any exists.

    ``` swift
    users.average(balance) // -> Double?
    // SELECT avg("balance") FROM "users"
    ```

  - `sum` takes a numeric column expression and returns the sum total of all rows if any exist.

    ``` swift
    users.sum(balance) // -> Double?
    // SELECT sum("balance") FROM "users"
    ```

  - `total`, like `sum`, takes a numeric column expression and returns the sum total of all rows, but in this case always returns a `Double`, and returns `0.0` for an empty query.

    ``` swift
    users.total(balance) // -> Double
    // SELECT total("balance") FROM "users"
    ```

> _Note:_ Most of the above aggregate functions (except `max` and `min`) can be called with a `distinct` parameter to aggregate `DISTINCT` values only.
>
> ``` swift
> users.count(distinct: name)
> // SELECT count(DISTINCT "name") FROM "users"
> ```


## Updating Rows

We can update a table’s rows by calling a [query’s](#queries) `update` function with a list of [setters](#setters), typically [typed column expressions](#expressions) and values (which can also be expressions), each joined by the `<-` operator.

When an unscoped query calls `update`, it will update _every_ row in the table.

``` swift
users.update(email <- "<EMAIL>")?
// UPDATE "users" SET "email" = '<EMAIL>'
```

Be sure to scope `UPDATE` statements beforehand using [the `filter` function](#filtering-rows).

``` swift
let alice = users.filter(id == 1)
alice.update(email <- "<EMAIL>")?
// UPDATE "users" SET "email" = '<EMAIL>' WHERE ("id" = 1)
```

Like [`insert`](#inserting-rows) (and [`delete`](#updating-rows)), `update` can return several different types that are useful in different contexts.

  - An `Int?` representing the number of updated rows (or `nil` on failure), for simplicity.

    ``` swift
    if alice.update(email <- "<EMAIL>") > 0 {
        println("updated Alice")
    }
    ```

    We can use the optional nature of the value to disambiguate with a simple `?` or `!`.

    ``` swift
    // ignore failure
    alice.update(email <- "<EMAIL>")?

    // assertion on failure
    alice.update(email <- "<EMAIL>")!
    ```

  - A `Statement`, for [the transaction and savepoint helpers](#transactions-and-savepoints) that take a list of statements.

  - A tuple of the above number of updated rows and statement: `(changes: Int?, Statement)`, for flexibility.


## Deleting Rows

We can delete rows from a table by calling a [query’s](#queries) `delete` function.

When an unscoped query calls `delete`, it will delete _every_ row in the table.

``` swift
users.delete()?
// DELETE FROM "users"
```

Be sure to scope `DELETE` statements beforehand using [the `filter` function](#filtering-rows).

``` swift
let alice = users.filter(id == 1)
alice.delete()?
// DELETE FROM "users" WHERE ("id" = 1)
```

Like [`insert`](#inserting-rows) and [`update`](#updating-rows), `delete` can return several different types that are useful in different contexts.

  - An `Int?` representing the number of deleted rows (or `nil` on failure), for simplicity.

    ``` swift
    if alice.delete() > 0 {
        println("deleted Alice")
    }
    ```

    We can use the optional nature of the value to disambiguate with a simple `?` or `!`.

    ``` swift
    // ignore failure
    alice.delete()?

    // assertion on failure
    alice.delete()!
    ```

  - A `Statement`, for [the transaction and savepoint helpers](#transactions-and-savepoints) that take a list of statements.

  - A tuple of the above number of deleted rows and statement: `(changes: Int?, Statement)`, for flexibility.


## Transactions and Savepoints

Using the `transaction` and `savepoint` functions, we can run a series of statements, commiting the changes to the database if they all succeed. If a single statement fails, we bail out early and roll back.

``` swift
db.transaction(
    users.insert(email <- "<EMAIL>"),
    users.insert(email <- "<EMAIL>", manager_id <- db.lastID)
)
```

> _Note:_ Each statement is captured in an auto-closure and won’t execute till the preceding statement succeeds. This means we can use the `lastID` property on `Database` to reference the previous statement’s insert [`ROWID`][ROWID].


## Altering the Schema

SQLite.swift comes with several functions (in addition to `create(table:)`) for altering a database schema in a type-safe manner.


### Renaming Tables

We can rename a table by calling the `rename(table:to:)` function on a database connection.

``` swift
db.rename(users, to: "users_old")
// ALTER TABLE "users" RENAME TO "users_old"
```


### Adding Columns

We can add columns to a table by calling `alter` function on a database connection. SQLite.swift enforces [the same limited subset](https://www.sqlite.org/lang_altertable.html) of `ALTER TABLE` that SQLite supports.

``` swift
db.alter(table: users, add: suffix)
// ALTER TABLE "users" ADD COLUMN "suffix" TEXT
```


#### Added Column Constraints

The `alter` function shares several of the same [`column` function parameters](#column-constraints) used when [creating tables](#creating-a-table).

  - `check` attaches a `CHECK` constraint to a column definition in the form of a boolean expression (`Expression<Bool>`). (See also the `check` function under [Table Constraints](#table-constraints).)

    ``` swift
    let check = contains(["JR", "SR"], suffix)
    db.alter(table: users, add: suffix, check: check)
    // ALTER TABLE "users"
    // ADD COLUMN "suffix" TEXT CHECK ("suffix" IN ('JR', 'SR'))
    ```

  - `defaultValue` adds a `DEFAULT` clause to a column definition and _only_ accepts a value matching the column’s type. This value is used if none is explicitly provided during [an `INSERT`](#inserting-rows).

    ``` swift
    db.alter(table: users, add: suffix, defaultValue: "SR")
    // ALTER TABLE "users" ADD COLUMN "suffix" TEXT DEFAULT 'SR'
    ```

    > _Note:_ Unlike the [`CREATE TABLE` constraint](#table-constraints), default values may not be expression structures (including `CURRENT_TIME`, `CURRENT_DATE`, or `CURRENT_TIMESTAMP`).

<!-- FIXME
  - `collate` adds a `COLLATE` clause to `Expression<String>` (and `Expression<String?>`) column definitions with [a collating sequence](https://www.sqlite.org/datatype3.html#collation) defined in the `Collation` enumeration.

    ``` swift
    t.column(email, collate: .NoCase)
    // email TEXT NOT NULL COLLATE NOCASE
    ```
-->

  - `references` adds a `REFERENCES` clause to `Int` (and `Int?`) column definitions and accepts a table or namespaced column expression. (See the `foreignKey` function under [Table Constraints](#table-constraints) for non-integer foreign key support.)

    ``` swift
    db.alter(table: posts, add: user_id, references: users[id])
    // ALTER TABLE "posts" ADD COLUMN "user_id" INTEGER REFERENCES "users"("id")

    db.alter(table: posts, add: user_id, references: users)
    // ALTER TABLE "posts" ADD COLUMN "user_id" INTEGER REFERENCES "users"
    // -- assumes "users" has a PRIMARY KEY
    ```


### Indexes


#### Creating Indexes

We can run [`CREATE INDEX` statements](https://www.sqlite.org/lang_createindex.html) by calling the `create(index:)` function on a database connection.

``` swift
db.create(index: users, on: email)
// CREATE INDEX "index_users_on_email" ON "users" ("email")
```

The index name is generated automatically based on the table and column names.

The `create(index:)` function has a couple default parameters we can override.

  - `unique` adds a `UNIQUE` constraint to the index. Default: `false`.

{'fraction_non_alphanumeric': 0.1442032643446071, 'fraction_numerical': 0.002664198777272982, 'mean_word_length': 3.5933917300012883, 'pattern_counts': {'":': 2, '<': 151, '<?xml version=': 0, '>': 261, 'https://': 30, 'lorem ipsum': 0, 'www.': 20, 'xml': 0}, 'pii_count': 2, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1421025', 'n_tokens_mistral': 21866, 'n_tokens_neox': 20342, 'n_words': 7582}