From c60bf5cc4b62bdb5259ed085e197f8b3385fea24 Mon Sep 17 00:00:00 2001 From: Banana Date: Mon, 11 Sep 2023 15:44:48 +0200 Subject: [PATCH] update install sql file --- documentation/insipid-edit-me.sql | 104 ++++++++++++++++++------------ 1 file changed, 62 insertions(+), 42 deletions(-) diff --git a/documentation/insipid-edit-me.sql b/documentation/insipid-edit-me.sql index fea2e5d..889201a 100644 --- a/documentation/insipid-edit-me.sql +++ b/documentation/insipid-edit-me.sql @@ -1,5 +1,4 @@ SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; -SET AUTOCOMMIT = 0; START TRANSACTION; SET time_zone = "+00:00"; @@ -9,44 +8,54 @@ SET time_zone = "+00:00"; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; +-- +-- Database: `insipid` +-- + +-- -------------------------------------------------------- + -- -- Table structure for table `#REPLACE_ME#_category` -- DROP TABLE IF EXISTS `#REPLACE_ME#_category`; CREATE TABLE `#REPLACE_ME#_category` ( - `id` int(10) NOT NULL, - `name` varchar(128) COLLATE utf8mb4_bin NOT NULL + `id` int NOT NULL, + `name` varchar(128) COLLATE utf8mb4_bin NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT; +-- -------------------------------------------------------- + -- -- Table structure for table `#REPLACE_ME#_categoryrelation` -- DROP TABLE IF EXISTS `#REPLACE_ME#_categoryrelation`; CREATE TABLE `#REPLACE_ME#_categoryrelation` ( - `linkid` int(10) NOT NULL, - `categoryid` int(10) NOT NULL + `linkid` int NOT NULL, + `categoryid` int NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT; +-- -------------------------------------------------------- + -- -- Stand-in structure for view `#REPLACE_ME#_combined` -- (See below for the actual view) -- DROP VIEW IF EXISTS `#REPLACE_ME#_combined`; CREATE TABLE `#REPLACE_ME#_combined` ( - `id` int(10) - ,`link` mediumtext - ,`created` datetime - ,`status` int(2) - ,`description` varchar(255) - ,`title` varchar(255) - ,`image` varchar(255) - ,`hash` char(32) - ,`tag` varchar(64) - ,`tagId` int(10) - ,`category` varchar(128) - ,`categoryId` int(10) + `category` varchar(128) + ,`categoryId` int + ,`created` datetime + ,`description` varchar(255) + ,`hash` char(32) + ,`id` int + ,`image` varchar(255) + ,`link` mediumtext + ,`status` int + ,`tag` varchar(64) + ,`tagId` int + ,`title` varchar(255) ); -- -------------------------------------------------------- @@ -57,42 +66,48 @@ CREATE TABLE `#REPLACE_ME#_combined` ( DROP TABLE IF EXISTS `#REPLACE_ME#_link`; CREATE TABLE `#REPLACE_ME#_link` ( - `id` int(10) NOT NULL, - `link` mediumtext COLLATE utf8mb4_bin NOT NULL, - `created` datetime NOT NULL, - `updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - `status` int(2) NOT NULL, - `description` varchar(255) COLLATE utf8mb4_bin NOT NULL, - `title` varchar(255) COLLATE utf8mb4_bin NOT NULL, - `image` varchar(255) COLLATE utf8mb4_bin NOT NULL, - `hash` char(32) COLLATE utf8mb4_bin NOT NULL, - `search` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL + `id` int NOT NULL, + `link` mediumtext COLLATE utf8mb4_bin NOT NULL, + `created` datetime NOT NULL, + `updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `status` int NOT NULL, + `description` varchar(255) COLLATE utf8mb4_bin NOT NULL, + `title` varchar(255) COLLATE utf8mb4_bin NOT NULL, + `image` varchar(255) COLLATE utf8mb4_bin NOT NULL, + `hash` char(32) COLLATE utf8mb4_bin NOT NULL, + `search` text COLLATE utf8mb4_bin NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT; +-- -------------------------------------------------------- + -- -- Table structure for table `#REPLACE_ME#_tag` -- DROP TABLE IF EXISTS `#REPLACE_ME#_tag`; CREATE TABLE `#REPLACE_ME#_tag` ( - `id` int(10) NOT NULL, - `name` varchar(64) COLLATE utf8mb4_bin NOT NULL + `id` int NOT NULL, + `name` varchar(64) COLLATE utf8mb4_bin NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT; +-- -------------------------------------------------------- + -- -- Table structure for table `#REPLACE_ME#_tagrelation` -- DROP TABLE IF EXISTS `#REPLACE_ME#_tagrelation`; CREATE TABLE `#REPLACE_ME#_tagrelation` ( - `linkid` int(10) NOT NULL, - `tagid` int(10) NOT NULL + `linkid` int NOT NULL, + `tagid` int NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT; +-- -------------------------------------------------------- + -- -- Structure for view `#REPLACE_ME#_combined` -- -DROP VIEW IF EXISTS `#REPLACE_ME#_combined`; +DROP TABLE IF EXISTS `#REPLACE_ME#_combined`; CREATE VIEW `#REPLACE_ME#_combined` AS select `#REPLACE_ME#_link`.`id` AS `id`, @@ -121,33 +136,38 @@ left join `#REPLACE_ME#_category` on((`#REPLACE_ME#_category`.`id` = `#REPLACE_M -- Indexes for table `#REPLACE_ME#_category` -- ALTER TABLE `#REPLACE_ME#_category` - ADD PRIMARY KEY (`id`); + ADD PRIMARY KEY (`id`), + ADD KEY `name` (`name`); -- -- Indexes for table `#REPLACE_ME#_categoryrelation` -- ALTER TABLE `#REPLACE_ME#_categoryrelation` - ADD UNIQUE KEY `linkid` (`linkid`,`categoryid`); + ADD UNIQUE KEY `linkid` (`linkid`,`categoryid`), + ADD KEY `categoryid` (`categoryid`); -- -- Indexes for table `#REPLACE_ME#_link` -- ALTER TABLE `#REPLACE_ME#_link` - ADD PRIMARY KEY (`id`), - ADD UNIQUE KEY `hash` (`hash`); + ADD PRIMARY KEY (`id`), + ADD UNIQUE KEY `hash` (`hash`), + ADD KEY `status` (`status`), + ADD KEY `created` (`created`); ALTER TABLE `#REPLACE_ME#_link` ADD FULLTEXT KEY `search` (`search`); -- -- Indexes for table `#REPLACE_ME#_tag` -- ALTER TABLE `#REPLACE_ME#_tag` - ADD PRIMARY KEY (`id`); + ADD PRIMARY KEY (`id`); -- -- Indexes for table `#REPLACE_ME#_tagrelation` -- ALTER TABLE `#REPLACE_ME#_tagrelation` - ADD UNIQUE KEY `linkid` (`linkid`,`tagid`); + ADD UNIQUE KEY `tagid` (`linkid`,`tagid`), + ADD KEY `linkid` (`linkid`); -- -- AUTO_INCREMENT for dumped tables @@ -157,21 +177,21 @@ ALTER TABLE `#REPLACE_ME#_tagrelation` -- AUTO_INCREMENT for table `#REPLACE_ME#_category` -- ALTER TABLE `#REPLACE_ME#_category` - MODIFY `id` int(10) NOT NULL AUTO_INCREMENT; + MODIFY `id` int NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `#REPLACE_ME#_link` -- ALTER TABLE `#REPLACE_ME#_link` - MODIFY `id` int(10) NOT NULL AUTO_INCREMENT; + MODIFY `id` int NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `#REPLACE_ME#_tag` -- ALTER TABLE `#REPLACE_ME#_tag` - MODIFY `id` int(10) NOT NULL AUTO_INCREMENT; + MODIFY `id` int NOT NULL AUTO_INCREMENT; COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; -/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; \ No newline at end of file -- 2.39.5