594 lines
21 KiB
Python
594 lines
21 KiB
Python
#!/usr/bin/env python3
|
||
# -*- coding: utf-8 -*-
|
||
"""VPS Hub 数据库管理模块"""
|
||
|
||
import os
|
||
import sqlite3
|
||
from datetime import datetime
|
||
|
||
|
||
class DatabaseManager:
|
||
"""数据库管理器"""
|
||
|
||
def __init__(self, db_dir=None):
|
||
if db_dir is None:
|
||
db_dir = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'db')
|
||
|
||
self.db_dir = db_dir
|
||
os.makedirs(db_dir, exist_ok=True)
|
||
|
||
self.vps_db = os.path.join(db_dir, 'vps.db')
|
||
self.vpslist_db = os.path.join(db_dir, 'vpslist.db')
|
||
self.status_db = os.path.join(db_dir, 'status.db')
|
||
|
||
self.init_vps_db()
|
||
self.init_vpslist_db()
|
||
self.init_status_db()
|
||
|
||
def get_connection(self, db_path):
|
||
"""获取数据库连接"""
|
||
conn = sqlite3.connect(db_path)
|
||
conn.row_factory = sqlite3.Row
|
||
return conn
|
||
|
||
def init_vps_db(self):
|
||
"""初始化vps.db - VPS配置表"""
|
||
conn = self.get_connection(self.vps_db)
|
||
cursor = conn.cursor()
|
||
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS configs (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
api_label TEXT NOT NULL UNIQUE,
|
||
site_type TEXT NOT NULL,
|
||
site_url TEXT,
|
||
account TEXT NOT NULL,
|
||
api_key TEXT NOT NULL,
|
||
auto_monitor BOOLEAN DEFAULT 1,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(site_url, account)
|
||
)
|
||
''')
|
||
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
def init_vpslist_db(self):
|
||
"""初始化vpslist.db - VPS列表缓存表"""
|
||
conn = self.get_connection(self.vpslist_db)
|
||
cursor = conn.cursor()
|
||
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS vps_list (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
config_id INTEGER NOT NULL,
|
||
vps_id INTEGER NOT NULL,
|
||
domain TEXT,
|
||
ip_address TEXT,
|
||
product_name TEXT,
|
||
cpu_cores INTEGER,
|
||
memory_size TEXT,
|
||
disk_size TEXT,
|
||
bandwidth TEXT,
|
||
os_type TEXT,
|
||
status TEXT,
|
||
amount TEXT,
|
||
nextduedate INTEGER,
|
||
section BOOLEAN DEFAULT 0,
|
||
last_check TIMESTAMP,
|
||
FOREIGN KEY (config_id) REFERENCES configs(id)
|
||
)
|
||
''')
|
||
|
||
cursor.execute('CREATE INDEX IF NOT EXISTS idx_vps_config ON vps_list(config_id)')
|
||
cursor.execute('CREATE INDEX IF NOT EXISTS idx_vps_vps_id ON vps_list(vps_id)')
|
||
cursor.execute('CREATE INDEX IF NOT EXISTS idx_vps_unique ON vps_list(vps_id, ip_address)')
|
||
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
def init_status_db(self):
|
||
"""初始化status.db - Ping状态记录和摘要统计表"""
|
||
conn = self.get_connection(self.status_db)
|
||
cursor = conn.cursor()
|
||
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS ping_status (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
vps_id INTEGER NOT NULL,
|
||
target TEXT NOT NULL,
|
||
status TEXT NOT NULL,
|
||
latency_ms REAL,
|
||
check_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
)
|
||
''')
|
||
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS vps_summary (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
vps_id INTEGER NOT NULL,
|
||
date DATE NOT NULL,
|
||
avg_latency_ms REAL,
|
||
max_latency_ms REAL,
|
||
min_latency_ms REAL,
|
||
count_under_100 INTEGER DEFAULT 0,
|
||
count_100_to_300 INTEGER DEFAULT 0,
|
||
count_300_to_500 INTEGER DEFAULT 0,
|
||
count_abnormal INTEGER DEFAULT 0,
|
||
availability TEXT,
|
||
UNIQUE(vps_id, date)
|
||
)
|
||
''')
|
||
|
||
cursor.execute('CREATE INDEX IF NOT EXISTS idx_ping_vps ON ping_status(vps_id)')
|
||
cursor.execute('CREATE INDEX IF NOT EXISTS idx_ping_time ON ping_status(check_time)')
|
||
cursor.execute('CREATE INDEX IF NOT EXISTS idx_summary_vps ON vps_summary(vps_id)')
|
||
cursor.execute('CREATE INDEX IF NOT EXISTS idx_summary_date ON vps_summary(date)')
|
||
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
# ==================== vps.db 操作 ====================
|
||
|
||
def add_config(self, api_label, site_type, account, api_key, site_url=None, auto_monitor=True):
|
||
"""添加VPS配置，基于site_url和account去重"""
|
||
conn = self.get_connection(self.vps_db)
|
||
cursor = conn.cursor()
|
||
|
||
try:
|
||
existing = cursor.execute(
|
||
'SELECT id FROM configs WHERE site_url = ? AND account = ?',
|
||
(site_url, account)
|
||
).fetchone()
|
||
|
||
if existing:
|
||
return existing['id']
|
||
|
||
cursor.execute('''
|
||
INSERT INTO configs (api_label, site_type, site_url, account, api_key, auto_monitor)
|
||
VALUES (?, ?, ?, ?, ?, ?)
|
||
''', (api_label, site_type, site_url, account, api_key, auto_monitor))
|
||
|
||
config_id = cursor.lastrowid
|
||
conn.commit()
|
||
return config_id
|
||
except Exception as e:
|
||
conn.rollback()
|
||
raise e
|
||
finally:
|
||
conn.close()
|
||
|
||
def get_all_configs(self):
|
||
"""获取所有配置"""
|
||
conn = self.get_connection(self.vps_db)
|
||
cursor = conn.cursor()
|
||
|
||
cursor.execute('SELECT * FROM configs ORDER BY id')
|
||
configs = [dict(row) for row in cursor.fetchall()]
|
||
|
||
conn.close()
|
||
return configs
|
||
|
||
def get_config_by_id(self, config_id):
|
||
"""根据ID获取配置"""
|
||
conn = self.get_connection(self.vps_db)
|
||
cursor = conn.cursor()
|
||
|
||
cursor.execute('SELECT * FROM configs WHERE id = ?', (config_id,))
|
||
row = cursor.fetchone()
|
||
|
||
conn.close()
|
||
return dict(row) if row else None
|
||
|
||
def update_config(self, config_id, **kwargs):
|
||
"""更新配置"""
|
||
if not kwargs:
|
||
return False
|
||
|
||
conn = self.get_connection(self.vps_db)
|
||
cursor = conn.cursor()
|
||
|
||
fields = ', '.join([f"{key} = ?" for key in kwargs.keys()])
|
||
values = list(kwargs.values())
|
||
values.append(config_id)
|
||
|
||
cursor.execute(f'UPDATE configs SET {fields}, updated_at = CURRENT_TIMESTAMP WHERE id = ?', values)
|
||
|
||
affected = cursor.rowcount
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
return affected > 0
|
||
|
||
def delete_config(self, config_id):
|
||
"""删除配置"""
|
||
conn = self.get_connection(self.vps_db)
|
||
cursor = conn.cursor()
|
||
|
||
cursor.execute('DELETE FROM configs WHERE id = ?', (config_id,))
|
||
|
||
affected = cursor.rowcount
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
return affected > 0
|
||
|
||
def reset_config_ids(self):
|
||
"""重置configs表的ID序列，从1开始连续编号"""
|
||
conn = self.get_connection(self.vps_db)
|
||
cursor = conn.cursor()
|
||
|
||
try:
|
||
cursor.execute('SELECT * FROM configs ORDER BY id')
|
||
configs = [dict(row) for row in cursor.fetchall()]
|
||
|
||
if not configs:
|
||
return True
|
||
|
||
cursor.execute('DROP TABLE IF EXISTS configs_backup')
|
||
cursor.execute('''
|
||
CREATE TABLE configs_backup AS SELECT * FROM configs
|
||
''')
|
||
|
||
cursor.execute('DELETE FROM configs')
|
||
cursor.execute("DELETE FROM sqlite_sequence WHERE name='configs'")
|
||
|
||
for config in configs:
|
||
cursor.execute('''
|
||
INSERT INTO configs (api_label, site_type, site_url, account, api_key, auto_monitor, created_at, updated_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
|
||
''', (
|
||
config['api_label'],
|
||
config['site_type'],
|
||
config['site_url'],
|
||
config['account'],
|
||
config['api_key'],
|
||
config['auto_monitor'],
|
||
config['created_at'],
|
||
config['updated_at']
|
||
))
|
||
|
||
cursor.execute('DROP TABLE IF EXISTS configs_backup')
|
||
conn.commit()
|
||
return True
|
||
except Exception as e:
|
||
conn.rollback()
|
||
raise e
|
||
finally:
|
||
conn.close()
|
||
|
||
# ==================== vpslist.db 操作 ====================
|
||
|
||
def get_next_available_id(self):
|
||
"""获取下一个可用的ID（填补空缺）"""
|
||
conn = self.get_connection(self.vpslist_db)
|
||
cursor = conn.cursor()
|
||
|
||
cursor.execute('SELECT id FROM vps_list ORDER BY id')
|
||
ids = [row['id'] for row in cursor.fetchall()]
|
||
conn.close()
|
||
|
||
if not ids:
|
||
return 1
|
||
|
||
for i in range(1, max(ids) + 1):
|
||
if i not in ids:
|
||
return i
|
||
|
||
return None
|
||
|
||
def add_vps(self, config_id, vps_id, domain=None, ip_address=None, product_name=None, section=False, custom_id=None):
|
||
"""添加VPS到列表，支持指定ID以填补空缺"""
|
||
conn = self.get_connection(self.vpslist_db)
|
||
cursor = conn.cursor()
|
||
|
||
if custom_id is None:
|
||
custom_id = self.get_next_available_id()
|
||
|
||
if custom_id:
|
||
cursor.execute('''
|
||
INSERT INTO vps_list (id, config_id, vps_id, domain, ip_address, product_name, section, last_check)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
|
||
''', (custom_id, config_id, vps_id, domain, ip_address, product_name, section))
|
||
else:
|
||
cursor.execute('''
|
||
INSERT INTO vps_list (config_id, vps_id, domain, ip_address, product_name, section, last_check)
|
||
VALUES (?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
|
||
''', (config_id, vps_id, domain, ip_address, product_name, section))
|
||
|
||
record_id = cursor.lastrowid
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
return record_id
|
||
|
||
def batch_add_vps(self, vps_list):
|
||
"""批量添加VPS，基于vps_id和ip_address去重"""
|
||
conn = self.get_connection(self.vpslist_db)
|
||
cursor = conn.cursor()
|
||
|
||
for vps in vps_list:
|
||
existing = cursor.execute(
|
||
'SELECT id FROM vps_list WHERE vps_id = ? AND ip_address = ?',
|
||
(vps['vps_id'], vps.get('ip_address'))
|
||
).fetchone()
|
||
|
||
if existing:
|
||
continue
|
||
|
||
custom_id = self.get_next_available_id()
|
||
|
||
if custom_id:
|
||
cursor.execute('''
|
||
INSERT INTO vps_list
|
||
(id, config_id, vps_id, domain, ip_address, product_name, cpu_cores, memory_size, disk_size, bandwidth, os_type, amount, nextduedate, section, last_check)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
|
||
''', (
|
||
custom_id,
|
||
vps['config_id'],
|
||
vps['vps_id'],
|
||
vps.get('domain'),
|
||
vps.get('ip_address'),
|
||
vps.get('product_name'),
|
||
vps.get('cpu_cores'),
|
||
vps.get('memory_size'),
|
||
vps.get('disk_size'),
|
||
vps.get('bandwidth'),
|
||
vps.get('os_type'),
|
||
vps.get('amount'),
|
||
vps.get('nextduedate'),
|
||
vps.get('section', False)
|
||
))
|
||
else:
|
||
cursor.execute('''
|
||
INSERT INTO vps_list
|
||
(config_id, vps_id, domain, ip_address, product_name, cpu_cores, memory_size, disk_size, bandwidth, os_type, amount, nextduedate, section, last_check)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
|
||
''', (
|
||
vps['config_id'],
|
||
vps['vps_id'],
|
||
vps.get('domain'),
|
||
vps.get('ip_address'),
|
||
vps.get('product_name'),
|
||
vps.get('cpu_cores'),
|
||
vps.get('memory_size'),
|
||
vps.get('disk_size'),
|
||
vps.get('bandwidth'),
|
||
vps.get('os_type'),
|
||
vps.get('amount'),
|
||
vps.get('nextduedate'),
|
||
vps.get('section', False)
|
||
))
|
||
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
def get_all_vps(self):
|
||
"""获取所有VPS"""
|
||
conn = self.get_connection(self.vpslist_db)
|
||
cursor = conn.cursor()
|
||
|
||
cursor.execute('SELECT * FROM vps_list ORDER BY config_id, vps_id')
|
||
vps_list = [dict(row) for row in cursor.fetchall()]
|
||
|
||
conn.close()
|
||
return vps_list
|
||
|
||
def get_vps_by_config(self, config_id):
|
||
"""根据配置ID获取VPS列表"""
|
||
conn = self.get_connection(self.vpslist_db)
|
||
cursor = conn.cursor()
|
||
|
||
cursor.execute('SELECT * FROM vps_list WHERE config_id = ? ORDER BY vps_id', (config_id,))
|
||
vps_list = [dict(row) for row in cursor.fetchall()]
|
||
|
||
conn.close()
|
||
return vps_list
|
||
|
||
def update_vps_details(self, vps_id, **kwargs):
|
||
"""更新VPS详细信息"""
|
||
if not kwargs:
|
||
return False
|
||
|
||
conn = self.get_connection(self.vpslist_db)
|
||
cursor = conn.cursor()
|
||
|
||
fields = ', '.join([f"{key} = ?" for key in kwargs.keys()])
|
||
values = list(kwargs.values())
|
||
values.append(vps_id)
|
||
|
||
cursor.execute(f'UPDATE vps_list SET {fields}, last_check = CURRENT_TIMESTAMP WHERE vps_id = ?', values)
|
||
|
||
affected = cursor.rowcount
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
return affected > 0
|
||
|
||
def update_vps_status(self, vps_id, status):
|
||
"""更新VPS状态"""
|
||
return self.update_vps_details(vps_id, status=status)
|
||
|
||
def get_monitored_vps(self):
|
||
"""获取所有标记为需要监控的VPS"""
|
||
conn = self.get_connection(self.vpslist_db)
|
||
cursor = conn.cursor()
|
||
|
||
cursor.execute('SELECT * FROM vps_list WHERE section = 1 ORDER BY config_id, vps_id')
|
||
vps_list = [dict(row) for row in cursor.fetchall()]
|
||
|
||
conn.close()
|
||
return vps_list
|
||
|
||
def reset_vps_list_ids(self):
|
||
"""重置vps_list表的ID序列，从1开始连续编号"""
|
||
conn = self.get_connection(self.vpslist_db)
|
||
cursor = conn.cursor()
|
||
|
||
try:
|
||
cursor.execute('SELECT * FROM vps_list ORDER BY id')
|
||
vps_items = [dict(row) for row in cursor.fetchall()]
|
||
|
||
if not vps_items:
|
||
return True
|
||
|
||
cursor.execute('DROP TABLE IF EXISTS vps_list_backup')
|
||
cursor.execute('''
|
||
CREATE TABLE vps_list_backup AS SELECT * FROM vps_list
|
||
''')
|
||
|
||
cursor.execute('DELETE FROM vps_list')
|
||
cursor.execute("DELETE FROM sqlite_sequence WHERE name='vps_list'")
|
||
|
||
for vps in vps_items:
|
||
cursor.execute('''
|
||
INSERT INTO vps_list (config_id, vps_id, domain, ip_address, product_name, cpu_cores, memory_size, disk_size, bandwidth, os_type, amount, nextduedate, status, section, last_check)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
''', (
|
||
vps['config_id'],
|
||
vps['vps_id'],
|
||
vps.get('domain'),
|
||
vps.get('ip_address'),
|
||
vps.get('product_name'),
|
||
vps.get('cpu_cores'),
|
||
vps.get('memory_size'),
|
||
vps.get('disk_size'),
|
||
vps.get('bandwidth'),
|
||
vps.get('os_type'),
|
||
vps.get('amount'),
|
||
vps.get('nextduedate'),
|
||
vps.get('status'),
|
||
vps.get('section', 0),
|
||
vps.get('last_check')
|
||
))
|
||
|
||
cursor.execute('DROP TABLE IF EXISTS vps_list_backup')
|
||
conn.commit()
|
||
return True
|
||
except Exception as e:
|
||
conn.rollback()
|
||
raise e
|
||
finally:
|
||
conn.close()
|
||
|
||
# ==================== status.db 操作 ====================
|
||
|
||
def save_ping_status(self, vps_id, target, status, latency_ms=None):
|
||
"""保存Ping状态记录"""
|
||
conn = self.get_connection(self.status_db)
|
||
cursor = conn.cursor()
|
||
|
||
cursor.execute('''
|
||
INSERT INTO ping_status (vps_id, target, status, latency_ms)
|
||
VALUES (?, ?, ?, ?)
|
||
''', (vps_id, target, status, latency_ms))
|
||
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
def get_ping_records(self, vps_id, date=None):
|
||
"""获取Ping记录"""
|
||
conn = self.get_connection(self.status_db)
|
||
cursor = conn.cursor()
|
||
|
||
if date:
|
||
cursor.execute('''
|
||
SELECT * FROM ping_status
|
||
WHERE vps_id = ? AND DATE(check_time) = ?
|
||
ORDER BY check_time
|
||
''', (vps_id, date))
|
||
else:
|
||
cursor.execute('''
|
||
SELECT * FROM ping_status
|
||
WHERE vps_id = ?
|
||
ORDER BY check_time DESC
|
||
''', (vps_id,))
|
||
|
||
records = [dict(row) for row in cursor.fetchall()]
|
||
conn.close()
|
||
|
||
return records
|
||
|
||
def cleanup_old_ping_records(self, days=30):
|
||
"""清理旧的Ping记录"""
|
||
from datetime import timedelta
|
||
|
||
conn = self.get_connection(self.status_db)
|
||
cursor = conn.cursor()
|
||
|
||
cutoff_date = (datetime.now() - timedelta(days=days)).strftime('%Y-%m-%d %H:%M:%S')
|
||
cursor.execute('DELETE FROM ping_status WHERE check_time < ?', (cutoff_date,))
|
||
|
||
deleted = cursor.rowcount
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
return deleted
|
||
|
||
def save_vps_summary(self, vps_id, date, avg_latency, max_latency, min_latency,
|
||
count_under_100, count_100_to_300, count_300_to_500,
|
||
count_abnormal, availability):
|
||
"""保存VPS摘要统计"""
|
||
conn = self.get_connection(self.status_db)
|
||
cursor = conn.cursor()
|
||
|
||
cursor.execute('''
|
||
INSERT OR REPLACE INTO vps_summary
|
||
(vps_id, date, avg_latency_ms, max_latency_ms, min_latency_ms,
|
||
count_under_100, count_100_to_300, count_300_to_500, count_abnormal, availability)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
''', (vps_id, date, avg_latency, max_latency, min_latency,
|
||
count_under_100, count_100_to_300, count_300_to_500,
|
||
count_abnormal, availability))
|
||
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
def get_vps_summary(self, vps_id, date=None):
|
||
"""获取VPS摘要统计"""
|
||
conn = self.get_connection(self.status_db)
|
||
cursor = conn.cursor()
|
||
|
||
if date:
|
||
cursor.execute('SELECT * FROM vps_summary WHERE vps_id = ? AND date = ?', (vps_id, date))
|
||
else:
|
||
cursor.execute('SELECT * FROM vps_summary WHERE vps_id = ? ORDER BY date DESC LIMIT 1', (vps_id,))
|
||
|
||
row = cursor.fetchone()
|
||
conn.close()
|
||
|
||
return dict(row) if row else None
|
||
|
||
def get_all_summaries(self, date=None):
|
||
"""获取所有VPS的摘要统计"""
|
||
conn = self.get_connection(self.status_db)
|
||
cursor = conn.cursor()
|
||
|
||
if date:
|
||
cursor.execute('SELECT * FROM vps_summary WHERE date = ? ORDER BY vps_id', (date,))
|
||
else:
|
||
cursor.execute('''
|
||
SELECT vs.* FROM vps_summary vs
|
||
INNER JOIN (
|
||
SELECT vps_id, MAX(date) as max_date
|
||
FROM vps_summary
|
||
GROUP BY vps_id
|
||
) latest ON vs.vps_id = latest.vps_id AND vs.date = latest.max_date
|
||
ORDER BY vs.vps_id
|
||
''')
|
||
|
||
summaries = [dict(row) for row in cursor.fetchall()]
|
||
conn.close()
|
||
|
||
return summaries
|
||
|
||
|
||
if __name__ == '__main__':
|
||
# 测试代码
|
||
db = DatabaseManager()
|
||
print("数据库初始化完成")
|
||
print(f"vps.db: {db.vps_db}")
|
||
print(f"vpslist.db: {db.vpslist_db}")
|
||
print(f"status.db: {db.status_db}")
|