SQL (SourceMod Scripting)

This article is an introduction to using SourceMod's SQL features. It is not an introduction or tutorial about SQL or any specific SQL implementation.

SourceMod's SQL layer is formally called DBI, or the Database Interface. The interface is a generic abstraction of common SQL functions. To connect to a specific database implementation (such as MySQL, or sqLite), a SourceMod DBI "driver" must be loaded. Currently, there are drivers for MySQL and SQLite

SourceMod automatically detects and loads drivers on demand (if they exist, of course). All database natives are in scripting/include/dbi.inc. The C++ API is in public/IDBDriver.h.

Connecting

There are two ways to connect to a database. The first is through named configurations. Named configurations are preset configurations listed in configs/databases.cfg. SourceMod specifies that if SQL is being used, there should always be one configuration named "default."

An example of such a configuration looks like:

	"default"
	{
		"host"				"localhost"
		"database"			"sourcemod"
		"user"				"root"
		"pass"				""
		//"timeout"			"0"
		//"port"			"0"
	}

Connections based on named configurations can be instantiated with either SQL_Connect or SQL_DefConnect.

The other option is to use SQL_ConnectEx and manually specify all connection parameters.

Example of a typical connection:

new String:error[255]
new Handle:db = SQL_DefConnect(error, sizeof(error))
 
if (db == INVALID_HANDLE)
{
	PrintToServer("Could not connect: %s", error)
} else {
	CloseHandle(db)
}

Queries

No Results

The simplest queries are ones which do not return results -- for example, CREATE, DROP, UPDATE, INSERT, and DELETE. For such queries it is recommended to use SQL_FastQuery(). The name does not imply that the query will be faster, but rather, it is faster to write code using this function. For example, given that db is a valid database Handle:

if (!SQL_FastQuery(db, "UPDATE stats SET players = players + 1"))
{
	new String:error[255]
	SQL_GetError(db, error, sizeof(error))
	PrintToServer("Failed to query (error: %s)", error)
}

Results

If a query returns a result set and the results must be processed, you must use SQL_Query(). Unlike SQL_FastQuery(), this function returns a Handle which must be closed.

An example of a query which will produce results is:

new Handle:query = SQL_Query(db, "SELECT userid FROM vb_user WHERE username = 'BAILOPAN'")
if (query == INVALID_HANDLE)
{
	new String:error[255]
	SQL_GetError(db, error, sizeof(error))
	PrintToServer("Failed to query (error: %s)", error)
} else {
	/* Process results here!
	 */
 
	/* Free the Handle */
	CloseHandle(query)
}

Prepared Statements

Prepared statements are another method of querying. The idea behind a prepared statement is that you construct a query "template" once, and re-use it as many times as needed. Prepared statements have the following benefits:

• A good SQL implementation will be able to cache the query better if it is a prepared statement.
• You don't have to rebuild the entire query string every execution.
• You don't have to allocate a new query structure on every execution.
• Input is "always" secure (more on this later).

A prepared statement has "markers" for inputs. For example, let's consider a function that takes in a database Handle and a name, and retrieves some info from a table:

GetSomeInfo(Handle:db, const String:name[])
{
	new Handle:hQuery
	new String:query[100]
 
	/* Create enough space to make sure our string is quoted properly  */
	new buffer_len = strlen(name) * 2 + 1
	new String:new_name[buffer_len]
 
	/* Ask the SQL driver to make sure our string is safely quoted */
	SQL_QuoteString(db, name, new_name, buffer_len)
 
	/* Build the query */
	Format(query, sizeof(query), "SELECT userid FROM vb_user WHERE username = '%s'", new_name)
 
	/* Execute the query */
	if ((hQuery = SQL_Query(query)) == INVALID_HANDLE)
	{
		return 0
	}
 
	/* Get some info here
	 */
 
	CloseHandle(hQuery)
}

Observe a version with prepared statements:

new Handle:hUserStmt = INVALID_HANDLE;
GetSomeInfo(Handle:db, const String:name[])
{
	/* Check if we haven't already created the statement */
	if (hUserStmt == INVALID_HANDLE)
	{
		new String:error[255]
		hUserStmt = SQL_PrepareQuery(db, "SELECT userid FROM vb_user WHERE username = ?", error, sizeof(error));
		if (hUserStmt == INVALID_HANDLE)
		{
			return 0;
		}
	}
 
	SQL_BindParamString(hUserStmt, 0, name, false);
	if (!SQL_Execute(hUserStmt))
	{
		return 0;
	}
 
	/**
	 * Get some info here
	 */
}

The important differences:

