Database
===============

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

.. figure:: img/database.png
    
    Database tab

Creating a connection
------------------------

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

.. figure:: img/browser.png

    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``. 

.. figure:: img/ssh.png

    SSH tunnel

Certificate
~~~~~~~~~~~~~~~~

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

.. figure:: img/certificate.png

    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 :ref:`db_extended_connection`.

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`` :math:`\to` ``Drivers`` :math:`\to` ``Add Driver button``. 
   In the opened window fill in all fields and click ``Save`` button.

    .. figure:: img/add_driver.png

        Adding InterBase driver

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

    .. figure:: img/ib_con.png

        Connect to InterBase database

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

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 :ref:`db_connection`.

.. figure:: img/create_db.png

    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.

.. figure:: img/execute_from_file.png

    Execute SQL script from file

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`` :math:`\to` ``Import connections`` :math:`\to` ``Import from DB`` and select the required connection in the opened window.

.. figure:: img/import_connection.png

    Import connection from database

Importing a connection from a file is also available:

.. figure:: img/import_connection_file.png

    Import connection from file

Export connection
-----------------------

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

.. figure:: img/export_connection.png

    Export connection

Conversion of InterBase database
------------------------------------

To convert the InterBase database to RedDatabase, select the menu item ``Database`` :math:`\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:

    .. figure:: img/ib_convert.png

        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:

    .. figure:: img/create_convert_db.png

        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").

Backup and restore
----------------------------------------

To perform a backup or restore, select the menu item  ``Database`` :math:`\to` ``Backup and Restore``.

.. figure:: img/backup.png

    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``.

.. figure:: img/restore.png

    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``.
