[INC] SQLVault

Exolent[jNr] · **Author**

SQLVault
Version 0.0.3
by Exolent

Introduction:

SQLVault is a new type of vault system that uses SQL to save the data.
This was made for those that wanted SQL to save their data but needed the vault-style of coding.
It allows for remote databases as well as local databases (sqlite).

Starting Functions:

These functions are based on the ideas from NVault, NFVault (Emp's custom file vault), and a few ideas of my own.

Opening/Closing Vault:

Code:

/*
 * Opens a vault database
 * 
 * @param       szHost - The host used to connect to the database
 * @param       szUser - The user used to connect to the database
 * @param       szPass - The password used to connect to the database
 * @param       szDb - The name of the database to connect to
 * @param       szVaultName - The vault name to open
 * @param       bAutoInit - If true, sqlv_init() is called inside the function. If false, sqlv_init() is not called.
 * 
 * @return      Returns a valid vault handle on success, Invalid_SQLVault on failure.
 * 
 */
SQLVault:sqlv_open(szHost[], szUser[], szPass[], szDb[], szVaultName[], bool:bAutoInit = true)

/*
 * Opens a vault database based on the amx_sql_* cvars in addons/amxmodx/configs/sql.cfg
 * 
 * @param       szVaultName - The vault name to open
 * @param       bAutoInit - If true, sqlv_init() is called inside the function. If false, sqlv_init() is not called.
 * 
 * @return      Returns a valid vault handle on success, Invalid_SQLVault on failure.
 * 
 */
SQLVault:sqlv_open_default(szVaultName[], bool:bAutoInit = true)

/*
 * Opens a local vault database using the sqlite module
 * 
 * @param       szVaultName - The vault name to open
 * @param       bAutoInit - If true, sqlv_init() is called inside the function. If false, sqlv_init() is not called.
 * 
 * @return      Returns a valid vault handle on success, Invalid_SQLVault on failure.
 * 
 */
SQLVault:sqlv_open_local(szVaultName[], bool:bAutoInit = true)

/*
 * Closes the vault
 * 
 * @param       hVault - The vault to close
 * 
 * @return      No return
 * 
 * @note        The vault handle is set to Invalid_SQLVault after it is closed.
 * 
 */
sqlv_close(&SQLVault:hVault)

Initialization:

After a vault is opened, it needs to be initialized.
If a vault is never initialized, it will never work and will most likely cause errors to occur.
To initialize a vault, you can leave the last argument in the open function to true, or set it to false and call this function afterwards:

Code:

/*
 * Initializes the vault for use
 * 
 * @param       hVault - The vault to initialize
 * 
 * @return      Returns 1 on success, 0 on failure
 * 
 * @note        This must be used before any other vault functions are used (except open and close)!
 * 
 */
sqlv_init(SQLVault:hVault)

Connections:

When using the functions in this vault, a connection must be opened and closed each time to the database.
This can cause the CPU to slow down when several of these functions are in the same body.
Basically, this means that a connection can be opened and closed several times in 1 body of code, which is unnecessary.
To go around this, you can directly open and close the connection to save CPU time.
These are the functions to use:

Code:

/*
 * Connects to the vault database
 * 
 * @param       hVault - The vault to connect to
 * 
 * @return      Returns 1 on success, 0 on failure.
 * 
 * @note        This does not open the vault!
 *          It only creates a cached connection.
 * 
 * @note        This should be used when multiple vault functions are used in a row
 *          Doing this will reduce CPU usage in connecting/disconnecting each time those functions are used.
 * 
 * @note        After those functions are used, this connection should be closed.
 * 
 * @note        It is not recommended to keep the connection open the whole time the plugin is running.
 * 
 */
sqlv_connect(SQLVault:hVault)

/*
 * Disconnects from the vault database
 * 
 * @param       hVault - The vault to disconnect
 * 
 * @return      Returns 1 if disconnected, 0 on error or no connection existed.
 * 
 * @note        This does not close the vault!
 *          It only closes the cached connection made.
 * 
 */
sqlv_disconnect(SQLVault:hVault)

Here is an example usage:

Code:

sqlv_connect(hVault);

// functions like get/set/delete/etc. go here

sqlv_disconnect(hVault);

Get/Set Data:

The main reason for having a vault is to store and retrieve data.
However, having workarounds for different data types can be uncomfortable to do in your own code.
Therefore, specific functions are made to differentiate string, integer, and float data types to be stored:

Code:

/*
 * Gets a string value from a vault
 * 
 * @param       hVault - The vault to get the data from
 * @param       szKey - The key holding the data
 * @param       szData - The buffer to hold the data
 * @param       iDataLen - The max length of the data buffer
 * @param       iTimeStamp - The byref variable holding the timestamp
 * 
 * @return      Returns 1 on success, 0 on failure
 * 
 */
sqlv_get_data(SQLVault:hVault, szKey[], szData[], iDataLen, &iTimeStamp = 0)

/*
 * Gets an integer value from a vault
 * 
 * @param       hVault - The vault to get the data from
 * @param       szKey - The key holding the data
 * @param       iTimeStamp - The byref variable holding the timestamp
 * 
 * @return      Returns the integer value on success, 0 on failure
 * 
 */
sqlv_get_num(SQLVault:hVault, szKey[], &iTimeStamp = 0)

/*
 * Gets a float value from a vault
 * 
 * @param       hVault - The vault to get the data from
 * @param       szKey - The key holding the data
 * @param       iTimeStamp - The byref variable holding the timestamp
 * 
 * @return      Returns the float value on success, 0.0 on failure
 * 
 */
Float:sqlv_get_float(SQLVault:hVault, szKey[], &iTimeStamp = 0)

Respectively, there are similar functions to set that data:

Code:

/*
 * Sets a vault entry to a string value
 * 
 * @param       hVault - The vault to set the data in
 * @param       szKey - The key to hold the data
 * @param       szData - The string value to set
 * 
 * @return      Returns 1 on success, 0 on failure.
 * 
 * @note        Use "*" for the key to set all entries to that data.
 *          This will update all the timestamps and also set all to non-permanent (even permanent entries)!
 * 
 */
sqlv_set_data(SQLVault:hVault, szKey[], szData[])

/*
 * Sets a vault entry to an integer value
 * 
 * @param       hVault - The vault to set the data in
 * @param       szKey - The key to hold the data
 * @param       iData - The integer value to set
 * 
 * @return      Returns 1 on success, 0 on failure.
 * 
 * @note        Use "*" for the key to set all entries to that data.
 *          This will update all the timestamps and also set all to non-permanent (even permanent entries)!
 * 
 */
sqlv_set_num(SQLVault:hVault, szKey[], const iData)

/*
 * Sets a vault entry to a float value
 * 
 * @param       hVault - The vault to set the data in
 * @param       szKey - The key to hold the data
 * @param       flData - The float value to set
 * 
 * @return      Returns 1 on success, 0 on failure.
 * 
 * @note        Use "*" for the key to set all entries to that data.
 *          This will update all the timestamps and also set all to non-permanent (even permanent entries)!
 * 
 */
sqlv_set_float(SQLVault:hVault, szKey[], Float:flData)

Something new that you may see is the "*" notes for the keys.
That basically means, when you use "*" for the key, all entries are updated with the data that you set.
Also, the timestamps are all updated to the current timestamp, and all keys are set to be non-permanent [even existing ones that are permanent (permanent keys will be described later)].
However, when using the "*" key to update all data, no new entries are made. Only updates are done.

Permanent keys are keys that have a timestamp, but are unaffected when a vault is pruned.
When a vault is pruned, that means that all keys that have a timestamp in a given range are deleted.
Permanent keys are never deleted when a vault is pruned.

Here are the functions to set permanent data.
You will find that they are quite similar to the non-permanent functions:

Code:

/*
 * Sets a vault entry to a string value with a permanent entry
 * 
 * @param       hVault - The vault to set the data in
 * @param       szKey - The key to hold the data
 * @param       szData - The string value to set
 * 
 * @return      Returns 1 on success, 0 on failure.
 * 
 * @note        Permanent means that the entry cannot be deleted by sqlv_prune().
 * 
 * @note        Use "*" for the key to set all entries to that data.
 *          This will update all the timestamps and also set all to permanent (even non-permanent entries)!
 * 
 */
sqlv_pset_data(SQLVault:hVault, szKey[], szData[])

/*
 * Sets a vault entry to an integer value with a permanent entry
 * 
 * @param       hVault - The vault to set the data in
 * @param       szKey - The key to hold the data
 * @param       iData - The integer value to set
 * 
 * @return      Returns 1 on success, 0 on failure.
 * 
 * @note        Permanent means that the entry cannot be deleted by sqlv_prune().
 * 
 * @note        Use "*" for the key to set all entries to that data.
 *          This will update all the timestamps and also set all to permanent (even non-permanent entries)!
 * 
 */
sqlv_pset_num(SQLVault:hVault, szKey[], const iData)

/*
 * Sets a vault entry to a float value with a permanent entry
 * 
 * @param       hVault - The vault to set the data in
 * @param       szKey - The key to hold the data
 * @param       flData - The float value to set
 * 
 * @return      Returns 1 on success, 0 on failure.
 * 
 * @note        Permanent means that the entry cannot be deleted by sqlv_prune().
 * 
 * @note        Use "*" for the key to set all entries to that data.
 *          This will update all the timestamps and also set all to permanent (even non-permanent entries)!
 * 
 */
sqlv_pset_float(SQLVault:hVault, szKey[], Float:flData)

Similar to the non-permanent functions, you can use the "*" as the key.
The only difference is that all keys will be set to permanent, instead of non-permanent.

Key-Specific Functions:

Some functions were made to deal specifically with the keys to manipulate them or their data, or read them or their data.
Here are a few of them:

Code:

/*
 * Checks if a key exists in a vault
 * 
 * @param       hVault - The vault to look in
 * @param       szKey - The key to look for
 * 
 * @return      Returns 1 if exists, 0 if it doesn't.
 * 
 */
sqlv_key_exists(SQLVault:hVault, szKey[])

/*
 * Removes a key from a vault
 * 
 * @param       hVault - The vault to delete the key from
 * @param       szKey - The key to delete
 * 
 * @return      Returns 1 on success, 0 on failure.
 * 
 */
sqlv_remove(SQLVault:hVault, szKey[])

/*
 * Updates the timestamp for a key in a vault
 * 
 * @param       hVault - The vault to update the key in
 * @param       szKey - The key to update the timestamp for
 * @param       iTimeStamp - The timestamp to set for the vault (optional, default is -1)
 * 
 * @return      Returns 1 on success, 0 on failure.
 * 
 * @note        Use -1 for timestamp to update with the current timestamp
 * 
 * @note        Use "*" for the key to touch all entries.
 * 
 */
sqlv_touch(SQLVault:hVault, szKey[], iTimeStamp = -1)

Vault-Specific Functions:

Some functions were made to manage the whole vault.

Here are the standard ones:

Code:

/*
 * Removes all non-permanent entries in a vault that have a timestamp between the start and end timestamps
 * 
 * @param       hVault - The vault to prune
 * @param       iStart - The start timestamp
 * @param       iEnd - The end timestamp
 * 
 * @return      Returns the number of deleted entries (or 1 if none deleted) on success, 0 on failure.
 * 
 */
sqlv_prune(SQLVault:hVault, iStart, iEnd)

/*
 * Deletes all entries in a vault
 * 
 * @param       hVault - The vault to delete entries from
 * @param       bSavePermanent - If true, deletes only entries that are not permanent. If false, deletes all entries. (optional, default is false)
 * 
 * @return      Returns total entries deleted (or 1 if empty) on success, 0 on failure.
 * 
 */
sqlv_clear(SQLVault:hVault, bool:bSavePermanent = false)

/*
 * Gets the total number of entries in the vault
 * 
 * @param       hVault - The vault to find the size of
 * 
 * @return      Returns the total number of entries in the vault
 * 
 */
sqlv_size(SQLVault:hVault)

Some new functions that I added for the vault-style of scripting are to be able to read all keys in a vault.

First, this is a function to read keys 1 at a time:

Code:

/*
 * Reads a vault by key index
 * 
 * @param       hVault - The vault to read from
 * @param       iKeyIndex - The key index to read
 * @param       szKey - The string to hold the key (optional)
 * @param       iKeyLen - The max length of the key buffer (optional)
 * @param       szData - The string to hold the data (optional)
 * @param       iDataLen - The max length of the data buffer (optional)
 * @param       iTimeStamp - The byref variable that holds the timestamp (optional)
 * @param       szWhere - The where condition for selecting specific vault data (optional)
 * @param       szSort - The method to sort the vault data by (optional)
 * 
 * @return      Returns 1 on success, 0 on failure.
 * 
 * @note        Key indexes start at 0 and stop at 1 before the size of the vault (sqlv_size() - 1)
 * 
 * @note        If you want to read all keys in the vault, use sqlv_read_all().
 * 
 * @note        If a vault is changed by setting data, changing timestamps, deleteing, etc. then the key indexes may change.
 *          Therefore, those type of actions should not be done if more than 1 key is being read.
 * 
 * @note        The where condition is the same as the where clause from MySQL's SELECT statement.
 *          Do not include "WHERE" inside the where clause.
 * 
 * @note        Sorting the vault data can be done 2 ways:
 *          1. Pass "asc" to sort in ascending order by the data value
 *             Pass "desc" to sort in descending order by the data value
 *          2. Pass any other string to format the ORDER BY clause from MySQL
 *          Do not include "ORDER BY" inside the sort string
 * 
 */
sqlv_read(SQLVault:hVault, iKeyIndex, szKey[] = "", iKeyLen = 0, szData[] = "", iDataLen = 0, &iTimeStamp = 0, const szWhere[] = "", const szSort[] = "")

This reads each vault entry for it's key, data, and timestamp.
The key index is what specifies the entry to read from.

You can also specify which data to select, and how it should be sorted.

Here is an example:

Code:

new iSize = sqlv_size(hVault);

// get a random key index to read from
new iKeyIndex = random(iSize);

new szKey[64], szData[32], iTimeStamp;
sqlv_read(hVault, iKeyIndex, szKey, charsmax(szKey), szData, charsmax(szData), iTimeStamp);

// convert timestamp to display format
new szTimeStamp[32];
format_time(szTimeStamp, charsmax(szTimeStamp), "%Y-%m-%d %H:%M:%S", iTimeStamp);

// output data
server_print("Key [%s] Data [%s] TimeStamp [%s]", szKey, szData, szTimeStamp);

Code:

// get the best player's stats
new szKey[64], szData[32], iTimeStamp;
sqlv_read(hVault, 0, szKey, charsmax(szKey), szData, charsmax(szData), iTimeStamp, _, "desc");

// convert timestamp to display format
new szTimeStamp[32];
format_time(szTimeStamp, charsmax(szTimeStamp), "%Y-%m-%d %H:%M:%S", iTimeStamp);

// output data
server_print("Key [%s] Data [%s] TimeStamp [%s]", szKey, szData, szTimeStamp);

Second, this function reads all of the vault data at once and stores it in a cell array.

Code:

/*
 * Reads all of the vault data into a cell array
 * 
 * @param       hVault - The vault to read from
 * @param       aVaultData - The cell array to hold the data
 * @param       szWhere - The where condition for selecting specific vault data (optional)
 * @param       szSort - The method to sort the vault data by (optional)
 * 
 * @return      Returns the total number of vault entries
 * 
 * @note        The where condition is the same as the where clause from MySQL's SELECT statement.
 *          Do not include "WHERE" inside the where clause.
 * 
 * @note        Sorting the vault data can be done 2 ways:
 *          1. Pass "asc" to sort in ascending order by the data value
 *             Pass "desc" to sort in descending order by the data value
 *          2. Pass any other string to format the ORDER BY clause from MySQL
 *          Do not include "ORDER BY" inside the sort string
 * 
 * @note        The cell array contains arrays that correspond to the SQLVaultEntry enum
 *          
 * @note        Example:
 *          
 *          new Array:aVaultData;
 *          new iVaultKeys = sqlv_read_all(hVault, aVaultData);
 *          
 *          new eVaultData[SQLVaultEntry];
 *          
 *          for(new i = 0; i < iVaultKeys; i++)
 *          {
 *              ArrayGetArray(aVaultData, i, eVaultData);
 *              
 *              eVaultData[SQLV_Key] = key
 *              eVaultData[SQLV_Data] = data
 *              eVaultData[SQLV_TimeStamp] = timestamp
 *          }
 *          
 *          ArrayDestroy(aVaultData);
 * 
 * @note        The cell array should not be created.
 *          It is auto-created in the function.
 *          If the cell array already contains a handle, it is destroyed first.
 * 
 * @note        The cell array needs to be destroyed after being used
 * 
 */
sqlv_read_all(SQLVault:hVault, &Array:aVaultData, const szWhere[] = "", const szSort[] = "")

As shown in the function description, there is an example that shows exactly how to use this.

You can also specify which data to select, and how it should be sorted.

Code:

new Array:aVaultData;
new iVaultKeys = sqlv_read_all(hVault, aVaultData);

new eVaultData[SQLVaultEntry];
new szTimeStamp[32];

for(new i = 0; i < iVaultKeys; i++)
{
    ArrayGetArray(aVaultData, i, eVaultData);
    
    // eVaultData[SQLV_Key] = key
    // eVaultData[SQLV_Data] = data
    // eVaultData[SQLV_TimeStamp] = timestamp
    
    format_time(szTimeStamp, charsmax(szTimeStamp), "%Y-%m-%d %H:%M:%S", eVaultData[SQLV_TimeStamp]);
    
    server_print("Key [%s] Data [%s] TimeStamp [%s]", eVaultData[SQLV_Key], eVaultData[SQLV_Data], szTimeStamp);
}

ArrayDestroy(aVaultData);

Third, this function reads a set of the vault data at once and stores it in a cell array.

Code:

/*
 * Reads a set of the vault data into an array
 * 
 * @param       hVault - The vault to read from
 * @param       eOutputData - The array to store all of the selected set
 * @param       iOutputSize - The size of the set to select
 * @param       iStart - The offset of the vault to start at
 * @param       szWhere - The where condition for selecting specific vault data (optional)
 * @param       szSort - The method to sort the vault data by (optional)
 * 
 * @return      Returns the total number of vault entries
 * 
 * @note        The where condition is the same as the where clause from MySQL's SELECT statement.
 *          Do not include "WHERE" inside the where clause.
 * 
 * @note        Sorting the vault data can be done 2 ways:
 *          1. Pass "asc" to sort in ascending order by the data value
 *             Pass "desc" to sort in descending order by the data value
 *          2. Pass any other string to format the ORDER BY clause from MySQL
 *          Do not include "ORDER BY" inside the sort string
 *          
 * @note        Example for grabbing top entries:
 *          
 *          new eVaultData[10][SQLVaultEntry];
 *          new iVaultKeys = sqlv_read_set(hVault, eVaultData, sizeof(eVaultData), _, _, "desc");
 *          
 *          for(new i = 0; i < iVaultKeys; i++)
 *          {
 *              eVaultData[i][SQLV_Key] = key
 *              eVaultData[i][SQLV_Data] = data
 *              eVaultData[i][SQLV_TimeStamp] = timestamp
 *          }
 * 
 */
sqlv_read_set(SQLVault:hVault, eOutputData[][SQLVaultEntry], iOutputSize, iStart = 0, const szWhere[] = "", const szSort[] = "")

As shown in the function description, there is an example that shows exactly how to use this.

You can also specify which data to select, and how it should be sorted.

Code:

new eVaultData[10][SQLVaultEntry];
new iVaultKeys = sqlv_read_set(hVault, eVaultData, sizeof(eVaultData), _, _, "desc");

new szTimeStamp[32];

for(new i = 0; i < iVaultKeys; i++)
{
    // eVaultData[i][SQLV_Key] = key
    // eVaultData[i][SQLV_Data] = data
    // eVaultData[i][SQLV_TimeStamp] = timestamp
    
    format_time(szTimeStamp, charsmax(szTimeStamp), "%Y-%m-%d %H:%M:%S", eVaultData[i][SQLV_TimeStamp]);
    
    server_print("Key [%s] Data [%s] TimeStamp [%s]", eVaultData[i][SQLV_Key], eVaultData[i][SQLV_Data], szTimeStamp);
}

Error Logging:

All error logs are NOT logged to the normal error logs, nor do they stop the plugin from running.
The logs are sent as normal AMXX logs to addons/amxmodx/logs/LYYYYMMDD.log where YYYY = year, MM = month, DD = day.

Notes:

These functions do not use threaded queries.

For those who don't know, a threaded query is a query that is not executed at the time it is submitted.
Threaded queries stack up and are executed on a FIFO (first-in-first-out) basis when the SQLx module has time to execute them.
This means that the query could be executed in 0.001 seconds or 5 minutes.
The time is never determined when it is executed, only after it is done.
Because they are executed when the module allows them to, there is no stress on the server to execute queries immediately.

There is no version for this that uses threaded queries.
If there is a large enough demand for a threaded queries version, then I shall work on one.

The SQLite module is automatically loaded when this include is used.
Even if local databases are not used for this, it is still required to run.

You may notice that the keys and vault names in the functions are not constants.
This is because the variables need to be escaped for any bad characters when used in the SQL queries.
Therefore, no constants can be used in these functions.

For example:
Code:
// bad
new const g_szVaultName[] = "test";

new SQLVault:g_hVault;

public plugin_init()
{
    g_hVault = sqlv_open_local(g_szVaultName);
}

// good
new g_szVaultName[] = "test";

new SQLVault:g_hVault;

public plugin_init()
{
    g_hVault = sqlv_open_local(g_szVaultName);
}

// also good
new SQLVault:g_hVault;

public plugin_init()
{
    g_hVault = sqlv_open_local("test");
}
My own test file is attached.
The functions do not have any meaning to them at all except for testing for compilation and execution.
Meaning, the results of them are ignored because I was only looking for errors.

All comments, suggestions, and bug reports are encouraged to be posted!

Exolent[jNr] · 01-02-2011 , 02:37 Re: SQLVault

SQLVault: Extended
Version 0.0.3
by Exolent

Introduction:

This is an extension from the SQLVault that is posted above.
Instead of using 1 key to set data, this extension uses a pair of 2 keys that have data.

Starting Functions:

The functions to open, close, connect, and disconnect to a vault are the same.

However, when opening a vault, the auto-init feature needs to be turned off.

Example:
Code:
new SQLVault:hVault = sqlv_open_local("test", false);
After the vault is opened, the init function specific to this extension needs to be called.
Code:
/*
 * Initializes the vault for use
 * 
 * @param       hVault - The vault to initialize
 * 
 * @return      Returns 1 on success, 0 on failure
 * 
 * @note        This must be used before any other vault functions are used (except open and close)!
 * 
 */
sqlv_init_ex(SQLVault:hVault)
The original auto-init can still be enabled because the vault will be allowed to use both the single key and paired key system in the same vault.

Example for paired keys only:
Code:
new SQLVault:hVault = sqlv_open_local("test", false);
sqlv_init_ex(hVault);
Example to allow single and paired keys:
Code:
new SQLVault:hVault = sqlv_open_local("test");
sqlv_init_ex(hVault);

Get/Set Data:

Storing and retrieving data is almost the same as the original SQLVault functions.
The only difference is that there are 2 key parameters that are used.

Code:

/*
 * Gets a string value from a vault
 * 
 * @param       hVault - The vault to get the data from
 * @param       szKey1 - The key1 of the 2 key pair holding the data
 * @param       szKey2 - The key2 of the 2 key pair holding the data
 * @param       szData - The buffer to hold the data
 * @param       iDataLen - The max length of the data buffer
 * @param       iTimeStamp - The byref variable holding the timestamp
 * 
 * @return      Returns 1 on success, 0 on failure
 * 
 */
sqlv_get_data_ex(SQLVault:hVault, szKey1[], szKey2[], szData[], iDataLen, &iTimeStamp = 0)

/*
 * Gets an integer value from a vault
 * 
 * @param       hVault - The vault to get the data from
 * @param       szKey1 - The key1 holding the data of the 2 key pair
 * @param       szKey2 - The key2 holding the data of the 2 key pair
 * @param       iTimeStamp - The byref variable holding the timestamp
 * 
 * @return      Returns the integer value on success, 0 on failure
 * 
 */
sqlv_get_num_ex(SQLVault:hVault, szKey1[], szKey2[], &iTimeStamp = 0)

/*
 * Gets a float value from a vault
 * 
 * @param       hVault - The vault to get the data from
 * @param       szKey1 - The key1 holding the data of the 2 key pair
 * @param       szKey2 - The key2 holding the data of the 2 key pair
 * @param       iTimeStamp - The byref variable holding the timestamp
 * 
 * @return      Returns the float value on success, 0.0 on failure
 * 
 */
Float:sqlv_get_float_ex(SQLVault:hVault, szKey1[], szKey2[], &iTimeStamp = 0)

Respectively, there are similar functions to set that data:

Code:

/*
 * Sets a vault entry to a string value
 * 
 * @param       hVault - The vault to set the data in
 * @param       szKey1 - The key1 to hold the data of the 2 key pair
 * @param       szKey2 - The key2 to hold the data of the 2 key pair
 * @param       szData - The string value to set
 * 
 * @return      Returns 1 on success, 0 on failure.
 * 
 * @note        Use "*" for szKey1 and it will set all values matching szKey2
 * @note        Use "*" for szKey2 and it will set all values matching szKey1
 * @note        Use "*" for szKey1 and szKey2 and all values in the vault will be set
 * 
 * @note        When using "*" as either key, all entries that are affected have the timestamp update.
 *          Also, all entries affected are set to non-permanent (even permanent entries)!
 * 
 */
sqlv_set_data_ex(SQLVault:hVault, szKey1[], szKey2[], szData[])

/*
 * Sets a vault entry to an integer value
 * 
 * @param       hVault - The vault to set the data in
 * @param       szKey1 - The key1 to hold the data of the 2 key pair
 * @param       szKey2 - The key2 to hold the data of the 2 key pair
 * @param       iData - The integer value to set
 * 
 * @return      Returns 1 on success, 0 on failure.
 * 
 * @note        Use "*" for szKey1 and it will set all values matching szKey2
 * @note        Use "*" for szKey2 and it will set all values matching szKey1
 * @note        Use "*" for szKey1 and szKey2 and all values in the vault will be set
 * 
 * @note        When using "*" as either key, all entries that are affected have the timestamp update.
 *          Also, all entries affected are set to non-permanent (even permanent entries)!
 * 
 */
sqlv_set_num_ex(SQLVault:hVault, szKey1[] ,szKey2[], const iData)

/*
 * Sets a vault entry to a float value
 * 
 * @param       hVault - The vault to set the data in
 * @param       szKey1 - The key1 to hold the data of the 2 key pair
 * @param       szKey2 - The key2 to hold the data of the 2 key pair
 * @param       flData - The float value to set
 * 
 * @return      Returns 1 on success, 0 on failure.
 * 
 * @note        Use "*" for szKey1 and it will set all values matching szKey2
 * @note        Use "*" for szKey2 and it will set all values matching szKey1
 * @note        Use "*" for szKey1 and szKey2 and all values in the vault will be set
 * 
 * @note        When using "*" as either key, all entries that are affected have the timestamp update.
 *          Also, all entries affected are set to non-permanent (even permanent entries)!
 * 
 */
sqlv_set_float_ex(SQLVault:hVault, szKey1[], szKey2[], Float:flData)

The main purpose for this is for situations that require formatting 2 strings into 1 string so that the single key functions can be used.

Example:

Code:

new szSteamID[35];
get_user_authid(iPlayer, szSteamID, charsmax(szSteamID));

new szKey[64];
formatex(szKey, charsmax(szKey), "%s-xp", szSteamID);

sqlv_set_num(g_hVault, szKey, g_iXP[iPlayer]);

formatex(szKey, charsmax(szKey), "%s-level", szSteamID);

sqlv_set_num(g_hVault, szKey, g_iLevel[iPlayer]);

This seems unnecessary, so this is how paired keys eases the complexity of this:

Code:

new szSteamID[35];
get_user_authid(iPlayer, szSteamID, charsmax(szSteamID));

sqlv_set_num_ex(g_hVault, szSteamID, "xp", g_iXP[iPlayer]);
sqlv_set_num_ex(g_hVault, szSteamID, "level", g_iLevel[iPlayer]);

Here are the similar functions for permanent keys:

Code:

/*
 * Sets a vault entry to a string value with a permanent entry
 * 
 * @param       hVault - The vault to set the data in
 * @param       szKey1 - The key1 to hold the data of the 2 key pair
 * @param       szKey2 - The key2 to hold the data of the 2 key pair
 * @param       szData - The string value to set
 * 
 * @return      Returns 1 on success, 0 on failure.
 * 
 * @note        Permanent means that the entry cannot be deleted by sqlv_prune_ex().
 * 
 * @note        Use "*" for szKey1 and it will set all values matching szKey2
 * @note        Use "*" for szKey2 and it will set all values matching szKey1
 * @note        Use "*" for szKey1 and szKey2 and all values in the vault will be set
 * 
 * @note        When using "*" as either key, all entries that are affected have the timestamp update.
 *          Also, all entries affected are set to permanent (even non-permanent entries)!
 * 
 */
sqlv_pset_data_ex(SQLVault:hVault, szKey1[], szKey2[], szData[])

/*
 * Sets a vault entry to an integer value with a permanent entry
 * 
 * @param       hVault - The vault to set the data in
 * @param       szKey1 - The key1 to hold the data of the 2 key pair
 * @param       szKey2 - The key2 to hold the data of the 2 key pair
 * @param       iData - The integer value to set
 * 
 * @return      Returns 1 on success, 0 on failure.
 * 
 * @note        Permanent means that the entry cannot be deleted by sqlv_prune_ex().
 * 
 * @note        Use "*" for szKey1 and it will set all values matching szKey2
 * @note        Use "*" for szKey2 and it will set all values matching szKey1
 * @note        Use "*" for szKey1 and szKey2 and all values in the vault will be set
 * 
 * @note        When using "*" as either key, all entries that are affected have the timestamp update.
 *          Also, all entries affected are set to permanent (even non-permanent entries)!
 * 
 */
sqlv_pset_num_ex(SQLVault:hVault, szKey1[], szKey2[], const iData)

/*
 * Sets a vault entry to a float value with a permanent entry
 * 
 * @param       hVault - The vault to set the data in
 * @param       szKey1 - The key1 to hold the data of the 2 key pair
 * @param       szKey2 - The key2 to hold the data
 * @param       flData - The float value to set
 * 
 * @return      Returns 1 on success, 0 on failure.
 * 
 * @note        Permanent means that the entry cannot be deleted by sqlv_prune_ex().
 * 
 * @note        Use "*" for szKey1 and it will set all values matching szKey2
 * @note        Use "*" for szKey2 and it will set all values matching szKey1
 * @note        Use "*" for szKey1 and szKey2 and all values in the vault will be set
 * 
 * @note        When using "*" as either key, all entries that are affected have the timestamp update.
 *          Also, all entries affected are set to permanent (even non-permanent entries)!
 * 
 */
sqlv_pset_float_ex(SQLVault:hVault, szKey1[], szKey2[], Float:flData)

When using the "*" as a key for these functions, this is how it works:

key1 = "*", key2 = valid key
- all data with a matching "key2" key will have the data that is being set

key1 = valid key, key2 = "*"
- all data with a matching "key1" key will have the data that is being set

key1 = "*", key2 = "*"
- all entries in the vault will be updated to that data

When using the "*" as any of the 2 keys, no new entries are made, only updates are done to existing entries.
Also, all timestamps are updated for affected keys and they are set to permanent or non-permanent depending on the "set" or "pset" function that was used.

Key-Specific Functions:

Similar to the original vault, key-specific functions exist for this extension:

Code:

/*
 * Checks if a key exists in a vault
 * 
 * @param       hVault - The vault to look in
 * @param       szKey1 - The key1 to look for of the 2 key pair
 * @param       szKey2 - The key2 to look for of the 2 key pair
 * 
 * @return      Returns 1 if exists, 0 if it doesn't.
 * 
 * @note        Use "*" for key1 to delete all keys matching key2.
 * @note        Use "*" for key2 to delete all keys matching key1.
 * @note        If "*" is used for both keys, it is the same as using sqlv_clear_ex().
 * 
 */
sqlv_key_exists_ex(SQLVault:hVault, szKey1[], szKey2[])

/*
 * Removes a key from a vault
 * 
 * @param       hVault - The vault to delete the key from
 * @param       szKey1 - The key1 to delete of the 2 key pair
 * @param       szKey2 - The key2 to delete of the 2 key pair
 * 
 * @return      Returns 1 on success, 0 on failure.
 * 
 * @note        Use "*" for key1 to delete all keys matching key2.
 * @note        Use "*" for key2 to delete all keys matching key1.
 * @note        If "*" is used for both keys, it is the same as using sqlv_clear_ex().
 * 
 */
sqlv_remove_ex(SQLVault:hVault, szKey1[], szKey2[])

/*
 * Updates the timestamp for a key in a vault
 * 
 * @param       hVault - The vault to update the key in
 * @param       szKey1 - The key1 to update the timestamp for of the 2 key pair
 * @param       szKey2 - The key2 to update the timestamp for of the 2 key pair
 * @param       iTimeStamp - The timestamp to set for the vault (optional, default is -1)
 * 
 * @return      Returns 1 on success, 0 on failure.
 * 
 * @note        Use -1 for timestamp to update with the current timestamp
 * 
 * @note        Permanent means that the entry cannot be deleted by sqlv_prune_ex().
 * 
 * @note        Use "*" for szKey1 and it will update all timestamps on entries matching szKey2
 * @note        Use "*" for szKey2 and it will update all timestamps on entries matching szKey1
 * @note        Use "*" for szKey1 and szKey2 and all timestamps will be updated
 * 
 */
sqlv_touch_ex(SQLVault:hVault, szKey1[], szKey2[], iTimeStamp = -1)

Vault-Specific Functions:

These are the similar vault-specific functions:

Code:

/*
 * Removes all non-permanent entries in a vault that have a timestamp between the start and end timestamps
 * 
 * @param       hVault - The vault to prune
 * @param       iStart - The start timestamp
 * @param       iEnd - The end timestamp
 * 
 * @return      Returns the number of deleted entries (or 1 if none deleted) on success, 0 on failure.
 * 
 */
sqlv_prune_ex(SQLVault:hVault, iStart, iEnd)

/*
 * Deletes all entries in a vault
 * 
 * @param       hVault - The vault to delete entries from
 * @param       bSavePermanent - If true, deletes only entries that are not permanent. If false, deletes all entries. (optional, default is false)
 * 
 * @return      Returns total entries deleted (or 1 if empty) on success, 0 on failure.
 * 
 */
sqlv_clear_ex(SQLVault:hVault, bool:bSavePermanent = false)

/*
 * Gets the total number of entries in the vault
 * 
 * @param       hVault - The vault to find the size of
 * 
 * @return      Returns the total number of entries in the vault
 * 
 */
sqlv_size_ex(SQLVault:hVault)

Some new functions that I added for the vault-style of scripting are to be able to read all keys in a vault.

First, this is a function to read keys 1 at a time:

Code:

/*
 * Reads a vault by key index
 * 
 * @param       hVault - The vault to read from
 * @param       iKeyIndex - The key index to read
 * @param       szKey - The string to hold the key of the 2 key pair (optional)
 * @param       iKeyLen - The max length of the key buffer of the 2 key pair (optional)
 * @param       szKey1 - The string to hold the key1 of the 2 key pair (optional)
 * @param       iKey2Len - The max length of the key2 buffer of the 2 key pair (optional)
 * @param       szData - The string to hold the data (optional)
 * @param       iDataLen - The max length of the data buffer (optional)
 * @param       iTimeStamp - The byref variable that holds the timestamp (optional)
 * @param       szWhere - The where condition for selecting specific vault data (optional)
 * @param       szSort - The method to sort the vault data by (optional)
 * 
 * @return      Returns 1 on success, 0 on failure.
 * 
 * @note        Key indexes start at 0 and stop at 1 before the size of the vault (sqlv_size_ex() - 1)
 * 
 * @note        The where condition is the same as the where clause from MySQL's SELECT statement.
 *          Do not include "WHERE" inside the where clause.
 * 
 * @note        Sorting the vault data is done by passing the "ORDER BY" clause from the MySQL SELECT statement.
 *          Do not include "ORDER BY" inside the sort string
 * 
 * @note        If you want to read all keys in the vault, use sqlv_read_all_ex().
 * 
 */
sqlv_read_ex(SQLVault:hVault, iKeyIndex, szKey1[] = "", iKey1Len = 0, szKey2[] = "", iKey2Len = 0, szData[] = "", iDataLen = 0, &iTimeStamp = 0, const szWhere[] = "", const szSort[] = "")

This reads each vault entry for it's key, data, and timestamp.
The key index is what specifies the entry to read from.

You can also specify which data to select, and how it should be sorted.

Here is an example:

Code:

new iSize = sqlv_size_ex(hVault);

// get a random key index to read from
new iKeyIndex = random(iSize);

new szKey1[64], szKey2[64], szData[32], iTimeStamp;
sqlv_read_ex(hVault, iKeyIndex, szKey1, charsmax(szKey1), szKey2, charsmax(szKey2), szData, charsmax(szData), iTimeStamp);

// convert timestamp to display format
new szTimeStamp[32];
format_time(szTimeStamp, charsmax(szTimeStamp), "%Y-%m-%d %H:%M:%S", iTimeStamp);

// output data
server_print("Key1 [%s] Key2 [%s] Data [%s] TimeStamp [%s]", szKey1, szKey2, szData, szTimeStamp);

Code:

// get the best player's points
new szKey1[64], szData[32], iTimeStamp;
sqlv_read_ex(hVault, 0, szKey1, charsmax(szKey1), "", 0, szData, charsmax(szData), iTimeStamp, "`key2` = 'points'", "`data` DESC");

// convert points to integer
new iPoints = str_to_num(szData);

// grab player's name
new szName[32];
sqlv_get_data(hVault, szKey1, "name", szName, charsmax(szName));

// convert timestamp to display format
new szTimeStamp[32];
format_time(szTimeStamp, charsmax(szTimeStamp), "%Y-%m-%d %H:%M:%S", iTimeStamp);

// output data
server_print("Best Player: %s <%s> has %d point%s [Last Save: %s]", szName, szKey1, iPoints, (iPoints == 1) ? "" : "s", szTimeStamp);

Second, this function reads all of the vault data at once and stores it in a cell array.

Code:

/*
 * Reads all of the vault data into a cell array
 * 
 * @param       hVault - The vault to read from
 * @param       aVaultData - The cell array to hold the data
 * @param       szWhere - The where condition for selecting specific vault data (optional)
 * @param       szSort - The method to sort the vault data by (optional)
 * 
 * @return      Returns the total number of vault entries
 * 
 * @note        The where condition is the same as the where clause from MySQL's SELECT statement.
 *          Do not include "WHERE" inside the where clause.
 * 
 * @note        Sorting the vault data is done by passing the "ORDER BY" clause from the MySQL SELECT statement.
 *          Do not include "ORDER BY" inside the sort string
 * 
 * @note        The cell array contains arrays that correspond to the SQLVaultEntryEx enum
 *          
 * @note        Example:
 *          
 *          new Array:aVaultData;
 *          new iVaultKeys = sqlv_read_all_ex(hVault, aVaultData);
 *          
 *          new eVaultData[SQLVaultEntryEx];
 *          
 *          for(new i = 0; i < iVaultKeys; i++)
 *          {
 *              ArrayGetArray(aVaultData, i, eVaultData);
 *              
 *              eVaultData[SQLVEx_Key1] = key1 of the 2 key pair
 *              eVaultData[SQLVEx_Key2] = key2 of the 2 key pair
 *              eVaultData[SQLVEx_Data] = data
 *              eVaultData[SQLVEx_TimeStamp] = timestamp
 *          }
 *          
 *          ArrayDestroy(aVaultData);
 * 
 * @note        The cell array should not be created.
 *          It is auto-created in the function.
 *          If the cell array already contains a handle, it is destroyed first.
 * 
 * @note        The cell array needs to be destroyed after being used
 * 
 */
sqlv_read_all_ex(SQLVault:hVault, &Array:aVaultData, const szWhere[] = "", const szSort[] = "")

As shown in the function description, there is an example that shows exactly how to use this.

You can also specify which data to select, and how it should be sorted.

Code:

new Array:aVaultData;
new iVaultKeys = sqlv_read_all_ex(hVault, aVaultData);

new eVaultData[SQLVaultEntryEx];
new szTimeStamp[32];

for(new i = 0; i < iVaultKeys; i++)
{
    ArrayGetArray(aVaultData, i, eVaultData);
    
    // eVaultData[SQLVEx_Key1] = key1
    // eVaultData[SQLVEx_Key2] = key2
    // eVaultData[SQLVEx_Data] = data
    // eVaultData[SQLVEx_TimeStamp] = timestamp
    
    format_time(szTimeStamp, charsmax(szTimeStamp), "%Y-%m-%d %H:%M:%S", eVaultData[SQLVEx_TimeStamp]);
    
    server_print("Key1 [%s] Key2 [%s] Data [%s] TimeStamp [%s]", eVaultData[SQLVEx_Key1], eVaultData[SQLVEx_Key2], eVaultData[SQLVEx_Data], szTimeStamp);
}

ArrayDestroy(aVaultData);

Third, this function reads a set of the vault data at once and stores it in a cell array.

Code:

/*
 * Reads a set of the vault data into an array
 * 
 * @param       hVault - The vault to read from
 * @param       eOutputData - The array to store all of the selected set
 * @param       iOutputSize - The size of the set to select
 * @param       iStart - The offset of the vault to start at
 * @param       szWhere - The where condition for selecting specific vault data (optional)
 * @param       szSort - The method to sort the vault data by (optional)
 * 
 * @return      Returns the total number of vault entries
 * 
 * @note        The where condition is the same as the where clause from MySQL's SELECT statement.
 *          Do not include "WHERE" inside the where clause.
 * 
 * @note        Sorting the vault data is done by passing the "ORDER BY" clause from the MySQL SELECT statement.
 *          Do not include "ORDER BY" inside the sort string
 *          
 * @note        Example for grabbing top entries:
 *          
 *          new eVaultData[10][SQLVaultEntryEx];
 *          new iVaultKeys = sqlv_read_set_ex(hVault, eVaultData, sizeof(eVaultData), _, "`key2` = 'points'", "`data` DESC");
 *          
 *          for(new i = 0; i < iVaultKeys; i++)
 *          {
 *              eVaultData[i][SQLVEx_Key1] = key1
 *              eVaultData[i][SQLVEx_Key2] = key2
 *              eVaultData[i][SQLVEx_Data] = data
 *              eVaultData[i][SQLVEx_TimeStamp] = timestamp
 *          }
 * 
 */
sqlv_read_set_ex(SQLVault:hVault, eOutputData[][SQLVaultEntryEx], iOutputSize, iStart = 0, const szWhere[] = "", const szSort[] = "")

As shown in the function description, there is an example that shows exactly how to use this.

You can also specify which data to select, and how it should be sorted.

Code:

new eVaultData[10][SQLVaultEntryEx];
new iVaultKeys = sqlv_read_set_ex(hVault, eVaultData, sizeof(eVaultData), _, "`key2` = 'points'", "`data` DESC")

new szTimeStamp[32];

for(new i = 0; i < iVaultKeys; i++)
{
    // eVaultData[i][SQLVEx_Key1] = key1
    // eVaultData[i][SQLVEx_Key2] = "points"
    // eVaultData[i][SQLVEx_Data] = data
    // eVaultData[i][SQLVEx_TimeStamp] = timestamp
    
    format_time(szTimeStamp, charsmax(szTimeStamp), "%Y-%m-%d %H:%M:%S", eVaultData[i][SQLVEx_TimeStamp]);
    
    server_print("Key1 [%s] Key2 [%s] Data [%s] TimeStamp [%s]", eVaultData[i][SQLVEx_Key1], eVaultData[i][SQLVEx_Key2], eVaultData[i][SQLVEx_Data], szTimeStamp);
}

When using these vault-specific functions, you should keep in mind that they do not in any way deal with the single key section of the vault.
Therefore, if you prune using this extension function, then the single key vault is not pruned, only this paired key section is.

Error Logging:

All error logs are NOT logged to the normal error logs, nor do they stop the plugin from running.
The logs are sent as normal AMXX logs to addons/amxmodx/logs/LYYYYMMDD.log where YYYY = year, MM = month, DD = day.

Notes:

This is only an extension and not a full vault.
Therefore, the original SQLVault include is required for this extension.

These functions do not use threaded queries.

For those who don't know, a threaded query is a query that is not executed at the time it is submitted.
Threaded queries stack up and are executed on a FIFO (first-in-first-out) basis when the SQLx module has time to execute them.
This means that the query could be executed in 0.001 seconds or 5 minutes.
The time is never determined when it is executed, only after it is done.
Because they are executed when the module allows them to, there is no stress on the server to execute queries immediately.

There is no version for this that uses threaded queries.
If there is a large enough demand for a threaded queries version, then I shall work on one.

The SQLite module is automatically loaded when this include is used.
Even if local databases are not used for this, it is still required to run.

My own test file is attached.
The functions do not have any meaning to them at all except for testing for compilation and execution.
Meaning, the results of them are ignored because I was only looking for errors.

All comments, suggestions, and bug reports are encouraged to be posted!

abdul-rehman · 01-02-2011 , 03:25 Re: [INC] SQLVault

Simply Amazing, i was waiting for something like this

GXLZPGX · 01-02-2011 , 09:26 Re: [INC] SQLVault

I just had an AMXOrgasm.

wrecked_ · 01-02-2011 , 10:13 Re: [INC] SQLVault

Quote:

Originally Posted by GXLZPGX

I just had an AMXOrgasm.

Doesn't flow well. Think harder next time.

Good job Exolent, as always.

Kreation · 01-02-2011 , 16:50 Re: [INC] SQLVault

Nice work.

fezh · 01-02-2011 , 17:58 Re: [INC] SQLVault

Quote:

Originally Posted by GXLZPGX

I just had an AMXOrgasm.

It's better to say it's just orgasmic.

hleV · 01-02-2011 , 18:42 Re: [INC] SQLVault

Cool stuff.

Exolent[jNr] · 01-02-2011 , 22:13 Re: [INC] SQLVault

Thanks for all the great feedback.

The new extended version is now posted:
https://forums.alliedmods.net/showth...46#post1384246

gladius · 01-02-2011 , 23:48 Re: [INC] SQLVault

Nice work that helpful very much to all people who don't know how to use SQL.

Mmmm I think you can do this more functionally. (something how work sorting.inc) For example: if someone makes a "top15", it is required to order.
I use this. SQL_PrepareQuery(Sql_Connection, "SELECT authid FROM `kzcl_pro15` WHERE mapname='%s' ORDER BY time LIMIT 15", mapname) bla bla bla

And why when you put something on the "vault" and it is not there, you use REPLACE instead of INSERT? is better? (I always use insert in such cases, then I want to know

)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode