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:
Code:
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:
Code:
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!
