migration.sql · javaSpring/ezlink

Fetch the repository succeeded.

Scan WeChat QR to Pay

Cancel

Complete

richgiteeai

Watch

Unwatch Watching Releases Only Ignoring

1 Star 0 Fork 4

javaSpring/ezlink

forked from 小牛肉/ezlink

Code Issues 0 Pull Requests 0 Wiki Insights Pipelines

Service

Tencent Cloud Serverless

Create your Gitee Account

Explore and code with more than 14 million developers,Free private repositories !:)

Already have an account? Sign in

文件

master

Branches (1)

Manage

master

This repository doesn't specify license. Please pay attention to the specific project description and its upstream code dependency when using it.

The license selected for the repository is subject to the license used by the main branch of the repository.

Clone or Download

Clone/Download

HTTPS SSH SVN SVN+SSH Download ZIP

Prompt

To download the code, please copy the following command and execute it in the terminal

To ensure that your submitted code identity is correctly recognized by Gitee, please execute the following command.

git config --global user.name userName 
git config --global user.email userEmail

When using the SSH protocol for the first time to clone or push code, follow the prompts below to complete the SSH configuration.

1 Generate RSA keys.

2 Obtain the content of the RSA public key and configure it in SSH Public Keys

To use SVN on Gitee, please visit the usage guide

When using the HTTPS protocol, the command line will prompt for account and password verification as follows. For security reasons, Gitee recommends configure and use personal access tokens instead of login passwords for cloning, pushing, and other operations.

Username for 'https://gitee.com': userName

Password for 'https://userName@gitee.com': # Private Token

Branches 1

Tags 0

ezlink

migration.sql

migration.sql 7.83 KB

DROP
DATABASE `ezlink`;
CREATE
DATABASE `ezlink` CHARSET 'utf8mb4';

USE
`ezlink`;

CREATE TABLE `url_map`
(
 `id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
 `short_url` VARCHAR(100) NOT NULL COMMENT '短链URL',
 `long_url` VARCHAR(768) NOT NULL COMMENT '长链URL',
 `compression_code` VARCHAR(16) NOT NULL COMMENT '压缩码',
 `description` VARCHAR(256) COMMENT '描述',
 `url_status` TINYINT NOT NULL DEFAULT 1 COMMENT 'URL状态,1:正常,2:已失效',
 `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `edit_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 `creator` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '创建者',
 `editor` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '更新者',
 `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '软删除标识',
 `version` BIGINT NOT NULL DEFAULT 1 COMMENT '版本号',
 UNIQUE uniq_compression_code (`compression_code`),
 INDEX idx_short_url (`short_url`)
) COMMENT 'URL映射表';

CREATE TABLE `domain_conf`
(
 `id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
 `domain_value` VARCHAR(16) NOT NULL COMMENT '域名',
 `protocol` VARCHAR(8) NOT NULL DEFAULT 'https' COMMENT '协议,https或者http',
 `domain_status` TINYINT NOT NULL DEFAULT 1 COMMENT '域名状态,1:正常,2:已失效',
 `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `edit_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 `creator` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '创建者',
 `editor` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '更新者',
 `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '软删除标识',
 `version` BIGINT NOT NULL DEFAULT 1 COMMENT '版本号',
 UNIQUE uniq_domain (`domain_value`)
) COMMENT '域名配置';

CREATE TABLE `compression_code`
(
 `id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
 `compression_code` VARCHAR(16) NOT NULL COMMENT '62进制的压缩码',
 `code_status` TINYINT NOT NULL DEFAULT 1 COMMENT '压缩码状态,1:未使用,2:已使用,3:已失效',
 `sequence_value` VARCHAR(64) NOT NULL COMMENT '10进制的压缩码',
 `strategy` VARCHAR(8) NOT NULL COMMENT '策略,hash或者id(ID生成器)',
 `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `edit_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 `creator` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '创建者',
 `editor` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '更新者',
 `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '软删除标识',
 `version` BIGINT NOT NULL DEFAULT 1 COMMENT '版本号',
 UNIQUE uniq_compression_code (`compression_code`)
) COMMENT '压缩码';

CREATE TABLE `visit_statistics`
(
 `id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
 `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `edit_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 `creator` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '创建者',
 `editor` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '更新者',
 `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '软删除标识',
 `version` BIGINT NOT NULL DEFAULT 1 COMMENT '版本号',
 `statistics_date` DATE NOT NULL DEFAULT '1970年01月01日' COMMENT '统计日期',
 `pv_count` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '页面流量数',
 `uv_count` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '独立访客数',
 `ip_count` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '独立IP数',
 `effective_redirection_count` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '有效跳转数',
 `ineffective_redirection_count` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '无效跳转数',
 `compression_code` VARCHAR(16) NOT NULL COMMENT '压缩码',
 `short_url` VARCHAR(100) NOT NULL COMMENT '短链URL',
 `long_url` VARCHAR(768) NOT NULL COMMENT '长链URL',
 UNIQUE uniq_date_code (`statistics_date`, `compression_code`)
) COMMENT '短链访问数据统计';

