MySQL

Provides a client-side api to the EZ query methods of the server-side MySQL module. For more complex data handling, you must provide an api on the server-side.

Database Required

Before you can use the MySQL module, you will need to create a MySQL database. See the Administration section for more information on connecting to your database.

select

Select multiple records from a database table.

core.mysql.select(query_tbl, listener)

Parameters

Name	Description	Type	Required
query_tbl	The query parameters for the call.	Table	Y
listener	The api listener callback function.	Function	Y

Query Table Keys

Name	Description	Type	Required
db	The database to run the query against.	String	Y
tbl	Name of the table to operate on.	String	Y
columns	Array of columns to select as strings.	Table	N
where	Any additional WHERE clause to apply.	String or Table	N
orderby	The sorting attributes. See Orderby below.	Table	N
limit	Limit the records returned. See Limit below.	Number or Table	N
distinct	Filter out duplicate column values. Default: false	Boolean	N

Orderby

The orderby key should be a table filled with column = direction pairs. The direction can be either "ASC" for ascending order or "DESC" for descending order.

Limit

To limit the rows returned, supply a number value to the limit key. To offset the limit, supply a table array of number values. For example, to return rows 6-15: limit = {5, 10}.

Event Response

On success, the result will contain a table array of records.

Example

Using the "app" database, select all records from the "users" table.

local function apiListener( evt )
  if evt.error then
    print(evt.error)
  else
    for i=1, #evt.result do
      print(evt.result[i].id)
    end
  end
end

local params = {
  db = "app",
  tbl = "users"
}

core.mysql.select(params, apiListener)

More Examples

See the server-side core.mysql.select method for more examples.

selectOne

Select and return a single record from a database table.

core.mysql.selectOne(query_tbl, listener)

Parameters

Name	Description	Type	Required
query_tbl	The query parameters for the call.	Table	Y
listener	The api listener callback function.	Function	Y

Query Table Keys

Name	Description	Type	Required
db	The database to run the query against.	String	Y
tbl	Name of the table to operate on.	String	Y
where	The WHERE clause to apply.	String or Table	Y
columns	Array of columns to select as strings.	Table	N

Event Response

On success, the result will contain a table.

Special Response

Unlike the select method, the result is returned as a single record as opposed to an array of records.

Example

local function apiListener( evt )
  if evt.error then
    print(evt.error)
  else
    print(evt.result.name)
  end
end

local params = {
  db = "app",
  tbl = "users",
  where = { user_id = 20 },
  columns = { "name" }
}

core.mysql.selectOne(params, apiListener)

selectBatch

Query multiple tables of a database and return the individual results in a keyed response.

core.mysql.selectBatch(batch_tbl, listener)

Parameters

Name	Description	Type	Required
batch_tbl	The batch parameters for the call (see below).	Table	Y
listener	The api listener callback function.	Function	Y

Batch Table Keys

Name	Description	Type	Required
db	The database to run the query against.	String	Y
batch	A table array of EZ Query select tables, excluding the db key. (see select). Each select table must also include a key property for the result response (see example below).	Table	Y

Event Response

On success, the result will be a keyed table with the results of each select query as a table array of records. If limit in any of the query entries is set to 1, then a single table with the record data is returned (not an array).

Example

local function apiListener( evt )
  if evt.error then
    print(evt.error)
  else
    local bikes = evt.result.bikes --Array of "Bike" records
    local shoes = evt.result.shoes --Array of "Shoe" records
    local store = evt.result.store --Table "Store" record
  end
end

local batch_select = {
  {
    tbl = "products",
    where = { type = "Bike" },
    key = "bikes"
  },
  {
    tbl = "products",
    where = { type = "Shoes" },
    key = "shoes"
  },
  {
    tbl = "locations",
    where = { name = "Happy Toys" },
    limit = 1, --Return as non-array record
    key = "store"
  }
}

local params = {
  db = "store",
  batch = batch_select
}

core.mysql.selectBatch(params, apiListener)

Query Errors

If any of the queries in the batch result in an error, the results key for that particular query will contain an error key. Best practice is to check for this key before accessing the results.

-- Assuming batch call as shown in the example above

