BelowTheBelt wrote...
Thanks, all.
With SQL, I've been slow to fully use it (rather than a database object) for PC variables because of the transient nature of players and the need to archive. To my mind it seems that normally with SQL, there ends up being lots of different DB tables created to house different sorts of data. Not sure if this aids in the speed of the reading or not, but conceptually, I'd rather have a single DB table with each record being a unique PC and having lots of data columns containing different data about that PC. This seems like it would be the easiest to manage and modify.
That's part of the flexibility of SQL. You can set up tables however you like. We have some 84 tables, but you could certain set up a table for each player, or each character, automatically - it all depends on wha you need.
Here are our tables, by way of comparison:
+----------------------+
| Tables_in_nwn |
+----------------------+
| arena |
| bankchest_items |
| delete_block |
| donor_list |
| fkymessageboard |
| guild_list |
| hardcore |
| hgll_feats |
| market |
| objdata |
| pcvars |
| plvars |
| portrait_duplicate |
| pwdata |
| qs_quests |
| qs_status |
| qs_synonyms |
| reinc_items |
| reinc_slots |
| reinc_subexception |
| respawn_loss |
| rip_augments |
| rip_augprops |
| rip_augvars |
| rip_props |
| rip_safe_patterns |
| server_list |
| simtools |
| sort_100 |
| sort_110 |
| sort_111 |
| sort_112 |
| sort_113 |
| sort_114 |
| sort_115 |
| sort_116 |
| sort_117 |
| sort_118 |
| sort_119 |
| sort_210 |
| sort_211 |
| sort_212 |
| sort_213 |
| sort_214 |
| sort_215 |
| sort_216 |
| sort_217 |
| sort_218 |
| sort_219 |
| sort_310 |
| sort_311 |
| sort_312 |
| sort_313 |
| sort_314 |
| sort_315 |
| sort_316 |
| sort_317 |
| sort_318 |
| sort_319 |
| sort_410 |
| sort_411 |
| sort_412 |
| sort_413 |
| sort_414 |
| sort_415 |
| sort_416 |
| sort_417 |
| sort_418 |
| sort_419 |
| sub_abilities |
| sub_appearances |
| sub_feats |
| sub_languages |
| sub_list |
| sub_skills |
| sub_usable |
| user_early |
| user_list |
| user_message_history |
| user_messages |
| user_passwords |
| user_totals |
| wallet_balance |
| wallet_history |
The sort tables are used for on-the-fly sorting of lists, one per server instance (for instance, alphabetizing feats in the legendary leveler, or spells in the spell swapper), and are usually empty. Note that each table is defined according to the specific needs of the user, with as many columns of whatever data type you want to store. Any way you set it up, it's pretty easy to manage.
Also, if a record hadn't been accessed in a period of time (6 months, maybe), I could then archive a single record to another file, rather than having to archive many different tables that would contain little bits of data about a player.
I'm a little unclear on the terminology you're using, but bloat really isn't an issue. We use the database extensively, with one of if not the largest servervaults out there, and are still only at a couple Gigs - and we don't purge back records, ever (we've had players return after a 6 year lull). If we wanted to, though, it'd be easy, whether or not we'd set up one table per character, per player, or for all pc variables (the last is what we did). This is my point - SQL is enormously flexible, allowing you to do whatever it is you want to do with relative ease.
I'll offer an example of how we would purge old records, which also allows me to highlight SQL's one downside - the need to forge an indelible link between bic file and database table. We do this via a Unique ID, which is set in the character's tag field (otherwise unused), along with some other data. It's based on UNIX time, server number, and an incrementing counter, so is guaranteed unique. It's what is referenced when the pc enters the server, to load stored vars onto it. Here's an excerpt from our pcvars table (first, the table info):
mysql> describe pcvars;;
+----------+-----------------------------------------------------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------------------------------------------------------+------+-----+-------------------+-------+
| pcv_uid | varchar(32) | NO | PRI | NULL | |
| pcv_type | enum('int','float','string','object','vector','location') | NO | PRI | NULL | |
| pcv_key | varchar(64) | NO | PRI | NULL | |
| pcv_val | text | YES | | NULL | |
| pcv_last | timestamp | NO | | CURRENT_TIMESTAMP | |
+----------+-----------------------------------------------------------+------+-----+-------------------+-------+
Here are a few sample rows:
mysql> select * from pcvars limit 5;
+------------------+----------+---------------------+---------+---------------------+
| pcv_uid | pcv_type | pcv_key | pcv_val | pcv_last |
+------------------+----------+---------------------+---------+---------------------+
| 100-1255280229-1 | int | Filters | 320 | 2010-07-04 09:47:53 |
| 100-1255280229-1 | int | FKY_CHAT_LANGBASE | 1 | 2009-10-12 07:44:56 |
| 100-1255280229-1 | int | FKY_CHAT_TRUEAPPEAR | 6 | 2010-07-04 05:38:11 |
| 100-1255280229-1 | int | Guild | 34 | 2010-07-29 09:59:15 |
| 100-1255280229-1 | int | ResumeRests | 1 | 2009-11-28 02:36:00 |
+------------------+----------+---------------------+---------+---------------------+
Now, I just pulled up the first 5 of 580,612 rows in the table (count(*) is another very handy MySQL feature), which all happen to belong to the same pc (identical uid column). The variable name is in the key column, and the value in the val column (note all columns are prefixed by pcv_ for the purpose of joins (table comparisons)). The pcv_last column is when the variable was last modified - in most cases, when it was set. The type column is pretty self exaplanatory (type of variable). If I wanted to set up a system that purged old pc vars, I would maintain a table that tracked last login date of each UID. On each mod load, I would do a select from that table for dates over x months old (or what have you), and if any came up, simply DELETE FROM pcvars where pcv_uid = 'blah'; Blah would be 100-1255280229-1 if I wanted to delete all the vars belonging to the character whose uid that is.
Is that how others have it set up? Or, do you have many different tables housing specific data types?
As you can see above, our stuff is largely organized by system. I think having one table per pc would be enormously clunky, and bad normalization, but it might be a reasonable solution if you're not using MySQL, and have no other way to purge old data. Typically, you want to store data in ways that is non-duplicative, and in discrete chunks. That's why all the random item property (rip_) tables are kept seperate from the older and mostly defunct pwdata (the generic table that NWNX uses for beginners).
By using a database object, if the player leaves, then the DB isn't stuck warehousing the variables. I can easily move a bic file to an alternate storage location. Yes, I'm stuck not being able to modify the data, nor modify it more than one PC at a time.
You're not stuck with that if you use MySQL, either, regarless of how you set up your tables.
Love the idea of reading the DB and putting it on the player at login. Maybe I'll get there if I can get my hands around putting all my DB calls into one table (provided that's as good a solution as multiple tables).
A single unitary table is likely a very bad idea, though it's a good idea for player variables called up on login (hence our pcvars table).
Also, how do others manage the archiving/back up process? What systems do you use?
We run weekly (or twice-weekly, forget) backups, but I don't know the ins and outs - benefits of having a network administrator as your co-admin.
'>
Funky
