new Handle:h_db;
new ShopDBType:db_type;
new Handle:backup_dp;
new iDays = -1;
DB_CreateNatives()
{
CreateNative("Shop_GetDatabase", DB_GetDatabase);
CreateNative("Shop_GetDatabasePrefix", DB_GetDatabasePrefix);
CreateNative("Shop_GetDatabaseType", DB_GetDatabaseType);
}
public DB_GetDatabase(Handle:plugin, numParams)
{
return _:CloneHandle(h_db, plugin);
}
public DB_GetDatabasePrefix(Handle:plugin, numParams)
{
new bytes;
SetNativeString(1, g_sDbPrefix, GetNativeCell(2), false, bytes);
return bytes;
}
public DB_GetDatabaseType(Handle:plugin, numParams)
{
return _:db_type;
}
DB_OnPluginStart()
{
RegServerCmd("sm_shop_clear_db", DB_Command_Clear, "Clears database");
backup_dp = CreateDataPack();
DB_TryConnect();
}
DB_OnSettingsLoad(Handle:kv)
{
KvGetString(kv, "db_prefix", g_sDbPrefix, sizeof(g_sDbPrefix), "shop_");
TrimString(g_sDbPrefix);
}
DB_OnMapStart()
{
iDays = -1;
}
new num_rows;
public Action:DB_Command_Clear(argc)
{
if (h_db == INVALID_HANDLE || !IsStarted())
{
PrintToServer("[Shop] Database is not ready! Try again later");
DB_TryConnect();
return Plugin_Handled;
}
if (argc == 0 && iDays == -1)
{
iDays = 0;
PrintToServer("[Shop] Full database clear. Type sm_shop_clear_db 'ok' to process the query or 'deny' to cancel the process!");
}
else
{
decl String:sDays[8];
GetCmdArg(1, sDays, sizeof(sDays));
if (iDays != -1)
{
if (StrEqual(sDays, "ok", false))
{
decl String:s_Query[128];
if (iDays == 0)
{
if (db_type == DB_MySQL)
{
FormatEx(s_Query, sizeof(s_Query), "TRUNCATE TABLE `%sboughts`;", g_sDbPrefix);
DB_TQuery(DB_Clear, s_Query, iDays);
num_rows++;
FormatEx(s_Query, sizeof(s_Query), "TRUNCATE TABLE `%splayers`;", g_sDbPrefix);
DB_TQuery(DB_Clear, s_Query, iDays);
num_rows++;
}
else
{
FormatEx(s_Query, sizeof(s_Query), "DELETE FROM `%sboughts`;", g_sDbPrefix);
DB_TQuery(DB_Clear, s_Query, iDays);
num_rows++;
FormatEx(s_Query, sizeof(s_Query), "DELETE FROM `%splayers`;", g_sDbPrefix);
DB_TQuery(DB_Clear, s_Query, iDays);
num_rows++;
}
PrintToServer("[Shop] Full clearing database...");
}
else
{
FormatEx(s_Query, sizeof(s_Query), "SELECT `id` FROM `%splayers` WHERE %d - `lastconnect` > %d;", g_sDbPrefix, global_timer, iDays*86400);
DB_TQuery(DB_Clear, s_Query, iDays);
num_rows++;
PrintToServer("[Shop] Clearing database from inactive players for more than %d %s!", iDays, (iDays == 1) ? "day" : "days");
}
iDays = -1;
}
else if (StrEqual(sDays, "deny", false))
{
iDays = -1;
PrintToServer("[Shop] Database clear denied!");
}
else
{
if (iDays == 0)
{
PrintToServer("[Shop] Current clearing option is set to - Full database clear");
PrintToServer("[Shop] This will also delete all data of players that are currently on the server");
}
else
{
PrintToServer("[Shop] Current clearing option is set to - Clear from inactive players for more than %d %s!", iDays, (iDays == 1) ? "day" : "days");
}
PrintToServer("[Shop] Type sm_shop_clear_db 'ok' to process the query or 'deny' to cancel the process!");
}
return Plugin_Handled;
}
new days = StringToInt(sDays);
if (days < 1)
{
PrintToServer("[Shop] Days can not be less than 1!");
return Plugin_Handled;
}
iDays = days;
PrintToServer("[Shop] Database clear from players that are inactive for more than %d %s!", iDays, (iDays == 1) ? "day" : "days");
PrintToServer("[Shop] Type sm_shop_clear_db 'ok' to process the query or 'deny' to cancel the process!");
}
return Plugin_Handled;
}
public DB_Clear(Handle:owner, Handle:hndl, const String:error[], any:data)
{
if (num_rows) num_rows--;
if (error[0])
{
LogError("DB_Clear %d: %s", data, error);
return;
}
if (!num_rows)
{
switch (data)
{
case -1 :
{
PrintToServer("[Shop] Database is cleared!");
return;
}
case 0 :
{
DatabaseClear();
PrintToServer("[Shop] Database is fully cleared!");
return;
}
}
if (SQL_GetRowCount(hndl) > 0)
{
decl String:s_Query[128], id;
while (SQL_FetchRow(hndl))
{
id = SQL_FetchInt(hndl, 0);
if (!IsInGame(id))
{
FormatEx(s_Query, sizeof(s_Query), "DELETE FROM `%sboughts` WHERE `player_id` = '%d';", g_sDbPrefix, id);
DB_TQuery(DB_Clear, s_Query, -1);
num_rows++;
FormatEx(s_Query, sizeof(s_Query), "DELETE FROM `%splayers` WHERE `id` = '%d';", g_sDbPrefix, id);
DB_TQuery(DB_Clear, s_Query, -1);
num_rows++;
}
}
}
else
{
PrintToServer("[Shop] Database is already clear from inactive players for more than %d %s!", data, (data == 1) ? "day" : "days");
}
}
}
new bool:isLoading;
DB_TryConnect()
{
if (isLoading)
{
return;
}
if (h_db != INVALID_HANDLE)
{
isLoading = false;
return;
}
isLoading = true;
PrintToServer("[Shop] Trying to connect!");
if (SQL_CheckConfig("shop"))
{
SQL_TConnect(DB_Connect, "shop", 1);
}
else
{
decl String:error[256];
error[0] = '\0';
h_db = SQLite_UseDatabase("shop", error, sizeof(error));
DB_Connect(h_db, h_db, error, 2);
}
}
public Action:DB_ReconnectTimer(Handle:timer)
{
if (h_db == INVALID_HANDLE)
{
DB_TryConnect();
}
}
new Handle:upgrade_dp;
new Handle:insert_dp;
public DB_Connect(Handle:owner, Handle:hndl, const String:error[], any:data)
{
h_db = hndl;
if (h_db == INVALID_HANDLE)
{
LogError("DB_Connect %d: %s", data, error);
CreateTimer(15.0, DB_ReconnectTimer);
isLoading = false;
return;
}
if (error[0])
{
LogError("DB_Connect %d: %s", data, error);
}
decl String:driver[16];
switch (data)
{
case 1 :
{
SQL_GetDriverIdent(owner, driver, sizeof(driver));
}
default :
{
SQL_ReadDriver(owner, driver, sizeof(driver));
}
}
if (StrEqual(driver, "mysql", false))
{
db_type = DB_MySQL;
}
else if (StrEqual(driver, "sqlite", false))
{
db_type = DB_SQLite;
}
else
{
SetFailState("DB_Connect: Driver \"%s\" is not supported!", driver);
}
decl String:s_Query[256];
if (db_type == DB_MySQL)
{
DB_TQueryEx("SET NAMES 'utf8'");
DB_TQueryEx("SET CHARSET 'utf8'");
DB_TQuery(DB_GlobalTimer, "SELECT UNIX_TIMESTAMP()", _, DBPrio_High);
FormatEx(s_Query, sizeof(s_Query), "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '%sboughts';", g_sDbPrefix);
DB_TQuery(DB_CheckTable, s_Query);
}
else
{
global_timer = GetTime();
FormatEx(s_Query, sizeof(s_Query), "PRAGMA TABLE_INFO(%sboughts);", g_sDbPrefix);
DB_TQuery(DB_CheckTable, s_Query);
}
}
public DB_GlobalTimer(Handle:owner, Handle:hndl, const String:error[], any:data)
{
if (error[0])
{
LogError("DB_GlobalTimer: %s", error);
}
if (hndl == INVALID_HANDLE || !SQL_HasResultSet(hndl))
{
DB_TQuery(DB_GlobalTimer, "SELECT UNIX_TIMESTAMP()");
return;
}
SQL_FetchRow(hndl);
global_timer = SQL_FetchInt(hndl, 0);
}
public DB_CheckTable(Handle:owner, Handle:hndl, const String:error[], any:data)
{
if (error[0])
{
LogError("DB_CheckTable: %s", error);
CloseHandle(h_db);
h_db = INVALID_HANDLE;
CreateTimer(15.0, DB_ReconnectTimer);
isLoading = false;
return;
}
decl String:s_Query[256];
if (db_type == DB_MySQL)
{
if (!SQL_HasResultSet(hndl) || !SQL_FetchRow(hndl) || SQL_FetchInt(hndl, 0) < 1)
{
FormatEx(s_Query, sizeof(s_Query), "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '%sitems';", g_sDbPrefix);
DB_TQuery(DB_CheckTable2, s_Query);
return;
}
}
else if (!SQL_GetRowCount(hndl))
{
FormatEx(s_Query, sizeof(s_Query), "PRAGMA TABLE_INFO(%sitems);", g_sDbPrefix);
DB_TQuery(DB_CheckTable2, s_Query);
return;
}
DB_RunBackup();
OnReadyToStart();
}
public DB_CheckTable2(Handle:owner, Handle:hndl, const String:error[], any:data)
{
if (error[0])
{
LogError("DB_CheckTable2: %s", error);
CloseHandle(h_db);
h_db = INVALID_HANDLE;
CreateTimer(15.0, DB_ReconnectTimer);
isLoading = false;
return;
}
if (SQL_GetRowCount(hndl) > 0)
{
if (db_type == DB_MySQL)
{
if (SQL_FetchRow(hndl) && SQL_FetchInt(hndl, 0) > 0)
{
DB_UpgradeToNewVersion();
return;
}
}
else
{
DB_UpgradeToNewVersion();
return;
}
}
DB_CreateTables();
}
DB_CreateTables()
{
decl String:s_Query[512];
if (db_type == DB_MySQL)
{
FormatEx(s_Query, sizeof(s_Query), "CREATE TABLE IF NOT EXISTS `%sboughts` (\
`player_id` int(5) NOT NULL,\
`item_id` int(5) NOT NULL,\
`count` int(4) NOT NULL,\
`duration` int(8) NOT NULL,\
`timeleft` int(8) NOT NULL,\
`buy_price` int(5) NOT NULL,\
`sell_price` int(5) NOT NULL,\
`buy_time` int(10)\
) ENGINE=InnoDB DEFAULT CHARSET=utf8;", g_sDbPrefix);
DB_TQuery(DB_OnPlayersTableLoad, s_Query, 1);
FormatEx(s_Query, sizeof(s_Query), "CREATE TABLE IF NOT EXISTS `%sitems` (\
`id` int(5) NOT NULL AUTO_INCREMENT,\
`category` varchar(64) NOT NULL,\
`item` varchar(64) NOT NULL,\
PRIMARY KEY (`id`)\
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;", g_sDbPrefix);
DB_TQuery(DB_OnPlayersTableLoad, s_Query, 2);
FormatEx(s_Query, sizeof(s_Query), "CREATE TABLE IF NOT EXISTS `%splayers` (\
`id` int(5) NOT NULL AUTO_INCREMENT,\
`name` varchar(32) NOT NULL DEFAULT 'unknown',\
`auth` varchar(22) NOT NULL,\
`money` int(12) NOT NULL,\
`lastconnect` int(10),\
PRIMARY KEY (`id`), \
UNIQUE KEY `auth` (`auth`) \
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;", g_sDbPrefix);
DB_TQuery(DB_OnPlayersTableLoad, s_Query, 3);
}
else
{
FormatEx(s_Query, sizeof(s_Query), "CREATE TABLE IF NOT EXISTS `%sboughts` (\
`player_id` NUMERIC NOT NULL,\
`item_id` INTEGER NOT NULL,\
`count` NUMERIC NOT NULL,\
`duration` INTEGER NOT NULL,\
`timeleft` NUMERIC NOT NULL,\
`buy_price` INTEGER NOT NULL,\
`sell_price` NUMERIC NOT NULL,\
`buy_time` NUMERIC NOT NULL);", g_sDbPrefix);
DB_TQuery(DB_OnPlayersTableLoad, s_Query, 1);
FormatEx(s_Query, sizeof(s_Query), "CREATE TABLE IF NOT EXISTS `%sitems` (\
`id` INTEGER PRIMARY KEY AUTOINCREMENT,\
`category` VARCHAR NOT NULL,\
`item` VARCHAR NOT NULL);", g_sDbPrefix);
DB_TQuery(DB_OnPlayersTableLoad, s_Query, 2);
FormatEx(s_Query, sizeof(s_Query), "CREATE TABLE IF NOT EXISTS `%splayers` (\
`id` INTEGER PRIMARY KEY AUTOINCREMENT,\
`name` VARCHAR DEFAULT 'unknown',\
`auth` VARCHAR UNIQUE ON CONFLICT IGNORE,\
`money` NUMERIC DEFAULT '0',\
`lastconnect` INTEGER NOT NULL);", g_sDbPrefix);
DB_TQuery(DB_OnPlayersTableLoad, s_Query, 3);
}
isLoading = false;
}
public DB_OnPlayersTableLoad(Handle:owner, Handle:hndl, const String:error[], any:data)
{
if (error[0])
{
LogError("DB_OnPlayersTableLoad %d: %s", data, error);
CloseHandle(h_db);
h_db = INVALID_HANDLE;
CreateTimer(15.0, DB_ReconnectTimer);
return;
}
if (data != 3)
{
return;
}
if (db_type == DB_MySQL)
{
DB_TQueryEx("SET NAMES 'utf8'");
DB_TQueryEx("SET CHARSET 'utf8'");
}
if (upgrade_dp != INVALID_HANDLE)
{
DB_RunUpgrade();
}
else
{
OnReadyToStart();
}
}
stock DB_FastQuery(const String:query[])
{
if (h_db == INVALID_HANDLE)
{
return;
}
SQL_LockDatabase(h_db);
SQL_FastQuery(h_db, query);
SQL_UnlockDatabase(h_db);
}
DB_TQuery(SQLTCallback:callback, const String:query[], any:data = 0, DBPriority:prio = DBPrio_Normal)
{
if (h_db == INVALID_HANDLE)
{
new Handle:dp = CreateDataPack();
WritePackCell(dp, _:callback);
WritePackString(dp, query);
WritePackCell(dp, data);
WritePackCell(dp, _:prio);
WritePackCell(backup_dp, _:dp);
return;
}
SQL_TQuery(h_db, callback, query, data, prio);
}
DB_TQueryEx(const String:query[], DBPriority:prio = DBPrio_Normal)
{
if (h_db == INVALID_HANDLE)
{
new Handle:dp = CreateDataPack();
WritePackCell(dp, _:DB_ErrorCheck);
WritePackString(dp, query);
WritePackCell(dp, 0);
WritePackCell(dp, _:prio);
WritePackCell(backup_dp, _:dp);
return;
}
SQL_TQuery(h_db, DB_ErrorCheck, query, _, prio);
}
DB_EscapeString(const String:string[], String:buffer[], maxlength, &written=0)
{
SQL_EscapeString(h_db, string, buffer, maxlength, written);
}
DB_RunBackup()
{
decl String:buffer[256], Handle:dp, SQLTCallback:callback, any:data, DBPriority:prio;
ResetPack(backup_dp);
while (IsPackReadable(backup_dp, 1))
{
dp = Handle:ReadPackCell(backup_dp);
callback = SQLTCallback:ReadPackCell(dp);
ReadPackString(dp, buffer, sizeof(buffer));
data = ReadPackCell(dp);
prio = DBPriority:ReadPackCell(dp);
CloseHandle(dp);
DB_TQuery(callback, buffer, data, prio);
}
ResetPack(backup_dp, true);
}
stock bool:DB_IsConnected()
{
return (h_db != INVALID_HANDLE);
}
public DB_ErrorCheck(Handle:owner, Handle:hndl, const String:error[], any:data)
{
if (error[0])
{
LogError("DB_ErrorCheck: %s", error);
}
}
DB_UpgradeToNewVersion()
{
PrintToServer("[Shop] Started upgrading to version 2!");
decl String:s_Query[256];
if (db_type == DB_MySQL)
{
FormatEx(s_Query, sizeof(s_Query), "ALTER IGNORE TABLE `%splayers` ADD `lastconnect` int(10) NOT NULL DEFAULT '0';", g_sDbPrefix);
}
else
{
FormatEx(s_Query, sizeof(s_Query), "ALTER TABLE `%splayers` ADD `lastconnect` NUMERIC NOT NULL DEFAULT '0'", g_sDbPrefix);
}
DB_TQueryEx(s_Query);
FormatEx(s_Query, sizeof(s_Query), "SELECT * FROM `%sitems`;", g_sDbPrefix);
DB_TQuery(DB_UgradeState_1, s_Query);
}
public DB_UgradeState_1(Handle:owner, Handle:hndl, const String:error[], any:data)
{
if (error[0])
{
LogError("DB_UgradeState_1: %s", error);
DB_CreateTables();
return;
}
PrintToServer("[Shop] Reading old tables...");
upgrade_dp = CreateDataPack();
insert_dp = CreateDataPack();
new bool:got_categories;
decl String:category[64], String:item[64], String:buffer[2048], id, String:part[128];
while (SQL_FetchRow(hndl))
{
id = SQL_FetchInt(hndl, 0);
for (new i = 1; i < SQL_GetFieldCount(hndl); i++)
{
SQL_FieldNumToName(hndl, i, category, sizeof(category));
if (!got_categories)
{
SQL_LockDatabase(h_db);
FormatEx(buffer, sizeof(buffer), "SELECT `item` FROM `%s`;", category);
new Handle:hQuery = SQL_Query(h_db, buffer);
if (hQuery != INVALID_HANDLE)
{
while (SQL_FetchRow(hQuery))
{
SQL_FetchString(hQuery, 0, item, sizeof(item));
FormatEx(buffer, sizeof(buffer), "INSERT INTO `%sitems` (`category`, `item`) VALUES ('%s', '%s');", g_sDbPrefix, category, item);
WritePackString(insert_dp, buffer);
}
CloseHandle(hQuery);
}
SQL_UnlockDatabase(h_db);
}
SQL_FetchString(hndl, i, buffer, sizeof(buffer));
new num, itemId[256], count[256], duration[256], item_id;
new reloc_idx = 0, var2 = 0;
while ((var2 = SplitString(buffer[reloc_idx], ",", part, sizeof(part))) != -1)
{
reloc_idx += var2;
if (!part[0]) continue;
new ture = FindCharInString(part, '-');
if (ture != -1)
{
ture++;
strcopy(item, ture, part);
item_id = StringToInt(item);
duration[item_id] = StringToInt(part[ture]);
}
else
{
item_id = StringToInt(part);
}
if (count[item_id] == 0)
{
itemId[num++] = item_id;
}
count[item_id]++;
}
for (new x = 0; x < num; x++)
{
FormatEx(buffer, sizeof(buffer), "INSERT INTO `%sboughts` (`player_id`, `item_id`, `count`, `duration`, `timeleft`, `buy_price`, `sell_price`, `buy_time`) VALUES \
('%d', (SELECT `id` FROM `%sitems` WHERE `category` = '%s' AND `item` = (SELECT `item` FROM `%s` WHERE `id` = '%d')), '%d', '%d', '%d', '0', '-1', '%d');",
g_sDbPrefix, id, g_sDbPrefix, category, category, itemId[x], count[itemId[x]], duration[itemId[x]], duration[itemId[x]], global_timer);
WritePackString(upgrade_dp, buffer);
}
}
got_categories = true;
}
FormatEx(buffer, sizeof(buffer), "DROP TABLE `%sitems`;", g_sDbPrefix);
DB_TQueryEx(buffer, DBPrio_High);
DB_CreateTables();
}
new num_queries;
DB_RunUpgrade()
{
PrintToServer("[Shop] Running queries...");
decl String:buffer[256];
ResetPack(insert_dp);
while (IsPackReadable(insert_dp, 1))
{
ReadPackString(insert_dp, buffer, sizeof(buffer));
DB_TQuery(DB_UgradeState_2, buffer);
num_queries++;
}
CloseHandle(insert_dp);
insert_dp = INVALID_HANDLE;
}
new num_queries2;
public DB_UgradeState_2(Handle:owner, Handle:hndl, const String:error[], any:data)
{
num_queries--;
if (error[0])
{
LogError("DB_UgradeState_2: %s", error);
}
if (num_queries > 0)
{
return;
}
decl String:buffer[512];
ResetPack(upgrade_dp);
while (IsPackReadable(upgrade_dp, 1))
{
ReadPackString(upgrade_dp, buffer, sizeof(buffer));
DB_TQuery(DB_UgradeState_3, buffer, DBPrio_High);
num_queries2++;
}
CloseHandle(upgrade_dp);
upgrade_dp = INVALID_HANDLE;
}
public DB_UgradeState_3(Handle:owner, Handle:hndl, const String:error[], any:data)
{
num_queries2--;
if (error[0])
{
LogError("DB_UgradeState_3: %s", error);
}
if (num_queries2 > 0)
{
return;
}
PrintToServer("[Shop] Upgrade to version 2 completed!");
OnReadyToStart();
}