-- This script creates the geogig tables in the database it's executed
-- e.g.: psql -d geogig-database -f geogig_postgres.sql
-- The database DOES NOT need PostGIS. A plain PostgreSQL database is used as a pure key/value store for most needs
-- Beware that for performance reasons, PostgreSQL 9.4 or newer is recommended, since prior versions have a
-- performance issue with hash indexing that has been fixed since
SELECT pg_advisory_lock(-1);
-- Replace <database name> in the line bellow by the name of the database where the script is being run
ALTER DATABASE "<database name>" SET bytea_output = 'escape';
SELECT pg_advisory_unlock(-1);

SET constraint_exclusion=ON;

-- This function is to create a type if it does not exist since there's no support for IF NOT EXISTS for CREATE TYPE
-- The composite type OBJECTID decomposes a 20-byte SHA-1 hash into one integer and two longs
-- taking the first 4 bytes of the SHA-1 hash for the integer, the next 8 bytes for the first
-- long, and the last 8 bytes for the second long. The transcoding to and from OBJECTID is performed
-- by geogig's PostgreSQL storage backend.
-- OBJECTID serves as the identifier for the geogig_object table, which stores the revision objects (commits,
-- trees, tags, features, and featuretypes).
-- The decomposition into these three numbers allows to create a hash index on the first member, the integer
-- field, and hence to perform very fast lookups. Possible hash collissions (where more than one row have the
-- same integer value for the OBJECTID's h1 attribute) are resolved in the query itself making use of the hash
-- index as follows. 
-- Given an OBJECTID with values (1,2,3): SELECT * FROM geogig_object WHERE ((id).h1) = 1 AND id = CAST(ROW(1, 2, 3) AS OBJECTID)
-- GeoGig will also query a concrete table when it has enough information as to know what to look up for.
-- For example, the above query may be querying geogig_object_tree directly if GeoGig knows it's been asked
-- for a tree object as opposed to a generic revision object query, or geogig_object_feature_15 if it's been
-- asked specifically for a feature who's SHA-1 id falls into the geogig_object_feature_15 partition range.
CREATE OR REPLACE FUNCTION create_objectid_type() RETURNS integer AS $$
DECLARE v_exists INTEGER;
BEGIN
    SELECT into v_exists (SELECT 1 FROM pg_type WHERE typname = 'objectid');
    IF v_exists IS NULL THEN
        CREATE TYPE OBJECTID AS(h1 INTEGER, h2 BIGINT, h3 BIGINT);
    END IF;
    RETURN v_exists;
END;
$$ LANGUAGE plpgsql;

SELECT create_objectid_type();
DROP function create_objectid_type();
-- end of CREATE TYPE

-- The GeoGig PostgreSQL storage backend allows to host several repositories in the same Postgres database
-- A row in geogig_repository is created whenever a new repository is created, and it's auto incremental
-- repository PK used as the foreign key for the other tables that hold repository specific information
CREATE TABLE geogig_repository (repository serial PRIMARY KEY, created TIMESTAMP);

-- Default value to be used as the GLOBAL repository id foreign key in geogig_config
INSERT INTO geogig_repository (repository, created) VALUES (-1, NOW());

-- Config "database", discriminated by repository PK
CREATE TABLE IF NOT EXISTS geogig_config (repository INTEGER, section TEXT, key TEXT, value TEXT, 
                                          PRIMARY KEY (repository, section, key), 
                                          FOREIGN KEY (repository) REFERENCES geogig_repository(repository) ON DELETE CASCADE);
CREATE INDEX geogig_config_section_idx ON geogig_config (repository, section);

CREATE VIEW geogig_repository_name AS SELECT r.*, c.value AS name FROM geogig_repository r INNER JOIN geogig_config c ON r.repository = c.repository WHERE c.section = 'repo' AND c.key = 'name';

-- Refs "database", discriminated by repository PK
CREATE TABLE geogig_ref (repository INTEGER, path TEXT, name TEXT, value TEXT, 
                         PRIMARY KEY(repository, path, name), 
                         FOREIGN KEY (repository) REFERENCES geogig_repository(repository) ON DELETE CASCADE);

-- Conflicts "database", discriminated by repository PK
CREATE TABLE geogig_conflict (repository INTEGER, namespace TEXT, path TEXT, ancestor bytea, ours bytea NOT NULL, theirs bytea NOT NULL, 
                              PRIMARY KEY(repository, namespace, path), 
                              FOREIGN KEY (repository) REFERENCES geogig_repository(repository) ON DELETE CASCADE);

-- Blobs "database", discriminated by repository PK (these are not the revision objects like commit, etc, but a store
-- for some GeoGig auxiliary information like temporary autogenerated merge messages and such)
CREATE TABLE geogig_blob (repository INTEGER, namespace TEXT, path TEXT, blob BYTEA, 
                          PRIMARY KEY(repository,namespace,path), 
                          FOREIGN KEY (repository) REFERENCES geogig_repository(repository) ON DELETE CASCADE);

-- tables related to DAG indexing
CREATE TABLE geogig_index (repository INTEGER, treeName TEXT, attributeName TEXT, strategy TEXT, metadata BYTEA, PRIMARY KEY(repository, treeName, attributeName), FOREIGN KEY (repository) REFERENCES geogig_repository(repository) ON DELETE CASCADE);
CREATE TABLE geogig_index_mappings (repository INTEGER, indexId OBJECTID, treeId OBJECTID, indexTreeId OBJECTID, PRIMARY KEY(repository, indexId, treeId), FOREIGN KEY (repository) REFERENCES geogig_repository(repository) ON DELETE CASCADE);
CREATE TABLE geogig_index_object (id OBJECTID, object BYTEA) WITHOUT OIDS;
CREATE OR REPLACE RULE geogig_index_object_ignore_duplicate_inserts AS ON INSERT TO geogig_index_object WHERE (EXISTS ( SELECT 1 FROM geogig_index_object WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE INDEX geogig_index_object_objectid_h1_hash ON geogig_index_object (((id).h1));


-- The commit graph "database", discriminated by repository PK, used to have a two-way navigable commit graph
-- to speed up some operations and to hold extra commit metadata such as the upstream commit a given commit is
-- mapped to when creating sparse and shallow clones 
CREATE TABLE geogig_graph_edge (src OBJECTID, dst OBJECTID, PRIMARY KEY (src,dst));
CREATE INDEX geogig_graph_edge_src_index ON geogig_graph_edge(src);
CREATE INDEX geogig_graph_edge_dst_index ON geogig_graph_edge(dst);
CREATE TABLE geogig_graph_property (nid OBJECTID, key VARCHAR, val VARCHAR, PRIMARY KEY(nid,key));
CREATE TABLE geogig_graph_mapping (alias OBJECTID PRIMARY KEY, nid OBJECTID);
CREATE INDEX geogig_graph_mapping_nid_index ON geogig_graph_mapping(nid);

-- geogig_obect is an "abstract" table to store the revision objects (tags, commits, trees, featuretypes, and features)
-- it does not reference geogig_repository since the objects table is shared among all repositories in the same database
-- allowing the object DAG to be shared among repositories and hence avoiding duplication. Cloning a repository from
-- a postgres database to another repository in the same database is hence almost instantaneous, since no revision objects
-- need to be transferred, but only the refs need to be copied in the geogig_refs table for the new repo
CREATE TABLE geogig_object (id OBJECTID, object BYTEA) WITHOUT OIDS;
CREATE TABLE geogig_object_commit ( ) INHERITS(geogig_object);
CREATE TABLE geogig_object_featuretype ( ) INHERITS(geogig_object);
CREATE TABLE geogig_object_tag ( ) INHERITS(geogig_object);
CREATE TABLE geogig_object_tree ( ) INHERITS(geogig_object);
-- geogig_object_feature inherits from geogig_object and is also a paritioned table.
-- constraint_exclustion MUST be set to on, like in the above "SET constraint_exclusion=ON;" statement
-- in order for the range checks to be applied whenever a row is inserted into this table
-- and for the queries to know which partition to look up for a given key
CREATE TABLE geogig_object_feature ( ) INHERITS(geogig_object);
CREATE TABLE geogig_object_feature_0 ( CHECK ( ((id).h1) < -1879048192) ) INHERITS (geogig_object_feature);
CREATE TABLE geogig_object_feature_1 ( CHECK ( ((id).h1) >= -1879048192 AND ((id).h1) < -1610612736) ) INHERITS (geogig_object_feature);
CREATE TABLE geogig_object_feature_2 ( CHECK ( ((id).h1) >= -1610612736 AND ((id).h1) < -1342177280) ) INHERITS (geogig_object_feature);
CREATE TABLE geogig_object_feature_3 ( CHECK ( ((id).h1) >= -1342177280 AND ((id).h1) < -1073741824) ) INHERITS (geogig_object_feature);
CREATE TABLE geogig_object_feature_4 ( CHECK ( ((id).h1) >= -1073741824 AND ((id).h1) < -805306368) ) INHERITS (geogig_object_feature);
CREATE TABLE geogig_object_feature_5 ( CHECK ( ((id).h1) >= -805306368 AND ((id).h1) < -536870912) ) INHERITS (geogig_object_feature);
CREATE TABLE geogig_object_feature_6 ( CHECK ( ((id).h1) >= -536870912 AND ((id).h1) < -268435456) ) INHERITS (geogig_object_feature);
CREATE TABLE geogig_object_feature_7 ( CHECK ( ((id).h1) >= -268435456 AND ((id).h1) < 0) ) INHERITS (geogig_object_feature);
CREATE TABLE geogig_object_feature_8 ( CHECK ( ((id).h1) >= 0 AND ((id).h1) < 268435456) ) INHERITS (geogig_object_feature);
CREATE TABLE geogig_object_feature_9 ( CHECK ( ((id).h1) >= 268435456 AND ((id).h1) < 536870912) ) INHERITS (geogig_object_feature);
CREATE TABLE geogig_object_feature_10 ( CHECK ( ((id).h1) >= 536870912 AND ((id).h1) < 805306368) ) INHERITS (geogig_object_feature);
CREATE TABLE geogig_object_feature_11 ( CHECK ( ((id).h1) >= 805306368 AND ((id).h1) < 1073741824) ) INHERITS (geogig_object_feature);
CREATE TABLE geogig_object_feature_12 ( CHECK ( ((id).h1) >= 1073741824 AND ((id).h1) < 1342177280) ) INHERITS (geogig_object_feature);
CREATE TABLE geogig_object_feature_13 ( CHECK ( ((id).h1) >= 1342177280 AND ((id).h1) < 1610612736) ) INHERITS (geogig_object_feature);
CREATE TABLE geogig_object_feature_14 ( CHECK ( ((id).h1) >= 1610612736 AND ((id).h1) < 1879048192) ) INHERITS (geogig_object_feature);
CREATE TABLE geogig_object_feature_15 ( CHECK ( ((id).h1) >= 1879048192 ) ) INHERITS (geogig_object_feature);

-- We use a HASH index on the first member of the composite OBJECTID type. This imposes a huge performance benefit when
-- using postgres as a pure key value store to perform (starting with version 9.4, prior versions have a bug in hash
-- indexing that has been fixed since). We don't need to do range look ups, our usage pattern is always to look up an
-- object by it's SHA-1 identifier, which OBJECT id decomposes into one integer and two longs
CREATE INDEX geogig_object_commit_objectid_h1_hash ON geogig_object_commit (((id).h1));
CREATE INDEX geogig_object_featuretype_objectid_h1_hash ON geogig_object_featuretype (((id).h1));
CREATE INDEX geogig_object_tag_objectid_h1_hash ON geogig_object_tag (((id).h1));
CREATE INDEX geogig_object_tree_objectid_h1_hash ON geogig_object_tree (((id).h1));
CREATE INDEX geogig_object_feature_0_objectid_h1_hash ON geogig_object_feature_0 (((id).h1));
CREATE INDEX geogig_object_feature_1_objectid_h1_hash ON geogig_object_feature_1 (((id).h1));
CREATE INDEX geogig_object_feature_2_objectid_h1_hash ON geogig_object_feature_2 (((id).h1));
CREATE INDEX geogig_object_feature_3_objectid_h1_hash ON geogig_object_feature_3 (((id).h1));
CREATE INDEX geogig_object_feature_4_objectid_h1_hash ON geogig_object_feature_4 (((id).h1));
CREATE INDEX geogig_object_feature_5_objectid_h1_hash ON geogig_object_feature_5 (((id).h1));
CREATE INDEX geogig_object_feature_6_objectid_h1_hash ON geogig_object_feature_6 (((id).h1));
CREATE INDEX geogig_object_feature_7_objectid_h1_hash ON geogig_object_feature_7 (((id).h1));
CREATE INDEX geogig_object_feature_8_objectid_h1_hash ON geogig_object_feature_8 (((id).h1));
CREATE INDEX geogig_object_feature_9_objectid_h1_hash ON geogig_object_feature_9 (((id).h1));
CREATE INDEX geogig_object_feature_10_objectid_h1_hash ON geogig_object_feature_10 (((id).h1));
CREATE INDEX geogig_object_feature_11_objectid_h1_hash ON geogig_object_feature_11 (((id).h1));
CREATE INDEX geogig_object_feature_12_objectid_h1_hash ON geogig_object_feature_12 (((id).h1));
CREATE INDEX geogig_object_feature_13_objectid_h1_hash ON geogig_object_feature_13 (((id).h1));
CREATE INDEX geogig_object_feature_14_objectid_h1_hash ON geogig_object_feature_14 (((id).h1));
CREATE INDEX geogig_object_feature_15_objectid_h1_hash ON geogig_object_feature_15 (((id).h1));

-- Given geogig_object and it's inherited tables don't have a primary key, these rules are used to avoid duplicates
CREATE OR REPLACE RULE geogig_object_commit_ignore_duplicate_inserts AS ON INSERT TO geogig_object_commit WHERE (EXISTS ( SELECT 1 FROM geogig_object_commit WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE geogig_object_featuretype_ignore_duplicate_inserts AS ON INSERT TO geogig_object_featuretype WHERE (EXISTS ( SELECT 1 FROM geogig_object_featuretype WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE geogig_object_tag_ignore_duplicate_inserts AS ON INSERT TO geogig_object_tag WHERE (EXISTS ( SELECT 1 FROM geogig_object_tag WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE geogig_object_tree_ignore_duplicate_inserts AS ON INSERT TO geogig_object_tree WHERE (EXISTS ( SELECT 1 FROM geogig_object_tree WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE geogig_object_feature_0_ignore_duplicate_inserts AS ON INSERT TO geogig_object_feature_0 WHERE (EXISTS ( SELECT 1 FROM geogig_object_feature_0 WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE geogig_object_feature_1_ignore_duplicate_inserts AS ON INSERT TO geogig_object_feature_1 WHERE (EXISTS ( SELECT 1 FROM geogig_object_feature_1 WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE geogig_object_feature_2_ignore_duplicate_inserts AS ON INSERT TO geogig_object_feature_2 WHERE (EXISTS ( SELECT 1 FROM geogig_object_feature_2 WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE geogig_object_feature_3_ignore_duplicate_inserts AS ON INSERT TO geogig_object_feature_3 WHERE (EXISTS ( SELECT 1 FROM geogig_object_feature_3 WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE geogig_object_feature_4_ignore_duplicate_inserts AS ON INSERT TO geogig_object_feature_4 WHERE (EXISTS ( SELECT 1 FROM geogig_object_feature_4 WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE geogig_object_feature_5_ignore_duplicate_inserts AS ON INSERT TO geogig_object_feature_5 WHERE (EXISTS ( SELECT 1 FROM geogig_object_feature_5 WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE geogig_object_feature_6_ignore_duplicate_inserts AS ON INSERT TO geogig_object_feature_6 WHERE (EXISTS ( SELECT 1 FROM geogig_object_feature_6 WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE geogig_object_feature_7_ignore_duplicate_inserts AS ON INSERT TO geogig_object_feature_7 WHERE (EXISTS ( SELECT 1 FROM geogig_object_feature_7 WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE geogig_object_feature_8_ignore_duplicate_inserts AS ON INSERT TO geogig_object_feature_8 WHERE (EXISTS ( SELECT 1 FROM geogig_object_feature_8 WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE geogig_object_feature_9_ignore_duplicate_inserts AS ON INSERT TO geogig_object_feature_9 WHERE (EXISTS ( SELECT 1 FROM geogig_object_feature_9 WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE geogig_object_feature_10_ignore_duplicate_inserts AS ON INSERT TO geogig_object_feature_10 WHERE (EXISTS ( SELECT 1 FROM geogig_object_feature_10 WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE geogig_object_feature_11_ignore_duplicate_inserts AS ON INSERT TO geogig_object_feature_11 WHERE (EXISTS ( SELECT 1 FROM geogig_object_feature_11 WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE geogig_object_feature_12_ignore_duplicate_inserts AS ON INSERT TO geogig_object_feature_12 WHERE (EXISTS ( SELECT 1 FROM geogig_object_feature_12 WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE geogig_object_feature_13_ignore_duplicate_inserts AS ON INSERT TO geogig_object_feature_13 WHERE (EXISTS ( SELECT 1 FROM geogig_object_feature_13 WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE geogig_object_feature_14_ignore_duplicate_inserts AS ON INSERT TO geogig_object_feature_14 WHERE (EXISTS ( SELECT 1 FROM geogig_object_feature_14 WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE geogig_object_feature_15_ignore_duplicate_inserts AS ON INSERT TO geogig_object_feature_15 WHERE (EXISTS ( SELECT 1 FROM geogig_object_feature_15 WHERE ((id).h1) = (NEW.id).h1 AND id = NEW.id)) DO INSTEAD NOTHING;


-- The trigger that redirects inserts to geogig_object_feature to the correct partition
-- based on the OBJECTID's h1 field, homogeneously distributed among the int4 value range
CREATE OR REPLACE FUNCTION geogig_object_feature_partitioning_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE

id objectid;
h1 integer;

BEGIN

id = NEW.id;
-- raise notice 'id : %', id;
h1 = id.h1;
-- raise notice 'h1 : %', h1;
IF ( h1 >= -2147483648 AND h1 < -1879048192 ) THEN
  INSERT INTO geogig_object_feature_0 VALUES (NEW.*);
ELSIF ( h1 >= -1879048192 AND h1 < -1610612736 ) THEN
  INSERT INTO geogig_object_feature_1 VALUES (NEW.*);
ELSIF ( h1 >= -1610612736 AND h1 < -1342177280 ) THEN
  INSERT INTO geogig_object_feature_2 VALUES (NEW.*);
ELSIF ( h1 >= -1342177280 AND h1 < -1073741824 ) THEN
  INSERT INTO geogig_object_feature_3 VALUES (NEW.*);
ELSIF ( h1 >= -1073741824 AND h1 < -805306368 ) THEN
  INSERT INTO geogig_object_feature_4 VALUES (NEW.*);
ELSIF ( h1 >= -805306368 AND h1 < -536870912 ) THEN
  INSERT INTO geogig_object_feature_5 VALUES (NEW.*);
ELSIF ( h1 >= -536870912 AND h1 < -268435456 ) THEN
  INSERT INTO geogig_object_feature_6 VALUES (NEW.*);
ELSIF ( h1 >= -268435456 AND h1 < 0 ) THEN
  INSERT INTO geogig_object_feature_7 VALUES (NEW.*);
ELSIF ( h1 >= 0 AND h1 < 268435456 ) THEN
  INSERT INTO geogig_object_feature_8 VALUES (NEW.*);
ELSIF ( h1 >= 268435456 AND h1 < 536870912 ) THEN
  INSERT INTO geogig_object_feature_9 VALUES (NEW.*);
ELSIF ( h1 >= 536870912 AND h1 < 805306368 ) THEN
  INSERT INTO geogig_object_feature_10 VALUES (NEW.*);
ELSIF ( h1 >= 805306368 AND h1 < 1073741824 ) THEN
  INSERT INTO geogig_object_feature_11 VALUES (NEW.*);
ELSIF ( h1 >= 1073741824 AND h1 < 1342177280 ) THEN
  INSERT INTO geogig_object_feature_12 VALUES (NEW.*);
ELSIF ( h1 >= 1342177280 AND h1 < 1610612736 ) THEN
  INSERT INTO geogig_object_feature_13 VALUES (NEW.*);
ELSIF ( h1 >= 1610612736 AND h1 < 1879048192 ) THEN
  INSERT INTO geogig_object_feature_14 VALUES (NEW.*);
ELSIF ( h1 >= 1879048192 ) THEN
  INSERT INTO geogig_object_feature_15 VALUES (NEW.*);
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
;
CREATE TRIGGER geogig_object_feature_partitioning_insert_trigger BEFORE INSERT ON geogig_object_feature FOR EACH ROW EXECUTE PROCEDURE geogig_object_feature_partitioning_insert_trigger();

