[MDEV-30745] Feature request - Procedure/Function/Event hierarachy analyze(or explain?) Created: 2023-02-28  Updated: 2023-04-17

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Major
Reporter: Mitchell Lee Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: beginner-friendly


 Description   
  • Need a hierarachy analyzer that show the Procedure/Function/Event calling stack.
  • For example, when a ViewA uses/calls TableA, View1, View2, and View1 calls View1_1, View1_2 and so on. By one command, wanna see the all the call stack hierarachy like:

case A
no|parent | child
1, ViewA, TableA
2, ViewA, View1
3, ViewA, View2
4, View1, View1_1
5, View1, View1_2
or
case B.
ViewA
----- TableA
----- View1
----- View2
----- View1_1
----- View1_2

  • Need exception breaker the infinite loop by using the config 'max_recursive_iterations', and produce error message that recursive loop problem.

The reason why is that,
in these days DB business logic is so complex and hard, even painful to manually/humanly trace all the continuely changed code.
So, like the command 'EXPLAIN', provide a command to analyze call stack hierarachy since need to trace 15~10 depth calling stack manually.

 Comments   
Comment by Daniel Black [ 2023-02-28 ]

Does something like the following meet your requirements?

A parser trace of a single SQL that returns a JSON object. As views are processed in a SQL, they are parsed and the underlying tables form part of the parse object. Eg. parse select a, b, c from view1 join view2 ... where ends up with a hierarchy JSON object of the parsed SQL.

I'm not sure how to extend this to Procedure/Function/Event as it becomes one object per SQL statement, with potentially loops, and would been to be fully executed so it becomes less and EXPLAIN like feature and more an ANALYZE.

Comment by Mitchell Lee [ 2023-02-28 ]

Erm, I do not exacly understand your...example,
but it seems very similar to my idea.

BTW, don't need any involveness of JSON format. it makes complexity more to reuse the result. but, if there is a kinda parser which trace/travel into all child call stack, and can return in plain recordset it fine. and if there is an option to produce JSON, its also fine as an option.
Its behavior/outlook very similar to the command EXPLAIN result.

Generated at Thu Feb 08 10:18:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.