vubVqv7h

1-----------------------------------------------------------------------
2-- user
3-----------------------------------------------------------------------
4
5DROP TABLE IF EXISTS [user];
6
7CREATE TABLE [user]
8(
9    [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
10    [first_name] VARCHAR(128) NOT NULL,
11    [last_name] VARCHAR(128) NOT NULL,
12    [email] VARCHAR(128) NOT NULL,
13    [role_id] INTEGER NOT NULL,
14    UNIQUE ([id]),
15    FOREIGN KEY ([role_id]) REFERENCES [role] ([id])
16);
17
18-----------------------------------------------------------------------
19-- role
20-----------------------------------------------------------------------
21
22DROP TABLE IF EXISTS [role];
23
24CREATE TABLE [role]
25(
26    [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
27    [name] VARCHAR(128) NOT NULL,
28    UNIQUE ([id])
29);
30
31-----------------------------------------------------------------------
32-- credential
33-----------------------------------------------------------------------
34
35DROP TABLE IF EXISTS [credential];
36
37CREATE TABLE [credential]
38(
39    [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
40    [username] VARCHAR(128) NOT NULL,
41    [password] VARCHAR(128) NOT NULL,
42    [note] VARCHAR(255) NOT NULL,
43    UNIQUE ([id])
44);
45
46-----------------------------------------------------------------------
47-- tag
48-----------------------------------------------------------------------
49
50DROP TABLE IF EXISTS [tag];
51
52CREATE TABLE [tag]
53(
54    [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
55    [name] VARCHAR(128) NOT NULL,
56    UNIQUE ([id])
57);
58
59-----------------------------------------------------------------------
60-- user_role
61-----------------------------------------------------------------------
62
63DROP TABLE IF EXISTS [user_role];
64
65CREATE TABLE [user_role]
66(
67    [user_id] INTEGER NOT NULL,
68    [role_id] INTEGER NOT NULL,
69    PRIMARY KEY ([user_id],[role_id]),
70    UNIQUE ([user_id],[role_id]),
71    FOREIGN KEY ([user_id]) REFERENCES [user] ([id]),
72    FOREIGN KEY ([role_id]) REFERENCES [role] ([id])
73);
74
75-----------------------------------------------------------------------
76-- credential_tags
77-----------------------------------------------------------------------
78
79DROP TABLE IF EXISTS [credential_tags];
80
81CREATE TABLE [credential_tags]
82(
83    [credential_id] INTEGER NOT NULL,
84    [tag_id] INTEGER NOT NULL,
85    PRIMARY KEY ([credential_id],[tag_id]),
86    UNIQUE ([credential_id],[tag_id]),
87    FOREIGN KEY ([credential_id]) REFERENCES [credential] ([id]),
88    FOREIGN KEY ([tag_id]) REFERENCES [tag] ([id])
89);
90
91-----------------------------------------------------------------------
92-- credential_role
93-----------------------------------------------------------------------
94
95DROP TABLE IF EXISTS [credential_role];
96
97CREATE TABLE [credential_role]
98(
99    [credential_id] INTEGER NOT NULL,
100    [role_id] INTEGER NOT NULL,
101    PRIMARY KEY ([credential_id],[role_id]),
102    UNIQUE ([credential_id],[role_id]),
103    FOREIGN KEY ([credential_id]) REFERENCES [credential] ([id]),
104    FOREIGN KEY ([role_id]) REFERENCES [role] ([id])
105);