Swift-Kuery：来自 IBM Swift 小组的 SQL 数据库抽象层 API 库

Swift-Kuery

SQL database abstraction layer

Summary

Swift-Kuery is a pluggable SQL database driver/SDK abstraction layer. Its main idea is to unify the APIs to the various relational databases, providing a Swifty yet SQL-like API.

While Swift-Kuery is not an Object-Relational Mapping (ORM), it provides a great basis to build an ORM. It is also useful if you don't want to commit to a specific database, allowing easy switch between databases.

Swift-Kuery is easy to learn, consumable framework that comes with a set ofimplemented plugins.

Table of Contents

• Example
• Query Examples
• List of plugins
• License

Example

This example demonstrates how to execute an SQL query using Swift-Kuery and Swift-Kuery-PostgreSQL. It assumes there is a PostgreSQL server running on localhost:5432, that contains a table called Grades:

id   |  course   | grade
------+-----------+-------
12345 | physics   |    82
12345 | chemistry |    90
12345 | history   |    98
78901 | history   |   100
78901 | chemistry |    94
78901 | physics   |    90
24680 | physics   |    74
24680 | chemistry |    92
24680 | history   |    90

First we import Swift-Kuery and Swift-Kuery-PostgreSQL:

import SwiftKuery
import SwiftKueryPostgreSQL

Now we create a Table that corresponds to our Grades table in PostgreSQL - we set the table's name and its columns:

class Grades : Table {
    let tableName = "Grades"
    let id = Column("id")
    let course = Column("course")
    let grade = Column("grade")
}
let grades = Grades()

Next we create a connection to PostgreSQL and connect to it. Note that the API is asynchronous:

let connection = PostgreSQLConnection(host: "localhost", port: 5432, options: [.userName("username"), .password("password")])
connection.connect() { error: QueryError in
  if let error = error {
    print(error)
  }
  else {
    // Build and execute your query here.
  }

Now lets build the query. Suppose we want to retrieve the average grades for courses with average greater than 90, and sort the results by the average ascending. Here is the SQL query we need to build:

SELECT course, ROUND(AVG(grade), 1) AS "average" FROM grades
GROUP BY course
HAVING AVG(grade) > 90
ORDER BY AVG(grade) ASC

Note, that we also round the average grades and alias this column in the result as "average".

Here is how to create such query using Swift-Kuery:

let query = Select(grades.course, round(avg(grades.grade), to: 1).as("average"), from: grades)
            .group(by: grades.course)
            .having(avg(grades.grade) > 90)
            .order(by: .ASC(avg(grades.grade)))

As you can see, it is very similar to the SQL query syntax.

Now we execute the created query on our PostgreSQL connection:

connection.execute(query: query) { result: QueryResult in
  if let resultSet = queryResult.asResultSet {
    for title in resultSet.titles {
      // The column names of the result.
    }
    for row in resultSet.rows {
      for value in row {
        ...
      }
    }
  }
  else if let queryError = result.asError {
      // Something went wrong.
  }
}

The expected result is:

course     average
chemistry  92.0
history    96.0

Query Examples

Lets see more examples of how to build and execute SQL queries using Swift-Kuery.

Classes used in the examples:

class T1 {
  let tableName = "t1"
  let a = Column("a")
  let b = Column("b")
}

class T2 {
  let tableName = "t2"
  let c = Column("c")
  let b = Column("b")
}

SELECT * FROM t1;

let t1 = T1()

let s = Select(from: t1)

s.execute(connection) { queryResult in
  if let resultSet = queryResult.asResultSet {
    for title in resultSet.titles {
      ...
    }
    for row in resultSet.rows {
      for value in row {
        ...
      }
    }
  }
  else if let queryError = result.asError {
    ...
  }
}

...
let s = Select(t1.a, t1.b, from: t1)
  .where((t1.a.like("b%") || t1.a == "apple") && t1.b > 5)
  .order(by: .ASC(t1.b), .DESC(t1.a))
  .offset(5)

connection.execute(query: s) { queryResult in
   ...
}

...
let s = Select(ucase(t1.a).as("name"), from: t1)
  .where(t1.b >= 0)
  .group(by: t1.a)
  .having(sum(t1.b) > 3)
  .order(by: .DESC(t1.a))
...

...
let i = Insert(into: t1, rows: [["apple", 10], ["apricot", 3], ["banana", 17]])

connection.execute(query: i) { queryResult in
  if queryResult.success  {
    ...
  }
  else if let queryError = result.asError {
    ...
  }
}

...
let i = Insert(into: t1, values: "apple", 10)
...

...
let i = Insert(into: t1, valueTuples: (t1.a, "apricot"), (t1.b, "3"))
...

...
let i = Insert(into: t1, columns: [t1.a, t1.b], values: ["apricot", 3])
...

...
let u = Update(t1, set: [(t1.a, "peach"), (t1.b, 2)])
  .where(t1.a == "banana")
  .returning(t1.b)
...

let t1 = T1()
let t2 = T2()

let leftTable = t1.as("left")
let rightTable = t2.as("right")
let s2 = Select(from: leftTable)
  .leftJoin(rightTable)
  .on(leftTable.b == rightTable.b)
...

...
let s2 = Select(from: t1)
  .join(t2)
  .using(t1.b)
...

let i = Insert(into: t1, values: Parameter(), Parameter())

connection.execute(query: i1, parameters: "banana", 28) { queryResult in
  ...
}

let i = Insert(into: t1, values: Parameter("fruit"), Parameter("number"))

connection.execute(query: i1, parameters: ["number" : 28, "fruit" : "banana"]) { queryResult in
  ...
}

Raw query:

connection.execute("CREATE TABLE myTable (a varchar(40), b integer)") {  queryResult in
  ...
}

...
let s = Select(RawField("LEFT(a, 2) as raw"), from: t1)
  .where("b >= 0")
  .group(by: t1.a)
  .having("sum(b) > 3")
  .order(by: .DESC(t1.a))
...

...
let s = Select(from: t1)
  .where(t1.b >= any(Select(t2.b, from: t2)))
...

...
let s = Select(from: t1)
  .where(notExists(Select(from: t2).where(t2.b < 8)))
...

...
let s = Select(t2.c, from: t2)
    .group(by: t2.c)
    .having(sum(t2.b).notIn(Select(t1.b, from: t1).where(t1.a == "apple")))
...

List of plugins:

• PostgreSQL

• SQLite

License

This library is licensed under Apache 2.0. Full license text is available inLICENSE.