From: rk <rk>
Date: Fri, 12 Dec 2003 18:23:57 +0000 (+0000)
Subject: some minor changes on indexes, ogg support media_type, unique for login
X-Git-Tag: LATEST_MERGED_1_1~203
X-Git-Url: http://erislabs.net/gitweb/?p=mir.git;a=commitdiff_plain;h=ba26236a30dd58eec15a0bbc88f13dd1c8f2acc4

some minor changes on indexes, ogg support media_type, unique for login
---

diff --git a/dbscripts/updates/update20031212.sql b/dbscripts/updates/update20031212.sql
new file mode 100755
index 00000000..9baa2076
--- /dev/null
+++ b/dbscripts/updates/update20031212.sql
@@ -0,0 +1,56 @@
+-- update script 2003-12-12 by rk
+--
+-- * makes login unique on webdb_users
+
+-- * drops unnecessary index on webdb_users
+-- * inserts a line for ogg support in media_types
+-- * drop "- deprecated -" from media_types
+-- * drop redundant index on id of topic
+-- * drop redundant index on id of comment
+-- * recreate indexes on content_x_media
+-- 
+-- Application of this scripts requires tomcat restart
+-- because media_type is cached
+--
+-- This script will terminate with an error the second time it's run,
+--   so running this script when it isn't needed can't do any harm.
+--
+
+BEGIN TRANSACTION;
+
+ALTER TABLE "webdb_users" ADD CONSTRAINT "webdb_users_login_key" UNIQUE (login);
+DROP INDEX "idx_webdb_user_log_pas_is_admin";
+
+--
+-- ogg format support
+-- 
+
+DELETE FROM "media_type" WHERE mime_type='- deprecated -';
+DELETE FROM "media_type" WHERE mime_type='application/x-ogg';
+INSERT INTO "media_type" ("name", "mime_type", "classname","tablename")
+VALUES ('ogg', 'application/x-ogg', 'Ogg', 'Audio');
+
+--
+-- topic index double
+--
+DROP INDEX "idx_topic_id";
+
+--
+-- comment index double
+--
+DROP INDEX "idx_comment_id";
+
+
+--
+-- recreate indexes on cxm
+--
+DROP INDEX "idx_content_media";
+DROP INDEX "idx_media_content";
+CREATE INDEX "idx_cxm_content" using btree ( "content_id" "int4_ops" );
+CREATE INDEX "idx_cxm_media" using btree ( "media_id" "int4_ops" );
+ANALYZE "content_x_media";
+
+--
+-- that's it!
+  
+COMMIT TRANSACTION;