"name" character varying(30) NOT NULL
);
---
--- TOC Entry ID 18 (OID 20063)
---
--- Name: language Type: TABLE Owner: postgres
---
+-- language
+
+CREATE SEQUENCE "language_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;\r
+\r
CREATE TABLE "language" (
- "id" integer NOT NULL,
- "name" character varying(40) NOT NULL,
- "code" character varying(2) NOT NULL,
- Constraint "language_pkey" Primary Key ("id")
+ "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")
);
--
"topic_id" integer NOT NULL
);
---
--- TOC Entry ID 23 (OID 20172)
---
--- Name: article_type Type: TABLE Owner: postgres
---
+-- article type
+CREATE SEQUENCE "article_type_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;\r
+\r
CREATE TABLE "article_type" (
- "id" integer NOT NULL,
- "name" character varying(20) NOT NULL
-);
+ "id" integer DEFAULT nextval('article_type_id_seq') NOT NULL,
+ "name" character varying(40) NOT NULL,
+ CONSTRAINT "article_type_pkey" PRIMARY KEY ("id")
+);\r
--
-- TOC Entry ID 24 (OID 20183)
"to_media" integer NOT NULL,
"to_comment_status" smallint,
"checksum" integer,
+ "is_html" boolean DEFAULT '0' NOT NULL,
Constraint "comment_pkey" Primary Key ("id")
);
"webdb_create" timestamp with time zone NOT NULL
);
---
--- TOC Entry ID 32 (OID 20577)
---
--- Name: comment_status Type: TABLE Owner: postgres
---
+-- comment_status
+
+CREATE SEQUENCE "comment_status_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;\r
+\r
CREATE TABLE "comment_status" (
- "id" smallint NOT NULL,
- "name" character varying(40) NOT NULL
+ "id" integer DEFAULT nextval('comment_status_id_seq') NOT NULL,
+ "name" character varying(40) NOT NULL,
+ CONSTRAINT "comment_status_pkey" PRIMARY KEY ("id")
);
+\r
--
-- TOC Entry ID 33 (OID 20588)
--- /dev/null
+#!/bin/bash
+
+
+# experimental bash script to create a database
+#
+#
+
+usage () {
+ echo "usage: $0 dbname superuser username password"
+}
+
+if [ $# -ne 4 ]; then
+ usage
+ exit;
+fi;
+
+SCRIPT_FILE="/tmp/createmir.$$.sql"
+
+# database/user creation
+echo "CREATE DATABASE $1 WITH ENCODING='UNICODE';" > ${SCRIPT_FILE}
+echo "CREATE USER "$3" WITH PASSWORD '$4';" >> ${SCRIPT_FILE}
+echo "UPDATE pg_database SET datdba=(select usesysid from pg_user where usename='$3') where datname='$1';" >> ${SCRIPT_FILE}
+
+echo "\connect $1" >> ${SCRIPT_FILE}
+
+# create/populate scripts:
+echo "\i create_pg.sql" >> ${SCRIPT_FILE}
+for i in help*.sql ; do echo "\i ${i}" >> ${SCRIPT_FILE} ; done
+for i in populate*.sql ; do echo "\i ${i}" >> ${SCRIPT_FILE} ; done
+echo "\i update_all_sequences.sql" >> ${SCRIPT_FILE}
+
+# grant rights
+echo "grant all on img_format to $3;" >> ${SCRIPT_FILE}
+echo "grant all on img_layout to $3;" >> ${SCRIPT_FILE}\r
+echo "grant all on img_type to $3;" >> ${SCRIPT_FILE}
+echo "grant all on img_color to $3;" >> ${SCRIPT_FILE}\r
+echo "grant all on language to $3;" >> ${SCRIPT_FILE}
+echo "grant all on rights to $3;" >> ${SCRIPT_FILE}\r
+echo "grant all on feature to $3;" >> ${SCRIPT_FILE}
+echo "grant all on article_type to $3;" >> ${SCRIPT_FILE}\r
+echo "grant all on media to $3;" >> ${SCRIPT_FILE}
+echo "grant all on breaking to $3;" >> ${SCRIPT_FILE}\r
+echo "grant all on messages to $3;" >> ${SCRIPT_FILE}
+echo "grant all on comment_status to $3;" >> ${SCRIPT_FILE}\r
+echo "grant all on links_imcs to $3;" >> ${SCRIPT_FILE}
+echo "grant all on other_media to $3;" >> ${SCRIPT_FILE}\r
+echo "grant all on webdb_users to $3;" >> ${SCRIPT_FILE}
+echo "grant all on content_x_topic to $3;" >> ${SCRIPT_FILE}\r
+echo "grant all on topic to $3;" >> ${SCRIPT_FILE}
+echo "grant all on uploaded_media\r to $3;" >> ${SCRIPT_FILE}\r
+echo "grant all on images to $3;" >> ${SCRIPT_FILE}
+echo "grant all on content_x_media to $3;" >> ${SCRIPT_FILE}\r
+echo "grant all on audio to $3;" >> ${SCRIPT_FILE}
+echo "grant all on video to $3;" >> ${SCRIPT_FILE}\r
+echo "grant all on content to $3;" >> ${SCRIPT_FILE}
+echo "grant all on comment to $3;" >> ${SCRIPT_FILE}
+echo "grant all on media_folder to $3;" >> ${SCRIPT_FILE}
+
+
+# execute the script
+psql -U $2 -f ${SCRIPT_FILE} -d template1
+
+rm ${SCRIPT_FILE}
-UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" !~ '^pg_';
-- \connect - postgres
INSERT INTO "article_type" VALUES (0,'openposting');
INSERT INTO "article_type" VALUES (1,'newswire');
INSERT INTO "article_type" VALUES (2,'feature');
INSERT INTO "article_type" VALUES (3,'topicspecial');
INSERT INTO "article_type" VALUES (4,'startspecial');
-BEGIN TRANSACTION;
-CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint);
-INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class" C, "pg_trigger" T WHERE C."oid" = T."tgrelid" AND C."relname" !~ '^pg_' GROUP BY 1;
-UPDATE "pg_class" SET "reltriggers" = TMP."tmp_reltriggers" FROM "tr" TMP WHERE "pg_class"."relname" = TMP."tmp_relname";
-COMMIT TRANSACTION;
-UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" !~ '^pg_';
+
-- \connect - postgres
INSERT INTO "img_color" VALUES (0,'--');
INSERT INTO "img_color" VALUES (3,'color (16)');
INSERT INTO "img_color" VALUES (4,'color (256)');
INSERT INTO "img_color" VALUES (1,'b/w');
INSERT INTO "img_color" VALUES (2,'color');
-BEGIN TRANSACTION;
-CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint);
-INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class" C, "pg_trigger" T WHERE C."oid" = T."tgrelid" AND C."relname" !~ '^pg_' GROUP BY 1;
-UPDATE "pg_class" SET "reltriggers" = TMP."tmp_reltriggers" FROM "tr" TMP WHERE "pg_class"."relname" = TMP."tmp_relname";
-COMMIT TRANSACTION;
+
-UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" !~ '^pg_';
+
-- \connect - postgres
INSERT INTO "img_format" VALUES (0,'jpeg','jpg','image/jpg','jpeg nur vom original bearbeiten');
INSERT INTO "img_format" VALUES (1,'gif','gif','image/gif',NULL);
-BEGIN TRANSACTION;
-CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint);
-INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class" C, "pg_trigger" T WHERE C."oid" = T."tgrelid" AND C."relname" !~ '^pg_' GROUP BY 1;
-UPDATE "pg_class" SET "reltriggers" = TMP."tmp_reltriggers" FROM "tr" TMP WHERE "pg_class"."relname" = TMP."tmp_relname";
-COMMIT TRANSACTION;
+
-UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" !~ '^pg_';
+
-- \connect - postgres
INSERT INTO "img_layout" VALUES (0,'--');
INSERT INTO "img_layout" VALUES (1,'quer');
INSERT INTO "img_layout" VALUES (2,'hoch');
INSERT INTO "img_layout" VALUES (3,'quadrat');
-BEGIN TRANSACTION;
-CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint);
-INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class" C, "pg_trigger" T WHERE C."oid" = T."tgrelid" AND C."relname" !~ '^pg_' GROUP BY 1;
-UPDATE "pg_class" SET "reltriggers" = TMP."tmp_reltriggers" FROM "tr" TMP WHERE "pg_class"."relname" = TMP."tmp_relname";
-COMMIT TRANSACTION;
+
-UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" !~ '^pg_';
+
-- \connect - postgres
INSERT INTO "img_type" VALUES (0,'--');
INSERT INTO "img_type" VALUES (1,'Plakat');
INSERT INTO "img_type" VALUES (2,'Photo');
INSERT INTO "img_type" VALUES (3,'Grafik');
INSERT INTO "img_type" VALUES (4,'Kartenmaterial');
-BEGIN TRANSACTION;
-CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint);
-INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class" C, "pg_trigger" T WHERE C."oid" = T."tgrelid" AND C."relname" !~ '^pg_' GROUP BY 1;
-UPDATE "pg_class" SET "reltriggers" = TMP."tmp_reltriggers" FROM "tr" TMP WHERE "pg_class"."relname" = TMP."tmp_relname";
-COMMIT TRANSACTION;
+
-UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" !~ '^pg_';
+
-- \connect - postgres
INSERT INTO "language" VALUES (0,'deutsch','de');
INSERT INTO "language" VALUES (1,'english','en');
-BEGIN TRANSACTION;
-CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint);
-INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class" C, "pg_trigger" T WHERE C."oid" = T."tgrelid" AND C."relname" !~ '^pg_' GROUP BY 1;
-UPDATE "pg_class" SET "reltriggers" = TMP."tmp_reltriggers" FROM "tr" TMP WHERE "pg_class"."relname" = TMP."tmp_relname";
-COMMIT TRANSACTION;
-UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" !~ '^pg_';
-- \connect - postgres
INSERT INTO "rights" VALUES (0,'keine angabe',NULL);
INSERT INTO "rights" VALUES (1,'indymedia','rechte liegen bei indymedia');
INSERT INTO "rights" VALUES (2,'rechte bei autor/innen',NULL);
INSERT INTO "rights" VALUES (3,'frei ','keiner hat rechte dran');
-BEGIN TRANSACTION;
-CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint);
-INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class" C, "pg_trigger" T WHERE C."oid" = T."tgrelid" AND C."relname" !~ '^pg_' GROUP BY 1;
-UPDATE "pg_class" SET "reltriggers" = TMP."tmp_reltriggers" FROM "tr" TMP WHERE "pg_class"."relname" = TMP."tmp_relname";
-COMMIT TRANSACTION;
-UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" !~ '^pg_';
-- \connect - postgres
INSERT INTO "webdb_users" VALUES (1,'open','','f');
INSERT INTO "webdb_users" VALUES (2,'redaktion','indymedia','t');
INSERT INTO "webdb_users" VALUES (3,'tollendorf','indymedia','t');
INSERT INTO "webdb_users" VALUES (4,'admin','indymedia','t');
-BEGIN TRANSACTION;
-CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint);
-INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class" C, "pg_trigger" T WHERE C."oid" = T."tgrelid" AND C."relname" !~ '^pg_' GROUP BY 1;
-UPDATE "pg_class" SET "reltriggers" = TMP."tmp_reltriggers" FROM "tr" TMP WHERE "pg_class"."relname" = TMP."tmp_relname";
-COMMIT TRANSACTION;
INSERT INTO "media_folder" VALUES (7,'openposting','20020329','','openposting','media anonymously uploaded','2002-03-29 14:45:53+01',NULL);
-- Enable triggers
UPDATE pg_class SET reltriggers = (SELECT count(*) FROM pg_trigger where pg_class.oid = tgrelid) WHERE relname = 'media_folder';
-
---
--- TOC Entry ID 1 (OID 18921)
---
--- Name: media_folder_id_seq Type: SEQUENCE SET Owner:
---
-
-SELECT setval ('"media_folder_id_seq"', 1, 't');
-
-- \connect - de_indy
--- Disable triggers
-UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = 'media_type';
-
INSERT INTO "media_type" VALUES (4,'mp3','audio/mp3','Mp3','Audio',NULL);
INSERT INTO "media_type" VALUES (13,'mp3','audio/x-mp3','Mp3','Audio',NULL);
INSERT INTO "media_type" VALUES (14,'mp3','audio/x-mpeg','Mp3','Audio',NULL);
INSERT INTO "media_type" VALUES (18,'mp3','audio/mpeg','Mp3','Audio',NULL);
INSERT INTO "media_type" VALUES (19,'png','image/gif','ImagesPng','Images',NULL);
INSERT INTO "media_type" VALUES (20,'avi','video/avi','Video','Video',NULL);
--- Enable triggers
-UPDATE pg_class SET reltriggers = (SELECT count(*) FROM pg_trigger where pg_class.oid = tgrelid) WHERE relname = 'media_type';
-
---
--- TOC Entry ID 1 (OID 46923)
---
--- Name: media_type_id_seq Type: SEQUENCE SET Owner:
---
-
-SELECT setval ('"media_type_id_seq"', 1, 't');
-
--- /dev/null
+-- This script updates all sequences in a postgres mir database\r
+\r
+select setval('media_id_seq', (select max(id) from media));\r
+select setval('media_folder_id_seq', (select max(id) from media_folder));\r
+select setval('feature_id_seq', (select max(id) from feature));\r
+select setval('topic_id_seq', (select max(id) from topic));\r
+select setval('webdb_users_id_seq', (select max(id) from webdb_users));\r
+select setval('comment_id_seq', (select max(id) from comment));\r
+select setval('breaking_id_seq', (select max(id) from breaking));\r
+select setval('messages_id_seq', (select max(id) from messages));\r
+select setval('media_type_id_seq', (select max(id) from media_type));\r
+\r
+select setval('comment_status_id_seq',(select max(id) from comment_status));\r
+select setval('article_type_id_seq', (select max(id) from article_type));\r
+select setval('language_id_seq', (select max(id) from language));\r
+\r
+\r
+\r
+\r
--- /dev/null
+-- update script 2002-12-12 by Zapata\r
+-- * introduces sequences and primary keys for comment_status, article_type and language\r
+-- * adds is_html to comment\r
+-- The first operation will fail the second time this script is run,\r
+-- 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\r
+\r
+BEGIN TRANSACTION;\r
+\r
+-- task 1: add sequenced and unique id's to comment_status \r
+\r
+ CREATE SEQUENCE "comment_status_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1;\r
+\r
+ ALTER TABLE "comment_status" RENAME TO comment_status_old;\r
+
+ 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")
+ );\r
+\r
+ INSERT INTO "comment_status" ("id", "name")\r
+ SELECT "id", "name"\r
+ FROM "comment_status_old";\r
+\r
+ UPDATE pg_class\r
+ SET\r
+ relowner = (SELECT relowner FROM pg_class WHERE relname='comment_status_old'),\r
+ relacl = (SELECT relacl FROM pg_class WHERE relname='comment_status_old')\r
+ WHERE \r
+ relname = 'comment_status';\r
+ \r
+ DROP TABLE "comment_status_old";\r
+ \r
+-- task 2: add sequenced and unique id's to article_type\r
+\r
+ CREATE SEQUENCE "article_type_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;\r
+\r
+ ALTER TABLE "article_type" RENAME TO "article_type_old";\r
+
+ 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")
+ );\r
+\r
+ INSERT INTO "article_type" ("id", "name")\r
+ SELECT "id", "name"\r
+ FROM "article_type_old";\r
+\r
+ UPDATE pg_class\r
+ SET\r
+ relowner = (SELECT relowner FROM pg_class WHERE relname='article_type_old'),\r
+ relacl = (SELECT relacl FROM pg_class WHERE relname='article_type_old')\r
+ WHERE \r
+ relname = 'article_type';\r
+ \r
+ DROP TABLE "article_type_old";\r
+ \r
+\r
+-- task 3: add sequenced and unique id's to language\r
+\r
+ CREATE SEQUENCE "language_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;\r
+\r
+ ALTER TABLE "language" RENAME TO "language_old";\r
+ DROP INDEX language_pkey;\r
+ \r
+ CREATE TABLE "language" (
+ "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")
+ );
+\r
+ INSERT INTO "language" ("id", "name", "code")\r
+ SELECT "id", "name", "code"\r
+ FROM "language_old";\r
+\r
+ UPDATE pg_class\r
+ SET\r
+ relowner = (SELECT relowner FROM pg_class WHERE relname='language_old'),\r
+ relacl = (SELECT relacl FROM pg_class WHERE relname='language_old')\r
+ WHERE \r
+ relname = 'language';\r
+ \r
+ DROP TABLE "language_old";\r
+\r
+ \r
+-- task 4: add is_html to table comment\r
+ \r
+ ALTER TABLE "comment" RENAME TO "comment_old";\r
+ DROP INDEX comment_pkey;\r
+ \r
+ CREATE TABLE "comment" (
+ "id" integer DEFAULT nextval('comment_id_seq') NOT NULL,
+ "title" character varying(80) NOT NULL,
+ "creator" character varying(80) NOT NULL,
+ "description" text NOT NULL,
+ "main_url" character varying(255),
+ "email" character varying(80),
+ "address" character varying(80),
+ "phone" character varying(20),
+ "webdb_create" timestamp with time zone NOT NULL,
+ "is_published" boolean DEFAULT '1' NOT NULL,
+ "to_language" integer DEFAULT '0' NOT NULL,
+ "to_media" integer NOT NULL,
+ "to_comment_status" smallint,
+ "checksum" integer,
+ "is_html" boolean DEFAULT '0' NOT NULL,
+ Constraint "comment_pkey" Primary Key ("id")
+ );
+ \r
+ INSERT INTO "comment" (\r
+ "id", \r
+ "title", \r
+ "creator",
+ "description",
+ "main_url",
+ "email",
+ "address",
+ "phone",
+ "webdb_create",
+ "is_published",
+ "to_language",
+ "to_media",
+ "to_comment_status",
+ "checksum",
+ "is_html"\r
+ )\r
+ SELECT
+ "id", \r
+ "title", \r
+ "creator",
+ "description",
+ "main_url",
+ "email",
+ "address",
+ "phone",
+ "webdb_create",
+ "is_published",
+ "to_language",
+ "to_media",
+ "to_comment_status",
+ "checksum",\r
+ '0'
+ FROM "comment_old";\r
+\r
+ UPDATE pg_class\r
+ SET\r
+ relowner = (SELECT relowner FROM pg_class WHERE relname='comment_old'),\r
+ relacl = (SELECT relacl FROM pg_class WHERE relname='comment_old')\r
+ WHERE \r
+ relname = 'comment';\r
+ \r
+ DROP TABLE "comment_old";\r
+ \r
+-- that's it!\r
+ \r
+COMMIT TRANSACTION;
\ No newline at end of file
projects / mir.git / commitdiff