Qore SqlUtil Module Reference  1.2
SQL Operations

Table of Contents

Introduction to SQL Operations

The Table class (which is a wrapper for AbstractTable) provides methods for SQL operations.

This class uses knowledge about the internal structure of database tables to create and execute SQL on the database.

SQL strings are constructed programmatically for the following reasons:

  • to present an abstract, database-independent interface to the programmer
  • to allow for database-specific optimizations to be used without requiring programming expertise for the specific database being used
  • to minimize dynamic SQL and therefore use the database server's SQL statement cache more efficiently (if applicable)
  • to prevent SQL injection attacks

Also for the above reasons, values are bound by value in all possible cases rather than inserted into SQL strings directly.

Retrieving Data from the Database

There are many methods in the Table class for retrieving data from the database; here is an overview:

Retrieving a Single Row

From the Primary Key
To retrieve a single row based on a single primary key value, use SqlUtil::Table::find(any):
my *hash $row = $table.find($id);
If no primary key value matches the given argument, then NOTHING is returned. A Qore SQL statement like the following is generated from the above:
my *hash $row = $ds.selectRow("select * from schema.table where id = %v", $id);

To retrieve a single row based on a complex primary key value, or a single primary key value and extra criteria, use SqlUtil::Table::find(hash):
my *hash $row = $table.find(("account_type": $type, "name": $name));
If no row value matches the given argument hash, then NOTHING is returned. A Qore SQL statement like the following is generated from the above:
my *hash $row = $ds.selectRow("select * from schema.table where account_type = %v and name = %v", $type, $name);
At Most One Matching Row From the Table
To retrieve at most one matching row from the table, use SqlUtil::Table::findSingle():
my *hash $row = $table.findSingle(("permission_type": op_between("US", "UX")));
This method can be used to efficiently check the table if at least one record matches the given where criteria; even if more rows match, only a single row is returned. If no rows match, then NOTHING is returned. A Qore SQL statement like the following is generated from the above:
my *hash $row = $ds.selectRow("select * from schema.table where permission_type between %v and %v limit %v", "US", "UX", 1);
Note
handling the "limit" option depends on the underlying database; see Select With Paging for more information

Retrieving Multiple Rows

From the Primary Key
To retrieve multiple rows from a list of single primary key values, use SqlUtil::Table::find(list)
my *list $rows = $table.find($list);
All rows matching the single primary key values in the argument list are returned; if no rows match, NOTHING is returned.
From Simple Where Criteria
To find all rows matching simple where criteria, use SqlUtil::Table::findAll():
my *list $rows = $table.findAll(("id": op_gt(100), "created": op_gt(2013-03-01)));
See Where Clauses for a description of the hash argument; if no rows match, NOTHING is returned.
From Complex Select Criteria
To select a hash keyed by column name of lists (row values) based on complex select criteria, use SqlUtil::Table::select():
my hash $sh = (
"columns": ("id", "name", "q.*"),
"where": ("type": "user"),
"limit": 100,
"offset": 200,
"join": join_inner($queues, "q"),
);
my *hash $h = $table.select($sh);
See Complex Select Criteria for a description of the hash argument; if no rows match, then a hash with column names assigned to empty lists is returned.

To select a list of row hashes based on complex select criteria, use SqlUtil::Table::selectRows():
my *list $rows = $table.selectRows($sh);
If no rows match, then NOTHING is returned.

Acquiring a Row Iterator for Query Results

From Complex Select Criteria
To get an iterator for the row values corresponding to a select statement, use SqlUtil::Table::getRowIterator():
my Table $t($ds, "table_name");
my SQLStatement $i = $table.getRowIterator($sh);
on_exit $table.commit();
map printf("row: %y\n", $1), $i;
See Complex Select Criteria for a description of the hash argument; if no argument is passed to the method, then an iterator for all rows in the table is returned.

Retrieving Multiple Rows in Batches

From Complex Select Criteria
Multiple rows can be retrieved from a single call to an Qore::SQL::SQLStatement object as returned by SqlUtil::Table::getRowIterator():
my Table $t($ds, "table_name");
my SQLStatement $i = $table.getRowIterator($sh);
on_exit $table.commit();
while (True) {
my list $l = $i.fetchRows(1000);
if (!$l)
break;
printf("rows read: %d\n", $l.size());
}
See Complex Select Criteria for a description of the hash argument; if no argument is passed to the method, then an iterator for all rows in the table is returned.

