comparison artifact-database/src/main/resources/sql/org-postgresql-driver.properties @ 187:a85d889a1b0d

Fixed some backend specific stuff (sql statements, schema). artifacts/trunk@1417 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Mon, 07 Mar 2011 16:23:27 +0000
parents a22b7e367b25
children 3bce5556a2e5
comparison
equal deleted inserted replaced
186:15c7638511dc 187:a85d889a1b0d
36 36
37 users.delete.collections=DELETE FROM collections where owner_id = ? 37 users.delete.collections=DELETE FROM collections where owner_id = ?
38 38
39 users.select.all=SELECT id, gid, name, role FROM users 39 users.select.all=SELECT id, gid, name, role FROM users
40 40
41 collection.check.artifact=SELECT id FROM collections_items \ 41 collection.check.artifact=SELECT id FROM collection_items \
42 WHERE artifact_id = ? AND collection_id = ? 42 WHERE artifact_id = ? AND collection_id = ?
43 43
44 # COLLECTION ITEMS 44 # COLLECTION ITEMS
45 collection.items.id.nextval=SELECT NEXTVAL('COLLECTION_ITEMS_ID_SEQ') 45 collection.items.id.nextval=SELECT NEXTVAL('COLLECTION_ITEMS_ID_SEQ')
46 46
47 collection.items.insert=INSERT INTO collections_items \ 47 collection.items.insert=INSERT INTO collection_items \
48 (id, collection_id, artifact_id, attribute) \ 48 (id, collection_id, artifact_id, attribute) \
49 VALUES (?, ?, ?, ?) 49 VALUES (?, ?, ?, ?)
50 50
51 collection.item.get.attribute= \ 51 collection.item.get.attribute= \
52 SELECT ci.attribute FROM collections_items ci \ 52 SELECT ci.attribute FROM collection_items ci \
53 INNER JOIN collection c ON ci.collection_id = c.id \ 53 INNER JOIN collection c ON ci.collection_id = c.id \
54 INNER JOIN attributes a ON ci.artifact_id = a.id \ 54 INNER JOIN attributes a ON ci.artifact_id = a.id \
55 WHERE c.gid = ?::uuid AND a.gid = ?::uuid 55 WHERE c.gid = ?::uuid AND a.gid = ?::uuid
56 56
57 collection.item.set.attribute= \ 57 collection.item.set.attribute= \
58 UPDATE collections_items SET attribute = ? WHERE id IN ( \ 58 UPDATE collection_items SET attribute = ? WHERE id IN ( \
59 SELECT ci.id FROM collections_items ci \ 59 SELECT ci.id FROM collection_items ci \
60 INNER JOIN collection c ON ci.collection_id = c.id \ 60 INNER JOIN collection c ON ci.collection_id = c.id \
61 INNER JOIN attributes a ON ci.artifact_id = a.id \ 61 INNER JOIN attributes a ON ci.artifact_id = a.id \
62 WHERE c.gid = ?::uuid AND a.gid = ?::uuid) 62 WHERE c.gid = ?::uuid AND a.gid = ?::uuid)
63 63
64 collection.item.id.cid.aid= \ 64 collection.item.id.cid.aid= \
65 SELECT ci.id, c.collection_id, c.artifact_id FROM collections_items ci \ 65 SELECT ci.id, c.collection_id, c.artifact_id FROM collection_items ci \
66 INNER JOIN collection c ON ci.collection_id = c.id \ 66 INNER JOIN collection c ON ci.collection_id = c.id \
67 INNER JOIN attributes a ON ci.artifact_id = a.id \ 67 INNER JOIN attributes a ON ci.artifact_id = a.id \
68 WHERE c.gid = ?::uuid AND a.gid = ?::uuid 68 WHERE c.gid = ?::uuid AND a.gid = ?::uuid
69 69
70 collection.item.outdate.artifact= \ 70 collection.item.outdate.artifact= \
71 UPDATE artifacts \ 71 UPDATE artifacts \
72 SET last_access = CURRENT_TIMESTAMP - '2 microseconds'::interval, ttl = 1 \ 72 SET last_access = CURRENT_TIMESTAMP - '2 microseconds'::interval, ttl = 1 \
73 WHERE id = ? AND \ 73 WHERE id = ? AND \
74 NOT EXSITS (SELECT id FROM collections_items WHERE <> collection_id = ?) 74 NOT EXSITS (SELECT id FROM collection_items WHERE <> collection_id = ?)
75 75
76 collection.item.delete=DELETE FROM collections_items WHERE id = ? 76 collection.item.delete=DELETE FROM collection_items WHERE id = ?
77 77
78 collection.items.list.gid= \ 78 collection.items.list.gid= \
79 SELECT a.gid, ci.attribute FROM collections_items ci \ 79 SELECT a.gid, ci.attribute FROM collection_items ci \
80 INNER JOIN artifacts a ON ci.artifact_id = a.id \ 80 INNER JOIN artifacts a ON ci.artifact_id = a.id \
81 WHERE ci.id IN (SELECT id FROM collections WHERE gid = ?) 81 WHERE ci.id IN (SELECT id FROM collections WHERE gid = ?::uuid)
82 82
83 # COLLECTIONS 83 # COLLECTIONS
84 collections.touch.by.gid =\ 84 collections.touch.by.gid =\
85 UPDATE collection SET last_access = CURRENT_TIMESTAMP \ 85 UPDATE collection SET last_access = CURRENT_TIMESTAMP \
86 WHERE gid = ?::uuid 86 WHERE gid = ?::uuid

http://dive4elements.wald.intevation.org