media in comments, part 1: the admin side

[mir.git] / dbscripts / create_pg.sql

diff --git a/dbscripts/create_pg.sql b/dbscripts/create_pg.sql
index 90d891c..1e331c3 100755 (executable)
--- a/dbscripts/create_pg.sql
+++ b/dbscripts/create_pg.sql
@@ -152,17 +152,16 @@ CREATE TABLE "img_color" (
        "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 ;
 
 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")
 );
 
 --
@@ -219,15 +218,14 @@ CREATE TABLE "content_x_topic" (
        "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 ;
 
 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")
 );
 
 --
@@ -263,11 +261,13 @@ CREATE TABLE "comment" (
        "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")
 );
 
@@ -385,17 +385,18 @@ CREATE TABLE "messages" (
        "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 ;
 
 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")
 );
 
+
 --
 -- TOC Entry ID 33 (OID 20588)
 --
@@ -697,3 +698,10 @@ CREATE  INDEX "idx_comment_tomedia_ispublished" on "comment" using btree ( "to_m
 
 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);