tbaMUD Continued development of the CircleMUD core

Turns out I was too tired today. I made a suggestion that can be seen here:
http://www.tbamud.com/content/mwb-schema-file

Use the free "Mysql WorkBench" to view it.

Thanks for that explanation - I was unaware of the timeout, and will do it this way. It will only really access the database at MUD startup and player login, but for Trigun, I also planned a website-based quest where players click on something on the website to gain in-game rewards, which involves checking the database every 60 seconds or so for reward updates.

Yes, this was the plan, as I can use the field names in the same way that the tags are used in the current pfiles, and it would then be mostly copy/paste from the old load_char function and change a few bits. I did look at trying to find another method for prf/aff/plr flags, but just kept coming back to saving them as a varchar string, like in current pfiles, as the most sensible method I could think of.

This is one of my MySQL weaknesses, and I'd really appreciate any help with the schema.

Sorry, missed this on page 2...
Seems the link doesn't work, says the .zip file is 0 bytes long.

fixed the link

The basic idea behind the schema is to be able to add new fields easily (and no, ALTER TABLE does not qualify as easy).

Say you have ten attributes for a character - ac, hitpoints, title, strength, description, etc. You store them each in a row in the userdata table:

insert into userdata (userid, tag, type, stringvalue) values (GET_ID(ch), 'TITL', 1, GET_TITLE(ch));
insert into userdata (userid, tag, type, intvalue) values (GET_ID(ch), 'STRE', 2, ch->points.str);
...

and parse it out the same way:
select userid, tag, type, intvalue, stringvalue, boolvalue from userdata where userid = GET_ID(ch);
for (each_result_row) {
set value on ch based on tag and type.
}

it makes sense to postpone bitfields and such (for switching out the ascii bits) a bit.

Also, this gets so generic it makes sense to make accessor functions.

Just downloaded v5.0.3, but when I try to open the mwb file, it says:
"The document was created in an incompatible version of the application."
Which version do you use, and maybe I can downgrade it?

Now I've had some time to think about it, I do like your method, Welcor, although I think it would make it more difficult to update the user's data. When you save the player, you would have to search the database for the old values, and if they have changed, update them. If the old values aren't there, then insert new ones. As you said, functions can be written to do this quickly and easily. In my method, I'd just use one single (admittedly, huge) 'update' query to save all of the player's data.

I also think it makes it more difficult to find and edit a user's data manually using (for example) phpmyadmin or the SQL CLI. Having said that, with an efficient and well-written web-based pfile editor, and with the 'set' in-game command, player data can still be updated easily enough.

With the hugely "wide" table I had initially thought of, player data is all stored on one 'row' of the database, making it easier to find and edit.

You method would also take up more room on the server, as each player data field would have the user ID and tag name, whereas having all the data on 1 line means that the ID is only stored once, and the tag name is the field/column name, so isn't repeated for every entry.

Of course, when it comes to MySQL, you probably have far more experience than I do, and I'm more than happy to bow to greater knowledge. ;) If you think that your method is better, I'll gladly do it that way...

The main reason I suggest this solution is that I've seen it working.

If you want to use the single row per player approach, I suggest you instead split up into different tables with related info. Sort of like the player struct is split into separate sub-structs. Have 1 main table with things
you'll need often (username, password and id), and several "subtables" with related info. All should have a 1 to 1 relation to the id on the main table, and the main table should then have an "on delete Cascade" rule set up.

A user once asked on a sql site: how many columns are allowed in 1 table. The answer was "255 - and you're doing it wrong". Having 1 superwide table prevents the database from helping you structure the data.

W00t - double reply here.

Advantage of single-row per player:
- easy overview.
- simple insert (if given proper defaults)
- simple update (you always _know_ a correct row exists)
- simple select
- table is short but wide
Disadvantage:
mysql limits the maximum width http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html (though you probably wont be hitting such high numbers anytime soon)
- Altering which items to save potentially requires changes to all queries as well as the database schema (ALTER TABLE calls). This is the MAJOR disadvantage. Remeber that you want other system to integrate with your data. You don't want to be altering the schema very often - because then you'll need to rewrite the integrating tools as well.

Advantages of using a datatable:
- easy overview
- simple insert (if you write a simple wrapper that checks if a given tag is set before inserting)
- simple update (using the same wrapper)
- simple select (instead of getting 1 row with many columns, you'll get one row per value)
If you decide to store a new value, you don't need to alter the schema.
You can potentially add lots and lots of different variables.

Disadvantages of using a datatable:
You need to write an wrapper function for updates/inserts
The datatable can potentially get very long (indices are important).
A select for 1 char with all data is 20 letters longer:

select * from user where username='xxx';
vs.
select * from userdata where id=(select distinct id from user where username='xxx');

Pardon my late entry, but what is the benefit of the overhead and complexity of a full relational database like MySQL, as opposed to a classic hash database like Berkeley DB? Both are highly portable, and Berkeley DB can be used just as easily from the web.

While I can understand the benefit of a database over a text file and am surprised the movement hasn't happened sooner, I can't really understand the benefit of MySQL over more classic solutions.

Personally, I have a lot of experience with SQL based databases, and most online resources for web frontend programmers assume a SQL capable backend.

Thus it is easier to find help (not just for initial development, but also for future web-integration) for a SQL based solution than for one that is berkeley based.

However, the choice of mysql over postgreSql (or any of the other SQL servers outthere), comes from the fact that we already run a mysql server for this (and other) site(s).