Data Tool¶

propertyCase for item property name handling¶

Starting with schema version 7, you can specify if and how you want item property names to be normalized. With schema version 7, the default is to preserve case. Older schema versions continue to have the legacy behavior of lowercasing property names from column names but from pivoted values from merge-columns. The valid values for propertyCase are:

preserve

Use whatever case we get back from the database

lower

All property names are converted to locale lower case

upper

All property names are converted to locale upper case

legacy

Property names from sql column labels are converted to lower case, but property names from vertical table values are preserved.

<sqlPublisher name="production"
  dataSource="dataSource" profile="profile">
  ...
</sqlPublisher>

Changeset Categories¶

A SqlPublisher can be configured to produce two categories of changesets.

• Complete changesets: one of snapshot, bulk, or full
• Incremental changesets: delta

A SqlPublisher may optionally contain one complete changeset defintion. A complete changeset definition is provided by the snapshot, bulk, and full elements. As the name suggests, a complete changeset should include all the items in your dataset.

A SqlPublisher may contain any number of incremental changeset definitions, each one defined in a delta element. A delta changeset includes only the items that have been modified or deleted since the previous incremental or complete changeset was created for a particular profile.

The changeset category elements contain one or more set-item elements, which in turn contain a query and optional merge-columns and subquery elements. The snapshot and delta elements may also contain add-to-item and remove-item elements, which in turn contain a query and, in the case of add-to-item, optional merge-columns and subquery elements. The bulk and full elements cannot contain any remove-item or add-to-item elements, but can contain a query and optional merge-columns and subquery elements.

When the Data Tool processes a changeset for a SqlPublisher, the queries inside of the set-item elements are used to create set-item elements in the resulting changeset to represent items that were created or modified. The queries inside of the add-to-item elements are used to create add-to-item elements in the resulting changeset to represent properties that are set on preexisting items. The queries inside of the remove-item elements are used to create remove-item elements in the resulting changeset to record when items are deleted.

In all queries and subqueries, the column names are used for property names unless they are overridden by specifying a column alias.

To include modified or created items in a delta changeset:

<delta>
  <set-item idColumn="id">
    <query><![CDATA[
    select * from items where last_updated >= :start and last_updated < :end
  ]]></query>
  </set-item>
</delta>

<delta>
  <remove-item idColumn="id">
    <query><![CDATA[
    select id from deleted_items where last_updated >= :start and last_updated < :end
  ]]></query>
  </remove-item>
</delta>

To follow deletions in a delta changeset:

<delta>
  <remove-item idColumn="id">
    <query><![CDATA[
    select id from deleted_items where last_updated >= :start and last_updated < :end
  ]]></query>
  </remove-item>
</delta>

To include all items in a snapshot changeset:

<snapshot>
  <set-item idColumn="id">
    <query><![CDATA[
    select * from items
  ]]></query>
  </set-item>
</snapshot>

Note that a snapshot may contain a remove-item query. Bulk and full must not contain a remove-item.

<snapshot>
  <set-item idColumn="id">
    <query><![CDATA[
    select * from items
  ]]></query>
  </set-item>
  <remove-item idColumn="id">
    <query><![CDATA[
    select id from deleted_items
  ]]></query>
  </remove-item>
</snapshot>

Note that, unlike snapshots, bulk and full changesets must not include any duplicate items.

Note that calling stored procedures is supported. Calling a stored procedure looks like this.

<delta>
  <set-item idColumn="id">
    <query><![CDATA[
    call get_updated_items(:start, :end)
  ]]></query>
  </set-item>
</delta>

The SQL is processed by a org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate which supports named parameters.

The following bound parameters are available for your queries. Bound parameter names are not case sensitive. Note that start and end are null for snapshot, bulk and full queries, so you probably do not want to include them in your sql:

• start The inclusive start of the date range for this changeset as a java.util.Date.
• end The exclusive end of the date range for this changeset as a java.util.Date.
• kind ‘snapshot’ or ‘delta’

Each set-item can contain one query element, and zero or more subquery elements. Each set-item is executed in sequence. This supports creating changesets that union across various different views or tables.

Each add-to-item can contain one query element, and zero or more subquery elements. Each add-to-item is executed in sequence. This supports creating changesets that union across various different views or tables.

Provider and Kind Support¶

