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/3.sql | 85 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 85 insertions(+)
 create mode 100644 data/schema/3.sql

(limited to 'data/schema/3.sql')

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;
-- 
cgit v1.2.3-54-g00ecf

