
--
-- TOC entry 181 (class 1259 OID 20021)
-- Name: applicationid; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE applicationid
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 182 (class 1259 OID 20023)
-- Name: application; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE application (
    applicationid smallint DEFAULT nextval('applicationid'::regclass) NOT NULL,
    applicationcode character(30) NOT NULL,
    applicationmediatype character varying(40)
);


--
-- TOC entry 183 (class 1259 OID 20027)
-- Name: auditoryid; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE auditoryid
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 184 (class 1259 OID 20029)
-- Name: auditory; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE auditory (
    auditoryid bigint DEFAULT nextval('auditoryid'::regclass) NOT NULL,
    auditoryobjectid bigint NOT NULL,
    auditoryobjecttype character(2) NOT NULL,
    auditorymessage character(9999) NOT NULL,
    auditorycreationtime timestamp without time zone
);


--
-- TOC entry 185 (class 1259 OID 20036)
-- Name: authorid; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE authorid
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 186 (class 1259 OID 20038)
-- Name: author; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE author (
    authorid bigint DEFAULT nextval('authorid'::regclass) NOT NULL,
    authorfirstname character varying(40) NOT NULL,
    authorsecondname character varying(40),
    authorfamilyname character varying(40) NOT NULL,
    authormothersname character varying(40),
    authorsuffix character varying(10),
    authorrole character varying(40)
);


--
-- TOC entry 187 (class 1259 OID 20042)
-- Name: authoridentifier; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE authoridentifier (
    authorid bigint NOT NULL,
    authoridentifierextension character varying(40) NOT NULL,
    personidtypeid bigint NOT NULL
);


--
-- TOC entry 188 (class 1259 OID 20045)
-- Name: clinicaldocumentid; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE clinicaldocumentid
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 189 (class 1259 OID 20047)
-- Name: clinicaldocument; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE clinicaldocument (
    clinicaldocumentid bigint DEFAULT nextval('clinicaldocumentid'::regclass) NOT NULL,
    cddocumentid character(120) NOT NULL,
    cdclasscodecode character(70) NOT NULL,
    cdclassdisplayname character varying(9999) NOT NULL,
    cdtypecodecode character(70) NOT NULL,
    cdtypecodedisplayname character varying(9999) NOT NULL,
    cdpracticesettingcode character(70),
    cdpracticesettingdisplayname character varying(9999),
    cdcreationtime timestamp without time zone,
    cdserverstarttime timestamp without time zone NOT NULL,
    cdserverstoptime timestamp without time zone NOT NULL,
    cdconfidencialitycode character(1) NOT NULL,
    cddocumenttitle character varying(9999),
    cddocumentname character varying(9999),
    cddocumentdescription character varying(9999),
    patientid bigint NOT NULL,
    authorid bigint NOT NULL,
    stateid smallint NOT NULL,
    cdorganizationid character(70) NOT NULL,
    cdorganizationname character varying(9999),
    cdcustodianid character(70) NOT NULL,
    cdcustodianname character varying(9999),
    cddocumenttype character(4) NOT NULL,
    cdsetid character(70) NOT NULL,
    cdversion smallint NOT NULL,
    cddatetime timestamp without time zone,
    applicationid smallint NOT NULL
);


--
-- TOC entry 190 (class 1259 OID 20054)
-- Name: clinicaldocumentassoc; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE clinicaldocumentassoc (
    clinicaldocumentid bigint NOT NULL,
    cdassocdocumentid character(120) NOT NULL
);


--
-- TOC entry 191 (class 1259 OID 20057)
-- Name: clinicaldocumentblobid; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE clinicaldocumentblobid
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 192 (class 1259 OID 20059)
-- Name: clinicaldocumentblob; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE clinicaldocumentblob (
    clinicaldocumentblobid bigint DEFAULT nextval('clinicaldocumentblobid'::regclass) NOT NULL,
    clinicaldocumentid bigint NOT NULL,
    clinicaldocumentblob bytea NOT NULL
);


--
-- TOC entry 193 (class 1259 OID 20066)
-- Name: clinicaldocumenttextid; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE clinicaldocumenttextid
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 194 (class 1259 OID 20068)
-- Name: clinicaldocumenttext; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE clinicaldocumenttext (
    clinicaldocumenttextid bigint DEFAULT nextval('clinicaldocumenttextid'::regclass) NOT NULL,
    clinicaldocumentid bigint NOT NULL,
    clinicaldocumenttext text NOT NULL
);


