[openempi~source-repository:13] Various updates to the schema to bring it up to date with the current rel

[odysseas@kenai.com]

Project:    openempi
Repository: source-repository
Revision:   13
Author:     odysseas
Date:       2010-01-12 22:25:15 UTC
Link:       

Log Message:
------------
Various updates to the schema to bring it up to date with the current release


Revisions:
----------
13


Modified Paths:
---------------
trunk/data/drop_database_schema.sql
trunk/data/create_database_schema.sql


Diffs:
------
Index: trunk/data/create_database_schema.sql
===================================================================
--- trunk/data/create_database_schema.sql       (revision 12)
+++ trunk/data/create_database_schema.sql       (revision 13)
@@ -2359,6 +2359,46 @@
     user_id bigint NOT NULL
 ) WITHOUT OIDS;
 
+-- Structure for table audit_event (OID = 59451):
+CREATE TABLE audit_event (
+    audit_event_id integer NOT NULL,
+    date_created timestamp without time zone NOT NULL,
+    audit_event_type_cd integer NOT NULL,
+    audit_event_description varchar(255),
+    ref_person_id integer,
+    alt_ref_person_id integer,
+    creator_id integer NOT NULL
+) WITHOUT OIDS;
+
+-- Structure for table audit_event_type (OID = 59461):
+CREATE TABLE audit_event_type (
+    audit_event_type_cd integer NOT NULL,
+    audit_event_type_name varchar(64) NOT NULL,
+    audit_event_type_description varchar(255),
+    audit_event_type_code varchar(64) NOT NULL
+) WITHOUT OIDS;
+
+-- Structure for table person_link_review (OID = 59489):
+CREATE TABLE person_link_review (
+    person_link_review_id integer NOT NULL,
+    rh_person_id integer NOT NULL,
+    lh_person_id integer NOT NULL,
+    date_created timestamp without time zone NOT NULL,
+    weight double precision,
+    creator_id integer NOT NULL,
+    reviewer_id integer,
+    date_reviewed integer
+) WITHOUT OIDS;
+
+
+
+-- Definition for sequence audit_event_seq
+CREATE SEQUENCE audit_event_seq
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+  
 -- Definition for sequence hibernate_sequence (OID = 34671):
 CREATE SEQUENCE hibernate_sequence
     INCREMENT BY 1
@@ -2394,6 +2434,12 @@
     NO MINVALUE
     CACHE 1;
 
+CREATE SEQUENCE person_link_review_seq
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
 -- Definition for sequence person_seq (OID = 34679):
 CREATE SEQUENCE person_seq
     INCREMENT BY 1
@@ -2465,6 +2511,13 @@
 -- Definition for index identifier_domain_universal_identifier (OID = 34684):
 CREATE UNIQUE INDEX identifier_domain_universal_identifier ON 
identifier_domain USING btree (universal_identifier, 
universal_identifier_type_code);
 
+-- Definition for index idx_audit_event_ref_person (OID = 59456):
+CREATE INDEX idx_audit_event_ref_person ON audit_event USING btree 
(ref_person_id);
+
+-- Definition for index idx_audit_event_type_code (OID = 59468):
+CREATE INDEX idx_audit_event_type_code ON audit_event_type USING btree 
(audit_event_type_code);
+
+-- Definition for index idx_identifier_domain_id
 CREATE INDEX idx_identifier_domain_id ON public.identifier_domain_attribute 
USING btree (identifier_domain_id);
 
 -- Definition for index person_identifier_person_id (OID = 34685):
@@ -2655,4 +2708,60 @@
 -- Definition for index fk_name_type (OID = 34797):
 ALTER TABLE ONLY person
     ADD CONSTRAINT fk_name_type FOREIGN KEY (name_type_cd) REFERENCES 
