#sql #postgresql #recursion #hierarchical-data #recursive-query
Вопрос:
У меня есть таблица узлов, содержащая узлы графика, и у каждого узла есть столбец JSONB, называемый аннотацией. Этот столбец jsonb содержит разрешение в следующем формате:
{
"role_name": { "permission" : { "read" : "true" , "write" : "true"}}
}
и схема таблицы такова:
CREATE TABLE IF NOT EXISTS node
(
id SERIAL NOT NULL,
annotation jsonb,
parent_id int,
CONSTRAINT node_pkey PRIMARY KEY (id),
CONSTRAINT node_parent_id_fk FOREIGN KEY (parent_id)
REFERENCES node (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);
Я загружал узлы в приложение Java и выполнял обход DFS от корня до каждого узла, чтобы вычислить разрешение, а затем выполнить разбиение на страницы.
Проблема в том, что, когда узел графика имеет МНОГО узлов, требуется время для загрузки в приложение java и разбиения на страницы, поэтому стоимость загрузки всех и нескольких узлов (размер страницы 100) одинакова, так как все загружается в память и для вычисления разрешения затем на разбиение на страницы. Я хотел бы перенести эту логику в вычисление разрешений на хранимые процедуры PSQL и разбиение на страницы для данной роли. Как следует начать переход на хранимую процедуру для повышения производительности разбиения на страницы?
SQL для загрузки всех узлов графика:
WITH RECURSIVE
starting (id, parent_id) AS
(
SELECT t.id,t.parent_id
FROM node AS t
WHERE t.id = :node // given node
),
descendants (id, parent_id) AS
(
SELECT x.id ,x.parent_id
FROM starting as x
UNION ALL
SELECT t.id, t.parent_id
FROM node AS t JOIN descendants AS d ON t.parent_id = d.id
),
ancestors (id, parent_id) AS
(
SELECT t.id, t.parent_id
FROM node AS t
WHERE t.id IN (SELECT parent_id FROM starting)
UNION ALL
SELECT t.id, t.parent_id
FROM node AS t JOIN ancestors AS a ON t.id = a.parent_id
)
select * from node where id in ( select alltables.id from
(
TABLE ancestors
UNION ALL
TABLE descendants
) as alltables);
Вот логика для вычисления разрешения:
class Node{
Node node;
List<Node> children;
Permissions calculatedPermissions; // annotation jbson mapped to Permissions class
}
class Permissions{
Map<String,Permission> permissions;
}
class Permission{
boolean isRead;
boolean isWrite;
}
private void recurseComputePermissions(Node node) {
List<Node> children = node.children;
children.stream().forEach(c -> {
// Merge keep NODE permission for given role otherwise get from parent permission
if (c.getPermissions() != null) {
c.setCalculatedPermissions(c.getPermissions().clone().merge(node.getCalculatedPermissions()));
} else {
// just use parent permission if NODE does not have any permission
Permissions calPermussions = node.getCalculatedPermissions().clone();
c.setCalculatedPermissions(calPermussions);
}
recurseComputePermissions(c);
});
}
/**
Merge keep NODE permission for given role otherwise get from parent permission
*/
public Permissions merge(Permissions permissions) {
if(permissions != null) {
permissions.permissions.forEach((role,permission)->{
if(!this.permissions.containsKey(role)) {
this.permissions.put(role, permission.clone(role));
}
});
}
return this;
}
Any pointer or any idea would be greatly appreciated.
Thanks,
Bhavesh