logo logo

 Back to main page

The NWNX Community Forum

 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
 
NWNX/MySql Persistent Storage?
Goto page 1, 2  Next
 
Post new topic   Reply to topic    nwnx.org Forum Index -> Scripts and Modules
View previous topic :: View next topic  
Author Message
Ciretose420



Joined: 31 Aug 2012
Posts: 2

PostPosted: Fri Aug 31, 2012 20:19    Post subject: NWNX/MySql Persistent Storage? Reply with quote

I'm sure this has been asked before. But I cannot find anything on vault, google, or these forums, that fits my needs. Can someone point me in the direction of a NWNX MySql persistent chest system, that isn't based on the markshire design?
Preferably, I'd like to have someone to chat with who knows scripting and would be willing to answer my questions about designing my own chest system. If you're interested in assisting me with this, send me a PM on here.

Thanks guys.
Back to top
View user's profile Send private message
Baaleos



Joined: 02 Sep 2007
Posts: 830

PostPosted: Fri Aug 31, 2012 22:59    Post subject: Reply with quote

its not actually very hard to persistently store items.

The easiest way to store items, is to use a mule creature to hold the items.

AddHenchman and OpenInventory are the functions of interest.
Pack his inventory full of items, then when done, use

StoreCampaignObject on the creature.

This will store the creature, along with his items to the database.

This is alot easier than storing each item individually

RetrievePersistentObject or RetrieveCampaignObject etc - will re-summon the creature, at which point you can re-add the creature to party, and then open the inventory.
Back to top
View user's profile Send private message
Ciretose420



Joined: 31 Aug 2012
Posts: 2

PostPosted: Sat Sep 01, 2012 1:51    Post subject: Reply with quote

I realize that's the easiest way. But I've seen it done with static chests. And I think it looks better. How much harder is it to do with a regular chest?
Back to top
View user's profile Send private message
Ravine



Joined: 26 Jul 2006
Posts: 105

PostPosted: Sat Sep 01, 2012 17:03    Post subject: Reply with quote

