Swift Sql database 抽象层.zip

  • N0_231035
  • 1.1MB
  • zip
  • 0
  • VIP专享
  • 0
  • 2022-05-09 01:11
Swift Sql database 抽象层.zip,sql数据库抽象层
<p align="center"> <a href="http://kitura.io/" rel='nofollow' onclick='return false;'> <img src="https://raw.githubusercontent.com/IBM-Swift/Kitura/master/Sources/Kitura/resources/kitura-bird.svg?sanitize=true" height="100" alt="Kitura"> </a> </p> <p align="center"> <a href="https://ibm-swift.github.io/Swift-Kuery/index.html" rel='nofollow' onclick='return false;'> <img src="https://img.shields.io/badge/apidoc-SwiftKuery-1FBCE4.svg?style=flat" alt="APIDoc"> </a> <a href="https://travis-ci.org/IBM-Swift/Swift-Kuery" rel='nofollow' onclick='return false;'> <img src="https://travis-ci.org/IBM-Swift/Swift-Kuery.svg?branch=master" alt="Build Status - Master"> </a> <img src="https://img.shields.io/badge/os-macOS-green.svg?style=flat" alt="macOS"> <img src="https://img.shields.io/badge/os-linux-green.svg?style=flat" alt="Linux"> <img src="https://img.shields.io/badge/license-Apache2-blue.svg?style=flat" alt="Apache 2"> <a href="http://swift-at-ibm-slack.mybluemix.net/" rel='nofollow' onclick='return false;'> <img src="http://swift-at-ibm-slack.mybluemix.net/badge.svg" alt="Slack Status"> </a> </p> # Swift-Kuery `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. This allows easy switching between databases and forms the basis for an Object-Relational Mapping (ORM) framework. [Swift-Kuery-ORM](https://github.com/IBM-Swift/Swift-Kuery-ORM) is an ORM, built on top of Swift-Kuery, which allows you to simplify the persistence of model objects with your server. `Swift-Kuery` is an easy to learn, consumable framework that comes with a set of [implemented plugins](#list-of-plugins). ## Table of Contents * [Swift version](#swift-version) * [Usage](#usage) * [SQL Injection Prevention using Parameterization](#sql-injection-prevention-using-parameterization) * [Prepared Statements](#prepared-statements) * [Schema Management](#schema-management) * [Query Examples](#query-examples) * [List of plugins](#list-of-plugins) * [API Reference](#api-reference) * [Community](#community) * [License](#license) ## Swift version The latest version of Swift-Kuery requires **Swift 4.0** or newer. You can download this version of the Swift binaries by following this [link](https://swift.org/download/). Compatibility with other Swift versions is not guaranteed. ## Usage This example demonstrates how to execute an SQL query using `Swift-Kuery` with the [Swift-Kuery-PostgreSQL](https://github.com/IBM-Swift/Swift-Kuery-PostgreSQL) plugin. The starting point for this example is an existing Swift package. If you don't have one already, create and enter a directory named e.g. `SwiftKueryExample`. Now run the swift package's init command, to create an executable type, by running `swift package init --type executable`. ### Creating A PostgreSQL Database 1. Install PostgreSQL #### Mac `brew install postgresql` #### Ubuntu Linux `sudo apt-get install postgresql postgresql-contrib` 2. Create a `school` database ``` createdb school psql school ``` 3. Create a `grades` table ``` CREATE TABLE "Grades" ( id varchar(100) PRIMARY KEY, course text NOT NULL, grade integer ); ``` ### Update your Package.swift file Add Swift-Kuery and your Kuery plugin, in this case Swift-Kuery-PostgreSQL, to the dependencies within your application's `Package.swift` file. Substitute `"x.x.x"` with the latest `Swift-Kuery` [release](https://github.com/IBM-Swift/Swift-Kuery/releases) and `"y.y.y"` with the latest plugin [release](https://github.com/IBM-Swift/Swift-Kuery-PostgreSQL/releases). ```swift dependencies: [ ... // Add this line .package(url: "https://github.com/IBM-Swift/Swift-Kuery.git", from: "x.x.x"), .package(url: "https://github.com/IBM-Swift/Swift-Kuery-PostgreSQL.git", from: "y.y.y"), ], targets: [ .target( name: ... // Add the module to your target(s) dependencies: [..., "SwiftKuery", "SwiftKueryPostgreSQL"]), ] ``` ### Executing SQL queries Inside the `main.swift` file: 1. Add SwiftKuery and SwiftKueryPostgreSQL to your import statements: ```swift import SwiftKuery import SwiftKueryPostgreSQL ``` 2. Create a `Table` class, which matches the `grades` table you created in the database: ```swift class Grades: Table { let tableName = "Grades" let id = Column("id", Int32.self, primaryKey: true) let course = Column("course", String.self) let grade = Column("grade", Int32.self) } let grades = Grades() ``` 3. Create a pool of connections to PostgreSQL: ```swift let pool = PostgreSQLConnection.createPool(host: "localhost", port: 5432, options: [.databaseName("school")], poolOptions: ConnectionPoolOptions(initialCapacity: 10, maxCapacity: 50)) ``` 4. Create some example students: ```swift let students: [[Any]] = [[0, "computing", 92], [1, "physics", 75], [2, "history", 83]] ``` 5. Connect to database and perform an SQL query: ```swift pool.getConnection() { connection, error in guard let connection = connection else { guard let error = error else { return print("Unknown error") } return print("Error when getting connection from pool: \(error.localizedDescription)") } let insertQuery = Insert(into: grades, rows: students) connection.execute(query: insertQuery) { insertResult in connection.execute(query: Select(from: grades)) { selectResult in guard let resultSet = selectResult.asResultSet else { return print("No result set returned from query") } resultSet.forEach() { row, error in guard let row = row else { guard let error = error else { // Processed all results return } // Handle error return } guard row.count == 3 else { // Expecting three elements per row return print("Row has wrong number of elements. Expecting 3, returned: \(row.count)") } print("Student \(row[0] ?? ""), studying \(row[1] ?? ""), scored \(row[2] ?? "")") } } } } ``` 6. If you were to run the application at this point it would execute immediately because the SwiftKuery API behaves asynchronously. In the case of this simple executable you can add a Dispatch Semaphore to force the application to wait for the asynchronous callbacks to complete: ```swift // Add the following after the existing imports: import Dispatch let waitSemaphore = DispatchSemaphore(value: 0) // Update the forEach callback to look like: resultSet.forEach() { row, error in guard let row = row else { // Processed all results waitSemaphore.signal() return } print("Student \(row[0] ?? ""), studying \(row[1] ?? ""), scored \(row[2] ?? "")") } // Add the following line at the end of the main.swift file waitSemaphore.wait() ``` 7. Save the `main.swift` file. Run `swift build` to build the executable. 8. Run the executable `.build/debug/<yourPackageName>.` This will print the `id`, `course` and `grade` for each student, which are queried from the database: ``` Student 0, studying computing, scored 92 Student 1, studying physics, scored 75 Student 2, studying history, scored 83 ``` If you go to your database with `psql school` and enter `TABLE grades;` you can see that the table has been populated with the student data. ## SQL Injection Prevention using Parameterization Unsanitized data that is used in dynamic queries is one of the most common causes of SQL injection vulnerabilities. Parameterizing queries can help to prevent SQL injection attacks. The following code is vulnerable to SQL injection if `supplied_key1` or `supplied_key2` contain untrusted data (that is, data which has not been validated): ```swift let query = Select(from: confidential) .where(confidential.key1 == sup