CaiWuXiTong/app/Utilities/SystemDatabaseConfiguration.php

610 lines
22 KiB
PHP
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

<?php
namespace App\Utilities;
use App\Console\Commands\ApplicationReady;
use App\Console\Commands\VerifySystemDatabase;
use App\Exceptions\SystemDatabaseVerificationException;
use Dotenv\Dotenv;
use Illuminate\Support\Facades\Artisan;
use PDO;
use PDOException;
use RuntimeException;
use Symfony\Component\Process\PhpExecutableFinder;
use Symfony\Component\Process\Process;
use Throwable;
class SystemDatabaseConfiguration
{
private string $environmentPath;
private ?string $backupDirectory;
public function __construct(
?string $environmentPath = null,
?string $backupDirectory = null
)
{
$this->environmentPath = $environmentPath ?: base_path('.env');
$this->backupDirectory = $backupDirectory;
}
public function current(): array
{
$connectionName = (string) config('database.default', 'mysql');
$connection = (array) config('database.connections.' . $connectionName, []);
$options = (array) ($connection['options'] ?? []);
$host = (string) (
data_get($connection, 'write.host.0')
?? data_get($connection, 'read.host.0')
?? ($connection['host'] ?? '')
);
$tlsRequired = isset($options[PDO::MYSQL_ATTR_SSL_CA])
|| isset($options[PDO::MYSQL_ATTR_SSL_CIPHER]);
$verifyServerCertificate = defined('PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT')
? (bool) ($options[constant('PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT')] ?? false)
: false;
return [
'connection' => $connectionName,
'driver' => (string) ($connection['driver'] ?? $connectionName),
'host' => $host,
'port' => (int) ($connection['port'] ?? 3306),
'database' => (string) ($connection['database'] ?? ''),
'username' => (string) ($connection['username'] ?? ''),
'password' => (string) ($connection['password'] ?? ''),
'prefix' => (string) ($connection['prefix'] ?? ''),
'mode' => $this->databaseMode($host, (string) ($connection['database'] ?? '')),
'tls_required' => $tlsRequired,
'tls_verify_server_cert' => $tlsRequired && $verifyServerCertificate,
'tls_ca' => (string) ($options[PDO::MYSQL_ATTR_SSL_CA] ?? ''),
];
}
/**
* @return array{cipher:string,user_count:int,admin_count:int,source_count:int,prefix:string}
*/
public function probe(array $settings): array
{
if (($settings['driver'] ?? 'mysql') !== 'mysql') {
throw new RuntimeException('系统数据库目前只支持 MariaDB/MySQL。');
}
$this->assertHostResolves((string) $settings['host']);
$this->assertTcpReachable((string) $settings['host'], (int) $settings['port']);
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_TIMEOUT => 8,
];
if (! empty($settings['tls_required'])) {
$options[PDO::MYSQL_ATTR_SSL_CIPHER] = 'DEFAULT';
if (! empty($settings['tls_ca'])) {
$options[PDO::MYSQL_ATTR_SSL_CA] = (string) $settings['tls_ca'];
}
if (defined('PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT')) {
$options[constant('PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT')] =
! empty($settings['tls_verify_server_cert']);
}
}
try {
$pdo = new PDO(
sprintf(
'mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4',
$settings['host'],
$settings['port'],
$settings['database']
),
$settings['username'],
$settings['password'],
$options
);
$cipher = (string) ($pdo
->query("SHOW SESSION STATUS LIKE 'Ssl_cipher'")
->fetch(PDO::FETCH_NUM)[1] ?? '');
if (! empty($settings['tls_required']) && $cipher === '') {
throw new RuntimeException('数据库连接没有启用 TLS 加密。');
}
$tableNames = $pdo->query(
'SELECT table_name FROM information_schema.tables '
. 'WHERE table_schema = DATABASE()'
)->fetchAll(PDO::FETCH_COLUMN);
$prefix = $this->detectTablePrefix(
array_map('strval', $tableNames),
$this->current()['prefix']
);
$userCount = (int) $pdo->query(
'SELECT COUNT(*) FROM ' . $this->quoteIdentifier($prefix . 'users')
)->fetchColumn();
$sourceCount = (int) $pdo->query(
'SELECT COUNT(*) FROM '
. $this->quoteIdentifier($prefix . 'system_database_connections')
. ' WHERE company_id = 0'
)->fetchColumn();
return [
'cipher' => $cipher,
'user_count' => $userCount,
'admin_count' => 0,
'source_count' => $sourceCount,
'prefix' => $prefix,
];
} catch (PDOException $exception) {
throw new RuntimeException($this->safeConnectionError($exception), 0, $exception);
} catch (RuntimeException $exception) {
throw $exception;
} catch (Throwable $exception) {
throw new RuntimeException($this->safeConnectionError($exception), 0, $exception);
}
}
public function persist(array $settings): string
{
if (! is_file($this->environmentPath) || ! is_writable($this->environmentPath)) {
throw new RuntimeException('服务器私有启动配置不可写,未执行切换。');
}
$current = file_get_contents($this->environmentPath);
if ($current === false) {
throw new RuntimeException('无法读取服务器私有启动配置。');
}
$backupDirectory = $this->writableBackupDirectory();
$backupPath = $backupDirectory . DIRECTORY_SEPARATOR . 'runtime-env-' . now()->format('Ymd-His-u') . '.backup';
if (@file_put_contents($backupPath, $current, LOCK_EX) === false) {
throw new RuntimeException('系统数据库配置备份失败,未执行切换。');
}
@chmod($backupPath, 0600);
$prefix = array_key_exists('prefix', $settings)
? (string) $settings['prefix']
: $this->current()['prefix'];
$this->assertValidPrefix($prefix);
$values = [
'DB_CONNECTION' => 'mysql',
'DB_HOST' => (string) $settings['host'],
// Laravel prioritizes split read/write hosts, URL and socket over
// DB_HOST. They must point to the same newly tested connection.
'DB_HOST_READ' => (string) $settings['host'],
'DB_HOST_WRITE' => (string) $settings['host'],
'DATABASE_URL' => '',
'DB_SOCKET' => '',
'DB_PORT' => (string) $settings['port'],
'DB_DATABASE' => (string) $settings['database'],
'DB_USERNAME' => (string) $settings['username'],
'DB_PASSWORD' => (string) $settings['password'],
'DB_PREFIX' => $prefix,
'MYSQL_ATTR_SSL_CIPHER' => ! empty($settings['tls_required']) ? 'DEFAULT' : '',
'MYSQL_ATTR_SSL_CA' => ! empty($settings['tls_required'])
? (string) ($settings['tls_ca'] ?? '')
: '',
'MYSQL_ATTR_SSL_VERIFY_SERVER_CERT' => ! empty($settings['tls_required'])
&& ! empty($settings['tls_verify_server_cert'])
? 'true'
: 'false',
'SYSTEM_DATABASE_MODE' => 'shared',
// 系统数据库保存后统一读取共享云数据库配置,不再要求开发机
// 另外维护完整的 DB_<SOURCE>_* 连接参数。
'UPSTREAM_STORED_CONFIG_ENABLED' => 'true',
'UPSTREAM_LOCAL_CONFIG_ENABLED' => 'false',
];
if (isset($settings['mirror_database'])) {
// 团队接入统一两个库的端点,避免旧 BUSINESS_MIRROR_DB_* 覆盖新系统连接。
$values += [
'APP_INSTALLED' => 'true',
'BUSINESS_MIRROR_DB_HOST' => (string) $settings['host'],
'BUSINESS_MIRROR_DB_HOST_READ' => (string) $settings['host'],
'BUSINESS_MIRROR_DB_HOST_WRITE' => (string) $settings['host'],
'BUSINESS_MIRROR_DB_PORT' => (string) $settings['port'],
'BUSINESS_MIRROR_DB_DATABASE' => (string) $settings['mirror_database'],
'BUSINESS_MIRROR_DB_USERNAME' => (string) $settings['username'],
'BUSINESS_MIRROR_DB_PASSWORD' => (string) $settings['password'],
'BUSINESS_MIRROR_DB_SOCKET' => '',
];
}
$updated = $this->replaceEnvironmentValues($current, $values);
try {
if (file_put_contents($this->environmentPath, $updated, LOCK_EX) === false) {
throw new SystemDatabaseVerificationException('新配置写入失败');
}
$this->clearConfigurationCache();
$this->runFreshVerification('system-database:verify');
} catch (Throwable $exception) {
$restored = file_put_contents($this->environmentPath, $current, LOCK_EX) !== false;
$candidateStage = $this->safeVerificationStage($exception);
if (! $restored) {
throw new RuntimeException(
"新配置未通过应用级验证({$candidateStage}),且旧配置恢复失败。请立即使用私有备份恢复。",
0,
$exception
);
}
try {
$this->clearConfigurationCache();
$this->runFreshVerification('app:ready');
} catch (Throwable $rollbackException) {
$rollbackStage = $this->safeVerificationStage($rollbackException);
throw new RuntimeException(
"新配置未通过应用级验证({$candidateStage}),旧配置文件已经恢复,但旧配置复核失败({$rollbackStage})。",
0,
$rollbackException
);
}
throw new RuntimeException(
"新配置未通过应用级验证({$candidateStage}),已自动恢复原系统数据库。",
0,
$exception
);
}
return $backupPath;
}
private function writableBackupDirectory(): string
{
$preferred = $this->backupDirectory
?: storage_path('app/system-database-config-backups');
if (
(is_dir($preferred) && is_writable($preferred))
|| (
! file_exists($preferred)
&& @mkdir($preferred, 0700, true)
&& is_writable($preferred)
)
) {
return $preferred;
}
// 某些开发机曾用 root 执行诊断命令,可能留下仅 root 可写的旧目录。
// storage/app 由容器入口统一授权给网页进程,可安全作为备份回退位置。
$fallback = storage_path('app');
if (is_dir($fallback) && is_writable($fallback)) {
return $fallback;
}
throw new RuntimeException('系统数据库配置备份目录不可写,未执行切换。');
}
/**
* 根据一组完整的系统必需表自动识别目标库表前缀。
*
* @param array<int,string> $tableNames
*/
public function detectTablePrefix(array $tableNames, string $preferredPrefix = ''): string
{
$requiredTables = [
'users',
'settings',
'system_database_connections',
];
$candidates = null;
foreach ($requiredTables as $requiredTable) {
$matchingPrefixes = [];
foreach ($tableNames as $tableName) {
if ($tableName !== $requiredTable && ! str_ends_with($tableName, '_' . $requiredTable)) {
continue;
}
$prefix = substr($tableName, 0, strlen($tableName) - strlen($requiredTable));
try {
$this->assertValidPrefix($prefix);
$matchingPrefixes[] = $prefix;
} catch (RuntimeException) {
continue;
}
}
$matchingPrefixes = array_values(array_unique($matchingPrefixes));
$candidates = $candidates === null
? $matchingPrefixes
: array_values(array_intersect($candidates, $matchingPrefixes));
}
if ($candidates === null || $candidates === []) {
throw new RuntimeException('目标库缺少系统必需表,已拒绝切换。');
}
if (in_array($preferredPrefix, $candidates, true)) {
return $preferredPrefix;
}
if (count($candidates) !== 1) {
throw new RuntimeException('目标库存在多套系统表,无法自动确定表前缀。');
}
return $candidates[0];
}
private function databaseMode(string $host, string $database): string
{
$configured = strtolower(trim((string) config('system_database.mode', '')));
if (in_array($configured, ['bootstrap', 'shared'], true)) {
return $configured;
}
return app()->environment(['local', 'development'])
&& $host === 'mysql'
&& $database === 'akaunting'
? 'bootstrap'
: 'shared';
}
private function assertHostResolves(string $host): void
{
if (
filter_var($host, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4)
|| filter_var($host, FILTER_VALIDATE_IP, FILTER_FLAG_IPV6)
) {
return;
}
if ($host === '' || gethostbynamel($host) === false) {
throw new RuntimeException('系统数据库地址暂时无法解析,请稍后重试或联系负责人。');
}
}
private function assertTcpReachable(string $host, int $port): void
{
$address = filter_var($host, FILTER_VALIDATE_IP, FILTER_FLAG_IPV6)
? sprintf('tcp://[%s]:%d', $host, $port)
: sprintf('tcp://%s:%d', $host, $port);
$socket = @stream_socket_client($address, $errorCode, $errorMessage, 4);
if (! is_resource($socket)) {
throw new RuntimeException('系统数据库暂时无法连接,请稍后重试或联系负责人。');
}
fclose($socket);
}
private function safeConnectionError(Throwable $exception): string
{
$driverCode = $exception instanceof PDOException
? (int) ($exception->errorInfo[1] ?? 0)
: 0;
$message = strtolower($exception->getMessage());
if ($driverCode === 1045 || str_contains($message, 'access denied')) {
return '系统数据库密码不正确,请重新输入。';
}
if ($driverCode === 1049 || str_contains($message, 'unknown database')) {
return '团队系统数据库配置异常,请联系负责人。';
}
if (
$driverCode === 2026
|| str_contains($message, 'ssl')
|| str_contains($message, 'certificate')
|| str_contains($message, 'tls')
) {
return '系统数据库加密连接失败,请稍后重试或联系负责人。';
}
if ($driverCode === 2002 || str_contains($message, 'timed out')) {
return '系统数据库暂时无法连接,请稍后重试或联系负责人。';
}
return '系统数据库连接初始化失败,请稍后重试或联系负责人。';
}
protected function runFreshVerification(string $command): void
{
// 当前网页/Artisan 进程启动时,Laravel Dotenv 已把旧数据库值放进
// 进程环境。若直接启动子进程,Symfony Process 会继承这些旧值,
// 新进程便不会读取刚写入 .env 的共享库配置,导致全新环境必然误判
// 为 bootstrap 并回滚。显式移除本次切换涉及的键,让子进程以磁盘
// 上最新(或刚恢复)的 .env 为唯一事实来源。
$php = $this->phpExecutable();
try {
$process = new Process(
[$php, base_path('artisan'), $command, '--no-ansi', '--quiet'],
base_path(),
$this->freshVerificationEnvironment()
);
$process->setTimeout(60);
$process->run();
} catch (SystemDatabaseVerificationException $exception) {
throw $exception;
} catch (Throwable $exception) {
throw new SystemDatabaseVerificationException('PHP 验证进程无法启动', 0, $exception);
}
if (! $process->isSuccessful()) {
throw new SystemDatabaseVerificationException(
$this->verificationExitStage($command, (int) $process->getExitCode())
);
}
}
protected function phpExecutable(): string
{
$binary = DIRECTORY_SEPARATOR === '\\' ? 'php.exe' : 'php';
$candidates = [
(new PhpExecutableFinder())->find(false),
PHP_BINARY,
PHP_BINDIR.DIRECTORY_SEPARATOR.$binary,
];
foreach (array_unique($candidates) as $php) {
if (is_string($php) && $php !== '' && is_file($php) && is_executable($php)) {
return $php;
}
}
throw new SystemDatabaseVerificationException('PHP 验证进程无法启动');
}
protected function clearConfigurationCache(): void
{
try {
$exitCode = Artisan::call('config:clear');
} catch (Throwable $exception) {
throw new SystemDatabaseVerificationException('运行目录不可写', 0, $exception);
}
if ($exitCode !== 0) {
throw new SystemDatabaseVerificationException('运行目录不可写');
}
}
private function verificationExitStage(string $command, int $exitCode): string
{
if ($command === 'system-database:verify') {
return match ($exitCode) {
VerifySystemDatabase::EXIT_CONFIG_NOT_LOADED => '新配置未被加载',
VerifySystemDatabase::EXIT_DATABASE_CONNECTION_FAILED => 'Laravel 数据库连接失败',
VerifySystemDatabase::EXIT_SYSTEM_TABLE_VALIDATION_FAILED => '系统表验证失败',
VerifySystemDatabase::EXIT_SHARED_NOT_CONFIGURED => '新配置仍指向本机临时引导库',
default => '应用级验证失败',
};
}
if ($command === 'app:ready') {
return match ($exitCode) {
ApplicationReady::EXIT_NOT_INSTALLED => 'APP_INSTALLED 未被加载',
ApplicationReady::EXIT_RUNTIME_NOT_WRITABLE => '运行目录不可写',
ApplicationReady::EXIT_DATABASE_CONNECTION_FAILED => 'Laravel 数据库连接失败',
default => '旧配置应用复核失败',
};
}
return '应用级验证失败';
}
private function safeVerificationStage(Throwable $exception): string
{
return $exception instanceof SystemDatabaseVerificationException
? $exception->getMessage()
: '应用级验证失败';
}
/**
* @return array<string, string|false>
*/
protected function freshVerificationEnvironment(): array
{
$keys = [
'APP_ENV',
'APP_INSTALLED',
'DATABASE_URL',
'DB_CONNECTION',
'DB_HOST',
'DB_HOST_READ',
'DB_HOST_WRITE',
'DB_PORT',
'DB_DATABASE',
'DB_USERNAME',
'DB_PASSWORD',
'DB_PREFIX',
'DB_SOCKET',
'BUSINESS_MIRROR_DB_HOST',
'BUSINESS_MIRROR_DB_HOST_READ',
'BUSINESS_MIRROR_DB_HOST_WRITE',
'BUSINESS_MIRROR_DB_PORT',
'BUSINESS_MIRROR_DB_DATABASE',
'BUSINESS_MIRROR_DB_USERNAME',
'BUSINESS_MIRROR_DB_PASSWORD',
'BUSINESS_MIRROR_DB_SOCKET',
'MYSQL_ATTR_SSL_CIPHER',
'MYSQL_ATTR_SSL_CA',
'MYSQL_ATTR_SSL_VERIFY_SERVER_CERT',
'SYSTEM_DATABASE_MODE',
'UPSTREAM_STORED_CONFIG_ENABLED',
'UPSTREAM_LOCAL_CONFIG_ENABLED',
];
$environment = array_fill_keys($keys, false);
$contents = file_get_contents($this->environmentPath);
if ($contents === false) {
throw new SystemDatabaseVerificationException('新配置未被加载');
}
try {
$saved = Dotenv::parse($contents);
} catch (Throwable $exception) {
throw new SystemDatabaseVerificationException('新配置未被加载', 0, $exception);
}
foreach ($keys as $key) {
if (array_key_exists($key, $saved)) {
$environment[$key] = (string) $saved[$key];
}
}
return $environment;
}
private function quoteIdentifier(string $identifier): string
{
return '`' . str_replace('`', '``', $identifier) . '`';
}
private function assertValidPrefix(string $prefix): void
{
if (strlen($prefix) > 50 || preg_match('/^[A-Za-z0-9_]*$/D', $prefix) !== 1) {
throw new RuntimeException('目标库系统表前缀不安全,已拒绝切换。');
}
}
private function replaceEnvironmentValues(string $environment, array $values): string
{
$lines = preg_split('/\r\n|\r|\n/', $environment) ?: [];
foreach ($values as $key => $value) {
$replacement = $key . '=' . $this->encodeEnvironmentValue((string) $value);
$found = false;
foreach ($lines as $index => $line) {
if (preg_match('/^\s*(?:export\s+)?' . preg_quote($key, '/') . '\s*=/', $line) === 1) {
$lines[$index] = $replacement;
$found = true;
}
}
if (! $found) {
$lines[] = $replacement;
}
}
return rtrim(implode("\n", $lines), "\n") . "\n";
}
private function encodeEnvironmentValue(string $value): string
{
return '"' . str_replace(
['\\', '"', '$', "\r", "\n"],
['\\\\', '\\"', '\\$', '', '\\n'],
$value
) . '"';
}
}