• The input string (name) did not need to be backticked (quoted). The SQL engine automatically performs all type safety and insertion checks.
• There was no need for quotation marks around the parameter marker, ?, even though it accepted a string.
• We only needed to create the statement Handle once; after that it can live for the lifetime of the database connection.

Processing Results

Processing results is done in the same manner for both normal queries and prepared statements. The important functions are:

• SQL_GetRowCount() - Returns the number of rows.
• SQL_FetchRow() - Fetches the next row if one is available.
• SQL_Fetch[Int|String|Float]() - Fetches data from a field in the current row.

Let's consider a sample table that looks like this:

CREATE TABLE users (
	name VARCHAR(64) NOT NULL PRIMARY KEY,
	age INT UNSIGNED NOT NULL
	);

The following example has code that will print out all users matching a certain age. There is an example for both prepared statements and normal queries.

PrintResults(Handle:query)
{
	/* Even if we have just one row, you must call SQL_FetchRow() first */
	new String:name[65]
	while (SQL_FetchRow(query))
	{
		SQL_FetchString(query, 0, name, sizeof(name))
		PrintToServer("Name \"%s\" was found.", name)
	}
}
 
bool:GetByAge_Query(Handle:db, age)
{
	new String:query[100]
	Format(query, sizeof(query), "SELECT name FROM users WHERE age = %d", age)
 
	new Handle:hQuery = SQL_Query(db, query)
	if (hQuery == INVALID_HANDLE)
	{
		return false
	}
 
	PrintResults(hQuery)
 
	CloseHandle(hQuery)
 
	return true
}
 
new Handle:hAgeStmt = INVALID_HANDLE
bool:GetByAge_Statement(Handle:db, age)
{
	if (hAgeSmt == INVALID_HANDLE)
	{
		new String:error[255]
		if ((hAgeStmt = SQL_PrepareQuery(db, 
			"SELECT name FROM users WHERE age = ?", 
			error, 
			sizeof(error))) 
		     == INVALID_HANDLE)
		{
			return false
		}
	}
 
	SQL_BindParamInt(hAgeStmt, 0, age)
	if (!SQL_Execute(hAgeStmt))
	{
		return false
	}
 
	PrintResults(hAgeStmt)
 
	return true
}

Note that these examples did not close the statement Handles. These examples assume a global database instance that is only closed when the plugin is unloaded. For plugins which maintain temporary database connections, prepared statement Handles must be freed or else the database connection will never be closed.

Threading

SourceMod supports threaded SQL querying. That is, SQL operations can be completed in a separate thread from main gameplay. If your database server is remote or requires a network connection, queries can cause noticeable gameplay lag, and supporting threading is often a good idea if your queries occur in the middle of gameplay.

Threaded queries are asynchronous. That is, they are dispatched and you can only find the results through a callback. Although the callback is guaranteed to fire eventually, it may not fire in any specific given timeframe. Certain drivers may not support threading; if this is the case, an RTE will be thrown. If the threader cannot start or the threader is currently disabled, SourceMod will transparently execute the query in the main thread as a fallback.

Operations

All threaded operations use the same callback for result notification: SQLTCallback. The parameters are:

• owner - The "owner" of the operation. For a query, this is a database. For a database, this is a driver. If the owner was not found or was invalidated, INVALID_HANDLE is passed.
• hndl - The object being requested. The value is defined by the SQL operation.
• error - An error string.
• data - Custom data that can be passed via some SQL operations.

The following operations can be done via threads:

• Connection, via SQL_TConnect.
• Querying, via SQL_TQuery().
• Note: prepared statements are not yet available for the threader.

It is always safe to chain one operation from another.

Connecting

It is not necessary to make a threaded connection in order to make threaded queries. However, creating a threaded connection will not pause the game server if a connection cannot be immediately established.

The following parameters are used for the threaded connection callback:

• owner: A Handle to the driver, or INVALID_HANDLE if it could not be found.
• hndl: A Handle to the database, or INVALID_HANDLE if the connection failed.
• error: The error string, if any.
• data: Unused (0)

Example:

new Handle:hDatabase = INVALID_HANDLE;
 
StartSQL()
{
	SQL_TConnect(GotDatabase);
}
 
public GotDatabase(Handle:owner, Handle:hndl, const String:error[], any:data)
{
	if (hndl == INVALID_HANDLE)
	{
		LogError("Database failure: %s", error);
	} else {
		hDatabase = hndl;
	}
}

Querying

Threaded queries can be performed on any database Handle as long as the driver supports threading. All query results for the first result set are retrieved while in the thread. If your query returns more than one set of results (for example, CALL on MySQL with certain functions), the behaviour of the threader is undefined at this time. Note that if you want to perform both threaded and non-threaded queries on the same connection, you MUST read the "Locking" section below.

