mysql.sql

CREATE TABLE classes (
                       id INTEGER PRIMARY KEY NOT NULL,
                       mask integer NOT NULL,
                       powertype VARCHAR(255) NOT NULL,
                       name VARCHAR(255) NOT NULL
);

CREATE TABLE class_colors (
                            id INTEGER PRIMARY KEY,
                            class_id INTEGER NOT NULL,
                            color VARCHAR(255) NOT NULL,
                            FOREIGN KEY (class_id) REFERENCES classes(id)
);

CREATE TABLE races (
                     id integer PRIMARY KEY NOT NULL,
                     mask integer NOT NULL,
                     side VARCHAR(255) NOT NULL,
                     name VARCHAR(255) NOT NULL
);

CREATE TABLE toon (
                    id integer AUTO_INCREMENT PRIMARY KEY NOT NULL,
                    name VARCHAR(255) NOT NULL,
                    race_id integer NOT NULL,
                    class_id integer NOT NULL,
                    gender integer NOT NULL,
                    realm VARCHAR(255) NOT NULL,
                    region VARCHAR(255) NOT NULL,
                    UNIQUE (name, realm),
                    FOREIGN KEY (class_id) REFERENCES classes(id),
                    FOREIGN KEY (race_id) REFERENCES races(id)
);

CREATE TABLE stats (
                     id integer AUTO_INCREMENT PRIMARY KEY NOT NULL ,
                     toon_id integer NOT NULL,
                     last_modified bigint,
                     create_date date DEFAULT now(),
                     achievement_points integer,
                     number_exalted integer,
                     mounts_owned integer,
                     quests_completed integer,
                     fish_caught integer,
                     pets_owned integer,
                     pet_battles_won integer,
                     pet_battles_pvp_won integer,
                     level integer,
                     item_level integer,
                     honorable_kills integer,
                     UNIQUE (create_date, toon_id),
                     FOREIGN KEY (toon_id) REFERENCES toon(id)
);


INSERT INTO classes VALUES (1, 1, 'rage', 'Warrior');
INSERT INTO classes VALUES (2, 2, 'mana', 'Paladin');
INSERT INTO classes VALUES (3, 4, 'focus', 'Hunter');
INSERT INTO classes VALUES (4, 8, 'energy', 'Rogue');
INSERT INTO classes VALUES (5, 16, 'mana', 'Priest');
INSERT INTO classes VALUES (6, 32, 'runic-power', 'Death Knight');
INSERT INTO classes VALUES (7, 64, 'mana', 'Shaman');
INSERT INTO classes VALUES (8, 128, 'mana', 'Mage');
INSERT INTO classes VALUES (9, 256, 'mana', 'Warlock');
INSERT INTO classes VALUES (10, 512, 'energy', 'Monk');
INSERT INTO classes VALUES (11, 1024, 'mana', 'Druid');
INSERT INTO classes VALUES (12, 2048, 'fury', 'Demon Hunter');

INSERT INTO races VALUES (1, 1, 'alliance', 'Human');
INSERT INTO races VALUES (2, 2, 'horde', 'Orc');
INSERT INTO races VALUES (3, 4, 'alliance', 'Dwarf');
INSERT INTO races VALUES (4, 8, 'alliance', 'Night Elf');
INSERT INTO races VALUES (5, 16, 'horde', 'Undead');
INSERT INTO races VALUES (6, 32, 'horde', 'Tauren');
INSERT INTO races VALUES (7, 64, 'alliance', 'Gnome');
INSERT INTO races VALUES (8, 128, 'horde', 'Troll');
INSERT INTO races VALUES (9, 256, 'horde', 'Goblin');
INSERT INTO races VALUES (10, 512, 'horde', 'Blood Elf');
INSERT INTO races VALUES (11, 1024, 'alliance', 'Draenei');
INSERT INTO races VALUES (22, 2097152, 'alliance', 'Worgen');
INSERT INTO races VALUES (24, 8388608, 'neutral', 'Pandaren');
INSERT INTO races VALUES (25, 16777216, 'alliance', 'Pandaren');
INSERT INTO races VALUES (26, 33554432, 'horde', 'Pandaren');
INSERT INTO races VALUES (27, 67108864, 'horde', 'Nightborne');
INSERT INTO races VALUES (28, 134217728, 'horde', 'Highmountain Tauren');
INSERT INTO races VALUES (29, 268435456, 'alliance', 'Void Elf');
INSERT INTO races VALUES (30, 536870912, 'alliance', 'Lightforged Draenei');

INSERT INTO class_colors VALUES (1, 1, '#C79C6E');
INSERT INTO class_colors VALUES (2, 2, '#F58CBA');
INSERT INTO class_colors VALUES (3, 3, '#ABD473');
INSERT INTO class_colors VALUES (4, 4, '#FFF569');
INSERT INTO class_colors VALUES (5, 5, '#F0EBE0');
INSERT INTO class_colors VALUES (6, 6, '#C41F3B');
INSERT INTO class_colors VALUES (7, 7, '#0070DE');
INSERT INTO class_colors VALUES (8, 8, '#69CCF0');
INSERT INTO class_colors VALUES (9, 9, '#9482C9');
INSERT INTO class_colors VALUES (10, 10, '#00FF96');
INSERT INTO class_colors VALUES (11, 11, '#FF7D0A');
INSERT INTO class_colors VALUES (12, 12, '#A330C9');