Persistant data storage frameworks & NWNx

[Ronan]

How do you guys think we should handle persistant data storate, on PCs and on servers, knowing that some of these datas may be moved to an external database as NWNx4 becomes available for NWN2?

On data stored relative to a PC,
I've coupled things a bit tightly here, and I hope I've made the right choice. Even still, it would be an easy thing to change no.
Should we want move all persistance data to a SQL DB when one becomes available for NWN2, this would break totally:
GetLocalInt( GetPCPersistentStorageObject(oPC), sVarName);
While this could still work, with modifications to GetPersistantPCInt() of course:
GetPersistantPCInt(oPC, sVarName);

The way I've done things now, and what I think is best, is to keep data storage tools tightly coupled with BW's functions (like in the former example) The reasons for this are:

-Local variables are much faster than NWNx2 is at accessing a SQL DB. I can't imagine us moving everything over to an external DB in any situation. If we only move select parts, re-tooling of the original code would be needed regardless of how things are done now.
-The downside of local variables is less effecient memory usage (ie, a string to indentify a single integer), but I don't think local variable size will have a significant impact next to the size of the new heightmaps.
-It reduces the number of wrappers functions needed, improving speed.
-I don't like the idea of critical data which should travel with the PC being stored anywhere but the PC's .bic file itself, for obvious reasons.
-SQL DBs and BW's functions store data so totally differently, would we even wish to use a wrapper compatible with both?

Anyone think otherwise? Obviously, storing everything in a central DB and caching it to a PC (in the form of local variables) when he enters is a possibility. PC persistant data should only be used by the ACR of course, we don't want servers messing with it directly. Servers should use their own DBs.

On server-specific variables,
It would take a ton of effort to wrap the transaction features of a SQL DB in the API NWN gives us. So I don't try, instead using code that would have to be changed if we decide to move to an external DB when one becomes available. The good news is I don't think anything outside of pChests really needs binary transactions, and most people don't even know what they are.

Again, because SQL DBs use such totally different syntax for storing and retrieving data, can we really create wrappers which will work for them? I've currently got every BW campaign variable function wrapped.

On cross-server variables,
Nothing to be said here, we need an external DB for this. The question is, what might we want to move from NWN's storage formats to the external DB, when one becomes available? Logs are one obvious choice. pChests I'm more iffy on, especially with the prospect of patches to the game potentially breaking NWNx.

My thoughts on this (and other "should we wait?" topics) are that if we can achieve the ideal functionality of a system with the tools we have now, we have no reason to wait and see what becomes available. If NWN's API is lacking for some systems, such as PW-wide trading, we should hold off a bit for NWNx4.

Unfortunately, I have no experience with a NWNx-based PW, and I'm not entirely sure of what all it's advantages are, other than cross-server persistance of course. So if anyone can point out any flaws in my reasoning, please do so.

[Fionn]

pChests should stay local BW DB I'm thinking. There is no reason to have the items in SQL, especially with the (minor) risk of incompatability due to patches.

Item tag, value, and owner can be logged to an external DB. If we loose this functionality, it just means lack of tracking. I would actually urge we have two log systems. One for ALFA data like this, and one for server data as we're used to. There is no reason ALFA needs see 95% of the crap in my logs, but they do need to see what is stored in my pChests (I'm sure there will be other data like this).

With the early warnings on TCP traffic, I'm thinking excessive use of local strings on the bic won't be a big deal. Obviously we should load out 10 PCs worse case and do some login/logout/export testing.

*******************************************

One thing that has been discussed in the past is parsing the entire bic to SQL. This allows the server to recreate the bic from an external DB. It also allows changes on the fly to things BW/Obsidian does not expose (e.g. gender). This is, obvioiusly, sure to be broken in almost every patch unless Obsidian is a lot better about reserving space for community editing.

If we can do this, we could also use replication &/or SOAP to transfer PCs between servers. It may mean an extra few weeks to upgrade as patches come out, but that is acceptable to me. We would need to be sure we can easily convert such DB PCs to default bics as a plan b. Likely we wouldn't completely replace the existing functionality, so this would be default functionality.

[Ronan]

pChests should stay local BW DB I'm thinking. There is no reason to have the items in SQL, especially with the (minor) risk of incompatability due to patches.

Agreed. Thats why I went to the trouble of giving the pChest code what I hope will be foolproof reliability.

Item tag, value, and owner can be logged to an external DB. If we loose this functionality, it just means lack of tracking. I would actually urge we have two log systems. One for ALFA data like this, and one for server data as we're used to. There is no reason ALFA needs see 95% of the crap in my logs, but they do need to see what is stored in my pChests (I'm sure there will be other data like this).

