DataSpell
 
2023.1
Shortcuts: Windows
Get DataSpell
You are viewing the documentation for an earlier version of DataSpell.
  1. Database tools and SQL
  2. Database objects
  3. Manage objects and source code
  4. Differences viewer for database objects

Differences viewer for database objects

Last modified: 02 March 2023

In the Database tool window, select two database objects and press Ctrl0D.

Compare Content is available in the context menu for tables, views, and materialized views.

To increase the number of compared rows, configure the Limit page size to parameter.

In DataSpell, you can compare database objects and the output of tables, views, and materialized views.

Compare database objects

In DataSpell, you can compare two database objects of the same type. For example, you can compare two schemas, two tables, or two routines. DataSpell shows you the differences in the structures of these two objects in the differences viewer of the Migration dialog.

Migration is a process of moving data from one object to another (for example, from one schema to another). In DataSpell, these objects are called Origin and Target. The migration script is generated to make Target equal to Origin. But you can modify the script to satisfy your requirements.

Compare table structures

For changes, DataSpell uses the following color coding.

Color

Description

Items are identical

Objects added in Origin, which will be created in Target in case of a successful migration.

Items are different

Modified objects, which will be altered in Target.

Items to be deleted

Objects deleted in Origin, which will be dropped in Target in case of a successful migration.

See the description of the dialog controls in the reference section.

Show differences between objects

  1. In the Database tool window ( Window | Tool Windows | Database) , select two objects of the same type. For example, two schemas.

  2. Right-click the selection and navigate to Tools | Compare Structure. Alternatively, press Ctrl0D.

Migrate changes between objects

  1. In the Database tool window ( Window | Tool Windows | Database) , select two objects of the same type. For example, two schemas.

  2. Right-click the selection and navigate to Tools | Compare Structure. Alternatively, press Ctrl0D.

    A set of statements that you can use for writing migration scripts is generated in the Script Preview tab.

  3. In the Script Preview tab, verify that the migration scripts are correct.

  4. Click Execute. Alternatively, click Open Query in Console to open all the scripts in a query console.

Show differences between changes in a table form

  1. In the Migration dialog, click an item in Origin or Target fields.

  2. Click the Object Properties Diff tab.

Show differences between changes in DDL

  1. In the Migration dialog, click an item in Origin or Target fields.

  2. Click the DDL Diff tab.

    differences between object properties

Select changes to migrate

  • To exclude a change, click the checkbox neat the change.

  • To exclude all the changes, click the checkbox near the Target label.

  • To include all the changes that were excluded, click the checkbox near the Target label.

Show identical items

  • To display all items that are identical in Origin and Target objects, select the Show identical checkbox.

    Show identical records

Compare the contents of tables, materialized views, and views

The Compare Content action compares the output of tables, views, and materialized views. To find this action, select two objects in the Database tool window ( Window | Tool Windows | Database), right-click the selection and navigate to Tools. Also, you can double-click these objects and then compare them from the editor.

You can compare the contents of two objects that belong to different types. For example, the output of a table and a materialized view.

Table contents

Description of Tolerance and Detect column insertion is available in Controls of the difference viewer for contents.

By default, when you issue a query, the number of returned rows is limited to 500. This limitation is introduced to avoid an overload (for example, when your SELECT statement returns one million rows). To increase the number of compared rows, configure the Limit page size to parameter.

Compare the contents of two tables from the Database tool window

By default, when you issue a query, the number of returned rows is limited to 500. This limitation is introduced to avoid an overload (for example, when your SELECT statement returns one million rows). To increase the number of compared rows, configure the Limit page size to parameter.

  1. In the Database tool window ( Window | Tool Windows | Database) , select two tables.

  2. Right-click the selection and navigate to Tools | Compare Content.

    If needed, change the value of the Tolerance parameter in the comparison dialog. The Tolerance parameter defines a maximum number of differences that are allowed between two result sets. For example, if you want to consider two rows as equal if their data differs in a single column, enter 1 in the Tolerance field.

    https://resources.jetbrains.com/help/img/idea/2023.1/db_compare_two_tables_from_the_database_tool_window.png
    Gif

Compare the contents of two tables from the data editor

  1. Open two tables that you want to compare. To open a table, double-click it in the Database tool window.

  2. In the editor, click the Compare Data button (the Compare Content button) and select the second table.

    https://resources.jetbrains.com/help/img/idea/2023.1/db_compare_table_data.png
    Gif

Controls of the difference viewer for database objects

Icon

Tooltip and shortcut

Description

Expand All

Expand All

Expands all the collapsed nodes.

Collapse All