Specifying Columns in Query Output

From Complex Select Criteria
Table methods taking a select option hash argument support specifying output columns including column operators by assigning a column / column operator list to the columns key.
my *list $rows = $table.selectRows(("columns": ("id", "name", "started", cop_as("warnings", "warning_count"), cop_as("errors", "error_count")), "where": ("type": "user")));
See also
select option columns for details about the "columns" option of the select option hash

Complex Select Criteria

Selecting data is performed by passing a select option hash argument to a suitable method that will build and execute a DB-specific SQL command based on this value; the following methods take a select option hash argument:

SQL strings for select statements are constructed programmatically based on a select option hash described below.

Select Option Hash Example:
my hash $soh = (
"columns": ("id", "name", "q.*"),
"where": ("type": "user"),
"limit": 100,
"offset": 200,
"join": join_inner($queues, "q"),
);

The select option hash argument has the following keys; all of which are optional:

  • comment: an optional string with with comment used in select statement
  • hint: an optional string with with hint used in select statement (platform dependent)
  • columns: describes the output columns and any output column operations; if not present, then all columns are returned directly in the output
  • where: (hash) describes how any "where" clause will be built; if not present, then there is no where clause
  • orderby: (list of strings) describes the ordering of the results; if not present (and no other output ordering is required, such as implied by the offset option), then results are returned in the order returned by the database server
  • desc: (bool) specifies descending order for results; if not present or not True and results are ordered, then the results are returned in ascending order
  • limit: (int) specifies the maximum number of results to be returned; if not present, then all results are returned
  • offset: (int) specifies the starting offset of the first record to be returned (starting with 0)
  • join: (hash) specifies any SQL join operations to return results from multiple tables
  • groupby: (list of strings) specifies grouping for aggregate column functions
  • having: (hash) specifies filtering for results with aggregate column functions
  • superquery: (hash) specifies that the rest of the argument belong to a subquery and the hash arguments under this key will select from the subquery
  • forupdate: (bool) adds a "for update" clause to a select query

Select Option "comment"
The comment does not have any impact to data returned from the statement but it can help with statement finding and optimizations.

Comment Example:

$table.selectRows( ("comment" : "foo bar") );

will produce select statement like this:

select /* foo bar */ ...
Warning
Oracle: using different comments in the same SQL can lead to new optimizer statement hard parsing.

Select Option "hint"
In database query operations, various SQL implementations use hints as additions to the SQL standard that instruct the database engine on how to execute the query. For example, a hint may tell the engine to use as little memory as possible (even if the query will run slowly), or to use or not to use an index (even if the query optimizer would decide otherwise).

Hint Example:

$table.selectRows( ("hint" : "full(t1)") );

will produce select statement like this:

select /*+ full(a) */ ...

The string is taken as is and it's up to user to handle correct aliases in join functions etc.

Note
Hints are platform dependent. Curently only Oracle and some versions of PostgreSQL hints are supported in Sqlutil module.
Warning
Use hints only when you know what you are doing.

Select Option "columns"
Columns Example:
my list $columns = ("id", "name", "started", cop_as("warnings", "warning_count"), cop_as("errors", "error_count"));
my *list $rows = $table.selectRows(("columns": $columns, "where": ("type": "user")));
By default, all columns are returned from a query; to limit the columns returned, or to perform column operations on the columns returned, use the "columns" option of the select option hash.

This option takes a list, each element of the list can be one of the following.

A Simple String Giving a Column Name
ex: "name"
my *list $rows = $table.selectRows(("columns": ("id", "name", "started")));

A String in Dot Notation
This format is for use with joins; ex: "q.name"
my *list $rows = $table.selectRows(("columns": ("table.id", "t2.customer_name"), "join": join_inner($table2, "t2", ("id": "altid"))));

A Column Operation Specified by a Column Operator Function
ex: cop_as("column_name", "column_alias")
See column operator function for more information on column operator functions
my *list $rows = $table.selectRows(("columns": ("id", cop_as("warnings", "warning_count"), cop_as("errors", "error_count"))));
For column operator functions taking a column name, either a string name or a name in dot notation is acceptable

The Value "*", Meaning All Columns
ex: "*"
my *list $rows = $table.selectRows(("columns": "*"));
This is the default if no "columns" key is included in the select option hash

