Persistent Items and Mission Objects

Having some more fun using the SQLite resource. After my last blog, I had a few people asking questions about using the SQLite resource and I thought I'd write up some code where I spawn items (Crossbow, CrossbowAmmo, HealthPacks, etc) from my SQLite database.

I created two tables in my database, "Items" which holds ItemId and ItemDatablock. ItemId being the unique number of the Item, and ItemDatablock being the text datablock name, for instance "Crossbow". The second table is my "WorldItems" table. This holds "WorldItemId", "ItemId", "Area", "SpawnPoint", and "Rotation" WorldItemId is a unique number identifying each item in the map, ItemId links us with the Items table, Area is the map the item is currently in, SpawnPoint is where the item should be spawned at, and Rotation is the items orientation.

I have two function, one to spawn the items once the mission has been started, and one to save them once the mission is exited or the game is closed. This allows for the player to be able to pickup items and then leave the game, and come back and the items won't be on the map anymore. However they won't be on the player either. :) But the basis of the idea is there and then be created for the player as well.

So here is the code. Enjoy :D

Spawn Code

function spawnItems()
{ 
   %query = "SELECT * FROM WorldItems INNER JOIN Items ON WorldItems.ItemId = Items.ItemId";
   
   %result = sqlite.query(%query, 0);
   
   echo(%result);

   %rowCounter = 0;
   %rowCount = sqlite.numRows(%result);

   echo(%rowCount);

   while (%rowCounter < %rowCount)
   {
      %itemId     = sqlite.getColumn(%result, "ItemId");
      %spawnPoint = sqlite.getColumn(%result, "SpawnPoint");
      %rotation   = sqlite.getColumn(%result, "rotation");
      %datablock  = sqlite.getColumn(%result, "itemDatablock");
      
      echo(%wItemId SPC %itemId SPC %spawnPoint SPC %rotation);
      
      echo("Datablock :: " @ %datablock);
      
      new Item() 
      {
         position = %spawnPoint;
         rotation = %rotation;
         dataBlock = %datablock;
      };
      
      %rowCounter++;
      sqlite.nextRow(%result);
   }
   
   if (%result)
   {
      sqlite.clear(%result);
   }
}

Save Code

function saveItems()
{
   %query = "DELETE FROM WorldItems WHERE Area ='" @ MissionInfo.name @ "'";
   %result = sqlite.query(%query);
   
   %objectCount = ServerConnection.getCount();
   
   %foundCount = 1;
   
   for(%i = 1 ; %i < %objectCount ; %i++)
   {
      %obj = ServerConnection.getObject(%i);
      
      echo(%obj);
      
      %className = %obj.getClassName();
      
      if(%className $= "Item")
      {
         %dataBlock = %obj.getDatablock();
         %dataBlockName = %dataBlock.getName();
         
         %query = "SELECT ItemId FROM Items WHERE ItemDatablock='" @ %dataBlockName @ "'";
         %result = sqlite.query(%query, 0);
         
         %itemId = sqlite.getColumn(%result, "ItemId");
         
         %spawnPoint = %obj.getPosition();
         %rotation   = %obj.rotation;

         echo(%i SPC %itemId SPC MissionInfo.name SPC %spawnPoint SPC %rotation);

         %query = "INSERT INTO WorldItems ("
                @ "WorldItemId, ItemId, Area, SpawnPoint, Rotation) "
                @ "VALUES ("
                @ "'" @ %foundCount @ "',"
                @ "'" @ %itemId @ "',"
                @ "'" @ MissionInfo.name @ "',"
                @ "'" @ %spawnPoint      @ "',"
                @ "'" @ %rotation @ "')";
         %result = sqlite.query(%query, 0);
         
         %foundCount++;   
      }      
   }
}

Table Creation and Data Insert Code (for Reference)

function createItems()
{
   %query = "CREATE TABLE WorldItems ("
          @ "WorldItemId INTEGER,"
          @ "ItemId INTEGER,"
          @ "Area TEXT,"
          @ "SpawnPoint TEXT,"
          @ "Rotation TEXT);";
          
   %result = sqlite.query(%query, 0);

   %query = "INSERT INTO WorldItems ("
          @ "WorldItemId, ItemId, Area, SpawnPoint, Rotation) "
          @ "VALUES ("
          @ "'" @ 1 @ "',"
          @ "'" @ 1 @ "',"
          @ "'" @ "Stronghold" @ "',"
          @ "'" @ "28.1121 1.70186 0.0957217" @ "',"
          @ "'" @ "1 0 0 0" @ "')";
          
   %result = sqlite.query(%query, 0);
}