--
-- TOC entry 195 (class 1259 OID 20075)
-- Name: parameter; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE parameter (
    parametertypeid smallint NOT NULL,
    parametercode character(6) NOT NULL,
    parametervalue character varying(500) NOT NULL,
    parameterdesc character varying(40) NOT NULL
);


--
-- TOC entry 196 (class 1259 OID 20081)
-- Name: parametertypeid; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE parametertypeid
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 197 (class 1259 OID 20083)
-- Name: parametertype; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE parametertype (
    parametertypeid smallint DEFAULT nextval('parametertypeid'::regclass) NOT NULL,
    parametertypedesc character varying(40) NOT NULL,
    parametertypecode character(6) NOT NULL
);


--
-- TOC entry 198 (class 1259 OID 20087)
-- Name: patientid; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE patientid
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 199 (class 1259 OID 20089)
-- Name: patient; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE patient (
    patientid bigint DEFAULT nextval('patientid'::regclass) NOT NULL,
    patientfirstname character varying(40) NOT NULL,
    patientsecondname character varying(40),
    patientfamilyname character varying(40) NOT NULL,
    patientmothersname character varying(40),
    patientgender character(1),
    patientbirthday date,
    patientaddressname character varying(9999),
    patientaddressnumber character(20),
    patientaddresscity character varying(40),
    patientphonenumber character varying(40),
    patientstateid smallint NOT NULL,
    patientdatetime timestamp without time zone
);


--
-- TOC entry 200 (class 1259 OID 20096)
-- Name: patientidentifier; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE patientidentifier (
    patientid bigint NOT NULL,
    patientidentifierextension character varying(40) NOT NULL,
    patientpersonidtypeid bigint NOT NULL
);


--
-- TOC entry 201 (class 1259 OID 20099)
-- Name: personidtypeid; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE personidtypeid
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 202 (class 1259 OID 20101)
-- Name: personidtype; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE personidtype (
    personidtypeid bigint DEFAULT nextval('personidtypeid'::regclass) NOT NULL,
    personidtypecode character(30) NOT NULL,
    personidtyperoot character(70) NOT NULL,
    personidtypedescription character varying(40),
    personidtypetype character(3) NOT NULL
);


--
-- TOC entry 203 (class 1259 OID 20105)
-- Name: stateid; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE stateid
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 204 (class 1259 OID 20107)
-- Name: state; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE state (
    stateid smallint DEFAULT nextval('stateid'::regclass) NOT NULL,
    statecode character(6) NOT NULL,
    statevalue smallint NOT NULL,
    statedescr character varying(40) NOT NULL
);


--
-- TOC entry 2987 (class 2606 OID 20112)
-- Name: application_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY application
    ADD CONSTRAINT application_pkey PRIMARY KEY (applicationid);


--
-- TOC entry 2989 (class 2606 OID 20114)
-- Name: auditory_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY auditory
    ADD CONSTRAINT auditory_pkey PRIMARY KEY (auditoryid);


--
-- TOC entry 2991 (class 2606 OID 20116)
-- Name: author_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY author
    ADD CONSTRAINT author_pkey PRIMARY KEY (authorid);


--
-- TOC entry 2993 (class 2606 OID 20118)
-- Name: authoridentifier_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY authoridentifier
    ADD CONSTRAINT authoridentifier_pkey PRIMARY KEY (authorid, authoridentifierextension, personidtypeid);


--
-- TOC entry 2996 (class 2606 OID 20120)
-- Name: clinicaldocument_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY clinicaldocument
    ADD CONSTRAINT clinicaldocument_pkey PRIMARY KEY (clinicaldocumentid);


--
-- TOC entry 3002 (class 2606 OID 20122)
-- Name: clinicaldocumentassoc_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY clinicaldocumentassoc
    ADD CONSTRAINT clinicaldocumentassoc_pkey PRIMARY KEY (clinicaldocumentid, cdassocdocumentid);


--
-- TOC entry 3004 (class 2606 OID 20124)
-- Name: clinicaldocumentblob_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY clinicaldocumentblob
    ADD CONSTRAINT clinicaldocumentblob_pkey PRIMARY KEY (clinicaldocumentblobid);


--
-- TOC entry 3007 (class 2606 OID 20126)
-- Name: clinicaldocumenttext_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY clinicaldocumenttext
    ADD CONSTRAINT clinicaldocumenttext_pkey PRIMARY KEY (clinicaldocumenttextid);


