Perfect 数据库篇

工具篇

MySQL

Mac自带MySQL 工具，

  //在终端 mysql --version 查询MySQL版本。显示
  mysql  Ver 8.0.13 for osx10.14 on x86_64 (Homebrew)

导入依赖包.package(url: "https://github.com/PerfectlySoft/Perfect-MySQL.git", from: "3.0.0")，编译报错，原因是MySQL版本过高，不兼容。

  // 解决办法：卸载MySQL，然后重新安装：
  //终端输入以下命令：

   brew uninstall mysql

   brew install mysql@5.7

   brew install mysql@5.7 && brew link mysql@5.7 --force

然在查询MySQL版本确认。

mysql  Ver 14.14 Distrib 5.7.24, for osx10.14 (x86_64) using  EditLine wrapper

注意这时MySQL@5.7就安装完成了，需要重新生成依赖关系才能使用

// 先编译
$ swift build
// 编译通过后，重新生成工程包
$ swift package generate-xcodeproj

MySQL 常用命令

// 查看版本
mysql --version
// 启动
mysql.server start
//  查看mysql是否启动：
ps -ef |grep mysql 
// 杀死进程
kill -9  (./mysqld前面第二个数字 )
// 卸载
brew remove mysql
brew cleanup
sudo rm /usr/local/mysql
sudo rm -rf /usr/local/var/mysql
sudo rm -rf /usr/local/mysql*
sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
launchctl unload -w~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
edit /etc/hostconfig and remove the line MYSQLCOM=-YES-
rm -rf ~/Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /private/var/db/receipts/*mysql*

Navicat Premium

Navicat premium是一款数据库管理工具,是一个可多重连线资料库的管理工具，它可以让你以单一程式同时连线到 MySQL、SQLite、Oracle 及 PostgreSQL 资料库，让管理不同类型的资料库更加的方便.下载地址

// 使用Navicat premium管理数据库必须先启动MySQL
$ mysql.server start
// 看见下面信息则启动成功
Starting MySQL
. SUCCESS! 

创建数据库连接 PerfectSever

屏幕快照 2019-07-16 上午11.18.56.png

MySQL启动成功了，打开连接变绿，后创建UserDataBase数据库

屏幕快照 2019-07-16 上午11.22.21.png

右键打开数据库变绿，创建userTable表, 设计表字段，保存，打开表，添加数据， 保存，command + r 刷新快捷键

屏幕快照 2019-07-16 上午11.27.27.png
屏幕快照 2019-07-16 上午11.33.12.png

这时，基于MySQL的简单数据库搭建完成,
PerfectSever连接 -> UserDataBase数据库 -> userTable表

代码篇

创建数据库工具类，连接数据库，做增删改查功能

//
//  DataBaseManager.swift
//  COpenSSL
//
//  Created by apple on 2019/7/15.
//

/* 这里演示的事单张表格查询 */

import Foundation
/**
 *  安装的是 mysql@5.7
 *  swift- build
 *  swift package generate-xcodeproj 重新生成依赖关系
 */
import PerfectMySQL

class DataBaseManager {
    /// 本地连接地址（localhost）
    fileprivate let mySql_host = "127.0.0.1"
    
    /// 连接账号
   fileprivate let mySql_user = "root"
    
    /// 连接密码,数据库未设置
   fileprivate let mySql_password = ""
    
    /// 数据库名称
    let mySql_dataBase = "UserDataBase"
    
    /// 表名
    let mySql_table = "userTable"
    
    /// 数据库对象
    fileprivate let mySql: MySQL!
    
