Mercurial > dive4elements > framework
view artifact-database/src/main/resources/sql/org-h2-driver.properties @ 497:6eb9ce20ba8b
Added tag 3.0.13 for changeset 090f08a64b59
author | Andre Heinecke <aheinecke@intevation.de> |
---|---|
date | Tue, 30 Jul 2013 19:15:48 +0200 |
parents | 4213dd6ca169 |
children | 9497f58484a0 |
line wrap: on
line source
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, gid FROM artifacts WHERE ttl IS NOT NULL \ AND DATEDIFF('MILLISECOND', last_access, CURRENT_TIMESTAMP) > ttl \ AND id NOT IN (SELECT DISTINCT artifact_id FROM collection_items) \ AND id NOT IN ($LOCKED_IDS$) \ LIMIT 50 artifacts.select.gid=SELECT id, ttl, factory, data FROM artifacts WHERE gid = ?::uuid \ AND (ttl IS NULL \ OR (DATEDIFF('MILLISECOND', last_access, CURRENT_TIMESTAMP) <= ttl) \ OR id IN (SELECT artifact_id FROM collection_items)) 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, account, role) VALUES (?, ?, ?, ?, ?) users.select.id.by.gid=SELECT id FROM users WHERE gid = ? users.select.gid=SELECT id, name, account, role FROM users WHERE gid = ? users.select.account=SELECT gid, name, account, role FROM users WHERE account = ? users.delete.id=DELETE FROM users WHERE id = ? users.delete.collections=DELETE FROM collections where owner_id = ? users.select.all=SELECT id, gid, name, account, role FROM users collection.check.artifact=SELECT id FROM collection_items \ WHERE artifact_id = ? AND collection_id = ? # COLLECTION ITEMS collection.items.id.nextval=SELECT NEXTVAL('COLLECTION_ITEMS_ID_SEQ') collection.items.insert=INSERT INTO collection_items \ (id, collection_id, artifact_id, attribute, creation) \ VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP) collection.item.get.attribute= \ SELECT ci.attribute FROM collection_items ci \ INNER JOIN collections c ON ci.collection_id = c.id \ INNER JOIN artifacts a ON ci.artifact_id = a.id \ WHERE c.gid = ? AND a.gid = ? collection.item.set.attribute= \ UPDATE collection_items SET attribute = ? WHERE id IN ( \ SELECT ci.id FROM collection_items ci \ INNER JOIN collections c ON ci.collection_id = c.id \ INNER JOIN artifacts a ON ci.artifact_id = a.id \ WHERE c.gid = ? AND a.gid = ?) collection.item.id.cid.aid= \ SELECT ci.id, ci.collection_id, ci.artifact_id FROM collection_items ci \ INNER JOIN collections c ON ci.collection_id = c.id \ INNER JOIN artifacts a ON ci.artifact_id = a.id \ WHERE c.gid = ? AND a.gid = ? collection.item.outdate.artifact= \ UPDATE artifacts \ SET last_access = DATEADD('MILLISECOND', -2, CURRENT_TIMESTAMP), ttl = 1 \ WHERE id = ? AND \ NOT EXISTS \ (SELECT id FROM collection_items WHERE collection_id <> ? AND artifact_id = ?) collection.item.delete=DELETE FROM collection_items WHERE id = ? collection.items.list.gid= \ SELECT a.gid, ci.attribute FROM collection_items ci \ INNER JOIN artifacts a ON ci.artifact_id = a.id \ WHERE ci.collection_id IN (SELECT id FROM collections WHERE gid = ?) \ ORDER BY ci.creation # COLLECTIONS collections.outdated= \ SELECT c.id, c.gid FROM collections c \ INNER JOIN collection_items ci ON c.id = ci.collection_id \ INNER JOIN artifacts a ON ci.artifact_id = a.id \ WHERE c.ttl IS NOT NULL \ AND DATEDIFF('MILLISECOND', c.last_access, CURRENT_TIMESTAMP) > c.ttl \ AND a.id NOT IN ($LOCKED_IDS$) collections.update.ttl=UPDATE collections SET ttl = ? WHERE gid = ? collections.update.name=UPDATE collections SET name = ? WHERE gid = ? collections.touch.trigger.function = \ UPDATE collections SET last_access = current_timestamp \ WHERE id IN \ (SELECT c.id FROM collections c \ INNER JOIN collection_items ci ON c.id = ci.collection_id \ INNER JOIN artifacts a ON a.id = ci.artifact_id \ WHERE a.id = ?) collections.touch.by.gid =\ UPDATE collections SET last_access = CURRENT_TIMESTAMP \ WHERE gid = ? collections.touch.by.id =\ UPDATE collections SET last_access = CURRENT_TIMESTAMP \ WHERE id = ? collections.id.nextval=SELECT NEXTVAL('COLLECTIONS_ID_SEQ') collections.id.by.gid=SELECT id FROM collections WHERE gid = ? delete.collection.items=DELETE FROM collection_items WHERE collection_id = ? delete.collection=DELETE FROM collections WHERE id = ? 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, attribute) \ VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?) collection.creation.time=SELECT creation from collections WHERE id = ? collections.artifacts.oldest=SELECT a.gid, ci.artifact_id \ FROM artifacts AS a, collection_items AS ci, collections AS c \ WHERE ci.collection_id = c.id AND c.gid = ?::uuid AND ci.artifact_id = a.id \ ORDER BY ci.creation collections.select.user= \ SELECT c.gid, c.name, c.creation, u.gid, c.ttl FROM \ collections c LEFT OUTER JOIN users u ON c.owner_id = u.id \ WHERE u.gid = ? collections.select.all= \ SELECT c.gid, c.name, c.creation, u.gid, c.ttl FROM \ collections c LEFT OUTER JOIN users u ON c.owner_id = u.id collections.select.by.gid= \ SELECT id, name, owner_id, creation, last_access, attribute, ttl \ FROM collections WHERE gid = ? 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 <> ?))) collection.get.attribute= \ SELECT c.attribute FROM collections c WHERE c.gid = ? collection.set.attribute= \ UPDATE collections SET attribute = ? WHERE gid = ? all.artifacts = \ SELECT u.gid AS u_gid, c.gid AS c_gid, c.name AS c_name, c.creation as c_creation, \ a.gid AS a_gid, a.factory AS factory, a.creation AS a_creation, a.data AS data \ FROM \ users u INNER JOIN collections c ON u.id = c.owner_id \ INNER JOIN collection_items ci ON c.id = ci.collection_id \ INNER JOIN artifacts a ON a.id = ci.artifact_id \ ORDER BY u_gid, c_gid