5. Database Support

Database Integration Adapter provides out-of-the-box support for MariaDB and PostgreSQL databases. This chapter explains the requirements and process to add support for additional database systems.

5.1. Add Support for Other Databases

The Database Integration Adapter uses Open Database Connectivity (ODBC), which is a database-agnostic API. To add support for a database other than MariaDB and PostgreSQL, you must implement specific SQL syntax handlers. While ODBC provides a universal connection interface, custom handlers ensure compatibility with the varying SQL dialects inherent to different database engines.

To add support for a new database:

1. Implement a custom QueryConverter class.

2. If advanced SQL features are needed, modify the Queries class.

3. Register your custom QueryConverter in the factory and map it from the query_converter connection property.

5.1.1. Creating a custom QueryConverter class

The QueryConverter abstract class defines the interface for database-specific SQL generation. You must implement all pure virtual methods:

Header Location: plugins/adapters/dia/srcCxx/QueryConverter.hpp

5.1.2. Extending the Queries Class (optional)

The Queries class (located in plugins/adapters/dia/srcCxx/Queries.hpp and Queries.cxx) uses your QueryConverter to build SQL statements. In most cases, you won’t need to modify this class.

However, if your database requires significantly different SQL syntax for advanced features, you may need to extend or modify the Queries class methods.

5.1.3. Registering your QueryConverter

Add your custom QueryConverter to the enum in plugins/adapters/dia/srcCxx/QueryConverter.hpp and to the factory in plugins/adapters/dia/srcCxx/QueryConverter.cxx:

enum class QueryConverterFormat {
    mariadb,
    postgresql,
    oracle
};

std::unique_ptr<QueryConverter> QueryConverterFactory::create_query_converter(
        const QueryConverterFormat& query_converter) const
{
    switch (query_converter) {
    case QueryConverterFormat::mariadb:
        return std::unique_ptr<QueryConverter>(new MariaDBQueryConverter());
    case QueryConverterFormat::postgresql:
        return std::unique_ptr<QueryConverter>(new PostgreQueryConverter());
    case QueryConverterFormat::oracle:
        return std::unique_ptr<QueryConverter>(new OracleQueryConverter());
    default:
        throw std::runtime_error("Incorrect database");
    }
}

Then map the new string value from connection properties in plugins/adapters/dia/srcCxx/DiaConnection.cxx (for example, query_converter = oracle).

5.1.4. Configuring Routing Service

Once implemented and registered, use your custom database by setting the query_converter connection property in your Routing Service configuration:

<connection name="my_custom_db_connection" plugin_name="plugin_library::dia_adapter">
    <property>
        <value>
            <element>
                <name>query_converter</name>
                <value>oracle</value>
            </element>
            <!-- other properties -->
        </value>
    </property>
</connection>

5.1.5. Configuring the ODBC Data Source Name

Don’t forget to configure your new database’s ODBC Data Source Name (DSN) in your odbc.ini file. Without this name, the ODBC driver manager will not be able to locate your database connection.

Add an entry for your custom database:

[oracle]
DRIVER       = Oracle ODBC Driver
SERVER       = localhost
DATABASE     = your_database_name
USER         = your_username
PASSWORD     = your_password
PORT         = 1521

The DSN name (e.g., [oracle]) must match the DSN property in your Database Integration Adapter connection configuration:

<connection name="my_custom_db_connection" plugin_name="plugin_library::dia_adapter">
    <property>
        <value>
            <element>
                <name>DSN</name>
                <value>oracle</value>  <!-- Must match odbc.ini section name -->
            </element>
        </value>
    </property>
</connection>

See the odbc.ini file in plugins/adapters/dia/resources/odbc/ for complete configuration examples.

5.2. Common Considerations

Consider these common architectural variations when adding support for a new database:

5.2.1. Data types

• Binary data: MariaDB uses BINARY(n), PostgreSQL uses BYTEA, SQL Server uses VARBINARY

• Strings: Different maximum lengths and VARCHAR behavior

• Integers: Signed vs unsigned, size limits

5.2.2. Identifier quoting

• MariaDB: backticks (`)

• PostgreSQL: double quotes (")

• SQL Server: square brackets ([]) or double quotes

• Oracle: double quotes

5.2.3. Auto-increment columns

• MariaDB: AUTO_INCREMENT

• PostgreSQL: SERIAL or IDENTITY

• SQL Server: IDENTITY(1,1)

• Oracle: GENERATED AS IDENTITY or sequences

5.2.4. UPSERT operations

• MariaDB: REPLACE INTO or INSERT ... ON DUPLICATE KEY UPDATE

• PostgreSQL: INSERT ... ON CONFLICT DO UPDATE

• SQL Server: MERGE statement

• Oracle: MERGE statement

5.2.5. Transaction and locking

Different databases have varying default behaviors for:

• Transaction isolation levels

• Lock timeouts

• Concurrent writes

Ensure your ODBC driver and connection settings are appropriate for your use case.