    init() {
        mySql = MySQL.init()
        guard connectDataBase() else { return }
    }
    
    
    /// 连接数据库
    ///
    /// - Returns: 是否成功
    func connectDataBase() -> Bool {
        let connect = mySql.connect(host: mySql_host, user: mySql_user, password: mySql_password, db: mySql_dataBase)
        guard connect else {
            print("MySQL: - connect Fialer" + mySql.errorMessage())
            return false
        }
        print("MySQL: - connect Success")
        return true
    }
    
    
    /// 根据sql语句执行
    ///
    /// - Parameter sql: sql语句
    /// - Returns: 执行后结果
    func mySqlStatement(sql: String) -> (success: Bool, mysqlResult: MySQL.Results?) {
        
        /// 查找数据库
        let mySqlDataBase = mySql.selectDatabase(named: mySql_dataBase)
        
        guard  mySqlDataBase else {
            print("未能找到\(mySql_dataBase)数据库");
            return (false, nil)
        }
        
        /// 执行sql语句
        let sqlString = mySql.query(statement: sql)
        guard sqlString else {
            print("sql语句\(sql) 执行失败")
            return(false, nil)
        }
        print("sql语句执行成功，返回执行结果")
        return(true, mySql.storeResults())
    }

    
    /// 插入数据
    ///
    /// - Parameters:
    ///   - tableName: 表名
    ///   - keyValueDic: 数据
    /// - Returns: 插入结果
    func insertDataBase(tableName: String, keyValueDic: Dictionary<String, Any>) -> (success: Bool, mysqlResult: MySQL.Results?) {
        
        //避免重复插入
        let searchResoult = selectDataWithKey(tableName: tableName, keyValue: "user_id = \(keyValueDic["user_id"] ?? "")")
        guard searchResoult.mysqlResult?.numRows() == 0 else {
            print("数据库\(tableName)已存在user_id = \(keyValueDic["user_id"] ?? "")， 请勿重复插入")
            return(false, nil)
        }
        
        var keys: [String] = []
        var values: [String] = []
        for (key, value) in keyValueDic {
            if let str = value as? String {
                keys.append(key)
                values.append(str)
            }
        }
        let keysAll: String = keys.joined(separator: ",")
        let valuesAll: String = values.joined(separator: ",")
        let sql = "insert into \(tableName)(\(keysAll)) values(\(valuesAll))"
        return mySqlStatement(sql: sql)
    }
    
    
    /// 根据key 查找数据
    ///
    /// - Parameters:
    ///   - tableName: 表名
    ///   - key: 查找字段
    /// - Returns: 返回结果
    func selectDataWithKey(tableName: String, keyValue: String) -> (success: Bool, mysqlResult: MySQL.Results?) {
        let sql = "SELECT * FROM \(tableName) WHERE \(keyValue)"
        return mySqlStatement(sql: sql)
    }
    
    /// 查找真个表格
    ///
    /// - Parameter tableName: 表名
    /// - Returns: 返回结果
    func selectAllTable(tableName: String) -> (success: Bool, mysqlResult: MySQL.Results?) {
        let sql = "SELECT * FROM \(tableName)"
        return mySqlStatement(sql: sql)
    }
    
    /// 根据字段删除数据
    ///
    /// - Parameters:
    ///   - tableName: 表名
    ///   - keyValue: 删除数据
    /// - Returns: 返回数据
    func delectData(tableName: String, keyValue: String) -> (success: Bool, mysqlResult: MySQL.Results?) {
        //数据是否存在
        let searchResoult = selectDataWithKey(tableName: tableName, keyValue: keyValue)
        guard searchResoult.mysqlResult?.numRows() != 0 else {
            print("数据库\(tableName)没有 \(keyValue)数据，无法删除")
            return(false, nil)
        }
        let sql = "DELETE FROM \(tableName) WHERE \(keyValue)"
        return mySqlStatement(sql: sql)
    }
    
    
    /// 将表中数据转化成字典
    ///
    /// - Returns: 返回数据
    func searceAllResoult() -> [Dictionary<String, Any>] {
        let resoult = selectAllTable(tableName: mySql_table)
        var resoultDic = Dictionary<String, String>()
        var resoultArray = [Dictionary<String, Any>]()
        
        resoult.mysqlResult?.forEachRow(callback: { (dic) in
            resoultDic.updateValue(dic[0] ?? "", forKey: "user_name")
            resoultDic.updateValue(dic[1] ?? "", forKey: "user_age")
            resoultDic.updateValue(dic[2] ?? "", forKey: "user_sex")
            resoultDic.updateValue(dic[3] ?? "", forKey: "user_country")
            resoultDic.updateValue(dic[4] ?? "", forKey: "user_id")
            resoultArray.append(resoultDic)
        })
        return resoultArray
    }
}

调用数据库，查看返回数据

//
//  NetworkServerManager.swift
//  PerfectDemo
//
//  Created by apple on 2019/7/11.
//

// 引入头文件，需要重新编译，生成新的xcode工程包
import Foundation
import PerfectLib
import PerfectHTTP
import PerfectHTTPServer


class NetworkServerManager {
    
    /// 创建服务器
    fileprivate var Server: HTTPServer = HTTPServer.init()
    
    /// 服务器名称,通常您可以使用服务器的域名作为这个名称
    var name: String = "localhost"
    
    /// 端口：即服务器监听端口
    var port: UInt16 = 8080
    
    /// 根目录
    var documentRoots: String = "webroot"
    
    /// 创建主路由
    var routes = Routes.init()
    
    /// 创建v1版本路由
    fileprivate var VersionRoutes_1: Routes  = Routes(baseUri: "/v1")
    
   ///创建v1版本路由
    fileprivate var VersionRoutes_2: Routes = Routes(baseUri: "/v2")
    
    /// 创建api路由表
    fileprivate var ApiRoutes: Routes = Routes.init()
    
