1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
|
-- ----------------------------------------------------- --
-- Mariadb/Mysql Database --
-- Initialize Glewlwyd Database for the backend server --
-- The administration client app --
-- Copyright 2020 Nicolas Mora <mail@babelouest.org> --
-- License: MIT --
-- ----------------------------------------------------- --
DROP TABLE IF EXISTS g_misc_config;
DROP TABLE IF EXISTS g_api_key;
DROP TABLE IF EXISTS g_client_user_scope;
DROP TABLE IF EXISTS g_scope_group_auth_scheme_module_instance;
DROP TABLE IF EXISTS g_scope_group;
DROP TABLE IF EXISTS g_user_session_scheme;
DROP TABLE IF EXISTS g_scope;
DROP TABLE IF EXISTS g_plugin_module_instance;
DROP TABLE IF EXISTS g_user_module_instance;
DROP TABLE IF EXISTS g_user_middleware_module_instance;
DROP TABLE IF EXISTS g_user_auth_scheme_module_instance;
DROP TABLE IF EXISTS g_client_module_instance;
DROP TABLE IF EXISTS g_user_session;
CREATE TABLE g_user_module_instance (
gumi_id INT(11) PRIMARY KEY AUTO_INCREMENT,
gumi_module VARCHAR(128) NOT NULL,
gumi_order INT(11) NOT NULL,
gumi_name VARCHAR(128) NOT NULL,
gumi_display_name VARCHAR(256) DEFAULT '',
gumi_parameters MEDIUMBLOB,
gumi_readonly TINYINT(1) DEFAULT 0,
gumi_multiple_passwords TINYINT(1) DEFAULT 0,
gumi_enabled TINYINT(1) DEFAULT 1
);
CREATE TABLE g_user_middleware_module_instance (
gummi_id INT(11) PRIMARY KEY AUTO_INCREMENT,
gummi_module VARCHAR(128) NOT NULL,
gummi_order INT(11) NOT NULL,
gummi_name VARCHAR(128) NOT NULL,
gummi_display_name VARCHAR(256) DEFAULT '',
gummi_parameters MEDIUMBLOB,
gummi_enabled TINYINT(1) DEFAULT 1
);
CREATE TABLE g_user_auth_scheme_module_instance (
guasmi_id INT(11) PRIMARY KEY AUTO_INCREMENT,
guasmi_module VARCHAR(128) NOT NULL,
guasmi_expiration INT(11) NOT NULL DEFAULT 0,
guasmi_max_use INT(11) DEFAULT 0, -- 0: unlimited
guasmi_allow_user_register TINYINT(1) DEFAULT 1,
guasmi_name VARCHAR(128) NOT NULL,
guasmi_display_name VARCHAR(256) DEFAULT '',
guasmi_parameters MEDIUMBLOB,
guasmi_enabled TINYINT(1) DEFAULT 1
);
CREATE TABLE g_client_module_instance (
gcmi_id INT(11) PRIMARY KEY AUTO_INCREMENT,
gcmi_module VARCHAR(128) NOT NULL,
gcmi_order INT(11) NOT NULL,
gcmi_name VARCHAR(128) NOT NULL,
gcmi_display_name VARCHAR(256) DEFAULT '',
gcmi_parameters MEDIUMBLOB,
gcmi_readonly TINYINT(1) DEFAULT 0,
gcmi_enabled TINYINT(1) DEFAULT 1
);
CREATE TABLE g_plugin_module_instance (
gpmi_id INT(11) PRIMARY KEY AUTO_INCREMENT,
gpmi_module VARCHAR(128) NOT NULL,
gpmi_name VARCHAR(128) NOT NULL,
gpmi_display_name VARCHAR(256) DEFAULT '',
gpmi_parameters MEDIUMBLOB,
gpmi_enabled TINYINT(1) DEFAULT 1
);
CREATE TABLE g_user_session (
gus_id INT(11) PRIMARY KEY AUTO_INCREMENT,
gus_session_hash VARCHAR(128) NOT NULL,
gus_user_agent VARCHAR(256),
gus_issued_for VARCHAR(256), -- IP address or hostname
gus_username VARCHAR(256) NOT NULL,
gus_expiration TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
gus_last_login TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
gus_current TINYINT(1),
gus_enabled TINYINT(1) DEFAULT 1
);
CREATE INDEX i_g_user_session_username ON g_user_session(gus_username);
CREATE INDEX i_g_user_session_last_login ON g_user_session(gus_last_login);
CREATE INDEX i_g_user_session_expiration ON g_user_session(gus_expiration);
CREATE TABLE g_user_session_scheme (
guss_id INT(11) PRIMARY KEY AUTO_INCREMENT,
gus_id INT(11) NOT NULL,
guasmi_id INT(11) DEFAULT NULL, -- NULL means scheme 'password'
guss_expiration TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
guss_last_login TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
guss_use_counter INT(11) DEFAULT 0,
guss_enabled TINYINT(1) DEFAULT 1,
FOREIGN KEY(gus_id) REFERENCES g_user_session(gus_id) ON DELETE CASCADE,
FOREIGN KEY(guasmi_id) REFERENCES g_user_auth_scheme_module_instance(guasmi_id) ON DELETE CASCADE
);
CREATE INDEX i_g_user_session_scheme_last_login ON g_user_session_scheme(guss_last_login);
CREATE INDEX i_g_user_session_scheme_expiration ON g_user_session_scheme(guss_expiration);
CREATE TABLE g_scope (
gs_id INT(11) PRIMARY KEY AUTO_INCREMENT,
gs_name VARCHAR(128) NOT NULL UNIQUE,
gs_display_name VARCHAR(256) DEFAULT '',
gs_description VARCHAR(512),
gs_password_required TINYINT(1) DEFAULT 1,
gs_password_max_age INT(11) DEFAULT 0,
gs_enabled TINYINT(1) DEFAULT 1
);
CREATE TABLE g_scope_group (
gsg_id INT(11) PRIMARY KEY AUTO_INCREMENT,
gs_id INT(11),
gsg_name VARCHAR(128) NOT NULL,
gsg_scheme_required INT(11) DEFAULT 1,
FOREIGN KEY(gs_id) REFERENCES g_scope(gs_id) ON DELETE CASCADE
);
CREATE TABLE g_scope_group_auth_scheme_module_instance (
gsgasmi_id INT(11) PRIMARY KEY AUTO_INCREMENT,
gsg_id INT(11) NOT NULL,
guasmi_id INT(11) NOT NULL,
FOREIGN KEY(gsg_id) REFERENCES g_scope_group(gsg_id) ON DELETE CASCADE,
FOREIGN KEY(guasmi_id) REFERENCES g_user_auth_scheme_module_instance(guasmi_id) ON DELETE CASCADE
);
CREATE TABLE g_client_user_scope (
gcus_id INT(11) PRIMARY KEY AUTO_INCREMENT,
gs_id INT(11) NOT NULL,
gcus_username VARCHAR(256) NOT NULL,
gcus_client_id VARCHAR(256) NOT NULL,
gcus_granted TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
gcus_enabled TINYINT(1) DEFAULT 1,
FOREIGN KEY(gs_id) REFERENCES g_scope(gs_id) ON DELETE CASCADE
);
CREATE INDEX i_g_client_user_scope_username ON g_client_user_scope(gcus_username);
CREATE INDEX i_g_client_user_scope_client_id ON g_client_user_scope(gcus_client_id);
CREATE TABLE g_api_key (
gak_id INT(11) PRIMARY KEY AUTO_INCREMENT,
gak_token_hash VARCHAR(512) NOT NULL,
gak_counter INT(11) DEFAULT 0,
gak_username VARCHAR(256) NOT NULL,
gak_issued_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
gak_issued_for VARCHAR(256), -- IP address or hostname
gak_user_agent VARCHAR(256),
gak_enabled TINYINT(1) DEFAULT 1
);
CREATE INDEX i_gak_token_hash ON g_api_key(gak_token_hash);
CREATE TABLE g_misc_config (
gmc_id INT(11) PRIMARY KEY AUTO_INCREMENT,
gmc_type VARCHAR(128) NOT NULL,
gmc_name VARCHAR(128),
gmc_value MEDIUMBLOB
);
CREATE INDEX i_gmc_type ON g_misc_config(gmc_type);
CREATE INDEX i_gmc_name ON g_misc_config(gmc_name);
|
