. , , ,

,,,

— ,

. . (.. ) . , , , , . . , , . .

, , Interbase 6.

, , . , , . , .

, , . , , . .

.

. . . :

.

.

( ) .

. , . , . . - . SQL:

CREATE TABLE "CATALOG" (

"ID" INTEGER NOT NULL PRIMARY KEY,

"NAME" VARCHAR(200) CHARACTER SET WIN1251 NOT NULL,

"PARENT_ID" INTEGER

);

. . . PARENT_ID ? ( ID) + , . . , , NULL, . , , , 0, (1) PARENT_ID. PARENT_ID NULL. PARENT_ID :

"PARENT_ID" INTEGER

CHECK(

("PARENT_ID" IS NULL)

OR

( "PARENT_ID" = ANY(SELECT "ID" FROM "CATALOG") )

)

( ,

.

, ..

. ,

. ..)

( ). , . Level. :

CREATE TABLE "CATALOG" (

"ID" INTEGER NOT NULL PRIMARY KEY,

"NAME" VARCHAR(200) CHARACTER SET WIN1251 NOT NULL,

"PARENT_ID" INTEGER

CHECK(

"PARENT_ID" = ANY(SELECT "ID" FROM "CATALOG") or "PARENT_ID" is NULL

),

"LEVEL" INTEGER DEFAULT 1 NOT NULL

);

.

. .

n- , n

, ( ) .

5, SQL- :

CREATE TABLE "CATALOG2" (

"LEVEL" INTEGER NOT NULL,

"OFFSET" SMALLINT NOT NULL CHECK("OFFSET" > 0 and "OFFSET" < 6),

"NAME_1" VARCHAR(200) CHARACTER SET WIN1251,

"NAME_2" VARCHAR(200) CHARACTER SET WIN1251,

"NAME_3" VARCHAR(200) CHARACTER SET WIN1251,

"NAME_4" VARCHAR(200) CHARACTER SET WIN1251,

"NAME_5" VARCHAR(200) CHARACTER SET WIN1251,

"PARENT_LEVEL" INTEGER,

"PARENT_OFFSET" SMALLINT CHECK(("PARENT_OFFSET" > 0 and "PARENT_OFFSET" < 6) or ("PARENT_OFFSET" is NULL)),

CONSTRAINT "PK_CATALOG2" PRIMARY KEY("LEVEL","OFFSET")

);

, , . , .

. , CATALOG_LEVEL_1 , CATALOG_LEVEL_2 , .. .

CREATE TABLE "CATALOG3_LEVEL1" (

"ID" INTEGER NOT NULL PRIMARY KEY,

"NAME" VARCHAR(200) CHARACTER SET WIN1251 NOT NULL

);

CREATE TABLE "CATALOG3_LEVEL2" (

"ID" INTEGER NOT NULL UNIQUE,

"NAME" VARCHAR(200) CHARACTER SET WIN1251 NOT NULL,

"PARENT_ID" INTEGER NOT NULL REFERENCES "CATALOG3_LEVEL1"("ID"),

PRIMARY KEY("ID","PARENT_ID")

);

CREATE TABLE "CATALOG3_LEVEL3" (

"ID" INTEGER NOT NULL UNIQUE,

"NAME" VARCHAR(200) CHARACTER SET WIN1251 NOT NULL,

"PARENT_ID" INTEGER NOT NULL REFERENCES "CATALOG3_LEVEL1"("ID"),

"PARENT_ID2" INTEGER NOT NULL REFERENCES "CATALOG3_LEVEL2"("ID"),

PRIMARY KEY ("ID", "PARENT_ID", "PARENT_ID2")

);

, CATALOG3_LEVEL2. , . , . .

. , , . , , , , . 9 ( ) 5 . ? , . . .

:

CREATE TABLE "CATALOG4" (

"ID" DECIMAL(5) NOT NULL PRIMARY KEY,

"NAME" VARCHAR(200) CHARACTER SET WIN1251 NOT NULL

);

ID. , . , ID = 00001, 00002. , , ID = 00031, .. . . , , , . . , , , :

CREATE TABLE "CATALOG5" (

"ID" INTEGER NOT NULL PRIMARY KEY,

"NAME" VARCHAR(200) CHARACTER SET WIN1251 NOT NULL,

"PARENT_ID" INTEGER

CHECK(

"PARENT_ID" = ANY(SELECT "ID" FROM "CATALOG") or "PARENT_ID" is NULL

),

"LOW" INTEGER NOT NULL,

"HIGH" INTEGER NOT NULL

);

