Re: [SQLite] Combining Rows like nVault
Quote:
Originally Posted by Bugsy
(Post 2452079)
There are 40 skills that each player can have and you need to store data for? Can you explain your plugin?
|
The plugin is a heavily edited UWC3 Next Generation (RPG Mod)
Here is the SQLite Code: (You can ignore everything not related to Skills)
PHP Code:
// Uwc3ng SQLITE functions
#define TOTAL_SQLITE_TABLES 4
new const szTablesSQLite[TOTAL_SQLITE_TABLES][] = { "CREATE TABLE `uwc3ng_player` ( `player_id` INTEGER PRIMARY KEY AUTOINCREMENT, `player_steamid` varchar(25) NOT NULL default '', `player_ip` varchar(20) NOT NULL default '', `player_name` varchar(35) NOT NULL default '', `player_xp` int(16) default NULL );", "CREATE TABLE `uwc3ng_skill` ( `player_id` int(8) NOT NULL default '0', `skill_id` int(8) NOT NULL default '0', `skill_level` int(8) default NULL, PRIMARY KEY (`player_id`,`skill_id`) );", "CREATE TABLE `uwc3ng_attrib` ( `player_id` int(8) NOT NULL default '0', `attrib_id` int(8) NOT NULL default '0', `attrib_level` int(8) NOT NULL default '0', PRIMARY KEY (`player_id`,`attrib_id`) );", "CREATE TABLE `uwc3ng_resist` ( `player_id` int(8) NOT NULL default '0', `resist_id` int(8) NOT NULL default '0', `resist_level` int(8) NOT NULL default '0', PRIMARY KEY (`player_id`,`resist_id`) );" };
new const szTableNames[TOTAL_SQLITE_TABLES][] = { "uwc3ng_player", "uwc3ng_skill", "uwc3ng_attrib", "uwc3ng_resist" };
// Initiate the connection to the SQLite database public SQLITE_Init() { new szError[256], iErrNum;
// Set up the tuple that will be used for threading g_DBTuple = SQL_MakeDbTuple( "", "", "", "uwc3ng" );
// Attempt to connect g_DBConn = SQL_Connect( g_DBTuple, iErrNum, szError, 255 );
if ( !g_DBConn ) { log_amx( "[SQLITE] Database Connection Failed: [%d] %s", iErrNum, szError );
return; }
server_print( "%s SQLite database connection successful", MOD_NAME );
new Handle:query;
// Create the default tables if we need to for ( new i = 0; i < TOTAL_SQLITE_TABLES; i++ ) { query = SQL_PrepareQuery( g_DBConn, szTablesSQLite[i] );
if ( !sqlite_TableExists( g_DBConn, szTableNames[i] ) ) { if ( !SQL_Execute( query ) ) { SQLITE_Error( query, szTablesSQLite[i], 1 );
return; } }
SQL_FreeHandle( query ); }
/* These probably should be subject to a CVAR Lets fine tune the database: "synchronous = NORMAL" - Put back the 2.x behaviour (faster than the defalt for 3.x) "synchronous = OFF" - Way faster, but it might get corrupted data if a server os system crash occurs "integrity_check" - well it's what it says, we do have to check the value it returns since it's important PRAGMA commands don't return anything so no need to check the result of the query */
query = SQL_PrepareQuery( g_DBConn, "PRAGMA integrity_check" ); if ( !SQL_Execute( query ) ) { SQLITE_Error( query, "PRAGMA integrity_check", 2 );
return; } // Get the integrity check value new szIntegrityCheck[64]; if ( SQL_NumResults( query ) > 0 ) { SQL_ReadResult( query, 0, szIntegrityCheck, charsmax( szIntegrityCheck ) ); }
// Free the result SQL_FreeHandle( query );
// Check to make sure the integrity check passed if ( !equali(szIntegrityCheck, "ok") ) { // Should we disable saving here? log_amx( "[SQLITE] SQL Lite integrity check failed, disabling saving XP." );
set_pcvar_num( CVAR_uwc3ng_save_xp, 0 );
return; } // Do some synchronous crap new szQuery[128]; format( szQuery, charsmax( szQuery ), "PRAGMA synchronous = %d", SQLITE_SYNC_OFF ); query = SQL_PrepareQuery( g_DBConn, szQuery );
if ( !SQL_Execute( query ) ) { SQLITE_Error( query, szQuery, 3 );
return; } // Free the last handle SQL_FreeHandle( query ); }
// Close the SQLite connection public SQLITE_Close() { if ( g_DBTuple ) { SQL_FreeHandle( g_DBTuple ); }
if ( g_DBConn ) { SQL_FreeHandle( g_DBConn ); } }
public SQLITE_FetchUniqueID( id ) { // Make sure our connection is working if ( !SQLITE_Connection_Available() ) { return; }
new szKey[66], szKeyName[32]; Save_GetKey( id, szKey, charsmax( szKey ) ); Save_GetKeyName( szKeyName, charsmax( szKeyName ) );
new szQuery[512]; format( szQuery, charsmax( szQuery ), "SELECT `player_id` FROM `uwc3ng_player` WHERE `%s` = '%s';", szKeyName, szKey ); new Handle:query = SQL_PrepareQuery( g_DBConn, szQuery );
if ( !SQL_Execute( query ) ) { SQLITE_Error( query, szQuery, 4 );
return; } // If no rows we need to insert! if ( SQL_NumResults( query ) == 0 ) { // Free the last handle! SQL_FreeHandle( query );
// Insert this player! format( szQuery, charsmax( szQuery ), "INSERT INTO `uwc3ng_player` ( `player_id` , `%s` ) VALUES ( NULL , '%s' );", szKeyName, szKey ); query = SQL_PrepareQuery( g_DBConn, szQuery );
if ( !SQL_Execute( query ) ) { SQLITE_Error( query, szQuery, 5 );
return; }
p_data[id][PLAYER_UNIQUEID] = SQL_GetInsertId( query ); }
// They have been here before - store their ID else { p_data[id][PLAYER_UNIQUEID] = SQL_ReadResult( query, 0 ); }
// Free the last handle! SQL_FreeHandle( query ); }
public SQLITE_Save( id ) { // Make sure our connection is working if ( !SQLITE_Connection_Available() ) { return; } // Get the UniqueID new iUniqueID = SAVE_GetUniqueID( id ); new szKey[66], szKeyName[32]; Save_GetKey( id, szKey, charsmax( szKey ) ); Save_GetKeyName( szKeyName, charsmax( szKeyName ) );
// Save the user's XP! new szQuery[512]; formatex( szQuery, charsmax( szQuery ), "REPLACE INTO `uwc3ng_player` ( `player_id` , `%s` , `player_xp` ) VALUES ( '%d', '%s', '%d');", szKeyName, iUniqueID, szKey, p_data[id][PLAYER_XP] ); new Handle:query = SQL_PrepareQuery( g_DBConn, szQuery );
if ( !SQL_Execute( query ) ) { SQLITE_Error( query, szQuery, 6 );
return; }
// Now we need to save the skill levels! for ( new iSkillID = 0; iSkillID < MAX_SKILLS; iSkillID++ ) { formatex( szQuery, 511, "REPLACE INTO `uwc3ng_skill` ( `player_id` , `skill_id` , `skill_level` ) VALUES ( '%d', '%d', '%d' );", iUniqueID, iSkillID, p_data_skill[id][iSkillID] ); query = SQL_PrepareQuery( g_DBConn, szQuery );
if ( !SQL_Execute( query ) ) { SQLITE_Error( query, szQuery, 7 );
return; } } // Now we need to save the attrib levels! for ( new iAttribID = 0; iAttribID < MAX_ATTRIBS; iAttribID++ ) { formatex( szQuery, 511, "REPLACE INTO `uwc3ng_attrib` ( `player_id` , `attrib_id` , `attrib_level` ) VALUES ( '%d', '%d', '%d' );", iUniqueID, iAttribID, p_data_attrib[id][iAttribID] ); query = SQL_PrepareQuery( g_DBConn, szQuery );
if ( !SQL_Execute( query ) ) { SQLITE_Error( query, szQuery, 7 );
return; } } // Now we need to save the resist levels! for ( new iResistID = 0; iResistID < MAX_RESIST; iResistID++ ) { formatex( szQuery, 511, "REPLACE INTO `uwc3ng_resist` ( `player_id` , `resist_id` , `resist_level` ) VALUES ( '%d', '%d', '%d' );", iUniqueID, iResistID, p_data_resist[id][iResistID] ); query = SQL_PrepareQuery( g_DBConn, szQuery );
if ( !SQL_Execute( query ) ) { SQLITE_Error( query, szQuery, 7 );
return; } } return; }
public SQLITE_GetAllXP( id ) { // Make sure our connection is working if ( !SQLITE_Connection_Available() ) { return; }
// Then we have a problem and cannot retreive the user's XP if ( p_data[id][PLAYER_UNIQUEID] <= 0 ) { client_print( id, print_chat, "%s Unable to retreive your XP from the database", MOD_NAME );
log_amx( "[ERROR] Unable to retreive user's Unique ID" );
return; }
new szQuery[256]; format(szQuery, charsmax( szQuery ), "SELECT `player_xp` FROM `uwc3ng_player` WHERE ( `player_id` = '%d' );", p_data[id][PLAYER_UNIQUEID] ); new Handle:query = SQL_PrepareQuery( g_DBConn, szQuery );
if ( !SQL_Execute( query ) ) { SQLITE_Error( query, szQuery, 8 );
return; }
// Set last saved XP to 0 p_data[id][PLAYER_XP] = 0;
// Read the XPs from the table p_data[id][PLAYER_XP] = SQL_ReadResult( query, 0 ); // Free the handle SQL_FreeHandle( query ); // Select the right Table format( szQuery, charsmax( szQuery ), "SELECT `skill_id`, `skill_level` FROM `uwc3ng_skill` WHERE ( `player_id` = '%d' );", p_data[id][PLAYER_UNIQUEID] ); query = SQL_PrepareQuery( g_DBConn, szQuery );
// Check if the DB ready if ( !SQL_Execute( query ) ) { SQLITE_Error ( query, szQuery ,8 );
return; } // While we have a result! new iSkillID = 0; while ( SQL_MoreResults( query ) ) { iSkillID = SQL_ReadResult( query, 0 ); p_data_skill[id][iSkillID] = SQL_ReadResult( query, 1 );
SQL_NextRow( query ); } // Close the connection SQL_FreeHandle( query ); // Select the right Table format( szQuery, charsmax( szQuery ), "SELECT `attrib_id`, `attrib_level` FROM `uwc3ng_attrib` WHERE ( `player_id` = '%d' );", p_data[id][PLAYER_UNIQUEID] ); query = SQL_PrepareQuery( g_DBConn, szQuery );
// Check if the DB ready if ( !SQL_Execute( query ) ) { SQLITE_Error ( query, szQuery, 8 );
return; } // While we have a result! new iAttribID = 0; while ( SQL_MoreResults( query ) ) { iAttribID = SQL_ReadResult( query, 0 ); p_data_attrib[id][iAttribID] = SQL_ReadResult( query, 1 );
SQL_NextRow( query ); } // Close the connection SQL_FreeHandle( query ); // Select the right Table format( szQuery, charsmax( szQuery ), "SELECT `resist_id`, `resist_level` FROM `uwc3ng_resist` WHERE ( `player_id` = '%d' );", p_data[id][PLAYER_UNIQUEID] ); query = SQL_PrepareQuery( g_DBConn, szQuery );
// Check if the DB ready if ( !SQL_Execute( query ) ) { SQLITE_Error ( query, szQuery, 8 );
return; } // While we have a result! new iResistID = 0; while ( SQL_MoreResults( query ) ) { iResistID = SQL_ReadResult( query, 0 ); p_data_resist[id][iResistID] = SQL_ReadResult( query, 1 );
SQL_NextRow( query ); } // Close the connection SQL_FreeHandle( query );
return; }
// Verifies that the database connection is ok bool:SQLITE_Connection_Available() { if ( !g_DBConn ) { return false; }
return true; }
// The id should be a unique number, so we know what function called it (useful for debugging) SQLITE_Error( Handle:query, szQuery[], id ) { new szError[256]; new iErrNum = SQL_QueryError( query, szError, 255 );
log_amx( "[SQLITE] Error in querying database, location: %d", id ); log_amx( "[SQLITE] Message: %s (%d)", szError, iErrNum ); log_amx( "[SQLITE] Query statement: %s ", szQuery );
// Free the handle SQL_FreeHandle( query ); } /* AMXX-Studio Notes - DO NOT MODIFY BELOW HERE *{\\ rtf1\\ ansi\\ deff0{\\ fonttbl{\\ f0\\ fnil Tahoma;}}\n\\ viewkind4\\ uc1\\ pard\\ lang1031\\ f0\\ fs16 \n\\ par } */
|