New SQL schema

As you might see on the Roadmap already, the new MirrorBrain version will also require a new database schema.

The idea behind this page here is to be used as a starting point for discussion.

--
-- PostgreSQL database dump
--

-- Dumped from database version 12.2
-- Dumped by pg_dump version 12.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: btree_gin; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS btree_gin WITH SCHEMA public;


--
-- Name: EXTENSION btree_gin; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION btree_gin IS 'support for indexing common datatypes in GIN';


--
-- Name: btree_gist; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS btree_gist WITH SCHEMA public;


--
-- Name: EXTENSION btree_gist; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION btree_gist IS 'support for indexing common datatypes in GiST';


--
-- Name: intarray; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS intarray WITH SCHEMA public;


--
-- Name: EXTENSION intarray; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION intarray IS 'functions, operators, and index support for 1-D arrays of integers';


--
-- Name: pgcrypto; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;


--
-- Name: EXTENSION pgcrypto; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION pgcrypto IS 'cryptographic functions';


--
-- Name: count_mirrors(); Type: FUNCTION; Schema: public; Owner: mb
--

CREATE FUNCTION public.count_mirrors() RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
    filemetadata_entry RECORD;
    mirror       integer;
    mirror_count integer;
BEGIN
    RAISE NOTICE 'Counting mirrors ...';
    WITH count_data AS (
        SELECT mirrors.filemetadata_id, count(mirrors.server_id) AS count
          FROM mirrors
          GROUP BY mirrors.filemetadata_id
    )
    INSERT INTO filemetadata_mirror_count SELECT * FROM count_data  
    ON CONFLICT (filemetadata_id) DO UPDATE SET count = EXCLUDED.count;
    RAISE NOTICE '... done counting things.';
    RETURN 1;
END;
$$;


ALTER FUNCTION public.count_mirrors() OWNER TO mb;

--
-- Name: filemetadata_add_related_records(); Type: FUNCTION; Schema: public; Owner: mb
--

CREATE FUNCTION public.filemetadata_add_related_records() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO filemetadata_mirror_count (filemetadata_id) VALUES (NEW.id)
    -- exists already. mostly for migrating
    ON CONFLICT DO NOTHING;
    INSERT INTO mirrors_array (filemetadata_id) VALUES (NEW.id)                          
    -- exists already. mostly for migrating    
    ON CONFLICT DO NOTHING;
    RETURN NEW;
END;
$$;


ALTER FUNCTION public.filemetadata_add_related_records() OWNER TO mb;

--
-- Name: inserted_update_mirror_count(); Type: FUNCTION; Schema: public; Owner: mb
--

CREATE FUNCTION public.inserted_update_mirror_count() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    PERFORM update_mirror_count(NEW.filemetadata_id);
    RETURN NEW;
END;
$$;


ALTER FUNCTION public.inserted_update_mirror_count() OWNER TO mb;

--
-- Name: mirror_add_filelist(integer, integer[], integer[]); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.mirror_add_filelist(arg_server_id integer, insert_file_ids integer[], delete_file_ids integer[]) RETURNS integer
    LANGUAGE plpgsql
    AS $$
BEGIN
  INSERT INTO mirrors SELECT * FROM insert_file_ids;
  DELETE FROM mirrors WHERE server_id = arg_server_id AND filemetadata_id IN ( delete_file_ids );

  -- update_mirror_count(delete_file_ids);
  -- update_mirror_count(new_file_ids);
END $$;


ALTER FUNCTION public.mirror_add_filelist(arg_server_id integer, insert_file_ids integer[], delete_file_ids integer[]) OWNER TO postgres;

--
-- Name: refresh_serverswithprefix(); Type: FUNCTION; Schema: public; Owner: mb
--

CREATE FUNCTION public.refresh_serverswithprefix() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY public.serverswithprefix;
  REINDEX TABLE public.serverswithprefix;
  RETURN NULL;
END $$;


ALTER FUNCTION public.refresh_serverswithprefix() OWNER TO mb;

--
-- Name: seed_mirrors_inverted(); Type: FUNCTION; Schema: public; Owner: mb
--

CREATE FUNCTION public.seed_mirrors_inverted() RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE                                                                                                
    loop_server_id integer;
    file_ids     integer[];
