Newer
Older
-- :name schema-exists? :? :*
-- :doc check if the dstorage schema exists
SELECT exists(select schema_name FROM information_schema.schemata WHERE schema_name = :storage-schema);
-- :name get-data :? :*
-- :doc retrieve a user given the id.
SELECT * FROM dstorage.users
WHERE id = :id
-- :name get-repositories :? :*
-- :doc retrieve a list of all repositories.
SELECT * FROM :i:repositories-table

Bruno Burke
committed
-- :name get-repository-info :? :1
-- :doc retrieve repository infos.
SELECT * FROM :i:repositories-table WHERE id = :repository-name
-- :name get-document-frame :? :1
-- :doc retrieve a document-frame given the uuid.
SELECT * FROM :i:repository-table WHERE id = :id::integer
-- :name get-document-versions :? :*
-- :doc retrieve all document-versions given the document-id.
SELECT version, data_type, created_at, id, created_at as "date"
FROM :i:repository-table
WHERE document_id = :document-id
ORDER BY version DESC
-- :name get-document-frame-by-version :? :1
-- :doc retrieve a document-frame given the document-id and version.
SELECT * FROM :i:repository-table WHERE document_id = :document-id AND version = :version
-- :name get-all-document-ids :? :*
-- :doc retrieve all document-ids
SELECT document_id
FROM :i:repository-table
WHERE document_id NOT IN (SELECT document_id FROM :i:repository-table WHERE data_type = 'delete')
GROUP BY document_id
-- :name create-document-frame! :! :n
-- :doc creates a new document frame record
INSERT INTO :i:repository-table
(document_id, data_type, data_encoding, version, reference, "data")
VALUES (:document-id, CAST(:type::varchar AS :i:ds-type-fqn), CAST(:encoding::varchar AS :i:ds-encoding-fqn), :version, :reference, :data);
--;; TODO type cast is problematic with multiple storage schemas in one database
-- :name overwrite-document-frame! :! :n
-- :doc overwrite a document frame record given the version
UPDATE :i:repository-table
SET data_type = CAST(:type::varchar AS :i:ds-type-fqn),
data_encoding = CAST(:encoding::varchar AS :i:ds-encoding-fqn),
reference = :reference,
created_at = CURRENT_TIMESTAMP,
"data" = :data
WHERE document_id = :document-id AND version = :version;
-- :name get-last-document-frame :? :1
-- :doc retrieve the last document-frame given the document-id.
SELECT * FROM :i:repository-table WHERE document_id = :document-id order by "version" desc limit 1
-- :name select-relevant-document-frames :? :*
-- :doc retrieve all necessary document frames to restore the latest document version
with RECURSIVE rec AS (
SELECT ru.*, 0 AS degree
FROM :i:repository-table ru, (select rr.id from :i:repository-table rr where document_id = :document-id
order by version desc limit 1) as init
WHERE ru.id = init.id
UNION
SELECT ru.*, rec.degree + 1 AS degree
FROM :i:repository-table ru
JOIN rec ON ru.id = rec.reference and rec.data_type != 'key')
select * from rec;
-- :name select-relevant-document-frames-by-version :? :*
-- :doc retrieve all necessary document frames to restore a given document version
with RECURSIVE rec AS (
SELECT ru.*, 0 AS degree
FROM :i:repository-table ru, (select rr.id from :i:repository-table rr where document_id = :document-id
AND version = :version) as init
WHERE ru.id = init.id
UNION
SELECT ru.*, rec.degree + 1 AS degree
FROM :i:repository-table ru
JOIN rec ON ru.id = rec.reference and rec.data_type != 'key')
select * from rec;