Perfect 数据库篇
2019-07-16 本文已影响12人
逾期的誓言
工具篇
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": "请求成功"
}
至此，本地数据库介绍完毕，下篇介绍部署到线上，成为真正的接口