Also agreed. The debugging system has a (currently unused, obviously) flag for sending data to a DB, but their default behavior on most error levels and how most will configure them will just send data to the local server log. The 1984 scripts are currently totally blank, as I'm really hoping for a good DB on those. If NWNx4 doesn't pan out for NWN2, one thing we could consider is actually logging PC events using NWN2's Codebase DB. This would have the advantage of seperating logs by PC (database) name. PC logs or debugging data could then be retrieved via a 3rd-party browser (I think there are a few available for Codebase). We wouldn't have central access to logs, but things would be a lot easier than looking through the current server logs.

One thing that has been discussed in the past is parsing the entire bic to SQL. This allows the server to recreate the bic from an external DB. It also allows changes on the fly to things BW/Obsidian does not expose (e.g. gender). This is, obvioiusly, sure to be broken in almost every patch unless Obsidian is a lot better about reserving space for community editing.

If we can do this, we could also use replication &/or SOAP to transfer PCs between servers. It may mean an extra few weeks to upgrade as patches come out, but that is acceptable to me. We would need to be sure we can easily convert such DB PCs to default bics as a plan b. Likely we wouldn't completely replace the existing functionality, so this would be default functionality.

This seems like a lot of work for little benifit to me, but if someone wants to tackle it I wouldn't object.

[ç i p h é r]

I had been hoping we would discuss persistence in more detail in each feature spec, not just WHAT is stored but HOW and WHERE it's stored (discuss schemas for a possible central database). Storing data on a PC ensures that it will be recorded with every save to the character vault. I don't think all PC data we may want to preserve will need to be updated with that kind of frequency, and it's this data that's best suited for a central database. It would only get updated as needed. Let's keep this in mind when working on our specs.

[Ronan]

I had been hoping we would discuss persistence in more detail in each feature spec, not just WHAT is stored but HOW and WHERE it's stored (discuss schemas for a possible central database).

In my mind there is some ambiguity as to what our storage formats will be like when NWN2 is released, and as it ages. This thread was more about whether or not we should create appropriate wrappers to make expansion into other storage formats as painless as possible, and if we should bank on NWNx4 being available when ALFA goes live.

Storing data on a PC ensures that it will be recorded with every save to the character vault. I don't think all PC data we may want to preserve will need to be updated with that kind of frequency, and it's this data that's best suited for a central database. It would only get updated as needed. Let's keep this in mind when working on our specs.

Well, I had only wanted to store critical data on a PC itself. Things which the PC needs in order to play the game. I think if the central DB does down, PCs should not be kept from playing. As I see it we've got four possible sources of persistant data storage, and this is how I see the data being broken up,

PC's bic file
Data related to the PC which must be available cross-server.

NWN's CodeBase DB
Data which does not need to leave the server (pChests, static quest data, spawn persistance, etC).

Local NWNx-powered SQL DB
Data which does not need to leave the server (pChests, static quest data, spawn persistance, etC).

Remote NWNx-powered SQL DB
Non-critical data which needs to be available globally, such as authorization and logging.

So the biggest question in my mind is, what, if anything, do we store on a local SQL database?

[ç i p h é r]

This thread was more about whether or not we should create appropriate wrappers to make expansion into other storage formats as painless as possible, and if we should bank on NWNx4 being available when ALFA goes live.

Yeah I think having wrapper functions to simplify switching formats is a good idea, where possible.

So the biggest question in my mind is, what, if anything, do we store on a local SQL database?

I think a local SQL database would be the preferred option over using CodeBase. Data in this format is far easier to get at for almost anything, from making manual changes to the data (ie pChest wipes) to reporting. Add remote querying and standardized tables, then even Admin could access the various information they might need/want to conduct case reviews.

[Ronan]

I think a local SQL database would be the preferred option over using CodeBase. Data in this format is far easier to get at for almost anything, from making manual changes to the data (ie pChest wipes) to reporting. Add remote querying and standardized tables, then even Admin could access the various information they might need/want to conduct case reviews.

Feature-wise, I agree. But I'm worried about making critical portions of the ACR require 3rd-party tools. There are problems with NWNx2 with 1.67 which are not corrected, for example. I'm wondering what sort of tools exist for browsing CodeBase databases?

Its a bit off topic, but we all know the dangers of the type of developement we are doing now: if new features or options are released in the next few years, we may be stuck on something old. Thats exactly the problem with ALFA today, and I'd prefer not to lead it down that path again. Thats why I'm warry of implementing any system with which we can't produce our ideal behavior, since we may want to change it in the future.

Modularity of course can help this a lot, but its really difficult to wrap the CodeBase DB in a way which is compatible with a SQL DB.

[ç i p h é r]

Valid concern and in this case, I guess we need to weigh that against any expected benefits. IIRC, you can open up and view a CodeBase db with MS Access, but you cannot actually modify it. I don't know what sorts of tools are out there that we can work with, but if servers record the information we want recorded in the format and tables we need them in, then it becomes possible to do the sorts of things we're contemplating, albeit not as nicely or neatly as SQL.