Easier. I've done it with virusman's chest-storing functions in nwnx ( http://www.nwnx.org/phpBB2/viewtopic.php?t=1083&postdays=0&postorder=asc&start=15 ), no need for 'mules' anymore (at least under linux). But probably you have to design it yourself to suit your needs.
Back to top
View user's profile Send private message
Baaleos



Joined: 02 Sep 2007
Posts: 830

PostPosted: Mon Sep 03, 2012 11:08    Post subject: Reply with quote

a Chest or a Mule creature would probably work just as well.
Anything with an inventory should have the same capabilities.

Even if they didnt, you could simple spawn the chest,
on the onClosing event, transfer items from the chest to a back-end mule creature. and then persistently store the mule creature.

Or
Just store the chest directly.
Back to top
View user's profile Send private message
PlasmaJohn



Joined: 04 Mar 2005
Posts: 70
Location: The Garage

PostPosted: Wed Sep 05, 2012 16:00    Post subject: Reply with quote

We've had a lot of trouble with a placeable inventory based system. The OnClose event is unreliable. When you have stackable items, partial stacks automatically recombine when added to an inventory. Unfortunately the OnDisturbed event fires -after- that happens making it impossible to keep our counts accurate which would often cause massive item duplication.

We have mostly abandoned this method and have adopted a dialog based approach.
Back to top
View user's profile Send private message
virusman



Joined: 30 Jan 2005
Posts: 1020
Location: Russia

PostPosted: Wed Sep 05, 2012 23:22    Post subject: Reply with quote

Ravine was referring to the latest additions to NWNX ODBC, with them you can store the entire placeable with its inventory and properties, without using OnDisturbed and handling every item individually.
_________________
In Soviet Russia, NWN plays you!
Back to top
View user's profile Send private message Visit poster's website Yahoo Messenger
PlasmaJohn



Joined: 04 Mar 2005
Posts: 70
Location: The Garage

PostPosted: Thu Sep 06, 2012 6:58    Post subject: Reply with quote

Neat idea... but. Very Happy

Writing out the whole inventory just rubs me the wrong way. Yeah, I know, NWN often forces us into extreme measures.

Our menu based chests were a byproduct of a persistent merchant project. They produce an alphabetized list of the contents and we're not limited to the standard placeable inventory capacity (ours can be any number of 'squares'). As part of it, I added a self-serve menu where the owner can upgrade the capacity if they have the gold to pay for it without having to submit a Player Housing revision.

Somebody told me that they preferred the realism of the graphical chest. *shrugs* Ok, so it's not to everybody's taste. The only feature missing is the red highlighting telling you that you can't use an item.
Back to top
View user's profile Send private message
Ravine



Joined: 26 Jul 2006
Posts: 105

PostPosted: Thu Sep 06, 2012 11:41    Post subject: Reply with quote

Hm, not really 'writing out the whole inventory'. Simply save the whole placeable-object. It only takes one row in the DB per user. I assume, your approach is to save every item individually to the DB.
As i understand, your conversation-driven bank lists the contents by item-name. For example, we are using item-creation scripts, which generates random 'fantasy-names', so the properties must have to be seen by players.

PlasmaJohn wrote:
Neat idea... but. Very Happy

Writing out the whole inventory just rubs me the wrong way. Yeah, I know, NWN often forces us into extreme measures.

Our menu based chests were a byproduct of a persistent merchant project. They produce an alphabetized list of the contents and we're not limited to the standard placeable inventory capacity (ours can be any number of 'squares'). As part of it, I added a self-serve menu where the owner can upgrade the capacity if they have the gold to pay for it without having to submit a Player Housing revision.

Somebody told me that they preferred the realism of the graphical chest. *shrugs* Ok, so it's not to everybody's taste. The only feature missing is the red highlighting telling you that you can't use an item.
Back to top
View user's profile Send private message
PlasmaJohn



Joined: 04 Mar 2005
Posts: 70
Location: The Garage

PostPosted: Thu Sep 06, 2012 16:16    Post subject: Reply with quote

Ravine wrote:
Hm, not really 'writing out the whole inventory'. Simply save the whole placeable-object. It only takes one row in the DB per user.

Which is saving out the whole inventory. You're just encoding it into one huge blob. I want to say that's a bad habit to get into. However, doing it "right" probably performs worse due to how inefficient the NWN script VM is.

For something with infrequent updates that's fine, in fact it's something we need for a different system but could never get working. Our plugins are pretty old, so I should revisit that.

Quote:
I assume, your approach is to save every item individually to the DB.

We have several tables. The item blob is stored in an item instance table keyed by the hash returned by the SCORCO hook. A single bit difference often causes a hash to become wildly different (eg. setting the plot flag). The NWN engine stores local vars on items for inventory management. To reduce the number of unnecessary duplicates I remove all temporary effects, remove the superfluous local vars and reduce stack sizes to 1 (that's just for the instance table, the container table records the proper inventory delta).

For the dialog based containers, I have a companion table that stores a unique id, the template item's hash, the template item's name, its max stack size, and its volume in inventory squares.

The container table stores a record containing the container id, the item id and the inventory count of that item. Merchants have a table that contains buy/sell pricing, min/max inventory targets.

Quote:
As i understand, your conversation-driven bank lists the contents by item-name. For example, we are using item-creation scripts, which generates random 'fantasy-names', so the properties must have to be seen by players.

If an item has a custom name, that's reflected in the dialog. In addition to that, there's an examine option where the system spawns a copy of the item at an inaccessible point that is within visual range, assigns an examine action to the user and destroys the item.
Back to top
View user's profile Send private message
maddogfargo



Joined: 03 Nov 2009
Posts: 49

PostPosted: Thu Oct 11, 2012 4:42    Post subject: Reply with quote

Based on what I've read you cannot save a placeable chest to the database in a blob field. It has to be a creature. This is why some storage systems use a mule creature with the Mimic chest creature appearance.

It is simple, and I used it for 5+ years on NWNDB which is FoxPro DBF based with no issues. Routine maintenance repacks helped greatly.

Also used the NWNDB for player housing, and it supported one player who loved to push the limits having a castle with 800+ placeable objects stored to the database. Again for 4-5 years with no incidents of corruption or problems.

This worked fine for us, but the flipside is I would have preferred to use SQL for better performance. A lot of the systems would need to be re-written though so we just decided to use it until it broke and turns out nothing broke. Wink
_________________
* illegible scribble *
Back to top
View user's profile Send private message
Tsais



Joined: 18 Sep 2017
Posts: 6

PostPosted: Thu Oct 12, 2017 2:02    Post subject: Reply with quote

Regarding this comment about saving a placeable and its inventory (using NWNX2):

Quote:
Ravine was referring to the latest additions to NWNX ODBC, with them you can store the entire placeable with its inventory and properties, without using OnDisturbed and handling every item individually.


... I was hoping someone could offer a bit more detail on how that can be done? I found the below functions proposed in Pastebin, but it's not clear to me how exactly they're to be used, and they're not in aps_include. Not really sure what the SQL is supposed to look like here?

Code:
void SQLSCOExec(string sSQL, object oObject)
{
    SetLocalString(GetModule(), "NWNX!ODBC!SETSCORCOSQL", sSQL);
    switch(GetObjectType(oObject))
    {
        case OBJECT_TYPE_CREATURE:
        case OBJECT_TYPE_ITEM:
            StoreCampaignObject("NWNX", "-", oObject);
        break;
        case OBJECT_TYPE_PLACEABLE:
        case OBJECT_TYPE_STORE:
        case OBJECT_TYPE_TRIGGER:
            SetLocalString(GetModule(), "NWNX!ODBC!STOREOBJECT", ObjectToString(oObject));
    }
}
 
object SQLRCOExec(string sSQL, location lLocation, int nObjectType)
{
    SetLocalString(GetModule(), "NWNX!ODBC!SETSCORCOSQL", sSQL);
    vector vLocation = GetPositionFromLocation(lLocation);
    switch(nObjectType)
    {
        case OBJECT_TYPE_PLACEABLE:
        case OBJECT_TYPE_STORE:
        case OBJECT_TYPE_TRIGGER:
            SetLocalString(GetModule(), "NWNX!ODBC!RETRIEVEOBJECT", ObjectToString(GetAreaFromLocation(lLocation))+""+FloatToString(vLocation.x)+""+FloatToString(vLocation.y)+""+FloatToString(vLocation.z)+""+FloatToString(GetFacingFromLocation(lLocation)));
            return GetLocalObject(GetModule(), "NWNX!ODBC!RETRIEVEOBJECT");
        break;
        default:
            return RetrieveCampaignObject("NWNX", "-", lLocation, OBJECT_INVALID);
    }
    return OBJECT_INVALID;
}
Back to top
View user's profile Send private message
virusman



Joined: 30 Jan 2005
Posts: 1020
Location: Russia

PostPosted: Thu Oct 12, 2017 9:27    Post subject: Reply with quote

Here is a snippet from my persistency system code:

Code:
void PSSaveObject(object oObject)
{
    int nID = PSGetObjectID(oObject);
    if(!nID)
        nID = PSRegisterNewObject(oObject);
    if(nID)
    {
        SQLSCOExec("UPDATE ax_psobjects SET area='"+GetTag(GetArea(oObject))+"', location='"+LocationToString(GetLocation(oObject))+"', object=%s WHERE id="+IntToString(nID), oObject);
    }
}

object PSRestoreObject(object oObject)
{
    int nID = PSGetObjectID(oObject);
    if(nID)
    {
        WriteTimestampedLogEntry("Restoring object "+IntToString(nID)+"...");
        SQLExec("SELECT id, type, location, version FROM ax_psobjects WHERE id="+IntToString(nID)+" AND location IS NOT NULL AND object IS NOT NULL AND version>0");
        if(SQLFetch())
        {
            nID = StringToInt(SQLGet(1));
            int nType = StringToInt(SQLGet(2));
            location lLoc = StringToLocation(SQLGet(3));
            int nVersion = StringToInt(SQLGet(4));
            object oNewObject = SQLRCOExec("SELECT object FROM ax_psobjects WHERE id="+IntToString(nID), lLoc, nType);
            if(GetIsObjectValid(oNewObject))
            {
                PSUnsetObjectID(oObject);
                AssignCommand(oObject, SetIsDestroyable(TRUE));
                DestroyObject(oObject);

                PSSetObjectID(oNewObject, nID);
                if(GetIsObjectValid(oObject))
                {
                    SetLocalString(oNewObject, "ps_mlocation", GetLocalString(oObject, "ps_mlocation"));
                    SetLocalInt(oNewObject, "ps_mod_object", GetLocalInt(oObject, "ps_mod_object"));
                }
                SetLocalInt(oNewObject, "ps_initialized", 1);
                PSInitObject(oNewObject, nID);
                return oNewObject;
            }
        }
    }
    return OBJECT_INVALID;
}
Basically, you can do any INSERT/UPDATE query with SQLSCOExec; put %s where the object BLOB should be inserted.
For SQLRCOExec, do a SELECT query that returns one column - the object BLOB.
_________________
In Soviet Russia, NWN plays you!
Back to top
View user's profile Send private message Visit poster's website Yahoo Messenger
Tsais



Joined: 18 Sep 2017
Posts: 6

PostPosted: Thu Oct 12, 2017 21:15    Post subject: Reply with quote

Thanks for the examples! Unfortunately, I'm still not able to get it working. Here's the simple test I tried:

Code:
CREATE TABLE `containers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag` varchar(45) NOT NULL,
  `val` blob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Then I put this on a chest's OnClose event.

Code:
void main()
{
    string sTag = GetTag(OBJECT_SELF);
    string sSQL = "SELECT id FROM containers WHERE tag='"+sTag+"'";
    SQLExecDirect(sSQL);
    if (SQLFetch() == SQL_ERROR)
    {
        SQLSCOExec("INSERT INTO containers (tag, val) VALUES ('"+sTag+"', %s)", OBJECT_SELF);
    }
}


I drop an item in the chest, close it. The ODBC log shows:

Code:
o Got request: SELECT id FROM containers WHERE tag='x2_medium_Chest3'
o Sent response (0 bytes):
o Got request (scorco): INSERT INTO containers (tag, val) VALUES ('x2_medium_Chest3', ~s)


... but no record is inserted, and no indication of an SQL error. Sad Is there anything obviously wrong with what I'm doing?
Back to top
View user's profile Send private message
Terra_777



Joined: 27 Jun 2008
Posts: 216
Location: Sweden

PostPosted: Thu Oct 12, 2017 21:30    Post subject: Reply with quote

Can only store creatures and or items. 'x2_medium_Chest3' looks like a peaceable.
_________________
I dun have any signature, I'm happy anyway.
Back to top
View user's profile Send private message Send e-mail MSN Messenger
Display posts from previous:   
Post new topic   Reply to topic    nwnx.org Forum Index -> Scripts and Modules All times are GMT + 2 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group