| | |
| | | CREATE SEQUENCE sepa_mandat_id_seq |
| | | INCREMENT BY 1 |
| | | NO MAXVALUE |
| | | NO MINVALUE |
| | | 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 |
| | | ); |
| | | |
| | | 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); |
| | | |
| | | 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 ); |
| | | |