_ds_postgres.sql

-- :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

-- :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;