Implementation Technical How-Tos
How-To: Implement a REST Server Service
Introduction
Implementing a REST service in Qorus is straightforward; the service should have the REST implementation either as library code or implemented directly in the [init method](https://qoretechnologies.com/manual/qorus/current/qorus/implementingservices.html#serviceinit).In the following example, we’ll implement a service that handles the
/rest-example URI path with the following URI paths, methods, and actions:*
PUT /rest-example/caller?action=updateStatus or PUT /rest-example/caller/updateStatus*
POST /rest-example/data*
GET /rest-example/data?action=check;filename=filename or GET /rest-example/data/check?filename=filename> **Note**: Qore and Qorus implement REST as an API; the HTTP message body for REST messages is assumed to be serialized data (and not HTML, although the REST [server implementation](https://www.qoretechnologies.com/manual/qorus/current/qore/modules/RestHandler/html/index.html) does support HTML rendering as a response to HTTP requests. See [REST Data Serialization Support](https://www.qoretechnologies.com/manual/qorus/current/qore/modules/RestHandler/html/index.html#restserialization) for more information.
1. Implement the Root REST Handler Class
The first step will be to implement the root REST handler class, which must be a subclass of [AbstractServiceRestHandler](https://www.qoretechnologies.com/manual/qorus/current/qorus/classOMQ_1_1AbstractServiceRestHandler.html); this will be the main entry point for external REST requests in the service.For example:
“`
# the root REST handler object
class ExampleRestHandler inherits AbstractServiceRestHandler { constructor() : AbstractServiceRestHandler("rest-example") { }
}
“`
The string argument in the [constructor()]
(https://www.qoretechnologies.com/manual/qorus/current/qorus/classOMQ_1_1AbstractServiceRestHandler.html#ae168909c9d873bd097c6dfaabace2616) above gives the root URI path component that this handler will service; see the documentation in the preceding link for more information.
2. Implement URI Path Handler Classes
Implement subclasses of [AbstractRestClass](https://www.qoretechnologies.com/manual/qorus/current/qore/modules/RestHandler/html/class_rest_handler_1_1_abstract_rest_class.html) for each URI path component to be handled.For example:
“`
# class for handling REST data operations
class DataRestClass inherits AbstractRestClass {
string name() {
return "data";
}
}
# class for handling REST caller operations
class CallerRestClass inherits AbstractRestClass {
string name() {
return "caller";
}
}
“`
2.1. Implement Action Method Handlers
Implement action method handlers for each URI path and each HTTP method and action that you want to handle in your [AbstractRestClass](https://www.qoretechnologies.com/manual/qorus/current/qore/modules/RestHandler/html/class_rest_handler_1_1_abstract_rest_class.html) URI path handling classes.Method action handlers are implemented with the following naming convention:
*
method[Action]Where
method is an HTTP method in lower case, and
[Action]See examples in the following table:
| Request | Example Method |
| ———— | ———— |
| GET /path (no action) |
hash get(hash cx, *hash ah) {}|| PUT /path (no action)|
hash put(hash cx, *hash ah) {}|| POST /path (no action) |
hash post(hash cx, *hash ah) {}|| DELETE /path (no action) |
hash del(hash cx, *hash ah) {} || OPTIONS /path (no action)|
hash options(hash cx, *hash ah) {}|| GET /path?action=check |
hash getCheck(hash cx, *hash ah) {} || PUT /path?action=updateStatus |
hash putUpdateStatus(hash cx, *hash ah) {} |For example:
“`
# class for handling REST data operations
class DataRestClass inherits AbstractRestClass {
string name() {
return "data";
}
hash<HttpHandlerResponseInfo> post(hash<auto> cx, *hash<auto> ah) {
log(LL_INFO, "POST received with args: %y", ah);
return RestHandler::makeResponse(200, "OK");
}
hash<HttpHandlerResponseInfo> getCheck(hash<auto> cx, *hash<auto> ah) {
if (!exists ah.filename)
throw "DATA-ERROR", "missing
'filename' argument";
log(LL_INFO, "checking filename: %y (OK)", ah.filename);
# fake the response here
return RestHandler::makeResponse(200, "OK");
}
}
class CallerRestClass inherits AbstractRestClass {
string name() {
return "caller";
}
hash<HttpHandlerResponseInfo> putUpdateStatus(hash<auto> cx, *hash<auto> ah) {
log(LL_INFO, "PUT received with args: %y", ah);
return RestHandler::makeResponse(200, "OK");
}
}
“`
See [Implementing REST Services](https://www.qoretechnologies.com/manual/qorus/current/qore/modules/RestHandler/html/index.html#restimplementation) for more information.
3. Add URI Path Handlers
Add the URI path handlers that you’ve created in the constructor of the class subclassing [AbstractServiceRestHandler](https://www.qoretechnologies.com/manual/qorus/current/qorus/classOMQ_1_1AbstractServiceRestHandler.html) by calling [AbstractServiceRestHandler::addClass()](https://www.qoretechnologies.com/manual/qorus/current/qore/modules/RestHandler/html/class_rest_handler_1_1_rest_handler.html#ae815241876b688d621eed966ab0437b2) with [AbstractRestClass](https://www.qoretechnologies.com/manual/qorus/current/qore/modules/RestHandler/html/class_rest_handler_1_1_abstract_rest_class.html) objects.For example:
“`
# the root REST handler object
class ExampleRestHandler inherits AbstractServiceRestHandler {
constructor() : AbstractServiceRestHandler("rest-example") {
addClass(new DataRestClass());
addClass(new CallerRestClass());
}
}
“`
To add URI path handlers for further path components, reimplement the [AbstractRestClass::subClass()](https://www.qoretechnologies.com/manual/qorus/current/qore/modules/RestHandler/html/class_rest_handler_1_1_abstract_rest_class.html#a35b26c641f8c0ccaee705a4c1e7af163) method and return the appropriate URI path handler object ([AbstractRestClass](https://www.qoretechnologies.com/manual/qorus/current/qore/modules/RestHandler/html/class_rest_handler_1_1_abstract_rest_class.html) object) corresponding to the path component in the request as given by the
name argument to [AbstractRestClass::subClass()](https://www.qoretechnologies.com/manual/qorus/current/qore/modules/RestHandler/html/class_rest_handler_1_1_abstract_rest_class.html#a35b26c641f8c0ccaee705a4c1e7af163).
4. Instantiate the REST Handler Class and Bind the HTTP Handler
In the service’s [init method](https://www.qoretechnologies.com/manual/qorus/current/qorus/implementingservices.html#serviceinit) function, instantiate the REST handler class and call [svc_bind_http()](https://www.qoretechnologies.com/manual/qorus/current/qorus/namespaceOMQ_1_1UserApi_1_1Service.html#af24d7e8b6dd535d582ac81a15fe2df83) to bind the handler to the HTTP server.If the [AbstractServiceHttpHandler::addListener()](https://www.qoretechnologies.com/manual/qorus/current/qorus/classOMQ_1_1AbstractServiceHttpHandler.html#a86fd5c292fc50c08730ee963970412ad) call is made, then the REST handler will be bound to all global HTTP listeners for Qorus Integration Engine.
For example:
“`
class MyService inherits QorusServiice {
init() {
# create the REST handler object
ExampleRestHandler lh();
# bind the handler to all global Qorus listeners
bindHttp(lh);
}
}
“`
5. Optional: Implement Custom User Authentication and Authorization
By default, a [DefaultQorusRBACAuthenticator](https://www.qoretechnologies.com/manual/qorus/current/qorus/classOMQ_1_1DefaultQorusRBACAuthenticator.html) object is passed to the constructor, which results in standard Qorus [RBAC](https://www.qoretechnologies.com/manual/qorus/current/qorus/sysarch.html#RBAC) security being applied (which is only enforced if [rbac-security](https://www.qoretechnologies.com/manual/qorus/current/qorus/systemoptions.html#rbac-security) is enabled, in which case users must have at least the [LOGIN](https://www.qoretechnologies.com/manual/qorus/current/qorus/group__RBACPermissions.html#gaf8cdba0b16224a52ae9509d191317f76) role to connect to the REST handler).Custom user authentication can be implemented for REST handlers by passing an object from a user-defined class that inherits directly from [AbstractAuthenticator](https://www.qoretechnologies.com/manual/qorus/current/qore/modules/HttpServerUtil/html/class_http_server_1_1_abstract_authenticator.html) as the auth argument to the [AbstractServiceRestHandler::constructor()](https://www.qoretechnologies.com/manual/qorus/current/qorus/classOMQ_1_1AbstractServiceRestHandler.html#ae168909c9d873bd097c6dfaabace2616) method.
To allow any user to connect to the REST service, even if [rbac-security](https://www.qoretechnologies.com/manual/qorus/current/qorus/systemoptions.html#rbac-security) is enabled, use a [PermissiveAuthenticator](https://www.qoretechnologies.com/manual/qorus/current/qorus/classOMQ_1_1PermissiveAuthenticator.html) object instead as in the following example:
“`
class ExampleRestHandler inherits AbstractServiceRestHandler {
constructor() : AbstractServiceRestHandler("rest-example", False, new PermissiveAuthenticator()) {
addClass(new DataRestClass());
addClass(new CallerRestClass());
}
}
“`
6. Example Service Source
The following code is in the rest-example-v1.0.qsd example file.“`
# -*- mode: qore; indent-tabs-mode: nil -*-
# service: rest-example
# serviceversion: 1.0
# servicedesc: REST API example service
# serviceauthor: Qore Technologies, s.r.o.
# parse-options: PO_NEW_STYLE, PO_REQUIRE_TYPES, PO_STRICT_ARGS
# autostart: true
# classname: RestExample
# class-based: true
# define-group: EXAMPLES: example interface objects
# define-group: REST-SERVICE-EXAMPLE-1: REST service example 1 interface objects
# groups: EXAMPLES, REST-SERVICE-EXAMPLE-1
# ENDSERVICE
# class for handling REST data operations
class DataRestClass inherits AbstractRestClass {
string name() {
return "data";
}
hash<HttpHandlerResponseInfo> post(hash<auto> cx, *hash<auto> ah) {
log(LL_INFO, "POST received with args: %y", ah);
return RestHandler::makeResponse(200, "OK");
}
hash<HttpHandlerResponseInfo> getCheck(hash<auto> cx,
*hash<auto> ah) {
if (!ah.filename)
throw "DATA-ERROR", "missing 'filename' argument";
log(LL_INFO, "checking filename: %y (OK)", ah.filename);
# fake the response here
return RestHandler::makeResponse(200, "OK");
}
}
# class for handling REST caller operations
class CallerRestClass inherits AbstractRestClass {
string name() {
return "caller";
}
hash<HttpHandlerResponseInfo> putUpdateStatus(hash<auto> cx, *hash<auto> ah) {
log(LL_INFO, "PUT received with args: %y", ah);
return RestHandler::makeResponse(200, "OK");
}
}
# the root REST handler object
class ExampleRestHandler inherits AbstractServiceRestHandler {
constructor() : AbstractServiceRestHandler("rest-example") {
addClass(new DataRestClass());
addClass(new CallerRestClass());
}
}
class RestExample inherits QorusService {
# name: init
# desc: initializes the service and sets up the REST handler
init() {
# create the REST handler object
ExampleRestHandler lh();
# bind the handler to all global Qorus listeners
bindHttp(lh);
}
}
# END
“`
How-To: Implement a Schema Module
Qorus provides support for automated database schema management through [schema modules](https://www.qoretechnologies.com/manual/qorus/current/qorus/schemamanagement.html#schemamodulefile).Automated schema management means that the creation, upgrade, downgrade, and drop actions for all schema objects are completely automated.
The base functionality for schema management in Qorus is provided by the [Schema](https://www.qoretechnologies.com/manual/qorus/current/qore/modules/Schema/html/index.html) module. The Schema module’s approach is based on schema alignment and works as follows:
* Write a [schema module](https://www.qoretechnologies.com/manual/qorus/current/qorus/schemamanagement.html#schemamodulefile) file with a class that inherits [AbstractSchema](https://www.qoretechnologies.com/manual/qorus/current/qore/modules/Schema/html/class_schema_1_1_abstract_schema.html) providing a description of the schema (and optionally reference data) as Qore data.
* Implement the following two
public functions in the module:
* get_datasource_name(): returns the name of the [Qorus datasource](https://www.qoretechnologies.com/manual/qorus/current/qorus/connmon.html#dsconn) where the schema should reside* get_user_schema(): creates and returns the schema object itself
For example:
“`
public namespace ExampleSchema {
public string sub get_datasource_name() {
return "omquser";
}
public ExampleSchema sub get_user_schema(AbstractDatasource ds, *string dts, *string its) {
return new ExampleSchema(ds, dts, its);
}
class ExampleSchema inherits AbstractSchema {
# the actual schema implementation goes here
}
}
“`
Schema modules end in
.qsm and are processed by [oload](https://www.qoretechnologies.com/manual/qorus/current/qorus/commandline.html#oload), which will automatically run schema alignment in the database identified by the [Qorus datasource](https://www.qoretechnologies.com/manual/qorus/current/qorus/connmon.html#dsconn) provided by get_datasource_name() in the schema module.If the schema implements the [AbstractVersionedSchema](https://www.qoretechnologies.com/manual/qorus/current/qore/modules/Schema/html/class_schema_1_1_abstract_versioned_schema.html) interface, then [oload](https://www.qoretechnologies.com/manual/qorus/current/qorus/commandline.html#oload) will only execute schema alignment if the schema does not exist in the database or if the declared schema version is greater than the version in the database (or if the
--force option is given to [oload](https://www.qoretechnologies.com/manual/qorus/current/qorus/commandline.html#oload)).For more information on automatic schema management, see:
* [User Schema Management](https://www.qoretechnologies.com/manual/qorus/current/qorus/schemamanagement.html): Qorus documentation with a complete schema module file example
* [HowTo: Implement Automatic Schema Management](https://github.com/qorelanguage/qore/wiki/HowTo%3A-Implement-Automatic-Schema-Management): Qore Programming Language Wiki
* [Schema](https://www.qoretechnologies.com/manual/qorus/current/qore/modules/Schema/html/index.html): Qore Programming Language Schema module documentation
* [SqlUtil Schema Management](www.qoretechnologies.com/manual/qorus/3.1.0/qore/modules/SqlUtil/html/schema_management.html): Qore Programming Language SqlUtil module providing the underlying API support for schema management
How-To: Synchronize Database Data Between Databases
Introduction
This HowTo explains how to implement inter-database data exchange interfaces in Qorus; by using [Bulk DML](https://github.com/qorelanguage/qore/wiki/HowTo:-Use-Bulk-DML), [SqlUtil](https://qoretechnologies.com/manual/qorus/current/qore/modules/SqlUtil/html/index.html), and the [DataStream protocol](https://qoretechnologies.com/manual/qorus/current/yaml/DataStreamUtil/html/index.html#datastreamprotocol), Qorus provides infrastructure capable of transferring large amounts of data with high performance and a small memory footprint between databases from disparate vendors even in geographically-separated networking environments.
1. Local Source Database
With a locally connected source databases, you will need a [datasource](https://qoretechnologies.com/manual/qorus/current/qorus/connmon.html#dsconn) in the local system.You can create an [SQLStatement](https://qoretechnologies.com/manual/qorus/current/qore/lang/html/class_qore_1_1_s_q_l_1_1_s_q_l_statement.html) object manually by acquiring a [DatasourcePool](https://qoretechnologies.com/manual/qorus/current/qore/lang/html/class_qore_1_1_s_q_l_1_1_datasource_pool.html) object from a [datasource](https://qoretechnologies.com/manual/qorus/current/qorus/connmon.html#dsconn) with [get_datasource_pool()](https://qoretechnologies.com/manual/qorus/current/qorus/namespaceOMQ_1_1UserApi.html#aec754a8158a53b476a029d6630450f51) as in the following example:
“`
DatasourcePool dsp = get_datasource_pool("source-db");
SQLStatement stmt(dsp);
stmt.prepare("select id, name, amount from source_table where type = %v and amount >= 100", current_type);
“`
Alternatively, you can use [SqlUtil](https://qoretechnologies.com/manual/qorus/current/qore/modules/SqlUtil/html/index.html) to acquire an input iterator by acquiring an [AbstractTable](https://qoretechnologies.com/manual/qorus/current/qore/modules/SqlUtil/html/class_sql_util_1_1_abstract_table.html) object by calling [get_sql_table()](https://qoretechnologies.com/manual/qorus/current/qorus/namespaceOMQ_1_1UserApi.html#a85238c978eacc4d1d952d2733126c369) and then calling [AbstractTable::getRowIterator()](https://qoretechnologies.com/manual/qorus/current/qore/modules/SqlUtil/html/class_sql_util_1_1_abstract_table.html#a673ac8cdf7d7ad4d3763b4435b152930) to create the [SQLStatement](https://qoretechnologies.com/manual/qorus/current/qore/lang/html/class_qore_1_1_s_q_l_1_1_s_q_l_statement.html) object. Once you have an input iterator for the input data, you can use [Bulk DML](https://github.com/qorelanguage/qore/wiki/HowTo:-Use-Bulk-DML) (Qore wiki) to retrieve the input data using [SQLStatement::fetchColumns](https://qoretechnologies.com/manual/qorus/current/qore/lang/html/class_qore_1_1_s_q_l_1_1_s_q_l_statement.html#a204d8637dc5c072ad5e54d6063c25415) as in the following example:
“`
# get the source table object
AbstractTable source_table = get_sql_table("source_datasource", "source_table");
# get a select iterator from the source table
hash sh = (
"columns": ("id", "name", "amount"), "where": ("type": current_type, "amount":
op_ge(100)),
);
string sql;
SQLStatement stmt = source_table.getRowIterator(sh, \sql);
if (opt.verbose)
log(LL_DETAIL_2, "input SQL: %y\n", sql);
# even though we only select from the SQLStatement, we have to release the
transaction lock when we're done
on_error source_table.rollback();
on_success source_table.commit();
# use a block size of 1000 to select the source data
while (*hash h = stmt.fetchColumns(1000)) {
# … process the data
map process_row($1), h.contextIterator();
}
“`
2. Remote Source Database
If the database is connected to a remote Qorus instance, you can use the [DbRemoteReceive](https://qoretechnologies.com/manual/qorus/current/qorus/classOMQ_1_1DbRemoteReceive.html) class to stream the remote data to the local system from the remote [datasource](https://qoretechnologies.com/manual/qorus/current/qorus/connmon.html#dsconn).“`
# get the remote connection object
QorusSystemRestHelper remote = get_remote_rest_connection("remote");
# setup the select hash arguments for the remote source table
hash sh = (
"columns": ("id", "name", "amount"),
"where": ("type": current_type, "amount":
op_ge(100)),
);
# create the remote select stream object
DbRemoteReceive recv(remote, "remote_source_datasource_name",
"select", table_name, ("select": sh));
# DbRemoteReceive::getData() returns data in a "hash of lists" format
(column format)
while (*hash h = recv.getData()) {
# … process the data
map process_row($1), h.contextIterator();
}
“`
The [DbRemoteReceive](https://qoretechnologies.com/manual/qorus/current/qorus/classOMQ_1_1DbRemoteReceive.html) class uses the [DataStream protocol](https://qoretechnologies.com/manual/qorus/current/yaml/DataStreamUtil/html/index.html#datastreamprotocol) to transfer the data, which is selected from the remote database using the [sqlutil](https://qoretechnologies.com/manual/qorus/current/qorus/sqlutil-v1_82_8qsd.html) service in the remote Qorus instance. The [DbRemoteReceive](https://qoretechnologies.com/manual/qorus/current/qorus/classOMQ_1_1DbRemoteReceive.html) class uses a block size of
1000 by default; this and other options can be set in the [DbRemoteReceive::constructor()](https://qoretechnologies.com/manual/qorus/current/qorus/classOMQ_1_1DbRemoteReceive.html#a2ced39081cde90be57ac1132f7dbfea0) call (in the above example, only the select option is used).> **Note**: The [DataStream protocol](https://qoretechnologies.com/manual/qorus/current/yaml/DataStreamUtil/html/index.html#datastreamprotocol) requires a direct point-to-point connection or HTTP infrastructure that allows HTTP chunked transfers to pass through any intermediate nodes without modifying the HTTP traffic. If there is an HTTP proxy between the two Qorus instances that modifies HTTP chunked data sent through it, the [DataStream protocol](https://qoretechnologies.com/manual/qorus/current/yaml/DataStreamUtil/html/index.html#datastreamprotocol) may not be usable.
3. Local Target Database
With a locally connected target database, you can use the [BulkUpsertOperation](https://qoretechnologies.com/manual/qorus/current/qore/modules/BulkSqlUtil/html/class_bulk_sql_util_1_1_bulk_upsert_operation.html#details) or [BulkInsertOperation](https://qoretechnologies.com/manual/qorus/current/qore/modules/BulkSqlUtil/html/class_bulk_sql_util_1_1_bulk_insert_operation.html) classes to perform upserts (SQL merging or data alignment) or inserts in the target database, respectively.These classes require an [AbstractTable](https://qoretechnologies.com/manual/qorus/current/qore/modules/SqlUtil/html/class_sql_util_1_1_abstract_table.html) object for the target table, which can be acquired by calling [get_sql_table()](https://qoretechnologies.com/manual/qorus/current/qorus/namespaceOMQ_1_1UserApi.html#a85238c978eacc4d1d952d2733126c369). Then the data to be upserted/merged or inserted can be passed to the object with the [AbstractBulkOperation::queueData()](https://qoretechnologies.com/manual/qorus/current/qore/modules/BulkSqlUtil/html/class_bulk_sql_util_1_1_abstract_bulk_operation.html#a73eb495bcdbef0b6356f986afc8f5817) method. Note that this method will take raw data in column format (hash of lists), as returned from [SQLStatement::fetchColumns](https://qoretechnologies.com/manual/qorus/current/qore/lang/html/class_qore_1_1_s_q_l_1_1_s_q_l_statement.html#a204d8637dc5c072ad5e54d6063c25415) or [DbRemoteRecv::getData()](https://qoretechnologies.com/manual/qorus/current/qorus/classOMQ_1_1DbRemoteReceive.html#ab1fdee88d8f46447339099b8a9d37507).
3.1. Local Source Target Database
Example with local source:“`
# get the local source datasource object
AbstractTable source_table = get_sql_table("source_datasource", "source_table");
# get a select iterator from the source table
hash sh = (
"columns": ("id", "name", "amount"),
"where": ("type": current_type, "amount": op_ge(100)),
);
string sql;
SQLStatement stmt = source_table.getRowIterator(sh, \sql);
if (opt.verbose)
log(LL_DETAIL_2, "input SQL: %y\n", sql);
# even though we only select from the SQLStatement, we have to release the
transaction lock when we're done
on_error source_table.rollback();
on_success source_table.commit();
# get the target table object
AbstractTable target_table = get_sql_table("target_datasource", "target_table");
# create the bulk upsert operation object
BulkUpsertOperation upsert(target_table);
# perform bulk API and transaction handling on exit
on_error {
upsert.discard();
source_table.rollback();
}
on_success {
upsert.flush();
source_table.commit();
}
# use a block size of 1000 to select the source data
while (*hash h = stmt.fetchColumns(1000)) {
# BulkUpsertOperation::queueData() accepts data in "column format" (a hash of lists)
upsert.queueData(h);
}
“`
3.2. Remote Source Target Database
Example with a remote source:“`
# get the remote connection object QorusSystemRestHelper remote = get_remote_rest_connection("remote");
hash sh = (
"columns": ("id", "name", "amount"),
"where": ("type": current_type, "amount": op_ge(100)),
);
# open the remote DB select stream
DbRemoteReceive recv(remote, "remote_source_datasource_name",
"select", source_table_name, ("select": sh));
# get the target table object
AbstractTable target_table = get_sql_table("target_datasource",
"target_table");
# create the bulk upsert operation object
BulkUpsertOperation upsert(target_table);
# handle the bulk API and transaction handling
on_error {
upsert.discard();
source_table.rollback();
}
on_success {
upsert.flush();
source_table.commit();
}
# use a block size of 1000 to select the source data
while (*hash h = recv.getData()) {
# BulkUpsertOperation::queueData() accepts data in "column format" (a hash of lists)
upsert.queueData(h);
}
“`
> **Note**: upserting/merging can only work if the target table has a unique key that can be used to perform the merge; see [upserting or merging data](https://qoretechnologies.com/manual/qorus/current/qore/modules/SqlUtil/html/sql_operations.html#sql_upsert) for more information
4. Remote Target Database
With a remote target database, you can use the [DbRemoteSend](https://qoretechnologies.com/manual/qorus/current/qorus/classOMQ_1_1DbRemoteSend.html#details) classes to stream upserts (SQL merge statements) or inserts in the target database.The [DbRemoteSend](https://qoretechnologies.com/manual/qorus/current/qorus/classOMQ_1_1DbRemoteSend.html#details) class uses the [DataStream protocol](https://qoretechnologies.com/manual/qorus/current/yaml/DataStreamUtil/html/index.html#datastreamprotocol) to transfer the data, which is then upserted/merged or inserted into the remote database using the [sqlutil](https://qoretechnologies.com/manual/qorus/current/qorus/sqlutil-v1_82_8qsd.html) service in the remote Qorus instance. The [DbRemoteSend](https://qoretechnologies.com/manual/qorus/current/qorus/classOMQ_1_1DbRemoteSend.html#details) class uses a block size of
1000 by default; this and other options can be set in the [DbRemoteSend::constructor()](https://qoretechnologies.com/manual/qorus/current/qorus/classOMQ_1_1DbRemoteSend.html#aec84a91ba878fbd3a36d335ad73b46a9) call (in the example below no options are used).Example with local source:
“`
# get the source table object AbstractTable source_table = get_sql_table("source_datasource",
"source_table");
# get a select iterator from the source table
hash sh = (
"columns": ("id", "name", "amount"),
"where": ("type": current_type, "amount":
op_ge(100)),
);
string sql;
SQLStatement stmt = source_table.getRowIterator(sh, \sql);
if (opt.verbose)
log(LL_DETAIL_2, "input SQL: %y\n", sql);
# even though we only select from the SQLStatement, we have to release the
transaction lock when we're done
on_error source_table.rollback();
on_success source_table.commit();
# get the remote connection object
QorusSystemRestHelper remote = get_remote_rest_connection("remote");
# start the remote "upsert" stream
DbRemoteSend out(qrest, "remote_target_datasource_name",
"upsert", "target_table");
# in case of error, disconnect which will cause a rollback on the remote end
# due to the fact that the DataStream protocol relies on HTTP chunked transfer
# encoding, the socket could be in the middle of a chunked transfer when an
# error occurs, therefore it's better to simply disconnect than to try to
# execute a manual rollback when errors occur
on_error out.disconnect();
on_success out.commit();
# use a block size of 1000 to select the source data
while (*hash h = stmt.fetchColumns(1000)) {
# DbRemoteSend::append(hash) accepts data in "column format" (a hash of lists)
out.append(h);
}
“`
Example with a remote source:
“`
# get the remote source connection object
QorusSystemRestHelper remote_source =
get_remote_rest_connection("remote_source");
hash sh = (
"columns": ("id", "name", "amount"), "where": ("type": current_type, "amount": op_ge(100)), ); # open the remote DB select stream DbRemoteReceive recv(remote_source, "remote_source_datasource_name",
"select", source_table_name, ("select": sh));
# get the remote target connection object
QorusSystemRestHelper remote_target =
get_remote_rest_connection("remote_target");
# start the remote "upsert" stream
DbRemoteSend out(remote_target, "remote_target_datasource_name",
"upsert", "target_table");
# in case of error, disconnect which will cause a rollback on the remote end
# due to the fact that the DataStream protocol relies on HTTP chunked transfer
# encoding, the socket could be in the middle of a chunked transfer when an
# error occurs, therefore it's better to simply disconnect than to try to
# execute a manual rollback when errors occur
on_error out.disconnect();
on_success out.commit();
# use a block size of 1000 to select the source data
while (*hash h = recv.getData()) {
# DbRemoteSend::append(hash) accepts data in "column format" (a hash
of lists)
out.append(h);
}
“`
> **Note**:
* If the source and target are both remote as in the above example, then the data will be transferred through the instance executing the code; this is only recommended if no direct connection between the source and target is possible. * Upserting/merging can only work if the target table has a unique key that can be used to perform the merge; see [upserting or merging data](https://qoretechnologies.com/manual/qorus/current/qore/modules/SqlUtil/html/sql_operations.html#sql_upsert) for more information
> **See Also**:
* [DbRemote](https://qoretechnologies.com/manual/qorus/current/qorus/classOMQ_1_1DbRemote.html#details)
* [DbRemoteReceive](https://qoretechnologies.com/manual/qorus/current/qorus/classOMQ_1_1DbRemoteReceive.html#details)
* [DbRemoteSend](https://qoretechnologies.com/manual/qorus/current/qorus/classOMQ_1_1DbRemoteSend.html#details)
* [sqlutil streams](https://qoretechnologies.com/manual/qorus/current/qorus/sqlutil-v1_82_8qsd.html#details)
* [DataStream protocol](https://qoretechnologies.com/manual/qorus/current/yaml/DataStreamUtil/html/index.html#datastreamprotocol)
* [BulkInsertOperation](https://qoretechnologies.com/manual/qorus/current/qore/modules/BulkSqlUtil/html/class_bulk_sql_util_1_1_bulk_insert_operation.html#details)
* [BulkUpsertOperation](https://qoretechnologies.com/manual/qorus/current/qore/modules/BulkSqlUtil/html/class_bulk_sql_util_1_1_bulk_upsert_operation.html#details)
* [HowTo: Use Bulk DML](https://github.com/qorelanguage/qore/wiki/HowTo:-Use-Bulk-DML) (Qore wiki)
Misc Technical How-Tos
How-To: Back-Port Code from Qorus 3.1 to Qorus 3.0
Introduction
The following sections will outline issues to be aware of when back-porting code from Qorus 3.1 to Qorus 3.0.Note the following documentation references:
* Qorus 3.1 Release Notes describing changes from Qorus 3.0: http://www.qoretechnologies.com/manual/qorus/3.1.0/qorus/releasenotes.html
* Qorus 3.0.4 Documentation: http://www.qoretechnologies.com/manual/qorus/3.0.4.p7/qorus/index.html
No Mappers or Value Maps
Functionality configured in mappers and value maps must be implemented by hand in Qorus 3.0. The [Mapper](https://www.qoretechnologies.com/manual/qorus/3.0.4.p7/qore/modules/Mapper/html/index.html) and [TableMapper](https://www.qoretechnologies.com/manual/qorus/3.0.4.p7/qore/modules/TableMapper/html/index.html) modules are available though with reduced functionality
No BulkSqlUtil
[BulkSqlUtil](www.qoretechnologies.com/manual/qorus/3.1.0/qore/modules/BulkSqlUtil/html/index.html) was introduced in Qorus 3.1.0. To implement the equivalent of bulk DML in Qorus 3.0.4, custom types, collections and stored procedures need to be implemented in PL/SQL instead.
No Coalescing Operators
[??](www.qoretechnologies.com/manual/qorus/3.1.0/qore/lang/html/operators.html#null_coalescing_operator) and [?*](www.qoretechnologies.com/manual/qorus/3.1.0/qore/lang/html/operators.html#value_coalescing_operator) were introduced in Qorus 3.1.0; trying to use them in Qorus 3.0.4 will result in syntax errors in your code.
SQLStatement::fetchColumns() and SQLStatement::fetchRows() not usable
Due to a bug in the oracle driver in Qorus 3.0.4 (that was only fixed in Qorus 3.1), trying to use [SQLStatement::fetchRows()](www.qoretechnologies.com/manual/qorus/3.1.0/qore/lang/html/class_qore_1_1_s_q_l_1_1_s_q_l_statement.html#af877021d62eec2eed34359081a21b879) and [SQLStatement::fetchColumns()](www.qoretechnologies.com/manual/qorus/3.1.0/qore/lang/html/class_qore_1_1_s_q_l_1_1_s_q_l_statement.html#a204d8637dc5c072ad5e54d6063c25415) will result in an error with Qorus 3.0.4. Use [SQLStatement::fetchRow()](www.qoretechnologies.com/manual/qorus/3.1.0/qorus/qorusinterfacetest.html#qorusunittestintro) instead.
No Column Functions in the Schema module
The [Schema](https://www.qoretechnologies.com/manual/qorus/3.0.4.p7/qore/modules/Schema/html/index.html) module in Qorus 3.0.4 has no c_*() functions (ex: [c_varchar()](www.qoretechnologies.com/manual/qorus/3.1.0/qore/modules/Schema/html/namespace_schema.html#a97e8d12319759d1cbb6ff0e7571e6553), [c_number()](www.qoretechnologies.com/manual/qorus/3.1.0/qore/modules/Schema/html/namespace_schema.html#ab0dbcdc72bb137420dcfda520ca5f979), etc); these were introduced in the Schema module in Qorus 3.1.0.
No Testing Infrastructure
[QUnit](www.qoretechnologies.com/manual/qorus/3.1.0/qore/modules/QUnit/html/index.html) and [QorusInterfaceTest](www.qoretechnologies.com/manual/qorus/3.1.0/qorus/qorusinterfacetest.html#qorusunittestintro) were introduced with Qorus 3.1; Qorus 3.0.4 has no testing infrastructure. Please note that since Qorus 3.1 is backwards-compatible with Qorus 3.0.4, you can still write tests for Qorus 3.1 and test your interfaces on Qorus 3.1.
No SQL Cache
Qorus 3.0.4 has no [SQL cache API](www.qoretechnologies.com/manual/qorus/3.1.0/qorus/devel-sqlcache.html), so the [get_sql_table()](www.qoretechnologies.com/manual/qorus/3.1.0/qorus/namespaceOMQ_1_1UserApi.html#a85238c978eacc4d1d952d2733126c369) API function is not available.Instead just declare a [Table](www.qoretechnologies.com/manual/qorus/3.1.0/qore/modules/SqlUtil/html/class_sql_util_1_1_table.html) object directly with the following syntax:
“`
%requires SqlUtil
sub step_func() {
DatasourcePool dsp = get_datasource_pool("my-datasource");
Table table(dsp, "table_name");
# …
}
“`
