1 -- update script 2002-12-12 by Zapata
2 -- * introduces sequences and primary keys for comment_status, article_type and language
3 -- * adds is_html to comment
4 -- The first operation will fail 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: add sequenced and unique id's to comment_status
13 CREATE SEQUENCE "comment_status_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1;
15 ALTER TABLE "comment_status" RENAME TO comment_status_old;
17 CREATE TABLE "comment_status" (
18 "id" integer DEFAULT nextval('comment_status_id_seq') NOT NULL,
19 "name" character varying(40) NOT NULL,
20 CONSTRAINT "comment_status_pkey" PRIMARY KEY ("id")
23 INSERT INTO "comment_status" ("id", "name")
25 FROM "comment_status_old";
29 relowner = (SELECT relowner FROM pg_class WHERE relname='comment_status_old'),
30 relacl = (SELECT relacl FROM pg_class WHERE relname='comment_status_old')
32 relname = 'comment_status' or relname='comment_status_id_seq';
34 DROP TABLE "comment_status_old";
38 -- task 2: add sequenced and unique id's to article_type
40 CREATE SEQUENCE "article_type_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
42 ALTER TABLE "article_type" RENAME TO "article_type_old";
44 CREATE TABLE "article_type" (
45 "id" integer DEFAULT nextval('article_type_id_seq') NOT NULL,
46 "name" character varying(40) NOT NULL,
47 CONSTRAINT "article_type_pkey" PRIMARY KEY ("id")
50 INSERT INTO "article_type" ("id", "name")
52 FROM "article_type_old";
56 relowner = (SELECT relowner FROM pg_class WHERE relname='article_type_old'),
57 relacl = (SELECT relacl FROM pg_class WHERE relname='article_type_old')
59 relname = 'article_type' or relname= 'article_type_id_seq';
61 DROP TABLE "article_type_old";
64 -- task 3: add sequenced and unique id's to language
66 CREATE SEQUENCE "language_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
68 ALTER TABLE "language" RENAME TO "language_old";
69 DROP INDEX language_pkey;
71 CREATE TABLE "language" (
72 "id" integer DEFAULT nextval('language_id_seq') NOT NULL,
73 "name" character varying(40) NOT NULL,
74 "code" character varying(2) NOT NULL,
75 Constraint "language_pkey" Primary Key ("id")
78 INSERT INTO "language" ("id", "name", "code")
79 SELECT "id", "name", "code"
84 relowner = (SELECT relowner FROM pg_class WHERE relname='language_old'),
85 relacl = (SELECT relacl FROM pg_class WHERE relname='language_old')
87 relname = 'language' or relname='language_id_seq';
89 DROP TABLE "language_old";
92 -- task 4: add is_html to table comment
94 ALTER TABLE "comment" RENAME TO "comment_old";
95 DROP INDEX comment_pkey;
97 CREATE TABLE "comment" (
98 "id" integer DEFAULT nextval('comment_id_seq') NOT NULL,
99 "title" character varying(80) NOT NULL,
100 "creator" character varying(80) NOT NULL,
101 "description" text NOT NULL,
102 "main_url" character varying(255),
103 "email" character varying(80),
104 "address" character varying(80),
105 "phone" character varying(20),
106 "webdb_create" timestamp with time zone NOT NULL,
107 "is_published" boolean DEFAULT '1' NOT NULL,
108 "to_language" integer DEFAULT '0' NOT NULL,
109 "to_media" integer NOT NULL,
110 "to_comment_status" smallint,
112 "is_html" boolean DEFAULT '0' NOT NULL,
113 Constraint "comment_pkey" Primary Key ("id")
116 INSERT INTO "comment" (
153 relowner = (SELECT relowner FROM pg_class WHERE relname='comment_old'),
154 relacl = (SELECT relacl FROM pg_class WHERE relname='comment_old')
158 DROP TABLE "comment_old";
160 CREATE INDEX "comment_checksum_index" on "comment" using btree ( "checksum" "int4_ops" );
161 CREATE INDEX "idx_comment_to_media" on "comment" using btree ( "to_media" "int4_ops" );
162 create index idx_comment_webdb_create on comment(webdb_create);
163 CREATE INDEX "idx_comment_tomedia_ispublished" on "comment" using btree ( "to_media" "int4_ops", "is_published" "bool_ops" );
164 CREATE UNIQUE INDEX "idx_comment_id" on "comment" using btree ( "id" "int4_ops" );