From dce141f1f23f9694f0094572eb06535991afcf8f Mon Sep 17 00:00:00 2001
From: peter <peter.hormanns@jalin.de>
Date: Tue, 23 Apr 2013 22:02:58 +0200
Subject: [PATCH] Merge branch 'master' of ssh://hsh04-source@hsh04.hostsharing.net/home/doms/source.hostsharing.net/source/hsadmin.git

---
 hsarback/database/schema.sql |  108 ++++++++++++++++++++++++++++++++++++++++++++----------
 1 files changed, 88 insertions(+), 20 deletions(-)

diff --git a/hsarback/database/schema.sql b/hsarback/database/schema.sql
index 4ffc9fd..93d89e9 100644
--- a/hsarback/database/schema.sql
+++ b/hsarback/database/schema.sql
@@ -5,14 +5,13 @@
 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,
-    autodebit_ga boolean,
-    autodebit_ar boolean,
-    autodebit_op boolean,
-    bank_customer character varying(50),
-    bank_account character varying(10),
-    bank_code character varying(8),
-    bank_name character varying(50),
-    CONSTRAINT ckt_bank_account CHECK (((((((bank_customer IS NOT NULL) AND (bank_account IS NOT NULL)) AND (bank_code IS NOT NULL)) AND (bank_name IS NOT NULL)) AND (((autodebit_ga = true) OR (autodebit_ar = true)) OR (autodebit_op = true))) OR (((autodebit_ga = false) AND (autodebit_ar = false)) AND (autodebit_op = false))))
+    autodebit_ga boolean NOT NULL DEFAULT false,
+    autodebit_ar boolean NOT NULL DEFAULT false,
+    autodebit_op boolean NOT NULL DEFAULT false,
+    bank_customer character varying(50) NOT NULL DEFAULT '',
+    bank_account character varying(10) NOT NULL DEFAULT '',
+    bank_code character varying(8) NOT NULL DEFAULT '',
+    bank_name character varying(50) NOT NULL DEFAULT ''
 );
 
 
@@ -59,6 +58,7 @@
     basepacket_id integer DEFAULT nextval(('"basepacket_basepacket_id_seq"'::text)::regclass) NOT NULL,
     basepacket_code character varying(10) NOT NULL,
     description character varying(100) NOT NULL,
+    article_number integer NOT NULL,
     sorting integer NOT NULL,
     valid boolean NOT NULL
 );
@@ -122,7 +122,8 @@
     uid_vat character varying(20),
     CONSTRAINT ckc_member_id_business CHECK (((member_id >= 10000) AND (member_id <= 99999))),
     CONSTRAINT ckc_shares_signed_business CHECK ((shares_signed >= 0)),
-    CONSTRAINT ckt_business_partner CHECK ( ( ((member_since IS NULL) AND (member_until IS NULL)) OR ((member_since IS NOT NULL) AND (member_until IS NULL)) OR ((member_since IS NOT NULL) AND (member_until IS NOT NULL) AND (member_since < member_until)) ) AND ((member_code)::text ~~ 'hsh00-%'::text))
+    CONSTRAINT ckt_business_partner CHECK ( ( ((member_since IS NULL) AND (member_until IS NULL)) OR ((member_since IS NOT NULL) AND (member_until IS NULL)) OR ((member_since IS NOT NULL) AND (member_until IS NOT NULL) AND (member_since < member_until)) ) AND ((member_code)::text ~~ 'hsh00-%'::text)),
+    UNIQUE (bp_id)
 );
 
 
