Added features:

[mir.git] / dbscripts / updates / update20031124.sql

diff --git a/dbscripts/updates/update20031124.sql b/dbscripts/updates/update20031124.sql
new file mode 100755 (executable)
index 0000000..99645ec
--- /dev/null
+++ b/dbscripts/updates/update20031124.sql
@@ -0,0 +1,97 @@
+-- update script 2003-11-25 by Zapata
+-- * Clean up comments 
+-- * added a comment field for internal comments
+-- This script will terminate with an error the second time it's run,
+--   so running this script when it isn't needed is prevented from doing do any harm.
+
+BEGIN TRANSACTION;
+
+----------------------
+-- COMMENT changes
+----------------------
+
+ALTER TABLE "comment" RENAME TO "comment_old";
+DROP INDEX comment_pkey;
+DROP INDEX comment_checksum_index;
+DROP INDEX idx_comment_to_media;
+DROP INDEX idx_comment_webdb_create;
+DROP INDEX idx_comment_tomedia_ispublished;
+DROP INDEX idx_comment_id;
+  
+CREATE TABLE "comment" (
+  "id" integer DEFAULT nextval('comment_id_seq'::text) NOT NULL,
+  "title" character varying(80) NOT NULL,
+  "creator" character varying(80) NOT NULL,
+  "description" text NOT NULL,
+  "main_url" character varying(255),
+  "email" character varying(80),
+  "address" character varying(80),
+  "phone" character varying(20),
+  "webdb_create" timestamp with time zone NOT NULL,
+  "webdb_lastchange" timestamp with time zone,
+  "is_published" boolean DEFAULT '1' NOT NULL,
+  "to_language" integer DEFAULT '0' NOT NULL,
+  "to_media" integer NOT NULL,
+  "to_comment_status" smallint,
+       "comment" text,
+  "is_html" boolean DEFAULT '0' NOT NULL,
+  Constraint "comment_pkey" Primary Key ("id")
+);
+
+CREATE        INDEX "idx_comment_to_media"            on "comment" using btree ( "to_media" "int4_ops" );
+CREATE        INDEX idx_comment_webdb_create          on  comment(webdb_create);
+CREATE        INDEX "idx_comment_tomedia_ispublished" on "comment" using btree ( "to_media" "int4_ops", "is_published" "bool_ops" );
+CREATE UNIQUE INDEX "idx_comment_id"                  on "comment" using btree ( "id" "int4_ops" );
+
+INSERT INTO "comment" (
+  "id",
+  "title",
+  "creator",
+  "description",
+  "main_url",
+  "email",
+  "address",
+  "phone",
+  "webdb_create",
+  "webdb_lastchange",
+  "is_published",
+  "to_language",
+  "to_media",
+  "to_comment_status",
+  "is_html",
+       "comment")
+select
+  "id",
+  "title",
+  "creator",
+  "description",
+  "main_url",
+  "email",
+  "address",
+  "phone",
+  "webdb_create",
+  "webdb_lastchange",
+  "is_published",
+  "to_language",
+  "to_media",
+  "to_comment_status",
+  "is_html",
+       ''
+from comment_old;      
+
+
+UPDATE  pg_class
+SET
+  relowner = (SELECT relowner FROM pg_class WHERE relname='comment_old'),
+  relacl =   (SELECT relacl FROM pg_class WHERE relname='comment_old')
+WHERE 
+  relname = 'comment';
+  
+-- to prevent this script from being run successfully for a second time
+INSERT INTO comment_old(to_media, description, checksum, title, creator, webdb_create) values(1, '', 1, '', '', now());
+
+DROP TABLE comment_old;
+
+-- thats it!
+  
+COMMIT TRANSACTION;