migrate update-database script to schema.sql
| | |
| | | url="jdbc:postgresql://${database.host}:5432/${database.name}" |
| | | userid="${database.user}" password="${database.password}" |
| | | src="database/data.sql" /> |
| | | <sql |
| | | classpath="/usr/share/java/postgresql-jdbc3.jar" |
| | | driver="org.postgresql.Driver" |
| | | url="jdbc:postgresql://${database.host}:5432/${database.name}" |
| | | userid="${database.user}" password="${database.password}" |
| | | src="database/database_update.sql" /> |
| | | </target> |
| | | |
| | | <target name="drop-db" description="make empty database"> |
| | |
| | | -- |
| | | -- table: packet |
| | | -- |
| | | INSERT INTO packet (packet_name, bp_id, hive_id, created, cur_inet_addr_id, free) |
| | | SELECT 'hsh00', business_partner.bp_id, hive.hive_id, current_date, inet_addr.inet_addr_id, true FROM business_partner, hive, inet_addr |
| | | WHERE hive_name='h99' AND inet_addr = inet '176.9.242.72' AND member_id = 10000; |
| | | INSERT INTO packet (packet_name, bp_id, hive_id, created, cur_inet_addr_id, free) |
| | | SELECT 'hsh01', business_partner.bp_id, hive.hive_id, current_date, inet_addr.inet_addr_id, true FROM business_partner, hive, inet_addr |
| | | WHERE hive_name='h99' AND inet_addr = inet '176.9.242.73' AND member_id = 10000; |
| | | INSERT INTO packet (packet_name, bp_id, hive_id, created, cur_inet_addr_id, free, basepacket_id) |
| | | SELECT 'hsh00', business_partner.bp_id, hive.hive_id, current_date, inet_addr.inet_addr_id, true, basepacket.basepacket_id |
| | | FROM business_partner, hive, inet_addr, basepacket |
| | | WHERE hive_name='h99' AND inet_addr = inet '176.9.242.72' AND member_id = 10000 AND basepacket.basepacket_code='PAC/DW'; |
| | | INSERT INTO packet (packet_name, bp_id, hive_id, created, cur_inet_addr_id, free, basepacket_id) |
| | | SELECT 'hsh01', business_partner.bp_id, hive.hive_id, current_date, inet_addr.inet_addr_id, true, basepacket.basepacket_id |
| | | FROM business_partner, hive, inet_addr, basepacket |
| | | WHERE hive_name='h99' AND inet_addr = inet '176.9.242.73' AND member_id = 10000 AND basepacket.basepacket_code='PAC/DW'; |
| | | |
| | | -- |
| | | -- table: packet_component |
| | | -- |
| | | INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created) |
| | | SELECT 1, 1, packet.packet_id, 2, current_date FROM packet |
| | | INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created) |
| | | SELECT 1, packet.packet_id, 2, current_date FROM packet |
| | | WHERE packet.packet_name = 'hsh00'; |
| | | INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created) |
| | | SELECT 1, 2, packet.packet_id, 128, current_date FROM packet |
| | | INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created) |
| | | SELECT 2, packet.packet_id, 128, current_date FROM packet |
| | | WHERE packet.packet_name = 'hsh00'; |
| | | INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created) |
| | | SELECT 1, 3, packet.packet_id, 1, current_date FROM packet |
| | | INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created) |
| | | SELECT 3, packet.packet_id, 1, current_date FROM packet |
| | | WHERE packet.packet_name = 'hsh00'; |
| | | INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created) |
| | | SELECT 1, 4, packet.packet_id, 1, current_date FROM packet |
| | | INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created) |
| | | SELECT 4, packet.packet_id, 1, current_date FROM packet |
| | | WHERE packet.packet_name = 'hsh00'; |
| | | INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created) |
| | | SELECT 1, 5, packet.packet_id, 1, current_date FROM packet |
| | | INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created) |
| | | SELECT 5, packet.packet_id, 1, current_date FROM packet |
| | | WHERE packet.packet_name = 'hsh00'; |
| | | INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created) |
| | | SELECT 1, 1, packet.packet_id, 2, current_date FROM packet |
| | | INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created) |
| | | SELECT 1, packet.packet_id, 2, current_date FROM packet |
| | | WHERE packet.packet_name = 'hsh01'; |
| | | INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created) |
| | | SELECT 1, 2, packet.packet_id, 128, current_date FROM packet |
| | | INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created) |
| | | SELECT 2, packet.packet_id, 128, current_date FROM packet |
| | | WHERE packet.packet_name = 'hsh01'; |
| | | INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created) |
| | | SELECT 1, 3, packet.packet_id, 1, current_date FROM packet |
| | | INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created) |
| | | SELECT 3, packet.packet_id, 1, current_date FROM packet |
| | | WHERE packet.packet_name = 'hsh01'; |
| | | INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created) |
| | | SELECT 1, 4, packet.packet_id, 1, current_date FROM packet |
| | | INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created) |
| | | SELECT 4, packet.packet_id, 1, current_date FROM packet |
| | | WHERE packet.packet_name = 'hsh01'; |
| | | INSERT INTO packet_component (basepacket_id, basecomponent_id, packet_id, quantity, created) |
| | | SELECT 1, 5, packet.packet_id, 1, current_date FROM packet |
| | | INSERT INTO packet_component (basecomponent_id, packet_id, quantity, created) |
| | | SELECT 5, packet.packet_id, 1, current_date FROM packet |
| | | WHERE packet.packet_name = 'hsh01'; |
| | | |
| | | -- |
| | |
| | | -- Name: packet_component_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
| | | -- |
| | | |
| | | CREATE SEQUENCE packet_component_id_seq |
| | | INCREMENT BY 1 |
| | | NO MAXVALUE |
| | | NO MINVALUE |
| | | CACHE 1; |
| | | |
| | | CREATE SEQUENCE component_id_seq |
| | | INCREMENT BY 1 |
| | | NO MAXVALUE |
| | | NO MINVALUE |
| | | CACHE 1; |
| | | |
| | | ALTER TABLE packet_component ADD COLUMN packet_component_id integer |
| | | DEFAULT nextval(('"packet_component_id_seq"'::text)::regclass) NOT NULL; |
| | | |
| | | ALTER TABLE component ADD COLUMN component_id integer |
| | | DEFAULT nextval(('"component_id_seq"'::text)::regclass) NOT NULL; |
| | | |
| | | ALTER TABLE ONLY packet_component |
| | | DROP CONSTRAINT pk_packet_component; |
| | | |
| | | ALTER TABLE ONLY component |
| | | DROP CONSTRAINT pk_component CASCADE; |
| | | |
| | | ALTER TABLE ONLY packet_component |
| | | ADD CONSTRAINT pk_packet_component PRIMARY KEY (packet_component_id); |
| | | |
| | | ALTER TABLE ONLY component |
| | | ADD CONSTRAINT pk_component PRIMARY KEY (component_id); |
| | | |
| | | ALTER TABLE ONLY packet ADD COLUMN basepacket_id integer; |
| | | |
| | | UPDATE packet SET basepacket_id = ( SELECT basepacket_id FROM packet_component |
| | | WHERE packet_component.packet_id = packet.packet_id LIMIT 1 ); |
| | | |
| | | ALTER TABLE ONLY packet |
| | | ADD CONSTRAINT base_packet_ref FOREIGN KEY (basepacket_id) REFERENCES basepacket(basepacket_id); |
| | | |
| | | ALTER TABLE ONLY packet_component |
| | | DROP COLUMN basepacket_id; |
| | |
| | | -- Name: basepacket; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| | | -- |
| | | |
| | | CREATE SEQUENCE basepacket_basepacket_id_seq |
| | | INCREMENT BY 1 |
| | | NO MAXVALUE |
| | | NO MINVALUE |
| | | CACHE 1; |
| | | |
| | | CREATE TABLE basepacket ( |
| | | basepacket_id integer DEFAULT nextval(('"basepacket_basepacket_id_seq"'::text)::regclass) NOT NULL, |
| | | basepacket_code character varying(10) NOT NULL, |
| | |
| | | valid boolean NOT NULL |
| | | ); |
| | | |
| | | |
| | | -- |
| | | -- Name: basepacket_basepacket_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
| | | -- |
| | | |
| | | CREATE SEQUENCE basepacket_basepacket_id_seq |
| | | INCREMENT BY 1 |
| | | NO MAXVALUE |
| | | NO MINVALUE |
| | | CACHE 1; |
| | | |
| | | ALTER TABLE ONLY basepacket |
| | | ADD CONSTRAINT pk_basepacket PRIMARY KEY (basepacket_id); |
| | | |
| | | -- |
| | | -- Name: business_partner; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| | |
| | | -- Name: component; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| | | -- |
| | | |
| | | CREATE SEQUENCE component_id_seq |
| | | INCREMENT BY 1 |
| | | NO MAXVALUE |
| | | NO MINVALUE |
| | | CACHE 1; |
| | | |
| | | CREATE TABLE component ( |
| | | component_id integer DEFAULT nextval(('"component_id_seq"'::text)::regclass) NOT NULL, |
| | | basepacket_id integer NOT NULL, |
| | | basecomponent_id integer NOT NULL, |
| | | article_number integer NOT NULL, |
| | |
| | | -- Name: packet; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| | | -- |
| | | |
| | | CREATE SEQUENCE packet_packet_id_seq |
| | | INCREMENT BY 1 |
| | | NO MAXVALUE |
| | | NO MINVALUE |
| | | CACHE 1; |
| | | |
| | | CREATE TABLE packet ( |
| | | packet_id integer DEFAULT nextval(('"packet_packet_id_seq"'::text)::regclass) NOT NULL, |
| | | basepacket_id integer NOT NULL, |
| | | packet_name character varying(5) NOT NULL, |
| | | bp_id integer NOT NULL, |
| | | hive_id integer NOT NULL, |
| | |
| | | CONSTRAINT ckt_packet CHECK (((cancelled IS NULL) OR (cancelled > created))) |
| | | ); |
| | | |
| | | |
| | | -- |
| | | -- Name: packet_packet_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
| | | -- |
| | | |
| | | CREATE SEQUENCE packet_packet_id_seq |
| | | INCREMENT BY 1 |
| | | NO MAXVALUE |
| | | NO MINVALUE |
| | | CACHE 1; |
| | | ALTER TABLE ONLY packet |
| | | ADD CONSTRAINT base_packet_ref FOREIGN KEY (basepacket_id) REFERENCES basepacket(basepacket_id); |
| | | |
| | | |
| | | -- |
| | | -- Name: packet_component; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| | | -- |
| | | |
| | | CREATE SEQUENCE packet_component_id_seq |
| | | INCREMENT BY 1 |
| | | NO MAXVALUE |
| | | NO MINVALUE |
| | | CACHE 1; |
| | | |
| | | CREATE TABLE packet_component ( |
| | | basepacket_id integer NOT NULL, -- move to packet |
| | | packet_component_id integer DEFAULT nextval(('"packet_component_id_seq"'::text)::regclass) NOT NULL, |
| | | basecomponent_id integer NOT NULL, |
| | | packet_id integer NOT NULL, |
| | | quantity integer NOT NULL, |
| | |
| | | |
| | | |
| | | -- |
| | | -- Name: pk_basepacket; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| | | -- |
| | | |
| | | ALTER TABLE ONLY basepacket |
| | | ADD CONSTRAINT pk_basepacket PRIMARY KEY (basepacket_id); |
| | | |
| | | |
| | | -- |
| | | -- Name: pk_business_partner; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| | | -- |
| | | |
| | |
| | | -- |
| | | |
| | | ALTER TABLE ONLY component |
| | | ADD CONSTRAINT pk_component PRIMARY KEY (basepacket_id, basecomponent_id); |
| | | ADD CONSTRAINT pk_component PRIMARY KEY (component_id); |
| | | |
| | | |
| | | -- |
| | |
| | | -- |
| | | |
| | | ALTER TABLE ONLY packet_component |
| | | ADD CONSTRAINT pk_packet_component PRIMARY KEY (basepacket_id, basecomponent_id, packet_id); |
| | | ADD CONSTRAINT pk_packet_component PRIMARY KEY (packet_component_id); |
| | | |
| | | |
| | | -- |
| | |
| | | |
| | | ALTER TABLE ONLY component |
| | | ADD CONSTRAINT fk_bcomp_bpack FOREIGN KEY (basepacket_id) REFERENCES basepacket(basepacket_id) ON UPDATE RESTRICT ON DELETE RESTRICT; |
| | | |
| | | |
| | | -- |
| | | -- Name: fk_comp_bcomp; Type: FK CONSTRAINT; Schema: public; Owner: - |
| | | -- |
| | | |
| | | ALTER TABLE ONLY packet_component |
| | | ADD CONSTRAINT fk_comp_bcomp FOREIGN KEY (basepacket_id, basecomponent_id) REFERENCES component(basepacket_id, basecomponent_id) ON UPDATE RESTRICT ON DELETE RESTRICT; |
| | | |
| | | |
| | | -- |