    init() {
        
        /// 给api表添加接口
        configApiRoutes(routes: ApiRoutes)
        
        /// 给v1 版本指定路由表
        VersionRoutes_1.add(ApiRoutes)
        
        /// 给v1 版本指定路由表
        VersionRoutes_2.add(ApiRoutes)
        
        ///所有版本都添加到主路由上
        routes.add(VersionRoutes_1)
        routes.add(VersionRoutes_2)
        
        /// 配置服务器
        Server.serverName = name
        Server.serverPort = port
        Server.documentRoot = documentRoots
        Server.setResponseFilters([(Filter404(),.high)])
        Server.addRoutes(routes)
        
        /// 启动服务器
        startServer()
        
    }
}

// MARK: - 配置接口
extension NetworkServerManager{
    func configApiRoutes(routes: Routes) {
        //get
        ApiRoutes.add(method: .get, uri: "/call1") { (request, response) in
            print("根据参数查数据：\(request.queryParams)" + "***********" + "接口路径：\(request.path)")
            response.setBody(string: "调用/call1接口")
            response.completed()
        }
        // get
        ApiRoutes.add(method: .get, uri: "/call2") { (request, response) in
            //测试删除数据
            let dataBase = DataBaseManager.init()
            let resoult = dataBase.delectData(tableName: "userTable", keyValue: "user_id = '6'")
            let allTable = dataBase.searceAllResoult()
            let json = self.responseJson(code: 200, message: "请求成功", data: allTable, path: request.path, params: request.postParams)
            response.setBody(string: json)
            response.completed()
        }
        // post
        ApiRoutes.add(method: .post, uri: "/call3") { (request, response) in
            //测试添加数据
            let dataBase = DataBaseManager.init()
            let resoult = dataBase.insertDataBase(tableName: "userTable", keyValueDic: ["user_name": "'李志玲'", "user_age": "17", "user_sex": "'女'", "user_country": "'中国'", "user_id": "5"])
            let allTable = dataBase.searceAllResoult()
            let json = self.responseJson(code: 200, message: "请求成功", data: allTable, path: request.path, params: request.postParams)
            response.setBody(string: json)
            response.completed()
        }
    }
}

// MARK: - 404过滤
extension NetworkServerManager{
    struct Filter404: HTTPResponseFilter {
        func filterHeaders(response: HTTPResponse, callback: (HTTPResponseFilterResult) -> ()) {
            if case .notFound = response.status {
                response.setBody(string: "文件 \(response.request.path) 不存在。")
                response.setHeader(.contentLength, value: "\(response.bodyBytes.count)")
                callback(.done)
            } else {
                callback(.continue)
            }
        }
        func filterBody(response: HTTPResponse, callback: (HTTPResponseFilterResult) -> ()) {
            callback(.continue)
        }
    }
}

// MARK: - 统一处理数据格式
extension NetworkServerManager{
    func responseJson(code: Int, message: String, data: Any?, path: String, params: [(String, String)]) -> String {
        // 参数
        var paramsDic = Dictionary<String, Any>()
        for (key, value) in params {
            paramsDic.updateValue(value, forKey: key)
        }
        
        var resoult = Dictionary<String, Any>()
        resoult.updateValue(code, forKey: "code")
        resoult.updateValue(message, forKey: "msg")
        resoult.updateValue(path, forKey: "adress")
        if data != nil {
            resoult.updateValue(data!, forKey: "data")
        }
        resoult.updateValue(paramsDic, forKey: "params")
        guard let jsonString = try? resoult.jsonEncodedString() else {
            return ""
        }
        print("\(jsonString)")
        return jsonString
    }
}

extension NetworkServerManager{
    func startServer() {
        do{
            try Server.start()
            print("服务器启动")
        }catch PerfectError.networkError(let err, let msg){
            print("网络出现错误：\(err) \(msg)")
        }catch{
            print("未知错误")
        }
    }
}

测试接口

这里是用postMan 做请求
http://localhost:8080/v2/call2,各个接口注意get，post

{
    "data": [
        {
            "user_sex": "女",
            "user_id": "1",
            "user_name": "波多野结衣",
            "user_country": "日本",
            "user_age": "19"
        },
        {
            "user_name": "苍井空",
            "user_id": "2",
            "user_age": "18",
            "user_sex": "女",
            "user_country": "日本"
        },
        {
            "user_name": "小泽玛利亚",
            "user_sex": "女",
            "user_age": "17",
            "user_country": "日本",
            "user_id": "3"
        },
        {
            "user_id": "4",
            "user_age": "16",
            "user_name": "范冰冰",
            "user_sex": "女",
            "user_country": "中国"
        },
        {
            "user_age": "17",
            "user_country": "中国",
            "user_name": "李志玲",
            "user_sex": "女",
            "user_id": "5"
        }
    ],
    "code": 200,
    "adress": "/v2/call2",
    "params": {
        "name": "zhao"
    },
    "msg": "请求成功"
}

至此，本地数据库介绍完毕，下篇介绍部署到线上，成为真正的接口