dougal-software/etc/db/database-template.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 14.2
-- Dumped by pg_dump version 14.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: dougal; Type: DATABASE; Schema: -; Owner: postgres
--

CREATE DATABASE dougal WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'en_GB.UTF-8';


ALTER DATABASE dougal OWNER TO postgres;

\connect dougal

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: dougal; Type: DATABASE PROPERTIES; Schema: -; Owner: postgres
--

ALTER DATABASE dougal SET search_path TO '$user', 'public', 'topology';


\connect dougal

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: topology; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA topology;


ALTER SCHEMA topology OWNER TO postgres;

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

COMMENT ON SCHEMA topology IS 'PostGIS Topology schema';


--
-- 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: postgis; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;


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

COMMENT ON EXTENSION postgis IS 'PostGIS geometry, geography, and raster spatial types and functions';


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

CREATE EXTENSION IF NOT EXISTS postgis_sfcgal WITH SCHEMA public;


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

COMMENT ON EXTENSION postgis_sfcgal IS 'PostGIS SFCGAL functions';


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

CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology;


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

COMMENT ON EXTENSION postgis_topology IS 'PostGIS topology spatial types and functions';


--
-- Name: queue_item_status; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE public.queue_item_status AS ENUM (
    'queued',
    'cancelled',
    'failed',
    'sent'
);


ALTER TYPE public.queue_item_status OWNER TO postgres;

--
-- Name: event_meta(timestamp with time zone); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.event_meta(tstamp timestamp with time zone) RETURNS jsonb
    LANGUAGE plpgsql
    AS $$
	BEGIN
		RETURN event_meta(tstamp, NULL, NULL);
	END;
	$$;


ALTER FUNCTION public.event_meta(tstamp timestamp with time zone) OWNER TO postgres;

--
-- Name: FUNCTION event_meta(tstamp timestamp with time zone); Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON FUNCTION public.event_meta(tstamp timestamp with time zone) IS 'Overload of event_meta (timestamptz, integer, integer) for use when searching by timestamp.';


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

CREATE FUNCTION public.event_meta(sequence integer, point integer) RETURNS jsonb
    LANGUAGE plpgsql
    AS $$
	BEGIN
		RETURN event_meta(NULL, sequence, point);
	END;
	$$;


ALTER FUNCTION public.event_meta(sequence integer, point integer) OWNER TO postgres;

--
-- Name: FUNCTION event_meta(sequence integer, point integer); Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON FUNCTION public.event_meta(sequence integer, point integer) IS 'Overload of event_meta (timestamptz, integer, integer) for use when searching by sequence / point.';


