Skip to content

GitLab

db · Changes

Page history
xiaolong.jin created page: db authored by 金小龙's avatar 金小龙
Hide whitespace changes
Inline Side-by-side
Showing with 207 additions and 3 deletions
db.md
View page @ 41c40eba
......@@ -21,9 +21,7 @@ INSERT INTO personal_credit.api (name, cn_name, `desc`, url, status, create_time
INSERT INTO personal_credit.api (name, cn_name, `desc`, url, status, create_time, last_upadte) VALUES ('phoneinfo', '电话号码信息', '电话号码相关查询', 'https://i.trustutn.org/b/phoneinfo', 1, 0, '2016-09-01 06:25:41');
INSERT INTO personal_credit.api (name, cn_name, `desc`, url, status, create_time, last_upadte) VALUES ('phonetag', '电话号码标记', '电话号码标记查询', 'https://i.trustutn.org/phonetag', 1, 0, '2016-09-01 06:25:42');
INSERT INTO personal_credit.api (name, cn_name, `desc`, url, status, create_time, last_upadte) VALUES ('phoneMatch', '设备号匹配', '手机号设备号匹配查询', 'https://i.trustutn.org/b/phoneMatch', 1, 0, '2016-09-01 06:25:45');
```
```
CREATE TABLE `api_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
......@@ -44,4 +42,210 @@ CREATE TABLE `api_log` (
KEY `idx_uast` (`user_id`,`api_id`,`status`,`time_index`),
KEY `idx_tuas` (`time_index`,`user_id`,`api_id`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=280043 DEFAULT CHARSET=utf8 COMMENT='接口调用日志表';
CREATE TABLE `api_log_statistics` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
`api_id` int(11) DEFAULT '0' COMMENT '接口id,0则为汇总数据',
`data_key` varchar(100) NOT NULL DEFAULT '' COMMENT '键',
`time_type` enum('DAY','WEEK','MONTH','YEAR','TOTAL') DEFAULT 'DAY' COMMENT '时间维度的类别',
`time_index` int(11) NOT NULL DEFAULT '0' COMMENT '时间维度,0则为汇总数据',
`data_value` int(11) NOT NULL DEFAULT '0' COMMENT '值',
`create_time` int(11) NOT NULL DEFAULT '0',
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_utd` (`user_id`,`api_id`,`data_key`,`time_type`,`time_index`)
) ENGINE=InnoDB AUTO_INCREMENT=13257 DEFAULT CHARSET=utf8;
```
```
CREATE TABLE `base_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) NOT NULL DEFAULT '0',
`info` varchar(50) NOT NULL DEFAULT '',
`code` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (0, '查询类型', 0);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (0, '证件类型', 0);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (1, '基本查询', 0);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (1, '基本查询加地址', 1);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (1, '基本查询加照片', 2);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (1, '全部信息', 3);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '居民身份证', 111);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '军官证', 114);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '外国人临时居留证', 555);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '其他证件', 990);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '法人证件', 992);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '组织机构代码', 991);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '营业执照', 889);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '临时居民身份证', 112);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '暂住证', 116);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '学生证', 133);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '机动车驾驶证', 335);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '外交护照', 411);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '公务护照', 412);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '因公普通护照', 413);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '普通护照', 414);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '旅行证', 415);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '入出境通行证', 416);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '外国人出入境证', 417);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '外国人旅行证', 418);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '香港特别行政区护照', 420);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '澳门特别行政区护照', 421);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '台湾居民来往大陆通行证', 511);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '往来港澳通行证', 513);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '来往港澳通行证', 515);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '港澳同胞回乡证(通行卡)', 516);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '大陆居民往来台湾通行证', 517);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '因公往来香港澳门特别行政区通行证', 518);
INSERT INTO personal_credit.base_info (pid, info, code) VALUES (2, '边境管理区通行证', 711);
```
```
CREATE TABLE `notice` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL DEFAULT '0' COMMENT '被通知人,0则为全体通知',
`type` varchar(10) NOT NULL DEFAULT '' COMMENT '通知类型',
`title` varchar(100) NOT NULL DEFAULT '' COMMENT '标题',
`content` varchar(500) NOT NULL DEFAULT '' COMMENT '正文',
`create_time` int(11) NOT NULL DEFAULT '0',
`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态',
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_u` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=87 DEFAULT CHARSET=utf8;
CREATE TABLE `notice_template` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`type` varchar(10) NOT NULL DEFAULT '' COMMENT '通知类型',
`title` varchar(100) NOT NULL DEFAULT '' COMMENT '标题',
`content` varchar(500) NOT NULL DEFAULT '' COMMENT '内容, ? 为占位符,可动态替换',
`create_time` int(11) NOT NULL DEFAULT '0',
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='通知模板';
```
```
CREATE TABLE `support` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL DEFAULT '' COMMENT '标题',
`content` text NOT NULL COMMENT '正文',
`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态',
`helpful` mediumint(9) NOT NULL DEFAULT '0' COMMENT '是否有帮助',
`create_time` int(11) NOT NULL DEFAULT '0',
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8;
```
```
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(100) NOT NULL DEFAULT '' COMMENT '用户名',
`password` varchar(64) NOT NULL DEFAULT '' COMMENT '密码',
`salt` varchar(10) NOT NULL DEFAULT '',
`company` varchar(255) DEFAULT '' COMMENT '公司名',
`fchar` char(1) NOT NULL DEFAULT '' COMMENT '公司首字母',
`pname` varchar(20) NOT NULL DEFAULT '' COMMENT 'pname用于接口安全校验',
`pkey` char(32) NOT NULL DEFAULT '' COMMENT 'pkey用于接口安全校验',
`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '账号状态',
`create_time` int(11) NOT NULL DEFAULT '0',
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_u` (`username`),
KEY `idx_up` (`username`,`password`)
) ENGINE=InnoDB AUTO_INCREMENT=210 DEFAULT CHARSET=utf8;
CREATE TABLE `user_statistics` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`time_type` enum('week','month','day') DEFAULT 'week' COMMENT '时间纬度的类别',
`time_index` int(11) NOT NULL DEFAULT '0' COMMENT '时间纬度,0则为汇总数据',
`data_key` varchar(50) NOT NULL DEFAULT '' COMMENT '键',
`data_value` int(11) NOT NULL DEFAULT '0' COMMENT '值',
`create_time` int(11) NOT NULL DEFAULT '0',
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_tkv` (`time_index`,`time_type`,`data_key`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8;
```
```
CREATE TABLE `user_account` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
`account_balance` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '账户余额',
`accumulated_amount` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '累计消费金额',
`create_time` int(11) NOT NULL DEFAULT '0',
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_ut` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=146 DEFAULT CHARSET=utf8;
CREATE TABLE `user_account_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
`type` varchar(20) NOT NULL DEFAULT 'consumption' COMMENT '消费consumption 赠送free 充值recharge',
`money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '金额',
`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态',
`create_time` int(11) NOT NULL DEFAULT '0',
`time_index` int(11) NOT NULL DEFAULT '0' COMMENT '时间维度',
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_ut` (`user_id`),
KEY `idx_tut` (`user_id`,`type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6712 DEFAULT CHARSET=utf8;
CREATE TABLE `user_account_log_statistics` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
`time_index` int(11) NOT NULL DEFAULT '0' COMMENT '时间维度',
`data_key` varchar(100) NOT NULL DEFAULT '' COMMENT '键',
`data_value` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '值',
`create_time` int(11) NOT NULL DEFAULT '0',
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_utd` (`user_id`,`data_key`,`time_index`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8;
```
```
CREATE TABLE `user_api` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
`api_id` int(11) NOT NULL DEFAULT '0' COMMENT '接口id',
`price` decimal(6,2) NOT NULL DEFAULT '0.00',
`rule` enum('hit','call') NOT NULL DEFAULT 'call' COMMENT 'hit命中,call调用',
`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态',
`create_time` int(11) NOT NULL DEFAULT '0',
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_ua` (`user_id`,`api_id`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8 COMMENT='用户接口服务';
CREATE TABLE `user_api_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
`api_id` int(11) NOT NULL DEFAULT '0' COMMENT '接口id',
`price` decimal(6,2) NOT NULL DEFAULT '0.00' COMMENT '价格',
`rule` enum('hit','call') NOT NULL DEFAULT 'call' COMMENT '计费规则',
`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态',
`create_time` int(11) NOT NULL DEFAULT '0',
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
```
```
CREATE TABLE `user_ip_whitelist` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
`ip` bigint(20) NOT NULL DEFAULT '0' COMMENT 'ip地址,用 INET_ATON() 和 INET_NTOA() 函数互转',
`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态',
`create_time` int(11) NOT NULL DEFAULT '0',
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_ui` (`user_id`,`ip`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8 COMMENT='用户ip白名单';
```
\ No newline at end of file