59

GitHub - IBM-Swift/Swift-Kuery-PostgreSQL: PostgreSQL plugin for Swift-Kuery fra...

 5 years ago
source link: https://github.com/IBM-Swift/Swift-Kuery-PostgreSQL
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

README.md

Kitura

APIDoc Build Status - Master macOS Linux Apache 2 Slack Status

Swift-Kuery-PostgreSQL

PostgreSQL plugin for the Swift-Kuery framework. It enables you to use Swift-Kuery to manipulate data in a PostgreSQL database.

PostgreSQL client installation

To use Swift-Kuery-PostgreSQL you must have the appropriate PostgreSQL C-language client installed.

macOS

$ brew install postgresql

Linux

$ sudo apt-get install libpq-dev

Usage

Add dependencies

Add the SwiftKueryPostgreSQL package to the dependencies within your application’s Package.swift file. Substitute "x.x.x" with the latest SwiftKueryPostgreSQL release.

.package(url: "https://github.com/IBM-Swift/Swift-Kuery-PostgreSQL.git", from: "x.x.x")

Add SwiftKueryPostgreSQL to your target's dependencies:

.target(name: "example", dependencies: ["SwiftKueryPostgreSQL"]),

Import package

import SwiftKueryPostgreSQL

Using Swift-Kuery-PostgreSQL

First create an instance of Swift-Kuery-PostgreSQL by calling:

let connection = PostgreSQLConnection(host: host, port: port, options: [ConnectionOptions]?)

Where:

  • host and port are the host and the port of PostgreSQL
  • ConnectionOptions an optional set of:
    • options - command-line options to be sent to the server
    • databaseName - the database name
    • userName - the user name
    • password - the user password
    • connectionTimeout - maximum wait for connection in seconds. Zero or not specified means wait indefinitely.

For more details refer to the PostgreSQL manual.


Alternatively, call:

let connection = PostgreSQLConnection(url: URL(string: "Postgres://\(username):\(password)@\(host):\(port)")!))

To establish a connection call:

PostgreSQLConnection.connect(onCompletion: (QueryError?) -> ())

You now have a connection that can be used to execute SQL queries created using Swift-Kuery.

Getting Started with Swift-Kuery-PostgreSQL locally

Install PostgreSQL server

Mac

brew install postgresql

Ubuntu Linux

sudo apt-get install postgresql postgresql-contrib

Make sure you have the database running. This installation should have also installed two applications we need, namely (createdb and psql) which will be used as clients to your locally running PostgreSQL.

Create a database

Let's create a database called school:

createdb school

Create the tables

Now, let's create the tables we need for this example.

Use the interative psql client to open the database we created:

$ psql school
psql (9.5.4)
Type "help" for help.

school=#

First, create the student table:

CREATE TABLE student (
 studentId BIGSERIAL PRIMARY KEY,
 name varchar(100) NOT NULL CHECK (name <> '')
);

Next, create the grades table:

CREATE TABLE grades (
  key BIGSERIAL PRIMARY KEY,
  studentId integer NOT NULL,
  course varchar(40) NOT NULL,
  grade integer
);

Populate the tables

First the students table:

INSERT INTO student VALUES (1, 'Tommy Watson');
INSERT INTO student VALUES (2, 'Fred Flintstone');

And then the grades table:

INSERT INTO grades (studentId, course, grade) VALUES (1, 'How to build your first computer', 99);
INSERT INTO grades (studentId, course, grade) VALUES (2, 'How to work at a rock quarry', 71);

Use Swift-Kuery

Now we are set to connect to our database from Swift and use Swift-Kuery to query the data into our Swift application.

Create simple Swift executable

First create a directory for our project and then initialize it.

$ mkdir swift-kuery-play
$ cd swift-kuery-play
$ swift package init --type executable
Creating executable package: swift-kuery-play
Creating Package.swift
Creating README.md
Creating .gitignore
Creating Sources/
Creating Sources/swift-kuery-play/main.swift
Creating Tests/
$

