???????????????????????? Swift ???￡???????????? Swift ??????????????????????
??????????????о??????????? Vapor ????? MySQL wrapper ?????? MySQL ?????
????????????????????? MySQL ?? SQL ?????￡????????????????????????д??????????????????????????? Linux ????? Swift 3.0 ?????? MySQL ?????
???????
?????????????У???????? Ubuntu 16.04 ???? MySQL 5.7??MySQL 5.7 ?????????е?????????????????????????Ч??洢 JSON ??????????????????? JSON ????????????????????? MySQL 5.7 ????? Ubuntu 16.04 ?????? MySQL ?汾??????????? Ubuntu 16.04 ???????????????
???????????а?? Swift?? ???????? apt-get ???????????μ???? ???? ??????????2016 ?? 9 ?μ??????????? Ubuntu16.04 ????? Swift ???????? Swift.org ?????????????
?????????????
??????????????????? swift_test ?? ?????????? swift ?? ?????? swiftpass ?????????? MySQL???????????????? GRANT ALL ON swift_test.* ?????????
???????????????????
# 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
???????? Swift ??
????????????????б?????????????????
????# mkdir swift_mysql
????# swift package init --type executable
??????д Package.swift ?????
????import PackageDescription
????let package = Package(
????name: "swift_mysql"??
????dependencies:[
????.Package(url:"https://github.com/vapor/mysql"?? majorVersion:1)
????]
????)
???????????????????Щ???????????????????Щ???????????????????С?
?????? Sources ??????? utils.swift ???????????????????????
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
?????????????????????????? main.swift ???????? Vapor MySQL ??顣
?????????????
??????????′??? 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)
}
????????????????????????? mysql???????? Database(host:String?? user:String?? password:String?? database:String) ???????
??????? try mysql.execute("SELECT @@version”) ????????????????????????????????????????????? do ??????????????????????????????????????
??????????????
???????ж? MySQL ??????????? execute(_:String) ???????????????÷??????Щ???? API ?????????????? .create(table:String?? ...) ???? .insert(table:String?? … ?? execute ??????? SQL ??????? MySQL ????????
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) (baz)")
}
}
} catch {
print("Error:  (error)")
exit(-1)
}
????????????????? execute(_:String) ????????????????????? [String:Node] ??????? key ?????????????????
????Node ?????? Vapor ?е???????????????????????????????? ???? ???????????????? Node ????????? MySQL ????????????????? Swift ????????磺 let bar = result["bar"]?.int ??????????????
????????
?????????????????Щ???????????????紴?????????????? MySQL ?? DATE?? POINT ?? JSON ??????????????????? 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)
}
???????????????????????У????????? 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 ???????????? POINT ??
????// ... Point
????let location = (37.20262?? -112.98785) // latitude?? longitude
???????????????? MySQL 5.7 ???μ? JSON ?????????????????????? Jay ???????????? Swift ??? [String:Any] ???? JSON ????????????
????// ... Sample
????let sample:[String:Any] = [
????"heading":90??
????"gps":[
????"latitude":37.20262??
????"longitude":-112.98785
????]??
????"speed":82??
????"temperature":200
????]
??????????????????? Package.swift ???????? Jay ????????????? MySQL ???????????????????????
??????????????? JSON ???????? String????????? MySQL ???
????let sampleData = try Jay(formatting:.minified).dataFromJson(any:sample) // [UInt8]
????let sampleJSON = String(data:Data(sampleData)?? encoding:.utf8)
???????????????? date?? point ?? JSON ???????sample) ??? ???????????? sample ???У?
????// ... Insert
????let stmt = "INSERT INTO samples (created_at?? location?? sample) VALUES ('(created_at)'?? POINT(point)?? '(sampleJSON)')"
????try mysql.execute(stmt)
?????????????????? POINT ?????????Щ???ɡ???? (point) ????????? (37.20262?? -112.98785) ??????????????? POINT(37.20262?? -112.98785)?????? MySQL ????????????????????????????￡?
????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}')
??????????
???????棺??д??????μ????(2016-09-22)?? Vapor MySQL 1.0.0 ????? bug?????? POINT ??????????? crash ?????????ò???????????м??? do ????飬?????? select ???
?????????? Vapor MySQL ?м??????? issue??????? issue ????????????????????
????????????????У????????? MySQL 5.7 ??????? JSON ??????????????????? SELECT … WHERE ??? JSON ????????????????? samples ???? JSON ???????? sample
?????С?speed ??δ??? 80 ???????
????// ... ???
????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) Speed:(speed) Temperature:(temperature)")
????}
????}
???????????Щ?????JSON_EXTRACT ?????????? ????? JSON ????е??????????????·???????????????????????????????????У??????????? sample ?е? speed ???
??????????????????????????? for result in results ?????????? if let ????????????????????? let sample = result["sample"]?.object ???????????? MySQL ?е? JSON ???????????????????Vapor MySQL ????з?????? String???? String ??????? JSON ??????????????????????????????????????????????? sample ???????
??????μ? let ?????????? speed??temperature ?? created_at????? created_at ?? MySQL ???? DATETIME ?????????????????????????? Swift ??????? Date ??????????? .date(from:String) ????????? DateFormatter ?????????????
???????????
?????????????????д????? github ??????????????
???????κε????? swift build ???б??????п???д???????????????????????????????????????????