From c6101ba37422dcfcf16131d9dc41692e0725aa81 Mon Sep 17 00:00:00 2001
From: Tom Willemse
Date: Mon, 10 Jun 2024 23:35:51 -0700
Subject: Fix initial database import for sqlite

My understanding of the ‘KEY’ keyword in the ‘tables.sql’ was incomplete. I
didn't realize that it made indexes.
---
 data/tables.sqlite.sql | 26 +++++++++++++++-----------
 1 file changed, 15 insertions(+), 11 deletions(-)

(limited to 'data/tables.sqlite.sql')

diff --git a/data/tables.sqlite.sql b/data/tables.sqlite.sql
index 6a22ad1..b7265cc 100644
--- a/data/tables.sqlite.sql
+++ b/data/tables.sqlite.sql
@@ -20,12 +20,13 @@ CREATE TABLE IF NOT EXISTS `sc_bookmarks` (
   `bVotes` int(11) NOT NULL default '0',
   `bVoting` int(11) NOT NULL default '0',
   `bShort` varchar(16) default NULL,
-  PRIMARY KEY  (`bId`),
-  CONSTRAINT `sc_bookmarks_usd` UNIQUE (`uId`,`bStatus`,`bDatetime`),
-  CONSTRAINT `sc_bookmarks_hui` UNIQUE (`bHash`,`uId`,`bId`),
-  CONSTRAINT `sc_bookmarks_du` UNIQUE (`bDatetime`,`uId`)
+  PRIMARY KEY  (`bId`)
 );
 
+CREATE INDEX IF NOT EXISTS `sc_bookmarks_usd` ON `sc_bookmarks` (`uId`, `bStatus`, `bDatetime`);
+CREATE INDEX IF NOT EXISTS `sc_bookmarks_hui` ON `sc_bookmarks` (`bHash`, `uId`, `bId`);
+CREATE INDEX IF NOT EXISTS `sc_bookmarks_du` ON `sc_bookmarks` (`bDatetime`, `uId`);
+
 -- --------------------------------------------------------
 
 --
@@ -52,10 +53,11 @@ CREATE TABLE IF NOT EXISTS `sc_bookmarks2tags` (
   `bId` int(11) NOT NULL default '0',
   `tag` varchar(100) NOT NULL default '',
   PRIMARY KEY  (`id`),
-  UNIQUE (`tag`,`bId`),
-  CONSTRAINT `sc_bookmarks2tags_bId` UNIQUE (`bId`)
+  CONSTRAINT `sc_bookmarks2tags_tag_bId` UNIQUE (`tag`,`bId`)
 );
 
+CREATE INDEX IF NOT EXISTS `sc_bookmarks2tags_bId` ON `sc_bookmarks2tags` (`bId`);
+
 -- --------------------------------------------------------
 
 --
@@ -97,10 +99,11 @@ CREATE TABLE IF NOT EXISTS `sc_watched` (
   `wId` integer NOT NULL,
   `uId` int(11) NOT NULL default '0',
   `watched` int(11) NOT NULL default '0',
-  PRIMARY KEY  (`wId`),
-  CONSTRAINT `sc_watched_uId` UNIQUE (`uId`)
+  PRIMARY KEY  (`wId`)
 );
 
+CREATE INDEX IF NOT EXISTS `sc_watched_uId` ON `sc_watched` (`uId`);
+
 -- --------------------------------------------------------
 
 --
@@ -191,11 +194,12 @@ CREATE TABLE IF NOT EXISTS `sc_votes` (
   `bId` INT NOT NULL ,
   `uId` INT NOT NULL ,
   `vote` INT( 2 ) NOT NULL ,
-  CONSTRAINT `bid_2` UNIQUE (`bId`,`uId`),
-  CONSTRAINT `bid` UNIQUE (`bId`),
-  CONSTRAINT `uid` UNIQUE (`uId`)
+  CONSTRAINT `bid_2` UNIQUE (`bId`,`uId`)
 );
 
+CREATE INDEX IF NOT EXISTS `bId` ON `sc_votes` (`bId`);
+CREATE INDEX IF NOT EXISTS `uId` ON `sc_votes` (`uId`);
+
 
 CREATE TABLE IF NOT EXISTS `sc_version` (
   `schema_version` int(11) NOT NULL
-- 
cgit v1.2.3-54-g00ecf

