Plugin for analyzing PostgreSQL plans in Sublime Text, and its development

We are completing a series of articles about plugins for explain.tensor.ru – PostgreSQL plan visualization service. This time we will talk about a plugin for Sublime Text.

Installation

Unfortunately in dependencies There is no way to install related plugins:

Dependencies are not a way to install multiple related packages. Currently no such functionality exists.

Therefore, first install the plugin SQLTools and then install our pluginusing Package Control (Ctrl+Shift+P and type the Install Package command):

The plugin can also be installed manually – download from the website explain.tensor.ru In chapter Download -> Plugins open the directory in Sublime -> Preferences -> Browse Packages and unpack the archive in it, the plugin is immediately ready for use.

Formatting a request

On the menu Edit or in the SQL editor context menu select Format SQL :

Query analysis

On the menu Edit or in the SQL editor context menu select Explain Analyze ,
in this case, the request will be executed on the PG server (configured in SQLTools) and the resulting plan will be sent to the service for analysis explain.tensor.ru through public API To open the result, click on the link in the popup window:

Settings

On the menu Preferences -> Package Settings -> Explain PostgreSQL -> Settings you can change the site URL, analysis options and method of obtaining the result – by default it shows the link in the popup window.

Development

Creating a new plugin Tools -> Developer -> New Plugin... it registers the text command “example”, when called, “Hello, World” will be inserted into the document text:

import sublime
import sublime_plugin

class ExampleCommand(sublime_plugin.TextCommand):
	def run(self, edit):
		self.view.insert(edit, 0, "Hello, World!")

