899 lines
36 KiB
Python
899 lines
36 KiB
Python
"""
|
||
database.py
|
||
数据库模块：负责 SQLite 连接、表初始化和基础 CRUD 操作。
|
||
使用 Python 内置 sqlite3，无需额外 ORM，保持轻量。
|
||
"""
|
||
|
||
import sqlite3
|
||
import os
|
||
from contextlib import contextmanager
|
||
from datetime import datetime
|
||
|
||
# 数据库文件路径（放在当前目录下，轻量本地运行）
|
||
DB_PATH = os.path.join(os.path.dirname(__file__), "app.db")
|
||
|
||
# 上传文件根目录（与 uploads 子目录共用）
|
||
UPLOAD_DIR = os.path.join(os.path.dirname(__file__), "uploads")
|
||
|
||
# 固件公共目录（Next.js public 下，已有实际固件文件）
|
||
FIRMWARE_PUBLIC_DIR = os.path.join(os.path.dirname(__file__), "..", "public", "uploads", "GD", "firmware")
|
||
FIRMWARE_PUBLIC_DIR = os.path.normpath(FIRMWARE_PUBLIC_DIR)
|
||
|
||
# 固件类型到子目录映射
|
||
FIRMWARE_TYPE_FOLDER = {
|
||
"采集板": "CJB",
|
||
"发射板": "FSB",
|
||
"主协板": "XCL",
|
||
"主机服务": "",
|
||
}
|
||
|
||
|
||
def get_db_connection() -> sqlite3.Connection:
|
||
"""
|
||
创建并返回一个 SQLite 连接对象。
|
||
设置 row_factory 为 sqlite3.Row，使查询结果可以通过列名访问。
|
||
"""
|
||
conn = sqlite3.connect(DB_PATH, check_same_thread=False)
|
||
conn.row_factory = sqlite3.Row
|
||
conn.execute("PRAGMA foreign_keys = ON")
|
||
return conn
|
||
|
||
|
||
@contextmanager
|
||
def get_db():
|
||
"""
|
||
上下文管理器，用于在 API 接口中安全获取和释放数据库连接。
|
||
用法：
|
||
with get_db() as db:
|
||
db.execute(...)
|
||
"""
|
||
conn = get_db_connection()
|
||
try:
|
||
yield conn
|
||
finally:
|
||
conn.close()
|
||
|
||
|
||
def init_db():
|
||
"""
|
||
初始化数据库：创建所有表（如果不存在）。
|
||
"""
|
||
# 确保上传目录存在
|
||
os.makedirs(UPLOAD_DIR, exist_ok=True)
|
||
os.makedirs(os.path.join(UPLOAD_DIR, "calibration"), exist_ok=True)
|
||
os.makedirs(os.path.join(UPLOAD_DIR, "apps"), exist_ok=True)
|
||
os.makedirs(os.path.join(UPLOAD_DIR, "firmware"), exist_ok=True)
|
||
|
||
with get_db() as db:
|
||
# 1. 设备表（统一前后端结构）
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS devices (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
sn TEXT NOT NULL UNIQUE,
|
||
model TEXT,
|
||
type TEXT,
|
||
status TEXT NOT NULL DEFAULT '待激活',
|
||
firmware TEXT DEFAULT '',
|
||
production_date INTEGER,
|
||
customer TEXT DEFAULT '-',
|
||
batch TEXT DEFAULT '',
|
||
activated_at INTEGER,
|
||
created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
|
||
)
|
||
""")
|
||
|
||
# 2. 校准文件表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS calibration_files (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
material_sn TEXT NOT NULL,
|
||
sn TEXT,
|
||
uid TEXT,
|
||
file_name TEXT,
|
||
file_path TEXT,
|
||
file_size INTEGER,
|
||
md5 TEXT,
|
||
result TEXT,
|
||
operator TEXT,
|
||
remark TEXT,
|
||
upload_time INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
|
||
channels_count INTEGER DEFAULT 0
|
||
)
|
||
""")
|
||
|
||
# 3. 校准通道数据表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS calibration_channels (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
calibration_id INTEGER NOT NULL,
|
||
channel_id INTEGER,
|
||
factor_80v REAL,
|
||
offset_80v REAL,
|
||
factor_2_5v REAL,
|
||
offset_2_5v REAL,
|
||
FOREIGN KEY (calibration_id) REFERENCES calibration_files(id) ON DELETE CASCADE
|
||
)
|
||
""")
|
||
|
||
# 4. APP 版本表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS app_versions (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
app_name TEXT NOT NULL,
|
||
package_name TEXT,
|
||
platform_type INTEGER DEFAULT 2,
|
||
version_name TEXT NOT NULL,
|
||
major_version INTEGER DEFAULT 0,
|
||
minor_version INTEGER DEFAULT 0,
|
||
patch_version INTEGER DEFAULT 0,
|
||
file_name TEXT,
|
||
file_path TEXT,
|
||
file_size INTEGER DEFAULT 0,
|
||
file_type TEXT,
|
||
distribution_type TEXT DEFAULT 'direct',
|
||
primary_url TEXT,
|
||
os_min_version TEXT,
|
||
is_force_update INTEGER DEFAULT 0,
|
||
changelog TEXT,
|
||
status INTEGER DEFAULT 1,
|
||
created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
|
||
updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
|
||
)
|
||
""")
|
||
|
||
# 5. 配置文件表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS config_files (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
name TEXT NOT NULL,
|
||
model TEXT NOT NULL,
|
||
version TEXT NOT NULL DEFAULT 'v1.0',
|
||
status TEXT NOT NULL DEFAULT '生效',
|
||
max_tx_voltage TEXT,
|
||
max_tx_current TEXT,
|
||
tx_waveform TEXT,
|
||
tx_pulse_width TEXT,
|
||
acq_channels TEXT,
|
||
acq_sample_rate TEXT,
|
||
acq_voltage_range TEXT,
|
||
full_waveform_capture TEXT,
|
||
ssid_prefix TEXT,
|
||
created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
|
||
updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
|
||
)
|
||
""")
|
||
|
||
# 6. 固件版本表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS firmware_versions (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
version TEXT NOT NULL,
|
||
firmware_type TEXT NOT NULL,
|
||
board_model TEXT,
|
||
device_model TEXT,
|
||
file_name TEXT,
|
||
file_path TEXT,
|
||
file_size INTEGER DEFAULT 0,
|
||
hw_range TEXT,
|
||
upgrade_type TEXT DEFAULT '可选',
|
||
signed INTEGER DEFAULT 0,
|
||
notes TEXT,
|
||
status TEXT DEFAULT '已发布',
|
||
created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
|
||
updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
|
||
)
|
||
""")
|
||
|
||
# 7. 设备型号表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS device_models (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
name TEXT NOT NULL,
|
||
code TEXT NOT NULL UNIQUE,
|
||
status TEXT NOT NULL DEFAULT '在产',
|
||
description TEXT DEFAULT '',
|
||
create_date INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
|
||
)
|
||
""")
|
||
|
||
# 8. 装配Checklist模板表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS checklist_templates (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
model_code TEXT NOT NULL,
|
||
name TEXT NOT NULL,
|
||
required INTEGER NOT NULL DEFAULT 1,
|
||
standard TEXT,
|
||
sort_order INTEGER NOT NULL DEFAULT 0
|
||
)
|
||
""")
|
||
|
||
# 9. 板卡类型表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS board_types (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
name TEXT NOT NULL,
|
||
category TEXT NOT NULL,
|
||
device_models TEXT NOT NULL DEFAULT '[]',
|
||
description TEXT DEFAULT '',
|
||
status TEXT NOT NULL DEFAULT '启用'
|
||
)
|
||
""")
|
||
|
||
# 10. 板卡版本表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS board_versions (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
type TEXT NOT NULL,
|
||
version TEXT NOT NULL,
|
||
status TEXT NOT NULL DEFAULT '在产'
|
||
)
|
||
""")
|
||
|
||
# 11. 物料实例表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS materials (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
sn TEXT NOT NULL UNIQUE,
|
||
name TEXT NOT NULL DEFAULT '',
|
||
category TEXT NOT NULL DEFAULT '',
|
||
type TEXT NOT NULL,
|
||
device_model TEXT NOT NULL DEFAULT '',
|
||
version TEXT NOT NULL,
|
||
description TEXT DEFAULT '',
|
||
firmware TEXT DEFAULT '-',
|
||
status TEXT NOT NULL DEFAULT '在库',
|
||
device_sn TEXT DEFAULT '-',
|
||
production_date INTEGER,
|
||
calib_status TEXT DEFAULT '-',
|
||
calib_date INTEGER DEFAULT 0
|
||
)
|
||
""")
|
||
|
||
# 12. BOM模板表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS bom_templates (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
model_code TEXT NOT NULL,
|
||
name TEXT NOT NULL,
|
||
material_name TEXT NOT NULL DEFAULT '',
|
||
model TEXT NOT NULL,
|
||
versions TEXT NOT NULL DEFAULT '[]',
|
||
qty INTEGER NOT NULL DEFAULT 1,
|
||
required INTEGER NOT NULL DEFAULT 1,
|
||
need_calibration INTEGER NOT NULL DEFAULT 0,
|
||
enforce_version_match INTEGER NOT NULL DEFAULT 0
|
||
)
|
||
""")
|
||
|
||
# 13. 授权表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS licenses (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
model TEXT NOT NULL,
|
||
modules TEXT NOT NULL,
|
||
expiry TEXT DEFAULT '',
|
||
status TEXT NOT NULL DEFAULT '生效',
|
||
config_id INTEGER DEFAULT NULL,
|
||
license_file TEXT DEFAULT '',
|
||
device_sn TEXT DEFAULT '',
|
||
created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
|
||
updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
|
||
FOREIGN KEY (config_id) REFERENCES config_files(id) ON DELETE SET NULL
|
||
)
|
||
""")
|
||
|
||
# 14. 维修工单表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS repair_orders (
|
||
id TEXT PRIMARY KEY,
|
||
sn TEXT NOT NULL,
|
||
fault_type TEXT NOT NULL,
|
||
status TEXT NOT NULL DEFAULT '待处理',
|
||
priority TEXT NOT NULL DEFAULT '中',
|
||
assignee TEXT DEFAULT '',
|
||
create_date INTEGER NOT NULL,
|
||
description TEXT DEFAULT ''
|
||
)
|
||
""")
|
||
|
||
# 15. 报废记录表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS scrap_records (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
sn TEXT NOT NULL,
|
||
model TEXT NOT NULL,
|
||
reason TEXT NOT NULL,
|
||
applicant TEXT DEFAULT '',
|
||
status TEXT NOT NULL DEFAULT '待审批',
|
||
order_id TEXT DEFAULT '',
|
||
date INTEGER NOT NULL,
|
||
value INTEGER DEFAULT 0,
|
||
materials TEXT DEFAULT '[]'
|
||
)
|
||
""")
|
||
|
||
# 16. 物料分类表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS material_categories (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
name TEXT NOT NULL UNIQUE,
|
||
description TEXT DEFAULT '',
|
||
has_firmware INTEGER NOT NULL DEFAULT 0,
|
||
has_calibration INTEGER NOT NULL DEFAULT 0,
|
||
sort_order INTEGER NOT NULL DEFAULT 0,
|
||
status TEXT NOT NULL DEFAULT '启用'
|
||
)
|
||
""")
|
||
|
||
# 17. 设备装机BOM记录表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS device_bom_records (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
device_sn TEXT NOT NULL,
|
||
name TEXT NOT NULL,
|
||
material_sn TEXT DEFAULT '',
|
||
model TEXT DEFAULT '',
|
||
version TEXT DEFAULT '',
|
||
calibration TEXT DEFAULT '无需校准'
|
||
)
|
||
""")
|
||
|
||
# 18. 设备操作日志表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS device_logs (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
device_sn TEXT NOT NULL,
|
||
action TEXT NOT NULL,
|
||
operator TEXT DEFAULT '',
|
||
detail TEXT DEFAULT '',
|
||
date INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
|
||
)
|
||
""")
|
||
|
||
# 19. 更新日志表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS update_logs (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
title TEXT NOT NULL,
|
||
content TEXT DEFAULT '',
|
||
category TEXT NOT NULL DEFAULT 'feature',
|
||
version TEXT DEFAULT '',
|
||
created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
|
||
)
|
||
""")
|
||
|
||
# 20. 授权下载日志表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS license_download_logs (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
license_id INTEGER NOT NULL,
|
||
device_sn TEXT NOT NULL,
|
||
download_time INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
|
||
ip_address TEXT DEFAULT '',
|
||
app_version TEXT DEFAULT ''
|
||
)
|
||
""")
|
||
|
||
# 21. 授权模板表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS license_templates (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
name TEXT NOT NULL,
|
||
model_code TEXT NOT NULL,
|
||
auth_items TEXT NOT NULL DEFAULT '[]',
|
||
config_id INTEGER DEFAULT NULL,
|
||
status TEXT NOT NULL DEFAULT '启用',
|
||
created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
|
||
)
|
||
""")
|
||
|
||
# 22. 授权项定义表（全局授权模块字典）
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS auth_items (
|
||
id TEXT PRIMARY KEY,
|
||
name TEXT NOT NULL,
|
||
description TEXT DEFAULT '',
|
||
category TEXT NOT NULL,
|
||
sort_order INTEGER DEFAULT 0,
|
||
status TEXT NOT NULL DEFAULT '启用'
|
||
)
|
||
""")
|
||
|
||
# 23. 设备装配检查记录表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS device_checklist_records (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
device_sn TEXT NOT NULL,
|
||
checklist_name TEXT NOT NULL,
|
||
passed INTEGER DEFAULT 0,
|
||
photos TEXT DEFAULT '[]',
|
||
note TEXT DEFAULT '',
|
||
created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
|
||
updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
|
||
)
|
||
""")
|
||
|
||
# 24. 维修处理记录表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS repair_process_records (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
order_id TEXT NOT NULL,
|
||
action TEXT NOT NULL,
|
||
operator TEXT DEFAULT '',
|
||
date INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
|
||
note TEXT DEFAULT ''
|
||
)
|
||
""")
|
||
|
||
# 25. 维修板卡更换记录表
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS repair_board_replacements (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
order_id TEXT NOT NULL,
|
||
type TEXT NOT NULL,
|
||
model TEXT DEFAULT '',
|
||
old_sn TEXT DEFAULT '',
|
||
new_sn TEXT DEFAULT '',
|
||
date INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
|
||
)
|
||
""")
|
||
|
||
# 27. 通用字典表（枚举值、选项配置）
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS reference_values (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
category TEXT NOT NULL,
|
||
code TEXT NOT NULL,
|
||
label TEXT NOT NULL,
|
||
sort_order INTEGER DEFAULT 0,
|
||
status TEXT NOT NULL DEFAULT '启用',
|
||
description TEXT DEFAULT '',
|
||
extra TEXT DEFAULT '{}',
|
||
UNIQUE(category, code)
|
||
)
|
||
""")
|
||
|
||
# 创建索引优化查询性能
|
||
indexes = [
|
||
("idx_devices_status", "devices", "status"),
|
||
("idx_devices_model", "devices", "model"),
|
||
("idx_calibration_material_sn", "calibration_files", "material_sn"),
|
||
("idx_calibration_sn", "calibration_files", "sn"),
|
||
("idx_calibration_uid", "calibration_files", "uid"),
|
||
("idx_app_name_platform", "app_versions", "app_name, platform_type"),
|
||
("idx_app_versions_status", "app_versions", "status"),
|
||
("idx_config_model", "config_files", "model"),
|
||
("idx_config_status", "config_files", "status"),
|
||
("idx_firmware_type_model", "firmware_versions", "firmware_type, device_model, board_model"),
|
||
("idx_firmware_status", "firmware_versions", "status"),
|
||
("idx_licenses_device_sn", "licenses", "device_sn"),
|
||
("idx_licenses_config_id", "licenses", "config_id"),
|
||
("idx_materials_device_sn", "materials", "device_sn"),
|
||
("idx_materials_category", "materials", "category"),
|
||
("idx_repair_status", "repair_orders", "status"),
|
||
("idx_scrap_status", "scrap_records", "status"),
|
||
("idx_device_bom_sn", "device_bom_records", "device_sn"),
|
||
("idx_bom_model", "bom_templates", "model_code"),
|
||
("idx_checklist_model", "checklist_templates", "model_code"),
|
||
("idx_reference_category", "reference_values", "category, sort_order"),
|
||
]
|
||
for idx_name, table, columns in indexes:
|
||
db.execute(f"CREATE INDEX IF NOT EXISTS {idx_name} ON {table}({columns})")
|
||
|
||
db.commit()
|
||
print(f"[DB] 数据库已初始化: {DB_PATH}")
|
||
|
||
|
||
def _column_exists(db: sqlite3.Connection, table: str, column: str) -> bool:
|
||
"""检查表中是否已存在指定列"""
|
||
rows = db.execute(f"PRAGMA table_info({table})").fetchall()
|
||
return any(r["name"] == column for r in rows)
|
||
|
||
|
||
def migrate_db():
|
||
"""
|
||
增量迁移：为已存在的表添加新列和索引（不删除数据）。
|
||
将现有 TEXT 格式的时间数据迁移为 INTEGER Unix 时间戳。
|
||
"""
|
||
with get_db() as db:
|
||
# 1. config_files 表列迁移
|
||
new_columns = [
|
||
("max_tx_voltage", "TEXT"),
|
||
("max_tx_current", "TEXT"),
|
||
("tx_waveform", "TEXT"),
|
||
("tx_pulse_width", "TEXT"),
|
||
("acq_channels", "TEXT"),
|
||
("acq_sample_rate", "TEXT"),
|
||
("acq_voltage_range", "TEXT"),
|
||
("full_waveform_capture", "TEXT"),
|
||
("ssid_prefix", "TEXT"),
|
||
]
|
||
for col, ctype in new_columns:
|
||
if not _column_exists(db, "config_files", col):
|
||
try:
|
||
db.execute(f"ALTER TABLE config_files ADD COLUMN {col} {ctype}")
|
||
print(f"[DB MIGRATE] config_files 新增列: {col}")
|
||
except Exception as e:
|
||
print(f"[DB MIGRATE] 添加列 {col} 失败: {e}")
|
||
|
||
# 2. 创建索引（对已存在的数据库补充索引）
|
||
indexes = [
|
||
("idx_devices_status", "devices", "status"),
|
||
("idx_devices_model", "devices", "model"),
|
||
("idx_calibration_material_sn", "calibration_files", "material_sn"),
|
||
("idx_calibration_sn", "calibration_files", "sn"),
|
||
("idx_calibration_uid", "calibration_files", "uid"),
|
||
("idx_app_name_platform", "app_versions", "app_name, platform_type"),
|
||
("idx_app_versions_status", "app_versions", "status"),
|
||
("idx_config_model", "config_files", "model"),
|
||
("idx_config_status", "config_files", "status"),
|
||
("idx_firmware_type_model", "firmware_versions", "firmware_type, device_model, board_model"),
|
||
("idx_firmware_status", "firmware_versions", "status"),
|
||
("idx_licenses_device_sn", "licenses", "device_sn"),
|
||
("idx_licenses_config_id", "licenses", "config_id"),
|
||
("idx_materials_device_sn", "materials", "device_sn"),
|
||
("idx_materials_category", "materials", "category"),
|
||
("idx_repair_status", "repair_orders", "status"),
|
||
("idx_scrap_status", "scrap_records", "status"),
|
||
("idx_device_bom_sn", "device_bom_records", "device_sn"),
|
||
("idx_bom_model", "bom_templates", "model_code"),
|
||
("idx_checklist_model", "checklist_templates", "model_code"),
|
||
]
|
||
for idx_name, table, columns in indexes:
|
||
try:
|
||
db.execute(f"CREATE INDEX IF NOT EXISTS {idx_name} ON {table}({columns})")
|
||
except Exception as e:
|
||
print(f"[DB MIGRATE] 创建索引 {idx_name} 失败: {e}")
|
||
|
||
# 3. 授权项定义表（兼容已有数据库）
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS auth_items (
|
||
id TEXT PRIMARY KEY,
|
||
name TEXT NOT NULL,
|
||
description TEXT DEFAULT '',
|
||
category TEXT NOT NULL,
|
||
sort_order INTEGER DEFAULT 0,
|
||
status TEXT NOT NULL DEFAULT '启用'
|
||
)
|
||
""")
|
||
|
||
# 4. 设备装配检查记录表（兼容已有数据库）
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS device_checklist_records (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
device_sn TEXT NOT NULL,
|
||
checklist_name TEXT NOT NULL,
|
||
passed INTEGER DEFAULT 0,
|
||
photos TEXT DEFAULT '[]',
|
||
note TEXT DEFAULT '',
|
||
created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
|
||
updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
|
||
)
|
||
""")
|
||
|
||
# 5. 维修处理记录表（兼容已有数据库）
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS repair_process_records (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
order_id TEXT NOT NULL,
|
||
action TEXT NOT NULL,
|
||
operator TEXT DEFAULT '',
|
||
date INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
|
||
note TEXT DEFAULT ''
|
||
)
|
||
""")
|
||
|
||
# 6. 维修板卡更换记录表（兼容已有数据库）
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS repair_board_replacements (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
order_id TEXT NOT NULL,
|
||
type TEXT NOT NULL,
|
||
model TEXT DEFAULT '',
|
||
old_sn TEXT DEFAULT '',
|
||
new_sn TEXT DEFAULT '',
|
||
date INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
|
||
)
|
||
""")
|
||
|
||
# 7. 通用字典表（兼容已有数据库）
|
||
db.execute("""
|
||
CREATE TABLE IF NOT EXISTS reference_values (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
category TEXT NOT NULL,
|
||
code TEXT NOT NULL,
|
||
label TEXT NOT NULL,
|
||
sort_order INTEGER DEFAULT 0,
|
||
status TEXT NOT NULL DEFAULT '启用',
|
||
description TEXT DEFAULT '',
|
||
extra TEXT DEFAULT '{}',
|
||
UNIQUE(category, code)
|
||
)
|
||
""")
|
||
|
||
# 8. 将现有 TEXT 时间数据迁移为 INTEGER 时间戳（SQLite 动态类型，无需重建表）
|
||
timestamp_tables = {
|
||
"devices": ["created_at", "activated_at", "production_date"],
|
||
"calibration_files": ["upload_time"],
|
||
"app_versions": ["created_at", "updated_at"],
|
||
"config_files": ["created_at", "updated_at"],
|
||
"firmware_versions": ["created_at", "updated_at"],
|
||
"device_models": ["create_date"],
|
||
"licenses": ["created_at", "updated_at"],
|
||
"repair_orders": ["create_date"],
|
||
"scrap_records": ["date"],
|
||
"device_logs": ["date"],
|
||
"update_logs": ["created_at"],
|
||
"license_download_logs": ["download_time"],
|
||
"license_templates": ["created_at"],
|
||
"device_checklist_records": ["created_at", "updated_at"],
|
||
"repair_process_records": ["date"],
|
||
"repair_board_replacements": ["date"],
|
||
"materials": ["production_date", "calib_date"],
|
||
}
|
||
for tbl, cols in timestamp_tables.items():
|
||
for col in cols:
|
||
try:
|
||
db.execute(f"""
|
||
UPDATE {tbl}
|
||
SET {col} = CAST(strftime('%s', COALESCE({col}, '1970-01-01 00:00:00')) AS INTEGER)
|
||
WHERE {col} IS NOT NULL
|
||
AND typeof({col}) = 'text'
|
||
AND {col} != ''
|
||
""")
|
||
if db.total_changes > 0:
|
||
print(f"[DB MIGRATE] {tbl}.{col} 已迁移为时间戳")
|
||
except Exception as e:
|
||
print(f"[DB MIGRATE] {tbl}.{col} 迁移失败或无需迁移: {e}")
|
||
|
||
# 9. 数据库迁移：为 checklist_templates 表添加 standard 字段
|
||
if not _column_exists(db, "checklist_templates", "standard"):
|
||
try:
|
||
db.execute("ALTER TABLE checklist_templates ADD COLUMN standard TEXT")
|
||
print("[DB] 已为 checklist_templates 表添加 standard 字段")
|
||
except Exception as e:
|
||
print(f"[DB] 添加 standard 字段失败: {e}")
|
||
|
||
db.commit()
|
||
print("[DB MIGRATE] 数据库迁移完成")
|
||
|
||
|
||
def seed_demo_data():
|
||
"""
|
||
插入演示数据，方便新手直接体验接口。
|
||
如果已存在相同 SN，则忽略（INSERT OR IGNORE）。
|
||
"""
|
||
# 将演示日期转换为时间戳
|
||
demo_date_ts = int(datetime.strptime("2026-05-07", "%Y-%m-%d").timestamp())
|
||
demo_devices = [
|
||
("GD30-20260507-001", "GD30-2024", "标准型", "待激活", "", demo_date_ts, "华东地质局", "B2026-05"),
|
||
("GD30-20260507-002", "GD30-2024", "标准型", "待激活", "", demo_date_ts, "华北勘探院", "B2026-05"),
|
||
("MT-20260507-003", "MT-2024", "标准型", "已禁用", "", demo_date_ts, "-", "B2026-05"),
|
||
]
|
||
with get_db() as db:
|
||
for sn, model, type_, status, firmware, production_date, customer, batch in demo_devices:
|
||
db.execute(
|
||
"""
|
||
INSERT OR IGNORE INTO devices
|
||
(sn, model, type, status, firmware, production_date, customer, batch)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
|
||
""",
|
||
(sn, model, type_, status, firmware, production_date, customer, batch)
|
||
)
|
||
# 授权项定义种子数据
|
||
demo_auth_items = [
|
||
('1D', '一维自电/电阻率/激电测试模块', '包含一维自然电位法、电阻率测深、激发极化测深', '一维', 1),
|
||
('2D', '二维自电/电阻率/激电测试模块', '包含二维自然电位法、电阻率成像、激发极化成像', '二维', 2),
|
||
('3D', '三维自电/电阻率/激电测试模块', '包含三维自然电位法、电阻率成像、激发极化成像', '三维', 3),
|
||
('WATER', '水上', '水上电法探测', '水上', 4),
|
||
('CROSS', '跨孔', '跨孔电阻率成像', '跨孔', 5),
|
||
('CF', '电流场法', '电流场法', '电流场法', 6),
|
||
]
|
||
for id_, name, desc, cat, sort in demo_auth_items:
|
||
db.execute(
|
||
"INSERT OR IGNORE INTO auth_items (id, name, description, category, sort_order) VALUES (?, ?, ?, ?, ?)",
|
||
(id_, name, desc, cat, sort)
|
||
)
|
||
|
||
# 固件分类种子数据（确保4种固件类型分类存在）
|
||
demo_categories = [
|
||
("采集板", "数据采集板固件", 1, 0, 1),
|
||
("发射板", "信号发射板固件", 1, 0, 2),
|
||
("主协板", "主控协处理板固件", 1, 0, 3),
|
||
("主机服务", "主机服务升级包", 1, 0, 4),
|
||
]
|
||
for name, desc, has_fw, has_cal, sort in demo_categories:
|
||
db.execute(
|
||
"""
|
||
INSERT OR IGNORE INTO material_categories
|
||
(name, description, has_firmware, has_calibration, sort_order, status)
|
||
VALUES (?, ?, ?, ?, ?, '启用')
|
||
""",
|
||
(name, desc, has_fw, has_cal, sort)
|
||
)
|
||
|
||
# 通用字典种子数据
|
||
demo_refs = [
|
||
# 设备状态
|
||
("device_status", "待激活", "待激活", 1),
|
||
("device_status", "装配中", "装配中", 2),
|
||
("device_status", "测试通过", "测试通过", 3),
|
||
("device_status", "测试不通过", "测试不通过", 4),
|
||
("device_status", "已出厂", "已出厂", 5),
|
||
("device_status", "已激活", "已激活", 6),
|
||
("device_status", "已禁用", "已禁用", 7),
|
||
# 型号状态
|
||
("model_status", "在产", "在产", 1),
|
||
("model_status", "停产", "停产", 2),
|
||
# 物料状态
|
||
("material_status", "在库", "在库", 1),
|
||
("material_status", "已装配", "已装配", 2),
|
||
("material_status", "故障", "故障", 3),
|
||
("material_status", "报废", "报废", 4),
|
||
# 校准状态
|
||
("calibration_status", "待校准", "待校准", 1),
|
||
("calibration_status", "合格", "合格", 2),
|
||
("calibration_status", "不合格", "不合格", 3),
|
||
("calibration_status", "无需校准", "无需校准", 4),
|
||
# 维修状态
|
||
("repair_status", "待处理", "待处理", 1),
|
||
("repair_status", "处理中", "处理中", 2),
|
||
("repair_status", "已处理", "已处理", 3),
|
||
# 维修优先级
|
||
("repair_priority", "高", "高", 1),
|
||
("repair_priority", "中", "中", 2),
|
||
("repair_priority", "低", "低", 3),
|
||
# 维修故障类型
|
||
("repair_fault_type", "板卡故障", "板卡故障", 1),
|
||
("repair_fault_type", "固件异常", "固件异常", 2),
|
||
("repair_fault_type", "通信故障", "通信故障", 3),
|
||
("repair_fault_type", "电源故障", "电源故障", 4),
|
||
("repair_fault_type", "传感器故障", "传感器故障", 5),
|
||
("repair_fault_type", "其他", "其他", 6),
|
||
# 维修处理动作
|
||
("repair_action", "更换板卡", "更换板卡", 1),
|
||
("repair_action", "固件修复", "固件修复", 2),
|
||
("repair_action", "参数重置", "参数重置", 3),
|
||
("repair_action", "其他处理", "其他处理", 4),
|
||
# 报废状态
|
||
("scrap_status", "待审批", "待审批", 1),
|
||
("scrap_status", "审批中", "审批中", 2),
|
||
("scrap_status", "已审批", "已审批", 3),
|
||
("scrap_status", "已驳回", "已驳回", 4),
|
||
("scrap_status", "回收中", "回收中", 5),
|
||
("scrap_status", "已回收", "已回收", 6),
|
||
# 报废流程步骤
|
||
("scrap_step", "申请报废", "申请报废", 1),
|
||
("scrap_step", "主管审批", "主管审批", 2),
|
||
("scrap_step", "物料检测", "物料检测", 3),
|
||
("scrap_step", "回收入库", "回收入库", 4),
|
||
("scrap_step", "报废完成", "报废完成", 5),
|
||
# 固件升级类型
|
||
("firmware_upgrade_type", "可选", "可选", 1),
|
||
("firmware_upgrade_type", "强制", "强制", 2),
|
||
# 注册测试状态
|
||
("registration_test_status", "装配中", "装配中", 1),
|
||
("registration_test_status", "测试通过", "测试通过", 2),
|
||
("registration_test_status", "测试不通过", "测试不通过", 3),
|
||
# 配置文件电压选项
|
||
("config_voltage", "500V", "500V", 1),
|
||
("config_voltage", "800V", "800V", 2),
|
||
("config_voltage", "1000V", "1000V", 3),
|
||
("config_voltage", "1200V", "1200V", 4),
|
||
("config_voltage", "1500V", "1500V", 5),
|
||
# 配置文件电流选项
|
||
("config_current", "2A", "2A", 1),
|
||
("config_current", "5A", "5A", 2),
|
||
("config_current", "8A", "8A", 3),
|
||
("config_current", "10A", "10A", 4),
|
||
("config_current", "15A", "15A", 5),
|
||
# 配置文件波形选项
|
||
("config_waveform", "0+0-", "0+0-", 1),
|
||
("config_waveform", "+0-0", "+0-0", 2),
|
||
("config_waveform", "+-", "+-", 3),
|
||
# 配置文件脉冲宽度选项
|
||
("config_pulse_width", "0.25s/0.5s/1s/2s/4s/8s", "0.25s/0.5s/1s/2s/4s/8s", 1),
|
||
("config_pulse_width", "0.25s/0.5s/1s/2s/4s/8s/16s/32s/64s", "0.25s/0.5s/1s/2s/4s/8s/16s/32s/64s", 2),
|
||
# 配置文件通道数选项
|
||
("config_channels", "1", "1", 1),
|
||
("config_channels", "6", "6", 2),
|
||
("config_channels", "12", "12", 3),
|
||
# 配置文件采样率选项
|
||
("config_sample_rate", "50Hz/60Hz", "50Hz/60Hz", 1),
|
||
("config_sample_rate", "50Hz/60Hz/100Hz/1000Hz", "50Hz/60Hz/100Hz/1000Hz", 2),
|
||
# 配置文件电压范围选项
|
||
("config_voltage_range", "±2.5V", "±2.5V", 1),
|
||
("config_voltage_range", "±2.5V/±80V", "±2.5V/±80V", 2),
|
||
("config_voltage_range", "±80V/±600V", "±80V/±600V", 3),
|
||
# APP平台类型
|
||
("app_platform", "1", "iOS", 1),
|
||
("app_platform", "2", "Android", 2),
|
||
("app_platform", "3", "HarmonyOS", 3),
|
||
("app_platform", "4", "Windows", 4),
|
||
("app_platform", "5", "macOS", 5),
|
||
("app_platform", "6", "Linux", 6),
|
||
("app_platform", "7", "Web", 7),
|
||
]
|
||
for cat, code, label, sort in demo_refs:
|
||
db.execute(
|
||
"INSERT OR IGNORE INTO reference_values (category, code, label, sort_order) VALUES (?, ?, ?, ?)",
|
||
(cat, code, label, sort)
|
||
)
|
||
|
||
db.commit()
|
||
print("[DB] 演示数据已插入")
|
||
|
||
|
||
def scan_firmware_directory():
|
||
"""
|
||
扫描 FIRMWARE_PUBLIC_DIR 目录，自动发现固件文件。
|
||
返回列表，每项包含：type, folder, file_name, file_path, file_size, version
|
||
"""
|
||
results = []
|
||
base_dir = FIRMWARE_PUBLIC_DIR
|
||
if not os.path.isdir(base_dir):
|
||
return results
|
||
|
||
type_map = {
|
||
"CJB": "采集板",
|
||
"FSB": "发射板",
|
||
"XCL": "主协板",
|
||
}
|
||
|
||
# 扫描子目录
|
||
for folder, fw_type in type_map.items():
|
||
folder_path = os.path.join(base_dir, folder)
|
||
if not os.path.isdir(folder_path):
|
||
continue
|
||
for fname in os.listdir(folder_path):
|
||
fpath = os.path.join(folder_path, fname)
|
||
if not os.path.isfile(fpath):
|
||
continue
|
||
size = os.path.getsize(fpath)
|
||
# 尝试从文件名解析版本，如 CJB_2_8(Checksum=0xc5b1).bin -> 2.8
|
||
version = "-"
|
||
if "_" in fname:
|
||
parts = fname.split("_")
|
||
if len(parts) >= 3:
|
||
try:
|
||
major = parts[1]
|
||
minor = parts[2].split("(")[0].split(".")[0]
|
||
version = f"{major}.{minor}"
|
||
except Exception:
|
||
pass
|
||
results.append({
|
||
"type": fw_type,
|
||
"folder": folder,
|
||
"file_name": fname,
|
||
"file_path": fpath,
|
||
"file_size": size,
|
||
"version": version,
|
||
})
|
||
|
||
# 扫描根目录（主机服务包）
|
||
for fname in os.listdir(base_dir):
|
||
fpath = os.path.join(base_dir, fname)
|
||
if not os.path.isfile(fpath):
|
||
continue
|
||
if fname.startswith("package_arm") and fname.endswith(".tar.gz"):
|
||
size = os.path.getsize(fpath)
|
||
# 尝试从文件名解析版本，如 package_arm_20260507.tar.gz -> 2026.05.07
|
||
version = "-"
|
||
if fname.startswith("package_arm_"):
|
||
ver_part = fname[len("package_arm_"):].replace(".tar.gz", "")
|
||
if len(ver_part) == 8 and ver_part.isdigit():
|
||
version = f"{ver_part[:4]}.{ver_part[4:6]}.{ver_part[6:8]}"
|
||
else:
|
||
version = ver_part
|
||
results.append({
|
||
"type": "主机服务",
|
||
"folder": "",
|
||
"file_name": fname,
|
||
"file_path": fpath,
|
||
"file_size": size,
|
||
"version": version,
|
||
})
|
||
|
||
return results
|