CircleMUD -> MySQL

  • Kinther
  • Kinther's Avatar Topic Author
  • Offline
  • Fresh Boarder
  • Fresh Boarder
  • 4 8 15 16 23 42
More
1 year 4 days ago #8257 by Kinther
CircleMUD -> MySQL was created by Kinther
Currently CircleMUD writes files in plain text to various folders in lib/

Has there ever been a successful migration of those data structures into a MySQL database? I see there is a MySQL snippet "circle_mysql" available under:

www.circlemud.org/pub/CircleMUD/submissions/snippets/utils/

However it seems like it is simply a basic function which can be run in game to pull a single query from a database. I'm concerned more with a full migration path to get away from storing objects, rooms, mobs, players, and so on in plain text.

If not, is there any interest in a collaboration attempt at making it happen?

4 8 15 16 23 42

Please Log in or Create an account to join the conversation.

More
11 months 4 weeks ago #8266 by lacrc
Replied by lacrc on topic CircleMUD -> MySQL
I have a working version of TBA running with MySQL Database for almost everything except some text files like Help and Socials (tho I am in the process of converting these files to MySQL too).

I haven't gotten around to make a patch for it not sure it was something people would use/require, you probably made the same consideration lol.

For now it is:
- Migrating the database (tho only for creating and refreshing, not updating since I haven't even release it yet I haven't made any upgrade migrations, thought I should be ready for it heh).
- Populating the tables with existing text files (TBA new file format with array tags and whatnot)
- There's tables for: World, Mobs, Objects, Quests, Shops, Triggers, Player files (player files and objects in inventory, rent is free in the current code) and Combat Messages. (I might be forgetting something here but that's the bulk of it)

I tried to keep most of the TBA original code untouched so patching it would be easy, like simply changing functions being called or setting defines before compiling to switch between text and MySQL but for now the version I have is all mixed up with custom content so I'm not sure it would be useful as patch. I intende to release one in the future but let me know (via pm or reply this topic) if you have any questions and I'll share my thoughts or how I handled it.
The following user(s) said Thank You: rudeboyrave

Please Log in or Create an account to join the conversation.

More
4 months 2 weeks ago #8422 by Blizzard
Replied by Blizzard on topic CircleMUD -> MySQL
Some tips for those looking to attempt this from my experience in my MUD:
- Write a replacement for OLC's save_to_disk functions, eg. redit_save_to_db then use those to populate your database tables with temporary functions in boot_db by saving the entire world to SQL. You'll not only add support for database-backed OLC but save yourself from writing a separate data migration tool.
- Don't be afraid to split items into multiple tables for arrays/lists. It is easy to have a load/save function query multiple tables.
- Consider storing old versions of objects/rooms/mobs in the database with a column for the version, date modified, and who modified it. You can use this to add save/revert support to your editor and track who changes what.

My main goal for moving things to a database is to make it easier to implement a web-based OLC editor in my web language of choice, PHP, which I haven't completed yet. If anyone is curious to see a mock up (non-functional) of the editor I'm planning, check out worldofpa.in/editor/

I may release the source code to this once I get something working, if anyone's interested.
The following user(s) said Thank You: lacrc

Please Log in or Create an account to join the conversation.

More
4 months 2 weeks ago #8423 by lacrc
Replied by lacrc on topic CircleMUD -> MySQL
Well, you went and described what I am currently attempting to do with my MySQL version of TBA hahaha. I also started a web based OLC using the Laravel PHP Framework but I didn't think of putting up versions of stuff so they could be reverted back, that's a real nice feature!

So to answer your question I am **very** interested in seeing how you achieved that. I'm in the process of cleaning up the MySQL stuff I wrote for the mud I dev for, so it can be easily used with a fresh TBA installation, I might speed that project up so I can share it here.

Question: is your boot up time using SQL significantly slower than when using text?

I imagined that it would be slower but I discovered that I couldn't write optimized sql loading functions that were similar to the existing db.c functions that load the world, so I'm curious to see how you did that.

