Prev/Next Blog
by date

Persistent Items and Mission Objects

Name:	Matt Huston
Date Posted:	Oct 31, 2006
Rating:	3.5 out of 5
Public:	YES
Comments:	YES
RSS Feed:	or Subscribe with .
Profile Page:	View profile page for Matt Huston

Blog post

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);
}

Recent Blog Posts

List:	04/19/08 - Back to the future 07/06/07 - Resource Dump - GuiAnimatedCrosshairHud, guiObjectSelectionHud 04/30/07 - ModMaker, TGB, TorqueX and More 11/19/06 - Dev-Diary #4 - Action RPG :: Talking Heads 10/31/06 - Persistent Items and Mission Objects 10/28/06 - Dev-Diary #3 - A single player action-RPG 07/31/06 - TGB RPGness Part 1 (Inventory) 07/06/06 - TGB Gaming Goodness

Submit your own resources!

Unsung Zero (Oct 31, 2006 at 14:07 GMT)

Great information! You may want to consider putting this as a resource so others can find it in the future.

Matt Vitelli (Oct 31, 2006 at 14:15 GMT)

Really great information. I'll have to merge this into my saving code.

Dreamer (Oct 31, 2006 at 16:20 GMT)

Wouldn't work multiplayer I don't think. All of your items and stuff are going to end up spawning in the boonies somewhere. When spawning stuff multiplayer I've found it best to spawn first and then use setTransform.

Mincetro (Nov 01, 2006 at 05:10 GMT) Resource Rating: 4

Cool - I just added SQLite to my game for the same function, thankyou very much for this, It's a great resource and starting point for me.

Matt Huston (Nov 01, 2006 at 18:46 GMT)

I don't see why it wouldn't work in multiplayer but I haven't tested it. This was meant for a single-player oriented game and it works in that respect.

You must be a member and be logged in to either append comments or rate this resource.