Support for other databases

[jogibear9988]

MySQL:
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
(Maria Db https://mariadb.com/kb/en/explain/)

SqLite:
https://www.sqlite.org/eqp.html

Oracle:
https://blogs.oracle.com/optimizer/post/how-to-generate-a-useful-sql-execution-plan

[Giorgi]

Thanks for the suggestion @jogibear9988. The idea behind this extension was to provide a graphical plan and not a text-based plan but if you or others send a PR with textual plan I will gladly accept it.

[jogibear9988]

I thought you create it from a text document. Didn't look exactly how it worked.

[Giorgi]

I do but I use other open-source projects to convert the text plan to visual representation. I wasn't able to find anything like that for the database engines you suggested.

[jogibear9988]

Short idea:

	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Text;

	public class SQLiteQueryPlanToGraphviz
	{
		public static string ConvertToGraphviz(string queryPlan)
		{
			StringBuilder dotGraph = new StringBuilder();
			dotGraph.AppendLine("digraph G {");
			dotGraph.AppendLine("  node [shape=box];"); // Set the node shape to box, typical for Graphviz diagrams

			var lines = queryPlan.Split(new[] { '\n', '\r' }, StringSplitOptions.RemoveEmptyEntries);
			var parentIdStack = new Stack<string>();
			int nodeIdCounter = 0;

			foreach (var line in lines)
			{
				var indentLevel = line.TakeWhile(c => c == ' ').Count() / 3; // Assuming 3 spaces per indent level for simplicity
				var trimmedLine = line.Trim();
				var nodeId = $"node{nodeIdCounter++}";

				// Create a label for the node that includes the query plan operation
				dotGraph.AppendLine($"  {nodeId} [label=\"{trimmedLine.Replace("\"", "\\\"")}\"];");

				// If the stack has an item at the current indent level, pop it (and any deeper items)
				while (parentIdStack.Count > indentLevel)
				{
					parentIdStack.Pop();
				}

				// If there's a parent node, create an edge from it to the current node
				if (parentIdStack.TryPeek(out string parent))
				{
					dotGraph.AppendLine($"  {parent} -> {nodeId};");
				}

				// Push the current node onto the stack
				parentIdStack.Push(nodeId);
			}

			dotGraph.AppendLine("}");

			return dotGraph.ToString();
		}
	}

	class Program
	{
		static void Main()
		{
			string queryPlan = @"`--COMPOUND QUERY
	   |--LEFT-MOST SUBQUERY
	   |  `--SCAN t1 USING COVERING INDEX i1
	   `--UNION USING TEMP B-TREE
		  `--SCAN t2 USING COVERING INDEX i4";
			string graphvizDot = SQLiteQueryPlanToGraphviz.ConvertToGraphviz(queryPlan);
			Console.WriteLine(graphvizDot);
		}
	}

rendered with graphviz:

[jogibear9988]

It's only a idea... still will need more work :-)

[jogibear9988]

[Giorgi]

That's a good idea 👍

[Giorgi]

The image looks wrong though, SCAN t1 USING COVERING INDEX i1 should be under LEFT-MOST SUBQUERY

[AzubuikeKevin]

Looking forward to MySQL been supported.

[Giorgi]

Oracle support is available in version 1.0.9

[Giorgi]

MySQL and SQLite support are planned for the next month or two.

[jogibear9988]

How will you do the support?

[Giorgi]

Version 1.1.10 adds support for SQLite:

[Giorgi]

Just published a new version with MySQL support. Check it out!