August 3, 2017 at 14:52DataGrip 2017.2: Support for Redshift and Azure, several databases in PostgreSQL, transaction control and more
Hello! Continuing a series of posts about new versions of the IDE from JetBrains. Let's talk about what's new in DataGrip 2017.2 .
- Support for Amazon Redshift and Microsoft Azure
- Multiple databases for one PostgreSQL source
- Transaction control
- Expression calculation
- Separated DDL and Data tabs for tables
- Integration with recovery tools for PostgreSQL and MySQL
- Improvements related to query triggering
- Improvements related to writing code
and more ...
Cloud technology is gaining momentum, and we are trying to keep up. Users asked to support some of them in our tracker, especially Redshift .
Microsoft Azure is similar to SQL Server : we added a driver, an interface for creating a data source, and improved retrieval of information about objects. This process is called "introspection."
Introspection at Amazon Redshift has become incremental: after the operation, DataGrip seeks information only about changed objects.
They supported specific parts of the grammar that PostgreSQL does not have . For example, UNLOADit is highlighted correctly, and the query in the argument line is processed like regular SQL - autocompletion and navigation work.
Another example: they supported functions that are not in PostgreSQL .
If something is highlighted in red in the code for Redshift , and you know that it is correct, this is a bug. Please write about this in our tracker .
If you connected to Azure and Redshift through drivers for SQL Server and PostgreSQL , please switch to the required driver from the context menu.
We have been waiting for this thing for a long time , and we thank those who have waited :)
For this, we have rewritten a significant part of the kernel and are still working on it. So your opinion about using multiple databases in PostgreSQL is especially important to us.
Multi-database data sources now work in Amazon Redshift , in Microsoft Azure, and in other databases that you connect to through JDBC if the driver itself supports it.
Transaction Control has replaced the Auto-commit option .
Define the level of transaction control for each data source. In manual mode ( the Manual ) transaction must be fixed by performing a COMMIT . In automatic mode ( Auto ) - no.
The level of transaction control can also be determined for each console separately along with the isolation level ( Isolation level ), if the database supports it.
In the data editor, in manual mode, two buttons were added: Commit and Rollback . These actions are also available in the context menu.
It works like this:
The Submit or Ctrl / Cmd + Enter button sends data to the database: your local changes are rolled up , which until that moment were highlighted and stored inside the DataGrip session. But this transaction will not be committed if you have manual mode.
Revert Selected from the context menu or Ctrl / Cmd + Alt + Z on selected lines rolls back local changes in these lines. This was previously called by Ctrl + Z , but usually this key combination means cancel, not rollback.
The Commit or Shift + Ctrl + Alt + Enter button captures the transaction. If you have local changes that are not sent to the database (recall: highlighted), they will automatically be sent to the database before committing.
The Rollback button rolls back an uncommitted transaction.
This will help you quickly see the data without writing a separate request.
As with our other IDEs, use the keyboard shortcut Ctrl + Alt + F8 to quickly calculate the value of an expression. In this case, the expression refers to the value of the database object, for example, for a table, this is the data itself.
For the column from the query, these are the column values in the expected result.
If you perform the same action on a keyword in a query (or subquery), its result will appear in a pop-up window. Alt + Click also works for this .
Alt + F8 brings up a separate window for evaluating expressions. As in the “quick” version, for the table you will see the data.
Here you can calculate expressions in the classical sense of this.
We have separated the table data from the source code - there are no more DDL and Data tabs .
Now double-clicking on the table opens the data. To view DDL, click Edit Source on the toolbar or Ctrl / Cmd + B on the table.
You will see the same DDL editor if you press Ctrl / Cmd + B on the table name in the SQL script. Ctrl / Cmd + click does the same. In previous versions, this action highlighted an object in the database tree. In 2017.2, to do this, press Alt + F1 and select Database view . Write to us if it became inconvenient: after all, for such an action there used to be one click, and now two.
But the data editor for the table has become easier to open - just press F4in code or in a tree.
In 2016.3, we integrated mysqldump and pg_dump into DataGrip . It was logical to integrate recovery tools for these databases, they even asked for it on Habr last time. They appeared in the context menu. If only one tool is available in this context, for example, the menu item is called ' Restore with pg_restore '.
If the database is PostgreSQL , then you can use pg_dump or psql : select at the top of the dialog.
And so the window looks for MySQL :
Added a new action - Attach console . It is called, as always, from the action search by Ctrl + Shift + A or the file context menu. The goal is to run the file in the context of the specific console that you attach to it.
Thus, you can execute the script on several data sources in series.
It was requested : notification of the completion of long requests. Now a request, the execution time of which is more than 20 seconds, will throw such a notification inside the DataGrip. Notification can be turned off: the notification name is “Database queries that took much time”.
One more thing that was asked : now, if you switch the console, the result obtained from it also switches.
And vice versa: when switching tabs with results, consoles switch.
This behavior is now by default - to disable it, look for the “ Autoscroll ..” options in the settings menu using the gear icon.
Another small improvement - when you run a query with a subquery, the default selection window has an external query, rather than an internal one, as before. Internal queries often cannot be completed.
We fixed the schema switch for read-only connections in MySQL .
Auto-completion now works for functions that return tables. NEW and OLD
links are correctly processed for triggers in PostgreSQL. Added support for MERGE offers . Supported sequences in scripts.
- database objects you can add to your favorites ( the Bookmarks ).
- File indexing can be stopped and continued at any time.
- Search results in Find in Path from the same lines are combined into a single result.
- Support for ALTER FOREIGN TABLE in PostgreSQL .
- Support for the ' json_table ' function in Oracle .
- All attributes in the generated DDL for Redshift are displayed .
- DBE-4600 , the renamed schema is now immediately displayed in the script and in the tree.
- DBE-1288 , renaming sequences in PostgreSQL does not break the query.
- DBE-4507 , now you can create a composite primary key.
- DBE-4637 , queries with GROUP BY no longer report an error that is not there.
- Scrolling in the data editor has become faster.
And as always: download here , report bugs here , and we also respond on the forum , on Twitter and here, in the comments.
Thanks!
JetBrains
- Support for Amazon Redshift and Microsoft Azure
- Multiple databases for one PostgreSQL source
- Transaction control
- Expression calculation
- Separated DDL and Data tabs for tables
- Integration with recovery tools for PostgreSQL and MySQL
- Improvements related to query triggering
- Improvements related to writing code
and more ...
Amazon Redshift and Microsoft Azure Support
Cloud technology is gaining momentum, and we are trying to keep up. Users asked to support some of them in our tracker, especially Redshift .
Microsoft Azure is similar to SQL Server : we added a driver, an interface for creating a data source, and improved retrieval of information about objects. This process is called "introspection."
Introspection at Amazon Redshift has become incremental: after the operation, DataGrip seeks information only about changed objects.
They supported specific parts of the grammar that PostgreSQL does not have . For example, UNLOADit is highlighted correctly, and the query in the argument line is processed like regular SQL - autocompletion and navigation work.
Another example: they supported functions that are not in PostgreSQL .
If something is highlighted in red in the code for Redshift , and you know that it is correct, this is a bug. Please write about this in our tracker .
If you connected to Azure and Redshift through drivers for SQL Server and PostgreSQL , please switch to the required driver from the context menu.
Multiple Databases for a Single PostgreSQL Source
We have been waiting for this thing for a long time , and we thank those who have waited :)
For this, we have rewritten a significant part of the kernel and are still working on it. So your opinion about using multiple databases in PostgreSQL is especially important to us.
Multi-database data sources now work in Amazon Redshift , in Microsoft Azure, and in other databases that you connect to through JDBC if the driver itself supports it.
Trade Control
Transaction Control has replaced the Auto-commit option .
Define the level of transaction control for each data source. In manual mode ( the Manual ) transaction must be fixed by performing a COMMIT . In automatic mode ( Auto ) - no.
The level of transaction control can also be determined for each console separately along with the isolation level ( Isolation level ), if the database supports it.
In the data editor, in manual mode, two buttons were added: Commit and Rollback . These actions are also available in the context menu.
It works like this:
Automatic and manual mode.
The Submit or Ctrl / Cmd + Enter button sends data to the database: your local changes are rolled up , which until that moment were highlighted and stored inside the DataGrip session. But this transaction will not be committed if you have manual mode.
Revert Selected from the context menu or Ctrl / Cmd + Alt + Z on selected lines rolls back local changes in these lines. This was previously called by Ctrl + Z , but usually this key combination means cancel, not rollback.
Only in manual mode
The Commit or Shift + Ctrl + Alt + Enter button captures the transaction. If you have local changes that are not sent to the database (recall: highlighted), they will automatically be sent to the database before committing.
The Rollback button rolls back an uncommitted transaction.
Expression Evaluation
This will help you quickly see the data without writing a separate request.
As with our other IDEs, use the keyboard shortcut Ctrl + Alt + F8 to quickly calculate the value of an expression. In this case, the expression refers to the value of the database object, for example, for a table, this is the data itself.
For the column from the query, these are the column values in the expected result.
If you perform the same action on a keyword in a query (or subquery), its result will appear in a pop-up window. Alt + Click also works for this .
Alt + F8 brings up a separate window for evaluating expressions. As in the “quick” version, for the table you will see the data.
Here you can calculate expressions in the classical sense of this.
DDL tables
We have separated the table data from the source code - there are no more DDL and Data tabs .
Now double-clicking on the table opens the data. To view DDL, click Edit Source on the toolbar or Ctrl / Cmd + B on the table.
You will see the same DDL editor if you press Ctrl / Cmd + B on the table name in the SQL script. Ctrl / Cmd + click does the same. In previous versions, this action highlighted an object in the database tree. In 2017.2, to do this, press Alt + F1 and select Database view . Write to us if it became inconvenient: after all, for such an action there used to be one click, and now two.
But the data editor for the table has become easier to open - just press F4in code or in a tree.
Integration with recovery tools for PostgreSQL and MySQL
In 2016.3, we integrated mysqldump and pg_dump into DataGrip . It was logical to integrate recovery tools for these databases, they even asked for it on Habr last time. They appeared in the context menu. If only one tool is available in this context, for example, the menu item is called ' Restore with pg_restore '.
If the database is PostgreSQL , then you can use pg_dump or psql : select at the top of the dialog.
And so the window looks for MySQL :
Running queries
Added a new action - Attach console . It is called, as always, from the action search by Ctrl + Shift + A or the file context menu. The goal is to run the file in the context of the specific console that you attach to it.
Thus, you can execute the script on several data sources in series.
It was requested : notification of the completion of long requests. Now a request, the execution time of which is more than 20 seconds, will throw such a notification inside the DataGrip. Notification can be turned off: the notification name is “Database queries that took much time”.
One more thing that was asked : now, if you switch the console, the result obtained from it also switches.
And vice versa: when switching tabs with results, consoles switch.
This behavior is now by default - to disable it, look for the “ Autoscroll ..” options in the settings menu using the gear icon.
Another small improvement - when you run a query with a subquery, the default selection window has an external query, rather than an internal one, as before. Internal queries often cannot be completed.
We fixed the schema switch for read-only connections in MySQL .
Code writing
Auto-completion now works for functions that return tables. NEW and OLD
links are correctly processed for triggers in PostgreSQL. Added support for MERGE offers . Supported sequences in scripts.
Rest
Added by:
- database objects you can add to your favorites ( the Bookmarks ).
- File indexing can be stopped and continued at any time.
- Search results in Find in Path from the same lines are combined into a single result.
- Support for ALTER FOREIGN TABLE in PostgreSQL .
- Support for the ' json_table ' function in Oracle .
- All attributes in the generated DDL for Redshift are displayed .
Fixed:
- DBE-4600 , the renamed schema is now immediately displayed in the script and in the tree.
- DBE-1288 , renaming sequences in PostgreSQL does not break the query.
- DBE-4507 , now you can create a composite primary key.
- DBE-4637 , queries with GROUP BY no longer report an error that is not there.
- Scrolling in the data editor has become faster.
And as always: download here , report bugs here , and we also respond on the forum , on Twitter and here, in the comments.
Thanks!
JetBrains