167 lines
12 KiB
PHP
167 lines
12 KiB
PHP
<?php
|
|
|
|
namespace Tests\InvestMoz;
|
|
|
|
use App\Models\Investor;
|
|
use Illuminate\Database\QueryException;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Facades\Schema;
|
|
use Tests\TestCase;
|
|
|
|
class SchemaLifecycleTest extends TestCase
|
|
{
|
|
private function rejected(string $sql, array $bindings, string $sqlState): void
|
|
{
|
|
DB::beginTransaction();
|
|
try {
|
|
DB::statement($sql, $bindings);
|
|
$this->fail('The database accepted an invalid operation: '.$sql);
|
|
} catch (QueryException $exception) {
|
|
$this->assertSame($sqlState, $exception->errorInfo[0]);
|
|
} finally {
|
|
DB::rollBack();
|
|
}
|
|
}
|
|
|
|
public function test_schema_constraints_events_and_rollback_in_isolated_postgis(): void
|
|
{
|
|
if (getenv('INVESTMOZ_SCHEMA_TESTS') !== '1') {
|
|
$this->markTestSkipped('Set INVESTMOZ_SCHEMA_TESTS=1 only for an isolated disposable *_test database.');
|
|
}
|
|
$this->assertSame('testing', app()->environment());
|
|
$this->assertSame('pgsql', DB::connection()->getDriverName());
|
|
$this->assertStringEndsWith('_test', DB::connection()->getDatabaseName());
|
|
$this->assertSame(DB::connection()->getDatabaseName(), DB::selectOne('SELECT current_database() AS name')->name);
|
|
$this->assertFileDoesNotExist(base_path('bootstrap/cache/config.php'), 'Clear cached configuration before the destructive test.');
|
|
// This manifest describes the reconciled 24 September baseline. Later
|
|
// additive upgrades are exercised by their feature/migration tests.
|
|
$baselinePaths = array_values(array_filter(glob(database_path('migrations/*.php')),
|
|
fn ($path) => strcmp(basename($path), '2026_09_25') < 0));
|
|
// This historical baseline predates several tables required by DatabaseSeeder.
|
|
$this->artisan('migrate:fresh', ['--force' => true, '--path' => $baselinePaths, '--realpath' => true])->assertExitCode(0);
|
|
$manifest = json_decode(file_get_contents(base_path('docs/investmoz/schema.json')), true, 512, JSON_THROW_ON_ERROR);
|
|
|
|
$legacy = json_decode(file_get_contents(base_path('docs/investmoz/legacy-schema.json')), true, 512, JSON_THROW_ON_ERROR);
|
|
foreach (array_merge($manifest['tables'], $legacy['tables']) as $table) {
|
|
$actual = DB::select(
|
|
"SELECT a.attname AS column_name, CASE WHEN a.attnotnull THEN 'NO' ELSE 'YES' END AS is_nullable, format_type(a.atttypid,a.atttypmod) AS pg_type, pg_get_expr(d.adbin,d.adrelid) AS default_expression FROM pg_attribute a LEFT JOIN pg_attrdef d ON d.adrelid=a.attrelid AND d.adnum=a.attnum JOIN pg_class c ON c.oid=a.attrelid JOIN pg_namespace n ON n.oid=c.relnamespace WHERE n.nspname='public' AND c.relname=? AND a.attnum>0 AND NOT a.attisdropped ORDER BY a.attnum",
|
|
[$table['name']],
|
|
);
|
|
$this->assertSame(array_column($table['columns'], 'name'), array_column($actual, 'column_name'), $table['name']);
|
|
foreach ($actual as $position => $column) {
|
|
$this->assertSame($table['columns'][$position]['nullable'] ? 'YES' : 'NO', $column->is_nullable);
|
|
$expectedType = str_replace(['timestamptz', 'char('], ['timestamp with time zone', 'character('], $table['columns'][$position]['type']);
|
|
$this->assertSame($expectedType, $column->pg_type, $table['name'].'.'.$column->column_name);
|
|
$expectedDefault = $table['columns'][$position]['default'];
|
|
$actualDefault = $column->default_expression === null ? null : str_replace(['::text', '::character varying'], '', $column->default_expression);
|
|
$this->assertSame($expectedDefault, $actualDefault, $table['name'].'.'.$column->column_name.' default');
|
|
}
|
|
}
|
|
$expectedIndexes = array_merge(array_column($manifest['indexes'], 'name'), array_column($legacy['indexes'], 'name'));
|
|
foreach ($manifest['constraints'] as $constraint) {
|
|
if ($constraint['kind'] === 'PRIMARY KEY') {
|
|
$expectedIndexes[] = $constraint['name'];
|
|
}
|
|
}
|
|
foreach ($legacy['tables'] as $table) {
|
|
$expectedIndexes[] = $table['name'].'_pkey';
|
|
}
|
|
sort($expectedIndexes);
|
|
$actualIndexes = DB::table('pg_indexes')->where('schemaname', 'public')->whereIn('tablename', array_column(array_merge($manifest['tables'], $legacy['tables']), 'name'))->orderBy('indexname')->pluck('indexname')->all();
|
|
$this->assertSame($expectedIndexes, $actualIndexes);
|
|
foreach ($manifest['indexes'] as $index) {
|
|
$row = DB::selectOne("SELECT indexdef FROM pg_indexes WHERE schemaname='public' AND indexname=?", [$index['name']]);
|
|
$this->assertNotNull($row, $index['name']);
|
|
$this->assertStringContainsString('USING '.$index['method'], $row->indexdef);
|
|
}
|
|
foreach ($manifest['constraints'] as $constraint) {
|
|
$this->assertNotNull(DB::selectOne(
|
|
"SELECT oid FROM pg_constraint WHERE connamespace='public'::regnamespace AND conname=?",
|
|
[$constraint['name']],
|
|
), $constraint['name']);
|
|
}
|
|
$actions = ['CASCADE' => 'c', 'RESTRICT' => 'r', 'SET NULL' => 'n', 'NO ACTION' => 'a'];
|
|
foreach (array_merge($manifest['relationships'], $legacy['relationships']) as $relationship) {
|
|
$actualFk = DB::selectOne("SELECT confdeltype, confupdtype FROM pg_constraint WHERE connamespace='public'::regnamespace AND conname=?", [$relationship['name']]);
|
|
$this->assertNotNull($actualFk, $relationship['name']);
|
|
$this->assertSame($actions[$relationship['on_delete']], $actualFk->confdeltype);
|
|
$this->assertSame($actions[$relationship['on_update']], $actualFk->confupdtype);
|
|
}
|
|
foreach ($manifest['triggers'] as $trigger) {
|
|
$this->assertNotNull(DB::selectOne('SELECT oid FROM pg_trigger WHERE tgname=? AND NOT tgisinternal', [$trigger['name']]));
|
|
}
|
|
|
|
$project = DB::selectOne("INSERT INTO projects(code,name,status,source_system,external_id,budget_amount,budget_currency) VALUES ('TEST-P','Fictício','draft','test','P-1',123456789012345678.90,'MZN') RETURNING id,budget_amount");
|
|
$this->assertSame('123456789012345678.90', $project->budget_amount);
|
|
$this->rejected("INSERT INTO projects(code,name,status) VALUES ('TEST-P','Duplicado','draft')", [], '23505');
|
|
$this->rejected("INSERT INTO projects(code,name,status) VALUES ('BAD-S','Fictício','unknown')", [], '23514');
|
|
$this->rejected("INSERT INTO projects(code,name,status,budget_amount,budget_currency) VALUES ('BAD-M','Fictício','draft',-1,'MZN')", [], '23514');
|
|
$this->rejected("INSERT INTO projects(code,name,status,budget_amount) VALUES ('BAD-C','Fictício','draft',1)", [], '23514');
|
|
DB::update('UPDATE projects SET deleted_at=now() WHERE id=?', [$project->id]);
|
|
DB::insert("INSERT INTO projects(code,name,status) VALUES ('TEST-P','Código reutilizado','draft')");
|
|
$this->rejected("INSERT INTO projects(code,name,status,source_system,external_id) VALUES ('NEW-CODE','Fictício','draft','test','P-1')", [], '23505');
|
|
|
|
$location = DB::selectOne("INSERT INTO geo_locations(name,location_type,code) VALUES ('Local fictício','site','TEST-GEO') RETURNING id");
|
|
DB::insert("INSERT INTO geo_geometries(geo_location_id,geometry_type,geom) VALUES (?,'point',ST_GeomFromText('POINT(32 -25)',4326))", [$location->id]);
|
|
$this->rejected("INSERT INTO geo_geometries(geo_location_id,geometry_type,geom) VALUES (?,'line',ST_GeomFromText('POINT(32 -25)',4326))", [$location->id], '23514');
|
|
$this->rejected("INSERT INTO geo_geometries(geo_location_id,geometry_type,geom) VALUES (?,'polygon',ST_GeomFromText('POLYGON((0 0,1 1,1 0,0 1,0 0))',4326))", [$location->id], '23514');
|
|
$this->rejected("INSERT INTO geo_geometries(geo_location_id,geometry_type,geom) VALUES (?,'point',ST_GeomFromText('POINT EMPTY',4326))", [$location->id], '23514');
|
|
$this->rejected("INSERT INTO geo_geometries(geo_location_id,geometry_type,geom) VALUES (?,'point',ST_GeomFromText('POINT(181 0)',4326))", [$location->id], '23514');
|
|
$this->rejected("INSERT INTO geo_geometries(geo_location_id,geometry_type,geom) VALUES (?,'point',ST_GeomFromText('POINT(1 1)',3857))", [$location->id], '22023');
|
|
$this->rejected("INSERT INTO project_locations(project_id,geo_location_id,role) VALUES (gen_random_uuid(),?,'primary')", [$location->id], '23503');
|
|
|
|
$i1 = Investor::factory()->create();
|
|
$i2 = Investor::factory()->create();
|
|
$contact = DB::selectOne("INSERT INTO contacts(full_name) VALUES ('Contacto fictício') RETURNING id");
|
|
DB::insert('INSERT INTO investor_contacts(investor_id,contact_id) VALUES (?,?),(?,?)', [$i1->id, $contact->id, $i2->id, $contact->id]);
|
|
$this->rejected('INSERT INTO investor_contacts(investor_id,contact_id) VALUES (?,?)', [$i1->id, $contact->id], '23505');
|
|
$this->rejected('INSERT INTO project_documents(project_id,document_id) VALUES (?,gen_random_uuid())', [$project->id], '23503');
|
|
|
|
$audit = DB::selectOne("INSERT INTO audit_events(entity_type,action) VALUES ('test','created') RETURNING id");
|
|
$this->rejected("UPDATE audit_events SET action='tampered' WHERE id=?", [$audit->id], '42501');
|
|
$this->rejected('DELETE FROM audit_events WHERE id=?', [$audit->id], '42501');
|
|
$this->rejected('TRUNCATE audit_events', [], '42501');
|
|
$event = DB::selectOne("INSERT INTO outbox_events(aggregate_type,aggregate_id,event_type,payload,status) VALUES ('test',?,'test.created','{}','pending') RETURNING id", [$project->id]);
|
|
DB::update("UPDATE outbox_events SET status='published',published_at=now() WHERE id=?", [$event->id]);
|
|
$this->rejected("UPDATE outbox_events SET payload='{\"changed\":true}' WHERE id=?", [$event->id], '42501');
|
|
$this->rejected('DELETE FROM outbox_events WHERE id=?', [$event->id], '42501');
|
|
$this->rejected('TRUNCATE outbox_events', [], '42501');
|
|
DB::insert("INSERT INTO inbox_events(message_id,event_type,payload,status) VALUES ('TEST-MESSAGE','test','{}','received')");
|
|
$this->rejected("INSERT INTO inbox_events(message_id,event_type,payload,status) VALUES ('TEST-MESSAGE','test','{}','received')", [], '23505');
|
|
|
|
DB::statement("SET TIME ZONE 'Africa/Maputo'");
|
|
try {
|
|
DB::update("UPDATE projects SET name='Actualizado' WHERE id=?", [$project->id]);
|
|
$age = DB::selectOne('SELECT abs(extract(epoch FROM (CURRENT_TIMESTAMP-updated_at))) AS age FROM projects WHERE id=?', [$project->id]);
|
|
$this->assertLessThan(5.0, (float) $age->age);
|
|
} finally {
|
|
DB::statement("SET TIME ZONE 'UTC'");
|
|
}
|
|
// Index eligibility, not a latency/SLO claim on tiny fixtures.
|
|
DB::statement('SET enable_seqscan = off');
|
|
try {
|
|
$plan = json_encode(DB::select('EXPLAIN (FORMAT JSON) SELECT id FROM geo_geometries WHERE geom && ST_MakeEnvelope(31,-26,33,-24,4326)'));
|
|
$this->assertStringContainsString('imz_geo_geometries_geom_idx', $plan);
|
|
} finally {
|
|
DB::statement('RESET enable_seqscan');
|
|
}
|
|
$this->artisan('migrate', ['--path' => $baselinePaths, '--realpath' => true])->assertExitCode(0); // Ledger idempotency.
|
|
$this->artisan('db:seed', ['--class' => 'InvestMozReferenceSeeder'])->assertExitCode(0);
|
|
$this->assertSame(1, DB::table('projects')->where('source_system', 'example')->where('external_id', 'PROJECT-001')->count());
|
|
$this->artisan('migrate:rollback', ['--step' => 15, '--force' => true])->assertExitCode(0);
|
|
foreach ($manifest['tables'] as $table) {
|
|
$this->assertFalse(Schema::hasTable($table['name']));
|
|
}
|
|
$this->assertTrue(Schema::hasTable('users'));
|
|
$this->assertTrue(Schema::hasTable('opportunities'));
|
|
$this->assertCount(2, DB::select("SELECT extname FROM pg_extension WHERE extname IN ('postgis','pgcrypto')"));
|
|
// Reapplying the partial baseline must not invoke seeders for later schemas.
|
|
$this->artisan('migrate', ['--force' => true, '--path' => $baselinePaths, '--realpath' => true])->assertExitCode(0);
|
|
// This is a separate upgrade batch: ordinary rollback must now revert it.
|
|
$this->artisan('migrate:rollback', ['--force' => true])->assertExitCode(0);
|
|
$this->assertFalse(Schema::hasTable('projects'));
|
|
$this->assertTrue(Schema::hasTable('users'));
|
|
$this->artisan('migrate', ['--force' => true, '--path' => $baselinePaths, '--realpath' => true])->assertExitCode(0);
|
|
}
|
|
}
|