BEGIN                                             
  RAISE NOTICE 'Seeding mirrors ...';                                                         
  SELECT INTO file_ids array_agg(id) FROM filemetadata;
  FOR loop_server_id IN
    SELECT id from servers where enabled = 't'
  LOOP
    RAISE NOTICE 'Processing mirror %', loop_server_id;                    
    INSERT INTO mirrors_inverted (server_id, filemetadata_id) VALUES (loop_server_id, file_ids)
      ON CONFLICT DO NOTHING;
  END LOOP;
  RETURN 1;            
END;                                                                           
$$;


ALTER FUNCTION public.seed_mirrors_inverted() OWNER TO mb;

--
-- Name: servers_add_related_records(); Type: FUNCTION; Schema: public; Owner: mb
--

CREATE FUNCTION public.servers_add_related_records() RETURNS trigger
    LANGUAGE plpgsql
    AS $$     
BEGIN
    INSERT INTO mirrors_inverted (server_id) VALUES (NEW.id)                             
    -- exists already. mostly for migrating    
    ON CONFLICT DO NOTHING;
    RETURN NEW;
END;
$$;


ALTER FUNCTION public.servers_add_related_records() OWNER TO mb;

--
-- Name: update_all_mirror_count(); Type: FUNCTION; Schema: public; Owner: mb
--

CREATE FUNCTION public.update_all_mirror_count() RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
  all_files integer[];
  modified_filemetadata_id integer;
BEGIN
  FOR modified_filemetadata_id IN 
    SELECT id from filemetadata
  LOOP
  PERFORM update_mirror_count(modified_filemetadata_id);
  END LOOP;
  RETURN 1;
END $$;


ALTER FUNCTION public.update_all_mirror_count() OWNER TO mb;