Now, add Swift-Kuery-PostgreSQL as a dependency for our project, this will automatically pull in Swift-Kuery. Edit Package.swift to contain the following, substituting "x.x.x" with the latest Kitura and Swift-Kuery-PostgreSQL releases.

// swift-tools-version:4.0
import PackageDescription

let package = Package(
    name: "swift-kuery-play",
    dependencies: [
        .package(url: "https://github.com/IBM-Swift/Kitura.git", from: "x.x.x"),
        .package(url: "https://github.com/IBM-Swift/Swift-Kuery-PostgreSQL", from: "x.x.x")
    ],
    targets: [
        .target(
            name: "swift-kuery-play",
            dependencies: ["Kitura", "SwiftKueryPostgreSQL"]),
    ]
)

Now, edit your main.swift file to contain:

import SwiftKuery
import SwiftKueryPostgreSQL
import Kitura

let router = Router()

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

let grades = Grades()

let connection = PostgreSQLConnection(host: "localhost", port: 5432, options: [.databaseName("school")])

func grades(_ callback: @escaping (String) -> Void) -> Void {
  connection.connect() { error in
    if let error = error {
      callback("Error is \(error)")
      return
    }
    else {
      // Build and execute your query here.

      // First build query
      let query = Select(grades.course, grades.grade, from: grades)

      connection.execute(query: query) { result in
        if let resultSet = result.asResultSet {
          var retString = ""

          for title in resultSet.titles {
            // The column names of the result.
            retString.append("\(title.padding(toLength: 35, withPad: " ", startingAt: 0))")
          }
          retString.append("\n")

          for row in resultSet.rows {
            for value in row {
              if let value = value {
                 let valueString = String(describing: value)
                 retString.append("\(valueString.padding(toLength: 35, withPad: " ", startingAt: 0))")
              }
            }
            retString.append("\n")
          }
          callback(retString)
        }
        else if let queryError = result.asError {
          // Something went wrong.
          callback("Something went wrong \(queryError)")
        }
      }
    }
  }
}

router.get("/") {
  request, response, next in

  grades() {
    resp in
    response.send(resp)
    next()
  }
}

Kitura.addHTTPServer(onPort: 8080, with: router)
Kitura.run()

Now build the program and run it:

$ swift build
$ .build/debug/swift-kuery-play

Now open a web page to http://localhost:8080 and you should see:

course                             grade                              
How to build your first computer   99                                 
How to work at a rock quarry       71

Now we can change our query line and see different results.

Change the line:

      let query = Select(grades.course, grades.grade, from: grades)

to

      let query = Select(grades.course, grades.grade, from: grades)
        .where(grades.grade > 80)

and we should only see grades greater than 80:

course                             grade                              
How to build your first computer   99

Another possibility is to use QueryResult.asRows that returns the result as an array of dictionaries where each dictionary represents a row of the result with the column title as the key.
Change your grades function as follows:

func grades(_ callback: @escaping (String) -> Void) -> Void {
  connection.connect() { error in
    if let error = error {
      callback("Error is \(error)")
      return
    }
    else {
      let query = Select(grades.course, grades.grade, from: grades)
      connection.execute(query: query) { result in
        if let rows = result.asRows {
            var retString = ""
            for row in rows {
                for (title, value) in row {
                    if let value = value {
                        retString.append("\(title): \(value) ")
                    }
                }
                retString.append("\n")
            }
            callback("\(retString)")
        }
        else if let queryError = result.asError {
          callback("Something went wrong \(queryError)")
        }
      }
    }
  }
}

At http://localhost:8080 you should see:

grade: 99 course: How to build your first computer
grade: 71 course: How to work at a rock quarry

API Documentation

For more information visit our API reference.

Community

We love to talk server-side Swift, and Kitura. Join our Slack to meet the team!

License

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


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK