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";
36 -- task 2: add sequenced and unique id's to article_type
38 CREATE SEQUENCE "article_type_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
40 ALTER TABLE "article_type" RENAME TO "article_type_old";
42 CREATE TABLE "article_type" (
43 "id" integer DEFAULT nextval('article_type_id_seq') NOT NULL,
44 "name" character varying(40) NOT NULL,
45 CONSTRAINT "article_type_pkey" PRIMARY KEY ("id")
48 INSERT INTO "article_type" ("id", "name")
50 FROM "article_type_old";
54 relowner = (SELECT relowner FROM pg_class WHERE relname='article_type_old'),
55 relacl = (SELECT relacl FROM pg_class WHERE relname='article_type_old')
57 relname = 'article_type' or relname= 'article_type_id_seq';
59 DROP TABLE "article_type_old";
62 -- task 3: add sequenced and unique id's to language
64 CREATE SEQUENCE "language_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
66 ALTER TABLE "language" RENAME TO "language_old";
67 DROP INDEX language_pkey;
69 CREATE TABLE "language" (
70 "id" integer DEFAULT nextval('language_id_seq') NOT NULL,
71 "name" character varying(40) NOT NULL,
72 "code" character varying(2) NOT NULL,
73 Constraint "language_pkey" Primary Key ("id")
76 INSERT INTO "language" ("id", "name", "code")
77 SELECT "id", "name", "code"
82 relowner = (SELECT relowner FROM pg_class WHERE relname='language_old'),
83 relacl = (SELECT relacl FROM pg_class WHERE relname='language_old')
85 relname = 'language' or relname='language_id_seq';
87 DROP TABLE "language_old";
90 -- task 4: add is_html to table comment
92 ALTER TABLE "comment" RENAME TO "comment_old";
93 DROP INDEX comment_pkey;
95 CREATE TABLE "comment" (
96 "id" integer DEFAULT nextval('comment_id_seq') NOT NULL,
97 "title" character varying(80) NOT NULL,
98 "creator" character varying(80) NOT NULL,
99 "description" text NOT NULL,
100 "main_url" character varying(255),
101 "email" character varying(80),
102 "address" character varying(80),
103 "phone" character varying(20),
104 "webdb_create" timestamp with time zone NOT NULL,
105 "is_published" boolean DEFAULT '1' NOT NULL,
106 "to_language" integer DEFAULT '0' NOT NULL,
107 "to_media" integer NOT NULL,
108 "to_comment_status" smallint,
110 "is_html" boolean DEFAULT '0' NOT NULL,
111 Constraint "comment_pkey" Primary Key ("id")
114 INSERT INTO "comment" (
151 relowner = (SELECT relowner FROM pg_class WHERE relname='comment_old'),
152 relacl = (SELECT relacl FROM pg_class WHERE relname='comment_old')
156 DROP TABLE "comment_old";