Mercurial > dive4elements > framework
annotate artifact-database/doc/schema-pg.sql @ 186:15c7638511dc
Added an interface for CollectionItems and renamed the CollectionItem class from the artifact-database package to DefaultCollectionItem - because this is the default implementation of the interface.
artifacts/trunk@1416 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Ingo Weinzierl <ingo.weinzierl@intevation.de> |
---|---|
date | Mon, 07 Mar 2011 15:13:36 +0000 |
parents | a76de72ad6d1 |
children | a85d889a1b0d |
rev | line source |
---|---|
38
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
1 -- |
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
2 -- schema to store artifacts in PostgreSQL databases. |
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
3 -- |
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
4 |
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
5 BEGIN; |
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 -- not using AUTO_INCREMENT to be more compatible with |
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
8 -- other dbms. |
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
9 CREATE SEQUENCE ARTIFACTS_ID_SEQ; |
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
10 |
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
11 CREATE TABLE artifacts ( |
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
12 id int PRIMARY KEY NOT NULL, |
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
13 gid uuid NOT NULL UNIQUE, |
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
14 creation timestamp NOT NULL, |
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
15 last_access timestamp NOT NULL, |
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
16 ttl bigint, -- NULL means eternal |
41
5e4bc24ea438
Made serilization more flexible. DB update required!!!
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
38
diff
changeset
|
17 factory VARCHAR(256) NOT NULL, |
38
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
18 data bytea |
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
19 ); |
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
20 |
121
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
21 CREATE SEQUENCE USERS_ID_SEQ; |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
22 |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
23 CREATE TABLE users ( |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
24 id int PRIMARY KEY NOT NULL, |
124
caf9f456f7e3
Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
121
diff
changeset
|
25 gid uuid NOT NULL UNIQUE, |
121
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
26 name VARCHAR(256) NOT NULL UNIQUE, |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
27 role BINARY |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
28 ); |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
29 |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
30 CREATE SEQUENCE COLLECTIONS_ID_SEQ; |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
31 |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
32 CREATE TABLE collections ( |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
33 id int PRIMARY KEY NOT NULL, |
124
caf9f456f7e3
Added SQL statements to handle users in database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
121
diff
changeset
|
34 gid uuid NOT NULL UNIQUE, |
121
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
35 name VARCHAR(256) NOT NULL, |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
36 owner_id int NOT NULL REFERENCES users(id), |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
37 creation timestamp NOT NULL, |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
38 last_access timestamp NOT NULL, |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
39 ttl bigint, -- NULL means eternal |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
40 ); |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
41 |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
42 CREATE SEQUENCE COLLECTION_ITEMS_SEQ; |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
43 |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
44 CREATE TABLE collection_items ( |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
45 id int PRIMARY KEY NOT NULL, |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
46 collection_id int NOT NULL REFERENCES collections(id), |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
47 artifact_id int NOT NULL REFERENCES artifacts(id), |
156
a76de72ad6d1
Added stubs for the collection handling in db backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
124
diff
changeset
|
48 attribute BINARY, |
121
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
49 UNIQUE (collection_id, artifact_id) |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
50 ); |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
51 |
38
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
52 COMMIT; |