# learn-simple-plaform

**Repository Path**: xiaoxiaorge/learn-simple-plaform

## Basic Information

- **Project Name**: learn-simple-plaform
- **Description**: No description available
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2021-03-30
- **Last Updated**: 2021-04-19

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

#数据库信息：
##广告库(edu_ad)
###ddl:
create schema if not exists edu_ad collate latin1_swedish_ci;

create table if not exists promotion_ad
(
	id int auto_increment
		primary key,
	name varchar(255) null comment '广告名',
	spaceId int null comment '广告位id',
	keyword varchar(255) null comment '精确搜索关键词',
	htmlContent text null comment '静态广告的内容',
	text varchar(255) null comment '文字',
	link varchar(255) null comment '链接',
	startTime datetime null comment '开始时间',
	endTime datetime null comment '结束时间',
	createTime datetime null,
	updateTime datetime null,
	status int(2) default 0 not null,
	priority int(4) default 0 null comment '优先级',
	img varchar(255) null
)
charset=utf8;

create index promotion_ad_SEG
	on promotion_ad (spaceId, startTime, endTime, status);

create table if not exists promotion_space
(
	id int auto_increment
		primary key,
	name varchar(255) null comment '名称',
	spaceKey varchar(255) null comment '广告位key',
	createTime datetime null,
	updateTime datetime null,
	isDel int(2) default 0 null
)
charset=utf8;

create index promotion_space_key_isDel
	on promotion_space (spaceKey, isDel);

###dml:
INSERT INTO edu_ad.promotion_ad (id, name, spaceId, keyword, htmlContent, text, link, startTime, endTime, createTime, updateTime, status, priority, img) VALUES (1090, '高薪训练营', 172, '', null, null, null, '2021-03-26 08:53:44', '2021-03-28 08:53:48', null, null, 1, 0, null);
INSERT INTO edu_ad.promotion_ad (id, name, spaceId, keyword, htmlContent, text, link, startTime, endTime, createTime, updateTime, status, priority, img) VALUES (1091, '顶部广告名称', 172, null, null, null, null, '2020-01-01 00:00:00', '2020-03-01 00:00:00', '2021-03-29 14:52:04', '2021-03-29 14:52:58', 1, 0, null);

INSERT INTO edu_ad.promotion_space (id, name, spaceKey, createTime, updateTime, isDel) VALUES (172, '顶部广告位', 'top', '2021-03-27 05:13:01', '2021-03-27 05:13:04', 0);
INSERT INTO edu_ad.promotion_space (id, name, spaceKey, createTime, updateTime, isDel) VALUES (173, '右边栏广告位', 'right', '2021-03-27 09:56:49', '2021-03-29 14:38:22', 0);
INSERT INTO edu_ad.promotion_space (id, name, spaceKey, createTime, updateTime, isDel) VALUES (174, '左边栏广告位', 'left', '2021-03-27 09:58:23', '2021-03-27 10:04:09', 0);


##广告库(edu_user)
###ddl:
create schema if not exists edu_user collate latin1_swedish_ci;

create table if not exists roles
(
	id int auto_increment
		primary key,
	code varchar(100) null,
	description varchar(500) null,
	name varchar(200) null,
	create_time datetime null,
	update_time datetime null
);

create table if not exists user
(
	id int auto_increment comment '用户id'
		primary key,
	name varchar(255) not null comment '用户昵称',
	portrait varchar(255) null comment '用户头像地址',
	phone varchar(255) not null comment '注册手机',
	password varchar(255) null comment '用户密码（可以为空，支持只用验证码注册、登录）',
	reg_ip varchar(255) null comment '注册ip',
	account_non_expired bit default b'1' null comment '是否有效用户',
	credentials_non_expired bit default b'1' null comment '账号是否未过期',
	account_non_locked bit default b'1' null comment '是否未锁定',
	status varchar(20) default 'ENABLE' not null comment '用户状态：ENABLE能登录，DISABLE不能登录',
	is_del bit default b'0' not null comment '是否删除',
	create_time datetime not null comment '注册时间',
	update_time datetime not null comment '记录更新时间',
	constraint idx_phone_is_del
		unique (phone, is_del)
)
charset=utf8;

create table if not exists user_phone_verification_code
(
	id int(10) auto_increment
		primary key,
	phone varchar(15) default '' null comment '手机号',
	verification_code varchar(15) default '' null comment '验证码',
	create_time datetime null comment '创建时间',
	isCheck bit default b'0' null comment '验证码是否校验过',
	check_times int(2) default 0 null comment '校验次数'
)
charset=utf8;

create index l_phone_verification_code_ind_01
	on user_phone_verification_code (phone, create_time);

create table if not exists user_roles_relation
(
	id int auto_increment
		primary key,
	user_id int not null,
	roles_id int not null,
	create_time datetime null,
	update_time datetime null
);

