From: zapata Date: Sun, 15 Dec 2002 00:41:34 +0000 (+0000) Subject: fixed a possible bug in the update script dealing with permissions on new X-Git-Tag: BEFORE_MERGE_1_1~397 X-Git-Url: http://erislabs.net/gitweb/?p=mir.git;a=commitdiff_plain;h=cd4705e40f791f0c2f9dacbef91c99b05c955414 fixed a possible bug in the update script dealing with permissions on new sequences. --- diff --git a/dbscripts/updates/update20021213.sql b/dbscripts/updates/update20021213.sql index ecf54a5c..ce003c43 100755 --- a/dbscripts/updates/update20021213.sql +++ b/dbscripts/updates/update20021213.sql @@ -1,97 +1,97 @@ --- update script 2002-12-12 by Zapata --- * introduces sequences and primary keys for comment_status, article_type and language --- * adds is_html to comment --- The first operation will fail the second time this script is run, +-- update script 2002-12-12 by Zapata +-- * introduces sequences and primary keys for comment_status, article_type and language +-- * adds is_html to comment +-- The first operation will fail the second time this script is run, -- so running this script when it isn't needed can't do any harm. -- --- IMPORTANT: after running this script, run the update_all_sequences script as well - -BEGIN TRANSACTION; - --- task 1: add sequenced and unique id's to comment_status - - CREATE SEQUENCE "comment_status_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1; - - ALTER TABLE "comment_status" RENAME TO comment_status_old; +-- IMPORTANT: after running this script, run the update_all_sequences script as well + +BEGIN TRANSACTION; + +-- task 1: add sequenced and unique id's to comment_status + + CREATE SEQUENCE "comment_status_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1; + + ALTER TABLE "comment_status" RENAME TO comment_status_old; CREATE TABLE "comment_status" ( "id" integer DEFAULT nextval('comment_status_id_seq') NOT NULL, "name" character varying(40) NOT NULL, CONSTRAINT "comment_status_pkey" PRIMARY KEY ("id") - ); - - INSERT INTO "comment_status" ("id", "name") - SELECT "id", "name" - FROM "comment_status_old"; - - UPDATE pg_class - SET - relowner = (SELECT relowner FROM pg_class WHERE relname='comment_status_old'), - relacl = (SELECT relacl FROM pg_class WHERE relname='comment_status_old') - WHERE - relname = 'comment_status'; - - DROP TABLE "comment_status_old"; - --- task 2: add sequenced and unique id's to article_type - - CREATE SEQUENCE "article_type_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; - - ALTER TABLE "article_type" RENAME TO "article_type_old"; + ); + + INSERT INTO "comment_status" ("id", "name") + SELECT "id", "name" + FROM "comment_status_old"; + + UPDATE pg_class + SET + relowner = (SELECT relowner FROM pg_class WHERE relname='comment_status_old'), + relacl = (SELECT relacl FROM pg_class WHERE relname='comment_status_old') + WHERE + relname = 'comment_status' or relname='comment_status_id_seq'; + + DROP TABLE "comment_status_old"; + +-- task 2: add sequenced and unique id's to article_type + + CREATE SEQUENCE "article_type_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; + + ALTER TABLE "article_type" RENAME TO "article_type_old"; CREATE TABLE "article_type" ( "id" integer DEFAULT nextval('article_type_id_seq') NOT NULL, "name" character varying(40) NOT NULL, CONSTRAINT "article_type_pkey" PRIMARY KEY ("id") - ); - - INSERT INTO "article_type" ("id", "name") - SELECT "id", "name" - FROM "article_type_old"; - - UPDATE pg_class - SET - relowner = (SELECT relowner FROM pg_class WHERE relname='article_type_old'), - relacl = (SELECT relacl FROM pg_class WHERE relname='article_type_old') - WHERE - relname = 'article_type'; - - DROP TABLE "article_type_old"; - - --- task 3: add sequenced and unique id's to language - - CREATE SEQUENCE "language_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; - - ALTER TABLE "language" RENAME TO "language_old"; - DROP INDEX language_pkey; - + ); + + INSERT INTO "article_type" ("id", "name") + SELECT "id", "name" + FROM "article_type_old"; + + UPDATE pg_class + SET + relowner = (SELECT relowner FROM pg_class WHERE relname='article_type_old'), + relacl = (SELECT relacl FROM pg_class WHERE relname='article_type_old') + WHERE + relname = 'article_type' or relname= 'article_type_id_seq'; + + DROP TABLE "article_type_old"; + + +-- task 3: add sequenced and unique id's to language + + CREATE SEQUENCE "language_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; + + ALTER TABLE "language" RENAME TO "language_old"; + DROP INDEX language_pkey; + CREATE TABLE "language" ( - "id" integer DEFAULT nextval('language_id_seq') NOT NULL, + "id" integer DEFAULT nextval('language_id_seq') NOT NULL, "name" character varying(40) NOT NULL, "code" character varying(2) NOT NULL, Constraint "language_pkey" Primary Key ("id") ); - - INSERT INTO "language" ("id", "name", "code") - SELECT "id", "name", "code" - FROM "language_old"; - - UPDATE pg_class - SET - relowner = (SELECT relowner FROM pg_class WHERE relname='language_old'), - relacl = (SELECT relacl FROM pg_class WHERE relname='language_old') - WHERE - relname = 'language'; - - DROP TABLE "language_old"; - - --- task 4: add is_html to table comment - - ALTER TABLE "comment" RENAME TO "comment_old"; - DROP INDEX comment_pkey; - + + INSERT INTO "language" ("id", "name", "code") + SELECT "id", "name", "code" + FROM "language_old"; + + UPDATE pg_class + SET + relowner = (SELECT relowner FROM pg_class WHERE relname='language_old'), + relacl = (SELECT relacl FROM pg_class WHERE relname='language_old') + WHERE + relname = 'language' or relname='language_id_seq'; + + DROP TABLE "language_old"; + + +-- task 4: add is_html to table comment + + ALTER TABLE "comment" RENAME TO "comment_old"; + DROP INDEX comment_pkey; + CREATE TABLE "comment" ( "id" integer DEFAULT nextval('comment_id_seq') NOT NULL, "title" character varying(80) NOT NULL, @@ -110,10 +110,10 @@ BEGIN TRANSACTION; "is_html" boolean DEFAULT '0' NOT NULL, Constraint "comment_pkey" Primary Key ("id") ); - - INSERT INTO "comment" ( - "id", - "title", + + INSERT INTO "comment" ( + "id", + "title", "creator", "description", "main_url", @@ -126,11 +126,11 @@ BEGIN TRANSACTION; "to_media", "to_comment_status", "checksum", - "is_html" - ) + "is_html" + ) SELECT - "id", - "title", + "id", + "title", "creator", "description", "main_url", @@ -142,19 +142,19 @@ BEGIN TRANSACTION; "to_language", "to_media", "to_comment_status", - "checksum", + "checksum", '0' - FROM "comment_old"; - - UPDATE pg_class - SET - relowner = (SELECT relowner FROM pg_class WHERE relname='comment_old'), - relacl = (SELECT relacl FROM pg_class WHERE relname='comment_old') - WHERE - relname = 'comment'; - - DROP TABLE "comment_old"; - --- that's it! - -COMMIT TRANSACTION; \ No newline at end of file + FROM "comment_old"; + + UPDATE pg_class + SET + relowner = (SELECT relowner FROM pg_class WHERE relname='comment_old'), + relacl = (SELECT relacl FROM pg_class WHERE relname='comment_old') + WHERE + relname = 'comment'; + + DROP TABLE "comment_old"; + +-- that's it! + +COMMIT TRANSACTION;