--
-- TOC entry 3010 (class 2606 OID 20128)
-- Name: parameter_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY parameter
    ADD CONSTRAINT parameter_pkey PRIMARY KEY (parametertypeid, parametercode);


--
-- TOC entry 3012 (class 2606 OID 20130)
-- Name: parametertype_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY parametertype
    ADD CONSTRAINT parametertype_pkey PRIMARY KEY (parametertypeid);


--
-- TOC entry 3015 (class 2606 OID 20132)
-- Name: patient_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY patient
    ADD CONSTRAINT patient_pkey PRIMARY KEY (patientid);


--
-- TOC entry 3018 (class 2606 OID 20134)
-- Name: patientidentifier_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY patientidentifier
    ADD CONSTRAINT patientidentifier_pkey PRIMARY KEY (patientid, patientidentifierextension, patientpersonidtypeid);


--
-- TOC entry 3020 (class 2606 OID 20136)
-- Name: personidtype_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY personidtype
    ADD CONSTRAINT personidtype_pkey PRIMARY KEY (personidtypeid);


--
-- TOC entry 3022 (class 2606 OID 20138)
-- Name: state_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY state
    ADD CONSTRAINT state_pkey PRIMARY KEY (stateid);


--
-- TOC entry 2994 (class 1259 OID 20139)
-- Name: iauthoridentifier1; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX iauthoridentifier1 ON authoridentifier USING btree (personidtypeid);


--
-- TOC entry 2997 (class 1259 OID 20140)
-- Name: iclinicaldocument1; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX iclinicaldocument1 ON clinicaldocument USING btree (authorid);


--
-- TOC entry 2998 (class 1259 OID 20141)
-- Name: iclinicaldocument2; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX iclinicaldocument2 ON clinicaldocument USING btree (patientid);


--
-- TOC entry 2999 (class 1259 OID 20142)
-- Name: iclinicaldocument3; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX iclinicaldocument3 ON clinicaldocument USING btree (stateid);


--
-- TOC entry 3000 (class 1259 OID 20143)
-- Name: iclinicaldocument4; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX iclinicaldocument4 ON clinicaldocument USING btree (applicationid);


--
-- TOC entry 3013 (class 1259 OID 20144)
-- Name: ipatient1; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX ipatient1 ON patient USING btree (patientstateid);


--
-- TOC entry 3016 (class 1259 OID 20145)
-- Name: ipatientidentifier1; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX ipatientidentifier1 ON patientidentifier USING btree (patientpersonidtypeid);


--
-- TOC entry 3008 (class 1259 OID 20146)
-- Name: uclinicaldocument; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX uclinicaldocument ON clinicaldocumenttext USING btree (clinicaldocumentid);


--
-- TOC entry 3005 (class 1259 OID 20147)
-- Name: uclinicaldocumentid; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX uclinicaldocumentid ON clinicaldocumentblob USING btree (clinicaldocumentid);


--
-- TOC entry 3023 (class 2606 OID 20148)
-- Name: iauthoridentifier1; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY authoridentifier
    ADD CONSTRAINT iauthoridentifier1 FOREIGN KEY (personidtypeid) REFERENCES personidtype(personidtypeid);


--
-- TOC entry 3024 (class 2606 OID 20153)
-- Name: iauthoridentifier2; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY authoridentifier
    ADD CONSTRAINT iauthoridentifier2 FOREIGN KEY (authorid) REFERENCES author(authorid);


--
-- TOC entry 3025 (class 2606 OID 20158)
-- Name: iclinicaldocument1; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY clinicaldocument
    ADD CONSTRAINT iclinicaldocument1 FOREIGN KEY (authorid) REFERENCES author(authorid);


--
-- TOC entry 3026 (class 2606 OID 20163)
-- Name: iclinicaldocument2; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY clinicaldocument
    ADD CONSTRAINT iclinicaldocument2 FOREIGN KEY (patientid) REFERENCES patient(patientid);


--
-- TOC entry 3027 (class 2606 OID 20168)
-- Name: iclinicaldocument3; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY clinicaldocument
    ADD CONSTRAINT iclinicaldocument3 FOREIGN KEY (stateid) REFERENCES state(stateid);


--
-- TOC entry 3028 (class 2606 OID 20173)
-- Name: iclinicaldocument4; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY clinicaldocument
    ADD CONSTRAINT iclinicaldocument4 FOREIGN KEY (applicationid) REFERENCES application(applicationid);


