New Mob DB SQL Tables

[vstumpf]

Provide Details

I want to refactor our mob_db (and eventually item_db) to make it easier to query and get real values.

this is related to rathena/rathena#6369

Instead of using yml2sql, the map-server-generator would generate 2 sql files with schemas like so:
NB:

CREATE TABLE `mob_db` (
  `id` int(11) unsigned NOT NULL,
  `name_aegis` varchar(24) NOT NULL,
  `name_english` text NOT NULL,
  `name_japanese` text DEFAULT NULL,
  `level` smallint(6) unsigned DEFAULT NULL,
  `hp` int(11) unsigned DEFAULT NULL,
  `sp` mediumint(9) unsigned DEFAULT NULL,
  `base_exp` int(11) unsigned DEFAULT NULL,
  `job_exp` int(11) unsigned DEFAULT NULL,
  `mvp_exp` int(11) unsigned DEFAULT NULL,
  `attack` smallint(6) unsigned DEFAULT NULL,
  `attack2` smallint(6) unsigned DEFAULT NULL,
  `defense` smallint(6) unsigned DEFAULT NULL,
  `magic_defense` smallint(6) unsigned DEFAULT NULL,
  `str` smallint(6) unsigned DEFAULT NULL,
  `agi` smallint(6) unsigned DEFAULT NULL,
  `vit` smallint(6) unsigned DEFAULT NULL,
  `int` smallint(6) unsigned DEFAULT NULL,
  `dex` smallint(6) unsigned DEFAULT NULL,
  `luk` smallint(6) unsigned DEFAULT NULL,
  `attack_range` tinyint(4) unsigned DEFAULT NULL,
  `skill_range` tinyint(4) unsigned DEFAULT NULL,
  `chase_range` tinyint(4) unsigned DEFAULT NULL,
  `size` varchar(24) DEFAULT NULL,
  `race` varchar(24) DEFAULT NULL,
  `racegroup_goblin` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_kobold` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_orc` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_golem` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_guardian` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_ninja` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_gvg` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_battlefield` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_treasure` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_biolab` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_manuk` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_splendide` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_scaraba` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_ogh_atk_def` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_ogh_hidden` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_bio5_swordman_thief` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_bio5_acolyte_merchant` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_bio5_mage_archer` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_bio5_mvp` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_clocktower` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_thanatos` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_faceworm` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_hearthunter` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_rockridge` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_werner_lab` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_temple_demon` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_illusion_vampire` tinyint(1) unsigned DEFAULT NULL,
  `racegroup_malangdo` tinyint(1) unsigned DEFAULT NULL,
  `element` varchar(24) DEFAULT NULL,
  `element_level` tinyint(4) unsigned DEFAULT NULL,
  `walk_speed` smallint(6) unsigned DEFAULT NULL,
  `attack_delay` smallint(6) unsigned DEFAULT NULL,
  `attack_motion` smallint(6) unsigned DEFAULT NULL,
  `damage_motion` smallint(6) unsigned DEFAULT NULL,
  `damage_taken` smallint(6) unsigned DEFAULT NULL,
);

CREATE TABLE `mob_drop_db` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mob_id` mediumint(9) unsigned NOT NULL default '0',
  `item_id` int(11) NOT NULL,
  `index` tinyint(3) NOT NULL,
  `rate` smallint(9) NOT NULL,
  `nosteal` tinyint(1) NOT NULL default '0',
  `randomoption` smallint(9) NOT NULL default '0',
  `mvp` tinyint(1) NOT NULL default '0',
  PRIMARY KEY (id)
);

These will already have rates applied to them, as the map-server-generator generates these after it reads the configs and all imports.
We won't have to keep two similar tables updated anymore (mob_db, mob_db2) to drops.
Searching for mobs that have a specific item drop will be easier, as instead of checking drop1_item, drop2_item, drop3_item, etc, we can just search through mob_drop_db for item_id, then find all mobs.
Extending the fields for mob_drops would also be much easier than redoing them 13 times (10 normal drops, 3 mvp drops). We can also add more than the normal amount of drops for a monster.

[Balferian]

As suggestion:
We can drop mob/item sql files from server side and add yaml parser for FluxCP with Symfony sfYaml component, so rA can remove xxx2sql tools.
Make admin page where you can upload needed yaml files to form and flux will generate db byself.

[aleos89]

We had planned on dropping SQL with the mob and item conversions to YAML but there was a huge backlash of those who adamantly use SQL. I originally had pushed to SQL-fy every database years ago but not many seemed interested. Somehow we are stuck in this gap of wanting both, not neither.

[vstumpf]

flux will generate db

My issue with this is an admin would still have to duplicate the server configuration in flux

FluxCP/config/servers.php

Lines 66 to 105 in dc8ebf2

	'DropRates' => array(
	// If drop rate was below this amount and bonus is applied to it, the bonus can't make it exceed this amount.
	'DropRateCap' => 9000,
	// The rate the common items (in the ETC tab, besides card) are dropped
	'Common' => 100,
	'CommonBoss' => 100,
	'CommonMVP' => 100,
	'CommonMin' => 1,
	'CommonMax' => 10000,
	// The rate healing items (that restore HP or SP) are dropped
	'Heal' => 100,
	'HealBoss' => 100,
	'HealMVP' => 100,
	'HealMin' => 1,
	'HealMax' => 10000,
	// The rate usable items (in the item tab other then healing items) are dropped
	'Useable' => 100,
	'UseableBoss' => 100,
	'UseableMVP' => 100,
	'UseableMin' => 1,
	'UseableMax' => 10000,
	// The rate at which equipment is dropped
	'Equip' => 100,
	'EquipBoss' => 100,
	'EquipMVP' => 100,
	'EquipMin' => 1,
	'EquipMax' => 10000,
	// The rate at which cards are dropped
	'Card' => 100,
	'CardBoss' => 100,
	'CardMVP' => 100,
	'CardMin' => 1,
	'CardMax' => 10000,
	// The rate adjustment for the MVP items that the MVP gets directly in their inventory
	'MvpItem' => 100,
	'MvpItemMin' => 1,
	'MvpItemMax' => 10000,
	// 0 - official order (Show message "Note: Only one MVP drop will be rewarded.") , 2 - all items
	'MvpItemMode' => 0,
	),

Whereas if the mapserver generates it, it creates one table with all the info after any configuration and script changes.

Somehow we are stuck in this gap of wanting both, not neither

Unfortunately YAML isn't great for querying. We'd either have to load the yaml files on every request that requires it (yikes), or keep it in a memory cache. Or, just query a sql db like we do now.