Part II. SQL database interface
Prev		Next

Part II. SQL database interface

Introduction

The x::sql::dbi namespace implements classes and templates that map SQL database tables as objects, similar in spirit to Perl's DBIx::Class module. x::sql::dbi classes are not used directly. A separate XML schema file defines the tables in the database, their columns, and keys. This schema file gets processed by an XML stylesheet file. The stylesheet generates a header and a class file with C++ bindings, that declare x::sql::dbi subclasses, based on the specification in the XML file. The resulting code gets compiled and linked with LIBCXX SQL Library. The final product is tables and rows in the database that are represented as C++ objects. They get created by instantiating and using these classes; this gets translated to executing SQL SELECT, INSERT, and UPDATE statements.

The generated header and class files implement C++ classes that encapsulate SQL tables as reference-counted objects called “resultset”s. A resultset class gets defined for each table in the schema file. The resultset is basically a container that produces iterators over input sequences. The input sequence contains row objects. Each row object represents one row in the table. For example, with an appropriate stylesheet:

auto payments_rs=payments::create(conn);

auto source_account=payments_rs->join_source_ledger_id()->join_accounts();

auto dest_account=payments_rs->join_dest_ledger_id()->join_accounts();

payments_rs->search(source_account->get_table_alias() + ".code", "=", "Acct1",
                    dest_account->get_table_alias() + ".code", "=", "Acct2");

for (const payments_rs::base::row &row: *payments_rs)
{
    std::cout << row->payment_id.value()
              << " "
              << row->source_ledger_id.value()
              << " "
              << row->dest_ledger_id.value();
}

This executes (an equivalent of) the following SQL, and iterates over the SELECTed rows:

SELECT payments.payment_id, payments.source_ledger_id,
       payments.dest_ledger_id FROM payments AS payments
    INNER JOIN ledger_entries AS ledger_entries
       ON payments.source_ledger_id=ledger_entries.ledger_entry_id
       INNER JOIN accounts AS accounts
           ON ledger_entries.account_id=accounts.account_id
    INNER JOIN ledger_entries AS ledger_entries_2
       ON payments.dest_ledger_id=ledger_entries_2.ledger_entry_id
       INNER JOIN accounts AS accounts_2
           ON ledger_entries_2.account_id=accounts_2.account_id
WHERE (accounts.code = 'Acct1' AND accounts_2.code = 'Acct2')

Other methods in the resultset create new rows (returning new row objects), or update existing rows in the table. Each row object also implements its own update method, that updates the row in its table with the object's new values.

An XML file describes the tables and their joins. The resulting stylesheet generates all classes and methods used in the above example, that implement this query.

These high level classes and methods are designed for ease of use, rather performance, and may not be the best choice for processing a large set of data, use direct SQL and execute_vector() and fetch_vector().