Théophile Choutri's questions

Estou usando o PostgreSQL 17

Estou modelando um índice de pacote para o ecossistema Haskell, e um recurso útil é determinar dependências transitivas. Pacotes Haskell podem ser normalizados como:

Package 
(name + package-specific metadata)
 \-> Releases 
    (version + release-specific metadata like synopsis, attached data files)
   \-> Components 
      (library, executable, test suite, benchmark suite)
     \-> Dependencies 
        (Each component declares a dependency of a package name and version expression).

(Cada uma dessas seções é uma tabela e elas são vinculadas por relacionamentos um-para-muitos. Um pacote vincula-se a muitas versões, cada versão vincula-se a muitos componentes, cada componente vincula-se a muitas dependências)

Para o propósito do meu próprio esclarecimento, primeiro reduzi a complexidade do modelo para criar um CTE que faz o que espero. Especialmente, não uso bigints como PKs da tabela na minha base de código, mas UUIDs.

(O dbfiddle completo está disponível em https://dbfiddle.uk/hVOmMdYQ )

-- Data model where packages and versions are combined, 
-- and dependencies refer to packages

create table packages (
  package_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text unique not null,
  version int[] not null
);

create unique index on packages(name, version);

create table dependencies (
  dependency_id bigint generated always as identity PRIMARY KEY,
  dependent_id bigint references packages(package_id),
  depended_id bigint references packages(package_id) 
);

create unique index on dependencies(dependent_id, depended_id);

E aqui estão os dados:

insert into packages (name, version) values ('base', '{1,0,0,0}');
insert into packages (name, version) values ('vector', '{0,0,7,0}');
insert into packages (name, version) values ('random', '{0,1,5,8}');
insert into packages (name, version) values ('unix', '{1,2,1,0}');
insert into packages (name, version) values ('time', '{3,14,1,2}');

insert into dependencies (dependent_id, depended_id) values (2, 1);
insert into dependencies (dependent_id, depended_id) values (3, 1);
insert into dependencies (dependent_id, depended_id) values (3, 2);
insert into dependencies (dependent_id, depended_id) values (4, 1);
insert into dependencies (dependent_id, depended_id) values (5, 1);
insert into dependencies (dependent_id, depended_id) values (5, 3);
insert into dependencies (dependent_id, depended_id) values (5, 4);

Aqui está um resultado preliminar:

select dependent.package_id, dependent.name as dependent, depended.name as depended
  from dependencies as d1
  inner join packages as dependent on d1.dependent_id = dependent.package_id
  inner join packages as depended  on d1.depended_id = depended.package_id;

Até agora, tudo parece bom. Então, fiz este CTE recursivo para criar uma visão de dependências transitivas, com breadcrumbs:

with recursive transitive_dependencies ( dependent_id, dependent, depended_id, breadcrumbs) as 
( select dependent.package_id as dependent_id
       , dependent.name as dependent
       , depended.package_id as depended_id
       , concat_ws(' > ', dependent.name, depended.name) as breadcrumbs
  from dependencies as d1
    inner join packages as dependent on d1.dependent_id = dependent.package_id
    inner join packages as depended  on d1.depended_id = depended.package_id
    where dependent_id = 5

  union all
  
  select dependent.package_id as dependent_id
       , dependent.name as dependent
       , depended.package_id as depended_id
       , concat_ws(' > ', t2.breadcrumbs, depended.name) as breadcrumbs   

  from dependencies as d1
     inner join packages as dependent on d1.dependent_id = dependent.package_id
     inner join packages as depended  on d1.depended_id = depended.package_id
     inner join transitive_dependencies as t2 on t2.depended_id = dependent.package_id -- ← This is where we refer to the CTE
)

  cycle dependent_id set is_cycle using path
 
select t3.dependent_id
     , t3.dependent
     , t3.depended_id
     , t3.breadcrumbs
from transitive_dependencies as t3;

Vejam só, funciona!

Agora, estou pensando em dividir as coisas um pouco mais. Ou seja, pacote e release serão separados. Isso se deve ao fato de que há alguns metadados específicos para o ecossistema Haskell que têm como alvo a noção de "pacote" e alguns que são relevantes apenas para "releases", e eles não são intercambiáveis.

-- Data model where packages and releases are separated

create table packages2 (
  package_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text unique not null
);

create table releases2 (
  release_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  package_id bigint references packages2,
  version text not null
);

create unique index on releases2(package_id, version);

create table dependencies2 (
  dependency_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  release_id bigint references releases2 not null,
  package_id bigint references packages2 not null,
  requirement int[] not null
);

E aqui estão os dados

insert into packages2 (name) values ('base'); -- 1
insert into packages2 (name) values ('vector'); -- 2
insert into packages2 (name) values ('random'); -- 3
insert into packages2 (name) values ('unix'); -- 4
insert into packages2 (name) values ('time'); -- 5

insert into releases2 (package_id, version) values (1, '{1,0,0,0}');
insert into releases2 (package_id, version) values (2, '{0,0,7,0}');
insert into releases2 (package_id, version) values (3, '{0,1,5,8}');
insert into releases2 (package_id, version) values (4, '{1,2,1,0}');
insert into releases2 (package_id, version) values (5, '{3,14,1,2}');


insert into dependencies2 (release_id, package_id, requirement) values ( 2, 1, '== 1.0.0.0' );
insert into dependencies2 (release_id, package_id, requirement) values ( 3, 1, '== 1.0.0.0' );
insert into dependencies2 (release_id, package_id, requirement) values ( 3, 2, '>= 0.0.7.0' );
insert into dependencies2 (release_id, package_id, requirement) values ( 4, 1, '== 1.0.0.0' );
insert into dependencies2 (release_id, package_id, requirement) values ( 5, 1, '== 1.0.0.0' );
insert into dependencies2 (release_id, package_id, requirement) values ( 5, 3, '<= 0.1.5.8' );
insert into dependencies2 (release_id, package_id, requirement) values ( 5, 4, '== 1.2.1.0' );

E tentei aplicar a lição do CTE acima a este esquema:

with recursive transitive_dependencies2 ( dependent_id, dependent, dependency_id, breadcrumbs) as
(select p2.package_id as dependent_id
       , p2.name as dependent
       , p3.package_id as dependency_id
       , concat_ws(' > ', p2.name, p3.name) as breadcrumbs
  from dependencies2 as d0
    -- Dependent releases
    inner join releases2 as r1 on d0.release_id = r1.release_id
    -- Dependent packages
    inner join packages2 as p2 on r1.package_id = p2.package_id
    -- Dependencies packages
    inner join packages2 as p3 on d0.package_id = p3.package_id
  where r1.release_id = 5

  union

  select p2.package_id as dependent_id
       , p2.name as dependent
       , p3.package_id as dependency_id
       , concat_ws(' > ', p2.name, p3.name) as breadcrumbs
  from dependencies2 as d0
    -- Dependent releases
    inner join releases2 as r1 on d0.release_id = r1.release_id
    -- Dependent packages
    inner join packages2 as p2 on r1.package_id = p2.package_id
    -- Dependencies packages
    inner join packages2 as p3 on d0.package_id = p3.package_id
    inner join transitive_dependencies2 as t2 on t2.dependency_id = p2.package_id ← This is where we refer to the CTE
)

    cycle dependent_id set is_cycle using path

select t3.dependent_id
     , t3.dependent
     , t3.dependency_id
     , t3.breadcrumbs
from transitive_dependencies2 as t3;

Infelizmente, isso não dá o resultado esperado:

Minha pergunta é a seguinte: Como posso construir minha intuição para dividir ainda mais um CTE que funcione, em tabelas mais granulares? Ainda sou muito novo em tudo isso, e este é meu primeiro caso de uso "real" de CTEs.

Ficarei feliz em esclarecer ou esclarecer coisas.

Por meio disso, também estou interessado nas melhores práticas quando se trata de modelagem de dados. Fui avisado no passado contra o armazenamento de arrays de chaves estrangeiras, por exemplo, e para me esforçar e atingir formas normais e dividir entidades que têm ciclos de vida diferentes.