CREATE TABLE `project_rule` ( `rule_id` bigint NOT NULL COMMENT '规则id，unique_key', `project_id` bigint NOT NULL COMMENT '项目id，unique_key', `rule_update_time` datetime NULL COMMENT '项目匹配规则的更新时间' ) ENGINE = OLAP UNIQUE KEY(`rule_id`, `project_id`) DISTRIBUTED BY HASH(`rule_id`) BUCKETS 10 PROPERTIES ( "replication_num" = "1" ); CREATE TABLE `rule` ( `id` bigint NOT NULL AUTO_INCREMENT (1) COMMENT '规则id，unique_key', `interval` bigint NOT NULL COMMENT '执行周期', `condition` string NOT NULL COMMENT '规则内容', `remark` string NULL COMMENT '备注信息', `active` boolean NULL COMMENT '激活状态', `delete` boolean NULL COMMENT '是否删除', `create_time` datetime NULL COMMENT '创建时间', `schedule_time` datetime NULL COMMENT '最近调度时间', `max_parallel` int NULL COMMENT '最大并行度' ) ENGINE = OLAP UNIQUE KEY(`id`) DISTRIBUTED BY HASH(`id`) BUCKETS 10 PROPERTIES ( "replication_num" = "1" ); CREATE TABLE `rule_executor` ( `id` bigint NOT NULL AUTO_INCREMENT (1) COMMENT '实例 id，unique_key', `rule_id` bigint NOT NULL COMMENT '规则ID', `start_time` datetime NOT NULL COMMENT '开始时间', `end_time` datetime NULL COMMENT '结束时间', `duration` BIGINT NOT NULL DEFAULT 0 COMMENT '实例执行时长', `target_num` BIGINT NOT NULL DEFAULT 0 COMMENT '命中规则项目数', `process_num` BIGINT NOT NULL DEFAULT 0 COMMENT '实例已处理的数据条数', `status` int NOT NULL COMMENT '实例状态, 0：运行中;1：正常结束;-1：异常结束' ) ENGINE = OLAP UNIQUE KEY(`id`) DISTRIBUTED BY HASH(`id`) BUCKETS 10 PROPERTIES ( "replication_num" = "1" ); CREATE TABLE `rule_execute_record` ( `rule_id` bigint NOT NULL COMMENT '规则ID', `executor_id` bigint NOT NULL COMMENT '执行器ID', `id` bigint NOT NULL AUTO_INCREMENT (1) COMMENT '执行记录片段id，unique_key', `start_time` DATETIME NOT NULL COMMENT '记录的开始时间', `end_time` DATETIME NULL COMMENT '记录的结束时间', `project_id_from` bigint NOT NULL COMMENT '规划的扫描project_id 起始值 ', `project_id_to` bigint NOT NULL COMMENT '规划的扫描project_id 终止值', `duration` BIGINT NOT NULL DEFAULT 0 COMMENT '实例执行时长', `target_num` BIGINT NOT NULL DEFAULT 0 COMMENT '命中规则项目数', `process_num` BIGINT NOT NULL DEFAULT 0 COMMENT '实例已处理的数据条数', `status` bigint NOT NULL COMMENT '记录的状态', ) ENGINE = OLAP UNIQUE KEY(`rule_id`,`executor_id`, `id`) DISTRIBUTED BY HASH(`id`) BUCKETS 10 PROPERTIES ( "replication_num" = "1" ); CREATE TABLE `important_project_crwal_info` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '任务ID，自增，unique_key', `project_id` bigint NOT NULL COMMENT '项目ID', `name` varchar(100) NULL COMMENT '项目名', `full_name` varchar(255) NULL COMMENT 'Github 平台内唯一的项目全称', `url` varchar(500) NULL COMMENT '项目链接', `commit_id` varchar(100) NULL COMMENT '上次采集的commit_id', `pt` datetime NULL COMMENT '上次采集的pt时间', `task_time` datetime NULL COMMENT '任务插入时间', INDEX full_name_inverted_index (`full_name`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true") ) ENGINE = OLAP UNIQUE KEY( `id`) DISTRIBUTED BY HASH(`id`) BUCKETS 10 PROPERTIES ( "replication_num" = "1" ); CREATE TABLE `suspected_tool` ( `id` bigint NOT NULL AUTO_INCREMENT (1) COMMENT '序号/ID', `tool_name` varchar(255) NULL COMMENT '工具名', `full_name` varchar(255) NULL COMMENT '首发项目全称', `url` varchar(1024) NULL COMMENT '工具链接', `version_number` varchar(100) NULL COMMENT '版本号(About信息)', `platform` tinyint NULL COMMENT '平台(1:Android,2:Hardware,3:IOS,4:JAVA,5:MAC,6:PC,7:VM,8:Linux)', `release_time` datetime NULL COMMENT '版本发布时间', `package_name` varchar(255) NULL COMMENT '包名', `tool_release_time` datetime NULL COMMENT '工具发布时间', `finder_id` varchar(255) NULL COMMENT 'Downloadurl来源', `download_url` varchar(1024) NULL COMMENT '样本下载链接', `remark` text NULL COMMENT '备注信息', `user_id` bigint NULL COMMENT '首发项目作者ID', `user_login` varchar(255) NULL COMMENT '首发项目作者登录名', `release` varchar(1024) NULL COMMENT 'Release链接', `ft` datetime NULL COMMENT '发现时间', `ss` tinyint NULL DEFAULT "0" COMMENT '发送状态(0:默认值未发送,1:发送成功,-1:未发送成功)', `st` datetime NULL COMMENT '发送时间', `sr` text NULL COMMENT '发送结果', `vr` tinyint NULL DEFAULT "0" COMMENT '验证结果(-1:验证不通过,0:默认值,1:验证通过为新增网址,2:验证通过为新增网站)', INDEX tool_name_inverted_index (`tool_name`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"), INDEX url_inverted_index (`url`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"), INDEX full_name_inverted_index (`full_name`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"), INDEX user_login_inverted_index (`user_login`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true") ) ENGINE = OLAP UNIQUE KEY(`id`) DISTRIBUTED BY HASH(`id`) BUCKETS 10 PROPERTIES ( "replication_num" = "1" ); CREATE TABLE `sample_url` ( `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '序号/ID', `name` VARCHAR(255) NULL COMMENT '网站名', `url` VARCHAR(1024) NULL COMMENT '样本链接地址', `full_name` VARCHAR(255) NULL COMMENT '首发项目全称', `user_id` BIGINT NULL COMMENT '首发项目作者ID', `user_login` VARCHAR(255) NULL COMMENT '作者登录名', `file_path` VARCHAR(1024) NULL COMMENT '首发项目文件路径', `ft` DATETIME NULL COMMENT '发现时间', `ss` TINYINT NULL DEFAULT "0" COMMENT '发送状态(0:默认值未发送,1:发送成功,-1:未发送成功)', `st` DATETIME NULL COMMENT '发送时间', `sr` TEXT NULL COMMENT '发送结果', `vr` TINYINT NULL DEFAULT "0" COMMENT '验证结果(-1:验证不通过,0:默认值,1:验证通过为新增网址,2:验证通过为新增网站)', -- 倒排索引定义 INDEX name_inverted_index (`name`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"), INDEX url_inverted_index (`url`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"), INDEX full_name_inverted_index (`full_name`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"), INDEX user_login_inverted_index (`user_login`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true") ) ENGINE = OLAP UNIQUE KEY(`id`) DISTRIBUTED BY HASH(`id`) BUCKETS 10 PROPERTIES ( "replication_num" = "1" ); CREATE TABLE `mirror_site` ( `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '序号/ID', `name` VARCHAR(255) NULL COMMENT '网站名', `url` VARCHAR(1024) NULL COMMENT '网站首页地址', `full_name` VARCHAR(255) NULL COMMENT '项目全称', `user_id` BIGINT NULL COMMENT '首发项目作者ID', `user_login` VARCHAR(255) NULL COMMENT '作者登录名', `commit_id` VARCHAR(255) NULL COMMENT 'commit id信息', `file_path` VARCHAR(1024) NULL COMMENT '首发项目文件路径', `type` INT NULL COMMENT '类型(采用二进制位标记法: 1-机场, 2-涉政, 3-机场+涉政)', `ft` DATETIME NULL COMMENT '发现时间', `ss` TINYINT NULL DEFAULT "0" COMMENT '发送状态(0:默认值未发送,1:发送成功,-1:未发送成功)', `st` DATETIME NULL COMMENT '发送时间', `sr` TEXT NULL COMMENT '发送结果', `vr` TINYINT NULL DEFAULT "0" COMMENT '验证结果(-1:验证不通过,0:默认值,1:新增网址,2:新增网站)', -- 倒排索引定义 INDEX name_inverted_idx (`name`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"), INDEX url_inverted_idx (`url`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"), INDEX full_name_inverted_idx (`full_name`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"), INDEX user_login_inverted_idx (`user_login`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true") ) ENGINE = OLAP UNIQUE KEY(`id`) DISTRIBUTED BY HASH(`id`) BUCKETS 10 PROPERTIES ( "replication_num" = "1" ); CREATE TABLE `resource_url` ( `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '序号/ID', `protocol` VARCHAR(50) NULL COMMENT '协议类型', `url` VARCHAR(1024) NULL COMMENT '资源URL', `full_name` VARCHAR(255) NULL COMMENT '项目全称', `user_id` BIGINT NULL COMMENT '首发项目作者ID', `user_login` VARCHAR(255) NULL COMMENT '作者登录名', `commit_id` VARCHAR(255) NULL COMMENT 'commit id信息', `file_path` VARCHAR(1024) NULL COMMENT '首发项目文件路径', `type` TINYINT NULL COMMENT '类型(1:资源链接, 2:订阅链接)', `ft` DATETIME NULL COMMENT '发现时间', `ss` TINYINT NULL DEFAULT "0" COMMENT '发送状态(0:默认值未发送,1:发送成功,-1:未发送成功)', `st` DATETIME NULL COMMENT '发送时间', `sr` TEXT NULL COMMENT '发送结果', `vr` TINYINT NULL DEFAULT "0" COMMENT '验证结果(-1:验证不通过,0:默认值,1:新增网址,2:新增网站)', -- 倒排索引定义 INDEX url_inverted_idx (`url`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"), INDEX full_name_inverted_idx (`full_name`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"), INDEX user_login_inverted_idx (`user_login`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true") ) ENGINE = OLAP UNIQUE KEY(`id`) DISTRIBUTED BY HASH(`id`) BUCKETS 10 PROPERTIES ( "replication_num" = "1" ); -- 分析结果记录表 create table `analysis_res` ( `project_id` BIGINT NOT NULL COMMENT '项目ID,Github平台唯一', `type` INT NULL COMMENT '发现的信息类型1:疑似工具；2：样本链接；3：sz镜像网站；4：机场镜像网站;5：FQ资源；6：订阅资源', `source` INT NULL COMMENT '分析来源1：公开项目信息；2：重点项目信息；3：重点项目文件；4：重点项目release', `relation_id` BIGINT NOT NULL COMMENT '关联表ID 某个类型（工具/资源/网站）下的ID', `project_full_name` varchar(255) NULL COMMENT '项目全名信息 [user_login]/[project_name]', `file_path` varchar(500) NULL COMMENT '分析的具体文件', `release_tag` VARCHAR(200) NULL COMMENT '分析的某个release 的tag', `release_asserts` VARCHAR(500) NULL COMMENT '分析的某个release 的 assert，同release表', `release_commit_id` VARCHAR(200) NULL COMMENT '分析的某个release 的 commit_id', `ft` datetime NULL COMMENT '发现时间' ) ENGINE=OLAP UNIQUE KEY(`project_id`, `type`, `source`, `relation_id`) DISTRIBUTED BY HASH(`project_id`) BUCKETS 10 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "min_load_replica_num" = "-1", "is_being_synced" = "false", "storage_medium" = "hdd", "storage_format" = "V2", "inverted_index_storage_format" = "V2", "enable_unique_key_merge_on_write" = "true", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false", "group_commit_interval_ms" = "10000", "group_commit_data_bytes" = "134217728", "enable_mow_light_delete" = "false" ) -- 基础应用信息表 CREATE TABLE app_basic_info ( `api_key` VARCHAR(64) COMMENT '应用唯一标识ID', `secret` VARCHAR(128) COMMENT '应用密钥', `app_name` VARCHAR(255) COMMENT '应用名称', `description` VARCHAR COMMENT '描述', `industry_category` VARCHAR(100) COMMENT '行业分类', `email` VARCHAR(100) COMMENT '邮箱', `phone` VARCHAR(20) COMMENT '手机', `company` VARCHAR(255) COMMENT '联系人/单位', `qps_limit` INT DEFAULT 1000 COMMENT 'QPS限流值', `status` TINYINT DEFAULT 0 COMMENT '假删除标记(0:未删除,1:已删除)', `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', -- 倒排索引定义 INDEX company_inverted_idx (`company`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"), INDEX app_name_inverted_idx (`app_name`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true"), INDEX description_inverted_idx (`description`) USING INVERTED PROPERTIES("support_phrase" = "true", "parser" = "unicode", "lower_case" = "true") ) ENGINE=OLAP UNIQUE KEY(`api_key`, `secret`) DISTRIBUTED BY HASH(`api_key`) BUCKETS 10 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "min_load_replica_num" = "-1", "is_being_synced" = "false", "storage_medium" = "hdd", "storage_format" = "V2", "inverted_index_storage_format" = "V2", "enable_unique_key_merge_on_write" = "true", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false", "group_commit_interval_ms" = "10000", "group_commit_data_bytes" = "134217728", "enable_mow_light_delete" = "false" ) -- 应用权限点关联表 CREATE TABLE app_permission_relation ( `app_id` VARCHAR(64) COMMENT '应用的app_id', `permission_id` VARCHAR(64) COMMENT '权限点ID', `id` BIGINT COMMENT '表ID', `permission_name` VARCHAR(255) COMMENT '权限点名称', `data_limit` BIGINT COMMENT '权限点查询的数据量限制', `rate_limit` INT COMMENT '限流次数(次数/s)', `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' ) ENGINE=OLAP UNIQUE KEY(`app_id`, `permission_id`) DISTRIBUTED BY HASH(`app_id`) BUCKETS 10 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "min_load_replica_num" = "-1", "is_being_synced" = "false", "storage_medium" = "hdd", "storage_format" = "V2", "inverted_index_storage_format" = "V2", "enable_unique_key_merge_on_write" = "true", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false", "group_commit_interval_ms" = "10000", "group_commit_data_bytes" = "134217728", "enable_mow_light_delete" = "false" ) -- 权限点表 CREATE TABLE permission_points ( `permission_id` VARCHAR(64) COMMENT '权限点ID', `permission_name` VARCHAR(255) COMMENT '权限点名称', `description` TEXT COMMENT '权限点描述', `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' ) UNIQUE KEY(`permission_id`) DISTRIBUTED BY HASH(`permission_id`) BUCKETS 10 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "min_load_replica_num" = "-1", "is_being_synced" = "false", "storage_medium" = "hdd", "storage_format" = "V2", "inverted_index_storage_format" = "V2", "enable_unique_key_merge_on_write" = "true", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false", "group_commit_interval_ms" = "10000", "group_commit_data_bytes" = "134217728", "enable_mow_light_delete" = "false" ) -- 日志记录表 CREATE TABLE api_logs ( `log_id` BIGINT COMMENT '日志ID', `api_key` VARCHAR(64) COMMENT 'API标识ID', `permission_name` VARCHAR(64) COMMENT '查询的权限点', `data_size` BIGINT COMMENT '获取数据量(size)', `query_time` DATETIME COMMENT '查询时间', `response_time` INT COMMENT '接口响应时间(ms)', `limited_flag` TINYINT DEFAULT 0 COMMENT '是否触发限流，0没有限流 1 限流', `limited_type` VARCHAR(50) COMMENT '限流类型(QPS/数据量/其他)', `limited_desc` TEXT COMMENT '限流原因描述', `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '日志创建时间' ) ENGINE=OLAP UNIQUE KEY(`log_id`,`api_key`) DISTRIBUTED BY HASH(`api_key`) BUCKETS 10 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "min_load_replica_num" = "-1", "is_being_synced" = "false", "storage_medium" = "hdd", "storage_format" = "V2", "inverted_index_storage_format" = "V2", "enable_unique_key_merge_on_write" = "true", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false", "group_commit_interval_ms" = "10000", "group_commit_data_bytes" = "134217728", "enable_mow_light_delete" = "false" )