From 0b888d2a736b1b307da7bb412fc4beb151af0173 Mon Sep 17 00:00:00 2001
From: Peter Hormanns <peter.hormanns@jalin.de>
Date: Sat, 11 May 2013 17:52:53 +0200
Subject: [PATCH] prepare for moving basepacket foreign key

---
 hsarback/database/database_update.sql |  141 +++++++---------------------------------------
 1 files changed, 23 insertions(+), 118 deletions(-)

diff --git a/hsarback/database/database_update.sql b/hsarback/database/database_update.sql
index 3d69d26..b244b8f 100644
--- a/hsarback/database/database_update.sql
+++ b/hsarback/database/database_update.sql
@@ -15,125 +15,30 @@
 -- 			AND domain__domain_option.domain_option_id = domain_option.domain_option_id
 -- 			AND domain_option.domain_option_name = 'php' ;
 
---- Updates related to HSBilling
+--
+-- Name: packet_component_id_seq; Type: SEQUENCE; Schema: public; Owner: -
+--
 
-UPDATE bank_account SET autodebit_ar = false WHERE autodebit_ar IS NULL;
-ALTER TABLE bank_account ALTER COLUMN autodebit_ar SET NOT NULL;
+CREATE SEQUENCE packet_component_id_seq
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
 
-UPDATE bank_account SET autodebit_ga = false WHERE autodebit_ga IS NULL;
-ALTER TABLE bank_account ALTER COLUMN autodebit_ga SET NOT NULL;
+ALTER TABLE packet_component ADD COLUMN packet_component_id integer 
+	DEFAULT nextval(('"packet_component_id_seq"'::text)::regclass) NOT NULL;
+	
+ALTER TABLE ONLY packet_component	
+	DROP CONSTRAINT pk_packet_component;
+	
+ALTER TABLE ONLY packet_component
+    ADD CONSTRAINT pk_packet_component PRIMARY KEY (packet_component_id);
 
-UPDATE bank_account SET autodebit_op = false WHERE autodebit_op IS NULL;
-ALTER TABLE bank_account ALTER COLUMN autodebit_op SET NOT NULL;
+ALTER TABLE ONLY packet ADD COLUMN basepacket_id integer;
 
