storage.sql
3.0 KB
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
DROP DATABASE IF EXISTS `myproject`;
CREATE DATABASE IF NOT EXISTS `myproject`;
USE `myproject`;
DROP TABLE IF EXISTS `app_user`;
CREATE TABLE IF NOT EXISTS `app_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`account_expired` bit(1) NOT NULL,
`account_locked` bit(1) NOT NULL,
`credentials_expired` bit(1) NOT NULL,
`email` varchar(255) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
`account_enabled` bit(1) DEFAULT NULL,
`first_name` varchar(50) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
`last_name` varchar(50) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
`password` varchar(255) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
`password_hint` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
`phone_number` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
`username` varchar(50) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
`version` int(11) DEFAULT NULL,
`language` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
`role_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_ltbhj43xjmmebuwsfny9c5fqw` (`role_id`),
CONSTRAINT `FK_ltbhj43xjmmebuwsfny9c5fqw` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
DELETE FROM `app_user`;
INSERT INTO `app_user` (`id`, `account_expired`, `account_locked`, `credentials_expired`, `email`, `account_enabled`, `first_name`, `last_name`, `password`, `password_hint`, `phone_number`, `username`, `version`, `language`, `role_id`) VALUES
(-3, b'0', b'0', b'0', 'bbb@bbb.org', b'1', 'Two Roles', 'User', '$2a$10$zbjMCzzy1NZRfdz35BffK.HuBXCVCA15KKpVOzzWButVuOFGKjc9G', 'Not a female kitty.', '', 'two_roles_user', 2, 'en', -1),
(-2, b'0', b'0', b'0', 'xxx@xxx.com', b'1', 'xxx', 'xxx', '$2a$10$rbE5NqXfyaqdY4Q3BbxBtOc3RY9gUOFol0YlD/IYai1gMdHT7sEUq', 'Not a female kitty...', '123', 'admin', 7, 'zh-CN', -1),
(7, b'0', b'0', b'0', 'sunky045@163.com', b'1', 'Sun', 'Ke', '$2a$10$8U9jAXVhVjIh32CwvCnRReJzcMceJoF4tVMOR6aAZzKIVXD9nuyx6', 'abc', '123', 'sunke', 10, 'zh-CN', -1),
DROP TABLE IF EXISTS `role`;
CREATE TABLE IF NOT EXISTS `role` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`description` varchar(64) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`permissions` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
DELETE FROM `role`;
INSERT INTO `role` (`id`, `description`, `name`, `permissions`) VALUES
(-2, 'Default role for all Users', 'ROLE_USER', '8,10'),
(-1, 'Administrator role (can edit Users)', 'ROLE_ADMIN', '4,5'),
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE IF NOT EXISTS `user_role` (
`user_id` bigint(20) NOT NULL,
`role_id` bigint(20) NOT NULL,
PRIMARY KEY (`user_id`,`role_id`),
KEY `FK_it77eq964jhfqtu54081ebtio` (`role_id`),
CONSTRAINT `FK_apcc8lxk2xnug8377fatvbn04` FOREIGN KEY (`user_id`) REFERENCES `app_user` (`id`),
CONSTRAINT `FK_it77eq964jhfqtu54081ebtio` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DELETE FROM `user_role`;
INSERT INTO `user_role` (`user_id`, `role_id`) VALUES
(-3, -2),
(-3, -1),
(-2, -1);