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