Gungame Sql

#include <amxmodx>
#include <sqlx>

#define VERSION "1.04"

/***************************************
********* QUERIES
***************************************/

#define QUERY_CREATE_TABLE "CREATE TABLE IF NOT EXISTS `%s` ( \
`authid` VARCHAR( 24 ) NOT NULL, \
`wins` INT( 6 ) NOT NULL, \
`lastname` VARCHAR( 32 ) NOT NULL, \
`timestamp` INT( 12 ) NOT NULL, \
`points` INT( 8 ) NOT NULL, \
`serverip` VARCHAR( 21 ) NOT NULL, \
PRIMARY KEY ( `authid` ) );"

#define QUERY_CLEAR_TABLE "DELETE FROM `%s` WHERE `serverip` = '%s';"

#define QUERY_FIX_TABLE "REPAIR TABLE `%s`; OPTIMIZE TABLE `%s`;"

#define QUERY_INSERT_DATA "INSERT INTO `%s` ( `authid`, `wins`, `lastname`, `timestamp`, `points`, `serverip` ) \
VALUES ( '%s', '%i', '%s', '%i', '%i', '%s' );"

#define QUERY_INSERT_UPDATEFLAG "INSERT INTO `%s` ( `authid`, `wins`, `lastname`, `timestamp`, `points`, `serverip` ) \
VALUES ( 'updating', '0', '', '%i', '0', '%s' );"

#define QUERY_DELETE_UPDATEFLAG "DELETE FROM `%s` WHERE `authid` = 'updating' AND serverip = '%s';"

/***************************************
********* VARIABLES
***************************************/

// globals
new cvHost, cvUser, cvPass, cvDB, cvTable, cvRate, init, table[32], insert_error, serverip[22], queryNum, queryIter;

// mysql
new Handle:tuple, Handle:db, Handle:query, mkQuery[512], error[1024], errcode;

/***************************************
********* PLUGIN LOAD AND UNLOAD
***************************************/

public plugin_init()
{
register_plugin("GunGameSQL",VERSION,"Avalanche");
register_cvar("gg_sql_version",VERSION,FCVAR_SERVER);
set_cvar_string("gg_sql_version",VERSION);

get_user_ip(0,serverip,21);

cvHost = register_cvar("gg_sql_host","127.0.0.1",FCVAR_PROTECTED);
cvUser = register_cvar("gg_sql_user","root",FCVAR_PROTECTED);
cvPass = register_cvar("gg_sql_pass","",FCVAR_PROTECTED);
cvDB = register_cvar("gg_sql_db","amx",FCVAR_PROTECTED);
cvTable = register_cvar("gg_sql_table","gg_stats",FCVAR_PROTECTED);

cvRate = register_cvar("gg_sql_updaterate","3");

set_task(1.0,"sql_init");
set_task(2.0,"count_map_load");
}

public plugin_end()
{
sql_uninit();
}

/***************************************
********* MAIN FUNCTIONS
***************************************/

// keep track of map loads and only update database every so often
public count_map_load()
{
// could not establish connection
if(!init) return;

// get how many plugin loads more until we prune
new buffer[8], sql_update;
get_localinfo("gg_sql_update",buffer,7);
sql_update = str_to_num(buffer);

// localinfo not set yet
if(sql_update == 0)
{
// set to rate minus one
get_pcvar_string(cvRate,buffer,7);
num_to_str(str_to_num(buffer)-1,buffer,7);
set_localinfo("gg_sql_update",buffer);

return;
}

// time to update
else if(sql_update <= 1)
{
// reset our count
get_pcvar_string(cvRate,buffer,7);
set_localinfo("gg_sql_update",buffer);

update_database();

return;
}

// decrement our count
num_to_str(sql_update-1,buffer,7);
set_localinfo("gg_sql_update",buffer);

// tune-up
get_pcvar_string(cvTable,table,31);
format(mkQuery,511,QUERY_FIX_TABLE,table,table);
SQL_ThreadQuery(tuple,"sql_query_handler",mkQuery);
}