@@ -144,13 +145,22 @@
 CREATE TABLE component (
     basepacket_id integer NOT NULL,
     basecomponent_id integer NOT NULL,
+    article_number integer NOT NULL,
     min_quantity integer NOT NULL,
     max_quantity integer NOT NULL,
     default_quantity integer NOT NULL,
     increment_quantity integer NOT NULL,
     include_quantity integer NOT NULL,
     admin_only boolean NOT NULL,
-    CONSTRAINT ckt_component CHECK (((((((((min_quantity <= max_quantity) AND (min_quantity <= default_quantity)) AND (default_quantity <= max_quantity)) AND (min_quantity <= include_quantity)) AND (include_quantity <= max_quantity)) AND (mod(max_quantity, increment_quantity) = 0)) AND (mod(default_quantity, increment_quantity) = 0)) AND (mod(include_quantity, increment_quantity) = 0)))
+    CONSTRAINT ckt_component CHECK (
+    	(min_quantity <= max_quantity) AND 
+    	(min_quantity <= default_quantity) AND 
+    	(default_quantity <= max_quantity) AND 
+    	(include_quantity <= max_quantity) AND 
+    	(mod(max_quantity, increment_quantity) = 0) AND 
+    	(mod(default_quantity, increment_quantity) = 0) AND 
+    	(mod(include_quantity, increment_quantity) = 0)
+    )
 );
 
 
@@ -192,6 +202,38 @@
 
 
 --
+-- Name: price_list
+--
+
+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)
+);
+
+
+--
+-- Name: price
+--
+
+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)                                             
+);
+
+--
 -- Name: database_database_id_seq; Type: SEQUENCE; Schema: public; Owner: -
 --
 
@@ -201,8 +243,6 @@
     NO MINVALUE
     CACHE 1;
 
-
-SET default_with_oids = false;
 
 --
 -- Name: database; Type: TABLE; Schema: public; Owner: -; Tablespace: 
@@ -258,17 +298,10 @@
 
 CREATE TABLE domain (
     domain_name character varying(256) NOT NULL,
-    domain_status character varying(12) NOT NULL,
-    domain_status_changed date NOT NULL,
-    domain_filed date,
     domain_since date,
-    domain_until date,
     domain_dns_master character varying(64),
     domain_id integer DEFAULT nextval('domain_domain_id_seq'::regclass) NOT NULL,
-    domain_owner integer NOT NULL,
-    domain_reminder date,
-    domain_free boolean DEFAULT false NOT NULL,
-    domain_template character varying(32)
+    domain_owner integer NOT NULL
 );
 
 
@@ -907,4 +940,39 @@
 ALTER TABLE ONLY queue_task
     ADD CONSTRAINT queue_task_user_id_fkey FOREIGN KEY (user_id) REFERENCES unixuser(unixuser_id) ON DELETE SET NULL;
 
+--
+-- domain options
+--
+
+CREATE SEQUENCE domain_option_id_seq
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+CREATE TABLE domain_option (
+    domain_option_id integer DEFAULT nextval(('"domain_option_id_seq"'::text)::regclass) NOT NULL,
+    domain_option_name character varying(50) NOT NULL
+);
+
+ALTER TABLE ONLY domain_option
+    ADD CONSTRAINT pk_domain_option PRIMARY KEY (domain_option_id);
+
+CREATE UNIQUE INDEX domain_option_name_idx ON domain_option USING btree ( domain_option_name );
+    
+CREATE TABLE domain__domain_option (
+	domain_option_id integer NOT NULL,
+	domain_id integer NOT NULL
+); 
+
+ALTER TABLE ONLY domain__domain_option
+    ADD CONSTRAINT pk_domain__domain_option PRIMARY KEY (domain_option_id, domain_id);
+
+ALTER TABLE ONLY domain__domain_option
+    ADD CONSTRAINT domain_option_id_fkey FOREIGN KEY (domain_option_id) 
+    	REFERENCES domain_option(domain_option_id) DEFERRABLE;
+
+ALTER TABLE ONLY domain__domain_option
+    ADD CONSTRAINT domain_id_fkey FOREIGN KEY (domain_id) 
+    	REFERENCES domain(domain_id) DEFERRABLE;
 

--
Gitblit v1.9.0-SNAPSHOT