Import std.db.database

Database

std.db.database

class

Public Class Database

A connection to an SQLite database, which closes when Close is called, or when its last reference goes, and those of its Statements and Transactions. One thread uses it at a time.

Examples

Local db = Try Database.Open(":memory:")
Try db.Execute("CREATE TABLE people (name TEXT, age INTEGER)")
Try db.Execute("INSERT INTO people VALUES (?, ?)", "Ada", 36)
Try db.Execute("INSERT INTO people VALUES (?, ?)", "Alan", 41)
Local rows = Try db.Rows("SELECT name FROM people WHERE age > ?", 40)
Expect(rows.Count == 1 && rows[0].Text("name") == "Alan")
db.Close()

std/db/database.fb, line 390

Public Static Method Open(path: String): Database Throws DatabaseError static method #

The database at path, made when there's none: a file, ":memory:" for a new one in memory that only this Database reaches, or a URI file name, like "file:data.db?mode=ro" to only read one, or "file:name?mode=memory&cache=shared" for one in memory that each Database of that name shares while one is open. Its foreign keys are checked, and it waits up to 5 seconds for another connection that holds the database before it throws a DatabaseError of BUSY.
Public Method Close() method #

Closes it now, and rolls back what an open Transaction did, as its last reference going would, and finalizes its Statements, so it lets go of its file and locks then. Using it, or them, after that panics, but for Close, which does nothing then.
Public Property IsClosed: Bool property #

Whether Close has closed it.
@PanicsAtCaller Public Property Changes: Int64 property #

How many rows the last INSERT, UPDATE, or DELETE changed.
@PanicsAtCaller Public Property LastId: Int64 property #

The rowid of the last row an INSERT added, or 0 before one has.
@PanicsAtCaller Public Method Execute(sql: String) Throws DatabaseError method #

Runs each statement in sql, in turn, and throws what the first that fails fails with, leaving what the ones before it did, but for what a Transaction undoes. Panics when one takes parameters, which Execute(sql, values) binds.
@PanicsAtCaller Public Method Execute<A: SqlValue>(sql: String, a: A) Throws DatabaseError method #

Runs sql's one statement with its parameters bound to values, in order, a value for each, as a Statement's Execute does. Throws a DatabaseError of QUERY when sql isn't one statement.
@PanicsAtCaller Public Method Execute<A, B>(sql: String, a: A, b: B) Throws DatabaseError method #

Runs sql's one statement with its parameters bound to a through b, in order, as the one that takes a alone does.
@PanicsAtCaller Public Method Execute<A, B, C>(sql: String, a: A, b: B, c: C) Throws DatabaseError method #

Runs sql's one statement with its parameters bound to a through c, in order, as the one that takes a alone does.
@PanicsAtCaller Public Method Execute<A, B, C, D>(sql: String, a: A, b: B, c: C, d: D) Throws DatabaseError method #

Runs sql's one statement with its parameters bound to a through d, in order, as the one that takes a alone does.
@PanicsAtCaller Public Method Execute<A, B, C, D, E>(sql: String, a: A, b: B, c: C, d: D, e: E) Throws DatabaseError method #

Runs sql's one statement with its parameters bound to a through e, in order, as the one that takes a alone does.
@PanicsAtCaller Public Method Execute<A, B, C, D, E, F>(sql: String, a: A, b: B, c: C, d: D, e: E, f: F) Throws DatabaseError method #

Runs sql's one statement with its parameters bound to a through f, in order, as the one that takes a alone does.
@PanicsAtCaller Public Method Execute<A, B, C, D, E, F, G>(sql: String, a: A, b: B, c: C, d: D, e: E, f: F, g: G) Throws DatabaseError method #

Runs sql's one statement with its parameters bound to a through g, in order, as the one that takes a alone does.
@PanicsAtCaller Public Method Execute<A, B, C, D, E, F, G, H>(sql: String, a: A, b: B, c: C, d: D, e: E, f: F, g: G, h: H) Throws DatabaseError method #

Runs sql's one statement with its parameters bound to a through h, in order, as the one that takes a alone does.
@PanicsAtCaller Public Method Rows(sql: String): Row[] Throws DatabaseError method #

The rows sql's one query reads, with its parameters bound to values as Execute's are, so Rows(sql) panics when it takes any.

@PanicsAtCaller Public Method Query<T>(sql: String): T[] Throws DatabaseError, ParseError method #

The rows sql's one query reads, each a T, as a Statement's Query<T>() gives them. Panics when sql takes parameters, which a Statement binds.

Examples

Struct Person
    Field Name: String
    Field Age: Int
End Struct

Local db = Try Database.Open(":memory:")
Try db.Execute("CREATE TABLE people (Name TEXT, Age INTEGER)")
Try db.Execute("INSERT INTO people VALUES ('Ada', 36), ('Alan', 41)")
Local people = Try db.Query<Person>("SELECT Name, Age FROM people ORDER BY Age")
Expect(people.Count == 2 && people[0].Name == "Ada" && people[1].Age == 41)

@PanicsAtCaller Public Method Query<T, A: SqlValue>(sql: String, a: A): T[] Throws DatabaseError, ParseError method #

The rows sql's one query reads as Ts, with its parameters bound to values as Execute's are.
@PanicsAtCaller Public Method Query<T, A, B>(sql: String, a: A, b: B): T[] Throws DatabaseError, ParseError method #

The rows sql's one query reads as Ts, with its parameters bound to a through b, in order.
@PanicsAtCaller Public Method Query<T, A, B, C>(sql: String, a: A, b: B, c: C): T[] Throws DatabaseError, ParseError method #