--
-- Name: event_meta(timestamp with time zone, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.event_meta(tstamp timestamp with time zone, sequence integer, point integer) RETURNS jsonb
    LANGUAGE plpgsql
    AS $$
	DECLARE
		result jsonb;
		-- Tolerance is hard-coded, at least until a need to expose arises.
		tolerance numeric;
	BEGIN
		tolerance := 3; -- seconds

		-- We search by timestamp if we can, as that's a lot quicker
		IF tstamp IS NOT NULL THEN

			SELECT meta
			INTO result
			FROM real_time_inputs rti
			WHERE
				rti.tstamp BETWEEN (event_meta.tstamp - tolerance * interval '1 second') AND (event_meta.tstamp + tolerance * interval '1 second')
			ORDER BY abs(extract('epoch' FROM rti.tstamp - event_meta.tstamp ))
			LIMIT 1;

		ELSE

			SELECT meta
			INTO result
			FROM real_time_inputs rti
			WHERE
				(meta->>'_sequence')::integer = event_meta.sequence AND
				(meta->>'_point')::integer = event_meta.point
			ORDER BY rti.tstamp DESC
			LIMIT 1;

		END IF;

		RETURN result;

	END;
	$$;


ALTER FUNCTION public.event_meta(tstamp timestamp with time zone, sequence integer, point integer) OWNER TO postgres;

--
-- Name: FUNCTION event_meta(tstamp timestamp with time zone, sequence integer, point integer); Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON FUNCTION public.event_meta(tstamp timestamp with time zone, sequence integer, point integer) IS 'Return the real-time event metadata associated with a sequence / point in the current project or
	with a given timestamp. Timestamp that is first searched for in the shot tables
	of the current prospect or, if not found, in the real-time data.

	Returns a JSONB object.';


--
-- Name: geometry_from_tstamp(timestamp with time zone, numeric); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.geometry_from_tstamp(ts timestamp with time zone, tolerance numeric, OUT geometry public.geometry, OUT delta numeric) RETURNS record
    LANGUAGE sql
    AS $$
		SELECT
			geometry,
			extract('epoch' FROM tstamp - ts ) AS delta
		FROM real_time_inputs
		WHERE
			geometry IS NOT NULL AND
			tstamp BETWEEN (ts - tolerance * interval '1 second') AND (ts + tolerance * interval '1 second')
		ORDER BY abs(extract('epoch' FROM tstamp - ts ))
		LIMIT 1;
	$$;


ALTER FUNCTION public.geometry_from_tstamp(ts timestamp with time zone, tolerance numeric, OUT geometry public.geometry, OUT delta numeric) OWNER TO postgres;

--
-- Name: FUNCTION geometry_from_tstamp(ts timestamp with time zone, tolerance numeric, OUT geometry public.geometry, OUT delta numeric); Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON FUNCTION public.geometry_from_tstamp(ts timestamp with time zone, tolerance numeric, OUT geometry public.geometry, OUT delta numeric) IS 'Get geometry from timestamp';


--
-- Name: interpolate_geometry_from_tstamp(timestamp with time zone, numeric); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.interpolate_geometry_from_tstamp(ts timestamp with time zone, maxspan numeric) RETURNS public.geometry
    LANGUAGE plpgsql
    AS $$
	DECLARE
		ts0 timestamptz;
		ts1 timestamptz;
		geom0 geometry;
		geom1 geometry;
		span numeric;
		fraction numeric;
	BEGIN

		SELECT tstamp, geometry
		INTO ts0, geom0
		FROM real_time_inputs
		WHERE tstamp <= ts
		ORDER BY tstamp DESC
		LIMIT 1;

		SELECT tstamp, geometry
		INTO ts1, geom1
		FROM real_time_inputs
		WHERE tstamp >= ts
		ORDER BY tstamp ASC
		LIMIT 1;

		IF geom0 IS NULL OR geom1 IS NULL THEN
			RAISE NOTICE 'Interpolation failed (no straddling data)';
			RETURN NULL;
		END IF;

		-- See if we got an exact match
		IF ts0 = ts THEN
			RETURN geom0;
		ELSIF ts1 = ts THEN
			RETURN geom1;
		END IF;

		span := extract('epoch' FROM ts1 - ts0);

		IF span > maxspan THEN
			RAISE NOTICE 'Interpolation timespan % outside maximum requested (%)', span, maxspan;
			RETURN NULL;
		END IF;

		fraction := extract('epoch' FROM ts - ts0) / span;

		IF fraction < 0 OR fraction > 1 THEN
			RAISE NOTICE 'Requested timestamp % outside of interpolation span (fraction: %)', ts, fraction;
			RETURN NULL;
		END IF;

		RETURN ST_LineInterpolatePoint(St_MakeLine(geom0, geom1), fraction);

	END;
	$$;


ALTER FUNCTION public.interpolate_geometry_from_tstamp(ts timestamp with time zone, maxspan numeric) OWNER TO postgres;

--
-- Name: FUNCTION interpolate_geometry_from_tstamp(ts timestamp with time zone, maxspan numeric); Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON FUNCTION public.interpolate_geometry_from_tstamp(ts timestamp with time zone, maxspan numeric) IS 'Interpolate a position over a given maximum timespan (in seconds)
	based on real-time inputs. Returns a POINT geometry.';


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

CREATE FUNCTION public.notify() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
channel text := TG_ARGV[0];
payload text;
pid text;
BEGIN

SELECT projects.pid INTO pid FROM projects WHERE schema = TG_TABLE_SCHEMA;

payload := json_build_object(
'tstamp', CURRENT_TIMESTAMP,
'operation', TG_OP,
'schema', TG_TABLE_SCHEMA,
'table', TG_TABLE_NAME,
'old', row_to_json(OLD),
'new', row_to_json(NEW),
'pid', pid
)::text;

IF octet_length(payload) < 8000 THEN
PERFORM pg_notify(channel, payload);
ELSE
-- We need to find another solution
RAISE INFO 'Payload over limit';
END IF;
RETURN NULL;
END;
$$;


ALTER FUNCTION public.notify() OWNER TO postgres;

--
-- Name: sequence_shot_from_tstamp(timestamp with time zone); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.sequence_shot_from_tstamp(ts timestamp with time zone, OUT sequence numeric, OUT point numeric, OUT delta numeric) RETURNS record
    LANGUAGE sql
    AS $$
		SELECT * FROM public.sequence_shot_from_tstamp(ts, 3);
	$$;


ALTER FUNCTION public.sequence_shot_from_tstamp(ts timestamp with time zone, OUT sequence numeric, OUT point numeric, OUT delta numeric) OWNER TO postgres;

--
-- Name: FUNCTION sequence_shot_from_tstamp(ts timestamp with time zone, OUT sequence numeric, OUT point numeric, OUT delta numeric); Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON FUNCTION public.sequence_shot_from_tstamp(ts timestamp with time zone, OUT sequence numeric, OUT point numeric, OUT delta numeric) IS 'Get sequence and shotpoint from timestamp.

Overloaded form in which the tolerance value is implied and defaults to three seconds.';


--
-- Name: sequence_shot_from_tstamp(timestamp with time zone, numeric); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.sequence_shot_from_tstamp(ts timestamp with time zone, tolerance numeric, OUT sequence numeric, OUT point numeric, OUT delta numeric) RETURNS record
    LANGUAGE sql
    AS $$
		SELECT
			(meta->>'_sequence')::numeric AS sequence,
			(meta->>'_point')::numeric AS point,
			extract('epoch' FROM (meta->>'tstamp')::timestamptz - ts ) AS delta
		FROM real_time_inputs
		WHERE
			meta ? '_sequence' AND
			abs(extract('epoch' FROM (meta->>'tstamp')::timestamptz - ts )) < tolerance
		ORDER BY abs(extract('epoch' FROM (meta->>'tstamp')::timestamptz - ts ))
		LIMIT 1;
	$$;


ALTER FUNCTION public.sequence_shot_from_tstamp(ts timestamp with time zone, tolerance numeric, OUT sequence numeric, OUT point numeric, OUT delta numeric) OWNER TO postgres;

--
-- Name: FUNCTION sequence_shot_from_tstamp(ts timestamp with time zone, tolerance numeric, OUT sequence numeric, OUT point numeric, OUT delta numeric); Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON FUNCTION public.sequence_shot_from_tstamp(ts timestamp with time zone, tolerance numeric, OUT sequence numeric, OUT point numeric, OUT delta numeric) IS 'Get sequence and shotpoint from timestamp.

Given a timestamp this function returns the closest shot to it within the given tolerance value.

This uses the `real_time_inputs` table and it does not give an indication of which project the shotpoint belongs to. It is assumed that a single project is being acquired at a given time.';


--
-- Name: set_survey(text); Type: PROCEDURE; Schema: public; Owner: postgres
--

CREATE PROCEDURE public.set_survey(IN project_id text)
    LANGUAGE sql
    AS $$
	SELECT set_config('search_path', (SELECT schema||',public' FROM public.projects WHERE pid = lower(project_id)), false);
$$;


ALTER PROCEDURE public.set_survey(IN project_id text) OWNER TO postgres;

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

CREATE FUNCTION public.update_timestamp() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
	BEGIN
		IF NEW.updated_on IS NOT NULL THEN
			NEW.updated_on := current_timestamp;
		END IF;
		RETURN NEW;
	EXCEPTION
		WHEN undefined_column THEN RETURN NEW;
	END;
$$;


ALTER FUNCTION public.update_timestamp() OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: info; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.info (
    key text NOT NULL,
    value jsonb
);


ALTER TABLE public.info OWNER TO postgres;

--
-- Name: projects; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.projects (
    pid text NOT NULL,
    name text NOT NULL,
    schema text NOT NULL,
    meta jsonb DEFAULT '{}'::jsonb NOT NULL
);


ALTER TABLE public.projects OWNER TO postgres;

--
-- Name: queue_items; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.queue_items (
    item_id integer NOT NULL,
    status public.queue_item_status DEFAULT 'queued'::public.queue_item_status NOT NULL,
    payload jsonb NOT NULL,
    results jsonb DEFAULT '{}'::jsonb NOT NULL,
    created_on timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_on timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    not_before timestamp with time zone DEFAULT '1970-01-01 00:00:00+00'::timestamp with time zone NOT NULL,
    parent_id integer
);


ALTER TABLE public.queue_items OWNER TO postgres;

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

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


ALTER TABLE public.queue_items_item_id_seq OWNER TO postgres;

--
-- Name: queue_items_item_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.queue_items_item_id_seq OWNED BY public.queue_items.item_id;


--
-- Name: real_time_inputs; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.real_time_inputs (
    tstamp timestamp with time zone NOT NULL,
    geometry public.geometry(Point,4326),
    meta jsonb
);


ALTER TABLE public.real_time_inputs OWNER TO postgres;

--
-- Name: queue_items item_id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.queue_items ALTER COLUMN item_id SET DEFAULT nextval('public.queue_items_item_id_seq'::regclass);


--
-- Name: info info_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.info
    ADD CONSTRAINT info_pkey PRIMARY KEY (key);


--
-- Name: projects projects_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.projects
    ADD CONSTRAINT projects_name_key UNIQUE (name);


--
-- Name: projects projects_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.projects
    ADD CONSTRAINT projects_pkey PRIMARY KEY (pid);


--
-- Name: projects projects_schema_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.projects
    ADD CONSTRAINT projects_schema_key UNIQUE (schema);


--
-- Name: queue_items queue_items_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.queue_items
    ADD CONSTRAINT queue_items_pkey PRIMARY KEY (item_id);


--
-- Name: tstamp_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX tstamp_idx ON public.real_time_inputs USING btree (tstamp DESC);


--
-- Name: info info_tg; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER info_tg AFTER INSERT OR DELETE OR UPDATE ON public.info FOR EACH ROW EXECUTE FUNCTION public.notify('info');


--
-- Name: projects projects_tg; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER projects_tg AFTER INSERT OR DELETE OR UPDATE ON public.projects FOR EACH ROW EXECUTE FUNCTION public.notify('project');


--
-- Name: queue_items queue_items_tg0; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER queue_items_tg0 BEFORE INSERT OR UPDATE ON public.queue_items FOR EACH ROW EXECUTE FUNCTION public.update_timestamp();


--
-- Name: queue_items queue_items_tg1; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER queue_items_tg1 AFTER INSERT OR DELETE OR UPDATE ON public.queue_items FOR EACH ROW EXECUTE FUNCTION public.notify('queue_items');


--
-- Name: real_time_inputs real_time_inputs_tg; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER real_time_inputs_tg AFTER INSERT ON public.real_time_inputs FOR EACH ROW EXECUTE FUNCTION public.notify('realtime');


--
-- Name: queue_items queue_items_parent_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.queue_items
    ADD CONSTRAINT queue_items_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES public.queue_items(item_id);


--
-- PostgreSQL database dump complete
--