path: root/doc/ddl.sql

--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: collagen; Type: DATABASE; Schema: -; Owner: postgres
--

CREATE DATABASE collagen WITH TEMPLATE = template0 ENCODING = 'UTF8';


\connect collagen

SET client_encoding = 'UTF8';
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';


SET search_path = public, pg_catalog;

--
-- Name: attachment_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE attachment_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: attachment; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE attachment (
    id integer DEFAULT nextval('attachment_id_seq'::regclass) NOT NULL,
    packageproperties_id integer NOT NULL,
    name character varying(255) NOT NULL,
    content text,
    mimetype character varying(255) DEFAULT 'text/plain'::character varying NOT NULL
);


SET default_with_oids = true;

--
-- Name: custom_fields_lists; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE custom_fields_lists (
    field_id integer,
    list_option_id integer,
    list_value character varying(250)
);


--
-- Name: file_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE file_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


SET default_with_oids = false;

--
-- Name: file; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE file (
    path text NOT NULL,
    id integer DEFAULT nextval('file_id_seq'::regclass) NOT NULL
);


--
-- 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: postgres
--

CREATE SEQUENCE package_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- Name: package; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE package (
    id integer DEFAULT nextval('package_id_seq'::regclass) NOT NULL,
    name character varying(255) NOT NULL
);


--
-- Name: packagecategory_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE packagecategory_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- Name: packagecategory; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE packagecategory (
    id integer DEFAULT nextval('packagecategory_id_seq'::regclass) NOT NULL,
    name character varying(255) NOT NULL
);


--
-- Name: packageproperties_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE packageproperties_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- Name: packageproperties; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE packageproperties (
    id integer DEFAULT nextval('packageproperties_id_seq'::regclass) NOT NULL,
    packageversion_id integer NOT NULL,
    profile_id integer NOT NULL,
    tinderbox_id integer
);


--
-- Name: packageproperties_file; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE packageproperties_file (
    packageproperties_id integer NOT NULL,
    file_id integer NOT NULL,
    hash character(32),
    size integer,
    type_id integer NOT NULL
);


--
-- Name: packageproperties_useflag; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE packageproperties_useflag (
    packageproperties_id integer NOT NULL,
    useflag_id integer NOT NULL
);


--
-- Name: packageversion_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE packageversion_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- Name: packageversion; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE packageversion (
    id integer DEFAULT nextval('packageversion_id_seq'::regclass) NOT NULL,
    category_id integer NOT NULL,
    package_id integer NOT NULL,
    version character varying(255) NOT NULL
);


--
-- Name: packageversion_packageversion; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE packageversion_packageversion (
    packageversion_id integer NOT NULL,
    dependency_id integer NOT NULL
);


--
-- 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: tinderbox_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE tinderbox_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- Name: tinderbox; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE tinderbox (
    id integer DEFAULT nextval('tinderbox_id_seq'::regclass) NOT NULL,
    ip inet
);


--
-- Name: useflag_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE useflag_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- Name: useflag; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE useflag (
    id integer DEFAULT nextval('useflag_id_seq'::regclass) NOT NULL,
    name character varying(255) NOT NULL
);


--
-- Name: PK_attachment; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY attachment
    ADD CONSTRAINT "PK_attachment" PRIMARY KEY (id);


--
-- Name: PK_file; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY file
    ADD CONSTRAINT "PK_file" PRIMARY KEY (id);


--
-- 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: postgres; Tablespace: 
--

ALTER TABLE ONLY packageproperties_file
    ADD CONSTRAINT "PK_packageproperties" PRIMARY KEY (packageproperties_id, file_id, type_id);


--
-- Name: PK_packageproperties_useflag; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY packageproperties_useflag
    ADD CONSTRAINT "PK_packageproperties_useflag" PRIMARY KEY (packageproperties_id, useflag_id);


--
-- Name: PK_packageversion_packageversion; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY packageversion_packageversion
    ADD CONSTRAINT "PK_packageversion_packageversion" PRIMARY KEY (packageversion_id, dependency_id);


--
-- Name: PK_portageprofile; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY portageprofile
    ADD CONSTRAINT "PK_portageprofile" PRIMARY KEY (id);


--
-- Name: PK_tinderbox; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY tinderbox
    ADD CONSTRAINT "PK_tinderbox" PRIMARY KEY (id);


--
-- Name: pk_package; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY package
    ADD CONSTRAINT pk_package PRIMARY KEY (id);


--
-- Name: pk_packagecategory; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY packagecategory
    ADD CONSTRAINT pk_packagecategory PRIMARY KEY (id);


--
-- Name: pk_packageproperties; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY packageproperties
    ADD CONSTRAINT pk_packageproperties PRIMARY KEY (id);


--
-- Name: pk_packageversion; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY packageversion
    ADD CONSTRAINT pk_packageversion PRIMARY KEY (id);


--
-- Name: pk_useflag; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY useflag
    ADD CONSTRAINT pk_useflag PRIMARY KEY (id);


--
-- Name: FK_attachment_packageproperties; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY attachment
    ADD CONSTRAINT "FK_attachment_packageproperties" FOREIGN KEY (packageproperties_id) REFERENCES packageproperties(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: FK_packageproperties_file_f_id; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY packageproperties_file
    ADD CONSTRAINT "FK_packageproperties_file_f_id" FOREIGN KEY (file_id) REFERENCES file(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: FK_packageproperties_file_filetype_id; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

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: postgres
--

ALTER TABLE ONLY packageproperties_file
    ADD CONSTRAINT "FK_packageproperties_file_pp_id" FOREIGN KEY (packageproperties_id) REFERENCES packageproperties(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: FK_packageproperties_packagerversion_id; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY packageproperties
    ADD CONSTRAINT "FK_packageproperties_packagerversion_id" FOREIGN KEY (packageversion_id) REFERENCES packageversion(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: FK_packageproperties_profile; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

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_tinderbox; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY packageproperties
    ADD CONSTRAINT "FK_packageproperties_tinderbox" FOREIGN KEY (tinderbox_id) REFERENCES tinderbox(id);


--
-- Name: FK_packageproperties_useflag_pp_id; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY packageproperties_useflag
    ADD CONSTRAINT "FK_packageproperties_useflag_pp_id" FOREIGN KEY (packageproperties_id) REFERENCES packageproperties(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: FK_packageproperties_useflag_u_id; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY packageproperties_useflag
    ADD CONSTRAINT "FK_packageproperties_useflag_u_id" FOREIGN KEY (useflag_id) REFERENCES useflag(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: FK_packageversion_dependency_id; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY packageversion_packageversion
    ADD CONSTRAINT "FK_packageversion_dependency_id" FOREIGN KEY (dependency_id) REFERENCES packageversion(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: FK_packageversion_package; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY packageversion
    ADD CONSTRAINT "FK_packageversion_package" FOREIGN KEY (package_id) REFERENCES package(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: FK_packageversion_packagecategory_id; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY packageversion
    ADD CONSTRAINT "FK_packageversion_packagecategory_id" FOREIGN KEY (category_id) REFERENCES packagecategory(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: FK_packageversion_packageversion_id; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY packageversion_packageversion
    ADD CONSTRAINT "FK_packageversion_packageversion_id" FOREIGN KEY (packageversion_id) REFERENCES packageversion(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- PostgreSQL database dump complete
--