1 -- update script 2003-11-25 by Zapata
2 -- * Removal of media (uploaded_media and content are now independently)
3 -- * Clean up of content
4 -- * Added a field for content-locking
5 -- * Added a keywords field for content
6 -- This script will terminate with an error the second time it's run,
7 -- so running this script when it isn't needed is prevented from doing do any harm.
9 DROP INDEX idx_content_webdb_create;
10 DROP INDEX idx_othermedia_is_published_produced;
11 DROP INDEX idx_othermedia_id;
12 DROP INDEX idx_video_id;
16 ----------------------
18 ----------------------
20 ALTER TABLE "content" RENAME TO "content_old";
22 DROP INDEX "content_checksum_index";
23 DROP INDEX "idx_content_to_article_type";
24 DROP INDEX "idx_content_is_produced";
25 DROP INDEX "idx_content_is_published__to_ar";
26 DROP INDEX "idx_content_is_published__id";
27 DROP INDEX "idx_content_is_pub__to_art__to_";
28 DROP INDEX "idx_content_id";
29 DROP INDEX "idx_content_is_published";
31 CREATE TABLE "content" (
32 "id" integer DEFAULT nextval('media_id_seq'::text) NOT NULL,
33 "title" character varying(255),
34 "subtitle" character varying(255),
35 "edittitle" character varying(255),
36 "date" character(8) NOT NULL,
37 "creator" character varying(80),
38 "creator_main_url" character varying(255),
39 "creator_email" character varying(80),
40 "creator_address" character varying(80),
41 "creator_phone" character varying(80),
44 "source" character varying(255),
45 "is_published" boolean DEFAULT '0' NOT NULL,
46 "is_produced" boolean DEFAULT '0' NOT NULL,
47 "to_publisher" integer NOT NULL,
48 "to_language" integer DEFAULT '0',
49 "to_rights" integer DEFAULT '0',
50 "webdb_create" timestamp with time zone NOT NULL,
51 "webdb_lastchange" timestamp with time zone,
53 "is_html" boolean DEFAULT '0' NOT NULL,
54 "to_article_type" smallint DEFAULT '0' NOT NULL,
57 "to_locking_user" integer,
58 Constraint "content_pkey" Primary Key ("id")
62 CREATE INDEX "idx_content_to_article_type" on "content" using btree ( "to_article_type" "int2_ops" );
63 CREATE INDEX "idx_content_is_produced" on "content" using btree ( "is_produced" "bool_ops" );
64 CREATE INDEX "idx_content_is_published__to_ar" on "content" using btree ( "is_published" "bool_ops", "to_article_type" "int2_ops" );
65 CREATE INDEX "idx_content_is_published__id" on "content" using btree ( "is_published" "bool_ops", "id" "int4_ops" );
66 CREATE INDEX "idx_content_is_pub__to_art__to_" on "content" using btree ( "is_published" "bool_ops", "to_article_type" "int2_ops", "id" "int4_ops" );
67 CREATE UNIQUE INDEX "idx_content_id" on "content" using btree ( "id" "int4_ops" );
68 CREATE INDEX "idx_content_is_published" on "content" using btree ( "is_published" "bool_ops" );
70 INSERT INTO "content" (
126 relowner = (SELECT relowner FROM pg_class WHERE relname='content_old'),
127 relacl = (SELECT relacl FROM pg_class WHERE relname='content_old')
131 DROP table content_old;
137 ALTER TABLE "uploaded_media" RENAME TO "uploaded_media_old";
138 DROP INDEX "idx_uploaded_media_id";
139 DROP INDEX "idx_uploaded_media_is_published";
141 CREATE TABLE "uploaded_media" (
142 "id" integer DEFAULT nextval('media_id_seq'::text) NOT NULL,
143 "title" character varying(255),
144 "subtitle" character varying(255),
145 "edittitle" character varying(255),
146 "date" character(8) NOT NULL,
147 "creator" character varying(80),
148 "creator_main_url" character varying(255),
149 "creator_email" character varying(80),
150 "creator_address" character varying(80),
151 "creator_phone" character varying(20),
154 "source" character varying(255),
155 "publish_server" character varying(255),
156 "publish_path" character varying(255),
157 "is_published" boolean DEFAULT '0' NOT NULL,
158 "is_produced" boolean DEFAULT '0' NOT NULL,
159 "to_media_folder" integer DEFAULT '0' NOT NULL,
160 "to_media_type" smallint DEFAULT '0' NOT NULL,
161 "to_publisher" integer NOT NULL,
162 "to_language" integer DEFAULT '0',
163 "to_rights" integer DEFAULT '0',
164 "webdb_create" timestamp with time zone NOT NULL,
165 "webdb_lastchange" timestamp with time zone,
166 "icon_is_produced" boolean DEFAULT '0' NOT NULL,
167 "icon_path" character varying(255),
169 Constraint "uploaded_media_pkey" Primary Key ("id")
172 CREATE UNIQUE INDEX "idx_uploaded_media_id" on "uploaded_media" using btree ( "id" "int4_ops" );
173 CREATE UNIQUE INDEX "idx_uploaded_media_is_published" on "uploaded_media" using btree ( "id" "int4_ops", "is_published" "bool_ops" );
179 ALTER TABLE "images" RENAME TO "images_old";
180 DROP INDEX "idx_images_is_published__icon_i";
181 DROP INDEX "idx_images_id";
184 CREATE TABLE "images" (
187 "year" character varying(40),
188 "img_width" smallint,
189 "img_height" smallint,
190 "to_img_format" smallint DEFAULT '0' NOT NULL,
191 "to_img_layout" smallint DEFAULT '0' NOT NULL,
192 "to_img_type" smallint DEFAULT '0' NOT NULL,
193 "to_img_color" smallint DEFAULT '0' NOT NULL,
194 "icon_width" smallint,
195 "icon_height" smallint
197 INHERITS ("uploaded_media");
199 CREATE INDEX "idx_images_is_published" on "images" using btree ( "is_published" "bool_ops", "icon_is_produced" "bool_ops" );
200 CREATE UNIQUE INDEX "idx_images_id" on "images" using btree ( "id" "int4_ops" );
285 relowner = (SELECT relowner FROM pg_class WHERE relname='images_old'),
286 relacl = (SELECT relacl FROM pg_class WHERE relname='images_old')
290 DROP table images_old;
296 ALTER TABLE "audio" RENAME TO "audio_old";
297 DROP INDEX "idx_audio_is_published_produced";
298 DROP INDEX "idx_audio_id";
300 CREATE TABLE "audio" (
303 INHERITS ("uploaded_media");
305 CREATE INDEX "idx_audio_is_published_produced" on "audio" using btree ( "is_published" "bool_ops", "is_produced" "bool_ops" );
306 CREATE UNIQUE INDEX "idx_audio_id" on "audio" using btree ( "id" "int4_ops" );
371 relowner = (SELECT relowner FROM pg_class WHERE relname='audio_old'),
372 relacl = (SELECT relacl FROM pg_class WHERE relname='audio_old')
376 DROP table audio_old;
382 DROP INDEX "idx_video_is_published_produced";
383 ALTER TABLE "video" RENAME TO "video_old";
385 CREATE TABLE "video" (
387 INHERITS ("uploaded_media");
389 CREATE INDEX "idx_video_is_published_produced" on "video" using btree ( "is_published" "bool_ops", "is_produced" "bool_ops" );
452 relowner = (SELECT relowner FROM pg_class WHERE relname='video_old'),
453 relacl = (SELECT relacl FROM pg_class WHERE relname='video_old')
457 DROP table video_old;
464 ALTER TABLE "other_media" RENAME TO "other_media_old";
466 CREATE TABLE "other_media" (
468 INHERITS ("uploaded_media");
470 INSERT INTO other_media(
527 FROM other_media_old;
531 relowner = (SELECT relowner FROM pg_class WHERE relname='other_media_old'),
532 relacl = (SELECT relacl FROM pg_class WHERE relname='other_media_old')
534 relname = 'other_media';
536 DROP table other_media_old;
538 DROP table uploaded_media_old;
545 CREATE INDEX idx_content_webdb_create on content(webdb_create);
546 CREATE INDEX "idx_othermedia_is_published_produced" on "other_media" using btree ( "is_published" "bool_ops", "is_produced" "bool_ops" );
547 CREATE UNIQUE INDEX "idx_othermedia_id" on "other_media" using btree ( "id" "int4_ops" );
548 CREATE UNIQUE INDEX "idx_video_id" on "video" using btree ( "id" "int4_ops" );