builds: Fix sorting

[pbs.git] / src / database.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 15.2 (Debian 15.2-2)
-- Dumped by pg_dump version 15.2 (Debian 15.2-2)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

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

-- *not* creating schema, since initdb creates it


--
-- Name: on_update_current_timestamp_sources(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.on_update_current_timestamp_sources() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
   NEW.updated = now();
   RETURN NEW;
END;
$$;


SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: build_comments; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.build_comments (
    id integer NOT NULL,
    build_id integer NOT NULL,
    user_id integer NOT NULL,
    text text DEFAULT ''::text NOT NULL,
    score integer NOT NULL,
    created_at timestamp without time zone DEFAULT now() NOT NULL,
    deleted boolean DEFAULT false NOT NULL
);


--
-- Name: build_groups; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.build_groups (
    id integer NOT NULL,
    uuid uuid DEFAULT gen_random_uuid() NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);


--
-- Name: build_groups_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.build_groups_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: build_groups_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.build_groups_id_seq OWNED BY public.build_groups.id;


--
-- Name: build_packages; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.build_packages (
    build_id integer NOT NULL,
    package_id integer NOT NULL,
    job_id integer NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);


--
-- Name: build_watchers; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.build_watchers (
    build_id integer NOT NULL,
    user_id integer NOT NULL,
    added_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    deleted_at timestamp without time zone
);


--
-- Name: builder_stats; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.builder_stats (
    builder_id integer NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    cpu_user double precision NOT NULL,
    cpu_nice double precision NOT NULL,
    cpu_system double precision NOT NULL,
    cpu_idle double precision NOT NULL,
    cpu_iowait double precision NOT NULL,
    cpu_irq double precision NOT NULL,
    cpu_softirq double precision NOT NULL,
    cpu_steal double precision NOT NULL,
    cpu_guest double precision NOT NULL,
    cpu_guest_nice double precision NOT NULL,
    loadavg1 double precision NOT NULL,
    loadavg5 double precision NOT NULL,
    loadavg15 double precision NOT NULL,
    mem_total bigint NOT NULL,
    mem_available bigint NOT NULL,
    mem_used bigint NOT NULL,
    mem_free bigint NOT NULL,
    mem_active bigint NOT NULL,
    mem_inactive bigint NOT NULL,
    mem_buffers bigint NOT NULL,
    mem_cached bigint NOT NULL,
    mem_shared bigint NOT NULL,
    swap_total bigint NOT NULL,
    swap_used bigint NOT NULL,
    swap_free bigint NOT NULL
);


--
-- Name: builders; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.builders (
    id integer NOT NULL,
    name text NOT NULL,
    description text,
    enabled boolean DEFAULT false NOT NULL,
    deleted boolean DEFAULT false NOT NULL,
    loadavg text DEFAULT '0'::character varying NOT NULL,
    testmode boolean DEFAULT true NOT NULL,
    max_jobs bigint DEFAULT (1)::bigint NOT NULL,
    pakfire_version text,
    os_name text,
    cpu_model text,
    cpu_count integer DEFAULT 1 NOT NULL,
    host_key_id text,
    time_created timestamp without time zone DEFAULT now() NOT NULL,
    updated_at timestamp without time zone,
    time_keepalive timestamp without time zone,
    online_until timestamp without time zone,
    cpu_arch text,
    instance_id text,
    instance_type text
);


--
-- Name: builders_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.builders_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: builders_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.builders_id_seq OWNED BY public.builders.id;


--
-- Name: builds; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.builds (
    id integer NOT NULL,
    uuid uuid DEFAULT gen_random_uuid() NOT NULL,
    pkg_id integer NOT NULL,
    severity text,
    message text,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    build_repo_id integer NOT NULL,
    owner_id integer,
    priority integer DEFAULT 0 NOT NULL,
    bug_ids integer[] DEFAULT ARRAY[]::integer[] NOT NULL,
    finished_at timestamp without time zone,
    failed boolean DEFAULT false NOT NULL,
    deleted_at timestamp without time zone,
    deleted_by integer,
    build_group_id integer,
    deprecating_build_id integer,
    deprecated_at timestamp without time zone,
    deprecated_by integer
);


--
-- Name: builds_bugs_updates; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.builds_bugs_updates (
    id integer NOT NULL,
    bug_id integer NOT NULL,
    status text,
    resolution text,
    comment text,
    "time" timestamp without time zone NOT NULL,
    error boolean DEFAULT false NOT NULL,
    error_msg text
);


--
-- Name: builds_bugs_updates_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.builds_bugs_updates_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: builds_bugs_updates_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.builds_bugs_updates_id_seq OWNED BY public.builds_bugs_updates.id;


--
-- Name: builds_comments_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.builds_comments_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: builds_comments_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.builds_comments_id_seq OWNED BY public.build_comments.id;


--
-- Name: builds_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.builds_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: builds_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.builds_id_seq OWNED BY public.builds.id;


--
-- Name: distributions; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.distributions (
    id integer NOT NULL,
    name text NOT NULL,
    distro_id text NOT NULL,
    slogan text DEFAULT ''::text NOT NULL,
    description text DEFAULT ''::text NOT NULL,
    vendor text DEFAULT ''::text NOT NULL,
    contact text DEFAULT ''::text NOT NULL,
    version_id integer NOT NULL,
    deleted boolean DEFAULT false NOT NULL,
    arches text[] DEFAULT ARRAY[]::text[] NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    custom_config text DEFAULT ''::text NOT NULL,
    codename text DEFAULT ''::text NOT NULL
);


--
-- Name: distributions_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.distributions_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: distributions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.distributions_id_seq OWNED BY public.distributions.id;


--
-- Name: images_types; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.images_types (
    id integer NOT NULL,
    type text NOT NULL
);


--
-- Name: images_types_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.images_types_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: images_types_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.images_types_id_seq OWNED BY public.images_types.id;


--
-- Name: jobs; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.jobs (
    id integer NOT NULL,
    uuid uuid DEFAULT gen_random_uuid() NOT NULL,
    build_id integer NOT NULL,
    arch text NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    started_at timestamp without time zone,
    finished_at timestamp without time zone,
    builder_id integer,
    message text,
    test boolean DEFAULT true NOT NULL,
    superseeded_by integer,
    depcheck_succeeded boolean,
    depcheck_performed_at timestamp without time zone,
    failed boolean DEFAULT false NOT NULL,
    log_path text,
    log_size bigint,
    log_digest_blake2s bytea,
    deleted_at timestamp without time zone,
    deleted_by integer,
    aborted boolean DEFAULT false NOT NULL,
    aborted_by integer
);


--
-- Name: job_queue; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.job_queue AS
 SELECT jobs.id AS job_id,
    rank() OVER (ORDER BY (NOT jobs.test), builds.priority DESC, jobs.created_at) AS rank,
    jobs.arch
   FROM (public.jobs
     LEFT JOIN public.builds ON ((jobs.build_id = builds.id)))
  WHERE ((jobs.deleted_at IS NULL) AND (jobs.started_at IS NULL) AND (jobs.finished_at IS NULL) AND (jobs.depcheck_succeeded IS TRUE));


--
-- Name: jobs_buildroots; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.jobs_buildroots (
    job_id integer NOT NULL,
    pkg_uuid uuid NOT NULL,
    pkg_name text NOT NULL
);


--
-- Name: jobs_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.jobs_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: jobs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.jobs_id_seq OWNED BY public.jobs.id;


--
-- Name: jobs_packages; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.jobs_packages (
    id integer NOT NULL,
    job_id integer NOT NULL,
    pkg_id integer NOT NULL
);


--
-- Name: jobs_packages_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.jobs_packages_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: jobs_packages_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.jobs_packages_id_seq OWNED BY public.jobs_packages.id;


--
-- Name: keys; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.keys (
    id integer NOT NULL,
    fingerprint text NOT NULL,
    uid text NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    expires_at timestamp without time zone,
    public_key text NOT NULL,
    secret_key text NOT NULL,
    name text NOT NULL,
    email text NOT NULL,
    deleted boolean DEFAULT false NOT NULL,
    algo text NOT NULL,
    length integer NOT NULL,
    parent_key_id integer,
    revoked_at timestamp without time zone
);


--
-- Name: keys_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.keys_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: keys_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.keys_id_seq OWNED BY public.keys.id;


--
-- Name: logfiles; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.logfiles (
    id integer NOT NULL,
    job_id integer NOT NULL,
    path text NOT NULL,
    filesize bigint NOT NULL,
    hash_sha512 text NOT NULL
);


--
-- Name: logfiles_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.logfiles_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: logfiles_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.logfiles_id_seq OWNED BY public.logfiles.id;


--
-- Name: messages; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.messages (
    id integer NOT NULL,
    message text NOT NULL,
    queued_at timestamp without time zone DEFAULT now() NOT NULL,
    sent_at timestamp without time zone,
    priority integer DEFAULT 0 NOT NULL,
    error_message text
);


--
-- Name: messages_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.messages_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: messages_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.messages_id_seq OWNED BY public.messages.id;


--
-- Name: mirrors; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.mirrors (
    id integer NOT NULL,
    hostname text NOT NULL,
    path text NOT NULL,
    owner text,
    contact text,
    deleted boolean DEFAULT false NOT NULL,
    supports_https boolean DEFAULT false NOT NULL
);


--
-- Name: mirrors_checks; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.mirrors_checks (
    id integer NOT NULL,
    mirror_id integer NOT NULL,
    "timestamp" timestamp without time zone DEFAULT now() NOT NULL,
    response_time double precision,
    http_status integer,
    last_sync_at timestamp without time zone,
    status text DEFAULT 'OK'::text NOT NULL
);


--
-- Name: mirrors_checks_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.mirrors_checks_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: mirrors_checks_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.mirrors_checks_id_seq OWNED BY public.mirrors_checks.id;


--
-- Name: mirrors_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.mirrors_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: mirrors_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.mirrors_id_seq OWNED BY public.mirrors.id;


--
-- Name: packages; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.packages (
    id integer NOT NULL,
    name text NOT NULL,
    evr text NOT NULL,
    arch text NOT NULL,
    groups text NOT NULL,
    packager text,
    license text NOT NULL,
    url text NOT NULL,
    summary text NOT NULL,
    description text NOT NULL,
    size bigint NOT NULL,
    supported_arches text,
    uuid uuid NOT NULL,
    commit_id integer,
    build_id text,
    build_host text NOT NULL,
    build_time timestamp without time zone NOT NULL,
    path text,
    filesize bigint NOT NULL,
    prerequires text[] DEFAULT ARRAY[]::text[] NOT NULL,
    requires text[] DEFAULT ARRAY[]::text[] NOT NULL,
    provides text[] DEFAULT ARRAY[]::text[] NOT NULL,
    obsoletes text[] DEFAULT ARRAY[]::text[] NOT NULL,
    conflicts text[] DEFAULT ARRAY[]::text[] NOT NULL,
    recommends text[] DEFAULT ARRAY[]::text[] NOT NULL,
    suggests text[] DEFAULT ARRAY[]::text[] NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    digest_type text NOT NULL,
    digest bytea NOT NULL,
    deleted_at timestamp without time zone,
    deleted_by integer,
    distro_id integer NOT NULL
);


--
-- Name: package_estimated_build_times; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.package_estimated_build_times AS
 SELECT packages.name,
    jobs.arch,
    avg((jobs.finished_at - jobs.started_at)) AS build_time
   FROM ((public.jobs
     LEFT JOIN public.builds ON ((jobs.build_id = builds.id)))
     LEFT JOIN public.packages ON ((builds.pkg_id = packages.id)))
  WHERE ((jobs.deleted_at IS NULL) AND (jobs.started_at IS NOT NULL) AND (jobs.finished_at IS NOT NULL) AND (jobs.failed IS FALSE) AND (jobs.test IS FALSE))
  GROUP BY packages.name, jobs.arch;


--
-- Name: VIEW package_estimated_build_times; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON VIEW public.package_estimated_build_times IS 'Should add this later: AND jobs.time_finished >= (CURRENT_TIMESTAMP - ''180 days''::interval)';


--
-- Name: package_files; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.package_files (
    pkg_id integer NOT NULL,
    path text NOT NULL,
    size bigint NOT NULL,
    config boolean DEFAULT false NOT NULL,
    mode integer NOT NULL,
    uname text NOT NULL,
    gname text NOT NULL,
    ctime timestamp without time zone NOT NULL,
    mtime timestamp without time zone NOT NULL,
    digest_sha2_512 bytea,
    digest_sha2_256 bytea,
    digest_blake2b512 bytea,
    digest_blake2s256 bytea,
    digest_sha3_512 bytea,
    digest_sha3_256 bytea,
    mimetype text,
    capabilities text
);


--
-- Name: package_search_index_generator; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.package_search_index_generator AS
 WITH p AS (
         SELECT DISTINCT ON (packages.name) packages.id,
            packages.name,
            packages.summary,
            packages.description
           FROM public.packages
          WHERE ((packages.deleted_at IS NULL) AND (packages.arch = 'src'::text))
          ORDER BY packages.name, packages.created_at DESC
        )
 SELECT p.id AS package_id,
    ((setweight(to_tsvector('simple'::regconfig, p.name), 'A'::"char") || setweight(to_tsvector('english'::regconfig, p.summary), 'B'::"char")) || setweight(to_tsvector('english'::regconfig, p.description), 'C'::"char")) AS document
   FROM p;


--
-- Name: package_search_index; Type: MATERIALIZED VIEW; Schema: public; Owner: -
--

CREATE MATERIALIZED VIEW public.package_search_index AS
 SELECT package_search_index_generator.package_id,
    package_search_index_generator.document
   FROM public.package_search_index_generator
  WITH NO DATA;


--
-- Name: packages_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.packages_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: packages_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.packages_id_seq OWNED BY public.packages.id;


--
-- Name: relation_sizes; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.relation_sizes AS
 SELECT c.relname AS relation,
    pg_size_pretty(pg_relation_size((c.oid)::regclass)) AS size
   FROM (pg_class c
     LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
  WHERE (n.nspname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name]))
  ORDER BY (pg_relation_size((c.oid)::regclass)) DESC;


