znote_schema.sql

main 560 lines · 21.3 KB Raw
Alex Alex Commit Initial commit 01/10/2026 09:20
1-- Start of Znote AAC database schema
2
3SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;
4
5SET @znote_version = '2.0.0';
6
7CREATE TABLE IF NOT EXISTS `znote` (
8 `id` int NOT NULL AUTO_INCREMENT,
9 `version` varchar(30) NOT NULL COMMENT 'Znote AAC version',
10 `installed` int NOT NULL,
11 `cached` int DEFAULT NULL,
12 PRIMARY KEY (`id`)
13) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
14
15CREATE TABLE IF NOT EXISTS `znote_accounts` (
16 `id` int NOT NULL AUTO_INCREMENT,
17 `account_id` int NOT NULL,
18 `ip` bigint UNSIGNED NOT NULL,
19 `created` int NOT NULL,
20 `points` int DEFAULT 0,
21 `cooldown` int DEFAULT 0,
22 `active` tinyint NOT NULL DEFAULT '0',
23 `active_email` tinyint NOT NULL DEFAULT '0',
24 `activekey` int NOT NULL DEFAULT '0',
25 `flag` varchar(20) NOT NULL,
26 `secret` char(16) DEFAULT NULL,
27 `password_hash` varchar(255) DEFAULT NULL,
28 PRIMARY KEY (`id`)
29) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
30
31CREATE TABLE IF NOT EXISTS `znote_news` (
32 `id` int NOT NULL AUTO_INCREMENT,
33 `title` varchar(30) NOT NULL,
34 `text` text NOT NULL,
35 `date` int NOT NULL,
36 `pid` int NOT NULL,
37 PRIMARY KEY (`id`)
38) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
39
40CREATE TABLE IF NOT EXISTS `znote_images` (
41 `id` int NOT NULL AUTO_INCREMENT,
42 `title` varchar(30) NOT NULL,
43 `desc` text NOT NULL,
44 `date` int NOT NULL,
45 `status` int NOT NULL,
46 `image` varchar(255) NOT NULL,
47 `delhash` varchar(30) NOT NULL,
48 `account_id` int NOT NULL,
49 PRIMARY KEY (`id`)
50) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
51
52CREATE TABLE IF NOT EXISTS `znote_paypal` (
53 `id` int NOT NULL AUTO_INCREMENT,
54 `txn_id` varchar(30) NOT NULL,
55 `email` varchar(255) NOT NULL,
56 `accid` int NOT NULL,
57 `price` int NOT NULL,
58 `points` int NOT NULL,
59 PRIMARY KEY (`id`)
60) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
61
62CREATE TABLE IF NOT EXISTS `znote_paygol` (
63 `id` int NOT NULL AUTO_INCREMENT,
64 `account_id` int NOT NULL,
65 `price` int NOT NULL,
66 `points` int NOT NULL,
67 `message_id` varchar(255) NOT NULL,
68 `service_id` varchar(255) NOT NULL,
69 `shortcode` varchar(255) NOT NULL,
70 `keyword` varchar(255) NOT NULL,
71 `message` varchar(255) NOT NULL,
72 `sender` varchar(255) NOT NULL,
73 `operator` varchar(255) NOT NULL,
74 `country` varchar(255) NOT NULL,
75 `currency` varchar(255) NOT NULL,
76 PRIMARY KEY (`id`)
77) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
78
79CREATE TABLE IF NOT EXISTS `znote_pagseguro` (
80 `id` int NOT NULL AUTO_INCREMENT,
81 `transaction` varchar(36) NOT NULL,
82 `account` int NOT NULL,
83 `price` decimal(11,2) NOT NULL,
84 `points` int NOT NULL,
85 `payment_status` tinyint NOT NULL,
86 `completed` tinyint NOT NULL,
87 PRIMARY KEY (`id`),
88 KEY `transaction` (`transaction`)
89) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
90
91CREATE TABLE IF NOT EXISTS `znote_pagseguro_notifications` (
92 `id` int NOT NULL AUTO_INCREMENT,
93 `notification_code` varchar(40) NOT NULL,
94 `details` text NOT NULL,
95 `receive_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
96 PRIMARY KEY (`id`)
97) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
98
99CREATE TABLE IF NOT EXISTS `znote_payment_transactions` (
100 `id` bigint NOT NULL AUTO_INCREMENT,
101 `provider` varchar(32) NOT NULL,
102 `reference` varchar(128) NOT NULL,
103 `provider_reference` varchar(128) DEFAULT NULL,
104 `account_id` int NOT NULL,
105 `price` decimal(11,2) NOT NULL,
106 `currency` varchar(8) NOT NULL,
107 `points` int NOT NULL,
108 `status` varchar(32) NOT NULL DEFAULT 'pending',
109 `credited` tinyint NOT NULL DEFAULT '0',
110 `test_mode` tinyint NOT NULL DEFAULT '0',
111 `created_at` int NOT NULL,
112 `updated_at` int NOT NULL,
113 `credited_at` int DEFAULT NULL,
114 `payload` longtext,
115 PRIMARY KEY (`id`),
116 UNIQUE KEY `provider_reference_internal` (`provider`, `reference`),
117 KEY `provider_reference_external` (`provider`, `provider_reference`),
118 KEY `account_status` (`account_id`, `status`, `created_at`)
119) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
120
121CREATE TABLE IF NOT EXISTS `znote_payment_events` (
122 `id` bigint NOT NULL AUTO_INCREMENT,
123 `provider` varchar(32) NOT NULL,
124 `event_id` varchar(128) NOT NULL,
125 `provider_reference` varchar(128) DEFAULT NULL,
126 `payment_reference` varchar(128) DEFAULT NULL,
127 `status` varchar(32) NOT NULL DEFAULT 'received',
128 `payload` longtext,
129 `received_at` int NOT NULL,
130 PRIMARY KEY (`id`),
131 UNIQUE KEY `provider_event` (`provider`, `event_id`),
132 KEY `payment_reference` (`provider`, `payment_reference`)
133) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
134
135CREATE TABLE IF NOT EXISTS `znote_players` (
136 `id` int NOT NULL AUTO_INCREMENT,
137 `player_id` int NOT NULL,
138 `created` int NOT NULL,
139 `hide_char` tinyint NOT NULL,
140 `comment` varchar(255) NOT NULL,
141 PRIMARY KEY (`id`)
142) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
143
144CREATE TABLE IF NOT EXISTS `znote_player_reports` (
145 `id` int NOT NULL AUTO_INCREMENT,
146 `name` varchar(50) NOT NULL,
147 `posx` int NOT NULL,
148 `posy` int NOT NULL,
149 `posz` int NOT NULL,
150 `report_description` varchar(255) NOT NULL,
151 `date` int NOT NULL,
152 `status` tinyint NOT NULL DEFAULT '0',
153 PRIMARY KEY (`id`)
154) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
155
156CREATE TABLE IF NOT EXISTS `znote_changelog` (
157 `id` int NOT NULL AUTO_INCREMENT,
158 `text` varchar(255) NOT NULL,
159 `time` int NOT NULL,
160 `report_id` int NOT NULL,
161 `status` tinyint NOT NULL DEFAULT '0',
162 PRIMARY KEY (`id`)
163) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
164
165CREATE TABLE IF NOT EXISTS `znote_shop` (
166 `id` int NOT NULL AUTO_INCREMENT,
167 `type` int NOT NULL,
168 `itemid` int DEFAULT NULL,
169 `count` int NOT NULL DEFAULT '1',
170 `description` varchar(255) NOT NULL,
171 `points` int NOT NULL DEFAULT '10',
172 PRIMARY KEY (`id`)
173) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
174
175CREATE TABLE IF NOT EXISTS `znote_shop_offers` (
176 `id` int NOT NULL AUTO_INCREMENT,
177 `type` int NOT NULL,
178 `itemid` int DEFAULT NULL,
179 `count` int NOT NULL DEFAULT '1',
180 `description` varchar(255) NOT NULL,
181 `points` int NOT NULL DEFAULT '10',
182 `active` tinyint NOT NULL DEFAULT '1',
183 `sort_order` int NOT NULL DEFAULT '0',
184 `created_by` int DEFAULT NULL,
185 `created_at` int DEFAULT NULL,
186 `updated_at` int DEFAULT NULL,
187 PRIMARY KEY (`id`),
188 KEY `active_sort` (`active`, `sort_order`, `id`)
189) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
190
191CREATE TABLE IF NOT EXISTS `znote_shop_logs` (
192 `id` int NOT NULL AUTO_INCREMENT,
193 `account_id` int NOT NULL,
194 `player_id` int NOT NULL,
195 `type` int NOT NULL,
196 `itemid` int NOT NULL,
197 `count` int NOT NULL,
198 `points` int NOT NULL,
199 `time` int NOT NULL,
200 PRIMARY KEY (`id`)
201) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
202
203CREATE TABLE IF NOT EXISTS `znote_shop_orders` (
204 `id` int NOT NULL AUTO_INCREMENT,
205 `account_id` int NOT NULL,
206 `type` int NOT NULL,
207 `itemid` int NOT NULL,
208 `count` int NOT NULL,
209 `time` int NOT NULL DEFAULT '0',
210 PRIMARY KEY (`id`)
211) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
212
213-- Audit trail of mutating actions taken from the admin panel: bans, skill
214-- edits, points, settings changes, plugin lifecycle, etc. Written by
215-- acp_log() in engine/function/adminlog.php, read by Admin Panel > Admin Log.
216CREATE TABLE IF NOT EXISTS `znote_admin_log` (
217 `id` int NOT NULL AUTO_INCREMENT,
218 `admin_id` int NOT NULL DEFAULT '0',
219 `admin_name` varchar(50) NOT NULL DEFAULT '',
220 `action` varchar(64) NOT NULL,
221 `target` varchar(191) NOT NULL DEFAULT '',
222 `details` text NOT NULL,
223 `ip` varchar(45) NOT NULL DEFAULT '',
224 `created` int NOT NULL,
225 PRIMARY KEY (`id`),
226 KEY `admin_created` (`admin_id`, `created`),
227 KEY `action_created` (`action`, `created`),
228 KEY `created` (`created`)
229) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
230
231-- Key/value settings written from the admin panel (active layout, etc).
232CREATE TABLE IF NOT EXISTS `znote_config` (
233 `key` varchar(64) NOT NULL,
234 `value` text NOT NULL,
235 PRIMARY KEY (`key`)
236) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
237
238-- Navigation entries, managed from Admin Panel > Menus.
239-- A theme declares the locations it renders (see layouts/README.md); entries
240-- are grouped by that location and ordered by sort_order.
241CREATE TABLE IF NOT EXISTS `znote_menu` (
242 `id` int NOT NULL AUTO_INCREMENT,
243 `location` varchar(32) NOT NULL COMMENT 'Theme-declared slot: main, sidebar, footer...',
244 `parent_id` int NOT NULL DEFAULT '0' COMMENT '0 = top level, else the id of the parent entry',
245 `label` varchar(64) NOT NULL,
246 `url` varchar(255) NOT NULL,
247 `icon` varchar(48) NOT NULL DEFAULT '' COMMENT 'Optional Font Awesome class',
248 `target` varchar(10) NOT NULL DEFAULT '',
249 `visibility` varchar(10) NOT NULL DEFAULT 'all' COMMENT 'all, guest, user or admin',
250 `sort_order` int NOT NULL DEFAULT '0',
251 `active` tinyint NOT NULL DEFAULT '1',
252 PRIMARY KEY (`id`),
253 KEY `loc_sort` (`location`, `active`, `sort_order`, `id`)
254) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
255
256-- Database-backed pages, used by importers and editable site content.
257CREATE TABLE IF NOT EXISTS `znote_pages` (
258 `id` int NOT NULL AUTO_INCREMENT,
259 `slug` varchar(64) NOT NULL,
260 `title` varchar(100) NOT NULL,
261 `body` mediumtext NOT NULL,
262 `created` int NOT NULL DEFAULT '0',
263 `updated` int NOT NULL DEFAULT '0',
264 `player_id` int NOT NULL DEFAULT '0',
265 `access` tinyint NOT NULL DEFAULT '0',
266 `active` tinyint NOT NULL DEFAULT '1',
267 PRIMARY KEY (`id`),
268 UNIQUE KEY `slug` (`slug`)
269) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
270
271-- Keeps legacy imports idempotent and stores source ids for follow-up imports.
272CREATE TABLE IF NOT EXISTS `znote_convert_map` (
273 `id` int NOT NULL AUTO_INCREMENT,
274 `source` varchar(32) NOT NULL,
275 `source_table` varchar(64) NOT NULL,
276 `source_id` varchar(64) NOT NULL,
277 `target_table` varchar(64) NOT NULL,
278 `target_id` int NOT NULL,
279 `created` int NOT NULL,
280 PRIMARY KEY (`id`),
281 UNIQUE KEY `source_row` (`source`, `source_table`, `source_id`, `target_table`)
282) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
283
284-- Raw archive of legacy AAC tables, including custom columns ZnoteX does not
285-- understand yet. This keeps migrations lossless.
286CREATE TABLE IF NOT EXISTS `znote_legacy_tables` (
287 `id` int NOT NULL AUTO_INCREMENT,
288 `source` varchar(32) NOT NULL,
289 `table_name` varchar(64) NOT NULL,
290 `schema_sql` longtext NOT NULL,
291 `row_count` int NOT NULL DEFAULT '0',
292 `captured` int NOT NULL,
293 PRIMARY KEY (`id`),
294 UNIQUE KEY `source_table` (`source`, `table_name`)
295) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
296
297CREATE TABLE IF NOT EXISTS `znote_legacy_rows` (
298 `id` bigint NOT NULL AUTO_INCREMENT,
299 `source` varchar(32) NOT NULL,
300 `table_name` varchar(64) NOT NULL,
301 `source_pk` varchar(128) NOT NULL DEFAULT '',
302 `row_json` longtext NOT NULL,
303 `captured` int NOT NULL,
304 PRIMARY KEY (`id`),
305 KEY `source_table` (`source`, `table_name`),
306 KEY `source_pk` (`source`, `table_name`, `source_pk`)
307) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
308
309CREATE TABLE IF NOT EXISTS `znote_visitors` (
310 `id` int NOT NULL AUTO_INCREMENT,
311 `ip` bigint NOT NULL,
312 `value` int NOT NULL,
313 PRIMARY KEY (`id`)
314) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
315
316CREATE TABLE IF NOT EXISTS `znote_visitors_details` (
317 `id` int NOT NULL AUTO_INCREMENT,
318 `ip` bigint NOT NULL,
319 `time` int NOT NULL,
320 `type` tinyint NOT NULL,
321 `account_id` int NOT NULL,
322 PRIMARY KEY (`id`)
323) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
324
325-- Forum 1/3 (boards)
326CREATE TABLE IF NOT EXISTS `znote_forum` (
327 `id` int NOT NULL AUTO_INCREMENT,
328 `name` varchar(50) NOT NULL,
329 `access` tinyint NOT NULL,
330 `closed` tinyint NOT NULL,
331 `hidden` tinyint NOT NULL,
332 `guild_id` int NOT NULL,
333 PRIMARY KEY (`id`)
334) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
335
336-- Forum 2/3 (threads)
337CREATE TABLE IF NOT EXISTS `znote_forum_threads` (
338 `id` int NOT NULL AUTO_INCREMENT,
339 `forum_id` int NOT NULL,
340 `player_id` int NOT NULL,
341 `player_name` varchar(50) NOT NULL,
342 `title` varchar(50) NOT NULL,
343 `text` text NOT NULL,
344 `created` int NOT NULL,
345 `updated` int NOT NULL,
346 `sticky` tinyint NOT NULL,
347 `hidden` tinyint NOT NULL,
348 `closed` tinyint NOT NULL,
349 PRIMARY KEY (`id`)
350) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
351
352-- Forum 3/3 (posts)
353CREATE TABLE IF NOT EXISTS `znote_forum_posts` (
354 `id` int NOT NULL AUTO_INCREMENT,
355 `thread_id` int NOT NULL,
356 `player_id` int NOT NULL,
357 `player_name` varchar(50) NOT NULL,
358 `text` text NOT NULL,
359 `created` int NOT NULL,
360 `updated` int NOT NULL,
361 PRIMARY KEY (`id`)
362) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
363
364-- Pending characters for deletion
365CREATE TABLE IF NOT EXISTS `znote_deleted_characters` (
366 `id` int NOT NULL AUTO_INCREMENT,
367 `original_account_id` int NOT NULL,
368 `character_name` varchar(255) NOT NULL,
369 `time` datetime NOT NULL,
370 `done` tinyint NOT NULL,
371 PRIMARY KEY (`id`)
372) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
373
374CREATE TABLE IF NOT EXISTS `znote_guild_wars` (
375 `id` int NOT NULL AUTO_INCREMENT,
376 `limit` int NOT NULL DEFAULT '0',
377 PRIMARY KEY (`id`)
378) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
379
380-- Helpdesk system
381CREATE TABLE IF NOT EXISTS `znote_tickets` (
382 `id` int NOT NULL AUTO_INCREMENT,
383 `owner` int NOT NULL,
384 `username` varchar(32) NOT NULL,
385 `subject` text NOT NULL,
386 `message` text NOT NULL,
387 `ip` bigint NOT NULL,
388 `creation` int NOT NULL,
389 `status` varchar(20) NOT NULL,
390 PRIMARY KEY (`id`)
391) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
392
393CREATE TABLE IF NOT EXISTS `znote_tickets_replies` (
394 `id` int NOT NULL AUTO_INCREMENT,
395 `tid` int NOT NULL,
396 `username` varchar(32) NOT NULL,
397 `message` text NOT NULL,
398 `created` int NOT NULL,
399 PRIMARY KEY (`id`)
400) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
401
402CREATE TABLE IF NOT EXISTS `znote_global_storage` (
403 `key` varchar(32) NOT NULL,
404 `value` TEXT NOT NULL,
405 UNIQUE (`key`)
406) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
407
408-- Character auction system
409CREATE TABLE IF NOT EXISTS `znote_auction_player` (
410 `id` int NOT NULL AUTO_INCREMENT,
411 `player_id` int NOT NULL,
412 `original_account_id` int NOT NULL,
413 `bidder_account_id` int NOT NULL,
414 `time_begin` int NOT NULL,
415 `time_end` int NOT NULL,
416 `price` int NOT NULL,
417 `bid` int NOT NULL,
418 `deposit` int NOT NULL,
419 `sold` tinyint NOT NULL,
420 `claimed` tinyint NOT NULL,
421 PRIMARY KEY (`id`)
422) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
423
424-- Populate basic info
425INSERT INTO `znote` (`version`, `installed`) VALUES
426(@znote_version, UNIX_TIMESTAMP(CURDATE()));
427
428-- Default settings
429INSERT INTO `znote_config` (`key`, `value`) VALUES
430('layout', 'default');
431
432INSERT INTO `znote_menu` (`location`, `parent_id`, `label`, `url`, `icon`, `visibility`, `sort_order`)
433SELECT `seed`.`location`, `seed`.`parent_id`, `seed`.`label`, `seed`.`url`, `seed`.`icon`, `seed`.`visibility`, `seed`.`sort_order`
434FROM (
435 SELECT 'main' AS `location`, 0 AS `parent_id`, 'Home' AS `label`, 'index.php' AS `url`, 'fa-home' AS `icon`, 'all' AS `visibility`, 10 AS `sort_order`
436 UNION ALL SELECT 'main', 0, 'Changelog', 'changelog.php', '', 'all', 20
437 UNION ALL SELECT 'main', 0, 'Account', 'myaccount.php', 'fa-user-circle', 'user', 30
438 UNION ALL SELECT 'main', 0, 'Login', 'login.php', 'fa-user-circle', 'guest', 30
439 UNION ALL SELECT 'main', 0, 'Register', 'register.php', 'fa-key', 'guest', 40
440 UNION ALL SELECT 'main', 0, 'Downloads', 'downloads.php', '', 'all', 50
441 UNION ALL SELECT 'main', 0, 'Community', 'onlinelist.php', 'fa-users', 'all', 60
442 UNION ALL SELECT 'main', 0, 'Highscores', 'highscores.php', '', 'all', 70
443 UNION ALL SELECT 'main', 0, 'Guilds', 'guilds.php', '', 'all', 80
444 UNION ALL SELECT 'main', 0, 'Forum', 'forum.php', '', 'all', 90
445 UNION ALL SELECT 'main', 0, 'Houses', 'houses.php', '', 'all', 100
446 UNION ALL SELECT 'main', 0, 'Latest deaths', 'deaths.php', '', 'all', 110
447 UNION ALL SELECT 'main', 0, 'Kill statistics', 'killers.php', '', 'all', 120
448 UNION ALL SELECT 'main', 0, 'Bans', 'bans.php', '', 'all', 125
449 UNION ALL SELECT 'main', 0, 'Creatures', 'creatures.php', '', 'all', 135
450 UNION ALL SELECT 'main', 0, 'Library', 'serverinfo.php', 'fa-book', 'all', 130
451 UNION ALL SELECT 'main', 0, 'Spells', 'spells.php', '', 'all', 140
452 UNION ALL SELECT 'main', 0, 'Support', 'support.php', 'fa-info-circle', 'all', 150
453 UNION ALL SELECT 'main', 0, 'Helpdesk', 'helpdesk.php', '', 'all', 160
454 UNION ALL SELECT 'main', 0, 'Shop', 'shop.php', 'fa-shopping-cart', 'all', 170
455 UNION ALL SELECT 'main', 0, 'Buy points', 'buypoints.php', '', 'all', 180
456 UNION ALL SELECT 'main', 0, 'Admin Panel', 'admin/index.php', 'fa-sliders', 'admin', 190
457) `seed`
458LEFT JOIN `znote_menu` `existing`
459 ON `existing`.`location` = `seed`.`location`
460 AND `existing`.`label` = `seed`.`label`
461 AND `existing`.`url` = `seed`.`url`
462WHERE `existing`.`id` IS NULL;
463
464
465-- Nest the sub-entries under their section. Done as a second pass because the
466-- parent ids are only known once the rows above exist.
467UPDATE `znote_menu` `c`
468 JOIN `znote_menu` `p` ON `p`.`location` = 'main' AND `p`.`parent_id` = 0 AND `p`.`label` = 'Home'
469 SET `c`.`parent_id` = `p`.`id`
470 WHERE `c`.`location` = 'main' AND `c`.`label` IN ('Changelog');
471
472UPDATE `znote_menu` `c`
473 JOIN `znote_menu` `p` ON `p`.`location` = 'main' AND `p`.`parent_id` = 0 AND `p`.`label` = 'Account'
474 SET `c`.`parent_id` = `p`.`id`
475 WHERE `c`.`location` = 'main' AND `c`.`label` IN ('Downloads');
476
477UPDATE `znote_menu` `c`
478 JOIN `znote_menu` `p` ON `p`.`location` = 'main' AND `p`.`parent_id` = 0 AND `p`.`label` = 'Community'
479 SET `c`.`parent_id` = `p`.`id`
480 WHERE `c`.`location` = 'main' AND `c`.`label` IN ('Highscores','Guilds','Forum','Houses','Latest deaths','Kill statistics','Bans');
481
482UPDATE `znote_menu` `c`
483 JOIN `znote_menu` `p` ON `p`.`location` = 'main' AND `p`.`parent_id` = 0 AND `p`.`label` = 'Library'
484 SET `c`.`parent_id` = `p`.`id`
485 WHERE `c`.`location` = 'main' AND `c`.`label` IN ('Spells','Creatures');
486
487UPDATE `znote_menu` `c`
488 JOIN `znote_menu` `p` ON `p`.`location` = 'main' AND `p`.`parent_id` = 0 AND `p`.`label` = 'Support'
489 SET `c`.`parent_id` = `p`.`id`
490 WHERE `c`.`location` = 'main' AND `c`.`label` IN ('Helpdesk');
491
492UPDATE `znote_menu` `c`
493 JOIN `znote_menu` `p` ON `p`.`location` = 'main' AND `p`.`parent_id` = 0 AND `p`.`label` = 'Shop'
494 SET `c`.`parent_id` = `p`.`id`
495 WHERE `c`.`location` = 'main' AND `c`.`label` IN ('Buy points');
496
497-- Add default forum boards
498INSERT INTO `znote_forum` (`name`, `access`, `closed`, `hidden`, `guild_id`)
499SELECT 'Staff Board', '4', '0', '0', '0' FROM DUAL
500WHERE NOT EXISTS (SELECT 1 FROM `znote_forum` WHERE `name` = 'Staff Board' AND `guild_id` = '0');
501INSERT INTO `znote_forum` (`name`, `access`, `closed`, `hidden`, `guild_id`)
502SELECT 'Tutors Board', '2', '0', '0', '0' FROM DUAL
503WHERE NOT EXISTS (SELECT 1 FROM `znote_forum` WHERE `name` = 'Tutors Board' AND `guild_id` = '0');
504INSERT INTO `znote_forum` (`name`, `access`, `closed`, `hidden`, `guild_id`)
505SELECT 'Discussion', '1', '0', '0', '0' FROM DUAL
506WHERE NOT EXISTS (SELECT 1 FROM `znote_forum` WHERE `name` = 'Discussion' AND `guild_id` = '0');
507INSERT INTO `znote_forum` (`name`, `access`, `closed`, `hidden`, `guild_id`)
508SELECT 'Feedback', '1', '0', '1', '0' FROM DUAL
509WHERE NOT EXISTS (SELECT 1 FROM `znote_forum` WHERE `name` = 'Feedback' AND `guild_id` = '0');
510
511-- Convert existing accounts in database to be Znote AAC compatible
512INSERT INTO `znote_accounts` (`account_id`, `ip`, `created`, `flag`)
513SELECT
514 `a`.`id` AS `account_id`,
515 0 AS `ip`,
516 UNIX_TIMESTAMP(CURDATE()) AS `created`,
517 '' AS `flag`
518FROM `accounts` AS `a`
519LEFT JOIN `znote_accounts` AS `z`
520 ON `a`.`id` = `z`.`account_id`
521WHERE `z`.`created` IS NULL;
522
523-- Convert existing players in database to be Znote AAC compatible
524INSERT INTO `znote_players` (`player_id`, `created`, `hide_char`, `comment`)
525SELECT
526 `p`.`id` AS `player_id`,
527 UNIX_TIMESTAMP(CURDATE()) AS `created`,
528 0 AS `hide_char`,
529 '' AS `comment`
530FROM `players` AS `p`
531LEFT JOIN `znote_players` AS `z`
532 ON `p`.`id` = `z`.`player_id`
533WHERE `z`.`created` IS NULL;
534
535-- Delete duplicate account records
536DELETE `d` FROM `znote_accounts` AS `d`
537INNER JOIN (
538 SELECT `i`.`account_id`,
539 MAX(`i`.`id`) AS `retain`
540 FROM `znote_accounts` AS `i`
541 GROUP BY `i`.`account_id`
542 HAVING COUNT(`i`.`id`) > 1
543) AS `x`
544 ON `d`.`account_id` = `x`.`account_id`
545 AND `d`.`id` != `x`.`retain`;
546
547-- Delete duplicate player records
548DELETE `d` FROM `znote_players` AS `d`
549INNER JOIN (
550 SELECT `i`.`player_id`,
551 MAX(`i`.`id`) AS `retain`
552 FROM `znote_players` AS `i`
553 GROUP BY `i`.`player_id`
554 HAVING COUNT(`i`.`id`) > 1
555) AS `x`
556 ON `d`.`player_id` = `x`.`player_id`
557 AND `d`.`id` != `x`.`retain`;
558
559-- End of Znote AAC database schema
560
Top