Everything described below is very important for business analysis and development.
UI
-
The word "Run" on the button should be replaced with "Lineage" or "Analysis".
-
If nodes are connected by a single edge (e.g., CTE–CTE), there should be a way to collapse/expand that edge.
This would later allow implementing lazy graph loading from the server, for example when a node is not terminal (a View rather than a table).
-
Additional icons are needed on nodes to display (also, corresponding additional attributes should be provided in the Node type):
- icon to display (if exists) description/comment — technical comments for columns and tables
- icon to display (if exists) additional free-form information (HTML) about columns and tables in a popup
Navigation (bidirectional)
-
From text to graph (I understand the complexity)
It is necessary to add the ability to navigate from text to graph, i.e.:
if, for example, the cursor is positioned on a table/field/CTE_name or is inside a CTE,
then upon clicking a context menu item or a nearby floating button,
the corresponding element should be displayed (scrolled into view) in the graph and focused.
In other words, it should be ergonomic and fast to find graph elements based on words in SQL text.
-
From graph to text
In addition to positioning, scrolling, and highlighting the CTE that was clicked (dblclick) in the graph,
the same behavior should be added for fields / table names / CTE names
(it is necessary to distinguish between the CTE body and the CTE name).
Fields, table names, and CTE names (unlike the CTE body) may appear multiple times in SQL text,
this must be accounted for both in the model — the node should contain a list of start and stop positions of its name
(Node type: span[] with start/stop) and start and stop positions of its body (only for CTE),
and in the UI — navigation buttons "<", ">" (for each node) should be provided to find and position occurrences of the node name in the text
(as well as an occurrence counter).
At the same time, searching and highlighting the CTE body should also remain (very convenient).
It would also be useful to position and highlight JOINs in SQL by double-clicking an edge
(but this is more complex, as the model would also need to store parsing context positions).
-
It is also necessary to account for SQL editing:
if the SQL text is modified after parsing and graph construction,
a warning should be displayed indicating mismatch between the graph and the text and that navigation is unavailable.
The warning should be cleared either after sufficient undo operations (returning the text to the state before the last parsing),
or after re-parsing and rebuilding the graph.
IntelliSense code-completion
It would be nice to add:
- node names from parsing results and built-in function names of the selected dialect
- field names after an alias followed by a dot (table or CTE) (I understand the complexity)
Model + Backend
2 lists instead of 4
It is preferable to use 2 flat lists of nodes and edges.
Nodes should be assigned statement_identifiers/file_names and grouped dynamically as needed — this is more flexible and simpler.
Cross-statement edges should have a CrossStatement type;
it is easier to assign during graph generation since it is already clear that such edges connect nodes from different statements.
Instead of 4 lists (Nodes, GlobalNodes, Edges, GlobalEdges), there would be only 2 (Nodes, Edges).
Moreover, it would be possible to introduce CrossAssets edges (new enum element), i.e. edges between files on the frontend or between the current element and data lazily loaded from the backend.
For example, when drilling down into views (instead of base tables) during analysis —
in this case, maintaining just 2 lists would be significantly easier.
Everything described below is very important for business analysis and development.
UI
The word "Run" on the button should be replaced with "Lineage" or "Analysis".
If nodes are connected by a single edge (e.g., CTE–CTE), there should be a way to collapse/expand that edge.
This would later allow implementing lazy graph loading from the server, for example when a node is not terminal (a View rather than a table).
Additional icons are needed on nodes to display (also, corresponding additional attributes should be provided in the Node type):
Navigation (bidirectional)
From text to graph (I understand the complexity)
It is necessary to add the ability to navigate from text to graph, i.e.:
if, for example, the cursor is positioned on a table/field/CTE_name or is inside a CTE,
then upon clicking a context menu item or a nearby floating button,
the corresponding element should be displayed (scrolled into view) in the graph and focused.
In other words, it should be ergonomic and fast to find graph elements based on words in SQL text.
From graph to text
In addition to positioning, scrolling, and highlighting the CTE that was clicked (dblclick) in the graph,
the same behavior should be added for fields / table names / CTE names
(it is necessary to distinguish between the CTE body and the CTE name).
Fields, table names, and CTE names (unlike the CTE body) may appear multiple times in SQL text,
this must be accounted for both in the model — the node should contain a list of start and stop positions of its name
(Node type: span[] with start/stop) and start and stop positions of its body (only for CTE),
and in the UI — navigation buttons "<", ">" (for each node) should be provided to find and position occurrences of the node name in the text
(as well as an occurrence counter).
At the same time, searching and highlighting the CTE body should also remain (very convenient).
It would also be useful to position and highlight JOINs in SQL by double-clicking an edge
(but this is more complex, as the model would also need to store parsing context positions).
It is also necessary to account for SQL editing:
if the SQL text is modified after parsing and graph construction,
a warning should be displayed indicating mismatch between the graph and the text and that navigation is unavailable.
The warning should be cleared either after sufficient undo operations (returning the text to the state before the last parsing),
or after re-parsing and rebuilding the graph.
IntelliSense code-completion
It would be nice to add:
Model + Backend
2 lists instead of 4
It is preferable to use 2 flat lists of nodes and edges.
Nodes should be assigned statement_identifiers/file_names and grouped dynamically as needed — this is more flexible and simpler.
Cross-statement edges should have a CrossStatement type;
it is easier to assign during graph generation since it is already clear that such edges connect nodes from different statements.
Instead of 4 lists (Nodes, GlobalNodes, Edges, GlobalEdges), there would be only 2 (Nodes, Edges).
Moreover, it would be possible to introduce CrossAssets edges (new enum element), i.e. edges between files on the frontend or between the current element and data lazily loaded from the backend.
For example, when drilling down into views (instead of base tables) during analysis —
in this case, maintaining just 2 lists would be significantly easier.