Sublime text for PL / SQL developer
I wanted to talk about how many of your favorite Sublime Text can be used as a good tool for developing 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 such as 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, as the organization on projects left its mark on the Sublime Text setting.
We have one rule at work - "The source is true." Those. we work directly with source files, and not in the database itself. First of all, because we need versioning, and no one has canceled the source version control system. Thus, we can always look at what is now at the base and what is in SVN in order to understand what the discrepancies are, if the client, for example, got into the database with his hands, corrected something, for example in a procedure or package. Also at our place everything rolls through sqlplus.
Our project has adopted a specific source storage structure, where each file contains its own objects. Nr: the tables for the task are in the “tables” folder, the file name has the table name “table_contract.tab.sql”, the views are in the “views” folder and the files are called “vv_table_contract.sql”, the packages are in the “packages” folder ”And the 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 is interesting how we have organized the structure, SVN, release versions, update clients, etc. taking into account the fact that we are writing 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 in Habré.
Let's start with the simple - this is syntax highlighting. It's not about the color palette, this is a personal matter, but we will highlight the database objects, since it is convenient and the code becomes clearer and more understandable. Here is an example of how it looks:
To highlight objects, I use the file (PL_SQL (Oracle) .tmLanguage) to highlight the syntax from the github.com/bizoo/OracleSQL plugin, which will be discussed later.
In the file, add a new tag:
Where between brackets we add new database objects for highlighting. I have tables and views. I get a list of objects in 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 set up a tooltip for objects using a combination of ctrl + space, as well as to assist in completing 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 by combining Ctrl + Space. I extract the necessary data in approximately the same script as the data for highlighting database objects.
To assist in completing words or naming database objects, I use the “All autocomplete” plugin - this plugin looks through all open tabs, selects all words in them, excluding duplicates, and also shows it in the tooltip. In the end, it looks like this.
As a version control system, we use SVN. Sublime Text has many different plugins in the repository, I stopped at “TourtoiseSVN”, idle time, the necessary basic operations are performed by Commit, Update, Show log, Diff with corresponding hot keys (Alt + C, Alt + L, Alt + U), and for more complex things, such as tagging or merging, there is a customer of the same name.
To search for objects in a task (project), I use both the standard functionality of Sublime Text - Goto Anything, and the small plugin I wrote myself. The plugin is very simple and in fact slightly complements the standard Goto Anything by the fact that in the input field immediately adds the word on which the cursor stands. Plugin code:
Taking into account how organized the file structure of the project is, as described at the beginning of the article, I got the functionality for quick access to the object I need.
Now we come to the most interesting, this execution / rolling of scripts from Sublime Text. To accomplish these goals, I use the github.com/bizoo/OracleSQL package that has been modified to fit my needs and requirements. Modifications were made mainly in terms of simplifying the plugin, since The plugin itself is also a bit sharpened to the needs of the author. Now the plugin looks like this:
oracle_exec.py
oracle_functions.py
It looks like this.
Simple SQL queries can be executed in the same way. The only thing you need to do is make the sqlplus settings, for this you can try using login.sql, which should work every time you connect via sqlplus. I didn’t do that, I just made a snippet for the commands I needed or in general you can keep one file for these purposes where everything is already recorded in advance.
As a result, I got a fast, easy, free, cross-platform and easily extensible PL / SQL development tool that also fits perfectly with the rules at work.
PS: I understand that there are flaws in this “assembly”, but for me they are not critical, and even if you can overcome your laziness, you can continue to write.
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 such as 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 general sensations, the presence of many unnecessary functions, buttons, responsiveness, etc.
- Most of the acceptable IDE are paid or shareware with limited functionality.
- I would like cross-platform, under this requirement from the above IDE only Oracle SQL Developer falls.
- Functional text editing. Most of them have only basic methods for working with text: this is typing, copy-paste, syntax highlighting. No “CTRL + D” is meant as a Sublime Text speech.
- Ease of expansion, almost all IDEs are closed, no support for samopisny paginov, 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, as the organization on projects left its mark on the Sublime Text setting.
We have one rule at work - "The source is true." Those. we work directly with source files, and not in the database itself. First of all, because we need versioning, and no one has canceled the source version control system. Thus, we can always look at what is now at the base and what is in SVN in order to understand what the discrepancies are, if the client, for example, got into the database with his hands, corrected something, for example in a procedure or package. Also at our place everything rolls through sqlplus.
Our project has adopted a specific source storage structure, where each file contains its own objects. Nr: the tables for the task are in the “tables” folder, the file name has the table name “table_contract.tab.sql”, the views are in the “views” folder and the files are called “vv_table_contract.sql”, the packages are in the “packages” folder ”And the 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 is interesting how we have organized the structure, SVN, release versions, update clients, etc. taking into account the fact that we are writing 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 in Habré.
Highlight syntax and database objects
Let's start with the simple - this is syntax highlighting. It's not about the color palette, this is a personal matter, but we will highlight the database objects, since it is convenient and the code becomes clearer and more understandable. Here is an example of how it looks:
To highlight objects, I use the file (PL_SQL (Oracle) .tmLanguage) to highlight the syntax from the github.com/bizoo/OracleSQL plugin, which will be discussed later.
In the file, add a new tag:
<dict> <key>match</key> <string>(?i)\b()\b</string> <key>name</key> <string>dbobject.oracle</string> </dict> Where between brackets we add new database objects for highlighting. I have tables and views. I get a list of objects in 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:
<dict> <key>name</key> <string>String</string> <key>scope</key> <string>dbobject.oracle</string> <key>settings</key> <dict> <key>foreground</key> <string>#7F7F00</string> </dict> </dict> Object tooltip
To set up a tooltip for objects using a combination of ctrl + space, as well as to assist in completing 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 by combining Ctrl + Space. I extract the necessary data in approximately 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 assist in completing words or naming database objects, I use the “All autocomplete” plugin - this plugin looks through all open tabs, selects all words in them, excluding duplicates, and also shows it in the tooltip. In the end, it looks like this.
Version Control System
As a version control system, we use SVN. Sublime Text has many different plugins in the repository, I stopped at “TourtoiseSVN”, idle time, the necessary basic operations are performed by Commit, Update, Show log, Diff with corresponding hot keys (Alt + C, Alt + L, Alt + U), and for more complex things, such as tagging or merging, there is a customer of the same name.
Search by objects
To search for objects in a task (project), I use both the standard functionality of Sublime Text - Goto Anything, and the small plugin I wrote myself. The plugin is very simple and in fact slightly complements the standard Goto Anything by the fact that in the input field immediately adds the word on which the cursor stands. 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}) Taking into account how organized the file structure of the project is, as described at the beginning of the article, I got the functionality for quick access to the object I need.
Code execution
Now we come to the most interesting, this execution / rolling of scripts from Sublime Text. To accomplish these goals, I use the github.com/bizoo/OracleSQL package that has been modified to fit my needs and requirements. Modifications were made mainly in terms of simplifying the plugin, since The plugin itself is also a bit sharpened to 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 like this.
Simple SQL queries can be executed in the same way. The only thing you need to do is make the sqlplus settings, for this you can try using login.sql, which should work every time you connect via sqlplus. I didn’t do that, I just made a snippet for the commands I needed or in general you can keep one file for these purposes where everything is already recorded in advance.
Total
As a result, I got a fast, easy, free, cross-platform and easily extensible PL / SQL development tool that also fits perfectly with the rules at work.
PS: I understand that there are flaws in this “assembly”, but for me they are not critical, and even if you can overcome your laziness, you can continue to write.
Source: https://habr.com/ru/post/272155/
All Articles