2.0.0_menus.sql

main 97 lines · 5.3 KB Raw
Alex Alex Commit Initial commit 01/10/2026 09:20
1-- ---------------------------------------------------------------------------
2-- ZnoteX 2.0.0 - navigation menus
3--
4-- Only needed for databases created BEFORE this feature. A fresh import of
5-- SQL/znote_schema.sql already contains the table and its default entries.
6--
7-- Menu links used to be hardcoded in every theme, which meant editing PHP to
8-- add a link and duplicating the whole menu in each theme. They live here now
9-- and are edited from Admin Panel > Menus.
10--
11-- Run once:
12-- mysql -u <user> -p <database> < SQL/migrations/2.0.0_menus.sql
13-- ---------------------------------------------------------------------------
14
15-- Navigation entries, managed from Admin Panel > Menus.
16-- A theme declares the locations it renders (see layouts/README.md); entries
17-- are grouped by that location and ordered by sort_order.
18CREATE TABLE IF NOT EXISTS `znote_menu` (
19 `id` int NOT NULL AUTO_INCREMENT,
20 `location` varchar(32) NOT NULL COMMENT 'Theme-declared slot: main, sidebar, footer...',
21 `parent_id` int NOT NULL DEFAULT '0' COMMENT '0 = top level, else the id of the parent entry',
22 `label` varchar(64) NOT NULL,
23 `url` varchar(255) NOT NULL,
24 `icon` varchar(48) NOT NULL DEFAULT '' COMMENT 'Optional Font Awesome class',
25 `target` varchar(10) NOT NULL DEFAULT '',
26 `visibility` varchar(10) NOT NULL DEFAULT 'all' COMMENT 'all, guest, user or admin',
27 `sort_order` int NOT NULL DEFAULT '0',
28 `active` tinyint NOT NULL DEFAULT '1',
29 PRIMARY KEY (`id`),
30 KEY `loc_sort` (`location`, `active`, `sort_order`, `id`)
31) ENGINE=InnoDB;
32
33INSERT INTO `znote_menu` (`location`, `parent_id`, `label`, `url`, `icon`, `visibility`, `sort_order`)
34SELECT `seed`.`location`, `seed`.`parent_id`, `seed`.`label`, `seed`.`url`, `seed`.`icon`, `seed`.`visibility`, `seed`.`sort_order`
35FROM (
36 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`
37 UNION ALL SELECT 'main', 0, 'Changelog', 'changelog.php', '', 'all', 20
38 UNION ALL SELECT 'main', 0, 'Account', 'myaccount.php', 'fa-user-circle', 'user', 30
39 UNION ALL SELECT 'main', 0, 'Login', 'login.php', 'fa-user-circle', 'guest', 30
40 UNION ALL SELECT 'main', 0, 'Register', 'register.php', 'fa-key', 'guest', 40
41 UNION ALL SELECT 'main', 0, 'Downloads', 'downloads.php', '', 'all', 50
42 UNION ALL SELECT 'main', 0, 'Community', 'onlinelist.php', 'fa-users', 'all', 60
43 UNION ALL SELECT 'main', 0, 'Highscores', 'highscores.php', '', 'all', 70
44 UNION ALL SELECT 'main', 0, 'Guilds', 'guilds.php', '', 'all', 80
45 UNION ALL SELECT 'main', 0, 'Forum', 'forum.php', '', 'all', 90
46 UNION ALL SELECT 'main', 0, 'Houses', 'houses.php', '', 'all', 100
47 UNION ALL SELECT 'main', 0, 'Latest deaths', 'deaths.php', '', 'all', 110
48 UNION ALL SELECT 'main', 0, 'Kill statistics', 'killers.php', '', 'all', 120
49 UNION ALL SELECT 'main', 0, 'Bans', 'bans.php', '', 'all', 125
50 UNION ALL SELECT 'main', 0, 'Creatures', 'creatures.php', '', 'all', 135
51 UNION ALL SELECT 'main', 0, 'Library', 'serverinfo.php', 'fa-book', 'all', 130
52 UNION ALL SELECT 'main', 0, 'Spells', 'spells.php', '', 'all', 140
53 UNION ALL SELECT 'main', 0, 'Support', 'support.php', 'fa-info-circle', 'all', 150
54 UNION ALL SELECT 'main', 0, 'Helpdesk', 'helpdesk.php', '', 'all', 160
55 UNION ALL SELECT 'main', 0, 'Shop', 'shop.php', 'fa-shopping-cart', 'all', 170
56 UNION ALL SELECT 'main', 0, 'Buy points', 'buypoints.php', '', 'all', 180
57 UNION ALL SELECT 'main', 0, 'Admin Panel', 'admin/index.php', 'fa-sliders', 'admin', 190
58) `seed`
59LEFT JOIN `znote_menu` `existing`
60 ON `existing`.`location` = `seed`.`location`
61 AND `existing`.`parent_id` = 0
62 AND `existing`.`label` = `seed`.`label`
63WHERE `existing`.`id` IS NULL;
64
65-- Nest the sub-entries under their section. Done as a second pass because the
66-- parent ids are only known once the rows above exist.
67UPDATE `znote_menu` `c`
68 JOIN `znote_menu` `p` ON `p`.`location` = 'main' AND `p`.`parent_id` = 0 AND `p`.`label` = 'Home'
69 SET `c`.`parent_id` = `p`.`id`
70 WHERE `c`.`location` = 'main' AND `c`.`label` IN ('Changelog');
71
72UPDATE `znote_menu` `c`
73 JOIN `znote_menu` `p` ON `p`.`location` = 'main' AND `p`.`parent_id` = 0 AND `p`.`label` = 'Account'
74 SET `c`.`parent_id` = `p`.`id`
75 WHERE `c`.`location` = 'main' AND `c`.`label` IN ('Downloads');
76
77UPDATE `znote_menu` `c`
78 JOIN `znote_menu` `p` ON `p`.`location` = 'main' AND `p`.`parent_id` = 0 AND `p`.`label` = 'Community'
79 SET `c`.`parent_id` = `p`.`id`
80 WHERE `c`.`location` = 'main' AND `c`.`label` IN ('Highscores','Guilds','Forum','Houses','Latest deaths','Kill statistics','Bans');
81
82UPDATE `znote_menu` `c`
83 JOIN `znote_menu` `p` ON `p`.`location` = 'main' AND `p`.`parent_id` = 0 AND `p`.`label` = 'Library'
84 SET `c`.`parent_id` = `p`.`id`
85 WHERE `c`.`location` = 'main' AND `c`.`label` IN ('Spells','Creatures');
86
87UPDATE `znote_menu` `c`
88 JOIN `znote_menu` `p` ON `p`.`location` = 'main' AND `p`.`parent_id` = 0 AND `p`.`label` = 'Support'
89 SET `c`.`parent_id` = `p`.`id`
90 WHERE `c`.`location` = 'main' AND `c`.`label` IN ('Helpdesk');
91
92UPDATE `znote_menu` `c`
93 JOIN `znote_menu` `p` ON `p`.`location` = 'main' AND `p`.`parent_id` = 0 AND `p`.`label` = 'Shop'
94 SET `c`.`parent_id` = `p`.`id`
95 WHERE `c`.`location` = 'main' AND `c`.`label` IN ('Buy points');
96
97
Top