hierarchyid

package module
v0.1.2 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Aug 16, 2024 License: MIT Imports: 7 Imported by: 0

README

Gorm hierarchyid

  • Library to handle hierarchyid type in SQL Server and go.
    • Generation and parsing of hierarchyid type in go.
    • Type wrapper for usage with gorm ORM.
  • The hierarchyid is data to represent a position in a hierarchy in SQL Server.
    • It is a variable length type with reduced storage requirements.
  • Encodes the position in the hierarchy as a list of indexes
    • For example in the tree below the path to E is /1/1/2/
    • Indexes can be used to sort elements inside of a tree level.

How it works

  • The HierarchyID is defined as a []int64 in go.
  • When serialized into JSON a textual representation is used for readability.
    • Represented as list separated by /. (e.g. /1/2/3/4/5/)
  • Each element in the slice represents a level in the hierarchy.
  • An empty slice represents the root of the hierarchy.
    • Elements placed in the root should not use an empty list.
    • They should instead by represented by /1/, /2/, etc.

Installation

  • The library can be installed using go get.
go get github.com/tentone/gorm-hierarchyid

Model definition

  • Declare HierarchyID type in your gorm model, there is no need to specify the DB data type.
  • Is is recommended to also mark the field as unique to avoid duplicates.
  • The library will handle the serialization and deserialization of the field to match the SQL Server hierarchyid type. 
    type Model struct {
    gorm.Model

    Path HierarchyID `gorm:"unique;not null;"`
}
  • In some scenarios it might be usefull to also keep a tradicional relationship to the parent.
    • This can be done by adding a ParentID field to the model.
    • It ensures that the tree is consistent and that actions (e.g. delete) are cascaded to the children.
    • Some operations might also be easier to perform with the parent relationship.
    type Model struct {
  gorm.Model

  Path HierarchyId `gorm:"unique;not null;"`

  ParentID uint              `gorm:"index"`
  Parent   *TestParentsTable `foreignKey:"parent_id;references:id;constraint:OnUpdate:NO ACTION,OnDelete:CASCADE;"`
}

Usage

Create
  • Elements can be added to the tree as regular entries
  • Just make sure that the tree indexes are filled correctly, indexes dont need to be sequential.
db.Create(&Table{Path: HierarchyID{Data: []int64{1}}})
db.Create(&Table{Path: HierarchyID{Data: []int64{1, 1}}})
db.Create(&Table{Path: HierarchyID{Data: []int64{1, 1, 2}}})
Get Ancestors
  • To get all parents of a node use the GetAncestors method.
  • The method will return a slice with all the parents of the node. This can be used as param for a query.
db.Model(&Table{}).Where("[path] IN (?)", child.Path.GetAncestors()).Find(&parents)
  • Its also possible to get parents with the SQL version of the GetAncestor method.
  • Example on getting the parent of an element.
db.Model(&Table{}).Where("[path] = ?.GetAncestor(1)", child.Path).Find(&parent)
Get Descendants
  • To get all children of a node use the IsDescendantOf method in SQL.
  • Example on getting all children of a node (including the node itself).
elements := []Table{}
db.Where("[path].IsDescendantOf(?)=1", HierarchyId{Data: []int64{1, 2}}).Find(&elements)
  • It is also possible to filter the children based on sub-levels.
  • Example on getting all nodes from root where at least one of the sub-level has a name that contains the text 'de'
SELECT *
FROM "table" as a
WHERE ([path].GetLevel()=1 AND [path].IsDescendantOf('/')=1) AND
(SELECT COUNT(*) FROM "table" AS b WHERE b.path.IsDescendantOf(a.path)=1 AND b.name LIKE '%de%')>0
  • The GetLevel method can be used to filter nodes based on their level in the hierarchy. Also available in SQL with the same name GetLevel.
  • A more generic version of the same code presented above writen in go.
root := GetRoot()
subQuery := db.Table("table AS b").Select("COUNT(*)").Where("[b].[path].IsDescendantOf([a].[path])=1 AND [b].[name] LIKE '%de%'")
conn = db.Table("table AS a").
  Where("[a].[path].GetLevel()=? AND [a].[path].IsDescendantOf(?)=1 AND (?)>0", root.GetLevel()+1, root, subQuery).
  Find(&elements)
Move nodes
  • To move a node to a new parent there is the GetReparentedValue method that receives the old parent and new parent and calculates the new hierarchyid value.
  • Example on moving a node to a new parent.
db.Model(&Table{}).Where("[id] = ?", id).Update("[path]=?", node.Path.GetReparentedValue(oldParent.Path, newParent.Path))

Resources

License

  • The project is distributed using a MIT license. Available on the project repository.

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var Patterns = []HierarchyIdPattern{
	{-281479271682120, -4294971465, "000100xxxxxxxxxxxxxx0xxxxxxxxxxxxxxxxxxxxx0xxxxxx0xxx0x1xxxT"},
	{4294972496, 281479271683151, "111111xxxxxxxxxxxxxx0xxxxxxxxxxxxxxxxxxxxx0xxxxxx0xxx0x1xxxT"},
	{-4294971464, -4169, "000101xxxxxxxxxxxxxxxxxxx0xxxxxx0xxx0x1xxxT"},
	{5200, 4294972495, "111110xxxxxxxxxxxxxxxxxxx0xxxxxx0xxx0x1xxxT"},
	{-4168, -73, "000110xxxxx0xxx0x1xxxT"},
	{1104, 5199, "11110xxxxx0xxx0x1xxxT"},
	{80, 1103, "1110xxx0xxx0x1xxxT"},
	{-72, -9, "0010xx0x1xxxT"},
	{16, 79, "110xx0x1xxxT"},
	{-8, -1, "00111xxxT"},
	{8, 15, "101xxxT"},
	{4, 7, "100xxT"},
	{0, 3, "01xxT"},
}