An "*" in Dot Notation
ex: "q.*"
my *list $rows = $table.selectRows(("columns": ("table.id", "t2.*"), "join": join_inner($table2, "t2", ("id": "altid"))));

Select Option "where"
Where Example:
my *list $rows = $table.selectRows(("where": ("type": "user"), "limit": 100, "offset": 200));
The hash value assigned to this key describes how the "where" clause in the query is built. Because the "where" clause logic is common to many SQL methods, this topic is covered in a separate section. See Where Clauses for a detailed description of the value of this key.

Select Option "orderby"
Orderby Example:
my *list $rows = $table.selectRows(("where": ("account_type": "CUSTOMER"), "orderby": "created_date"));
This option is a list of the following values:
  • a simple string giving a column name; ex: "name"
  • a string giving a table or table alias and a column name in dot notation (for use with joins); ex: "q.name"
Note
  • By using the offset option the results will be automatically ordered according to the primary key of the table

Select Option "desc"
Desc Example:
my *list $rows = $table.selectRows(("where": ("account_type": "CUSTOMER"), "orderby": "created_date", "desc": True));
If the value of this key is True and results are ordered (either due to the orderby option or due to implicit ordering by the primary key due to the use of the offset option), then results will be sorted in descending order.

Otherwise, ordered results are returned in ascending order by default.

Select Option "limit"
Limit Example:
my *list $rows = $table.selectRows(("where": ("type": "user"), "limit": 100, "offset": 200));
This option will limit the number of rows returned.
Note
  • This option is required if the offset option is non-zero
  • If this option is present and an orderby option is also present, then at least a subset of the orderby columns must correspond to a unique key of the table or an exception is raised

Select Option "offset"
Offset Example:
my *list $rows = $table.selectRows(("where": ("type": "user"), "limit": 100, "offset": 200));
This option specifies the row number offset for the rows returned where the first row is at offset zero.
Note
  • If this option is present, then either an orderby option must be present of which at least a subset of the orderby columns must correspond to a unique key of the table, or, if no orderby option is used, then the table must have a primary key which is used for the ordering instead.
  • Additionally, this option requires the presence of the limit option, or an exception will be thrown.
See also
Select With Paging

Select Option "join"
Join Example:
my *list $rows = $table.selectRows(("columns": ("name", "version", "id", cop_as("st.value", "source"), cop_as("st.value", "offset")),
"join": join_left($function_instance_tags, "st", NOTHING, ("st.tag": "_source"))
+ join_left($function_instance_tags, "lt", NOTHING, ("st.tag": "_offset"))));
To join multiple tables in a single query, use the "join" option. The "join" hash key should be assigned to a join description hash as returned by one of the SQL Join Operator Functions or combined join description hash created by concatenating such values (see an example of this above).
Note
the join columns do not need to be specified in the case that a foreign key in one table exists to the primary key of the other table; in this case this information is assumed for the join automatically
See also
Joining Tables for more examples

Select Option "groupby"
Groupby Example:
my *list $rows = $table.selectRows(("columns": (cop_as(cop_max("service_type"), "type"), cop_count()), "groupby": "service_type"));
The "groupby" option allows for aggregate SQL column operator functions to be used (ex: cop_max(), cop_min()) in select statements. The "groupby" hash key should be assigned to a list of column specifiers or a single column specifier. Column specifies for the "groupby" key are strings giving column names, optionally in dot notation.

Select Option "having"
Having Example:
my *list $rows = $table.selectRows(("columns": (cop_as(cop_max("service_type"), "type"), cop_count()), "groupby": "service_type", "having": ("service_type": (COP_COUNT, op_ge(100)))));
The "having" option allows for query results with aggregate SQL column operator functions to be filtered by user-defined criteria. The "having" hash key should be assigned to a hash where each key is a column specifier (optionally in dot notation) and the values are lists with two elements; the first element must be a column operator code, and the second element is a column operator description normally provided by using a column operator function as in the above example.

Select Option "superquery"
Superquery Example:
my *list $rows = $table.selectRows("columns": ("serviceid", "service_methodid", cop_as(cop_over(cop_max("service_methodid"), "serviceid"), "max_methodid")), "superquery": ("columns": ("serviceid", "service_methodid"), "where": ("max_methodid": op_ceq("service_methodid"))));
The "superquery" option allows for the rest of the query arguments to define a subquery where as the hash arguments assigned to the "superquery" key define the select made from the subquery. In the example above, the "OVER" sql windowing function is used and then rows matching the "max_methodid)" result value are selected.

