Itemdb/db2 SQL & CONF

[GmOcean 92]

Okay, so adding items via itemdb.conf is really easy now, since you essentially only need to input 3 fields to get an item to exist in game. However, on the SQL side for those of us who use it, you still need to add all the information or it'll be buggy and could cause issues. Since default is either NULL or 0.

 

#1. I suggest we update this to take on the same parameters as itemdb.conf when not filling them in. It only makes sense in my opinion. (Yes I know I can easily do this myself by editing the main.sql file. But this is a suggestion so that we won't have to in the future).

 

#2. of the suggestion is about the inherit feature. While looking in itemdb.c you'll notice that it does some PRE-checks before adding the data to the *db. It reads the sql entries -> checks to see if it's valid -> changes information so that it is valid in the instance where it is incorrect.

/** * Processes one itemdb entry from the sql backend, loading and inserting it * into the item database. * * @param *handle MySQL connection handle. It is expected to have data *                available (i.e. already queried) and it won't be freed (it *                is care of the caller to do so) * @param n       Ordinal number of the entry, to be displayed in case of *                validation errors. * @param *source Source of the entry (table name), to be displayed in case of *                validation errors. * @return Nameid of the validated entry, or 0 in case of failure. */int itemdb_readdb_sql_sub(Sql *handle, int n, const char *source) {	struct item_data id = { 0 };	char *data = NULL;	/*	 * `id`              smallint(5)   unsigned NOT NULL DEFAULT '0'	 * `name_english`    varchar(50)            NOT NULL DEFAULT ''	 * `name_japanese`   varchar(50)            NOT NULL DEFAULT ''	 * `type`            tinyint(2)    unsigned NOT NULL DEFAULT '0'	 * `price_buy`       mediumint(10)                   DEFAULT NULL	 * `price_sell`      mediumint(10)                   DEFAULT NULL	 * `weight`          smallint(5)   unsigned          DEFAULT NULL	 * `atk`             smallint(5)   unsigned          DEFAULT NULL	 * `matk`            smallint(5)   unsigned          DEFAULT NULL	 * `defence`         smallint(5)   unsigned          DEFAULT NULL	 * `range`           tinyint(2)    unsigned          DEFAULT NULL	 * `slots`           tinyint(2)    unsigned          DEFAULT NULL	 * `equip_jobs`      int(12)       unsigned          DEFAULT NULL	 * `equip_upper`     tinyint(8)    unsigned          DEFAULT NULL	 * `equip_genders`   tinyint(2)    unsigned          DEFAULT NULL	 * `equip_locations` smallint(4)   unsigned          DEFAULT NULL	 * `weapon_level`    tinyint(2)    unsigned          DEFAULT NULL	 * `equip_level_min` smallint(5)   unsigned          DEFAULT NULL	 * `equip_level_max` smallint(5)   unsigned          DEFAULT NULL	 * `refineable`      tinyint(1)    unsigned          DEFAULT NULL	 * `view`            smallint(3)   unsigned          DEFAULT NULL	 * `bindonequip`     tinyint(1)    unsigned          DEFAULT NULL	 * `buyingstore`     tinyint(1)             NOT NULL DEFAULT NULL	 * `delay`           mediumint(9)           NOT NULL DEFAULT NULL	 * `trade_flag`      smallint(4)            NOT NULL DEFAULT NULL	 * `trade_group`     smallint(4)            NOT NULL DEFAULT NULL	 * `nouse_flag`      smallint(4)            NOT NULL DEFAULT NULL	 * `nouse_group`     smallint(4)            NOT NULL DEFAULT NULL	 * `stack_amount`    mediumint(6)           NOT NULL DEFAULT NULL	 * `stack_flag`      smallint(2)            NOT NULL DEFAULT NULL	 * `sprite`          mediumint(6)           NOT NULL DEFAULT NULL	 * `script`          text	 * `equip_script`    text	 * `unequip_script`  text	 */	SQL->GetData(handle,  0, &data, NULL); id.nameid = (uint16)atoi(data);	SQL->GetData(handle,  1, &data, NULL); safestrncpy(id.name, data, sizeof(id.name));	SQL->GetData(handle,  2, &data, NULL); safestrncpy(id.jname, data, sizeof(id.jname));	SQL->GetData(handle,  3, &data, NULL); id.type = atoi(data);	SQL->GetData(handle,  4, &data, NULL); id.value_buy = data ? atoi(data) : -1; // Using invalid price -1 when missing, it'll be validated later	SQL->GetData(handle,  5, &data, NULL); id.value_sell = data ? atoi(data) : -1;	SQL->GetData(handle,  6, &data, NULL); id.weight = data ? atoi(data) : 0;	SQL->GetData(handle,  7, &data, NULL); id.atk = data ? atoi(data) : 0;	SQL->GetData(handle,  8, &data, NULL); id.matk = data ? atoi(data) : 0;	SQL->GetData(handle,  9, &data, NULL); id.def = data ? atoi(data) : 0;	SQL->GetData(handle, 10, &data, NULL); id.range = data ? atoi(data) : 0;	SQL->GetData(handle, 11, &data, NULL); id.slot = data ? atoi(data) : 0;	SQL->GetData(handle, 12, &data, NULL); itemdb->jobid2mapid(id.class_base, data ? (unsigned int)strtoul(data,NULL,0) : UINT_MAX);	SQL->GetData(handle, 13, &data, NULL); id.class_upper = data ? (unsigned int)atoi(data) : ITEMUPPER_ALL;	SQL->GetData(handle, 14, &data, NULL); id.sex = data ? atoi(data) : 2;	SQL->GetData(handle, 15, &data, NULL); id.equip = data ? atoi(data) : 0;	SQL->GetData(handle, 16, &data, NULL); id.wlv = data ? atoi(data) : 0;	SQL->GetData(handle, 17, &data, NULL); id.elv = data ? atoi(data) : 0;	SQL->GetData(handle, 18, &data, NULL); id.elvmax = data ? atoi(data) : 0;	SQL->GetData(handle, 19, &data, NULL); id.flag.no_refine = data && atoi(data) ? 0 : 1;	SQL->GetData(handle, 20, &data, NULL); id.look = data ? atoi(data) : 0;	SQL->GetData(handle, 21, &data, NULL); id.flag.bindonequip = data && atoi(data) ? 1 : 0;	SQL->GetData(handle, 22, &data, NULL); id.flag.buyingstore = data && atoi(data) ? 1 : 0;	SQL->GetData(handle, 23, &data, NULL); id.delay = data ? atoi(data) : 0;	SQL->GetData(handle, 24, &data, NULL); id.flag.trade_restriction = data ? atoi(data) : ITR_NONE;	SQL->GetData(handle, 25, &data, NULL); id.gm_lv_trade_override = data ? atoi(data) : 0;	SQL->GetData(handle, 26, &data, NULL); id.item_usage.flag = data ? atoi(data) : INR_NONE;	SQL->GetData(handle, 27, &data, NULL); id.item_usage.override = data ? atoi(data) : 0;	SQL->GetData(handle, 28, &data, NULL); id.stack.amount = data ? atoi(data) : 0;	SQL->GetData(handle, 29, &data, NULL);	if (data) {		int stack_flag = atoi(data);		id.stack.inventory = (stack_flag&1)!=0;		id.stack.cart = (stack_flag&2)!=0;		id.stack.storage = (stack_flag&4)!=0;		id.stack.guildstorage = (stack_flag&8)!=0;	}	SQL->GetData(handle, 30, &data, NULL);	if (data) {		id.view_id = atoi(data);		if (id.view_id)			id.flag.available = 1;	}	SQL->GetData(handle, 31, &data, NULL); id.script = data && *data ? script->parse(data, source, -id.nameid, SCRIPT_IGNORE_EXTERNAL_BRACKETS, NULL) : NULL;	SQL->GetData(handle, 32, &data, NULL); id.equip_script = data && *data ? script->parse(data, source, -id.nameid, SCRIPT_IGNORE_EXTERNAL_BRACKETS, NULL) : NULL;	SQL->GetData(handle, 33, &data, NULL); id.unequip_script = data && *data ? script->parse(data, source, -id.nameid, SCRIPT_IGNORE_EXTERNAL_BRACKETS, NULL) : NULL;	return itemdb->validate_entry(&id, n, source);}

Knowing this we could add an INHERIT column in our sql itemdb entries to allow for the option of making an item inherit that of another item. Which leads to my third suggestion.

 

 

#3. While inherit is useful in some cases, it's fairly if not vastly limited, since it's a boolean option. Meaning either YES inherit or NO don't inherit. This should be changed to an item_id entry. So that we can specify WHAT item it inherits its information from. That way were not limited to re-making an existing item just to change or add a feature.

 

Example of current inherit:

{    Id: 501    AegisName: "Red_Potion"    Name: "Red Potion"    Buy: 1200    Weight: 150    Script: <" itemheal rand(325,405),0; ">    Inherit: true},

This will make a red potion inherit all of it's information from itemdb.conf but make it act like a White Potion in terms of buy/sell price, weight and heal power.

 

Example of suggested inherit:

{	Id: 501	AegisName: "Red_Potion"	Name: "Red Potion"	Inherit: 504},

This would effectively do the same thing as above should we decide to add it. BUT more importantly, i wouldn't be limited to using an existing item ID.

{	Id: 32000	AegisName: "New_Potion"	Name: "New Potion"	Inherit: 504},

Thusly allowing us to add new items in and inherit information without the need to overwrite an item already in existence. ( I'm repeating myself xD ).

 

Naturally this could be applied to the SQL side as well and rightfully should if there aren't any underlying compatibility issues.

 

For those who don't know what I'm talking about in relation to the src here is the section in question:

	if( (t = libconfig->setting_get_member(it, "Inherit")) && (inherit = libconfig->setting_get_bool(t)) ) {		if( !itemdb->exists(id.nameid) ) {			ShowWarning("itemdb_readdb_libconfig_sub: Trying to inherit nonexistent item %d, default values will be used instead.n", id.nameid);			inherit = false;		} else {			// Use old entry as default			struct item_data *old_entry = itemdb->load(id.nameid);			memcpy(&id, old_entry, sizeof(struct item_data));		}	}

It would be a simple change to: *Note - would also need to change   bool inherit = false   to int inherit = false

   	 if( (inherit = libconfig->setting_lookup_int(it, "Inherit", &i32 )) ) {            if( !itemdb->exists(id.nameid) && inherit == 1 || !itemdb->exists(inherit) && inherit != 1 ) {            ShowWarning("itemdb_readdb_libconfig_sub: Trying to inherit nonexistent item %d, default values will be used instead.n", inherit);            inherit = false;        } else {            // Use old entry as default            if( inherit = 1 ) {                struct item_data *old_entry = itemdb->load(id.nameid);                memcpy(&id, old_entry, sizeof(struct item_data));            } else {                struct item_data *old_entry = itemdb->load(inherit);                memcpy(&id, old_entry, sizeof(struct item_data));            }        }    }

 

From my limited knowledge on the src code ( and coding in general ), this would let it lookup the item specified rather than a boolean option and then use that information as the default, else go to the default paramters given in itemdb.conf

Edited October 4, 2014 by GmOcean