Start

In this tutorial, we use Ubuntu 16.04 system and MySQL 5.7. MySQL 5.7 introduces a series of new features. One of them is to provide the ability to store JSON data more efficiently, and at the same time provide the ability to query the interior of JSON data. Later, if MySQL 5.7 becomes the default MySQL version on Ubuntu 16.04, we will use Ubuntu 16.04 as our operating system.

If you haven’t installed Swift yet, you can use apt-get to install it. See this article for installation instructions. At the end of September 2016, Apple also began compiling Swift images on Ubuntu 16.04. Please check out Swift.org for more information.

Create database

We named the database swift_test, the assigned user is swift, and the password is swiftpass. If you are familiar with MySQL, you should know that you need to execute GRANT ALL ON swift_test.* for authorization.
The following are the commands for this part:

# sudo mysql
...
mysql> create user swift;
Query OK, 0 rows affected (0.00 sec)

mysql> create database swift_test;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on swift_test.* to 'swift'@'localhost' identified by 'swiftpass';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

Create Swift package

Now start the formal coding, first create a package:

# mkdir swift_mysql
# swift package init --type executable

Write the Package.swift file:

import PackageDescription

let package = Package(
    name: "swift_mysql",
    dependencies:[
      .Package(url:"https://github.com/vapor/mysql", majorVersion:1)
    ]
)

The second step, we use some auxiliary tool code To generate some random data and populate it into the database. Add the utils.swift file under the Sources directory and add the following content inside:

import Glibc

class Random {
  static let initialize:Void = {
    srandom(UInt32(time(nil)))
    return ()
  }()
}

func randomString(ofLength length:Int) -> String {
  Random.initialize
  let charactersString = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"
  let charactersArray:[Character] = Array(charactersString.characters)
  
  var string = ""
  for _ in 0..<length {
    string.append(charactersArray[Int(random()) % charactersArray.count])
  }
               
  return string
}

func randomInt() -> Int {
  Random.initialize
  return Int(random() % 10000)
}

Vapor MySQL

Then comes the real code, our main.swift file uses the Vapor MySQL module.

Connect to the database

Add the following code to Sources/main.swift:

import Glibc
import MySQL

var mysql:Database
do {
  mysql = try Database(host:"localhost",
                       user:"swift",
                       password:"swiftpass",
                       database:"swift_test")
  try mysql.execute("SELECT @@version")
} catch {
  print("Unable to connect to MySQL:  \(error)")
  exit(-1)
}

The above code sets up the database and processes mysql. The constructor Database(host:String, user:String, password:String, database:String) is self-explanatory.
The statement try mysql.execute("SELECT @@version") is used to test to ensure that we are connected correctly and successfully connected to the database. If the do code block runs without errors, you can start operating the database!

Integers and Strings

All calls to MySQL will be through the execute(_:String) method. It should be noted that this method is different from some abstract API methods, such as .create(table:String, ...) or .insert(table:String, .... execute obtains the original SQL statement and passes it to the MySQL connector.

do {
  try mysql.execute("DROP TABLE IF EXISTS foo")
  try mysql.execute("CREATE TABLE foo (bar INT(4), baz VARCHAR(16))")

  for i in 1...10 {
    let int    = randomInt()
    let string = randomString(ofLength:16)
    try mysql.execute("INSERT INTO foo VALUES (\(int), '\(string)')")
  }

  // Query
  let results = try mysql.execute("SELECT * FROM foo")
  for result in results {
    if let bar = result["bar"]?.int,
       let baz = result["baz"]?.string {
      print("\(bar)\t\(baz)")
    }
  }
} catch {
  print("Error:  \(error)")
  exit(-1)
}

The query result also uses the execute(_:String) method, but the returned result is a [String:Node] dictionary. The key of the dictionary corresponds to the column name of the database.

Node 類型是 Vapor 中的數(shù)據(jù)結(jié)構(gòu)，用于轉(zhuǎn)化為不同的類型。你可以從這里獲取更多的信息。使用 Node 類型來表達(dá) MySQL 可以方便的轉(zhuǎn)換成對應(yīng)的 Swift 類型。比如：let bar = result["bar"]?.int 給我們一個(gè)整型。

繼續(xù)

接著我們來看一些更復(fù)雜的例子，比如創(chuàng)建一個(gè)表，包含了 MySQL 的 DATE， POINT 和 JSON 數(shù)據(jù)類型。我們的表名叫 samples。

do {
  try mysql.execute("DROP TABLE IF EXISTS samples")
  try mysql.execute("CREATE TABLE samples (id INT PRIMARY KEY AUTO_INCREMENT, created_at DATETIME, location POINT, reading JSON)")

  // ... Date
  // ... Point
  // ... Sample
  // ... Insert
  // ... Query
} catch {
  print("Error:  \(error)")
  exit(-1)
}

要插入一個(gè)日期到數(shù)據(jù)庫中，需要正確的 SQL 語句：

// ... Date
let now              = Date()
let formatter        = DateFormatter()
formatter.dateFormat = "yyyy-MM-dd HH:mm:ss" // MySQL will accept this format
let created_at       = formatter.string(from:date)

接下來使用 Swift 元組來創(chuàng)建一個(gè) POINT：

// ... Point
let location = (37.20262, -112.98785) // latitude, longitude

最后，我們來處理 MySQL 5.7 中新的 JSON 數(shù)據(jù)類型，此外我們使用了 Jay 包來快速將一個(gè) Swift 字典 [String:Any] 轉(zhuǎn)換為 JSON 格式的字符串。

// ... Sample
  let sample:[String:Any] = [
    "heading":90,
    "gps":[
      "latitude":37.20262,
      "longitude":-112.98785
    ],
    "speed":82,
    "temperature":200
  ]

提示：你不需要顯式在 Package.swift 中聲明對 Jay 的依賴，因?yàn)樵?MySQL 的包中已經(jīng)包含了這個(gè)依賴。接下來我們把 JSON 數(shù)據(jù)轉(zhuǎn)換為 String，用來拼湊 MySQL 語句。

