Squirrel/Examples/SQLite
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.
Contents |
Creating/restoring a database
First thing to do is to load/create a new SQLite database. This is done with the function ConnectSQL. 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() { // Create/load the database and store it to a global variable 'statDB' statDB <- ConnectSQL( "serverstats.sqlite" ); // Create an array to store the stats for every player while they are ingame stats <- array( GetMaxPlayers(), null ); }
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 }
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 QuerySQL, which accepts the earlier retrieved database pointer and the query as a string as its parameters.
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).
QuerySQL( 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 QuerySQL will return null.
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 QuerySQL 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 GetSQLNextRow 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 GetSQLColumnCount and fetch the data in a specific column with GetSQLColumnData. GetSQLColumnCount takes the query result object as a parameter. GetSQLColumnCount 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), GetSQLColumnCount( q ), where q is the query result from QuerySQL, 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 = QuerySQL( database, "SELECT Name, Kills FROM Stats WHERE Name LIKE 'player%'" ); print( "Result columns: " + GetSQLColumnCount( q ) ); print( "\nPlayer name - Kills" ); // Iterate the rows in a loop until there are no more rows (column data = null) while ( GetSQLColumnData( q, 0 ) != null ) { // Print the data in the first (0) and second (1) column of the current row print( GetSQLColumnData( q, 0 ) + " - " + GetSQLColumnData( q, 1 ) ); // Read the next row available q = GetSQLNextRow( q ); }
The output would be:
SCRIPT: Result columns: 2 SCRIPT: Player name - Kills SCRIPT: player1 - 23 SCRIPT: player2 - 123 SCRIPT: player3 - 99
Note that GetSQLColumnData 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 = QuerySQL( 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 ( GetSQLColumnData( 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 = GetSQLColumnData( q, 0 ); stats[ id ].Deaths = GetSQLColumnData( q, 1 ); stats[ id ].Joins = GetSQLColumnData( q, 2 ); } // Ask for the next row to finalize the query result GetSQLNextRow( q ); // Increase player joins stats[ id ].Joins++; }
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 QuerySQL( 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 ); QuerySQL( statDB, query ); } // Reset the stat array slot stats[ id ] = null; }
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.
Complete example script
///////////////////////////// //// SQLite Stats Example /// ///////////////////////////// //-------------------------------------------------- function onScriptLoad() { // Create/load the database and store it to a global variable 'statDB' statDB <- ConnectSQL( "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 ===" ); } //-------------------------------------------------- /* 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" ) { QuerySQL( statDB, "CREATE TABLE Stats ( Name VARCHAR(32), Kills INT, Deaths INT, Joins INT )" ); } } //-------------------------------------------------- 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 = QuerySQL( 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 ( GetSQLColumnData( 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 = GetSQLColumnData( q, 0 ); stats[ id ].Deaths = GetSQLColumnData( q, 1 ); stats[ id ].Joins = GetSQLColumnData( q, 2 ); } // Ask for the next row to finalize the query result GetSQLNextRow( q ); // Increase player joins stats[ id ].Joins++; } //-------------------------------------------------- /* 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 QuerySQL( 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 ); QuerySQL( statDB, query ); } // Reset the stat array slot stats[ id ] = null; } //-------------------------------------------------- /* 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++; } function onPlayerDeath( player, reason ) { stats[ player.ID ].Deaths++; } //-------------------------------------------------- /* 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: /c stats <player>", player ); } }