. , , ,

,,,

— ,

-. . . , , , . .

, .

;

;

;

;

.

(Dewey)

, (Dewey Decimal Classification). , . .

, . . , .

:

1 .

1.1 .

1.1.1 .

1.1.2 .

1.1.2.1 .

1.1.3 .

1.2.

1.2.1 .

1.2.2 .

, LIKE. , , index scan .

.

CREATE TABLE DEPARTMENT

(

ID INT PRIMARY KEY IDENTITY(1,1),

Path VARCHAR(180) UNIQUE,

Position INT NOT NULL,

NAME VARCHAR(128)

)

GO

INSERT INTO DEPARTMENT (Path, Position, NAME)

VALUES ('1', 1, ' ')

INSERT INTO DEPARTMENT (Path, Position, NAME)

VALUES ('1.1', 1, ' ')

INSERT INTO DEPARTMENT (Path, Position, NAME)

VALUES ('1.1.1', 1, ' ')

INSERT INTO DEPARTMENT (Path, Position, NAME)

VALUES ('1.1.2', 2, ' ')

INSERT INTO DEPARTMENT (Path, Position, NAME)

VALUES ('1.1.2.1', 1, ' ')

INSERT INTO DEPARTMENT (Path, Position, NAME)

VALUES ('1.1.3', 3, ' ')

INSERT INTO DEPARTMENT (Path, Position, NAME)

VALUES ('1.2', 2, ' ')

INSERT INTO DEPARTMENT (Path, Position, NAME)

VALUES ('1.2.1', 1, ' ')

INSERT INTO DEPARTMENT (Path, Position, NAME)

VALUES ('1.2.2', 2, ' ')

GO

Path

, Path 180. . , ( 0 999 ). , . 4 ( ) 60. . . 20. 180 20, 9 . 8 ( ) . , 180 , , , . , . , . , Path . . -, .

.

, , .

Path .

SELECT * FROM DEPARTMENT WHERE Path LIKE '1.1.%'

LIKE, , Path , .

.

SELECT result.*

FROM DEPARTMENT parent INNER JOIN DEPARTMENT result

ON (result.Path LIKE parent.Path + '.%')

WHERE parent.NAME = ' '

Path.

SELECT * FROM DEPARTMENT

WHERE Path LIKE '1.1.%' AND Path NOT LIKE '1.1.%.%'

໔. :

SELECT * FROM DEPARTMENT

WHERE Path LIKE '1.1.%' AND Path NOT LIKE '1.1.%.%.%'

.

Position. Path 1.1. . . , Position , :

INSERT INTO DEPARTMENT (Path, Position, NAME)

VALUES ('1.1.4', 4, ' ')

Position , :

INSERT INTO DEPARTMENT (Path, Position, NAME)

SELECT '1.1' + '.'+ ISNULL(CAST(MAX(Position)+1 AS VARCHAR), '1'),

ISNULL(MAX(Position)+1, 1), ' '

FROM DEPARTMENT

WHERE Path LIKE '1.1.%' AND Path NOT LIKE '1.1.%.%'

, , , MSSQL, . , Serializable, Position , Path.

, :

DELETE FROM DEPARTMENT

WHERE Path LIKE '1.1%'

LIKE :

DELETE FROM DEPARTMENT

WHERE Path LIKE '1.1.%'

, :

CREATE TRIGGER DELETE_NODES_TR

ON DEPARTMENT AFTER DELETE

AS

DECLARE @ParentPath VARCHAR(180)

BEGIN

SELECT @ParentPath=Path FROM deleted

DELETE FROM DEPARTMENT WHERE Path LIKE @ParentPath+'.%'

END

, , .

DELETE FROM DEPARTMENT WHERE Path='1.1'

, . . , Path 1.1, 1.2. :

UPDATE DEPARTMENT

SET Path =

(SELECT '1.2.' + ISNULL(CAST(MAX(D.Position) + 1 AS VARCHAR), '1')

FROM DEPARTMENT D

WHERE D.Path LIKE '1.2.%' AND D.Path NOT LIKE '1.2.%.%'),

Position =

(SELECT ISNULL(MAX(D.Position) + 1, '1')

FROM DEPARTMENT D

WHERE D.Path LIKE '1.2.%' AND D.Path NOT LIKE '1.2.%.%')

WHERE Path = '1.1'

Path :

UPDATE DEPARTMENT SET Path=STUFF(Path, 1, 3, '1.2.4')

WHERE Path LIKE '1.1.%'

, , , , Position:

CREATE TRIGGER UPDATE_NODES_TR

ON DEPARTMENT

AFTER UPDATE

AS

DECLARE

@OldParentPath VARCHAR(180),

@NewParentPath VARCHAR(180),

@ParentPosition INT,

@RealParentPosition INT

BEGIN

IF UPDATE(Path)

BEGIN

SELECT @OldParentPath = Path FROM deleted

SELECT @NewParentPath = Path, @ParentPosition = Position FROM inserted

-- Position , Path

SELECT @RealParentPosition = CAST(RIGHT(@NewParentPath,

CHARINDEX('.', REVERSE(@NewParentPath)) - 1) AS INT)

IF (@RealParentPosition <> @ParentPosition)

UPDATE DEPARTMENT

SET Position = @RealParentPosition

WHERE Path = @NewParentPath

--

UPDATE DEPARTMENT

SET Path = STUFF(Path, 1, LEN(@OldParentPath), @NewParentPath)

WHERE Path LIKE @OldParentPath+'.%'

END

END

. . , , , Path.

, , . , Position c 2 Path 12, 1 . Position 12, 212. , Path.

, . Path . . , , .

, , , , , . . . - , , .


-. .

 

 

 

! , , , .
. , :