Mercurial > dive4elements > framework

comparison artifact-database/src/main/resources/sql/org-postgresql-driver.properties @ 243:33b9cc23ac9b

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Granularity of ttls should be milliseconds and not microseconds. artifacts/trunk@1678 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 13 Apr 2011 15:19:59 +0000
parents b35d32e507b6
children a2df2b48d2aa
comparison
equal deleted inserted replaced
242:b35d32e507b6 243:33b9cc23ac9b
8 data = ? WHERE id = ? 8 data = ? WHERE id = ?
9 9
10 artifacts.touch=UPDATE artifacts SET last_access = CURRENT_TIMESTAMP WHERE id = ? 10 artifacts.touch=UPDATE artifacts SET last_access = CURRENT_TIMESTAMP WHERE id = ?
11 11
12 artifacts.outdated=SELECT id, factory, data FROM artifacts WHERE ttl IS NOT NULL \ 12 artifacts.outdated=SELECT id, factory, data FROM artifacts WHERE ttl IS NOT NULL \
13 AND CURRENT_TIMESTAMP - last_access > (ttl || ' microseconds')::interval \ 13 AND CURRENT_TIMESTAMP - last_access > (ttl || ' milliseconds')::interval \
14 AND id NOT IN (SELECT DISTINCT artifact_id FROM collection_items) \ 14 AND id NOT IN (SELECT DISTINCT artifact_id FROM collection_items) \
15 AND id NOT IN ($LOCKED_IDS$) \ 15 AND id NOT IN ($LOCKED_IDS$) \
16 LIMIT 50 16 LIMIT 50
17 17
18 artifacts.select.gid=SELECT id, ttl, factory, data FROM artifacts WHERE gid = ?::uuid \ 18 artifacts.select.gid=SELECT id, ttl, factory, data FROM artifacts WHERE gid = ?::uuid \
19 AND (ttl IS NULL \ 19 AND (ttl IS NULL \
20 OR (CURRENT_TIMESTAMP - last_access <= (ttl || ' microseconds')::interval) \ 20 OR (CURRENT_TIMESTAMP - last_access <= (ttl || ' milliseconds')::interval) \
21 OR id IN (SELECT artifact_id FROM collection_items)) 21 OR id IN (SELECT artifact_id FROM collection_items))
22 22
23 artifacts.get.id=SELECT id FROM artifacts WHERE gid = ?::uuid 23 artifacts.get.id=SELECT id FROM artifacts WHERE gid = ?::uuid
24 24
25 artifacts.replace=UPDATE artifacts SET \ 25 artifacts.replace=UPDATE artifacts SET \
73 INNER JOIN artifacts a ON ci.artifact_id = a.id \ 73 INNER JOIN artifacts a ON ci.artifact_id = a.id \
74 WHERE c.gid = ?::uuid AND a.gid = ?::uuid 74 WHERE c.gid = ?::uuid AND a.gid = ?::uuid
75 75
76 collection.item.outdate.artifact= \ 76 collection.item.outdate.artifact= \
77 UPDATE artifacts \ 77 UPDATE artifacts \
78 SET last_access = CURRENT_TIMESTAMP - '2 microseconds'::interval, ttl = 1 \ 78 SET last_access = CURRENT_TIMESTAMP - '2 milliseconds'::interval, ttl = 1 \
79 WHERE id = ? AND \ 79 WHERE id = ? AND \
80 NOT EXSITS \ 80 NOT EXSITS \
81 (SELECT id FROM collection_items WHERE collection_id <> ? AND artifact_id = ?) 81 (SELECT id FROM collection_items WHERE collection_id <> ? AND artifact_id = ?)
82 82
83 collection.item.delete=DELETE FROM collection_items WHERE id = ? 83 collection.item.delete=DELETE FROM collection_items WHERE id = ?
92 collections.outdated= \ 92 collections.outdated= \
93 SELECT c.id FROM collections c \ 93 SELECT c.id FROM collections c \
94 INNER JOIN collection_items ci ON c.id = ci.collection_id \ 94 INNER JOIN collection_items ci ON c.id = ci.collection_id \
95 INNER JOIN artifacts a ON ci.artifact_id = a.id \ 95 INNER JOIN artifacts a ON ci.artifact_id = a.id \
96 WHERE c.ttl IS NOT NULL \ 96 WHERE c.ttl IS NOT NULL \
97 AND CURRENT_TIMESTAMP - c.last_access > (c.ttl || ' microseconds')::interval \ 97 AND CURRENT_TIMESTAMP - c.last_access > (c.ttl || ' milliseconds')::interval \
98 AND a.id NOT IN ($LOCKED_IDS$) 98 AND a.id NOT IN ($LOCKED_IDS$)
99 99
100 collections.touch.by.gid =\ 100 collections.touch.by.gid =\
101 UPDATE collections SET last_access = CURRENT_TIMESTAMP \ 101 UPDATE collections SET last_access = CURRENT_TIMESTAMP \
102 WHERE gid = ?::uuid 102 WHERE gid = ?::uuid
143 SELECT artifact_id FROM collection_items\ 143 SELECT artifact_id FROM collection_items\
144 WHERE collection_id = ? AND\ 144 WHERE collection_id = ? AND\
145 artifact_id NOT IN (SELECT DISTINCT artifact_id FROM collection_items WHERE collection_id <> ?) 145 artifact_id NOT IN (SELECT DISTINCT artifact_id FROM collection_items WHERE collection_id <> ?)
146 146
147 outdate.artifacts.collection=UPDATE artifacts \ 147 outdate.artifacts.collection=UPDATE artifacts \
148 SET last_access = CURRENT_TIMESTAMP - '2 microseconds'::interval, ttl = 1 \ 148 SET last_access = CURRENT_TIMESTAMP - '2 milliseconds'::interval, ttl = 1 \
149 WHERE id IN \ 149 WHERE id IN \
150 (SELECT artifact_id FROM collection_items \ 150 (SELECT artifact_id FROM collection_items \
151 WHERE collection_id = ? AND \ 151 WHERE collection_id = ? AND \
152 artifact_id NOT IN (SELECT DISTINCT artifact_id FROM collection_items WHERE collection_id <> ?)) 152 artifact_id NOT IN (SELECT DISTINCT artifact_id FROM collection_items WHERE collection_id <> ?))
153 153
154 outdate.artifacts.user=UPDATE artifacts \ 154 outdate.artifacts.user=UPDATE artifacts \
155 SET last_access = CURRENT_TIMESTAMP - '2 microseconds'::interval, ttl = 1 \ 155 SET last_access = CURRENT_TIMESTAMP - '2 milliseconds'::interval, ttl = 1 \
156 WHERE id IN \ 156 WHERE id IN \
157 (SELECT artifact_id FROM collection_items WHERE \ 157 (SELECT artifact_id FROM collection_items WHERE \
158 collection_id IN (SELECT id FROM collections WHERE owner_id = ?) \ 158 collection_id IN (SELECT id FROM collections WHERE owner_id = ?) \
159 AND artifact_id NOT IN \ 159 AND artifact_id NOT IN \
160 (SELECT artifact_id FROM collection_items WHERE collection_id IN \ 160 (SELECT artifact_id FROM collection_items WHERE collection_id IN \
http://dive4elements.wald.intevation.org