Libzdb

#include <zdb.h>
                            

#include <zdbpp.h>
using namespace zdb;
                            

Connection URL:

The URL given to a Connection Pool at creation time specify a database connection on the standard URL format. The format of the connection URL is defined as:

database://[user:password@][host][:port]/database[?name=value][&name=value]...

The property names user and password are always recognized and specify how to login to the database. Other properties depends on the database server in question. User name and password can alternatively be specified in the auth-part of the URL. If port number is omitted, the default port number for the database server is used. Reserved characters used in the Connection URL must be URL encoded.

MySQL:

Here is an example on how to connect to a MySQL database server:

mysql://localhost:3306/test?user=root&password=swordfish

In this case the username, root and password, swordfish are specified as properties to the URL. An alternative is to use the auth-part of the URL to specify authentication information:

mysql://root:swordfish@localhost:3306/test

See mysql options for all properties that can be set for a mysql connection URL.

SQLite:

For a SQLite database the connection URL should simply specify a database file, since a SQLite database is just a file in the filesystem. SQLite uses pragma commands for performance tuning and other special purpose database commands. Pragma syntax on the form, name=value can be added as properties to the URL and will be set when the Connection is created. In addition to pragmas, the following properties are supported:

• shared-cache=true - Make SQLite use shared-cache if value is true (default is false). Using shared cache can significantly reduce database lock errors and improve concurrency in multi-threaded scenarios. It is also recommended to build libsqlite and libzdb with unlock notify.
• serialized=true - Make SQLite switch to serialized mode if value is true, otherwise multi-thread mode is used (the default).

An URL for connecting to a SQLite database might look like:

sqlite:///var/sqlite/test.db?synchronous=normal&foreign_keys=on&journal_mode=wal&temp_store=memory

PostgreSQL:

The URL for connecting to a PostgreSQL database server might look like:

postgresql://localhost:5432/test?user=root&password=swordfish

As with the MySQL URL, the username and password are specified as properties to the URL. Likewise, the auth-part of the URL can be used instead to specify the username and the password:

postgresql://root:swordfish@localhost/test?use-ssl=true

In this example we have also omitted the port number to the server, in which case the default port number, 5432, for PostgreSQL is used. In addition we have added an extra parameter to the URL, so connection to the server is done over a secure SSL connection.

See postgresql options for all properties that can be set for a postgresql connection URL.

Oracle:

The URL for connecting to an Oracle database server might look like:

oracle://localhost:1521/servicename?user=scott&password=tiger

Instead of a database name, Oracle uses a service name. The information in the url above is typically specified in a tnsnames.ora configuration file, pointed to by the environment variable TNS_ADMIN. In the example below, instead of host, port and service name, we use a tnsname as defined in tnsnames.ora. We also use the auth-part of the URL to specify the username and the password. Finally, we specify that we want to connect to Oracle with the SYSDBA role.

oracle://sys:secret@/tnsname?sysdba=true

See oracle options for all properties that can be set for an oracle connection URL.

try {
    // Start a ConnectionPool to the Apples Warehouse Database 
    ConnectionPool apple_store("mysql://root:fruit@192.168.1.101:3306/apples");
    apple_store.start();

    // Start a ConnectionPool to the Oranges Warehouse Database                     
    ConnectionPool orange_store("postgresql://root:ninja@192.168.5.210/oranges");
    orange_store.start();

    // Get a Connection to the Orange store
    Connection orange_connection = orange_store.getConnection();

    // Select Apples we want to copy to Oranges
    Connection apple_connection = apple_store.getConnection();
    ResultSet apples = apple_connection.executeQuery(
        "SELECT name, color, weight FROM apples"
    );

    // Create a Prepared statement for storing Oranges
    PreparedStatement orange = orange_connection.prepareStatement(
        "INSERT INTO oranges (name, color, weight) VALUES (?, ?, ?)"
    );

    // Copy all Apples to Oranges under a transaction
    try {
        orange_connection.beginTransaction();
        while (apples.next()) {
            orange.bindValues(
                apples.getString("name").value_or(""),
                apples.getString("color").value_or(""),
                apples.getDouble("weight")
            );
            orange.execute();
        }
        orange_connection.commit();
        std::cout << "Transfer successful" << std::endl;
    } catch (const sql_exception& e) {
        orange_connection.rollback();
        std::cerr << "Transfer failed: " << e.what() << std::endl;
    }

    // Connections are automatically returned to their respective pools when 
    // they go out of scope
} catch (const sql_exception& e) {
    std::cerr << "Database error: " << e.what() << std::endl;
}