MySQL
The MySQL connector provides a wrapper around MySQL, allowing interaction between your Perfect Applications and MySQL databases.
System Requirements
macOS
Requires the use of Homebrew’s MySQL.
brew install mysql
If you need Homebrew, you can install it with:
/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
Unfortunately, at this point in time you will need to edit the mysqlclient.pc file located here:
/usr/local/lib/pkgconfig/mysqlclient.pc
Remove the occurrence of "-fno-omit-frame-pointer". This file is read-only by default so you will need to change its permissions first.
Linux
Ensure that you have installed libmysqlclient-dev for MySQL version 5.6 or greater:
sudo apt-get install libmysqlclient-dev
Please note that Ubuntu 14 defaults to including a version of MySQL client which will not compile with this package. Install MySQL client version 5.6 or greater manually.
Setup
Add the "Perfect-MySQL" project as a dependency in your Package.swift file:
.Package(url:"https://github.com/PerfectlySoft/Perfect-MySQL.git", majorVersion: 3)
Import
First and foremost, in any of the source files you intend to use with MySQL, import the required module with:
import PerfectMySQL
Quick Start
Access the Database
In order to access the database, set up your credentials:
let testHost = "127.0.0.1" let testUser = "test" let testPassword = "password" let testDB = "schema" //Obviously change these details to a database and user you have already defined
There are two common ways to connect to MySQL. First, you can omit the schema, so that you can use a separate selector. This is handy if you have multiple schemas that your program can choose:
func fetchData() {
let mysql = MySQL() // Create an instance of MySQL to work with
let connected = mysql.connect(host: testHost, user: testUser, password: testPassword)
guard connected else {
// verify we connected successfully
print(mysql.errorMessage())
return
}
defer {
mysql.close() //This defer block makes sure we terminate the connection once finished, regardless of the result
}
//Choose the database to work with
guard mysql.selectDatabase(named: testDB) else {
Log.info(message: "Failure: \(mysql.errorCode()) \(dataMysql.errorMessage())")
return
}
}
Alternatively, you can pass the database you would like to access into the connection and skip selection:
func fetchData() {
let mysql = MySQL() // Create an instance of MySQL to work with
let connected = mysql.connect(host: testHost, user: testUser, password: testPassword, db: testDB)
guard connected else {
// verify we connected successfully
print(mysql.errorMessage())
return
}
defer {
mysql.close() //This defer block makes sure we terminate the connection once finished, regardless of the result
}
}
Create Tables
Choosing the database is great, but it is much more helpful to run queries, such as adding tables programmatically. Expanding on our connection example above, it is relatively simple to add a query:
func setupMySQLDB() {
let mysql = MySQL() // Create an instance of MySQL to work with
let connected = mysql.connect(host: testHost, user: testUser, password: testPassword, db: testDB)
guard connected else {
// verify we connected successfully
print(mysql.errorMessage())
return
}
defer {
mysql.close() //This defer block makes sure we terminate the connection once finished, regardless of the result
}
//Run Query to Add Tables
let sql = """
CREATE TABLE IF NOT EXISTS ticket (
id VARCHAR(64) PRIMARY KEY NOT NULL,
expiration INTEGER)
"""
guard mysql.query(statement: sql) else {
// verify the table was created successfully
print(mysql.errorMessage())
return
}
}
Run Queries
Getting data from your schema is essential, and relatively easy to do. After running a query, save your data and then act on it. In the example below, we’re assuming we have a table called options with a row id, an option name (text) and an option value (text):
func fetchData() {
let mysql = MySQL() // Create an instance of MySQL to work with
let connected = mysql.connect(host: testHost, user: testUser, password: testPassword, db: testDB)
guard connected else {
// verify we connected successfully
print(mysql.errorMessage())
return
}
defer {
mysql.close() //This defer block makes sure we terminate the connection once finished, regardless of the result
}
// Run the Query (for example all rows in an options table)
let querySuccess = mysql.query(statement: "SELECT option_name, option_value FROM options")
// make sure the query worked
guard querySuccess else {
return
}
// Save the results to use during this session
let results = mysql.storeResults()! //We can implicitly unwrap because of the guard on the querySuccess. You’re welcome to use an if-let here if you like.
var ary = [[String:Any]]() //Create an array of dictionaries to store our results in for use
results.forEachRow { row in
let optionName = getRowString(forRow: row[0]) //Store our Option Name, which would be the first item in the row, and therefore row[0].
let optionValue = getRowString(forRow: row[1]) //Store our Option Value
ary.append("\(optionName)":optionValue]) //store our options
}
}
MySQL Server API
The MySQL server API provides you with a set of tools to connect to and work with MySQL server instances. This includes basic connections, disconnections, querying the instance for databases/tables, and running queries (which is actually a light wrapper for the full Statements API). Results returned, however, are handled and manipulated with the Results API. Statements also have a Statements API that lets you work with statements in much more detail than simply running queries though the main MySQL class.
init
public init()
Creates an instance of the MySQL class that allows you to interact with MySQL databases.
NOTE for ⚠️Character Encoding⚠️
If your dataset contains non-ascii characters, please set this option for proper encoding:
setOption(.MYSQL_SET_CHARSET_NAME, "utf8")
close
public func close()
Closes a connection to MySQL. Most commonly used as a defer after guarding a connection, making sure that your session will close no matter what the outcome.
ping
MySQL connection will go away when idle timeout. The ping() function
can confirm the connectivity and also reconnect if need.
guard mysql.ping() else {
// connection lost
}
clientInfo
public static func clientInfo() -> String
This will give you a string of the MySQL client library version, e.g. "5.7.x" or similar depending on your MySQL installation.
errorCode & errorMessage
public func errorCode() -> UInt32
public func errorMessage() -> String
Error codes and messages are useful when debugging. These functions retrieve, display, and make use of those in Swift. You can learn more about what those mean here. This is especially useful after connecting or running queries. Example:
let mysql = MySQL()
let connected = mysql.connect(host: dbHost, user: dbUser, password: dbPassword, db: dbName)
guard connected else {
// verify we connected successfully
print(mysql.errorMessage())
return
}
In this case, the console output would print any error messages that came up during a connection failure.
serverVersion
public func serverVersion() -> Int
Returns an integer representation of the MySQL server’s version.
connect
public func connect(host hst: String? = nil, user: String? = nil, password: String? = nil, db: String? = nil, port: UInt32 = 0, socket: String? = nil, flag: UInt = 0) -> Bool
Opens a connection to the MySQL database when supplied with the bare minimum credentials for your server (usually a host, user, and password). As an option, you can specify the port, database, or socket. Specifying the schema is not required, as you can use the selectDatabase() method after the connection has been made.
selectDatabase
public func selectDatabase(named namd: String) -> Bool
Selects a database from the active MySQL connection.
listTables
public func listTables(wildcard wild: String? = nil) -> [String]
Returns an array of strings representing the different tables available on the selected database.
listDatabases
public func listDatabases(wildcard wild: String? = nil) -> [String]
Returns an array of strings representing the databases available on the MySQL server currently connected.
commit
public func commit() -> Bool
Commits the transaction.
rollback
public func rollback() -> Bool
Rolls back the transaction.
moreResults
public func moreResults() -> Bool
Checks mysql_more_results to see if any more results exist.
nextResult
public func nextResult() -> Int
Returns the next result in a multi-result execution. Most commonly used in a while loop to produce an effect similar to running forEachRow(). For example:
var results = [[String?]]()
while let row = results?.next() {
results.append(row)
}
query
public func query(statement stmt: String) -> Bool
Runs an SQL Query given as a string.
storeResults
public func storeResults() -> MySQL.Results?
This retrieves a complete result set from the server and stores it on the client. This should be run after your query and before a function like forEachRow() or next() so that you can ensure that you iterate through all results.
setOption
public func setOption(_ option: MySQLOpt) -> Bool public func setOption(_ option: MySQLOpt, _ b: Bool) -> Bool public func setOption(_ option: MySQLOpt, _ i: Int) -> Bool public func setOption(_ option: MySQLOpt, _ s: String) -> Bool
Sets the options for connecting and returns a Boolean for success or failure. Requires a MySQLOpt and has several versions to support setting options that require Booleans, integers, or strings as values.
MySQLOpt values that are available to use are defined by the following enumeration:
public enum MySQLOpt {
case MYSQL_OPT_CONNECT_TIMEOUT, MYSQL_OPT_COMPRESS, MYSQL_OPT_NAMED_PIPE,
MYSQL_INIT_COMMAND, MYSQL_READ_DEFAULT_FILE, MYSQL_READ_DEFAULT_GROUP,
MYSQL_SET_CHARSET_DIR, MYSQL_SET_CHARSET_NAME, MYSQL_OPT_LOCAL_INFILE,
MYSQL_OPT_PROTOCOL, MYSQL_SHARED_MEMORY_BASE_NAME, MYSQL_OPT_READ_TIMEOUT,
MYSQL_OPT_WRITE_TIMEOUT, MYSQL_OPT_USE_RESULT,
MYSQL_OPT_USE_REMOTE_CONNECTION, MYSQL_OPT_USE_EMBEDDED_CONNECTION,
MYSQL_OPT_GUESS_CONNECTION, MYSQL_SET_CLIENT_IP, MYSQL_SECURE_AUTH,
MYSQL_REPORT_DATA_TRUNCATION, MYSQL_OPT_RECONNECT,
MYSQL_OPT_SSL_VERIFY_SERVER_CERT, MYSQL_PLUGIN_DIR, MYSQL_DEFAULT_AUTH,
MYSQL_OPT_BIND,
MYSQL_OPT_SSL_KEY, MYSQL_OPT_SSL_CERT,
MYSQL_OPT_SSL_CA, MYSQL_OPT_SSL_CAPATH, MYSQL_OPT_SSL_CIPHER,
MYSQL_OPT_SSL_CRL, MYSQL_OPT_SSL_CRLPATH,
MYSQL_OPT_CONNECT_ATTR_RESET, MYSQL_OPT_CONNECT_ATTR_ADD,
MYSQL_OPT_CONNECT_ATTR_DELETE,
MYSQL_SERVER_PUBLIC_KEY,
MYSQL_ENABLE_CLEARTEXT_PLUGIN,
MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS
}
MySQL Results API
The results API set provides a set of tools for working with result sets that are obtained from running queries.
close
public func close()
Closes the result set by releasing the results. Make sure you have a close function that is always executed after each session with a connection, otherwise you are going to have some memory problems. This is most commonly found in the defer block, just like in the examples at the top of this page.
dataSeek
public func dataSeek(_ offset: UInt)
Moves to an arbitrary row number in the results given an unsigned integer as an offset.
numRows
public func numRows() -> Int
Lets you know how many rows are in a result set.
numFields
public func numFields() -> Int
Similar to numRows, but returns the number of columns in a result set instead. Very useful for Select * type queries where you may need to know how many columns are in the results.
next
public func next() -> Element?
Returns the next row in the result set as long as one exists.
forEachRow
public func forEachRow(callback: (Element) -> ())
Iterates through all rows in query results. Most useful for appending elements to an array or dictionary, just like we did in the quick start guide.
MySQL Statements API
init
public init(_ mysql: MySQL)
Initializes the MySQL statement structure. This is very commonly used by other API functions to create a statement structure after you’ve passed in a string.
close
public func close()
This frees the MySQL statement structure pointer. Use it or lose valuable memory to the underlying MySQL C API. Most commonly in a defer block, just like we used in quick start.
reset
public func reset()
Resets the statement buffers that are in the server. This doesn’t affect bindings or stored result sets. Learn more about this feature here.
clearBinds
func clearBinds()
Clears the current bindings.
freeResult
public func freeResult(
Releases memory tied up in with the result set produced by execution of a prepared statement. Also closes a cursor if one is open for the statement.
errorCode & errorMessage
public func errorCode() -> UInt32
public func errorMessage() -> String
Error codes and messages are useful when debugging. These functions retrieve, display, and make use of both in Swift. You can learn more about what those mean here. This is especially useful after connecting or running queries. Example:
let mysql = MySQL()
let connected = mysql.connect(host: dbHost, user: dbUser, password: dbPassword, db: dbName)
guard connected else {
// verify we connected successfully
print(mysql.errorMessage())
return
}
In this case, the console output would print any error messages that came up during a connection failure.
prepare
public func prepare(statement query: String) -> Bool
Prepares a SQL statement for execution. More commonly called by other functions in the API, but public if you need it.
execute
public func execute() -> Bool
Executes a prepared statement.
results
public func results() -> MySQLStmt.Results
Returns current results from the server.
fetch
public func fetch() -> FetchResult
Fetches the next row of data from the result set.
numRows
public func numRows() -> UInt
Returns the row count in a buffered statement result set.
affectedRows
public func affectedRows() -> UInt
Returns the number of rows that were changed, deleted, or inserted by a prepared statement or an UPDATE, DELETE, or INSERT statement that was executed.
insertId
public func insertId() -> UInt
Returns the row id number for the last row inserted by a prepared statement, as long as id was an auto-increment enabled column.
fieldCount
public func fieldCount() -> UInt
Returns the number of columns in the results for the most recently executed statement.
nextResult
public func nextResult() -> Int
Returns the next result in a multi-result execution.
dataSeek
public func dataSeek(offset: Int)
Given an offset, it will seek to an arbitrary row in a statement result set.
paramCount
public func paramCount() -> Int
Returns the number of parameters in a prepared statement.
bindParam
public func bindParam() func bindParam(_ s: String, type: enum_field_types) public func bindParam(_ d: Double) public func bindParam(_ i: Int) public func bindParam(_ i: UInt64) public func bindParam(_ s: String) public func bindParam(_ b: UnsafePointer<Int8>, length: Int) public func bindParam(_ b: [UInt8])
Variations above on the bindParam() allow binding to statement parameters with different types. If no arguments are passed, it creates a null binding.
