hsadmin/hs.hsadmin.git - Hostsharing Git Server

parent: 2326fa74 | patch | commit | ignore whitespace

Peter Hormanns

2017-04-10 5c0c111a6552fdf310dd3b9d99c959b8746d7c99

update schema for member_shares

5 files modified

	hsarback/database/data.sql	12 ●●●●● patch \| view \| raw \| blame \| history
	hsarback/database/database_update.sql	125 ●●●●● patch \| view \| raw \| blame \| history
	hsarback/database/dropschema.sql	1 ●●●●● patch \| view \| raw \| blame \| history
	hsarback/database/schema.sql	147 ●●●●● patch \| view \| raw \| blame \| history
	hsarback/src/de/hsadmin/core/model/TicketValidator.java	2 ●●●●● patch \| view \| raw \| blame \| history

 hsarback/database/data.sql

@@ -87,14 +87,6 @@
    SELECT bp_id, 'Herr', 'Uwe', 'Mueller', 'Hostsharing eG', 'service@hostsharing.net' FROM business_partner WHERE member_id=10000;

--
-- table: bank_account 
--
INSERT INTO bank_account (bp_id, bank_customer, bank_account, bank_code, bank_name, bank_iban, bank_bic, mandat_ref, mandat_signed, mandat_since)
    SELECT bp_id, '', '', '', '', 'DEXX', 'XXX', member_id, current_date, current_date 
        FROM business_partner WHERE member_id=10000;

		
--
-- Table: inet_addr 
--
INSERT INTO inet_addr (inet_addr, description)
@@ -207,11 +199,11 @@
--
-- table: price_list
--
INSERT INTO price_list VALUES (1, 'Default Price List');
INSERT INTO price_list (name) VALUES ('Default Price List');

--
-- table: customer_price_list_mapping
--
INSERT INTO customer_price_list_mapping (SELECT bp_id, 1 FROM business_partner);
INSERT INTO pricelist_ref (SELECT business_partner.bp_id, price_list.name FROM business_partner, price_list);



 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 );
	
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 ;

 hsarback/database/dropschema.sql

@@ -39,6 +39,5 @@
DROP SEQUENCE business_partner_bp_id_seq ;
DROP SEQUENCE packet_component_id_seq ;
DROP SEQUENCE component_id_seq ;
DROP SEQUENCE bank_account_bank_account_id_seq;
DROP TABLE contactrole_ref CASCADE ;
DROP TABLE role CASCADE ;

 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;


--

 hsarback/src/de/hsadmin/core/model/TicketValidator.java

@@ -56,11 +56,13 @@
            while (nextLine != null) {
                if (nextLine.contains("<cas:user>")) {
                    String user = extractUser(nextLine);
                    reader.close();
                    inputStream.close();
                    return user;
                }
                nextLine = reader.readLine();
            }
            reader.close();
            inputStream.close();
            throw new AuthenticationException("Invalid Ticket: " + ticket);
        } catch (MalformedURLException e) {

			@@ -87,14 +87,6 @@
			SELECT bp_id, 'Herr', 'Uwe', 'Mueller', 'Hostsharing eG', 'service@hostsharing.net' FROM business_partner WHERE member_id=10000;

			--
			-- table: bank_account
			--
			INSERT INTO bank_account (bp_id, bank_customer, bank_account, bank_code, bank_name, bank_iban, bank_bic, mandat_ref, mandat_signed, mandat_since)
			SELECT bp_id, '', '', '', '', 'DEXX', 'XXX', member_id, current_date, current_date
			FROM business_partner WHERE member_id=10000;


			--
			-- Table: inet_addr
			--
			INSERT INTO inet_addr (inet_addr, description)
			@@ -207,11 +199,11 @@
			--
			-- table: price_list
			--
			INSERT INTO price_list VALUES (1, 'Default Price List');
			INSERT INTO price_list (name) VALUES ('Default Price List');

			--
			-- table: customer_price_list_mapping
			--
			INSERT INTO customer_price_list_mapping (SELECT bp_id, 1 FROM business_partner);
			INSERT INTO pricelist_ref (SELECT business_partner.bp_id, price_list.name FROM business_partner, price_list);

			@@ -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 );

			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 ;

			@@ -39,6 +39,5 @@
			DROP SEQUENCE business_partner_bp_id_seq ;
			DROP SEQUENCE packet_component_id_seq ;
			DROP SEQUENCE component_id_seq ;
			DROP SEQUENCE bank_account_bank_account_id_seq;
			DROP TABLE contactrole_ref CASCADE ;
			DROP TABLE role CASCADE ;

			@@ -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;


			--

			@@ -56,11 +56,13 @@
			while (nextLine != null) {
			if (nextLine.contains("<cas:user>")) {
			String user = extractUser(nextLine);
			reader.close();
			inputStream.close();
			return user;
			}
			nextLine = reader.readLine();
			}
			reader.close();
			inputStream.close();
			throw new AuthenticationException("Invalid Ticket: " + ticket);
			} catch (MalformedURLException e) {