1 -- update script 2003-04-09 by Zapata
2 -- * creates table comment_x_media, to support media in comments
3 -- * adds webdb_lastchange to comment
4 -- This script will the second time this script is run,
5 -- so running this script when it isn't needed can't do any harm.
7 -- IMPORTANT: after running this script, run the update_all_sequences script as well
11 -- task 1: comment_x_media
13 CREATE TABLE comment_x_media (
18 CREATE UNIQUE INDEX idx_comment_media on comment_x_media (comment_id, media_id);
19 CREATE UNIQUE INDEX idx_media_comment on comment_x_media (media_id, comment_id);
21 -- task 2: add webdb_lastchange to table comment
23 ALTER TABLE "comment" RENAME TO "comment_old";
24 DROP INDEX comment_pkey;
26 CREATE TABLE "comment" (
27 "id" integer DEFAULT nextval('comment_id_seq') NOT NULL,
28 "title" character varying(80) NOT NULL,
29 "creator" character varying(80) NOT NULL,
30 "description" text NOT NULL,
31 "main_url" character varying(255),
32 "email" character varying(80),
33 "address" character varying(80),
34 "phone" character varying(20),
35 "webdb_create" timestamp with time zone NOT NULL,
36 "webdb_lastchange" timestamp with time zone,
37 "is_published" boolean DEFAULT '1' NOT NULL,
38 "to_language" integer DEFAULT '0' NOT NULL,
39 "to_media" integer NOT NULL,
40 "to_comment_status" smallint,
42 "is_html" boolean DEFAULT '0' NOT NULL,
43 Constraint "comment_pkey" Primary Key ("id")
46 INSERT INTO "comment" (
85 relowner = (SELECT relowner FROM pg_class WHERE relname='comment_old'),
86 relacl = (SELECT relacl FROM pg_class WHERE relname='comment_old')
90 DROP TABLE "comment_old";
92 CREATE INDEX "comment_checksum_index" on "comment" using btree ( "checksum" "int4_ops" );
93 CREATE INDEX "idx_comment_to_media" on "comment" using btree ( "to_media" "int4_ops" );
94 create index idx_comment_webdb_create on comment(webdb_create);
95 CREATE INDEX "idx_comment_tomedia_ispublished" on "comment" using btree ( "to_media" "int4_ops", "is_published" "bool_ops" );
96 CREATE UNIQUE INDEX "idx_comment_id" on "comment" using btree ( "id" "int4_ops" );