If the identifiers in your data are only unique within the context of a specific content provider and/or data type, as of Discovery Engine 3.3.0 and Data Tool 1.6 you no longer need to manipulate the value returned for the idColumn to make it unique within your engine. Instead you can use the providerColumn and kindColumn attributes on the set-item, add-to-item, and remove-item configuration elements. The presence of these attributes will result in the generation of changeset set-item, add-to-item, and remove-item elements with locator, provider and kind attributes, and without id attributes. The Discovery Engine combines locator, provider and kind to create a unique identifier attribute for each item. This feature is compatible with merge-columns and subquery elements - just think of xxxxColumn as the item property key, whether it comes from the main query, merge-columns or a subquery. The properties for the idColumn, providerColumn and kindColumn are not included in the set-item or add-to-item properties structure, as this would create redundant data entries in the item properties. The special item properties _id, _locator, _provider, and _kind are included when you request item properties from the Discovery Engine.

Note that providerColumn and kindColumn are each optional. You may choose to include none, one, or both. If either of the attributes are present, even if they are empty, then the resulting set-item or add-to-item will use locator instead of id.

Here is an example snapshot configuration that contains two kinds of items (with possibly overlapping ids), and one provider. If you did not want to track the provider, then in this case the providerColumn could be omitted.

<snapshot>
  <set-item idColumn="id" providerColumn="provider" kindColumn="kind">
    <query><![CDATA[
    select m.*, 'imdb' as provider, 'movie' as kind from movies m
  ]]></query>
  </set-item>
  <set-item idColumn="id" providerColumn="provider" kindColumn="kind">
    <query><![CDATA[
    select p.*, 'imdb' as provider, 'person' as kind from person p
  ]]></query>
  </set-item>
</snapshot>

Vertical Table Support¶

If your database uses a vertical table design, then you will want to pivot multiple rows from your vertical table into one conceptual set-item in the generated changeset. The Data Tool supports this kind of pivot with one or more merge-columns elements.

With merge-columns, your query is expected to return multiple rows for each item. For each row returned, the merge-columns element tells the Data Tool to use the value from keyColumn for the item property key and and the value from valueColumn for the item property value. Multiple values returned across multiple rows for the same item and property are combined into an array structure as shown for entry “ratings” in the this example:

<changeset>
  <set-item id="007">
    <properties>
      <struct>
        <entry name="ratings">
          <array>
            <element><int>1</int></element>
            <element><int>5</int></element>
          </array>
        </entry>
      </struct>
    </properties>
  </set-item>
</changeset>

The data tool expects the result set to be ordered by item id, which is implemented through the idColumn value, so the SQL query to generate the result set should order the results appropriately.

If the results are not ordered by id, then the resulting set-item entries will not be reliable. No error will be thrown.

Note that any non-merged columns from the query resultset will be included in the resulting set-item. Unlike merge-columns, only the the first row will be used to populate the set-item.

You may include subquery elements after your merge-columns. Any subqueries that you include will be run only after the merges are complete.

Here is an example vertical table schema

create table vertical_master_items (
  id varchar(255) primary key not null,
  lastUpdated timestamp not null default current_timestamp);

create table vertical_data (
  id varchar(255) not null references vertical_master_items(id),
  name varchar(255),
  value varchar(255));

And here is an example of the use of the merge-columns configuration element.

<sqlPublisher name="test-simple" dataSource="dataSource">
  <delta>
    <set-item idColumn="id">
      <query><![CDATA[
        SELECT data.id, data.name, data.value
        FROM vertical_master_items master
        JOIN vertical_data data on data.id = master.id
        WHERE lastUpdated >= :start and lastUpdated < :end
        ORDER BY data.id
    ]]></query>
      <merge-columns keyColumn="name" valueColumn="value"/>
    </set-item>
  </delta>
</sqlPublisher>

With this data

vertical_master_items

vertical_data

The following changeset will be created:

<changeset>
  <set-item id="1">
    <properties>
      <struct>
        <entry name="id">
          <string>1</string>
        </entry>
        <entry name="color">
          <string>red</string>
        </entry>
      </struct>
    </properties>
  </set-item>
  <set-item id="2">
    <properties>
      <struct>
        <entry name="id">
          <string>2</string>
        </entry>
        <entry name="color">
          <array>
            <element>
              <string>orange</string>
            </element>
            <element>
              <string>yellow</string>
            </element>
          </array>
        </entry>
      </struct>
    </properties>
  </set-item>
</changeset>

Subqueries¶

The engine requires that data available through one-to-many relationships be “flattened” or denormalized as part of the main item property query. A subquery is typically used to pull in values from child tables. Subqueries provide a very flexible means for managing these relationships.

Subqueries are optional. Each set-item element can contain zero or more subquery elements. Subqueries are executed after the single main required query.

Subqueries are quite powerful and flexible. Here are some ways that you might use a subquery.

1. Map a list of values from a single column in a child table to an item property as an array of values.
2. Map a list of values from a single column in a child table to an item property as a single string of delimited values.
3. Map a list of subquery rows to multiple item properties with each column mapped to an item property. Each property containing an array of values.
4. Map a list of subquery rows to multiple item properties with each column mapped to an item property. Each property containing a single string of delimited values.
5. Map a list of subquery rows to a single item property containing a map with each column as a map key for a string of delimited values.
6. Map a list of subquery rows to a single item property containing a list of maps (one per subquery row), with each column as a map key for that columns value.
7. Use a discriminator column returned from the subquery to map a list of subquery rows to a single item property containing a map with each discriminator value used as a map key with its value being a map of column name to value for that discriminator’s row.

The results from each subquery are merged into the set-item element. The column names or aliases are used as property names, overwriting any entries previously populated by the main query. The subquery results can optionally be scoped by specifying either a property or propertyPrefix attribute on the subquery. Specifying a property means use that as the set-item property name. Specifying a propertyPrefix means prepend the prefix to the column name(s) or alias(es) when creating set-item property names.

Subquery results can be represented as arrays of values, a delimited string, arrays of maps, maps of arrays, maps of delimited strings, or mapped to multiple entries on the main set-item with the values as arrays or delimited strings. The choice of how multiple values are mapped is controlled by the type attribute.

If a subquery returns more than one row, then the row values are represented as an array. If a delimiter is specified, then the row values returned by the subquery are joined by the delimiter into a single value.

If a subquery returns more that one column, and it is mapped using a property, then the property value will be represented by a dictionary (map) or an array of dictionaries, with subquery each row consisting of a single dictionary of column name to column value. Each column is mapped to an entry in the struct. Column aliases are respected, so you can re-map the database column names to arbitrary strings in the returned dictionaries.

By specifying a propertyPrefix, each column from the subquery is merged into the main set-item using the propertyPrefix combined with the column name or column alias to create the set-item property name. The values are either a single value (when the subquery returns a single row), an array of values, or a string of delimited values depending on whether they type specified was array or delimited.

Subqueries that return multiple columns may use the discriminator feature. When a discriminator is used, the values of each row are mapped to a dictionary with the discriminator value as the key. If multiple subquery rows return the same discriminator value, then the values from the last row returned overwrite any previous values for that discriminator.

The default subquery type is array - which builds an array element in the set-item structure for the multiple values for each column returned. The other valid choice for type is delimited - which builds a single delimited string containing the multiple values. For type of delimited, a delimiter can specified. If unspecified, the default delimiter is a comma.

All the columns or aliases returned by the main query are available to the subquery as named bound parameters. The bound parameters from the main query (:start, :end, and :kind) are not available to the subquery. Bound parameter names are not case sensitive. If the main query returns null or an empty string for a column, then the corresponding bound parameter will have a null value. Bound parameters in a subquery that do not have a corresponding column in the main query will have a null value.

The subquery examples below use the id returned from the main query as a bound parameter.

<subquery><![CDATA[
  select name as color from SubqueryColors where parent_id=:id order by name
]]></subquery>
<subquery type="delimited" delimiter="|"><![CDATA[
  select name as color_delim from SubqueryColors where parent_id=:id order by name
]]></subquery>
<subquery property="color-default"><![CDATA[
  select name from SubqueryColors where parent_id=:id order by name
]]></subquery>
<subquery property="color-array" type="array"><![CDATA[
  select name from SubqueryColors where parent_id=:id order by name
]]></subquery>
<subquery property="color-delimited" type="delimited"><![CDATA[
  select name from SubqueryColors where parent_id=:id order by name
]]></subquery>
<subquery property="color-delimited" type="delimited" delimiter="|"><![CDATA[
  select name from SubqueryColors where parent_id=:id order by name
]]></subquery>
<subquery property="address" discriminator="addr_type"><![CDATA[
  select addr_type, street, city, state, zipcode
  from SubqueryAddress
  where parent_id=:id
  order by id
]]></subquery>
<subquery propertyPrefix="product"><![CDATA[
  select id, price, description from order_lines where order_id=:id order by id
]]></subquery>

Supported Column Types¶

The following SQL data types are supported by the Data Tool and will be mapped from your query and subquery results to changeset set-item or add-to-item property values.

• BIGINT
• BIT
• BOOLEAN
• CHAR
• CLOB
• DATE
• DECIMAL
• DOUBLE
• FLOAT
• INTEGER
• LONGVARCHAR
• NUMERIC
• REAL
• SMALLINT
• TIME
• TIMESTAMP
• TINYINT
• VARCHAR

Only char, varchar, longvarchar and clob columns can be treated as embedded JSON columns.

Unsupported Column Types¶

The following SQL data types are not supported by the Data Tool. They will be ignored if they are returned from your query or subquery.

• ARRAY
• BINARY
• BLOB
• DATALINK
• DISTINCT
• JAVA_OBJECT
• LONGVARBINARY
• NULL
• OTHER
• REF
• STRUCT
• VARBINARY