--
-- Name: repo_builds; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.repo_builds (
    repo_id integer NOT NULL,
    build_id integer NOT NULL,
    added_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    added_by integer,
    removed_at timestamp without time zone,
    removed_by integer
);


--
-- Name: repositories; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.repositories (
    id integer NOT NULL,
    name text NOT NULL,
    slug text,
    description text DEFAULT ''::text NOT NULL,
    distro_id integer NOT NULL,
    parent_id integer,
    key_id integer NOT NULL,
    mirrored boolean DEFAULT false NOT NULL,
    updated_at timestamp without time zone,
    deleted boolean DEFAULT false NOT NULL,
    priority integer,
    owner_id integer,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    listed boolean DEFAULT true NOT NULL
);


--
-- Name: repositories_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.repositories_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: repositories_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.repositories_id_seq OWNED BY public.repositories.id;


--
-- Name: repository_builds; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.repository_builds (
    id integer NOT NULL,
    repo_id integer NOT NULL,
    build_id bigint NOT NULL,
    added_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    added_by integer,
    removed_at timestamp without time zone,
    removed_by integer
);


--
-- Name: repository_builds_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.repository_builds_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: repository_builds_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.repository_builds_id_seq OWNED BY public.repository_builds.id;


--
-- Name: sessions; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.sessions (
    id integer NOT NULL,
    session_id text NOT NULL,
    created_at timestamp without time zone DEFAULT now() NOT NULL,
    valid_until timestamp without time zone DEFAULT (now() + '7 days'::interval) NOT NULL,
    user_id integer NOT NULL,
    address inet,
    user_agent text
);