create table if not exists user_weixin
(
	id int auto_increment
		primary key,
	user_id int null comment '用户id',
	union_id varchar(255) default '' not null comment '认证id,微信对应的时unionId',
	open_id varchar(255) null comment 'openId',
	nick_name varchar(255) not null comment '昵称',
	portrait varchar(512) null comment '头像',
	city varchar(255) null comment '城市',
	sex int null comment '性别, 1-男，2-女',
	create_time datetime not null comment '创建时间',
	update_time datetime not null comment '更新时间',
	is_del bit default b'0' not null comment '是否删除',
	constraint oauthId_and_oauthType_unique
		unique (union_id, open_id, is_del),
	constraint userId_and_oauthType_unique_index
		unique (user_id, open_id, is_del)
)
charset=utf8;

###dml:

##授权服务器库ddl（edu_oauth）:
create schema if not exists edu_oauth collate latin1_swedish_ci;
use edu_oauth;

DROP TABLE IF EXISTS `clientdetails`;
CREATE TABLE `clientdetails` (
                                 `appId` varchar(128) NOT NULL,
                                 `resourceIds` varchar(256) DEFAULT NULL,
                                 `appSecret` varchar(256) DEFAULT NULL,
                                 `scope` varchar(256) DEFAULT NULL,
                                 `grantTypes` varchar(256) DEFAULT NULL,
                                 `redirectUrl` varchar(256) DEFAULT NULL,
                                 `authorities` varchar(256) DEFAULT NULL,
                                 `access_token_validity` int(11) DEFAULT NULL,
                                 `refresh_token_validity` int(11) DEFAULT NULL,
                                 `additionalInformation` varchar(4096) DEFAULT NULL,
                                 `autoApproveScopes` varchar(256) DEFAULT NULL,
                                 PRIMARY KEY (`appId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;;

-- ----------------------------
-- Table structure for oauth_access_token
-- ----------------------------
DROP TABLE IF EXISTS `oauth_access_token`;
CREATE TABLE `oauth_access_token` (
                                      `token_id` varchar(256) DEFAULT NULL,
                                      `token` blob,
                                      `authentication_id` varchar(128) NOT NULL,
                                      `user_name` varchar(256) DEFAULT NULL,
                                      `client_id` varchar(256) DEFAULT NULL,
                                      `authentication` blob,
                                      `refresh_token` varchar(256) DEFAULT NULL,
                                      PRIMARY KEY (`authentication_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;;

-- ----------------------------
-- Table structure for oauth_approvals
-- ----------------------------
DROP TABLE IF EXISTS `oauth_approvals`;
CREATE TABLE `oauth_approvals` (
                                   `userId` varchar(256) DEFAULT NULL,
                                   `clientId` varchar(256) DEFAULT NULL,
                                   `scope` varchar(256) DEFAULT NULL,
                                   `status` varchar(10) DEFAULT NULL,
                                   `expiresAt` datetime DEFAULT NULL,
                                   `lastModifiedAt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;;

-- ----------------------------
-- Table structure for oauth_client_details
-- ----------------------------
DROP TABLE IF EXISTS `oauth_client_details`;
CREATE TABLE `oauth_client_details` (
                                        `client_id` varchar(128) NOT NULL,
                                        `resource_ids` varchar(256) DEFAULT NULL,
                                        `client_secret` varchar(256) DEFAULT NULL,
                                        `scope` varchar(256) DEFAULT NULL,
                                        `authorized_grant_types` varchar(256) DEFAULT NULL,
                                        `web_server_redirect_uri` varchar(256) DEFAULT NULL,
                                        `authorities` varchar(256) DEFAULT NULL,
                                        `access_token_validity` int(11) DEFAULT NULL,
                                        `refresh_token_validity` int(11) DEFAULT NULL,
                                        `additional_information` varchar(4096) DEFAULT NULL,
                                        `autoapprove` varchar(256) DEFAULT NULL,
                                        PRIMARY KEY (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;;

-- ----------------------------
-- Table structure for oauth_client_token
-- ----------------------------
DROP TABLE IF EXISTS `oauth_client_token`;
CREATE TABLE `oauth_client_token` (
                                      `token_id` varchar(256) DEFAULT NULL,
                                      `token` blob,
                                      `authentication_id` varchar(128) NOT NULL,
                                      `user_name` varchar(256) DEFAULT NULL,
                                      `client_id` varchar(256) DEFAULT NULL,
                                      PRIMARY KEY (`authentication_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;;

-- ----------------------------
-- Table structure for oauth_code
-- ----------------------------
DROP TABLE IF EXISTS `oauth_code`;
CREATE TABLE `oauth_code` (
                              `code` varchar(256) DEFAULT NULL,
                              `authentication` blob
) ENGINE=InnoDB DEFAULT CHARSET=utf8;;

-- ----------------------------
-- Table structure for oauth_refresh_token
-- ----------------------------
DROP TABLE IF EXISTS `oauth_refresh_token`;
CREATE TABLE `oauth_refresh_token` (
                                       `token_id` varchar(256) DEFAULT NULL,
                                       `token` blob,
                                       `authentication` blob
) ENGINE=InnoDB DEFAULT CHARSET=utf8;;

SET FOREIGN_KEY_CHECKS = 1;

###dml:
INSERT INTO edu_oauth.oauth_client_details (client_id, resource_ids, client_secret, scope, authorized_grant_types, web_server_redirect_uri, authorities, access_token_validity, refresh_token_validity, additional_information, autoapprove) VALUES ('user-service', '', '$2a$10$Fml3Viur17oQjVR/xFN1tes3/8HvpbaYIyeC77vjkde/VDk7rKewq', 'all', 'refresh_token,password', null, null, 20, 108000, null, null);


###公钥、私钥生成命令：
$ keytool -genkeypair -alias pri-jwt -validity 3650 -keyalg RSA -dname "CN=jwt,OU=jtw,O=jwt,L=zurich,S=zurich, C=CH" -keypass pri123 -keystore pri-jwt.jks -storepass pri123

keytool -list -rfc --keystore C:\Users\zhouhr\pri-jwt.jks | openssl x509 -inform pem -pubkey

-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAmVv9LtagoriRARQMortX
s3rUedYWi7yaeHZ0PTYwjhlSXG/2fPJv9zP7vTZI4Mc0xkj1T3VqQIzUuSRnPAht
J0lWRj8p4ByKoyN/s6UM5HDHt5sPvn5YkHrAui32uszKrNuaBSIU/kC6hfA4EY9N
gmBxj1juovzwDkIip+MrGRod4iFH34Pi9+1H1jQAWMvqKnvO9aUsXlZq7onhhER5
V5Ir7mrTuhnBmAet5nE4DWb/vHu6T7Zhjr71LIvHtP78n7fx7dOxBRSzt+egofgi
sgzkQiqmgIE+NOfE0WgUB+jLHWw7yCTDXBPplxOCnVIZ7vt1IPM0ZynGHsR7a0r7
TwIDAQAB
-----END PUBLIC KEY-----
-----BEGIN CERTIFICATE-----
MIIDUTCCAjmgAwIBAgIEYuKp7jANBgkqhkiG9w0BAQsFADBZMQswCQYDVQQGEwJD
SDEPMA0GA1UECBMGenVyaWNoMQ8wDQYDVQQHEwZ6dXJpY2gxDDAKBgNVBAoTA2p3
dDEMMAoGA1UECxMDanR3MQwwCgYDVQQDEwNqd3QwHhcNMjEwNDAxMDU0NDQzWhcN
MzEwMzMwMDU0NDQzWjBZMQswCQYDVQQGEwJDSDEPMA0GA1UECBMGenVyaWNoMQ8w
DQYDVQQHEwZ6dXJpY2gxDDAKBgNVBAoTA2p3dDEMMAoGA1UECxMDanR3MQwwCgYD
VQQDEwNqd3QwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQCZW/0u1qCi
uJEBFAyiu1ezetR51haLvJp4dnQ9NjCOGVJcb/Z88m/3M/u9NkjgxzTGSPVPdWpA
jNS5JGc8CG0nSVZGPyngHIqjI3+zpQzkcMe3mw++fliQesC6Lfa6zMqs25oFIhT+
QLqF8DgRj02CYHGPWO6i/PAOQiKn4ysZGh3iIUffg+L37UfWNABYy+oqe871pSxe
VmruieGERHlXkivuatO6GcGYB63mcTgNZv+8e7pPtmGOvvUsi8e0/vyft/Ht07EF
FLO356Ch+CKyDORCKqaAgT4058TRaBQH6MsdbDvIJMNcE+mXE4KdUhnu+3Ug8zRn
KcYexHtrSvtPAgMBAAGjITAfMB0GA1UdDgQWBBQMCauqCstGacyPhWtmUmDZL4J5
izANBgkqhkiG9w0BAQsFAAOCAQEAlaxUhh6TOT8WczDTzyfO+18XpwVmrt5ufw+G
xbqmf3kDemj3Q3p8JrXVPZgKB1b8AiBWo8bK/yA/dFNX++MOAQVkkdWHfMdw+qTz
HTQB7QrLuC1d1jnikOh/FPTMhxGJIiED+xQSr8pNnri4syYXA0HmIb19+6W87Bz3
4ESSmYo0Tc0SyMrh0/WWm8Niymyn/xzCenUUZYjnmCNJwP8Sj6YFEz3ZoHqQvFDf
Ku5q55NvxP7XuxELTOyp83McRHYaNziYSDrZOUbxoLLHwiwmwKDt2I0vIzF6/sTi
6QFLuI92XHFUXc4lSRsrvQrqLTZxRcPb1D4qRK9wGz9kViZhng==
-----END CERTIFICATE-----