What I did was query everything from a table (say, objects) and their related tables (affects and extra descriptions) and iterate through the result setting up the list just like the stock db.c function does. Though the existing functions in the MySQL C API didn't really help with that (or maybe I'm not using them right?) so it ended being kinda messy. Here's my object loading function:
void mysql_objects_load(void) {
    MYSQL_RES *obj_res, *edesc_res, *oaff_res;
    MYSQL_ROW obj_row, edesc_row, oaff_row;
    char query[MAX_SQLQUERY_LENGTH], *temp_char,
            flags0[50], flags1[50], flags2[50], flags3[50];
    int i = 0, j;
    long odesc_count = 0, oaff_count = 0;
    long vnum;
    struct extra_descr_data *new_descr;

    snprintf(query, sizeof (query), "SELECT * FROM object ORDER BY vnum ASC");
    mysql_execute_query(query, &obj_res); /* T_query OK */

    /* extra descriptions */
    snprintf(query, sizeof (query), "SELECT * FROM extra_description WHERE type='object' ORDER BY vnum ASC");
    mysql_execute_query(query, &edesc_res); /* T_query OK */
    odesc_count = (long) mysql_num_rows(edesc_res);

    int extra_descriptions[odesc_count];
    extra_descriptions[0] = NOWHERE;

    i = 0;
    while (edesc_res && (edesc_row = mysql_fetch_row(edesc_res)))
        extra_descriptions[i++] = atoi(edesc_row[T_EXTRA_VNUM]);

    /* object affects */
    snprintf(query, sizeof (query), "SELECT * FROM object_affect ORDER BY obj_vnum DESC");
    mysql_execute_query(query, &oaff_res);

    oaff_count = (long) mysql_num_rows(oaff_res);
    int object_affects[oaff_count];

    i = 0;
    while (oaff_res && (oaff_row = mysql_fetch_row(oaff_res)))
        object_affects[i++] = atoi(oaff_row[T_OBJ_AFFECT_OBJ_VNUM]);

    /* loading objects */
    i = 0;
    while (obj_res && (obj_row = mysql_fetch_row(obj_res))) {
        vnum = atoi(obj_row[T_OBJECT_VNUM]);

        obj_index[i].vnum = vnum;
        obj_index[i].number = 0;
        obj_index[i].func = NULL;

        clear_object(obj_proto + i);
        obj_proto[i].item_number = i;

        /* strings */
        obj_proto[i].name = strdup(obj_row[T_OBJECT_KEYWORDS]);
        temp_char = obj_proto[i].short_description = strdup(obj_row[T_OBJECT_SDESC]);
        if (temp_char && *temp_char)
            if (!str_cmp(fname(temp_char), "a") || !str_cmp(fname(temp_char), "an") ||
                    !str_cmp(fname(temp_char), "the"))
                *temp_char = LOWER(*temp_char);

        temp_char = obj_proto[i].description = strdup(obj_row[T_OBJECT_LDESC]);
        if (temp_char && *temp_char)
            CAP(temp_char);

        obj_proto[i].action_description = strdup(obj_row[T_OBJECT_ADESC]);

        /* Flags */
        sscanf(obj_row[T_OBJECT_EXTRA_FLAGS], "%s %s %s %s", flags0, flags1, flags2, flags3);
        GET_OBJ_EXTRA(obj_proto + i)[0] = asciiflag_conv(flags0);
        GET_OBJ_EXTRA(obj_proto + i)[1] = asciiflag_conv(flags1);
        GET_OBJ_EXTRA(obj_proto + i)[2] = asciiflag_conv(flags2);
        GET_OBJ_EXTRA(obj_proto + i)[3] = asciiflag_conv(flags3);
        sscanf(obj_row[T_OBJECT_WEAR_FLAGS], "%s %s %s %s", flags0, flags1, flags2, flags3);
        GET_OBJ_WEAR(obj_proto + i)[0] = asciiflag_conv(flags0);
        GET_OBJ_WEAR(obj_proto + i)[1] = asciiflag_conv(flags1);
        GET_OBJ_WEAR(obj_proto + i)[2] = asciiflag_conv(flags2);
        GET_OBJ_WEAR(obj_proto + i)[3] = asciiflag_conv(flags3);
        sscanf(obj_row[T_OBJECT_PERMA_AFFECTS], "%s %s %s %s", flags0, flags1, flags2, flags3);
        GET_OBJ_AFFECT(obj_proto + i)[0] = asciiflag_conv(flags0);
        GET_OBJ_AFFECT(obj_proto + i)[1] = asciiflag_conv(flags1);
        GET_OBJ_AFFECT(obj_proto + i)[2] = asciiflag_conv(flags2);
        GET_OBJ_AFFECT(obj_proto + i)[3] = asciiflag_conv(flags3);
        /* type */
        GET_OBJ_TYPE(obj_proto + i) = atoi(obj_row[T_OBJECT_TYPE]);
        /* values */
        GET_OBJ_VAL(obj_proto + i, 0) = atoi(obj_row[T_OBJECT_VALUE0]);
        GET_OBJ_VAL(obj_proto + i, 1) = atoi(obj_row[T_OBJECT_VALUE1]);
        GET_OBJ_VAL(obj_proto + i, 2) = atoi(obj_row[T_OBJECT_VALUE2]);
        GET_OBJ_VAL(obj_proto + i, 3) = atoi(obj_row[T_OBJECT_VALUE3]);
        /* other stuff */
        GET_OBJ_WEIGHT(obj_proto + i) = atoi(obj_row[T_OBJECT_WEIGHT]);
        GET_OBJ_COST(obj_proto + i) = atoi(obj_row[T_OBJECT_PRICE]);
        GET_OBJ_LEVEL(obj_proto + i) = atoi(obj_row[T_OBJECT_MIN_LEVEL]);
        GET_OBJ_TIMER(obj_proto + i) = atoi(obj_row[T_OBJECT_TIMER]);
        GET_OBJ_LASTEDIT(obj_proto + i) = mysql_strtotime(obj_row[T_OBJECT_LAST_EDIT]);
        GET_OBJ_LASTEDIT_IDNUM(obj_proto + i) = atol(obj_row[T_OBJECT_LAST_EDIT_IDNUM]);

        /* check to make sure that weight of containers exceeds curr. quantity */
        if (GET_OBJ_TYPE(obj_proto + i) == ITEM_DRINKCON || GET_OBJ_TYPE(obj_proto + i) == ITEM_FOUNTAIN) {
            if (GET_OBJ_WEIGHT(obj_proto + i) < GET_OBJ_VAL(obj_proto + i, 1) &&
                    CAN_WEAR(obj_proto + i, ITEM_WEAR_TAKE))
                GET_OBJ_WEIGHT(obj_proto + i) = GET_OBJ_VAL(obj_proto + i, 1) + 5;
        }

        /* extra descriptions */
        for (; extra_descriptions[odesc_count] <= vnum; odesc_count++) {
            if (extra_descriptions[odesc_count] != vnum) continue;
            mysql_data_seek(edesc_res, odesc_count);
            if (mysql_errno(inferno_db)) {
                log("load_objects_db() couldn't access edesc_res line %ld: %s", odesc_count, mysql_error(inferno_db));
                continue;
            }
            if (!(edesc_row = mysql_fetch_row(edesc_res))) {
                if (mysql_errno(inferno_db))
                    log("load_objects_db() couldn't access edesc_res line %ld: %s", odesc_count, mysql_error(inferno_db));
                continue;
            }

            CREATE(new_descr, struct extra_descr_data, 1);
            new_descr->keyword = strdup(edesc_row[T_EXTRA_KEYWORDS]);
            new_descr->description = strdup(edesc_row[T_EXTRA_DESC]);
            new_descr->next = obj_proto[i].ex_description;
            obj_proto[i].ex_description = new_descr;
        }

        /* obj affects */
        for (j = 0; j < MAX_OBJ_AFFECT; j++) {
            obj_proto[i].affected[j].location = APPLY_NONE;
            obj_proto[i].affected[j].modifier = 0;
        }

        j = 0;
        for (; object_affects[oaff_count] <= vnum; oaff_count++) {
            if (object_affects[oaff_count] != vnum) continue;
            mysql_data_seek(oaff_res, oaff_count);
            if (mysql_errno(inferno_db)) {
                log("load_rooms_db() couldn't access oaff_res line %ld: %s", oaff_count, mysql_error(inferno_db));
                continue;
            }
            if (!(oaff_row = mysql_fetch_row(oaff_res))) {
                if (mysql_errno(inferno_db))
                    log("load_rooms_db() couldn't access oaff_res line %ld: %s", oaff_count, mysql_error(inferno_db));
                continue;
            }
            obj_proto[i].affected[j].location = atoi(oaff_row[T_OBJ_AFFECT_LOCATION]);
            obj_proto[i].affected[j].modifier = atoi(oaff_row[T_OBJ_AFFECT_MODIFIER]);
            j++;
        }

        /* object triggers */
        mysql_triggers_attached_load(&obj_proto[i], vnum, OBJ_TRIGGER);

        top_of_objt = i;
        check_object(obj_proto + i);
        i++;
    }

    if (obj_res)
        mysql_free_result(obj_res);
    if (edesc_res)
        mysql_free_result(edesc_res);
    if (oaff_res)
        mysql_free_result(oaff_res);
}