--
-- Name: sessions_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.sessions_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: sessions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.sessions_id_seq OWNED BY public.sessions.id;


--
-- Name: settings; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.settings (
    k text NOT NULL,
    v text NOT NULL
);


--
-- Name: source_commits; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.source_commits (
    id integer NOT NULL,
    source_id integer NOT NULL,
    revision text NOT NULL,
    author text NOT NULL,
    committer text NOT NULL,
    subject text NOT NULL,
    body text NOT NULL,
    date timestamp without time zone NOT NULL,
    state text DEFAULT 'pending'::text NOT NULL,
    imported_at timestamp without time zone DEFAULT now() NOT NULL
);


--
-- Name: sources; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.sources (
    id integer NOT NULL,
    name text NOT NULL,
    slug text NOT NULL,
    url text NOT NULL,
    gitweb text,
    revision text NOT NULL,
    branch text NOT NULL,
    last_updated_at timestamp without time zone,
    repo_id integer NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    created_by integer NOT NULL,
    deleted_at timestamp without time zone,
    deleted_by integer
);


--
-- Name: sources_commits_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.sources_commits_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: sources_commits_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.sources_commits_id_seq OWNED BY public.source_commits.id;


--
-- Name: sources_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.sources_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: sources_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.sources_id_seq OWNED BY public.sources.id;


