Saving Key/Value Pairs (cont'd)

[Undertowe]

This is a continuation of the thread found here.

Things will probably be more clear if I explain a little bit about my data model and the reasoning behind my approach.

One of my tables is called characters, and is used to store information that is intrinsic to every PC, e.g. name, location, current hit points, etc. Each record in this table represents one PC, with columns like name, location, hp_current, etc. to record that info.

However, there is a potentially limitless number of other variables that I might want to track for any given PC. For example, whether or not a PC is drunk. I didn't want to add a new column to characters for every new variable, because my table would eventually become large and unwieldy. So, I decided to create another table called character_data. This table is very similar to the pwdata table included with APS, consisting of "key/value" pairs. It's purpose is to store information that is not "instrinsic" to every PC.

Now, instead of constantly reading from and writing to the db throughout a player's session, I decided that when a player joins the server I would read all of the variables from characters and character_data and store them locally on the PC. Then, throughout the player's session, these variables are modified on the PC. Periodically, and when the players leaves the server, the db is updated from the current values stored on the PC. This is all done in the name of efficiency.

So, this brings me to the problem I was facing. When a player joins the server, it's a simple matter to read from characters and character_data and write the relevant variables to the PC. However, when I want to update character_data from the variables on the PC, I run into a small problem. How can I know which variables to look for on the PC? I can think of several inelegant ways to do this, but I was wondering if anyone has faced a similar problem and if so then how they solved it.

As it stands, I'm thinking that the best approach would be to read/write the "non-intrinsic" variables to the db as they are changed during a player's session, without using the intermediate step of storing them on the PC. Only the variables in characters will be stored on the PC.

OK, I hope this makes sense now. Someone out there must have faced a similar situation. I'm not doing anything radical, just following recommendations.

[Senalaya]

Here my 2 cents to that:

The only chance to 'browse' through the variables on the PC would be to use string vars with names like 'VAR1' ... 'VAR10' and a local int 'MAXVAR', so that you can use a loop to access them. You could then start each string with the SQL column name, followed by a seperator and the stringified value.

On the other hand, why would you want to /periodically/ write values into the DB, when they havn't changed? The smarter way is to just update the ones, you changed (locally and into the DB). In that case, you also know what var you are dealing with. Just script some wrappers like 'UpdateLocal<var>()', that use SetLocal<var>() and then update the DB column.

[JeroenB]

As I explained in the other forum, the persistency that come with the APS system do exactly that. For efficiency reasons you don't have to store all variables on the player instead of looking them up with a simple query in the database.

So, what most PW's do is making use of those persistency functions. They can store all types of variables in the database in the pwdata table. This table is designed for storing key/value pairs per object, and not limitted to players, but it enables you to store values on every object.

If you look at efficiency: most variables you store on the characters are used at max once per second. The connection to your database is so fast that the server won't notice any performance issues. The way I described above only handles one key/value at a time when requested. Your method requires a loop through all your variables (like Senalaya described) and insert/update them in the database every now and then plus loading at enter and saving when the character leaves. So, this method is not much more efficient then storing the values right in the database.

I hope this is a bit clear now.

[Blacksting]

The easiest way to store variables that you wish to "browse" through is by using the nwnx_hashset.dll.

By creating a hash set of variables for a PC (naming the hashset PCvariables+charactername and storing it on the module) you can EASILY use get first hash key ... get next hash key to find what you have in the list.

If you need more explanation I will give it.

[JeroenB]

That method is also perfectly valid. But one major drawback in storing these kinds of sensitive information in memory is that when the server crashes, the memory is gone and so are the values of your variables. So when a character managed to succeed a part of the quest, lost a token and then the server crashes, the character has to start all over and he's not going to like that.

[Blacksting]

I agree

The knowledge of using hash sets to "group" browsable variables is something that hopefully he will find other uses for as well. I began using it in my DM Runtime Language when I got tired of running loops to clean up NWN Local variables. DestroyHashSet did the trick in one line. Eventually I found many reasons to group variables.

Has anyone done any real stress testing on the hashset functions to try and bring NWN to a screeching halt?

[JeroenB]

I believe the ingredient system on Avlis does some stress testing for NWN as much users are busy forging all kinds of items based on the stuff in the hashset. So everytime a user makes an item, the hashset must be consulted to see if the right ingredients are available.

[Undertowe]

First off, thank you, everybody, for your responses.

I've forgotten exactly where I came upon this notion, but I was under the impression that it is more efficient to read/write variables that are stored locally than read/write records in the db. Is this not true?

That was my motivation for storing the values on the PC OnClientEnter, having my scripts work with the local variables instead of the db, and then updating the db from the local variables periodically, and OnClientLeave.

However, I can see the drawback. Using my approach, you will always read/write each value at least once during a player session. This would happen whether or not the value had been changed. Not very efficient, I admit.

So, as it appears I had some mistaken assumptions, what IS the most efficient way to do this? If we consider storing db variables locally on the PC as a form of "caching", what variables, if any should be cached? Only the most frequently used ones?

In my data model, every player and PC has a unique ID. If I'm frequently using these IDs to reference various tables, would it make sense to at least store THEM on the PC? Otherwise, when I wanted to access just about any variable in the db, I would have to look up the ID for the player and character.

Hashsets sound intriguing. I'll probably look further into the subject.

JeroenB, I'm not sure why you keep telling me about the APS functions. I'm well-aware of them, and have chosen not to use them. Most of them simply don't fit my needs, seeing as how I'm not using the pwdata table. I've been writing all my queries myself, to work with my db structure.

[Timendainum]

Undertowe,

I took an approach somewhat simular to yours.

I created seperate tables for storing key/value pairs. I ended up with one for PCs, one for items etc...

I then modified the APS include to meet my needs. The old APS table had few unneeded fields.

What I did was created a buffered read system. When I read the variable using my flavor of GetPersistentInt, I first look for the local, if a local exists, I don't re-read from the DB. If the local doesn't exist, I read the value from the DB, and then put the value on the PC object as a local.

When I call my SetPersistentInt() function, I set the local, and the DB.

I've not done any load testing, but the idea is to help eliminate excessive database reads. Who knows if it will actually help or not.
_________________
"Good, bad... I'm the guy with the gun."
TKW

[JeroenB]

I just used the APS as example. It is ofcourse possible to write your own set of functions and tables that enable you to store the information you want.

[Undertowe]

So, it seems there are many approaches to storing and retrieving variables from a DB:

(1) OnClientEnter, read all relevant variables from the DB and store them on the PC object. All scripts work with these local variables. Periodically, and OnClientLeave, the variables stored on the PC object are written back to the DB.

(2) OnClientEnter, read variables from the DB and store on PC object. All scripts read from local variables. However, when a variable needs to be modified, it is set both on the PC object and in the DB.

(3) When a variable is needed, it is looked for on the PC object first. If not found, it is read from the DB and set on the PC object. When a variable is modified, it is set on the PC object and in the DB.

(4) Variables are read from and written directly to the DB in "real-time". No variables are stored locally on the PC object.

(5) Only frequently used variables are stored on the PC object. For instance, if your data model assigns each PC a unique ID, that ID is read from the DB OnClientEnter and set on the local PC object. Rather than having to continually get the ID from the DB when running a query, the ID is read from the PC object. If these frequently used variables need to be updated, it is done with either "real-time" writes or periodic writes to the DB. But, for variables like ID, which will never change, DB writes are not necessary.

I'm sure there are many more valid approaches.

So, I ask you... with an eye towards efficiency, which is the best approach?

[JeroenB]

4 or 5, this really depends on what you mean with regular usage of variables. When a variable is used only once in a few seconds (and longer periods included ofcourse) the direct database method is best, as it saves a lot of overhead. When you access variables a few times per second then local storage will be best.

Maybe Papillon knows what the difference is in milliseconds between the normal local storage and in a database. But I assume that it is an almost negligible factor, so I would say, try to save yourself from overhead where possible (just my idea ofcourse).

[Lokey]

[Timendainum]

Lokey, to expand your question. Could there be a way to benchmark database queries?

We can use the profiler to benchmark script running times, but it would prove interesting to have a DB benchmark as well.
_________________
"Good, bad... I'm the guy with the gun."
TKW

[monezz]

To avoid problems and inconsistencies with crashes, I always store the character and the persistant data at the same time. Savepoints are when a character leaves the game, and at a certain time interval.
If a crash occured, the state of the character is set back to the state of the last savepoint.

I kept this "rule" in my mind all the time during the design of all the database systems.
If I would want to build a metadata structure (key/value pares) to store character parameters in, I would retrieve all parameters when a character enters the game and store them on the character.
The values should be stored when a savepoint occurs.

more in depth:
on_client_enter:
select character_keys.name, character_values.value, character_keys.datatype
from character_keys
, character_values
where character_keys.id = character_value.cky_id
and character_values.cha_id = cha_id

loop through the values and store them as local variables, something like:

if (getData(3) == 'string' ){
setLocalString(character,getData(1),getData(2));
}elseif ..


On the savepoint, you could get all character_key names fromthe database and retrieve them fromthe character object. If a local variable doesn;t exist, just don;t store it.
Best way to do this is to delete all variables from the database and insert the new values afterwards. That aproach is easier, cleaner and faster than trying to mkae update statements.

I'll try to implement this aproachin the near future and stress test it for performance.
Its just an idea, hope it gives an alternative aproach.

Monezz