From 5c0c111a6552fdf310dd3b9d99c959b8746d7c99 Mon Sep 17 00:00:00 2001
From: Peter Hormanns <peter.hormanns@jalin.de>
Date: Mon, 10 Apr 2017 18:40:55 +0200
Subject: [PATCH] update schema for member_shares

---
 hsarback/database/schema.sql |  147 +++++++++++++++++++++++-------------------------
 1 files changed, 71 insertions(+), 76 deletions(-)

diff --git a/hsarback/database/schema.sql b/hsarback/database/schema.sql
index 8561024..8d4f02d 100644
--- a/hsarback/database/schema.sql
+++ b/hsarback/database/schema.sql
@@ -1,34 +1,33 @@
---
--- Name: bank_account; Type: TABLE; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE TABLE bank_account (
-    bank_account_id integer DEFAULT nextval(('"bank_account_bank_account_id_seq"'::text)::regclass) NOT NULL,
-    bp_id integer NOT NULL,
-    bank_customer character varying(50),
-    bank_account character varying(10),
-    bank_code character varying(8),
-    bank_name character varying(50),
-    bank_iban character varying(30),
-    bank_bic character varying(15),
-    mandat_ref character varying(10) NOT NULL,
-    mandat_signed date,
-    mandat_since date,
-    mandat_until date,
-    mandat_used date
-);
-
-
---
--- Name: bank_account_bank_account_id_seq; Type: SEQUENCE; Schema: public; Owner: -
---
-
-CREATE SEQUENCE bank_account_bank_account_id_seq
+CREATE SEQUENCE sepa_mandat_id_seq
     INCREMENT BY 1
     NO MAXVALUE
     NO MINVALUE
     CACHE 1;
 
+CREATE TABLE sepa_mandat (
+	sepa_mandat_id integer NOT NULL,
+    bp_id integer NOT NULL,
+    bank_customer character varying(50) NOT NULL,
+    bank_name character varying(50),
+    bank_iban character varying(40) NOT NULL,
+    bank_bic character varying(40) NOT NULL,
+    mandat_ref character varying(40) NOT NULL,
+    mandat_signed date NOT NULL,
+    mandat_since date NOT NULL,
+    mandat_used date,
+    mandat_until date
+);
+
+ALTER TABLE ONLY sepa_mandat ALTER COLUMN sepa_mandat_id SET DEFAULT nextval('sepa_mandat_id_seq'::regclass);
+
+ALTER TABLE ONLY sepa_mandat
+    ADD CONSTRAINT pk_sepa_mandat PRIMARY KEY (sepa_mandat_id);
+
+ALTER TABLE ONLY sepa_mandat
+    ADD CONSTRAINT dateschk1 CHECK (mandat_signed <= mandat_since AND mandat_since <= mandat_until);
+
+ALTER TABLE ONLY sepa_mandat
+    ADD CONSTRAINT dateschk2 CHECK (mandat_since <= mandat_until AND mandat_since <= mandat_used AND mandat_used <= mandat_until);
 
 --
 -- Name: basecomponent; Type: TABLE; Schema: public; Owner: -; Tablespace: 
@@ -112,6 +111,10 @@
     NO MINVALUE
     CACHE 1;
 
+ALTER TABLE ONLY sepa_mandat
+    ADD CONSTRAINT fk_bank_acc_reference_business FOREIGN KEY (bp_id) REFERENCES business_partner(bp_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
+
+
 
 --
 -- Name: component; Type: TABLE; Schema: public; Owner: -; Tablespace: 
@@ -170,12 +173,12 @@
     phone_mobile character varying(30),
     fax character varying(30),
     email character varying(100) NOT NULL,
-    business_contact boolean DEFAULT TRUE,
-    technical_contact boolean DEFAULT TRUE,
     CONSTRAINT ckc_email_contact CHECK (((email)::text ~~ '%@%.%'::text))
 );
 
-
+ALTER TABLE ONLY contact
+    ADD CONSTRAINT pk_contact PRIMARY KEY (contact_id);
+    
 --
 -- Name: contact_contact_id_seq; Type: SEQUENCE; Schema: public; Owner: -
 --
@@ -186,26 +189,54 @@
     NO MINVALUE
     CACHE 1;
 
