Introduction to SQL Operations

The AbstractTable class provides methods for SQL operations. Instances of this class are returned from most of the API calls working with tables. You need to use a helper class Table if you need to create table object in your own.

AbstractTable 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 AbstractTable class for retrieving data from the database; here is an overview:

SqlUtil::AbstractTable::find(auto): finds a row corresponding to the given single value of the primary key; if not present NOTHING is returned
SqlUtil::AbstractTable::find(list<auto>): finds all rows corresponding to the given list of values of the primary key; if none are present NOTHING is returned
SqlUtil::AbstractTable::find(hash<auto>): find a row corresponding to the given hash of column name/value pairs, which must at least reference all columns of the table's primary key; if no row matches, NOTHING is returned
SqlUtil::AbstractTable::findSingle(): finds the first (of possibly many) rows that match the where clause hash argument or returns NOTHING if no row matches
SqlUtil::AbstractTable::findAll(): returns all rows that match the where clause hash argument or returns NOTHING if no row matches
SqlUtil::AbstractTable::getStatement(): returns an AbstractSQLStatement object iterating rows that match the select option hash argument
SqlUtil::AbstractTable::getSelectSql(): returns an SQL string and bind arguments for the select option hash argument
SqlUtil::AbstractTable::select(): returns a hash keyed by column name of lists (row values) corresponding to the select option hash argument
SqlUtil::AbstractTable::selectRow(): return a hash repersenting a single row corresponding to the select option hash argument; if the query returns more than one row an exception will be raised
SqlUtil::AbstractTable::selectRows(): returns lists of row values corresponding to the select option hash argument

Retrieving a Single Row

From the Primary Key: To retrieve a single row based on a single primary key value, use SqlUtil::AbstractTable::find(auto):
*hash<auto> 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:
*hash<auto> 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::AbstractTable::find(hash):
*hash<auto> 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:
*hash<auto> 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::AbstractTable::findSingle():
*hash<auto> 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:
*hash<auto> 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::AbstractTable::find(list)
*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::AbstractTable::findAll():
*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::AbstractTable::select():
hash<auto> sh = (

"columns": ("id", "name", "q.*"),

"where": ("type": "user"),

"limit": 100,

"offset": 200,

"join": join_inner(queues, "q"),

);

*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::AbstractTable::selectRows():
*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::AbstractTable::getStatement():
Table t(ds, "table_name");

AbstractSQLStatement i = table.getStatement(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::AbstractSQLStatement object as returned by SqlUtil::AbstractTable::getStatement():
Table t(ds, "table_name");

AbstractSQLStatement i = table.getStatement(sh);

on_exit table.commit();

while (True) {

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: AbstractTable 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.
*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<auto> described below.

Select Option Hash Example:: 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:

alias: an optional string providing a table alias for the main table
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 "alias"

This option provides a table alias for the main table when join options are also used.

Alias Example:

hash<auto> sh = (
    "columns": ("t1.*", "t2.customer_name"),
    "join": join_inner(table2, "t2", ("id": "altid"))),
    "alias": "t1",
);
*list rows = table.selectRows(sh);

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

list columns = (
    "id", "name", "started",
    cop_as("warnings", "warning_count"),
    cop_as("errors", "error_count"),
);
*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"

*list rows = table.selectRows(("columns": ("id", "name", "started")));

A String in Dot Notation
This format is for use with joins; ex: "q.name"

hash<auto> sh = (
    "columns": ("t1.id", "t2.customer_name"),
    "join": join_inner(table2, "t2", ("id": "altid"))),
    "alias": "t1",
);
*list rows = table.selectRows(sh);

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

list columns = (
    "id",
    cop_as("warnings", "warning_count"),
    cop_as("errors", "error_count"),
);
*list rows = table.selectRows(("columns": columns));

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

*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.*"

hash<auto> sh = (
    "columns": ("table.id", "t2.*"),
    "join": join_inner(table2, "t2", ("id": "altid")),
);
*list rows = table.selectRows(sh);

Select Option "where"

Where Example:

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

hash<auto> sh = (
    "where": (
        "account_type": "CUSTOMER",
    ),
    "orderby": "created_date",
);
*list rows = table.selectRows(sh);

This option is a list of the following values:

a simple string giving a column name; ex: "name"
a simple string with a column name preceded by a "-" sign; ex: "-name", meaning that that column should be sorted in descending order
a string giving a table or table alias and a column name in dot notation (for use with joins); ex: "q.name"
a positive integer giving the column number for the ordering
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:

hash<auto> sh = (
    "where": (
        "account_type": "CUSTOMER",
    ),
    "orderby": "created_date",
    "desc": True,
);
*list rows = table.selectRows(sh);

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.

Note: per-column descending options can be given by prepending a "-" character to the column name in the orderby option list

Select Option "limit"

Limit Example:

hash<auto> sh = (
    "where": ("type": "user"),
    "limit": 100,
    "offset": 200
);
*list rows = table.selectRows(sh);

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:

hash<auto> sh = (
    "where": ("type": "user"),
    "limit": 100,
    "offset": 200
);
*list rows = table.selectRows(sh);

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:

hash<auto> sh = (
    "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")),
);
*list rows = table.selectRows(sh);

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:

hash<auto> sh = (
    "columns": (
        cop_as(cop_max("service_type"), "type"),
        cop_count(),
    ),
    "groupby": "service_type",
);
*list rows = table.selectRows(sh);

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 specifiers for the "groupby" key are strings giving column names, optionally in dot notation or positive column numbers.

Select Option "having"

Having Example:

hash<auto> sh = (
    "columns": (
        cop_as(cop_max("service_type"), "type"),
        cop_count(),
    ),
    "groupby": "service_type",
    "having": (
        "service_type": (COP_COUNT, op_ge(100)),
    ),
);
*list rows = table.selectRows(sh);

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:

hash<auto> sh = (
    "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")),
    ),
);
*list rows = table.selectRows(sh);

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:

*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();
 
hash<auto> sh = (
    "columns": ("serviceid", "service_methodid"),
    "forupdate": True,
)
*list rows = table.selectRows(sh);

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:

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

*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::AbstractTable::findSingle() method to find at least one matching row:

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

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

SqlUtil::AbstractTable::insert(): inserts a single row into a table without committing the transaction
SqlUtil::AbstractTable::insertCommit(): inserts a single row into a table and commits the transaction
SqlUtil::AbstractTable::insertFromSelect(): inserts data in a table based on a select statement created from the select option hash argument and without committing the transaction
SqlUtil::AbstractTable::insertFromSelectCommit(): inserts data in a table based on a select statement created from the select option hash argument and commits the transaction

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::AbstractTable::insert() and SqlUtil::AbstractTable::insertCommit() as in the above example.

Additionally, instead of giving a literal value to be inserted, SQL Insert Operator Functions can be used to insert values based on SQL operations used directly in the insert statement.

Inserting Data From a Select Statement

Example:: 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::AbstractTable::insertFromSelect() and SqlUtil::AbstractTable::insertFromSelectCommit() 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::AbstractTable::insertFromIterator() or SqlUtil::AbstractTable::insertFromIteratorCommit() as in the following example:

Example:: # get the rows to be inserted

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::AbstractTable::insertFromIterator() or SqlUtil::AbstractTable::insertFromIteratorCommit() 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:

SqlUtil::AbstractTable::update(): updates a single row and does not commit the transaction
SqlUtil::AbstractTable::updateCommit(): updates a single row and commits the transaction

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

SqlUtil::AbstractTable::del(): updates the table based on a where clause and does not commit the transaction
SqlUtil::AbstractTable::delCommit(): updates the table based on a where clause and commits the transaction
SqlUtil::AbstractTable::truncate(): truncates the table and does not commit the transaction
SqlUtil::AbstractTable::truncateCommit(): truncates the table and commits the transaction releasing the transaction lock on the underlying datasource object

Example:: 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::AbstractTable::del() and SqlUtil::AbstractTable::delCommit() 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:

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

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

*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", there are two options:

use the SqlUtil::wop_or() function to combine where expressions with the "or" operator
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:
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:: 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:
Table t(ds, "table");

*hash<auto> 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:: hash w1 = (

"name": "Smith",

"account_type": op_like("%CUSTOMER%"),

"id": op_ge(500),

);

hash w2 = (

"name": "Jones",

"account_type": op_like("%VENDOR%"),

"id": op_ge(2500),

);