--
-- TOC entry 3029 (class 2606 OID 20178)
-- Name: iclinicaldocumentassoc1; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY clinicaldocumentassoc
    ADD CONSTRAINT iclinicaldocumentassoc1 FOREIGN KEY (clinicaldocumentid) REFERENCES clinicaldocument(clinicaldocumentid);


--
-- TOC entry 3030 (class 2606 OID 20183)
-- Name: iclinicaldocumentblob1; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY clinicaldocumentblob
    ADD CONSTRAINT iclinicaldocumentblob1 FOREIGN KEY (clinicaldocumentid) REFERENCES clinicaldocument(clinicaldocumentid);


--
-- TOC entry 3031 (class 2606 OID 20188)
-- Name: iclinicaldocumenttext1; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY clinicaldocumenttext
    ADD CONSTRAINT iclinicaldocumenttext1 FOREIGN KEY (clinicaldocumentid) REFERENCES clinicaldocument(clinicaldocumentid);


--
-- TOC entry 3032 (class 2606 OID 20193)
-- Name: iparameter1; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY parameter
    ADD CONSTRAINT iparameter1 FOREIGN KEY (parametertypeid) REFERENCES parametertype(parametertypeid);


--
-- TOC entry 3033 (class 2606 OID 20198)
-- Name: ipatient1; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY patient
    ADD CONSTRAINT ipatient1 FOREIGN KEY (patientstateid) REFERENCES state(stateid);


--
-- TOC entry 3034 (class 2606 OID 20203)
-- Name: ipatientidentifier1; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY patientidentifier
    ADD CONSTRAINT ipatientidentifier1 FOREIGN KEY (patientpersonidtypeid) REFERENCES personidtype(personidtypeid);


--
-- TOC entry 3035 (class 2606 OID 20208)
-- Name: ipatientidentifier2; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY patientidentifier
    ADD CONSTRAINT ipatientidentifier2 FOREIGN KEY (patientid) REFERENCES patient(patientid);

ALTER TABLE Patient ADD patientBirthplaceCountry VARCHAR(100) , ADD patientBirthplaceState VARCHAR(40) , ADD patientBirthplaceCity VARCHAR(40);

CREATE SEQUENCE ByOrderOfId MINVALUE 1 INCREMENT 1;

CREATE TABLE ByOrderOf (
  ByOrderOfId   BIGINT     NOT NULL     DEFAULT Nextval('ByOrderOfId'),
  ByOrderOfOID  VARCHAR(120)     NOT NULL,
  ByOrderOfName VARCHAR(200),
      PRIMARY KEY ( ByOrderOfId ));

 
CREATE SEQUENCE FunderId MINVALUE 1 INCREMENT 1;
 
CREATE TABLE Funder (
  FunderId   BIGINT     NOT NULL     DEFAULT Nextval('FunderId'),
  FunderOID  VARCHAR(120)     NOT NULL,
  FunderName VARCHAR(200),
      PRIMARY KEY ( FunderId ));

 
CREATE SEQUENCE AxisId MINVALUE 1 INCREMENT 1;

CREATE TABLE Axis (
  AxisId           BIGINT     NOT NULL     DEFAULT Nextval('AxisId'),
  AxisUUID         VARCHAR(46)     NOT NULL,
  AxisName         VARCHAR(200)     NOT NULL,
  AxisCode         VARCHAR(200)     NOT NULL,
  AxisCodingScheme VARCHAR(120)     NOT NULL,
  AxisDisplayName  VARCHAR(200)     NOT NULL,
      PRIMARY KEY ( AxisId ));

 CREATE TABLE ClinicalDocumentAxis (
  clinicalDocumentId BIGINT     NOT NULL,
  AxisId             BIGINT     NOT NULL,
      PRIMARY KEY ( clinicalDocumentId,AxisId ));

CREATE INDEX ICLINICALDOCUMENTAXIS1 ON ClinicalDocumentAxis (AxisId);

alter table clinicaldocument  add FunderId bigint;

alter table clinicaldocument  add ByOrderOfId bigint;

CREATE INDEX ICLINICALDOCUMENT6 ON ClinicalDocument (FunderId);

CREATE INDEX ICLINICALDOCUMENT5 ON ClinicalDocument (ByOrderOfId);

alter table author add authorlocation character varying(200);
alter table clinicaldocument  add amountofstudies integer;

ALTER TABLE public.clinicaldocument   ALTER COLUMN byorderofid DROP NOT NULL; 
 
 