The above example results in an SQL command equivalent to the following:
my *list $rows = $table.vselectRows("select serviceid,service_methodid from (select serviceid,service_methodid,max(service_methodid) over (partition by serviceid) as max_methodid from schema.service_methods) subquery where max_methodid = service_methodid");
Note
that MySQL does not support SQL windowing functions so the above example would fail on MySQL.

Select Option "forupdate"
For Update Example:
on_success $ds.commit();
on_error $ds.rollback();
my *list $rows = $table.selectRows("columns": ("serviceid", "service_methodid"), "forupdate": True);

The "forupdate" option allows for the rows selected to be locked for updating; to release the locks, call commit() or rollback() on the underlying datasource object. The above example results in an SQL commit equivalent to the following:
my *list $rows = $table.vselectRows("select serviceid,service_methodid from schema.service_methods for update");

Select With Paging

There is support for paging query results in the following methods:

Note
the above list also applies to the corresponding SqlUtil::AbstractTable methods

Each of these methods takes a select option hash argument that allows the "limit" and "offset" options to be specified to specify the data window for the results.

If the "offset" options is used, then an "orderby" option is required which must match some unique constraint or unique index on the table to guarantee the order of results, unless the table has a primary key, in which case the primary key will be used by default if no "orderby" option is supplied.

