AlliedModders - View Single Post

Zefir · **Plugin Info:**

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
- sqlx
- vdf
- fakemeta
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: