Mercurial > dive4elements > framework

annotate artifact-database/src/main/resources/sql/org-postgresql-driver.properties @ 176:a0eff2227588

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added code to add artifacts to collections. artifacts/trunk@1402 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Fri, 04 Mar 2011 17:51:15 +0000
parents 16e6e661e6bf
children 535e4ea2ef9b
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
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
51 # COLLECTIONS
159
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
52 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
53
175
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
54 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
55
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
56 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
57
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
58 delete.collection=DELETE FROM collections WHERE id = ?
16e6e661e6bf Added code to delete collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 173
diff changeset
59
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
60 delete.user.collection.items= \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
61 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
62 (SELECT id FROM collections WHERE owner_id = ?)
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
63
159
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
64 collections.insert= \
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
65 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
66 VALUES (?, ?::uuid, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?)
db0d20440b92 Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 144
diff changeset
67
170
ac0f8bd97277 Fix parameter propagation of creation time ond collection names.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 167
diff changeset
68 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
69
167
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
70 collections.select.user= \
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
71 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
72 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
73 WHERE u.gid = ?::uuid
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
74
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
75 collections.select.all= \
c9c27aca2f70 Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 159
diff changeset
76 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
77 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
78
144
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
79 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
80 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
81
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
82 artifacts.in.one.collection.only=\
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
83 SELECT artifact_id FROM collection_items\
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
84 WHERE collection_id = ? AND\
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
85 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
86
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
87 outdate.artifacts.collection=UPDATE artifacts \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
88 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
89 WHERE id IN \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
90 (SELECT artifact_id FROM collection_items \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
91 WHERE collection_id = ? AND \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
92 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
93
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
94 outdate.artifacts.user=UPDATE artifacts \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
95 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
96 WHERE id IN \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
97 (SELECT artifact_id FROM collection_items WHERE \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
98 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
99 AND artifact_id NOT IN \
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
100 (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
101 (SELECT id FROM collections WHERE owner_id <> ?)))
5369582d4fbf Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 133
diff changeset
102
http://dive4elements.wald.intevation.org