The SQL Explorer, used in both SymmetricDS Pro and Metl, has received a major update. Many exciting new features, including code completion, visual table editing, and database and trigger information tabs, have been included in the newest version of the SQL Explorer, in addition to several performance improvements.

The new features and improvements to the SQL Explorer are now available in SymmetricDS Pro version 3.8.0 and Metl version 1.4.0.

 

Code Completion

One of the most exciting new features to the SQL Explorer is the incorporation of code completion. The new feature allows users to get suggestions for catalog names, schema names and table names after pressing control + space or typing a dot. The code completion also supports aliases, and it can suggest alias names for tables after they have been declared. In addition, valid column names are suggested after table names have been included in the sql code, both ahead and behind the cursor. Valid column names will also be displayed when using aliases, as seen below.

 

Table Editing

Another great new feature in the SQL Explorer is the ability to update database tables through the table displays. This allows for database tables to be easily updated after querying for tables via a select statement or while viewing the table data through the table information screen, without the need of a separate update statement.

To open the editor, the user must simply double-click on a row on one of these screens. While editing, the edited values are checked for valid data types for the given column and are checked for repeated primary keys to help prevent failed updates. The “save” button on the editor executes an update for all changed values and commits them to the database automatically.

Note: The editor will not allow editing of columns that cannot be modified. If the table cannot be modified, then the previous read-only information window will be used in replacement of the editor.

 

Download & Upload Lobs

In addition to editing most values through the editor mentioned above, additional support for large objects, such as blobs and clobs, has been added through the read-only information window, which can be accessed via a button in the editor.

Now large objects can be either downloaded from or uploaded to the database. The information window provides a button for downloading the file and lists the size of the file prior to download. There is also a button that opens an upload screen, in which the user can select a local file to upload to the database as a large object.

 

Db Compare

Db Compare is a handy feature included in SymmetricDS that is now supported by the SQL Explorer in SymmetricDS Pro. The feature can be accessed via the drop-down menu above any query panel.

 

The Db Compare feature allows the user to select two different local engines, a source and a target, and compare tables that are located on both databases. The user is able to select specific tables to compare or compare all valid tables.

 

The user can also select one of four output options for the comparison:

  1. A report showing comparison information regarding the selected tables on both databases
  2. A set of sql statements that, when executed, sync the tables on the target database to match the source database, which are added to a new query tab for the target database
  3. A sql file containing the sql statements that sync the target tables, as mentioned in #2
  4. The immediate syncing of the target tables through the execution of the sql statements that sync the target tables, as mentioned in #2

 

Database Data

Previously, the SQL Explorer featured the ability to view table information, including column information, foreign keys and source sql. With this new update, it is now possible to view, not only table information, but also database information including metadata, connection details, data types and supported functions. Such additional information can be accessed by selecting a database from the left menu.

 

Trigger Data

In addition to adding database information to SQL Explorer, tables in the left menu can now be expanded to show a list of triggers that are associated with it.

By clicking on these triggers, information about them, including their source sql, can be viewed in a new tab.

 

Cancel Queries

A small, but lucrative feature now included in the SQL Explorer is the ability to cancel queries while they are being executed. This provides a simple method to put an end to expensive queries when needed.

 

Improvements

Faster Database Table Load

The newest update to the SQL Explorer features up to 3x faster load times for the tables listed in the left menu for SQL Server and Oracle databases.

Handle Open Connections

While using the SQL Explorer with the auto-commit setting off, query tabs are now restricted from closing if a connection is left open. This improves the safety of modifying databases by preventing hanging open connections.

Sort Tables

Tables listed in the left menu are now always displayed in alphabetical order, rather than the default order provided by the database to eliminate searching through long lists for specific tables.

Summary

The new features and improvements, from code completion and visual table editing, to database and trigger data, make the newest update of SQL Explorer more useful and applicable to users' needs. Hope you enjoy!

Ryan SunderHaus

Ryan is a software developer intern working on SQL Explorer and the Gradle build of the SymmetricDS C client. He is a student of Computer Science at Miami University of Ohio, graduating in Spring of 2017. He holds the current high score for Joust at the office.