Perfect CRUD 简体中文

CRUD is an object-relational mapping (ORM) system for Swift 4+. CRUD takes Swift 4 Codable types and maps them to SQL database tables. CRUD can create tables based on Codable types and perform inserts and updates of objects in those tables. CRUD can also perform selects and joins of tables, all in a type-safe manner.

CRUD uses a simple, expressive, and type safe methodology for constructing queries as a series of operations. It is designed to be light-weight and has zero additional dependencies. It uses generics, KeyPaths and Codables to ensure as much misuse as possible is caught at compile time.

Database client library packages can add CRUD support by implementing a few protocols. Support is available for SQLite, Postgres, and MySQL.

To use CRUD in your project simply include the database connector of your choice as a dependency in your Package.swift file. For example:

// postgres
.package(url: "https://github.com/PerfectlySoft/Perfect-PostgreSQL.git", from: "3.2.0")
// mysql
.package(url: "https://github.com/PerfectlySoft/Perfect-MySQL.git", from: "3.2.0")
// sqlite
.package(url: "https://github.com/PerfectlySoft/Perfect-SQLite.git", from: "3.1.0")

CRUD support is built directly into each of these database connector packages.

Contents

• General Usage
• Operations
  • Database
    • Transaction
    • Create
      • Policy
    • Table
    • SQL
  • Table
    • Index
  • Join
    • Parent Child
    • Many to Many
    • Self Join
    • Junction Join
  • Where
    • Expression Operators
  • Order
  • Limit
  • Update
  • Insert
  • Delete
  • Select
  • Count
• Database Specific Operations
  • MySQL
    • Last Insert Id
  • SQLite
    • Last Insert Id
  • PostgreSQL
    • Returning
• Codable Types
  • Identity
• Error Handling
• Logging

General Usage

This is a simple example to show how CRUD is used.

// CRUD can work with most Codable types.
struct PhoneNumber: Codable {
	let personId: UUID
	let planetCode: Int
	let number: String
}
struct Person: Codable {
	let id: UUID
	let firstName: String
	let lastName: String
	let phoneNumbers: [PhoneNumber]?
}

// CRUD usage begins by creating a database connection.
// The inputs for connecting to a database will differ depending on your client library.
// Create a `Database` object by providing a configuration.
// These examples will use SQLite for demonstration purposes,
// 	but all code would be identical regardless of the datasource type.
let db = Database(configuration: try SQLiteDatabaseConfiguration(testDBName))

// Create the table if it hasn't been done already.
// Table creates are recursive by default, so "PhoneNumber" is also created here.
try db.create(Person.self, policy: .reconcileTable)

// Get a reference to the tables we will be inserting data into.
let personTable = db.table(Person.self)
let numbersTable = db.table(PhoneNumber.self)

// Add an index for personId, if it does not already exist.
try numbersTable.index(\.personId)

// Insert some sample data.
do {
	// Insert some sample data.
	let owen = Person(id: UUID(), firstName: "Owen", lastName: "Lars", phoneNumbers: nil)
	let beru = Person(id: UUID(), firstName: "Beru", lastName: "Lars", phoneNumbers: nil)
	
	// Insert the people
	try personTable.insert([owen, beru])
	
	// Give them some phone numbers
	try numbersTable.insert([
		PhoneNumber(personId: owen.id, planetCode: 12, number: "555-555-1212"),
		PhoneNumber(personId: owen.id, planetCode: 15, number: "555-555-2222"),
		PhoneNumber(personId: beru.id, planetCode: 12, number: "555-555-1212")])
}

// Perform a query.
// Let's find all people with the last name of Lars which have a phone number on planet 12.
let query = try personTable
		.order(by: \.lastName, \.firstName)
	.join(\.phoneNumbers, on: \.id, equals: \.personId)
		.order(descending: \.planetCode)
	.where(\Person.lastName == "Lars" && \PhoneNumber.planetCode == 12)
	.select()

// Loop through the results and print the names.
for user in query {
	// We joined PhoneNumbers, so we should have values here.
	guard let numbers = user.phoneNumbers else {
		continue
	}
	for number in numbers {
		print(number.number)
	}
}

