Diffstat (limited to 'backend/node/src/clipperz.schema.sql') (more/less context) (ignore whitespace changes)
-rw-r--r-- | backend/node/src/clipperz.schema.sql | 60 |
1 files changed, 60 insertions, 0 deletions
diff --git a/backend/node/src/clipperz.schema.sql b/backend/node/src/clipperz.schema.sql new file mode 100644 index 0000000..ba6f482 --- a/dev/null +++ b/backend/node/src/clipperz.schema.sql | |||
@@ -0,0 +1,60 @@ | |||
1 | CREATE SCHEMA clipperz; | ||
2 | |||
3 | CREATE TABLE clipperz.theuser ( | ||
4 | u_id serial PRIMARY KEY, | ||
5 | u_name varchar NOT NULL UNIQUE, | ||
6 | u_srp_s varchar NOT NULL, | ||
7 | u_srp_v varchar NOT NULL, | ||
8 | u_header json NOT NULL, | ||
9 | u_statistics varchar NOT NULL, | ||
10 | u_authversion varchar NOT NULL, | ||
11 | u_version varchar NOT NULL, | ||
12 | u_lock varchar NOT NULL | ||
13 | ); | ||
14 | |||
15 | CREATE TABLE clipperz.therecord ( | ||
16 | r_id serial PRIMARY KEY, | ||
17 | u_id integer NOT NULL REFERENCES clipperz.theuser(u_id) ON UPDATE CASCADE ON DELETE CASCADE, | ||
18 | r_ref varchar NOT NULL UNIQUE, | ||
19 | r_data varchar NOT NULL, | ||
20 | r_version varchar NOT NULL, | ||
21 | r_ctime timestamp NOT NULL DEFAULT current_timestamp, | ||
22 | r_mtime timestamp NOT NULL DEFAULT current_timestamp, | ||
23 | r_atime timestamp NOT NULL DEFAULT current_timestamp | ||
24 | ); | ||
25 | CREATE INDEX therecord_u_id_key ON clipperz.therecord (u_id); | ||
26 | |||
27 | CREATE TABLE clipperz.therecordversion ( | ||
28 | rv_id serial PRIMARY KEY, | ||
29 | r_id integer NOT NULL REFERENCES clipperz.therecord (r_id) ON UPDATE CASCADE ON DELETE CASCADE, | ||
30 | rv_ref varchar NOT NULL UNIQUE, | ||
31 | rv_header varchar, | ||
32 | rv_data varchar NOT NULL, | ||
33 | rv_version varchar NOT NULL, | ||
34 | rv_previous_key varchar NOT NULL, | ||
35 | rv_previous_id varchar, | ||
36 | rv_ctime timestamp NOT NULL DEFAULT current_timestamp, | ||
37 | rv_mtime timestamp NOT NULL DEFAULT current_timestamp, | ||
38 | rv_atime timestamp NOT NULL DEFAULT current_timestamp | ||
39 | ); | ||
40 | |||
41 | CREATE TABLE clipperz.otpstatus ( | ||
42 | otps_id serial PRIMARY KEY, | ||
43 | otps_code varchar NOT NULL, | ||
44 | otps_name varchar NOT NULL, | ||
45 | otps_desc varchar NOT NULL | ||
46 | ); | ||
47 | |||
48 | CREATE TABLE clipperz.theotp ( | ||
49 | otp_id serial PRIMARY KEY, | ||
50 | u_id integer REFERENCES clipperz.theuser (u_id) ON UPDATE CASCADE ON DELETE CASCADE, | ||
51 | otps_id integer REFERENCES clipperz.otpstatus (otps_id) ON UPDATE CASCADE ON DELETE CASCADE, | ||
52 | otp_ref varchar NOT NULL UNIQUE, | ||
53 | otp_key varchar NOT NULL UNIQUE, | ||
54 | otp_key_checksum varchar NOT NULL, | ||
55 | otp_data varchar NOT NULL, | ||
56 | otp_version varchar NOT NULL, | ||
57 | otp_ctime timestamp NOT NULL DEFAULT current_timestamp, | ||
58 | otp_rtime timestamp NOT NULL DEFAULT current_timestamp, | ||
59 | otp_utime timestamp NOT NULL DEFAULT current_timestamp | ||
60 | ); | ||