--
-- Name: uploads; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.uploads (
    id integer NOT NULL,
    uuid uuid DEFAULT gen_random_uuid() NOT NULL,
    user_id integer,
    builder_id integer,
    filename text NOT NULL,
    path text NOT NULL,
    size bigint NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    expires_at timestamp without time zone DEFAULT (CURRENT_TIMESTAMP + '24:00:00'::interval) NOT NULL
);


--
-- Name: uploads_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.uploads_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: uploads_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.uploads_id_seq OWNED BY public.uploads.id;


--
-- Name: user_disk_usages; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.user_disk_usages AS
 WITH objects AS (
         SELECT uploads.user_id,
            uploads.size
           FROM public.uploads
          WHERE (uploads.expires_at > CURRENT_TIMESTAMP)
        UNION ALL
         SELECT builds.owner_id,
            packages.size
           FROM (public.builds
             LEFT JOIN public.packages ON ((builds.pkg_id = packages.id)))
          WHERE ((builds.deleted_at IS NULL) AND (builds.owner_id IS NOT NULL))
        UNION ALL
         SELECT builds.owner_id,
            jobs.log_size
           FROM (public.jobs
             LEFT JOIN public.builds ON ((builds.id = jobs.build_id)))
          WHERE ((builds.deleted_at IS NULL) AND (jobs.deleted_at IS NULL) AND (builds.owner_id IS NOT NULL) AND (jobs.log_size IS NOT NULL))
        )
 SELECT objects.user_id,
    sum(objects.size) AS disk_usage
   FROM objects
  GROUP BY objects.user_id;


--
-- Name: users; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.users (
    id integer NOT NULL,
    name text NOT NULL,
    deleted boolean DEFAULT false NOT NULL,
    registered_at timestamp without time zone DEFAULT now() NOT NULL,
    admin boolean DEFAULT false NOT NULL,
    quota bigint,
    perms text[] DEFAULT ARRAY[]::text[] NOT NULL,
    _attrs bytea
);


--
-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.users_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.users_id_seq OWNED BY public.users.id;


--
-- Name: build_comments id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.build_comments ALTER COLUMN id SET DEFAULT nextval('public.builds_comments_id_seq'::regclass);


--
-- Name: build_groups id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.build_groups ALTER COLUMN id SET DEFAULT nextval('public.build_groups_id_seq'::regclass);


--
-- Name: builders id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.builders ALTER COLUMN id SET DEFAULT nextval('public.builders_id_seq'::regclass);


--
-- Name: builds id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.builds ALTER COLUMN id SET DEFAULT nextval('public.builds_id_seq'::regclass);


--
-- Name: builds_bugs_updates id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.builds_bugs_updates ALTER COLUMN id SET DEFAULT nextval('public.builds_bugs_updates_id_seq'::regclass);


--
-- Name: distributions id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.distributions ALTER COLUMN id SET DEFAULT nextval('public.distributions_id_seq'::regclass);


--
-- Name: images_types id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.images_types ALTER COLUMN id SET DEFAULT nextval('public.images_types_id_seq'::regclass);


--
-- Name: jobs id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.jobs ALTER COLUMN id SET DEFAULT nextval('public.jobs_id_seq'::regclass);


--
-- Name: jobs_packages id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.jobs_packages ALTER COLUMN id SET DEFAULT nextval('public.jobs_packages_id_seq'::regclass);


