Special Features.
Here is the list of features that we would like to highlight specially.
- Dynamic syntax and errors highlighting
- Intelligent Code Completion
- Refactoring
- SQL Debugger
- Database Comparer
- Database Designer - new style of database development
- SUID Procedures
- Statistics Analyzer
- Regular database maintenance
Dynamic syntax and errors highlighting.
For the first time the database development tool includes a feature like this. SQL code becomes colored dynamically when you edit it and all errors are visible immediately. Thanks to full-featured SQL syntax parser that made it possible. You will not have a chance to make typing errors ! And even more: it is possible to check the code for some logical errors. For example, take a look at the following statement:
It has two fields in "select" statement and only one
variable specified for the output. The parser generates the following error
message: "Invalid number of parameters (1), should be 2". And a number of other
logical errors are also checked.
When you type a field or variable name
that does not exists it also becomes highlighted and you have a signal to
correct error. Take a look at the following example:
Variable "location" is not defined yet so it appears to be red-colored. The
same is true for table fields.
Each type of object may have different
color. As you can see on examples above table names, fields, variables and
reserved words have different colors. Color settings are customizable.
There are several color schemes comes with installation package and you are free
to create your own. See details in Options->Code Editor.
Code Completion is a "must have" feature for the mordern programming environment. And of course it is supported in IB/FB Development Studio. We tried to make it as smart as possible. It does not show just a list of reserved words and database object names. Instead it checks the syntax of the statement you writing and shows the list of words that fits the syntax requirements.
Project structure cannon be unchanged from the initial design. Sometimes you need to change a lot of things to support new features, requirements or to communicate with external system. Unfortunately a lot of simultaneous changes in an Interbase/Firebird database is a real pain. Imagine that you want to rename stored procedure that is used in dozens of other procedures and triggers. To pass through dependencies between objects you have to drop almost half of your database and recreate it from scratch. Simple rename may take whole day to complete. And what about adding one more parameter to the stored procedure used in half of the rest procedures ? Only automated solution may help.
Refactoring is now a well-known approach for regular programming languages like Java, C++ and Pascal. But for the first time it becomes available for SQL development tool. Database Editor and Database Designer has a "Refactoring" menu item in the main menu. Here is the list of actions you can do with it:
- Highlight - allows to highlight selected name in the editor's text. It can be useful when your procedure is pretty big and you want to find all occurances of specified table or table field or variable.
- Rename - allows to rename all occurances of the selected object. Of course it is not equal to simple text renaming because it uses syntax parser to find next occurance of the same object.
- Find References - finds references to the selected object in the whole database.
- Rename References - works like a Find References and provides a button which will generate SQL script to alter all dependent objects to reference the new object name.
- Add/Remove Parameter - allows automatically add or remove parameter to the stored procedure. All dependent objects will be altered automatically.
- Extract Procedure - the same as "Extract Method" in the regular programming languages. Allows to extract a part of the procedure code to new procedure and replace the code to the call to this new procedure.
Any code needs to be tested and debugged. And of course you cannot work productively without debugger for your stored procedures and triggers.
SQL Debugger has all things that you can expect from the debugger - step over and trace into, breakpoins, conditional breakpoints and watch list. And it also offers such SQL specific things like Result Data, statement Plan and statement execution statistics.
How do you update production database with a new metadata structure ? In most cases this is a manual process and if so - it takes a lot of time and needs to be done very very carefully. Database Comparer is an ideal solution for this. Deployment of the new structure will take about a minute to complete. All you need is to select your development database, production database and click "Compare" button. The result script will contain all statements required to carefully update your production database. You will be able to review the script and execute it right here.
Note: we always recommend to backup your production database before running any automatically generated scripts. Just to be safe.
Database Designer - new style of database development.
It is a common practice to use ER diagrams in the beginning of the database development. As the name implies, Database Designer offers ER diagraming feature but this is about quater of the functionality of this tool. The real database is not only tables. You need to write a bunch of stored procedures and triggers and they should be developed together with tables structure. The database should be developed as a whole.
Database Designer is a tool that can be used for complete database development. Database Designer project contains all sort of database objects and it may create database from scratch. The project is a repository where database objects are stored. And ER diagram is just one additional object type here. Database Designer has a version control system built-in so it can be used by team of developers. There is no need to explain how important version control system in a real work. Refactoring features can also be used here and SQL syntax parser allows to minimize number of errors. Moreover, before creating or updating physical database Database Designer performs project validation procedure to warn you about potential problems with your SQL code. Of course you may import your existing database to the Database Designer and continue development with help of version control and other useful features. You may update structure of the production database directly from Database Designer - it uses the same engine as Database Comparer. One more nice feature here is a HTML documentation on the project.
So the Database Designer may be used by team of developers for their daily work.
Select, Update, Insert and Delete Procedures
One of the good approach in the databae design is accessing data only via stored procedures. IB/FB Development Studio allows to automatically create such procedures based on the table's structure. Both Database Designer and Database Editor have this feature. You will find a menu item in the table's context menu.
Database statistics contains a lot of important information about how your database runs. You may find bad indices that decreases performace instead of increasing it and tables that seems to be not used at all because contains no data. But the way how Interbase/Firebird produces database statistics is hard to read. Administrative Console tool contains a Statistics Analyzer to simplify data presentation.
Interbase/Firebird servers requires almost none administration. Anyway, deployed database should be regulary backed up, checked for consistensy and may need to perform some other tasks depended on the implementation. IB/FB Development Studio contains a service for regular maintenance of the database. You could schedule backup with a lot of options like "check restore" - it will perform database restore to the temporary file to be sure that this backup can be restored at all. You could also schedule some stored procedure or SQL script to run in a specified day and time.
The maintenance service is installed by default and can be managed by "Maintenance Service Manager" tool.