Squirrel/Examples/SQLite

From Liberty Unleashed Wiki
Revision as of 02:07, 16 October 2010 by Juppi (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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


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. 

  1.  
  2. function onScriptLoad()
  3. {
  4.      // Load the module which provides SQLite functions
  5.      LoadModule( "lu_sqlite" );
  6.      
  7.      // Create/load the database and store it to a global variable 'statDB'
  8.      statDB <- sqlite_open( "serverstats.sqlite" );
  9.      
  10.      // Create an array to store the stats for every player while they are ingame
  11.      stats <- array( GetMaxPlayers(), null );
  12.      
  13.      return 1;
  14. }
  15.  

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. 

  1.  
  2. class PlayerStats
  3. {
  4.      Kills = 0;
  5.      Deaths = 0;
  6.      Joins = 0;
  7.      PreviousData = false; // This variable will tell us later whether the player has any previous stats saved in the table
  8. }
  9.  

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). 

  1.  
  2. sqlite_query( statDB, "CREATE TABLE Stats ( Name VARCHAR(32), Kills INT, Deaths INT, Joins INT )" );
  3.  

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.

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: 

  1.  
  2. -------------------------------------
  3. |            ~ Stats ~              |
  4. -------------------------------------
  5. |   Name   | Kills | Deaths | Joins |
  6. |------------------------------------
  7. | player1  |  23   |   12   |  14   |
  8. | player2  |  123  |  377   |  93   |
  9. | player3  |  99   |  999   |  99   |
  10. | someone  |  78   |   34   |  7    |
  11. | spaner   | 1337  |  429   |  152  |
  12. | tammer   |  38   |  3482  |  392  |
  13. -------------------------------------
  14.  

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: 

  1.  
  2. |-------------------
  3. | player1  |  23   |
  4. | player2  |  123  |
  5. | player3  |  99   |
  6. --------------------
  7.  

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: 

  1.  
  2. local q = sqlite_query( database, "SELECT Name, Kills FROM Stats WHERE Name LIKE 'player%'" );
  3.  
  4. print( "Result columns: " + sqlite_column_count( q ) );
  5. print( "\nPlayer name - Kills" );
  6.  
  7. // Iterate the rows in a loop until there are no more rows (column data = null)
  8. while ( sqlite_column_data( q, 0 ) != null )
  9. {
  10.      // Print the data in the first (0) and second (1) column of the current row
  11.      print( sqlite_column_data( q, 0 ) + " - " + GetSQLColumnData( q, 1 ) );
  12.      
  13.      // Read the next row available
  14.      q = sqlite_next_row( q );
  15. }
  16.  