Operations

Activity in CRUD is accomplished by obtaining a database connection object and then chaining a series of operations on that database. Some operations execute immediately while others (select) are executed lazily. Each operation that is chained will return an object which can be further chained or executed.

Operations are grouped here according to the objects which implement them. Note that many of the type definitions shown below have been abbreviated for simplicity and some functions implemented in extensions have been moved in to keep things in a single block.

Database

A Database object wraps and maintains a connection to a database. Database connectivity is specified by using a DatabaseConfigurationProtocol object. These will be specific to the database in question.

// postgres sample configuration
let db = Database(configuration: 
	try PostgresDatabaseConfiguration(database: postgresTestDBName, host: "localhost"))
// sqlite sample configuration
let db = Database(configuration: 
	try SQLiteDatabaseConfiguration(testDBName))
// MySQL sample configuration
let db = Database(configuration:
    	try MySQLDatabaseConfiguration(database: "testDBName", host: "localhost", username: "username", password: "password"))

Database objects implement this set of logical functions:

public struct Database<C: DatabaseConfigurationProtocol>: DatabaseProtocol {
	public typealias Configuration = C
	public let configuration: Configuration
	public init(configuration c: Configuration)
	public func table<T: Codable>(_ form: T.Type) -> Table>
	public func transaction<T>(_ body: () throws -> T) throws -> T
	public func create<A: Codable>(_ type: A.Type, 
		primaryKey: PartialKeyPath? = nil, 
		policy: TableCreatePolicy = .defaultPolicy) throws -> CreateSelf>
}

Transaction

The transaction operation will execute the body between a set of "BEGIN" and "COMMIT" or "ROLLBACK" statements. If the body completes execution without throwing an error then the transaction will be committed, otherwise it is rolled-back.

public extension Database {
	func transaction<T>(_ body: () throws -> T) throws -> T
}

Example usage:

try db.transaction {
	... further operations
}

The body of a transaction may optionally return a value.

let value = try db.transaction {
	... further operations
	return 42
}

Create

The create operation is given a Codable type. It will create a table corresponding to the type's structure. The table's primary key can be indicated as well as a "create policy" which determines some aspects of the operation.

public extension DatabaseProtocol {
	func create<A: Codable>(
		_ type: A.Type, 
		primaryKey: PartialKeyPath? = nil, 
		policy: TableCreatePolicy = .defaultPolicy) throws -> CreateSelf>
}

try db.create(TestTable1.self, primaryKey: \.id, policy: .reconcileTable)

TableCreatePolicy consists of the following options:

• .reconcileTable - If the database table already exists then any columns which differ between the type and the table will be either removed or added. Note that this policy will not alter columns which exist but have changed their types. For example, if a type's property changes from a String to an Int, this policy will not alter the column changing its type to Int.
• .shallow - If indicated, then joined type tables will not be automatically created. If not indicated, then any joined type tables will be automatically created.
• .dropTable - The database table will be dropped before it is created. This can be useful during development and testing, or for tables that contain ephemeral data which can be reset after a restart.

Calling create on a table which already exists is a harmless operation resulting in no changes unless the .reconcileTable or .dropTable policies are indicated. Existing tables will not be modified to match changes in the corresponding Codable type unless .reconcileTable is indicated.

Table

The table operation returns a Table object based on the indicated Codable type. Table objects are used to perform further operations.

public protocol DatabaseProtocol {
	func table<T: Codable>(_ form: T.Type) -> TableSelf>
}

Example usage:

let table1 = db.table(TestTable1.self)

SQL

CRUD can also execute bespoke SQL statements, mapping the results to an array of any suitable Codable type.

public extension Database {
	func sql(_ sql: String, bindings: Bindings = []) throws
	func sql<A: Codable>(_ sql: String, bindings: Bindings = [], _ type: A.Type) throws -> [A]
}

Example Usage:

try db.sql("SELECT * FROM mytable WHERE id = 2", TestTable1.self)

Table

Table can follow: Database.

