Add package metadata support (email)

Signed-off-by: Magnus Granberg <zorry@gentoo.org>

1 files changed, 186 insertions, 15 deletions

diff --git a/sql/gentoo_ci_schema.sql b/sql/gentoo_ci_schema.sql
index d26e0c8..7de44ba 100644
--- a/sql/gentoo_ci_schema.sql
+++ b/sql/gentoo_ci_schema.sql
@@ -17,6 +17,32 @@ SET client_min_messages = warning;
 SET row_security = off;
 
 --
+-- Name: email_proxied; Type: TYPE; Schema: public; Owner: buildbot
+--
+
+CREATE TYPE public.email_proxied AS ENUM (
+    'no',
+    'yes',
+    'proxy'
+);
+
+
+ALTER TYPE public.email_proxied OWNER TO buildbot;
+
+--
+-- Name: email_type; Type: TYPE; Schema: public; Owner: buildbot
+--
+
+CREATE TYPE public.email_type AS ENUM (
+    'person',
+    'project',
+    'unknown'
+);
+
+
+ALTER TYPE public.email_type OWNER TO buildbot;
+
+--
 -- Name: projects_builds_status; Type: TYPE; Schema: public; Owner: buildbot
 --
 
@@ -216,27 +242,32 @@ CREATE TYPE public.workers_config_type AS ENUM (
 ALTER TYPE public.workers_config_type OWNER TO buildbot;
 
 --
--- Name: workers_type; Type: TYPE; Schema: public; Owner: buildbot
+-- Name: workers_images_type; Type: TYPE; Schema: public; Owner: buildbot
 --
 
-CREATE TYPE public.workers_type AS ENUM (
-    'log',
-    'build'
+CREATE TYPE public.workers_images_type AS ENUM (
+    'docker'
 );
 
 
-ALTER TYPE public.workers_type OWNER TO buildbot;
+ALTER TYPE public.workers_images_type OWNER TO buildbot;
 
 --
--- Name: workers_images_type; Type: TYPE; Schema: public; Owner: buildbot
+-- Name: workers_type; Type: TYPE; Schema: public; Owner: buildbot
 --
 
-CREATE TYPE public.workers_images_type AS ENUM (
-    'docker'
+CREATE TYPE public.workers_type AS ENUM (
+    'default',
+    'local',
+    'latent',
+    'chroot',
+    'docker',
+    'log',
+    'build'
 );
 
 
-ALTER TYPE public.workers_images_type OWNER TO buildbot;
+ALTER TYPE public.workers_type OWNER TO buildbot;
 
 SET default_tablespace = '';
 
@@ -255,6 +286,39 @@ CREATE TABLE public.categorys (
 ALTER TABLE public.categorys OWNER TO buildbot;
 
 --
+-- Name: emails; Type: TABLE; Schema: public; Owner: buildbot
+--
+
+CREATE TABLE public.emails (
+    id integer DEFAULT nextval('public.packages_metadata_id_seq'::regclass) NOT NULL,
+    email character varying(255)
+);
+
+
+ALTER TABLE public.emails OWNER TO buildbot;
+
+--
+-- Name: emails_id_seq; Type: SEQUENCE; Schema: public; Owner: buildbot
+--
+
+CREATE SEQUENCE public.emails_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+ALTER TABLE public.emails_id_seq OWNER TO buildbot;
+
+--
+-- Name: emails_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: buildbot
+--
+
+ALTER SEQUENCE public.emails_id_seq OWNED BY public.emails.id;
+
+
+--
 -- Name: keywords; Type: TABLE; Schema: public; Owner: buildbot
 --
 
@@ -286,9 +350,9 @@ ALTER TABLE public.migrate_version OWNER TO buildbot;
 CREATE TABLE public.nodes (
     name character varying,
     docker_host_url character varying,
-    bin_host_url character varying,
     enable boolean,
-    uuid character varying(36) NOT NULL
+    uuid character varying(36) NOT NULL,
+    bin_host_url character varying
 );
 
 
@@ -324,6 +388,64 @@ CREATE TABLE public.packages (
 ALTER TABLE public.packages OWNER TO buildbot;
 
 --
+-- Name: packages_emails_id_seq; Type: SEQUENCE; Schema: public; Owner: buildbot
+--
+
+CREATE SEQUENCE public.packages_emails_id_seq
+    AS integer
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+ALTER TABLE public.packages_emails_id_seq OWNER TO buildbot;
+
+--
+-- Name: packages_emails; Type: TABLE; Schema: public; Owner: buildbot
+--
+
+CREATE TABLE public.packages_emails (
+    id integer DEFAULT nextval('public.packages_emails_id_seq'::regclass) NOT NULL,
+    package_uuid character varying,
+    email_id integer,
+    mail_type public.email_type,
+    proxied public.email_proxied
+);
+
+
+ALTER TABLE public.packages_emails OWNER TO buildbot;
+
+--
+-- Name: packages_metadata_id_seq; Type: SEQUENCE; Schema: public; Owner: buildbot
+--
+
+CREATE SEQUENCE public.packages_metadata_id_seq
+    AS integer
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+ALTER TABLE public.packages_metadata_id_seq OWNER TO buildbot;
+
+--
+-- Name: packages_metadata; Type: TABLE; Schema: public; Owner: buildbot
+--
+
+CREATE TABLE public.packages_metadata (
+    id integer DEFAULT nextval('public.packages_metadata_id_seq'::regclass) NOT NULL,
+    package_uuid character varying(36),
+    sha256 character varying
+);
+
+
+ALTER TABLE public.packages_metadata OWNER TO buildbot;
+
+--
 -- Name: portages_makeconf; Type: TABLE; Schema: public; Owner: buildbot
 --
 
@@ -614,7 +736,7 @@ ALTER SEQUENCE public.projects_portages_makeconf_id_seq OWNED BY public.projects
 --
 
 CREATE TABLE public.projects_portages_package (
-    id bigint NOT NULL,
+    id integer NOT NULL,
     project_uuid character varying(36),
     directory public.projects_portage_package_directorys,
     package character varying(50),
@@ -669,7 +791,7 @@ ALTER SEQUENCE public.projects_repositorys_id_seq OWNED BY public.projects_repos
 --
 
 CREATE TABLE public.projects_workers (
-    id bigint NOT NULL,
+    id integer NOT NULL,
     project_uuid character varying(36),
     worker_uuid character varying(36)
 );
@@ -897,6 +1019,7 @@ CREATE TABLE public.workers_images (
 
 ALTER TABLE public.workers_images OWNER TO buildbot;
 
+
 --
 -- Name: portages_makeconf id; Type: DEFAULT; Schema: public; Owner: buildbot
 --
@@ -984,6 +1107,14 @@ ALTER TABLE ONLY public.categorys
 
 
 --
+-- Name: emails email_pkey; Type: CONSTRAINT; Schema: public; Owner: buildbot
+--
+
+ALTER TABLE ONLY public.emails
+    ADD CONSTRAINT email_pkey PRIMARY KEY (id);
+
+
+--
 -- Name: keywords keywords_pkey; Type: CONSTRAINT; Schema: public; Owner: buildbot
 --
 
@@ -1024,6 +1155,22 @@ ALTER TABLE ONLY public.nodes_workers
 
 
 --
+-- Name: packages_emails packages_emails_pkey; Type: CONSTRAINT; Schema: public; Owner: buildbot
+--
+
+ALTER TABLE ONLY public.packages_emails
+    ADD CONSTRAINT packages_emails_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: packages_metadata packages_metadata_pkey; Type: CONSTRAINT; Schema: public; Owner: buildbot
+--
+
+ALTER TABLE ONLY public.packages_metadata
+    ADD CONSTRAINT packages_metadata_pkey PRIMARY KEY (id);
+
+
+--
 -- Name: packages packages_pkey; Type: CONSTRAINT; Schema: public; Owner: buildbot
 --
 
@@ -1328,6 +1475,14 @@ ALTER TABLE ONLY public.packages
 
 
 --
+-- Name: packages_emails email_id; Type: FK CONSTRAINT; Schema: public; Owner: buildbot
+--
+
+ALTER TABLE ONLY public.packages_emails
+    ADD CONSTRAINT email_id FOREIGN KEY (email_id) REFERENCES public.emails(id);
+
+
+--
 -- Name: projects keywords_fkey; Type: FK CONSTRAINT; Schema: public; Owner: buildbot
 --
 
@@ -1360,6 +1515,22 @@ ALTER TABLE ONLY public.projects_portages_makeconf
 
 
 --
+-- Name: packages_emails package_uuid; Type: FK CONSTRAINT; Schema: public; Owner: buildbot
+--
+
+ALTER TABLE ONLY public.packages_emails
+    ADD CONSTRAINT package_uuid FOREIGN KEY (package_uuid) REFERENCES public.packages(uuid);
+
+
+--
+-- Name: packages_metadata package_uuid; Type: FK CONSTRAINT; Schema: public; Owner: buildbot
+--
+
+ALTER TABLE ONLY public.packages_metadata
+    ADD CONSTRAINT package_uuid FOREIGN KEY (package_uuid) REFERENCES public.packages(uuid) NOT VALID;
+
+
+--
 -- Name: versions packages_fkey; Type: FK CONSTRAINT; Schema: public; Owner: buildbot
 --
 
@@ -1488,10 +1659,10 @@ ALTER TABLE ONLY public.versions_metadata
 
 
 --
--- Name: SCHEMA public; Type: ACL; Schema: -; Owner: postgres
+-- Name: SCHEMA public; Type: ACL; Schema: -; Owner: buildbot
 --
 
-REVOKE ALL ON SCHEMA public FROM postgres;
+REVOKE ALL ON SCHEMA public FROM buildbot;
 GRANT ALL ON SCHEMA public TO buildbot;