Newer
Older
-- :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,

Bruno Burke
committed
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));
--;;

Bruno Burke
committed
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',

Bruno Burke
committed
'edn',
'nippy');
--;;
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")
);

Bruno Burke
committed
--;;
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)
);