local function apiListener( evt )
  if evt.error then
    print(evt.error)
  else
    if evt.result.bikes.error then
      --an error occurred on this particular query
      print(evt.result.bikes.error)
    else
      --loop over the records
      for i=1, #evt.result.bikes do
        print(evt.result.bikes[i].name)
      end
    end
  end
end

selectMerge

Select from multiple databases and tables and return the results in a keyed table.

core.mysql.selectMerge(merge_tbl, listener)

Parameters

Name	Description	Type	Required
merge_tbl	The merge parameters for the call (see below).	Table	Y
listener	The api listener callback function.	Function	Y

Merge Table Keys

Name	Description	Type	Required
merge	A table array of EZ Query select tables. (see select). Each select table should also include a key property for the result table (see example below).	Table	Y

Event Response

On success, the result will be a keyed table with the results of each select query. If limit in any of the query entries is set to 1, then a single table with the record data is returned (not an array). See Errors below.

Example

local function apiListener( evt )
  if evt.error then
    print(evt.error)
  else
    local locs = evt.result.locs

    --check for errors
    if evt.result.errors then
      for i=1, #evt.results.errors do
        local err_tbl = evt.results.error[i]
        print(err_tbl.db, err_tbl.error, err_tbl.index)
      end
    end
  end
end

local merge_dbs = {
  {
    db = "locations",
    tbl = "spots",
    key = "locs"
  },
  {
    db = "products",
    tbl = "parts",
    where = { part_id = 20 },
    key = "part"
  },
  {
    db = "stores",
    tbl = "toy",
    limit = 1, --Return as non-array record
    where = { name = "Happy Toys" },
    key = "store"
  }
}

core.mysql.selectMerge({ merge = merge_dbs }, apiListener)

Errors

If any of the database queries fail, the successful queries will still be returned in their respective key names. On failed queries, the result will also contain an errors key, containing a table array describing any errors.

Example Debug Response

result:
  errors:
  1:
    db: products
    error: Table 'products.parts' doesn't exist
    index: 2
    status: 1146
  locs:
  1:
    id: 1
    latitude: 80
    longitude: 200
    user_id: d23b8738-4d28-41ed-a967-98e83e855a38
  2:
    id: 3
    latitude: -64
    longitude: 200
    user_id: d23b8738-4d28-41ed-a967-98e83e855a39
  store:
    name = Happy Toys
    city = San Diego

selectCount

Get a record count based on passed in query.

core.mysql.selectCount(count_tbl, listener)

Parameters

Name	Description	Type	Required
count_tbl	The count parameters for the call (see below).	Table	Y
listener	The api listener callback function.	Function	Y

Count Table Keys

Name	Description	Type	Required
db	The database to run the count query against.	String	Y
tbl	Name of the table to operate with.	String	Y
where	The WHERE clause to apply.	String or Table	N
column	The column to use as the counting key. Defaults to "id".	String	N

If the where key is not included, the result is the total records in the provided table.

Example

local function apiListener( evt )
  if evt.error then
    print(evt.error)
  else
    local count = evt.result.count
    print("Total red toys:", count)
end

core.mysql.selectCount({
  db = "products",
  tbl = "toys",
  where = { color = "Red" }
}, apiListener)

insert

Insert a single record into a database table.

core.mysql.insert(query_tbl, listener)

Parameters

Name	Description	Type	Required
query_tbl	The query parameters for the call.	Table	Y
listener	The api listener callback function.	Function	Y

Query Table Keys

Name	Description	Type	Required
db	The database to run the query against.	String	Y
tbl	Name of the table to operate on.	String	Y
values	A table of column = value pairs.	Table	Y

Important

Strings in the values tables are run through the mysql.escape method. Do not double-escape values.

Event Response

On success, the result will contain the inserted record id as a number.

Example

local function apiListener( evt )
  if evt.error then
    print(evt.error)
  else
    print("insert id:", evt.result)
  end
end

local params = {
  db = "app",
  tbl = "users",
  values = {
    name = "Bobby",
    age = 34
  },
}

core.mysql.insert(params, apiListener)

insertMany

Insert records into a single table of a database in an optimized way.

core.mysql.insertMany(insert_tbl, listener)

Parameters

Name	Description	Type	Required
insert_tbl	The insert parameters table for the call (see below).	Table	Y
listener	The api listener callback function.	Function	Y