I've used constants that refer to each column on the table but I've been rewriting some of the loops to use the fieldnames in a switch more like the player object loading function does in objsave.c.

Something like:
        for (i = 0; i < mysql_num_fields(res_obj) && (field = mysql_fetch_field(res_obj)) != NULL; i++) {
            snprintf(fname, sizeof (fname), "%s", field->name);
            snprintf(value, sizeof (value), "%s", obj_row[i]);
            if (!strcmp(fname, "obj_vnum")) continue; /* don't need this one */
            switch (LOWER(*fname)) {
                case 'a':
                    if (!strcmp(fname, "a_desc")) temp->action_description = strdup(value);
                    break;
                case 'c':
                    if (!strcmp(fname, "cost")) GET_OBJ_COST(temp) = atoi(value);
                    break;

                ..... //more cases here for every column

                default:
                    log("Unknown tag in rentfile: %s", fname);
            }
        }

Anyway, glad too see someone else is doing this!
Thanks and have a good one! :)

Please Log in or Create an account to join the conversation.

More
4 months 2 weeks ago - 4 months 1 week ago #8424 by Blizzard
Replied by Blizzard on topic CircleMUD -> MySQL
Here is my obj_proto table:
CREATE TABLE `obj_proto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` int(11) NOT NULL,
  `zone` int(11) NOT NULL,
  `name` varchar(256) COLLATE ascii_bin NOT NULL,
  `short_descr` varchar(1024) COLLATE ascii_bin NOT NULL,
  `action_descr` text COLLATE ascii_bin NOT NULL,
  `description` text COLLATE ascii_bin NOT NULL,
  `obj_type` int(11) NOT NULL,
  `obj_extra` int(11) NOT NULL,
  `wear` int(11) NOT NULL,
  `level` int(11) NOT NULL,
  `val0` int(11) NOT NULL,
  `val1` int(11) NOT NULL,
  `val2` int(11) NOT NULL,
  `val3` int(11) NOT NULL,
  `obj_special` int(11) NOT NULL,
  `weight` int(11) NOT NULL,
  `cost` int(11) NOT NULL,
  `rent` int(11) NOT NULL,
  `innate` tinyint(1) NOT NULL,
  `race` int(11) NOT NULL,
  `obj_spell` int(11) NOT NULL,
  `spell_target` int(11) NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified_by` int(11) NOT NULL,
  `status` set('current','old','deleted','draft','pending') COLLATE ascii_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2335 DEFAULT CHARSET=ascii COLLATE=ascii_bin;

The key piece to versioning being the last 3 fields. modified updates automatically with ON UPDATE so you only have to set the last two during INSERTs. Whenever I do updates to a table, I run a query like this:
	query << "UPDATE obj_proto SET status='old' WHERE status='current'" \
        << " AND number=" << nr;
Then I insert new rows with a status of current instead of updating existing ones. Note that I'm using MySQL++ in my game, not the MySQL C APIs, a design decision I made back in college in 2001 or so, haha. Right now, "current" and "old" status states are all I have implemented. Haven't implemented revert or anything like that yet, although it is possible to do so manually in SQL followed by a reboot.

Deleted status would be if we wanted to undelete an object, draft and pending are meant for web-OLC, where drafts would be anything unsaved, which would then be published by the editor to a pending state, where the MUD, possibly as part of the game loop, would look for anything in a pending state and update the world accordingly, at least that's how I'm planning it.

I haven't noticed a major lag in game loading times with SQL. Currently I've got zones, rooms, mobs, objects, players and help loading and saving to SQL. I'm running this off of SSD though so database performance should be good, and the database is typically small enough that MySQL should be able to fit the whole thing in RAM. Also important are indexes in SQL. I have any relational columns (typically id) used to reference keys in other tables setup as indexes, as well as anything commonly queried for in WHEREs.
Last edit: 4 months 1 week ago by Blizzard.

Please Log in or Create an account to join the conversation.

Time to create page: 0.216 seconds