SQL unlimited classification storage process sorting 1

Advantage: there are few fields and the addition, deletion, modification, and query functions are available. However, the query is too general.

Disadvantages:

1. The classpath field defines the limit.

2. The primary key classid is not auto-incrementing. Using codesmith to generate multi-layer architecture code in batches may cause errors.

 

 

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [articleclass] ') and objectproperty (ID, n'isusertable') = 1)
Drop table [DBO]. [articleclass]
Go

Create Table [DBO]. [articleclass] (
[Classid] [int] identity (1, 1) not null, -- primary key (Note: Not identified)
[Classname] [nvarchar] (50) Collate chinese_taiwan_stroke_ci_as not null, -- category name
[Parentid] [int] not null, -- parent category ID (default: 0)
[Classpath] [nvarchar] (1000) Collate chinese_taiwan_stroke_ci_as not null, -- category path
[Classdepth] [int] not null, -- Classification depth (0 by default)
[Classorder] [int] not null, -- sorting (0 by default)
[Classdescription] [nvarchar] (1000) Collate chinese_taiwan_stroke_ci_as null, -- Classification description
[Isystem] [int] Null -- whether it is the default category of the system
) On [primary]
Go

Alter table [DBO]. [articleclass] add
Constraint [df_articleclass_parentid] default (0) for [parentid],
Constraint [df_articleclass_classdepth] default (0) for [classdepth],
Constraint [df_articleclass_classorder] default (0) for [classorder],
Constraint [df_articleclass_isystem] default (0) for [isystem],
Constraint [pk_articleclass] primary key clustered
(
[Classid]
) On [primary]
Go
Exec sp_addextendedproperty n 'Ms _ description', n' primary key (unidentified) ', n'user', n'dbo', n'table', n'articleclass ', N 'column ', N 'classid'
Go
Exec sp_addextendedproperty n 'Ms _ description', n'category name', n'user', n'dbo', n'table', n'articleclass', n'column ', n' classname'
Go
Exec sp_addextendedproperty n 'Ms _ description', N 'parent category id', N 'user', N 'dbo', N 'table', N 'articleclass ', N 'column ', N 'parentid'
Go
Exec sp_addextendedproperty n 'Ms _ description', n'classification path', n'user', n'dbo', n'table', n'articleclass', n'column ', N 'classpath'
Go
Exec sp_addextendedproperty n 'Ms _ description', n' classification depth ', n' user', n' dbo', n' table', n' articleclass', n' column ', N 'classdepth'
Go
Exec sp_addextendedproperty n 'Ms _ description', n'classification sort', n'user', n'dbo', n'table', n'articleclass', n'column ', N 'classorder'
Go
Exec sp_addextendedproperty n 'Ms _ description', n' classification description', n' user', n' dbo', n' table', n' articleclass', n' column ', N 'classdescription'
Go
Exec sp_addextendedproperty n 'Ms _ description', n' whether it is a system Classification ', n'user', n'dbo', n'table', n'articleclass ', N 'column', N 'isystem'

Go

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [sp_insert_articleclass] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [sp_insert_articleclass]
Go

Set quoted_identifier on
Go
Set ansi_nulls on
Go

-- Create a stored procedure for a category
/*
Exec sp_insert_articleclass 0, 'example and example operation', 'agriculture and fishing'
Exec sp_insert_articleclass 1, 'crop crop species ', 'agriculture and fishing'
Exec sp_insert_articleclass 2, 'vegetable species ', 'agriculture and fishing'
Exec sp_insert_articleclass 3, 'planting vegetables ', 'agriculture and fishing'
Exec sp_insert_articleclass 2, 'other crop specializing', 'agriculture and fishing'
Exec sp_insert_articleclass 4, 'Flower planting ', 'agriculture and fishing'
Exec sp_insert_articleclass 4, 'specifies of fruit seeding ', 'agriculture and fishing'
Exec sp_insert_articleclass 4, 'planting of other crop plans', 'agriculture and fishing'
*/

-- Exec sp_delete_articleclass 2
-- Select * From articleclass

Create procedure sp_insert_articleclass
(
@ Parentid int,
@ Classname nvarchar (50 ),
@ Classdescription nvarchar (1000)
)
As

Declare @ err as int

Set @ err = 0

Begin tran

-- Obtain the column ID from the existing record

Declare @ classid as int

Declare @ classdepth as int

Select @ classid = max (classid) from articleclass

If @ classid is not null

Set @ classid = @ classid + 1

Else

Set @ classid = 1

-- Determine whether it is a top-level column and set its classpath and classorder

Declare @ classpath as nvarchar (1000)

Declare @ classorder as int

If @ parentid = 0

Begin

Set @ classpath = ltrim (STR (@ classid ))

Select @ classorder = max (classorder) from articleclass

If @ classorder is not null

Set @ classorder = @ classorder + 1

Else -- if no record is found, this is the first record.

