Mercurial > dive4elements > framework
annotate artifact-database/src/main/resources/sql/org-postgresql-driver.properties @ 167:c9c27aca2f70
Added code to list collections.
artifacts/trunk@1392 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Thu, 03 Mar 2011 17:01:04 +0000 |
parents | db0d20440b92 |
children | ac0f8bd97277 |
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 |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
41 # COLLECTIONS |
159
db0d20440b92
Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
144
diff
changeset
|
42 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
|
43 |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
44 delete.user.collection.items= \ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
45 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
|
46 (SELECT id FROM collections WHERE owner_id = ?) |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
47 |
159
db0d20440b92
Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
144
diff
changeset
|
48 collections.insert= \ |
db0d20440b92
Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
144
diff
changeset
|
49 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
|
50 VALUES (?, ?::uuid, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?) |
db0d20440b92
Added code to create collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
144
diff
changeset
|
51 |
167
c9c27aca2f70
Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
159
diff
changeset
|
52 collections.select.user= \ |
c9c27aca2f70
Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
159
diff
changeset
|
53 SELECT c.gid, c.name, c.creation, u.gid FROM \ |
c9c27aca2f70
Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
159
diff
changeset
|
54 collections c OUTER LEFT JOIN user u ON c.owner_id = u.id \ |
c9c27aca2f70
Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
159
diff
changeset
|
55 WHERE u.gid = ?::uuid |
c9c27aca2f70
Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
159
diff
changeset
|
56 |
c9c27aca2f70
Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
159
diff
changeset
|
57 collections.select.all= \ |
c9c27aca2f70
Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
159
diff
changeset
|
58 SELECT c.gid, c.name, c.creation, u.gid FROM \ |
c9c27aca2f70
Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
159
diff
changeset
|
59 collections c OUTER LEFT JOIN user u ON c.owner_id = u.id |
c9c27aca2f70
Added code to list collections.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
159
diff
changeset
|
60 |
144
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
61 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
|
62 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
|
63 |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
64 artifacts.in.one.collection.only=\ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
65 SELECT artifact_id FROM collection_items\ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
66 WHERE collection_id = ? AND\ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
67 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
|
68 |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
69 outdate.artifacts.collection=UPDATE artifacts \ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
70 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
|
71 WHERE id IN \ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
72 (SELECT artifact_id FROM collection_items \ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
73 WHERE collection_id = ? AND \ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
74 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
|
75 |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
76 outdate.artifacts.user=UPDATE artifacts \ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
77 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
|
78 WHERE id IN \ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
79 (SELECT artifact_id FROM collection_items WHERE \ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
80 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
|
81 AND artifact_id NOT IN \ |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
82 (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
|
83 (SELECT id FROM collections WHERE owner_id <> ?))) |
5369582d4fbf
Enable backend to delete users
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
133
diff
changeset
|
84 |