dbscripts/updates/update20031125.sql

   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.
   8
   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;
  13
  14 BEGIN TRANSACTION;
  15
  16 ----------------------
  17 -- CONTENT changes
  18 ----------------------
  19
  20 ALTER TABLE "content" RENAME TO "content_old";
  21
  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";
  30
  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),
  42         "description" text,
  43         "comment" text,
  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,
  52         "content_data" text,
  53         "is_html" boolean DEFAULT '0' NOT NULL,
  54         "to_article_type" smallint DEFAULT '0' NOT NULL,
  55         "to_content" integer,
  56         "keywords" text,
  57         "to_locking_user" integer,
  58   Constraint "content_pkey" Primary Key ("id")
  59 );
  60
  61
  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" );
  69
  70 INSERT INTO "content" (
  71         "id",
  72         "title",
  73         "subtitle",
  74         "edittitle",
  75         "date",
  76         "creator",
  77         "creator_main_url",
  78         "creator_email",
  79         "creator_address",
  80         "creator_phone",
  81         "description",
  82         "comment",
  83         "source",
  84         "is_published",
  85         "is_produced",
  86         "to_publisher",
  87         "to_language",
  88         "to_rights",
  89         "webdb_create",
  90         "webdb_lastchange",
  91         "content_data",
  92         "is_html",
  93         "to_article_type",
  94         "to_content",
  95         "keywords")
  96 select
  97         "id",
  98         "title",
  99         "subtitle",
 100         "edittitle",
 101         "date",
 102         "creator",
 103         "creator_main_url",
 104         "creator_email",
 105         "creator_address",
 106         "creator_phone",
 107         "description",
 108         "comment",
 109         "source",
 110         "is_published",
 111         "is_produced",
 112         "to_publisher",
 113         "to_language",
 114         "to_rights",
 115         "webdb_create",
 116         "webdb_lastchange",
 117         "content_data",
 118         "is_html",
 119         "to_article_type",
 120         "to_content",
 121         ''
 122 from content_old;
 123
 124 UPDATE  pg_class
 125 SET
 126   relowner = (SELECT relowner FROM pg_class WHERE relname='content_old'),
 127   relacl =   (SELECT relacl FROM pg_class WHERE relname='content_old')
 128 WHERE
 129   relname = 'content';
 130
 131 DROP table content_old;
 132
 133 --
 134 -- uploaded_media
 135 --
 136
 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";
 140
 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),
 152         "description" text,
 153         "comment" text,
 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),
 168         "size" integer,
 169   Constraint "uploaded_media_pkey" Primary Key ("id")
 170 );
 171
 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" );
 174
 175 --
 176 -- images
 177 --
 178
 179 ALTER TABLE "images" RENAME TO "images_old";
 180 DROP INDEX "idx_images_is_published__icon_i";
 181 DROP INDEX "idx_images_id";
 182
 183
 184 CREATE TABLE "images" (
 185         "image_data" oid,
 186         "icon_data" oid,
 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
 196 )
 197 INHERITS ("uploaded_media");
 198
 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" );
 201
 202 INSERT INTO images(
 203         "id",
 204         "title",
 205         "subtitle",
 206         "edittitle",
 207         "date",
 208         "creator",
 209         "creator_main_url",
 210         "creator_email",
 211         "creator_address",
 212         "creator_phone",
 213         "description",
 214         "comment",
 215         "source",
 216         "publish_server",
 217         "publish_path",
 218         "is_published",
 219         "is_produced",
 220         "to_media_folder",
 221         "to_media_type",
 222         "to_publisher",
 223         "to_language",
 224         "to_rights",
 225         "webdb_create",
 226         "webdb_lastchange",
 227         "icon_is_produced",
 228         "icon_path",
 229         "size",
 230         "image_data",
 231         "icon_data",
 232         "year",
 233         "img_width",
 234         "img_height",
 235         "to_img_format",
 236         "to_img_layout",
 237         "to_img_type",
 238         "to_img_color",
 239         "icon_width",
 240         "icon_height"
 241 )
 242 SELECT
 243         "id",
 244         "title",
 245         "subtitle",
 246         "edittitle",
 247         "date",
 248         "creator",
 249         "creator_main_url",
 250         "creator_email",
 251         "creator_address",
 252         "creator_phone",
 253         "description",
 254         "comment",
 255         "source",
 256         "publish_server",
 257         "publish_path",
 258         "is_published",
 259         "is_produced",
 260         "to_media_folder",
 261         "to_media_type",
 262         "to_publisher",
 263         "to_language",
 264         "to_rights",
 265         "webdb_create",
 266         "webdb_lastchange",
 267         "icon_is_produced",
 268         "icon_path",
 269         "size",
 270         "image_data",
 271         "icon_data",
 272         "year",
 273         "img_width",
 274         "img_height",
 275         "to_img_format",
 276         "to_img_layout",
 277         "to_img_type",
 278         "to_img_color",
 279         "icon_width",
 280         "icon_height"
 281 FROM images_old;
 282
 283 UPDATE  pg_class
 284 SET
 285   relowner = (SELECT relowner FROM pg_class WHERE relname='images_old'),
 286   relacl =   (SELECT relacl FROM pg_class WHERE relname='images_old')
 287 WHERE
 288   relname = 'images';
 289
 290 DROP table images_old;
 291
 292 --
 293 -- audio
 294 --
 295
 296 ALTER TABLE "audio" RENAME TO "audio_old";
 297 DROP INDEX "idx_audio_is_published_produced";
 298 DROP INDEX "idx_audio_id";
 299
 300 CREATE TABLE "audio" (
 301   "kbits" smallint
 302 )
 303 INHERITS ("uploaded_media");
 304
 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" );
 307
 308 INSERT INTO audio(
 309         "id",
 310         "title",
 311         "subtitle",
 312         "edittitle",
 313         "date",
 314         "creator",
 315         "creator_main_url",
 316         "creator_email",
 317         "creator_address",
 318         "creator_phone",
 319         "description",
 320         "comment",
 321         "source",
 322         "publish_server",
 323         "publish_path",
 324         "is_published",
 325         "is_produced",
 326         "to_media_folder",
 327         "to_media_type",
 328         "to_publisher",
 329         "to_language",
 330         "to_rights",
 331         "webdb_create",
 332         "webdb_lastchange",
 333         "icon_is_produced",
 334         "icon_path",
 335         "size",
 336   "kbits"
 337 )
 338 SELECT
 339         "id",
 340         "title",
 341         "subtitle",
 342         "edittitle",
 343         "date",
 344         "creator",
 345         "creator_main_url",
 346         "creator_email",
 347         "creator_address",
 348         "creator_phone",
 349         "description",
 350         "comment",
 351         "source",
 352         "publish_server",
 353         "publish_path",
 354         "is_published",
 355         "is_produced",
 356         "to_media_folder",
 357         "to_media_type",
 358         "to_publisher",
 359         "to_language",
 360         "to_rights",
 361         "webdb_create",
 362         "webdb_lastchange",
 363         "icon_is_produced",
 364         "icon_path",
 365         "size",
 366         "kbits"
 367 FROM audio_old;
 368
 369 UPDATE  pg_class
 370 SET
 371   relowner = (SELECT relowner FROM pg_class WHERE relname='audio_old'),
 372   relacl =   (SELECT relacl FROM pg_class WHERE relname='audio_old')
 373 WHERE
 374   relname = 'audio';
 375
 376 DROP table audio_old;
 377
 378 --
 379 -- video
 380 --
 381
 382 DROP INDEX "idx_video_is_published_produced";
 383 ALTER TABLE "video" RENAME TO "video_old";
 384
 385 CREATE TABLE "video" (
 386 )
 387 INHERITS ("uploaded_media");
 388
 389 CREATE        INDEX "idx_video_is_published_produced" on "video" using btree ( "is_published" "bool_ops", "is_produced" "bool_ops" );
 390
 391 INSERT INTO video(
 392         "id",
 393         "title",
 394         "subtitle",
 395         "edittitle",
 396         "date",
 397         "creator",
 398         "creator_main_url",
 399         "creator_email",
 400         "creator_address",
 401         "creator_phone",
 402         "description",
 403         "comment",
 404         "source",
 405         "publish_server",
 406         "publish_path",
 407         "is_published",
 408         "is_produced",
 409         "to_media_folder",
 410         "to_media_type",
 411         "to_publisher",
 412         "to_language",
 413         "to_rights",
 414         "webdb_create",
 415         "webdb_lastchange",
 416         "icon_is_produced",
 417         "icon_path",
 418         "size"
 419 )
 420 SELECT
 421         "id",
 422         "title",
 423         "subtitle",
 424         "edittitle",
 425         "date",
 426         "creator",
 427         "creator_main_url",
 428         "creator_email",
 429         "creator_address",
 430         "creator_phone",
 431         "description",
 432         "comment",
 433         "source",
 434         "publish_server",
 435         "publish_path",
 436         "is_published",
 437         "is_produced",
 438         "to_media_folder",
 439         "to_media_type",
 440         "to_publisher",
 441         "to_language",
 442         "to_rights",
 443         "webdb_create",
 444         "webdb_lastchange",
 445         "icon_is_produced",
 446         "icon_path",
 447         "size"
 448 FROM video_old;
 449
 450 UPDATE  pg_class
 451 SET
 452   relowner = (SELECT relowner FROM pg_class WHERE relname='video_old'),
 453   relacl =   (SELECT relacl FROM pg_class WHERE relname='video_old')
 454 WHERE
 455   relname = 'video';
 456
 457 DROP table video_old;
 458
 459
 460 --
 461 -- other_media
 462 --
 463
 464 ALTER TABLE "other_media" RENAME TO "other_media_old";
 465
 466 CREATE TABLE "other_media" (
 467 )
 468 INHERITS ("uploaded_media");
 469
 470 INSERT INTO other_media(
 471         "id",
 472         "title",
 473         "subtitle",
 474         "edittitle",
 475         "date",
 476         "creator",
 477         "creator_main_url",
 478         "creator_email",
 479         "creator_address",
 480         "creator_phone",
 481         "description",
 482         "comment",
 483         "source",
 484         "publish_server",
 485         "publish_path",
 486         "is_published",
 487         "is_produced",
 488         "to_media_folder",
 489         "to_media_type",
 490         "to_publisher",
 491         "to_language",
 492         "to_rights",
 493         "webdb_create",
 494         "webdb_lastchange",
 495         "icon_is_produced",
 496         "icon_path",
 497         "size"
 498 )
 499 SELECT
 500         "id",
 501         "title",
 502         "subtitle",
 503         "edittitle",
 504         "date",
 505         "creator",
 506         "creator_main_url",
 507         "creator_email",
 508         "creator_address",
 509         "creator_phone",
 510         "description",
 511         "comment",
 512         "source",
 513         "publish_server",
 514         "publish_path",
 515         "is_published",
 516         "is_produced",
 517         "to_media_folder",
 518         "to_media_type",
 519         "to_publisher",
 520         "to_language",
 521         "to_rights",
 522         "webdb_create",
 523         "webdb_lastchange",
 524         "icon_is_produced",
 525         "icon_path",
 526         "size"
 527 FROM other_media_old;
 528
 529 UPDATE  pg_class
 530 SET
 531   relowner = (SELECT relowner FROM pg_class WHERE relname='other_media_old'),
 532   relacl =   (SELECT relacl FROM pg_class WHERE relname='other_media_old')
 533 WHERE
 534   relname = 'other_media';
 535
 536 DROP table other_media_old;
 537
 538 DROP table uploaded_media_old;
 539 DROP table media;
 540
 541 -- that's it!
 542
 543 COMMIT TRANSACTION;
 544
 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" );
 549
 550