. LOW HIGH . (. ). . .

, . , :

;

;

;

, , .

. :

, . , , (PARENT_ID ID ). ( ) :

SELECT ID FROM CATALOG WHERE PARENT_ID = < id >

. , , . :

SELECT ID FROM < > where < > = < >

, ID = 10 PARENT_ID = 5 :

SELECT ID FROM CATALOG3_LEVEL3 WHERE PARENT_ID=5 AND PARENT_ID2 = 10

c , . (ID = 1) ID 11,21,31,41, 91. :

SELECT ID FROM CATALOG4 WHERE ID IN (11,21,31,41,51,61,71,81,91)

(ID = 10000) 11000, 12000,1300019000.

, . .

, , . .

ID . (UNION) .

select "ID",'1' as "LEVEL" from CATALOG3_LEVEL2 where PARENT_ID = 1

union

select "ID",'2' as "LEVEL" from CATALOG3_LEVEL3 where PARENT_ID = 1

LEVEL , ID . , , .. , ID.

. .

ID 10001 19999, 2000129999 ..

, . ID = 1, 11 .. , ID , ID .

LOW HIGH .

- . .

,

. , .

. - .

, .

.

,

, . . :

CREATE PROCEDURE GET_PARENTS (ID INTEGER)

RETURNS (E_ID INTEGER, NAME CHAR(200))

AS

declare variable P_ID integer;

BEGIN

select PARENT_ID from CATALOG where ID = :ID into :ID;

WHILE (ID > 0) DO

BEGIN

SELECT C.ID, C.PARENT_ID, C.NAME

FROM CATALOG C

WHERE ID = :ID

INTO :E_ID, :P_ID,:NAME;

ID=P_ID;

SUSPEND;

END

END ^

,

.

, ,

:

insert into CATALOG(name,parent_id) values( _win1251 , < >);

: . :

CREATE TRIGGER "CATALOG_AFTER_DEL" FOR "CATALOG"

ACTIVE AFTER DELETE POSITION 0

AS

BEGIN

delete from "CATALOG" where "PARENT_ID" = OLD."ID";

END ^

: .

UPDATE CATALOG SET PARENT_ID = < > WHERE ID = < >

, . Level :

CREATE EXCEPTION "WRONG_LEVEL" ' ';

/*

- Level ID

*/

CREATE TRIGGER "CATALOG_BEFORE_INS" FOR "CATALOG"

ACTIVE BEFORE INSERT POSITION 0

AS

declare variable parent_level integer;

BEGIN

if (NEW."ID" is null) then NEW."ID" =GEN_ID(CATALOG_GEN,1);

/* 1*/

if ((NEW."PARENT_ID" is NULL) and (NEW."LEVEL" <> 1)) then

exception WRONG_LEVEL;

/* Level 1 , */

if (NEW."PARENT_ID" is NOT NULL) THEN

begin

select "LEVEL" from "CATALOG" WHERE "ID" = NEW."PARENT_ID" into :parent_level;

if (NEW."LEVEL" <> :parent_level+1) then

exception WRONG_LEVEL;

end

END ^

/*

- Level

*/

CREATE TRIGGER "CATALOG_BEFORE_UPD" FOR "CATALOG"

ACTIVE BEFORE UPDATE POSITION 0

AS

declare variable parent_level integer;

declare variable child_id integer;

BEGIN

if ((NEW."PARENT_ID" is NULL) and (NEW."LEVEL" <> 1)) then

exception WRONG_LEVEL;

select "LEVEL" from "CATALOG" WHERE "ID" = NEW."PARENT_ID" into :parent_level;

if (NEW."LEVEL" <> :parent_level+1) then

exception WRONG_LEVEL;

END ^

/*

- Level

*/

CREATE TRIGGER "CATALOG_AFTER_UPD" FOR "CATALOG"

ACTIVE AFTER UPDATE POSITION 0

AS

BEGIN

update "CATALOG" set "LEVEL" = NEW."LEVEL"+1 where "PARENT_ID" = NEW."ID";

END ^

, . , .

. , .

, , . , . , - , mgoblin@mail.ru


. .

 

 

 

! , , , .
. , :