_ds_postgres_storage.sql

-- :name create-storage!
-- :command :execute
-- :result :raw
-- :doc creates the document storage tables and datatypes
CREATE SCHEMA IF NOT EXISTS :i:schema-name;
--;;
CREATE TABLE IF NOT EXISTS :i:repositories-table (
	id varchar(32) NOT NULL,
  caching bool NOT NULL DEFAULT FALSE,
	"versioning" bool NOT NULL DEFAULT TRUE,
	created_at timestamptz(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT repositories_pk PRIMARY KEY (id));
--;;
ALTER TABLE :i:repositories-table ADD COLUMN IF NOT EXISTS delete_permanently bool NOT NULL DEFAULT false;
--;;
DO $$DECLARE
  BEGIN
    CREATE TYPE
      :i:ds-type-fqn
      AS ENUM (
	      'keydiffcopydelete',
	      'key',
	      'diff',
	      'clone',
	      'delete');
EXCEPTION
  when others then
end $$;
--;;
DO $$DECLARE
  BEGIN
    CREATE TYPE
      :i:ds-encoding-fqn
      AS ENUM (
	      'transit+json',
	      'edn',
	      'nippy');
EXCEPTION
  when others then
end $$;
--;;
CREATE TABLE IF NOT EXISTS :i:cache-table (
	repository varchar(32) NOT NULL,
	"document" varchar(64) NOT NULL,
	"version" int8 NOT NULL,
	"data" bytea NOT NULL,
  datasize numeric NULL GENERATED ALWAYS AS (length("data")) STORED,
	created_at timestamptz NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT newtable_pk PRIMARY KEY (repository,"document","version")
);
--;;
CREATE TABLE IF NOT EXISTS :i:request-log-table (
	repository varchar(32) NOT NULL,
	"document" varchar(64) NOT NULL,
	"version" int8 NOT NULL,
	created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT request_log_pk PRIMARY KEY (repository, document, version, created_at)
);
--;;
CREATE TABLE IF NOT EXISTS :i:document-stats-table (
	repository varchar(32) NOT NULL,
	"document" varchar(64) NOT NULL,
	"version" int8 NOT NULL,
	request_count int8 NULL DEFAULT 0,
	created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT document_stats_pk PRIMARY KEY (repository, document, version)
);