Mercurial > dive4elements > river

annotate flys-artifacts/doc/conf/datacage.sql @ 990:e573c4715d81

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add artifacts data into datacage db at initial scan. flys-artifacts/trunk@2422 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Fri, 29 Jul 2011 08:40:11 +0000
parents dbe39e1fb5e7
children 5b467bc27eda
rev   line source
979
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
1 BEGIN;
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
2
985
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
3 CREATE SEQUENCE USERS_ID_SEQ;
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
4
979
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
5 CREATE TABLE users (
985
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
6 id INT PRIMARY KEY NOT NULL,
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
7 gid UUID NOT NULL UNIQUE
979
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
8 );
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
9
985
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
10 CREATE SEQUENCE COLLECTIONS_ID_SEQ;
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
11
979
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
12 CREATE TABLE collections (
985
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
13 id INT PRIMARY KEY NOT NULL,
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
14 gid UUID NOT NULL UNIQUE,
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
15 user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
16 name VARCHAR(256) NOT NULL
979
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
17 );
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
18
985
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
19 CREATE SEQUENCE ARTIFACTS_ID_SEQ;
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
20
979
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
21 CREATE TABLE artifacts (
985
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
22 id INT PRIMARY KEY NOT NULL,
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
23 gid UUID NOT NULL UNIQUE,
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
24 state VARCHAR(256) NOT NULL
979
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
25 );
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
26
988
dbe39e1fb5e7 Datacage: Add collection items at initial scan.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 985
diff changeset
27 CREATE SEQUENCE COLLECTION_ITEMS_ID_SEQ;
985
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
28
979
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
29 CREATE TABLE collection_items (
985
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
30 id INT PRIMARY KEY NOT NULL,
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
31 collection_id INT NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
32 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE
979
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
33 );
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
34
985
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
35 CREATE SEQUENCE ARTIFACT_DATA_ID_SEQ;
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
36
979
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
37 CREATE TABLE artifact_data (
985
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
38 id INT PRIMARY KEY NOT NULL,
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
39 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
990
e573c4715d81 Add artifacts data into datacage db at initial scan.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 988
diff changeset
40 kind VARCHAR(256) NOT NULL,
985
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
41 k VARCHAR(256) NOT NULL,
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
42 v VARCHAR(256), -- Maybe too short
979
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
43 UNIQUE (artifact_id, k)
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
44 );
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
45
985
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
46 CREATE SEQUENCE OUTS_ID_SEQ;
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
47
981
799c7108ea6d datacage: prepared access to outs of artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 980
diff changeset
48 CREATE TABLE outs (
985
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
49 id INT PRIMARY KEY NOT NULL,
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
50 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
51 name VARCHAR(256) NOT NULL,
981
799c7108ea6d datacage: prepared access to outs of artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 980
diff changeset
52 description VARCHAR(256),
799c7108ea6d datacage: prepared access to outs of artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 980
diff changeset
53 out_type VARCHAR(256)
799c7108ea6d datacage: prepared access to outs of artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 980
diff changeset
54 );
799c7108ea6d datacage: prepared access to outs of artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 980
diff changeset
55
985
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
56 CREATE SEQUENCE FACETS_ID_SEQ;
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
57
979
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
58 CREATE TABLE facets (
985
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
59 id INT PRIMARY KEY NOT NULL,
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
60 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
61 out_id INT NOT NULL REFERENCES outs(id) ON DELETE CASCADE,
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
62 name VARCHAR(256) NOT NULL,
979
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
63 num INT NOT NULL,
980
f9a6a9cd918e Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 979
diff changeset
64 state VARCHAR(256) NOT NULL,
979
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
65 description VARCHAR(256),
985
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
66 UNIQUE (artifact_id, out_id, num, name)
979
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
67 );
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
68
985
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
69 -- DROP SEQUENCE USERS_ID_SEQ;
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
70 -- DROP SEQUENCE COLLECTIONS_ID_SEQ;
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
71 -- DROP SEQUENCE ARTIFACTS_ID_SEQ;
988
dbe39e1fb5e7 Datacage: Add collection items at initial scan.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 985
diff changeset
72 -- DROP SEQUENCE COLLECTION_ITEMS_ID_SEQ;
985
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
73 -- DROP SEQUENCE ARTIFACT_DATA_ID_SEQ;
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
74 -- DROP SEQUENCE OUTS_ID_SEQ;
8094ba4ab873 Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 981
diff changeset
75 -- DROP SEQUENCE FACETS_ID_SEQ;
980
f9a6a9cd918e Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 979
diff changeset
76 -- DROP TABLE facets;
981
799c7108ea6d datacage: prepared access to outs of artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 980
diff changeset
77 -- DROP TABLE outs;
980
f9a6a9cd918e Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 979
diff changeset
78 -- DROP TABLE artifact_data;
f9a6a9cd918e Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 979
diff changeset
79 -- DROP TABLE collection_items;
f9a6a9cd918e Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 979
diff changeset
80 -- DROP TABLE collections;
f9a6a9cd918e Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 979
diff changeset
81 -- DROP TABLE artifacts;
f9a6a9cd918e Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 979
diff changeset
82 -- DROP TABLE users;
f9a6a9cd918e Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 979
diff changeset
83
979
2306340d7540 Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
84 COMMIT;
http://dive4elements.wald.intevation.org