Insert Table Keys

Name	Description	Type	Required
db	The database to run the inserts against.	String	Y
tbl	Name of the table to operate on.	String	Y
records	A table array of values tables from the insert method.	Table	Y

Important

Strings in the values tables are run through the mysql.escape method. Do not double-escape values.

Event Response

On success, the result will hold an indexed table array of tables containing either an id key; with the id of the newly created record, or an error key; containing the error string.

The response table is indexed the same order as the records table that was sent to the server.

Example

local function apiListener( evt )
  if evt.error then
    print(evt.error)
  else
    for i=1, #evt.result do
      local entry = evt.result[i]

      if entry.error then
        print("error in insert entry #"..i..": "..entry.error)
      else
        print("inserted record with id: "..entry.id.." for entry #"..i)
      end
    end
  end
end

local toys = {
  {
    name = "Car",
    color = "red"
  },
  {
    name = "Teddy Bear",
    color = "brown"
  }
}

local params = {
  db = "products",
  tbl = "toys",
  records = toys
}

core.mysql.insertMany(params, apiListener)

insertBatch

Insert records into multiple tables of a database in an optimized way.

core.mysql.insertBatch(batch_tbl, listener)

Parameters

Name	Description	Type	Required
batch_tbl	The batch parameters table for the call (see below).	Table	Y
listener	The api listener callback function.	Function	Y

Batch Table Keys

Name	Description	Type	Required
db	The database to run the batch against.	String	Y
batch	A table array of tables with the tbl and values keys from the insert method.	Table	Y

Important

Strings in the values tables are run through the mysql.escape method. Do not double-escape values.

Event Response

On success, the result will hold an indexed table array of tables containing either an id key; with the id of the newly created record, or an error key; containing the error string.

The response table is indexed the same order as the batch table that was sent to the server.

Example

local function apiListener( evt )
  if evt.error then
    print(evt.error)
  else
    for i=1, #evt.result do
      local entry = evt.result[i]

      if entry.error then
        print("error in insert entry #"..i..": "..entry.error)
      else
        print("inserted record with id: "..entry.id.." for entry #"..i)
      end
    end
  end
end

local batch_insert = {
  {
    tbl = "shoes"
    values = {
      name = "Adidas",
      size = 12
    }
  },
  {
    tbl = "toys",
    values = {
      name = "Car",
      age_group = 10
    }
  },
  {
    tbl = "toys",
    values = {
      name = "Bear",
      age_group = 5,
      color = "Brown"
    }
  }
}

local params = {
  db = "products",
  batch = batch_insert
}

core.mysql.insertBatch(params, apiListener)

update

Update record(s) in a database table.

core.mysql.update(query_tbl, listener)

Parameters

Name	Description	Type	Required
query_tbl	The query parameters for the call.	Table	Y
listener	The api listener callback function.	Function	Y

Query Table Keys

Name	Description	Type	Required
db	The database to run the query against.	String	Y
tbl	The name of the table to operate on.	String	Y
values	A table of column = value pairs.	Table	Y
where	Where the columns should be updated.	String or Table	N

Important

Strings in the values tables are run through the mysql.escape method. Do not double-escape values.

Event Response

On success, the result will contain the amount of records updated as a number.

Example

local function apiListener( evt )
  if evt.error then
    print(evt.error)
  else
    print("updated:", evt.result)
  end
end

local params = {
  db = "app",
  tbl = "users",
  values = {
    name = "Paco"
  },
  where = { name = "Bobby" }
}

core.mysql.update(params, apiListener)

updateMany

Update records in a single table of a database in an optimized way.

core.mysql.updateMany(update_tbl, listener)

Parameters

Name	Description	Type	Required
update_tbl	The update parameters table for the call (see below).	Table	Y
listener	The api listener callback function.	Function	Y

Update Table Keys

Name	Description	Type	Required
db	The database to run the updates against.	String	Y
tbl	Name of the table to operate on.	String	Y
update	A table array of tables with the values and where keys from the update method.	Table	Y

Important

Strings in the values tables are run through the mysql.escape method. Do not double-escape values.

Event Response

On success, the result will hold an indexed table array of tables containing either an updated key; with the number of records updated, or an error key; containing the error string.

The response table is indexed the same order as the update table that was sent to the server.

