72 lines
6.7 KiB
PHP
72 lines
6.7 KiB
PHP
<?php
|
|
namespace Tempo\Purchasing\SgfSourcing;
|
|
|
|
use Illuminate\Database\ConnectionInterface;
|
|
use Illuminate\Database\Schema\Blueprint;
|
|
use Tempo\Purchasing\Delivery\Installation;
|
|
|
|
final class Schema
|
|
{
|
|
public const RIGHTS=['purchasing_sourcing.prepare','purchasing_sourcing.evaluate','purchasing_sourcing.propose','purchasing_sourcing.confirm'];
|
|
public const TABLES=['ss_suppliers','ss_processes','ss_events','ss_commands'];
|
|
public static function hash():string{return hash_file('sha256',__FILE__);}
|
|
public static function signature(ConnectionInterface $db):string{return Installation::signature($db,[...Installation::tables(),...self::TABLES]);}
|
|
public static function compatible(ConnectionInterface $db):bool{
|
|
$row=$db->table('sgc_installations')->where('module','sourcing')->first();
|
|
return $row&&$row->revision===1&&hash_equals($row->migration_sha256,self::hash())&&hash_equals($row->schema_signature,self::signature($db));
|
|
}
|
|
public static function migrate(ConnectionInterface $db,string $backupHash,string $operator):array{
|
|
if($db->getDriverName()!=='pgsql'||!preg_match('/^[a-f0-9]{64}$/D',$backupHash)||trim($operator)==='')throw new \RuntimeException('Backup e operador obrigatórios.');
|
|
return $db->transaction(function()use($db,$backupHash,$operator){
|
|
$db->select("select pg_advisory_xact_lock(hashtext('tempo.sourcing.install.v1'))");
|
|
if($db->table('sgc_installations')->where('module','sourcing')->exists()){
|
|
if(!self::compatible($db))throw new \RuntimeException('Schema de sourcing divergente.');
|
|
return ['prepared'=>true,'replayed'=>true];
|
|
}
|
|
$old=$db->table('sgc_installations')->where('module','internal-requisitions')->first();
|
|
if(!$old||$old->revision!==2||!hash_equals($old->schema_signature,Installation::signature($db,Installation::tables())))throw new \RuntimeException('Conferir schema anterior antes de ampliar catálogo.');
|
|
$schema=$db->getSchemaBuilder();
|
|
foreach(self::TABLES as $table){
|
|
if($schema->hasTable($table))throw new \RuntimeException('Schema parcial: não sobrescrever.');
|
|
$schema->create($table,function(Blueprint $t)use($table){
|
|
$t->id();foreach(['tenant_id','company_id','unit_id'] as $key)$t->unsignedBigInteger($key);
|
|
if($table==='ss_suppliers'){$t->string('reference');$t->jsonb('payload');$t->unique(['tenant_id','company_id','unit_id','reference']);}
|
|
elseif($table==='ss_processes'){$t->unsignedBigInteger('request_id');$t->unsignedInteger('version');$t->jsonb('payload');$t->unique(['tenant_id','company_id','unit_id','request_id']);}
|
|
elseif($table==='ss_events'){$t->unsignedBigInteger('request_id');$t->unsignedInteger('version');$t->unsignedBigInteger('actor_id');$t->string('action');$t->jsonb('payload');$t->unique(['tenant_id','company_id','unit_id','request_id','version']);}
|
|
else{$t->unsignedBigInteger('actor_id');$t->uuid('operation_key');$t->string('request_hash',64);$t->jsonb('result');$t->unique(['tenant_id','company_id','unit_id','actor_id','operation_key']);}
|
|
$t->timestampTz('created_at');
|
|
});
|
|
$db->statement("ALTER TABLE $table ADD FOREIGN KEY(tenant_id,company_id,unit_id) REFERENCES units(tenant_id,company_id,id)");
|
|
$db->statement("ALTER TABLE $table ADD UNIQUE(tenant_id,company_id,unit_id,id)");
|
|
}
|
|
foreach(['ss_processes','ss_events'] as $table)$db->statement("ALTER TABLE $table ADD FOREIGN KEY(tenant_id,company_id,unit_id,request_id) REFERENCES ir_requests(tenant_id,company_id,unit_id,id)");
|
|
foreach(['ss_events','ss_commands'] as $table){
|
|
$db->statement("ALTER TABLE $table ADD FOREIGN KEY(actor_id) REFERENCES users(id)");
|
|
$db->unprepared("CREATE FUNCTION {$table}_immutable() RETURNS trigger LANGUAGE plpgsql AS \$\$ BEGIN RAISE EXCEPTION 'Preserve sourcing history'; END; \$\$; CREATE TRIGGER {$table}_immutable BEFORE UPDATE OR DELETE ON $table FOR EACH ROW EXECUTE FUNCTION {$table}_immutable();");
|
|
}
|
|
$db->statement("ALTER TABLE ss_processes ADD CHECK(payload->'confirmed' IS NULL OR payload->'confirmed'='null'::jsonb OR (payload->'confirmed'->>'confirmed_by')::bigint<>(payload->'confirmed'->'proposal'->>'proposed_by')::bigint)");
|
|
$db->unprepared(<<<'SQL'
|
|
CREATE FUNCTION ss_process_guard() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
DECLARE requester bigint;
|
|
BEGIN
|
|
IF TG_OP='DELETE' THEN RAISE EXCEPTION 'Preserve sourcing origin'; END IF;
|
|
IF (NEW.tenant_id,NEW.company_id,NEW.unit_id,NEW.request_id) IS DISTINCT FROM (OLD.tenant_id,OLD.company_id,OLD.unit_id,OLD.request_id) OR NEW.payload->'origin' IS DISTINCT FROM OLD.payload->'origin' THEN RAISE EXCEPTION 'Immutable sourcing origin'; END IF;
|
|
IF NEW.version<>OLD.version+1 THEN RAISE EXCEPTION 'Sourcing version must advance once'; END IF;
|
|
IF OLD.payload->'confirmed' IS NOT NULL AND OLD.payload->'confirmed'<>'null'::jsonb THEN RAISE EXCEPTION 'Confirmed award immutable'; END IF;
|
|
SELECT requester_id INTO requester FROM ir_requests WHERE id=NEW.request_id;
|
|
IF NEW.payload->'confirmed' IS NOT NULL AND NEW.payload->'confirmed'<>'null'::jsonb AND (requester=(NEW.payload->'confirmed'->>'confirmed_by')::bigint OR requester=(NEW.payload->'confirmed'->'proposal'->>'proposed_by')::bigint) THEN RAISE EXCEPTION 'Requester cannot award'; END IF;
|
|
RETURN NEW;
|
|
END; $$;
|
|
CREATE TRIGGER ss_process_guard BEFORE UPDATE OR DELETE ON ss_processes FOR EACH ROW EXECUTE FUNCTION ss_process_guard();
|
|
SQL);
|
|
$c=$db->selectOne("select pg_get_constraintdef(oid) as definition from pg_constraint where conrelid='permission_grants'::regclass and conname='permission_grants_scope_check'");
|
|
if(!preg_match('/^CHECK\s*\((.*)\)$/s',$c->definition,$match))throw new \RuntimeException('Catálogo desconhecido.');
|
|
$rights=implode(',',array_map(fn($r)=>$db->getPdo()->quote($r).'::text',self::RIGHTS));
|
|
$db->statement('ALTER TABLE permission_grants DROP CONSTRAINT permission_grants_scope_check');
|
|
$db->statement("ALTER TABLE permission_grants ADD CONSTRAINT permission_grants_scope_check CHECK (({$match[1]}) OR (unit_id IS NOT NULL AND permission::text IN ($rights)))");
|
|
$db->table('sgc_installations')->insert(['module'=>'sourcing','revision'=>1,'schema_signature'=>self::signature($db),'migration_sha256'=>self::hash(),'backup_sha256'=>$backupHash,'operator'=>$operator,'database_operator'=>$db->selectOne('select current_user as role')->role,'prepared_at'=>now()]);
|
|
return ['prepared'=>true,'replayed'=>false,'tables'=>self::TABLES,'access_granted'=>false];
|
|
});
|
|
}
|
|
}
|