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