Version: 10.2.8

Data Store plugins

Data Store plugins can be of two types:

Execution: perform actions, such as database queries.
Transaction: enable transaction management and configuration.

Execution plugins

datastore

Open plugin description

The plugin is used to select data from Data Stores or execute queries. For recommendations on data selection, refer to Best practices for optimized memory usage.

The plugin contains the following attributes:

Name	Required	Default	Description
`name`	Yes		Data Store name.
`max`	No		Max row count in the result.

The plugin returns ListVariable that contains the query result from a Data Store.

Example:

<?xml version="1.0" encoding="UTF-8"?>
<config charset="UTF-8">
    <var-def name="state">
      colorado
    </var-def>
    <var-def name="result">
        <datastore name="States data store">
          select state_id from @this where state_name = '${state}';
        </datastore>
    </var-def>
    <export include-original-data="false">
        <single-column name="result" value="${result}"/>
    </export>
</config>

Example of how to work with the result set retrieved by a SQL query:

<?xml version="1.0" encoding="UTF-8"?>
<config>
    
    <var-def name="private_equity_datastore_name">
        <template>${private_equity_datastore_name}</template>
    </var-def>
             
    <var-def name="priority">
        <template>${priority}</template>
    </var-def>
  
    <var-def name="datastoreColumns">          
        name,nation,website,type,deeplink,priority,website_status,first_time_processing,screenshot_hash,last_checked,screenshot_url,last_processed_time
    </var-def>
   
    <var-def name="statusColumn">
        last_checked_status
    </var-def>
  
    <var-def name="websiteStatusColumn">
        website_status
    </var-def>
  
    <var-def name="query">
        <template>select @id, ${datastoreColumns} from @this
            where 
              (${websiteStatusColumn}='ACTIVE' or ${websiteStatusColumn}='' or ${websiteStatusColumn} is null) 
              and (${statusColumn} is null or ${statusColumn} = '')                              
              and priority='${priority.toString()}';
        </template>
    </var-def>

    <script><![CDATA[
      String[] outputColumns = org.apache.commons.lang.StringUtils.split(datastoreColumns.toString(), ",");
      List exportItems = new ArrayList();
      List ids = new ArrayList();
    ]]></script>
    
    <loop item="companyRow">
        <list>
            <datastore name="${private_equity_datastore_name}">
                ${query}
            </datastore>
        </list>
        <body>
            <script><![CDATA[
              ids.add(companyRow.get(0));
              Map exportItem = new HashMap();
         
              for (String column : outputColumns) {
                exportItem.put(column, companyRow.get(column).toString());
              }
              
              exportItem.put("private_equity_datastore_name", private_equity_datastore_name.toString());
            
              exportItems.add(exportItem);
            ]]></script>
        </body>
    </loop>


    <case>
        <if condition="${ids.size() &lt;  0 }">
            <datastore name="${private_equity_datastore_name}">
                update @this set ${statusColumn} = 'NEW' where @id in (${org.apache.commons.lang.StringUtils.join(ids, ",")});
            </datastore>
        </if>
    </case>


    <export include-original-data="false">
        <multi-column list="${exportItems}" split-results="true">
            <loop item="column">
                <list>
                    <script return="outputColumns" />
                </list>
                <body>
                    <put-to-column-getter name="${column}" property="${column}" />
                </body>
            </loop>
        </multi-column>
    </export>

</config>

create-datastore

Open plugin description

The plugin is used to create a Data Store or a DB table.

The plugin contains the following attributes:

Name	Required	Default	Description
`name`	Yes		Data Store name.

The child elements are as follows:

Name	Required	Default	Description
`datastore-column`	No		New column description with the following attributes: `name` `type—TEXT` (default), `INTEGER`, `DATE`, or `TIMESTAMP`.

Example:

<?xml version="1.0" encoding="UTF-8"?>
<config>
    <create-datastore name="data store name">
        <datastore-column name="a"/>
    </create-datastore>
    <export include-original-data="false"/>  
</config>

insert-datastore

Open plugin description

The plugin is used to insert a single record into your Data Store.

The plugin contains the following attributes:

