Mercurial > dive4elements > framework

annotate artifact-database/src/main/resources/sql/org-h2-driver.properties @ 481:6f6a5b229a7a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use correct data type for user role in sql statement.
author Raimund Renkert <rrenkert@intevation.de>
date Mon, 06 May 2013 16:36: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