-
Notifications
You must be signed in to change notification settings - Fork 8
/
test_data.sql
165 lines (148 loc) · 5.52 KB
/
test_data.sql
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
164
165
GRANT ALL PRIVILEGES ON ninja.* TO 'ninja'@'%' IDENTIFIED BY 'ninja';
GRANT ALL PRIVILEGES ON douitsu.* TO 'douitsu'@'%' IDENTIFIED BY 'douitsu';
FLUSH PRIVILEGES;
CREATE DATABASE IF NOT EXISTS `douitsu`;
USE `douitsu`
CREATE TABLE sys_entity (
id varchar(255) NOT NULL,
zone varchar(255) DEFAULT NULL,
base varchar(255) DEFAULT NULL,
name varchar(255) DEFAULT NULL,
fields varchar(4000) DEFAULT NULL,
seneca varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE sys_settings (
id varchar(255) NOT NULL,
kind varchar(255) DEFAULT NULL,
spec varchar(255) DEFAULT NULL,
ref varchar(255) DEFAULT NULL,
settings varchar(255) DEFAULT NULL,
data varchar(4000) DEFAULT NULL,
seneca varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE sys_user (
id varchar(255) NOT NULL,
nick varchar(255) DEFAULT NULL,
name varchar(255) DEFAULT NULL,
email varchar(255) DEFAULT NULL,
active tinyint(1) DEFAULT NULL,
created datetime DEFAULT NULL,
updated datetime DEFAULT NULL,
confirmed tinyint(1) DEFAULT NULL,
confirmcode varchar(255) DEFAULT NULL,
admin tinyint(1) DEFAULT NULL,
salt varchar(255) DEFAULT NULL,
pass varchar(255) DEFAULT NULL,
image varchar(255) DEFAULT NULL,
accounts varchar(4000) DEFAULT NULL,
seneca varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE sys_reset (
id varchar(255) NOT NULL,
active tinyint(1) DEFAULT NULL,
nick varchar(255) DEFAULT NULL,
user varchar(255) DEFAULT NULL,
`when` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE sys_login (
id varchar(255) NOT NULL,
nick varchar(255) DEFAULT NULL,
email varchar(255) DEFAULT NULL,
user varchar(255) DEFAULT NULL,
active tinyint(1) DEFAULT NULL,
auto tinyint(1) DEFAULT NULL,
`when` datetime DEFAULT NULL,
why varchar(255) DEFAULT NULL,
token varchar(255) DEFAULT NULL,
context varchar(255) DEFAULT NULL,
ended timestamp NOT NULL,
seneca varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE sys_account (
id varchar(255) NOT NULL,
name varchar(255) DEFAULT NULL,
orignick varchar(255) DEFAULT NULL,
origuser varchar(255) DEFAULT NULL,
active tinyint(1) DEFAULT NULL,
users varchar(4000) DEFAULT NULL,
projects varchar(4000) DEFAULT NULL,
seneca varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE application (
id varchar(255) NOT NULL,
account varchar(255) DEFAULT NULL,
name varchar(255) DEFAULT NULL,
appid varchar(255) DEFAULT NULL,
secret varchar(255) DEFAULT NULL,
homeurl varchar(255) DEFAULT NULL,
callback varchar(255) DEFAULT NULL,
`desc` varchar(255) DEFAULT NULL,
image varchar(255) DEFAULT NULL,
active tinyint(1) DEFAULT NULL,
is_ninja_official tinyint(1) NOT NULL DEFAULT 0,
client_type enum('confidential', 'public') NOT NULL DEFAULT 'confidential',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE authcode (
id varchar(255) NOT NULL,
code varchar(255) NOT NULL,
clientID varchar(255) NOT NULL,
redirectURI varchar(255) NOT NULL,
userID varchar(255) NOT NULL,
scope varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE accesstoken (
id varchar(255) NOT NULL,
userID varchar(255) NOT NULL,
clientID varchar(255) NOT NULL,
clientName varchar(255) NOT NULL,
type varchar(255) NOT NULL DEFAULT 'application',
`mqtt_client_id` int(20) NOT NULL AUTO_INCREMENT UNIQUE,
`node_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE accesstoken_scope (
id varchar(255) NOT NULL,
accesstoken varchar(255) NOT NULL,
scope_domain varchar(255) NOT NULL,
scope_item varchar(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY `scope_specific` (accesstoken, scope_domain, scope_item),
FOREIGN KEY (accesstoken) REFERENCES accesstoken (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE DATABASE IF NOT EXISTS `ninja`;
USE `ninja`;
CREATE TABLE `nodes` (
-- `mqtt_client_id` int(20) NOT NULL AUTO_INCREMENT UNIQUE,
`user_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`node_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`site_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`hardware_type` varchar(64) NOT NULL,
-- `token` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`mqtt_client_id` int(20) not null auto_increment,
PRIMARY KEY (`user_id`, `node_id`),
UNIQUE KEY `mqtt_client_id_key` (`mqtt_client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `users` (
`user_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`name` varchar(128) NOT NULL,
`email` varchar(128) NOT NULL,
`lastAccessToken` varchar(128) NULL,
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`sphere_network_key` varchar(64) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `sites` (
`user_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`site_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`master_node_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NULL,
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`, `site_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;