231 lines
7.7 KiB
PHP
231 lines
7.7 KiB
PHP
<?php
|
||
/**
|
||
* 数据清理脚本
|
||
* 用于定期清理过期的IP限制记录和旧的投稿数据
|
||
* 建议通过定时任务每天运行一次
|
||
*/
|
||
|
||
require_once 'config/database.php';
|
||
|
||
class DataCleanup {
|
||
private $db;
|
||
|
||
public function __construct() {
|
||
$database = new Database();
|
||
$this->db = $database->getConnection();
|
||
}
|
||
|
||
/**
|
||
* 清理过期的IP限制记录
|
||
* 删除7天前的记录
|
||
*/
|
||
public function cleanupIPLimits() {
|
||
try {
|
||
$sql = "DELETE FROM ip_limits WHERE DATE(last_submit) < DATE_SUB(NOW(), INTERVAL 7 DAY)";
|
||
$stmt = $this->db->prepare($sql);
|
||
$result = $stmt->execute();
|
||
|
||
$deletedRows = $stmt->rowCount();
|
||
$this->log("清理IP限制记录: 删除了 {$deletedRows} 条过期记录");
|
||
|
||
return $deletedRows;
|
||
} catch (Exception $e) {
|
||
$this->log("清理IP限制记录失败: " . $e->getMessage(), 'ERROR');
|
||
return false;
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 清理旧的投稿记录
|
||
* 删除6个月前已处理的记录
|
||
*/
|
||
public function cleanupOldSubmissions() {
|
||
$deletedWebsite = 0;
|
||
$deletedApp = 0;
|
||
|
||
try {
|
||
// 清理网址投稿记录
|
||
$sql = "DELETE FROM website_submissions WHERE status != 'pending' AND DATE(created_at) < DATE_SUB(NOW(), INTERVAL 6 MONTH)";
|
||
$stmt = $this->db->prepare($sql);
|
||
$stmt->execute();
|
||
$deletedWebsite = $stmt->rowCount();
|
||
|
||
// 清理APP投稿记录
|
||
$sql = "DELETE FROM app_submissions WHERE status != 'pending' AND DATE(created_at) < DATE_SUB(NOW(), INTERVAL 6 MONTH)";
|
||
$stmt = $this->db->prepare($sql);
|
||
$stmt->execute();
|
||
$deletedApp = $stmt->rowCount();
|
||
|
||
$this->log("清理旧投稿记录: 网址投稿 {$deletedWebsite} 条, APP投稿 {$deletedApp} 条");
|
||
|
||
return ['website' => $deletedWebsite, 'app' => $deletedApp];
|
||
} catch (Exception $e) {
|
||
$this->log("清理旧投稿记录失败: " . $e->getMessage(), 'ERROR');
|
||
return false;
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 优化数据库表
|
||
*/
|
||
public function optimizeTables() {
|
||
try {
|
||
$tables = ['admins', 'website_submissions', 'app_submissions', 'ip_limits'];
|
||
|
||
foreach ($tables as $table) {
|
||
$sql = "OPTIMIZE TABLE {$table}";
|
||
$stmt = $this->db->prepare($sql);
|
||
$stmt->execute();
|
||
}
|
||
|
||
$this->log("数据库表优化完成");
|
||
return true;
|
||
} catch (Exception $e) {
|
||
$this->log("数据库表优化失败: " . $e->getMessage(), 'ERROR');
|
||
return false;
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 获取数据库统计信息
|
||
*/
|
||
public function getStatistics() {
|
||
try {
|
||
$stats = [];
|
||
|
||
// 统计各表记录数
|
||
$tables = [
|
||
'website_submissions' => '网址投稿',
|
||
'app_submissions' => 'APP投稿',
|
||
'ip_limits' => 'IP限制记录',
|
||
'admins' => '管理员账户'
|
||
];
|
||
|
||
foreach ($tables as $table => $name) {
|
||
$sql = "SELECT COUNT(*) as count FROM {$table}";
|
||
$stmt = $this->db->prepare($sql);
|
||
$stmt->execute();
|
||
$result = $stmt->fetch(PDO::FETCH_ASSOC);
|
||
$stats[$name] = $result['count'];
|
||
}
|
||
|
||
// 统计各状态的投稿数量
|
||
$statusStats = [];
|
||
$statuses = ['pending' => '待处理', 'approved' => '已通过', 'rejected' => '已拒绝'];
|
||
|
||
foreach ($statuses as $status => $name) {
|
||
// 网址投稿
|
||
$sql = "SELECT COUNT(*) as count FROM website_submissions WHERE status = ?";
|
||
$stmt = $this->db->prepare($sql);
|
||
$stmt->execute([$status]);
|
||
$result = $stmt->fetch(PDO::FETCH_ASSOC);
|
||
$statusStats["网址投稿-{$name}"] = $result['count'];
|
||
|
||
// APP投稿
|
||
$sql = "SELECT COUNT(*) as count FROM app_submissions WHERE status = ?";
|
||
$stmt = $this->db->prepare($sql);
|
||
$stmt->execute([$status]);
|
||
$result = $stmt->fetch(PDO::FETCH_ASSOC);
|
||
$statusStats["APP投稿-{$name}"] = $result['count'];
|
||
}
|
||
|
||
return array_merge($stats, $statusStats);
|
||
} catch (Exception $e) {
|
||
$this->log("获取统计信息失败: " . $e->getMessage(), 'ERROR');
|
||
return false;
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 记录日志
|
||
*/
|
||
private function log($message, $level = 'INFO') {
|
||
$timestamp = date('Y-m-d H:i:s');
|
||
$logMessage = "[{$timestamp}] [{$level}] {$message}" . PHP_EOL;
|
||
|
||
// 输出到控制台
|
||
echo $logMessage;
|
||
|
||
// 写入日志文件
|
||
$logFile = 'data/cleanup.log';
|
||
if (!file_exists('data')) {
|
||
mkdir('data', 0755, true);
|
||
}
|
||
file_put_contents($logFile, $logMessage, FILE_APPEND | LOCK_EX);
|
||
}
|
||
|
||
/**
|
||
* 执行完整的清理流程
|
||
*/
|
||
public function runFullCleanup() {
|
||
$this->log("开始执行数据清理任务");
|
||
|
||
// 显示清理前的统计信息
|
||
$beforeStats = $this->getStatistics();
|
||
if ($beforeStats) {
|
||
$this->log("清理前统计信息:");
|
||
foreach ($beforeStats as $key => $value) {
|
||
$this->log(" {$key}: {$value}");
|
||
}
|
||
}
|
||
|
||
// 执行清理任务
|
||
$ipCleanup = $this->cleanupIPLimits();
|
||
$submissionCleanup = $this->cleanupOldSubmissions();
|
||
$optimize = $this->optimizeTables();
|
||
|
||
// 显示清理后的统计信息
|
||
$afterStats = $this->getStatistics();
|
||
if ($afterStats) {
|
||
$this->log("清理后统计信息:");
|
||
foreach ($afterStats as $key => $value) {
|
||
$this->log(" {$key}: {$value}");
|
||
}
|
||
}
|
||
|
||
$this->log("数据清理任务完成");
|
||
|
||
return [
|
||
'ip_cleanup' => $ipCleanup,
|
||
'submission_cleanup' => $submissionCleanup,
|
||
'optimize' => $optimize,
|
||
'before_stats' => $beforeStats,
|
||
'after_stats' => $afterStats
|
||
];
|
||
}
|
||
}
|
||
|
||
// 如果直接运行此脚本
|
||
if (php_sapi_name() === 'cli' || !isset($_SERVER['HTTP_HOST'])) {
|
||
$cleanup = new DataCleanup();
|
||
$result = $cleanup->runFullCleanup();
|
||
|
||
echo "\n清理任务执行完成!\n";
|
||
if ($result['ip_cleanup'] !== false) {
|
||
echo "IP限制记录清理: {$result['ip_cleanup']} 条\n";
|
||
}
|
||
if ($result['submission_cleanup'] !== false) {
|
||
echo "投稿记录清理: 网址 {$result['submission_cleanup']['website']} 条, APP {$result['submission_cleanup']['app']} 条\n";
|
||
}
|
||
echo "数据库优化: " . ($result['optimize'] ? '成功' : '失败') . "\n";
|
||
} else {
|
||
// 如果通过Web访问，返回JSON格式结果
|
||
header('Content-Type: application/json; charset=utf-8');
|
||
|
||
// 简单的安全检查
|
||
if (!isset($_GET['token']) || $_GET['token'] !== 'cleanup_token_2024') {
|
||
http_response_code(403);
|
||
echo json_encode(['error' => '访问被拒绝']);
|
||
exit;
|
||
}
|
||
|
||
$cleanup = new DataCleanup();
|
||
$result = $cleanup->runFullCleanup();
|
||
|
||
echo json_encode([
|
||
'success' => true,
|
||
'message' => '清理任务执行完成',
|
||
'data' => $result
|
||
], JSON_UNESCAPED_UNICODE);
|
||
}
|
||
?>
|