Mercurial > dive4elements > framework

annotate artifact-database/src/main/resources/sql/org-h2-driver.properties @ 370:203bd78def81

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improved ClientProtocolUtils to extract min and max values for items with two new functions. artifacts/trunk@3701 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Wed, 18 Jan 2012 09:23:43 +0000
parents 2d525562fae9
children 08731e7403b5
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
173
940ed629419f Solve the SQL problem with the collection listings.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 170
diff changeset
36 users.insert=INSERT INTO users (id, gid, name, 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 = ?
124
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
39 users.select.gid=SELECT id, name, role FROM users WHERE gid = ?
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
40
133
2950c6011afa Fixed typo in keys.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 124
diff changeset
41 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
42
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
43 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
44
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
45 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
46
187
a85d889a1b0d Fixed some backend specific stuff (sql statements, schema).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 184
diff changeset
47 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
48 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
49
a0eff2227588 Added code to add artifacts to collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 175
diff changeset
50 # COLLECTION ITEMS
a0eff2227588 Added code to add artifacts to collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 175
diff changeset
51 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
52
187
a85d889a1b0d Fixed some backend specific stuff (sql statements, schema).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 184
diff changeset
53 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
54 (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
55 VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP)
176
a0eff2227588 Added code to add artifacts to collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 175
diff changeset
56
178
535e4ea2ef9b Added code to get the attribute of a collection item
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 176
diff changeset
57 collection.item.get.attribute= \
187
a85d889a1b0d Fixed some backend specific stuff (sql statements, schema).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 184
diff changeset
58 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
59 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
60 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
61 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
62
179
644fd11ddd9f Added code to set attribute of a collection item.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 178
diff changeset
63 collection.item.set.attribute= \
187
a85d889a1b0d Fixed some backend specific stuff (sql statements, schema).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 184
diff changeset
64 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
65 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
66 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
67 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
68 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
69
180
38fbbeffe8fe Added code to remove an artifact from a collection
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 179
diff changeset
70 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
71 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
72 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
73 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
74 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
75
38fbbeffe8fe Added code to remove an artifact from a collection
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 179
diff changeset
76 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
77 UPDATE artifacts \
38fbbeffe8fe Added code to remove an artifact from a collection
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 179
diff changeset
78 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
79 WHERE id = ? AND \
340
560d2e6eaf9c Fix SQL typo.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 320
diff changeset
80 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
81 (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
82
187
a85d889a1b0d Fixed some backend specific stuff (sql statements, schema).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 184
diff changeset
83 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
84
184
a22b7e367b25 Added Backend code to list the collection items in a collection.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 181
diff changeset
85 collection.items.list.gid= \
187
a85d889a1b0d Fixed some backend specific stuff (sql statements, schema).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 184
diff changeset
86 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
87 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
88 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
89 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
90
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
91 # 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
92
232
202b6ae2fcad Database cleaner: Removes outdated collections, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 231
diff changeset
93 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
94 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
95 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
96 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
97 WHERE c.ttl IS NOT NULL \
202b6ae2fcad Database cleaner: Removes outdated collections, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 231
diff changeset
98 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
99 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
100
319
61017c6031b8 Fixed H2 SQL to set TTL of collection.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 317
diff changeset
101 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
102
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
103 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
104
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
105 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
106 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
107 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
108 (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
109 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
110 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
111 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
112
179
644fd11ddd9f Added code to set attribute of a collection item.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 178
diff changeset
113 collections.touch.by.gid =\
191
3bce5556a2e5 Fixed some sql statements that contained typos.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 187
diff changeset
114 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
115 WHERE gid = ?
644fd11ddd9f Added code to set attribute of a collection item.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 178
diff changeset
116
180
38fbbeffe8fe Added code to remove an artifact from a collection
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 179
diff changeset
117 collections.touch.by.id =\
191
3bce5556a2e5 Fixed some sql statements that contained typos.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 187
diff changeset
118 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
119 WHERE id = ?
38fbbeffe8fe Added code to remove an artifact from a collection
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 179
diff changeset
120
159
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
121 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
122
175
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
123 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
124
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
125 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
126
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
127 delete.collection=DELETE FROM collections WHERE id = ?
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
128
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
129 delete.user.collection.items= \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
130 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
131 (SELECT id FROM collections WHERE owner_id = ?)
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
132
159
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
133 collections.insert= \
199
face2302387c Insert collection attribute into database, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 197
diff changeset
134 INSERT INTO collections \
face2302387c Insert collection attribute into database, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 197
diff changeset
135 (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
136 VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?)
159
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
137
170
ac0f8bd97277 Fix parameter propagation of creation time ond collection names.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
138 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
139
343
542caebea773 Add functionality to get 'oldest' artifact of a collection.'
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 340
diff changeset
140 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
141 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
142 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
143 ORDER BY ci.creation
542caebea773 Add functionality to get 'oldest' artifact of a collection.'
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 340
diff changeset
144
167
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
145 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
146 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
147 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
148 WHERE u.gid = ?
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
149
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
150 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
151 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
152 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
153
215
a9c8fa38ba03 Added new collection specific sql statements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 213
diff changeset
154 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
155 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
156 FROM collections WHERE gid = ?
a9c8fa38ba03 Added new collection specific sql statements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 213
diff changeset
157
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
158 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
159 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
160
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
161 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
162
159
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
163 artifacts.in.one.collection.only= \
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
164 SELECT artifact_id FROM collection_items\
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
165 WHERE collection_id = ? AND\
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
166 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
167
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
168 outdate.artifacts.collection=UPDATE artifacts \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
169 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
170 WHERE id IN \
346
2d525562fae9 Bugfix: added missing bracket to a H2 sql statement.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 343
diff changeset
171 (SELECT artifact_id FROM collection_items \
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
172 WHERE collection_id = ? AND \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
173 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
174
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
175 outdate.artifacts.user=UPDATE artifacts \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
176 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
177 WHERE id IN \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
178 (SELECT artifact_id FROM collection_items WHERE \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
179 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
180 AND artifact_id NOT IN \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
181 (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
182 (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
183
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 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
185 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
186
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 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
188 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
189
190aa68ae7a8 Added method to artifact database to load all artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 281
diff changeset
190 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
191 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
192 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
193 FROM \
303
190aa68ae7a8 Added method to artifact database to load all artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 281
diff changeset
194 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
195 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
196 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
197 ORDER BY u_gid, c_gid
http://dive4elements.wald.intevation.org