aboutsummaryrefslogtreecommitdiff
path: root/data/schema
diff options
context:
space:
mode:
Diffstat (limited to 'data/schema')
-rw-r--r--data/schema/1.sql20
-rw-r--r--data/schema/2.sql10
-rw-r--r--data/schema/3.sql85
-rw-r--r--data/schema/4.sql11
-rw-r--r--data/schema/5.sql1
-rw-r--r--data/schema/6.sql19
6 files changed, 146 insertions, 0 deletions
diff --git a/data/schema/1.sql b/data/schema/1.sql
new file mode 100644
index 0000000..e09a210
--- /dev/null
+++ b/data/schema/1.sql
@@ -0,0 +1,20 @@
+RENAME TABLE `sc_tags` TO `sc_bookmarks2tags` ;
+
+ CREATE TABLE `sc_searchhistory` (
+ `shId` int(11) NOT NULL auto_increment,
+ `shTerms` varchar(255) NOT NULL default '',
+ `shRange` varchar(32) NOT NULL default '',
+ `shDatetime` datetime NOT NULL default '0000-00-00 00:00:00',
+ `shNbResults` int(6) NOT NULL default '0',
+ `uId` int(11) NOT NULL default '0',
+ PRIMARY KEY (`shId`)
+ );
+
+ CREATE TABLE `sc_tags` (
+ `tId` int(11) NOT NULL auto_increment,
+ `tag` varchar(32) NOT NULL default '',
+ `uId` int(11) NOT NULL default '0',
+ `tDescription` varchar(255) default NULL,
+ PRIMARY KEY (`tId`),
+ UNIQUE KEY `sc_tags_tag_uId` (`tag`, `uId`)
+ );
diff --git a/data/schema/2.sql b/data/schema/2.sql
new file mode 100644
index 0000000..171e011
--- /dev/null
+++ b/data/schema/2.sql
@@ -0,0 +1,10 @@
+ALTER TABLE `sc_bookmarks` CHANGE `bDescription` `bDescription` VARCHAR( 1500 );
+CREATE TABLE `sc_tagscache` (
+ `tcId` int(11) NOT NULL auto_increment,
+ `tag1` varchar(100) NOT NULL default '',
+ `tag2` varchar(100) NOT NULL default '',
+ `relationType` varchar(32) NOT NULL default '',
+ `uId` int(11) NOT NULL default '0',
+ PRIMARY KEY (`tcId`),
+ UNIQUE KEY `sc_tagscache_tag1_tag2_type_uId` (`tag1`,`tag2`,`relationType`,`uId`)
+);
diff --git a/data/schema/3.sql b/data/schema/3.sql
new file mode 100644
index 0000000..32431b9
--- /dev/null
+++ b/data/schema/3.sql
@@ -0,0 +1,85 @@
+/* modify and add fields */
+ALTER TABLE `sc_bookmarks` MODIFY `bAddress` varchar(1500) NOT NULL;
+ALTER TABLE `sc_bookmarks` MODIFY `bDescription` TEXT default NULL;
+ALTER TABLE `sc_bookmarks` ADD `bPrivateNote` TEXT NULL AFTER `bDescription` ;
+ALTER TABLE `sc_tags` MODIFY `tDescription` TEXT default NULL;
+ALTER TABLE `sc_commondescription` MODIFY `cdDescription` TEXT default NULL;
+
+/* convert to UTF-8 if your table is ISO-something (through BLOB: tips provided by MYSQL documentation)*/
+/* first need to remove index keys because of BLOB constraints*/
+ALTER TABLE `sc_tags` DROP INDEX `sc_tags_tag_uId`;
+ALTER TABLE `sc_bookmarks2tags` DROP INDEX `sc_bookmarks2tags_tag_bId`;
+ALTER TABLE `sc_bookmarks2tags` DROP INDEX `sc_bookmarks2tags_bId`;
+ALTER TABLE `sc_tags2tags` DROP INDEX `sc_tags2tags_tag1_tag2_uId`;
+ALTER TABLE `sc_commondescription` DROP INDEX `sc_commondescription_tag_datetime`;
+ALTER TABLE `sc_tagscache` DROP INDEX `sc_tagscache_tag1_tag2_type_uId`;
+ALTER TABLE `sc_tagsstats` DROP INDEX `sc_tagsstats_tag1_type_uId`;
+
+/* secondly convert through BLOB type */
+ALTER TABLE `sc_bookmarks` CHANGE `bTitle` `bTitle` BLOB;
+ALTER TABLE `sc_bookmarks` CHANGE `bTitle` `bTitle` varchar(255) CHARACTER SET utf8;
+ALTER TABLE `sc_bookmarks` CHANGE `bAddress` `bAddress` BLOB;
+ALTER TABLE `sc_bookmarks` CHANGE `bAddress` `bAddress` varchar(1500) CHARACTER SET utf8;
+ALTER TABLE `sc_bookmarks` CHANGE `bDescription` `bDescription` BLOB;
+ALTER TABLE `sc_bookmarks` CHANGE `bDescription` `bDescription` text CHARACTER SET utf8;
+ALTER TABLE `sc_bookmarks` CHANGE `bPrivateNote` `bPrivateNote` BLOB;
+ALTER TABLE `sc_bookmarks` CHANGE `bPrivateNote` `bPrivateNote` text CHARACTER SET utf8;
+
+ALTER TABLE `sc_tags` CHANGE `tag` `tag` BLOB;
+ALTER TABLE `sc_tags` CHANGE `tag` `tag` varchar(100) CHARACTER SET utf8;
+ALTER TABLE `sc_tags` CHANGE `tDescription` `tDescription` BLOB;
+ALTER TABLE `sc_tags` CHANGE `tDescription` `tDescription` text CHARACTER SET utf8;
+
+ALTER TABLE `sc_bookmarks2tags` CHANGE `tag` `tag` BLOB;
+ALTER TABLE `sc_bookmarks2tags` CHANGE `tag` `tag` varchar(100) CHARACTER SET utf8;
+
+ALTER TABLE `sc_users` CHANGE `name` `name` BLOB;
+ALTER TABLE `sc_users` CHANGE `name` `name` varchar(50) CHARACTER SET utf8;
+ALTER TABLE `sc_users` CHANGE `uContent` `uContent` BLOB;
+ALTER TABLE `sc_users` CHANGE `uContent` `uContent` text CHARACTER SET utf8;
+
+ALTER TABLE `sc_tags2tags` CHANGE `tag1` `tag1` BLOB;
+ALTER TABLE `sc_tags2tags` CHANGE `tag1` `tag1` varchar(100) CHARACTER SET utf8;
+ALTER TABLE `sc_tags2tags` CHANGE `tag2` `tag2` BLOB;
+ALTER TABLE `sc_tags2tags` CHANGE `tag2` `tag2` varchar(100) CHARACTER SET utf8;
+
+ALTER TABLE `sc_tagsstats` CHANGE `tag1` `tag1` BLOB;
+ALTER TABLE `sc_tagsstats` CHANGE `tag1` `tag1` varchar(100) CHARACTER SET utf8;
+
+ALTER TABLE `sc_tagscache` CHANGE `tag1` `tag1` BLOB;
+ALTER TABLE `sc_tagscache` CHANGE `tag1` `tag1` varchar(100) CHARACTER SET utf8;
+ALTER TABLE `sc_tagscache` CHANGE `tag2` `tag2` BLOB;
+ALTER TABLE `sc_tagscache` CHANGE `tag2` `tag2` varchar(100) CHARACTER SET utf8;
+
+ALTER TABLE `sc_commondescription` CHANGE `tag` `tag` BLOB;
+ALTER TABLE `sc_commondescription` CHANGE `tag` `tag` varchar(100) CHARACTER SET utf8;
+ALTER TABLE `sc_commondescription` CHANGE `cdTitle` `cdTitle` BLOB;
+ALTER TABLE `sc_commondescription` CHANGE `cdTitle` `cdTitle` varchar(255) CHARACTER SET utf8;
+ALTER TABLE `sc_commondescription` CHANGE `cdDescription` `cdDescription` BLOB;
+ALTER TABLE `sc_commondescription` CHANGE `cdDescription` `cdDescription` text CHARACTER SET utf8;
+
+ALTER TABLE `sc_searchhistory` CHANGE `shTerms` `shTerms` BLOB;
+ALTER TABLE `sc_searchhistory` CHANGE `shTerms` `shTerms` varchar(255) CHARACTER SET utf8;
+ALTER TABLE `sc_searchhistory` CHANGE `shRange` `shRange` BLOB;
+ALTER TABLE `sc_searchhistory` CHANGE `shRange` `shRange` varchar(32) CHARACTER SET utf8;
+
+/* Thirdly re-add index keys */
+ALTER TABLE `sc_tags` ADD UNIQUE KEY `sc_tags_tag_uId` (`tag`, `uId`);
+ALTER TABLE `sc_bookmarks2tags` ADD UNIQUE KEY `sc_bookmarks2tags_tag_bId` (`tag`,`bId`);
+ALTER TABLE `sc_bookmarks2tags` ADD KEY `sc_bookmarks2tags_bId` (`bId`);
+ALTER TABLE `sc_tags2tags` ADD UNIQUE KEY `sc_tags2tags_tag1_tag2_uId` (`tag1`,`tag2`,`relationType`,`uId`);
+ALTER TABLE `sc_commondescription` ADD UNIQUE KEY `sc_commondescription_tag_datetime` (`tag`,`cdDatetime`);
+ALTER TABLE `sc_tagscache` ADD UNIQUE KEY `sc_tagscache_tag1_tag2_type_uId` (`tag1`,`tag2`,`relationType`,`uId`);
+ALTER TABLE `sc_tagsstats` ADD UNIQUE KEY `sc_tagsstats_tag1_type_uId` (`tag1`,`relationType`,`uId`);
+
+/* Change tables to utf-8 charset */
+ALTER TABLE `sc_bookmarks` CHARACTER SET utf8 COLLATE utf8_general_ci;
+ALTER TABLE `sc_tags` CHARACTER SET utf8 COLLATE utf8_general_ci;
+ALTER TABLE `sc_bookmarks2tags` CHARACTER SET utf8 COLLATE utf8_general_ci;
+ALTER TABLE `sc_users` CHARACTER SET utf8 COLLATE utf8_general_ci;
+ALTER TABLE `sc_watched` CHARACTER SET utf8 COLLATE utf8_general_ci;
+ALTER TABLE `sc_tags2tags` CHARACTER SET utf8 COLLATE utf8_general_ci;
+ALTER TABLE `sc_tagsstats` CHARACTER SET utf8 COLLATE utf8_general_ci;
+ALTER TABLE `sc_tagscache` CHARACTER SET utf8 COLLATE utf8_general_ci;
+ALTER TABLE `sc_commondescription` CHARACTER SET utf8 COLLATE utf8_general_ci;
+ALTER TABLE `sc_searchhistory` CHARACTER SET utf8 COLLATE utf8_general_ci;
diff --git a/data/schema/4.sql b/data/schema/4.sql
new file mode 100644
index 0000000..022aaf0
--- /dev/null
+++ b/data/schema/4.sql
@@ -0,0 +1,11 @@
+ALTER TABLE `sc_bookmarks` ADD `bVoting` INT NOT NULL;
+ALTER TABLE `sc_bookmarks` ADD `bVotes` INT NOT NULL;
+
+CREATE TABLE `sc_votes` (
+ `bId` INT NOT NULL ,
+ `uId` INT NOT NULL ,
+ `vote` INT( 2 ) NOT NULL ,
+ UNIQUE KEY `bid_2` (`bId`,`uId`),
+ KEY `bid` (`bId`),
+ KEY `uid` (`uId`)
+) CHARACTER SET utf8 COLLATE utf8_general_ci ;
diff --git a/data/schema/5.sql b/data/schema/5.sql
new file mode 100644
index 0000000..d4c9afc
--- /dev/null
+++ b/data/schema/5.sql
@@ -0,0 +1 @@
+ALTER TABLE `sc_bookmarks` ADD `bShort` VARCHAR(16) NULL DEFAULT NULL;
diff --git a/data/schema/6.sql b/data/schema/6.sql
new file mode 100644
index 0000000..cf9dd37
--- /dev/null
+++ b/data/schema/6.sql
@@ -0,0 +1,19 @@
+CREATE TABLE `sc_version` (
+ `schema_version` int(11) NOT NULL
+) DEFAULT CHARSET=utf8;
+INSERT INTO `sc_version` (`schema_version`) VALUES ('6');
+
+CREATE TABLE `sc_users_sslclientcerts` (
+ `id` INT NOT NULL AUTO_INCREMENT ,
+ `uId` INT NOT NULL ,
+ `sslSerial` VARCHAR( 32 ) NOT NULL ,
+ `sslClientIssuerDn` VARCHAR( 1024 ) NOT NULL ,
+ `sslName` VARCHAR( 64 ) NOT NULL ,
+ `sslEmail` VARCHAR( 64 ) NOT NULL ,
+ PRIMARY KEY ( `id` ) ,
+ UNIQUE (`id`)
+) CHARACTER SET utf8 COLLATE utf8_general_ci;
+
+ALTER TABLE `sc_users` ADD `privateKey` VARCHAR(33) NULL;
+CREATE UNIQUE INDEX `privateKey` ON `sc_users` (`privateKey`);
+
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-04 21:45:25 +0000