Persistent Item Storage - SQL

[Legacy_BelowTheBelt]

               

Anyone have experience working with a persistent item storage solution using SQL?  I'm especially interested in how unique non-palette items are handled (items with custom appearances, names, descriptions, colors, properties, etc...) in addition to the approach.

 

I'm using a creature-based system now, but don't like it and it's had instances of losing player inventory.

 

Thanks.

               
               

               
            

[Legacy_Squatting Monk]

               

I use a Text or BLOB field to store the actual object data (not the resref). This means all local variables, etc. should be restored when the object is re-created from the database.


Use SQL to define where in the database the object should be stored, replacing the object itself with %s. For example:

string sSQL = "INSERT INTO pwobjdata (Tag, VarName, Value) VALUES ('MyChestTag', 'Item1', %s) ON DUPLICATE KEY UPDATE Value=%s";

Then use the following function to set the object in the database:

void SQLStoreCampaignObject(string sSQL, object oObject)
{
    SetLocalString(GetModule(), "NWNX!ODBC!SETSCORCOSQL", sSQL);
    StoreCampaignObject("NWNX", "-", oObject);
}

...where sSQL is your prepared statement and oObject is the object to store.


For getting the object back out, you can use this function:

object SQLRetrieveCampaignObject(string sSQL, location lDestination, object oOwner = OBJECT_INVALID)
{
    SetLocalString(GetModule(), "NWNX!ODBC!SETSCORCOSQL", sSQL);
    return RetrieveCampaignObject("NWNX", "-", lDestination, oOwner);
}

You don't have to use the %s placeholder in this case since you don't need to pass any object data to the NWNX plugin. This will create the object in oOwner's inventory. If the object cannot go in oOwner's inventory or oOwner is invalid, it will be created at lDestination.

               
               

               
            

[Legacy_BelowTheBelt]

               

Awesome!  I'm going to go screw this up and then come back for more help.

 

Thanks!

               
               

               
            

[Legacy_henesua]

               

Awesome!  I'm going to go screw this up and then come back for more help.

 

Thanks!

i fully endorse this plan of action as the single best way to learn.

               
               

               
            

[Legacy_BelowTheBelt]

               

As expected, I need some more help.

 

I have a separate table set up for item storage, called "storage".  It has 2 columns:  UID(a unique player-specific ID) and Items.

UID is varchar

Items is blob

 

I added the SQLStoreCampaignObject, and SQLRetrieveCampaignObject functions mentioned above to my aps_include file.

 

I'm using a chest placeable with OnDisturbed and OnUsed scripts.  The OnDisturbed script counts the number of items that have been added or removed from the placeable.  The OnUsed script creates a invisible creature and copies items from the chest to the creature for storage.

 

At this point, I don't know if I'm writing correctly to the db as I test it to hold 8-10 items.  I can see that I'm writing something to the db, at least, but when I look at it, the "Items" column appears as a binary/image type (is this what a blob is?) with a size of 6k.   Is this right?  8-10 items is 6k?  Surely something is wrong here.  The default nwnx only reserves 1k per row and although I've increased my size beyond that, it's not reserving 6+k.  I'm imagining a hundred+ items, if not more, to be saved.

 

When I reopen the chest, I'm not retrieving any of my stored Items, but that may be because the row is higher than I've reserved, so I wanted to ask about that first.  What size for your persistent items are you seeing in the db? 

 

My store call:

string sSQL = "UPDATE "+DBNAME+" SET Items =%s WHERE UID = "+sID;

            SQLStoreCampaignObject(sSQL, oCreature);

 

 

My retrieve call:

 string sSQL =  "SELECT Items FROM "+DBNAME+" WHERE UID = "+ GetTag(oStorage);

        object oCreature = SQLRetrieveCampaignObject(sSQL, lLoc);

 

 