One other thing. I think NWN1 locks db files that you access through the game, so if that's true (been a while and I can't recall for certain atm) that would obviously prevent anyone from even trying to make changes to the db with the game running, assuming it was reasonably easy to do so with CodeBase in the first place.

[Ronan]

One other thing. I think NWN1 locks db files that you access through the game, so if that's true (been a while and I can't recall for certain atm) that would obviously prevent anyone from even trying to make changes to the db with the game running, assuming it was reasonably easy to do so with CodeBase in the first place.

My linux server seems to be able to read and write DB files while the server is running. I'd imagine it picks up and drops the lock as needed, so I guess you'd have to open them in read-only mode in order to keep from interfering with the server.

Another thing I worry about is maintaining the server-side databases. Most of our current server teams don't have anyone profecient in SQL DB use, and I've admittedly not used one much outside a classroom. I'm not sure what sort of difficulties to expect when it comes to updating schemas and that sort of thing.

I think I'd like to see some comments on current users of SQL DBs in NWN1, perferably multi-server PWs. Can anyone point me in the direction of a good place to search for this sort of discussion before I start looking?

[ç i p h é r]

A few places that come to mind are:

1. NWNX forum

http://www.nwnx.org/phpBB2

2. NWN-MySQL (Fast French) Guild

http://nwn.bioware.com/guilds_registry/ ... l?gid=7729

This is in fact where I started my own experiments with NWNx b/c the MySQL optimizations seemed to have an enormous performance edge over standard NWNx. However, the code made my eyes bleed and why I ultimately reverted back to NWNx. There was some worthwhile analysis on the guild forum though at the time.

3. NWNX Guild

http://nwn.bioware.com/guilds_registry/ ... l?gid=3087

4. MySQL website (includes MySQL syntax and docs)

http://dev.mysql.com

I'm not sure what specific concerns you have and I've never participated in a multiserver PW outside of ALFA, but I can say from my own experiences that creating and updating schemas is fairly trivial if you understand the syntax. Generally speaking, you can alter or add columns to existing tables but you can't remove columns from them, though this could very well be DB dependent.

[Hialmar]

I could help here if you want.

I teach a little about MySQL (connected to PHP) and I work a little on it for my research. And this last part will increase as I'm more and more going to do research on MMOGs.
Talking about that, have you looked at what the people working on opensource MMOGs and private servers (like l2j for lineage 2 and such...) do ?

Then of course we have an expert around ALFA.
*nudges Baalster*

[Ronan]

I'm not sure what specific concerns you have and I've never participated in a multiserver PW outside of ALFA, but I can say from my own experiences that creating and updating schemas is fairly trivial if you understand the syntax. Generally speaking, you can alter or add columns to existing tables but you can't remove columns from them, though this could very well be DB dependent.

Yeah, I've updated and worked with schemas with about a dozen or so relations in them on Orcale 9i. Sometimes you might need to alter or reconfigure data a bit to get it to fit if you change any functional dependencies, I suppose. My main issue with local SQL databases isn't how to do it, its the HDMs and hosts.

I've had a hard time walking some through simple things such as cutting and pasting constants into alfa_options. Getting remote access would help, but I don't think we could count on that. Its just another piece of software hosts will have to become familiar with in order to update their servers, and currently there are still some that haven't updated their haks from the May 29th hak update. Of course the fewer servers we have the easier this will be.

I'm thinking if NWN2 allows us to delete data from a CodeBase DB without having to delete the entire database as is done now, we may want to stick with that for the local-server DBs.

[Fionn]

Do we have any utils for 'CodeBase'? I looked over their website, but none of the DBAs I know have heard of it.

[Ronan]

Do we have any utils for 'CodeBase'? I looked over their website, but none of the DBAs I know have heard of it.

I can't seem to find anything that doesn't cost money, but I'm thinking there must be something.

Another idea is to include a "version" tuple which marks the version of the local SQL DB. Then if the ACR hak is updated to use a newer schema, OnModuleLoad scripts detect it and automatically modify the schema as needed.

[ç i p h é r]

Yeah, hosting an SQL database will certainly increase the competency requirement of the host. However, assuming just about anyone can handle a simple application install, we may be able to manage the schema from the application OnModuleLoad (ie create the tables if they don't exist and edit them if needed) if NWNx does not prohibit or limit SQL queries. I haven't tested this but as long as you can execute an SQL query from the application, it should theoretically work. It would effectively be "database initialization".

For CodeBase, we can actually delete records, just not in real time. To delete records:

1. mark records for deletion in the database using the appropriate Delete*() db function
2. pack the database to purge all marked records using the DataPack tool (in the utils subdirectory of the main NWN directory)

Since the DB is the same, the current DataPack utility should still work, assuming it's not bundled with NWN2 as well. Again, this can't be done with the DB in use (or with NWN running IIRC).