The rows sql's one query reads as Ts, with its parameters bound to a through c, in order.
@PanicsAtCaller Public Method Query<T, A, B, C, D>(sql: String, a: A, b: B, c: C, d: D): T[] Throws DatabaseError, ParseError method #

The rows sql's one query reads as Ts, with its parameters bound to a through d, in order.
@PanicsAtCaller Public Method Query<T, A, B, C, D, E>(sql: String, a: A, b: B, c: C, d: D, e: E): T[] Throws DatabaseError, ParseError method #

The rows sql's one query reads as Ts, with its parameters bound to a through e, in order.
@PanicsAtCaller Public Method Query<T, A, B, C, D, E, F>(sql: String, a: A, b: B, c: C, d: D, e: E, f: F): T[] Throws DatabaseError, ParseError method #

The rows sql's one query reads as Ts, with its parameters bound to a through f, in order.
@PanicsAtCaller Public Method Query<T, A, B, C, D, E, F, G>(sql: String, a: A, b: B, c: C, d: D, e: E, f: F, g: G): T[] Throws DatabaseError, ParseError method #

The rows sql's one query reads as Ts, with its parameters bound to a through g, in order.
@PanicsAtCaller Public Method Query<T, A, B, C, D, E, F, G, H>(sql: String, a: A, b: B, c: C, d: D, e: E, f: F, g: G, h: H): T[] Throws DatabaseError, ParseError method #

The rows sql's one query reads as Ts, with its parameters bound to a through h, in order.
@PanicsAtCaller Public Method Rows<A: SqlValue>(sql: String, a: A): Row[] Throws DatabaseError method #

The rows sql's one query reads, with its parameters bound to a, in order.
@PanicsAtCaller Public Method Rows<A, B>(sql: String, a: A, b: B): Row[] Throws DatabaseError method #

The rows sql's one query reads, with its parameters bound to a through b, in order.
@PanicsAtCaller Public Method Rows<A, B, C>(sql: String, a: A, b: B, c: C): Row[] Throws DatabaseError method #

The rows sql's one query reads, with its parameters bound to a through c, in order.
@PanicsAtCaller Public Method Rows<A, B, C, D>(sql: String, a: A, b: B, c: C, d: D): Row[] Throws DatabaseError method #

The rows sql's one query reads, with its parameters bound to a through d, in order.
@PanicsAtCaller Public Method Rows<A, B, C, D, E>(sql: String, a: A, b: B, c: C, d: D, e: E): Row[] Throws DatabaseError method #

The rows sql's one query reads, with its parameters bound to a through e, in order.
@PanicsAtCaller Public Method Rows<A, B, C, D, E, F>(sql: String, a: A, b: B, c: C, d: D, e: E, f: F): Row[] Throws DatabaseError method #

The rows sql's one query reads, with its parameters bound to a through f, in order.
@PanicsAtCaller Public Method Rows<A, B, C, D, E, F, G>(sql: String, a: A, b: B, c: C, d: D, e: E, f: F, g: G): Row[] Throws DatabaseError method #

The rows sql's one query reads, with its parameters bound to a through g, in order.
@PanicsAtCaller Public Method Rows<A, B, C, D, E, F, G, H>(sql: String, a: A, b: B, c: C, d: D, e: E, f: F, g: G, h: H): Row[] Throws DatabaseError method #

The rows sql's one query reads, with its parameters bound to a through h, in order.

@PanicsAtCaller Public Method Prepare(sql: String): Statement Throws DatabaseError method #

sql's one statement, prepared to run many times. Throws a DatabaseError of QUERY when sql holds no statement or more than one, or one that doesn't prepare, like one that names a table the database hasn't got.

Examples

Local db = Try Database.Open(":memory:")
Try db.Execute("CREATE TABLE squares (n INTEGER, square INTEGER)")
Local insert = Try db.Prepare("INSERT INTO squares VALUES (?, ?)")
For n = 1 To 3
    Try insert.Execute(n, n * n)
End For

Local rows = Try db.Rows("SELECT SUM(square) FROM squares")
Expect(rows[0].Integer(0) == 14)

@PanicsAtCaller Public Method Begin(): Transaction Throws DatabaseError method #
A new Transaction, which keeps all of what this Database does until its Commit, or undoes it: see Transaction. One begun while another is open is a part of that one. The outermost is SQLite's IMMEDIATE, which waits for other connections to finish writing when it begins.

Examples
```
Local db = Try Database.Open(":memory:")
Try db.Execute("CREATE TABLE log (line TEXT)")
Local transaction = Try db.Begin()
Try db.Execute("INSERT INTO log VALUES ('kept only on Commit')")
Try transaction.Rollback()
Expect((Try db.Rows("SELECT * FROM log")).Count == 0)
```
@PanicsAtCaller Public Method Migrate(migrations: String...) Throws DatabaseError method #
Brings the database up to date with migrations, the SQL that makes each version of it from the one before: the first makes version 1 from an empty database, the second version 2, and so on. The database keeps its version, as PRAGMA user_version, so each migration runs once, in order, in a Transaction of its own with the version it makes, and one that fails leaves the database as the one before it left it, and throws. Throws a DatabaseError too when the database's version is past the last migration's, as a newer program's would be.

Examples
```
Local db = Try Database.Open(":memory:")
Local first = "CREATE TABLE users (name TEXT)"
Local second = "ALTER TABLE users ADD COLUMN email TEXT"
Try db.Migrate(first)
// Again, the same database, with one more migration: only the new one runs.
Try db.Migrate(first, second)
Try db.Execute("INSERT INTO users VALUES (?, ?)", "Ada", "ada@example.com")
```

Report an issue with this page or with FutureBlitz on GitHub.