Mercurial > dive4elements > framework

annotate artifact-database/src/main/resources/sql/org-h2-driver.properties @ 541:3b1e48d22ce0

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Experimentally let database cleaner and backend share the same sql executor.
author Sascha L. Teichmann <teichmann@intevation.de>
date Thu, 03 Sep 2015 15:34:07 +0200
parents 4213dd6ca169
children 9497f58484a0
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
314
31ee2b3b5a57 forward list of deleted collections and artifacts from data cleaner to backend to backend listeners.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 308
diff changeset
12 artifacts.outdated=SELECT id, factory, data, gid 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 DATEDIFF('MILLISECOND', last_access, CURRENT_TIMESTAMP) > ttl \
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: 229
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
14
0d16d1bb2df0 Initial checkin of artigact persistents back by database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
17
243
33b9cc23ac9b Granularity of ttls should be milliseconds and not microseconds.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 242
diff changeset
18 artifacts.select.gid=SELECT id, ttl, factory, data FROM artifacts WHERE gid = ?::uuid \
242
b35d32e507b6 Fix for flys/issue9
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 232
diff changeset
19 AND (ttl IS NULL \
b35d32e507b6 Fix for flys/issue9
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 232
diff changeset
20 OR (DATEDIFF('MILLISECOND', last_access, CURRENT_TIMESTAMP) <= ttl) \
243
33b9cc23ac9b Granularity of ttls should be milliseconds and not microseconds.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 242
diff changeset
21 OR id IN (SELECT artifact_id FROM collection_items))
14
0d16d1bb2df0 Initial checkin of artigact persistents back by database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
22
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
23 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
24
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
25 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
26 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
27 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
28 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
29
14
0d16d1bb2df0 Initial checkin of artigact persistents back by database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
30 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
31
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
32 # USERS
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
33
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
34 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
35
407
08731e7403b5 Update sql user queries to add the account column
Bjoern Ricks <bjoern.ricks@intevation.de>
parents: 346
diff changeset
36 users.insert=INSERT INTO users (id, gid, name, account, role) VALUES (?, ?, ?, ?, ?)
124
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
37
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
38 users.select.id.by.gid=SELECT id FROM users WHERE gid = ?
407
08731e7403b5 Update sql user queries to add the account column
Bjoern Ricks <bjoern.ricks@intevation.de>
parents: 346
diff changeset
39 users.select.gid=SELECT id, name, account, role FROM users WHERE gid = ?
421
4213dd6ca169 Fix queries for finding a user
Bjoern Ricks <bjoern.ricks@intevation.de>
parents: 413
diff changeset
40 users.select.account=SELECT gid, name, account, role FROM users WHERE account = ?
124
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
41
133
2950c6011afa Fixed typo in keys.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 124
diff changeset
42 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
43
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
44 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
45
407
08731e7403b5 Update sql user queries to add the account column
Bjoern Ricks <bjoern.ricks@intevation.de>
parents: 346
diff changeset
46 users.select.all=SELECT id, gid, name, account, role FROM users
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
47
187
a85d889a1b0d Fixed some backend specific stuff (sql statements, schema).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 184
diff changeset
48 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
49 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
50
a0eff2227588 Added code to add artifacts to collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 175
diff changeset
51 # COLLECTION ITEMS
a0eff2227588 Added code to add artifacts to collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 175
diff changeset
52 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
53
187
a85d889a1b0d Fixed some backend specific stuff (sql statements, schema).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 184
diff changeset
54 collection.items.insert=INSERT INTO collection_items \
274
92166f7c3842 #50 Added code on database level to recognize the oldest collection item in a collection (-> master artifact).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 273
diff changeset
55 (id, collection_id, artifact_id, attribute, creation) \
92166f7c3842 #50 Added code on database level to recognize the oldest collection item in a collection (-> master artifact).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 273
diff changeset
56 VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP)
176
a0eff2227588 Added code to add artifacts to collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 175
diff changeset
57
178
535e4ea2ef9b Added code to get the attribute of a collection item
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 176
diff changeset
58 collection.item.get.attribute= \
187
a85d889a1b0d Fixed some backend specific stuff (sql statements, schema).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 184
diff changeset
59 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
60 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
61 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
62 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
63
179
644fd11ddd9f Added code to set attribute of a collection item.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 178
diff changeset
64 collection.item.set.attribute= \
187
a85d889a1b0d Fixed some backend specific stuff (sql statements, schema).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 184
diff changeset
65 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
66 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
67 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
68 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
69 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
70
180
38fbbeffe8fe Added code to remove an artifact from a collection
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 179
diff changeset
71 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
72 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
73 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
74 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
75 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
76
38fbbeffe8fe Added code to remove an artifact from a collection
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 179
diff changeset
77 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
78 UPDATE artifacts \
38fbbeffe8fe Added code to remove an artifact from a collection
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 179
diff changeset
79 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
80 WHERE id = ? AND \
340
560d2e6eaf9c Fix SQL typo.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 320
diff changeset
81 NOT EXISTS \
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
82 (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
83
187
a85d889a1b0d Fixed some backend specific stuff (sql statements, schema).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 184
diff changeset
84 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
85
184
a22b7e367b25 Added Backend code to list the collection items in a collection.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 181
diff changeset
86 collection.items.list.gid= \
187
a85d889a1b0d Fixed some backend specific stuff (sql statements, schema).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 184
diff changeset
87 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: 181
diff changeset
88 INNER JOIN artifacts a ON ci.artifact_id = a.id \
274
92166f7c3842 #50 Added code on database level to recognize the oldest collection item in a collection (-> master artifact).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 273
diff changeset
89 WHERE ci.collection_id IN (SELECT id FROM collections WHERE gid = ?) \
92166f7c3842 #50 Added code on database level to recognize the oldest collection item in a collection (-> master artifact).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 273
diff changeset
90 ORDER BY ci.creation
184
a22b7e367b25 Added Backend code to list the collection items in a collection.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 181
diff changeset
91
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
92 # COLLECTIONS
229
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 215
diff changeset
93
232
202b6ae2fcad Database cleaner: Removes outdated collections, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 231
diff changeset
94 collections.outdated= \
314
31ee2b3b5a57 forward list of deleted collections and artifacts from data cleaner to backend to backend listeners.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 308
diff changeset
95 SELECT c.id, c.gid FROM collections c \
232
202b6ae2fcad Database cleaner: Removes outdated collections, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 231
diff changeset
96 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
97 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
98 WHERE c.ttl IS NOT NULL \
202b6ae2fcad Database cleaner: Removes outdated collections, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 231
diff changeset
99 AND DATEDIFF('MILLISECOND', c.last_access, CURRENT_TIMESTAMP) > c.ttl \
202b6ae2fcad Database cleaner: Removes outdated collections, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 231
diff changeset
100 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
101
319
61017c6031b8 Fixed H2 SQL to set TTL of collection.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 317
diff changeset
102 collections.update.ttl=UPDATE collections SET ttl = ? WHERE gid = ?
273
22a90706d32d Enables the artifact server to set the TTL of a specific collection via REST call.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 253
diff changeset
103
275
e92d5944fe4b Enabled the artifact database to retrieve requests to change the name of a specific collection.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 274
diff changeset
104 collections.update.name=UPDATE collections SET name = ? WHERE gid = ?
e92d5944fe4b Enabled the artifact database to retrieve requests to change the name of a specific collection.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 274
diff changeset
105
229
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 215
diff changeset
106 collections.touch.trigger.function = \
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 215
diff changeset
107 UPDATE collections SET last_access = current_timestamp \
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 215
diff changeset
108 WHERE id IN \
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 215
diff changeset
109 (SELECT c.id FROM collections c \
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 215
diff changeset
110 INNER JOIN collection_items ci ON c.id = ci.collection_id \
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 215
diff changeset
111 INNER JOIN artifacts a ON a.id = ci.artifact_id \
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 215
diff changeset
112 WHERE a.id = ?)
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 215
diff changeset
113
179
644fd11ddd9f Added code to set attribute of a collection item.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 178
diff changeset
114 collections.touch.by.gid =\
191
3bce5556a2e5 Fixed some sql statements that contained typos.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 187
diff changeset
115 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
116 WHERE gid = ?
644fd11ddd9f Added code to set attribute of a collection item.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 178
diff changeset
117
180
38fbbeffe8fe Added code to remove an artifact from a collection
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 179
diff changeset
118 collections.touch.by.id =\
191
3bce5556a2e5 Fixed some sql statements that contained typos.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 187
diff changeset
119 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
120 WHERE id = ?
38fbbeffe8fe Added code to remove an artifact from a collection
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 179
diff changeset
121
159
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
122 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
123
175
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
124 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
125
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
126 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
127
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
128 delete.collection=DELETE FROM collections WHERE id = ?
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
129
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
130 delete.user.collection.items= \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
131 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
132 (SELECT id FROM collections WHERE owner_id = ?)
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
133
159
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
134 collections.insert= \
199
face2302387c Insert collection attribute into database, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 197
diff changeset
135 INSERT INTO collections \
face2302387c Insert collection attribute into database, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 197
diff changeset
136 (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
137 VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?)
159
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
138
170
ac0f8bd97277 Fix parameter propagation of creation time ond collection names.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
139 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
140
343
542caebea773 Add functionality to get 'oldest' artifact of a collection.'
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 340
diff changeset
141 collections.artifacts.oldest=SELECT a.gid, ci.artifact_id \
542caebea773 Add functionality to get 'oldest' artifact of a collection.'
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 340
diff changeset
142 FROM artifacts AS a, collection_items AS ci, collections AS c \
542caebea773 Add functionality to get 'oldest' artifact of a collection.'
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 340
diff changeset
143 WHERE ci.collection_id = c.id AND c.gid = ?::uuid AND ci.artifact_id = a.id \
542caebea773 Add functionality to get 'oldest' artifact of a collection.'
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 340
diff changeset
144 ORDER BY ci.creation
542caebea773 Add functionality to get 'oldest' artifact of a collection.'
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 340
diff changeset
145
167
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
146 collections.select.user= \
281
942a54670a72 Made the TTL of a Collection retrievable via getter method.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 275
diff changeset
147 SELECT c.gid, c.name, c.creation, u.gid, c.ttl 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
148 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
149 WHERE u.gid = ?
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
150
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
151 collections.select.all= \
281
942a54670a72 Made the TTL of a Collection retrievable via getter method.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 275
diff changeset
152 SELECT c.gid, c.name, c.creation, u.gid, c.ttl 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
153 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
154
215
a9c8fa38ba03 Added new collection specific sql statements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 213
diff changeset
155 collections.select.by.gid= \
281
942a54670a72 Made the TTL of a Collection retrievable via getter method.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 275
diff changeset
156 SELECT id, name, owner_id, creation, last_access, attribute, ttl \
215
a9c8fa38ba03 Added new collection specific sql statements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 213
diff changeset
157 FROM collections WHERE gid = ?
a9c8fa38ba03 Added new collection specific sql statements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 213
diff changeset
158
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
159 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
160 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
161
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
162 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
163
159
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
164 artifacts.in.one.collection.only= \
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
165 SELECT artifact_id FROM collection_items\
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
166 WHERE collection_id = ? AND\
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
167 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
168
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
169 outdate.artifacts.collection=UPDATE artifacts \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
170 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
171 WHERE id IN \
346
2d525562fae9 Bugfix: added missing bracket to a H2 sql statement.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 343
diff changeset
172 (SELECT artifact_id FROM collection_items \
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
173 WHERE collection_id = ? AND \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
174 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
175
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
176 outdate.artifacts.user=UPDATE artifacts \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
177 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
178 WHERE id IN \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
179 (SELECT artifact_id FROM collection_items WHERE \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
180 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
181 AND artifact_id NOT IN \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
182 (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
183 (SELECT id FROM collections WHERE owner_id <> ?)))
253
a2df2b48d2aa Enabled the artifact database to handle requests specific to collection attributes and collection item attributes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 243
diff changeset
184
a2df2b48d2aa Enabled the artifact database to handle requests specific to collection attributes and collection item attributes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 243
diff changeset
185 collection.get.attribute= \
a2df2b48d2aa Enabled the artifact database to handle requests specific to collection attributes and collection item attributes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 243
diff changeset
186 SELECT c.attribute FROM collections c WHERE c.gid = ?
a2df2b48d2aa Enabled the artifact database to handle requests specific to collection attributes and collection item attributes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 243
diff changeset
187
a2df2b48d2aa Enabled the artifact database to handle requests specific to collection attributes and collection item attributes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 243
diff changeset
188 collection.set.attribute= \
a2df2b48d2aa Enabled the artifact database to handle requests specific to collection attributes and collection item attributes.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 243
diff changeset
189 UPDATE collections SET attribute = ? WHERE gid = ?
303
190aa68ae7a8 Added method to artifact database to load all artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 281
diff changeset
190
190aa68ae7a8 Added method to artifact database to load all artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 281
diff changeset
191 all.artifacts = \
320
b46c5b13ac94 Extract the collection and artifact creation times when doing the initial, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 319
diff changeset
192 SELECT u.gid AS u_gid, c.gid AS c_gid, c.name AS c_name, c.creation as c_creation, \
b46c5b13ac94 Extract the collection and artifact creation times when doing the initial, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 319
diff changeset
193 a.gid AS a_gid, a.factory AS factory, a.creation AS a_creation, a.data AS data \
317
824051dff018 When loading all artifacts fetch the name of the collections, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 314
diff changeset
194 FROM \
303
190aa68ae7a8 Added method to artifact database to load all artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 281
diff changeset
195 users u INNER JOIN collections c ON u.id = c.owner_id \
190aa68ae7a8 Added method to artifact database to load all artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 281
diff changeset
196 INNER JOIN collection_items ci ON c.id = ci.collection_id \
308
a077bb098eb4 Fixed broken SQL statement. Added debug output.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 303
diff changeset
197 INNER JOIN artifacts a ON a.id = ci.artifact_id \
303
190aa68ae7a8 Added method to artifact database to load all artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 281
diff changeset
198 ORDER BY u_gid, c_gid
http://dive4elements.wald.intevation.org