196 lines
8.9 KiB
SQL
196 lines
8.9 KiB
SQL
-- ============================================================================
|
||
-- 新数据库 Schema 定义 (v4 重构)
|
||
-- ============================================================================
|
||
-- 设计理念：
|
||
-- 1. 保留完整的原始数据（worker返回的所有字段）
|
||
-- 2. collection_task 既是任务列表也是执行记录，用 attempt 追踪重试
|
||
-- 3. 不做预计算，traffic 文件路径存为 JSON 数组，按需重算流量
|
||
-- 4. 运行时代码直接读取新表并在查询层按需计算分析结果
|
||
-- 5. 不存经过判断的受限状态，只存 worker 原始 success/failed
|
||
--
|
||
-- 运行库只创建基础表和索引，不创建兼容视图/投影
|
||
-- ============================================================================
|
||
|
||
PRAGMA journal_mode = WAL;
|
||
PRAGMA foreign_keys = OFF;
|
||
|
||
-- ============================================================================
|
||
-- 表 1: collection_task — 采集任务表（核心）
|
||
-- 既是任务列表也是执行记录。复合主键 (package_name, batch_tag, run_kind, attempt)
|
||
-- ============================================================================
|
||
CREATE TABLE IF NOT EXISTS collection_task (
|
||
-- 复合主键
|
||
package_name TEXT NOT NULL,
|
||
batch_tag TEXT NOT NULL, -- 批次标签，如 '2026-6-4'
|
||
run_kind TEXT NOT NULL DEFAULT 'ranking'
|
||
CHECK(run_kind IN ('ranking', 'block', 'model', 'manual')),
|
||
attempt INTEGER NOT NULL DEFAULT 1,-- 采集轮次（重试自增）
|
||
|
||
-- 任务基本信息
|
||
task_key TEXT, -- 任务唯一 key
|
||
app_name TEXT, -- 应用显示名称
|
||
app_magic_label TEXT, -- 应用魔法标签（关联同一应用的不同变体）
|
||
|
||
-- 任务类型标识
|
||
is_new_app INTEGER DEFAULT 0, -- ranking任务: 新增应用(1) / 更新应用(0)
|
||
|
||
-- 任务状态
|
||
task_status TEXT DEFAULT 'pending', -- 'pending' / 'running' / 'completed'
|
||
execution_status TEXT, -- worker原始状态: 'success'/'failed'/'stop'
|
||
|
||
-- Worker信息
|
||
worker_id TEXT,
|
||
|
||
-- 时间信息
|
||
created_at TEXT DEFAULT (datetime('now', 'localtime')),
|
||
started_at TEXT, -- 任务开始时间
|
||
completed_at TEXT, -- 任务完成时间
|
||
|
||
-- 时长统计（worker返回）
|
||
duration_seconds REAL,
|
||
download_duration_seconds REAL,
|
||
execution_duration_seconds REAL,
|
||
analysis_duration_seconds REAL,
|
||
|
||
-- 错误信息（worker返回的原始错误）
|
||
error_category TEXT, -- 'INFRA_ERROR'/'DOWNLOAD_ERROR'/'APP_ERROR'/'BUSINESS_ERROR'
|
||
error_code INTEGER, -- 错误代码
|
||
error_reason TEXT, -- 错误原因
|
||
error_details TEXT, -- 详细错误信息
|
||
crashed_source TEXT, -- 导致崩溃的下载源
|
||
|
||
-- 执行统计（worker返回的原始字段）
|
||
droidbot_steps INTEGER DEFAULT 0,
|
||
gui_agent_steps INTEGER DEFAULT 0,
|
||
total_steps INTEGER DEFAULT 0,
|
||
num_nodes INTEGER DEFAULT 0, -- UI节点数
|
||
num_reached_activities INTEGER DEFAULT 0, -- 到达的activity数量
|
||
app_num_total_activities INTEGER DEFAULT 0, -- 应用总activity数量
|
||
|
||
-- 流量统计（worker返回的原始数据）
|
||
total_traffic_bytes INTEGER DEFAULT 0,
|
||
self_traffic_bytes INTEGER DEFAULT 0,
|
||
server_traffic_bytes INTEGER DEFAULT 0,
|
||
unrecognized_traffic_bytes INTEGER DEFAULT 0,
|
||
model_flow_count INTEGER DEFAULT 0,
|
||
model_traffic_bytes INTEGER DEFAULT 0,
|
||
|
||
-- Metrics（worker返回的额外指标）
|
||
login_count INTEGER DEFAULT 0,
|
||
register_count INTEGER DEFAULT 0,
|
||
stuck_reason_code INTEGER,
|
||
guiagent_message TEXT,
|
||
scenario_triggered INTEGER DEFAULT 0,
|
||
|
||
-- 下载信息
|
||
download_source TEXT, -- 'google_play' / 'local'
|
||
is_retry INTEGER DEFAULT 0,
|
||
|
||
-- 执行追踪
|
||
exit_code INTEGER,
|
||
trace_json TEXT, -- 执行轨迹 JSON
|
||
|
||
-- 流量文件路径列表（JSON数组）
|
||
traffic_file_paths TEXT, -- ['\\\\lfs.../pkg/...', ...]
|
||
|
||
PRIMARY KEY (package_name, batch_tag, run_kind, attempt)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_collection_task_status
|
||
ON collection_task(task_status, run_kind, batch_tag);
|
||
CREATE INDEX IF NOT EXISTS idx_collection_task_execution
|
||
ON collection_task(execution_status, run_kind);
|
||
CREATE INDEX IF NOT EXISTS idx_collection_task_pkg
|
||
ON collection_task(package_name, run_kind);
|
||
CREATE INDEX IF NOT EXISTS idx_collection_task_time
|
||
ON collection_task(created_at);
|
||
CREATE INDEX IF NOT EXISTS idx_collection_task_worker
|
||
ON collection_task(worker_id, completed_at);
|
||
CREATE INDEX IF NOT EXISTS idx_collection_task_magic
|
||
ON collection_task(app_magic_label);
|
||
CREATE INDEX IF NOT EXISTS idx_collection_task_batch
|
||
ON collection_task(batch_tag, run_kind);
|
||
|
||
-- ============================================================================
|
||
-- 表 2: app_catalog — 应用目录元数据
|
||
-- 应用的分类、标签、优先级等管理信息（与采集结果分离）
|
||
-- ============================================================================
|
||
CREATE TABLE IF NOT EXISTS app_catalog (
|
||
package_name TEXT PRIMARY KEY,
|
||
app_name TEXT,
|
||
|
||
-- 分类管理
|
||
batch_tags TEXT, -- JSON数组：完整的批次标签列表
|
||
app_magic_label TEXT, -- 应用魔法标签
|
||
last_updated TEXT, -- 应用商店最后更新时间
|
||
country_code TEXT, -- 采集国家/地区
|
||
device_type TEXT, -- emulator / physical / any
|
||
task_payload_json TEXT, -- 下发给 worker 的完整任务 payload
|
||
last_update_interval_days INTEGER DEFAULT 0, -- 版本更新时间间隔
|
||
|
||
-- 优先级管理
|
||
task_queue TEXT DEFAULT 'default', -- 'default' / 'priority'
|
||
task_priority INTEGER DEFAULT 50,
|
||
source_order INTEGER, -- 在源列表中的顺序
|
||
|
||
-- 状态标记
|
||
is_active INTEGER DEFAULT 1, -- 是否激活
|
||
is_blocked INTEGER DEFAULT 0, -- 是否被屏蔽
|
||
|
||
-- 应用信息
|
||
category TEXT, -- 应用分类
|
||
downloads INTEGER, -- 下载量
|
||
|
||
created_at TEXT DEFAULT (datetime('now', 'localtime')),
|
||
updated_at TEXT DEFAULT (datetime('now', 'localtime'))
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_app_catalog_priority
|
||
ON app_catalog(task_priority DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_app_catalog_magic
|
||
ON app_catalog(app_magic_label);
|
||
CREATE INDEX IF NOT EXISTS idx_app_catalog_active
|
||
ON app_catalog(is_active, source_order);
|
||
|
||
-- ============================================================================
|
||
-- 表 3: apk_registry — APK文件注册表
|
||
-- 结构与原系统完全一致（由 apk_cloud/registry.py 维护），此处仅用于迁移数据落地
|
||
-- ============================================================================
|
||
CREATE TABLE IF NOT EXISTS apk_registry (
|
||
package_name TEXT PRIMARY KEY,
|
||
download_date TEXT NOT NULL,
|
||
download_time REAL NOT NULL,
|
||
local_dir TEXT,
|
||
smb_dir TEXT,
|
||
version_name TEXT,
|
||
source TEXT,
|
||
apk_files_json TEXT,
|
||
updated_at REAL NOT NULL
|
||
);
|
||
|
||
-- ============================================================================
|
||
-- 表 4: worker_activity_summary — Worker状态时间分布（每小时汇总）
|
||
-- ============================================================================
|
||
CREATE TABLE IF NOT EXISTS worker_activity_summary (
|
||
worker_id TEXT NOT NULL,
|
||
stat_date TEXT NOT NULL, -- YYYY-MM-DD
|
||
stat_hour INTEGER NOT NULL, -- 0-23（-1 表示全天汇总）
|
||
|
||
-- 状态时长统计（秒）
|
||
idle_duration_seconds INTEGER DEFAULT 0,
|
||
busy_duration_seconds INTEGER DEFAULT 0,
|
||
offline_duration_seconds INTEGER DEFAULT 0,
|
||
|
||
-- 任务统计
|
||
task_count INTEGER DEFAULT 0,
|
||
success_count INTEGER DEFAULT 0,
|
||
failed_count INTEGER DEFAULT 0,
|
||
|
||
PRIMARY KEY (worker_id, stat_date, stat_hour)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_worker_activity_date
|
||
ON worker_activity_summary(stat_date DESC);
|
||
|
||
-- 运行时代码直接查询真实表，不创建投影或视图。
|