Sublime text for PL / SQL developer
I wanted to talk about how many beloved Sublime Text can be used as a good development tool for PL / SQL.
I would like to start with what this bike was for, because there seem to be many other IDEs for working with SQL, and in particular Oracle PL / SQL, such as Toad for Oracle, SQL Navigator, PL / SQL Developer, and even free Oracle SQL Developer, however, most of them have several drawbacks compared to text editors like Emacs, SciTe, Vim, Notepad ++, Sublime Text, etc.
I will list some of them, this list is purely subjective:
There is another point, but it relates more to the organization of projects and tasks in the workplace. I would like to touch on this topic a bit, since the organization on the projects left its mark on the setting of Sublime Text.
We have one rule at work - "The truth is in the source." Those. we work directly with source files, and not in the database itself. First of all, because we need versioning, and no one canceled the source control version system. Thus, we can always look at what is now on the base and what we have in SVN, in order to understand what the discrepancies are, if the client, for example, crawled into the base with something by handles, for example, in the procedure or package. Also at work, everything goes through sqlplus.
Our project adopted a specific structure for storing source codes, where each file contains its own objects. For example: the tables for the task are in the “tables” folder, the file name is the name of the table “table_contract.tab.sql”, the views are in the “views” folder and the files are named “vv_table_contract.sql”, the packages are in the “packages” folder ”And package files are named“ contract_utl_spec.sql ”and“ contract_utl_body.sql ”and so on, the basic concept is clear and simple.
If it will be interesting how our structure, SVN, release of versions, updating of clients, etc. are organized taking into account that we actually write only in SQL and PL / SQL, I can try to write an article on this topic, although there were articles about the organization of SQL code on Habré.
Let's start with a simple one - this is syntax highlighting. This is not about the color palette, this is a personal matter, but we will highlight database objects, because it is convenient and the code becomes more clear and understandable. Here's an example of what it looks like:
To highlight objects, I use a file (PL_SQL (Oracle) .tmLanguage) to highlight syntax from the github.com/bizoo/OracleSQL plugin , which will be discussed a bit later.
Add a new tag in the file:
Where between brackets we add new database objects for highlighting. I have tables and views. I get a list of objects with a simple script:
It remains to adjust the color of the objects, this is done in your theme file .. \ Packages \ Color Scheme - Default \ YourTheme.tmTheme. Add to file:
To configure tooltip for objects using ctrl + space combination, as well as to help complete words or object names, a separate file “db_objects.sublime-completions” was created, it looks something like this:
This is the standard format for Sublime Text, for triggering the combination of Ctrl + Space. I get the necessary data in about the same script as the data for highlighting database objects.
To help complete words or name DB objects, I use the plugin “All autocomplete” - this plugin scans all open tabs, selects all words in them, excluding duplicates, and also shows it in a tooltip. In the end, it looks something like this.
As a version control system, we use SVN. Sublime Text has many different plugins in the repository, I settled on “TourtoiseSVN”, the simple, necessary basic operations are performed by Commit, Update, Show log, Diff with the corresponding hotkeys (Alt + C, Alt + L, Alt + U), and for more complex things, such as tagging or merging, there is a client of the same name.
To search for objects in a task (project), I use both the standard functionality of Sublime Text - Goto Anything, and a small plug-in that I wrote myself. The plugin is very simple and actually slightly complements the standard Goto Anything so that it immediately adds the word on which the cursor is in the input field. Plugin code:
Considering how we have organized the structure of the project files, described at the beginning of the article, I got functionality for quick access to the object I need.
Now we will pass to the most interesting, it is execution / rolling of scripts from Sublime Text. To accomplish these goals, I use the github.com/bizoo/OracleSQL package modified to suit my needs and requirements . Modifications were made mainly in terms of simplifying the plugin, as The plugin itself is also a little sharpened for the needs of the author. Now the plugin looks like this:
oracle_exec.py
oracle_functions.py
It looks as follows.
Simple SQL queries can be performed in the same way. The only thing you need to do is configure sqlplus, for this you can try using login.sql, which should work every time you connect via sqlplus. I didn’t do this, I just made a snippet for the commands I needed or in general you can keep one file for these purposes where everything has already been recorded in advance.
As a result, I got a quick, easy, free, cross-platform and easily extensible tool for developing PL / SQL, which also fits perfectly into the rules at work.
PS: I understand that this “build” has flaws, but for me they are not critical, and even if you overcome your laziness, you can add it further.
I would like to start with what this bike was for, because there seem to be many other IDEs for working with SQL, and in particular Oracle PL / SQL, such as Toad for Oracle, SQL Navigator, PL / SQL Developer, and even free Oracle SQL Developer, however, most of them have several drawbacks compared to text editors like Emacs, SciTe, Vim, Notepad ++, Sublime Text, etc.
I will list some of them, this list is purely subjective:
- The “heaviness” of each IDE, this is expressed not only in the size of the distribution, but also in the general feelings, the presence of many unnecessary functions, buttons, responsiveness, etc.
- Most acceptable IDEs are paid, or shareware with limited functionality.
- I would like cross-platform, only Oracle SQL Developer falls under this requirement from the given IDEs.
- Text editing functionality. Most of them have only basic methods for working with text: this is typing, copy-paste, syntax highlighting. About any “CTRL + D” as Sublime Text speech is out of the question.
- Ease of expansion, almost all IDEs are closed, no support for self-signed pagins, etc.
There is another point, but it relates more to the organization of projects and tasks in the workplace. I would like to touch on this topic a bit, since the organization on the projects left its mark on the setting of Sublime Text.
We have one rule at work - "The truth is in the source." Those. we work directly with source files, and not in the database itself. First of all, because we need versioning, and no one canceled the source control version system. Thus, we can always look at what is now on the base and what we have in SVN, in order to understand what the discrepancies are, if the client, for example, crawled into the base with something by handles, for example, in the procedure or package. Also at work, everything goes through sqlplus.
Our project adopted a specific structure for storing source codes, where each file contains its own objects. For example: the tables for the task are in the “tables” folder, the file name is the name of the table “table_contract.tab.sql”, the views are in the “views” folder and the files are named “vv_table_contract.sql”, the packages are in the “packages” folder ”And package files are named“ contract_utl_spec.sql ”and“ contract_utl_body.sql ”and so on, the basic concept is clear and simple.
If it will be interesting how our structure, SVN, release of versions, updating of clients, etc. are organized taking into account that we actually write only in SQL and PL / SQL, I can try to write an article on this topic, although there were articles about the organization of SQL code on Habré.
Syntax and database objects highlighting
Let's start with a simple one - this is syntax highlighting. This is not about the color palette, this is a personal matter, but we will highlight database objects, because it is convenient and the code becomes more clear and understandable. Here's an example of what it looks like:
To highlight objects, I use a file (PL_SQL (Oracle) .tmLanguage) to highlight syntax from the github.com/bizoo/OracleSQL plugin , which will be discussed a bit later.
Add a new tag in the file:
match (?i)\b()\b name dbobject.oracle Where between brackets we add new database objects for highlighting. I have tables and views. I get a list of objects with a simple script:
select lower(rtrim(xmlcast(xmlagg(xmlelement(e,object_name,'|') order by object_name) as clob),'|'))
from all_objects
where owner = user
and object_type in ('TABLE', 'VIEW')
order by object_name
It remains to adjust the color of the objects, this is done in your theme file .. \ Packages \ Color Scheme - Default \ YourTheme.tmTheme. Add to file:
name String scope dbobject.oracle settings foreground #7F7F00 Object tooltip
To configure tooltip for objects using ctrl + space combination, as well as to help complete words or object names, a separate file “db_objects.sublime-completions” was created, it looks something like this:
{ "scope": "source.plsql.oracle",
"completions":
[
{"trigger": "abon_device\tTABLE", "contents": "abon_device"},
{"trigger": "abon_device_conflict\tTABLE", "contents": "abon_device_conflict"},
{"trigger": "abon_device_err\tTABLE", "contents": "abon_device_err"}
…
]
}
This is the standard format for Sublime Text, for triggering the combination of Ctrl + Space. I get the necessary data in about the same script as the data for highlighting database objects.
select '{"trigger": "'||object_name||'\t'||object_type||'", "contents": "'||object_name||'"},'
from all_objects
where owner = user
and object_type in ('TABLE', 'VIEW')
order by object_name
To help complete words or name DB objects, I use the plugin “All autocomplete” - this plugin scans all open tabs, selects all words in them, excluding duplicates, and also shows it in a tooltip. In the end, it looks something like this.
Version control system
As a version control system, we use SVN. Sublime Text has many different plugins in the repository, I settled on “TourtoiseSVN”, the simple, necessary basic operations are performed by Commit, Update, Show log, Diff with the corresponding hotkeys (Alt + C, Alt + L, Alt + U), and for more complex things, such as tagging or merging, there is a client of the same name.
Search for objects
To search for objects in a task (project), I use both the standard functionality of Sublime Text - Goto Anything, and a small plug-in that I wrote myself. The plugin is very simple and actually slightly complements the standard Goto Anything so that it immediately adds the word on which the cursor is in the input field. Plugin code:
import sublime, sublime_plugin
import os.path, string
import re
class MeOpenCommand(sublime_plugin.WindowCommand):
def run(self):
view = self.window.active_view()
for region in view.sel():
if region.begin() == region.end():
word = view.word(region)
else:
word = region
if not word.empty():
keyword = view.substr(word)
self.window.run_command("show_overlay", {"overlay": "goto", "show_files": "true", "text": keyword})
Considering how we have organized the structure of the project files, described at the beginning of the article, I got functionality for quick access to the object I need.
Code execution
Now we will pass to the most interesting, it is execution / rolling of scripts from Sublime Text. To accomplish these goals, I use the github.com/bizoo/OracleSQL package modified to suit my needs and requirements . Modifications were made mainly in terms of simplifying the plugin, as The plugin itself is also a little sharpened for the needs of the author. Now the plugin looks like this:
oracle_exec.py
import sublime, sublime_plugin
execcmd = __import__("exec")
import re
import os
import thread
import subprocess
class OracleExecCommand(execcmd.ExecCommand):
def run(self, dsn="", **kwargs):
# Передаем параметры для прокатки скрипта, sqlplus.exe, строка подключения, имя файла активной вкладки в Sublime Text
cmd = ["sqlplus.exe", dsn, "@", self.window.active_view().file_name()]
# Выполняем команду и регулярное выражение для поиска ошибок по F4 и Shift+F4 после прокатки скрипта, что тоже встроено в Sublime Text.
super(OracleExecCommand, self).run(cmd, "($file)(^([0-9]+))", "(^ORA-([0-9]+)(.+)(.+)$)|(PLS-([0-9]+)(.+)$)", **kwargs)
oracle_functions.py
import sublime, sublime_plugin
# список баз и схем которые вам необходимы и с которыми вы работаете, где первый параметр это название какое вам захочется, а 2й параметр это стандартная строка для подключения через sqlplus
instance_list = [["DB1", "user/pass@tnsname"], ["DB2", "user/pass@tnsname"]]
class OracleExecuteListCommand(sublime_plugin.WindowCommand):
def run(self, *args, **kwargs):
self.window.show_quick_panel(instance_list, self._quick_panel_callback)
def _quick_panel_callback(self, index):
if (index > -1):
self.window.run_command("oracle_exec", {"dsn": instance_list[index][1]})
It looks as follows.
Simple SQL queries can be performed in the same way. The only thing you need to do is configure sqlplus, for this you can try using login.sql, which should work every time you connect via sqlplus. I didn’t do this, I just made a snippet for the commands I needed or in general you can keep one file for these purposes where everything has already been recorded in advance.
Total
As a result, I got a quick, easy, free, cross-platform and easily extensible tool for developing PL / SQL, which also fits perfectly into the rules at work.
PS: I understand that this “build” has flaws, but for me they are not critical, and even if you overcome your laziness, you can add it further.