Mercurial > dive4elements > framework
annotate artifact-database/src/main/resources/sql/org-h2-driver.properties @ 298:a5e6d1923c95
Made namespace resolution more compatible.
artifacts/trunk@2383 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Thu, 21 Jul 2011 08:32:34 +0000 |
parents | 942a54670a72 |
children | 190aa68ae7a8 |
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 \ |
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 \ |
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 \ |
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= \ |
202b6ae2fcad
Database cleaner: Removes outdated collections, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
231
diff
changeset
|
94 SELECT c.id FROM collections c \ |
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 |
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
|
101 collections.update.ttl=UPDATE collections SET ttl = ? WHERE id = ? |
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 |
167
c9c27aca2f70
Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
159
diff
changeset
|
140 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
|
141 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
|
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 WHERE u.gid = ? |
c9c27aca2f70
Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
159
diff
changeset
|
144 |
c9c27aca2f70
Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
159
diff
changeset
|
145 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
|
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 |
215
a9c8fa38ba03
Added new collection specific sql statements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
213
diff
changeset
|
149 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
|
150 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
|
151 FROM collections WHERE gid = ? |
a9c8fa38ba03
Added new collection specific sql statements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
213
diff
changeset
|
152 |
144
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
153 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
|
154 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
|
155 |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
156 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
|
157 |
159
db0d20440b92
Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
144
diff
changeset
|
158 artifacts.in.one.collection.only= \ |
144
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
159 SELECT artifact_id FROM collection_items\ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
160 WHERE collection_id = ? AND\ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
161 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
|
162 |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
163 outdate.artifacts.collection=UPDATE artifacts \ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
164 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
|
165 WHERE id IN \ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
166 SELECT artifact_id FROM collection_items \ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
167 WHERE collection_id = ? AND \ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
168 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
|
169 |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
170 outdate.artifacts.user=UPDATE artifacts \ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
171 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
|
172 WHERE id IN \ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
173 (SELECT artifact_id FROM collection_items WHERE \ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
174 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
|
175 AND artifact_id NOT IN \ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
176 (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
|
177 (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
|
178 |
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
|
179 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
|
180 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
|
181 |
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
|
182 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
|
183 UPDATE collections SET attribute = ? WHERE gid = ? |