+CREATE TABLE contactrole_ref (
+    contact_id integer NOT NULL,
+    role character varying(40) NOT NULL
+);
 
---
--- Name: price_list
---
+ALTER TABLE ONLY contactrole_ref
+    ADD CONSTRAINT pk_contactrole_ref PRIMARY KEY (contact_id, role);
+    
+CREATE TABLE role ( 
+    role_name character varying(40) NOT NULL
+);
+
+ALTER TABLE ONLY role
+    ADD CONSTRAINT pk_role PRIMARY KEY (role_name);
+
+INSERT INTO role VALUES ('billing'), ('operations');
+
+ALTER TABLE ONLY contactrole_ref
+    ADD CONSTRAINT fk_contactrole_ref_role FOREIGN KEY (role) REFERENCES role(role_name);
+
+ALTER TABLE ONLY contactrole_ref
+    ADD CONSTRAINT fk_contactrole_ref_contact FOREIGN KEY (contact_id) REFERENCES contact(contact_id);
+    
+INSERT INTO contactrole_ref (contact_id, role) 
+	( SELECT contact_id, 'billing' FROM contact );
+	
+INSERT INTO contactrole_ref (contact_id, role) 
+	( SELECT contact_id, 'operations' FROM contact );
 
 CREATE TABLE price_list (
     id serial primary key,                                                                   
     name character varying(20)
 );       
 
-
---
--- Name: customer_price_list_mapping
---
-
-CREATE TABLE customer_price_list_mapping (
-    customer integer references business_partner(bp_id),
-    price_list integer references price_list(id)
+ALTER TABLE price_list
+	ADD CONSTRAINT price_list_uniq_name UNIQUE (name);
+	
+CREATE TABLE pricelist_ref (
+    bp_id integer NOT NULL,
+    price_list character varying(40) NOT NULL
 );
 
+ALTER TABLE ONLY pricelist_ref
+    ADD CONSTRAINT fk_pricelist_ref_bp FOREIGN KEY (bp_id) REFERENCES business_partner(bp_id) ON UPDATE RESTRICT ON DELETE CASCADE;
+	
+ALTER TABLE ONLY pricelist_ref
+    ADD CONSTRAINT fk_pricelist_ref_pricelist FOREIGN KEY (price_list) REFERENCES price_list(name) ON UPDATE RESTRICT ON DELETE CASCADE;
+	
 
 --
 -- Name: price
@@ -523,14 +554,6 @@
 
 
 --
--- Name: pk_bank_account; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
---
-
-ALTER TABLE ONLY bank_account
-    ADD CONSTRAINT pk_bank_account PRIMARY KEY (bank_account_id);
-
-
---
 -- Name: pk_basecomponent; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
 --
 
@@ -552,19 +575,6 @@
 
 ALTER TABLE ONLY component
     ADD CONSTRAINT pk_component PRIMARY KEY (component_id);
-
-
---
--- Name: pk_contact; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
---
-
-ALTER TABLE ONLY contact
-    ADD CONSTRAINT pk_contact PRIMARY KEY (contact_id);
-
-
---
--- Name: pk_database; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
---
 
 ALTER TABLE ONLY database
     ADD CONSTRAINT pk_database PRIMARY KEY (database_id);
@@ -624,13 +634,6 @@
 
 ALTER TABLE ONLY unixuser
     ADD CONSTRAINT unixuser_name_key UNIQUE (name);
-
-
---
--- Name: bank_account_in_1; Type: INDEX; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE INDEX bank_account_in_1 ON bank_account USING btree (bp_id);
 
 
 --
@@ -798,14 +801,6 @@
 
 ALTER TABLE ONLY emailalias
     ADD CONSTRAINT emailalias_pac_id_fkey FOREIGN KEY (pac_id) REFERENCES packet(packet_id) DEFERRABLE;
-
-
---
--- Name: fk_bank_acc_reference_business; Type: FK CONSTRAINT; Schema: public; Owner: -
---
-
-ALTER TABLE ONLY bank_account
-    ADD CONSTRAINT fk_bank_acc_reference_business FOREIGN KEY (bp_id) REFERENCES business_partner(bp_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
 
 
 --

--
Gitblit v1.9.0-SNAPSHOT