Mercurial > dive4elements > framework

annotate artifact-database/src/main/resources/sql/org-postgresql-driver.properties @ 178:535e4ea2ef9b

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added code to get the attribute of a collection item artifacts/trunk@1404 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Sun, 06 Mar 2011 10:27:16 +0000
parents a0eff2227588
children 644fd11ddd9f
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
41
5e4bc24ea438 Made serilization more flexible. DB update required!!!
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 38
diff changeset
12 artifacts.outdated=SELECT id, factory, data FROM artifacts WHERE ttl IS NOT NULL \
38
93edc04f3a10 Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
13 AND CURRENT_TIMESTAMP - last_access > (ttl || ' microseconds')::interval LIMIT 50
93edc04f3a10 Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
14
41
5e4bc24ea438 Made serilization more flexible. DB update required!!!
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 38
diff changeset
15 artifacts.select.gid=SELECT id, last_access, ttl, factory, data FROM artifacts WHERE gid = ?::uuid
38
93edc04f3a10 Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
16
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
17 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
18
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
19 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
20 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
21 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
22 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
23
38
93edc04f3a10 Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
24 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
25
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
26 # USERS
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
27
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
28 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
29
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
30 users.insert=INSERT INTO users (id, gid, name, role) VALUES (?, ?, ?, ?)
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
31
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
32 users.select.id.by.gid=SELECT id FROM users WHERE gid = ?::uuid
124
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
33 users.select.gid=SELECT id, name, role FROM users WHERE gid = ?::uuid
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
34
133
2950c6011afa Fixed typo in keys.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 124
diff changeset
35 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
36
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
37 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
38
caf9f456f7e3 Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 80
diff changeset
39 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
40
176
a0eff2227588 Added code to add artifacts to collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 175
diff changeset
41 collection.check.artifact=SELECT id FROM collections_items \
a0eff2227588 Added code to add artifacts to collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 175
diff changeset
42 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
43
a0eff2227588 Added code to add artifacts to collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 175
diff changeset
44 # COLLECTION ITEMS
a0eff2227588 Added code to add artifacts to collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 175
diff changeset
45 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
46
a0eff2227588 Added code to add artifacts to collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 175
diff changeset
47 collection.items.insert=INSERT INTO collections_items \
a0eff2227588 Added code to add artifacts to collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 175
diff changeset
48 (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
49 VALUES (?, ?, ?, ?)
a0eff2227588 Added code to add artifacts to collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 175
diff changeset
50
178
535e4ea2ef9b Added code to get the attribute of a collection item
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 176
diff changeset
51 collection.item.get.attribute= \
535e4ea2ef9b Added code to get the attribute of a collection item
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 176
diff changeset
52 SELECT ci.attribute FROM collections_items ci \
535e4ea2ef9b Added code to get the attribute of a collection item
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 176
diff changeset
53 INNER JOIN collection c ON ci.collection_id = c.id \
535e4ea2ef9b Added code to get the attribute of a collection item
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 176
diff changeset
54 INNER JOIN attributes a ON ci.artifact_id = a.id \
535e4ea2ef9b Added code to get the attribute of a collection item
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 176
diff changeset
55 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
56
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
57 # COLLECTIONS
159
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
58 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
59
175
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
60 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
61
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
62 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
63
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
64 delete.collection=DELETE FROM collections WHERE id = ?
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
65
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
66 delete.user.collection.items= \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
67 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
68 (SELECT id FROM collections WHERE owner_id = ?)
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
69
159
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
70 collections.insert= \
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
71 INSERT INTO collections (id, gid, name, owner_id, creation, last_access, ttl) \
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
72 VALUES (?, ?::uuid, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?)
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
73
170
ac0f8bd97277 Fix parameter propagation of creation time ond collection names.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
74 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
75
167
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
76 collections.select.user= \
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
77 SELECT c.gid, c.name, c.creation, u.gid FROM \
173
940ed629419f Solve the SQL problem with the collection listings.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 170
diff changeset
78 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
79 WHERE u.gid = ?::uuid
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
80
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
81 collections.select.all= \
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
82 SELECT c.gid, c.name, c.creation, u.gid FROM \
173
940ed629419f Solve the SQL problem with the collection listings.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 170
diff changeset
83 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
84
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
85 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
86 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
87
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
88 artifacts.in.one.collection.only=\
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
89 SELECT artifact_id FROM collection_items\
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
90 WHERE collection_id = ? AND\
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
91 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
92
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
93 outdate.artifacts.collection=UPDATE artifacts \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
94 SET last_access = CURRENT_TIMESTAMP - '2 microseconds'::interval, ttl = 1 \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
95 WHERE id IN \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
96 (SELECT artifact_id FROM collection_items \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
97 WHERE collection_id = ? AND \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
98 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
99
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
100 outdate.artifacts.user=UPDATE artifacts \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
101 SET last_access = CURRENT_TIMESTAMP - '2 microseconds'::interval, ttl = 1 \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
102 WHERE id IN \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
103 (SELECT artifact_id FROM collection_items WHERE \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
104 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
105 AND artifact_id NOT IN \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
106 (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
107 (SELECT id FROM collections WHERE owner_id <> ?)))
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
108
http://dive4elements.wald.intevation.org