Table t(ds, "table");

*hash<auto> 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":

*hash<auto> row = table.findSingle(("permission_type": op_between("US", "UX")));

resulting in an internal SQL command that looks as follows (depending on the database):
*hash<auto> 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%":

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:

*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::AbstractTable::upsert() or SqlUtil::AbstractTable::upsertCommit() 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::AbstractTable::getUpsertClosure() or SqlUtil::AbstractTable::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

list l = get_table_rows();

if (l) {

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

int upsert_strategy = verbose ? AbstractTable::UpsertSelectFirst : AbstractTable::UpsertAuto;

# hash summarizing changes

hash<auto> sh;

# get the rows to be inserted

list l = get_table_rows();

if (l) {

# get the upsert closure to use based on the first row to be inserted

code upsert = table.getUpsertClosure(l[0], upsert_strategy);

on_success ds.commit();

on_error ds.rollback();

# loop through the reference data rows

foreach hash<auto> h in (l) {

int code = upsert(h);

if (code == AbstractTable::UR_Unchanged)

continue;

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::AbstractTable::upsertFromIterator() or SqlUtil::AbstractTable::upsertFromIteratorCommit() as in the following example:

Simple Example:: # get the rows to be inserted

list l = get_table_rows();

table.upsertFromIterator(l.iterator());

Complex Example With Callbacks:: # set the upsert strategy depending on the use case

int upsert_strategy = verbose ? AbstractTable::UpsertSelectFirst : AbstractTable::UpsertAuto;

# get the rows to be inserted

list l = get_table_rows();

code callback = sub (string table_name, hash<auto> row, int result) {

if (result == AbstractTable::UR_Unchanged)

return;

string change = AbstractTable::UpsertResultMap{result};

if (verbose)

printf("*** reference data %s: %y: %s\n", table_name, row, change);

};

hash<auto> 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::AbstractTable::upsertFromIterator() or SqlUtil::AbstractTable::upsertFromIteratorCommit() 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::AbstractTable::upsertFromSelect() or SqlUtil::AbstractTable::upsertFromSelectCommit() 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

int upsert_strategy = verbose ? AbstractTable::UpsertSelectFirst : AbstractTable::UpsertAuto;

code callback = sub (string table_name, hash<auto> row, int result) {

if (result == AbstractTable::UR_Unchanged)

return;

string change = AbstractTable::UpsertResultMap{result};

if (verbose)

printf("*** reference data %s: %y: %s\n", table_name, row, change);

};

hash<auto> 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 any that upsert option delete_others is True in these examples. any any

Simple Example: any: # get the rows to be inserted

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

int upsert_strategy = verbose ? AbstractTable::UpsertSelectFirst : AbstractTable::UpsertAuto;

# get the rows to be inserted

list l = get_table_rows();

code callback = sub (string table_name, hash<auto> row, int result) {

if (result == AbstractTable::UR_Unchanged)

return;

string change = AbstractTable::UpsertResultMap{result};

if (verbose)

printf("*** reference data %s: %y: %s\n", table_name, row, change);

};

hash<auto> 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):

AbstractTable::UpsertAuto: if the target table is empty, then SqlUtil::AbstractTable::UpsertInsertFirst is used, otherwise SqlUtil::AbstractTable::UpsertUpdateFirst is used; note that if a driver-specific optimized version of the upsert operation is implemented, this strategy will normally result in the best performance
AbstractTable::UpsertInsertFirst: first an insert will be attempted, if it fails due to a duplicate key, then an update will be made; this strategy should be used if more inserts will be made than updates
AbstractTable::UpsertUpdateFirst: first an update will be attempted, if it fails due to missing data, then an insert is performed; this strategy should be used if more updates will be made then inserts
AbstractTable::UpsertSelectFirst: first a select is made on the unique key, if the data to be updated is equal, nothing is done and upsert result SqlUtil::AbstractTable::UR_Unchanged is returned
AbstractTable::UpsertInsertOnly: insert if the row doesn't exist, otherwise do nothing and upsert result SqlUtil::AbstractTable::UR_Unchanged is returned
AbstractTable::UpsertUpdateOnly: update if the row exists, otherwise do nothing and upsert result SqlUtil::AbstractTable::UR_Unchanged is returned

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.

Table of Contents