From 2419885577b4e38b82f5b0f4fbfe8077f680d825 Mon Sep 17 00:00:00 2001 From: Christian Weiske Date: Fri, 27 May 2011 06:59:33 +0200 Subject: make upgrade file fully rST-compatible and move SQL commands out into single schema files --- data/schema/1.sql | 20 +++++++++++++ data/schema/2.sql | 10 +++++++ data/schema/3.sql | 85 +++++++++++++++++++++++++++++++++++++++++++++++++++++++ data/schema/4.sql | 11 +++++++ data/schema/5.sql | 1 + 5 files changed, 127 insertions(+) create mode 100644 data/schema/1.sql create mode 100644 data/schema/2.sql create mode 100644 data/schema/3.sql create mode 100644 data/schema/4.sql create mode 100644 data/schema/5.sql (limited to 'data') 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..6c3bfaf --- /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; -- cgit v1.2.3