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

annotate artifact-database/src/main/resources/sql/org-postgresql-driver.properties @ 232:202b6ae2fcad

Database cleaner: Removes outdated collections, too. artifacts/trunk@1636 c6561f87-3c4e-4783-a992-168aeb5c3f6f

author	Sascha L. Teichmann <sascha.teichmann@intevation.de>
date	Thu, 31 Mar 2011 17:14:22 +0000
parents	1ea35226a6de
children	b35d32e507b6

rev	line source
38 93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	1 artifacts.id.nextval=SELECT nextval('ARTIFACTS_ID_SEQ')
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	2
93edc04f3a10 Added postgresql support. 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: 38 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: 38 diff changeset	5 VALUES (?, ?::uuid, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?, ?)
38 93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	6
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	7 artifacts.update=UPDATE artifacts SET last_access = CURRENT_TIMESTAMP, \
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	8 data = ? WHERE id = ?
93edc04f3a10 Added postgresql support. 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: 38 diff changeset	10 artifacts.touch=UPDATE artifacts SET last_access = CURRENT_TIMESTAMP WHERE id = ?
38 93edc04f3a10 Added postgresql support. 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: 38 diff changeset	12 artifacts.outdated=SELECT id, factory, data FROM artifacts WHERE ttl IS NOT NULL \
195 63f555bbdbc0 Fix a foreign key constraint violation when deleting outdated artifacts. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 193 diff changeset	13 AND CURRENT_TIMESTAMP - last_access > (ttl \|\| ' microseconds')::interval \
197 94654cbf8db2 Fixed a typo in the sql statements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 195 diff changeset	14 AND id NOT IN (SELECT DISTINCT artifact_id FROM collection_items) \
231 1ea35226a6de Database cleaner: Ignoring locked artifacts is now done on database level. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 216 diff changeset	15 AND id NOT IN ($LOCKED_IDS$) \
195 63f555bbdbc0 Fix a foreign key constraint violation when deleting outdated artifacts. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 193 diff changeset	16 LIMIT 50
38 93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	17
41 5e4bc24ea438 Made serilization more flexible. DB update required!!! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 38 diff changeset	18 artifacts.select.gid=SELECT id, last_access, ttl, factory, data FROM artifacts WHERE gid = ?::uuid
38 93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	19
80 8447467cef86 Implementation to import artifacts from incoming xml documents (applied patch from issue208 by SLT). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 41 diff changeset	20 artifacts.get.id=SELECT id FROM artifacts WHERE gid = ?::uuid
8447467cef86 Implementation to import artifacts from incoming xml documents (applied patch from issue208 by SLT). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 41 diff changeset	21
8447467cef86 Implementation to import artifacts from incoming xml documents (applied patch from issue208 by SLT). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 41 diff changeset	22 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: 41 diff changeset	23 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: 41 diff changeset	24 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: 41 diff changeset	25 WHERE id = ?
8447467cef86 Implementation to import artifacts from incoming xml documents (applied patch from issue208 by SLT). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 41 diff changeset	26
38 93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	27 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	28
caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	29 # USERS
caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	30
caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	31 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	32
215 a9c8fa38ba03 Added new collection specific sql statements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 213 diff changeset	33 users.insert=INSERT INTO users (id, gid, name, role) VALUES (?, ?::uuid, ?, ?)
124 caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	34
144 5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	35 users.select.id.by.gid=SELECT id FROM users WHERE gid = ?::uuid
124 caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	36 users.select.gid=SELECT id, name, role FROM users WHERE gid = ?::uuid
caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	37
133 2950c6011afa Fixed typo in keys. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 124 diff changeset	38 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	39
caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	40 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	41
caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 80 diff changeset	42 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	43
187 a85d889a1b0d Fixed some backend specific stuff (sql statements, schema). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 184 diff changeset	44 collection.check.artifact=SELECT id FROM collection_items \
176 a0eff2227588 Added code to add artifacts to collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 175 diff changeset	45 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	46
a0eff2227588 Added code to add artifacts to collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 175 diff changeset	47 # COLLECTION ITEMS
a0eff2227588 Added code to add artifacts to collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 175 diff changeset	48 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	49
187 a85d889a1b0d Fixed some backend specific stuff (sql statements, schema). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 184 diff changeset	50 collection.items.insert=INSERT INTO collection_items \
176 a0eff2227588 Added code to add artifacts to collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 175 diff changeset	51 (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	52 VALUES (?, ?, ?, ?)
a0eff2227588 Added code to add artifacts to collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 175 diff changeset	53
178 535e4ea2ef9b Added code to get the attribute of a collection item Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 176 diff changeset	54 collection.item.get.attribute= \
187 a85d889a1b0d Fixed some backend specific stuff (sql statements, schema). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 184 diff changeset	55 SELECT ci.attribute FROM collection_items ci \
191 3bce5556a2e5 Fixed some sql statements that contained typos. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 187 diff changeset	56 INNER JOIN collections c ON ci.collection_id = c.id \
3bce5556a2e5 Fixed some sql statements that contained typos. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 187 diff changeset	57 INNER JOIN artifacts a ON ci.artifact_id = a.id \
178 535e4ea2ef9b Added code to get the attribute of a collection item Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 176 diff changeset	58 WHERE c.gid = ?::uuid AND a.gid = ?::uuid
535e4ea2ef9b Added code to get the attribute of a collection item Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 176 diff changeset	59
179 644fd11ddd9f Added code to set attribute of a collection item. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 178 diff changeset	60 collection.item.set.attribute= \
187 a85d889a1b0d Fixed some backend specific stuff (sql statements, schema). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 184 diff changeset	61 UPDATE collection_items SET attribute = ? WHERE id IN ( \
a85d889a1b0d Fixed some backend specific stuff (sql statements, schema). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 184 diff changeset	62 SELECT ci.id FROM collection_items ci \
191 3bce5556a2e5 Fixed some sql statements that contained typos. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 187 diff changeset	63 INNER JOIN collections c ON ci.collection_id = c.id \
3bce5556a2e5 Fixed some sql statements that contained typos. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 187 diff changeset	64 INNER JOIN artifacts a ON ci.artifact_id = a.id \
179 644fd11ddd9f Added code to set attribute of a collection item. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 178 diff changeset	65 WHERE c.gid = ?::uuid AND a.gid = ?::uuid)
644fd11ddd9f Added code to set attribute of a collection item. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 178 diff changeset	66
180 38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	67 collection.item.id.cid.aid= \
193 949d69ad3756 Fixed issue with outdating an artifact if it is removed from a collection. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 191 diff changeset	68 SELECT ci.id, ci.collection_id, ci.artifact_id FROM collection_items ci \
949d69ad3756 Fixed issue with outdating an artifact if it is removed from a collection. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 191 diff changeset	69 INNER JOIN collections c ON ci.collection_id = c.id \
949d69ad3756 Fixed issue with outdating an artifact if it is removed from a collection. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 191 diff changeset	70 INNER JOIN artifacts a ON ci.artifact_id = a.id \
180 38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	71 WHERE c.gid = ?::uuid AND a.gid = ?::uuid
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	72
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	73 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	74 UPDATE artifacts \
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	75 SET last_access = CURRENT_TIMESTAMP - '2 microseconds'::interval, ttl = 1 \
38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	76 WHERE id = ? AND \
193 949d69ad3756 Fixed issue with outdating an artifact if it is removed from a collection. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 191 diff changeset	77 NOT EXSITS \
949d69ad3756 Fixed issue with outdating an artifact if it is removed from a collection. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 191 diff changeset	78 (SELECT id FROM collection_items WHERE collection_id <> ? AND artifact_id = ?)
180 38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	79
187 a85d889a1b0d Fixed some backend specific stuff (sql statements, schema). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 184 diff changeset	80 collection.item.delete=DELETE FROM collection_items WHERE id = ?
180 38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	81
184 a22b7e367b25 Added Backend code to list the collection items in a collection. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 180 diff changeset	82 collection.items.list.gid= \
187 a85d889a1b0d Fixed some backend specific stuff (sql statements, schema). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 184 diff changeset	83 SELECT a.gid, ci.attribute FROM collection_items ci \
184 a22b7e367b25 Added Backend code to list the collection items in a collection. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 180 diff changeset	84 INNER JOIN artifacts a ON ci.artifact_id = a.id \
213 199f0e553e3a Bugfix: Repaired broken SQL statement. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 199 diff changeset	85 WHERE ci.collection_id IN (SELECT id FROM collections WHERE gid = ?::uuid)
184 a22b7e367b25 Added Backend code to list the collection items in a collection. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 180 diff changeset	86
144 5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	87 # COLLECTIONS
232 202b6ae2fcad Database cleaner: Removes outdated collections, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 231 diff changeset	88
202b6ae2fcad Database cleaner: Removes outdated collections, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 231 diff changeset	89 collections.outdated= \
202b6ae2fcad Database cleaner: Removes outdated collections, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 231 diff changeset	90 SELECT c.id FROM collections c \
202b6ae2fcad Database cleaner: Removes outdated collections, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 231 diff changeset	91 INNER JOIN collection_items ci ON c.id = ci.collection_id \
202b6ae2fcad Database cleaner: Removes outdated collections, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 231 diff changeset	92 INNER JOIN artifacts a ON ci.artifact_id = a.id \
202b6ae2fcad Database cleaner: Removes outdated collections, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 231 diff changeset	93 WHERE c.ttl IS NOT NULL \
202b6ae2fcad Database cleaner: Removes outdated collections, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 231 diff changeset	94 AND CURRENT_TIMESTAMP - c.last_access > (c.ttl \|\| ' microseconds')::interval \
202b6ae2fcad Database cleaner: Removes outdated collections, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 231 diff changeset	95 AND a.id NOT IN ($LOCKED_IDS$)
202b6ae2fcad Database cleaner: Removes outdated collections, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 231 diff changeset	96
179 644fd11ddd9f Added code to set attribute of a collection item. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 178 diff changeset	97 collections.touch.by.gid =\
191 3bce5556a2e5 Fixed some sql statements that contained typos. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 187 diff changeset	98 UPDATE collections SET last_access = CURRENT_TIMESTAMP \
179 644fd11ddd9f Added code to set attribute of a collection item. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 178 diff changeset	99 WHERE gid = ?::uuid
644fd11ddd9f Added code to set attribute of a collection item. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 178 diff changeset	100
180 38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	101 collections.touch.by.id =\
191 3bce5556a2e5 Fixed some sql statements that contained typos. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 187 diff changeset	102 UPDATE collections SET last_access = CURRENT_TIMESTAMP \
180 38fbbeffe8fe Added code to remove an artifact from a collection Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 179 diff changeset	103 WHERE id = ?
159 db0d20440b92 Added code to create collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 144 diff changeset	104 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	105
175 16e6e661e6bf Added code to delete collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 173 diff changeset	106 collections.id.by.gid=SELECT id FROM collections WHERE gid = ?::uuid
16e6e661e6bf Added code to delete collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 173 diff changeset	107
16e6e661e6bf Added code to delete collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 173 diff changeset	108 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	109
16e6e661e6bf Added code to delete collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 173 diff changeset	110 delete.collection=DELETE FROM collections WHERE id = ?
16e6e661e6bf Added code to delete collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 173 diff changeset	111
144 5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	112 delete.user.collection.items= \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	113 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	114 (SELECT id FROM collections WHERE owner_id = ?)
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	115
159 db0d20440b92 Added code to create collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 144 diff changeset	116 collections.insert= \
199 face2302387c Insert collection attribute into database, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 197 diff changeset	117 INSERT INTO collections \
face2302387c Insert collection attribute into database, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 197 diff changeset	118 (id, gid, name, owner_id, creation, last_access, ttl, attribute) \
face2302387c Insert collection attribute into database, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 197 diff changeset	119 VALUES (?, ?::uuid, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?)
159 db0d20440b92 Added code to create collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 144 diff changeset	120
170 ac0f8bd97277 Fix parameter propagation of creation time ond collection names. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 167 diff changeset	121 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	122
167 c9c27aca2f70 Added code to list collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 159 diff changeset	123 collections.select.user= \
c9c27aca2f70 Added code to list collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 159 diff changeset	124 SELECT c.gid, c.name, c.creation, u.gid FROM \
173 940ed629419f Solve the SQL problem with the collection listings. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	125 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	126 WHERE u.gid = ?::uuid
c9c27aca2f70 Added code to list collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 159 diff changeset	127
c9c27aca2f70 Added code to list collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 159 diff changeset	128 collections.select.all= \
c9c27aca2f70 Added code to list collections. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 159 diff changeset	129 SELECT c.gid, c.name, c.creation, u.gid FROM \
173 940ed629419f Solve the SQL problem with the collection listings. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 170 diff changeset	130 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	131
215 a9c8fa38ba03 Added new collection specific sql statements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 213 diff changeset	132 collections.select.by.gid= \
a9c8fa38ba03 Added new collection specific sql statements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 213 diff changeset	133 SELECT id, name, owner_id, creation, last_access, attribute \
216 2d90d5cfe8f9 Added a cast to uuid in collections.select.by.gid Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 215 diff changeset	134 FROM collections WHERE gid = ?::uuid
215 a9c8fa38ba03 Added new collection specific sql statements. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 213 diff changeset	135
144 5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	136 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	137 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	138
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	139 artifacts.in.one.collection.only=\
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	140 SELECT artifact_id FROM collection_items\
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	141 WHERE collection_id = ? AND\
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	142 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	143
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	144 outdate.artifacts.collection=UPDATE artifacts \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	145 SET last_access = CURRENT_TIMESTAMP - '2 microseconds'::interval, ttl = 1 \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	146 WHERE id IN \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	147 (SELECT artifact_id FROM collection_items \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	148 WHERE collection_id = ? AND \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	149 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	150
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	151 outdate.artifacts.user=UPDATE artifacts \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	152 SET last_access = CURRENT_TIMESTAMP - '2 microseconds'::interval, ttl = 1 \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	153 WHERE id IN \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	154 (SELECT artifact_id FROM collection_items WHERE \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	155 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	156 AND artifact_id NOT IN \
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	157 (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	158 (SELECT id FROM collections WHERE owner_id <> ?)))
5369582d4fbf Enable backend to delete users Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 133 diff changeset	159

Mercurial > dive4elements > framework

annotate artifact-database/src/main/resources/sql/org-postgresql-driver.properties @ 232:202b6ae2fcad