Difference between revisions of "Zh cn:SQL (SourceMod Scripting)"

From AlliedModders Wiki
Jump to: navigation, search
Line 1: Line 1:
 
{{Languages|SQL (SourceMod_Scripting)}}
 
{{Languages|SQL (SourceMod_Scripting)}}
本文将介绍如何使用sourceMod的SQL特性。它并不是关于SQL或任何特定SQL实现的介绍或教程。
+
本文将介绍如何使用SourceMod的SQL特性。请注意,这并不是关于SQL或任何特定SQL实现的介绍或教程。
  
 
SourceMod的SQL层的正式称呼是''DBI'',全写是'''D'''ata'''b'''ase '''I'''nterface,中文称呼为数据库接口,这个接口是通用SQL方法的一般抽象。要连接到特定数据库(譬如MySQL和sqLite),则必须加载对应的SourceMod DBI驱动。当前SourceMod已内置有MySQL和SQLite的驱动。
 
SourceMod的SQL层的正式称呼是''DBI'',全写是'''D'''ata'''b'''ase '''I'''nterface,中文称呼为数据库接口,这个接口是通用SQL方法的一般抽象。要连接到特定数据库(譬如MySQL和sqLite),则必须加载对应的SourceMod DBI驱动。当前SourceMod已内置有MySQL和SQLite的驱动。
Line 20: Line 20:
 
}</pre>
 
}</pre>
  
使用<tt>SQL_Connect</tt>或<tt>SQL_DefConnect</tt>来实例化基于命名配置的数据库连接。
+
请使用<tt>SQL_Connect</tt>或<tt>SQL_DefConnect</tt>来实例化基于命名配置的数据库连接。
  
 
另一种方式是使用<tt>SQL_ConnectCustom</tt>,并通过传递包含这些参数的键值对句柄对象手动指定所有连接参数。
 
另一种方式是使用<tt>SQL_ConnectCustom</tt>,并通过传递包含这些参数的键值对句柄对象手动指定所有连接参数。
Line 62: Line 62:
 
else  
 
else  
 
{
 
{
/* Process results here!
+
/* 在这里进行结果的处理!
 
*/
 
*/
  
/* Free the Handle */
+
/* 释放句柄对象 */
 
delete query;
 
delete query;
 
}</pawn>
 
}</pawn>
Line 82: Line 82:
 
char query[100];
 
char query[100];
  
/* Create enough space to make sure our string is quoted properly */
+
/* 创建足够的空间来保证我们的查询字符串能被正确引用 */
 
int buffer_len = strlen(name) * 2 + 1;
 
int buffer_len = strlen(name) * 2 + 1;
 
char[] new_name = new char[buffer_len];
 
char[] new_name = new char[buffer_len];
  
/* Ask the SQL driver to make sure our string is safely quoted */
+
/* 要求SQL驱动程序确保我们的字符串被安全引用 */
 
SQL_EscapeString(db, name, new_name, buffer_len);
 
SQL_EscapeString(db, name, new_name, buffer_len);
  
/* Build the query */
+
/* 生成查询语句串 */
 
Format(query, sizeof(query), "SELECT userid FROM vb_user WHERE username = '%s'", new_name);
 
Format(query, sizeof(query), "SELECT userid FROM vb_user WHERE username = '%s'", new_name);
 
 
/* Execute the query */
+
/* 执行查询语句 */
 
if ((hQuery = SQL_Query(query)) == null)
 
if ((hQuery = SQL_Query(query)) == null)
 
{
 
{
Line 98: Line 98:
 
}
 
}
  
/* Get some info here
+
/* 获取一些信息
 
*/
 
*/
  
Line 104: Line 104:
 
}</pawn>
 
}</pawn>
  
Observe a version with prepared statements:
+
让我们来看看使用预编译语句的版本:
 
<pawn>DBStatement hUserStmt = null;
 
<pawn>DBStatement hUserStmt = null;
 
int GetSomeInfo(Database db, const char[] name)
 
int GetSomeInfo(Database db, const char[] name)
 
