Migration de la base tricoteuses legi dans une structure à plat
Find a file
2026-01-05 14:50:59 +01:00
import_from_canutes_legifrance.sql Add SQL comments 2025-12-27 11:07:46 +01:00
README.md Change script name 2025-12-10 15:39:58 +01:00

legi-flat-db

Migration de la base tricoteuses legi dans une stucture à plat

Installation

Récupérer un dump de la base legi ou la recréer

cf https://git.tricoteuses.fr/logiciels/tricoteuses-legifrance#installation

Vous devez avoir une base legi fonctionnelle pour créer la base f_legi.

Créer la base f_legi

Installer PostgreSQL, et :

sudo su - postgres
createuser f_legi -P # ...et entrez un mot de passe de votre choix
createdb -O f_legi f_legi
psql f_legi
    create extension if not exists dblink;
    create extension if not exists ltree;
    \q

psql legi
    create extension if not exists ltree;
    \q
exit

Générer les données à partir de la base legi

En remplaçant les [*_password] par ceux définis précédemment.

psql -v LEGI_CONN_STRING='host=localhost port=5432 dbname=canutes_legifrance user=[canutes_legifrance_user] password=[canutes_legifrance_password]' postgresql://f_legi:[f_legi_password]]@localhost/f_legi < import_from_canutes_legifrance.sql

Utilisation

Récupérer le sommaire complet en vigueur d'un texte

version naïve

select scta1.*
from scta scta1
where subltree(chemin, 0, 1) = 'LEGITEXT000006069577'
and etat = (select id from etats where etat = 'VIGUEUR')
and not exists 
	(
	select null
    from scta scta2
    where scta2.chemin @> scta1.chemin
    and (
     nlevel(scta2.chemin) > nlevel(scta1.chemin)
	 or nlevel(scta2.chemin) < nlevel(scta1.chemin)
     )
    and scta2.etat <> (select id from etats where etat = 'VIGUEUR')
    )
order by tri_hierarchique;

Mais ici, il manque les "ABROGE_DIFF", tout comme les articles en vigueur, mais contenu dans une section dont les dates de validité sont erronées (ie. la date demandée n'est pas entre date_debut et date_fin de la section)... Donc :

Récupérer le sommaire complet d'un texte à une date donnée

with valid_sections as 
(
select scta1.*
from scta scta1
where subltree(chemin, 0, 1) = 'LEGITEXT000006069577'
and date '2025-09-01' <@ scta1.parents_valid_period
),
invalid_sections as
( 
select scta1.*
from scta scta1
where subltree(chemin, 0, 1) = 'LEGITEXT000006069577'
and date '2025-09-01' between date_debut and date_fin
and scta1.type_objet ='art'
)
select *, 0 as invalid_sections 
from valid_sections
union 
	(
	select invalids.*
	from
		(
		select ivs.*, row_number() over (partition by ivs.dernier_segment order by date_debut) invalid_sections
		from invalid_sections ivs 
		where not exists (select null from valid_sections vs where vs.dernier_segment = ivs.dernier_segment)
		) invalids
	where invalid_sections = 1
	)
order by tri_hierarchique;

--> Ici, on sélectionne, les "en vigueur", en partant du principe que les date_debut et date_fin sont correctes pour les articles. --> Un flag "invalid_sections" permet par ailleurs de repérer les articles en vigueur à la date donnée, mais dont au mons une des section parente n'est pas en vigueur à la même date.

Récupérer le 1er article d'un texte donné

select * from
(
select scta1.*, row_number() over (partition by scta1.type_objet order by scta1.tri_hierarchique ) rn
from scta scta1
where subltree(chemin, 0, 1) = 'LEGITEXT000006069577'
and etat = (select id from etats where etat = 'VIGUEUR')
and not exists 
	(
	select null
    from scta scta2
    where scta2.chemin @> scta1.chemin
    and (
     nlevel(scta2.chemin) > nlevel(scta1.chemin)
	 or nlevel(scta2.chemin) < nlevel(scta1.chemin)
	)
    and scta2.etat <> (select id from etats where etat = 'VIGUEUR')
    )
)
where rn=1 and type_objet='art'
limit 1;