Table supports: update, insert, delete, join, order, limit, where, select, and count.

A Table object can be used to perform updates, inserts, deletes or selects. Tables can only be accessed through a database object by providing the Codable type which is to be mapped. A table object can only appear in an operation chain once, and it must be the first item.

Table objects are parameterized based on the Swift object type you provide when you retrieve the table. Tables indicate the over-all resulting type of any operation. This will be referred to as the OverAllForm.

Example usage:

// get a table object representing the TestTable1 struct
// any inserts, updates, or deletes will affect "TestTable1"
// any selects will produce a collection of TestTable1 objects.
let table1 = db.table(TestTable1.self)

In the example above, TestTable1 is the OverAllForm. Any destructive operations will affect the corresponding database table. Any selects will produce a collection of TestTable1 objects.

Index

Index can follow: table.

Database indexes are important for good query performance. Given a table object, a database index can be added by calling the index function. Indexes should be added along with the code which creates the table.

The index function accepts one or more table keypaths.

Example usage:

struct Person: Codable {
    let id: UUID
    let firstName: String
    let lastName: String
}
// create the Person table
try db.create(Person.self)
// get a table object representing the Person struct
let table = db.table(Person.self)
// add index for lastName column
try table.index(\.lastName)
// add unique index for firstName & lastName columns
try table.index(unique: true, \.firstName, \.lastName)

Indexes can be created for individual columns, or for columns as a group. If multiple columns are frequenty used together in queries, then it can often improve performance by adding indexes including those columns.

By including the unique: true parameter, a unique index will be created, meaning that only one row can contain any possible column value. This can be applied to multiple columns, as seen in the example above. Consult your specific database's documentation for the exact behaviours of database indexes.

The index function is defined as:

public extension Table {
	func index(unique: Bool = false, _ keys: PartialKeyPath...) throws -> Index
}

Join

Join can follow: table, order, limit, or another join.

Join supports: join, where, order, limit, select, count.

A join brings in objects from another table in either a parent-child or many-to-many relationship scheme.

Parent-child example usage:

struct Parent: Codable {
	let id: Int
	let children: [Child]?
}
struct Child: Codable {
	let id: Int
	let parentId: Int
}
try db.transaction {
	try db.create(Parent.self, policy: [.shallow, .dropTable]).insert(
		Parent(id: 1, children: nil))
	try db.create(Child.self, policy: [.shallow, .dropTable]).insert(
		[Child(id: 1, parentId: 1),
		 Child(id: 2, parentId: 1),
		 Child(id: 3, parentId: 1)])
}
let join = try db.table(Parent.self)
	.join(\.children,
		  on: \.id,
		  equals: \.parentId)
	.where(\Parent.id == 1)
guard let parent = try join.first() else {
	return XCTFail("Failed to find parent id: 1")
}
guard let children = parent.children else {
	return XCTFail("Parent had no children")
}
XCTAssertEqual(3, children.count)
for child in children {
	XCTAssertEqual(child.parentId, parent.id)
}

The example above joins Child objects on the Parent.children property, which is of type [Child]?. When the query is executed, all objects from the Child table that have a parentId which matches the Parent id 1 will be included in the results. This is a typical parent-child relationship.

Many-to-many example usage:

struct Student: Codable {
	let id: Int
	let classes: [Class]?
}
struct Class: Codable {
	let id: Int
	let students: [Student]?
}
struct StudentClasses: Codable {
	let studentId: Int
	let classId: Int
}
try db.transaction {
	try db.create(Student.self, policy: [.dropTable, .shallow]).insert(
		Student(id: 1, classes: nil))
	try db.create(Class.self, policy: [.dropTable, .shallow]).insert([
		Class(id: 1, students: nil),
		Class(id: 2, students: nil),
		Class(id: 3, students: nil)])
	try db.create(StudentClasses.self, policy: [.dropTable, .shallow]).insert([
		StudentClasses(studentId: 1, classId: 1),
		StudentClasses(studentId: 1, classId: 2),
		StudentClasses(studentId: 1, classId: 3)])
}
let join = try db.table(Student.self)
	.join(\.classes,
		  with: StudentClasses.self,
		  on: \.id,
		  equals: \.studentId,
		  and: \.id,
		  is: \.classId)
	.where(\Student.id == 1)
