2.0.0_pages_and_convert_map.sql

main 67 lines · 2.2 KB Raw
Alex Alex Commit Initial commit 01/10/2026 09:20
1-- Adds database-backed pages and legacy conversion mapping tables.
2-- Safe to run more than once.
3
4CREATE TABLE IF NOT EXISTS `znote_pages` (
5 `id` int NOT NULL AUTO_INCREMENT,
6 `slug` varchar(64) NOT NULL,
7 `title` varchar(100) NOT NULL,
8 `body` mediumtext NOT NULL,
9 `created` int NOT NULL DEFAULT '0',
10 `updated` int NOT NULL DEFAULT '0',
11 `player_id` int NOT NULL DEFAULT '0',
12 `access` tinyint NOT NULL DEFAULT '0',
13 `active` tinyint NOT NULL DEFAULT '1',
14 PRIMARY KEY (`id`),
15 UNIQUE KEY `slug` (`slug`)
16) ENGINE=InnoDB;
17
18CREATE TABLE IF NOT EXISTS `znote_convert_map` (
19 `id` int NOT NULL AUTO_INCREMENT,
20 `source` varchar(32) NOT NULL,
21 `source_table` varchar(64) NOT NULL,
22 `source_id` varchar(64) NOT NULL,
23 `target_table` varchar(64) NOT NULL,
24 `target_id` int NOT NULL,
25 `created` int NOT NULL,
26 PRIMARY KEY (`id`),
27 UNIQUE KEY `source_row` (`source`, `source_table`, `source_id`, `target_table`)
28) ENGINE=InnoDB;
29
30CREATE TABLE IF NOT EXISTS `znote_legacy_tables` (
31 `id` int NOT NULL AUTO_INCREMENT,
32 `source` varchar(32) NOT NULL,
33 `table_name` varchar(64) NOT NULL,
34 `schema_sql` longtext NOT NULL,
35 `row_count` int NOT NULL DEFAULT '0',
36 `captured` int NOT NULL,
37 PRIMARY KEY (`id`),
38 UNIQUE KEY `source_table` (`source`, `table_name`)
39) ENGINE=InnoDB;
40
41SET @znote_add_schema_sql := IF(
42 (
43 SELECT COUNT(*)
44 FROM INFORMATION_SCHEMA.COLUMNS
45 WHERE TABLE_SCHEMA = DATABASE()
46 AND TABLE_NAME = 'znote_legacy_tables'
47 AND COLUMN_NAME = 'schema_sql'
48 ) = 0,
49 'ALTER TABLE `znote_legacy_tables` ADD `schema_sql` longtext NULL AFTER `table_name`',
50 'SELECT 1'
51);
52PREPARE znote_add_schema_sql_stmt FROM @znote_add_schema_sql;
53EXECUTE znote_add_schema_sql_stmt;
54DEALLOCATE PREPARE znote_add_schema_sql_stmt;
55
56CREATE TABLE IF NOT EXISTS `znote_legacy_rows` (
57 `id` bigint NOT NULL AUTO_INCREMENT,
58 `source` varchar(32) NOT NULL,
59 `table_name` varchar(64) NOT NULL,
60 `source_pk` varchar(128) NOT NULL DEFAULT '',
61 `row_json` longtext NOT NULL,
62 `captured` int NOT NULL,
63 PRIMARY KEY (`id`),
64 KEY `source_table` (`source`, `table_name`),
65 KEY `source_pk` (`source`, `table_name`, `source_pk`)
66) ENGINE=InnoDB;
67
Top