{
 
{
/* Check if we haven't already created the statement */
+
/* 检查一下我们是不是已经创建好了语句 */
 
if (hUserStmt == null)
 
if (hUserStmt == null)
 
{
 
{
Line 126: Line 126:
  
 
/**
 
/**
* Get some info here
+
* 在这处理信息
 
*/
 
*/
 
}</pawn>
 
}</pawn>
  
The important differences:
+
重要区别:
*The input string (<tt>name</tt>) did not need to be backticked (quoted).  The SQL engine automatically performs all type safety and insertion checks.
+
*输入字符串(<tt>name</tt>)不需要被反引号(引号)包起来。数据库引擎会自动处理类型安全和插入检查。
*There was no need for quotation marks around the parameter marker, <tt>?</tt>, even though it accepted a string.
+
*参数标记<tt>?</tt>周围不需要引号,即使它接受了一个字符串。
*We only needed to create the statement Handle once; after that it can live for the lifetime of the database connection.
+
*我们仅仅只需要创建语句句柄一次,在那之后,于该数据库连接的整个生命周期它都将存在。
  
 
=处理查询结果=
 
=处理查询结果=
Processing results is done in the same manner for both normal queries and prepared statements.  The important functions are:
+
对于普通查询和预编译语句查询,它们处理结果的方式相同。 相关的重要函数如下:
*<tt>SQL_GetRowCount()</tt> - Returns the number of rows.
+
*<tt>SQL_GetRowCount()</tt> - 返回查询结果的行数。
*<tt>SQL_FetchRow()</tt> - Fetches the next row if one is available.
+
*<tt>SQL_FetchRow()</tt> - 拉取下一行,如果有的话。
*<tt>SQL_Fetch[Int|String|Float]()</tt> - Fetches data from a field in the current row.
+
*<tt>SQL_Fetch[Int|String|Float]()</tt> - 从当前行拉取特定字段的数据。
  
Let's consider a sample table that looks like this:
+
我们假设有如下结构的一个简单表:(译注:这是一段建表语句)
 
<pawn>CREATE TABLE users (
 
<pawn>CREATE TABLE users (
 
name VARCHAR(64) NOT NULL PRIMARY KEY,
 
name VARCHAR(64) NOT NULL PRIMARY KEY,
Line 147: Line 147:
 
);</pawn>
 
);</pawn>
  
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.
+
下面的示例包含的代码将打印出与某个年龄段匹配的所有用户。常规查询和预编译语句各有一个例子。
 
<pawn>void PrintResults(Handle query)
 
<pawn>void PrintResults(Handle query)
 
{
 
{
/* Even if we have just one row, you must call SQL_FetchRow() first */
+
/* 即便结果只有一行,你也应该先使用SQL_FetchRow() */
 
char name[MAX_NAME_LENGTH];
 
char name[MAX_NAME_LENGTH];
 
while (SQL_FetchRow(query))
 
while (SQL_FetchRow(query))
Line 204: Line 204:
 
}</pawn>
 
}</pawn>
  
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.
+
注意,这些示例中都没有关闭预编译语句句柄。因为这些示例假定有一个全局的数据库实例,只有在卸载插件时才关闭它。对于那些自己维护临时数据库连接的插件,就必须释放预编译语句句柄,否则数据库连接将永远不会关闭。
  
=线程=
+
=多线程=
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.
+
<b>译注:下面的内容比较复杂,可以不用看</b>
 +
SourceMod支持多线程SQL查询。这意味着,数据库操作可以在游戏主线程之外的线程中完成。如果你使用远程的数据库服务器或者需要一个网络连接,查询可能会导致明显延迟,所以如果你的查询发生在游戏进行期间,那么支持线程通常是一个好主意。
  
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.
+
多线程查询是''异步的''。这意味着,他们会在回调函数里触发并返回结果。尽管回调函数最终一定能触发,但是你并不能让它在指定时刻触发。某些驱动可能不支持多线程,如果是这种情况,一个运行时错误会被抛出。如果线程器不能被启动或者被禁用,SourceMod会以回调函数的形式在主线程里执行查询。
  
 
==操作==
 
==操作==
All threaded operations (except connecting) use the same callback for result notification: <tt>SQLQueryCallback</tt>.  The parameters are:
+
所有被线程化的操作(除了数据库连接)都使用一样的回调<tt>SQLQueryCallback</tt>来接受结果,参数如下:
*<tt>db</tt> - The cloned database handle.  If the db handle was not found or was invalidated, <tt>null</tt> is passed.
+
*<tt>db</tt> - 数据库句柄对象的拷贝。如果db句柄找不到或者是无效的,会把 <tt>null</tt> 传过去。
*<tt>results</tt> - Result object, or null on failure.
+
*<tt>results</tt> - 结果对象,失败时为null。
*<tt>error</tt> - An error string.
+
*<tt>error</tt> - 包含错误内容的字符串。
*<tt>data</tt> - Custom data that can be passed via some SQL operations.
+
*<tt>data</tt> - 通过SQL操作传递的自定义数据。
  
The following operations can be done via threads:
+
多线程支持下面的操作:
*<b>Connection</b>, via <tt>SQL_TConnect</tt>.
+
*<b>数据库连接</b>,使用函数 <tt>SQL_TConnect</tt>
*<b>Querying</b>, via <tt>SQL_TQuery()</tt>.
+
*<b>数据库查询</b>,使用函数 <tt>SQL_TQuery()</tt>
*''Note: prepared statements are not yet available for the threader.''
+
*''注意: 预编译语句目前不支持多线程。''
  
It is always safe to chain one operation from another.
+
数据库操作的连续调用是安全的。
  
 
===连接===
 
===连接===
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.
+
没必要为了线程化的查询而使用线程化的数据据连接。但是,使用线程化的数据库连接就不会因为建立连接时的延时造成服务器卡顿。
Connecting however uses a different callback: <tt>SQLConnectCallback</tt>.
+
线程化的数据库连接使用这个回调: <tt>SQLConnectCallback</tt>
  
The following parameters are used for the threaded connection callback:
+
回调的参数如下:
*<tt>db</tt>: Handle to the database connection, or <tt>null</tt> if it could not be found.
+
*<tt>db</tt>: 数据库连接的句柄对象,如果无法连接,将会返回<tt>null</tt>
*<tt>error</tt>: The error string, if any.
+
*<tt>error</tt>: 错误的字符串,如果有
*<tt>data</tt>: Unused (0)
+
*<tt>data</tt>: 未使用
  
Example:  
+
例子:  
 
<pawn>Database hDatabase = null;
 
<pawn>Database hDatabase = null;
  
Line 255: Line 256:
  
 
===查询===
 
===查询===
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, <tt>CALL</tt> on MySQL with certain functions), the behaviour of the threader is undefined at this time.  <b>Note that if you want to perform both threaded and non-threaded queries on the same connection, you MUST read the "Locking" section below.</b>
+
只要驱动支持,线程化的查询可以用任何数据库句柄来执行。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, <tt>CALL</tt> on MySQL with certain functions), the behaviour of the threader is undefined at this time.  <b>Note that if you want to perform both threaded and non-threaded queries on the same connection, you MUST read the "Locking" section below.</b>
  
 
Query operations use the following callback parameters:
 
