TableMapper Module Introduction

Classes provided by this module:

AbstractSqlStatementOutboundMapper: the base class for outbound mappers based on a database source
InboundTableMapper: extends the Mapper::Mapper class; assumes an SQL table target
InboundIdentityTableMapper: extends the InboundTableMapper class for the case when the source and target tables have the exact same structure
InboundTableMapperIterator: provides a specialization of the Mapper::AbstractMapperIterator class; assumes an SQL table target; automatically inserts mapped data into the target table based on the mapper provided
SqlStatementOutboundMapper: provides a SQL statement source based mapper
SqlStatementMapperIterator: provides a helper SQL statement source based iterator with mapping capability

Inserting Sequence Values with the InboundTableMapper Class

InboundTableMapper objects can be used to insert sequence values in the target table, and the values inserted are returned in the output value of TableMapper::InboundTableMapper::insertRow() "InboundTableMapper::insertRow()" and also present in the argument to any rowcode call when using the bulk insert API on supported databases (databases supporting the returning clause in insert statements).

The mapper field options are:

sequence: inserts the value of the given sequence into the target column and increments the sequence
sequence_currval: inserts the current value the given sequence into the target column; does not increment the sequence

Note: "autoincrement" columns cannot be used if the inserted value should be returned from an insert operation; an explicit sequence needs to be used so that the inserted value can be returned and used for further processing.

See also: See the following section for an example and TableMapper Specification Format for more information

TableMapper Examples

The following is an example map for an InboundTableMapper hash with comments:

const DataMap = (
    # output column: "rec_id" populated from the given sequence
    "rec_id": ("sequence": "seq_rec_id"),
    # output column: "id" mapper from the "Id" element of any "^attributes^" hash in the input record
    "id": "^attributes^.Id",
    # output column: "name": maps from an input field with the same name (no translations are made)
    "name": True,
    # output column: "explicit_count": maps from the input "Count" field
    "explicit_count": "Count",
    # output column: "implicit_count": runs the given code on the input record and retuns the result, the code returns the number of "Products" sub-records
    "implicit_count": int sub (any ignored, hash rec) { return rec.Products.size(); },
    # output column: "order_date": converts the "OrderDate" string input field to a date in the specified format
    "order_date": ("name": "OrderDate", "date_format": "DD.MM.YYYY HH:mm:SS.us"),
    # output column: order_type: given as a constant value
    "order_type": ("constant": "NEW"),
);

If this map is applied in the following way:

Table table(ds, "order_table");
InboundTableMapper map1(table, DataMap);
{
    on_success map1.commit();
    on_error map1.rollback();
    # apply the map and insert the mapped data for each input record
    map map1.insertRow($1), input;
}
printf("%d record%s inserted\n", map.getCount(), map.getCount() == 1 ? "" : "s");

This will insert all the mapped input data into data into the ORDER_TABLE table and then print out the number of rows inserted.

The following is an example for TableMapper::SqlStatementOutboundMapper. It selects data from the ORDER_DATE table and it transforms rows according to the mappings supplied in the constructor.

# SqlUtil Table object
Table table(ds, "order_table");
# mapping definition
const DataMap = (
    "id": True,
    "foo": "name",
    "bar": ("code": string sub (any ignored, hash rec) { return format_date("YYYYMMDD", rec."order_table"); }),
);
# SqlUtil select hash
hash sh = (
    "columns": ("id", "name", "order_date"),
    "where": ("id": op_gt(1000)),
);
 
SqlStatementOutboundMapper m(table, sh, DataMap);
on_exit m.commit();
 
while (*hash h = m.getData()) {
    do_something_with_data(h);
}

InboundTableMapper Bulk Insert API

InboundTableMapper Bulk Insert API Introduction

The bulk insert API allows for multiple rows to be mapped and inserted in a single server round trip for high-performance applications. This requires bulk DML support in the underlying DBI driver and also in SqlUtil (to determine if bulk DML support is available, call SqlUtil::AbstractTable::hasArrayBind on the SqlUtil::AbstractTable object).

The bulk insert API consists of the following methods:

TableMapper::InboundTableMapper::queueData()
TableMapper::InboundTableMapper::flush
TableMapper::InboundTableMapper::discard
TableMapper::InboundTableMapper::setRowCode

The behavior of the bulk insert API can be modified or tuned with the following options:

"insert_block": the number of rows inserted in a single block (default: 1000)

Note: The bulk insert API is only used when "unstable_input" is False and bulk DML is supported in the SqlUtil::AbstractTable object

InboundTableMapper Bulk Insert API Usage

To queue data for bulk insert, call TableMapper::InboundTableMapper::queueData() instead of TableMapper::InboundTableMapper::insertRow(). To perform per-row actions, the TableMapper::InboundTableMapper::setRowCode method should be called with a closure that accepts a hash representing a single row; whenever data is flushed to the database, this closure will be called with the row actually inserted (including sequence values used, etc).

Before committing the transaction, ensure that TableMapper::InboundTableMapper::flush is called for each TableMapper::InboundTableMapper object participating in the transaction. This ensures that all data has been flushed to the database before committing the transaction.

If there are any errors, call TableMapper::InboundTableMapper::discard before rolling the transaction back.

Note: If an error occurs flushing data, the count is reset by calling Mapper::resetCount()

InboundTableMapper Bulk Insert API Examples

