Squirrel/Examples/SQLite
(Updated the tutorial) |
m |
||
Line 42: | Line 42: | ||
Next the database needs a table to store the data. ''Note'' that this only has to be done once, after that the table will stay in the database until it is removed by the user. | Next the database needs a table to store the data. ''Note'' that this only has to be done once, after that the table will stay in the database until it is removed by the user. | ||
− | To execute commands, for example create tables, read/write data, we need to query the database with the wanted command. In SQL new tables are created with the command 'CREATE TABLE'. The server function for querying the database is ''[[Squirrel/Server/Functions/SQLite/sqlite_query|sqlite_query]]'', which accepts the earlier retrieved database pointer and the query as a string as its parameters. Remember that the query pointer should always be freed after processing using ''[[Squirrel/Server/Functions/SQLite/sqlite_free]]'' to prevent possible memory leaks. | + | To execute commands, for example create tables, read/write data, we need to query the database with the wanted command. In SQL new tables are created with the command 'CREATE TABLE'. The server function for querying the database is ''[[Squirrel/Server/Functions/SQLite/sqlite_query|sqlite_query]]'', which accepts the earlier retrieved database pointer and the query as a string as its parameters. Remember that the query pointer should always be freed after processing using ''[[Squirrel/Server/Functions/SQLite/sqlite_free|sqlite_free]]'' to prevent possible memory leaks. |
This example creates a table called Stats, which contains player name (string, length may vary), their kills and deaths and the number of times they have joined the server (integers). | This example creates a table called Stats, which contains player name (string, length may vary), their kills and deaths and the number of times they have joined the server (integers). |
Latest revision as of 02:07, 16 October 2010
This is a basic tutorial on how to use the built-in SQLite functions. The tutorial also introduces a simple script which implements an SQLite database to save the stats of your server. Note that SQLite functions require the external module lu_sqlite.
Contents |
[edit] Creating/restoring a database
First thing to do is to load/create a new SQLite database. This is done with the function sqlite_open. It creates a new database and saves it using the provided filename; if the specified file exists, the function attempts to load the database from it and use it.
Our example script loads or creates a database for the statistics script. It loads the player data on join and saves it when they leave.
function onScriptLoad() { // Load the module which provides SQLite functions LoadModule( "lu_sqlite" ); // Create/load the database and store it to a global variable 'statDB' statDB <- sqlite_open( "serverstats.sqlite" ); // Create an array to store the stats for every player while they are ingame stats <- array( GetMaxPlayers(), null ); return 1; }
For future usage we are saving the database to a global variable. We will also create an array which will contain player data while they are ingame, in order to prevent unnecessary querying of the database.
To save player stats into the array, we need a table or a class. This example creates a class which contains the basic statistics for one player. The objects of this class will be saved into the array created earlier. For more info about Squirrel classes, see the Squirrel reference manual.
class PlayerStats { Kills = 0; Deaths = 0; Joins = 0; PreviousData = false; // This variable will tell us later whether the player has any previous stats saved in the table }
[edit] Creating a table
Next the database needs a table to store the data. Note that this only has to be done once, after that the table will stay in the database until it is removed by the user.
To execute commands, for example create tables, read/write data, we need to query the database with the wanted command. In SQL new tables are created with the command 'CREATE TABLE'. The server function for querying the database is sqlite_query, which accepts the earlier retrieved database pointer and the query as a string as its parameters. Remember that the query pointer should always be freed after processing using sqlite_free to prevent possible memory leaks.
This example creates a table called Stats, which contains player name (string, length may vary), their kills and deaths and the number of times they have joined the server (integers).
sqlite_query( statDB, "CREATE TABLE Stats ( Name VARCHAR(32), Kills INT, Deaths INT, Joins INT )" );
Since this only has to be done once, it could be added for example as a console input command, and executed when the server is started for the first time.
Also note that 'CREATE TABLE' doesn't return any value, thus sqlite_query will return null.
[edit] Returning data from a table
When a player joins the server, their previously stored stats should be loaded from the database. In this example the stats are temporarily stored into an object related to the player, loaded from the database on join and stored back on part.
When querying the database with a query that returns data, the function sqlite_query returns an object which contains the query result: all the rows and columns. To retrieve the info from this object we will have iterate through the result table rows and fetch the wanted data from each column.
Iterating through the rows is done with sqlite_next_row function. The function takes the query result object and modifies it so that the next row of the result is available. Then you can pick the row, get its column count with sqlite_column_count and fetch the data in a specific column with sqlite_column_data. sqlite_column_count takes the query result object as a parameter. sqlite_column_count accepts the query result object and the index of the wanted column as an integer.
Let's assume we have the following table saved to our database:
------------------------------------- | ~ Stats ~ | ------------------------------------- | Name | Kills | Deaths | Joins | |------------------------------------ | player1 | 23 | 12 | 14 | | player2 | 123 | 377 | 93 | | player3 | 99 | 999 | 99 | | someone | 78 | 34 | 7 | | spaner | 1337 | 429 | 152 | | tammer | 38 | 3482 | 392 | -------------------------------------
If we want to retrieve the kills for players whose nickname is 'Player*', we would query the server with "SELECT Name, Kills FROM Stats WHERE Name LIKE 'player%'". The result object from QuerySQL would then contain the following table:
|------------------- | player1 | 23 | | player2 | 123 | | player3 | 99 | --------------------
Since we asked for two columns (player name and kills), sqlite_column_count( q ), where q is the query result from sqlite_query, would return 2. To get the results, we need to iterate through the rows using GetSQLNextRow. The following script shows how to print the player names and their kill amounts on the server console:
local q = sqlite_query( database, "SELECT Name, Kills FROM Stats WHERE Name LIKE 'player%'" ); print( "Result columns: " + sqlite_column_count( q ) ); print( "\nPlayer name - Kills" ); // Iterate the rows in a loop until there are no more rows (column data = null) while ( sqlite_column_data( q, 0 ) != null ) { // Print the data in the first (0) and second (1) column of the current row print( sqlite_column_data( q, 0 ) + " - " + GetSQLColumnData( q, 1 ) ); // Read the next row available q = sqlite_next_row( q ); }
The output would be:
SCRIPT: Result columns: 2 SCRIPT: Player name - Kills SCRIPT: player1 - 23 SCRIPT: player2 - 123 SCRIPT: player3 - 99
Note that sqlite_column_data converts the data automatically to either integer, float or string depending in which format the data had been saved.
Also note, thats it's important to iterate through all the rows even though you would know how many lines you want to get, because the result object will auto-destroy itself after it has returned all the rows. If this is not done, the memory slot reserved for the result will stay reserved and eventually the server will use more memory than it would need.
For our example script the reading happens when a player joins the server. The query "SELECT Kills, Deaths, Joins FROM Stats WHERE Name=<player name here>" will return player's kills, deaths and joins in a single row respectively.
function onPlayerJoin( player ) { // Create a temporal stat object to store the player stats while they're online local id = player.ID; stats[ player.ID ] = PlayerStats(); // Convert player name to lower case; that way we can ignore the case local name = player.Name.tolower(); local q = sqlite_query( statDB, "SELECT Kills, Deaths, Joins FROM stats WHERE Name='" + name + "'" ); // If the player had previous stats saved (ie. there's value in the first column of the first row) if ( sqlite_column_data( q, 0 ) != null ) { // Remember that player had previous stats in the database stats[ id ].PreviousData = true; // Fetch the asked values from the columns of the returned row stats[ id ].Kills = sqlite_column_data( q, 0 ); stats[ id ].Deaths = sqlite_column_data( q, 1 ); stats[ id ].Joins = sqlite_column_data( q, 2 ); } // Free the query using sqlite_free sqlite_free( q ); // Increase player joins stats[ id ].Joins++; return 1; }
[edit] Saving data to a table
When the player leaves we need to save their stats into the database. This can be done with the 'INSERT INTO' command if player has no previous stats, or with 'UPDATE' if the database contains any previously saved data.
function onPlayerPart( player, partID ) { // Load the player stats from the array + lowercase name local id = player.ID, name = player.Name.tolower(); // If player had any data in the database, update the old stats if ( stats[ id ].PreviousData ) { // Format the query string using 'format' local query = format( "UPDATE Stats SET Kills=%i, Deaths=%i, Joins=%i WHERE Name='%s'", stats[ id ].Kills, stats[ id ].Deaths, stats[ id ].Joins, name ); // Then execute the query sqlite_query( statDB, query ) } // Else let's just make a new entry else { local query = format( "INSERT INTO Stats (Name, Kills, Deaths, Joins) VALUES ('%s', %i, %i, %i)", name, stats[ id ].Kills, stats[ id ].Deaths, stats[ id ].Joins ); sqlite_query( statDB, query ); } // Reset the stat array slot stats[ id ] = null; }
[edit] More about SQL language
This example is meant to introduce the usage of the built-in SQLite commands, not the query language itself. There are a lot of SQL related tutorials and documentations available everywhere. You can also check the following links for more details and for a list of commands/functions.
[edit] Complete example script
///////////////////////////// //// SQLite Stats Example /// ///////////////////////////// //-------------------------------------------------- function onScriptLoad() { // Load the module which provides SQLite functions LoadModule( "lu_sqlite" ); // Create/load the database and store it to a global variable 'statDB' statDB <- sqlite_open( "serverstats.sqlite" ); // Create an array to store the stats for every player while they are ingame stats <- array( GetMaxPlayers(), null ); print( "=== Loaded SQLite Stats Example ===" ); return 1; } //-------------------------------------------------- /* The class to store player stats */ class PlayerStats { Kills = 0; Deaths = 0; Joins = 0; PreviousData = false; } //-------------------------------------------------- /* A console command to initialize a table for the stats */ function onConsoleInput( cmd, text ) { if ( cmd == "createtable" ) { sqlite_query( statDB, "CREATE TABLE Stats ( Name VARCHAR(32), Kills INT, Deaths INT, Joins INT )" ); } return 1; } //-------------------------------------------------- function onPlayerJoin( player ) { // Create a temporal stat object to store the player stats while they're online local id = player.ID; stats[ player.ID ] = PlayerStats(); // Convert player name to lower case; that way we can ignore the case local name = player.Name.tolower(); local q = sqlite_query( statDB, "SELECT Kills, Deaths, Joins FROM stats WHERE Name='" + name + "'" ); // If the player had previous stats saved (ie. there's value in the first column of the first row) if ( sqlite_column_data( q, 0 ) != null ) { // Remember that player had previous stats in the database stats[ id ].PreviousData = true; // Fetch the asked values from the columns of the returned row stats[ id ].Kills = sqlite_column_data( q, 0 ); stats[ id ].Deaths = sqlite_column_data( q, 1 ); stats[ id ].Joins = sqlite_column_data( q, 2 ); } // Finalize query result sqlite_free( q ); // Increase player joins stats[ id ].Joins++; return 1; } //-------------------------------------------------- /* Save player stats on part */ function onPlayerPart( player, partID ) { // Load the player stats from the array + lowercase name local id = player.ID, name = player.Name.tolower(); // If player had any data in the database, update the old stats if ( stats[ id ].PreviousData ) { // Format the query string using 'format' local query = format( "UPDATE Stats SET Kills=%i, Deaths=%i, Joins=%i WHERE Name='%s'", stats[ id ].Kills, stats[ id ].Deaths, stats[ id ].Joins, name ); // Then execute the query sqlite_query( statDB, query ) } // Else let's just make a new entry else { local query = format( "INSERT INTO Stats (Name, Kills, Deaths, Joins) VALUES ('%s', %i, %i, %i)", name, stats[ id ].Kills, stats[ id ].Deaths, stats[ id ].Joins ); sqlite_query( statDB, query ); } // Reset the stat array slot stats[ id ] = null; return 1; } //-------------------------------------------------- /* These events will affect the player stats, so we need to grab the changes and temporarily save them into the array */ function onPlayerKill( killer, player, weapon ) { stats[ killer.ID ].Kills++; stats[ player.ID ].Deaths++; return 1; } function onPlayerDeath( player, reason ) { stats[ player.ID ].Deaths++; return 1; } //-------------------------------------------------- /* A basic /c stats command */ function onPlayerCommand( player, command, text ) { if ( command == "stats" ) { if ( text ) { local plr = FindPlayer( text ); if ( !plr ) MessagePlayer( "Player " + text + " is not online." ); else { local plrStats = stats[ plr.ID ]; MessagePlayer( plr.Name + "'s stats:", player ); MessagePlayer( "Kills: " + plrStats.Kills + ", Deaths: " + plrStats.Deaths, player ); MessagePlayer( "Joins: " + plrStats.Joins, player ); } } else MessagePlayer( "Usage: /stats <player>", player ); } return 1; }
[edit] Related functions
These functions are provided by the official module lu_sqlite.