-ALTER TABLE business_partner ADD COLUMN free boolean NOT NULL DEFAULT false;
-ALTER TABLE business_partner ADD COLUMN indicator_vat character varying(20) NOT NULL DEFAULT 'GROSS';
-ALTER TABLE business_partner ADD COLUMN exempt_vat boolean NOT NULL DEFAULT false;
-ALTER TABLE business_partner ADD UNIQUE(bp_id);
- 
-DROP TABLE billdata CASCADE;
-
-ALTER TABLE basepacket ADD COLUMN article_number integer NOT NULL DEFAULT 0;
-ALTER TABLE basepacket ALTER COLUMN article_number DROP DEFAULT;
-
-ALTER TABLE component ADD COLUMN article_number integer NOT NULL DEFAULT 0;
-ALTER TABLE component ALTER COLUMN article_number DROP DEFAULT;
-ALTER TABLE component DROP CONSTRAINT ckt_component;
-ALTER TABLE component 
-	ADD CONSTRAINT ckt_component CHECK (
-    	(0 <= min_quantity) AND 
-    	(min_quantity <= default_quantity) AND 
-    	(default_quantity <= max_quantity) AND 
-    	(include_quantity <= default_quantity) AND 
-    	(0 <= include_quantity) AND 
-    	(mod(min_quantity, increment_quantity) = 0) AND 
-    	(mod(max_quantity, increment_quantity) = 0) AND 
-    	(mod(default_quantity, increment_quantity) = 0) AND 
-    	(mod(include_quantity, increment_quantity) = 0)
-    );
-
-ALTER TABLE packet DROP COLUMN order_number;
-ALTER TABLE packet DROP COLUMN webserver_group;
-ALTER TABLE packet ADD COLUMN free boolean NOT NULL DEFAULT false;
-
-ALTER TABLE domain DROP COLUMN domain_status;
-ALTER TABLE domain DROP COLUMN domain_status_changed;
-ALTER TABLE domain DROP COLUMN domain_filed;
-ALTER TABLE domain DROP COLUMN domain_until;
-ALTER TABLE domain DROP COLUMN domain_reminder;
-ALTER TABLE domain DROP COLUMN domain_free;
-ALTER TABLE domain DROP COLUMN domain_template;
-
-CREATE TABLE price_list (
-  id serial PRIMARY KEY,                                                                   
-  name varchar(20)
-);       
-INSERT INTO price_list (name) VALUES ('Default Price List');
-
-CREATE TABLE customer_price_list_mapping (
-	customer integer REFERENCES business_partner(bp_id),
-	price_list integer REFERENCES price_list(id),
-	PRIMARY KEY (customer, price_list)
-);
-INSERT INTO customer_price_list_mapping 
-	(SELECT bp_id, (SELECT id FROM price_list WHERE name LIKE 'Default%') FROM business_partner);
-
-CREATE TABLE price (                                                                
-	id serial PRIMARY KEY,
-	article_number integer NOT NULL,
-	price decimal(10, 2) NOT NULL,
-	vat decimal(4,2) NOT NULL,
-	price_list integer REFERENCES price_list(id)                                             
-);
-
-update basecomponent set description='Benutzer, Datenbanken, E-Mail' where basecomponent_id=1;
-update basecomponent set description='%s GB Datentransfervolumen' where basecomponent_id=10;
-update basecomponent set description='%s MB Speicherplatz' where basecomponent_id=11;
-update basecomponent set description='CPU-Thread' where basecomponent_id=19;
-update basecomponent set description='%s MB Arbeitsspeicher' where basecomponent_id=20;
-update basecomponent set description='%s GB Datentransfervolumen' where basecomponent_id=10;
-
-update basepacket set basepacket_code='PAC/SW', description='Statik Web Paket', article_number=1100 where basepacket_id=19;
-update basepacket set basepacket_code='PAC/DW', description='Dynamik Web Paket', article_number=1200 where basepacket_id=20;
-update basepacket set basepacket_code='PAC/WEB', description='Web Paket', article_number=1000 where basepacket_id=21;
-update basepacket set basepacket_code='SRV/MGD', description='Managed Server', article_number=2000 where basepacket_id=22;
-
-insert into price (article_number, price, vat, price_list) values (1, 0, 0, 0, (select id from price_list where name like 'Default%')); 
-
-insert into price (article_number, price, vat, price_list) values (100, 8.00, 0.00, (select id from price_list where name like 'Default%'));
-
-insert into price (article_number, price, vat, price_list) values (1000, 2.00, 19.00, (select id from price_list where name like 'Default%'));
-insert into price (article_number, price, vat, price_list) values (1011, 1.00, 19.00, (select id from price_list where name like 'Default%'));
-insert into price (article_number, price, vat, price_list) values (1012, 1.00, 19.00, (select id from price_list where name like 'Default%'));
-insert into price (article_number, price, vat, price_list) values (1013, 1.00, 19.00, (select id from price_list where name like 'Default%'));
-insert into price (article_number, price, vat, price_list) values (1014, 2.00, 19.00, (select id from price_list where name like 'Default%'));
-insert into price (article_number, price, vat, price_list) values (1021, 10.00, 19.00, (select id from price_list where name like 'Default%'));
-
-insert into price (article_number, price, vat, price_list) values (1100, 1.00, 19.00, (select id from price_list where name like 'Default%'));
-insert into price (article_number, price, vat, price_list) values (1111, 1.00, 19.00, (select id from price_list where name like 'Default%'));
-insert into price (article_number, price, vat, price_list) values (1112, 1.00, 19.00, (select id from price_list where name like 'Default%'));
-insert into price (article_number, price, vat, price_list) values (1113, 1.00, 19.00, (select id from price_list where name like 'Default%'));
-insert into price (article_number, price, vat, price_list) values (1114, 2.00, 19.00, (select id from price_list where name like 'Default%'));
-insert into price (article_number, price, vat, price_list) values (1121, 8.00, 19.00, (select id from price_list where name like 'Default%'));
-
-insert into price (article_number, price, vat, price_list) values (1200, 1.00, 19.00, (select id from price_list where name like 'Default%'));
-insert into price (article_number, price, vat, price_list) values (1211, 1.00, 19.00, (select id from price_list where name like 'Default%'));
-insert into price (article_number, price, vat, price_list) values (1212, 1.00, 19.00, (select id from price_list where name like 'Default%'));
-insert into price (article_number, price, vat, price_list) values (1213, 1.00, 19.00, (select id from price_list where name like 'Default%'));
-insert into price (article_number, price, vat, price_list) values (1214, 2.00, 19.00, (select id from price_list where name like 'Default%'));
-insert into price (article_number, price, vat, price_list) values (1221, 8.00, 19.00, (select id from price_list where name like 'Default%'));
-
-update component set article_number=1111 where basepacket_id=19 and basecomponent_id=1;
-update component set article_number=1112 where basepacket_id=19 and basecomponent_id=11;
-update component set article_number=1113 where basepacket_id=19 and basecomponent_id=10;
-update component set article_number=1121 where basepacket_id=19 and basecomponent_id=21;
-
-update component set article_number=1211 where basepacket_id=20 and basecomponent_id=1;
-update component set article_number=1212 where basepacket_id=20 and basecomponent_id=11;
-update component set article_number=1213 where basepacket_id=20 and basecomponent_id=10;
-update component set article_number=1221 where basepacket_id=20 and basecomponent_id=21;
-
-update component set article_number=1011 where basepacket_id=21 and basecomponent_id=1;
-update component set article_number=1012 where basepacket_id=21 and basecomponent_id=11;
-update component set article_number=1013 where basepacket_id=21 and basecomponent_id=10;
-update component set article_number=1021 where basepacket_id=21 and basecomponent_id=21;
+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);
+		
\ No newline at end of file

--
Gitblit v1.9.0-SNAPSHOT