Consider the following example:

# table1 and table2 must use the same @ref Qore::SQL::Datasource "Datasource" or @ref Qore::SQL::DatasourcePool "DatasourcePool" to participate in the same transaction
TableMapper::InboundTableMapper map1(table1, maph1);
TableMapper::InboundTableMapper map2(table2, maph2);
 
# the transaction only needs to be committed once
on_success table1.commit();
on_error table1.rollback();
 
# ensure that data for each mapper is inserted and flushed before committing the transaction
{
    on_success map1.flush();
    on_error map1.discard();
 
    map map1.queueData($1), data1.iterator();
}
{
    on_success map2.flush();
    on_error map2.discard();
 
    map map2.queueData($1), data2.iterator();
}

TableMapper Specification Format

The mapper hash is made up of target (ie output) column names as the key values assigned to field specifications as specified in Mapper Specification Format, plus the following hash options:

"sequence": a name of a sequence to use to populate the column; the output buffers for this option are bound as type number, so the output type depends on the database driver's number option setting (for example, with "optimal-numbers", the values here are generally returned as strings; cannot be used with the upsert InboundTableMapper option)
"sequence_currval": a name of a sequence to use to return the current value of the sequence; this is useful when assigning the same sequence value to multiple columns; the output buffers for this option are bound as type number, so the output type depends on the database driver's number option setting (for example, with "optimal-numbers", the values here are generally returned as strings; cannot be used with the upsert InboundTableMapper option)

In both cases, the actual value inserted in the table is available in the following APIs:

TableMapper::InboundTableMapper::insertRow()
TableMapper::InboundTableMapper::queueData()
TableMapper::InboundTableMapper::flush

Additionally, the value is provided to any row code set with TableMapper::InboundTableMapper::setRowCode; see InboundTableMapper Bulk Insert API for more information.

Release Notes

TableMapper v1.3

updated the module to use the AbstractSQLStatement class instead of the SQLStatement
deprecated AbstractSqlStatementOutboundMapper::getRowIterator() for AbstractSqlStatementOutboundMapper::getStatement()

TableMapper v1.2.2

updated to use the new SQL statement DBI method for efficient execution of queries only for describing result sets with outbound mappers to solve performance problems related to mappers that have statements with large data sets (issue 2773)
fixed RawSqlStatementOutboundMapper to be usable without subclassing (issue 2775)

TableMapper v1.2.1

fixed issues where where description fields of input and output records for automatically-generated options did not reflect column comments and could not be overridden with user input (issue 2520)

TableMapper v1.2

added support for upserts in TableMapper::InboundTableMapper (issue 1067)
added TableMapper::InboundTableMapper::queueData(list, *hash)
fixed a bug with the TableMapper::SqlStatementOutboundMapper::iterator method; corrected the iterator object return value which was causing AbstractMapperIterator::mapBulk() to fail (issue 979)
fixed a bug with TableMapper::SqlStatementOutboundMapper; it would throw an error if the required "table" or "sh" options were used and only worked with subclasses that declared these options (issue 981)
updated for complex types

TableMapper v1.1.4

fixed a bug in flush messages in the InboundTableMapper class (issue 1849)

TableMapper v1.1.3

fixed bugs handling mapper fields with no input records in list mode (issue 1736)

TableMapper v1.1.2

performance enhancements for TableMapper::InboundTableMapper::queueData() "InboundTableMapper::queueData()" when called with a hash of lists (issue 1626)

TableMapper v1.1.1

fixed runtime option propagation to SqlStatementMapperIterator from AbstractSqlStatementOutboundMapper::iterator() (issue 1418)
fixed SqlStatementMapperIterator::getCount() (issue 1417)
added the following methods:
- AbstractSqlStatementOutboundMapper::getRowIterator()
- InboundTableMapper::iterator()
- InboundTableMapperIterator::getRuntime()
- InboundTableMapperIterator::replaceRuntime()
- InboundTableMapperIterator::setRuntime()
- SqlStatementMapperIterator::getRuntime()
- SqlStatementMapperIterator::replaceRuntime()
- SqlStatementMapperIterator::setRuntime()

TableMapper v1.1

added table name and datasource description to error messages
added the getDatasource() method to classes
implemented more efficient support for inserts from a sequence for databases supporting the "returning" clause in insert statements; now such inserts are made in a single round trip instead of n + 1 where n is the number of sequences in the insert
implemented an optimized insert approach assuming stable input data
implemented the following new options for TableMapper::InboundTableMapper:
- "unstable_input": to accommodate unstable input data and disable the insert optimization (default: False)
- "insert_block": for DB drivers supporting bulk DML (for use with the TableMapper::InboundTableMapper::queueData(), TableMapper::InboundTableMapper::flush(), and TableMapper::InboundTableMapper::discard() methods), the number of rows inserted at once (default: 1000, only used when "unstable_input" is False) and bulk inserts are supported in the table object
added method for bulk / batch inserts for db drivers supporting bulk DML (ex: Oracle)
updated to Mapper changes: use table description to define output record for the Mapper module
added the AbstractSqlStatementOutboundMapper class
added the InboundIdentityTableMapper class
added the RawSqlStatementOutboundMapper class
added the SqlStatementMapperIterator class
added the SqlStatementOutboundMapper class

TableMapper v1.0

Initial release.

Table of Contents