SQL Connection Manager
version 1.0
by Zefir
SQL Manager (sqlm) although an amxx plugin, but by itself does not perform any visible action. It is a library providing a more convenient functionality for working with SQL servers.
The main purpose of this plugin, it automatically reconnects when disconnected, and the ability to transparently work with several drivers databases.
Description
In sqlm no concept of connection. There is a connection tuple, keeps all the necessary data. The connection is created and updated as necessary. Handle queries are used as usual.
All functions except running's handle, work only with the name of the tuple.
Include sqlm.inc to use this plugin. Required constants from sql_manager.inc will be included automatically.
Depends (Required)
- Modules
- Library
- hashx.inc
- bits.inc
- text.inc
API
When connecting to or after the restoration of communication at breakage, called forward sqlm_connection_established function receives as a parameter the name of the raised tuple and name of the driver («mysql» or «sqlite» …).
Forward
- sqlm_connection_established(tuple[], driver[])
- Forward function is called when you first connect to the database or after the break when the connection is restored.
Native
- sqlm_affected_rows(Handle:query);
- Returns the number of affected rows.
- bool:sqlm_connected(tuple[] = "default");
- Returns state of connection.
- sqlm_exec(Handle:query);
- Execute a query prepared function .
- sqlm_field_name_to_num(Handle:query, const name[]);
- Returns the field number by name in the result set.
- sqlm_field_num_to_name(Handle:query, num, name[], maxlen);
- Returns the name of the field by its number in the resulting query.
- sqlm_free_handle(Handle:query);
- Releases the handle to the query.
- sqlm_get_insert_id(Handle:query);
- Returns the ID of the last inserted row. Used mainly when working with MySQL databases.
- sqlm_get_query_string(Handle:query, querystring[], maxlen);
- Returns the original query string for a handle.
- sqlm_is_null(Handle:query, column);
- Tells whether a specific column in the current row is NULL or not
- sqlm_more_results(Handle:query);
- Returns true if the result is still unread row
- bool:sqlm_next_row(Handle:query);
- Checks for available lines in the data set, and if reading the next one.
- sqlm_num_columns(Handle:query);
- Returns number of fields in the dataset.
- sqlm_num_results(Handle:query);
- Returns the total number of resulting rows.
- Handle:sqlm_prepare_query(const tuple[SQL_NAME], const fmt[], {Float,_}:...);
- The function prepares a synchronous request to run.
- sqlm_query_error(Handle:query, error[], maxlen);
- Returns the last database driver error
- sqlm_quote_string(const tuple[SQL_NAME], buffer[], buflen, const fmt[], {Float,_}:...);
- Escapes a string according to the rules database.
- sqlm_read_result(Handle:query, column, {Float,_}:...);
- Reads the value of fields in the current row result set.
- sqlm_simple_query(const tuple[SQL_NAME], const query_string[], {Float,_}:...);
- The function sends the request and ignores the result of execution. Often used for logging, etc.
- sqlm_simple_upd_ins(const tuple[SQL_NAME], const update_string[], const insert_string[]);
- Assumes the existence of a processing line. Sends two requests, first to update a row, and the second to add, if such a line yet.
- sqlm_thread_query(const tuple[SQL_NAME], const func[], const query_string[], data);
- The function creates and sends an asynchronous request to the database. That is when a driver receives a response, it will call the specified function to which the answer will be given.
Configuration file
Configuration is stored in the file config/sql_manager.vdf format Valve Data Format. Tuple «default» if it does not, create based on standard parameters of sql.cfg.
Code:
"Servers"
{
"default"
{
"type" "mysql"
"host" "127.0.0.1"
"db" "amx"
"user" "admin"
"pass" "admin_pass"
"timeout" "0.000"
"autoconnect" "1"
}
"local"
{
"type" "sqlite"
"host" ""
"db" "local"
"user" ""
"pass" ""
"timeout" "0.000"
"autoconnect" "1"
}
"remote"
{
"type" "mysql"
"host" "example.tld"
"db" "remote_db"
"user" "admin"
"pass" "admin_pass"
"timeout" "0.000"
"autoconnect" "0"
}
"test"
{
"type" "mysql"
"host" "example.tld"
"db" "test_db"
"user" "admin"
"pass" "admin_pass"
"timeout" "0.000"
"autoconnect" "-1"
}
}
- default, local, remote, test - random tuple names which are used for the treatment of plug-ins.
- type - defines the driver used for the tuple. Available «mysql» and «sqlite».
- host, db, user, pass - as usual
- timeout - corresponds to the same parameter for the function SQL _MakeDbTuple, and defines the connection timeout.
- autoconnect - manages the restoration of connections:
- -1: Allows you to disable unused profile without removing it from the file.
- 0: the connection to demand. If the link is often broken, but it should be infrequent, for example, only when the map changes, it will not pull the connection constantly.
- 1: a persistent connection. Disconnection and attempts to connect will always occur.
Debugging
These two constants in sql_manager.sma include debugging and tracing queries:
Code:
#define DEBUG
#define SQL_TRACE
Comment out their to disable debugging.
Statistics
Command
sqlm_stats [tuple_name] will show the current state of tuple and statistics queries:
Code:
sqlm_stats db1
SQL Connection Manager, version 1.0, by Zefir
Statistic for Tuple:............ 'db1'
Direct Queries:............... 0
Threaded Queries:............. 6
Errors:
Reconnect count:.............. 0
Error count:.................. 1
First Error:.................. (1) 'table 'nets' already exists'
Last Error:................... (1) 'table 'nets' already exists'
Links
Libs:
Hybrid Mode
