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

   <h2>Swift-Kuery</h2>    <p>SQL database abstraction layer</p>    <h2>Summary</h2>    <p>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.</p>    <p>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.</p>    <p>Swift-Kuery is easy to learn, consumable framework that comes with a set ofimplemented plugins.</p>    <h2>Table of Contents</h2>    <ul>     <li>Example</li>     <li>Query Examples</li>     <li>List of plugins</li>     <li>License</li>    </ul>    <h2>Example</h2>    <p>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:</p>    <pre>  <code class="language-swift">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</code></pre>    <p>First we import Swift-Kuery and Swift-Kuery-PostgreSQL:</p>    <pre>  <code class="language-swift">import SwiftKuery  import SwiftKueryPostgreSQL</code></pre>    <p>Now we create a Table that corresponds to our Grades table in PostgreSQL - we set the table's name and its columns:</p>    <pre>  <code class="language-swift">class Grades : Table {      let tableName = "Grades"      let id = Column("id")      let course = Column("course")      let grade = Column("grade")  }  let grades = Grades()</code></pre>    <p>Next we create a connection to PostgreSQL and connect to it. Note that the API is asynchronous:</p>    <pre>  <code class="language-swift">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.    }</code></pre>    <p>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:</p>    <pre>  <code class="language-swift">SELECT course, ROUND(AVG(grade), 1) AS "average" FROM grades  GROUP BY course  HAVING AVG(grade) > 90  ORDER BY AVG(grade) ASC</code></pre>    <p>Note, that we also round the average grades and alias this column in the result as "average".</p>    <p>Here is how to create such query using Swift-Kuery:</p>    <pre>  <code class="language-swift">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)))</code></pre>    <p>As you can see, it is very similar to the SQL query syntax.</p>    <p>Now we execute the created query on our PostgreSQL connection:</p>    <pre>  <code class="language-swift">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.    }  }</code></pre>    <p>The expected result is:</p>    <pre>  <code class="language-swift">course     average  chemistry  92.0  history    96.0</code></pre>    <h2>Query Examples</h2>    <p>Lets see more examples of how to build and execute SQL queries using Swift-Kuery.</p>    <p>Classes used in the examples:</p>    <pre>  <code class="language-swift">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")  }</code></pre>    <p>SELECT * FROM t1;</p>    <pre>  <code class="language-swift">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 {      ...    }  }</code></pre>    <p>SELECT a, b FROM t1</p>    <p>WHERE (a LIKE '%b' OR a = 'apple') AND b > 5</p>    <p>ORDER BY b ASC, a DESC</p>    <p>OFFSET 5;</p>    <pre>  <code class="language-swift">...  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     ...  }</code></pre>    <p>SELECT UCASE(a) AS name FROM t1</p>    <p>WHERE b >= 0</p>    <p>GROUP BY a</p>    <p>HAVING SUM(b) > 3</p>    <p>ORDER BY a DESC;</p>    <pre>  <code class="language-swift">...  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))  ...</code></pre>    <p>INSERT INTO t1</p>    <p>VALUES ('apple', 10), ('apricot', 3), ('banana', 17);</p>    <pre>  <code class="language-swift">...  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 {      ...    }  }</code></pre>    <p>INSERT INTO t1</p>    <p>VALUES ('apple', 10);</p>    <pre>  <code class="language-swift">...  let i = Insert(into: t1, values: "apple", 10)  ...</code></pre>    <p>INSERT INTO t1 (a, b)</p>    <p>VALUES ('apricot', '3');</p>    <pre>  <code class="language-swift">...  let i = Insert(into: t1, valueTuples: (t1.a, "apricot"), (t1.b, "3"))  ...</code></pre>    <p>INSERT INTO t1 (a, b)</p>    <p>VALUES ('apricot', '3');</p>    <pre>  <code class="language-swift">...  let i = Insert(into: t1, columns: [t1.a, t1.b], values: ["apricot", 3])  ...</code></pre>    <p>UPDATE t1 SET a = 'peach', b = 2</p>    <p>WHERE a = 'banana'</p>    <p>RETURNING b;</p>    <pre>  <code class="language-swift">...  let u = Update(t1, set: [(t1.a, "peach"), (t1.b, 2)])    .where(t1.a == "banana")    .returning(t1.b)  ...</code></pre>    <p>SELECT * FROM t1 AS left</p>    <p>LEFT JOIN t2 AS right</p>    <p>ON left.b = right.b;</p>    <pre>  <code class="language-swift">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)  ...</code></pre>    <p>SELECT * FROM t1</p>    <p>JOIN t2</p>    <p>USING (b);</p>    <pre>  <code class="language-swift">...  let s2 = Select(from: t1)    .join(t2)    .using(t1.b)  ...</code></pre>    <p>INSERT INTO t1</p>    <p>VALUES (@0,@1);</p>    <pre>  <code class="language-swift">let i = Insert(into: t1, values: Parameter(), Parameter())    connection.execute(query: i1, parameters: "banana", 28) { queryResult in    ...  }</code></pre>    <p>INSERT INTO t1</p>    <p>VALUES (@fruit,@number);</p>    <pre>  <code class="language-swift">let i = Insert(into: t1, values: Parameter("fruit"), Parameter("number"))    connection.execute(query: i1, parameters: ["number" : 28, "fruit" : "banana"]) { queryResult in    ...  }</code></pre>    <p>Raw query:</p>    <pre>  <code class="language-swift">connection.execute("CREATE TABLE myTable (a varchar(40), b integer)") {  queryResult in    ...  }</code></pre>    <p>SELECT LEFT(a, 2) as raw FROM t1</p>    <p>WHERE b >= 0 GROUP BY a</p>    <p>HAVING sum(b) > 3</p>    <p>ORDER BY a DESC;</p>    <pre>  <code class="language-swift">...  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))  ...</code></pre>    <p>SELECT * FROM t1</p>    <p>WHERE b >= ANY (SELECT b FROM t2);</p>    <pre>  <code class="language-swift">...  let s = Select(from: t1)    .where(t1.b >= any(Select(t2.b, from: t2)))  ...</code></pre>    <p>SELECT * FROM t1</p>    <p>WHERE NOT EXISTS (SELECT * FROM t2 WHERE b < 8);</p>    <pre>  <code class="language-swift">...  let s = Select(from: t1)    .where(notExists(Select(from: t2).where(t2.b < 8)))  ...</code></pre>    <p>SELECT c FROM t2 GROUP BY c</p>    <p>HAVING SUM(b) NOT IN (SELECT b FROM t1 WHERE a = 'apple');</p>    <pre>  <code class="language-swift">...  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")))  ...</code></pre>    <h2>List of plugins:</h2>    <ul>     <li> <p><a href="/misc/goto?guid=4959730697689804004" rel="nofollow,noindex">PostgreSQL</a></p> </li>     <li> <p><a href="/misc/goto?guid=4959730697770210818" rel="nofollow,noindex">SQLite</a></p> </li>    </ul>    <p> </p>    <p> </p>