guard let student = try join.first() else {
	return XCTFail("Failed to find student id: 1")
}
guard let classes = student.classes else {
	return XCTFail("Student had no classes")
}
XCTAssertEqual(3, classes.count)
for aClass in classes {
	let join = try db.table(Class.self)
		.join(\.students,
			  with: StudentClasses.self,
			  on: \.id,
			  equals: \.classId,
			  and: \.id,
			  is: \.studentId)
		.where(\Class.id == aClass.id)
	guard let found = try join.first() else {
		XCTFail("Class with no students")
		continue
	}
	guard nil != found.students?.first(where: { $0.id == student.id }) else {
		XCTFail("Student not found in class")
		continue
	}
}

Self Join example usage:

struct Me: Codable {
	let id: Int
	let parentId: Int
	let mes: [Me]?
	init(id i: Int, parentId p: Int) {
		id = i
		parentId = p
		mes = nil
	}
}
try db.transaction {
	try db.create(Me.self, policy: .dropTable).insert([
		Me(id: 1, parentId: 0),
		Me(id: 2, parentId: 1),
		Me(id: 3, parentId: 1),
		Me(id: 4, parentId: 1),
		Me(id: 5, parentId: 1)])
}
let join = try db.table(Me.self)
	.join(\.mes, on: \.id, equals: \.parentId)
	.where(\Me.id == 1)
guard let me = try join.first() else {
	return XCTFail("Unable to find me.")
}
guard let mes = me.mes else {
	return XCTFail("Unable to find meesa.")
}
XCTAssertEqual(mes.count, 4)

Junction Join example usage:

struct Student: Codable {
	let id: Int
	let classes: [Class]?
	init(id i: Int) {
		id = i
		classes = nil
	}
}
struct Class: Codable {
	let id: Int
	let students: [Student]?
	init(id i: Int) {
		id = i
		students = nil
	}
}
struct StudentClasses: Codable {
	let studentId: Int
	let classId: Int
}
try db.transaction {
	try db.create(Student.self, policy: [.dropTable, .shallow]).insert(
		Student(id: 1))
	try db.create(Class.self, policy: [.dropTable, .shallow]).insert([
		Class(id: 1),
		Class(id: 2),
		Class(id: 3)])
	try db.create(StudentClasses.self, policy: [.dropTable, .shallow]).insert([
		StudentClasses(studentId: 1, classId: 1),
		StudentClasses(studentId: 1, classId: 2),
		StudentClasses(studentId: 1, classId: 3)])
}
let join = try db.table(Student.self)
	.join(\.classes,
		  with: StudentClasses.self,
		  on: \.id,
		  equals: \.studentId,
		  and: \.id,
		  is: \.classId)
	.where(\Student.id == 1)
guard let student = try join.first() else {
	return XCTFail("Failed to find student id: 1")
}
guard let classes = student.classes else {
	return XCTFail("Student had no classes")
}
XCTAssertEqual(3, classes.count)

Joins are not currently supported in updates, inserts, or deletes (cascade deletes/recursive updates are not supported).

The Join protocol has two functions. The first handles standard two table joins. The second handles junction (three table) joins.

public protocol JoinAble: TableProtocol {
	// standard join
	func join<NewType: Codable, KeyType: Equatable>(
		_ to: KeyPath?>,
		on: KeyPath,
		equals: KeyPath) throws -> JoinSelf, NewType, KeyType>
	// junction join
	func join<NewType: Codable, Pivot: Codable, FirstKeyType: Equatable, SecondKeyType: Equatable>(
		_ to: KeyPath?>,
		with: Pivot.Type,
		on: KeyPath,
		equals: KeyPath,
		and: KeyPath,
		is: KeyPath) throws -> JoinPivotSelf, NewType, Pivot, FirstKeyType, SecondKeyType>
}

A standard join requires three parameters:

