1018 lines
34 KiB
PHP
1018 lines
34 KiB
PHP
<?php
|
||
|
||
namespace App\Service;
|
||
|
||
use App\BaseApp\BaseService;
|
||
use App\Service\common\UtilsService;
|
||
use Exception;
|
||
use Illuminate\Support\Facades\DB;
|
||
|
||
class DatabaseService extends BaseService
|
||
{
|
||
public function __construct()
|
||
{
|
||
parent::__construct();
|
||
// 检查权限：只有role_id=1才能操作
|
||
if ($this->roleId !== 1) {
|
||
$this->utils->errorThrow('权限不足，只有超级管理员可以操作数据库管理功能');
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 获取数据库表列表
|
||
* @return array
|
||
*/
|
||
public function listTables(): array
|
||
{
|
||
$database = config('database.connections.mysql.database');
|
||
|
||
$tables = DB::select("
|
||
SELECT
|
||
TABLE_NAME as `name`,
|
||
TABLE_COMMENT as `comment`,
|
||
TABLE_ROWS as `rows`,
|
||
DATA_LENGTH as data_length,
|
||
INDEX_LENGTH as index_length,
|
||
CREATE_TIME as create_time,
|
||
UPDATE_TIME as update_time,
|
||
TABLE_COLLATION as collation,
|
||
ENGINE as engine
|
||
FROM information_schema.TABLES
|
||
WHERE TABLE_SCHEMA = ?
|
||
ORDER BY TABLE_NAME
|
||
", [$database]);
|
||
|
||
$result = [];
|
||
foreach ($tables as $table) {
|
||
$result[] = [
|
||
'name' => $table->name,
|
||
'comment' => $table->comment ?: '',
|
||
'rows' => (int)$table->rows,
|
||
'data_length' => (int)$table->data_length,
|
||
'index_length' => (int)$table->index_length,
|
||
'create_time' => $table->create_time,
|
||
'update_time' => $table->update_time,
|
||
'collation' => $table->collation,
|
||
'engine' => $table->engine,
|
||
];
|
||
}
|
||
|
||
return $result;
|
||
}
|
||
|
||
/**
|
||
* 获取表详细信息
|
||
* @param string $tableName
|
||
* @return array
|
||
* @throws Exception
|
||
*/
|
||
public function getTableInfo(string $tableName): array
|
||
{
|
||
$this->validateTableName($tableName);
|
||
$database = config('database.connections.mysql.database');
|
||
|
||
// 获取表基本信息
|
||
$tableInfo = DB::selectOne("
|
||
SELECT
|
||
TABLE_NAME as `name`,
|
||
TABLE_COMMENT as `comment`,
|
||
TABLE_ROWS as `rows`,
|
||
DATA_LENGTH as data_length,
|
||
INDEX_LENGTH as index_length,
|
||
CREATE_TIME as create_time,
|
||
UPDATE_TIME as update_time,
|
||
TABLE_COLLATION as collation,
|
||
ENGINE as engine
|
||
FROM information_schema.TABLES
|
||
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
|
||
", [$database, $tableName]);
|
||
|
||
if (!$tableInfo) {
|
||
$this->utils->errorThrow('表不存在');
|
||
}
|
||
|
||
// 获取字段信息
|
||
$columns = $this->getTableColumns($tableName);
|
||
|
||
// 获取索引信息
|
||
$indexes = $this->getTableIndexes($tableName);
|
||
|
||
return [
|
||
'name' => $tableInfo->name,
|
||
'comment' => $tableInfo->comment ?: '',
|
||
'rows' => (int)$tableInfo->rows,
|
||
'data_length' => (int)$tableInfo->data_length,
|
||
'index_length' => (int)$tableInfo->index_length,
|
||
'create_time' => $tableInfo->create_time,
|
||
'update_time' => $tableInfo->update_time,
|
||
'collation' => $tableInfo->collation,
|
||
'engine' => $tableInfo->engine,
|
||
'columns' => $columns,
|
||
'indexes' => $indexes,
|
||
];
|
||
}
|
||
|
||
/**
|
||
* 获取表字段信息
|
||
* @param string $tableName
|
||
* @return array
|
||
*/
|
||
public function getTableColumns(string $tableName): array
|
||
{
|
||
$this->validateTableName($tableName);
|
||
$database = config('database.connections.mysql.database');
|
||
|
||
$columns = DB::select("
|
||
SELECT
|
||
COLUMN_NAME as `name`,
|
||
DATA_TYPE as `type`,
|
||
CHARACTER_MAXIMUM_LENGTH as `length`,
|
||
NUMERIC_PRECISION as `precision`,
|
||
NUMERIC_SCALE as `scale`,
|
||
COLUMN_DEFAULT as default_value,
|
||
COLUMN_COMMENT as `comment`,
|
||
IS_NULLABLE as nullable,
|
||
COLUMN_KEY as key_type,
|
||
EXTRA as extra,
|
||
COLUMN_TYPE as column_type,
|
||
ORDINAL_POSITION as `position`
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
|
||
ORDER BY ORDINAL_POSITION
|
||
", [$database, $tableName]);
|
||
|
||
$result = [];
|
||
foreach ($columns as $column) {
|
||
$result[] = [
|
||
'name' => $column->name,
|
||
'type' => $column->type,
|
||
'length' => $column->length,
|
||
'precision' => $column->precision,
|
||
'scale' => $column->scale,
|
||
'default_value' => $column->default_value,
|
||
'comment' => $column->comment ?: '',
|
||
'nullable' => $column->nullable === 'YES',
|
||
'key_type' => $column->key_type,
|
||
'extra' => $column->extra,
|
||
'column_type' => $column->column_type,
|
||
'position' => (int)$column->position,
|
||
];
|
||
}
|
||
|
||
return $result;
|
||
}
|
||
|
||
/**
|
||
* 获取表索引信息
|
||
* @param string $tableName
|
||
* @return array
|
||
*/
|
||
public function getTableIndexes(string $tableName): array
|
||
{
|
||
$this->validateTableName($tableName);
|
||
$database = config('database.connections.mysql.database');
|
||
|
||
$indexes = DB::select("
|
||
SELECT
|
||
INDEX_NAME as `name`,
|
||
COLUMN_NAME as column_name,
|
||
NON_UNIQUE as non_unique,
|
||
SEQ_IN_INDEX as seq_in_index,
|
||
INDEX_TYPE as `type`
|
||
FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
|
||
ORDER BY INDEX_NAME, SEQ_IN_INDEX
|
||
", [$database, $tableName]);
|
||
|
||
$result = [];
|
||
$indexMap = [];
|
||
|
||
foreach ($indexes as $index) {
|
||
$indexName = $index->name;
|
||
if (!isset($indexMap[$indexName])) {
|
||
$indexMap[$indexName] = [
|
||
'name' => $indexName,
|
||
'columns' => [],
|
||
'unique' => $index->non_unique == 0,
|
||
'type' => $index->type,
|
||
];
|
||
}
|
||
$indexMap[$indexName]['columns'][] = $index->column_name;
|
||
}
|
||
|
||
return array_values($indexMap);
|
||
}
|
||
|
||
/**
|
||
* 获取表数据（分页）
|
||
* @param string $tableName
|
||
* @param int $page
|
||
* @param int $pageSize
|
||
* @param array $where
|
||
* @return array
|
||
* @throws Exception
|
||
*/
|
||
public function getTableData(string $tableName, int $page = 1, int $pageSize = 20, array $where = []): array
|
||
{
|
||
$this->validateTableName($tableName);
|
||
|
||
// 移除表名前缀，因为 DB::table() 会自动添加
|
||
$tableNameWithoutPrefix = $this->removeTablePrefix($tableName);
|
||
$query = DB::table($tableNameWithoutPrefix);
|
||
|
||
// 应用查询条件
|
||
$this->applyWhereConditions($query, $where);
|
||
|
||
$total = $query->count();
|
||
$data = $query->skip(($page - 1) * $pageSize)->take($pageSize)->get()->toArray();
|
||
|
||
return [
|
||
'page' => $page,
|
||
'size' => $pageSize,
|
||
'total' => $total,
|
||
'page_count' => ceil($total / $pageSize),
|
||
'items' => $data,
|
||
];
|
||
}
|
||
|
||
/**
|
||
* 应用WHERE条件到查询构建器
|
||
* @param \Illuminate\Database\Query\Builder $query
|
||
* @param array $where
|
||
* @return void
|
||
*/
|
||
private function applyWhereConditions($query, array $where): void
|
||
{
|
||
$logic = 'AND'; // 默认逻辑连接符
|
||
|
||
foreach ($where as $index => $condition) {
|
||
// 处理逻辑连接符（AND/OR）
|
||
if (isset($condition['logic'])) {
|
||
$logic = strtoupper($condition['logic']);
|
||
} elseif ($index > 0) {
|
||
// 如果没有指定，默认使用AND
|
||
$logic = 'AND';
|
||
}
|
||
|
||
if (!isset($condition['field']) || !isset($condition['operator'])) {
|
||
continue;
|
||
}
|
||
|
||
$field = $condition['field'];
|
||
$operator = strtolower($condition['operator']);
|
||
$value = $condition['value'] ?? null;
|
||
|
||
// 根据逻辑连接符选择方法
|
||
$whereMethod = $logic === 'OR' ? 'orWhere' : 'where';
|
||
|
||
switch ($operator) {
|
||
case '=':
|
||
case '!=':
|
||
case '>':
|
||
case '>=':
|
||
case '<':
|
||
case '<=':
|
||
if ($value !== null) {
|
||
$query->{$whereMethod}($field, $operator, $value);
|
||
}
|
||
break;
|
||
|
||
case 'like':
|
||
if ($value !== null) {
|
||
$query->{$whereMethod}($field, 'like', "%{$value}%");
|
||
}
|
||
break;
|
||
|
||
case 'not like':
|
||
if ($value !== null) {
|
||
$query->{$whereMethod}($field, 'not like', "%{$value}%");
|
||
}
|
||
break;
|
||
|
||
case 'in':
|
||
if (is_array($value) && !empty($value)) {
|
||
$query->{$whereMethod . 'In'}($field, $value);
|
||
}
|
||
break;
|
||
|
||
case 'not in':
|
||
if (is_array($value) && !empty($value)) {
|
||
$query->{$whereMethod . 'NotIn'}($field, $value);
|
||
}
|
||
break;
|
||
|
||
case 'between':
|
||
if (is_array($value) && count($value) === 2) {
|
||
$query->{$whereMethod . 'Between'}($field, $value);
|
||
}
|
||
break;
|
||
|
||
case 'is null':
|
||
$query->{$whereMethod . 'Null'}($field);
|
||
break;
|
||
|
||
case 'is not null':
|
||
$query->{$whereMethod . 'NotNull'}($field);
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 更新表数据
|
||
* @param string $tableName
|
||
* @param array $data
|
||
* @param array $where
|
||
* @return bool
|
||
* @throws Exception
|
||
*/
|
||
public function updateTableData(string $tableName, array $data, array $where): bool
|
||
{
|
||
$this->validateTableName($tableName);
|
||
|
||
// 移除表名前缀，因为 DB::table() 会自动添加
|
||
$tableNameWithoutPrefix = $this->removeTablePrefix($tableName);
|
||
$query = DB::table($tableNameWithoutPrefix);
|
||
|
||
// 获取变更前的数据
|
||
$beforeQuery = clone $query;
|
||
$this->applyWhereConditions($beforeQuery, $where);
|
||
$beforeData = $beforeQuery->get()->toArray();
|
||
|
||
// 应用WHERE条件
|
||
$this->applyWhereConditions($query, $where);
|
||
|
||
$affected = $query->update($data);
|
||
|
||
if ($affected > 0) {
|
||
// 获取变更后的数据
|
||
$afterQuery = DB::table($tableNameWithoutPrefix);
|
||
$this->applyWhereConditions($afterQuery, $where);
|
||
$afterData = $afterQuery->get()->toArray();
|
||
|
||
// 记录变更日志
|
||
$this->logChange('data_change', $tableName, [
|
||
'action' => 'update',
|
||
'affected_rows' => $affected,
|
||
'where' => $where,
|
||
], null, ['data' => $beforeData], ['data' => $afterData]);
|
||
}
|
||
|
||
return $affected > 0;
|
||
}
|
||
|
||
/**
|
||
* 删除表数据
|
||
* @param string $tableName
|
||
* @param array $where
|
||
* @return bool
|
||
* @throws Exception
|
||
*/
|
||
public function deleteTableData(string $tableName, array $where): bool
|
||
{
|
||
$this->validateTableName($tableName);
|
||
|
||
// 移除表名前缀，因为 DB::table() 会自动添加
|
||
$tableNameWithoutPrefix = $this->removeTablePrefix($tableName);
|
||
$query = DB::table($tableNameWithoutPrefix);
|
||
|
||
// 获取变更前的数据
|
||
$beforeQuery = clone $query;
|
||
$this->applyWhereConditions($beforeQuery, $where);
|
||
$beforeData = $beforeQuery->get()->toArray();
|
||
|
||
// 应用WHERE条件
|
||
$this->applyWhereConditions($query, $where);
|
||
|
||
$affected = $query->delete();
|
||
|
||
if ($affected > 0) {
|
||
// 记录变更日志
|
||
$this->logChange('data_change', $tableName, [
|
||
'action' => 'delete',
|
||
'affected_rows' => $affected,
|
||
'where' => $where,
|
||
], null, ['data' => $beforeData], null);
|
||
}
|
||
|
||
return $affected > 0;
|
||
}
|
||
|
||
/**
|
||
* 批量删除表数据
|
||
* @param string $tableName
|
||
* @param array $where 多个WHERE条件，使用OR连接
|
||
* @return int 删除的记录数
|
||
* @throws Exception
|
||
*/
|
||
public function batchDeleteTableData(string $tableName, array $where): int
|
||
{
|
||
$this->validateTableName($tableName);
|
||
|
||
if (empty($where)) {
|
||
$this->utils->errorThrow('删除条件不能为空');
|
||
}
|
||
|
||
// 移除表名前缀，因为 DB::table() 会自动添加
|
||
$tableNameWithoutPrefix = $this->removeTablePrefix($tableName);
|
||
$query = DB::table($tableNameWithoutPrefix);
|
||
|
||
// 获取变更前的数据
|
||
$beforeQuery = clone $query;
|
||
$beforeQuery->where(function ($q) use ($where) {
|
||
foreach ($where as $index => $condition) {
|
||
if (isset($condition['field']) && isset($condition['operator']) && isset($condition['value'])) {
|
||
if ($index === 0) {
|
||
$q->where($condition['field'], $condition['operator'], $condition['value']);
|
||
} else {
|
||
$q->orWhere($condition['field'], $condition['operator'], $condition['value']);
|
||
}
|
||
}
|
||
}
|
||
});
|
||
$beforeData = $beforeQuery->get()->toArray();
|
||
|
||
// 批量删除：使用OR连接多个条件
|
||
$query->where(function ($q) use ($where) {
|
||
foreach ($where as $index => $condition) {
|
||
if (isset($condition['field']) && isset($condition['operator']) && isset($condition['value'])) {
|
||
if ($index === 0) {
|
||
$q->where($condition['field'], $condition['operator'], $condition['value']);
|
||
} else {
|
||
$q->orWhere($condition['field'], $condition['operator'], $condition['value']);
|
||
}
|
||
}
|
||
}
|
||
});
|
||
|
||
$affected = $query->delete();
|
||
|
||
if ($affected > 0) {
|
||
// 记录变更日志
|
||
$this->logChange('data_change', $tableName, [
|
||
'action' => 'batch_delete',
|
||
'affected_rows' => $affected,
|
||
'where' => $where,
|
||
], null, ['data' => $beforeData], null);
|
||
}
|
||
|
||
return $affected;
|
||
}
|
||
|
||
/**
|
||
* 插入表数据
|
||
* @param string $tableName
|
||
* @param array $data
|
||
* @return int
|
||
* @throws Exception
|
||
*/
|
||
public function insertTableData(string $tableName, array $data): int
|
||
{
|
||
$this->validateTableName($tableName);
|
||
// 移除表名前缀，因为 DB::table() 会自动添加
|
||
$tableNameWithoutPrefix = $this->removeTablePrefix($tableName);
|
||
$id = DB::table($tableNameWithoutPrefix)->insertGetId($data);
|
||
|
||
// 记录变更日志
|
||
$this->logChange('data_change', $tableName, [
|
||
'action' => 'insert',
|
||
'inserted_id' => $id,
|
||
], null, null, ['data' => $data]);
|
||
|
||
return $id;
|
||
}
|
||
|
||
/**
|
||
* 更新表注释
|
||
* @param string $tableName
|
||
* @param string $comment
|
||
* @return bool
|
||
* @throws Exception
|
||
*/
|
||
public function updateTableComment(string $tableName, string $comment): bool
|
||
{
|
||
$this->validateTableName($tableName);
|
||
|
||
// 获取变更前的注释
|
||
$beforeInfo = DB::selectOne("
|
||
SELECT TABLE_COMMENT as comment
|
||
FROM information_schema.TABLES
|
||
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
|
||
", [config('database.connections.mysql.database'), $tableName]);
|
||
|
||
$database = config('database.connections.mysql.database');
|
||
$sql = "ALTER TABLE `{$tableName}` COMMENT = " . DB::getPdo()->quote($comment);
|
||
|
||
DB::statement($sql);
|
||
|
||
// 记录变更日志
|
||
$this->logChange('structure_change', $tableName, [
|
||
'action' => 'update_table_comment',
|
||
'before_comment' => $beforeInfo->comment ?? '',
|
||
'after_comment' => $comment,
|
||
], $sql);
|
||
|
||
return true;
|
||
}
|
||
|
||
/**
|
||
* 更新字段注释
|
||
* @param string $tableName
|
||
* @param string $columnName
|
||
* @param string $comment
|
||
* @return bool
|
||
* @throws Exception
|
||
*/
|
||
public function updateColumnComment(string $tableName, string $columnName, string $comment): bool
|
||
{
|
||
$this->validateTableName($tableName);
|
||
|
||
// 获取字段的完整定义
|
||
$column = DB::selectOne("
|
||
SELECT COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?
|
||
", [config('database.connections.mysql.database'), $tableName, $columnName]);
|
||
|
||
if (!$column) {
|
||
$this->utils->errorThrow('字段不存在');
|
||
}
|
||
|
||
$nullable = $column->IS_NULLABLE === 'YES' ? 'NULL' : 'NOT NULL';
|
||
$default = $column->COLUMN_DEFAULT !== null ? "DEFAULT " . DB::getPdo()->quote($column->COLUMN_DEFAULT) : '';
|
||
$extra = $column->EXTRA ?: '';
|
||
|
||
// 获取变更前的注释
|
||
$beforeColumn = DB::selectOne("
|
||
SELECT COLUMN_COMMENT
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?
|
||
", [config('database.connections.mysql.database'), $tableName, $columnName]);
|
||
$beforeComment = $beforeColumn->COLUMN_COMMENT ?? '';
|
||
|
||
$sql = "ALTER TABLE `{$tableName}` MODIFY COLUMN `{$columnName}` {$column->COLUMN_TYPE} {$nullable} {$default} {$extra} COMMENT " . DB::getPdo()->quote($comment);
|
||
|
||
DB::statement($sql);
|
||
|
||
// 记录变更日志
|
||
$this->logChange('structure_change', $tableName, [
|
||
'action' => 'update_column_comment',
|
||
'column_name' => $columnName,
|
||
'before_comment' => $beforeComment,
|
||
'after_comment' => $comment,
|
||
], $sql);
|
||
|
||
return true;
|
||
}
|
||
|
||
/**
|
||
* 添加索引
|
||
* @param string $tableName
|
||
* @param string $indexName
|
||
* @param array $columns
|
||
* @param bool $unique
|
||
* @param string $type 索引类型：BTREE、HASH、FULLTEXT、SPATIAL
|
||
* @return bool
|
||
* @throws Exception
|
||
*/
|
||
public function addIndex(string $tableName, string $indexName, array $columns, bool $unique = false, string $type = 'BTREE'): bool
|
||
{
|
||
$this->validateTableName($tableName);
|
||
|
||
if (empty($columns)) {
|
||
$this->utils->errorThrow('索引列不能为空');
|
||
}
|
||
|
||
// 验证索引类型
|
||
$allowedTypes = ['BTREE', 'HASH', 'FULLTEXT', 'SPATIAL'];
|
||
$type = strtoupper($type);
|
||
if (!in_array($type, $allowedTypes)) {
|
||
$this->utils->errorThrow('不支持的索引类型');
|
||
}
|
||
|
||
$uniqueStr = $unique ? 'UNIQUE' : '';
|
||
$columnsStr = '`' . implode('`, `', $columns) . '`';
|
||
$usingType = "USING {$type}";
|
||
|
||
$sql = "ALTER TABLE `{$tableName}` ADD {$uniqueStr} INDEX `{$indexName}` ({$columnsStr}) {$usingType}";
|
||
|
||
DB::statement($sql);
|
||
|
||
// 记录变更日志
|
||
$this->logChange('structure_change', $tableName, [
|
||
'action' => 'add_index',
|
||
'index_name' => $indexName,
|
||
'columns' => $columns,
|
||
'unique' => $unique,
|
||
'type' => $type,
|
||
], $sql);
|
||
|
||
return true;
|
||
}
|
||
|
||
/**
|
||
* 删除索引
|
||
* @param string $tableName
|
||
* @param string $indexName
|
||
* @return bool
|
||
* @throws Exception
|
||
*/
|
||
public function dropIndex(string $tableName, string $indexName): bool
|
||
{
|
||
$this->validateTableName($tableName);
|
||
|
||
$sql = "ALTER TABLE `{$tableName}` DROP INDEX `{$indexName}`";
|
||
|
||
DB::statement($sql);
|
||
|
||
// 记录变更日志
|
||
$this->logChange('structure_change', $tableName, [
|
||
'action' => 'drop_index',
|
||
'index_name' => $indexName,
|
||
], $sql);
|
||
|
||
return true;
|
||
}
|
||
|
||
/**
|
||
* 修改表结构（添加/修改/删除字段）
|
||
* @param string $tableName
|
||
* @param string $action add|modify|drop
|
||
* @param array $columnInfo
|
||
* @return bool
|
||
* @throws Exception
|
||
*/
|
||
public function updateTableStructure(string $tableName, string $action, array $columnInfo): bool
|
||
{
|
||
$this->validateTableName($tableName);
|
||
|
||
switch ($action) {
|
||
case 'add':
|
||
return $this->addColumn($tableName, $columnInfo);
|
||
case 'modify':
|
||
return $this->modifyColumn($tableName, $columnInfo);
|
||
case 'drop':
|
||
if (!isset($columnInfo['name'])) {
|
||
$this->utils->errorThrow('字段名不能为空');
|
||
}
|
||
|
||
// 获取变更前的字段信息
|
||
$beforeColumn = $this->getTableColumns($tableName);
|
||
$droppedColumn = null;
|
||
foreach ($beforeColumn as $col) {
|
||
if ($col['name'] === $columnInfo['name']) {
|
||
$droppedColumn = $col;
|
||
break;
|
||
}
|
||
}
|
||
|
||
$sql = "ALTER TABLE `{$tableName}` DROP COLUMN `{$columnInfo['name']}`";
|
||
DB::statement($sql);
|
||
|
||
// 记录变更日志
|
||
$this->logChange('structure_change', $tableName, [
|
||
'action' => 'drop_column',
|
||
'column_name' => $columnInfo['name'],
|
||
'column_info' => $droppedColumn,
|
||
], $sql, $droppedColumn ? ['column' => $droppedColumn] : null);
|
||
|
||
return true;
|
||
default:
|
||
$this->utils->errorThrow('不支持的操作类型');
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 添加字段
|
||
* @param string $tableName
|
||
* @param array $columnInfo
|
||
* @return bool
|
||
* @throws Exception
|
||
*/
|
||
private function addColumn(string $tableName, array $columnInfo): bool
|
||
{
|
||
if (!isset($columnInfo['name']) || !isset($columnInfo['type'])) {
|
||
$this->utils->errorThrow('字段名和类型不能为空');
|
||
}
|
||
|
||
$name = $columnInfo['name'];
|
||
$type = $columnInfo['type'];
|
||
$length = isset($columnInfo['length']) ? "({$columnInfo['length']})" : '';
|
||
$nullable = isset($columnInfo['nullable']) && $columnInfo['nullable'] ? 'NULL' : 'NOT NULL';
|
||
$default = isset($columnInfo['default_value']) && $columnInfo['default_value'] !== null
|
||
? "DEFAULT " . DB::getPdo()->quote($columnInfo['default_value'])
|
||
: '';
|
||
$comment = isset($columnInfo['comment']) ? "COMMENT " . DB::getPdo()->quote($columnInfo['comment']) : '';
|
||
$after = isset($columnInfo['after']) ? "AFTER `{$columnInfo['after']}`" : '';
|
||
|
||
$sql = "ALTER TABLE `{$tableName}` ADD COLUMN `{$name}` {$type}{$length} {$nullable} {$default} {$comment} {$after}";
|
||
|
||
DB::statement($sql);
|
||
|
||
// 记录变更日志
|
||
$this->logChange('structure_change', $tableName, [
|
||
'action' => 'add_column',
|
||
'column_info' => $columnInfo,
|
||
], $sql, null, ['column' => $columnInfo]);
|
||
|
||
return true;
|
||
}
|
||
|
||
/**
|
||
* 解析字段类型（从 column_type 如 varchar(255) 中提取类型和长度）
|
||
* @param string $columnType
|
||
* @return array ['type' => string, 'length' => string]
|
||
*/
|
||
private function parseColumnType(string $columnType): array
|
||
{
|
||
// 匹配类型和长度，如 varchar(255) 或 decimal(10,2)
|
||
if (preg_match('/^(\w+)(?:\(([^)]+)\))?/i', $columnType, $matches)) {
|
||
return [
|
||
'type' => strtoupper($matches[1]),
|
||
'length' => isset($matches[2]) ? $matches[2] : '',
|
||
];
|
||
}
|
||
return ['type' => strtoupper($columnType), 'length' => ''];
|
||
}
|
||
|
||
/**
|
||
* 构建字段类型字符串
|
||
* @param string $type
|
||
* @param string $length
|
||
* @return string
|
||
*/
|
||
private function buildColumnType(string $type, string $length = ''): string
|
||
{
|
||
$type = strtoupper($type);
|
||
// 需要长度的类型
|
||
$typesNeedLength = ['VARCHAR', 'CHAR', 'INT', 'BIGINT', 'TINYINT', 'SMALLINT', 'DECIMAL', 'FLOAT', 'DOUBLE'];
|
||
|
||
if (in_array($type, $typesNeedLength) && !empty($length)) {
|
||
return "{$type}({$length})";
|
||
}
|
||
return $type;
|
||
}
|
||
|
||
/**
|
||
* 修改字段
|
||
* @param string $tableName
|
||
* @param array $columnInfo
|
||
* @return bool
|
||
* @throws Exception
|
||
*/
|
||
private function modifyColumn(string $tableName, array $columnInfo): bool
|
||
{
|
||
if (!isset($columnInfo['name']) || !isset($columnInfo['type'])) {
|
||
$this->utils->errorThrow('字段名和类型不能为空');
|
||
}
|
||
|
||
$name = $columnInfo['name'];
|
||
$type = $columnInfo['type'];
|
||
|
||
// 构建类型和长度
|
||
$length = '';
|
||
if (isset($columnInfo['length']) && !empty($columnInfo['length'])) {
|
||
$length = "({$columnInfo['length']})";
|
||
} else {
|
||
// 如果没有提供长度，尝试从现有字段获取
|
||
$existingColumn = DB::selectOne("
|
||
SELECT COLUMN_TYPE
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?
|
||
", [config('database.connections.mysql.database'), $tableName, $name]);
|
||
|
||
if ($existingColumn) {
|
||
$parsed = $this->parseColumnType($existingColumn->COLUMN_TYPE);
|
||
// 如果类型需要长度但新类型也需要，尝试保留长度
|
||
$typesNeedLength = ['VARCHAR', 'CHAR', 'INT', 'BIGINT', 'TINYINT', 'SMALLINT', 'DECIMAL', 'FLOAT', 'DOUBLE'];
|
||
if (in_array(strtoupper($type), $typesNeedLength) && !empty($parsed['length'])) {
|
||
$length = "({$parsed['length']})";
|
||
}
|
||
}
|
||
}
|
||
|
||
$nullable = isset($columnInfo['nullable']) && $columnInfo['nullable'] ? 'NULL' : 'NOT NULL';
|
||
$default = '';
|
||
if (isset($columnInfo['default_value']) && $columnInfo['default_value'] !== null && $columnInfo['default_value'] !== '') {
|
||
$default = "DEFAULT " . DB::getPdo()->quote($columnInfo['default_value']);
|
||
}
|
||
$comment = isset($columnInfo['comment']) && !empty($columnInfo['comment'])
|
||
? "COMMENT " . DB::getPdo()->quote($columnInfo['comment'])
|
||
: '';
|
||
|
||
// 位置调整
|
||
$after = '';
|
||
if (isset($columnInfo['after']) && !empty($columnInfo['after'])) {
|
||
$after = "AFTER `{$columnInfo['after']}`";
|
||
}
|
||
|
||
// 获取变更前的字段信息
|
||
$beforeColumn = $this->getTableColumns($tableName);
|
||
$beforeColumnInfo = null;
|
||
foreach ($beforeColumn as $col) {
|
||
if ($col['name'] === $name) {
|
||
$beforeColumnInfo = $col;
|
||
break;
|
||
}
|
||
}
|
||
|
||
$sql = "ALTER TABLE `{$tableName}` MODIFY COLUMN `{$name}` {$type}{$length} {$nullable} {$default} {$comment} {$after}";
|
||
|
||
DB::statement($sql);
|
||
|
||
// 记录变更日志
|
||
$this->logChange('structure_change', $tableName, [
|
||
'action' => 'modify_column',
|
||
'column_name' => $name,
|
||
'column_info' => $columnInfo,
|
||
], $sql, $beforeColumnInfo ? ['column' => $beforeColumnInfo] : null, ['column' => $columnInfo]);
|
||
|
||
return true;
|
||
}
|
||
|
||
/**
|
||
* 执行SELECT查询（仅支持SELECT语句）
|
||
* @param string $sql
|
||
* @return array
|
||
* @throws Exception
|
||
*/
|
||
public function executeSelectQuery(string $sql): array
|
||
{
|
||
// 验证SQL安全性
|
||
$trimmedSql = trim($sql);
|
||
$upperSql = strtoupper($trimmedSql);
|
||
|
||
// 必须以SELECT开头
|
||
if (!preg_match('/^\s*SELECT/i', $trimmedSql)) {
|
||
$this->utils->errorThrow('仅支持SELECT查询语句');
|
||
}
|
||
|
||
// 禁止的危险关键字
|
||
$dangerousKeywords = [
|
||
'DROP', 'DELETE', 'UPDATE', 'INSERT', 'ALTER', 'CREATE', 'TRUNCATE',
|
||
'REPLACE', 'GRANT', 'REVOKE', 'EXEC', 'EXECUTE', 'CALL', 'PROCEDURE',
|
||
'INTO OUTFILE', 'INTO DUMPFILE', 'LOAD_FILE', 'LOAD DATA',
|
||
];
|
||
|
||
foreach ($dangerousKeywords as $keyword) {
|
||
if (strpos($upperSql, $keyword) !== false) {
|
||
$this->utils->errorThrow("SQL语句包含危险关键字: {$keyword}");
|
||
}
|
||
}
|
||
|
||
try {
|
||
// 执行查询并限制结果数量
|
||
$results = DB::select($trimmedSql);
|
||
|
||
// 限制返回结果数量（最多1000条）
|
||
$maxResults = 1000;
|
||
if (count($results) > $maxResults) {
|
||
$results = array_slice($results, 0, $maxResults);
|
||
}
|
||
|
||
// 转换为数组格式
|
||
$data = array_map(function ($row) {
|
||
return (array) $row;
|
||
}, $results);
|
||
|
||
// 记录查询日志
|
||
$this->logChange('sql_query', '', [
|
||
'action' => 'select_query',
|
||
'result_count' => count($data),
|
||
], $trimmedSql);
|
||
|
||
return $data;
|
||
} catch (Exception $e) {
|
||
$this->utils->errorThrow('SQL执行失败: ' . $e->getMessage());
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 移除表名前缀（如果存在）
|
||
* @param string $tableName
|
||
* @return string
|
||
*/
|
||
private function removeTablePrefix(string $tableName): string
|
||
{
|
||
$prefix = config('database.connections.mysql.prefix', '');
|
||
if (!empty($prefix) && strpos($tableName, $prefix) === 0) {
|
||
return substr($tableName, strlen($prefix));
|
||
}
|
||
return $tableName;
|
||
}
|
||
|
||
/**
|
||
* 记录数据库变更日志
|
||
* @param string $operationType 操作类型：structure_change, data_change, sql_query
|
||
* @param string $tableName 表名
|
||
* @param array $detail 操作详情
|
||
* @param string|null $sql SQL语句
|
||
* @param array|null $beforeData 变更前数据
|
||
* @param array|null $afterData 变更后数据
|
||
* @return void
|
||
*/
|
||
private function logChange(string $operationType, string $tableName, array $detail, ?string $sql = null, ?array $beforeData = null, ?array $afterData = null): void
|
||
{
|
||
try {
|
||
DB::table('nl_database_change_log')->insert([
|
||
'user_id' => $this->userId ?? 0,
|
||
'table_name' => $tableName,
|
||
'operation_type' => $operationType,
|
||
'operation_detail' => json_encode($detail, JSON_UNESCAPED_UNICODE),
|
||
'sql_statement' => $sql,
|
||
'before_data' => $beforeData ? json_encode($beforeData, JSON_UNESCAPED_UNICODE) : null,
|
||
'after_data' => $afterData ? json_encode($afterData, JSON_UNESCAPED_UNICODE) : null,
|
||
'created_at' => time(),
|
||
]);
|
||
} catch (Exception $e) {
|
||
// 日志记录失败不影响主操作，只记录错误
|
||
\Log::error('数据库变更日志记录失败: ' . $e->getMessage());
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 获取变更记录列表
|
||
* @param int $page
|
||
* @param int $pageSize
|
||
* @param array $filters 筛选条件：table_name, operation_type, start_time, end_time
|
||
* @return array
|
||
*/
|
||
public function getChangeLog(int $page = 1, int $pageSize = 20, array $filters = []): array
|
||
{
|
||
$query = DB::table('database_change_log');
|
||
|
||
// 应用筛选条件
|
||
if (!empty($filters['table_name'])) {
|
||
$query->where('table_name', $filters['table_name']);
|
||
}
|
||
if (!empty($filters['operation_type'])) {
|
||
$query->where('operation_type', $filters['operation_type']);
|
||
}
|
||
if (!empty($filters['start_time'])) {
|
||
$query->where('created_at', '>=', $filters['start_time']);
|
||
}
|
||
if (!empty($filters['end_time'])) {
|
||
$query->where('created_at', '<=', $filters['end_time']);
|
||
}
|
||
|
||
$total = $query->count();
|
||
$items = $query->orderBy('created_at', 'desc')
|
||
->skip(($page - 1) * $pageSize)
|
||
->take($pageSize)
|
||
->get()
|
||
->toArray();
|
||
|
||
// 解析JSON字段
|
||
foreach ($items as &$item) {
|
||
$item = (array) $item;
|
||
if (!empty($item['operation_detail'])) {
|
||
$item['operation_detail'] = json_decode($item['operation_detail'], true);
|
||
}
|
||
if (!empty($item['before_data'])) {
|
||
$item['before_data'] = json_decode($item['before_data'], true);
|
||
}
|
||
if (!empty($item['after_data'])) {
|
||
$item['after_data'] = json_decode($item['after_data'], true);
|
||
}
|
||
}
|
||
|
||
return [
|
||
'page' => $page,
|
||
'size' => $pageSize,
|
||
'total' => $total,
|
||
'page_count' => ceil($total / $pageSize),
|
||
'items' => $items,
|
||
];
|
||
}
|
||
|
||
/**
|
||
* 验证表名（防止SQL注入）
|
||
* @param string $tableName
|
||
* @return void
|
||
* @throws Exception
|
||
*/
|
||
private function validateTableName(string $tableName): void
|
||
{
|
||
if (empty($tableName)) {
|
||
$this->utils->errorThrow('表名不能为空');
|
||
}
|
||
|
||
// 只允许字母、数字、下划线
|
||
if (!preg_match('/^[a-zA-Z0-9_]+$/', $tableName)) {
|
||
$this->utils->errorThrow('表名格式不正确');
|
||
}
|
||
|
||
// 检查表是否存在
|
||
$database = config('database.connections.mysql.database');
|
||
$exists = DB::selectOne("
|
||
SELECT COUNT(*) as count
|
||
FROM information_schema.TABLES
|
||
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
|
||
", [$database, $tableName]);
|
||
|
||
if ($exists->count == 0) {
|
||
$this->utils->errorThrow('表不存在');
|
||
}
|
||
}
|
||
}
|