Qore TableMapper Module Reference  1.3
TableMapper Module

Table of Contents

TableMapper Module Introduction

Classes provided by this module:

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 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:

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:

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

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

TableMapper v1.1.4

TableMapper v1.1.3

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

TableMapper v1.1.2

TableMapper v1.1.1

TableMapper v1.1

TableMapper v1.0

  • Initial release.