to - keypath to a property of the OverAllForm. This keypath should point to an Optional array of non-integral Codable types. This property will be set with the resulting objects.

on - keypath to a property of the OverAllForm which should be used as the primary key for the join (typically one would use the actual table primary key column).

equals - keypath to a property of the joined type which should be equal to the OverAllForm's on property. This would be the foreign key.

A junction join requires six parameters:

to - keypath to a property of the OverAllForm. This keypath should point to an Optional array of non-integral Codable types. This property will be set with the resulting objects.

with - The type of the junction table.

on - keypath to a property of the OverAllForm which should be used as the primary key for the join (typically one would use the actual table primary key column).

equals - keypath to a property of the junction type which should be equal to the OverAllForm's on property. This would be the foreign key.

and - keypath to a property of the child table type which should be used as the key for the join (typically one would use the actual table primary key column).

is - keypath to a property of the junction type which should be equal to the child table's and property.

Any joined type tables which are not explicitly included in a join will be set to nil for any resulting OverAllForm objects.

If a joined table is included in a join but there are no resulting joined objects, the OverAllForm's property will be set to an empty array.

Where

Where can follow: table, join, order.

Where supports: select, count, update (when following table), delete (when following table).

A where operation introduces a criteria which will be used to filter exactly which objects should be selected, updated, or deleted from the database. Where can only be used when performing a select/count, update, or delete.

public protocol WhereAble: TableProtocol {
	func `where`(_ expr: CRUDBooleanExpression) -> WhereSelf>
}

Where operations are optional, but only one where can be included in an operation chain and it must be the penultimate operation in the chain.

Example usage:

let table = db.table(TestTable1.self)
// insert a new object and then find it
let newOne = TestTable1(id: 2000, name: "New One", integer: 40)
try table.insert(newOne)
// search for this one object by id
let query = table.where(\TestTable1.id == newOne.id)
guard let foundNewOne = try query.first() else {
	...
}

The parameter given to the where operation is a CRUDBooleanExpression object. These are produced by using any of the supported expression operators.

Standard Swift Operators:

• Equality: ==, !=

• Comparison: <, <=, >, >=

• Logical: !, &&, ||

Custom Comparison Operators:

• Contained/In: ~, !~

• Like: %=%, =%, %=, %!=%, !=%, %!=

For the equality and comparison operators, the left-hand operand must be a KeyPath indicating a Codable property of a Codable type. The right-hand operand can be Int, Double, String, [UInt8], Bool, UUID, or Date. The KeyPath can indicate an Optional property value, in which case the right-hand operand may be nil to indicate an "IS NULL", "IS NOT NULL" type of query.

The equality and comparison operators are type-safe, meaning you can not make a comparison between, for example, an Int and a String. The type of the right-hand operand must match the KeyPath property type. This is how Swift normally works, so it should not come with any surprises.

Any type which has been introduced to the query through a table or join operation can be used in an expression. Using KeyPaths for types not used elsewhere in the query is a runtime error.

In this snippet:

table.where(\TestTable1.id > 20)

\TestTable1.id is the KeyPath, pointing to the Int id for the object. 20 is the literal operand value. The > operator between them produces a CRUDBooleanExpression which can be given directly to where or used with other operators to make more complex expressions.

The logical operators permit and, or, and not operations given two CRUDBooleanExpression objects. These use the standard Swift &&, ||, and ! operators.

table.where(\TestTable1.id > 20 && 
	!(\TestTable1.name == "Me" || \TestTable1.name == "You"))

The contained/in operators take a KeyPath in the right-hand side and an array of objects on the left.

table.where(\TestTable1.id ~ [2, 4])
table.where(\TestTable1.id !~ [2, 4])

The above will select all TestTable1 objects whose id is in the array, or not in the array, respectively.

The Like operators are used only with String values. These permit begins with, ends with, and contains searches on String based columns.

try table.where(\TestTable2.name %=% "me") // contains
try table.where(\TestTable2.name =% "me") // begins with
try table.where(\TestTable2.name %= "me") // ends with
try table.where(\TestTable2.name %!=% "me") // not contains
try table.where(\TestTable2.name !=% "me") // not begins with
try table.where(\TestTable2.name %!= "me") // not ends with

Property Optionals

In some cases you may need to query using an optional parameter in your model. In the Person model above, we may need to add an optional height field:

struct Person: Codable {
	// ...
	var height: Double? // height in cm
}

In the case we wanted to search for People who have not yet provided their height, this simple query will find all rows where height is NULL.

let people = try personTable.where(\Person.height == nil).select().map{ $0 }

Alternatively, you may need to query for individuals a certain height or taller.

let queryHeight = 170.0
let people = try personTable.where(\Person.height! >= queryHeight).select().map{ $0 }

Notice the force-unwraped key path - \Person.height!. This is type-safe and required by the compiler in order to compare the optional type on the model to the non-optional value in the query.

Order

Order can follow: table, join.

Order supports: join, where, order, limit select, count.

An order operation introduces an ordering of the over-all resulting objects and/or of the objects selected for a particular join. An order operation should immediately follow either a table or a join. You may also order over fields with optional types.

public protocol OrderAble: TableProtocol {
	func order(by: PartialKeyPath
...) -> OrderingSelf>
	func order(descending by: PartialKeyPath...) -> OrderingSelf>
}

Example usage:

let query = try db.table(TestTable1.self)
				.order(by: \.name)
			.join(\.subTables, on: \.id, equals: \.parentId)
				.order(by: \.id)
			.where(\TestTable2.name == "Me")

When the above query is executed it will apply orderings to both the main list of returned objects and to their individual "subTables" collections.

Ordering by a nullable field:

struct Person: Codable {
	// ...
	var height: Double? // height in cm
}

// ...

let person = try personTable.order(descending: \.height).select().map {$0}

Limit

Limit can follow: order, join, table.

Limit supports: join, where, order, select, count.

A limit operation can follow a table, join, or order operation. Limit can both apply an upper bound on the number of resulting objects and impose a skip value. For example the first five found records may be skipped and the result set will begin at the sixth row.

public protocol LimitAble: TableProtocol {
	func limit(_ max: Int, skip: Int) -> LimitSelf>
}

Ranges of Ints can also be passed to the limit func. This includes ranges in the form of a.., a...b, a..., ...b, ...

public extension Limitable {
	func limit(_ range: Range<Int>) -> LimitSelf>
	func limit(_ range: ClosedRange<Int>) -> LimitSelf>
	func limit(_ range: PartialRangeFrom<Int>) -> LimitSelf>
	func limit(_ range: PartialRangeThrough<Int>) -> LimitSelf>
	func limit(_ range: PartialRangeUpTo<Int>) -> LimitSelf>
}

let query = try db.table(TestTable1.self)
				.order(by: \.name)
				.limit(20..<30)
			.join(\.subTables, on: \.id, equals: \.parentId)
				.order(by: \.id)
				.limit(1000)
			.where(\TestTable2.name == "Me")

public protocol UpdateAble: TableProtocol {
	func update(_ instance: OverAllForm, setKeys: PartialKeyPath, _ rest: PartialKeyPath...) throws -> UpdateSelf>
	func update(_ instance: OverAllForm, ignoreKeys: PartialKeyPath, _ rest: PartialKeyPath...) throws -> UpdateSelf>
	func update(_ instance: OverAllForm) throws -> UpdateSelf>
}

let newOne = TestTable1(id: 2000, name: "New One", integer: 40)
let newId: Int = try db.transaction {
	try db.table(TestTable1.self).insert(newOne)
	let newOne2 = TestTable1(id: 2000, name: "New One Updated", integer: 41)
	try db.table(TestTable1.self)
		.where(\TestTable1.id == newOne.id)
		.update(newOne2, setKeys: \.name)
	return newOne2.id
}
let j2 = try db.table(TestTable1.self)
	.where(\TestTable1.id == newId)
	.select().map { $0 }
XCTAssertEqual(1, j2.count)
XCTAssertEqual(2000, j2[0].id)
XCTAssertEqual("New One Updated", j2[0].name)
XCTAssertEqual(40, j2[0].integer)

