Improvements to ddl

As per robbat's suggestions:
 * added portageprofile table FK to packageproperties
    this will make it possible to have non-colliding files in the same
    place in different profiles
 * changed 'type' in packageproperties_file table to FK to table
    'filetype' and conform to 3rd NF

1 files changed, 81 insertions, 4 deletions

diff --git a/doc/ddl.sql b/doc/ddl.sql
index afb0fad..39a2b93 100644
--- a/doc/ddl.sql
+++ b/doc/ddl.sql
@@ -95,6 +95,28 @@ CREATE TABLE file (
 
 
 --
+-- Name: filetype_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
+--
+
+CREATE SEQUENCE filetype_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: filetype; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
+--
+
+CREATE TABLE filetype (
+    id integer DEFAULT nextval('filetype_id_seq'::regclass) NOT NULL,
+    name character varying(20) NOT NULL
+);
+
+
+--
 -- Name: package_id_seq; Type: SEQUENCE; Schema: public; Owner: w0rm
 --
 
@@ -154,7 +176,8 @@ CREATE SEQUENCE packageproperties_seq_id
 
 CREATE TABLE packageproperties (
     id integer DEFAULT nextval('packageproperties_seq_id'::regclass) NOT NULL,
-    packageversion_id integer NOT NULL
+    packageversion_id integer NOT NULL,
+    profile_id integer NOT NULL
 );
 
 
@@ -167,7 +190,7 @@ CREATE TABLE packageproperties_file (
     file_id integer NOT NULL,
     hash character(32),
     size integer,
-    "type" character(10) NOT NULL
+    type_id integer NOT NULL
 );
 
 
@@ -215,6 +238,28 @@ CREATE TABLE packageversion_packageversion (
 
 
 --
+-- Name: portageprofile_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
+--
+
+CREATE SEQUENCE portageprofile_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: portageprofile; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
+--
+
+CREATE TABLE portageprofile (
+    id integer DEFAULT nextval('portageprofile_id_seq'::regclass) NOT NULL,
+    name character varying(255) NOT NULL
+);
+
+
+--
 -- Name: useflag; Type: TABLE; Schema: public; Owner: w0rm; Tablespace: 
 --
 
@@ -241,11 +286,19 @@ ALTER TABLE ONLY file
 
 
 --
--- Name: PK_packageproperties_file; Type: CONSTRAINT; Schema: public; Owner: w0rm; Tablespace: 
+-- Name: PK_filetype; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
+--
+
+ALTER TABLE ONLY filetype
+    ADD CONSTRAINT "PK_filetype" PRIMARY KEY (id);
+
+
+--
+-- Name: PK_packageproperties; Type: CONSTRAINT; Schema: public; Owner: w0rm; Tablespace: 
 --
 
 ALTER TABLE ONLY packageproperties_file
-    ADD CONSTRAINT "PK_packageproperties_file" PRIMARY KEY (packageproperties_id, file_id);
+    ADD CONSTRAINT "PK_packageproperties" PRIMARY KEY (packageproperties_id, file_id, type_id);
 
 
 --
@@ -265,6 +318,14 @@ ALTER TABLE ONLY packageversion_packageversion
 
 
 --
+-- Name: PK_portageprofile; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
+--
+
+ALTER TABLE ONLY portageprofile
+    ADD CONSTRAINT "PK_portageprofile" PRIMARY KEY (id);
+
+
+--
 -- Name: pk_package; Type: CONSTRAINT; Schema: public; Owner: w0rm; Tablespace: 
 --
 
@@ -321,6 +382,14 @@ ALTER TABLE ONLY packageproperties_file
 
 
 --
+-- Name: FK_packageproperties_file_filetype_id; Type: FK CONSTRAINT; Schema: public; Owner: w0rm
+--
+
+ALTER TABLE ONLY packageproperties_file
+    ADD CONSTRAINT "FK_packageproperties_file_filetype_id" FOREIGN KEY (packageproperties_id) REFERENCES filetype(id);
+
+
+--
 -- Name: FK_packageproperties_file_pp_id; Type: FK CONSTRAINT; Schema: public; Owner: w0rm
 --
 
@@ -337,6 +406,14 @@ ALTER TABLE ONLY packageproperties
 
 
 --
+-- Name: FK_packageproperties_profile; Type: FK CONSTRAINT; Schema: public; Owner: w0rm
+--
+
+ALTER TABLE ONLY packageproperties
+    ADD CONSTRAINT "FK_packageproperties_profile" FOREIGN KEY (profile_id) REFERENCES portageprofile(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
+
+
+--
 -- Name: FK_packageproperties_useflag_pp_id; Type: FK CONSTRAINT; Schema: public; Owner: w0rm
 --