hsarback/database/data.sql | ●●●●● patch | view | raw | blame | history | |
hsarback/database/database_update.sql | ●●●●● patch | view | raw | blame | history | |
hsarback/database/dropschema.sql | ●●●●● patch | view | raw | blame | history | |
hsarback/database/schema.sql | ●●●●● patch | view | raw | blame | history | |
hsarback/src/de/hsadmin/core/model/TicketValidator.java | ●●●●● 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) {