Skip to content

Instantly share code, notes, and snippets.

@jochumdev

jochumdev/01_schema.sql

Last active August 10, 2022 13:05
Show Gist options
  • Save jochumdev/c6541484c37bbcef58138570270eca4f to your computer and use it in GitHub Desktop.
Save jochumdev/c6541484c37bbcef58138570270eca4f to your computer and use it in GitHub Desktop.
Download ZIP
tree_schema
Raw
01_schema.sql
DROP TABLE IF EXISTS tree_node CASCADE;
CREATE TABLE tree_node
(
id BIGSERIAL NOT NULL UNIQUE,
PRIMARY KEY(id)
);
DROP TABLE IF EXISTS tree_info CASCADE;
CREATE TABLE tree_info
(
id BIGSERIAL NOT NULL UNIQUE,
/* node name/path */
npath CHARACTER VARYING(64) NOT NULL,
/* node id */
nid BIGINT NOT NULL,
/* parent id */
pid BIGINT NULL, -- root is NULL
alias BOOL default False,
PRIMARY KEY(id),
UNIQUE(npath, pid),
FOREIGN KEY(nid) REFERENCES tree_node(id),
FOREIGN KEY(pid) REFERENCES tree_node(id)
);
CREATE INDEX idx_nid ON tree_info (nid);
CREATE INDEX idx_pid ON tree_info (pid);
DROP MATERIALIZED VIEW IF EXISTS mat_view_tree;
CREATE MATERIALIZED VIEW mat_view_tree(npath, nid, pid, alias, cnpath) AS
WITH RECURSIVE cte AS(
SELECT
n.npath,
n.nid,
n.pid,
n.alias,
ARRAY[n.npath] AS cnpath
FROM tree_info AS n
FULL OUTER JOIN tree_info AS p ON n.pid = p.nid
WHERE
n.pid = 0
UNION ALL
SELECT i.npath, i.nid, i.pid, i.alias, (t.cnpath || i.npath)::VARCHAR(64)[]
FROM tree_info i
INNER JOIN cte AS t ON i.pid = t.nid
) SELECT * FROM cte;
CREATE INDEX idx_nid ON mat_view_tree (nid);
CREATE INDEX idx_pid ON mat_view_tree (pid);
CREATE UNIQUE INDEX idx_cnpath ON mat_view_tree (cnpath);
Raw
02_data.sql
DO $$
DECLARE
packages_id BIGINT;
library_id BIGINT;
github_id BIGINT;
gnginx_id BIGINT;
gnats_id BIGINT;
gredis_id BIGINT;
nats_id BIGINT;
redis_id BIGINT;
nginx_id BIGINT;
tmp_id BIGINT;
BEGIN
/* Create root node */
INSERT INTO tree_node(id) VALUES (0);
INSERT INTO tree_info(nid, npath, pid) VALUES (0, 'root', NULL);
/* Create node packages|pkgs */
INSERT INTO tree_node VALUES (default) RETURNING id INTO packages_id;
INSERT INTO tree_info(nid, npath, pid) VALUES (packages_id, 'packages', 0);
INSERT INTO tree_info(nid, npath, pid, alias) VALUES (packages_id, 'pkgs', 0, True);
/* Create node library|lib */
INSERT INTO tree_node VALUES (default) RETURNING id INTO library_id;
INSERT INTO tree_info(nid, npath, pid) VALUES (library_id, 'library', 0);
INSERT INTO tree_info(nid, npath, pid, alias) VALUES (library_id, 'lib', 0, True);
/* github and github stuff */
INSERT INTO tree_node VALUES (default) RETURNING id INTO github_id;
INSERT INTO tree_info(nid, npath, pid) VALUES (github_id, 'github', packages_id);
INSERT INTO tree_node VALUES (default) RETURNING id INTO gnginx_id;
INSERT INTO tree_info(nid, npath, pid) VALUES (gnginx_id, 'nginx-io', github_id);
INSERT INTO tree_node VALUES (default) RETURNING id INTO gnats_id;
INSERT INTO tree_info(nid, npath, pid) VALUES (gnats_id, 'nats-io', github_id);
INSERT INTO tree_node VALUES (default) RETURNING id INTO gredis_id;
INSERT INTO tree_info(nid, npath, pid) VALUES (gredis_id, 'redis-io', github_id);
INSERT INTO tree_node VALUES (default) RETURNING id INTO nginx_id;
INSERT INTO tree_info(nid, npath, pid) VALUES (nginx_id, 'nginx', gnginx_id);
INSERT INTO tree_node VALUES (default) RETURNING id INTO nats_id;
INSERT INTO tree_info(nid, npath, pid) VALUES (nats_id, 'nats', gnats_id);
INSERT INTO tree_node VALUES (default) RETURNING id INTO redis_id;
INSERT INTO tree_info(nid, npath, pid) VALUES (redis_id, 'redis', gredis_id);
INSERT INTO tree_node VALUES (default) RETURNING id INTO tmp_id;
INSERT INTO tree_info(nid, npath, pid) VALUES (tmp_id, '1.0.0', nginx_id);
INSERT INTO tree_node VALUES (default) RETURNING id INTO tmp_id;
INSERT INTO tree_info(nid, npath, pid) VALUES (tmp_id, '1.0.0', nats_id);
INSERT INTO tree_node VALUES (default) RETURNING id INTO tmp_id;
INSERT INTO tree_info(nid, npath, pid) VALUES (tmp_id, '1.0.0', redis_id);
REFRESH MATERIALIZED VIEW mat_view_tree;
END $$;
Raw
03_examples.sql
/* Show the whole tree */
-- SELECT * FROM mat_view_tree;
/* Select from the view "tree" where the path begins with ... */
-- SELECT * FROM mat_view_tree WHERE cnpath[0:2] = '{"packages", "github"}';
-- SELECT * FROM mat_view_tree WHERE cnpath[0:3] = '{"packages", "github", "nats-io"}';
-- Find locations where "packages/github" lives in
/*
SELECT a.* FROM mat_view_tree AS a
INNER JOIN mat_view_tree AS b ON b.pid = a.nid
WHERE b.cnpath = '{"packages", "github"}';
*/
/* Find nodes relaying on "packages/github" */
-- SELECT * FROM mat_view_tree WHERE pid = (SELECT nid FROM mat_view_tree WHERE cnpath = '{"packages", "github"}');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment