39 lines
3.0 KiB
PHP
39 lines
3.0 KiB
PHP
<?php
|
|
|
|
use Illuminate\Contracts\Console\Kernel;
|
|
use Illuminate\Support\Facades\DB;
|
|
|
|
require __DIR__.'/../vendor/autoload.php';
|
|
$app = require __DIR__.'/../bootstrap/app.php';
|
|
$app->make(Kernel::class)->bootstrap();
|
|
$report = DB::transaction(function (): array {
|
|
DB::statement('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY');
|
|
$tables = DB::select("SELECT table_schema,table_name,table_type FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog','information_schema') ORDER BY 1,2");
|
|
$counts = [];
|
|
foreach ($tables as $table) {
|
|
if ($table->table_type === 'BASE TABLE') {
|
|
$counts[$table->table_schema.'.'.$table->table_name] = DB::table($table->table_schema.'.'.$table->table_name)->count();
|
|
}
|
|
}
|
|
|
|
return [
|
|
'captured_at' => gmdate('c'),
|
|
'server' => DB::selectOne('SHOW server_version')->server_version,
|
|
'extensions' => DB::select('SELECT extname,extversion FROM pg_extension ORDER BY extname'),
|
|
'tables' => $tables,
|
|
'columns' => DB::select("SELECT table_schema,table_name,column_name,data_type,udt_name,is_nullable,column_default FROM information_schema.columns WHERE table_schema NOT IN ('pg_catalog','information_schema') ORDER BY table_schema,table_name,ordinal_position"),
|
|
'indexes' => DB::select("SELECT schemaname,tablename,indexname,indexdef FROM pg_indexes WHERE schemaname NOT IN ('pg_catalog','information_schema') ORDER BY 1,2,3"),
|
|
'constraints' => DB::select("SELECT n.nspname AS schema,c.relname AS table_name,k.conname,k.contype,k.convalidated,pg_get_constraintdef(k.oid) AS definition FROM pg_constraint k JOIN pg_class c ON c.oid=k.conrelid JOIN pg_namespace n ON n.oid=c.relnamespace WHERE n.nspname NOT IN ('pg_catalog','information_schema') ORDER BY 1,2,3"),
|
|
'views' => DB::select("SELECT schemaname,viewname,definition FROM pg_views WHERE schemaname NOT IN ('pg_catalog','information_schema') ORDER BY 1,2"),
|
|
'triggers' => DB::select("SELECT n.nspname AS schema,c.relname AS table_name,t.tgname,pg_get_triggerdef(t.oid) AS definition FROM pg_trigger t JOIN pg_class c ON c.oid=t.tgrelid JOIN pg_namespace n ON n.oid=c.relnamespace WHERE NOT t.tgisinternal AND n.nspname NOT IN ('pg_catalog','information_schema') ORDER BY 1,2,3"),
|
|
'row_counts' => $counts,
|
|
];
|
|
});
|
|
$directory = storage_path('app/private/reference-reconciliation-'.gmdate('Ymd-His'));
|
|
if (! mkdir($directory, 0700, true) && ! is_dir($directory)) {
|
|
throw new RuntimeException('Cannot create private evidence directory.');
|
|
}
|
|
$path = $directory.'/inventory.json';
|
|
file_put_contents($path, json_encode($report, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_THROW_ON_ERROR));
|
|
echo json_encode(['path' => $path, 'sha256' => hash_file('sha256', $path), 'tables' => count($report['row_counts']), 'columns' => count($report['columns']), 'indexes' => count($report['indexes']), 'constraints' => count($report['constraints'])], JSON_PRETTY_PRINT | JSON_THROW_ON_ERROR).PHP_EOL;
|