Coloration Syntaxique

Télécharger le script ! - mod phpBB

/* Extrait du projet Cadastre. */


CREATE TABLE Bornes (
     Born_Id smallint UNIQUE NOT NULL,
     Born_X smallint,
     Born_Y smallint,
     CONSTRAINT pk_Born_Id PRIMARY KEY (Born_Id)
);

CREATE TABLE Parcelles (
     Parc_Id smallint UNIQUE NOT NULL,
     Parc_Contenu varchar(15),
     Parc_Expl_Id smallint NOT NULL,
     CONSTRAINT pk_Parc_Id PRIMARY KEY (Parc_Id),
     CONSTRAINT ck_Parc_Contenu CHECK ( Parc_Contenu IN ('Terre', 'Bois', 'Habitation') ),
     CONSTRAINT fk_Parc_Expl_Id FOREIGN KEY (Parc_Expl_Id) REFERENCES Exploitations (Expl_Id)
);

CREATE TABLE Ordre (
     Ordr_Born_Id smallint NOT NULL,
     Ordr_Parc_Id smallint NOT NULL,
     Ordr_Numero smallint,
     CONSTRAINT pk_Ordr PRIMARY KEY (Ordr_Born_Id, Ordr_Parc_Id),
     CONSTRAINT fk_Ordr_Born_Id FOREIGN KEY (Ordr_Born_Id) REFERENCES Bornes (Born_Id),
     CONSTRAINT fk_Ordr_Parc_Id FOREIGN KEY (Ordr_Parc_Id) REFERENCES Parcelles (Parc_Id)
);

/* Etape_1 : */

select Parc_Id, O0.Ordr_Born_Id, O1.Ordr_Born_Id, O2.Ordr_Born_Id
from Parcelles, Ordre O0, Ordre O1, Ordre O2
where O0.Ordr_Parc_Id = Parc_Id and
      O1.Ordr_Parc_Id = Parc_Id and
      O2.Ordr_Parc_Id = Parc_Id and
      O1.Ordr_Numero = O0.Ordr_Numero+1 and
      O2.Ordr_Numero = O1.Ordr_Numero+1
UNION
select Parc_Id, O0.Ordr_Born_Id, O1.Ordr_Born_Id, O2.Ordr_Born_Id
from Parcelles, Ordre O0, Ordre O1, Ordre O2
where O0.Ordr_Parc_Id = Parc_Id and
      O1.Ordr_Parc_Id = Parc_Id and
      O2.Ordr_Parc_Id = Parc_Id and
      O1.Ordr_Numero = 1 and
      O2.Ordr_Numero = 2 and
      O0.Ordr_Numero in (
         select max(Ordr_Numero)
         from Ordre
         where Ordr_Parc_Id = O0.Ordr_Parc_Id )
UNION
select Parc_Id, O0.Ordr_Born_Id, O1.Ordr_Born_Id, O2.Ordr_Born_Id
from Parcelles, Ordre O0, Ordre O1, Ordre O2
where O0.Ordr_Parc_Id = Parc_Id and
      O1.Ordr_Parc_Id = Parc_Id and
      O2.Ordr_Parc_Id = Parc_Id and
      O2.Ordr_Numero = 1 and
      O0.Ordr_Numero = O1.Ordr_Numero-1 and
      O1.Ordr_Numero in (
         select max(Ordr_Numero)
         from Ordre
         where Ordr_Parc_Id = O0.Ordr_Parc_Id )

/* Etape_2 : */

select Parc_Id
from Parcelles
where Parc_Id not in (
   select Parc_Id
   from Parcelles, Ordre O0, Ordre O1, Ordre O2, Bornes B0, Bornes B1, Bornes B2
   where O0.Ordr_Born_Id = B0.Born_Id and
         O1.Ordr_Born_Id = B1.Born_Id and
         O2.Ordr_Born_Id = B2.Born_Id and
         O0.Ordr_Parc_Id = Parc_Id and
         O1.Ordr_Parc_Id = Parc_Id and
         O2.Ordr_Parc_Id = Parc_Id and
         O1.Ordr_Numero = O0.Ordr_Numero+1 and
         O2.Ordr_Numero = O1.Ordr_Numero+1 and
         (B1.Born_X-B0.Born_X)*(B2.Born_Y-B0.Born_Y) -
            (B2.Born_X-B0.Born_X)*(B1.Born_Y-B0.Born_Y) < 0 )
UNION
select Parc_Id
from Parcelles, Ordre O0, Ordre O1, Ordre O2, Bornes B0, Bornes B1, Bornes B2
where O0.Ordr_Born_Id = B0.Born_Id and
      O1.Ordr_Born_Id = B1.Born_Id and
      O2.Ordr_Born_Id = B2.Born_Id and
      O0.Ordr_Parc_Id = Parc_Id and
      O1.Ordr_Parc_Id = Parc_Id and
      O2.Ordr_Parc_Id = Parc_Id and
      O1.Ordr_Numero = 1 and
      O2.Ordr_Numero = 2 and
      O0.Ordr_Numero in (
         select max(Ordr_Numero)
         from Ordre
         where Ordr_Parc_Id = O0.Ordr_Parc_Id) and
         (B1.Born_X-B0.Born_X)*(B2.Born_Y-B0.Born_Y) -
            (B2.Born_X-B0.Born_X)*(B1.Born_Y-B0.Born_Y) < 0 )
UNION
select Parc_Id
from Parcelles, Ordre O0, Ordre O1, Ordre O2, Bornes B0, Bornes B1, Bornes B2
where O0.Ordr_Born_Id = B0.Born_Id and
      O1.Ordr_Born_Id = B1.Born_Id and
      O2.Ordr_Born_Id = B2.Born_Id and
      O0.Ordr_Parc_Id = Parc_Id and
      O1.Ordr_Parc_Id = Parc_Id and
      O2.Ordr_Parc_Id = Parc_Id and
      O2.Ordr_Numero = 1 and
      O0.Ordr_Numero = O1.Ordr_Numero-1 and
      O1.Ordr_Numero in (
         select max(Ordr_Numero)
         from Ordre
         where Ordr_Parc_Id = O0.Ordr_Parc_Id) and
         (B1.Born_X-B0.Born_X)*(B2.Born_Y-B0.Born_Y) -
            (B2.Born_X-B0.Born_X)*(B1.Born_Y-B0.Born_Y) < 0 )