238 lines
9.7 KiB
SQL
238 lines
9.7 KiB
SQL
DO
|
|
$$
|
|
begin
|
|
|
|
IF NOT EXISTS(SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'myhealth') THEN
|
|
CREATE SCHEMA myhealth;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname='usrmyhealth') THEN
|
|
create user usrmyhealth with encrypted password 'myhealth.123';
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname='USER') THEN
|
|
create user "USER" with encrypted password 'PASSWORD';
|
|
END IF;
|
|
|
|
GRANT ALL ON SCHEMA myhealth TO "USER";
|
|
GRANT ALL ON SCHEMA myhealth TO usrmyhealth;
|
|
|
|
if exists (SELECT 1 FROM pg_tables WHERE schemaname='myhealth' and tablename='administrator') THEN
|
|
drop table myhealth.administrator;
|
|
end if;
|
|
if exists (SELECT 1 FROM pg_tables WHERE schemaname='myhealth' and tablename='medicaltest') THEN
|
|
drop table myhealth.medicaltest;
|
|
end if;
|
|
if exists (SELECT 1 FROM pg_tables WHERE schemaname='myhealth' and tablename='question') THEN
|
|
drop table myhealth.question;
|
|
end if;
|
|
if exists (SELECT 1 FROM pg_tables WHERE schemaname='myhealth' and tablename='response') THEN
|
|
drop table myhealth.response;
|
|
end if;
|
|
if exists (SELECT 1 FROM pg_tables WHERE schemaname='myhealth' and tablename='visit') THEN
|
|
drop table myhealth.visit;
|
|
end if;
|
|
if exists (SELECT 1 FROM pg_tables WHERE schemaname='myhealth' and tablename='patient') THEN
|
|
drop table myhealth.patient;
|
|
end if;
|
|
if exists (SELECT 1 FROM pg_tables WHERE schemaname='myhealth' and tablename='familydoctor') THEN
|
|
drop table myhealth.familydoctor;
|
|
end if;
|
|
if exists (SELECT 1 FROM pg_tables WHERE schemaname='myhealth' and tablename='primaryhealthcarecenter') THEN
|
|
drop table myhealth.primaryhealthcarecenter;
|
|
end if;
|
|
|
|
if exists (SELECT 1 FROM pg_tables WHERE schemaname='myhealth' and tablename='specialistdoctor') THEN
|
|
drop table myhealth.specialistdoctor;
|
|
end if;
|
|
if exists (SELECT 1 FROM pg_tables WHERE schemaname='myhealth' and tablename='medicalspecialty') THEN
|
|
drop table myhealth.medicalspecialty;
|
|
end if;
|
|
|
|
/*
|
|
drop table myhealth.administrator;
|
|
drop table myhealth.familydoctor;
|
|
drop table myhealth.medicalspecialty;
|
|
drop table myhealth.medicaltest;
|
|
drop table myhealth.patient;
|
|
drop table myhealth.primaryhealthcarecenter;
|
|
drop table myhealth.question;
|
|
drop table myhealth.response;
|
|
drop table myhealth.specialistdoctor;
|
|
drop table myhealth.visit;
|
|
*/
|
|
|
|
if exists (SELECT 1 FROM pg_sequences WHERE schemaname='myhealth' and sequencename='profesionalnumber') THEN
|
|
drop sequence myhealth.profesionalnumber;
|
|
end if;
|
|
|
|
if exists (SELECT 1 FROM pg_sequences WHERE schemaname='myhealth' and sequencename='codigoidentificacionpaciente') THEN
|
|
drop sequence myhealth.codigoidentificacionpaciente;
|
|
end if;
|
|
|
|
CREATE SEQUENCE myhealth.profesionalnumber
|
|
INCREMENT 1
|
|
START 1000
|
|
MINVALUE 1000
|
|
CACHE 1;
|
|
|
|
CREATE SEQUENCE myhealth.codigoidentificacionpaciente
|
|
INCREMENT 1
|
|
START 1000
|
|
MINVALUE 1000
|
|
CACHE 1;
|
|
|
|
-- Table: myhealth.administrator
|
|
CREATE TABLE myhealth.administrator
|
|
(
|
|
email VARCHAR(120) COLLATE pg_catalog."default" NOT NULL,
|
|
password VARCHAR(100) COLLATE pg_catalog."default" NOT NULL,
|
|
CONSTRAINT administrator_pkey PRIMARY KEY (email)
|
|
)
|
|
TABLESPACE pg_default;
|
|
|
|
-- Table: myhealth.primaryhealthcarecenter
|
|
CREATE TABLE myhealth.primaryhealthcarecenter
|
|
(
|
|
id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
|
|
name VARCHAR(150) COLLATE pg_catalog."default" NOT NULL,
|
|
location VARCHAR(512) COLLATE pg_catalog."default",
|
|
CONSTRAINT primaryhealthcarecenter_pkey PRIMARY KEY (id)
|
|
)
|
|
TABLESPACE pg_default;
|
|
|
|
-- Table: myhealth.familydoctor
|
|
CREATE TABLE myhealth.familydoctor
|
|
(
|
|
id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
|
|
professionalnumber VARCHAR(15) NOT NULL COLLATE pg_catalog."default",
|
|
password VARCHAR(100) NOT NULL COLLATE pg_catalog."default",
|
|
nif VARCHAR(50) NOT NULL COLLATE pg_catalog."default",
|
|
name VARCHAR(100) NOT NULL COLLATE pg_catalog."default",
|
|
surname VARCHAR(100) NOT NULL COLLATE pg_catalog."default",
|
|
email VARCHAR(120) COLLATE pg_catalog."default",
|
|
primaryhealthcarecenterid integer REFERENCES myhealth.primaryhealthcarecenter(id) ,
|
|
CONSTRAINT familydoctor_pkey PRIMARY KEY (id)
|
|
)
|
|
TABLESPACE pg_default;
|
|
|
|
CREATE UNIQUE INDEX family_doctor_professionaln_index
|
|
ON myhealth.familydoctor (professionalnumber);
|
|
|
|
-- Table: myhealth.medicalspecialty
|
|
CREATE TABLE myhealth.medicalspecialty
|
|
(
|
|
id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
|
|
name VARCHAR(50) COLLATE pg_catalog."default" NOT NULL UNIQUE,
|
|
description VARCHAR(1000) COLLATE pg_catalog."default",
|
|
CONSTRAINT medicalspecialty_pkey PRIMARY KEY (id)
|
|
)
|
|
TABLESPACE pg_default;
|
|
|
|
-- Table: myhealth.patient
|
|
CREATE TABLE myhealth.patient
|
|
(
|
|
id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
|
|
personalIdentificationCode VARCHAR(15) NOT NULL COLLATE pg_catalog."default",
|
|
password VARCHAR(100) NOT NULL COLLATE pg_catalog."default",
|
|
nif VARCHAR(50) NOT NULL COLLATE pg_catalog."default",
|
|
name VARCHAR(100) NOT NULL COLLATE pg_catalog."default",
|
|
surname VARCHAR(100) NOT NULL COLLATE pg_catalog."default",
|
|
email VARCHAR(120) COLLATE pg_catalog."default",
|
|
familydoctorid INTEGER REFERENCES myhealth.familydoctor(id),
|
|
CONSTRAINT patient_pkey PRIMARY KEY (id)
|
|
)
|
|
TABLESPACE pg_default;
|
|
|
|
CREATE UNIQUE INDEX patient_pic_index
|
|
ON myhealth.patient (personalIdentificationCode);
|
|
|
|
-- Table: myhealth.specialistdoctor
|
|
CREATE TABLE myhealth.specialistdoctor
|
|
(
|
|
id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
|
|
professionalnumber VARCHAR(15) NOT NULL COLLATE pg_catalog."default",
|
|
password VARCHAR(100) NOT NULL COLLATE pg_catalog."default",
|
|
nif VARCHAR(50) NOT NULL COLLATE pg_catalog."default",
|
|
name VARCHAR(100) NOT NULL COLLATE pg_catalog."default",
|
|
surname VARCHAR(100) NOT NULL COLLATE pg_catalog."default",
|
|
email VARCHAR(120) COLLATE pg_catalog."default",
|
|
medicalspecialtyid INTEGER REFERENCES myhealth.medicalspecialty(id),
|
|
CONSTRAINT specialistdoctor_pkey PRIMARY KEY (id)
|
|
)
|
|
TABLESPACE pg_default;
|
|
|
|
CREATE UNIQUE INDEX specialistdoctor_professionaln_index
|
|
ON myhealth.specialistdoctor (professionalnumber);
|
|
|
|
-- Table: myhealth.visit
|
|
CREATE TABLE myhealth.visit
|
|
(
|
|
id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
|
|
date date NOT NULL,
|
|
"time" time NOT NULL,
|
|
observations TEXT COLLATE pg_catalog."default",
|
|
result TEXT COLLATE pg_catalog."default",
|
|
patientid INTEGER REFERENCES myhealth.patient(id) NOT NULL,
|
|
familydoctorid INTEGER REFERENCES myhealth.familydoctor(id) NOT NULL,
|
|
CONSTRAINT visit_pkey PRIMARY KEY (id)
|
|
)
|
|
TABLESPACE pg_default;
|
|
|
|
-- Table: myhealth.question
|
|
CREATE TABLE myhealth.question
|
|
(
|
|
id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
|
|
title VARCHAR(512) COLLATE pg_catalog."default" NOT NULL,
|
|
message TEXT COLLATE pg_catalog."default" NOT NULL,
|
|
status VARCHAR(20) NOT NULL,
|
|
response TEXT COLLATE pg_catalog."default",
|
|
patientid INTEGER REFERENCES myhealth.patient(id) NOT NULL,
|
|
familydoctorid INTEGER REFERENCES myhealth.familydoctor(id) NOT NULL,
|
|
CONSTRAINT question_pkey PRIMARY KEY (id)
|
|
)
|
|
TABLESPACE pg_default;
|
|
|
|
-- Table: myhealth.medicaltest
|
|
CREATE TABLE myhealth.medicaltest
|
|
(
|
|
id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
|
|
date DATE NOT NULL,
|
|
"time" TIME NOT NULL,
|
|
observations TEXT COLLATE pg_catalog."default",
|
|
highresimage TEXT,
|
|
type VARCHAR(50) NOT NULL,
|
|
patientid INTEGER REFERENCES myhealth.patient(id) NOT NULL,
|
|
specialistdoctorid INTEGER REFERENCES myhealth.specialistdoctor(id) NOT NULL,
|
|
CONSTRAINT medicaltest_pkey PRIMARY KEY (id)
|
|
)
|
|
TABLESPACE pg_default;
|
|
|
|
-- Permisos
|
|
ALTER TABLE myhealth.administrator OWNER to "USER";
|
|
ALTER TABLE myhealth.primaryhealthcarecenter OWNER to "USER";
|
|
ALTER TABLE myhealth.familydoctor OWNER to "USER";
|
|
ALTER TABLE myhealth.medicalspecialty OWNER to "USER";
|
|
ALTER TABLE myhealth.patient OWNER to "USER";
|
|
ALTER TABLE myhealth.specialistdoctor OWNER to "USER";
|
|
ALTER TABLE myhealth.visit OWNER to "USER";
|
|
ALTER TABLE myhealth.question OWNER to "USER";
|
|
ALTER TABLE myhealth.medicaltest OWNER to "USER";
|
|
ALTER SEQUENCE myhealth.profesionalnumber OWNER to "USER";
|
|
ALTER SEQUENCE myhealth.codigoidentificacionpaciente OWNER to "USER";
|
|
|
|
-- Permisos para la máquina de PRE (usuario: usrmyhealth)
|
|
GRANT ALL ON myhealth.administrator to usrmyhealth;
|
|
GRANT ALL ON myhealth.primaryhealthcarecenter to usrmyhealth;
|
|
GRANT ALL ON myhealth.familydoctor to usrmyhealth;
|
|
GRANT ALL ON myhealth.medicalspecialty to usrmyhealth;
|
|
GRANT ALL ON myhealth.patient to usrmyhealth;
|
|
GRANT ALL ON myhealth.specialistdoctor to usrmyhealth;
|
|
GRANT ALL ON myhealth.visit to usrmyhealth;
|
|
GRANT ALL ON myhealth.question to usrmyhealth;
|
|
GRANT ALL ON myhealth.medicaltest to usrmyhealth;
|
|
GRANT ALL ON myhealth.profesionalnumber to usrmyhealth;
|
|
GRANT ALL ON myhealth.codigoidentificacionpaciente to usrmyhealth;
|
|
|
|
END;
|
|
$$ |