--
-- Name: update_mirror_count(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_mirror_count(modified_filemetadata_id integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
  updated_count integer;
BEGIN
  SELECT INTO updated_count count(mirrors.server_id)
    FROM mirrors
    WHERE mirrors.filemetadata_id = modified_filemetadata_id;

  INSERT INTO filemetadata_mirror_count (filemetadata_id, count) values (modified_filemetadata_id, updated_count) ON CONFLICT (filemetadata_id) DO UPDATE
    SET count = updated_count
    WHERE filemetadata_mirror_count.filemetadata_id = modified_filemetadata_id;

   -- RAISE NOTICE 'Mirrors for %: %', modified_filemetadata_id, updated_count;
  RETURN updated_count;
END $$;


ALTER FUNCTION public.update_mirror_count(modified_filemetadata_id integer) OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: ar_internal_metadata; Type: TABLE; Schema: public; Owner: mb
--

CREATE TABLE public.ar_internal_metadata (
    key character varying NOT NULL,
    value character varying,
    created_at timestamp(6) without time zone NOT NULL,
    updated_at timestamp(6) without time zone NOT NULL
);


ALTER TABLE public.ar_internal_metadata OWNER TO mb;

--
-- Name: countries; Type: TABLE; Schema: public; Owner: mb
--

CREATE TABLE public.countries (
    id bigint NOT NULL,
    code character varying(3) NOT NULL,
    name character varying(128) NOT NULL,
    created_at timestamp(6) without time zone NOT NULL,
    updated_at timestamp(6) without time zone NOT NULL
);


ALTER TABLE public.countries OWNER TO mb;

--
-- Name: countries_id_seq; Type: SEQUENCE; Schema: public; Owner: mb
--

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


ALTER TABLE public.countries_id_seq OWNER TO mb;

--
-- Name: countries_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: mb
--

ALTER SEQUENCE public.countries_id_seq OWNED BY public.countries.id;


--
-- Name: filemetadata; Type: TABLE; Schema: public; Owner: mb
--

CREATE TABLE public.filemetadata (
    id integer NOT NULL,
    path character varying NOT NULL,
    mtime timestamp without time zone,
    size bigint,
    md5 bytea,
    sha1 bytea,
    sha256 bytea,
    sha1piecesize integer,
    sha1pieces bytea,
    btih bytea,
    pgp text,
    zblocksize smallint,
    zhashlens character varying(8),
    zsums bytea,
    created_at timestamp(6) without time zone DEFAULT now() NOT NULL,
    updated_at timestamp(6) without time zone DEFAULT now() NOT NULL,
    path_hash bytea GENERATED ALWAYS AS (public.digest((path)::text, 'sha256'::text)) STORED
);


ALTER TABLE public.filemetadata OWNER TO mb;

--
-- Name: filemetadata_id_seq; Type: SEQUENCE; Schema: public; Owner: mb
--

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


ALTER TABLE public.filemetadata_id_seq OWNER TO mb;

--
-- Name: filemetadata_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: mb
--

ALTER SEQUENCE public.filemetadata_id_seq OWNED BY public.filemetadata.id;


--
-- Name: filemetadata_mirror_count; Type: TABLE; Schema: public; Owner: mb
--

CREATE TABLE public.filemetadata_mirror_count (
    filemetadata_id integer,
    count smallint DEFAULT 0
);


ALTER TABLE public.filemetadata_mirror_count OWNER TO mb;

--
-- Name: markers; Type: TABLE; Schema: public; Owner: mb
--

CREATE TABLE public.markers (
    id smallint NOT NULL,
    filemetadata_id integer,
    subtree_name character varying,
    created_at timestamp(6) without time zone NOT NULL,
    updated_at timestamp(6) without time zone NOT NULL
);


ALTER TABLE public.markers OWNER TO mb;

--
-- Name: markers_id_seq; Type: SEQUENCE; Schema: public; Owner: mb
--

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


ALTER TABLE public.markers_id_seq OWNER TO mb;

--
-- Name: markers_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: mb
--

ALTER SEQUENCE public.markers_id_seq OWNED BY public.markers.id;


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

CREATE TABLE public.mirrors (
    server_id smallint NOT NULL,
    filemetadata_id integer NOT NULL
);


ALTER TABLE public.mirrors OWNER TO mb;

--
-- Name: mirrors_array; Type: TABLE; Schema: public; Owner: mb
--

CREATE TABLE public.mirrors_array (
    filemetadata_id integer,
    server_ids integer[] DEFAULT ARRAY[]::integer[],
    mirror_count integer GENERATED ALWAYS AS (array_length(server_ids, 1)) STORED
);


ALTER TABLE public.mirrors_array OWNER TO mb;

--
-- Name: mirrors_inverted; Type: TABLE; Schema: public; Owner: mb
--

CREATE TABLE public.mirrors_inverted (
    server_id integer,
    filemetadata_id integer[] DEFAULT ARRAY[]::integer[]
);


ALTER TABLE public.mirrors_inverted OWNER TO mb;

--
-- Name: regions; Type: TABLE; Schema: public; Owner: mb
--

CREATE TABLE public.regions (
    id bigint NOT NULL,
    code character varying(3) NOT NULL,
    name character varying(128) NOT NULL,
    created_at timestamp(6) without time zone NOT NULL,
    updated_at timestamp(6) without time zone NOT NULL
);


ALTER TABLE public.regions OWNER TO mb;

--
-- Name: regions_id_seq; Type: SEQUENCE; Schema: public; Owner: mb
--

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


ALTER TABLE public.regions_id_seq OWNER TO mb;

--
-- Name: regions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: mb
--

ALTER SEQUENCE public.regions_id_seq OWNED BY public.regions.id;


--
-- Name: schema_migrations; Type: TABLE; Schema: public; Owner: mb
--

CREATE TABLE public.schema_migrations (
    version character varying NOT NULL
);


ALTER TABLE public.schema_migrations OWNER TO mb;

--
-- Name: server_prefixes; Type: TABLE; Schema: public; Owner: mb
--

CREATE TABLE public.server_prefixes (
    id bigint NOT NULL,
    server_id smallint NOT NULL,
    prefix cidr NOT NULL,
    asn integer NOT NULL,
    created_at timestamp(6) without time zone NOT NULL,
    updated_at timestamp(6) without time zone NOT NULL
);


ALTER TABLE public.server_prefixes OWNER TO mb;

--
-- Name: server_prefixes_id_seq; Type: SEQUENCE; Schema: public; Owner: mb
--

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


ALTER TABLE public.server_prefixes_id_seq OWNER TO mb;

--
-- Name: server_prefixes_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: mb
--

ALTER SEQUENCE public.server_prefixes_id_seq OWNED BY public.server_prefixes.id;


--
-- Name: servers; Type: TABLE; Schema: public; Owner: mb
--

CREATE TABLE public.servers (
    id smallint NOT NULL,
    https boolean DEFAULT false NOT NULL,
    enabled boolean DEFAULT false NOT NULL,
    status_baseurl boolean DEFAULT false NOT NULL,
    has_ipv4 boolean DEFAULT true NOT NULL,
    has_ipv6 boolean DEFAULT false NOT NULL,
    score integer DEFAULT 100,
    region character varying(3) NOT NULL,
    country character varying(3) NOT NULL,
    country_only boolean DEFAULT false,
    region_only boolean DEFAULT false,
    as_only boolean DEFAULT false,
    prefix_only boolean DEFAULT false,
    identifier character varying NOT NULL,
    baseurl_http character varying NOT NULL,
    baseurl_https character varying,
    baseurl_ftp character varying,
    baseurl_rsync character varying,
    scan_url character varying NOT NULL,
    lat numeric(6,3) NOT NULL,
    lng numeric(6,3) NOT NULL,
    scan_fpm integer,
    last_scan timestamp without time zone,
    other_countries character varying,
    file_maxsize character varying DEFAULT '0'::character varying,
    operator_name character varying NOT NULL,
    operator_url character varying NOT NULL,
    admin_name character varying NOT NULL,
    admin_email character varying NOT NULL,
    comment text,
    public_notes text,
    created_at timestamp(6) without time zone NOT NULL,
    updated_at timestamp(6) without time zone NOT NULL
);


ALTER TABLE public.servers OWNER TO mb;

--
-- Name: servers_id_seq; Type: SEQUENCE; Schema: public; Owner: mb
--

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


ALTER TABLE public.servers_id_seq OWNER TO mb;

--
-- Name: servers_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: mb
--

ALTER SEQUENCE public.servers_id_seq OWNED BY public.servers.id;


--
-- Name: countries id; Type: DEFAULT; Schema: public; Owner: mb
--

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


--
-- Name: filemetadata id; Type: DEFAULT; Schema: public; Owner: mb
--

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


--
-- Name: markers id; Type: DEFAULT; Schema: public; Owner: mb
--

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


--
-- Name: regions id; Type: DEFAULT; Schema: public; Owner: mb
--

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


--
-- Name: server_prefixes id; Type: DEFAULT; Schema: public; Owner: mb
--

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


--
-- Name: servers id; Type: DEFAULT; Schema: public; Owner: mb
--

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


--
-- Name: ar_internal_metadata ar_internal_metadata_pkey; Type: CONSTRAINT; Schema: public; Owner: mb
--

ALTER TABLE ONLY public.ar_internal_metadata
    ADD CONSTRAINT ar_internal_metadata_pkey PRIMARY KEY (key);


--
-- Name: countries countries_pkey; Type: CONSTRAINT; Schema: public; Owner: mb
--

ALTER TABLE ONLY public.countries
    ADD CONSTRAINT countries_pkey PRIMARY KEY (id);


--
-- Name: filemetadata filemetadata_pkey; Type: CONSTRAINT; Schema: public; Owner: mb
--

ALTER TABLE ONLY public.filemetadata
    ADD CONSTRAINT filemetadata_pkey PRIMARY KEY (id);


--
-- Name: markers markers_pkey; Type: CONSTRAINT; Schema: public; Owner: mb
--

ALTER TABLE ONLY public.markers
    ADD CONSTRAINT markers_pkey PRIMARY KEY (id);


--
-- Name: regions regions_pkey; Type: CONSTRAINT; Schema: public; Owner: mb
--

ALTER TABLE ONLY public.regions
    ADD CONSTRAINT regions_pkey PRIMARY KEY (id);


--
-- Name: schema_migrations schema_migrations_pkey; Type: CONSTRAINT; Schema: public; Owner: mb
--

ALTER TABLE ONLY public.schema_migrations
    ADD CONSTRAINT schema_migrations_pkey PRIMARY KEY (version);


--
-- Name: server_prefixes server_prefixes_pkey; Type: CONSTRAINT; Schema: public; Owner: mb
--

ALTER TABLE ONLY public.server_prefixes
    ADD CONSTRAINT server_prefixes_pkey PRIMARY KEY (id);


--
-- Name: servers servers_pkey; Type: CONSTRAINT; Schema: public; Owner: mb
--

ALTER TABLE ONLY public.servers
    ADD CONSTRAINT servers_pkey PRIMARY KEY (id);


--
-- Name: filemetadata_count_pkey_idx; Type: INDEX; Schema: public; Owner: mb
--

CREATE UNIQUE INDEX filemetadata_count_pkey_idx ON public.filemetadata_mirror_count USING btree (filemetadata_id);


--
-- Name: filemetadata_mirror_count_count_existing_idx; Type: INDEX; Schema: public; Owner: mb
--

CREATE INDEX filemetadata_mirror_count_count_existing_idx ON public.filemetadata_mirror_count USING btree (count) WHERE (count > 0);


--
-- Name: filemetadata_mirror_count_count_idx; Type: INDEX; Schema: public; Owner: mb
--

CREATE INDEX filemetadata_mirror_count_count_idx ON public.filemetadata_mirror_count USING btree (count) WHERE (count = 0);


--
-- Name: index_filemetadata_on_mtime_and_size; Type: INDEX; Schema: public; Owner: mb
--

CREATE INDEX index_filemetadata_on_mtime_and_size ON public.filemetadata USING btree (mtime, size);


--
-- Name: index_filemetadata_on_path_unique; Type: INDEX; Schema: public; Owner: mb
--

CREATE UNIQUE INDEX index_filemetadata_on_path_unique ON public.filemetadata USING btree (path);


--
-- Name: index_markers_on_filemetadata_id; Type: INDEX; Schema: public; Owner: mb
--

CREATE INDEX index_markers_on_filemetadata_id ON public.markers USING btree (filemetadata_id);


--
-- Name: index_mirrors_on_server_id_and_filemetadata_id; Type: INDEX; Schema: public; Owner: mb
--

CREATE UNIQUE INDEX index_mirrors_on_server_id_and_filemetadata_id ON public.mirrors USING btree (filemetadata_id, server_id);

ALTER TABLE public.mirrors CLUSTER ON index_mirrors_on_server_id_and_filemetadata_id;


--
-- Name: index_server_prefixes_on_prefix; Type: INDEX; Schema: public; Owner: mb
--

CREATE INDEX index_server_prefixes_on_prefix ON public.server_prefixes USING spgist (prefix);


--
-- Name: index_server_prefixes_on_server_id; Type: INDEX; Schema: public; Owner: mb
--

CREATE INDEX index_server_prefixes_on_server_id ON public.server_prefixes USING btree (server_id);


--
-- Name: index_server_prefixes_on_server_id_and_prefix_and_asn; Type: INDEX; Schema: public; Owner: mb
--

CREATE UNIQUE INDEX index_server_prefixes_on_server_id_and_prefix_and_asn ON public.server_prefixes USING btree (server_id, prefix, asn);


--
-- Name: index_servers_on_identifier; Type: INDEX; Schema: public; Owner: mb
--

CREATE UNIQUE INDEX index_servers_on_identifier ON public.servers USING btree (identifier);


--
-- Name: index_servers_on_mod_mirrorbrain_selectors; Type: INDEX; Schema: public; Owner: mb
--

CREATE INDEX index_servers_on_mod_mirrorbrain_selectors ON public.servers USING btree (https, enabled, status_baseurl, has_ipv4, has_ipv6, score, region, country) INCLUDE (baseurl_http, baseurl_https, lat, lng, country_only, region_only, as_only, prefix_only);

ALTER TABLE public.servers CLUSTER ON index_servers_on_mod_mirrorbrain_selectors;


--
-- Name: mirrors_array_counter_idx; Type: INDEX; Schema: public; Owner: mb
--

CREATE INDEX mirrors_array_counter_idx ON public.mirrors_array USING btree (mirror_count) WHERE (mirror_count >= 1);


--
-- Name: mirrors_array_pkey; Type: INDEX; Schema: public; Owner: mb
--

CREATE UNIQUE INDEX mirrors_array_pkey ON public.mirrors_array USING btree (filemetadata_id);


--
-- Name: mirrors_array_zero_counter_idx; Type: INDEX; Schema: public; Owner: mb
--

CREATE INDEX mirrors_array_zero_counter_idx ON public.mirrors_array USING btree (mirror_count) WHERE (mirror_count IS NULL);


--
-- Name: mirrors_inverted_files_idx; Type: INDEX; Schema: public; Owner: mb
--

CREATE INDEX mirrors_inverted_files_idx ON public.mirrors_inverted USING gin (filemetadata_id public.gin__int_ops) WHERE (array_length(filemetadata_id, 1) > 0);


--
-- Name: mirrors_inverted_pkey; Type: INDEX; Schema: public; Owner: mb
--

CREATE UNIQUE INDEX mirrors_inverted_pkey ON public.mirrors_inverted USING btree (server_id) WHERE (server_id >= 1);


--
-- Name: mirrors_server_id_idx; Type: INDEX; Schema: public; Owner: mb
--

CREATE INDEX mirrors_server_id_idx ON public.mirrors USING btree (server_id);


--
-- Name: server_ids_idx2; Type: INDEX; Schema: public; Owner: mb
--

CREATE INDEX server_ids_idx2 ON public.mirrors_array USING gin (server_ids public.gin__int_ops);


--
-- Name: filemetadata trigger_filemetadata_add_related_records; Type: TRIGGER; Schema: public; Owner: mb
--

CREATE TRIGGER trigger_filemetadata_add_related_records AFTER INSERT OR UPDATE ON public.filemetadata FOR EACH ROW EXECUTE FUNCTION public.filemetadata_add_related_records();


--
-- Name: mirrors trigger_inserted_update_mirror_count; Type: TRIGGER; Schema: public; Owner: mb
--

CREATE TRIGGER trigger_inserted_update_mirror_count AFTER INSERT ON public.mirrors FOR EACH ROW EXECUTE FUNCTION public.inserted_update_mirror_count();


--
-- Name: servers trigger_servers_add_related_records; Type: TRIGGER; Schema: public; Owner: mb
--

CREATE TRIGGER trigger_servers_add_related_records AFTER INSERT OR UPDATE ON public.servers FOR EACH ROW EXECUTE FUNCTION public.servers_add_related_records();


--
-- Name: filemetadata_mirror_count fk_filemetadata_id; Type: FK CONSTRAINT; Schema: public; Owner: mb
--

ALTER TABLE ONLY public.filemetadata_mirror_count
    ADD CONSTRAINT fk_filemetadata_id FOREIGN KEY (filemetadata_id) REFERENCES public.filemetadata(id) ON DELETE CASCADE;


--
-- Name: server_prefixes fk_rails_37f0edcc8c; Type: FK CONSTRAINT; Schema: public; Owner: mb
--

ALTER TABLE ONLY public.server_prefixes
    ADD CONSTRAINT fk_rails_37f0edcc8c FOREIGN KEY (server_id) REFERENCES public.servers(id) ON DELETE CASCADE;


--
-- Name: markers fk_rails_3b528a1712; Type: FK CONSTRAINT; Schema: public; Owner: mb
--

ALTER TABLE ONLY public.markers
    ADD CONSTRAINT fk_rails_3b528a1712 FOREIGN KEY (filemetadata_id) REFERENCES public.filemetadata(id) ON DELETE CASCADE;


--
-- Name: mirrors fk_rails_637c261713; Type: FK CONSTRAINT; Schema: public; Owner: mb
--

ALTER TABLE ONLY public.mirrors
    ADD CONSTRAINT fk_rails_637c261713 FOREIGN KEY (filemetadata_id) REFERENCES public.filemetadata(id) ON DELETE CASCADE;


--
-- Name: mirrors fk_rails_ed3702eb5f; Type: FK CONSTRAINT; Schema: public; Owner: mb
--

ALTER TABLE ONLY public.mirrors
    ADD CONSTRAINT fk_rails_ed3702eb5f FOREIGN KEY (server_id) REFERENCES public.servers(id) ON DELETE CASCADE;


--
-- PostgreSQL database dump complete
--