public extension Table {
	func insert(_ instances: [Form]) throws -> Insert>
	func insert(_ instance: Form) throws -> Insert>
	func insert(_ instances: [Form], setKeys: PartialKeyPath, _ rest: PartialKeyPath...) throws -> Insert>
	func insert(_ instance: Form, setKeys: PartialKeyPath, _ rest: PartialKeyPath...) throws -> Insert>
	func insert(_ instances: [Form], ignoreKeys: PartialKeyPath, _ rest: PartialKeyPath...) throws -> Insert>
	func insert(_ instance: Form, ignoreKeys: PartialKeyPath, _ rest: PartialKeyPath...) throws -> Insert>
}

let table = db.table(TestTable1.self)
let newOne = TestTable1(id: 2000, name: "New One", integer: 40, double: nil, blob: nil, subTables: nil)
let newTwo = TestTable1(id: 2001, name: "New One", integer: 40, double: nil, blob: nil, subTables: nil)
try table.insert([newOne, newTwo], setKeys: \.id, \.name)

public protocol DeleteAble: TableProtocol {
	func delete() throws -> DeleteSelf>
}

let table = db.table(TestTable1.self)
let newOne = TestTable1(id: 2000, name: "New One", integer: 40, double: nil, blob: nil, subTables: nil)
try table.insert(newOne)
let query = table.where(\TestTable1.id == newOne.id)
let j1 = try query.select().map { $0 }
assert(j1.count == 1)
try query.delete()
let j2 = try query.select().map { $0 }
assert(j2.count == 0)

public protocol SelectAble: TableProtocol {
	func select() throws -> SelectSelf>
	func count() throws -> Int
	func first() throws -> OverAllForm?
}

let table = db.table(TestTable1.self)
let query = table.where(\TestTable1.blob == nil)
let values = try query.select().map { $0 }
let count = try query.count()
assert(count == values.count)

public extension Insert {
	func lastInsertId() throws -> UInt64?
}

let id = try table
	.insert(ReturningItem(id: 0, def: 0),
			ignoreKeys: \ReturningItem.id)
	.lastInsertId()

public extension Insert {
	func lastInsertId() throws -> Int?
}

public extension Table where C.Configuration == PostgresDatabaseConfiguration {
	func returning<R: Decodable>(
			_ returning: KeyPath, 
			insert: Form) throws -> R
	func returning<R: Decodable, Z: Decodable>(
			_ returning: KeyPath, 
			insert: Form,
			setKeys: KeyPath, 
			_ rest: PartialKeyPath...) throws -> R
	func returning<R: Decodable, Z: Decodable>(
			_ returning: KeyPath, 
			insert: Form,
			ignoreKeys: KeyPath, 
			_ rest: PartialKeyPath...) throws -> R
	func returning<R: Decodable>(
			_ returning: KeyPath, 
			insert: [Form]) throws -> [R]
	func returning<R: Decodable, Z: Decodable>(
			_ returning: KeyPath, 
			insert: [Form],
			setKeys: KeyPath, 
			_ rest: PartialKeyPath...) throws -> [R]
	func returning<R: Decodable, Z: Decodable>(
			_ returning: KeyPath, 
			insert: [Form],
			ignoreKeys: KeyPath, 
			_ rest: PartialKeyPath...) throws -> [R]
}

public extension Table where C.Configuration == PostgresDatabaseConfiguration {
	func returning(
			insert: Form) throws -> OverAllForm
	func returning<Z: Decodable>(
			insert: Form,
			setKeys: KeyPath, 
			_ rest: PartialKeyPath...) throws -> OverAllForm
	func returning<Z: Decodable>(
			insert: Form,
			ignoreKeys: KeyPath, 
			_ rest: PartialKeyPath...) throws -> OverAllForm
	func returning(
			insert: [Form]) throws -> [OverAllForm]
	func returning<Z: Decodable>(
			insert: [Form],
			setKeys: KeyPath, 
			_ rest: PartialKeyPath...) throws -> [OverAllForm]
	func returning<Z: Decodable>(
			insert: [Form],
			ignoreKeys: KeyPath, 
			_ rest: PartialKeyPath...) throws -> [OverAllForm]
}

