Version 0.0.3
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: Select all
/*
* 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)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: Select all
/*
* 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)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: Select all
/*
* 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)Code: Select all
sqlv_connect(hVault);
// functions like get/set/delete/etc. go here
sqlv_disconnect(hVault);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: Select all
/*
* 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)Code: Select all
/*
* 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)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: Select all
/*
* 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)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: Select all
/*
* 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)Some functions were made to manage the whole vault.
Here are the standard ones:
Code: Select all
/*
* 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)First, this is a function to read keys 1 at a time:
Code: Select all
/*
* 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[] = "")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: Select all
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: Select all
// 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);Code: Select all
/*
* 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[] = "")You can also specify which data to select, and how it should be sorted.
Code: Select all
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);Code: Select all
/*
* 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[] = "")You can also specify which data to select, and how it should be sorted.
Code: Select all
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);
}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: Select all
// 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");
}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.