Name	Required	Default	Description
`datastore-name`	Yes		Data Store name.
`json-value-map`	No		`java.util.Map` instance converted to JSON or `com.freedomoss.crowdcontrol.webharvest.plugin.datastore.dto.DataStoreRow`. JSON is formatted as follows: `{"values":[{"columnName":"column1","columnType":"TEXT","value":"value1"},{"columnName":"column2","columnType":"INTEGER","value":"5"}]}`. For a simple `column-name:value map`, the `TEXT` type is selected by default. Available types: `TEXT`, `INTEGER`, `DATE`, or `TIMESTAMP`.
`create`	No	false	If set to true, a Data Store is automatically created if it does not exist. Missing columns are added to the Data Store automatically.

As a result, a new record is inserted into the specified Data Store. Map entry keys are column names, and map entry values are inserted into the appropriate column.

Example:

<?xml version="1.0" encoding="UTF-8"?>
<config charset="UTF-8">
    <script><![CDATA[
        Map insertedParamMap = new HashMap();
        insertedParamMap.put("col_1", "col_1_val");
        jsonValueMap = new com.google.gson.Gson().toJson(insertedParamMap);
    ]]></script>
    <insert-datastore datastore-name="datastore_name"
                      json-value-map="${jsonValueMap}"/>
 
    <script><![CDATA[
        String dateFormat = "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'";
        com.freedomoss.crowdcontrol.webharvest.plugin.datastore.dto.DataStoreRow row = new com.freedomoss.crowdcontrol.webharvest.plugin.datastore.dto.DataStoreRow();
        row.addValue("column1", com.freedomoss.crowdcontrol.webharvest.plugin.datastore.enums.DataStoreColumnType.TEXT, "value1");
        row.addValue("column2", com.freedomoss.crowdcontrol.webharvest.plugin.datastore.enums.DataStoreColumnType.INTEGER, 5);
        row.addValue("column3", com.freedomoss.crowdcontrol.webharvest.plugin.datastore.enums.DataStoreColumnType.DATE, new Date(), dateFormat);
        row.addValue("column4", com.freedomoss.crowdcontrol.webharvest.plugin.datastore.enums.DataStoreColumnType.TIMESTAMP, new Date(), dateFormat);
        com.google.gson.Gson gson = new com.google.gson.GsonBuilder()
                .setDateFormat(dateFormat).create();
        jsonValueMap2 = gson.toJson(row);
    ]]></script>
    <insert-datastore datastore-name="datastore_name"
                      json-value-map="${jsonValueMap2}"/>
 
    <insert-datastore datastore-name="${datastoreName}" create="true">
        <script return="hit_submission_data_item.getWrappedObject().getItemValueMap()"/>
    </insert-datastore>
</config>

datastore-connection

Open plugin description

By default, when using the datastore plugin, you connect to a database configured on the WorkFusion server. When running a config locally from Eclipse, Data Store search is performed in the Data Stores directory.

The datastore-connection plugin is intended to connect to an external database and run queries of its child plugins: datastore, create-datastore, insert-datastore.

The plugin contains the following attributes:

Name	Required	Description
`url`	Yes	Database connection URL (JDBC URL).
`username`	No	Credentials, if needed.
`password`	No	Credentials, if needed.

The example contains instructions on how to:

Connect to an external PostgreSQL database.
Find a Data Store with the Countries_Lookup name
Run the select \* from @this query.

Example:

<?xml version="1.0" encoding="UTF-8"?>
<config charset="UTF-8">
    <datastore-connection url="jdbc:postgresql://localhost/wf_datastore"
                          username="use_secure_store_plugin"
                          password="use_secure_store_plugin" >
               
        <var-def name="result">                   
            <datastore name="Countries_Lookup">
                select * from @this;
            </datastore>
        </var-def>
    
    </datastore-connection>
</config>

The plugin returns a list variable that contains the query result from the Data Store.

Transaction plugins

Data Store transactions can be of two types:

Implicit: when a transaction starts implicitly, by a declaration in execution plugins, and has a defined commit part.
Explicit: when everything inside a plugin body is executed within a single transaction.

Data Store creation and schema updates are non-transactional operations.

datastore-transaction

Open plugin description

The plugin defines a transaction explicitly. It supports nested transactions. In this case, each nested transaction is executed as a separate transaction.

The inner Data Store plugin execution result is committed only at the end of the body execution.

The plugin contains the following attributes:

Name	Required	Default	Description
`id`	No		Transaction ID.
`isolation`	No		Transaction isolation level. Possible values: `READ_COMMITTED`, `READ_UNCOMMITTED`, `REPEATABLE_READ`.

Example:

<?xml version="1.0" encoding="UTF-8"?>                
<config>
      
        <var-def name="result">                   
         
            <script><![CDATA[
                Map insertedParamMap = new HashMap();
                insertedParamMap.put("col_1", "col_1_val");
                jsonValueMap = new com.google.gson.Gson().toJson(insertedParamMap);
                var dataStoreName="new original test Data Store";
            ]]></script>
             
            <datastore-transaction isolation="READ_COMMITTED">
                <var-def name="newRecordId">      
                    <insert-datastore datastore-name="${dataStoreName}"
                                      json-value-map="${jsonValueMap}" />
                </var-def>
                 
                <datastore name="${dataStoreName}">
                  UPDATE @this SET col_1 = 'changed0' WHERE @id = ${newRecordId};
                </datastore>
                 
                <datastore-transaction id="2">
                 
                    <var-def name="newRecordId2">      
                        <insert-datastore datastore-name="${dataStoreName}"
                                          json-value-map="${jsonValueMap}" />
                    </var-def>
                 
                    <datastore name="${dataStoreName}">
                      UPDATE @this SET col_1 = 'changed2' WHERE @id = ${newRecordId2};
                    </datastore>
                     
                </datastore-transaction>
                 
                <datastore name="${dataStoreName}">
                  UPDATE @this SET col_1 = 'changed1' WHERE @id = ${newRecordId};
                </datastore>
            </datastore-transaction>
        </var-def>
     
</config>

commit-transaction

Open plugin description

The plugin is used in conjunction with an implicit transaction declaration. When an implicit transaction is used, it is committed only when the plugin is used (for the specified transaction).

The plugin contains the following attributes:

Name	Required	Default	Description
`transaction-id`	Yes		Transaction ID.

Example:

<?xml version="1.0" encoding="UTF-8"?>                
<config>
      
        <var-def name="result">                   
         
            <script><![CDATA[
                Map insertedParamMap = new HashMap();
                insertedParamMap.put("col_1", "col_1_val");
                jsonValueMap = new com.google.gson.Gson().toJson(insertedParamMap);
                var dataStoreName="new original test Data Store";
            ]]></script>
             
            <var-def name="newRecordId">      
                <insert-datastore datastore-name="${dataStoreName}"
                                  json-value-map="${jsonValueMap}"
                                  transaction-id="1" />
            </var-def>
 
            <var-def name="newRecordId2">      
                <insert-datastore datastore-name="${dataStoreName}"
                                  json-value-map="${jsonValueMap}"
                                  transaction-id="2" />
            </var-def>
             
            <datastore name="${dataStoreName}" transaction-id="1">
              UPDATE @this SET col_1 = 'changed1' WHERE @id = ${newRecordId};
            </datastore>
                 
            <datastore name="${dataStoreName}" transaction-id="2">
              UPDATE @this SET col_1 = 'changed2' WHERE @id = ${newRecordId2};
            </datastore>
    
            <commit-transaction transaction-id="2" /> 
            <commit-transaction transaction-id="1" />    
        </var-def>
</config>

Data Store date and timestamp types

Data Store column types are date and timestamp. Pay attention to the DateTime formats provided as input. They are required to parse the date and time correctly.

DateTime example:

