4. Database

Tools for creating a database, connecting to it, and extracting and comparing metadata are under the Database tab.

_images/database.png

Fig. 4.1 Database tab

4.1. Creating a connection

RDBExpert allows multiple database connections to be used simultaneously. The Database Browser displays information about the connection.

_images/browser.png

Fig. 4.2 Database Browser

To create a connection, select the corresponding item in the Database menu or click the New Connection button in toolbar. Fill in the fields in the opened window and click the Connect button. Test button establishes a connection with the specified parameters, but does not save it in the Connection Tree. Save button applies changes to the connection parameters, but does not establish a connection. Close button closes the connection editing window without saving changes to the parameters.

Note

A network connection encryption error may occur when trying to connect. To support encryption you need Java version at least 21 or Java Cryptography Extension (JCE) add-on installed. Otherwise, change the value of the WireCrypt = Disabled parameter in firebird.conf.

Native connection

Native connection is performed via ISC API and uses the fbclient library, which is supplied with RDBExpert.

When connecting with the client library, the “Use new OO API” option becomes available, which allows you to use batches when generating test data.

Embedded server

Embedded server mode allows you to work with a database when the DBMS server is unavailable or not installed on the local computer. To connect, you only need a database file that the user who launched RBDExpert has read and write access to. However, embedded server mode imposes certain restrictions, which are listed below.

Embedded server mode only supports databases created with RED Database 5.

The following tools are not supported in embedded mode: Backup and Restore, Convert Database, Database Statistics, Grant Manager, Table Validator, Trace Manager, User Manager.

Users and jobs are not displayed in embedded mode.

SSH tunnel

You can connect to the database via an SSH tunnel. As with any SSH connection, all traffic between you and the database will be encrypted. To do this, fill in the parameters for the SSH connection.

_images/ssh.png

Fig. 4.3 SSH tunnel

Certificate

For authentication by certificate, you must fill out the following form:

_images/certificate.png

Fig. 4.4 Certificate

Extended connection parameters

Additional connection properties can be set using the Extended tab. On this tab you can see a table with two columns. The left one corresponds to the connection parameter and the right one to its value. Refer to the JDBC driver documentation to find out what additional database connection parameters can be set.

You can also select the transaction isolation level. Different levels of transaction isolation determine the behaviour of the client application, running this transaction, in relation to other concurrent processes, running on any computer on the local network, simultaneously reading or modifying the same database as the current process. For Red Database and Firebird the default is READ_COMMITED. For detailed description of extended connection parameters see section Extended database connection parameters.

Connect to other servers

RDBExpert allows to connect to other servers (not Firebird or RedDatabase). To establish such a connection do the following:

  1. Add driver for working with specified DBMS using menu System \(\to\) Drivers \(\to\) Add Driver button. In the opened window fill in all fields and click Save button.

    _images/add_driver.png

    Fig. 4.5 Adding InterBase driver

  2. Create a connection using menu Database \(\to\) Create Connection. In opened tab select correct JDBC driver and Server, fill in all fields and press Connect button:

    _images/ib_con.png

    Fig. 4.6 Connect to InterBase database

Note

Currently, such connections are supported only by Query Editor and Execute SQL script from file tool.

4.2. Create database

To create a database, select the corresponding item in the Database menu or click the Create Database button in the toolbar. Fill in the fields in the opened window and click the Create button. For a detailed description of the fields, see the section Database connection parameters.

_images/create_db.png

Fig. 4.7 Database creation

Note

When trying to create a database, a network connection encryption error may occur. To support encryption you need Java version at least 21 or JCE add-on installed. Otherwise, change the value of the WireCrypt = Disabled parameter in firebird.conf.

Execute SQL script from file

Select an open connection and specify the path to the file with SQL script and press the Execute SQL script button. Make a commit or rollback transaction by clicking on the corresponding buttons.

_images/execute_from_file.png

Fig. 4.8 Execute SQL script from file

4.3. Import connection

If you already have connections configured in IBExpert, there is an option to import connections. Start RDBExpert and connect to the required user database. Select the menu item Database \(\to\) Import connections \(\to\) Import from DB and select the required connection in the opened window.

_images/import_connection.png

Fig. 4.9 Import connection from database

Importing a connection from a file is also available:

_images/import_connection_file.png

Fig. 4.10 Import connection from file

4.4. Export connection

For exporting a connection to a file, select the menu item Database \(\to\) Export connections and select the required connection in the opened window.

_images/export_connection.png

Fig. 4.11 Export connection

4.5. Conversion of InterBase database

To convert the InterBase database to RedDatabase, select the menu item Database \(\to\) Convert Database or right-click on the active connection to InterBase in the object tree and select Convert to RedDatabase in the menu that opens.

Database conversion is performed in 6 stages:

  1. Select connection - define the database to be converted:

    _images/ib_convert.png

    Fig. 4.12 InterBase Database Conversion

  2. Extract metadata - extract source database metadata into an SQL script, validate metadata:

    • Escaping object names with double quotes when the IDENTIFIER matches keywords/reserved words.

    • Removing the SUSPEND operator from the body of non-selective procedures.

    • Conversion of GROUP BY expressions. Expressions in queries with grouping must be aggregate functions or parts of expressions in the "GROUP BY` clause.

    • Conversion of external functions (UDF) using the ib_udf module into stored functions of RedDatabase.

  3. Create a database - the definition of the RedDatabase database that will be created as a result of the conversion:

    _images/create_convert_db.png

    Fig. 4.13 Creating RedDatabase database

    • Driver - the driver used to create the database.

    • Database - the path where the converted database will be created.

    • Server - the server on which your Database is running.

    • Port - the port where the Database is running.

    • User - the user on whose behalf the database will be created.

    • Password - the user's password.

    • Charset - the encoding of the database, it is recommended to specify the encoding of the source database.

    • Page Size - the page size with which the RedDatabase database will be created.

    • Save As - add a connection with the specified name to the connection tree.

  4. Restore metadata - to restore metadata, it applies the SQL script generated at the 2nd stage to the new database. In this case, triggers and indexes are created deactivated (INACTIVE), and tables without constraints.

  5. Restore table date - adding data from the source database to the converted database.

    Warning

    The ARRAY type fields in the converted database will be filled with NULL values.

  6. Enable constraints - activate triggers and indexes, add table constraints (`PK", `FK", `UK").

4.6. Backup and restore

To perform a backup or restore, select the menu item Database \(\to\) Backup and Restore.

_images/backup.png

Fig. 4.14 Backup options Restore options

Backup options correspond to gbak keys:

  • Ignore checksums - gbak -ignore;

  • Metadata only - gbak -meta_data;

  • No garbage collection - gbak -garbage_collect;

  • Non-transoortable backup - gbak -nt.

_images/restore.png

Fig. 4.15 Restore options

Restore options also correspond to gbak keys:

  • Overwrite database file - gbak-replace_database;

  • Deactivate indexes - gbak -inactive;

  • Metadata only - gbak -meta_data;

  • No shadow - gbak -kill;

  • No validity check - gbak -no_validity;

  • Restore one at time - gbak -one_at_a_time.