ctf.schema

--
-- PostgreSQL database dump
--

-- Dumped from database version 12.12
-- Dumped by pg_dump version 14.5

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: 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: check_submission(text); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.check_submission(submission text) RETURNS boolean
    LANGUAGE sql
    AS $$
SELECT (CASE WHEN COUNT(*) > 0 THEN true ELSE false END) FROM flags f WHERE submission ~* f.regexp
 $$;


--
-- Name: purge_flags(); Type: PROCEDURE; Schema: public; Owner: -
--

CREATE PROCEDURE public.purge_flags()
    LANGUAGE sql
    AS $$
delete from attachments;
delete from flags;
delete from metaflags;


refresh materialized view v_solves;
$$;


--
-- Name: soft_delete_submissions(); Type: PROCEDURE; Schema: public; Owner: -
--

CREATE PROCEDURE public.soft_delete_submissions()
    LANGUAGE sql
    AS $$
UPDATE submissions SET deleted=true;
REFRESH MATERIALIZED VIEW v_solves;
$$;


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

CREATE FUNCTION public.solved(team_id integer) RETURNS TABLE(flag_id integer, flag_name text, points integer, parent integer, bonus boolean, "time" text)
    LANGUAGE sql
    AS $$ 
	SELECT v_solves.flag_id, v_solves.flag_name, 
		v_solves.points, v_solves.parent, v_solves.bonus, 
		v_valid_submissions.time 
	FROM v_solves 
	LEFT JOIN v_valid_submissions 
	ON v_solves.submissions[1]=v_valid_Submissions.submission_id 
	WHERE v_solves.team_id=solved.team_id; 
$$;


--
-- Name: submit(integer, text); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.submit(team_id integer, submission text, OUT submission_id integer, OUT flag_name text, OUT flag_id integer, OUT points integer, OUT bonus boolean, OUT nsubs integer, OUT parent integer) RETURNS record
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$ 
BEGIN 
	INSERT INTO submissions(team_id, submission) 
	VALUES (team_id, submission) 
	RETURNING id INTO submission_id; 

	REFRESH MATERIALIZED VIEW v_solves; 

	SELECT 
		vs.team_id, vs.flag_id, vs.nsubs, vs.points,
		vs.flag_name, vs.bonus, vs.parent
	INTO 
		submit.team_id, submit.flag_id, submit.nsubs, submit.points,
		submit.flag_name, submit.bonus, submit.parent
	FROM v_solves vs
	WHERE submission_id = ANY (submissions);
END $$;


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

CREATE FUNCTION public.unsolved(team_id integer) RETURNS TABLE(flag_id integer, flag_name text, points integer, parent integer, bonus boolean)
    LANGUAGE sql
    AS $$
	SELECT 
		flags.id, 
		flags.name, 
		flags.points, 
		flags.parent, 
		flags.bonus 
	FROM flags 
	LEFT JOIN solved(1) 
	ON flags.id=flag_id 
	WHERE solved.flag_id 
	IS NULL AND flags.visible;
$$;


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

CREATE FUNCTION public.unsolved_meta(team_id integer) RETURNS TABLE(id integer, name text, points integer)
    LANGUAGE sql
    AS $$
	SELECT 
		metaflags.id, 
		metaflags.name, 
		metaflags.points - coalesce(sum(solved.points), 0)::integer as points 
	FROM metaflags 
	FULL OUTER JOIN solved(unsolved_meta.team_id) 
	ON solved.parent=metaflags.id 
	WHERE metaflags.id IS NOT NULL
	GROUP BY metaflags.id
$$;


SET default_tablespace = '';

SET default_table_access_method = heap;

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

CREATE TABLE public.attachments (
    name text DEFAULT ''::text,
    uri text DEFAULT ''::text,
    flag_id integer,
    id integer NOT NULL,
    metaflag_id integer
);


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

ALTER TABLE public.attachments ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.attachments_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


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

CREATE TABLE public.flags (
    id integer NOT NULL,
    visible boolean DEFAULT true,
    enabled boolean DEFAULT true,
    name text DEFAULT ''::text,
    description text DEFAULT ''::text,
    points integer DEFAULT 0,
    regexp text DEFAULT ''::text,
    parent integer,
    bonus boolean DEFAULT false
);


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

ALTER TABLE public.flags ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.flags_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


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

CREATE TABLE public.metaflags (
    id integer NOT NULL,
    visible boolean DEFAULT true,
    name text DEFAULT ''::text,
    description text DEFAULT ''::text,
    points integer DEFAULT 0
);


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

ALTER TABLE public.metaflags ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.metaflags_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


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

CREATE TABLE public.ssh_keys (
    key text NOT NULL,
    id integer NOT NULL,
    team_id integer NOT NULL,
    key_type text NOT NULL,
    CONSTRAINT ssh_key_regex CHECK ((key ~* '^(ssh-rsa|ssh-dss) ([A-Za-z0-9+/]+)*$'::text))
);


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