Set @ classorder = 1

-- Depth

Set @ classdepth = 1

End

Else

Begin

-- Obtain the path and depth of the parent node

Select @ classpath = classpath, @ classdepth = classdepth from articleclass where classid = @ parentid

If @ classpath is null

Begin

Set @ err = 1

Goto theend

End

-- Obtain the maximum number of the same parent node

Select @ classorder = max (classorder) from articleclass where classpath like ''+ @ classpath + '%' or classid = @ parentid
If @ classorder is not null -- if the sequence number exists, add 1 to all the sequence numbers after the sequence number
Begin
-- Update the sequence numbers of all nodes to be inserted.
Update articleclass set classorder = classorder + 1 where classorder> @ classorder
-- Add 1 to the maximum serial number of the parent node to create its own serial number.
Set @ classorder = @ classorder + 1
End
Else
Begin
Set @ err = 1
Goto theend
End
-- Add the ID of the parent node to form the path.
Set @ classpath = @ classpath + ',' + ltrim (STR (@ classid ))
-- Depth
Set @ classdepth = @ classdepth + 1
End

Insert into articleclass (classid, classname, parentid, classpath, classdepth, classorder, classdescription) values (@ classid, @ classname, @ parentid, @ classpath, @ classdepth, @ classorder, @ classdescription)

If @ error <> 0

Begin

Set @ err = 1

Goto theend

End

-- Update the order of the record after the current record

-- Update articleclass set classorder = classorder + 1 where classorder> @ classorder

Theend:

If @ err = 0

Begin

Commit tran

Return @ classid

End

Else

Begin

Rollback tran

Return 0

End

Go

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [sp_delete_articleclass] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [sp_delete_articleclass]
Go

Set quoted_identifier on
Go
Set ansi_nulls on
Go
-- Delete a stored procedure by category
-- Exec sp_delete_articleclass 1
-- Select * From articleclass
Create procedure sp_delete_articleclass
(
@ Classid int
)

As

Declare @ err as int

Set @ err = 0

Begin tran

-- First, query whether a subnode exists under the node.

Select classid from articleclass where parentid = @ classid

If @ rowcount <> 0

Begin

Set @ err = 1

Goto theend

End

-- Obtain the classorder of the node. Sort other records after deletion.

Declare @ classorder as int

Select @ classorder = classorder from articleclass where classid = @ classid

If @ classorder is null

Begin

Set @ err = 2

Goto theend

End

-- Update the classorder of other records

Update articleclass set classorder = classorder-1 where classorder> @ classorder

If @ error <> 0

Begin

Set @ err = 3

Goto theend

End

-- Delete operation

Delete from articleclass where classid = @ classid

If @ error <> 0

Begin

Set @ err = 4

Goto theend

End

-- Update the classid of other records

-- Update articleclass set classid = classid-1 where classid> @ classid

-- If @ error <> 0

-- Begin

-- Set @ err = 5

-- Goto theend

-- End

Theend:

If @ err = 0

Begin

Commit tran

Return 0 -- deletion successful

End

Else

Begin

If @ err = 1

Begin

Rollback tran

Return 1 -- subnode exists

End

Else

Begin

Rollback tran

Return 2 -- unknown error

End

End

Go

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [sp_update_articleclass] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [sp_update_articleclass]
Go

Set quoted_identifier on
Go
Set ansi_nulls on
Go
-- Update the stored procedure of a category record
-- Exec sp_update_articleclass @ classid, @ parentid, @ classname, @ classdescription
-- Select * From articleclass
Create procedure sp_update_articleclass
(
@ Classid int,
@ Parentid int,
@ Classname nvarchar (50 ),
@ Classdescription nvarchar (1000)
)

As

Declare @ err as int

Set @ err = 0

Begin tran

-- Get the parentid, classdepth, and classorder before the modification.

Declare @ oparentid as int

Declare @ oclassdepth as int

Declare @ oclassorder as int

Declare @ oclasspath as nvarchar (1000)

Select @ oparentid = parentid, @ oclassdepth = classdepth, @ oclassorder = classorder, @ oclasspath = classpath from articleclass where classid = @ classid

If @ oparentid is null

Begin

Set @ err = 1

Goto theend

End

-- If the parent ID is not changed, modify the topic name and topic description.

If @ oparentid = @ parentid

Begin

Update articleclass set classname = @ classname, classdescription = @ classdescription where classid = @ classid

If @ error <> 0

Set @ err = 2

Goto theend

End

Declare @ nclasspath as nvarchar (1000)

Declare @ nclassdepth as int

Declare @ nclassorder as int

-- Get the number of nodes contained by the current node as the parent node [including itself] Note: If "1" is returned, it indicates a single node.

Declare @ thecount as int

Select @ thecount = count (classid) from articleclass where classid = @ classid or classpath like ''+ @ oclasspath + '%'

If @ thecount is null

Begin