public extension Table where C.Configuration == PostgresDatabaseConfiguration {
	func returning<R: Decodable>(
			_ returning: KeyPath, 
			update: Form) throws -> [R]
	func returning<R: Decodable, Z: Decodable>(
			_ returning: KeyPath, 
			update: Form,
			setKeys: KeyPath, 
			_ rest: PartialKeyPath...) throws -> [R]
	func returning<R: Decodable, Z: Decodable>(
			_ returning: KeyPath, 
			update: Form,
			ignoreKeys: KeyPath, 
			_ rest: PartialKeyPath...) throws -> [R]
	func returning(
			update: Form) throws -> [OverAllForm]
	func returning<Z: Decodable>(
			update: Form,
			setKeys: KeyPath, 
			_ rest: PartialKeyPath...) throws -> [OverAllForm]
	func returning<Z: Decodable>(
			update: Form,
			ignoreKeys: KeyPath, 
			_ rest: PartialKeyPath...) throws -> [OverAllForm]
}

struct ReturningItem: Codable, Equatable {
	let id: UUID
	let def: Int?
	init(id: UUID, def: Int? = nil) {
		self.id = id
		self.def = def
	}
}
try db.sql("DROP TABLE IF EXISTS \(ReturningItem.CRUDTableName)")
try db.sql("CREATE TABLE \(ReturningItem.CRUDTableName) (id UUID PRIMARY KEY, def int DEFAULT 42)")
let table = db.table(ReturningItem.self)
// returning inserts
do {
	let item = ReturningItem(id: UUID())
	let def = try table.returning(\.def, insert: item, ignoreKeys: \.def)
	XCTAssertEqual(def, 42)
}
do {
	let items = [ReturningItem(id: UUID()),
				 ReturningItem(id: UUID()),
				 ReturningItem(id: UUID())]
	let defs = try table.returning(\.def, insert: items, ignoreKeys: \.def)
	XCTAssertEqual(defs, [42, 42, 42])
}
do {
	let id = UUID()
	let item = ReturningItem(id: id, def: 42)
	let id0 = try table.returning(\.id, insert: item)
	XCTAssertEqual(id0, id)
}
do {
	let items = [ReturningItem(id: UUID()),
				 ReturningItem(id: UUID()),
				 ReturningItem(id: UUID())]
	let defs = try table.returning(insert: items, ignoreKeys: \.def)
	XCTAssertEqual(defs.map{$0.id}, items.map{$0.id})
	XCTAssertEqual(defs.compactMap{$0.def}.count, defs.count)
}

// returning update
do {
	let id = UUID()
	var item = ReturningItem(id: id)
	try table.insert(item, ignoreKeys: \.def)
	item.def = 300
	let item0 = try table
		.where(\ReturningItem.id == id)
		.returning(\.def, update: item, ignoreKeys: \.id)
	XCTAssertEqual(item0.count, 1)
	XCTAssertEqual(item.def, item0.first)
}

struct TestTable1: Codable, TableNameProvider {
	enum CodingKeys: String, CodingKey {
		// specify custom column names for some properties
		case id, name, integer = "int", double = "doub", blob, subTables
	}
	// specify a custom table name
	static let tableName = "test_table_1"
	
	let id: Int
	let name: String?
	let integer: Int?
	let double: Double?
	let blob: [UInt8]?
	let subTables: [TestTable2]?
}

struct TestTable2: Codable {
	let id: UUID
	let parentId: Int
	let date: Date
	let name: String?
	let int: Int?
	let doub: Double?
	let blob: [UInt8]?
}

// log an informative message.
CRUDLogging.log(.info, "This is my message.")

public extension CRUDLogging {
	public static var queryLogDestinations: [CRUDLogDestination]
	public static var errorLogDestinations: [CRUDLogDestination]
}

public enum CRUDLogDestination {
	case none
	case console
	case file(String)
	case custom((CRUDLogEvent) -> ())
}