dive4elements/framework: artifact-database/src/main/resources/sql/org-h2-driver.properties annotate

annotate artifact-database/src/main/resources/sql/org-h2-driver.properties @ 184:a22b7e367b25

Added Backend code to list the collection items in a collection. artifacts/trunk@1410 c6561f87-3c4e-4783-a992-168aeb5c3f6f

author	Sascha L. Teichmann <sascha.teichmann@intevation.de>
date	Mon, 07 Mar 2011 11:37:49 +0000
parents	116482169b44
children	a85d889a1b0d

rev	line source
14 0d16d1bb2df0 Initial checkin of artigact persistents back by database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	1 artifacts.id.nextval=SELECT NEXTVAL('ARTIFACTS_ID_SEQ')
0d16d1bb2df0 Initial checkin of artigact persistents back by database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	2
0d16d1bb2df0 Initial checkin of artigact persistents back by database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	3 artifacts.insert=INSERT INTO artifacts \
41 5e4bc24ea438 Made serilization more flexible. DB update required!!! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 33 diff changeset	4 (id, gid, creation, last_access, ttl, factory, data) \
5e4bc24ea438 Made serilization more flexible. DB update required!!! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 33 diff changeset	5 VALUES (?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?, ?)
14 0d16d1bb2df0 Initial checkin of artigact persistents back by database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	6
0d16d1bb2df0 Initial checkin of artigact persistents back by database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	7 artifacts.update=UPDATE artifacts SET last_access = CURRENT_TIMESTAMP, \
0d16d1bb2df0 Initial checkin of artigact persistents back by database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	8 data = ? WHERE id = ?
0d16d1bb2df0 Initial checkin of artigact persistents back by database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	9
41 5e4bc24ea438 Made serilization more flexible. DB update required!!! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 33 diff changeset	10 artifacts.touch=UPDATE artifacts SET last_access = CURRENT_TIMESTAMP WHERE id = ?
14 0d16d1bb2df0 Initial checkin of artigact persistents back by database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	11
41 5e4bc24ea438 Made serilization more flexible. DB update required!!! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 33 diff changeset	12 artifacts.outdated=SELECT id, factory, data FROM artifacts WHERE ttl IS NOT NULL \
43 b8516aa3d8a1 Time delta calculation in SQL statement to figure out outdated artifacts Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	13 AND DATEDIFF('MILLISECOND', last_access, CURRENT_TIMESTAMP) > ttl LIMIT 50
14 0d16d1bb2df0 Initial checkin of artigact persistents back by database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	14
41 5e4bc24ea438 Made serilization more flexible. DB update required!!! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 33 diff changeset	15 artifacts.select.gid=SELECT id, last_access, ttl, factory, data FROM artifacts WHERE gid = ?
14 0d16d1bb2df0 Initial checkin of artigact persistents back by database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	16
80 8447467cef86 Implementation to import artifacts from incoming xml documents (applied patch from issue208 by SLT). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 43 diff changeset	17 artifacts.get.id=SELECT id FROM artifacts WHERE gid = ?
8447467cef86 Implementation to import artifacts from incoming xml documents (applied patch from issue208 by SLT). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 43 diff changeset	18
8447467cef86 Implementation to import artifacts from incoming xml documents (applied patch from issue208 by SLT). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 43 diff changeset	19 artifacts.replace=UPDATE artifacts SET \
8447467cef86 Implementation to import artifacts from incoming xml documents (applied patch from issue208 by SLT). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 43 diff changeset	20 creation = CURRENT_TIMESTAMP, last_access = CURRENT_TIMESTAMP, \
8447467cef86 Implementation to import artifacts from incoming xml documents (applied patch from issue208 by SLT). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 43 diff changeset	21 ttl = ?, factory = ?, data = ? \
8447467cef86 Implementation to import artifacts from incoming xml documents (applied patch from issue208 by SLT). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 43 diff changeset	22 WHERE id = ?
8447467cef86 Implementation to import artifacts from incoming xml documents (applied patch from issue208 by SLT). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 43 diff changeset	23
14 0d16d1bb2df0 Initial checkin of artigact persistents back by database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	24 artifacts.delete=DELETE FROM artifacts WHERE id = ?
124 caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	25
caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	26 # USERS
caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	27
caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	28 users.id.nextval=SELECT NEXTVAL('USERS_ID_SEQ')
caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	29
173 940ed629419f Solve the SQL problem with the collection listings. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	30 users.insert=INSERT INTO users (id, gid, name, role) VALUES (?, ?, ?, ?)
124 caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	31
144 5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	32 users.select.id.by.gid=SELECT id FROM users WHERE gid = ?
124 caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	33 users.select.gid=SELECT id, name, role FROM users WHERE gid = ?
caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	34
133 2950c6011afa Fixed typo in keys. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 124 diff changeset	35 users.delete.id=DELETE FROM users WHERE id = ?
124 caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	36
caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	37 users.delete.collections=DELETE FROM collections where owner_id = ?
caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	38
caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	39 users.select.all=SELECT id, gid, name, role FROM users
144 5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	40
176 a0eff2227588 Added code to add artifacts to collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 175 diff changeset	41 collection.check.artifact=SELECT id FROM collections_items \
a0eff2227588 Added code to add artifacts to collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 175 diff changeset	42 WHERE artifact_id = ? AND collection_id = ?
a0eff2227588 Added code to add artifacts to collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 175 diff changeset	43
a0eff2227588 Added code to add artifacts to collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 175 diff changeset	44 # COLLECTION ITEMS
a0eff2227588 Added code to add artifacts to collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 175 diff changeset	45 collection.items.id.nextval=SELECT NEXTVAL('COLLECTION_ITEMS_ID_SEQ')
a0eff2227588 Added code to add artifacts to collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 175 diff changeset	46
a0eff2227588 Added code to add artifacts to collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 175 diff changeset	47 collection.items.insert=INSERT INTO collections_items \
a0eff2227588 Added code to add artifacts to collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 175 diff changeset	48 (id, collection_id, artifact_id, attribute) \
a0eff2227588 Added code to add artifacts to collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 175 diff changeset	49 VALUES (?, ?, ?, ?)
a0eff2227588 Added code to add artifacts to collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 175 diff changeset	50
178 535e4ea2ef9b Added code to get the attribute of a collection item Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 176 diff changeset	51 collection.item.get.attribute= \
535e4ea2ef9b Added code to get the attribute of a collection item Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 176 diff changeset	52 SELECT ci.attribute FROM collections_items ci \
535e4ea2ef9b Added code to get the attribute of a collection item Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 176 diff changeset	53 INNER JOIN collection c ON ci.collection_id = c.id \
535e4ea2ef9b Added code to get the attribute of a collection item Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 176 diff changeset	54 INNER JOIN attributes a ON ci.artifact_id = a.id \
535e4ea2ef9b Added code to get the attribute of a collection item Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 176 diff changeset	55 WHERE c.gid = ? AND a.gid = ?
535e4ea2ef9b Added code to get the attribute of a collection item Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 176 diff changeset	56
179 644fd11ddd9f Added code to set attribute of a collection item. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 178 diff changeset	57 collection.item.set.attribute= \
644fd11ddd9f Added code to set attribute of a collection item. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 178 diff changeset	58 UPDATE collections_items SET attribute = ? WHERE id IN ( \
644fd11ddd9f Added code to set attribute of a collection item. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 178 diff changeset	59 SELECT ci.id FROM collections_items ci \
644fd11ddd9f Added code to set attribute of a collection item. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 178 diff changeset	60 INNER JOIN collection c ON ci.collection_id = c.id \
644fd11ddd9f Added code to set attribute of a collection item. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 178 diff changeset	61 INNER JOIN attributes a ON ci.artifact_id = a.id \
644fd11ddd9f Added code to set attribute of a collection item. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 178 diff changeset	62 WHERE c.gid = ? AND a.gid = ?)
644fd11ddd9f Added code to set attribute of a collection item. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 178 diff changeset	63
180 38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	64 collection.item.id.cid.aid= \
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	65 SELECT ci.id, c.collection_id, c.artifact_id FROM collections_items ci \
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	66 INNER JOIN collection c ON ci.collection_id = c.id \
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	67 INNER JOIN attributes a ON ci.artifact_id = a.id \
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	68 WHERE c.gid = ? AND a.gid = ?
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	69
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	70 collection.item.outdate.artifact= \
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	71 UPDATE artifacts \
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	72 SET last_access = DATEADD('MILLISECOND', -2, CURRENT_TIMESTAMP), ttl = 1 \
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	73 WHERE id = ? AND \
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	74 NOT EXSITS (SELECT id FROM collections_items WHERE <> collection_id = ?)
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	75
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	76 collection.item.delete=DELETE FROM collections_items WHERE id = ?
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	77
184 a22b7e367b25 Added Backend code to list the collection items in a collection. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 181 diff changeset	78 collection.items.list.gid= \
a22b7e367b25 Added Backend code to list the collection items in a collection. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 181 diff changeset	79 SELECT a.gid, ci.attribute FROM collections_items ci \
a22b7e367b25 Added Backend code to list the collection items in a collection. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 181 diff changeset	80 INNER JOIN artifacts a ON ci.artifact_id = a.id \
a22b7e367b25 Added Backend code to list the collection items in a collection. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 181 diff changeset	81 WHERE ci.id IN (SELECT id FROM collections WHERE gid = ?)
a22b7e367b25 Added Backend code to list the collection items in a collection. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 181 diff changeset	82
144 5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	83 # COLLECTIONS
179 644fd11ddd9f Added code to set attribute of a collection item. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 178 diff changeset	84 collections.touch.by.gid =\
644fd11ddd9f Added code to set attribute of a collection item. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 178 diff changeset	85 UPDATE collection SET last_access = CURRENT_TIMESTAMP \
644fd11ddd9f Added code to set attribute of a collection item. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 178 diff changeset	86 WHERE gid = ?
644fd11ddd9f Added code to set attribute of a collection item. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 178 diff changeset	87
180 38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	88 collections.touch.by.id =\
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	89 UPDATE collection SET last_access = CURRENT_TIMESTAMP \
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	90 WHERE id = ?
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	91
159 db0d20440b92 Added code to create collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 144 diff changeset	92 collections.id.nextval=SELECT NEXTVAL('COLLECTIONS_ID_SEQ')
144 5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	93
175 16e6e661e6bf Added code to delete collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 173 diff changeset	94 collections.id.by.gid=SELECT id FROM collections WHERE gid = ?
16e6e661e6bf Added code to delete collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 173 diff changeset	95
16e6e661e6bf Added code to delete collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 173 diff changeset	96 delete.collection.items=DELETE FROM collection_items WHERE collection_id = ?
16e6e661e6bf Added code to delete collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 173 diff changeset	97
16e6e661e6bf Added code to delete collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 173 diff changeset	98 delete.collection=DELETE FROM collections WHERE id = ?
16e6e661e6bf Added code to delete collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 173 diff changeset	99
144 5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	100 delete.user.collection.items= \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	101 DELETE FROM collection_items WHERE collection_id IN \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	102 (SELECT id FROM collections WHERE owner_id = ?)
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	103
159 db0d20440b92 Added code to create collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 144 diff changeset	104 collections.insert= \
db0d20440b92 Added code to create collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 144 diff changeset	105 INSERT INTO collections (id, gid, name, owner_id, creation, last_access, ttl) \
db0d20440b92 Added code to create collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 144 diff changeset	106 VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?)
db0d20440b92 Added code to create collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 144 diff changeset	107
170 ac0f8bd97277 Fix parameter propagation of creation time ond collection names. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	108 collection.creation.time=SELECT creation from collections WHERE id = ?
ac0f8bd97277 Fix parameter propagation of creation time ond collection names. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	109
167 c9c27aca2f70 Added code to list collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 159 diff changeset	110 collections.select.user= \
c9c27aca2f70 Added code to list collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 159 diff changeset	111 SELECT c.gid, c.name, c.creation, u.gid FROM \
181 116482169b44 Adapted the sql statements for fetching all collections and all user collections from database. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 180 diff changeset	112 collections c LEFT OUTER JOIN users u ON c.owner_id = u.id \
167 c9c27aca2f70 Added code to list collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 159 diff changeset	113 WHERE u.gid = ?
c9c27aca2f70 Added code to list collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 159 diff changeset	114
c9c27aca2f70 Added code to list collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 159 diff changeset	115 collections.select.all= \
c9c27aca2f70 Added code to list collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 159 diff changeset	116 SELECT c.gid, c.name, c.creation, u.gid FROM \
181 116482169b44 Adapted the sql statements for fetching all collections and all user collections from database. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 180 diff changeset	117 collections c LEFT OUTER JOIN users u ON c.owner_id = u.id
167 c9c27aca2f70 Added code to list collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 159 diff changeset	118
144 5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	119 users.collections=SELECT collection_id, gid, name FROM collections WHERE owner_id = ?
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	120 users.collection.ids=SELECT collection_id FROM collections WHERE owner_id = ?
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	121
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	122 users.delete.all.collections=DELETE FROM collections WHERE owner_id = ?
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	123
159 db0d20440b92 Added code to create collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 144 diff changeset	124 artifacts.in.one.collection.only= \
144 5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	125 SELECT artifact_id FROM collection_items\
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	126 WHERE collection_id = ? AND\
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	127 artifact_id NOT IN (SELECT DISTINCT artifact_id FROM collection_items WHERE collection_id <> ?)
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	128
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	129 outdate.artifacts.collection=UPDATE artifacts \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	130 SET last_access = DATEADD('MILLISECOND', -2, CURRENT_TIMESTAMP), ttl = 1 \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	131 WHERE id IN \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	132 SELECT artifact_id FROM collection_items \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	133 WHERE collection_id = ? AND \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	134 artifact_id NOT IN (SELECT DISTINCT artifact_id FROM collection_items WHERE collection_id <> ?))
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	135
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	136 outdate.artifacts.user=UPDATE artifacts \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	137 SET last_access = DATEADD('MILLISECOND', -2, CURRENT_TIMESTAMP), ttl = 1 \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	138 WHERE id IN \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	139 (SELECT artifact_id FROM collection_items WHERE \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	140 collection_id IN (SELECT id FROM collections WHERE owner_id = ?) \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	141 AND artifact_id NOT IN \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	142 (SELECT artifact_id FROM collection_items WHERE collection_id IN \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	143 (SELECT id FROM collections WHERE owner_id <> ?)))

Mercurial > dive4elements > framework

annotate artifact-database/src/main/resources/sql/org-h2-driver.properties @ 184:a22b7e367b25