[Exolent[jNr]]

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:

new SQLVault:hVault = sqlv_open_local("test", false);

After the vault is opened, the init function specific to this extension needs to be called.

/*
 * 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:

new SQLVault:hVault = sqlv_open_local("test", false);
sqlv_init_ex(hVault);

Example to allow single and paired keys:

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.

/*
 * 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:

/*
 * 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:

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:

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:

/*
 * 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:

/*
 * 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:

/*
 * 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:

/*
 * 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:

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

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

/*
 * 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.

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.

/*
 * 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.

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!