name_type(name_type_cd);
+
+
+-- Definition for index audit_event_pkey (OID = 59454):
+ALTER TABLE ONLY audit_event
+    ADD CONSTRAINT audit_event_pkey PRIMARY KEY (audit_event_id);
+
+-- Definition for index idx_audit_event_type (OID = 59458):
+ALTER TABLE ONLY audit_event
+    ADD CONSTRAINT idx_audit_event_type UNIQUE (audit_event_type_cd);
+
+-- Definition for index audit_event_type_pkey (OID = 59464):
+ALTER TABLE ONLY audit_event_type
+    ADD CONSTRAINT audit_event_type_pkey PRIMARY KEY (audit_event_type_cd);
+
+-- Definition for index idx_audit_event_type_name (OID = 59466):
+ALTER TABLE ONLY audit_event_type
+    ADD CONSTRAINT idx_audit_event_type_name UNIQUE (audit_event_type_name);
+
+-- Definition for index fk_audit_event_type_cd (OID = 59469):
+ALTER TABLE ONLY audit_event
+    ADD CONSTRAINT fk_audit_event_type_cd FOREIGN KEY (audit_event_type_cd) 
REFERENCES audit_event_type(audit_event_type_cd);
+
+-- Definition for index fk_ref_person_id (OID = 59474):
+ALTER TABLE ONLY audit_event
+    ADD CONSTRAINT fk_ref_person_id FOREIGN KEY (ref_person_id) REFERENCES 
person(person_id);
+
+-- Definition for index fk_alt_ref_person_id (OID = 59479):
+ALTER TABLE ONLY audit_event
+    ADD CONSTRAINT fk_alt_ref_person_id FOREIGN KEY (alt_ref_person_id) 
REFERENCES person(person_id);
+
+-- Definition for index fk_creator_id (OID = 59484):
+ALTER TABLE ONLY audit_event
+    ADD CONSTRAINT fk_creator_id FOREIGN KEY (creator_id) REFERENCES 
app_user(id);
+
+-- Definition for index person_link_review_pkey (OID = 59492):
+ALTER TABLE ONLY person_link_review
+    ADD CONSTRAINT person_link_review_pkey PRIMARY KEY 
(person_link_review_id);
+
+-- Definition for index fk_rh_person_id (OID = 59494):
+ALTER TABLE ONLY person_link_review
+    ADD CONSTRAINT fk_rh_person_id FOREIGN KEY (rh_person_id) REFERENCES 
person(person_id);
+
+-- Definition for index fk_lh_person_id (OID = 59499):
+ALTER TABLE ONLY person_link_review
+    ADD CONSTRAINT fk_lh_person_id FOREIGN KEY (lh_person_id) REFERENCES 
person(person_id);
+
+-- Definition for index fk_creator_id (OID = 59504):
+ALTER TABLE ONLY person_link_review
+    ADD CONSTRAINT fk_creator_id FOREIGN KEY (creator_id) REFERENCES 
app_user(id);
+
+-- Definition for index fk_reviewer_id (OID = 59509):
+ALTER TABLE ONLY person_link_review
+    ADD CONSTRAINT fk_reviewer_id FOREIGN KEY (reviewer_id) REFERENCES 
app_user(id);
+
 COMMENT ON SCHEMA public IS 'standard public schema';
+COMMENT ON COLUMN audit_event.ref_person_id IS 'The field refers to a person 
record that is associated in some way with the audit event. For example in 
the case of a person record update audit event this field will refer to the 
person record that was updated.';
+COMMENT ON COLUMN audit_event.alt_ref_person_id IS 'The audit event may 
refer to a second person that is associated with the event in some way. For 
example a link audit event would refer to the second person record that was 
linked.';
Index: trunk/data/drop_database_schema.sql
===================================================================
--- trunk/data/drop_database_schema.sql (revision 12)
+++ trunk/data/drop_database_schema.sql (revision 13)
@@ -49,7 +49,16 @@
 ALTER TABLE ONLY public.app_user DROP CONSTRAINT app_user_username_key;
 ALTER TABLE ONLY public.app_user DROP CONSTRAINT app_user_pkey;
 ALTER TABLE ONLY public.address_type DROP CONSTRAINT address_type_pkey;
+ALTER TABLE ONLY public.audit_event DROP CONSTRAINT fk_creator_id;
+ALTER TABLE ONLY public.audit_event DROP CONSTRAINT fk_alt_ref_person_id;
+ALTER TABLE ONLY public.audit_event DROP CONSTRAINT fk_ref_person_id;
+ALTER TABLE ONLY public.audit_event DROP CONSTRAINT fk_audit_event_type_cd;
+ALTER TABLE ONLY public.audit_event_type DROP CONSTRAINT 
idx_audit_event_type_name;
+ALTER TABLE ONLY public.audit_event_type DROP CONSTRAINT 
audit_event_type_pkey;
+ALTER TABLE ONLY public.audit_event DROP CONSTRAINT idx_audit_event_type;
+ALTER TABLE ONLY public.audit_event DROP CONSTRAINT audit_event_pkey;
 
+
 DROP INDEX public.user_session_date_created;
 DROP INDEX public.user_session_session_key;
 DROP INDEX public.role_name;
@@ -59,6 +68,9 @@
 DROP INDEX public.person_identifier_person_id;
 DROP INDEX public.identifier_domain_universal_identifier;
 DROP INDEX public.identifier_domain_namespace_identifier_key;
+DROP INDEX public.idx_audit_event_type_code;
+DROP INDEX public.idx_audit_event_ref_person;
+
 DROP SEQUENCE public.user_session_seq;
 DROP SEQUENCE public.person_seq;
 DROP SEQUENCE public.person_link_seq;
@@ -67,11 +79,17 @@
 DROP SEQUENCE public.identifier_domain_attribute_seq;
 DROP SEQUENCE public.identifier_seq;
 DROP SEQUENCE public.hibernate_sequence;
+DROP SEQUENCE public.audit_event_seq;
+DROP SEQUENCE public.person_link_review_seq;
+
 DROP TABLE public.user_session;
 DROP TABLE public.user_role;
 DROP TABLE public.role;
 DROP TABLE public.religion;
 DROP TABLE public.race;
+DROP TABLE public.person_link_review;
+DROP TABLE public.audit_event;
+DROP TABLE public.audit_event_type;
 DROP TABLE public.person_link;
 DROP TABLE public.person_identifier;
 DROP TABLE public.person;