MySQL

Your Coronium instance contains a MySQL database that can be accessed through the cloud api. It's also possible to connect to a remote MySQL database as well.

`cloud.mysql`

Query

.query

cloud.mysql.query(connection_tbl, query_str)

Parameters

Name	Description	Default	Required
`connection_tbl`	The connection table for the database (see below).	`nil`	Y
`query_str`	The MySQL query to send to the database.	`nil`	Y

The Connection Table

You need to provide a database connection table to select the working database. The table uses the following keys:

Key	Description	Type	Default	Required
`database`	The database to work with.	`string`	`nil`	Y
`user`	The database user.	`string`	'cloud'	N
`password`	The user password	`string`	'cloudadmin'	N
`host`	The database host, can be used for external connections.	`string`	'127.0.0.1'	N
`port`	The database port number.	`number`	3306	N

Usage

local client_id = 100
local conn_tbl = { database = "clients" }
local query = cloud.sf("SELECT * FROM orders WHERE id=%d", client_id)
local ok, result = cloud.mysql.query(conn_tbl, query)
if not ok then
  cloud.log(result) --error is in the result
end

-- `result` is a table array of records.

String

.string

Return a MySQL safe string.

cloud.mysql.string( unwashed_string )

Parameters

Name	Description	Default	Requried
unwashed_string	The string to make SQL "safe".	`nil`	Y

Usage

local safe_str = cloud.mysql.string("Some possibly unsafe string.")

Using databags

A databag wraps common MySQL actions into a module format. Once you have a databag instance, you can apply the following methods to it:

Databag

.databag

Databags are prebuilt data objects for basic MySQL handling. If you prefer the query style shown above, feel free to use it instead. They both produce similar results.

cloud.mysql.databag( connection_tbl )

Parameters

Key	Description	Type	Default	Required
`database`	The database to work with.	`string`	`nil`	Y
`user`	The database user.	`string`	'cloud'	N
`password`	The user password	`string`	'cloudadmin'	N
`host`	The database host, can be used for external connections.	`string`	'127.0.0.1'	N
`port`	The database port number.	`number`	3306	N

Returns

A databag instance, connected to the specified database.

Example

local conn_tbl = { database = "leaderboard" }
local databag = cloud.mysql.databag( conn_tbl )

-- `databag` contains a databag instance.

Select

:select

databag:select( select_tbl )

Parameters

This method requires a table with the following keys (not all keys required):

Select Table Keys

Key	Description	Type	Required
`tableName`	Name of the table to operate on.	`string`	Y
`columns`	Specific columns to select	`table` array (string).	N
`where`	Any additional WHERE clause to apply.	`string`	N
`orderby`	The sorting attributes. See Orderby Table.	`table`	N
`limit`	Limit the amount of the return to this number.	`number`	N
`distinct`	Do not select duplicate column values.	`boolean`	N

Orderby Table Keys

The orderby key in the Select Table is a table array filled with table objects containing the following keys:

Key	Description	Type	Required
`colName`	Name of a column to sort on.	`string`	Y
`direction`	Which direction to sort the column	`cloud.ASCENDING` or `cloud.DESCENDING`	N

Returns

A result or nil, error, and possible errorCode.

Example

--== Select and return all records from `scores`.
local conn_tbl = { database = "leaderboard" }
local db = cloud.mysql.databag(conn_tbl)
local result, err = db:select({
  tableName = 'scores'
})

if not result then
  return cloud.error(err)
end

--== Records are Lua tables in a table array.
for _, record in ipairs(result) do
  cloud.log(record.player..' scored '..record.score)
end

return result

Example

--== Select and return refined query.
local conn_tbl = { database = "leaderboard" }
local db = cloud.mysql.databag( conn_tbl )

local result, err, errCode = db:select({
  tableName = 'scores',
  columns = {'score'},
  where = 'score > 10',
  limit = 10,
  orderby = {
    score = cloud.DESCENDING
  }
})

if not result then
  return cloud.error( err, errCode )
end

return result

Insert

:insert

databag:insert( insert_tbl )

Parameters

Key	Description	Type	Required
`insert_tbl`	The meta table for inserting the record.	`table`	Y

Insert Table

Key	Description	Type	Required
`tableName`	The MySQL table name to operate on.	`string`	Y
`columns`	An array of column names.	`table` array (string)	Y
`values`	An array of values for each specified column.	`table` array (mixed)	Y

Heads Up!

The amount of columns must match the amount of values for this action to be successful.

Returns

A result or nil, error, and possible errorCode.

Example

--== Inserting a player score
local conn_tbl = { database = "leaderboard" }
local db = cloud.mysql.databag( conn_tbl )
local result, err = db:insert({
  tableName = 'scores',
  columns = {'score', 'player'},
  values = {200, 'Sandy'}
})

if not result then
  return cloud.error(err)
end

return result

Update

:update

databag:update( update_tbl )

Parameters

Key	Description	Type	Required
`update_tbl`	The meta table for updating the record.	table	Y

Update Table Keys

Key	Description	Type	Required
`tableName`	The name of the table to operate on.	string	Y
`values`	Table of name = val pairs.	table	Y
`where`	Where the colums should be updated.	string	N

Returns

A result or nil, error, and possible errorCode.

Example

--== Update some data
local conn_tbl = { database = "leaderboard" }
local databag = cloud.mysql.databag( conn_tbl )
local result, err = databag:update({
  tableName = 'scores',
  values = {
    score = 230
  },
  where = 'player='..cloud.mysql.string(player)
})

if not result then
  return cloud.error(err)
end

return result

Delete

:delete

databag:delete( delete_tbl )

Parameters

Key	Description	Type	Required
`delete_tbl`	The meta table for deleteing the record.	table	Y

Delete Table Keys

Key	Description	Type	Required
`tableName`	The name of the table to operate on.	string	Y
`where`	A WHERE clause to limit deletions to.	string	N
`force`	Disables safety check for mising `where` clause.	boolean	N

Heads Up!

Though optional, you should always include a where key query when removing records. Without it, you could possibly delete all of the tables records.

Returns

A result or nil, error, and possible errorCode.

Example

--== Delete some data
local conn_tbl = { database = "leaderboard" }
local databag = cloud.mysql.databag( conn_tbl )
local result, err = databag:delete({
  tableName = 'scores',
  where = 'score < 10'
})

if not result then
  return cloud.error(err)
end

return result

Cleanbag

:cleanbag

Clears all internal databag query variables except the connection table. The databag is still usable.

Did You Know?

You generally don't need to call this method. It's called automatically before any new databag actions take place.

databag:cleanbag()

Parameters

None.

Returns

Nothing.

Example

--== Flush the databag
databag:cleanbag()

Administration

A browser based MySQL tool is available at https://your.coronium.instance:11000/

The default log in is:

User: cloud

Password: cloudadmin