List of possible patterns for hierarchyid values.

Sorted from the largest to the smallest.

Functions

func Compare 

func Compare(a HierarchyIdData, b HierarchyIdData) int

Compare two hierarchyid data types

The comparison is done by comparing each level of the hierarchyid. If the levels are the same, the next level is compared. If the levels are different, the comparison stops and the result is returned.

func Encode 

func Encode(levels HierarchyIdData) ([]byte, error)

Encode a hierarchyid from hierarchyid.

func IsDescendantOf 

func IsDescendantOf(child HierarchyIdData, parent HierarchyIdData) bool

Check if a hierarchyid is a descendant of another hierarchyid

func ToString 

func ToString(data HierarchyIdData) string

Create a string representation of the hierarchyid data type

The string representation is a series of integers separated by slashes. For example, \1\2\3\

Types

type HierarchyId 

type HierarchyId struct {
	// Path of the hierarchy (e.g "/1/2/3/4/")
	Data HierarchyIdData
}

HierarchyId is a structure to represent database hierarchy ids.

func GetRoot 

func GetRoot() HierarchyId

Get the root of the tree '\'.

The root is the hierarchyid with an empty path.

func (*HierarchyId) GetAncestor 

func (j *HierarchyId) GetAncestor() HierarchyId

Get the direct parent of a hierarchyid.

func (*HierarchyId) GetAncestors 

func (j *HierarchyId) GetAncestors() []HierarchyId

Get all ancestors of a hierarchyid.

E.g. '/1/2/3/4/' will return ['/1/', '/1/2/', '/1/2/3/']

func (*HierarchyId) GetLevel 

func (j *HierarchyId) GetLevel() int

Get the tree level where this hierarchyid is located.

'/1/2/3/4/' is at level 4, '/1/2/3/' is at level 3, etc.

func (*HierarchyId) GetReparentedValue 

func (j *HierarchyId) GetReparentedValue(oldAncestor HierarchyId, newAncestor HierarchyId) HierarchyId

Calculate a new hierarchyid when moving from a parent to another parent in the tree.

The position will be calculated based on the old and new parents.

E.g. if the element is on position '/1/2/57/8/' old parents is '/1/2/' and new parent is '/1/3/' the new position will be '/1/3/57/8/'

func (HierarchyId) GormDBDataType 

func (HierarchyId) GormDBDataType(db *gorm.DB, field *schema.Field) string

GormDBDataTypeInterface defines the data type to apply in the database.

func (HierarchyId) GormDataType 

func (HierarchyId) GormDataType() string

GormDataTypeInterface to specify the nema of data type.

func (*HierarchyId) IsDescendantOf 

func (j *HierarchyId) IsDescendantOf(parent HierarchyId) bool

Check if a hierarchyid is a descendant of another hierarchyid

func (HierarchyId) MarshalJSON 

func (j HierarchyId) MarshalJSON() ([]byte, error)

When marshaling to JSON, we want the field formatted as a string.

func (*HierarchyId) Scan 

func (j *HierarchyId) Scan(src any) error

Scan implements the sql.Scanner interface.

Used to read the value provided by the SQL server.

func (*HierarchyId) ToString 

func (j *HierarchyId) ToString() string

Create a string representation of the hierarchyid data type

func (*HierarchyId) UnmarshalJSON 

func (j *HierarchyId) UnmarshalJSON(data []byte) error

When unmarshaling from JSON, we want to parse the string into the field.

func (HierarchyId) Value 

func (j HierarchyId) Value() (driver.Value, error)

Value implements the driver.Valuer interface.

Used to provide a value to the SQL server for storage.

type HierarchyIdData 

type HierarchyIdData = []int64

HierarchyIdData is a type to represent a hierarchyid data type from SQL Server

The hierarchyid data type is a series of integers separated by slashes. For example, \1\2\3\.

func Decode 

func Decode(data []byte) (HierarchyIdData, error)

Decode takes a byte slice of data stored in SQL Server hierarchyid format and returns a HierarchyId.

SQL server uses a custom binary format for hierarchyid.

func FromString 

func FromString(data string) (HierarchyIdData, error)

Create a hierarchyid data type from a string representation

func GetAncestor 

func GetAncestor(data HierarchyIdData) HierarchyIdData

Get the direct ancestor of a hierarchyid.

func GetAncestors 

func GetAncestors(data HierarchyIdData) []HierarchyIdData

Get all ancestors (parents) of a hierarchyid.

type HierarchyIdPattern 

type HierarchyIdPattern struct {
	// Minimum value
	Min int64

	// Maximum value
	Max int64

	// Pattern structure (0 and 1 are fixed}, x corresponds to the value and T is the terminator.
	Pattern string
}

Represents a possible pattern for hierarchyid values.

The structure used to store the values, changes based on the value size. These patterns codify the possible structures based on value size.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.