Set @ err = 3

Goto theend

End

If @ parentid = 0 -- if it is set to a top-level node, set the node to the last top-level node.

Begin

-- Print 'set as top topic'

Set @ nclasspath = ltrim (STR (@ classid ))

Set @ nclassdepth = 1

Select @ nclassorder = max (classorder) from articleclass

If @ nclassorder is null

Begin

Set @ err = 4

Goto theend

End

Set @ nclassorder = @ nclassorder-@ thecount + 1

-- Update the sequence of records after the change in the tree of three parts 1 Node itself 2 all child nodes 2

-- Print 'Update the classorder' for all columns [excluding subcolumns under this topic] located after this topic'

Update articleclass set classorder = classorder-@ thecount where (classorder> @ oclassorder) and (classpath not like ''+ @ oclasspath + '% ')

If @ error <> 0

Begin

Set @ err = 7

Goto theend

End

-- Print 'Update this column: parentid, classpath, classdepth, classorder, classname, classdescription'

Print 'order: '+ ltrim (STR (@ nclassorder ))

Update articleclass set parentid = @ parentid, classpath = @ nclasspath, classdepth = @ nclassdepth, classorder = @ nclassorder, classname = @ classname, classdescription = @ classdescription where classid

If @ error <> 0

Begin

Set @ err = 5

Goto theend

End

-- Print 'Update all subcolumns in this topic: classpath, classdepth, classorder'

Update articleclass set classpath = Replace (classpath, @ oclasspath, @ nclasspath), classdepth = classdepth + (@ author-@ author), classorder = classorder + (@ nclassorder-@ oclassorder) where classpath like ''+ @ oclasspath + '%'

If @ error <> 0

Begin

Set @ err = 6

Goto theend

End

End

Else

Begin

-- Obtain information about the future parent node and set the value of the current node.

Select @ nclassdepth = classdepth, @ nclasspath = classpath from articleclass where classid = @ parentid

If @ nclassdepth is null or @ nclasspath is null

Begin

Set @ err = 8

Goto theend

End

Set @ nclassdepth = @ nclassdepth + 1

Select @ nclassorder = max (classorder) from articleclass where classid = @ parentid or classpath like ''+ @ nclasspath + '%'

If @ nclassorder is null

Begin

Set @ err = 9

Goto theend

End

Set @ nclasspath = @ nclasspath + ''+ ltrim (STR (@ classid ))

If @ nclassorder = @ oclassorder + 1 -- if the new parent node is the closest brother at the top of the original node, the order of all nodes remains unchanged.

Begin

Update articleclass set parentid = @ parentid, classpath = @ nclasspath, classdepth = @ nclassdepth, classname = @ classname, classdescription = @ classdescription where classid = @ classid

If @ error <> 0

Begin

Set @ err = 10

Goto theend

End

End

Set @ nclassorder = @ nclassorder + 1

-- Update three parts 1 the sequence of the previous (or previous) records of the current tree 1 Node itself 3 All subnode points

-- Move up or move down

-- Print 'Update the classorder' of all the columns after this topic [or after this topic] [not including subcolumns under this topic'

If @ nclassorder <@ oclassorder

Begin

Update articleclass set classorder = classorder + @ thecount where classorder <@ oclassorder and classorder> = @ nclassorder and (classpath not like ''+ @ oclasspath + '% ') and classid <> @ classid

If @ error <> 0

Begin

Set @ err = 12

Goto theend

End

End

Else

Begin

Update articleclass set classorder = classorder-@ thecount where classorder> @ oclassorder and classorder <@ nclassorder and (classpath not like ''+ @ oclasspath + '%') and classid <> @ classid

If @ error <> 0

Begin

Set @ err = 13

Goto theend

End

End

-- Print 'Update this column: parentid, classpath, classdepth, classorder, classname, classdescription'

Print 'order: '+ ltrim (STR (@ nclassorder ))

If @ nclassorder> @ oclassorder

Set @ nclassorder = @ nclassorder-@ thecount

Update articleclass set parentid = @ parentid, classpath = @ nclasspath, classdepth = @ nclassdepth, classorder = @ nclassorder, classname = @ classname, classdescription = @ classdescription where classid

If @ error <> 0

Begin

Set @ err = 10

Goto theend

End

-- Print 'Update column_paht, classdepth, classorder' for all subcolumns in this topic'

Update articleclass set classpath = Replace (classpath, @ oclasspath, @ nclasspath), classdepth = classdepth + (@ author-@ author), classorder = classorder + (@ nclassorder-@ oclassorder) where classpath like ''+ @ oclasspath + '%'

If @ error <> 0

Begin

Set @ err = 11

Goto theend

End

End

Theend:

If @ err <> 0 -- if an error exists, the error number is returned.

Begin

Rollback tran

Return @ err

End

Else -- returns 0 if no error exists.

Begin

Commit tran

Return 0

End

Go