ALTER TABLE public.ssh_keys ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.ssh_keys_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


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

CREATE TABLE public.submissions (
    id integer NOT NULL,
    team_id integer,
    submission text,
    "timestamp" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    deleted boolean DEFAULT false
);


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

ALTER TABLE public.submissions ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.submissions_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


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

CREATE TABLE public.teams (
    id integer NOT NULL,
    name text DEFAULT ''::text,
    enabled boolean DEFAULT true,
    hash text,
    CONSTRAINT team_name_regex CHECK ((name ~* '^[a-z0-9_\-''☃ ]{1,30}$'::text))
);


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

ALTER TABLE public.teams ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.teams_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


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

CREATE VIEW public.v_active_submissions AS
 SELECT submissions.id,
    submissions.team_id,
    submissions.submission,
    submissions."timestamp",
    submissions.deleted
   FROM public.submissions
  WHERE (NOT submissions.deleted);


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

CREATE VIEW public.v_flag_info AS
SELECT
    NULL::text AS name,
    NULL::integer AS flag_id,
    NULL::integer AS solves;


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

CREATE VIEW public.v_scoreboard AS
SELECT
    NULL::bigint AS place,
    NULL::text AS name,
    NULL::bigint AS score;


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

CREATE VIEW public.v_submission_count AS
 SELECT submissions.team_id,
    count(*) AS attempts
   FROM public.submissions
  GROUP BY submissions.team_id;


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

CREATE VIEW public.v_team_flags AS
SELECT
    NULL::integer AS team_id,
    NULL::bigint AS flag_count,
    NULL::integer[] AS flags;


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

CREATE VIEW public.v_team_info AS
 SELECT t.name,
    t.id,
    COALESCE(tf.flag_count, (0)::bigint) AS flag_count,
    tf.flags,
    COALESCE(sc.attempts, (0)::bigint) AS submission_attempts
   FROM ((public.teams t
     LEFT JOIN public.v_team_flags tf ON ((t.id = tf.team_id)))
     LEFT JOIN public.v_submission_count sc ON ((sc.team_id = t.id)));


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

CREATE VIEW public.v_valid_submissions AS
SELECT
    NULL::text AS "time",
    NULL::text AS team_name,
    NULL::integer AS team_id,
    NULL::text AS flag_name,
    NULL::integer AS submission_id,
    NULL::integer AS flag_id,
    NULL::integer AS points;


--
-- Name: flags flags_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.flags
    ADD CONSTRAINT flags_pkey PRIMARY KEY (id);


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

CREATE MATERIALIZED VIEW public.v_solves AS
 SELECT t.id AS team_id,
    f.id AS flag_id,
    array_agg(s.id ORDER BY s."timestamp") AS submissions,
    count(s.id) AS nsubs,
    f.name AS flag_name,
    f.bonus,
    f.parent,
    f.points
   FROM ((public.v_active_submissions s
     LEFT JOIN public.teams t ON ((s.team_id = t.id)))
     LEFT JOIN public.flags f ON ((s.submission ~* f.regexp)))
  WHERE (f.enabled AND (f.regexp IS NOT NULL))
  GROUP BY t.id, f.id
  WITH NO DATA;


--
-- Name: attachments attachments_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.attachments
    ADD CONSTRAINT attachments_pkey PRIMARY KEY (id);


--
-- Name: metaflags metaflags_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.metaflags
    ADD CONSTRAINT metaflags_pkey PRIMARY KEY (id);


--
-- Name: ssh_keys ssh_keys_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.ssh_keys
    ADD CONSTRAINT ssh_keys_pkey PRIMARY KEY (id);


--
-- Name: submissions submissions_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.submissions
    ADD CONSTRAINT submissions_pkey PRIMARY KEY (id);


--
-- Name: teams teams_name_unique; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.teams
    ADD CONSTRAINT teams_name_unique UNIQUE (name);


--
-- Name: teams teams_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.teams
    ADD CONSTRAINT teams_pkey PRIMARY KEY (id);


--
-- Name: v_flag_info _RETURN; Type: RULE; Schema: public; Owner: -
--

CREATE OR REPLACE VIEW public.v_flag_info AS
 SELECT v_solves.flag_name AS name,
    v_solves.flag_id,
    (count(v_solves.team_id))::integer AS solves
   FROM (public.v_solves
     LEFT JOIN public.flags ON ((v_solves.flag_id = flags.id)))
  WHERE flags.visible
  GROUP BY v_solves.flag_id, v_solves.flag_name
  ORDER BY ((count(v_solves.team_id))::integer) DESC;


--
-- Name: v_scoreboard _RETURN; Type: RULE; Schema: public; Owner: -
--

