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/database_update.sql |  125 +++++++++++++++--------------------------
 1 files changed, 47 insertions(+), 78 deletions(-)

diff --git a/hsarback/database/database_update.sql b/hsarback/database/database_update.sql
index fe401f4..8935755 100644
--- a/hsarback/database/database_update.sql
+++ b/hsarback/database/database_update.sql
@@ -1,89 +1,58 @@
-CREATE SEQUENCE sepa_mandat_id_seq
+	
+INSERT INTO contactrole_ref (contact_id, role) 
+	( SELECT contact_id, 'voting-right' FROM contact );
+	
+CREATE TABLE member_asset (
+    member_asset_id integer NOT NULL,
+    bp_id integer NOT NULL,
+    date date,
+    action character varying(60),
+    amount numeric,
+    comment character varying(160)
+);
+
+CREATE SEQUENCE member_asset_member_asset_id_seq
+    START WITH 1
     INCREMENT BY 1
-    NO MAXVALUE
     NO MINVALUE
+    NO MAXVALUE
     CACHE 1;
 
-CREATE TABLE sepa_mandat (
-    sepa_mandat_id integer DEFAULT nextval(('"sepa_mandat_id_seq"'::text)::regclass) 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 SEQUENCE member_asset_member_asset_id_seq OWNED BY member_asset.member_asset_id;
 
-INSERT INTO sepa_mandat (bp_id, bank_customer, bank_name, bank_iban, bank_bic, mandat_ref, mandat_signed, mandat_since, mandat_used, mandat_until)
-	(SELECT bp_id, bank_customer, bank_name, bank_iban, bank_bic, mandat_ref, mandat_signed, mandat_since, mandat_used, mandat_until FROM bank_account);
-
-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);
-
-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;
-
-DROP TABLE bank_account;
-
-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
-);
-
-INSERT INTO pricelist_ref (bp_id, price_list)
-	( SELECT customer, name FROM customer_price_list_mapping, price_list WHERE id = price_list );
-
-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;
-	
-DROP TABLE customer_price_list_mapping;
-	
-ALTER TABLE contact DROP COLUMN business_contact;
-
-ALTER TABLE contact DROP COLUMN technical_contact;
-
-CREATE TABLE contactrole_ref (
-    contact_id integer NOT NULL,
-    role character varying(40) NOT NULL
-);
-
-ALTER TABLE ONLY contactrole_ref
-    ADD CONSTRAINT pk_contactrole_ref PRIMARY KEY (contact_id, role);
+ALTER TABLE ONLY member_asset ALTER COLUMN member_asset_id SET DEFAULT nextval('member_asset_member_asset_id_seq'::regclass);
     
-CREATE TABLE role ( 
-    role_name character varying(40) NOT NULL
+CREATE TABLE member_share (
+    member_share_id integer NOT NULL,
+    bp_id integer NOT NULL,
+    date date,
+    action character varying(60),
+    quantity integer,
+    comment character varying(160)
 );
 
-ALTER TABLE ONLY role
-    ADD CONSTRAINT pk_role PRIMARY KEY (role_name);
+CREATE SEQUENCE member_share_member_share_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
 
-INSERT INTO role VALUES ('billing'), ('operations');
+ALTER SEQUENCE member_share_member_share_id_seq OWNED BY member_share.member_share_id;
+ 
+ALTER TABLE ONLY member_share ALTER COLUMN member_share_id SET DEFAULT nextval('member_share_member_share_id_seq'::regclass);
 
-ALTER TABLE ONLY contactrole_ref
-    ADD CONSTRAINT fk_contactrole_ref_role FOREIGN KEY (role) REFERENCES role(role_name);
+ALTER TABLE ONLY member_asset
+    ADD CONSTRAINT pk_member_asset PRIMARY KEY (member_asset_id);
 
-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 );
-	
\ No newline at end of file
+ALTER TABLE ONLY member_share
+    ADD CONSTRAINT pk_member_share PRIMARY KEY (member_share_id);
+
+ALTER TABLE ONLY member_asset
+    ADD CONSTRAINT pk_member_asset PRIMARY KEY (member_asset_id);
+   
+ALTER TABLE business_partner DROP CONSTRAINT ckc_shares_signed_business;
+
+ALTER TABLE business_partner DROP COLUMN shares_updated ;
+
+ALTER TABLE business_partner DROP COLUMN shares_signed ;

--
Gitblit v1.9.0-SNAPSHOT