--
-- Name: keys id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.keys ALTER COLUMN id SET DEFAULT nextval('public.keys_id_seq'::regclass);


--
-- Name: logfiles id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.logfiles ALTER COLUMN id SET DEFAULT nextval('public.logfiles_id_seq'::regclass);


--
-- Name: messages id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.messages ALTER COLUMN id SET DEFAULT nextval('public.messages_id_seq'::regclass);


--
-- Name: mirrors id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.mirrors ALTER COLUMN id SET DEFAULT nextval('public.mirrors_id_seq'::regclass);


--
-- Name: mirrors_checks id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.mirrors_checks ALTER COLUMN id SET DEFAULT nextval('public.mirrors_checks_id_seq'::regclass);


--
-- Name: packages id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.packages ALTER COLUMN id SET DEFAULT nextval('public.packages_id_seq'::regclass);


--
-- Name: repositories id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.repositories ALTER COLUMN id SET DEFAULT nextval('public.repositories_id_seq'::regclass);


--
-- Name: repository_builds id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.repository_builds ALTER COLUMN id SET DEFAULT nextval('public.repository_builds_id_seq'::regclass);


--
-- Name: sessions id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sessions ALTER COLUMN id SET DEFAULT nextval('public.sessions_id_seq'::regclass);


--
-- Name: source_commits id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.source_commits ALTER COLUMN id SET DEFAULT nextval('public.sources_commits_id_seq'::regclass);


--
-- Name: sources id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sources ALTER COLUMN id SET DEFAULT nextval('public.sources_id_seq'::regclass);


--
-- Name: uploads id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.uploads ALTER COLUMN id SET DEFAULT nextval('public.uploads_id_seq'::regclass);


--
-- Name: users id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.users ALTER COLUMN id SET DEFAULT nextval('public.users_id_seq'::regclass);


--
-- Name: build_comments build_comments_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.build_comments
    ADD CONSTRAINT build_comments_pkey PRIMARY KEY (id);


--
-- Name: build_groups build_groups_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.build_groups
    ADD CONSTRAINT build_groups_pkey PRIMARY KEY (id);


--
-- Name: builds builds_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.builds
    ADD CONSTRAINT builds_pkey PRIMARY KEY (id);


--
-- Name: distributions distributions_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.distributions
    ADD CONSTRAINT distributions_pkey PRIMARY KEY (id);


--
-- Name: builders idx_2197954_primary; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.builders
    ADD CONSTRAINT idx_2197954_primary PRIMARY KEY (id);


--
-- Name: builds_bugs_updates idx_2198008_primary; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.builds_bugs_updates
    ADD CONSTRAINT idx_2198008_primary PRIMARY KEY (id);


--
-- Name: images_types idx_2198057_primary; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.images_types
    ADD CONSTRAINT idx_2198057_primary PRIMARY KEY (id);


--
-- Name: jobs_packages idx_2198085_primary; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.jobs_packages
    ADD CONSTRAINT idx_2198085_primary PRIMARY KEY (id);


--
-- Name: logfiles idx_2198109_primary; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.logfiles
    ADD CONSTRAINT idx_2198109_primary PRIMARY KEY (id);


--
-- Name: mirrors idx_2198115_primary; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.mirrors
    ADD CONSTRAINT idx_2198115_primary PRIMARY KEY (id);


--
-- Name: users idx_2198244_primary; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT idx_2198244_primary PRIMARY KEY (id);


--
-- Name: jobs_packages jobs_packages_unique; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.jobs_packages
    ADD CONSTRAINT jobs_packages_unique UNIQUE (job_id, pkg_id);


--
-- Name: jobs jobs_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.jobs
    ADD CONSTRAINT jobs_pkey PRIMARY KEY (id);


--
-- Name: keys keys_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.keys
    ADD CONSTRAINT keys_pkey PRIMARY KEY (id);


--
-- Name: messages messages_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.messages
    ADD CONSTRAINT messages_pkey PRIMARY KEY (id);


--
-- Name: mirrors_checks mirrors_checks_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.mirrors_checks
    ADD CONSTRAINT mirrors_checks_pkey PRIMARY KEY (id);


--
-- Name: packages packages_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.packages
    ADD CONSTRAINT packages_pkey PRIMARY KEY (id);


--
-- Name: repositories repositories_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.repositories
    ADD CONSTRAINT repositories_pkey PRIMARY KEY (id);


--
-- Name: repository_builds repository_builds_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.repository_builds
    ADD CONSTRAINT repository_builds_pkey PRIMARY KEY (id);


--
-- Name: sessions sessions_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sessions
    ADD CONSTRAINT sessions_pkey PRIMARY KEY (id);


--
-- Name: sessions sessions_session_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sessions
    ADD CONSTRAINT sessions_session_id_key UNIQUE (session_id);


--
-- Name: source_commits source_commits_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.source_commits
    ADD CONSTRAINT source_commits_pkey PRIMARY KEY (id);


--
-- Name: sources sources_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sources
    ADD CONSTRAINT sources_pkey PRIMARY KEY (id);


--
-- Name: uploads uploads_id; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.uploads
    ADD CONSTRAINT uploads_id PRIMARY KEY (id);


