Mercurial > dive4elements > framework
comparison artifact-database/src/main/resources/sql/org-h2-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 |
comparison
equal
deleted
inserted
replaced
| 231:1ea35226a6de | 232:202b6ae2fcad |
|---|---|
| 84 INNER JOIN artifacts a ON ci.artifact_id = a.id \ | 84 INNER JOIN artifacts a ON ci.artifact_id = a.id \ |
| 85 WHERE ci.collection_id IN (SELECT id FROM collections WHERE gid = ?) | 85 WHERE ci.collection_id IN (SELECT id FROM collections WHERE gid = ?) |
| 86 | 86 |
| 87 # COLLECTIONS | 87 # COLLECTIONS |
| 88 | 88 |
| 89 collections.outdated= \ | |
| 90 SELECT c.id FROM collections c \ | |
| 91 INNER JOIN collection_items ci ON c.id = ci.collection_id \ | |
| 92 INNER JOIN artifacts a ON ci.artifact_id = a.id \ | |
| 93 WHERE c.ttl IS NOT NULL \ | |
| 94 AND DATEDIFF('MILLISECOND', c.last_access, CURRENT_TIMESTAMP) > c.ttl \ | |
| 95 AND a.id NOT IN ($LOCKED_IDS$) | |
| 96 | |
| 89 collections.touch.trigger.function = \ | 97 collections.touch.trigger.function = \ |
| 90 UPDATE collections SET last_access = current_timestamp \ | 98 UPDATE collections SET last_access = current_timestamp \ |
| 91 WHERE id IN \ | 99 WHERE id IN \ |
| 92 (SELECT c.id FROM collections c \ | 100 (SELECT c.id FROM collections c \ |
| 93 INNER JOIN collection_items ci ON c.id = ci.collection_id \ | 101 INNER JOIN collection_items ci ON c.id = ci.collection_id \ |
| 94 INNER JOIN artifacts a ON a.id = ci.artifact_id \ | 102 INNER JOIN artifacts a ON a.id = ci.artifact_id \ |
| 95 WHERE a.id = ?) | 103 WHERE a.id = ?) |
| 96 | |
| 97 | 104 |
| 98 collections.touch.by.gid =\ | 105 collections.touch.by.gid =\ |
| 99 UPDATE collections SET last_access = CURRENT_TIMESTAMP \ | 106 UPDATE collections SET last_access = CURRENT_TIMESTAMP \ |
| 100 WHERE gid = ? | 107 WHERE gid = ? |
| 101 | 108 |