442 lines
18 KiB
PHP
442 lines
18 KiB
PHP
<?php
|
|
|
|
namespace App\Services;
|
|
|
|
use App\Models\SourceDatabase;
|
|
use App\Models\Table;
|
|
use App\Models\Column;
|
|
use App\Models\Index;
|
|
use App\Models\ForeignKey;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Collection;
|
|
|
|
class SchemaExtractionService
|
|
{
|
|
public function __construct(
|
|
private DatabaseConnectionService $connectionService
|
|
) {}
|
|
|
|
/**
|
|
* Extract all tables from source database
|
|
*/
|
|
public function extractTables(SourceDatabase $database): Collection
|
|
{
|
|
$config = $database->getConnectionConfig();
|
|
$connection = $this->connectionService->getConnection($config);
|
|
|
|
try {
|
|
if ($database->driver === 'mssql') {
|
|
return $this->extractTablesMssql($connection);
|
|
}
|
|
|
|
return $this->extractTablesPgsql($connection);
|
|
} finally {
|
|
$this->connectionService->purgeConnection($connection->getName());
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Extract tables from PostgreSQL
|
|
*/
|
|
private function extractTablesPgsql($connection): Collection
|
|
{
|
|
$tables = $connection->select("
|
|
SELECT
|
|
table_schema,
|
|
table_name,
|
|
obj_description(
|
|
(quote_ident(table_schema) || '.' || quote_ident(table_name))::regclass::oid
|
|
) as comment
|
|
FROM information_schema.tables
|
|
WHERE table_schema NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
|
|
AND table_type = 'BASE TABLE'
|
|
ORDER BY table_schema, table_name
|
|
");
|
|
|
|
return collect($tables);
|
|
}
|
|
|
|
/**
|
|
* Extract tables from MSSQL
|
|
*/
|
|
private function extractTablesMssql($connection): Collection
|
|
{
|
|
$tables = $connection->select("
|
|
SELECT
|
|
TABLE_SCHEMA as table_schema,
|
|
TABLE_NAME as table_name,
|
|
CAST(ep.value AS NVARCHAR(MAX)) as comment
|
|
FROM INFORMATION_SCHEMA.TABLES t
|
|
LEFT JOIN sys.tables st ON t.TABLE_NAME = st.name
|
|
LEFT JOIN sys.extended_properties ep ON st.object_id = ep.major_id AND ep.minor_id = 0 AND ep.name = 'MS_Description'
|
|
WHERE t.TABLE_TYPE = 'BASE TABLE'
|
|
AND t.TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA', 'sys')
|
|
ORDER BY TABLE_SCHEMA, TABLE_NAME
|
|
");
|
|
|
|
return collect($tables);
|
|
}
|
|
|
|
/**
|
|
* Extract columns for a specific table
|
|
*/
|
|
public function extractColumns(SourceDatabase $database, string $schema, string $table): Collection
|
|
{
|
|
$config = $database->getConnectionConfig();
|
|
$connection = $this->connectionService->getConnection($config);
|
|
|
|
try {
|
|
if ($database->driver === 'mssql') {
|
|
return $this->extractColumnsMssql($connection, $schema, $table);
|
|
}
|
|
|
|
return $this->extractColumnsPgsql($connection, $schema, $table);
|
|
} finally {
|
|
$this->connectionService->purgeConnection($connection->getName());
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Extract columns from PostgreSQL
|
|
*/
|
|
private function extractColumnsPgsql($connection, string $schema, string $table): Collection
|
|
{
|
|
$columns = $connection->select("
|
|
SELECT
|
|
c.column_name,
|
|
c.data_type,
|
|
c.udt_name,
|
|
c.ordinal_position,
|
|
CASE WHEN c.is_nullable = 'YES' THEN true ELSE false END as is_nullable,
|
|
c.column_default,
|
|
c.character_maximum_length,
|
|
c.numeric_precision,
|
|
c.numeric_scale,
|
|
c.datetime_precision,
|
|
pgd.description as comment,
|
|
CASE WHEN pk.column_name IS NOT NULL THEN true ELSE false END as is_primary_key,
|
|
CASE WHEN fk.column_name IS NOT NULL THEN true ELSE false END as is_foreign_key,
|
|
CASE WHEN idx.column_name IS NOT NULL THEN true ELSE false END as is_indexed
|
|
FROM information_schema.columns c
|
|
LEFT JOIN pg_catalog.pg_statio_all_tables AS st ON c.table_schema = st.schemaname AND c.table_name = st.relname
|
|
LEFT JOIN pg_catalog.pg_description pgd ON pgd.objoid = st.relid AND pgd.objsubid = c.ordinal_position
|
|
LEFT JOIN (
|
|
SELECT kcu.column_name
|
|
FROM information_schema.table_constraints tc
|
|
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
|
|
WHERE tc.constraint_type = 'PRIMARY KEY'
|
|
AND tc.table_schema = ?
|
|
AND tc.table_name = ?
|
|
) pk ON c.column_name = pk.column_name
|
|
LEFT JOIN (
|
|
SELECT DISTINCT kcu.column_name
|
|
FROM information_schema.table_constraints tc
|
|
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
|
|
WHERE tc.constraint_type = 'FOREIGN KEY'
|
|
AND tc.table_schema = ?
|
|
AND tc.table_name = ?
|
|
) fk ON c.column_name = fk.column_name
|
|
LEFT JOIN (
|
|
SELECT DISTINCT kcu.column_name
|
|
FROM pg_indexes i
|
|
JOIN information_schema.key_column_usage kcu ON i.indexname = kcu.constraint_name
|
|
WHERE i.schemaname = ?
|
|
AND i.tablename = ?
|
|
) idx ON c.column_name = idx.column_name
|
|
WHERE c.table_schema = ?
|
|
AND c.table_name = ?
|
|
ORDER BY c.ordinal_position
|
|
", [$schema, $table, $schema, $table, $schema, $table, $schema, $table]);
|
|
|
|
return collect($columns);
|
|
}
|
|
|
|
/**
|
|
* Extract columns from MSSQL
|
|
*/
|
|
private function extractColumnsMssql($connection, string $schema, string $table): Collection
|
|
{
|
|
$columns = $connection->select("
|
|
SELECT
|
|
c.COLUMN_NAME as column_name,
|
|
c.DATA_TYPE as data_type,
|
|
c.DATA_TYPE as udt_name,
|
|
c.ORDINAL_POSITION as ordinal_position,
|
|
CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END as is_nullable,
|
|
c.COLUMN_DEFAULT as column_default,
|
|
c.CHARACTER_MAXIMUM_LENGTH as character_maximum_length,
|
|
c.NUMERIC_PRECISION as numeric_precision,
|
|
c.NUMERIC_SCALE as numeric_scale,
|
|
NULL as datetime_precision,
|
|
CAST(ep.value AS NVARCHAR(MAX)) as comment,
|
|
CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END as is_primary_key,
|
|
CASE WHEN fk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END as is_foreign_key,
|
|
CASE WHEN idx.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END as is_indexed
|
|
FROM INFORMATION_SCHEMA.COLUMNS c
|
|
LEFT JOIN sys.columns sc ON c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.name
|
|
LEFT JOIN sys.extended_properties ep ON sc.object_id = ep.major_id AND sc.column_id = ep.minor_id AND ep.name = 'MS_Description'
|
|
LEFT JOIN (
|
|
SELECT kcu.COLUMN_NAME
|
|
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
|
|
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
|
|
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
|
|
AND tc.TABLE_SCHEMA = ?
|
|
AND tc.TABLE_NAME = ?
|
|
) pk ON c.COLUMN_NAME = pk.COLUMN_NAME
|
|
LEFT JOIN (
|
|
SELECT DISTINCT kcu.COLUMN_NAME
|
|
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
|
|
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
|
|
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
|
|
AND tc.TABLE_SCHEMA = ?
|
|
AND tc.TABLE_NAME = ?
|
|
) fk ON c.COLUMN_NAME = fk.COLUMN_NAME
|
|
LEFT JOIN (
|
|
SELECT DISTINCT ic.name as COLUMN_NAME
|
|
FROM sys.indexes i
|
|
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
|
|
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
|
|
WHERE i.object_id = OBJECT_ID(?)
|
|
) idx ON c.COLUMN_NAME = idx.COLUMN_NAME
|
|
WHERE c.TABLE_SCHEMA = ?
|
|
AND c.TABLE_NAME = ?
|
|
ORDER BY c.ORDINAL_POSITION
|
|
", [$schema, $table, $schema, $table, $schema . '.' . $table, $schema, $table]);
|
|
|
|
return collect($columns);
|
|
}
|
|
|
|
/**
|
|
* Extract indexes for a specific table
|
|
*/
|
|
public function extractIndexes(SourceDatabase $database, string $schema, string $table): Collection
|
|
{
|
|
$config = $database->getConnectionConfig();
|
|
$connection = $this->connectionService->getConnection($config);
|
|
|
|
try {
|
|
if ($database->driver === 'mssql') {
|
|
return $this->extractIndexesMssql($connection, $schema, $table);
|
|
}
|
|
|
|
return $this->extractIndexesPgsql($connection, $schema, $table);
|
|
} finally {
|
|
$this->connectionService->purgeConnection($connection->getName());
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Extract indexes from PostgreSQL
|
|
*/
|
|
private function extractIndexesPgsql($connection, string $schema, string $table): Collection
|
|
{
|
|
$indexes = $connection->select("
|
|
SELECT
|
|
i.indexname as index_name,
|
|
i.indexdef as definition,
|
|
CASE WHEN i.indexdef LIKE '%UNIQUE%' THEN true ELSE false END as is_unique,
|
|
CASE WHEN con.contype = 'p' THEN true ELSE false END as is_primary,
|
|
'btree' as index_type,
|
|
ARRAY(
|
|
SELECT kcu.column_name
|
|
FROM information_schema.key_column_usage kcu
|
|
WHERE kcu.constraint_name = i.indexname
|
|
ORDER BY kcu.ordinal_position
|
|
) as columns
|
|
FROM pg_indexes i
|
|
LEFT JOIN pg_catalog.pg_constraint con ON con.conname = i.indexname
|
|
WHERE i.schemaname = ?
|
|
AND i.tablename = ?
|
|
", [$schema, $table]);
|
|
|
|
return collect($indexes);
|
|
}
|
|
|
|
/**
|
|
* Extract indexes from MSSQL
|
|
*/
|
|
private function extractIndexesMssql($connection, string $schema, string $table): Collection
|
|
{
|
|
$indexes = $connection->select("
|
|
SELECT
|
|
i.name as index_name,
|
|
'CREATE ' + CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END + 'INDEX ' + i.name + ' ON ' + QUOTENAME(?) + '.' + QUOTENAME(?) + ' (...)' as definition,
|
|
CASE WHEN i.is_unique = 1 THEN 1 ELSE 0 END as is_unique,
|
|
CASE WHEN i.is_primary_key = 1 THEN 1 ELSE 0 END as is_primary,
|
|
i.type_desc as index_type,
|
|
(
|
|
SELECT STRING_AGG(c.name, ',')
|
|
FROM sys.index_columns ic
|
|
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
|
|
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
|
|
ORDER BY ic.key_ordinal
|
|
) as columns
|
|
FROM sys.indexes i
|
|
WHERE i.object_id = OBJECT_ID(?)
|
|
AND i.type > 0
|
|
", [$schema, $table, $schema . '.' . $table]);
|
|
|
|
return collect($indexes);
|
|
}
|
|
|
|
/**
|
|
* Extract foreign keys for a specific table
|
|
*/
|
|
public function extractForeignKeys(SourceDatabase $database, string $schema, string $table): Collection
|
|
{
|
|
$config = $database->getConnectionConfig();
|
|
$connection = $this->connectionService->getConnection($config);
|
|
|
|
try {
|
|
if ($database->driver === 'mssql') {
|
|
return $this->extractForeignKeysMssql($connection, $schema, $table);
|
|
}
|
|
|
|
return $this->extractForeignKeysPgsql($connection, $schema, $table);
|
|
} finally {
|
|
$this->connectionService->purgeConnection($connection->getName());
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Extract foreign keys from PostgreSQL
|
|
*/
|
|
private function extractForeignKeysPgsql($connection, string $schema, string $table): Collection
|
|
{
|
|
$foreignKeys = $connection->select("
|
|
SELECT
|
|
tc.constraint_name,
|
|
kcu.column_name,
|
|
ccu.table_schema as referenced_schema,
|
|
ccu.table_name as referenced_table,
|
|
ccu.column_name as referenced_column,
|
|
rc.update_rule as on_update,
|
|
rc.delete_rule as on_delete
|
|
FROM information_schema.table_constraints tc
|
|
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
|
|
JOIN information_schema.constraint_column_usage ccu ON tc.constraint_name = ccu.constraint_name
|
|
JOIN information_schema.referential_constraints rc ON tc.constraint_name = rc.constraint_name
|
|
WHERE tc.constraint_type = 'FOREIGN KEY'
|
|
AND tc.table_schema = ?
|
|
AND tc.table_name = ?
|
|
", [$schema, $table]);
|
|
|
|
return collect($foreignKeys);
|
|
}
|
|
|
|
/**
|
|
* Extract foreign keys from MSSQL
|
|
*/
|
|
private function extractForeignKeysMssql($connection, string $schema, string $table): Collection
|
|
{
|
|
$foreignKeys = $connection->select("
|
|
SELECT
|
|
fk.name as constraint_name,
|
|
c.name as column_name,
|
|
OBJECT_SCHEMA_NAME(fk.referenced_object_id) as referenced_schema,
|
|
OBJECT_NAME(fk.referenced_object_id) as referenced_table,
|
|
rc.name as referenced_column,
|
|
CASE WHEN fk.update_referential_action = 1 THEN 'CASCADE' WHEN fk.update_referential_action = 2 THEN 'SET_NULL' WHEN fk.update_referential_action = 3 THEN 'SET_DEFAULT' ELSE 'NO_ACTION' END as on_update,
|
|
CASE WHEN fk.delete_referential_action = 1 THEN 'CASCADE' WHEN fk.delete_referential_action = 2 THEN 'SET_NULL' WHEN fk.delete_referential_action = 3 THEN 'SET_DEFAULT' ELSE 'NO_ACTION' END as on_delete
|
|
FROM sys.foreign_keys fk
|
|
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
|
|
JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
|
|
JOIN sys.columns rc ON fkc.referenced_object_id = rc.object_id AND fkc.referenced_column_id = rc.column_id
|
|
WHERE fk.parent_object_id = OBJECT_ID(?)
|
|
", [$schema . '.' . $table]);
|
|
|
|
return collect($foreignKeys);
|
|
}
|
|
|
|
/**
|
|
* Save extracted schema to database
|
|
*/
|
|
public function saveSchema(SourceDatabase $database): void
|
|
{
|
|
$tables = $this->extractTables($database);
|
|
|
|
foreach ($tables as $tableData) {
|
|
$table = Table::firstOrCreate(
|
|
[
|
|
'source_database_id' => $database->id,
|
|
'schema_name' => $tableData->table_schema,
|
|
'table_name' => $tableData->table_name,
|
|
],
|
|
['comment' => $tableData->comment]
|
|
);
|
|
|
|
$this->saveColumns($table, $this->extractColumns($database, $tableData->table_schema, $tableData->table_name));
|
|
$this->saveIndexes($table, $this->extractIndexes($database, $tableData->table_schema, $tableData->table_name));
|
|
$this->saveForeignKeys($table, $this->extractForeignKeys($database, $tableData->table_schema, $tableData->table_name));
|
|
|
|
$table->update(['last_checked_at' => now()]);
|
|
}
|
|
|
|
$database->update(['last_synced_at' => now()]);
|
|
}
|
|
|
|
/**
|
|
* Save columns to database
|
|
*/
|
|
private function saveColumns(Table $table, Collection $columns): void
|
|
{
|
|
foreach ($columns as $columnData) {
|
|
Column::updateOrCreate(
|
|
[
|
|
'table_id' => $table->id,
|
|
'column_name' => $columnData->column_name,
|
|
],
|
|
(array) $columnData
|
|
);
|
|
}
|
|
|
|
// Remove deleted columns
|
|
$columnNames = $columns->pluck('column_name');
|
|
$table->columns()->whereNotIn('column_name', $columnNames)->delete();
|
|
}
|
|
|
|
/**
|
|
* Save indexes to database
|
|
*/
|
|
private function saveIndexes(Table $table, Collection $indexes): void
|
|
{
|
|
foreach ($indexes as $indexData) {
|
|
Index::updateOrCreate(
|
|
[
|
|
'table_id' => $table->id,
|
|
'index_name' => $indexData->index_name,
|
|
],
|
|
[
|
|
'columns' => is_array($indexData->columns) ? $indexData->columns : json_decode($indexData->columns, true) ?? [],
|
|
'is_unique' => $indexData->is_unique,
|
|
'is_primary' => $indexData->is_primary,
|
|
'index_type' => $indexData->index_type,
|
|
'definition' => $indexData->definition,
|
|
]
|
|
);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Save foreign keys to database
|
|
*/
|
|
private function saveForeignKeys(Table $table, Collection $foreignKeys): void
|
|
{
|
|
foreach ($foreignKeys as $fkData) {
|
|
ForeignKey::updateOrCreate(
|
|
[
|
|
'table_id' => $table->id,
|
|
'constraint_name' => $fkData->constraint_name,
|
|
'column_name' => $fkData->column_name,
|
|
],
|
|
[
|
|
'referenced_table' => $fkData->referenced_table,
|
|
'referenced_column' => $fkData->referenced_column,
|
|
'on_update' => $fkData->on_update,
|
|
'on_delete' => $fkData->on_delete,
|
|
]
|
|
);
|
|
}
|
|
}
|
|
}
|