Example

local function apiListener( evt )
  if evt.error then
    print(evt.error)
  else
    for i=1, #evt.result do
      local entry = evt.result[i]

      if entry.error then
        print("error in update entry #"..i..": "..entry.error)
      else
        print("updated "..entry.updated.." records for entry #"..i)
      end
    end
  end
end

local update_tbl = {
  {
    values = {
      name = "Nike"
    },
    where = { name = "Adidas" }
  },
  {
    values = {
      cost = "1.99"
    },
    where = { id = 3 }
  }
}

local params = {
  db = "products",
  tbl = "shoes",
  update = update_tbl
}

core.mysql.updateMany(params, apiListener)

updateBatch

Update records in multiple tables of a database in an optimized way.

core.mysql.updateBatch(batch_tbl, listener)

Parameters

Name	Description	Type	Required
batch_tbl	The batch parameters table for the call (see below).	Table	Y
listener	The api listener callback function.	Function	Y

Batch Table Keys

Name	Description	Type	Required
db	The database to run the batch against.	String	Y
batch	A table array of tables with the tbl, values and where keys from the update method.	Table	Y

Important

Strings in the values tables are run through the mysql.escape method. Do not double-escape values.

Event Response

On success, the result will hold an indexed table array of tables containing either an updated key; with the number of records updated, or an error key; containing the error string.

The response table is indexed the same order as the batch table that was sent to the server.

Example

local function apiListener( evt )
  if evt.error then
    print(evt.error)
  else
    for i=1, #evt.result do
      local entry = evt.result[i]

      if entry.error then
        print("error in update entry #"..i..": "..entry.error)
      else
        print("updated "..entry.updated.." records for entry #"..i)
      end
    end
  end
end

local batch_update = {
  {
    tbl = "shoes",
    values = {
      name = "Adidas"
    },
    where = { id = 3 }
  },
  {
    tbl = "toys",
    values = {
      name = "Raggedy Ann",
      gender = "female"
    },
    where = { name = "Raggedy Andy" }
  },
  {
    tbl = "toys",
    values = {
      company = "Tonka"
    },
    where = { id = 4 }
  }
}

local params = {
  db = "products",
  batch = batch_update
}

core.mysql.updateBatch(params, apiListener)

delete

Delete record(s) from a database table.

core.mysql.delete(query_tbl, listener)

Parameters

Name	Description	Type	Required
query_tbl	The query parameters table for the call (see below).	Table	Y
listener	The api listener callback function.	Function	Y

Query Table Keys

Name	Description	Type	Required
db	The database to run the query against.	String	Y
tbl	The name of the table to operate on.	String	Y
where	A WHERE clause to limit deletions to.	String or Table	N
force	Disables safety check for missing where key.	Boolean	N

Force Delete

To run the delete command without a where clause, you must set force to true.

Event Response

On success, the result will contain the amount of records deleted as a number.

Example

local function apiListener( evt )
  if evt.error then
    print(evt.error)
  else
    print("deleted:", evt.result)
  end
end

local params = {
  db = "app",
  tbl = "users",
  where = { id = 10 }
}

core.mysql.delete(params, apiListener)

Tip

See the server-side core.mysql.delete method for more examples.

deleteMany

Delete records from a single table in a database in an optimized way.

core.mysql.deleteMany(delete_tbl, listener)

Parameters

Name	Description	Type	Required
delete_tbl	The delete parameters table for the call (see below).	Table	Y
listener	The api listener callback function.	Function	Y

Delete Table Keys

Name	Description	Type	Required
db	The database to run the deletes against.	String	Y
tbl	Name of the table to operate on.	String	Y
delete	A table array of tables with the where key from the delete method.	Table	Y

Event Response

On success, the result will hold an indexed table array of tables containing either a deleted key; with the number of records deleted, or an error key; containing the error string.

The response table is indexed the same order as the delete table that was sent to the server.

Example

local function apiListener( evt )
  if evt.error then
    print(evt.error)
  else
    for i=1, #evt.result do
      local entry = evt.result[i]

      if entry.error then
        print("error in delete entry #"..i..": "..entry.error)
      else
        print("deleted "..entry.deleted.." records for entry #"..i)
      end
    end
  end
end

