TreeStructure table mixin for hierarchies
Сделать возможность указывания в миксине - нужно ли вести специальную таблицу - Tree Structure.
Пример для org_unit
- должна создасться таблица org_unit_ts
:
CREATE TABLE org_unit_ts (
parentID bigint NOT NULL
, objectID bigint NOT NULL
, level smallint NOT NULL
);
/*
-- MSSQL ONLY, Postgres does not have conception of clustered indexes and before version 11 does not support covered index (INCLUDE).
CREATE CLUSTERED INDEX idx_org_unit_ts_parentID ON org_unit_ts (parentID, objectID, level);
CREATE INDEX idx_org_unit_ts_objectID ON org_unit_ts (objectID, parentID) INCLUDE (level);
*/
-- This will work on all DB vendors, but where possible, it is better to use INLCUDE columns and clustered index.
-- ...better for huge hierarchies, for small - does not really matter.
CREATE INDEX idx_org_unit_ts_parentID ON org_unit_ts (parentID, objectID, level);
CREATE INDEX idx_org_unit_ts_objectID ON org_unit_ts (objectID, parentID, level);
Таблица должна для каждого объекта содержать по записи на каждый из предков, начиная с себя. Т.е. для структуры
ID | parentID | code |
---|---|---|
1 | null | Org1 |
2 | 1 | Org1_1 |
3 | 2 | Dep1_1_1 |
Должны быть такие записи
objectID | parentID | level |
---|---|---|
1 | 1 | 0 |
2 | 2 | 0 |
2 | 1 | 1 |
3 | 3 | 0 |
3 | 2 | 1 |
3 | 1 | 2 |
Таблица должна первоначально - сразу после создания - должна заполняться подобным скриптом (писать циклы или CTE - лень :) ):
INSERT
INTO org_unit_ts (parentID, objectID, level)
SELECT ID
, ID
, 0
FROM org_unit;
INSERT
INTO org_unit_ts (parentID, objectID, level)
SELECT parentID, ID, 1
FROM org_unit
WHERE parentID IS NOT NULL;
INSERT
INTO org_unit_ts (parentID, objectID, level)
SELECT p.parentID, u.ID, 2
FROM org_unit u
JOIN org_unit p ON p.ID = u.parentID
WHERE p.parentID IS NOT NULL;
INSERT
INTO org_unit_ts
SELECT p2.parentID, u.ID, 3
FROM org_unit u
JOIN org_unit p ON p.ID = u.parentID
JOIN org_unit p2 ON p2.ID = p.parentID
WHERE p2.parentID IS NOT NULL;
INSERT
INTO org_unit_ts
SELECT p3.parentID, u.ID, 4
FROM org_unit u
JOIN org_unit p ON p.ID = u.parentID
JOIN org_unit p2 ON p2.ID = p.parentID
JOIN org_unit p3 ON p3.ID = p2.parentID
WHERE p3.parentID IS NOT NULL;
INSERT
INTO org_unit_ts
SELECT p4.parentID, u.ID, 5
FROM org_unit u
JOIN org_unit p ON p.ID = u.parentID
JOIN org_unit p2 ON p2.ID = p.parentID
JOIN org_unit p3 ON p3.ID = p2.parentID
JOIN org_unit p4 ON p4.ID = p3.parentID
WHERE p4.parentID IS NOT NULL;
INSERT
INTO org_unit_ts
SELECT p5.parentID, u.ID, 6
FROM org_unit u
JOIN org_unit p ON p.ID = u.parentID
JOIN org_unit p2 ON p2.ID = p.parentID
JOIN org_unit p3 ON p3.ID = p2.parentID
JOIN org_unit p4 ON p4.ID = p3.parentID
JOIN org_unit p5 ON p5.ID = p4.parentID
WHERE p5.parentID IS NOT NULL;
Для поддержания таблиц в актуальном состоянии нужно добавить такие обработчики к сущности:
const dom = require('@unitybase/dom')
const me = global[require('path').basename(__filename).split('.')[0]]
me.on('insert:after', orgUnit_insertTreeStructure)
me.on('delete:before', orgUnit_deleteTreeStructure)
me.on('update:before', orgUnit_beforeUpdate)
me.on('update:after', orgUnit_afterUpdate)
/**
* @param {ubMethodParams} ctx
*/
function orgUnit_insertTreeStructure(ctx) {
const {ID, parentID} = ctx.mParams.execParams
if (parentID) {
console.debug('orgUnit_insertTreeStructure: insert links to ancestors: parentID=%d', parentID)
ctx.dataStore.execSQL(
`
INSERT
INTO org_unit_ts (parentID, objectID, level)
SELECT parentID, :ID:, level + 1
FROM org_unit_ts ts
WHERE ts.objectID = :parentID:
`,
{ID, parentID}
)
}
console.debug('orgUnit_insertTreeStructure: link to itself (level=0)')
ctx.dataStore.execSQL(`
INSERT
INTO org_unit_ts (parentID, objectID, level)
VALUES (:ID:, :ID:, 0)
`,
{ID}
)
console.debug('orgUnit_insertTreeStructure: done')
}
/**
* @param {ubMethodParams} ctx
*/
function orgUnit_deleteTreeStructure(ctx) {
const {ID} = ctx.mParams.execParams
console.debug('orgUnit_deleteTreeStructure: deleting links with ancestors')
ctx.dataStore.execSQL(`
DELETE
FROM org_unit_ts
WHERE objectID = :ID:
`,
{ID}
)
console.debug('orgUnit_deleteTreeStructure: done')
}
/**
* If change parent from non-empty value to a new empty or non-empty value, delete all the links between descendants and ancestors.
* @param {ubMethodParams} ctx
*/
function orgUnit_beforeUpdate(ctx) {
const {ID, parentID} = ctx.mParams.execParams
if (parentID === undefined) {
console.debug('orgUnit_beforeUpdate: parentID is not updated, do nothing')
return
}
let oldParentID = null
dom.store.switchContext(ctx.dataStore, 'selectBeforeUpdate', /** @type {TubDataStore} */store => {
oldParentID = store.get('parentID')
})
if (oldParentID === null) {
console.debug('orgUnit_beforeUpdate: old parent is null, do nothing')
return
}
if (parentID === oldParentID) {
console.debug('orgUnit_beforeUpdate: parentID has not been changed, do nothing')
return
}
console.debug(
'orgUnit_beforeUpdate: parentID has been changed from %d to %d; delete links with ancestors',
parentID, oldParentID
)
// Delete links between object descendants and object ascendants
ctx.dataStore.execSQL(`
DELETE
FROM ts
FROM org_unit_ts ts
WHERE EXISTS (SELECT 1 FROM org_unit_ts ts_anc WHERE ts_anc.parentID = ts.parentID AND ts_anc.objectID = :ID: AND ts_anc.level >= 1)
AND EXISTS (SELECT 1 FROM org_unit_ts ts_dsc WHERE ts_dsc.objectID = ts.objectID AND ts_dsc.parentID = :ID:)
`,
{ID}
)
console.debug('orgUnit_beforeUpdate', 'links with ancestors deleted!')
}
/**
* If change parent to a new non-empty value, insert all the new links between descendants and new ancestors.
* @param {ubMethodParams} ctx
*/
function orgUnit_afterUpdate(ctx) {
const {ID, parentID} = ctx.mParams.execParams
if (parentID === undefined) {
console.debug('orgUnit_beforeUpdate: parentID is not updated, do nothing')
return
}
if (parentID === null) {
console.debug('orgUnit_beforeUpdate: parentID is set empty, do nothing')
return
}
let oldParentID = null
dom.store.switchContext(ctx.dataStore, 'selectBeforeUpdate', /** @type {TubDataStore} */store => {
oldParentID = store.get('parentID')
})
if (parentID === oldParentID) {
console.debug('orgUnit_afterUpdate: parentID has not been changed, do nothing')
return
}
console.debug('orgUnit_afterUpdate', 'adding links to ancestors')
ctx.dataStore.execSQL(`
INSERT
INTO org_unit_ts (parentID, objectID, level)
SELECT ts_anc.parentID
, ts_dsc.objectID
, ts_dsc.level + ts_anc.level + 1
FROM org_unit_ts ts_dsc
, org_unit_ts ts_anc
WHERE ts_dsc.parentID = :ID:
AND ts_anc.objectID = :parentID:
`,
{ID, parentID}
)
console.debug('orgUnit_afterUpdate: done')
}
Все это легко может быть изменено для того, чтобы оно было применимо для любой сущности с иерархией, не только для org_unit.
Я бы для миксина tree сделал опции:
- Флаг "нужно поле mi_treePath"
- Флаг "нужна таблица _ts"
Теперь пару слов о том, чем это все полезно:
- Вывести список организаций для каждого staffunit - в грид, например (а попробуй сделать тоже самое без tree structure?! :) ):
SELECT su.ID
, (SELECT TOP 1 FROM org_organization o JOIN org_unit_ts ts ON ts.parentID = o.ID WHERE objectID = su.ID ORDER BY level) organizationID
FROM org_staffunit su
Подобный запрос может быть полезен в очень большом количестве случаев. Можно реализовывать настройки с наслендованием. Работает БЫСТРО!
Также можно делать RLS который базируется на ACL с наследованием и т.п.