--
-- Name: build_comments_build_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX build_comments_build_id ON public.build_comments USING btree (build_id) WHERE (deleted IS FALSE);


--
-- Name: build_comments_created_at; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX build_comments_created_at ON public.build_comments USING btree (created_at) WHERE (deleted IS FALSE);


--
-- Name: build_comments_user_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX build_comments_user_id ON public.build_comments USING btree (user_id) WHERE (deleted IS FALSE);


--
-- Name: build_groups_uuid; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX build_groups_uuid ON public.build_groups USING btree (uuid);


--
-- Name: build_packages_build_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX build_packages_build_id ON public.build_packages USING btree (build_id);


--
-- Name: build_watchers_unique; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX build_watchers_unique ON public.build_watchers USING btree (build_id, user_id) WHERE (deleted_at IS NULL);


--
-- Name: builders_name; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX builders_name ON public.builders USING btree (name) WHERE (deleted IS FALSE);


--
-- Name: builds_build_group_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX builds_build_group_id ON public.builds USING btree (build_group_id) WHERE (deleted_at IS NULL);


--
-- Name: builds_created_at; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX builds_created_at ON public.builds USING btree (created_at DESC) WHERE (deleted_at IS NULL);


--
-- Name: builds_deleted; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX builds_deleted ON public.builds USING btree (deleted_at) WHERE (deleted_at IS NOT NULL);


--
-- Name: builds_deprecating_build_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX builds_deprecating_build_id ON public.builds USING btree (deprecating_build_id) WHERE ((deleted_at IS NULL) AND (deprecated_at IS NOT NULL));


--
-- Name: builds_pkg_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX builds_pkg_id ON public.builds USING btree (pkg_id) WHERE (deleted_at IS NULL);


--
-- Name: builds_uuid; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX builds_uuid ON public.builds USING btree (uuid) WHERE (deleted_at IS NULL);


--
-- Name: distributions_unique; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX distributions_unique ON public.distributions USING btree (distro_id, version_id) WHERE (deleted IS FALSE);


--
-- Name: idx_2198199_k; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX idx_2198199_k ON public.settings USING btree (k);


--
-- Name: idx_2198244_name; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX idx_2198244_name ON public.users USING btree (name);


--
-- Name: jobs_arch; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX jobs_arch ON public.jobs USING btree (arch);


--
-- Name: jobs_build_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX jobs_build_id ON public.jobs USING btree (build_id) WHERE (deleted_at IS NULL);


--
-- Name: jobs_buildroots_job_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX jobs_buildroots_job_id ON public.jobs_buildroots USING btree (job_id);

ALTER TABLE public.jobs_buildroots CLUSTER ON jobs_buildroots_job_id;


--
-- Name: jobs_buildroots_pkg_uuid; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX jobs_buildroots_pkg_uuid ON public.jobs_buildroots USING btree (pkg_uuid);


--
-- Name: jobs_finished_at; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX jobs_finished_at ON public.jobs USING btree (finished_at DESC) WHERE (finished_at IS NOT NULL);


--
-- Name: jobs_pending; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX jobs_pending ON public.jobs USING btree (id) WHERE ((deleted_at IS NULL) AND (started_at IS NULL) AND (finished_at IS NULL) AND (depcheck_succeeded IS TRUE));


--
-- Name: jobs_running; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX jobs_running ON public.jobs USING btree (started_at) WHERE ((started_at IS NOT NULL) AND (finished_at IS NULL));


--
-- Name: jobs_uuid; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX jobs_uuid ON public.jobs USING btree (uuid) WHERE (deleted_at IS NULL);


--
-- Name: keys_fingerprint; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX keys_fingerprint ON public.keys USING btree (fingerprint) WHERE (deleted IS FALSE);


--
-- Name: messages_queued; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX messages_queued ON public.messages USING btree (priority DESC, queued_at) WHERE (sent_at IS NULL);


--
-- Name: mirrors_checks_sort; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX mirrors_checks_sort ON public.mirrors_checks USING btree (mirror_id, "timestamp");

ALTER TABLE public.mirrors_checks CLUSTER ON mirrors_checks_sort;


--
-- Name: package_files_path; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX package_files_path ON public.package_files USING btree (path);


--
-- Name: package_files_pkg_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX package_files_pkg_id ON public.package_files USING btree (pkg_id);


--
-- Name: package_search_index_unique; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX package_search_index_unique ON public.package_search_index USING btree (package_id);


--
-- Name: packages_name; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX packages_name ON public.packages USING btree (name);


--
-- Name: repo_builds_build_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX repo_builds_build_id ON public.repo_builds USING btree (build_id);


--
-- Name: repo_builds_repo_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX repo_builds_repo_id ON public.repo_builds USING btree (repo_id);


--
-- Name: repo_builds_unique; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX repo_builds_unique ON public.repo_builds USING btree (repo_id, build_id) WHERE ((added_at IS NOT NULL) AND (removed_at IS NULL));


--
-- Name: repositories_unique; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX repositories_unique ON public.repositories USING btree (owner_id, distro_id, slug) WHERE (deleted IS FALSE);


--
-- Name: repository_builds_build_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX repository_builds_build_id ON public.repository_builds USING btree (build_id);


