--- /dev/null
+-- update script 2003-11-25 by Zapata
+-- * Removal of media (uploaded_media and content are now independently)
+-- * Clean up of content
+-- * Added a field for content-locking
+-- * Added a keywords field for content
+-- 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.
+
+DROP INDEX idx_content_webdb_create;
+DROP INDEX idx_othermedia_is_published_produced;
+DROP INDEX idx_othermedia_id;
+DROP INDEX idx_video_id;
+
+BEGIN TRANSACTION;
+
+----------------------
+-- CONTENT changes
+----------------------
+
+ALTER TABLE "content" RENAME TO "content_old";
+
+DROP INDEX "content_checksum_index";
+DROP INDEX "idx_content_to_article_type";
+DROP INDEX "idx_content_is_produced";
+DROP INDEX "idx_content_is_published__to_ar";
+DROP INDEX "idx_content_is_published__id";
+DROP INDEX "idx_content_is_pub__to_art__to_";
+DROP INDEX "idx_content_id";
+DROP INDEX "idx_content_is_published";
+
+CREATE TABLE "content" (
+ "id" integer DEFAULT nextval('media_id_seq'::text) NOT NULL,
+ "title" character varying(255),
+ "subtitle" character varying(255),
+ "edittitle" character varying(255),
+ "date" character(8) NOT NULL,
+ "creator" character varying(80),
+ "creator_main_url" character varying(255),
+ "creator_email" character varying(80),
+ "creator_address" character varying(80),
+ "creator_phone" character varying(80),
+ "description" text,
+ "comment" text,
+ "source" character varying(255),
+ "is_published" boolean DEFAULT '0' NOT NULL,
+ "is_produced" boolean DEFAULT '0' NOT NULL,
+ "to_publisher" integer NOT NULL,
+ "to_language" integer DEFAULT '0',
+ "to_rights" integer DEFAULT '0',
+ "webdb_create" timestamp with time zone NOT NULL,
+ "webdb_lastchange" timestamp with time zone,
+ "content_data" text,
+ "is_html" boolean DEFAULT '0' NOT NULL,
+ "to_article_type" smallint DEFAULT '0' NOT NULL,
+ "to_content" integer,
+ "keywords" text,
+ "to_locking_user" integer,
+ Constraint "content_pkey" Primary Key ("id")
+);
+
+
+CREATE INDEX "idx_content_to_article_type" on "content" using btree ( "to_article_type" "int2_ops" );
+CREATE INDEX "idx_content_is_produced" on "content" using btree ( "is_produced" "bool_ops" );
+CREATE INDEX "idx_content_is_published__to_ar" on "content" using btree ( "is_published" "bool_ops", "to_article_type" "int2_ops" );
+CREATE INDEX "idx_content_is_published__id" on "content" using btree ( "is_published" "bool_ops", "id" "int4_ops" );
+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" );
+CREATE UNIQUE INDEX "idx_content_id" on "content" using btree ( "id" "int4_ops" );
+CREATE INDEX "idx_content_is_published" on "content" using btree ( "is_published" "bool_ops" );
+
+INSERT INTO "content" (
+ "id",
+ "title",
+ "subtitle",
+ "edittitle",
+ "date",
+ "creator",
+ "creator_main_url",
+ "creator_email",
+ "creator_address",
+ "creator_phone",
+ "description",
+ "comment",
+ "source",
+ "is_published",
+ "is_produced",
+ "to_publisher",
+ "to_language",
+ "to_rights",
+ "webdb_create",
+ "webdb_lastchange",
+ "content_data",
+ "is_html",
+ "to_article_type",
+ "to_content",
+ "keywords")
+select
+ "id",
+ "title",
+ "subtitle",
+ "edittitle",
+ "date",
+ "creator",
+ "creator_main_url",
+ "creator_email",
+ "creator_address",
+ "creator_phone",
+ "description",
+ "comment",
+ "source",
+ "is_published",
+ "is_produced",
+ "to_publisher",
+ "to_language",
+ "to_rights",
+ "webdb_create",
+ "webdb_lastchange",
+ "content_data",
+ "is_html",
+ "to_article_type",
+ "to_content",
+ ''
+from content_old;
+
+UPDATE pg_class
+SET
+ relowner = (SELECT relowner FROM pg_class WHERE relname='content_old'),
+ relacl = (SELECT relacl FROM pg_class WHERE relname='content_old')
+WHERE
+ relname = 'content';
+
+DROP table content_old;
+
+--
+-- uploaded_media
+--
+
+ALTER TABLE "uploaded_media" RENAME TO "uploaded_media_old";
+DROP INDEX "idx_uploaded_media_id";
+DROP INDEX "idx_uploaded_media_is_published";
+
+CREATE TABLE "uploaded_media" (
+ "id" integer DEFAULT nextval('media_id_seq'::text) NOT NULL,
+ "title" character varying(255),
+ "subtitle" character varying(255),
+ "edittitle" character varying(255),
+ "date" character(8) NOT NULL,
+ "creator" character varying(80),
+ "creator_main_url" character varying(255),
+ "creator_email" character varying(80),
+ "creator_address" character varying(80),
+ "creator_phone" character varying(20),
+ "description" text,
+ "comment" text,
+ "source" character varying(255),
+ "publish_server" character varying(255),
+ "publish_path" character varying(255),
+ "is_published" boolean DEFAULT '0' NOT NULL,
+ "is_produced" boolean DEFAULT '0' NOT NULL,
+ "to_media_folder" integer DEFAULT '0' NOT NULL,
+ "to_media_type" smallint DEFAULT '0' NOT NULL,
+ "to_publisher" integer NOT NULL,
+ "to_language" integer DEFAULT '0',
+ "to_rights" integer DEFAULT '0',
+ "webdb_create" timestamp with time zone NOT NULL,
+ "webdb_lastchange" timestamp with time zone,
+ "icon_is_produced" boolean DEFAULT '0' NOT NULL,
+ "icon_path" character varying(255),
+ "size" integer,
+ Constraint "uploaded_media_pkey" Primary Key ("id")
+);
+
+CREATE UNIQUE INDEX "idx_uploaded_media_id" on "uploaded_media" using btree ( "id" "int4_ops" );
+CREATE UNIQUE INDEX "idx_uploaded_media_is_published" on "uploaded_media" using btree ( "id" "int4_ops", "is_published" "bool_ops" );
+
+--
+-- images
+--
+
+ALTER TABLE "images" RENAME TO "images_old";
+DROP INDEX "idx_images_is_published__icon_i";
+DROP INDEX "idx_images_id";
+
+
+CREATE TABLE "images" (
+ "image_data" oid,
+ "icon_data" oid,
+ "year" character varying(40),
+ "img_width" smallint,
+ "img_height" smallint,
+ "to_img_format" smallint DEFAULT '0' NOT NULL,
+ "to_img_layout" smallint DEFAULT '0' NOT NULL,
+ "to_img_type" smallint DEFAULT '0' NOT NULL,
+ "to_img_color" smallint DEFAULT '0' NOT NULL,
+ "icon_width" smallint,
+ "icon_height" smallint
+)
+INHERITS ("uploaded_media");
+
+CREATE INDEX "idx_images_is_published" on "images" using btree ( "is_published" "bool_ops", "icon_is_produced" "bool_ops" );
+CREATE UNIQUE INDEX "idx_images_id" on "images" using btree ( "id" "int4_ops" );
+
+INSERT INTO images(
+ "id",
+ "title",
+ "subtitle",
+ "edittitle",
+ "date",
+ "creator",
+ "creator_main_url",
+ "creator_email",
+ "creator_address",
+ "creator_phone",
+ "description",
+ "comment",
+ "source",
+ "publish_server",
+ "publish_path",
+ "is_published",
+ "is_produced",
+ "to_media_folder",
+ "to_media_type",
+ "to_publisher",
+ "to_language",
+ "to_rights",
+ "webdb_create",
+ "webdb_lastchange",
+ "icon_is_produced",
+ "icon_path",
+ "size",
+ "image_data",
+ "icon_data",
+ "year",
+ "img_width",
+ "img_height",
+ "to_img_format",
+ "to_img_layout",
+ "to_img_type",
+ "to_img_color",
+ "icon_width",
+ "icon_height"
+)
+SELECT
+ "id",
+ "title",
+ "subtitle",
+ "edittitle",
+ "date",
+ "creator",
+ "creator_main_url",
+ "creator_email",
+ "creator_address",
+ "creator_phone",
+ "description",
+ "comment",
+ "source",
+ "publish_server",
+ "publish_path",
+ "is_published",
+ "is_produced",
+ "to_media_folder",
+ "to_media_type",
+ "to_publisher",
+ "to_language",
+ "to_rights",
+ "webdb_create",
+ "webdb_lastchange",
+ "icon_is_produced",
+ "icon_path",
+ "size",
+ "image_data",
+ "icon_data",
+ "year",
+ "img_width",
+ "img_height",
+ "to_img_format",
+ "to_img_layout",
+ "to_img_type",
+ "to_img_color",
+ "icon_width",
+ "icon_height"
+FROM images_old;
+
+UPDATE pg_class
+SET
+ relowner = (SELECT relowner FROM pg_class WHERE relname='images_old'),
+ relacl = (SELECT relacl FROM pg_class WHERE relname='images_old')
+WHERE
+ relname = 'images';
+
+DROP table images_old;
+
+--
+-- audio
+--
+
+ALTER TABLE "audio" RENAME TO "audio_old";
+DROP INDEX "idx_audio_is_published_produced";
+DROP INDEX "idx_audio_id";
+
+CREATE TABLE "audio" (
+ "kbits" smallint
+)
+INHERITS ("uploaded_media");
+
+CREATE INDEX "idx_audio_is_published_produced" on "audio" using btree ( "is_published" "bool_ops", "is_produced" "bool_ops" );
+CREATE UNIQUE INDEX "idx_audio_id" on "audio" using btree ( "id" "int4_ops" );
+
+INSERT INTO audio(
+ "id",
+ "title",
+ "subtitle",
+ "edittitle",
+ "date",
+ "creator",
+ "creator_main_url",
+ "creator_email",
+ "creator_address",
+ "creator_phone",
+ "description",
+ "comment",
+ "source",
+ "publish_server",
+ "publish_path",
+ "is_published",
+ "is_produced",
+ "to_media_folder",
+ "to_media_type",
+ "to_publisher",
+ "to_language",
+ "to_rights",
+ "webdb_create",
+ "webdb_lastchange",
+ "icon_is_produced",
+ "icon_path",
+ "size",
+ "kbits"
+)
+SELECT
+ "id",
+ "title",
+ "subtitle",
+ "edittitle",
+ "date",
+ "creator",
+ "creator_main_url",
+ "creator_email",
+ "creator_address",
+ "creator_phone",
+ "description",
+ "comment",
+ "source",
+ "publish_server",
+ "publish_path",
+ "is_published",
+ "is_produced",
+ "to_media_folder",
+ "to_media_type",
+ "to_publisher",
+ "to_language",
+ "to_rights",
+ "webdb_create",
+ "webdb_lastchange",
+ "icon_is_produced",
+ "icon_path",
+ "size",
+ "kbits"
+FROM audio_old;
+
+UPDATE pg_class
+SET
+ relowner = (SELECT relowner FROM pg_class WHERE relname='audio_old'),
+ relacl = (SELECT relacl FROM pg_class WHERE relname='audio_old')
+WHERE
+ relname = 'audio';
+
+DROP table audio_old;
+
+--
+-- video
+--
+
+DROP INDEX "idx_video_is_published_produced";
+ALTER TABLE "video" RENAME TO "video_old";
+
+CREATE TABLE "video" (
+)
+INHERITS ("uploaded_media");
+
+CREATE INDEX "idx_video_is_published_produced" on "video" using btree ( "is_published" "bool_ops", "is_produced" "bool_ops" );
+
+INSERT INTO video(
+ "id",
+ "title",
+ "subtitle",
+ "edittitle",
+ "date",
+ "creator",
+ "creator_main_url",
+ "creator_email",
+ "creator_address",
+ "creator_phone",
+ "description",
+ "comment",
+ "source",
+ "publish_server",
+ "publish_path",
+ "is_published",
+ "is_produced",
+ "to_media_folder",
+ "to_media_type",
+ "to_publisher",
+ "to_language",
+ "to_rights",
+ "webdb_create",
+ "webdb_lastchange",
+ "icon_is_produced",
+ "icon_path",
+ "size"
+)
+SELECT
+ "id",
+ "title",
+ "subtitle",
+ "edittitle",
+ "date",
+ "creator",
+ "creator_main_url",
+ "creator_email",
+ "creator_address",
+ "creator_phone",
+ "description",
+ "comment",
+ "source",
+ "publish_server",
+ "publish_path",
+ "is_published",
+ "is_produced",
+ "to_media_folder",
+ "to_media_type",
+ "to_publisher",
+ "to_language",
+ "to_rights",
+ "webdb_create",
+ "webdb_lastchange",
+ "icon_is_produced",
+ "icon_path",
+ "size"
+FROM video_old;
+
+UPDATE pg_class
+SET
+ relowner = (SELECT relowner FROM pg_class WHERE relname='video_old'),
+ relacl = (SELECT relacl FROM pg_class WHERE relname='video_old')
+WHERE
+ relname = 'video';
+
+DROP table video_old;
+
+
+--
+-- other_media
+--
+
+ALTER TABLE "other_media" RENAME TO "other_media_old";
+
+CREATE TABLE "other_media" (
+)
+INHERITS ("uploaded_media");
+
+INSERT INTO other_media(
+ "id",
+ "title",
+ "subtitle",
+ "edittitle",
+ "date",
+ "creator",
+ "creator_main_url",
+ "creator_email",
+ "creator_address",
+ "creator_phone",
+ "description",
+ "comment",
+ "source",
+ "publish_server",
+ "publish_path",
+ "is_published",
+ "is_produced",
+ "to_media_folder",
+ "to_media_type",
+ "to_publisher",
+ "to_language",
+ "to_rights",
+ "webdb_create",
+ "webdb_lastchange",
+ "icon_is_produced",
+ "icon_path",
+ "size"
+)
+SELECT
+ "id",
+ "title",
+ "subtitle",
+ "edittitle",
+ "date",
+ "creator",
+ "creator_main_url",
+ "creator_email",
+ "creator_address",
+ "creator_phone",
+ "description",
+ "comment",
+ "source",
+ "publish_server",
+ "publish_path",
+ "is_published",
+ "is_produced",
+ "to_media_folder",
+ "to_media_type",
+ "to_publisher",
+ "to_language",
+ "to_rights",
+ "webdb_create",
+ "webdb_lastchange",
+ "icon_is_produced",
+ "icon_path",
+ "size"
+FROM other_media_old;
+
+UPDATE pg_class
+SET
+ relowner = (SELECT relowner FROM pg_class WHERE relname='other_media_old'),
+ relacl = (SELECT relacl FROM pg_class WHERE relname='other_media_old')
+WHERE
+ relname = 'other_media';
+
+DROP table other_media_old;
+
+DROP table uploaded_media_old;
+DROP table media;
+
+-- that's it!
+
+COMMIT TRANSACTION;
+
+CREATE INDEX idx_content_webdb_create on content(webdb_create);
+CREATE INDEX "idx_othermedia_is_published_produced" on "other_media" using btree ( "is_published" "bool_ops", "is_produced" "bool_ops" );
+CREATE UNIQUE INDEX "idx_othermedia_id" on "other_media" using btree ( "id" "int4_ops" );
+CREATE UNIQUE INDEX "idx_video_id" on "video" using btree ( "id" "int4_ops" );
+
+
projects / mir.git / blobdiff