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