[ArChron]

New Years Resolutions?

Get off my butt and finally put together the Rails project that has been stewing around in my head since I found EQEmu, and wanted to figure out the database.

I use MySQL 5, and have a few trivial items constructed to help make sense of the DB... like adding a classes table and a races table so that I can see real names of values in a query...

USE `ax_peq`;
CREATE TABLE `ax_peq`.`classes` (
  `class_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `class_name` CHAR(30) DEFAULT 'Un-named' COMMENT 'Class Name',
  PRIMARY KEY(`class_id`)
)
ENGINE = MYISAM;

USE `ax_peq`;
INSERT INTO `classes` (`class_id`,`class_name`) VALUES ( 1, 'Warrior');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES ( 2, 'Cleric');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES ( 3, 'Paladin');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES ( 4, 'Ranger');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES ( 5, 'Shadow Knight');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES ( 6, 'Druid');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES ( 7, 'Monk');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES ( 8, 'Bard');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES ( 9, 'Rogue');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (10, 'Shaman');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (11, 'Necromancer');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (12, 'Wizard');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (13, 'Magician');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (14, 'Enchanter');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (15, 'Beastlord');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (16, 'Berserker');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (20, 'GM Warrior');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (21, 'GM Cleric');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (22, 'GM Paladin');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (23, 'GM Ranger');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (24, 'GM Shadow Knight');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (25, 'GM Druid');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (26, 'GM Monk');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (27, 'GM Bard');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (28, 'GM Rogue');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (29, 'GM Shaman');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (30, 'GM Necromancer');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (31, 'GM Wizard');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (32, 'GM Magician');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (33, 'GM Enchanter');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (34, 'GM Beastlord');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (35, 'GM Berserker');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (40, 'Banker');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (41, 'Shopkeeper');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (60, 'Adventure Recruiter');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (61, 'Adventure Merchant');
INSERT INTO `classes` (`class_id`,`class_name`) VALUES (63, 'Tribute Master');

and you can create some views out of SELECTs that are useful as well...

SELECT s.id, s.race, r.race_desc, s.class, c.class_name, s.zoneid, 
  z.long_name, s.itemid, i.Name, i.filename FROM starting_items s 
  INNER JOIN classes c ON s.class=c.class_id 
  INNER JOIN races r ON s.race=r.race_id 
  INNER JOIN items i ON s.itemid=i.id 
  INNER JOIN zone z ON s.zoneid=z.zoneidnumber 
  ORDER BY s.race, s.class;

and

SELECT l.id, l.name, e.item_id, i.Name AS itemName, e.chance FROM lootdrop l
  INNER JOIN lootdrop_entries e ON l.id=e.lootdrop_id
  INNER JOIN items i ON e.item_id=i.id
  ORDER BY l.id;

I can build stuff as I have time, but there's a fairly steep curve for the database, so it's largely hunt and peck thus far.