--
-- Name: repository_builds_repo_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX repository_builds_repo_id ON public.repository_builds USING btree (repo_id) WHERE (removed_at IS NULL);


--
-- Name: repository_builds_unique; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX repository_builds_unique ON public.repository_builds USING btree (repo_id, build_id) WHERE (removed_at IS NULL);


--
-- Name: source_commits_unique; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX source_commits_unique ON public.source_commits USING btree (source_id, revision);


--
-- Name: sources_repo_id; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX sources_repo_id ON public.sources USING btree (repo_id) WHERE (deleted_at IS NULL);


--
-- Name: sources_slug; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX sources_slug ON public.sources USING btree (slug) WHERE (deleted_at IS NULL);


--
-- Name: uploads_uuid; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX uploads_uuid ON public.uploads USING btree (uuid);


--
-- Name: sources on_update_current_timestamp; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER on_update_current_timestamp BEFORE UPDATE ON public.sources FOR EACH ROW EXECUTE FUNCTION public.on_update_current_timestamp_sources();


--
-- Name: build_packages build_packages_build_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.build_packages
    ADD CONSTRAINT build_packages_build_id FOREIGN KEY (build_id) REFERENCES public.builds(id);


--
-- Name: build_packages build_packages_job_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.build_packages
    ADD CONSTRAINT build_packages_job_id FOREIGN KEY (job_id) REFERENCES public.jobs(id);


--
-- Name: build_packages build_packages_package_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.build_packages
    ADD CONSTRAINT build_packages_package_id FOREIGN KEY (package_id) REFERENCES public.packages(id);


--
-- Name: build_watchers build_watchers_build_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.build_watchers
    ADD CONSTRAINT build_watchers_build_id FOREIGN KEY (build_id) REFERENCES public.builds(id);


--
-- Name: build_watchers build_watchers_user_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.build_watchers
    ADD CONSTRAINT build_watchers_user_id FOREIGN KEY (user_id) REFERENCES public.users(id);


--
-- Name: builder_stats builder_stats_builder_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.builder_stats
    ADD CONSTRAINT builder_stats_builder_id FOREIGN KEY (builder_id) REFERENCES public.builders(id);


--
-- Name: builds builds_build_group_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.builds
    ADD CONSTRAINT builds_build_group_id FOREIGN KEY (build_group_id) REFERENCES public.build_groups(id);


--
-- Name: builds builds_build_repo_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.builds
    ADD CONSTRAINT builds_build_repo_id FOREIGN KEY (build_repo_id) REFERENCES public.repositories(id);


--
-- Name: build_comments builds_comments_build_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.build_comments
    ADD CONSTRAINT builds_comments_build_id FOREIGN KEY (build_id) REFERENCES public.builds(id);


--
-- Name: build_comments builds_comments_user_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.build_comments
    ADD CONSTRAINT builds_comments_user_id FOREIGN KEY (user_id) REFERENCES public.users(id);


--
-- Name: builds builds_deleted_by; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.builds
    ADD CONSTRAINT builds_deleted_by FOREIGN KEY (deleted_by) REFERENCES public.users(id);


--
-- Name: builds builds_deprecated_by; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.builds
    ADD CONSTRAINT builds_deprecated_by FOREIGN KEY (deprecated_by) REFERENCES public.users(id);


--
-- Name: builds builds_deprecating_build_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.builds
    ADD CONSTRAINT builds_deprecating_build_id FOREIGN KEY (deprecating_build_id) REFERENCES public.builds(id);


--
-- Name: builds builds_owner_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.builds
    ADD CONSTRAINT builds_owner_id FOREIGN KEY (owner_id) REFERENCES public.users(id);


--
-- Name: builds builds_pkg_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.builds
    ADD CONSTRAINT builds_pkg_id FOREIGN KEY (pkg_id) REFERENCES public.packages(id);


--
-- Name: jobs jobs_aborted_by; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.jobs
    ADD CONSTRAINT jobs_aborted_by FOREIGN KEY (aborted_by) REFERENCES public.users(id);


--
-- Name: jobs jobs_build_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.jobs
    ADD CONSTRAINT jobs_build_id FOREIGN KEY (build_id) REFERENCES public.builds(id);


--
-- Name: jobs jobs_builder_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.jobs
    ADD CONSTRAINT jobs_builder_id FOREIGN KEY (builder_id) REFERENCES public.builders(id);


--
-- Name: jobs_buildroots jobs_buildroots_job_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.jobs_buildroots
    ADD CONSTRAINT jobs_buildroots_job_id FOREIGN KEY (job_id) REFERENCES public.jobs(id);


--
-- Name: jobs jobs_deleted_by; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.jobs
    ADD CONSTRAINT jobs_deleted_by FOREIGN KEY (deleted_by) REFERENCES public.users(id);


--
-- Name: jobs_packages jobs_packaged_job_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.jobs_packages
    ADD CONSTRAINT jobs_packaged_job_id FOREIGN KEY (job_id) REFERENCES public.jobs(id);


--
-- Name: jobs_packages jobs_packages_pkg_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.jobs_packages
    ADD CONSTRAINT jobs_packages_pkg_id FOREIGN KEY (pkg_id) REFERENCES public.packages(id);


