docker
dockerFROM postgres:10.1
ENV POSTGRES_USER postgres
ENV POSTGRES_PASSWORD root
VOLUME /var/lib/postgresql/data
COPY init.sql /docker-entrypoint-initdb.d/
EXPOSE 5432
--------------------------------------------------------------
CREATE DATABASE fds;
CREATE SCHEMA fds
AUTHORIZATION postgres;
CREATE TABLE fds.fds (
fds_id serial2 NOT null PRIMARY key ,
prov_id int2 null,
status varchar(32) NULL DEFAULT 'Новый'::character varying ,
first_publ_tms timestamp DEFAULT current_timestamp
)
WITH (
OIDS=FALSE
) ;
CREATE TABLE fds.fds_provider (
prov_id serial2 NOT NULL PRIMARY KEY ,
user_id int2 NULL UNIQUE ,
agency varchar(512) NULL,
fname varchar(64) NULL,
sname varchar(64) NULL,
mname varchar(64) NULL,
post varchar(64) NULL,
email varchar(64) NULL,
phone varchar(32) NULL,
last_updt_tms timestamp DEFAULT current_timestamp
)
WITH (
OIDS=FALSE
) ;
CREATE TABLE fds.fds_passport (
pass_id serial2 NOT NULL PRIMARY KEY ,
fds_id int2 NULL UNIQUE,
pass_name varchar(128) NULL,
ctgr varchar(32) NULL,
terr varchar(128) NULL,
descr varchar(512) NULL,
first_publ_tms timestamp DEFAULT current_timestamp ,
last_updt_tms timestamp DEFAULT current_timestamp,
last_updt_by varchar(32) NULL,
last_updt_content varchar(512) NULL,
tags varchar(128) NULL,
pass_lang varchar(16) NULL,
pass_name_token tsvector NULL,
descr_token tsvector NULL,
pass_name_descr_tokens tsvector NULL
)
WITH (
OIDS=FALSE
) ;
CREATE TABLE fds.fds_structure (
struct_id serial2 NOT NULL PRIMARY KEY ,
fds_id int2 NULL,
struct_name varchar(1024) NULL,
struct_format varchar(16) NULL,
compress_format varchar(16) NOT NULL,
struct_content bytea NULL,
last_updt_tms timestamp DEFAULT current_timestamp,
struct_version varchar(16) NULL
)
WITH (
OIDS=FALSE
) ;
CREATE TABLE fds.fds_content (
content_id serial2 NOT NULL PRIMARY KEY ,
struct_id int2 NULL,
content_name varchar(1024) NULL,
fds_id int2 NULL,
content bytea NULL,
compress_format varchar(16) NOT NULL,
last_updt_tms timestamp DEFAULT current_timestamp,
content_version int2 NULL
)
WITH (
OIDS=FALSE
) ;
ALTER TABLE fds.fds_content ADD CONSTRAINT fds_content_fds_structure_fk FOREIGN KEY (struct_id) REFERENCES fds.fds_structure(struct_id) ;
ALTER TABLE fds.fds_content ADD CONSTRAINT fds_content_fds_fk FOREIGN KEY (fds_id) REFERENCES fds.fds(fds_id) ;
ALTER TABLE fds.fds_passport ADD CONSTRAINT fds_passport_fds_fk FOREIGN KEY (fds_id) REFERENCES fds.fds(fds_id) ;
ALTER TABLE fds.fds_structure ADD CONSTRAINT fds_structure_fds_fk FOREIGN KEY (fds_id) REFERENCES fds.fds(fds_id) ;
ALTER TABLE fds.fds ADD CONSTRAINT fds_fds_provider_fk FOREIGN KEY (prov_id) REFERENCES fds.fds_provider(prov_id) ;
CREATE INDEX pass_name_token_index on fds.fds_passport USING GIN(pass_name_token);
CREATE INDEX descr_token_index on fds.fds_passport USING GIN(descr_token);
CREATE INDEX pass_name_descr_tokens_index on fds.fds_passport USING GIN(pass_name_descr_tokens);