Working with source codes in DataGrip
Basics
The source code of the object is the core part of the DDL script which is needed to create this object. In other words, the CREATE statement is not part of the object’s source code. The source code is stored inside the database. The most common objects found with the source code are views, functions, and stored procedures.
In many other tools, to update the source code, you would need to generate the CREATE OR REPLACE script, make the required changes and run it. In DataGrip it works a little differently. You just need to make the changes you need to make and the proper script will be generated for you.
People coming from other tools all make the same mistake: they open the DDL, copy it, modify it, adjust the CREATE statement (usually add the OR REPLACE part) and run the new script. This is not the way you are supposed to update the source code.
Loading sources
For any data source, DataGrip performs the process called ‘introspection’ – the IDE retrieves all the metadata for objects in advance. This means that all source code is loaded when the introspection is performed.
You can manage this process by choosing the required value in the Load Sources for options in the data source properties.
In Oracle there is the possibility not to load sources by choosing a lower introspection level:
You might be asking: if the source code is loaded when introspection is performed, doesn’t it mean that they are out-dated?
Yes, it does. Handling this situation will be covered later in our tutorial.
The flow
Double-clicking on the object in the database explorer or pressing Cmd/Ctrl+B on it in the SQL script opens the DDL editor. In case of the view, double-clicking on it in the database explorer opens the data: to open the DDL explorer click on the DDL button in the toolbar.
The DDL editor will open. Here you can find the CREATE script for the object.
Important! The object references in the generated script are not qualified, in other words, %view_name% is used rather than the %schema_name%.%view_name%. The reason for this is the ability to copy the script for applying it in another context. If you want a script with qualified references, use the SQL generator.
In the DDL editor, you can change the source code. When you edit the source code of any object, DataGrip tracks changes and highlights them in the gutter.
For example, add a comment line to a procedure or a function. The line you added is highlighted. If you click the highlighted line in the gutter, a small toolbar is displayed with the Show Diff button. You can click the Show Diff button to see the difference between the code that you added and the source code.
After you made the required changes, click the Submit button.
DataGrip will generate the modification script and show you a preview.
If you are okay with the result, click on OK and the script will be executed in the database. As a result, the required source code will be changed.
DataGrip not only adds OR REPLACE to the creation script, but it can also handle more difficult situations, i.e. changing the object’s signature or object renaming. When it’s needed, DROP and CREATE scripts will be created.
Changing several objects at once
When the changes are made in the DDL editor, but not submitted yet, DataGrip stores them until you submit them. For example, if you changed several objects, several changed DDL are cached waiting for being applied. In the tool window called Database Changes, you can observe all pending source code changes and submit them all together at once.
Outdated cached objects
As we mentioned, DataGrip caches the source code which was loaded at the introspection. If an object that you opened was updated from a third-party location, you will see a notification that the cached object differs from the source code of the same object in the database.
If you see this warning in the IDE, you can choose one of the following actions to take:
- Synchronize: fetch changes from the database and update the cached local object.
- Disable check: disable this notification.
Also, there might be a conflict between your version of the object source code and that in the database. For example, when you have modified the same source code as someone else and clicked Submit.
You can forcefully replace the source code of the object in the database using Force Refactoring or you can synchronize the object state and then proceed with changes by clicking Abort Refactoring and Synchronize.
If you’ve selected Abort Refactoring and Synchronize, DataGrip aborts the submit operation and fetches the changes from the database just like if you had clicked Synchronize. If the conflict still exists, you will see the following notification.
In this notification, you can choose between the following options:
- Revert local changes: roll back all your changes and replace them with the version from the database.
- Keep local changes: use your changes and overwrite changes in the database.
- Merge: display the diff dialog to merge the versions of the object source code.
Local history
All the changes you make to the source code are stored locally. If you want to go back in time and check the source code of the function before the latest updates you can do it with the help of Local History.
The revisions in Local History contain all your local changes and also include the versions retrieved from the database during introspections. So, the versions of the object that were introduced from third-party sources and not ‘loaded’ during the introspection may be missing here. You can compare any historical revision with the current version of the source code.
If you are the only user who works with a particular database, and you change sources only from DataGrip, Local History has a complete history of the objects’ changes.
That’s it! We know that this flow can feel unfamiliar, especially if you came from the other tool, but following this flow will make your life easier by removing repetitive operations.
The DataGrip team.
P.S. The color scheme used for the screenshots is Monocai.
Subscribe to DataGrip Blog updates