<?xml version="1.0" encoding="UTF-8"?>
<config charset="UTF-8">
   
    <create-datastore name="DifferentDataStoreTypes2">
        <datastore-column name="text_field" type="text"/>
        <datastore-column name="integer_field" type="integer"/>
        <datastore-column name="date_field" type="date"/>
        <datastore-column name="timestamp_field" type="timestamp"/>
    </create-datastore>
   
     <var-def name="result">                  
         
            <script><![CDATA[
                com.freedomoss.crowdcontrol.webharvest.plugin.datastore.dto.DataStoreRow row = new com.freedomoss.crowdcontrol.webharvest.plugin.datastore.dto.DataStoreRow();
                row.addValue("text_field", com.freedomoss.crowdcontrol.webharvest.plugin.datastore.enums.DataStoreColumnType.TEXT, "value1");
                row.addValue("integer_field", com.freedomoss.crowdcontrol.webharvest.plugin.datastore.enums.DataStoreColumnType.INTEGER, 5);
                row.addValue("date_field", com.freedomoss.crowdcontrol.webharvest.plugin.datastore.enums.DataStoreColumnType.DATE, "06/10/1989", "MM/dd/yyyy");
                row.addValue("timestamp_field", com.freedomoss.crowdcontrol.webharvest.plugin.datastore.enums.DataStoreColumnType.TIMESTAMP, "06/10/1989 15:35", "MM/dd/yyyy hh:mm");
                jsonValueMap = new com.google.gson.Gson().toJson(row);
            ]]></script>
             
            <insert-datastore create="true" datastore-name="DifferentDataStoreTypes2"
                              json-value-map="${jsonValueMap}" />
      </var-def>
   
    <export include-original-data="true">
    </export>
   
</config>

Add index to Data Store column

This trick can be helpful for Data Stores containing reference data, for example, for the Select One answer type.

Index names for PostgreSQL are public, so make sure there is no duplication. The suggested naming is ds_DataStoreName_ColumnName_idx. Mind that a name cannot start from a digit.

<!-- Add INDEX -->
<datastore name="50k autosuggest">
      CREATE INDEX ds_50k_autosuggest_name_idx ON @this (name);
</datastore>
  
<datastore name="50k autosuggest">
      CREATE UNIQUE INDEX ds_50k_autosuggest_id_idx ON @this (id);
</datastore>
 
 
<!-- Drop INDEX -->
<datastore name="50k autosuggest">
      DROP INDEX ds_50k_autosuggest_name_idx;
</datastore>
  
<datastore name="50k autosuggest">
      DROP INDEX ds_50k_autosuggest_id_idx;
</datastore>

Pass Data Store records to export

To achieve the goal, use the script below:

<?xml version="1.0" encoding="UTF-8"?>
<config xmlns="http://web-harvest.sourceforge.net/schema/1.0/config"
    charset="UTF-8" scriptlang="groovy">

    <script><![CDATA[
        result = new ArrayList();
        columns = [
            "emp_id",
            "emp_first_name",
            "emp_last_name"
        ];
    ]]></script>
    <loop item="row">
        <list>
            <datastore name="emp">
                select * from @this;
            </datastore>
        </list>
        <body>
            <script><![CDATA[
                Map conversionMap = new HashMap();
                for (column in columns) {
                    conversionMap.put(column, row.get(column).toString());
                }
                result.add(conversionMap);
            ]]></script>
        </body>
    </loop>

    <export include-original-data="true">
        <multi-column list="${result}" split-results="true">
            <loop item="column">
                <list>
                    <script return="columns" />
                </list>
                <body>
                    <put-to-column-getter name="${column.toString()}"
                        property="${column.toString()}" />
                </body>
            </loop>
        </multi-column>
    </export>
</config>

Optimize memory usage

To exclude memory consumption issues, avoid selecting all data from a Data Store when designing a Bot Task.

For example, the script selects all data from a Data Store, but only the first record is required. As a result, a large amount of unused data is loaded into the memory:

<var-def name="resultList">
<datastore name="${someDatastore}">
    select * from @this;
</datastore>
</var-def>
<script>
    <![CDATA[
        String name = resultList.toList().get(0).get("name").toString();
        // some actions with variable name
    ]]>
</script>

The recommendation is to select only required rows and columns as shown below:

<var-def name="resultList">
<datastore name="${someDatastore}">
    select name from @this LIMIT 1;
</datastore>
</var-def>
<script>
    <![CDATA[
        String name = resultList.toList().get(0).get("name").toString();
        // some actions with variable name
    ]]>
</script>

troubleshooting

For troubleshooting tips, refer to the following support guides:

Execution plugins​

datastore​

create-datastore​

insert-datastore​

datastore-connection​

Transaction plugins​

datastore-transaction​

commit-transaction​

Data Store date and timestamp types​

Add index to Data Store column​

Pass Data Store records to export​

Optimize memory usage​

Execution plugins

datastore

create-datastore

insert-datastore

datastore-connection

Transaction plugins

datastore-transaction

commit-transaction

Data Store date and timestamp types

Add index to Data Store column

Pass Data Store records to export

Optimize memory usage