CREATE TABLE `transform_event_record`
(
 `id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
 `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `edit_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 `creator` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '创建者',
 `editor` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '更新者',
 `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '软删除标识',
 `version` BIGINT NOT NULL DEFAULT 1 COMMENT '版本号',
 `unique_identity` VARCHAR(128) NOT NULL COMMENT '唯一身份标识,SHA-1(客户端IP-UA)',
 `client_ip` VARCHAR(64) NOT NULL COMMENT '客户端IP',
 `short_url` VARCHAR(32) NOT NULL COMMENT '短链URL',
 `long_url` VARCHAR(768) NOT NULL COMMENT '长链URL',
 `compression_code` VARCHAR(16) NOT NULL COMMENT '压缩码',
 `record_time` DATETIME NOT NULL COMMENT '记录时间戳',
 `user_agent` VARCHAR(2048) COMMENT 'UA',
 `cookie_value` VARCHAR(2048) COMMENT 'cookie',
 `query_param` VARCHAR(2048) COMMENT 'URL参数',
 `province` VARCHAR(32) COMMENT '省份',
 `city` VARCHAR(32) COMMENT '城市',
 `phone_type` VARCHAR(64) COMMENT '手机型号',
 `browser_type` VARCHAR(64) COMMENT '浏览器类型',
 `browser_version` VARCHAR(128) COMMENT '浏览器版本号',
 `os_type` VARCHAR(32) COMMENT '操作系统型号',
 `device_type` VARCHAR(32) COMMENT '设备型号',
 `os_version` VARCHAR(32) COMMENT '操作系统版本号',
 `transform_status` TINYINT NOT NULL DEFAULT 0 COMMENT '转换状态,1:转换成功,2:转换失败,3:重定向成功,4:重定向失败',
 INDEX idx_record_time (`record_time`),
 INDEX idx_compression_code (`compression_code`),
 INDEX idx_unique_identity (`unique_identity`)
) COMMENT '转换事件记录';

CREATE TABLE `access_token`
(
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `app_id` char(100) NOT NULL,
 `app_secret` varchar(1000) NOT NULL,
 `access_token` varchar(1000) DEFAULT NULL,
 `access_token_expire_timestamp` datetime DEFAULT NULL,
 `old_access_token` varchar(1000) DEFAULT NULL,
 `old_access_token_expire_timestamp` datetime DEFAULT NULL,
 `available_count` int(11) NOT NULL COMMENT '该 appId 剩余可用次数',
 `datachange_createtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `datachange_lasttime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `udx_app_id` (`app_id`) USING BTREE
) COMMENT 'AccessToken';

Copy Edit Raw Blame History

小牛肉 authored 2024年05月24日 14:44 +08:00 . update migration.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

DROP
DATABASE `ezlink`;
CREATE
DATABASE `ezlink` CHARSET 'utf8mb4';

USE
`ezlink`;

CREATE TABLE `url_map`
(
 `id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
 `short_url` VARCHAR(100) NOT NULL COMMENT '短链URL',
 `long_url` VARCHAR(768) NOT NULL COMMENT '长链URL',
 `compression_code` VARCHAR(16) NOT NULL COMMENT '压缩码',
 `description` VARCHAR(256) COMMENT '描述',
 `url_status` TINYINT NOT NULL DEFAULT 1 COMMENT 'URL状态,1:正常,2:已失效',
 `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `edit_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 `creator` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '创建者',
 `editor` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '更新者',
 `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '软删除标识',
 `version` BIGINT NOT NULL DEFAULT 1 COMMENT '版本号',
 UNIQUE uniq_compression_code (`compression_code`),
 INDEX idx_short_url (`short_url`)
) COMMENT 'URL映射表';

CREATE TABLE `domain_conf`
(
 `id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
 `domain_value` VARCHAR(16) NOT NULL COMMENT '域名',
 `protocol` VARCHAR(8) NOT NULL DEFAULT 'https' COMMENT '协议,https或者http',
 `domain_status` TINYINT NOT NULL DEFAULT 1 COMMENT '域名状态,1:正常,2:已失效',
 `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `edit_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 `creator` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '创建者',
 `editor` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '更新者',
 `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '软删除标识',
 `version` BIGINT NOT NULL DEFAULT 1 COMMENT '版本号',
 UNIQUE uniq_domain (`domain_value`)
) COMMENT '域名配置';

