Mercurial > dive4elements > framework

annotate artifact-database/src/main/resources/sql/org-postgresql-driver.properties @ 542:9497f58484a0

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Ugly: Filter collection items in user land.
author Sascha L. Teichmann <teichmann@intevation.de>
date Thu, 03 Sep 2015 17:00:30 +0200
parents 4213dd6ca169
children e9abf747f6ce
rev   line source
38
93edc04f3a10 Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
1 artifacts.id.nextval=SELECT nextval('ARTIFACTS_ID_SEQ')
93edc04f3a10 Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
2
93edc04f3a10 Added postgresql support.
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: 38
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: 38
diff changeset
5 VALUES (?, ?::uuid, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?, ?)
38
93edc04f3a10 Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
6
93edc04f3a10 Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
7 artifacts.update=UPDATE artifacts SET last_access = CURRENT_TIMESTAMP, \
93edc04f3a10 Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
8 data = ? WHERE id = ?
93edc04f3a10 Added postgresql support.
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: 38
diff changeset
10 artifacts.touch=UPDATE artifacts SET last_access = CURRENT_TIMESTAMP WHERE id = ?
38
93edc04f3a10 Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
11
542
9497f58484a0 Ugly: Filter collection items in user land.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 421
diff changeset
12 collection.items.artifact.id=SELECT artifact_id FROM collection_items
9497f58484a0 Ugly: Filter collection items in user land.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 421
diff changeset
13
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
14 artifacts.outdated=SELECT id, factory, data, gid FROM artifacts WHERE ttl IS NOT NULL \
243
33b9cc23ac9b Granularity of ttls should be milliseconds and not microseconds.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 242
diff changeset
15 AND CURRENT_TIMESTAMP - last_access > (ttl || ' milliseconds')::interval \
231
1ea35226a6de Database cleaner: Ignoring locked artifacts is now done on database level.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 216
diff changeset
16 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
17 LIMIT 50
38
93edc04f3a10 Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
18
242
b35d32e507b6 Fix for flys/issue9
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 232
diff changeset
19 artifacts.select.gid=SELECT id, ttl, factory, data FROM artifacts WHERE gid = ?::uuid \
b35d32e507b6 Fix for flys/issue9
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 232
diff changeset
20 AND (ttl IS NULL \
243
33b9cc23ac9b Granularity of ttls should be milliseconds and not microseconds.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 242
diff changeset
21 OR (CURRENT_TIMESTAMP - last_access <= (ttl || ' milliseconds')::interval) \
242
b35d32e507b6 Fix for flys/issue9
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 232
diff changeset
22 OR id IN (SELECT artifact_id FROM collection_items))
38
93edc04f3a10 Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
23
80
8447467cef86 Implementation to import artifacts from incoming xml documents (applied patch from issue208 by SLT).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 41
diff changeset
24 artifacts.get.id=SELECT id FROM artifacts WHERE gid = ?::uuid
8447467cef86 Implementation to import artifacts from incoming xml documents (applied patch from issue208 by SLT).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 41
diff changeset
25
8447467cef86 Implementation to import artifacts from incoming xml documents (applied patch from issue208 by SLT).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 41
diff changeset
26 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: 41
diff changeset
27 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: 41
diff changeset
28 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: 41
diff changeset
29 WHERE id = ?
8447467cef86 Implementation to import artifacts from incoming xml documents (applied patch from issue208 by SLT).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 41
diff changeset
30
38
93edc04f3a10 Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
31 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
32
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
33 # USERS
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
34
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
35 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
36
407
08731e7403b5 Update sql user queries to add the account column
Bjoern Ricks <bjoern.ricks@intevation.de>
parents: 343
diff changeset
37 users.insert=INSERT INTO users (id, gid, name, account, role) VALUES (?, ?::uuid, ?, ?, ?)
124
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
38
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
39 users.select.id.by.gid=SELECT id FROM users WHERE gid = ?::uuid
407
08731e7403b5 Update sql user queries to add the account column
Bjoern Ricks <bjoern.ricks@intevation.de>
parents: 343
diff changeset
40 users.select.gid=SELECT id, name, account, role FROM users WHERE gid = ?::uuid
421
4213dd6ca169 Fix queries for finding a user
Bjoern Ricks <bjoern.ricks@intevation.de>
parents: 413
diff changeset
41 users.select.account=SELECT gid, name, account, role FROM users WHERE account = ?
124
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
42
133
2950c6011afa Fixed typo in keys.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 124
diff changeset
43 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
44
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
45 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
46
407
08731e7403b5 Update sql user queries to add the account column
Bjoern Ricks <bjoern.ricks@intevation.de>
parents: 343
diff changeset
47 users.select.all=SELECT id, gid, name, account, role FROM users
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
48
187
a85d889a1b0d Fixed some backend specific stuff (sql statements, schema).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 184
diff changeset
49 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
50 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
51
a0eff2227588 Added code to add artifacts to collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 175
diff changeset
52 # COLLECTION ITEMS
a0eff2227588 Added code to add artifacts to collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 175
diff changeset
53 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
54
187
a85d889a1b0d Fixed some backend specific stuff (sql statements, schema).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 184
diff changeset
55 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
56 (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
57 VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP)
176
a0eff2227588 Added code to add artifacts to collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 175
diff changeset
58
178
535e4ea2ef9b Added code to get the attribute of a collection item
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 176
diff changeset
59 collection.item.get.attribute= \
187
a85d889a1b0d Fixed some backend specific stuff (sql statements, schema).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 184
diff changeset
60 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
61 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
62 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
63 WHERE c.gid = ?::uuid AND a.gid = ?::uuid
535e4ea2ef9b Added code to get the attribute of a collection item
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 176
diff changeset
64
179
644fd11ddd9f Added code to set attribute of a collection item.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 178
diff changeset
65 collection.item.set.attribute= \
187
a85d889a1b0d Fixed some backend specific stuff (sql statements, schema).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 184
diff changeset
66 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
67 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
68 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
69 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
70 WHERE c.gid = ?::uuid AND a.gid = ?::uuid)
644fd11ddd9f Added code to set attribute of a collection item.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 178
diff changeset
71
180
38fbbeffe8fe Added code to remove an artifact from a collection
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 179
diff changeset
72 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
73 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
74 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
75 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
76 WHERE c.gid = ?::uuid AND a.gid = ?::uuid
38fbbeffe8fe Added code to remove an artifact from a collection
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 179
diff changeset
77
38fbbeffe8fe Added code to remove an artifact from a collection
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 179
diff changeset
78 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
79 UPDATE artifacts \
243
33b9cc23ac9b Granularity of ttls should be milliseconds and not microseconds.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 242
diff changeset
80 SET last_access = CURRENT_TIMESTAMP - '2 milliseconds'::interval, ttl = 1 \
180
38fbbeffe8fe Added code to remove an artifact from a collection
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 179
diff changeset
81 WHERE id = ? AND \
340
560d2e6eaf9c Fix SQL typo.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 320
diff changeset
82 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
83 (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
84
187
a85d889a1b0d Fixed some backend specific stuff (sql statements, schema).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 184
diff changeset
85 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
86
184
a22b7e367b25 Added Backend code to list the collection items in a collection.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 180
diff changeset
87 collection.items.list.gid= \
187
a85d889a1b0d Fixed some backend specific stuff (sql statements, schema).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 184
diff changeset
88 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: 180
diff changeset
89 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
90 WHERE ci.collection_id IN (SELECT id FROM collections WHERE gid = ?::uuid) \
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
91 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: 180
diff changeset
92
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
93 # COLLECTIONS
232
202b6ae2fcad Database cleaner: Removes outdated collections, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 231
diff changeset
94
202b6ae2fcad Database cleaner: Removes outdated collections, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 231
diff changeset
95 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
96 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
97 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
98 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
99 WHERE c.ttl IS NOT NULL \
243
33b9cc23ac9b Granularity of ttls should be milliseconds and not microseconds.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 242
diff changeset
100 AND CURRENT_TIMESTAMP - c.last_access > (c.ttl || ' milliseconds')::interval \
232
202b6ae2fcad Database cleaner: Removes outdated collections, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 231
diff changeset
101 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
102
273
22a90706d32d Enables the artifact server to set the TTL of a specific collection via REST call.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 253
diff changeset
103 collections.update.ttl=UPDATE collections SET ttl = ? WHERE gid = ?::uuid
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
104
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
105 collections.update.name=UPDATE collections SET name = ? WHERE gid = ?::uuid
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
106
179
644fd11ddd9f Added code to set attribute of a collection item.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 178
diff changeset
107 collections.touch.by.gid =\
191
3bce5556a2e5 Fixed some sql statements that contained typos.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 187
diff changeset
108 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
109 WHERE gid = ?::uuid
644fd11ddd9f Added code to set attribute of a collection item.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 178
diff changeset
110
180
38fbbeffe8fe Added code to remove an artifact from a collection
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 179
diff changeset
111 collections.touch.by.id =\
191
3bce5556a2e5 Fixed some sql statements that contained typos.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 187
diff changeset
112 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
113 WHERE id = ?
159
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
114 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
115
175
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
116 collections.id.by.gid=SELECT id FROM collections WHERE gid = ?::uuid
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
117
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
118 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
119
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
120 delete.collection=DELETE FROM collections WHERE id = ?
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
121
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
122 delete.user.collection.items= \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
123 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
124 (SELECT id FROM collections WHERE owner_id = ?)
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
125
159
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
126 collections.insert= \
199
face2302387c Insert collection attribute into database, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 197
diff changeset
127 INSERT INTO collections \
face2302387c Insert collection attribute into database, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 197
diff changeset
128 (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
129 VALUES (?, ?::uuid, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?)
159
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
130
170
ac0f8bd97277 Fix parameter propagation of creation time ond collection names.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
131 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
132
343
542caebea773 Add functionality to get 'oldest' artifact of a collection.'
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 340
diff changeset
133 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
134 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
135 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
136 ORDER BY ci.creation
542caebea773 Add functionality to get 'oldest' artifact of a collection.'
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 340
diff changeset
137
167
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
138 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
139 SELECT c.gid, c.name, c.creation, u.gid, c.ttl FROM \
173
940ed629419f Solve the SQL problem with the collection listings.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 170
diff changeset
140 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
141 WHERE u.gid = ?::uuid
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
142
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
143 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
144 SELECT c.gid, c.name, c.creation, u.gid, c.ttl FROM \
173
940ed629419f Solve the SQL problem with the collection listings.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 170
diff changeset
145 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
146
215
a9c8fa38ba03 Added new collection specific sql statements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 213
diff changeset
147 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
148 SELECT id, name, owner_id, creation, last_access, attribute, ttl \
216
2d90d5cfe8f9 Added a cast to uuid in collections.select.by.gid
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 215
diff changeset
149 FROM collections WHERE gid = ?::uuid
215
a9c8fa38ba03 Added new collection specific sql statements.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents: 213
diff changeset
150
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
151 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
152 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
153
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
154 artifacts.in.one.collection.only=\
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
155 SELECT artifact_id FROM collection_items\
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
156 WHERE collection_id = ? AND\
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
157 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
158
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
159 outdate.artifacts.collection=UPDATE artifacts \
243
33b9cc23ac9b Granularity of ttls should be milliseconds and not microseconds.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 242
diff changeset
160 SET last_access = CURRENT_TIMESTAMP - '2 milliseconds'::interval, ttl = 1 \
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
161 WHERE id IN \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
162 (SELECT artifact_id FROM collection_items \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
163 WHERE collection_id = ? AND \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
164 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
165
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
166 outdate.artifacts.user=UPDATE artifacts \
243
33b9cc23ac9b Granularity of ttls should be milliseconds and not microseconds.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 242
diff changeset
167 SET last_access = CURRENT_TIMESTAMP - '2 milliseconds'::interval, ttl = 1 \
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
168 WHERE id IN \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
169 (SELECT artifact_id FROM collection_items WHERE \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
170 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
171 AND artifact_id NOT IN \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
172 (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
173 (SELECT id FROM collections WHERE owner_id <> ?)))
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
174
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
175 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
176 SELECT c.attribute FROM collections c WHERE c.gid = ?::uuid
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
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 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
179 UPDATE collections SET attribute = ? WHERE gid = ?::uuid
303
190aa68ae7a8 Added method to artifact database to load all artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 281
diff changeset
180
190aa68ae7a8 Added method to artifact database to load all artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 281
diff changeset
181 all.artifacts = \
320
b46c5b13ac94 Extract the collection and artifact creation times when doing the initial, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 317
diff changeset
182 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: 317
diff changeset
183 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
184 FROM \
303
190aa68ae7a8 Added method to artifact database to load all artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 281
diff changeset
185 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
186 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
187 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
188 ORDER BY u_gid, c_gid
http://dive4elements.wald.intevation.org