Skip to content
Snippets Groups Projects
_ds_postgres_repository.sql 1.56 KiB
Newer Older
-- :name create-repository!
-- :command :execute
-- :result :raw
-- :doc creates a new repository table
CREATE TABLE :i:repository-table (
	document_id varchar(64) NOT NULL,
	data_type :i:ds-type-fqn NOT NULL,
	reference integer NULL,
	created_at timestamptz(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  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;
--;;
DELETE FROM :i:cache-table WHERE repository=:repository-name;
--;;
DELETE FROM :i:document-stats-table WHERE repository=:repository-name;
--;;
DELETE FROM :i:request-log-table WHERE repository=:repository-name;


-- :name update-repository!
-- :command :execute
-- :result :raw
-- :doc update repository configuration
UPDATE :i:repositories-table
	 SET "versioning"=:versioning, caching=:caching, delete_permanently=:delete-permanently
 WHERE id=:repository-name;