In the everyday work, DTM SQL editor as an integrated environment - provides a lot of methods to speed up the work of a developer, a DBA or a user. We have been using this tool for many years and would like to share our experience concerning these techniques and solutions.
Predefined and User Defined Macros
The simplest method that is quite effective for entering standard SQL statements is using keyboard macros. There are two types of macros in the product: predefined and user-defined. A macro is a text string that will be inserted into the active (currently edited) script when the Ctrl+Shift+<letter> key combination is pressed. You can use the "Macros" dialog box (menu: Script->Macro definitions) to view the list of letters that can be used for user-defined macros and that are already used for predefined macros. This dialog box is opened when you click the corresponding button on the toolbar.
It is also possible to change the values of predefined macros. To do it, you should open the registry subkey of the product and add the 'MACn' entry in the "Settings" subkey, where n is a number from 1 to 5 corresponding to the macro type. The macro will work after the program is restarted.
The most effective way to use macros is storing the most frequently used fragments of SQL
statement in them. There are already predefined macros for such statements as "select * from"
and "order by". For example, if you want to quickly create various result sets from the
same table, it is convenient to use a macro for the list of fields that must be selected.
The most frequent problem that users face when they work with keyboard macros is taking the
specified values to another computer or system. The easiest way to do it is to use the
"Export/Import Settings" commands from the "Tools" menu. However, this method allows you to
transfer only user-defined macros.
SQL Library
Another way we are going to tell you about is SQL Library. A script library is a hierarchical storage of files with the SQL extension. DTM SQL editor provides you with a convenient mechanism for both extracting files and saving them to such a library.
By default (right after the product is installed), the script library is located in the
Library subdirectory of the directory where the product is installed. But you can easily
change its location in the Settings dialog box.
The most convenient location of the library for individual users will be a directory on
the local disk, while the most productive location of the library for a team of developers
can be on a shared network resource.
We think that such a library is the most convenient way to store both ready-made SQL statements and their frequently used (especially by team members) fragments. Perfect candidates for such storage are complex WHERE or ORDER BY clauses that represent the structure of the database and are used quite regularly, but do not have to be modified often.
Another group of scripts that are worth storing in the library are various reports created only once and used periodically - it will be particularly easy to extract them from the storage. The thought-out names of groups and scripts make it considerably easier to navigate the library.
Vesion Control for SQL scenarios
Version control is the most important technique when some complex software is being developed by a team of developers. There are two ways to use such systems with DTM SQL editor. The first one is to use the built-in VSS (Microsoft Visual Source Safe) support and the second one is to use a plug-in to start virtually any version control system from the command line.
The program has a special toolbar for integration with VSS and the corresponding menu where
all necessary operations can be accessed: project configuration, check-in, check-out, undo
check-out and add a new item to the project.
Integrating an external VCS is a bit more complicated. You need to configure at least two
menu items (check-in and check-out) using the "run-it" plug-in.
- create two copies of run_it.dll in the program directory. For instance, checkin.dll and checkout.dll
- use the example from the plug-in to create two configuration files with the same names, but with the INI extension, for instance, checkin.ini and checkout.ini.
- modify the "MenuItem" items in both files to specify how these menu items will be displayed in the editor
- modify the CommandLine parameter in both configuration files so that it executes the necessary operation corresponding to the menu item, that is either check-in or check-out. In this string you can use such special metacharacters as %f, etc. that are described in the documentation for the plug-in in detail.
- restart the program and right-click the script editor to access the new menu items
We suggest that you disable saving .BAK files in the settings if you use a version control system. It will allow you to speed up working with scripts and save disk space on the local system.
User Interface Tips
In conclusion, we are going to tell you about some peculiarities in the interface of the editor
that can make working with scripts easier or faster.
The local tab control menu of the list of scripts allows you to access the most useful
features without having to use the main menu of the program.
If you are developing a complex script with nested queries, executing only the selected
part of the currently edited script can turn out to be a very useful feature. The corresponding
option can be switched on or off in the "Settings" dialog box.
The program supports the drag-n-drop feature both for texts and their fragments and for files: you can easily drag and drop the file you need onto the editor and it will be opened automatically. Another useful mechanism is moving object names from the tree representing the database schema to the editor. This method of entering data is a good addition to entering the names of tables and fields for the existing connection automatically.
We suggest that developers who need to considerably enhance the functionality of the product pay attention to the possibility of enhancing the product features (including the text editor) by using the plug-in mechanism. Ready-made examples in SDK make it fairly easy to develop small plug-ins for the product. There are about 10 free modules including those directly extending the functionality of the editor or making it easier to work with scripts. Among them, we would like to mention the possibility to use a preprocessor (either built-in or external), sending the current script by e-mail, etc.