· 5 years ago · Mar 07, 2020, 06:41 PM
1-- Start of Znote AAC database schema
2
3SET @znote_version = '1.5_SVN';
4
5CREATE TABLE IF NOT EXISTS `znote` (
6 `id` int(10) NOT NULL AUTO_INCREMENT,
7 `version` varchar(30) NOT NULL COMMENT 'Znote AAC version',
8 `installed` int(10) NOT NULL,
9 `cached` int(11) DEFAULT NULL,
10 PRIMARY KEY (`id`)
11) ENGINE=InnoDB;
12
13CREATE TABLE IF NOT EXISTS `znote_accounts` (
14 `id` int(11) NOT NULL AUTO_INCREMENT,
15 `account_id` int(11) NOT NULL,
16 `ip` bigint(20) UNSIGNED NOT NULL,
17 `created` int(10) NOT NULL,
18 `points` int(10) DEFAULT 0,
19 `cooldown` int(10) DEFAULT 0,
20 `active` tinyint(4) NOT NULL DEFAULT '0',
21 `active_email` tinyint(4) NOT NULL DEFAULT '0',
22 `activekey` int(11) NOT NULL DEFAULT '0',
23 `flag` varchar(20) NOT NULL,
24 `secret` char(16) DEFAULT NULL,
25 PRIMARY KEY (`id`)
26) ENGINE=InnoDB;
27
28CREATE TABLE IF NOT EXISTS `znote_news` (
29 `id` int(11) NOT NULL AUTO_INCREMENT,
30 `title` varchar(30) NOT NULL,
31 `text` text NOT NULL,
32 `date` int(11) NOT NULL,
33 `pid` int(11) NOT NULL,
34 PRIMARY KEY (`id`)
35) ENGINE=InnoDB;
36
37CREATE TABLE IF NOT EXISTS `znote_images` (
38 `id` int(11) NOT NULL AUTO_INCREMENT,
39 `title` varchar(30) NOT NULL,
40 `desc` text NOT NULL,
41 `date` int(11) NOT NULL,
42 `status` int(11) NOT NULL,
43 `image` varchar(30) NOT NULL,
44 `account_id` int(11) NOT NULL,
45 PRIMARY KEY (`id`)
46) ENGINE=InnoDB;
47
48CREATE TABLE IF NOT EXISTS `znote_paypal` (
49 `id` int(11) NOT NULL AUTO_INCREMENT,
50 `txn_id` varchar(30) NOT NULL,
51 `email` varchar(255) NOT NULL,
52 `accid` int(11) NOT NULL,
53 `price` int(11) NOT NULL,
54 `points` int(11) NOT NULL,
55 PRIMARY KEY (`id`)
56) ENGINE=InnoDB;
57
58CREATE TABLE IF NOT EXISTS `znote_paygol` (
59 `id` int(11) NOT NULL AUTO_INCREMENT,
60 `account_id` int(11) NOT NULL,
61 `price` int(11) NOT NULL,
62 `points` int(11) NOT NULL,
63 `message_id` varchar(255) NOT NULL,
64 `service_id` varchar(255) NOT NULL,
65 `shortcode` varchar(255) NOT NULL,
66 `keyword` varchar(255) NOT NULL,
67 `message` varchar(255) NOT NULL,
68 `sender` varchar(255) NOT NULL,
69 `operator` varchar(255) NOT NULL,
70 `country` varchar(255) NOT NULL,
71 `currency` varchar(255) NOT NULL,
72 PRIMARY KEY (`id`)
73) ENGINE=InnoDB;
74
75CREATE TABLE IF NOT EXISTS `znote_players` (
76 `id` int(11) NOT NULL AUTO_INCREMENT,
77 `player_id` int(11) NOT NULL,
78 `created` int(11) NOT NULL,
79 `hide_char` tinyint(4) NOT NULL,
80 `comment` varchar(255) NOT NULL,
81 PRIMARY KEY (`id`)
82) ENGINE=InnoDB;
83
84CREATE TABLE IF NOT EXISTS `znote_player_reports` (
85 `id` int(11) NOT NULL AUTO_INCREMENT,
86 `name` varchar(50) NOT NULL,
87 `posx` int(6) NOT NULL,
88 `posy` int(6) NOT NULL,
89 `posz` int(6) NOT NULL,
90 `report_description` VARCHAR(255) NOT NULL,
91 `date` INT(11) NOT NULL,
92 `status` TINYINT(3) NOT NULL DEFAULT '0',
93 PRIMARY KEY (`id`)
94) ENGINE=InnoDB;
95
96CREATE TABLE IF NOT EXISTS `znote_changelog` (
97 `id` int(11) NOT NULL AUTO_INCREMENT,
98 `text` VARCHAR(255) NOT NULL,
99 `time` INT(11) NOT NULL,
100 `report_id` INT(11) NOT NULL,
101 `status` TINYINT(3) NOT NULL DEFAULT '0',
102 PRIMARY KEY (`id`)
103) ENGINE=InnoDB;
104
105CREATE TABLE IF NOT EXISTS `znote_shop` (
106 `id` int(11) NOT NULL AUTO_INCREMENT,
107 `type` int(11) NOT NULL,
108 `itemid` int(11) DEFAULT NULL,
109 `count` int(11) NOT NULL DEFAULT '1',
110 `description` varchar(255) NOT NULL,
111 `points` int(11) NOT NULL DEFAULT '10',
112 PRIMARY KEY (`id`)
113) ENGINE=InnoDB;
114
115CREATE TABLE IF NOT EXISTS `znote_shop_logs` (
116 `id` int(11) NOT NULL AUTO_INCREMENT,
117 `account_id` int(11) NOT NULL,
118 `player_id` int(11) NOT NULL,
119 `type` int(11) NOT NULL,
120 `itemid` int(11) NOT NULL,
121 `count` int(11) NOT NULL,
122 `points` int(11) NOT NULL,
123 `time` int(11) NOT NULL,
124 PRIMARY KEY (`id`)
125) ENGINE=InnoDB;
126
127CREATE TABLE IF NOT EXISTS `znote_shop_orders` (
128 `id` int(11) NOT NULL AUTO_INCREMENT,
129 `account_id` int(11) NOT NULL,
130 `type` int(11) NOT NULL,
131 `itemid` int(11) NOT NULL,
132 `count` int(11) NOT NULL,
133 `time` int(11) NOT NULL DEFAULT '0',
134 PRIMARY KEY (`id`)
135) ENGINE=InnoDB;
136
137CREATE TABLE IF NOT EXISTS `znote_visitors` (
138 `id` int(11) NOT NULL AUTO_INCREMENT,
139 `ip` bigint(20) NOT NULL,
140 `value` int(11) NOT NULL,
141 PRIMARY KEY (`id`)
142) ENGINE=InnoDB;
143
144CREATE TABLE IF NOT EXISTS `znote_visitors_details` (
145 `id` int(11) NOT NULL AUTO_INCREMENT,
146 `ip` bigint(20) NOT NULL,
147 `time` int(11) NOT NULL,
148 `type` tinyint(4) NOT NULL,
149 `account_id` int(11) NOT NULL,
150 PRIMARY KEY (`id`)
151) ENGINE=InnoDB;
152
153-- Forum 1/3 (boards)
154CREATE TABLE IF NOT EXISTS `znote_forum` (
155 `id` int(11) NOT NULL AUTO_INCREMENT,
156 `name` varchar(50) NOT NULL,
157 `access` tinyint(4) NOT NULL,
158 `closed` tinyint(4) NOT NULL,
159 `hidden` tinyint(4) NOT NULL,
160 `guild_id` int(11) NOT NULL,
161 PRIMARY KEY (`id`)
162) ENGINE=InnoDB;
163
164-- Forum 2/3 (threads)
165CREATE TABLE IF NOT EXISTS `znote_forum_threads` (
166 `id` int(11) NOT NULL AUTO_INCREMENT,
167 `forum_id` int(11) NOT NULL,
168 `player_id` int(11) NOT NULL,
169 `player_name` varchar(50) NOT NULL,
170 `title` varchar(50) NOT NULL,
171 `text` text NOT NULL,
172 `created` int(11) NOT NULL,
173 `updated` int(11) NOT NULL,
174 `sticky` tinyint(4) NOT NULL,
175 `hidden` tinyint(4) NOT NULL,
176 `closed` tinyint(4) NOT NULL,
177 PRIMARY KEY (`id`)
178) ENGINE=InnoDB;
179
180-- Forum 3/3 (posts)
181CREATE TABLE IF NOT EXISTS `znote_forum_posts` (
182 `id` int(11) NOT NULL AUTO_INCREMENT,
183 `thread_id` int(11) NOT NULL,
184 `player_id` int(11) NOT NULL,
185 `player_name` varchar(50) NOT NULL,
186 `text` text NOT NULL,
187 `created` int(11) NOT NULL,
188 `updated` int(11) NOT NULL,
189 PRIMARY KEY (`id`)
190) ENGINE=InnoDB;
191
192-- Pending characters for deletion
193CREATE TABLE IF NOT EXISTS `znote_deleted_characters` (
194 `id` int(11) NOT NULL AUTO_INCREMENT,
195 `original_account_id` int(11) NOT NULL,
196 `character_name` varchar(255) NOT NULL,
197 `time` datetime NOT NULL,
198 `done` tinyint(1) NOT NULL,
199 PRIMARY KEY (`id`)
200) ENGINE=InnoDB;
201
202CREATE TABLE IF NOT EXISTS `znote_guild_wars` (
203 `id` int(11) NOT NULL AUTO_INCREMENT,
204 `limit` int(11) NOT NULL DEFAULT '0',
205 PRIMARY KEY (`id`)
206 --,FOREIGN KEY (`id`) REFERENCES `guild_wars` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
207) ENGINE=InnoDB;
208
209-- Helpdesk system
210CREATE TABLE IF NOT EXISTS `znote_tickets` (
211 `id` int(11) NOT NULL AUTO_INCREMENT,
212 `owner` int(11) NOT NULL,
213 `username` varchar(32) CHARACTER SET latin1 NOT NULL,
214 `subject` text CHARACTER SET latin1 NOT NULL,
215 `message` text CHARACTER SET latin1 NOT NULL,
216 `ip` bigint(20) NOT NULL,
217 `creation` int(11) NOT NULL,
218 `status` varchar(20) CHARACTER SET latin1 NOT NULL,
219 PRIMARY KEY (`id`)
220) ENGINE=InnoDB;
221
222CREATE TABLE IF NOT EXISTS `znote_tickets_replies` (
223 `id` int(11) NOT NULL AUTO_INCREMENT,
224 `tid` int(11) NOT NULL,
225 `username` varchar(32) CHARACTER SET latin1 NOT NULL,
226 `message` text CHARACTER SET latin1 NOT NULL,
227 `created` int(11) NOT NULL,
228 PRIMARY KEY (`id`)
229) ENGINE=InnoDB;
230
231CREATE TABLE IF NOT EXISTS `znote_global_storage` (
232 `key` VARCHAR(32) NOT NULL,
233 `value` TEXT NOT NULL,
234 UNIQUE (`key`)
235) ENGINE=InnoDB;
236
237-- Character auction system
238CREATE TABLE IF NOT EXISTS `znote_auction_player` (
239 `id` int(11) NOT NULL AUTO_INCREMENT,
240 `player_id` int(11) NOT NULL,
241 `original_account_id` int(11) NOT NULL,
242 `bidder_account_id` int(11) NOT NULL,
243 `time_begin` int(11) NOT NULL,
244 `time_end` int(11) NOT NULL,
245 `price` int(11) NOT NULL,
246 `bid` int(11) NOT NULL,
247 `deposit` int(11) NOT NULL,
248 `sold` tinyint(1) NOT NULL,
249 `claimed` tinyint(1) NOT NULL,
250 PRIMARY KEY (`id`)
251) ENGINE=InnoDB;
252
253-- Populate basic info
254INSERT INTO `znote` (`version`, `installed`) VALUES
255(@znote_version, UNIX_TIMESTAMP(CURDATE()));
256
257-- Add default forum boards
258INSERT INTO `znote_forum` (`name`, `access`, `closed`, `hidden`, `guild_id`) VALUES
259('Staff Board', '4', '0', '0', '0'),
260('Tutors Board', '2', '0', '0', '0'),
261('Discussion', '1', '0', '0', '0'),
262('Feedback', '1', '0', '1', '0');
263
264-- Convert existing accounts in database to be Znote AAC compatible
265INSERT INTO `znote_accounts` (`account_id`, `ip`, `created`, `flag`)
266SELECT
267 `a`.`id` AS `account_id`,
268 0 AS `ip`,
269 UNIX_TIMESTAMP(CURDATE()) AS `created`,
270 '' AS `flag`
271FROM `accounts` AS `a`
272LEFT JOIN `znote_accounts` AS `z`
273 ON `a`.`id` = `z`.`account_id`
274WHERE `z`.`created` IS NULL;
275
276-- Convert existing players in database to be Znote AAC compatible
277INSERT INTO `znote_players` (`player_id`, `created`, `hide_char`, `comment`)
278SELECT
279 `p`.`id` AS `player_id`,
280 UNIX_TIMESTAMP(CURDATE()) AS `created`,
281 0 AS `hide_char`,
282 '' AS `comment`
283FROM `players` AS `p`
284LEFT JOIN `znote_players` AS `z`
285 ON `p`.`id` = `z`.`player_id`
286WHERE `z`.`created` IS NULL;
287
288-- Delete duplicate account records
289DELETE `d` FROM `znote_accounts` AS `d`
290INNER JOIN (
291 SELECT `i`.`account_id`,
292 MAX(`i`.`id`) AS `retain`
293 FROM `znote_accounts` AS `i`
294 GROUP BY `i`.`account_id`
295 HAVING COUNT(`i`.`id`) > 1
296) AS `x`
297 ON `d`.`account_id` = `x`.`account_id`
298 AND `d`.`id` != `x`.`retain`;
299
300-- Delete duplicate player records
301DELETE `d` FROM `znote_players` AS `d`
302INNER JOIN (
303 SELECT `i`.`player_id`,
304 MAX(`i`.`id`) AS `retain`
305 FROM `znote_players` AS `i`
306 GROUP BY `i`.`player_id`
307 HAVING COUNT(`i`.`id`) > 1
308) AS `x`
309 ON `d`.`player_id` = `x`.`player_id`
310 AND `d`.`id` != `x`.`retain`;
311
312-- End of Znote AAC database schema