-- 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);

--
-- 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" ON "content_x_media" using btree ( "content_id" "int4_ops" );
CREATE INDEX "idx_cxm_media" ON "content_x_media" using btree ( "media_id" "int4_ops" );

--
-- that's it!
  
COMMIT TRANSACTION;
ANALYZE "content_x_media";