Collapse All

Collapses all the expanded nodes.

Options

Options

Select options according to which changes are differentiated. If the option is selected, properties that are mentioned in the option are ignored in the diff.

  • Ignore Order: ignore the order in which items are going in the object.

  • Ignore Case: ignore casing in names of items.

  • Ignore Implicit Objects: ignore objects that are generated automatically. For example, indexes for foreign keys, DEFAULT CONSTRAINT for a column default in Microsoft SQL Server.

  • Ignore Source Code :ignore the body in functions or procedures. Objects are matched according to their kind and identity (for example, a function signature). Two functions are considered equal if their kind and identity are equal. Then the differences viewer takes into account this option – whether to ignore the function body or not.

  • Ignore Owner: ignore user privileges that are set for an item.

Selected

Include change

Apply the selected change.

Show identical

Show all items that are identical.

Controls of DDL Diff

Item

Tooltip and Shortcut

Description

the Previous Difference button/the Next Difference button

Previous Difference / Next Difference

ShiftF7 / F7

Jump to the next or previous difference.

the Jump to Source button

Jump to Source

F4

Open a definition of the selected object in the active pane in the editor. The caret is placed in the same position as in the differences viewer.

Side-by-side viewer

Select a viewer mode: Side-by-side viewer or Unified viewer. The side-by-side mode has two panels, and the unified mode has one panel.

Whitespaces

Define how the differences viewer should treat whitespaces.

  • Do not ignore: white spaces are important, and all the differences are highlighted. This option is selected by default.

  • Trim whitespaces: trim whitespaces if they appear in the end and at the beginning of a line (("\t", " ")).

    • If two lines differ in trailing whitespaces only, these lines are considered equal.

    • If two lines are different, trailing whitespaces are not highlighted in the By word mode.

  • Ignore whitespaces: white spaces are not important, regardless of their location in the source code.

  • Ignore whitespaces and empty lines: ignores whitespaces and empty lines. The following entities are ignored:

    • all whitespaces (as in the Ignore whitespaces option)

    • all added or removed lines that consist of whitespaces only

    • all the changes that split or join lines without changes to non-whitespace parts.

      For example, differences between a b c and a \n b c are not highlighted in this mode.

Highlighting mode

Select the way differences are highlighted.

The available options are:

  • Highlight words: modified words are highlighted

  • Highlight lines: modified lines are highlighted

  • Highlight split changes: if this option is selected, large changes are split into smaller changes.

    For example, A \n B and A X \n B X are treated as two changes instead of one.

  • Highlight Symbols: modified symbols are highlighted

  • Do not highlight: if this option is selected, the differences are not highlighted at all.

    Use the Do not highlight option when you work with objects that were significantly modified. In such cases, highlighting might introduce additional difficulties during a review.

the Settings button

Settings

Select options that you want to ignore during the comparison.

  • Show Whitespaces: select this check command to show whitespaces as the dots in the differences viewer.

  • Show Line Numbers: select this check command to show line numbers in the differences viewer.

  • Show Indent Guides: select this check command to have DataSpell display vertical lines in the differences viewer to indicate positions of indents.

  • Soft-Wrap: select this check command to have DataSpell wrap the lines of code, when the dialog is resized.

  • Highlighting Level: use this menu item to select the highlighting level in the differences viewer.

  • Annotate: select this check command to annotate the changes.

    tip

    This command is available only for the files under version control.

the Settings button

the Help icon

Help

F1

Open a browser and show the corresponding help page.

Controls of the difference viewer for contents

In the differences viewer for contents, you can use the same sorting functionality that is available in the data editor. For more information about sorting columns, see Sort data.

Detect column insertion

When the tables have a different number of columns, extra columns in the table with more columns are ignored. If the Detect column insertion option is on, the most different columns are ignored. On the following picture, the first column in the second table is the most different and so it is ignored. As a result, the second row is shown as containing the same data.

Compare table data

If the option is off, ignored are the last of the columns. On the following picture, the last column in the second table is ignored. So all the rows are shown as containing different data.

Compare table data with Detect column insertion off

Tolerance

The Tolerance parameter defines how many columns might differ to consider two rows equal. For example, if you set Tolerance to one, rows that differ in one column are considered equal.

Compare table data with tolerance equals to one

With tolerance set to zero, such rows are considered different.

Compare table data tolerance equals to zero

With this setting, you can also check the columns that differ when data in rows is different. Such rows in those columns are highlighted. Increase the Tolerance option if you have different data more than in one row. For example, with Tolerance set to 1, you can see that between two tables only the last_name column differs.

columns differ when rows contain different data