810 lines
25 KiB
PHP
810 lines
25 KiB
PHP
<?php
|
||
/**
|
||
* SecHub 数据库管理类
|
||
* 负责JSON数据到SQLite的转换和管理
|
||
*/
|
||
|
||
class SecHubDatabase {
|
||
private $dbPath;
|
||
private $jsonDir;
|
||
private $db;
|
||
private $needsInitialSync = false;
|
||
|
||
public function __construct($dbPath, $jsonDir) {
|
||
$this->dbPath = $dbPath;
|
||
$this->jsonDir = $jsonDir;
|
||
$this->initDatabase();
|
||
}
|
||
|
||
/**
|
||
* 初始化数据库连接
|
||
*/
|
||
private function initDatabase() {
|
||
try {
|
||
// 检查数据库文件是否存在
|
||
$dbExists = file_exists($this->dbPath);
|
||
|
||
$this->db = new PDO('sqlite:' . $this->dbPath);
|
||
$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
||
$this->db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
|
||
|
||
// 创建同步日志表
|
||
$this->createSyncLogTable();
|
||
|
||
// 如果数据库文件是新创建的，或者没有任何业务数据表，标记需要同步
|
||
if (!$dbExists || $this->isEmptyDatabase()) {
|
||
$this->needsInitialSync = true;
|
||
}
|
||
} catch (PDOException $e) {
|
||
error_log("数据库连接失败: " . $e->getMessage());
|
||
throw $e;
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 检查数据库是否为空（没有业务数据表）
|
||
*/
|
||
private function isEmptyDatabase() {
|
||
try {
|
||
$sql = "SELECT count(*) as table_count FROM sqlite_master
|
||
WHERE type='table'
|
||
AND name NOT LIKE 'sqlite_%'
|
||
AND name != 'json_sync_log'";
|
||
$stmt = $this->db->query($sql);
|
||
$result = $stmt->fetch();
|
||
return $result['table_count'] == 0;
|
||
} catch (Exception $e) {
|
||
return true;
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 创建同步日志表
|
||
*/
|
||
private function createSyncLogTable() {
|
||
$sql = "CREATE TABLE IF NOT EXISTS json_sync_log (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
json_filename TEXT UNIQUE NOT NULL,
|
||
table_name TEXT NOT NULL,
|
||
section_no INTEGER DEFAULT 0,
|
||
last_sync_time DATETIME NOT NULL,
|
||
json_file_mtime INTEGER NOT NULL,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
)";
|
||
|
||
$this->db->exec($sql);
|
||
}
|
||
|
||
/**
|
||
* 检查并同步JSON数据到数据库
|
||
*/
|
||
public function syncJsonToDatabase() {
|
||
// 如果是初始同步，强制同步所有文件
|
||
if ($this->needsInitialSync) {
|
||
$jsonFiles = glob($this->jsonDir . '*.json');
|
||
|
||
foreach ($jsonFiles as $file) {
|
||
$this->syncSingleFile($file);
|
||
}
|
||
$this->needsInitialSync = false;
|
||
return;
|
||
}
|
||
|
||
// 正常增量同步
|
||
$jsonFiles = glob($this->jsonDir . '*.json');
|
||
|
||
foreach ($jsonFiles as $file) {
|
||
$this->syncSingleFile($file);
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 同步单个JSON文件到数据库
|
||
*/
|
||
private function syncSingleFile($filePath) {
|
||
$filename = basename($filePath);
|
||
$tableName = pathinfo($filename, PATHINFO_FILENAME);
|
||
|
||
// 检查是否需要更新
|
||
if (!$this->shouldUpdate($filePath, $tableName)) {
|
||
return;
|
||
}
|
||
|
||
// 读取JSON数据
|
||
$data = $this->loadJsonData($filePath);
|
||
if (empty($data)) {
|
||
return;
|
||
}
|
||
|
||
// 获取排序号（从第一个数据项的 no 字段）
|
||
$sectionNo = $data[0]['no'] ?? 0;
|
||
|
||
// 创建或更新表
|
||
$this->createTable($tableName, $data[0]);
|
||
|
||
// 清空旧数据
|
||
$this->clearTable($tableName);
|
||
|
||
// 插入新数据（跳过第一个section项）
|
||
$items = array_slice($data, 1);
|
||
foreach ($items as $item) {
|
||
$this->insertItem($tableName, $item, $data[0]['section'] ?? $tableName);
|
||
}
|
||
|
||
// 更新同步日志（包含排序号）
|
||
$this->updateSyncLog($filename, $tableName, $sectionNo);
|
||
}
|
||
|
||
/**
|
||
* 更新同步日志
|
||
*/
|
||
private function updateSyncLog($filename, $tableName, $sectionNo = 0) {
|
||
$jsonFile = $this->jsonDir . $filename;
|
||
$jsonModified = filemtime($jsonFile);
|
||
$syncTime = date('Y-m-d H:i:s');
|
||
|
||
// 检查是否已存在记录
|
||
$sql = "SELECT id FROM json_sync_log WHERE json_filename = :filename";
|
||
$stmt = $this->db->prepare($sql);
|
||
$stmt->execute([':filename' => $filename]);
|
||
$exists = $stmt->fetch();
|
||
|
||
if ($exists) {
|
||
// 更新现有记录
|
||
$sql = "UPDATE json_sync_log
|
||
SET table_name = :table_name,
|
||
section_no = :section_no,
|
||
last_sync_time = :sync_time,
|
||
json_file_mtime = :mtime
|
||
WHERE json_filename = :filename";
|
||
} else {
|
||
// 插入新记录
|
||
$sql = "INSERT INTO json_sync_log (json_filename, table_name, section_no, last_sync_time, json_file_mtime)
|
||
VALUES (:filename, :table_name, :section_no, :sync_time, :mtime)";
|
||
}
|
||
|
||
$stmt = $this->db->prepare($sql);
|
||
$stmt->execute([
|
||
':filename' => $filename,
|
||
':table_name' => $tableName,
|
||
':section_no' => $sectionNo,
|
||
':sync_time' => $syncTime,
|
||
':mtime' => $jsonModified
|
||
]);
|
||
}
|
||
|
||
/**
|
||
* 判断是否需要更新
|
||
*/
|
||
private function shouldUpdate($jsonFile, $tableName) {
|
||
$filename = basename($jsonFile);
|
||
$jsonModified = filemtime($jsonFile);
|
||
|
||
// 查询该JSON文件的同步记录
|
||
$sql = "SELECT * FROM json_sync_log WHERE json_filename = :filename";
|
||
$stmt = $this->db->prepare($sql);
|
||
$stmt->execute([':filename' => $filename]);
|
||
$log = $stmt->fetch();
|
||
|
||
// 如果没有同步记录，需要更新
|
||
if (!$log) {
|
||
return true;
|
||
}
|
||
|
||
// 计算时间差（秒）
|
||
$timeDiff = $jsonModified - $log['json_file_mtime'];
|
||
|
||
// 如果JSON文件修改时间比记录的晚至少5分钟（300秒），则需要更新
|
||
if ($timeDiff >= 300) {
|
||
return true;
|
||
}
|
||
|
||
// 否则不需要更新
|
||
return false;
|
||
}
|
||
|
||
|
||
/**
|
||
* 加载JSON数据
|
||
*/
|
||
private function loadJsonData($filePath) {
|
||
if (!file_exists($filePath)) {
|
||
return [];
|
||
}
|
||
|
||
$content = file_get_contents($filePath);
|
||
if ($content === false) {
|
||
return [];
|
||
}
|
||
|
||
$data = json_decode($content, true);
|
||
if (json_last_error() !== JSON_ERROR_NONE) {
|
||
error_log("JSON解析错误: " . json_last_error_msg());
|
||
return [];
|
||
}
|
||
|
||
return is_array($data) ? $data : [];
|
||
}
|
||
|
||
/**
|
||
* 创建数据表
|
||
*/
|
||
private function createTable($tableName, $firstItem) {
|
||
// 清理表名，只保留字母、数字和下划线
|
||
$tableName = preg_replace('/[^a-zA-Z0-9_]/', '_', $tableName);
|
||
|
||
$sql = "CREATE TABLE IF NOT EXISTS {$tableName} (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
section TEXT NOT NULL,
|
||
name TEXT NOT NULL,
|
||
url TEXT,
|
||
description TEXT,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
)";
|
||
|
||
$this->db->exec($sql);
|
||
}
|
||
|
||
/**
|
||
* 清空表数据
|
||
*/
|
||
private function clearTable($tableName) {
|
||
$tableName = preg_replace('/[^a-zA-Z0-9_]/', '_', $tableName);
|
||
$this->db->exec("DELETE FROM {$tableName}");
|
||
}
|
||
|
||
/**
|
||
* 插入数据项
|
||
*/
|
||
private function insertItem($tableName, $item, $section) {
|
||
$tableName = preg_replace('/[^a-zA-Z0-9_]/', '_', $tableName);
|
||
|
||
$sql = "INSERT INTO {$tableName} (section, name, url, description)
|
||
VALUES (:section, :name, :url, :description)";
|
||
|
||
$stmt = $this->db->prepare($sql);
|
||
$stmt->execute([
|
||
':section' => $section,
|
||
':name' => $item['name'] ?? '',
|
||
':url' => $item['url'] ?? '',
|
||
':description' => $item['description'] ?? ''
|
||
]);
|
||
}
|
||
|
||
/**
|
||
* 全局搜索
|
||
*/
|
||
public function globalSearch($keyword) {
|
||
if (empty($keyword)) {
|
||
return [];
|
||
}
|
||
|
||
$tables = $this->getAllTables();
|
||
$results = [];
|
||
|
||
foreach ($tables as $table) {
|
||
$tableName = $table['name'];
|
||
$sql = "SELECT *, '{$tableName}' as source_table FROM {$tableName}
|
||
WHERE name LIKE :keyword
|
||
OR description LIKE :keyword
|
||
OR url LIKE :keyword
|
||
ORDER BY name";
|
||
|
||
$stmt = $this->db->prepare($sql);
|
||
$stmt->execute([':keyword' => '%' . $keyword . '%']);
|
||
$items = $stmt->fetchAll();
|
||
|
||
if (!empty($items)) {
|
||
$results[$tableName] = [
|
||
'section' => $items[0]['section'] ?? $tableName,
|
||
'items' => $items
|
||
];
|
||
}
|
||
}
|
||
|
||
return $results;
|
||
}
|
||
|
||
/**
|
||
* 按栏目搜索
|
||
*/
|
||
public function searchBySection($section, $keyword) {
|
||
if (empty($keyword)) {
|
||
return [];
|
||
}
|
||
|
||
$tables = $this->getAllTables();
|
||
$results = [];
|
||
|
||
foreach ($tables as $table) {
|
||
$tableName = $table['name'];
|
||
$sql = "SELECT * FROM {$tableName}
|
||
WHERE section = :section
|
||
AND (name LIKE :keyword
|
||
OR description LIKE :keyword
|
||
OR url LIKE :keyword)
|
||
ORDER BY name";
|
||
|
||
$stmt = $this->db->prepare($sql);
|
||
$stmt->execute([
|
||
':section' => $section,
|
||
':keyword' => '%' . $keyword . '%'
|
||
]);
|
||
$items = $stmt->fetchAll();
|
||
|
||
if (!empty($items)) {
|
||
$results[] = $items;
|
||
}
|
||
}
|
||
|
||
return array_merge(...$results);
|
||
}
|
||
|
||
/**
|
||
* 获取所有栏目配置
|
||
*/
|
||
public function getSectionsConfig() {
|
||
$tables = $this->getAllTables();
|
||
$sections = [];
|
||
|
||
foreach ($tables as $table) {
|
||
$tableName = $table['name'];
|
||
|
||
// 从同步日志中获取排序号
|
||
$sql = "SELECT section_no FROM json_sync_log WHERE table_name = :table_name LIMIT 1";
|
||
$stmt = $this->db->prepare($sql);
|
||
$stmt->execute([':table_name' => $tableName]);
|
||
$log = $stmt->fetch();
|
||
$sectionNo = $log ? $log['section_no'] : 0;
|
||
|
||
// 获取栏目名称
|
||
$sql = "SELECT DISTINCT section FROM {$tableName} LIMIT 1";
|
||
$stmt = $this->db->query($sql);
|
||
$row = $stmt->fetch();
|
||
|
||
if ($row) {
|
||
$sections[$tableName] = [
|
||
'title' => $row['section'],
|
||
'table' => $tableName,
|
||
'no' => $sectionNo
|
||
];
|
||
}
|
||
}
|
||
|
||
// 按 no 字段排序
|
||
uasort($sections, function($a, $b) {
|
||
return ($a['no'] ?? 0) - ($b['no'] ?? 0);
|
||
});
|
||
|
||
return $sections;
|
||
}
|
||
|
||
/**
|
||
* 获取指定栏目的所有项目
|
||
*/
|
||
public function getItemsBySection($tableName) {
|
||
if (!$this->validateTableName($tableName)) {
|
||
return [];
|
||
}
|
||
$tableName = preg_replace('/[^a-zA-Z0-9_]/', '_', $tableName);
|
||
$sql = "SELECT * FROM {$tableName} ORDER BY name";
|
||
$stmt = $this->db->query($sql);
|
||
return $stmt->fetchAll();
|
||
}
|
||
|
||
/**
|
||
* 获取所有表名
|
||
*/
|
||
private function getAllTables() {
|
||
$sql = "SELECT name FROM sqlite_master
|
||
WHERE type='table'
|
||
AND name NOT LIKE 'sqlite_%'
|
||
AND name != 'json_sync_log'
|
||
ORDER BY name";
|
||
$stmt = $this->db->query($sql);
|
||
return $stmt->fetchAll();
|
||
}
|
||
|
||
/**
|
||
* 关闭数据库连接
|
||
*/
|
||
public function close() {
|
||
$this->db = null;
|
||
}
|
||
|
||
// ======================== 表名安全验证 ========================
|
||
|
||
/**
|
||
* 验证表名是否存在于数据库中（防SQL注入）
|
||
*/
|
||
private function validateTableName($tableName) {
|
||
$tables = $this->getAllTables();
|
||
$validNames = array_column($tables, 'name');
|
||
return in_array($tableName, $validNames, true);
|
||
}
|
||
|
||
// ======================== JSON 文件操作 ========================
|
||
|
||
/**
|
||
* 读取JSON文件并解析
|
||
* @return array|null
|
||
*/
|
||
private function readJsonArray($filePath) {
|
||
if (!file_exists($filePath)) return null;
|
||
$content = file_get_contents($filePath);
|
||
if ($content === false) return null;
|
||
$data = json_decode($content, true);
|
||
if (json_last_error() !== JSON_ERROR_NONE) return null;
|
||
return is_array($data) ? $data : null;
|
||
}
|
||
|
||
/**
|
||
* 写入JSON文件（保持格式）
|
||
*/
|
||
private function writeJsonArray($filePath, $data) {
|
||
$json = json_encode($data, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES);
|
||
return file_put_contents($filePath, $json . "\n") !== false;
|
||
}
|
||
|
||
/**
|
||
* 从表名获取JSON文件路径
|
||
*/
|
||
private function getJsonFilePath($tableName) {
|
||
return $this->jsonDir . $tableName . '.json';
|
||
}
|
||
|
||
/**
|
||
* 获取所有栏目带详细信息
|
||
* @return array 每个元素包含 name, title, no, item_count, filename
|
||
*/
|
||
public function getAllSectionsWithInfo() {
|
||
$tables = $this->getAllTables();
|
||
$sections = [];
|
||
|
||
foreach ($tables as $table) {
|
||
$info = $this->getSectionInfo($table['name']);
|
||
if ($info) {
|
||
$sections[] = $info;
|
||
}
|
||
}
|
||
|
||
// 按 no 排序
|
||
usort($sections, function($a, $b) {
|
||
return ($a['no'] ?? 0) - ($b['no'] ?? 0);
|
||
});
|
||
|
||
return $sections;
|
||
}
|
||
|
||
/**
|
||
* 获取单个栏目配置信息
|
||
* @return array|null
|
||
*/
|
||
public function getSectionInfo($tableName) {
|
||
if (!$this->validateTableName($tableName)) return null;
|
||
|
||
$filePath = $this->getJsonFilePath($tableName);
|
||
$data = $this->readJsonArray($filePath);
|
||
if (!$data || empty($data)) return null;
|
||
|
||
$meta = $data[0];
|
||
return [
|
||
'name' => $tableName,
|
||
'title' => $meta['section'] ?? $tableName,
|
||
'no' => $meta['no'] ?? 0,
|
||
'item_count' => count($data) - 1,
|
||
'filename' => $tableName . '.json'
|
||
];
|
||
}
|
||
|
||
// ======================== 工具条目 CRUD ========================
|
||
|
||
/**
|
||
* 在栏目中添加工具条目
|
||
*/
|
||
public function addItemToSection($tableName, $name, $url, $description) {
|
||
if (!$this->validateTableName($tableName)) {
|
||
return ['success' => false, 'error' => '无效的栏目'];
|
||
}
|
||
|
||
$filePath = $this->getJsonFilePath($tableName);
|
||
$data = $this->readJsonArray($filePath);
|
||
if (!$data) {
|
||
return ['success' => false, 'error' => '读取JSON文件失败'];
|
||
}
|
||
|
||
$data[] = [
|
||
'name' => $name,
|
||
'url' => $url,
|
||
'description' => $description
|
||
];
|
||
|
||
if (!$this->writeJsonArray($filePath, $data)) {
|
||
return ['success' => false, 'error' => '写入JSON文件失败'];
|
||
}
|
||
|
||
$this->syncSingleFile($filePath);
|
||
return ['success' => true];
|
||
}
|
||
|
||
/**
|
||
* 更新栏目中的工具条目
|
||
* @param string $tableName 表名
|
||
* @param int $itemIndex 工具在JSON中的索引（0表示第一个工具项）
|
||
*/
|
||
public function updateItemInSection($tableName, $itemIndex, $name, $url, $description) {
|
||
if (!$this->validateTableName($tableName)) {
|
||
return ['success' => false, 'error' => '无效的栏目'];
|
||
}
|
||
|
||
$filePath = $this->getJsonFilePath($tableName);
|
||
$data = $this->readJsonArray($filePath);
|
||
if (!$data) {
|
||
return ['success' => false, 'error' => '读取JSON文件失败'];
|
||
}
|
||
|
||
// itemIndex 从 0 开始，对应 JSON 数组中的 data[1], data[2], ...
|
||
$arrayIndex = $itemIndex + 1;
|
||
if (!isset($data[$arrayIndex])) {
|
||
return ['success' => false, 'error' => '工具条目不存在'];
|
||
}
|
||
|
||
$data[$arrayIndex]['name'] = $name;
|
||
$data[$arrayIndex]['url'] = $url;
|
||
$data[$arrayIndex]['description'] = $description;
|
||
|
||
if (!$this->writeJsonArray($filePath, $data)) {
|
||
return ['success' => false, 'error' => '写入JSON文件失败'];
|
||
}
|
||
|
||
$this->syncSingleFile($filePath);
|
||
return ['success' => true];
|
||
}
|
||
|
||
/**
|
||
* 删除栏目中的工具条目
|
||
*/
|
||
public function deleteItemFromSection($tableName, $itemIndex) {
|
||
if (!$this->validateTableName($tableName)) {
|
||
return ['success' => false, 'error' => '无效的栏目'];
|
||
}
|
||
|
||
$filePath = $this->getJsonFilePath($tableName);
|
||
$data = $this->readJsonArray($filePath);
|
||
if (!$data) {
|
||
return ['success' => false, 'error' => '读取JSON文件失败'];
|
||
}
|
||
|
||
$arrayIndex = $itemIndex + 1;
|
||
if (!isset($data[$arrayIndex])) {
|
||
return ['success' => false, 'error' => '工具条目不存在'];
|
||
}
|
||
|
||
array_splice($data, $arrayIndex, 1);
|
||
|
||
if (!$this->writeJsonArray($filePath, $data)) {
|
||
return ['success' => false, 'error' => '写入JSON文件失败'];
|
||
}
|
||
|
||
$this->syncSingleFile($filePath);
|
||
return ['success' => true];
|
||
}
|
||
|
||
/**
|
||
* 批量添加工具条目
|
||
* @param string $tableName 表名
|
||
* @param array $items 条目数组，每个元素包含 [name, url, description]
|
||
*/
|
||
public function batchAddItems($tableName, $items) {
|
||
if (!$this->validateTableName($tableName)) {
|
||
return ['success' => false, 'error' => '无效的栏目', 'count' => 0, 'total' => count($items)];
|
||
}
|
||
|
||
$filePath = $this->getJsonFilePath($tableName);
|
||
$data = $this->readJsonArray($filePath);
|
||
if (!$data) {
|
||
return ['success' => false, 'error' => '读取JSON文件失败', 'count' => 0, 'total' => count($items)];
|
||
}
|
||
|
||
$added = 0;
|
||
foreach ($items as $item) {
|
||
$name = trim($item[0] ?? '');
|
||
$url = trim($item[1] ?? '');
|
||
$description = trim($item[2] ?? '');
|
||
|
||
if (empty($name) && empty($url)) continue;
|
||
|
||
$data[] = [
|
||
'name' => $name,
|
||
'url' => $url,
|
||
'description' => $description
|
||
];
|
||
$added++;
|
||
}
|
||
|
||
if ($added === 0) {
|
||
return ['success' => false, 'error' => '没有有效的条目可添加', 'count' => 0, 'total' => count($items)];
|
||
}
|
||
|
||
if (!$this->writeJsonArray($filePath, $data)) {
|
||
return ['success' => false, 'error' => '写入JSON文件失败', 'count' => $added, 'total' => count($items)];
|
||
}
|
||
|
||
$this->syncSingleFile($filePath);
|
||
return ['success' => true, 'count' => $added, 'total' => count($items)];
|
||
}
|
||
|
||
// ======================== 栏目管理 ========================
|
||
|
||
/**
|
||
* 创建新栏目
|
||
* @param string $sectionName 栏目显示名称
|
||
* @return array
|
||
*/
|
||
public function createSection($sectionName) {
|
||
if (empty(trim($sectionName))) {
|
||
return ['success' => false, 'error' => '栏目名称不能为空'];
|
||
}
|
||
|
||
$tableName = $this->generateTableName($sectionName);
|
||
|
||
// 检查JSON文件是否已存在
|
||
if (file_exists($this->getJsonFilePath($tableName))) {
|
||
return ['success' => false, 'error' => '栏目文件已存在: ' . $tableName . '.json'];
|
||
}
|
||
|
||
// 获取最大排序号
|
||
$maxNo = $this->getMaxSectionNo();
|
||
|
||
// 创建JSON数据
|
||
$data = [
|
||
['no' => $maxNo + 1, 'section' => $sectionName]
|
||
];
|
||
|
||
if (!$this->writeJsonArray($this->getJsonFilePath($tableName), $data)) {
|
||
return ['success' => false, 'error' => '创建JSON文件失败'];
|
||
}
|
||
|
||
// 同步到数据库
|
||
try {
|
||
$this->syncSingleFile($this->getJsonFilePath($tableName));
|
||
} catch (Exception $e) {
|
||
// 清理已创建的文件
|
||
unlink($this->getJsonFilePath($tableName));
|
||
return ['success' => false, 'error' => '数据库同步失败: ' . $e->getMessage()];
|
||
}
|
||
|
||
return ['success' => true, 'table_name' => $tableName];
|
||
}
|
||
|
||
/**
|
||
* 删除栏目（删除JSON文件 + 数据库表 + 同步日志）
|
||
*/
|
||
public function removeSection($tableName) {
|
||
if (!$this->validateTableName($tableName)) {
|
||
return ['success' => false, 'error' => '栏目不存在'];
|
||
}
|
||
|
||
// 删除JSON文件
|
||
$filePath = $this->getJsonFilePath($tableName);
|
||
if (file_exists($filePath)) {
|
||
if (!unlink($filePath)) {
|
||
return ['success' => false, 'error' => '删除JSON文件失败'];
|
||
}
|
||
}
|
||
|
||
// 删除数据库表
|
||
$safeName = preg_replace('/[^a-zA-Z0-9_]/', '_', $tableName);
|
||
$this->db->exec("DROP TABLE IF EXISTS {$safeName}");
|
||
|
||
// 删除同步日志
|
||
$stmt = $this->db->prepare("DELETE FROM json_sync_log WHERE table_name = :tn");
|
||
$stmt->execute([':tn' => $tableName]);
|
||
|
||
return ['success' => true];
|
||
}
|
||
|
||
/**
|
||
* 重命名栏目显示名称
|
||
*/
|
||
public function renameSection($tableName, $newSectionName) {
|
||
if (empty(trim($newSectionName))) {
|
||
return ['success' => false, 'error' => '栏目名称不能为空'];
|
||
}
|
||
|
||
if (!$this->validateTableName($tableName)) {
|
||
return ['success' => false, 'error' => '栏目不存在'];
|
||
}
|
||
|
||
$filePath = $this->getJsonFilePath($tableName);
|
||
$data = $this->readJsonArray($filePath);
|
||
if (!$data) {
|
||
return ['success' => false, 'error' => '读取JSON文件失败'];
|
||
}
|
||
|
||
// 更新元数据中的section名称
|
||
$data[0]['section'] = $newSectionName;
|
||
|
||
if (!$this->writeJsonArray($filePath, $data)) {
|
||
return ['success' => false, 'error' => '写入JSON文件失败'];
|
||
}
|
||
|
||
$this->syncSingleFile($filePath);
|
||
return ['success' => true];
|
||
}
|
||
|
||
/**
|
||
* 获取全部栏目数据用于导出
|
||
* @return array
|
||
*/
|
||
public function exportAllSections() {
|
||
$tables = $this->getAllTables();
|
||
$export = [];
|
||
|
||
foreach ($tables as $table) {
|
||
$filePath = $this->getJsonFilePath($table['name']);
|
||
$data = $this->readJsonArray($filePath);
|
||
if ($data) {
|
||
$export[$table['name']] = $data;
|
||
}
|
||
}
|
||
|
||
return $export;
|
||
}
|
||
|
||
/**
|
||
* 获取栏目CSV导出内容
|
||
*/
|
||
public function exportSectionToCsv($tableName) {
|
||
if (!$this->validateTableName($tableName)) return '';
|
||
|
||
$items = $this->getItemsBySection($tableName);
|
||
$info = $this->getSectionInfo($tableName);
|
||
|
||
$csv = "名称,URL,描述\r\n";
|
||
foreach ($items as $item) {
|
||
$name = str_replace('"', '""', $item['name'] ?? '');
|
||
$url = str_replace('"', '""', $item['url'] ?? '');
|
||
$desc = str_replace('"', '""', $item['description'] ?? '');
|
||
$csv .= "\"{$name}\",\"{$url}\",\"{$desc}\"\r\n";
|
||
}
|
||
|
||
return $csv;
|
||
}
|
||
|
||
// ======================== 辅助方法 ========================
|
||
|
||
/**
|
||
* 获取最大排序号
|
||
*/
|
||
private function getMaxSectionNo() {
|
||
$sql = "SELECT MAX(section_no) as max_no FROM json_sync_log";
|
||
$stmt = $this->db->query($sql);
|
||
$row = $stmt->fetch();
|
||
return $row['max_no'] ?? 0;
|
||
}
|
||
|
||
/**
|
||
* 从栏目名称生成表名/文件名
|
||
*/
|
||
private function generateTableName($name) {
|
||
// 先做基本清理
|
||
$name = preg_replace('/[^a-zA-Z0-9_\x{4e00}-\x{9fff}]/u', '_', $name);
|
||
$name = preg_replace('/_+/', '_', $name);
|
||
$name = trim($name, '_');
|
||
|
||
if (empty($name)) $name = 'section';
|
||
|
||
// 如果包含中文，提取英文部分+hash
|
||
if (preg_match('/[\x{4e00}-\x{9fff}]/u', $name)) {
|
||
$eng = preg_replace('/[\x{4e00}-\x{9fff}]/u', '', $name);
|
||
$eng = trim(preg_replace('/_+/', '_', $eng), '_');
|
||
if (empty($eng)) $eng = 'section';
|
||
$hash = substr(md5($name), 0, 4);
|
||
$name = $eng . '_' . $hash;
|
||
}
|
||
|
||
return strtolower($name);
|
||
}
|
||
} |