So it writes to the db, but when i reopen the chest, there are no items (my counter says I have 8-10 items, but they don't appear).  Is this the row size affecting it or perhaps something else?

 

I thought I'd start with the basics about the column size and the blob type first as a place to qualify/disqualify as an area of concern.

 

Any thoughts about how to further troubleshooting this?

 

Thanks!

               
               

               
            

[Legacy_BelowTheBelt]

               

Any thoughts?  I figured that BLOB seems to be the correct data type, but would love some insight about the size of that data.

               
               

               
            

[Legacy_meaglyn]

               

My version of aps_include came with SetPersistentObject and GetPersistentObject which do this already. They use blob data type in the table. You might want to check out a newer version of that include file and see how it's implemented.

Edit: of course you may need newer NWNX bits to support that...

               
               

               
            

[Legacy_BelowTheBelt]

               

THanks, though my aps_include has that as well, though the parameters for that call include:  player,tag,name,val,expire.  I didn't want all those variables.  All I want is to set the persistent object and a unique ID for the player.

 

The additional function above just sets the object.  It's technically the same main function as what the aps_include SPO/GPO do to set the object, it's just that the aps_include version includes a validation check that the row exists (to decide whether to Update or Insert) and then sets an overly long SQL string that is too long (for my preference in this instance).

 

Because I'm not appending the pwdata table and am rather updating a separate table dedicated to storage, I don't use all the required parameters that the aps_include SPO/GPO requires.

 

But, both are setting the actual object in the same way.

 

What are the size of data for the object blob that people are seeing in their db's?

               
               

               
            

[Legacy_meaglyn]

               

Sorry, yes. I did not read all the way back to the beginning.  But that said, I'd have just put a wrapper around the existing code. Something like:

void mySaveObject(string UID, object oObject) {
           SetPersistentObject(GetModule(), UID, oObject, "storage");
}

You'd have a few unneeded columns in the table but that won't hurt anything.

 

As to debugging, why not break it down into pieces instead of trying to test the whole thing end-to-end.  DId the creature object come back out right? That would be the first thing to look at. If it did then does it have inventory? Maybe the bug is in the code that you are using to put those items back in the chest etc.

 

Remember too, you are not saving the items, you are saving the whole creature object, which include it's inventory but also includes it's stats, and spells lists and whatnot. I don't have a blob entry handy since my PW is still pre-alpha I don't run it on the DB all the time. But a simple first level character bic file is 13k (with starting PW equipment probably 12 items or so). So 6k for your creature with 8 items is not out of the question to me. Presumably the stored object needs pretty much the same info as the binary bic file.

               
               

               
            

[Legacy_BelowTheBelt]

               

Good idea.  I put in detailed debugging and noticed that some variables that should have been deleted upon saving, weren't. This was preventing proper retrieval of items.

 

I was so focused on figuring out whether or not the read/write was working, I didn't look for other errors.

 

Anyway, it's all good and works like a charm once I fixed the issue.

 

Thanks!

               
               

               
            

[Baaleos]

               The interesting thing is that the blob data is actually the itm file itself - or something similar.

You can actually execute SQL Queries to write the stored blob data out to a file, and vice versa.

select VAL into outfile "C:/myFile.TXT" FIELDS TERMINATED BY '' ENCLOSED BY '' ESCAPED BY '' LINES TERMINATED BY '' STARTING BY '' from pwobjdata where id = 1;

This takes the object data in the 10th entry in pwobjdata and writes it to C:\myFile.txt
If the object was a creature, you could write it out to
C:\MyCreature.bic

If it is an item, it could be wrote to C:\MyItem.itm

You can also load items into the database via
update pwobjdata set val = LOAD_FILE('C:/MyItem.itm') where id = 10

I did a bit of experimenting with persistent object storage - discussed here
http://www.nwnx.org/...opic.php?t=1928


SetPersistentObject - when SCO/RCO is hooked with nwnx - will indeed save the exact state of an object in the database.
It doesnt care about resrefs or item templates in pallette, it saves the object in its current state, with vars, spells uses, colors, appearance changes etc.

From memory - the only thing that doesnt save well - is Placeables.
Creatures, Items work ok etc