Projekt

Obecné

Profil

« Předchozí | Další » 

Revize 4556011c

Přidáno uživatelem Michal Schwob před více než 2 roky(ů)

re #9135
Database model and sql created

Zobrazit rozdíly:

DB/db_script-v1.1.sql
1
CREATE SCHEMA IF NOT EXISTS aswi;
2

  
3
CREATE  TABLE aswi."catalog" ( 
4
	id                   uuid  NOT NULL  ,
5
	name                 varchar(100)  NOT NULL  ,
6
	certainty            integer    ,
7
	latitude             double precision    ,
8
	longitude            double precision    ,
9
	CONSTRAINT pk_katalog PRIMARY KEY ( id )
10
 );
11

  
12
CREATE  TABLE aswi.countries ( 
13
	name                 varchar(100)  NOT NULL  ,
14
	catalog_id           uuid  NOT NULL  ,
15
	CONSTRAINT pk_countries PRIMARY KEY ( name )
16
 );
17

  
18
CREATE  TABLE aswi.types ( 
19
	"type"               varchar(100)  NOT NULL  ,
20
	CONSTRAINT pk_types PRIMARY KEY ( "type" )
21
 );
22

  
23
CREATE  TABLE aswi.users ( 
24
	name                 varchar(100)    ,
25
	email                varchar(100)  NOT NULL  ,
26
	"password"           varchar(128)  NOT NULL  ,
27
	is_admin             bit DEFAULT 0   ,
28
	CONSTRAINT pk_users PRIMARY KEY ( email )
29
 );
30

  
31
CREATE  TABLE aswi.written_forms ( 
32
	form                 varchar(100)  NOT NULL  ,
33
	catalog_id           uuid  NOT NULL  ,
34
	CONSTRAINT pk_written_forms PRIMARY KEY ( form, catalog_id )
35
 );
36

  
37
CREATE  TABLE aswi.alternative_names ( 
38
	catalog_id           uuid  NOT NULL  ,
39
	name                 varchar(100)  NOT NULL  ,
40
	CONSTRAINT pk_alternative_names PRIMARY KEY ( catalog_id, name )
41
 );
42

  
43
CREATE  TABLE aswi.bibliography ( 
44
	"source"             varchar(150)  NOT NULL  ,
45
	catalog_id           uuid  NOT NULL  ,
46
	CONSTRAINT pk_bibliography PRIMARY KEY ( "source", catalog_id )
47
 );
48

  
49
CREATE  TABLE aswi.catalog_type ( 
50
	catalog_id           uuid  NOT NULL  ,
51
	"type"               uuid  NOT NULL  ,
52
	CONSTRAINT pk_catalog_type PRIMARY KEY ( catalog_id, "type" )
53
 );
54

  
55
ALTER TABLE aswi.alternative_names ADD CONSTRAINT fk_catalog FOREIGN KEY ( catalog_id ) REFERENCES aswi."catalog"( id ) ON DELETE CASCADE;
56

  
57
ALTER TABLE aswi.bibliography ADD CONSTRAINT fk_bibliography_catalog FOREIGN KEY ( catalog_id ) REFERENCES aswi."catalog"( id );
58

  
59
ALTER TABLE aswi.catalog_type ADD CONSTRAINT fk_catalog FOREIGN KEY ( catalog_id ) REFERENCES aswi."catalog"( id );
60

  
61
ALTER TABLE aswi.catalog_type ADD CONSTRAINT fk_type_id FOREIGN KEY (  ) REFERENCES aswi.types(  );
62

  
63
ALTER TABLE aswi.countries ADD CONSTRAINT fk_countries_catalog FOREIGN KEY ( catalog_id ) REFERENCES aswi."catalog"( id );
64

  
65
ALTER TABLE aswi.written_forms ADD CONSTRAINT fk_written_forms_catalog FOREIGN KEY ( catalog_id ) REFERENCES aswi."catalog"( id );
66

  
67
COMMENT ON TABLE aswi."catalog" IS 'Catalog with geografical terms';
68

  
69
COMMENT ON COLUMN aswi."catalog".name IS 'Name of the geografical term';
70

  
71
COMMENT ON COLUMN aswi."catalog".latitude IS 'Geografical latitude';
72

  
73
COMMENT ON COLUMN aswi."catalog".longitude IS 'Geografical longitude';
74

  
75
COMMENT ON TABLE aswi.users IS 'Table to store different application users';
76

  
77
COMMENT ON COLUMN aswi.users.is_admin IS 'Logical value if the user has admin privileges';
78

  

Také k dispozici: Unified diff