Save the plugin in Packages/User/test.py in this case, Sublime will automatically load it, check it – create a new window Ctrl+N open the python console Ctrl+` and type a command that calls the method view.insert :

view.run_command("example")

When constructing a command name, Sublime takes the class name, removes the keyword Command and separates words with underscores, so for the command format_sql need class name FormatSqlCommand .

When creating a command class, you can inherit from base classes:

sublime_plugin.TextCommand – such commands are available only when the panel is open, respectively in the attribute self.view get the panel in which they were created, and in the run method they get the object edit.

sublime_plugin.WindowCommand – commands are always available and have access to the entire window through self.window

To avoid overloading the menu with unnecessary commands, it is recommended to define a method in the command class is_visible in which to make a dependence on the syntax of the current window, in our case – SQL files:

def check_is_visible(view):
	syntax = view.settings().get("syntax")
	if (syntax.endswith("SQL.sublime-syntax")):
		return True
	else:
		return False

During the publication process, recommendations were received to replace syntax checking with the method view.match_selector since the syntax file name may change

def check_is_visible(view):
  return view.match_selector(0, 'source.sql')

In the request formatting command we use the method view.sel to get the selected text, and in case of absence, we take all the text in the window. Then using API explain.tensor.ruwe get the converted text and replace it with the method view.replace :

class EpFormatSqlCommand(sublime_plugin.TextCommand):
	def is_visible(self):
		return check_is_visible(self.view)

	def run(self, edit):
		v = self.view
		selection = v.sel()[0]
		if selection.empty():
			selection = sublime.Region(0, v.size())
			text = v.substr(selection)
		else:
			text = v.substr(selection)
		data = {"query_src": text}
		url = get_plugin_settings("api_url") + "/beautifier-api"
		res = send_post_request(url, data, True)
        v.replace(edit, selection, res['btf_query_text'])
        sublime.status_message("Text formatted")

In the query analysis command, we import plugin methods SQLTools we get the request text from the panel, if there is no connection to PostgreSQL, we execute the method SQLTools.selectConnectionQuickPanel to establish a connection, then using the method get_explain_cmd we get the request prefix (EXPLAIN with options from the settings) and execute the request using the method SQLTools.conn.Command.createAndRun .

As a result, we get a query plan, which we send to API explain.tensor.ru and in response we receive a URL with an analysis page, which we open immediately in the browser or display in a popup window, depending on the settings:

import SQLTools.SQLTools

class EpExplainAnalyzeCommand(sublime_plugin.TextCommand):
	def is_visible(self):
		return check_is_visible(self.view)

	def run(self, edit):
		if (check_deps() == False): return
		ST = SQLTools.SQLTools.ST
		v = self.view
		selection = v.sel()[0]
		if selection.empty():
			selection = sublime.Region(0, v.size())
			text = v.substr(selection)
		else:
			text = v.substr(selection)

		def cb(result):
			data = {"query": text, "plan": result}
			api_url = get_plugin_settings("api_url")
			explain_api_url = api_url + "/explain"
			url = send_post_request(explain_api_url, data)
			plan_url = api_url + url
			show_link = get_plugin_settings("show_link")
			if (show_link == 'browser'):
				webbrowser.open_new_tab(plan_url)
			elif (show_link == 'popup_text'):
				show_popup(self.view, get_text_html(plan_url))

		if not ST.conn:
			ST.selectConnectionQuickPanel(callback=lambda: sublime.active_window().run_command('ep_explain_analyze'))
			return

		args=ST.conn.buildArgs()
		args.append("-At")
		env=ST.conn.buildEnv()
		text = get_explain_cmd() + text
		v.window().status_message("Getting plan...")
		ST.conn.Command.createAndRun(args=args,	env=env, callback=cb, query=text, timeout=60, silenceErrors=True, stream=False)

Plugin settings are stored in the file “Packages/Explain PostgreSQL/Explain PostgreSQL.sublime-settings“, while personal settings are stored in the directory Packages/User in the file of the same name.

To load plugin settings into the store dictionary, use the method sublime.load_settings :

store = None

def load_plugin_settings():
	global store
	store = sublime.load_settings("Explain PostgreSQL.sublime-settings")

Since our plugin depends on having the SQLTools add it to the function plugin_loaded check for its presence and, if not, display an error message and suggest installing:

if ('Packages/SQLTools/package-metadata.json' not in sublime.find_resources('package-metadata.json')):
    sublime.error_message('"Explain PostgreSQL" plugin requires "SQLTools" plugin installed.')
    sublime.run_command('install_package')

To add items to the main menu Edit create a file Main.sublime-menu :

[  
    {  
        "id": "edit",
        "children":
            [
                {
                    "caption": "Format SQL",
                    "command": "ep_format_sql",
                },
                {
                    "caption": "Explain Analyze",
                    "command": "ep_explain_analyze",
                }
            ]
    }
]

Context menu in a file Context.sublime-menu :

[  
    {
	"caption": "Format SQL",
	"command": "ep_format_sql"
    },
    {
	"caption": "Explain Analyze",
	"command": "ep_explain_analyze"
    }
]

To add commands to Command Palette (Ctrl+Shift+P) create Default.sublime-commandswith a list of commands:

[
    {
    	"caption": "Explain PostgreSQL: Format SQL",
    	"command": "ep_format_sql"
    },
    {
    	"caption": "Explain PostgreSQL: Explain Analyze",
    	"command": "ep_explain_analyze"
    }
]

Key combinations for calling commands are stored in a file Default.sublime-keymap if they are the same for all platforms, otherwise – in a separate file for each platform: Default (PLATFORM).sublime-keymap , where PLATFORM is Window, Linux or OSX. For example, for Linux and Windows (for MacOS ctrl is replaced by command):

[
    {
        "keys": [ "ctrl+shift+g" ],
        "command": "ep_format_sql",
        "context": [
            { "key": "selector", "operand": "source.sql"}
        ]
    },
    {
        "keys": [ "ctrl+shift+e" ],
        "command": "ep_explain_analyze",
        "context": [
            { "key": "selector", "operand": "source.sql"}
        ]
    }
]

Here we added a context condition – an SQL file must be open in the panel.

Publishing a plugin

The package manager for Sublime is Package Control .

For publications fork the plugin Package Control Channel in its repository directory in the e.json file we add our plugin:

{
    "name": "Explain PostgreSQL",
    "details": "https://github.com/MGorkov/explain-postgresql-sublime",
    "labels": ["sql", "postgresql", "formatting", "formatter", "explain", "tools"],
    "releases": [
        {
            "sublime_text": ">=3000",
            "tags": true
        }
    ]
}

Let's run the tests:

And we do Pull Request to add changes with our plugin to the original project.

By default, the bot is launched manually to perform code review, here are the explanations:

Due to people using Github's Action runners to mine cryptocurrencies and do other ignoble stuff, they must now be approved for each new contributor once before they are run. I've done that for your PR.

We wait for the review to launch, correct the comments, and after the PR is completed, it becomes possible to install the plugin via Package Control.

Plugin code published under the MIT license.