The output would be: 

  1.  
  2. SCRIPT: Result columns: 2
  3.  
  4. SCRIPT: Player name - Kills
  5. SCRIPT: player1 - 23
  6. SCRIPT: player2 - 123
  7. SCRIPT: player3 - 99
  8.  

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. 

  1.  
  2. function onPlayerJoin( player )
  3. {
  4.      // Create a temporal stat object to store the player stats while they're online
  5.      local id = player.ID;
  6.      stats[ player.ID ] = PlayerStats();
  7. 	 
  8.      // Convert player name to lower case; that way we can ignore the case
  9.      local name = player.Name.tolower();
  10.     
  11.      local q = sqlite_query( statDB, "SELECT Kills, Deaths, Joins FROM stats WHERE Name='" + name + "'" );
  12.  
  13.      // If the player had previous stats saved (ie. there's value in the first column of the first row)
  14.      if ( sqlite_column_data( q, 0 ) != null )
  15.      {
  16.           // Remember that player had previous stats in the database
  17.           stats[ id ].PreviousData = true;
  18.           
  19.           // Fetch the asked values from the columns of the returned row
  20.           stats[ id ].Kills = sqlite_column_data( q, 0 );
  21.           stats[ id ].Deaths = sqlite_column_data( q, 1 );
  22.           stats[ id ].Joins = sqlite_column_data( q, 2 );
  23.           
  24.      }
  25. 	 
  26.      // Free the query using sqlite_free
  27.      sqlite_free( q );
  28.      
  29.      // Increase player joins
  30.      stats[ id ].Joins++;
  31.      
  32.      return 1;
  33. }
  34.  

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. 

  1.  
  2. function onPlayerPart( player, partID )
  3. {
  4.      // Load the player stats from the array + lowercase name
  5.      local
  6.           id = player.ID,
  7.           name = player.Name.tolower();
  8.      
  9.      // If player had any data in the database, update the old stats
  10.      if ( stats[ id ].PreviousData )
  11.      {
  12.           // Format the query string using 'format'
  13.           local query = format( "UPDATE Stats SET Kills=%i, Deaths=%i, Joins=%i WHERE Name='%s'",
  14.                                  stats[ id ].Kills, stats[ id ].Deaths, stats[ id ].Joins, name );
  15.           // Then execute the query
  16.           sqlite_query( statDB, query )
  17.      }
  18.      
  19.      // Else let's just make a new entry
  20.      else
  21.      {
  22.           local query = format( "INSERT INTO Stats (Name, Kills, Deaths, Joins) VALUES ('%s', %i, %i, %i)",
  23.                                  name, stats[ id ].Kills, stats[ id ].Deaths, stats[ id ].Joins );
  24.           sqlite_query( statDB, query );
  25.      }
  26.      
  27.      // Reset the stat array slot
  28.      stats[ id ] = null;
  29. }
  30.  

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

  1.  
  2. /////////////////////////////
  3. //// SQLite Stats Example ///
  4. /////////////////////////////
  5.  
  6. //--------------------------------------------------
  7.  
  8. function onScriptLoad()
  9. {
  10.      // Load the module which provides SQLite functions
  11.      LoadModule( "lu_sqlite" );
  12.      
  13.      // Create/load the database and store it to a global variable 'statDB'
  14.      statDB <- sqlite_open( "serverstats.sqlite" );
  15.      
  16.      // Create an array to store the stats for every player while they are ingame
  17.      stats <- array( GetMaxPlayers(), null );
  18.      
  19.      print( "=== Loaded SQLite Stats Example ===" );
  20.      
  21.      return 1;
  22. }
  23.  
  24. //--------------------------------------------------
  25.  
  26. /* The class to store player stats */
  27. class PlayerStats
  28. {
  29.      Kills = 0;
  30.      Deaths = 0;
  31.      Joins = 0;
  32.      PreviousData = false;
  33. }
  34.  
  35. //--------------------------------------------------
  36.  
  37. /* A console command to initialize a table for the stats */
  38. function onConsoleInput( cmd, text )
  39. {
  40.      if ( cmd == "createtable" )
  41.      {
  42.           sqlite_query( statDB, "CREATE TABLE Stats ( Name VARCHAR(32), Kills INT, Deaths INT, Joins INT )" );
  43.      }
  44.      
  45.      return 1;
  46. }
  47.  
  48. //--------------------------------------------------
  49.  
  50. function onPlayerJoin( player )
  51. {
  52.      // Create a temporal stat object to store the player stats while they're online
  53.      local id = player.ID;
  54.      stats[ player.ID ] = PlayerStats();
  55. 	 
  56.      // Convert player name to lower case; that way we can ignore the case
  57.      local name = player.Name.tolower();
  58.     
  59.      local q = sqlite_query( statDB, "SELECT Kills, Deaths, Joins FROM stats WHERE Name='" + name + "'" );
  60.  
  61.      // If the player had previous stats saved (ie. there's value in the first column of the first row)
  62.      if ( sqlite_column_data( q, 0 ) != null )
  63.      {
  64.           // Remember that player had previous stats in the database
  65.           stats[ id ].PreviousData = true;
  66.           
  67.           // Fetch the asked values from the columns of the returned row
  68.           stats[ id ].Kills = sqlite_column_data( q, 0 );
  69.           stats[ id ].Deaths = sqlite_column_data( q, 1 );
  70.           stats[ id ].Joins = sqlite_column_data( q, 2 );
  71.           
  72.      }
  73. 	 
  74.      // Finalize query result
  75.      sqlite_free( q );
  76.      
  77.      // Increase player joins
  78.      stats[ id ].Joins++;
  79.      
  80.      return 1;
  81. }
  82.  
  83. //--------------------------------------------------
  84.  
  85. /* Save player stats on part */
  86. function onPlayerPart( player, partID )
  87. {
  88.      // Load the player stats from the array + lowercase name
  89.      local
  90.           id = player.ID,
  91.           name = player.Name.tolower();
  92.      
  93.      // If player had any data in the database, update the old stats
  94.      if ( stats[ id ].PreviousData )
  95.      {
  96.           // Format the query string using 'format'
  97.           local query = format( "UPDATE Stats SET Kills=%i, Deaths=%i, Joins=%i WHERE Name='%s'",
  98.                                  stats[ id ].Kills, stats[ id ].Deaths, stats[ id ].Joins, name );
  99.           // Then execute the query
  100.           sqlite_query( statDB, query )
  101.      }
  102.      
  103.      // Else let's just make a new entry
  104.      else
  105.      {
  106.           local query = format( "INSERT INTO Stats (Name, Kills, Deaths, Joins) VALUES ('%s', %i, %i, %i)",
  107.                                  name, stats[ id ].Kills, stats[ id ].Deaths, stats[ id ].Joins );
  108.           sqlite_query( statDB, query );
  109.      }
  110.      
  111.      // Reset the stat array slot
  112.      stats[ id ] = null;
  113.      
  114.      return 1;
  115. }
  116.  
  117. //--------------------------------------------------
  118.  
  119. /*
  120.      These events will affect the player stats, so we need to grab the
  121.      changes and temporarily save them into the array
  122. */
  123.  
  124. function onPlayerKill( killer, player, weapon )
  125. {
  126.      stats[ killer.ID ].Kills++;
  127.      stats[ player.ID ].Deaths++;
  128.      
  129.      return 1;
  130. }
  131.  
  132. function onPlayerDeath( player, reason )
  133. {
  134.      stats[ player.ID ].Deaths++;
  135.      
  136.      return 1;
  137. }
  138.  
  139. //--------------------------------------------------
  140.  
  141. /* A basic /c stats command */
  142. function onPlayerCommand( player, command, text )
  143. {
  144.      if ( command == "stats" )
  145.      {
  146.            if ( text )
  147.            {
  148.                 local plr = FindPlayer( text );
  149.                   
  150.                  if ( !plr ) MessagePlayer( "Player " + text + " is not online." );
  151.                  else
  152.                  {
  153.                        local plrStats = stats[ plr.ID ];
  154.                       
  155.                        MessagePlayer( plr.Name + "'s stats:", player );
  156.                        MessagePlayer( "Kills: " + plrStats.Kills + ", Deaths: " + plrStats.Deaths, player );
  157.                        MessagePlayer( "Joins: " + plrStats.Joins, player );
  158.                  }
  159.            }
  160.            else MessagePlayer( "Usage: /stats <player>", player );
  161.      }
  162.      
  163.      return 1;
  164. }
  165.  

Related functions

These functions are provided by the official module lu_sqlite.

Retrieved from "http://liberty-unleashed.co.uk/libertywiki/index.php?title=Squirrel/Examples/SQLite&oldid=2119"
Personal tools
Namespaces

Variants
Actions
Navigation
scripting
Toolbox