From 14b4edb61d1ce0e34454d2dc46f8e263045c7aa0 Mon Sep 17 00:00:00 2001 From: zapata Date: Sat, 26 Apr 2003 23:20:24 +0000 Subject: [PATCH] enlarged some title fields + removed some junk fields (conversion script forthcoming) --- bundles/admin_de.properties | 5 +- bundles/admin_en.properties | 5 +- bundles/admin_es.properties | 5 +- bundles/admin_eu.properties | 5 +- bundles/admin_nl.properties | 5 +- bundles/admin_pt.properties | 5 +- bundles/admin_sv.properties | 5 +- dbscripts/create_pg.sql | 690 ++++++++----------------------- dbscripts/createmirdb.sh | 4 - dbscripts/update_all_sequences.sql | 2 - dbscripts/updates/README | 6 + dbscripts/updates/update20030409.sql | 2 +- templates/admin/FUNCTIONS_media.template | 1 - templates/admin/content.template | 3 +- 14 files changed, 210 insertions(+), 533 deletions(-) create mode 100755 dbscripts/updates/README diff --git a/bundles/admin_de.properties b/bundles/admin_de.properties index ef099c1b..fda0df09 100755 --- a/bundles/admin_de.properties +++ b/bundles/admin_de.properties @@ -1,6 +1,6 @@ ########## admin ########## # language: deutsch / german -# $Id: admin_de.properties,v 1.27 2003/04/09 02:06:06 zapata Exp $ +# $Id: admin_de.properties,v 1.28 2003/04/26 23:20:24 zapata Exp $ languagename=Deutsch @@ -164,7 +164,8 @@ content.language=Sprache content.articletype=Artikel Typ content.topic=Topic content.title=Titel -content.subtitle=Subtitel / Kontenttitek +content.subtitle=Subtitel +content.edittitle=Kontexttitel content.location=Ort content.creator=Autor content.creator.email=E-mail diff --git a/bundles/admin_en.properties b/bundles/admin_en.properties index f32e3e3b..caa631c1 100755 --- a/bundles/admin_en.properties +++ b/bundles/admin_en.properties @@ -1,6 +1,6 @@ ########## admin ########## # language: english -# $Id: admin_en.properties,v 1.44 2003/04/26 00:42:21 zapata Exp $ +# $Id: admin_en.properties,v 1.45 2003/04/26 23:20:24 zapata Exp $ languagename=English @@ -165,7 +165,8 @@ content.language=Language content.articletype=Article type content.topic=Topic content.title=Long title -content.subtitle=Sub title / context title +content.subtitle=Sub title +content.edittitle=Context title content.location=Location content.creator=Author content.creator.email=E-mail diff --git a/bundles/admin_es.properties b/bundles/admin_es.properties index 3f266780..e3832328 100755 --- a/bundles/admin_es.properties +++ b/bundles/admin_es.properties @@ -1,6 +1,6 @@ ########## admin ########## # language: spanish -# $Id: admin_es.properties,v 1.25 2003/04/09 17:58:30 zapata Exp $ +# $Id: admin_es.properties,v 1.26 2003/04/26 23:20:24 zapata Exp $ languagename = Castellano @@ -166,7 +166,8 @@ content.language = Idioma content.articletype = Tipo de art\u00edculo content.topic = Tema content.title = T\u00edtulo largo -content.subtitle = Subt\u00edtulo / t\u00edtulo contextualizador +content.subtitle = Subt\u00edtulo +content.edittitle = T\u00edtulo contextualizador content.location = Lugar de origen content.creator = Autor content.creator.email = Correo electr\u00f3nico diff --git a/bundles/admin_eu.properties b/bundles/admin_eu.properties index 7df359d7..b8011bd9 100755 --- a/bundles/admin_eu.properties +++ b/bundles/admin_eu.properties @@ -1,6 +1,6 @@ ########## admin ########## # language: euskera / basque -# $Id: admin_eu.properties,v 1.9 2003/04/09 02:06:06 zapata Exp $ +# $Id: admin_eu.properties,v 1.10 2003/04/26 23:20:24 zapata Exp $ languagename = Euskera @@ -198,7 +198,8 @@ content.language = content.articletype = content.topic = Gaia content.title = Izenburu luzea -content.subtitle = Azpi-izenburua/testuinguru izenburua +content.subtitle = Azpi-izenburua +content.edittitle = Testuinguru izenburua content.location = Jatorrizko lekua content.creator = Egilea content.creator.email = Posta elektronikoa diff --git a/bundles/admin_nl.properties b/bundles/admin_nl.properties index 5fe89089..924b2204 100755 --- a/bundles/admin_nl.properties +++ b/bundles/admin_nl.properties @@ -1,6 +1,6 @@ ########## admin ########## # language: dutch -# $Id: admin_nl.properties,v 1.5 2003/04/09 02:06:06 zapata Exp $ +# $Id: admin_nl.properties,v 1.6 2003/04/26 23:20:24 zapata Exp $ languagename = Nederlands @@ -165,7 +165,8 @@ content.language = Taal content.articletype = Artikel soort content.topic = Onderwerp content.title = Titel -content.subtitle = Onder titel / Context titel +content.subtitle = Onder titel +content.edittitle = Context titel content.location = Locatie content.creator = Auteur content.creator.email = E-mail diff --git a/bundles/admin_pt.properties b/bundles/admin_pt.properties index e9e43bf6..7f49e29d 100755 --- a/bundles/admin_pt.properties +++ b/bundles/admin_pt.properties @@ -1,6 +1,6 @@ ########## admin ########## # language: portuguese -# $Id: admin_pt.properties,v 1.4 2003/04/09 02:06:06 zapata Exp $ +# $Id: admin_pt.properties,v 1.5 2003/04/26 23:20:24 zapata Exp $ languagename=Português @@ -120,7 +120,8 @@ content.htmltitle=Artigo content.owner=Dono content.topic=Assunto content.title=Título longo -content.subtitle=Sub-título / conteúdo do título +content.subtitle=Sub-título +content.edittitle = Conteúdo do título content.location=Localização content.creator=Autor(a) content.creator.email=E-mail diff --git a/bundles/admin_sv.properties b/bundles/admin_sv.properties index 63b97daf..d49ca7e1 100755 --- a/bundles/admin_sv.properties +++ b/bundles/admin_sv.properties @@ -1,6 +1,6 @@ ########## admin ########## # language: svenska -# $Id: admin_sv.properties,v 1.5 2003/04/09 02:06:06 zapata Exp $ +# $Id: admin_sv.properties,v 1.6 2003/04/26 23:20:24 zapata Exp $ languagename=Svenska @@ -120,7 +120,8 @@ content.htmltitle=Artikel content.owner=ägare content.topic=ämne content.title=Rubrik -content.subtitle=Underrubrik / sammanfattning +content.subtitle=Underrubrik +content.edittitle=Sammanfattning content.location=Plats content.creator=Författare content.creator.email=E-mail diff --git a/dbscripts/create_pg.sql b/dbscripts/create_pg.sql index 1e331c33..f10b4a66 100755 --- a/dbscripts/create_pg.sql +++ b/dbscripts/create_pg.sql @@ -1,84 +1,9 @@ -- --- Selected TOC Entries: --- --- --- TOC Entry ID 2 (OID 19796) --- --- Name: media_id_seq Type: SEQUENCE Owner: postgres --- - -CREATE SEQUENCE "media_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; - --- --- TOC Entry ID 3 (OID 19815) --- --- Name: media_folder_id_seq Type: SEQUENCE Owner: postgres +-- media_folder -- CREATE SEQUENCE "media_folder_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; --- --- TOC Entry ID 4 (OID 19834) --- --- Name: feature_id_seq Type: SEQUENCE Owner: postgres --- - -CREATE SEQUENCE "feature_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; - --- --- TOC Entry ID 5 (OID 19853) --- --- Name: topic_id_seq Type: SEQUENCE Owner: postgres --- - -CREATE SEQUENCE "topic_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; - --- --- TOC Entry ID 6 (OID 19872) --- --- Name: webdb_users_id_seq Type: SEQUENCE Owner: postgres --- - -CREATE SEQUENCE "webdb_users_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; - --- --- TOC Entry ID 7 (OID 19891) --- --- Name: comment_id_seq Type: SEQUENCE Owner: postgres --- - -CREATE SEQUENCE "comment_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; - --- --- TOC Entry ID 8 (OID 19910) --- --- Name: breaking_id_seq Type: SEQUENCE Owner: postgres --- - -CREATE SEQUENCE "breaking_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; - --- --- TOC Entry ID 9 (OID 19929) --- --- Name: messages_id_seq Type: SEQUENCE Owner: postgres --- - -CREATE SEQUENCE "messages_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; - --- --- TOC Entry ID 10 (OID 19948) --- --- Name: media_type_id_seq Type: SEQUENCE Owner: postgres --- - -CREATE SEQUENCE "media_type_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; - --- --- TOC Entry ID 12 (OID 19967) --- --- Name: media_folder Type: TABLE Owner: postgres --- - CREATE TABLE "media_folder" ( "id" integer DEFAULT nextval('media_folder_id_seq'::text) NOT NULL, "name" character varying(255) NOT NULL, @@ -91,11 +16,11 @@ CREATE TABLE "media_folder" ( ); -- --- TOC Entry ID 13 (OID 20000) --- --- Name: media_type Type: TABLE Owner: postgres +-- media_type -- +CREATE SEQUENCE "media_type_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; + CREATE TABLE "media_type" ( "id" integer DEFAULT nextval('media_type_id_seq'::text) NOT NULL, "name" character varying(80) NOT NULL, @@ -106,68 +31,34 @@ CREATE TABLE "media_type" ( ); -- --- TOC Entry ID 14 (OID 20016) --- --- Name: img_format Type: TABLE Owner: postgres +-- language -- -CREATE TABLE "img_format" ( - "id" smallint NOT NULL, - "name" character varying(20) NOT NULL, - "extension" character varying(10) NOT NULL, - "mimetype" character varying(40) NOT NULL, - "commment" character varying(255) -); - --- --- TOC Entry ID 15 (OID 20030) --- --- Name: img_layout Type: TABLE Owner: postgres --- +CREATE SEQUENCE "language_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; -CREATE TABLE "img_layout" ( - "id" smallint NOT NULL, - "name" character varying(20) NOT NULL +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") ); -- --- TOC Entry ID 16 (OID 20041) --- --- Name: img_type Type: TABLE Owner: postgres +-- comment_status -- -CREATE TABLE "img_type" ( - "id" smallint NOT NULL, - "name" character varying(30) NOT NULL -); - --- --- TOC Entry ID 17 (OID 20052) --- --- Name: img_color Type: TABLE Owner: postgres --- +CREATE SEQUENCE "comment_status_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; -CREATE TABLE "img_color" ( - "id" smallint NOT NULL, - "name" character varying(30) NOT NULL +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") ); --- language - -CREATE SEQUENCE "language_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; - -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") -); -- --- TOC Entry ID 19 (OID 20078) --- --- Name: rights Type: TABLE Owner: postgres +-- rights -- CREATE TABLE "rights" ( @@ -178,26 +69,10 @@ CREATE TABLE "rights" ( ); -- --- TOC Entry ID 20 (OID 20108) --- --- Name: feature Type: TABLE Owner: postgres +-- webdb_users -- -CREATE TABLE "feature" ( - "id" integer DEFAULT nextval('feature_id_seq'::text) NOT NULL, - "title" character varying(80) NOT NULL, - "description" text, - "filename" character varying(20) NOT NULL, - "main_url" character varying(255), - "is_published" boolean DEFAULT '0' NOT NULL, - Constraint "feature_pkey" Primary Key ("id") -); - --- --- TOC Entry ID 21 (OID 20143) --- --- Name: webdb_users Type: TABLE Owner: postgres --- +CREATE SEQUENCE "webdb_users_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; CREATE TABLE "webdb_users" ( "id" integer DEFAULT nextval('webdb_users_id_seq'::text) NOT NULL, @@ -207,18 +82,13 @@ CREATE TABLE "webdb_users" ( Constraint "webdb_users_pkey" Primary Key ("id") ); --- --- TOC Entry ID 22 (OID 20161) --- --- Name: content_x_topic Type: TABLE Owner: postgres --- +CREATE INDEX "idx_webdb_user_log_pas_is_admin" on "webdb_users" using btree ( "login" "varchar_ops", "password" "varchar_ops", "is_admin" "bool_ops" ); -CREATE TABLE "content_x_topic" ( - "content_id" integer NOT NULL, - "topic_id" integer NOT NULL -); +CREATE INDEX "idx_webdb_user_log_pas" on "webdb_users" using btree ( "login" "varchar_ops", "password" "varchar_ops" ); +-- -- article type +-- CREATE SEQUENCE "article_type_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; @@ -229,11 +99,11 @@ CREATE TABLE "article_type" ( ); -- --- TOC Entry ID 24 (OID 20183) --- --- Name: topic Type: TABLE Owner: postgres +-- topic -- +CREATE SEQUENCE "topic_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; + CREATE TABLE "topic" ( "id" integer DEFAULT nextval('topic_id_seq'::text) NOT NULL, "parent_id" integer DEFAULT '0' NOT NULL, @@ -245,43 +115,74 @@ CREATE TABLE "topic" ( Constraint "topic_pkey" Primary Key ("id") ); +CREATE INDEX "idx_topic_title" on "topic" using btree ( "title" "varchar_ops" ); +CREATE UNIQUE INDEX "idx_topic_id" on "topic" using btree ( "id" "int4_ops" ); + + +-- +-- content_x_topics -- --- TOC Entry ID 25 (OID 20219) + +CREATE TABLE "content_x_topic" ( + "content_id" integer NOT NULL, + "topic_id" integer NOT NULL +); + +CREATE UNIQUE INDEX "idx_content" on "content_x_topic" using btree ( "content_id" "int4_ops", "topic_id" "int4_ops" ); +CREATE UNIQUE INDEX "idx_topic" on "content_x_topic" using btree ( "topic_id" "int4_ops", "content_id" "int4_ops" ); + -- --- Name: comment Type: TABLE Owner: postgres +-- comment -- +CREATE SEQUENCE "comment_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; CREATE TABLE "comment" ( - "id" integer DEFAULT nextval('comment_id_seq'::text) 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, - "webdb_lastchange" timestamp with time zone, - "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") + "id" integer DEFAULT nextval('comment_id_seq'::text) 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, + "webdb_lastchange" timestamp with time zone, + "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") ); +CREATE INDEX "comment_checksum_index" on "comment" using btree ( "checksum" "int4_ops" ); +CREATE INDEX "idx_comment_to_media" on "comment" using btree ( "to_media" "int4_ops" ); +CREATE INDEX idx_comment_webdb_create on comment(webdb_create); +CREATE INDEX "idx_comment_tomedia_ispublished" on "comment" using btree ( "to_media" "int4_ops", "is_published" "bool_ops" ); +CREATE UNIQUE INDEX "idx_comment_id" on "comment" using btree ( "id" "int4_ops" ); + + + + CREATE TABLE "comment_x_media" ( + "comment_id" integer, + "media_id" integer + ); + + CREATE UNIQUE INDEX idx_comment_media on comment_x_media (comment_id, media_id); + CREATE UNIQUE INDEX idx_media_comment on comment_x_media (media_id, comment_id); + + -- --- TOC Entry ID 26 (OID 20266) --- --- Name: media Type: TABLE Owner: postgres +-- media -- +CREATE SEQUENCE "media_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; CREATE TABLE "media" ( "id" integer DEFAULT nextval('media_id_seq'::text) NOT NULL, - "title" character varying(80) NOT NULL, - "subtitle" character varying(30), - "edittitle" character varying(30), + "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), @@ -289,7 +190,6 @@ CREATE TABLE "media" ( "creator_address" character varying(80), "creator_phone" character varying(20), "description" text, - "keywords" text, "comment" text, "source" character varying(255), "publish_date" timestamp with time zone, @@ -309,9 +209,7 @@ CREATE TABLE "media" ( ); -- --- TOC Entry ID 27 (OID 20326) --- --- Name: uploaded_media Type: TABLE Owner: postgres +-- uploaded_media -- CREATE TABLE "uploaded_media" ( @@ -321,10 +219,11 @@ CREATE TABLE "uploaded_media" ( ) INHERITS ("media"); +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" ); + -- --- TOC Entry ID 28 (OID 20392) --- --- Name: images Type: TABLE Owner: postgres +-- images -- CREATE TABLE "images" ( @@ -342,366 +241,137 @@ CREATE TABLE "images" ( ) INHERITS ("uploaded_media"); --- --- TOC Entry ID 29 (OID 20474) --- --- Name: content Type: TABLE Owner: postgres --- - -CREATE TABLE "content" ( - "content_data" text, - "link_url" character varying(255), - "is_html" boolean DEFAULT '0' NOT NULL, - "is_stored" boolean DEFAULT '0' NOT NULL, - "to_article_type" smallint DEFAULT '0' NOT NULL, - "to_content" integer, - "checksum" integer -) -INHERITS ("media"); - --- --- TOC Entry ID 30 (OID 20549) --- --- Name: breaking Type: TABLE Owner: postgres --- - -CREATE TABLE "breaking" ( - "id" integer DEFAULT nextval('breaking_id_seq'::text) NOT NULL, - "text" character varying(255) NOT NULL, - "webdb_create" timestamp with time zone NOT NULL -); - --- --- TOC Entry ID 31 (OID 20562) --- --- Name: messages Type: TABLE Owner: postgres --- - -CREATE TABLE "messages" ( - "id" integer DEFAULT nextval('messages_id_seq'::text) NOT NULL, - "title" character varying(30), - "description" character varying(255) NOT NULL, - "creator" character varying(30) NOT NULL, - "webdb_create" timestamp with time zone NOT NULL -); - - --- comment_status - -CREATE SEQUENCE "comment_status_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; - -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") -); - - --- --- TOC Entry ID 33 (OID 20588) --- --- Name: content_x_media Type: TABLE Owner: postgres --- - -CREATE TABLE "content_x_media" ( - "content_id" integer, - "media_id" integer -); - --- --- TOC Entry ID 11 (OID 20599) --- --- Name: links_imcs_id_seq Type: SEQUENCE Owner: postgres --- - -CREATE SEQUENCE "links_imcs_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; - --- --- TOC Entry ID 34 (OID 20618) --- --- Name: links_imcs Type: TABLE Owner: postgres --- - -CREATE TABLE "links_imcs" ( - "id" integer DEFAULT nextval('links_imcs_id_seq'::text) NOT NULL, - "to_parent_id" integer, - "title" character varying(80) NOT NULL, - "url" character varying(255) NOT NULL, - "sortpriority" integer DEFAULT '1', - "to_language" integer DEFAULT '0' NOT NULL, - Constraint "links_imcs_pkey" Primary Key ("id") -); +CREATE INDEX "idx_images_is_published__icon_i" 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" ); --- --- TOC Entry ID 35 (OID 37215) --- --- Name: audio Type: TABLE Owner: postgres + -- + -- img_format + -- + + CREATE TABLE "img_format" ( + "id" smallint NOT NULL, + "name" character varying(20) NOT NULL, + "extension" character varying(10) NOT NULL, + "mimetype" character varying(40) NOT NULL, + "commment" character varying(255) + ); + + -- + -- img_layout + -- + + CREATE TABLE "img_layout" ( + "id" smallint NOT NULL, + "name" character varying(20) NOT NULL + ); + + -- + -- img_type + -- + + CREATE TABLE "img_type" ( + "id" smallint NOT NULL, + "name" character varying(30) NOT NULL + ); + + -- + -- img_color + -- + + CREATE TABLE "img_color" ( + "id" smallint NOT NULL, + "name" character varying(30) NOT NULL + ); + + +-- +-- audio -- CREATE TABLE "audio" ( - "kbits" smallint + "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" ); + -- --- TOC Entry ID 36 (OID 37284) --- --- Name: video Type: TABLE Owner: postgres +-- video -- 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" ); +CREATE UNIQUE INDEX "idx_video_id" on "video" using btree ( "id" "int4_ops" ); + -- --- TOC Entry ID 37 (OID 45396) --- --- Name: other_media Type: TABLE Owner: postgres +-- other_media -- CREATE TABLE "other_media" ( - ) INHERITS ("uploaded_media"); -- --- TOC Entry ID 40 (OID 20143) --- --- Name: "idx_webdb_user_log_pas_is_admin" Type: INDEX Owner: postgres +-- content -- -CREATE INDEX "idx_webdb_user_log_pas_is_admin" on "webdb_users" using btree ( "login" "varchar_ops", "password" "varchar_ops", "is_admin" "bool_ops" ); - --- --- TOC Entry ID 41 (OID 20143) --- --- Name: "idx_webdb_user_log_pas" Type: INDEX Owner: postgres --- - -CREATE INDEX "idx_webdb_user_log_pas" on "webdb_users" using btree ( "login" "varchar_ops", "password" "varchar_ops" ); - --- --- TOC Entry ID 42 (OID 20161) --- --- Name: "idx_content" Type: INDEX Owner: postgres --- - -CREATE UNIQUE INDEX "idx_content" on "content_x_topic" using btree ( "content_id" "int4_ops", "topic_id" "int4_ops" ); - --- --- TOC Entry ID 43 (OID 20161) --- --- Name: "idx_topic" Type: INDEX Owner: postgres --- - -CREATE UNIQUE INDEX "idx_topic" on "content_x_topic" using btree ( "topic_id" "int4_ops", "content_id" "int4_ops" ); - --- --- TOC Entry ID 44 (OID 20183) --- --- Name: "idx_topic_title" Type: INDEX Owner: postgres --- - -CREATE INDEX "idx_topic_title" on "topic" using btree ( "title" "varchar_ops" ); - --- --- TOC Entry ID 45 (OID 20183) --- --- Name: "idx_topic_id" Type: INDEX Owner: postgres --- - -CREATE UNIQUE INDEX "idx_topic_id" on "topic" using btree ( "id" "int4_ops" ); - --- --- TOC Entry ID 38 (OID 20219) --- --- Name: "comment_checksum_index" Type: INDEX Owner: postgres --- - -CREATE INDEX "comment_checksum_index" on "comment" using btree ( "checksum" "int4_ops" ); - --- --- TOC Entry ID 46 (OID 20219) --- --- Name: "idx_comment_to_media" Type: INDEX Owner: postgres --- - -CREATE INDEX "idx_comment_to_media" on "comment" using btree ( "to_media" "int4_ops" ); - - -create index idx_comment_webdb_create on comment(webdb_create); - - - --- - --- --- TOC Entry ID 59 (OID 20326) --- --- Name: "idx_uploaded_media_id" Type: INDEX Owner: postgres --- - -CREATE UNIQUE INDEX "idx_uploaded_media_id" on "uploaded_media" using btree ( "id" "int4_ops" ); - --- --- TOC Entry ID 60 (OID 20326) --- --- Name: "idx_uploaded_media_is_published" Type: INDEX Owner: postgres --- - -CREATE UNIQUE INDEX "idx_uploaded_media_is_published" on "uploaded_media" using btree ( "id" "int4_ops", "is_published" "bool_ops" ); - --- --- TOC Entry ID 47 (OID 20392) --- --- Name: "idx_images_is_published__icon_i" Type: INDEX Owner: postgres --- - -CREATE INDEX "idx_images_is_published__icon_i" on "images" using btree ( "is_published" "bool_ops", "icon_is_produced" "bool_ops" ); - --- --- TOC Entry ID 48 (OID 20392) --- --- Name: "idx_images_id" Type: INDEX Owner: postgres --- - -CREATE UNIQUE INDEX "idx_images_id" on "images" using btree ( "id" "int4_ops" ); - --- --- TOC Entry ID 39 (OID 20474) --- --- Name: "content_checksum_index" Type: INDEX Owner: postgres --- - -CREATE INDEX "content_checksum_index" on "content" using btree ( "checksum" "int4_ops" ); - --- --- TOC Entry ID 49 (OID 20474) --- --- Name: "idx_content_to_article_type" Type: INDEX Owner: postgres --- - -CREATE INDEX "idx_content_to_article_type" on "content" using btree ( "to_article_type" "int2_ops" ); - --- --- TOC Entry ID 50 (OID 20474) --- --- Name: "idx_content_is_produced" Type: INDEX Owner: postgres --- - -CREATE INDEX "idx_content_is_produced" on "content" using btree ( "is_produced" "bool_ops" ); - --- --- TOC Entry ID 51 (OID 20474) --- --- Name: "idx_content_is_published__to_ar" Type: INDEX Owner: postgres --- - -CREATE INDEX "idx_content_is_published__to_ar" on "content" using btree ( "is_published" "bool_ops", "to_article_type" "int2_ops" ); - --- --- TOC Entry ID 52 (OID 20474) --- --- Name: "idx_content_is_stored" Type: INDEX Owner: postgres --- - -CREATE INDEX "idx_content_is_stored" on "content" using btree ( "is_stored" "bool_ops" ); - --- --- TOC Entry ID 53 (OID 20474) --- --- Name: "idx_content_is_published__id" Type: INDEX Owner: postgres --- - -CREATE INDEX "idx_content_is_published__id" on "content" using btree ( "is_published" "bool_ops", "id" "int4_ops" ); - --- --- TOC Entry ID 54 (OID 20474) --- --- Name: "idx_content_is_pub__to_art__to_" Type: INDEX Owner: postgres --- - -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" ); - --- --- TOC Entry ID 55 (OID 20474) --- --- Name: "idx_content_id" Type: INDEX Owner: postgres --- +CREATE TABLE "content" ( + "content_data" text, + "is_html" boolean DEFAULT '0' NOT NULL, + "to_article_type" smallint DEFAULT '0' NOT NULL, + "to_content" integer, + "checksum" integer +) +INHERITS ("media"); +CREATE INDEX "content_checksum_index" on "content" using btree ( "checksum" "int4_ops" ); +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" ); +CREATE INDEX idx_content_webdb_create on content(webdb_create); --- --- TOC Entry ID 56 (OID 20588) --- --- Name: "idx_content_media" Type: INDEX Owner: postgres --- -CREATE UNIQUE INDEX "idx_content_media" on "content_x_media" using btree ( "content_id" "int4_ops", "media_id" "int4_ops" ); + -- content_x_media --- --- TOC Entry ID 57 (OID 20588) --- --- Name: "idx_media_content" Type: INDEX Owner: postgres --- + CREATE TABLE "content_x_media" ( + "content_id" integer, + "media_id" integer + ); -CREATE UNIQUE INDEX "idx_media_content" on "content_x_media" using btree ( "media_id" "int4_ops", "content_id" "int4_ops" ); + CREATE UNIQUE INDEX "idx_content_media" on "content_x_media" using btree ( "content_id" "int4_ops", "media_id" "int4_ops" ); + CREATE UNIQUE INDEX "idx_media_content" on "content_x_media" using btree ( "media_id" "int4_ops", "content_id" "int4_ops" ); -- --- TOC Entry ID 62 (OID 37215) +-- breaking -- --- Name: "idx_audio_is_published_produced" Type: INDEX Owner: postgres --- - -CREATE INDEX "idx_audio_is_published_produced" on "audio" using btree ( "is_published" "bool_ops", "is_produced" "bool_ops" ); - --- --- TOC Entry ID 64 (OID 37215) --- --- Name: "idx_audio_id" Type: INDEX Owner: postgres --- - -CREATE UNIQUE INDEX "idx_audio_id" on "audio" using btree ( "id" "int4_ops" ); --- --- TOC Entry ID 65 (OID 37215) --- --- Name: "idx_video_id" Type: INDEX Owner: postgres --- - -CREATE UNIQUE INDEX "idx_video_id" on "audio" using btree ( "id" "int4_ops" ); - --- --- TOC Entry ID 63 (OID 37284) --- --- Name: "idx_video_is_published_produced" Type: INDEX Owner: postgres --- - -CREATE INDEX "idx_video_is_published_produced" on "video" using btree ( "is_published" "bool_ops", "is_produced" "bool_ops" ); - - --- --- TOC Entry ID 37 (OID 520246) --- --- Name: "idx_content_is_published" Type: INDEX Owner: postgres --- +CREATE SEQUENCE "breaking_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; -CREATE INDEX "idx_content_is_published" on "content" using btree ( "is_published" "bool_ops" ); +CREATE TABLE "breaking" ( + "id" integer DEFAULT nextval('breaking_id_seq'::text) NOT NULL, + "text" character varying(255) NOT NULL, + "webdb_create" timestamp with time zone NOT NULL +); -- --- TOC Entry ID 47 (OID 465036) +-- messages -- --- Name: "idx_comment_tomedia_ispublished" Type: INDEX Owner: postgres --- - -CREATE INDEX "idx_comment_tomedia_ispublished" on "comment" using btree ( "to_media" "int4_ops", "is_published" "bool_ops" ); -CREATE UNIQUE INDEX "idx_comment_id" on "comment" using btree ( "id" "int4_ops" ); +CREATE SEQUENCE "messages_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; -CREATE TABLE "comment_x_media" ( - "comment_id" integer, - "media_id" integer +CREATE TABLE "messages" ( + "id" integer DEFAULT nextval('messages_id_seq'::text) NOT NULL, + "title" character varying(30), + "description" character varying(255) NOT NULL, + "creator" character varying(30) NOT NULL, + "webdb_create" timestamp with time zone NOT NULL ); -CREATE UNIQUE INDEX idx_comment_media on comment_x_media (comment_id, media_id); -CREATE UNIQUE INDEX idx_media_comment on comment_x_media (media_id, comment_id); diff --git a/dbscripts/createmirdb.sh b/dbscripts/createmirdb.sh index dd26e9eb..1be2e211 100755 --- a/dbscripts/createmirdb.sh +++ b/dbscripts/createmirdb.sh @@ -37,13 +37,11 @@ echo "grant all on img_type to $3;" >> ${SCRIPT_FILE} echo "grant all on img_color to $3;" >> ${SCRIPT_FILE} echo "grant all on language to $3;" >> ${SCRIPT_FILE} echo "grant all on rights to $3;" >> ${SCRIPT_FILE} -echo "grant all on feature to $3;" >> ${SCRIPT_FILE} echo "grant all on article_type to $3;" >> ${SCRIPT_FILE} echo "grant all on media to $3;" >> ${SCRIPT_FILE} echo "grant all on breaking to $3;" >> ${SCRIPT_FILE} echo "grant all on messages to $3;" >> ${SCRIPT_FILE} echo "grant all on comment_status to $3;" >> ${SCRIPT_FILE} -echo "grant all on links_imcs to $3;" >> ${SCRIPT_FILE} echo "grant all on other_media to $3;" >> ${SCRIPT_FILE} echo "grant all on webdb_users to $3;" >> ${SCRIPT_FILE} echo "grant all on content_x_topic to $3;" >> ${SCRIPT_FILE} @@ -61,14 +59,12 @@ echo "grant all on media_folder to $3;" >> ${SCRIPT_FILE} echo "grant all on media_id_seq to $3;" >> ${SCRIPT_FILE} echo "grant all on media_folder_id_seq to $3;" >> ${SCRIPT_FILE} -echo "grant all on feature_id_seq to $3;" >> ${SCRIPT_FILE} echo "grant all on topic_id_seq to $3;" >> ${SCRIPT_FILE} echo "grant all on webdb_users_id_seq to $3;" >> ${SCRIPT_FILE} echo "grant all on comment_id_seq to $3;" >> ${SCRIPT_FILE} echo "grant all on breaking_id_seq to $3;" >> ${SCRIPT_FILE} echo "grant all on messages_id_seq to $3;" >> ${SCRIPT_FILE} echo "grant all on media_type_id_seq to $3;" >> ${SCRIPT_FILE} -echo "grant all on links_imcs_id_seq to $3;" >> ${SCRIPT_FILE} echo "grant all on comment_status_id_seq to $3;" >> ${SCRIPT_FILE} echo "grant all on article_type_id_seq to $3;" >> ${SCRIPT_FILE} echo "grant all on language_id_seq to $3;" >> ${SCRIPT_FILE} diff --git a/dbscripts/update_all_sequences.sql b/dbscripts/update_all_sequences.sql index b7c48420..a874112d 100755 --- a/dbscripts/update_all_sequences.sql +++ b/dbscripts/update_all_sequences.sql @@ -2,14 +2,12 @@ select setval('media_id_seq', (select max(id) from media)); select setval('media_folder_id_seq', (select max(id) from media_folder)); -select setval('feature_id_seq', (select max(id) from feature)); select setval('topic_id_seq', (select max(id) from topic)); select setval('webdb_users_id_seq', (select max(id) from webdb_users)); select setval('comment_id_seq', (select max(id) from comment)); select setval('breaking_id_seq', (select max(id) from breaking)); select setval('messages_id_seq', (select max(id) from messages)); select setval('media_type_id_seq', (select max(id) from media_type)); - select setval('comment_status_id_seq',(select max(id) from comment_status)); select setval('article_type_id_seq', (select max(id) from article_type)); select setval('language_id_seq', (select max(id) from language)); diff --git a/dbscripts/updates/README b/dbscripts/updates/README new file mode 100755 index 00000000..4afc2c0c --- /dev/null +++ b/dbscripts/updates/README @@ -0,0 +1,6 @@ +the scripts in this directory are for upgrading the databases of older +mirs. The scripts are date-marked. To upgrade a very old database, it +might be needed to run several scripts, chronologically. The scripts +are constructed so that running a script twice will not have ill effects. +However, database conversion is always risky, so making a dump before +converting is strongly advised. diff --git a/dbscripts/updates/update20030409.sql b/dbscripts/updates/update20030409.sql index 366dfbc3..a238b407 100755 --- a/dbscripts/updates/update20030409.sql +++ b/dbscripts/updates/update20030409.sql @@ -1,4 +1,4 @@ --- update script 2002-12-12 by Zapata +-- update script 2003-04-09 by Zapata -- * creates table comment_x_media, to support media in comments -- * adds webdb_lastchange to comment -- This script will the second time this script is run, diff --git a/templates/admin/FUNCTIONS_media.template b/templates/admin/FUNCTIONS_media.template index ec6c7353..a836b66f 100755 --- a/templates/admin/FUNCTIONS_media.template +++ b/templates/admin/FUNCTIONS_media.template @@ -31,7 +31,6 @@ - diff --git a/templates/admin/content.template b/templates/admin/content.template index 85bc115d..20c9a284 100755 --- a/templates/admin/content.template +++ b/templates/admin/content.template @@ -109,7 +109,8 @@ - + + -- 2.11.0