Query operations use the following callback parameters:

• owner: A Handle to the database used to query. The Handle is not the same as the Handle originally passed; however, it will test positively with SQL_IsSameConnection. The Handle can be cloned but it cannot be closed (it is closed automatically). It may be INVALID_HANDLE in the case of a serious error (for example, the driver being unloaded).
• hndl: A Handle to the query. It can be cloned, but not closed (it is closed automatically). It may be INVALID_HANDLE if there was an error.
• error: Error string, if any.
• data: Optional user-defined data passed in through SQL_TQuery().

Example, continuing from above:

CheckSteamID(userid, const String:auth[])
{
	decl String:query[255];
	Format(query, sizeof(query), "SELECT userid FROM users WHERE steamid = '%s'", auth);
	SQL_TQuery(hdatabase, T_CheckSteamID, query, userid)
}
 
public T_CheckSteamID(Handle:owner, Handle:hndl, const String:error[], any:data)
{
	new client;
 
	/* Make sure the client didn't disconnect while the thread was running */
	if ((client = GetClientOfUserId(data)) == 0)
	{
		return;
	}
 
	if (hndl == INVALID_HANDLE)
	{
		LogError("Query failed! %s", error);
		KickClient(client, "Authorization failed");
	} else if (!SQL_GetRowCount(hndl)) {
		KickClient(client, "You are not a member");
	}
}

Locking

It is possible to run both threaded and non-threaded queries on the same connection. However, without the proper precautions, you could corrupt the network stream (even if it's local), corrupt memory, or otherwise cause a crash in the SQL driver. To solve this, SourceMod has database locking. Locking is done via SQL_LockDatabase() and SQL_UnlockDatabase.

Whenever performing any of the following non-threaded operations on a database, it is absolutely necessary to enclose the entire operation with a lock:

• SQL_Query() (and SQL_FetchMoreResults pairings)
• SQL_FastQuery
• SQL_PrepareQuery
• SQL_Bind* and SQL_Execute pairings

The rule of thumb is: if your operation is going to use the database connection, it must be locked until the operation is fully completed.

Example:

bool:GetByAge_Query(Handle:db, age)
{
	new String:query[100]
	Format(query, sizeof(query), "SELECT name FROM users WHERE age = %d", age)
 
	SQL_LockDatabase(db);
	new Handle:hQuery = SQL_Query(db, query)
	if (hQuery == INVALID_HANDLE)
	{
		SQL_UnlockDatabase(db);
		return false
	}
	SQL_UnlockDatabase(db);
 
	PrintResults(hQuery)
 
	CloseHandle(hQuery)
 
	return true
}

Note that it was only necessary to lock the query; SourceMod pre-fetches the result set, and thus the network queue is clean.

Warnings

• Never call SQL_LockDatabase right before a threaded operation. You will deadlock the server and have to terminate/kill it.
• Always pair every Lock with an Unlock. Otherwise you risk a deadlock.
• If your query returns multiple result sets, for example, a procedure call on MySQL that returns results, you must lock both the query and the entire fetch operation. SourceMod is only able to fetch one result set at a time, and all result sets must be cleared before a new query is started.

Priority

Threaded SQL operations are placed in a simple priority queue. The priority levels are High, Medium, and Low. Connections always have the highest priority.

Changing the priority can be useful if you have many queries with different purposes. For example, a statistics plugin might execute 10 queries on death, and one query on join. Because the statistics might rely on the join info, the join query might need to be high priority, while the death queries can be low priority.

You should never simply assign a high priority to all of your queries simply because you want them to get done fast. Not only does it not work that way, but you may be inserting subtle problems into other plugins by being greedy.

SQLite

Introduction

SQLite is a fast local-file SQL database engine and SourceMod provides a DBI driver for it. SQLite differs from MySQL, and thus MySQL queries may not work in SQLite. The driver type for connections is sqlite.

Usage

Since SQLite is local only, most of the connection parameters can be ignored. The only connection parameter required is database, which specifies the file name of the database. Databases are created on demand if they do not already exist, and are stored in addons/sourcemod/data/sqlite. An extension of ".sq3" is automatically appended to the file name.

Additionally, you can specify sub-folders in the database name. For example, "cssdm/players" will become addons/sourcemod/data/sqlite/cssdm/players.sq3.

SQLite supports the threading layer, and requires all of the same rules as the MySQL driver (including locks on shared connections).

External Links

• SQLite Homepage
• SQLite Browser

Warning: This template (and by extension, language format) should not be used, any pages using it should be switched to Template:Languages
View this page in:  English  Russian  简体中文(Simplified Chinese)