Persistent Chest / Corpse implementation via SQL

[AcadiusLost]

So, the reason persistent storage has been so unreliable for so long, is that the somewhat lousy database system that shipped with NWN1 still forms the basis of all inventory storage across module resets (unless you count things in PC inventories when they log off). Because the data format used in .bic files and the database files is an inefficient one, these files can get quite large.

Since earlier this month, it's gotten essentially unusable on The Silver Marches. I believe it's time to try to switch to a better implementation as soon as possible.

Our alternative seems to be newly enabled functions through NWNx4 which allow binary storage fields to be inserted into database tables, and retrieved later. So, this would use the same central SQL database which hosts all the other persistency data and log information for the ACR. This may cause some lag problems, since the data will have to be passed across the net to the central SQL server and back each time (and it's more bits and bytes than the simple string fields we trade regularly between the SQL server and each of the Live and Beta host machines). The upside is the inventories would be crosserver persistent, allowing things like Portable Holes, PC corpses that can be carried across server borders without losing their inventories, pack animals that can be lead between servers, etc.

We may even be able to implement a kind of cross-server Limbo function, whereby a DM on BG could limbo a plot NPC for a TSM DM to pull out for use on TSM and vice versa. Or, builders could make items in the right kind of build module, and "send" them directly into a DM chest on an ALFA server without need for a module update/reset/import/etc.

Neat stuff. Unfortunately need to do some thinking about implementation and experimenting with the functions. First step is designing the database table. I'm thinking for fields:

• ObjectID (integer, unique, primary key, assigned automatically)
  
• ContainerID (string, unique for each p-chest, allows grouping/selecting for the contents of any one container for retrieval)
  
• Name ( of the object, string field)
  
• CharacterID ( integer, foreign key, default NULL/0, optional PC or DM avatar to associate the record with)
  
• ServerID (integer, foreign key, optional server the object is associated with)
  
• ObjectType (Integer, uses convention from nwscript for Item vs. Creature)
  
• GPValue (integer, value of the object in GP)
  
• ObjectData(BLOB type, stores the actual data for the game object)
  
• Date (string, date the object was added to the database)

Does that sound sufficient? Gives us some handles to pull selectively from the table at least. It means we'll have one record in the DB for each item in any persistent chest/corpse/etc on every server, but I'm not sure how we'd get around that.

So, OnOpen for a p-chest (or OnModuleLoad), we'd SELECT all records from the database which had ContainerID = (the unique DB name of the pchest). This would return one record per item in the chest, which could be looped through to populate the chest with real in-game items. Then, we could update it piecemeal each time the inventory is disturbed (saving the "big hit" OnClose). So, you pull a Light Crossbow out of the chest, and a quick SQL DeleteRow fires across the net to remove that ObjectID. Put a dagger into the chest, and a (larger) query goes out Inserting a new row with the dagger's data, attaching value, the PC's ID, the database ID, etc. So, were the server to crash just then, the repopulation function would know to put a new dagger in the chest OnModuleLoad based on the data transmitted.

That's the theory at least. Will update with progress as I carve out some development time for testing/implementation.

[Aelred]

A.L. The other day we were talking about persistant variables being stored in an object in the players inventory. Couldn't this work the same way for persistant storage? Write a script for a chest and a key. Any item the player drops into the chest is written on the key which is nondroppable in the players inventory. When the chest closes the items are destroyed, when the chest is opened with the key, the stored items info is retreived from the key and those items are spawned in the chest.

[johnlewismcleod]

It's a smallish concern perhaps, Aelred, but what you propose is persistent storage that connot be robbed. Given that there is nothing for rogues to steal IG as it is, it's perhaps a mute point.

[AcadiusLost]

Local variables can be saved on items persistently, but we're not talking about short lines of text or simple numbers here; to save a persistent inventory (a la persistent chest), you actually have to save lots of data for each item (including information on any variables set on each item). You can't save an item on an item; the SetLocalObject() function just stores a short hexadecimal code which serves as an address to find the object among the server's files in memory (no longer valid after a module reset).

[Riotnrrd]

Have a little SQL experience, lemme know if help is needed.

Your variables seem fine to me, AL.

[zicada]

I'm excited about this personally.

Seems to me the best approach is to set up a test requiring the minimum amt of effort, to prove/disprove the negative possibilities such a lag. I'm sure somebody else has some code out there,- we could just use that on the beta-db on the sql-machine for example.

[AcadiusLost]

I've already got my build rig connected to the beta SQL schema and vault from our portalling testing, so I'll be using that to conduct the tests. I haven't climbed into the guts of the NWNx4 functions yet, but hope to get a start on it tonight.

[NickD]

Neat stuff. Unfortunately need to do some thinking about implementation and experimenting with the functions. First step is designing the database table. I'm thinking for fields:

• ObjectID (integer, unique, primary key, assigned automatically)
  
• ContainerID (string, unique for each p-chest, allows grouping/selecting for the contents of any one container for retrieval)
  
• Name ( of the object, string field)
  
• CharacterID ( integer, foreign key, default NULL/0, optional PC or DM avatar to associate the record with)
  
• ServerID (integer, foreign key, optional server the object is associated with)
  
• ObjectType (Integer, uses convention from nwscript for Item vs. Creature)
  
• GPValue (integer, value of the object in GP)
  
• ObjectData(BLOB type, stores the actual data for the game object)
  
• Date (string, date the object was added to the database)

Does that sound sufficient? Gives us some handles to pull selectively from the table at least. It means we'll have one record in the DB for each item in any persistent chest/corpse/etc on every server, but I'm not sure how we'd get around that.

If I may... There is a fair bit of redundancy in there. I would suggest cutting it down somewhat.

Assuming that NWNx4 handles basic DB stuff, I would design it thus, with 2 tables:

PChest

• ContainerID (string, unique for each p-chest, allows grouping/selecting for the contents of any one container for retrieval, limit 16 characters )
  
• ServerID (integer, foreign key, optional server the p-chest is associated with)
  
• CharacterID ( integer, foreign key, default NULL/0, optioal PC or DM avatar to associate the p-chest with)

PObject

• ObjectID (integer, unique, primary key, assigned automatically)
  
• ContainerID (string, foreign key)
  
• ObjectData(BLOB type, stores the actual data for the game object)

This is assuming that Name, ObjectType and GPValue can all be derived from ObjectData. I am also guessing here that you want to put those values in for auditing purposes, in which case, it would be much more efficient to use an auditing module to calculate those values after retrieval than to calculate (in the case of GPValue) them and push and pull them all over the place every time a player accesses them.

I'm guessing Date is also for auditing purposes... I would ask you to question its value. If you decide you really need it, I would then suggest instead of having an ObjectID, an alternative would be to just have the DateTime "yyyyMMddHHmmss" (plus milliseconds if you can) as the unique key. On insert, if fail (i.e., someone else is inserting a record at that exact second), insert again, make n attempts.

Then, we could update it piecemeal each time the inventory is disturbed (saving the "big hit" OnClose). So, you pull a Light Crossbow out of the chest, and a quick SQL DeleteRow fires across the net to remove that ObjectID. Put a dagger into the chest, and a (larger) query goes out Inserting a new row with the dagger's data, attaching value, the PC's ID, the database ID, etc. So, were the server to crash just then, the repopulation function would know to put a new dagger in the chest OnModuleLoad based on the data transmitted.

If NWNx4 handles transactions, wrapping it all in a transaction (performing a commit on OnClose) should work better than inserting safety records.

[AcadiusLost]

I'm not sure I see the value of abstracting "Pchests" into it's own table, unless it's meant to also store summary information on chest contents (number of items, total GP value, last accessed?). It doesn't reduce the size of the PObjects table significantly as best I can tell. "Group By" type queries aren't my strong point, certainly, so if there is a significant performance reason to abstract it, please elaborate.

With regards to the extraneous information in PObjects, those pieces of data are only available by converting the BLOB data back into a game object via SQL/NWNx4/nwscript, then running the appropriate nwscript functions on them to determine them. Since we can SELECT a subset of the fields to query, this should allow us to perform comparatively efficient queries to check things like total GP value in a chest, number of objects inside, number of PCs who have placed items into a chest, etc; all without having to push the actual item GFFs across the Atlantic and back. Much of this is summarizable if we do abstract out the Pchests table, but that makes extra serverside CPU/nwscript work to populate those fields OnUpdate as well.

[NickD]

I'm at work now (and currently writing a program to report non-work related internet use for management! ) so I don't have a lot of time to expand too much into it right now... but there's a couple of changes/amendments I'd make to my suggestion, one being the PChest record would have an additional integer field for the reference id that the PObject table would use as a foreign key instead of using the string name. That would save on space in the database and traffic over the lines.

As for the BLOB thing, I understand why you want to put all that stuff in the database and I will elaborate later on why I don't think you should. It's basically a question of server performance and questioning how often audits are carried out over how often persistant storage is accessed.

[NickD]

I separated out the PChest into a separate table to remove data duplication. I had assumed that character id would be unique to the chest rather than object, and server id the same. You could then have an auto increment integer field instead of a string field as an identifier for the PChest (small strings are fine as keys, but integers are smaller and index better). That way you only have to send and store an integer instead of a string and two integers every time an item is put in the chest.

To get the items in the chest the query would be something like "SELECT O.ObjectData FROM PChest C LEFT JOIN PObject O ON C.ContainerID = O.ContainerID WHERE C.ContainerName = '[Insert ContainerName Here]' [AND C.ServerID = [Insert ServerID here]];"

Strictly speaking, I don't think a left join is necessary in this case, but I usually use them. It's how I roll.

However, if Character ID is based on who put the object into the storage rather than the pchest itself, separating the tables starts to have less value. I'd personally still split them out because of the logical grouping and future proofing.

My reason for removing the values that can be derived from the object is in the interest of lag reduction. On the one hand, keeping them in the table means you can query the database directly. On the other hand, keeping them in the table means you have to extract that information from the object, pass it to the server and store it in the database every single time for every single object that is put into persistant storage. I would suspect that persistant storage will be used significantly more frequently than auditing, so it would make more sense for the extra processing to fall on the shoulders of auditing.

So, anyway, an amendment:

PChest

• ContainerID (integer, unique, primary key, assigned automatically)
  
• ContainerName (string, unique for each p-chest/server combination, allows grouping/selecting for the contents of any one container for retrieval, limit 16 characters )
  
• ServerID (integer, foreign key, optional server the p-chest is associated with)
  
• CharacterID ( integer, foreign key, default NULL/0, optioal PC or DM avatar to associate the p-chest with)

PObject

• ObjectID (integer, unique, primary key, assigned automatically)
  
• ContainerID (string, foreign key)
  
• ObjectData(BLOB type, stores the actual data for the game object)
  
• Retrieved(boolean/bit, int "1/0" if boolean isn't available)

Added an integer primary key for the PChest table. Also added the Retrieved field to the PObject table. Assuming that transactions don't work with NWNx4, instead of deleting and inserting a record when an object is removed, I would simply update the existing record to say it has been taken out.

[AcadiusLost]

However, if Character ID is based on who put the object into the storage rather than the pchest itself, separating the tables starts to have less value. I'd personally still split them out because of the logical grouping and future proofing.

That was an idea I'd had as I was drafting up those example tables, I feel it's a useful piece of information that we'd otherwise not be able to query well. How many cases it would really be useful in is debatable, but it would give us an easy way to get a sense of whether multiple PCs are using the same p-storage object (not uncommon, I'd expect, depending). It's not at all essential, though.

• Retrieved(boolean/bit, int "1/0" if boolean isn't available)

Added an integer primary key for the PChest table. Also added the Retrieved field to the PObject table. Assuming that transactions don't work with NWNx4, instead of deleting and inserting a record when an object is removed, I would simply update the existing record to say it has been taken out.

The "Removed" flag is an interesting thought that hadn't occurred to me before. I'm not sure how an Update on that field compared to a row deletion performace-wise, I'd expect them to be comparable since neither have to push the BLOB field. The real performance bonus would be on anything removed but then subsequently returned to the chest. I'm not sure if we could identify the record that was formerly assigned to it in order to toggle the bit back to Available again. Within a module session (if there was no reset), the nwscript OID hex pointer might remain valid if we used it as a a field in the DB (via ObjectToString and vice versa), though if we're only working during a given mod session, we're better off caching with Locals (arrays of pointers, etc) for that sort of information for optimization. I worry about maintainance of the table against bloat from non-available items, though I suppose we could clean up the table OnModuleLoad() or another time when it wouldn't negatively impact on noticeable performance. Actually, now that I think about it, the object data itself might have changed while it was out of the chest (renamed, had changes to stored LocalVariables, charges spent if it were a wand, etc), which would be lost if the old BLOB field was used again to repopulate the chest after a mod update.

Re: Transactions, I haven't looked into the specifics of how some of the more advanced queries with through NWNx4/MySQL. It sounds like you've got more familiarity with SQL than I do, so you're welcome to peruse the NWNx4 forums and see what you can make of it. I know we can make multiple-result SELECT statements and step through the results via nwscript functions, but I haven't tried a multi-insert one yet. Further, I don't know if the options are more limited with the SCO/RCO library, esp since they're fairly new.

(extremely) brief explanations on them here:
http://www.nwnx.org/phpBB2/viewtopic.php?p=9408#9408
http://www.nwnx.org/phpBB2/viewtopic.php?t=1252

When I'm home tonight I can copy/paste/quote the functions they've put into the recent nwscript includes that correspond with the SQL-based Object database functions as well. Seems like the 1.22/SoZ transition will trump this project for a few days, but still hoping to get some testing in this weekend.

[NickD]

I'm late for work (again) so just a quick message...

I did a search on the forums and it does look like they support transactions.

Transactions are not so much SQL as a wrapper for a set of SQL statements. Basically what you do is when you open the chest you SQLExecDirect("BEGIN TRANSACTION;") and when you close the chest you SQLExecDirect("COMMIT;"). What happens is that the changes are made to the transaction log and not applied to the database until the transaction is committed. If the server crashes before the chest is closed, the transaction will get rolled back, effectively leaving the database as it was before beginning the transaction.

At least that's how it works for straight up windows apps. It may work differently for NWN - I've only written a few web based apps and they tend to handle things a bit differently.

[AcadiusLost]

It would certainly bear some testing; I know for example that you can't query anything new via SQL while looping through a query result set without losing the rest of the information retrieved; from that perspective I'd worry that there may be problems with other SQL transactions going on between the BEGIN and COMMIT commands causing problems.

We'll have to see how it works out.

[NickD]

It would certainly bear some testing; I know for example that you can't query anything new via SQL while looping through a query result set without losing the rest of the information retrieved; from that perspective I'd worry that there may be problems with other SQL transactions going on between the BEGIN and COMMIT commands causing problems.

We'll have to see how it works out.

Huh. I do know that with SQL Server you can specify multiple result sets via connection string with "MultipleActiveResultSets=true;" but I don't know if that's possible with whatever database you're using.

I'd be less concerned over whether you can have multiple queries within a transaction and more concerned over whether transactions apply to either the client or the server. If it's client/session based then that's no problem. If it's server based, transactions from different pchests could start interfereing with each other. Named transactions would be nice.

So...
OnOpen:
* SQLExecDirect("BEGIN TRANSACTION;")
* "SELECT C.ContainerID, O.ObjectData FROM PChest C LEFT JOIN PObject O ON C.ContainerID = O.ContainerID WHERE C.ContainerName = '[Insert ContainerName Here]' [AND C.ServerID = [Insert ServerID here]];"
* first(); while ( read() ){ // Populate the Chest from the dataset. } close();

OnGettingSomething out:
* (How do we get the ObjectID from the object?)
* "UPDATE PObject SET Retreived = ['true'/1] WHERE ObjectID = [ObjectID];"

OnClose
* "DELETE FROM PObject WHERE ContainerID = [ContainerID] AND Retrieved = ['true'/1]"
* SQLExecDirect("COMMIT TRANSACTION;")