CREATE TABLE `compression_code`
(
 `id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
 `compression_code` VARCHAR(16) NOT NULL COMMENT '62进制的压缩码',
 `code_status` TINYINT NOT NULL DEFAULT 1 COMMENT '压缩码状态,1:未使用,2:已使用,3:已失效',
 `sequence_value` VARCHAR(64) NOT NULL COMMENT '10进制的压缩码',
 `strategy` VARCHAR(8) NOT NULL COMMENT '策略,hash或者id(ID生成器)',
 `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `edit_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 `creator` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '创建者',
 `editor` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '更新者',
 `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '软删除标识',
 `version` BIGINT NOT NULL DEFAULT 1 COMMENT '版本号',
 UNIQUE uniq_compression_code (`compression_code`)
) COMMENT '压缩码';

CREATE TABLE `visit_statistics`
(
 `id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
 `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `edit_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 `creator` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '创建者',
 `editor` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '更新者',
 `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '软删除标识',
 `version` BIGINT NOT NULL DEFAULT 1 COMMENT '版本号',
 `statistics_date` DATE NOT NULL DEFAULT '1970-01-01' COMMENT '统计日期',
 `pv_count` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '页面流量数',
 `uv_count` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '独立访客数',
 `ip_count` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '独立IP数',
 `effective_redirection_count` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '有效跳转数',
 `ineffective_redirection_count` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '无效跳转数',
 `compression_code` VARCHAR(16) NOT NULL COMMENT '压缩码',
 `short_url` VARCHAR(100) NOT NULL COMMENT '短链URL',
 `long_url` VARCHAR(768) NOT NULL COMMENT '长链URL',
 UNIQUE uniq_date_code (`statistics_date`, `compression_code`)
) COMMENT '短链访问数据统计';

CREATE TABLE `transform_event_record`
(
 `id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
 `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `edit_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 `creator` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '创建者',
 `editor` VARCHAR(32) NOT NULL DEFAULT 'admin' COMMENT '更新者',
 `deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '软删除标识',
 `version` BIGINT NOT NULL DEFAULT 1 COMMENT '版本号',
 `unique_identity` VARCHAR(128) NOT NULL COMMENT '唯一身份标识,SHA-1(客户端IP-UA)',
 `client_ip` VARCHAR(64) NOT NULL COMMENT '客户端IP',
 `short_url` VARCHAR(32) NOT NULL COMMENT '短链URL',
 `long_url` VARCHAR(768) NOT NULL COMMENT '长链URL',
 `compression_code` VARCHAR(16) NOT NULL COMMENT '压缩码',
 `record_time` DATETIME NOT NULL COMMENT '记录时间戳',
 `user_agent` VARCHAR(2048) COMMENT 'UA',
 `cookie_value` VARCHAR(2048) COMMENT 'cookie',
 `query_param` VARCHAR(2048) COMMENT 'URL参数',
 `province` VARCHAR(32) COMMENT '省份',
 `city` VARCHAR(32) COMMENT '城市',
 `phone_type` VARCHAR(64) COMMENT '手机型号',
 `browser_type` VARCHAR(64) COMMENT '浏览器类型',
 `browser_version` VARCHAR(128) COMMENT '浏览器版本号',
 `os_type` VARCHAR(32) COMMENT '操作系统型号',
 `device_type` VARCHAR(32) COMMENT '设备型号',
 `os_version` VARCHAR(32) COMMENT '操作系统版本号',
 `transform_status` TINYINT NOT NULL DEFAULT 0 COMMENT '转换状态,1:转换成功,2:转换失败,3:重定向成功,4:重定向失败',
 INDEX idx_record_time (`record_time`),
 INDEX idx_compression_code (`compression_code`),
 INDEX idx_unique_identity (`unique_identity`)
) COMMENT '转换事件记录';


CREATE TABLE `access_token`
(
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `app_id` char(100) NOT NULL,
 `app_secret` varchar(1000) NOT NULL,
 `access_token` varchar(1000) DEFAULT NULL,
 `access_token_expire_timestamp` datetime DEFAULT NULL,
 `old_access_token` varchar(1000) DEFAULT NULL,
 `old_access_token_expire_timestamp` datetime DEFAULT NULL,
 `available_count` int(11) NOT NULL COMMENT '该 appId 剩余可用次数',
 `datachange_createtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `datachange_lasttime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `udx_app_id` (`app_id`) USING BTREE
) COMMENT 'AccessToken';