let sampleData = try Jay(formatting:.minified).dataFromJson(any:sample) // [UInt8]
let sampleJSON = String(data:Data(sampleData), encoding:.utf8)

這樣我們就有了 date， point 和 JSON 字符串（sample) 了， 現(xiàn)在添加數(shù)據(jù)到 sample 表中：

// ... Insert
let stmt = "INSERT INTO samples (created_at, location, sample) VALUES ('\(created_at)', POINT\(point), '\(sampleJSON)')"
try mysql.execute(stmt)

請注意我們在處理 POINT 時(shí)候，使用了一些技巧。在對 (point) 展開為字符串 (37.20262, -112.98785) 后，完整的字符串是 POINT(37.20262, -112.98785)，這是 MySQL 所需要的數(shù)據(jù)，整個(gè)語句的字符串如下：

INSERT INTO samples (created_at, location, sample) VALUES ('2016-09-21 22:28:44', POINT(37.202620000000003, -112.98784999999999), '{"gps":{"latitude":37.20262,"longitude":-112.98785},"heading":90,"speed":82,"temperature":200}')

獲取結(jié)果

警告：在寫這篇文章的時(shí)候(2016-09-22), Vapor MySQL 1.0.0 有一個(gè) bug：在讀取 POINT 數(shù)據(jù)類型時(shí)會(huì) crash 掉，所以不得不在下面代碼中加入 do 代碼塊，然后不使用 select 語句。我們在 Vapor MySQL 中記錄了這個(gè) issue，等這個(gè) issue 修復(fù)以后，我們將更新文章。

在下面的例子中，我們將使用 MySQL 5.7 中引入對 JSON 數(shù)據(jù)內(nèi)部的查詢特性，使用 SELECT … WHERE 查詢 JSON 數(shù)據(jù)。在這里查詢的是 samples 表中 JSON 數(shù)據(jù)類型 sample中、speed 字段大于 80 的數(shù)據(jù)。

// ... 查詢
  let results = try mysql.execute("SELECT created_at,sample FROM samples where JSON_EXTRACT(sample, '$.speed') > 80") 
  for result in results {
    if let sample      = result["sample"]?.object,
       let speed       = sample["speed"]?.int,
       let temperature = sample["temperature"]?.int,
       let created_at  = result["created_at"]?.string {
      print("Time:\(created_at)\tSpeed:\(speed)\tTemperature:\(temperature)")
    }
  }

這里做一些說明。JSON_EXTRACT 函數(shù)是用來 返回從 JSON 文檔中的數(shù)據(jù)，根據(jù)傳入的路徑參數(shù)選擇文檔中滿足條件的數(shù)據(jù)。在本例中，我們解包了列 sample 中的 speed 值。

為了循環(huán)處理結(jié)果，我們使用了 for result in results 語句，接著使用 if let 語句驗(yàn)證結(jié)果數(shù)據(jù)。首先使用?let sample = result["sample"]?.object?獲取一個(gè)字典，對應(yīng) MySQL 中的 JSON 文檔，這是一句關(guān)鍵的代碼！Vapor MySQL 庫并沒有返回一個(gè) String，而 String 還需進(jìn)行 JSON 的解析。這個(gè)解析工作庫已經(jīng)幫你做了，所以你可以直接使用 sample 字典啦。

剩下的 let 語句給了我們 speed，temperature 和 created_at。注意 created_at 在 MySQL 中是 DATETIME 類型，我們讀取它為字符串。為了在 Swift 中轉(zhuǎn)換成 Date 類型，需要使用 .date(from:String) 方法加一個(gè) DateFormatter 來做類型轉(zhuǎn)換。

獲取代碼

如果你想直接運(yùn)行代碼，請到?github?上下載我們的代碼。
在任何地方使用?swift build?進(jìn)行編譯，運(yùn)行可執(zhí)行代碼，不要忘了你還需要擁有一個(gè)數(shù)據(jù)庫，用戶名并且授權(quán)通過。