CREATE OR REPLACE VIEW public.v_scoreboard AS
 SELECT row_number() OVER (ORDER BY (sum(f.points)) DESC) AS place,
    t.name,
    sum(f.points) AS score
   FROM ((public.v_solves vs
     LEFT JOIN public.teams t ON ((t.id = vs.team_id)))
     LEFT JOIN public.flags f ON ((vs.flag_id = f.id)))
  GROUP BY t.name
  ORDER BY (sum(f.points)) DESC;


--
-- Name: v_team_flags _RETURN; Type: RULE; Schema: public; Owner: -
--

CREATE OR REPLACE VIEW public.v_team_flags AS
 SELECT v_solves.team_id,
    count(v_solves.flag_id) AS flag_count,
    array_agg(v_solves.flag_id) AS flags
   FROM public.v_solves
  GROUP BY v_solves.team_id;


--
-- Name: v_valid_submissions _RETURN; Type: RULE; Schema: public; Owner: -
--

CREATE OR REPLACE VIEW public.v_valid_submissions AS
 SELECT COALESCE(to_char(s."timestamp", 'YYYY-MM-DD HH24:MI:SS'::text), '- -'::text) AS "time",
    t.name AS team_name,
    t.id AS team_id,
    f.name AS flag_name,
    s.id AS submission_id,
    f.id AS flag_id,
    f.points
   FROM (((public.submissions s
     LEFT JOIN public.v_solves vs ON ((ARRAY[s.id] <@ vs.submissions)))
     LEFT JOIN public.teams t ON ((t.id = vs.team_id)))
     LEFT JOIN public.flags f ON ((vs.flag_id = f.id)))
  WHERE (f.id IS NOT NULL);


--
-- Name: attachments attachments_flag_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.attachments
    ADD CONSTRAINT attachments_flag_id_fkey FOREIGN KEY (flag_id) REFERENCES public.flags(id);


--
-- Name: attachments attachments_metaflag_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.attachments
    ADD CONSTRAINT attachments_metaflag_id_fkey FOREIGN KEY (metaflag_id) REFERENCES public.metaflags(id);


--
-- Name: flags flag_parent_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.flags
    ADD CONSTRAINT flag_parent_fkey FOREIGN KEY (parent) REFERENCES public.metaflags(id);


--
-- Name: ssh_keys ssh_keys_team_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.ssh_keys
    ADD CONSTRAINT ssh_keys_team_id_fkey FOREIGN KEY (team_id) REFERENCES public.teams(id);


--
-- Name: submissions submissions_team_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.submissions
    ADD CONSTRAINT submissions_team_fkey FOREIGN KEY (team_id) REFERENCES public.teams(id);


--
-- Name: TABLE attachments; Type: ACL; Schema: public; Owner: -
--

GRANT SELECT ON TABLE public.attachments TO bbs;


--
-- Name: COLUMN flags.id; Type: ACL; Schema: public; Owner: -
--

GRANT SELECT(id) ON TABLE public.flags TO bbs;


--
-- Name: COLUMN flags.visible; Type: ACL; Schema: public; Owner: -
--

GRANT SELECT(visible) ON TABLE public.flags TO bbs;


--
-- Name: COLUMN flags.enabled; Type: ACL; Schema: public; Owner: -
--

GRANT SELECT(enabled) ON TABLE public.flags TO bbs;


--
-- Name: COLUMN flags.name; Type: ACL; Schema: public; Owner: -
--

GRANT SELECT(name) ON TABLE public.flags TO bbs;


--
-- Name: COLUMN flags.description; Type: ACL; Schema: public; Owner: -
--

GRANT SELECT(description) ON TABLE public.flags TO bbs;


--
-- Name: COLUMN flags.points; Type: ACL; Schema: public; Owner: -
--

GRANT SELECT(points) ON TABLE public.flags TO bbs;


--
-- Name: COLUMN flags.parent; Type: ACL; Schema: public; Owner: -
--

GRANT SELECT(parent) ON TABLE public.flags TO bbs;


--
-- Name: COLUMN flags.bonus; Type: ACL; Schema: public; Owner: -
--

GRANT SELECT(bonus) ON TABLE public.flags TO bbs;


--
-- Name: TABLE ssh_keys; Type: ACL; Schema: public; Owner: -
--

GRANT SELECT,INSERT ON TABLE public.ssh_keys TO bbs;


--
-- Name: TABLE submissions; Type: ACL; Schema: public; Owner: -
--

GRANT INSERT ON TABLE public.submissions TO bbs;


--
-- Name: COLUMN submissions.id; Type: ACL; Schema: public; Owner: -
--

GRANT SELECT(id) ON TABLE public.submissions TO bbs;


--
-- Name: TABLE teams; Type: ACL; Schema: public; Owner: -
--

GRANT SELECT,INSERT ON TABLE public.teams TO bbs;


--
-- Name: TABLE v_submission_count; Type: ACL; Schema: public; Owner: -
--

GRANT SELECT ON TABLE public.v_submission_count TO bbs;


--
-- PostgreSQL database dump complete
--