_ds_postgres_repository.sql

-- :name create-repository!
-- :command :execute
-- :result :raw
-- :doc creates a new repository table
CREATE TABLE :i:repository-table (
	document_id varchar(64) NOT NULL,
	id varchar(32) NOT NULL,
	data_type :i:ds-type-fqn NOT NULL,
	reference varchar(32) NULL,
	created_at timestamptz(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
	"data" bytea NOT NULL,
  datasize numeric NULL GENERATED ALWAYS AS (length("data")) STORED,
	"version" int8 NOT NULL DEFAULT 0,
	data_encoding :i:ds-encoding-fqn NOT NULL,
	CONSTRAINT
  --~ (str "repo_" (:repository-name params) "_pk")
  PRIMARY KEY (id),
  CONSTRAINT
  --~ (str "repo_" (:repository-name params) "_uk")
  UNIQUE (version, document_id)
);
--;;
CREATE INDEX
  --~ (str "repo_" (:repository-name params) "_document_id_idx")
  ON :i:repository-table USING btree (document_id);
--;;
INSERT INTO :i:repositories-table
            (id, caching, versioning)
VALUES (:repository-name, :caching, :versioning)


-- :name delete-repository!
-- :command :execute
-- :result :raw
-- :doc delete the repository table
DROP TABLE :i:repository-table;
--;;
DELETE FROM :i:repositories-table
 WHERE id=:repository-name;