--
-- Name: jobs jobs_superseeded_by; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.jobs
    ADD CONSTRAINT jobs_superseeded_by FOREIGN KEY (superseeded_by) REFERENCES public.jobs(id);


--
-- Name: keys keys_parent_key_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.keys
    ADD CONSTRAINT keys_parent_key_id FOREIGN KEY (parent_key_id) REFERENCES public.keys(id);


--
-- Name: logfiles logfiles_job_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.logfiles
    ADD CONSTRAINT logfiles_job_id FOREIGN KEY (job_id) REFERENCES public.jobs(id);


--
-- Name: mirrors_checks mirrors_checks_mirror_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.mirrors_checks
    ADD CONSTRAINT mirrors_checks_mirror_id FOREIGN KEY (mirror_id) REFERENCES public.mirrors(id);


--
-- Name: package_files package_files_pkg_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.package_files
    ADD CONSTRAINT package_files_pkg_id FOREIGN KEY (pkg_id) REFERENCES public.packages(id);


--
-- Name: packages packages_commit_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.packages
    ADD CONSTRAINT packages_commit_id FOREIGN KEY (commit_id) REFERENCES public.source_commits(id);


--
-- Name: packages packages_deleted_by; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.packages
    ADD CONSTRAINT packages_deleted_by FOREIGN KEY (deleted_by) REFERENCES public.users(id);


--
-- Name: packages packages_distro_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.packages
    ADD CONSTRAINT packages_distro_id FOREIGN KEY (distro_id) REFERENCES public.distributions(id);


--
-- Name: repo_builds repo_builds_added_by; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.repo_builds
    ADD CONSTRAINT repo_builds_added_by FOREIGN KEY (added_by) REFERENCES public.users(id);


--
-- Name: repo_builds repo_builds_build_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.repo_builds
    ADD CONSTRAINT repo_builds_build_id FOREIGN KEY (build_id) REFERENCES public.builds(id);


--
-- Name: repo_builds repo_builds_removed_by; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.repo_builds
    ADD CONSTRAINT repo_builds_removed_by FOREIGN KEY (removed_by) REFERENCES public.users(id);


--
-- Name: repo_builds repo_builds_repo_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.repo_builds
    ADD CONSTRAINT repo_builds_repo_id FOREIGN KEY (repo_id) REFERENCES public.repositories(id);


--
-- Name: repositories repositories_distro_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.repositories
    ADD CONSTRAINT repositories_distro_id FOREIGN KEY (distro_id) REFERENCES public.distributions(id);


--
-- Name: repositories repositories_key_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.repositories
    ADD CONSTRAINT repositories_key_id FOREIGN KEY (key_id) REFERENCES public.keys(id);


--
-- Name: repositories repositories_owner_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.repositories
    ADD CONSTRAINT repositories_owner_id FOREIGN KEY (owner_id) REFERENCES public.users(id);


--
-- Name: repositories repositories_parent_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.repositories
    ADD CONSTRAINT repositories_parent_id FOREIGN KEY (parent_id) REFERENCES public.repositories(id);


--
-- Name: repository_builds repository_builds_added_by; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.repository_builds
    ADD CONSTRAINT repository_builds_added_by FOREIGN KEY (added_by) REFERENCES public.users(id);


--
-- Name: repository_builds repository_builds_build_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.repository_builds
    ADD CONSTRAINT repository_builds_build_id FOREIGN KEY (build_id) REFERENCES public.builds(id);


--
-- Name: repository_builds repository_builds_removed_by; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.repository_builds
    ADD CONSTRAINT repository_builds_removed_by FOREIGN KEY (removed_by) REFERENCES public.users(id);


--
-- Name: repository_builds repository_builds_repo_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.repository_builds
    ADD CONSTRAINT repository_builds_repo_id FOREIGN KEY (repo_id) REFERENCES public.repositories(id);


--
-- Name: sessions sessions_user_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sessions
    ADD CONSTRAINT sessions_user_id FOREIGN KEY (user_id) REFERENCES public.users(id);


--
-- Name: source_commits sources_commits_source_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.source_commits
    ADD CONSTRAINT sources_commits_source_id FOREIGN KEY (source_id) REFERENCES public.sources(id);


--
-- Name: sources sources_created_by; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sources
    ADD CONSTRAINT sources_created_by FOREIGN KEY (created_by) REFERENCES public.users(id);


--
-- Name: sources sources_deleted_by; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sources
    ADD CONSTRAINT sources_deleted_by FOREIGN KEY (deleted_by) REFERENCES public.users(id);


--
-- Name: sources sources_repo_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.sources
    ADD CONSTRAINT sources_repo_id FOREIGN KEY (repo_id) REFERENCES public.repositories(id);


--
-- Name: uploads uploads_builder_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.uploads
    ADD CONSTRAINT uploads_builder_id FOREIGN KEY (builder_id) REFERENCES public.builders(id);


--
-- Name: uploads uploads_user_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.uploads
    ADD CONSTRAINT uploads_user_id FOREIGN KEY (user_id) REFERENCES public.users(id);


--
-- Name: SCHEMA public; Type: ACL; Schema: -; Owner: -
--

REVOKE USAGE ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--