Mercurial > dive4elements > framework

annotate artifact-database/src/main/resources/sql/org-h2-driver.properties @ 253:a2df2b48d2aa

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Enabled the artifact database to handle requests specific to collection attributes and collection item attributes. artifacts/trunk@1746 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Tue, 26 Apr 2011 07:43:40 +0000
parents 33b9cc23ac9b
children 22a90706d32d
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
41
5e4bc24ea438 Made serilization more flexible. DB update required!!!
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 33
diff changeset
12 artifacts.outdated=SELECT id, factory, data 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 \
176
a0eff2227588 Added code to add artifacts to collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 175
diff changeset
54 (id, collection_id, artifact_id, attribute) \
a0eff2227588 Added code to add artifacts to collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 175
diff changeset
55 VALUES (?, ?, ?, ?)
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 \
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
80 NOT EXSITS \
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 \
213
199f0e553e3a Bugfix: Repaired broken SQL statement.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 199
diff changeset
88 WHERE ci.collection_id IN (SELECT id FROM collections WHERE gid = ?)
184
a22b7e367b25 Added Backend code to list the collection items in a collection.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 181
diff changeset
89
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
90 # 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
91
232
202b6ae2fcad Database cleaner: Removes outdated collections, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 231
diff changeset
92 collections.outdated= \
202b6ae2fcad Database cleaner: Removes outdated collections, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 231
diff changeset
93 SELECT c.id FROM collections c \
202b6ae2fcad Database cleaner: Removes outdated collections, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 231
diff changeset
94 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
95 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
96 WHERE c.ttl IS NOT NULL \
202b6ae2fcad Database cleaner: Removes outdated collections, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 231
diff changeset
97 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
98 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
99
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
100 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
101 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
102 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
103 (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
104 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
105 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
106 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
107
179
644fd11ddd9f Added code to set attribute of a collection item.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 178
diff changeset
108 collections.touch.by.gid =\
191
3bce5556a2e5 Fixed some sql statements that contained typos.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 187
diff changeset
109 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
110 WHERE gid = ?
644fd11ddd9f Added code to set attribute of a collection item.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 178
diff changeset
111
180
38fbbeffe8fe Added code to remove an artifact from a collection
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 179
diff changeset
112 collections.touch.by.id =\
191
3bce5556a2e5 Fixed some sql statements that contained typos.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 187
diff changeset
113 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
114 WHERE id = ?
38fbbeffe8fe Added code to remove an artifact from a collection
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 179
diff changeset
115
159
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
116 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
117
175
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
118 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
119
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
120 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
121
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
122 delete.collection=DELETE FROM collections WHERE id = ?
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
123
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
124 delete.user.collection.items= \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
125 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
126 (SELECT id FROM collections WHERE owner_id = ?)
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
127
159
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
128 collections.insert= \
199
face2302387c Insert collection attribute into database, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 197
diff changeset
129 INSERT INTO collections \
face2302387c Insert collection attribute into database, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 197
diff changeset
130 (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
131 VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?)
159
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
132
170
ac0f8bd97277 Fix parameter propagation of creation time ond collection names.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
133 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
134
167
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
135 collections.select.user= \
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
136 SELECT c.gid, c.name, c.creation, u.gid 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
137 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
138 WHERE u.gid = ?
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
139
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
140 collections.select.all= \
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
141 SELECT c.gid, c.name, c.creation, u.gid 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
142 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
143
215
a9c8fa38ba03 Added new collection specific sql statements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 213
diff changeset
144 collections.select.by.gid= \
a9c8fa38ba03 Added new collection specific sql statements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 213
diff changeset
145 SELECT id, name, owner_id, creation, last_access, attribute \
a9c8fa38ba03 Added new collection specific sql statements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 213
diff changeset
146 FROM collections WHERE gid = ?
a9c8fa38ba03 Added new collection specific sql statements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 213
diff changeset
147
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
148 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
149 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
150
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
151 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
152
159
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
153 artifacts.in.one.collection.only= \
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
154 SELECT artifact_id FROM collection_items\
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
155 WHERE collection_id = ? AND\
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
156 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
157
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
158 outdate.artifacts.collection=UPDATE artifacts \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
159 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
160 WHERE id IN \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
161 SELECT artifact_id FROM collection_items \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
162 WHERE collection_id = ? AND \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
163 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
164
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
165 outdate.artifacts.user=UPDATE artifacts \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
166 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
167 WHERE id IN \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
168 (SELECT artifact_id FROM collection_items WHERE \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
169 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
170 AND artifact_id NOT IN \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
171 (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
172 (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
173
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
174 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
175 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
176
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
177 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
178 UPDATE collections SET attribute = ? WHERE gid = ?
http://dive4elements.wald.intevation.org