From 522b89c5372746a79c500578967c3e8ba276320a Mon Sep 17 00:00:00 2001 From: Peter Hormanns <peter.hormanns@jalin.de> Date: Mon, 13 May 2013 19:52:13 +0200 Subject: [PATCH] refactoring of database on pac-components --- hsarback/database/database_update.sql | 27 ++++++++++++++++++++++----- 1 files changed, 22 insertions(+), 5 deletions(-) diff --git a/hsarback/database/database_update.sql b/hsarback/database/database_update.sql index b244b8f..ccf2c92 100644 --- a/hsarback/database/database_update.sql +++ b/hsarback/database/database_update.sql @@ -25,20 +25,37 @@ 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 ); - + 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); - \ No newline at end of file + +ALTER TABLE ONLY packet_component + DROP COLUMN basepacket_id; -- Gitblit v1.9.0-SNAPSHOT