表和触发器
DROP TABLE IF EXISTS base_menu;
CREATE TABLE base_menu (
id SERIAL,
parent INTEGER DEFAULT 0,
identify VARCHAR(100) NOT NULL,
title VARCHAR(50) NOT NULL,
link VARCHAR(200) DEFAULT NULL,
url VARCHAR(200) DEFAULT NULL,
target VARCHAR(50) DEFAULT NULL,
"queryParams" JSON DEFAULT NULL,
subtitle VARCHAR(200) DEFAULT NULL,
description TEXT DEFAULT NULL,
icon VARCHAR(100) DEFAULT NULL,
classes VARCHAR(100) DEFAULT NULL,
image INTEGER DEFAULT NULL,
"group" BOOLEAN DEFAULT FALSE,
sorts INTEGER DEFAULT 0,
PRIMARY KEY (id)
);
COMMENT ON TABLE base_menu IS '系统菜单数据表';
COMMENT ON COLUMN base_menu.id IS '自增主键';
COMMENT ON COLUMN base_menu.parent IS '上级ID';
COMMENT ON COLUMN base_menu.identify IS '菜单英文标识';
COMMENT ON COLUMN base_menu.title IS '菜单标题';
COMMENT ON COLUMN base_menu.link IS 'routerLink';
COMMENT ON COLUMN base_menu.url IS 'href';
COMMENT ON COLUMN base_menu.target IS 'target';
COMMENT ON COLUMN base_menu."queryParams" IS '路由传参';
COMMENT ON COLUMN base_menu.subtitle IS '子标题';
COMMENT ON COLUMN base_menu.description IS '描述';
COMMENT ON COLUMN base_menu.icon IS '图标';
COMMENT ON COLUMN base_menu.classes IS '样式';
COMMENT ON COLUMN base_menu.image IS '图片';
COMMENT ON COLUMN base_menu."group" IS '是否是系统菜单组';
COMMENT ON COLUMN base_menu.sorts IS '排序';
DROP FUNCTION IF EXISTS trigger_before_insert_update_base_menu();
CREATE OR REPLACE FUNCTION trigger_before_insert_update_base_menu()
RETURNS TRIGGER
AS $$
DECLARE
identify_val VARCHAR(100);
BEGIN
IF (NEW.link IS NULL AND NEW.url IS NULL) THEN
SELECT identify INTO identify_val FROM base_menu WHERE id = NEW.parent;
NEW.link = '/pages/' || identify_val || '/' || NEW.identify;
END IF;
PERFORM func_transform_nodes(
json_build_object(
'table', 'base_menu',
'parent', 0,
'key', 'webManage_indexedDB_menu:list'
)
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS base_menu_before_insert_update ON base_menu;
CREATE TRIGGER base_menu_before_insert_update BEFORE INSERT OR UPDATE ON base_menu
FOR EACH ROW
EXECUTE PROCEDURE trigger_before_insert_update_base_menu();
DROP FUNCTION IF EXISTS trigger_after_delete_base_menu();
CREATE OR REPLACE FUNCTION trigger_after_delete_base_menu()
RETURNS TRIGGER
AS $$
BEGIN
DELETE FROM base_pages WHERE mid = OLD.id;
DELETE FROM base_element WHERE mid = OLD.id AND pid IS NULL;
DELETE FROM base_structure WHERE mid = OLD.id AND pid IS NULL AND eid IS NULL;
DELETE FROM base_action WHERE mid = OLD.id AND pid IS NULL AND eid IS NULL;
DELETE FROM base_permission WHERE mid = OLD.id AND pid IS NULL AND eid IS NULL;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS base_menu_after_delete ON base_menu;
CREATE TRIGGER base_menu_after_delete AFTER DELETE ON base_menu
FOR EACH ROW
EXECUTE PROCEDURE trigger_after_delete_base_menu();
存储过程
DROP FUNCTION IF EXISTS func_transform_nodes(JSON);
CREATE OR REPLACE FUNCTION func_transform_nodes(
IN transforms JSON
)RETURNS JSONB
AS $$
DECLARE
parent INTEGER DEFAULT 0;
tabname VARCHAR(100);
key_val VARCHAR(200);
restful_val VARCHAR(200);
schemas_val VARCHAR(50);
storage_val VARCHAR(50);
store_val VARCHAR(50);
node JSONB;
nodes JSONB;
children JSONB;
depth JSONB;
result RECORD;
executesql TEXT;
executenote TEXT;
BEGIN
nodes := JSONB_BUILD_ARRAY();
depth := JSONB_BUILD_ARRAY();
IF (json_extract_path_text(transforms, 'table') IS NULL) THEN
RETURN json_build_object('type', 'Error', 'message', '指定数据库名不能为空!', 'code', 230);
ELSE
tabname := json_extract_path_text(transforms, 'table');
END IF;
IF (json_extract_path_text(transforms, 'key') IS NULL) THEN
IF (json_extract_path_text(transforms, 'schemas') IS NOT NULL) THEN
schemas_val := json_extract_path_text(transforms, 'schemas');
ELSE
schemas_val := 'webManage';
END IF;
IF (json_extract_path_text(transforms, 'storage') IS NOT NULL) THEN
storage_val := json_extract_path_text(transforms, 'storage');
ELSE
storage_val := 'indexedDB';
END IF;
IF (json_extract_path_text(transforms, 'store') IS NOT NULL) THEN
store_val := json_extract_path_text(transforms, 'store');
ELSE
SELECT rec[2] INTO store_val FROM string_to_array(tabname, '_') AS rec;
END IF;
key_val := schemas_val || '_' || storage_val || '_' || store_val || ':list';
ELSE
key_val := json_extract_path_text(transforms, 'key');
IF (json_extract_path_text(transforms, 'restful') IS NOT NULL) THEN
restful_val := json_extract_path_text(transforms, 'restful');
ELSE
SELECT rec[1], rec[2], (SELECT recd[1] FROM string_to_array(rec[3], ':') AS recd)
INTO schemas_val, storage_val, store_val
FROM string_to_array(key_val, '_') AS rec;
restful_val := 'api/' || initcap(store_val) || '/getList';
END IF;
END IF;
IF (json_extract_path_text(transforms, 'parent') IS NOT NULL) THEN
parent := json_extract_path_text(transforms, 'parent')::INTEGER;
END IF;
executesql := 'SELECT m.id AS key, m.*, (SELECT count(*) > 0 FROM '|| tabname ||' WHERE parent = m.id) AS has_child ' ||
' FROM '|| tabname ||' m where m.parent = $1 ORDER BY m.id';
FOR result IN EXECUTE executesql USING parent LOOP
executenote := 'SELECT row_to_json(bm) FROM (SELECT id AS key, * FROM ' || tabname || ' WHERE id = $1) bm';
EXECUTE executenote INTO node USING result.id;
IF (result.has_child) THEN
SELECT func_transform_nodes(json_build_object(
'table', tabname,
'parent', result.id,
'key', key_val
)) INTO children;
nodes := nodes || JSONB_BUILD_ARRAY(node || jsonb_build_object(
'children', jsonb_extract_path_text(children, 'nodes')::JSONB)
);
ELSE
nodes := nodes || JSONB_BUILD_ARRAY(node);
END IF;
END LOOP;
executesql := 'WITH RECURSIVE menu AS (
SELECT id, id AS key, parent, title, icon, 1 AS level,
(SELECT array_agg(bm0.id) FROM ' || tabname || ' bm0 WHERE bm0.parent = bm.id) AS lower_level
FROM ' || tabname || ' bm WHERE parent = $1
UNION ALL
SELECT bm.id, bm.id AS key, bm.parent, bm.title, bm.icon, (menu.level + 1),
(SELECT array_agg(bm0.id) FROM ' || tabname || ' bm0 WHERE bm0.parent = bm.id) AS lower_level
FROM ' || tabname || ' bm, menu WHERE bm.parent = menu.id
)
SELECT menu.*, (SELECT count(*) > 0 FROM ' || tabname || ' WHERE parent = menu.id) AS has_child ' ||
'FROM menu ORDER BY menu.id';
EXECUTE 'SELECT json_agg(row_to_json(c)) FROM (' || executesql || ') c;' INTO depth USING parent;
PERFORM structure_redis(
json_build_object('key', key_val, 'data', nodes, 'restful', restful_val)
);
RETURN jsonb_build_object(
'type', 'Success',
'message', '成功获取数据',
'key', key_val,
'nodes', nodes,
'depth', depth
);
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION func_transform_nodes(JSON) IS '无限极分类数据转NODES数据自定义函数';
网友评论