yZKNqmbE

1
2DROP TABLE IF EXISTS wcf1_gman_group;
3CREATE TABLE wcf1_gman_group (
4  groupID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
5  title VARCHAR(50)  NOT NULL,
6  teaser VARCHAR(250) NOT NULL DEFAULT '',
7  wcfGroupID INT(10) NOT NULL,
8  showCalender TINYINT(1) NOT NULL DEFAULT 0,
9  calendarTitle VARCHAR(50) NOT NULL,
10  calendartext text  NOT NULL,
11  fetchCalendar TINYINT(1) NOT NULL DEFAULT 0,
12  calendarQuerry VARCHAR(25) NOT NULL DEFAULT '',
13  gameTitle VARCHAR(50)  NOT NULL,
14  gameRank TINYINT(1) NOT NULL DEFAULT 0,
15  showRoaster TINYINT(1) NOT NULL DEFAULT 1,
16  articIeID INT(10) NULL,
17  threadID INT(10) NULL,
18  boardID INT(10) NULL,
19  mediaID INT(10) NULL,
20  isRaidgruop INT(10) NOT NULL,
21  fetchWCL TINYINT(1) NOT NULL DEFAULT 0,
22  wclQuerry VARCHAR(50) NOT NULL DEFAULT '',
23  orderNo SMALLINT(4) NOT NULL,
24  lastUpdate INT(10) NOT NULL
25  KEY (wcfGroupID, articIeID, threadID, boardID, mediaID);
26) ;
27
28ALTER TABLE wcf1_gman_application ADD FOREIGN KEY (articIeID) REFERENCES wcf1_acticle (articIeID) ON DELETE SET NULL;
29ALTER TABLE wcf1_gman_application ADD FOREIGN KEY (threadID) REFERENCES wbb1_thread (threadID) ON DELETE SET NULL;
30ALTER TABLE wcf1_gman_application ADD FOREIGN KEY (boardID) REFERENCES wbb1_borad (boardID) ON DELETE SET NULL;
31ALTER TABLE wcf1_gman_application ADD FOREIGN KEY (mediaID) REFERENCES wcf1_media (mediaID) ON DELETE SET NULL;
32
33
34CREATE TABLE wcf1_gman_wow_realm (
35  realmID SMALLINT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
36  type VARCHAR(20)  NOT NULL DEFAULT '',
37  population VARCHAR(10)  NOT NULL DEFAULT '',
38  queue TINYINT(1) NOT NULL DEFAULT 0,
39  status TINYINT(2) NOT NULL DEFAULT 0,
40  name VARCHAR(30)  NOT NULL,
41  battlegroup VARCHAR(50)  NOT NULL DEFAULT '',
42  timezone VARCHAR(25)  NOT NULL DEFAULT '',
43  connected_realms text  NOT NULL DEFAULT '',
44  slug VARCHAR(30)  NOT NULL,
45  locale VARCHAR(6)  NOT NULL DEFAULT ''
46  KEY(name)
47  UNIQUE KEY(slug)
48);
49
50CREATE TABLE wcf1_gman_wow_character (
51  charID INT(10) NOT NULL,
52  userID INT(10) NOT NULL,
53  charname VARCHAR(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
54  isMain TINYINT(1) NOT NULL DEFAULT 0,
55  inGuild  TINYINT(1) NOT NULL DEFAULT 0,
56  realmID SMALLINT(4) NOT NULL ,
57  bnetData text  NOT NULL ,
58  primaryGroup SMALLINT(5) NULL,
59  groups VARCHAR(100) NOT NULL,
60  bnetUpdate INT(10) NOT NULL,
61  firstSeen INT(10) NOT NULL 
62) ;
63
64ALTER TABLE wcf1_gman_wow_character ADD FOREIGN KEY (userID) REFERENCES wcf1_user (userID) ON DELETE SET NULL;
65ALTER TABLE wcf1_gman_wow_character ADD FOREIGN KEY (realmID) REFERENCES wcf1_gman_wow_realm (realmID) ON DELETE RESTRICT;
66ALTER TABLE wcf1_gman_wow_character ADD FOREIGN KEY (primaryGroup) REFERENCES wcf1_gman_group (primaryGroup) ON DELETE SET NULL;
67
68
69DROP TABLE IF EXISTS wcf1_gman_application;
70CREATE TABLE wcf1_gman_application (
71  appID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
72  threadID INT(10) NULL ,
73  pollID INT(10) DEFAULT NULL ,
74  charID INT(10) NOT NULL ,
75  autoClose TINYINT(10) NOT NULL DEFAULT 0,
76  pollEnd INT(10) NOT NULL,
77  assignedOfficerID INT(10) NULL,
78  InterviewDate INT(10) NOT NULL ,
79  appState TINYINT(1) NOT NULL DEFAULT 0,
80  openDate INT(10) NOT NULL,
81  KEY (appState, threadID, pollID)
82) ;
83
84ALTER TABLE wcf1_gman_application ADD FOREIGN KEY (assignedOfficerID) REFERENCES wcf1_user (userID) ON DELETE SET NULL;
85ALTER TABLE wcf1_gman_application ADD FOREIGN KEY (threadID) REFERENCES wbb1_thread (threadID) ON DELETE SET NULL;
86
87CREATE TABLE wcf1_gman_pointtype (
88  typeID SMALLINT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
89  groupID INT(10) NOT NULL,
90  content VARCHAR(250)  NOT NULL 
91) ;
92
93CREATE TABLE wcf1_gman_pointtrans (
94  transID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
95  charID INT(10) NOT NULL,
96  groupID INT(10) NOT NULL,
97  eventID INT(10) NOT NULL,
98  amount SMALLINT(4) NOT NULL,
99  typeID SMALLINT(4) NULL,
100  itemID INT(10) NOT NULL DEFAULT 0,
101  comment VARCHAR(250)  NOT NULL DEFAULT '' ,
102  issuerID INT(10) NULL,
103  transDate INT(10) NOT NULL,
104  autoDelete TINYINT(1) NOT NULL DEFAULT 0,
105  deleteDate INT(10) NULL
106
107  KEY (deleteDate, autoDelete, charID, groupID, typeID)
108) ;
109
110ALTER TABLE wcf1_gman_pointtrans ADD FOREIGN KEY (issuerID) REFERENCES wcf1_user (userID) ON DELETE SET NULL;
111ALTER TABLE wcf1_gman_pointtrans ADD FOREIGN KEY (eventID) REFERENCES wcf1_gman_calevent (eventID) ON DELETE SET NULL;
112ALTER TABLE wcf1_gman_pointtrans ADD FOREIGN KEY (charID) REFERENCES wcf1_gman_wow_character (charID) ON DELETE CASCADE;
113ALTER TABLE wcf1_gman_pointtrans ADD FOREIGN KEY (typeID) REFERENCES wcf1_gman_pointtype (typeID) ON DELETE RESTRICT;
114
115CREATE TABLE wcf1_gman_wow_character_item (
116  charID int(11) NOT NULL PRIMARY KEY, 
117  averageItemLevel smallint(5) NOT NULL,
118  averageItemLevelEquipped smallint(5) NOT NULL,
119  head TEXT NULL,
120  neck TEXT NULL,
121  shoulder TEXT NULL,
122  back TEXT NULL,
123  chest TEXT NULL,
124  wrist TEXT NULL,
125  hands TEXT NULL,
126  waist TEXT NULL,
127  legs TEXT NULL,
128  feet TEXT  NULL,
129  finger1 TEXT NULL,
130  finger2 TEXT NULL,
131  trinket1 TEXT NULL,
132  trinket2 TEXT NULL,
133  mainHand TEXT NULL,
134  offHand TEXT NULL,
135  lastUpdate int(11)
136);
137
138ALTER TABLE wcf1_gman_wow_character_item ADD FOREIGN KEY (charID) REFERENCES wcf1_gman_wow_character (charID) ON DELETE CASCADE;
139
140CREATE TABLE wcf1_gman_wow_classes (
141  id INT(10) NOT NULL PRIMARY KEY,
142  mask INT(10) NOT NULL,
143  powerType VARCHAR(15)  NOT NULL ,
144  name VARCHAR(20)  NOT NULL ,
145  color VARCHAR(12)  NOT NULL 
146) ;
147INSERT INTO wcf1_gman_wow_classes (id, mask, powerType, name, color) VALUES ('1', '1', 'rage', 'Krieger', '#C79C6E'), ('2', '2', 'mana', 'Paladin', '#F58CBA'), ('3', '4', 'focus', 'Jäger', '#ABD473'), ('4', '8', 'energy', 'Schurke', '#FFF569'), ('5', '16', 'mana', 'Priester', '#FFFFFF'), ('6', '32', 'runic-power', 'Todesritter', '#C41F3B'), ('7', '64', 'mana', 'Schamane', '#0070DE'), ('8', '128', 'mana', 'Magier', '#69CCF0'), ('9', '256', 'mana', 'Hexenmeister', '#9482C9'), ('10', '512', 'energy', 'Mönch', '#00FF96'), ('11', '1024', 'mana', 'Druide', '#FF7D0A'), ('12', '2048', 'fury', 'Dämonenjäger', '#A330C9')
148
149CREATE TABLE wcf1_gman_wow_races (
150  id INT(10) NOT NULL PRIMARY KEY,
151  mask INT(10) NOT NULL,
152  faction VARCHAR(20)  NOT NULL
153  factionID tinyINT(4) NOT NULL,
154  name VARCHAR(50)  NOT NULL
155) ;
156INSERT INTO wcf1_gman_wow_races (id, mask, side, sideID, name) VALUES ('1', '1', 'alliance', '0', 'Mensch'), ('2', '2', 'horde', '1', 'Orc'), ('3', '4', 'alliance', '0', 'Zwerg'), ('4', '8', 'alliance', '0', 'Nachtelf'), ('5', '16', 'horde', '1', 'Untoter'), ('6', '32', 'horde', '1', 'Tauren'), ('7', '64', 'alliance', '0', 'Gnom'), ('8', '128', 'horde', '1', 'Troll'), ('9', '256', 'horde', '1', 'Goblin'), ('10', '512', 'horde', '1', 'Blutelf'), ('11', '1024', 'alliance', '0', 'Draenei'), ('22', '2097152', 'alliance', '0', 'Worgen'), ('24', '8388608', 'neutral', '2', 'Pandaren'), ('25', '16777216', 'alliance', '0', 'Pandaren'), ('26', '33554432', 'horde', '1', 'Pandaren');
157
158CREATE TABLE wcf1_gman_wow_acms (
159  acmID INT(10) NOT NULL PRIMARY KEY,
160  bnetData text  NULL ,
161  bnetUpdate INT(10) NOT NULL 
162) ;
163
164  CREATE TABLE wcf1_gman_wow_gacms (
165  gacmID INT(10) NOT NULL PRIMARY KEY,
166  bnetData text  NULL,
167  bnetUpdate INT(10) NOT NULL
168) ;
169
170CREATE TABLE wcf1_gman_wow_items (
171  itemID INT(10) NOT NULL PRIMARY KEY,
172  bnetData text  NULL,
173  bnetUpdate INT(10) NOT NULL
174) ;
175
176CREATE TABLE wcf1_gman_wow_itemclass (
177  itemclassID INT(10) NOT NULL PRIMARY KEY,
178  bnetData text  NULL ,
179  bnetUpdate INT(10) NOT NULL 
180) ;
181
182CREATE TABLE wcf1_gman_guild_acm (
183  acmID int(5) NOT NULL PRIMARY KEY,
184  gacmID int(5) NOT NULL,
185  acmTime int(10) NOT NULL,
186  articelID int(10) NULL
187)
188
189CREATE TABLE wcf1_gman_feedlist (
190  feedID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
191  type enum('ACHIEVEMENT','CRITERIA','LOOT','BOSSKILL')  NOT NULL ,
192  charID INT(10) NULL,
193  itemID INT(10) NULL,
194  acmID INT(10) NULL,
195  quantity INT(10) NOT NULL,
196  bonusLists text  NULL,
197  context text  NULL,
198  feedTime INT(10) NOT NULL,
199  inGuild tinyINT(1) NOT NULL DEFAULT 1,
200  KEY (charID)
201) ;
202
203ALTER TABLE wcf1_gman_feedlist ADD FOREIGN KEY (charID) REFERENCES wcf1_gman_wow_character (charID) ON DELETE CASCADE;