2. Databases
Sponsored by Rampart Games
This article is sponsored by Rampart.Games, a modern container based game server provider. No slot limitations, you choose and pay for the resources that fit your needs.
In this scenario we will make a simple mod that explores the databases features of the global “dbi” object. If you haven’t yet, please review tutorial Getting ready and #1 Hello World which introduces you to setting up a local development area and the basic concepts of writing mods in the LiFx framework.
From our experience and testing, even though there are several functions available, the only two we’ve seen work without issues are Query and Select we recommend that you avoid using the other functions exposed, as they tend to put your server in an unstable state, eventually crashing.
It is also very important to close the handlers properly as to not create open connections that linger after they are no longer needed. This will cause the torque
engine to do heavy cleanups if not done properly.
The basic skeleton for setting up a select query:
dbi.select(ScriptObject,”callback””,”SQL”)
dbi
This is the global object that interacts with the database, it is the same object that the game uses to query the database
ScriptObject
Refers to the object you’ve defined as your mod, it is used to scope your queries to your object
callback
Your callback reference, it should match the function name that will handle the callback of your query.
SQL
Your SQL query this is where you will add your SQL statements that will be sent to the database
Select statements with examples
When setting up select, we do this with a callback property of the call, so that any results from your query is returned to the callback function you define.
Whenever we finish a query it is very important to eject the resultSet variable from tracking of dbi and delete the resultSet from memory. This is in order to keep performance of your server and to not overwhelm capacity of result tracking that dbi does.
graph LR;
dbi.select-->id1[(Database)]
id1[(Database)]-->DatabaseMod::result;
This example gets 1 row from characters and reads the ID column into a variable.
function DatabaseMod::select() {
dbi.select(DatabaseMod,"result", "SELECT * from `characters` LIMIT 1");
}
function DatabaseMod::result(%this, %resultSet) {
if(%resultSet.ok() && %resultSet.nextRecord())
{
%accountID = %resultSet.getFieldValue("ID");
}
dbi.remove(%resultSet);
%resultSet.delete();
}
In order to fetch multiple rows, we need to alter it a little bit. This will get all characters from the database and loop through each result as long as there is a new result.
function DatabaseMod::selectMultiple() {
dbi.select(DatabaseMod,"multipleResults", "SELECT * from `characters`");
}
function DatabaseMod::multipleResults(%this, %resultSet) {
if(%resultSet.ok())
{
while(%resultSet.nextRecord())
{
%accountID = %resultSet.getFieldValue("ID");
}
}
dbi.remove(%resultSet);
%resultSet.delete();
}
The basic skeleton for setting up a update query:
dbi.update(“SQL”)
dbi
This is the global object that interacts with the database, it is the same object that the game uses to query the database
SQL
Your SQL query this is where you will add your SQL statements that will be sent to the database
Update statements with examples
function DatabaseMod::update() {
dbi.update("UPDATE `characters` SET key='value' WHERE ID = 1");
}
Insert statements with examples
Simple insert, we use update here because we don’t need anything in return, update is purely a single statement so it works well for insertion.
function DatabaseMod::insert() {
dbi.update("INSERT INTO `table` (keys) VALUES (values)");
}
When we need to track the primary key that was given to the row after insertion, we can use select statements instead of update and pass the callback to a callback function, similar to the select above. The difference beeing we decide which column returns by the trailing SQL statemetn of RETURNING ID. Which in this case would return the primary key column “ID”.
function DatabaseMod::insertWithReturn() {
dbi.select(DatabaseMod,"insertWithReturnResult", "INSERT INTO `table` (keys) VALUES (values) RETURNING ID");
}
function DatabaseMod::insertWithReturnResult(%this, %resultSet) {
if(%resultSet.ok() && %resultSet.nextRecord())
{
%accountID = %resultSet.getFieldValue("ID");
}
dbi.remove(%resultSet);
%resultSet.delete();
}
Congratulations, that completes the mod! You should now have a foundational understanding of how to properly use databases from your server side mods.
The complete mod should look like the following
/**
* <author></author>
* <url></url>
* <credits></credits>
* <description></description>
* <license>GNU GENERAL PUBLIC LICENSE Version 3, 29 June 2007</license>
*/
// Register your mod as an object in the game engine, important for loading and unloading a package (mod)
if (!isObject(DatabaseMod))
{
new ScriptObject(DatabaseMod)
{
};
}
package DatabaseMod
{
function DatabaseMod::version() {
return "v1.0.0";
}
/**
* SELECT
**/
function DatabaseMod::select() {
dbi.select(DatabaseMod,"result", "SELECT * from `characters` LIMIT 1");
}
function DatabaseMod::result(%this, %resultSet) {
if(%resultSet.ok() && %resultSet.nextRecord())
{
%accountID = %resultSet.getFieldValue("ID");
}
dbi.remove(%resultSet);
%resultSet.delete();
}
function DatabaseMod::selectMultiple() {
dbi.select(DatabaseMod,"result", "SELECT * from `characters`");
}
function DatabaseMod::multipleResults(%this, %resultSet) {
if(%resultSet.ok())
{
while(%resultSet.nextRecord())
{
%accountID = %resultSet.getFieldValue("ID");
}
}
dbi.remove(%resultSet);
%resultSet.delete();
}
/**
* UPDATE
**/
function DatabaseMod::update() {
dbi.update("UPDATE `characters` SET key='value' WHERE ID = 1");
}
/**
* INSERT
**/
function DatabaseMod::insert() {
dbi.update("INSERT INTO `table` (keys) VALUES (values)");
}
function DatabaseMod::insert() {
dbi.update("INSERT INTO `table` (keys) VALUES (values)");
}
function DatabaseMod::insertWithReturn() {
dbi.select(DatabaseMod,"insertWithReturnResult", "INSERT INTO `table` (keys) VALUES (values) RETURNING ID");
}
function DatabaseMod::insertWithReturnResult(%this, %resultSet) {
if(%resultSet.ok() && %resultSet.nextRecord())
{
%accountID = %resultSet.getFieldValue("ID");
}
dbi.remove(%resultSet);
%resultSet.delete();
}
};
activatePackage(DatabaseMod);