Example:
Select 100 rows starting at row 200 (the table's primary key will be used for the "orderby" option by default):
my *list $rows = $table.selectRows(("where": ("type": "user"), "limit": 100, "offset": 200));
As an illustration of the different SQL that is generated for different database types; for the above query, here is the SQL generated for Oracle:
$ds.vselectRows("select * from (select /*+ first_rows(100) */ a.*, rownum rnum from (select * from schema.table where type = %v order by type) a where rownum <= %v) where rnum > %v", ("user", 300, 200));
And for PostgreSQL:
$ds.vselectRows("select * from public.table where type = %v order by type limit %v offset %v", ("user", 100, 200));

Check For At Least One Matching Row

Use the SqlUtil::Table::findSingle() method to find at least one matching row:

my *hash $h = $table.findSingle(("account_type": "CUSTOMER"));
if ($h)
printf("found 1 customer row: %y\n", $l[0]);

Also it's possible to use the "limit" option to make an efficient check for at least one matching row as in the following example (which is functionally equivalent to the previous example):

my *hash $h = $table.selectRow(("where": ("account_type": "CUSTOMER"), "limit": 1));
if ($h)
printf("found 1 customer row: %y\n", $l[0]);

Inserting Data into the Database

The following methods can be used to insert data into the database:

See also
Upserting or Merging Data for information about upserting or merging data

Inserting Data Explicitly

Example:
$table.insert(("id": $id, "name": $name, "created": now_us()));

Data can be explicitly inserted into the database with immediate values with SqlUtil::Table::insert() and SqlUtil::Table::insertNoCommit() as in the above example.

Inserting Data From a Select Statement

Example:
my int $rows = $table.insertFromSelect(("id", "name", "created"), $source_table, (("columns": ("cid", "fullname", "created"), "where": ("type": "CUSTOMER"))));

Data can be inserted into the database based on the results of a select statement with SqlUtil::Table::insertFromSelect() and SqlUtil::Table::insertFromSelectNoCommit() as in the above example.

The example above would generate a Qore SQL command like the following:

return $ds.vexec("insert into schema.table (id,name,created) select cid,fullname,created from schema.source_table where type = %v", ("CUSTOMER"));

The return value of these methods is the number of rows inserted. See select option hash for more information about how to form the select criteria in these methods.

Inserting Data from an Iterator Source

To insert data from an iterator source (such as an Qore::SQL::SQLStatement object), call SqlUtil::Table::insertFromIterator() or SqlUtil::Table::insertFromIteratorNoCommit() as in the following example:

Example:
# get the rows to be inserted
my list $l = get_table_rows();
# insert the data and commit after every 5000 rows
$table.insertFromIterator($l.iterator(), ("commit_block": 5000));

The iterator given to the SqlUtil::Table::insertFromIterator() or SqlUtil::Table::insertFromIteratorNoCommit() methods can be any iterator whose getValue() method returns a hash.

Note
the insert option "commit_block" can be used to insert a large amount of data in pieces in order to avoid overwhelming the database server's rollback cache

Updating Data

The following methods can be used to update data:

Example:
my int $rows_updated = t.update(("permission_type": uop_append("-migrated", uop_lower())));

The example above generates a Qore SQL command like the following on Oracle and PostgreSQL for example:

return $ds.vexec("update schema.table set permission_type = lower(permission_type) || '-migrated');

And the following on MySQL:

return $ds.vexec("update schema.table set permission_type = concat(lower(permission_type), '-migrated'));

Deleting Data

The following methods can be used to dekete data:

Example:
my int $dcnt = $table.del(("record_type": "OLD-CUSTOMER"));

The above example would generate a Qore SQL command like the following:

return $ds.vexec("delete from schema.table where record_type = %v", ("OLD-CUSTOMER"));

The SqlUtil::Table::del() and SqlUtil::Table::delNoCommit() methods can be used to delete data from the database.

See Where Clauses for information about specifying the criteria for the rows to be deleted.

Joining Tables

Joining tables is made by providing a join specification to the join select option in a select option hash as in the following example:

my *list $rows = $table.selectRows(("columns": ("table.id", "t2.customer_name"), "join": join_inner($table2, "t2", ("id": "altid"))));

In the above example, table is joined with table2 on table.id = table2.altid.

Joins on multiple tables are performed by combining the results of join functions with the + operator as follows:

my *list $rows = $table.selectRows(("join": join_inner($table2, "t2", ("id": "altid")) + join_inner($table3, "t3")));

In the above example, table is joined with table2 on table.id = table2.altid and with table3 on an automatically detected primary key to foreign key relationship between the two tables.

Joins are by default made with the primary table; to join with another join table, then give the alias for the table as the first argument to the join function as in the following example:

my *list $rows = $table.selectRows(("join": join_inner($table2, "t2", ("id": "altid")) + join_inner("t2", $table3, "t3")));

In the above example, table is joined with table2 on table.id = table2.altid and table2 (aliased as t2) is joined with table3 (aliased as t3) on an automatically detected primary key to foreign key relationship between the two tables.

See also
join select option

Where Clauses

Several methods accept a hash of conditions to build a "where" clause to restrict the rows that are operated on or returned; for example:

Note
the above list also applies to the corresponding SqlUtil::AbstractTable methods

The where clause or condition hash is made of keys signifying the column names, and either a direct value meaning that the column value has to match exactly, or SQL operators can be given by using the appropriate operator function as the key value. Each member of the where hash translates to an expression that is combined with "AND" in the SQL query; to combine expressions with "OR", then use a list of select option hashes, which will combine each select option hash with "OR" as in this example.

The where condition hash has the following format:

  • each key gives a column name or a table/alias with column name in dot notation
  • the values are either direct values, meaning that the equality operator ("=") is used, or a SQL operator function for operators in the where clause
Note
To reference a column more than once in a where clause, prefix the column specification with a unique number and a colon as in the following example:
my hash $w = ("0:created": op_ge($mindate), "1:created": op_lt($maxdate));

See SQL Operator Functions for a list of operator functions.

Where Hash Example:
my hash $w = (
"name": "Smith",
"account_type": op_like("%CUSTOMER%"),
"id": op_ge(500),
);

The preceding example results in a where clause equivalent to: "name = 'Smith' and type like '%CUSTOMER%' and id >= 500", except that bind by value is used, so, if used in a context like the following:
my Table $t($ds, "table");
my *hash $qh = $t.select(("where": $w));

the complete query would look instead as follows:
$ds.vselect("select * from table where name = %v and account_type like %v and id >= %v", ("Smith", "%CUSTOMER%", 500));

Where List Example:
my hash $w1 = (
"name": "Smith",
"account_type": op_like("%CUSTOMER%"),
"id": op_ge(500),
);
my hash $w2 = (
"name": "Jones",
"account_type": op_like("%VENDOR%"),
"id": op_ge(2500),
);
my Table $t($ds, "table");
my *hash $qh = $t.select(("where": ($w1, $w2)));

the complete query would look instead as follows:
$ds.vselect("select * from table where (name = %v and account_type like %v and id >= %v) or (name = %v and account_type like %v and id >= %v)", ("Smith", "%CUSTOMER%", 500, "Jones", "%VENDOR%", 2500));
Code Examples:
Find a single row in the table where the "permission_type" column is a value between "US" and "UX":
my *hash $row = $table.findSingle(("permission_type": op_between("US", "UX")));
resulting in an internal SQL command that looks as follows (depending on the database):
my *hash $row = $ds.vselectRow("select * from table where permission_type between %v and %v limit %v", ("US", "UX", 1));

Delete all rows in the table where the "name" column is like "%Smith%":
my int $row_count = $table.del(("name": op_like("%Smith%")));
resulting in an internal SQL command that looks as follows:
$ds.vexec("delete from table where name like %v", ("%Smith%"));

Find all rows where "id" is greater than 100 and "created" is after 2013-03-01:
my *list $rows = $table.findAll(("id": op_gt(100), "created": op_gt(2013-03-01)));
resulting in an internal SQL command that looks as follows:
$ds.vexec("select * from table where id > %v and created > %v", (100, 2013-03-01));

Upserting or Merging Data

This module offers a high-level api for "upserting" or merging data from one table into another table through the following methods:

Upsert a Single Row

Example:
$table.upsert(("id": $id, "name": $name, "account_type": $account_type));

To upsert or merge a single row in the database, call SqlUtil::Table::upsert() or SqlUtil::Table::upsertNoCommit() with the single row to be upserted or merged as a hash as in the preceding example.

Upserting Many Rows Using An Upsert Closure

To upsert or merge many rows by using an upsert closure, call SqlUtil::Table::getUpsertClosure() or SqlUtil::Table::getUpsertClosureWithValidation() and provide an example row as an argument to acquire a closure that will be executed on the rest of the rows as in the following example.

Simple Example:
# get the rows to be inserted
my list $l = get_table_rows();
if ($l) {
my code $upsert = $table.getUpsertClosure($l[0]);
on_success $ds.commit();
on_error $ds.rollback();
# loop through the reference data rows
map $upsert($1), $l;
}
Complex Example With Callbacks:
# set the upsert strategy depending on the use case
my int $upsert_strategy = $verbose ? AbstractTable::UpsertSelectFirst : AbstractTable::UpsertAuto;
# hash summarizing changes
my hash $sh;
# get the rows to be inserted
my list $l = get_table_rows();
if ($l) {
# get the upsert closure to use based on the first row to be inserted
my code $upsert = $table.getUpsertClosure($l[0], $upsert_strategy);
on_success $ds.commit();
on_error $ds.rollback();
# loop through the reference data rows
foreach my hash $h in ($l) {
my int $code = $upsert($h);
if ($code == AbstractTable::UR_Unchanged)
continue;
my string $change = AbstractTable::UpsertResultMap{$code};
++$sh{$change};
if (!$verbose) {
printf(".");
flush();
}
else if ($verbose > 1)
printf("*** reference data %s: %y: %s\n", $table.getName(), $h, $change);
}
# show table summary
if ($sh)
printf("*** reference data %s: %s\n", $table.getName(), (foldl $1 + ", " + $2, (map sprintf("%s: %d", $1.key, $1.value), $sh.pairIterator())));
else
printf("*** reference data %s: OK\n", $table.getName());
}

Upserting Many Rows from an Iterator Source

To upsert or merge many rows from an iterator source (such as an Qore::SQL::SQLStatement object), call SqlUtil::Table::upsertFromIterator() or SqlUtil::Table::upsertFromIteratorNoCommit() as in the following example:

Simple Example:
# get the rows to be inserted
my list $l = get_table_rows();
$table.upsertFromIterator($l.iterator());
Complex Example With Callbacks:
# set the upsert strategy depending on the use case
my int $upsert_strategy = $verbose ? AbstractTable::UpsertSelectFirst : AbstractTable::UpsertAuto;
# get the rows to be inserted
my list $l = get_table_rows();
my code $callback = sub (string $table_name, hash $row, int $result) {
if ($result == AbstractTable::UR_Unchanged)
return;
my string $change = AbstractTable::UpsertResultMap{$result};
if ($verbose)
printf("*** reference data %s: %y: %s\n", $table_name, $row, $change);
};
my hash $sh = $table.upsertFromIterator($l.iterator(), $upsert_strategy, False, ("info_callback": $callback, "commit_block": 5000));
if ($sh)
printf("*** reference data %s: %s\n", $table.getName(), (foldl $1 + ", " + $2, (map sprintf("%s: %d", $1.key, $1.value), $sh.pairIterator())));
else
printf("*** reference data %s: OK\n", $table.getName());

The iterator given to the SqlUtil::Table::upsertFromIterator() or SqlUtil::Table::upsertFromIteratorNoCommit() methods can be any iterator whose getValue() method returns a hash.

Note
the upsert option "commit_block" can be used to insert a large amount of data in pieces in order to avoid overwhelming the database server's rollback cache

Upserting Many Rows from a Select Statement

To upsert or merge many rows from a select statement, use SqlUtil::Table::upsertFromSelect() or SqlUtil::Table::upsertFromSelectNoCommit() as in the following example:

Simple Example:
$table.upsertFromSelect($table2, ("where": ("account_type": "CUSTOMER")));
Complex Example With Callbacks:
# set the upsert strategy depending on the use case
my int $upsert_strategy = $verbose ? AbstractTable::UpsertSelectFirst : AbstractTable::UpsertAuto;
my code $callback = sub (string $table_name, hash $row, int $result) {
if ($result == AbstractTable::UR_Unchanged)
return;
my string $change = AbstractTable::UpsertResultMap{$result};
if ($verbose)
printf("*** reference data %s: %y: %s\n", $table_name, $row, $change);
};
my hash $sh = $table.upsertFromSelect($table2, ("where": ("account_type": "CUSTOMER")), $upsert_strategy, False, ("info_callback": $callback, "commit_block": 5000));
if ($sh)
printf("*** reference data %s: %s\n", $table.getName(), (foldl $1 + ", " + $2, (map sprintf("%s: %d", $1.key, $1.value), $sh.pairIterator())));
else
printf("*** reference data %s: OK\n", $table.getName());

The source table does not have to be in the same database or even of the same database type (ie you can upsert to and from any database type supported by SqlUtil).

Note
the upsert option "commit_block" can be used to insert a large amount of data in pieces in order to avoid overwhelming the database server's rollback cache

Upserting Many Rows and Deleting Unwanted Rows

Call any of the batch upsert methods with upsert option delete_others set to True to perform a batch upsert / merge operation on a table, and then scan the table and delete any unwanted rows. If there are no rows to be deleted, these calls have very similar performance to the batch upsert method calls without any deletions, however, if there are rows to be deleted, then the entire source table must be iterated to compare each row to valid data to delete the rows that do not belong. Therefore for large tables this can be an expensive operation.

The only difference in the following examples and the preceding ones is that upsert option delete_others is True in these examples.

Simple Example:
# get the rows to be inserted
my list $l = get_table_rows();
$table.upsertFromSelect($table2, ("where": ("account_type": "CUSTOMER")), AbstractTable::UpsertAuto, ("delete_others": True, "commit_block": 5000));
Complex Example With Callbacks:
# set the upsert strategy depending on the use case
my int $upsert_strategy = $verbose ? AbstractTable::UpsertSelectFirst : AbstractTable::UpsertAuto;
# get the rows to be inserted
my list $l = get_table_rows();
my code $callback = sub (string $table_name, hash $row, int $result) {
if ($result == AbstractTable::UR_Unchanged)
return;
my string $change = AbstractTable::UpsertResultMap{$result};
if ($verbose)
printf("*** reference data %s: %y: %s\n", $table_name, $row, $change);
};
my hash $sh = $table.upsertFromSelect($table2, ("where": ("account_type": "CUSTOMER")), $upsert_strategy, ("delete_others": True, "info_callback": $callback, "commit_block": 5000));
if ($sh)
printf("*** reference data %s: %s\n", $table.getName(), (foldl $1 + ", " + $2, (map sprintf("%s: %d", $1.key, $1.value), $sh.pairIterator())));
else
printf("*** reference data %s: OK\n", $table.getName());
Note
the upsert option "commit_block" can be used to insert a large amount of data in pieces in order to avoid overwhelming the database server's rollback cache

Upsert Strategies

The approach used is based on one of the following strategies (see Upsert Strategy Codes):

Note
AbstractTable::UpsertSelectFirst is the only upsert strategy that can return SqlUtil::AbstractTable::UR_Updated; the AbstractTable::UpsertSelectFirst strategy should be used when verbose reporting is required, particularly if it's necessary to report the actual number of changed rows.