artifacts.id.nextval=SELECT NEXTVAL('ARTIFACTS_ID_SEQ')

artifacts.insert=INSERT INTO artifacts \
    (id, gid, creation, last_access, ttl, factory, data) \
    VALUES (?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?, ?)

artifacts.update=UPDATE artifacts SET last_access = CURRENT_TIMESTAMP, \
    data = ? WHERE id = ?

artifacts.touch=UPDATE artifacts SET last_access = CURRENT_TIMESTAMP WHERE id = ?

artifacts.outdated=SELECT id, factory, data FROM artifacts WHERE ttl IS NOT NULL \
    AND DATEDIFF('MILLISECOND', last_access, CURRENT_TIMESTAMP) > ttl LIMIT 50

artifacts.select.gid=SELECT id, last_access, ttl, factory, data FROM artifacts WHERE gid = ?

artifacts.get.id=SELECT id FROM artifacts WHERE gid = ?

artifacts.replace=UPDATE artifacts SET \
    creation = CURRENT_TIMESTAMP, last_access = CURRENT_TIMESTAMP, \
    ttl = ?, factory = ?, data = ? \
    WHERE id = ?

artifacts.delete=DELETE FROM artifacts WHERE id = ?

# USERS

users.id.nextval=SELECT NEXTVAL('USERS_ID_SEQ')

users.insert=INSERT INTO users (id, gid, name, role) VALUES (?, ?, ?, ?)

users.select.id.by.gid=SELECT id FROM users WHERE gid = ?
users.select.gid=SELECT id, name, role FROM users WHERE gid = ?

users.delete.id=DELETE FROM users WHERE id = ?

users.delete.collections=DELETE FROM collections where owner_id = ?

users.select.all=SELECT id, gid, name, role FROM users

# COLLECTIONS
collections.id.nextval=SELECT NEXTVAL('COLLECTIONS_ID_SEQ')

delete.user.collection.items= \
    DELETE FROM collection_items WHERE collection_id IN \
    (SELECT id FROM collections WHERE owner_id = ?)

collections.insert= \
    INSERT INTO collections (id, gid, name, owner_id, creation, last_access, ttl) \
    VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?)

collection.creation.time=SELECT creation from collections WHERE id = ?

collections.select.user= \
    SELECT c.gid, c.name, c.creation, u.gid FROM \
    collections c OUTER LEFT JOIN users u ON c.owner_id = u.id \
    WHERE u.gid = ?

collections.select.all= \
    SELECT c.gid, c.name, c.creation, u.gid FROM \
    collections c OUTER LEFT JOIN users u ON c.owner_id = u.id

users.collections=SELECT collection_id, gid, name FROM collections WHERE owner_id = ?
users.collection.ids=SELECT collection_id FROM collections WHERE owner_id = ?

users.delete.all.collections=DELETE FROM collections WHERE owner_id = ?

artifacts.in.one.collection.only= \
    SELECT artifact_id FROM collection_items\
    WHERE collection_id = ? AND\
    artifact_id NOT IN (SELECT DISTINCT artifact_id FROM collection_items WHERE collection_id <> ?)

outdate.artifacts.collection=UPDATE artifacts \
    SET last_access = DATEADD('MILLISECOND', -2, CURRENT_TIMESTAMP), ttl = 1 \
    WHERE id IN \
    SELECT artifact_id FROM collection_items \
        WHERE collection_id = ? AND \
        artifact_id NOT IN (SELECT DISTINCT artifact_id FROM collection_items WHERE collection_id <> ?))

outdate.artifacts.user=UPDATE artifacts \
    SET last_access = DATEADD('MILLISECOND', -2, CURRENT_TIMESTAMP), ttl = 1 \
    WHERE id IN \
    (SELECT artifact_id FROM collection_items WHERE \
        collection_id IN (SELECT id FROM collections WHERE owner_id = ?) \
        AND artifact_id NOT IN \
            (SELECT artifact_id FROM collection_items WHERE collection_id IN \
                (SELECT id FROM collections WHERE owner_id <> ?)))