local delete_tbl = {
  {
    where = { id = 34 }
  },
  {
    where = { color = "Red" }
  }
}

local params = {
  db = "products",
  tbl = "shoes",
  delete = delete_tbl
}

core.mysql.deleteMany(params, apiListener)

deleteBatch

Delete records from multiple tables of a database in an optimized way.

core.mysql.deleteBatch(batch_tbl, listener)

Parameters

Name	Description	Type	Required
batch_tbl	The batch parameters table for the call (see below).	Table	Y
listener	The api listener callback function.	Function	Y

Batch Table Keys

Name	Description	Type	Required
db	The database to run the batch against.	String	Y
batch	A table array of tables with the tbl and where keys from the delete method.	Table	Y

Event Response

On success, the result will hold an indexed table array of tables containing either a deleted key; with the number of records deleted, or an error key; containing the error string.

The response table is indexed the same order as the batch table that was sent to the server.

Example

local function apiListener( evt )
  if evt.error then
    print(evt.error)
  else
    for i=1, #evt.result do
      local entry = evt.result[i]

      if entry.error then
        print("error in delete entry #"..i..": "..entry.error)
      else
        print("deleted "..entry.deleted.." records for entry #"..i)
      end
    end
  end
end


local batch_delete = {
  {
    tbl = "toys",
    where = { id = 2 }
  },
  {
    tbl = "shoes",
    where = { type = "running" }
  }
}

local params = {
  db = "products",
  batch = batch_delete
}

core.mysql.deleteBatch(params, apiListener)

The WHERE Key

Many of the MySQL (and other) modules use a where key to specify the "WHERE" clause for a database query. Depending on what data type and structure you provide this key, a couple different things can happen automagically.

String Based

When passing a string to the where key, you are on your own to create a valid MySQL query string. The query string is interpreted as-is.

You never include the literal "WHERE" in the where key value.

Examples

where = "`color`='Red'"

where = "`kind`='Truck' AND `color`='Red'"

where = "`kind`='Truck' OR `kind`='Car'"

Table Based

The advantage of using a table based where key is that all of the values are properly formatted to make a valid and type-safe MySQL query.

Important

String values are automatically run through mysql.escape. Do not double-escape values.

To reproduce the three string based examples above as table based:

where = { color = "Red" }

where = { kind = "Truck", color = "Red" } --AND

where = { kind = { "Truck", "Car" } } --OR

Ordered Table Queries

With "ordered" query tables you can replicate some more complex queries:

where = "`color='Red' AND `model`='Ford' AND kind='Truck' OR kind='Car'"

To reproduce the query above, put the entries in a table array (order matters):

where = {
  { color = "Red", model = "Ford" },
  { kind = { "Truck", "Car" } }
}

For an all OR query like:

where = "`color='Red' OR `model`='Ford' OR kind='Truck' OR kind='Car'"

You can use an ordered table with a single entry:

where = {
  { color = { "Red" }, model = { "Ford" }, kind = { "Truck", "Car" } }
}

At this time multiple entries in an "ordered" where table are combined using 'AND', which may cause problems with more complicated queries, so you'll need to fall back to the string method.

Table type queries also do not support conditionals, so the following cannot be replicated with a table based query:

where = "`score` > 100"

Optimized Methods

When working with multiple entries per query, try to choose an optimized query method. The reason for this is due to the fact that each Coronium Client network request to the server requires opening a new database connection per call.

By using the optimized query methods, the Coronium Core server can take advantage of using cached database connections, providing lower overhead and better performance.

Most optimized query methods also make it easier to program your application logic, by grouping results and using less calls.

The following are optimized MySQL module methods:

• selectBatch
• selectMerge
• insertMany
• insertBatch
• updateMany
• updateBatch
• deleteMany
• deleteBatch

Network Timeout

Though rare, very large workloads may cause the Corona client to throw a network timeout error before you receive a reponse from the server. In these cases you can add a timeout parameter to the query_tbl table.

The Corona default network timeout is 30 seconds. This is an optional parameter.

Example

local function apiListener( evt )
  ...
end

local params = {
  db = "app",
  tbl = "users",
  timeout = 60
}

core.mysql.select(params, apiListener)

Server-Side Timeout

To adjust the MySQL server timeout for large queries, see MySQL Timeout.