New DBMS support
Database сloud services are growing more popular with every day. We were asked to add support for
these two, especially Redshift, and here they are.
Microsoft Azure is similar to SQL Server, so we just added a dedicated driver, UI for adding
the
data source and some enhancements in the introspection.
But there are many things we did for Amazon Redshift. The introspection is now incremental.
This
means that only modified objects will be refreshed in a database tree after any operation instead of
all objects.
Also, we started to support specific cases in SQL grammar which are different from PostgreSQL.
For example, UNLOAD statement is not only highlighted correctly, but has an SQL-injection
inside.
Another example: support for embedded Redshift functions that are absent in PostgreSQL.
Generation of the DDL scripts for tables and functions has been enhanced.
Now DDL for tables contains the following attribute:
[ BACKUP { YES | NO } ]
column_attributes:
[ IDENTITY ( seed, step ) ]
[ ENCODE encoding ]
[ DISTKEY ]
[ SORTKEY ]
and table_attributes:
[ DISTSTYLE { EVEN | KEY | ALL } ]
[ DISTKEY ( column_name ) ]
[ [COMPOUND | INTERLEAVED ] SORTKEY ( column_name [, ...] ) ]
The source code of a function is now generated with the following section:
{VOLATILE | STABLE | IMMUTABLE }
In general, our support means, that if a part of your Redshift code is highlighted as an error
but in fact it isn’t, we consider this a bug. Please report such cases to our issue tracker.
If you’re already using Azure and Redshift via SQL Server and PostgreSQL drivers, please
re-select the driver in the data source properties window.
Several databases in PostgreSQL
This was a really long-awaited feature, but the wait is finally over. Thanks for bearing with us! We
refactored a lot of code to implement this functionality, so some things might still be missing.
Please give us your feedback on your experience using data sources with multiple databases.
Data sources with several databases are also supported for Amazon Redshift, Microsoft Azure,
and any unsupported databases whose drivers report several databases.
Transaction Control
This feature replaced the Auto-commit option.
First, define what kind of transaction control you want to use when working with a particular data
source. This option is available in data source properties. In the Auto mode you don’t need to
commit any transactions executing the commit statement, while in the Manual mode, you, obviously
need to do this.
This also can be defined for each query console along with the isolation level.
The data editor in the Manual mode also has Commit and Rollback buttons on toolbar. All actions are also available in the context menu.
So, now it works like this:
Auto and Manual mode
Submit button or Ctrl+Enter → submits your data, which means that your local changes(they are
highlighted) are submitted to the database. If you are in the Manual mode this transaction is not
committed.
Revert Selected from the context menu or Ctrl+Alt+Z (it used to be Ctrl+Z , but Revert isn’t
Undo, right?) on selected rows → reverts unsubmitted local changes of the selected rows.
Only in Manual mode
Commit button or Shift+Ctrl+Alt+Enter → commits the transaction. If you have unsubmitted local
changes (again, they are highlighted) they will be automatically submitted before the commit.
Rollback button → rolls back transaction if it’s uncommitted.
Evaluate expression
This will help you see data without writing a query to the console.
In other IntelliJ-based IDEs, Ctrl+Alt+F8 on an object gives you quick evaluation. In DataGrip,
invoke it on a table in a query to see the data of that table.
Ctrl+Alt+F8 on a column name will show the values of that column in the expected result-set.
If you invoke the same quick evaluate on the keyword of a query (or subquery), the pop-up will show you the result. Note that Alt+Click also works for this.
Press Alt+F8 to open an Evaluate pop-up, where any query can be run. If invoked on a table, it will show the data.
You can evaluate expressions here as well.
Table DDL
We have divided the Data and DDL tabs when viewing a table. Actually, there are no more tabs. Now, when you open a table with a double-click, you just see data. Where is the DDL? It can be opened by Edit Source on the toolbar or by Ctrl+B.
The same DDL editor will open when pressing Ctrl+B on the table name in the SQL script. As
you may remember, Ctrl+Click does the same. In the previous versions this action brought you
to the database tree. If you still need this, press Alt+F1 on any object and choose Database
view.
It became easier to open Data editor for a table. Just click F4 on the table name, either in the
database tree or in SQL.
Integration with restore tools
In 2016.3 we integrated DataGrip with mysqldump and pg_dump, so it’s logical to add integration with the restore tools of these databases as well. Now they can be accessed from the context menu. If there’s only one tool available, this option will look like ‘Restore with pg_restore’, etc.
It looks like this in MySQL.
In the case of PostgreSQL, the restore operation can be done with pg_dump or psql: there is a chooser in the Restore dialog.
Executing queries
Attach console
There is a new action for any file called Attach console. You can find via Ctrl+Shift+A or by opening the context menu for a file. After performing this action, this file can be run against the console. Switching consoles will be useful if you want to run the same script in several data sources.
Notifications
Called-for feature: a notification is now displayed when a large query is finished. To disable it, in Settings look for the “Database queries that took much time” notification. It appears if it took more than 20 seconds to execute a statement.
Autoscroll from/to result-set
Many of you have asked for result-sets to be switched along with the corresponding consoles. Done! The same works in the opposite direction.
These behaviors are on by default, look for the “Autoscroll ..” toggles in the gear-icon menu of the Database Console tool window.
Subqueries
When you execute a query with subqueries, the outer statement is selected in the drop-down by default. Well, nested statements often just cannot be executed at all.
Read-only connections
Schema switcher works in MySQL even with a read-only connection.
Coding assistance
Completion now works for columns in table-valued functions.
We’ve added support for MERGE statements for all databases where they are available.
Sequences are resolved in scripts as well.
Other Improvements
New features
- Database objects can be added to Bookmarks.
- We added per-monitor DPI support for Windows. Font settings are automatically adjusted based on the display resolution.
- Lines with two identical results are merged in Find in Path.
- Support for non-int-literal expressions in TABLESAMPLE and ALTER FOREIGN TABLE in PostgreSQL.
- Support for ‘json_table’ function in Oracle.
- You can now pause file indexing when you need to free the CPU for other tasks, and then resume it when appropriate.