662 lines
21 KiB
PHP
662 lines
21 KiB
PHP
|
<?php
|
|||
|
|
|
|||
|
|
namespace App\Console\Commands;
|
|||
|
|
|
|||
|
|
use Illuminate\Console\Command;
|
|||
|
|
use Illuminate\Support\Facades\DB;
|
|||
|
|
use Illuminate\Support\Facades\Config;
|
|||
|
|
use Illuminate\Support\Facades\File;
|
|||
|
|
use PDOException;
|
|||
|
|
|
|||
|
|
class RunNlAdminSql extends Command
|
|||
|
|
{
|
|||
|
|
protected $signature = 'sql:run-nl-admin
|
|||
|
|
{--file=public/nl_admin.sql : SQL文件路径}
|
|||
|
|
{--force : 强制执行，跳过确认}
|
|||
|
|
{--database= : 指定数据库名称}
|
|||
|
|
{--charset=utf8mb4 : 数据库字符集}
|
|||
|
|
{--collation=utf8mb4_unicode_ci : 数据库排序规则}';
|
|||
|
|
|
|||
|
|
protected $description = '执行 SQL 文件，自动处理数据库相关事项';
|
|||
|
|
|
|||
|
|
protected $maxFileSize = 50; // 最大文件大小 (MB)
|
|||
|
|
protected $chunkSize = 10; // 分块大小 (MB)
|
|||
|
|
|
|||
|
|
public function handle()
|
|||
|
|
{
|
|||
|
|
$this->info('🚀 开始数据库安装程序');
|
|||
|
|
|
|||
|
|
// 1. 确保基础配置加载
|
|||
|
|
$this->ensureConfigLoaded();
|
|||
|
|
|
|||
|
|
// 2. 优先使用.env配置创建数据库
|
|||
|
|
$filePath = $this->getSqlFilePath();
|
|||
|
|
|
|||
|
|
// 3. 配置数据库连接（如果需要）
|
|||
|
|
$this->ensureDatabaseConnection();
|
|||
|
|
|
|||
|
|
// 4. 处理目标数据库
|
|||
|
|
$this->prepareTargetDatabase();
|
|||
|
|
|
|||
|
|
// 5. 执行SQL文件
|
|||
|
|
$this->executeSql($filePath);
|
|||
|
|
|
|||
|
|
$this->info('✅ 数据库安装成功完成');
|
|||
|
|
return 0;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 确保配置已加载
|
|||
|
|
*/
|
|||
|
|
protected function ensureConfigLoaded(): void
|
|||
|
|
{
|
|||
|
|
$this->call('config:clear');
|
|||
|
|
|
|||
|
|
// 确保数据库配置存在
|
|||
|
|
$envPath = base_path('.env');
|
|||
|
|
if (!file_exists($envPath)) {
|
|||
|
|
File::copy(base_path('.env.example'), $envPath);
|
|||
|
|
$this->info('📄 .env文件已创建（基于.example）');
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 获取SQL文件路径
|
|||
|
|
*/
|
|||
|
|
protected function getSqlFilePath(): string
|
|||
|
|
{
|
|||
|
|
$filePath = base_path($this->option('file'));
|
|||
|
|
|
|||
|
|
// 验证文件是否存在
|
|||
|
|
if (!file_exists($filePath)) {
|
|||
|
|
$this->error("❌ SQL 文件未找到：{$filePath}");
|
|||
|
|
$this->info("💡 请使用 --file 选项指定正确的文件路径");
|
|||
|
|
exit(1);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 检查文件大小
|
|||
|
|
$fileSize = filesize($filePath) / 1024 / 1024; // MB
|
|||
|
|
if ($fileSize > $this->maxFileSize) {
|
|||
|
|
$this->warn("⚠️ 注意：SQL文件较大 (" . round($fileSize, 2) . "MB)，执行可能需要较长时间");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$this->info("📁 SQL文件位置: {$filePath}");
|
|||
|
|
$this->info("📏 文件大小: " . round($fileSize, 2) . 'MB');
|
|||
|
|
|
|||
|
|
return $filePath;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 确保数据库连接可用
|
|||
|
|
*/
|
|||
|
|
protected function ensureDatabaseConnection(): void
|
|||
|
|
{
|
|||
|
|
$this->line('🔌 检查数据库连接...');
|
|||
|
|
$retryCount = 0;
|
|||
|
|
|
|||
|
|
while (true) {
|
|||
|
|
try {
|
|||
|
|
DB::connection()->getPdo();
|
|||
|
|
$this->info('✅ 数据库连接正常');
|
|||
|
|
return;
|
|||
|
|
} catch (\Exception $e) {
|
|||
|
|
$this->error("❌ 数据库连接失败: " . $e->getMessage());
|
|||
|
|
|
|||
|
|
if ($retryCount > 0 || !$this->confirm('❓ 是否配置数据库连接?', true)) {
|
|||
|
|
$this->error('🔌 无法连接数据库，操作终止');
|
|||
|
|
exit(1);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$this->configureDatabase();
|
|||
|
|
$retryCount++;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 配置数据库连接信息（优先使用env默认值）
|
|||
|
|
*/
|
|||
|
|
protected function configureDatabase(): void
|
|||
|
|
{
|
|||
|
|
$this->info('🔧 请提供数据库连接信息（留空使用默认值）');
|
|||
|
|
|
|||
|
|
$currentConfig = Config::get('database.connections.mysql');
|
|||
|
|
|
|||
|
|
$config = [
|
|||
|
|
'DB_HOST' => $this->ask('数据库主机', $currentConfig['host'] ?? '127.0.0.1'),
|
|||
|
|
'DB_PORT' => $this->ask('端口', $currentConfig['port'] ?? '3306'),
|
|||
|
|
'DB_DATABASE' => $this->ask('数据库名', $currentConfig['database'] ?? 'nl_admin'),
|
|||
|
|
'DB_USERNAME' => $this->ask('用户名', $currentConfig['username'] ?? 'root'),
|
|||
|
|
'DB_PASSWORD' => $this->secret('密码') ?: ($currentConfig['password'] ?? ''),
|
|||
|
|
];
|
|||
|
|
|
|||
|
|
// 更新 .env 文件
|
|||
|
|
$this->updateEnvFile($config);
|
|||
|
|
$this->info('📝 .env 文件已更新');
|
|||
|
|
|
|||
|
|
// 刷新配置缓存
|
|||
|
|
$this->call('config:clear');
|
|||
|
|
|
|||
|
|
// 更新运行时配置
|
|||
|
|
foreach ($config as $key => $value) {
|
|||
|
|
$configKey = strtolower(substr($key, 3));
|
|||
|
|
Config::set("database.connections.mysql.{$configKey}", $value);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 测试连接并创建数据库
|
|||
|
|
$this->testDatabaseConnection();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 测试数据库连接并创建数据库（如果需要）
|
|||
|
|
*/
|
|||
|
|
protected function testDatabaseConnection(): void
|
|||
|
|
{
|
|||
|
|
try {
|
|||
|
|
// 测试连接
|
|||
|
|
DB::connection('mysql')->getPdo();
|
|||
|
|
$this->info('✅ 数据库连接测试成功');
|
|||
|
|
} catch (\Exception $e) {
|
|||
|
|
$this->error("❌ 配置后连接测试失败: " . $e->getMessage());
|
|||
|
|
|
|||
|
|
if ($this->confirm('是否尝试创建数据库?', true)) {
|
|||
|
|
$this->createDatabaseFromEnv();
|
|||
|
|
$this->info('✅ 数据库创建成功');
|
|||
|
|
|
|||
|
|
// 再次尝试连接
|
|||
|
|
try {
|
|||
|
|
DB::connection()->getPdo();
|
|||
|
|
$this->info('✅ 数据库连接成功');
|
|||
|
|
} catch (\Exception $e) {
|
|||
|
|
$this->error("❌ 创建数据库后连接失败: " . $e->getMessage());
|
|||
|
|
exit(1);
|
|||
|
|
}
|
|||
|
|
} else {
|
|||
|
|
$this->error('❌ 无法连接数据库，操作终止');
|
|||
|
|
exit(1);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 使用.env配置创建数据库
|
|||
|
|
*/
|
|||
|
|
protected function createDatabaseFromEnv(): void
|
|||
|
|
{
|
|||
|
|
$dbConfig = [
|
|||
|
|
'driver' => 'mysql',
|
|||
|
|
'host' => config('database.connections.mysql.host'),
|
|||
|
|
'port' => config('database.connections.mysql.port'),
|
|||
|
|
'username' => config('database.connections.mysql.username'),
|
|||
|
|
'password' => config('database.connections.mysql.password'),
|
|||
|
|
'database' => config('database.connections.mysql.database'),
|
|||
|
|
'charset' => config('database.connections.mysql.charset', 'utf8mb4'),
|
|||
|
|
'collation' => config('database.connections.mysql.collation', 'utf8mb4_unicode_ci')
|
|||
|
|
];
|
|||
|
|
|
|||
|
|
$this->createDatabase($dbConfig);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 更新 .env 文件
|
|||
|
|
*/
|
|||
|
|
protected function updateEnvFile(array $config): void
|
|||
|
|
{
|
|||
|
|
$envPath = base_path('.env');
|
|||
|
|
$envContents = file_exists($envPath) ? File::get($envPath) : '';
|
|||
|
|
|
|||
|
|
foreach ($config as $key => $value) {
|
|||
|
|
$escapedValue = str_replace('"', '\\"', $value);
|
|||
|
|
$line = "{$key}=\"{$escapedValue}\"";
|
|||
|
|
|
|||
|
|
if (preg_match("/^{$key}=.*/m", $envContents)) {
|
|||
|
|
$envContents = preg_replace("/^{$key}=.*/m", $line, $envContents);
|
|||
|
|
} else {
|
|||
|
|
$envContents .= PHP_EOL . $line;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
File::put($envPath, $envContents);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 准备目标数据库（基于.env配置）
|
|||
|
|
*/
|
|||
|
|
protected function prepareTargetDatabase(): void
|
|||
|
|
{
|
|||
|
|
$this->line('🗄️ 检查目标数据库...');
|
|||
|
|
|
|||
|
|
$connection = config('database.default');
|
|||
|
|
$dbConfig = config("database.connections.{$connection}");
|
|||
|
|
$driver = $dbConfig['driver'];
|
|||
|
|
|
|||
|
|
// 处理数据库名称选项（优先使用命令行）
|
|||
|
|
$databaseName = $this->getDatabaseName($dbConfig);
|
|||
|
|
|
|||
|
|
$this->info("📊 数据库驱动: {$driver}");
|
|||
|
|
$this->info("🗃️ 目标数据库: {$databaseName}");
|
|||
|
|
|
|||
|
|
// 检查数据库是否存在
|
|||
|
|
if (!$this->databaseExists($dbConfig)) {
|
|||
|
|
$this->warn("❓ 数据库 {$databaseName} 不存在");
|
|||
|
|
|
|||
|
|
if ($this->option('force') || $this->confirm("是否创建数据库 {$databaseName}?", true)) {
|
|||
|
|
$this->createDatabase([
|
|||
|
|
'driver' => $driver,
|
|||
|
|
'host' => $dbConfig['host'],
|
|||
|
|
'port' => $dbConfig['port'],
|
|||
|
|
'username' => $dbConfig['username'],
|
|||
|
|
'password' => $dbConfig['password'],
|
|||
|
|
'database' => $databaseName,
|
|||
|
|
'charset' => $this->option('charset'),
|
|||
|
|
'collation' => $this->option('collation')
|
|||
|
|
]);
|
|||
|
|
$this->info("✅ 数据库 {$databaseName} 创建成功");
|
|||
|
|
} else {
|
|||
|
|
$this->error('❌ 操作已取消');
|
|||
|
|
exit(1);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 刷新数据库连接确保使用的是目标数据库
|
|||
|
|
$this->ensureUsingTargetDatabase($databaseName);
|
|||
|
|
|
|||
|
|
// 检查数据库表
|
|||
|
|
$this->checkDatabaseTables($databaseName);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 获取数据库名称（优先使用命令行选项）
|
|||
|
|
*/
|
|||
|
|
protected function getDatabaseName(array $defaultConfig): string
|
|||
|
|
{
|
|||
|
|
if ($database = $this->option('database')) {
|
|||
|
|
return $database;
|
|||
|
|
}
|
|||
|
|
return $defaultConfig['database'];
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 确保连接到目标数据库
|
|||
|
|
*/
|
|||
|
|
protected function ensureUsingTargetDatabase(string $databaseName): void
|
|||
|
|
{
|
|||
|
|
// 更新配置为当前目标数据库
|
|||
|
|
Config::set("database.connections.".config('database.default').".database", $databaseName);
|
|||
|
|
|
|||
|
|
// 刷新连接
|
|||
|
|
DB::purge(config('database.default'));
|
|||
|
|
DB::reconnect(config('database.default'));
|
|||
|
|
|
|||
|
|
$this->line("🔁 已切换至数据库: {$databaseName}");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 检查数据库表
|
|||
|
|
*/
|
|||
|
|
protected function checkDatabaseTables(string $databaseName): void
|
|||
|
|
{
|
|||
|
|
// 优化的表检查逻辑
|
|||
|
|
$tableCount = $this->getTableCount($databaseName);
|
|||
|
|
|
|||
|
|
if ($tableCount > 0) {
|
|||
|
|
$this->warn("⚠️ 数据库 {$databaseName} 包含 {$tableCount} 张表");
|
|||
|
|
|
|||
|
|
if (!$this->option('force') &&
|
|||
|
|
!$this->confirm("此操作将重置数据库 {$databaseName} 中的所有数据。继续执行吗?", false)) {
|
|||
|
|
$this->error('❌ 操作已取消');
|
|||
|
|
exit(1);
|
|||
|
|
}
|
|||
|
|
} elseif ($tableCount === 0) {
|
|||
|
|
$this->info("📭 数据库 {$databaseName} 为空");
|
|||
|
|
} else {
|
|||
|
|
$this->warn("⚠️ 无法确定数据库表数量，请谨慎操作");
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 获取数据库中的表数量（优化版）
|
|||
|
|
*/
|
|||
|
|
protected function getTableCount(string $databaseName): int
|
|||
|
|
{
|
|||
|
|
try {
|
|||
|
|
$driver = DB::getDriverName();
|
|||
|
|
|
|||
|
|
if ($driver === 'mysql') {
|
|||
|
|
$result = DB::select("SELECT COUNT(*) AS count
|
|||
|
|
FROM information_schema.tables
|
|||
|
|
WHERE table_schema = ?",
|
|||
|
|
[$databaseName]);
|
|||
|
|
return (int) ($result[0]->count ?? 0);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if ($driver === 'sqlite') {
|
|||
|
|
$result = DB::select("SELECT COUNT(*) AS count
|
|||
|
|
FROM sqlite_master
|
|||
|
|
WHERE type='table'");
|
|||
|
|
return (int) ($result[0]->count ?? 0);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if ($driver === 'pgsql') {
|
|||
|
|
$result = DB::select("SELECT COUNT(*) AS count
|
|||
|
|
FROM information_schema.tables
|
|||
|
|
WHERE table_schema='public'");
|
|||
|
|
return (int) ($result[0]->count ?? 0);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 其他数据库使用默认方法
|
|||
|
|
$result = DB::select("SHOW TABLES");
|
|||
|
|
return count($result);
|
|||
|
|
} catch (\Exception $e) {
|
|||
|
|
// 出现错误时返回-1表示无法确定
|
|||
|
|
$this->warn("⚠️ 无法确定表数量: " . $e->getMessage());
|
|||
|
|
return -1;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 执行SQL
|
|||
|
|
*/
|
|||
|
|
protected function executeSql(string $filePath): void
|
|||
|
|
{
|
|||
|
|
$fileSize = filesize($filePath) / 1024 / 1024; // MB
|
|||
|
|
|
|||
|
|
if ($fileSize > $this->maxFileSize) {
|
|||
|
|
$this->executeSqlFileChunks($filePath);
|
|||
|
|
} else {
|
|||
|
|
$this->executeSingleSqlFile($filePath);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 单块执行SQL文件
|
|||
|
|
*/
|
|||
|
|
protected function executeSingleSqlFile(string $filePath): void
|
|||
|
|
{
|
|||
|
|
$this->line('🚀 开始导入SQL文件...');
|
|||
|
|
|
|||
|
|
try {
|
|||
|
|
$sql = file_get_contents($filePath);
|
|||
|
|
$this->smartTransactionHandler(function() use ($sql) {
|
|||
|
|
$this->executeRawSql($sql);
|
|||
|
|
});
|
|||
|
|
|
|||
|
|
$this->info('✅ SQL文件导入成功');
|
|||
|
|
} catch (\Exception $e) {
|
|||
|
|
$this->handleSqlError($e, $sql ?? '');
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 分块执行SQL文件
|
|||
|
|
*/
|
|||
|
|
protected function executeSqlFileChunks(string $filePath): void
|
|||
|
|
{
|
|||
|
|
$this->line('🚀 开始分块导入SQL文件...');
|
|||
|
|
|
|||
|
|
$fileSize = filesize($filePath);
|
|||
|
|
$chunkSize = $this->chunkSize * 1024 * 1024;
|
|||
|
|
$chunkCount = ceil($fileSize / $chunkSize);
|
|||
|
|
|
|||
|
|
$this->info("📦 文件大小: " . round($fileSize / 1024 / 1024, 2) . "MB");
|
|||
|
|
$this->info("🧩 分块数量: {$chunkCount} (每块 {$this->chunkSize}MB)");
|
|||
|
|
|
|||
|
|
$handle = fopen($filePath, 'r');
|
|||
|
|
$chunkNum = 1;
|
|||
|
|
$position = 0;
|
|||
|
|
|
|||
|
|
try {
|
|||
|
|
while (!feof($handle)) {
|
|||
|
|
// 定位到下一分块
|
|||
|
|
fseek($handle, $position);
|
|||
|
|
|
|||
|
|
// 读取当前分块
|
|||
|
|
$chunkData = fread($handle, $chunkSize);
|
|||
|
|
|
|||
|
|
// 确保完整语句 (找到下一个分号)
|
|||
|
|
$lastSemicolon = strrpos($chunkData, ';');
|
|||
|
|
if ($lastSemicolon !== false) {
|
|||
|
|
$chunkData = substr($chunkData, 0, $lastSemicolon + 1);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$chunkSizeActual = strlen($chunkData);
|
|||
|
|
$this->line("🔧 处理分块 {$chunkNum}/{$chunkCount} (" . round($chunkSizeActual / 1024 / 1024, 2) . "MB)...");
|
|||
|
|
|
|||
|
|
$this->smartTransactionHandler(function() use ($chunkData) {
|
|||
|
|
$this->executeRawSql($chunkData);
|
|||
|
|
});
|
|||
|
|
|
|||
|
|
$position += $chunkSizeActual;
|
|||
|
|
$chunkNum++;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$this->info('✅ SQL文件导入成功');
|
|||
|
|
} catch (\Exception $e) {
|
|||
|
|
$this->handleSqlError($e, $chunkData ?? '');
|
|||
|
|
} finally {
|
|||
|
|
fclose($handle);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 智能事务处理
|
|||
|
|
*/
|
|||
|
|
protected function smartTransactionHandler(callable $callback): void
|
|||
|
|
{
|
|||
|
|
$driver = DB::getDriverName();
|
|||
|
|
|
|||
|
|
$this->prepareDatabaseForExecution($driver);
|
|||
|
|
|
|||
|
|
try {
|
|||
|
|
// 对于支持事务的数据库驱动使用事务
|
|||
|
|
if (in_array($driver, ['mysql', 'pgsql', 'sqlsrv', 'sqlite'])) {
|
|||
|
|
DB::beginTransaction();
|
|||
|
|
$callback();
|
|||
|
|
DB::commit();
|
|||
|
|
} else {
|
|||
|
|
// 不支持事务的数据库直接执行
|
|||
|
|
$callback();
|
|||
|
|
}
|
|||
|
|
} catch (\Exception $e) {
|
|||
|
|
// 回滚事务（如果已经开启）
|
|||
|
|
if (in_array($driver, ['mysql', 'pgsql', 'sqlsrv', 'sqlite']) &&
|
|||
|
|
DB::connection()->transactionLevel() > 0) {
|
|||
|
|
DB::rollBack();
|
|||
|
|
}
|
|||
|
|
throw $e;
|
|||
|
|
} finally {
|
|||
|
|
$this->restoreDatabaseSettings($driver);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 准备数据库执行环境
|
|||
|
|
*/
|
|||
|
|
protected function prepareDatabaseForExecution(string $driver): void
|
|||
|
|
{
|
|||
|
|
// MySQL 需要禁用外键检查
|
|||
|
|
if ($driver === 'mysql') {
|
|||
|
|
DB::statement('SET FOREIGN_KEY_CHECKS=0');
|
|||
|
|
DB::statement('SET AUTOCOMMIT=0');
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// PostgreSQL 需要禁用触发器
|
|||
|
|
if ($driver === 'pgsql') {
|
|||
|
|
DB::statement('SET session_replication_role = replica;');
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 恢复数据库设置
|
|||
|
|
*/
|
|||
|
|
protected function restoreDatabaseSettings(string $driver): void
|
|||
|
|
{
|
|||
|
|
// MySQL 恢复设置
|
|||
|
|
if ($driver === 'mysql') {
|
|||
|
|
DB::statement('SET FOREIGN_KEY_CHECKS=1');
|
|||
|
|
DB::statement('SET AUTOCOMMIT=1');
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// PostgreSQL 恢复设置
|
|||
|
|
if ($driver === 'pgsql') {
|
|||
|
|
DB::statement('SET session_replication_role = DEFAULT;');
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 执行原始SQL语句
|
|||
|
|
*/
|
|||
|
|
protected function executeRawSql(string $sql): void
|
|||
|
|
{
|
|||
|
|
// 使用原生方法执行以避免事务问题
|
|||
|
|
try {
|
|||
|
|
DB::getPdo()->exec($sql);
|
|||
|
|
} catch (PDOException $e) {
|
|||
|
|
// 处理部分SQL的错误（记录但不中断）
|
|||
|
|
if (str_contains($e->getMessage(), 'already exists') ||
|
|||
|
|
str_contains($e->getMessage(), 'doesn\'t exist')) {
|
|||
|
|
$this->warn("⚠️ SQL警告: " . $e->getMessage());
|
|||
|
|
} else {
|
|||
|
|
throw $e;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 处理SQL错误
|
|||
|
|
*/
|
|||
|
|
protected function handleSqlError(\Exception $e, string $sqlFragment): void
|
|||
|
|
{
|
|||
|
|
$errorMsg = $e->getMessage();
|
|||
|
|
$this->error("❌ SQL执行错误: {$errorMsg}");
|
|||
|
|
|
|||
|
|
// MySQL错误处理
|
|||
|
|
if ($this->isMysql() && preg_match('/at line (\d+)/i', $errorMsg, $matches)) {
|
|||
|
|
$lineNum = $matches[1];
|
|||
|
|
$this->error("📌 错误位置: 大约第 {$lineNum} 行");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 创建错误报告
|
|||
|
|
$this->createErrorReport($e, $sqlFragment);
|
|||
|
|
|
|||
|
|
$this->error('❌ 数据库安装失败');
|
|||
|
|
exit(1);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 创建错误报告
|
|||
|
|
*/
|
|||
|
|
protected function createErrorReport(\Exception $e, string $sqlFragment): void
|
|||
|
|
{
|
|||
|
|
$timestamp = date('Ymd-His');
|
|||
|
|
$reportFile = storage_path("logs/sql_error_{$timestamp}.log");
|
|||
|
|
|
|||
|
|
$content = "SQL 错误报告: " . date('Y-m-d H:i:s') . PHP_EOL;
|
|||
|
|
$content .= "错误信息: " . $e->getMessage() . PHP_EOL;
|
|||
|
|
$content .= "错误位置: " . $e->getFile() . ':' . $e->getLine() . PHP_EOL;
|
|||
|
|
$content .= "数据库驱动: " . config('database.default') . PHP_EOL;
|
|||
|
|
$content .= "数据库名称: " . config("database.connections.".config('database.default').".database") . PHP_EOL;
|
|||
|
|
|
|||
|
|
if (!empty($sqlFragment)) {
|
|||
|
|
$content .= PHP_EOL . "相关SQL片段: " . PHP_EOL;
|
|||
|
|
$content .= substr($sqlFragment, -2000) . PHP_EOL; // 最后2000字符
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
file_put_contents($reportFile, $content);
|
|||
|
|
$this->warn("📝 错误报告已保存到: {$reportFile}");
|
|||
|
|
$this->line("💡 请将此文件提供给开发人员以诊断问题");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 检查是否为MySQL驱动
|
|||
|
|
*/
|
|||
|
|
protected function isMysql(): bool
|
|||
|
|
{
|
|||
|
|
return DB::getDriverName() === 'mysql';
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 检查数据库是否存在（基于env配置）
|
|||
|
|
*/
|
|||
|
|
protected function databaseExists(array $config): bool
|
|||
|
|
{
|
|||
|
|
if ($config['driver'] === 'mysql') {
|
|||
|
|
try {
|
|||
|
|
$dbName = $this->getDatabaseName($config);
|
|||
|
|
$result = DB::select("SELECT SCHEMA_NAME
|
|||
|
|
FROM information_schema.SCHEMATA
|
|||
|
|
WHERE SCHEMA_NAME = ?",
|
|||
|
|
[$dbName]);
|
|||
|
|
return !empty($result);
|
|||
|
|
} catch (\Exception $e) {
|
|||
|
|
$this->warn("⚠️ 数据库检查错误: " . $e->getMessage());
|
|||
|
|
return false;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// SQLite 检查
|
|||
|
|
if ($config['driver'] === 'sqlite') {
|
|||
|
|
return file_exists($config['database']);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// PostgreSQL 检查
|
|||
|
|
if ($config['driver'] === 'pgsql') {
|
|||
|
|
try {
|
|||
|
|
DB::statement("SELECT 1 FROM pg_database WHERE datname = ?", [$config['database']]);
|
|||
|
|
return true;
|
|||
|
|
} catch (\Exception) {
|
|||
|
|
return false;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 默认检查方法
|
|||
|
|
try {
|
|||
|
|
DB::statement("USE {$config['database']}");
|
|||
|
|
return true;
|
|||
|
|
} catch (\Exception) {
|
|||
|
|
return false;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 创建数据库（基于env配置）
|
|||
|
|
*/
|
|||
|
|
protected function createDatabase(array $config): void
|
|||
|
|
{
|
|||
|
|
$dbName = $this->getDatabaseName($config);
|
|||
|
|
$charset = $this->option('charset');
|
|||
|
|
$collation = $this->option('collation');
|
|||
|
|
|
|||
|
|
$this->line("⚙️ 创建 {$config['driver']} 数据库: {$dbName} (字符集: {$charset}, 排序规则: {$collation})");
|
|||
|
|
|
|||
|
|
if ($config['driver'] === 'mysql') {
|
|||
|
|
// 临时连接到系统数据库以创建新数据库
|
|||
|
|
DB::purge('mysql');
|
|||
|
|
Config::set('database.connections.mysql.database', null);
|
|||
|
|
DB::reconnect('mysql');
|
|||
|
|
|
|||
|
|
$query = "CREATE DATABASE IF NOT EXISTS `{$dbName}` ";
|
|||
|
|
$query .= "CHARACTER SET {$charset} COLLATE {$collation}";
|
|||
|
|
|
|||
|
|
DB::statement($query);
|
|||
|
|
|
|||
|
|
// 恢复配置
|
|||
|
|
Config::set('database.connections.mysql.database', $dbName);
|
|||
|
|
return;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if ($config['driver'] === 'sqlite') {
|
|||
|
|
file_put_contents($config['database'], '');
|
|||
|
|
return;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if ($config['driver'] === 'pgsql') {
|
|||
|
|
DB::statement("CREATE DATABASE \"{$dbName}\"");
|
|||
|
|
return;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
$this->error("🚫 不支持的数据库驱动: {$config['driver']}");
|
|||
|
|
exit(1);
|
|||
|
|
}
|
|||
|
|
}
|