Revize 4556011c

re #9135
Database model and sql created

DB/db_script-v1.1.sql

@@ +1 @@
+CREATE SCHEMA IF NOT EXISTS aswi;
+
+CREATE  TABLE aswi."catalog" ( 
+	id                   uuid  NOT NULL  ,
+	name                 varchar(100)  NOT NULL  ,
+	certainty            integer    ,
+	latitude             double precision    ,
+	longitude            double precision    ,
+	CONSTRAINT pk_katalog PRIMARY KEY ( id )
+ );
+
+CREATE  TABLE aswi.countries ( 
+	name                 varchar(100)  NOT NULL  ,
+	catalog_id           uuid  NOT NULL  ,
+	CONSTRAINT pk_countries PRIMARY KEY ( name )
+ );
+
+CREATE  TABLE aswi.types ( 
+	"type"               varchar(100)  NOT NULL  ,
+	CONSTRAINT pk_types PRIMARY KEY ( "type" )
+ );
+
+CREATE  TABLE aswi.users ( 
+	name                 varchar(100)    ,
+	email                varchar(100)  NOT NULL  ,
+	"password"           varchar(128)  NOT NULL  ,
+	is_admin             bit DEFAULT 0   ,
+	CONSTRAINT pk_users PRIMARY KEY ( email )
+ );
+
+CREATE  TABLE aswi.written_forms ( 
+	form                 varchar(100)  NOT NULL  ,
+	catalog_id           uuid  NOT NULL  ,
+	CONSTRAINT pk_written_forms PRIMARY KEY ( form, catalog_id )
+ );
+
+CREATE  TABLE aswi.alternative_names ( 
+	catalog_id           uuid  NOT NULL  ,
+	name                 varchar(100)  NOT NULL  ,
+	CONSTRAINT pk_alternative_names PRIMARY KEY ( catalog_id, name )
+ );
+
+CREATE  TABLE aswi.bibliography ( 
+	"source"             varchar(150)  NOT NULL  ,
+	catalog_id           uuid  NOT NULL  ,
+	CONSTRAINT pk_bibliography PRIMARY KEY ( "source", catalog_id )
+ );
+
+CREATE  TABLE aswi.catalog_type ( 
+	catalog_id           uuid  NOT NULL  ,
+	"type"               uuid  NOT NULL  ,
+	CONSTRAINT pk_catalog_type PRIMARY KEY ( catalog_id, "type" )
+ );
+
+ALTER TABLE aswi.alternative_names ADD CONSTRAINT fk_catalog FOREIGN KEY ( catalog_id ) REFERENCES aswi."catalog"( id ) ON DELETE CASCADE;
+
+ALTER TABLE aswi.bibliography ADD CONSTRAINT fk_bibliography_catalog FOREIGN KEY ( catalog_id ) REFERENCES aswi."catalog"( id );
+
+ALTER TABLE aswi.catalog_type ADD CONSTRAINT fk_catalog FOREIGN KEY ( catalog_id ) REFERENCES aswi."catalog"( id );
+
+ALTER TABLE aswi.catalog_type ADD CONSTRAINT fk_type_id FOREIGN KEY (  ) REFERENCES aswi.types(  );
+
+ALTER TABLE aswi.countries ADD CONSTRAINT fk_countries_catalog FOREIGN KEY ( catalog_id ) REFERENCES aswi."catalog"( id );
+
+ALTER TABLE aswi.written_forms ADD CONSTRAINT fk_written_forms_catalog FOREIGN KEY ( catalog_id ) REFERENCES aswi."catalog"( id );
+
+COMMENT ON TABLE aswi."catalog" IS 'Catalog with geografical terms';
+
+COMMENT ON COLUMN aswi."catalog".name IS 'Name of the geografical term';
+
+COMMENT ON COLUMN aswi."catalog".latitude IS 'Geografical latitude';
+
+COMMENT ON COLUMN aswi."catalog".longitude IS 'Geografical longitude';
+
+COMMENT ON TABLE aswi.users IS 'Table to store different application users';
+
+COMMENT ON COLUMN aswi.users.is_admin IS 'Logical value if the user has admin privileges';
+