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 (executable)
index 0000000..9baa207
--- /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;