_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-document-frame :? :1
-- :doc retrieve a document-frame given the uuid.
SELECT * FROM :i:repository-table WHERE id = :id

-- :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
 GROUP BY document_id

-- :name create-document-frame! :! :n
-- :doc creates a new document frame record
INSERT INTO :i:repository-table
            (id, document_id, data_type, data_encoding, version,  reference, "data")
            VALUES (:uuid, :document-id, :type, :encoding, :version, :reference, :data)

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