// transfer stats file to website
public update_database()
{
new sfFile[64], sfLineData[81], sfAuthid[48], sfWins[6], sfName[64], sfTimestamp[12], sfPoints[8];

get_cvar_string("gg_stats_file",sfFile,63);
trim(sfFile);

// stats disabled/file doesn't exist
if(!sfFile[0] || !file_exists(sfFile))
{
log_amx("Could not update database: stats file ^"%s^" does not exist",sfFile);
return;
}

// clear old entries
get_pcvar_string(cvTable,table,31);
format(mkQuery,511,QUERY_CLEAR_TABLE,table,serverip);

query = SQL_PrepareQuery(db,mkQuery);
SQL_Execute(query);

SQL_QueryError(query,error,1023);
if(error[0])
{
log_amx("Could not execute query ^"%s^" -- Error: %s",mkQuery,error);
SQL_FreeHandle(query);
return;
}
SQL_FreeHandle(query);

// insert update flag
formatex(mkQuery,511,QUERY_INSERT_UPDATEFLAG,table,get_systime(),serverip);

query = SQL_PrepareQuery(db,mkQuery);
SQL_Execute(query);

SQL_QueryError(query,error,1023);
if(error[0])
{
log_amx("Could not execute query ^"%s^" -- Error: %s",mkQuery,error);
SQL_FreeHandle(query);
return;
}
SQL_FreeHandle(query);

new lines, file = fopen(sfFile,"rt");
if(!file) return;

queryIter = 0;
queryNum = 0;

while(!feof(file))
{
// one of our threaded queries produced an error
if(insert_error)
{
fclose(file);
return;
}

lines++;
fgets(file,sfLineData,80);

// isolate authid
strtok(sfLineData,sfAuthid,23,sfLineData,80,'^t');

// isolate wins
strtok(sfLineData,sfWins,5,sfLineData,80,'^t');

// isolate name
strtok(sfLineData,sfName,31,sfLineData,80,'^t');

// isolate timestamp (and points)
strtok(sfLineData,sfTimestamp,11,sfPoints,7,'^t');

// clean to prevent SQL injection
replace_all(sfAuthid,47,"'","\'");
replace_all(sfName,63,"'","\'");

// put it in the database
queryNum++;
formatex(mkQuery,511,QUERY_INSERT_DATA,table,sfAuthid,str_to_num(sfWins),sfName,
str_to_num(sfTimestamp),str_to_num(sfPoints),serverip);
SQL_ThreadQuery(tuple,"sql_query_handler",mkQuery);
}

fclose(file);

if(!insert_error) log_amx("Updated the database with %i entries",lines);
}

/***************************************
********* SQL FUNCTIONS
***************************************/

// opens the database
public sql_init()
{
init = 0;

new host[32], user[32], pass[32], dbname[32];
get_pcvar_string(cvHost,host,31);
get_pcvar_string(cvUser,user,31);
get_pcvar_string(cvPass,pass,31);
get_pcvar_string(cvDB,dbname,31);

tuple = SQL_MakeDbTuple(host,user,pass,dbname);
if(tuple) db = SQL_Connect(tuple,errcode,error,1023);

if(db == Empty_Handle)
{
log_amx("Could not connect to database. Error #%i: %s",errcode,error);
return 0;
}

get_pcvar_string(cvTable,table,31);
format(mkQuery,511,QUERY_CREATE_TABLE,table);

query = SQL_PrepareQuery(db,mkQuery);
SQL_Execute(query);

SQL_QueryError(query,error,1023);
if(error[0])
{
log_amx("Could not execute query ^"%s^" -- Error: %s",mkQuery,error);
SQL_FreeHandle(query);
return 0;
}
SQL_FreeHandle(query);

init = 1;
return init;
}

// closes the database
public sql_uninit()
{
if(init)
{
if(db) SQL_FreeHandle(db);
if(tuple) SQL_FreeHandle(tuple);
}
}

// checks the query for failure
public sql_query_handler(failstate,Handle:query,error[],errnum,data[],size,Float:queuet
ime)
{
// report first error that we come across
if(failstate != TQUERY_SUCCESS && !insert_error)
{
insert_error = 1;

SQL_GetQueryString(query,mkQuery,511);
log_amx("Could not execute query ^"%s^" -- Error #%i: %s",mkQuery,errnum,error);
}

// finished queries
if(queryNum && ++queryIter >= queryNum)
{
queryIter = 0;
queryNum = 0;

set_task(1.0,"delete_update_flag");
}
}

// get rid of the update flag in our database
public delete_update_flag()
{
get_pcvar_string(cvTable,table,31);
formatex(mkQuery,511,QUERY_DELETE_UPDATEFLAG,table,serverip);

query = SQL_PrepareQuery(db,mkQuery);
SQL_Execute(query);

SQL_QueryError(query,error,1023);
if(error[0]) log_amx("Could not execute query ^"%s^" -- Error: %s",mkQuery,error);

SQL_FreeHandle(query);
}

//// gungame_sql.sma
// C:\Server Test\hlds 3 GunGame\hlds\cstrike\addons\amxmodx\scripting\gungame_s
ql.sma(265) : error 001: expected token: ")", but found "-identifier-"
// C:\Server Test\hlds 3 GunGame\hlds\cstrike\addons\amxmodx\scripting\gungame_s
ql.sma(265) : error 017: undefined symbol "ime"
// C:\Server Test\hlds 3 GunGame\hlds\cstrike\addons\amxmodx\scripting\gungame_s
ql.sma(268) : error 010: invalid function or declaration
// C:\Server Test\hlds 3 GunGame\hlds\cstrike\addons\amxmodx\scripting\gungame_s
ql.sma(272) : error 021: symbol already defined: "SQL_GetQueryString"
// C:\Server Test\hlds 3 GunGame\hlds\cstrike\addons\amxmodx\scripting\gungame_s
ql.sma(277) : error 010: invalid function or declaration
// C:\Server Test\hlds 3 GunGame\hlds\cstrike\addons\amxmodx\scripting\gungame_s
ql.sma(282) : error 021: symbol already defined: "set_task"
// C:\Server Test\hlds 3 GunGame\hlds\cstrike\addons\amxmodx\scripting\gungame_s
ql.sma(299) : warning 204: symbol is assigned a value that is never used: "query
Iter"
//
// 6 Errors.
// Could not locate output file compiled\gungame_sql.amx (compile failed).
//
// Compilation Time: 0,08 sec
// ----------------------------------------