Query operations use the following callback parameters:
Line 341: Line 342:
 
=SQLite=
 
=SQLite=
 
==简介==
 
==简介==
[http://www.sqlite.org/ 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 <tt>sqlite</tt>.
+
[http://www.sqlite.org/ SQLite]是一个基于本地文件的数据库引擎,SourceMod给它内置了DBI。SQLite和MySql是不同的,所以有些Mysql的查询语句在SQLite中不生效,在配置文件中的数据库类型填<tt>sqlite</tt>
  
 
==使用==
 
==使用==
Line 350: Line 351:
 
SQLite supports the threading layer, and requires all of the same rules as the MySQL driver (including locks on shared connections).
 
SQLite supports the threading layer, and requires all of the same rules as the MySQL driver (including locks on shared connections).
  
==外部连接==
+
==外部链接==
 
*[http://www.sqlite.org SQLite Homepage]
 
*[http://www.sqlite.org SQLite Homepage]
 
*[http://sqlitebrowser.sourceforge.net/ SQLite Browser]
 
*[http://sqlitebrowser.sourceforge.net/ SQLite Browser]

Revision as of 04:53, 28 June 2019

Language: English  • 中文

本文将介绍如何使用SourceMod的SQL特性。请注意,这并不是关于SQL或任何特定SQL实现的介绍或教程。

SourceMod的SQL层的正式称呼是DBI,全写是Database Interface,中文称呼为数据库接口,这个接口是通用SQL方法的一般抽象。要连接到特定数据库(譬如MySQL和sqLite),则必须加载对应的SourceMod DBI驱动。当前SourceMod已内置有MySQL和SQLite的驱动。

SourceMod会自动按需(当然前提是有)检测并加载驱动。所有的数据库相关内置函数可以在scripting/include/dbi.inc中找到,对应的C++ API则在public/IDBDriver.h中。

连接数据库

当前共有两种方式连接数据库。第一种是通过命名配置文件,命名配置是在configs/databases.cfg中列出的预设配置。如果使用了数据库,那么SourceMod要求你至少提供一个名为"default"的配置。

下面是配置SQL的一个例子:

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

请使用SQL_ConnectSQL_DefConnect来实例化基于命名配置的数据库连接。

另一种方式是使用SQL_ConnectCustom,并通过传递包含这些参数的键值对句柄对象手动指定所有连接参数。

下面是一个典型的连接数据库的例子:

char error[255];
Database db = SQL_DefConnect(error, sizeof(error));
 
if (db == null)
{
	PrintToServer("Could not connect: %s", error);
} 
else 
{
	delete db;
}

查询语句

无返回结果类

最简单的查询就是那些不返回结果的查询了,例如,CREATE,DROP,UPDATE,INSERT和DELETE。对于这些查询语句,推荐使用SQL_FastQuery()。它的名字里带快,并不意味着它执行起来更快,实际上,它只是让我们在编写代码上更快。下面是使用例子,代码中给定的db是一个有效的数据库句柄:

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

有返回结果类

如果一个查询返回了结果集,并且必须处理它,那么你就必须使用SQL_Query()了。与SQL_FastQuery()不同的是,这个函数会返回一个必须关闭的句柄。

一个会返回结果的查询例子:

DBResultSet query = SQL_Query(db, "SELECT userid FROM vb_user WHERE username = 'BAILOPAN'");
if (query == null)
{
	char error[255];
	SQL_GetError(db, error, sizeof(error));
	PrintToServer("Failed to query (error: %s)", error);
} 
else 
{
	/* 在这里进行结果的处理!
	 */
 
	/* 释放句柄对象 */
	delete query;
}

预编译语句

预编译语句是另一种查询的方法。预编译语句背后的实质是,你构造一个查询的“模板”,在这之后可以任意次地复用。预编译语句有以下的优势:

  • 如果使用预编译语句,那么数据库就可以更好地缓存查询
  • 你不需要每次使用时重新构造查询语句
  • 你不需要每次使用时分配新的查询结构体
  • 输入总是安全的(下面会讲到)

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:

int GetSomeInfo(Database db, const char[] name)
{
	DBResultSet hQuery;
	char query[100];
 
	/* 创建足够的空间来保证我们的查询字符串能被正确引用  */
	int buffer_len = strlen(name) * 2 + 1;
	char[] new_name = new char[buffer_len];
 
	/* 要求SQL驱动程序确保我们的字符串被安全引用 */
	SQL_EscapeString(db, name, new_name, buffer_len);
 
	/* 生成查询语句串 */
	Format(query, sizeof(query), "SELECT userid FROM vb_user WHERE username = '%s'", new_name);
 
	/* 执行查询语句 */
	if ((hQuery = SQL_Query(query)) == null)
	{
		return 0;
	}
 
	/* 获取一些信息
	 */
 
	delete hQuery;
}

让我们来看看使用预编译语句的版本:

DBStatement hUserStmt = null;
int GetSomeInfo(Database db, const char[] name)
{
	/* 检查一下我们是不是已经创建好了语句 */
	if (hUserStmt == null)
	{
		char error[255];
		hUserStmt = SQL_PrepareQuery(db, "SELECT userid FROM vb_user WHERE username = ?", error, sizeof(error));
		if (hUserStmt == null)
		{
			return 0;
		}
	}
 
	SQL_BindParamString(hUserStmt, 0, name, false);
	if (!SQL_Execute(hUserStmt))
	{
		return 0;
	}
 
	/**
	 * 在这处理信息
	 */
}

重要区别:

  • 输入字符串(name)不需要被反引号(引号)包起来。数据库引擎会自动处理类型安全和插入检查。
  • 参数标记?周围不需要引号,即使它接受了一个字符串。
  • 我们仅仅只需要创建语句句柄一次,在那之后,于该数据库连接的整个生命周期它都将存在。

处理查询结果

对于普通查询和预编译语句查询,它们处理结果的方式相同。 相关的重要函数如下:

  • SQL_GetRowCount() - 返回查询结果的行数。
  • SQL_FetchRow() - 拉取下一行,如果有的话。
  • SQL_Fetch[Int|String|Float]() - 从当前行拉取特定字段的数据。

我们假设有如下结构的一个简单表:(译注:这是一段建表语句)

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

下面的示例包含的代码将打印出与某个年龄段匹配的所有用户。常规查询和预编译语句各有一个例子。

void PrintResults(Handle query)
{
	/* 即便结果只有一行,你也应该先使用SQL_FetchRow() */
	char name[MAX_NAME_LENGTH];
	while (SQL_FetchRow(query))
	{
		SQL_FetchString(query, 0, name, sizeof(name));
		PrintToServer("Name \"%s\" was found.", name);
	}
}
 
bool GetByAge_Query(Database db, int age)
{
	char query[100];
	Format(query, sizeof(query), "SELECT name FROM users WHERE age = %d", age);
 
	DBResultSet hQuery = SQL_Query(db, query);
	if (hQuery == null)
	{
		return false;
	}
 
	PrintResults(hQuery);
 
	delete hQuery;
 
	return true;
}
 
DBStatement hAgeStmt = null;
bool GetByAge_Statement(Database db, int age)
{
	if (hAgeSmt == null)
	{
		char error[255];
		if ((hAgeStmt = SQL_PrepareQuery(db, 
			"SELECT name FROM users WHERE age = ?", 
			error, 
			sizeof(error))) 
		     == null)
		{
			return false;
		}
	}
 
	SQL_BindParamInt(hAgeStmt, 0, age);
	if (!SQL_Execute(hAgeStmt))
	{
		return false;
	}
 
	PrintResults(hAgeStmt);
 
	return true;
}

注意,这些示例中都没有关闭预编译语句句柄。因为这些示例假定有一个全局的数据库实例,只有在卸载插件时才关闭它。对于那些自己维护临时数据库连接的插件,就必须释放预编译语句句柄,否则数据库连接将永远不会关闭。

多线程

译注:下面的内容比较复杂,可以不用看 SourceMod支持多线程SQL查询。这意味着,数据库操作可以在游戏主线程之外的线程中完成。如果你使用远程的数据库服务器或者需要一个网络连接,查询可能会导致明显延迟,所以如果你的查询发生在游戏进行期间,那么支持线程通常是一个好主意。

多线程查询是异步的。这意味着,他们会在回调函数里触发并返回结果。尽管回调函数最终一定能触发,但是你并不能让它在指定时刻触发。某些驱动可能不支持多线程,如果是这种情况,一个运行时错误会被抛出。如果线程器不能被启动或者被禁用,SourceMod会以回调函数的形式在主线程里执行查询。

操作

所有被线程化的操作(除了数据库连接)都使用一样的回调SQLQueryCallback来接受结果,参数如下:

  • db - 数据库句柄对象的拷贝。如果db句柄找不到或者是无效的,会把 null 传过去。
  • results - 结果对象,失败时为null。
  • error - 包含错误内容的字符串。
  • data - 通过SQL操作传递的自定义数据。

多线程支持下面的操作:

  • 数据库连接,使用函数 SQL_TConnect
  • 数据库查询,使用函数 SQL_TQuery()
  • 注意: 预编译语句目前不支持多线程。

数据库操作的连续调用是安全的。

连接

没必要为了线程化的查询而使用线程化的数据据连接。但是,使用线程化的数据库连接就不会因为建立连接时的延时造成服务器卡顿。 线程化的数据库连接使用这个回调: SQLConnectCallback

回调的参数如下:

  • db: 数据库连接的句柄对象,如果无法连接,将会返回null
  • error: 错误的字符串,如果有
  • data: 未使用

例子:

Database hDatabase = null;
 
void StartSQL()
{
	Database.Connect(GotDatabase);
}
 
public void GotDatabase(Database db, const char[] error, any data)
{
	if (db == null)
	{
		LogError("Database failure: %s", error);
	} 
        else 
        {
		hDatabase = db;
	}
}

查询

只要驱动支持,线程化的查询可以用任何数据库句柄来执行。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 null 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 null if there was an error.
  • error: Error string, if any.
  • data: Optional user-defined data passed in through SQL_TQuery().

Example, continuing from above:

void CheckSteamID(int userid, const char[] auth)
{
	char query[255];
	FormatEx(query, sizeof(query), "SELECT userid FROM users WHERE steamid = '%s'", auth);
	hdatabase.Query(T_CheckSteamID, query, userid);
}
 
public void T_CheckSteamID(Database db, DBResultSet results, const char[] error, any data)
{
	int client = 0;
 
	/* Make sure the client didn't disconnect while the thread was running */
	if ((client = GetClientOfUserId(data)) == 0)
	{
		return;
	}
 
	if (results == null)
	{
		LogError("Query failed! %s", error);
		KickClient(client, "Authorization failed");
	} else if (results.RowCount == 0) {
		KickClient(client, "You are not a member");
	}
}

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(Database db, int age)
{
	char query[100];
	FormatEx(query, sizeof(query), "SELECT name FROM users WHERE age = %d", age);
 
	SQL_LockDatabase(db);
	DBResultSet hQuery = SQL_Query(db, query);
	if (hQuery == null)
	{
		SQL_UnlockDatabase(db);
		return false;
	}
	SQL_UnlockDatabase(db);
 
	PrintResults(hQuery);
 
	delete 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.

警告

  • 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.

优先级

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

简介

SQLite是一个基于本地文件的数据库引擎,SourceMod给它内置了DBI。SQLite和MySql是不同的,所以有些Mysql的查询语句在SQLite中不生效,在配置文件中的数据库类型填sqlite

使用

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).

外部链接

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)