-- 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" );


