...
- Optional Mode - MERGE (default) or DELETE
- Optional Context
- Entity type - to give hints to import service as to what type of import entity we are working with and what data federation rules to apply.
- Import file descriptor - specifies how CSV data is to be processed (i.e. encoding, delimiters, text qualifier)
- Select/Insert/Delete statements - used by import service to manipulate CSV row tuples and look up data in database
- Import columns - describes what columns in CSV file match to what properties in target entity
skuprices.xml import descriptor
| Code Block | ||||
|---|---|---|---|---|
| ||||
<import-descriptor xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://www.yes-cart.org/schema/import-descriptor.xsd">
<entity-type>org.yes.cart.domain.entity.CarrierSla</entity-type>
<import-file-descriptor>
<file-encoding>UTF-8</file-encoding>
<file-name-mask>carrierslanames(.*).csv(.*)</file-name-mask>
<ignore-first-line>true</ignore-first-line>
<column-delimiter>;</column-delimiter>
<text-qualifier>"</text-qualifier>
</import-file-descriptor>
<select-sql>select c from CarrierSlaEntity c where c.name = {name} and c.carrier.name = {carrier}</select-sql>
<import-columns>
<column-descriptor>
<column-index>0</column-index>
<field-type>FIELD</field-type>
<name>name</name>
<value-regex>(.{0,255})(.*)</value-regex>
<value-regex-group>1</value-regex-group>
</column-descriptor>
<column-descriptor>
<column-index>1</column-index>
<field-type>FIELD</field-type>
<name>displayName</name>
<language>en</language>
<value-regex>(.{0,4000})(.*)</value-regex>
<value-regex-group>1</value-regex-group>
</column-descriptor>
...
<column-descriptor>
<column-index>5</column-index>
<field-type>FIELD</field-type>
<name>displayDescription</name>
<language>en</language>
<value-regex>(.{0,4000})(.*)</value-regex>
<value-regex-group>1</value-regex-group>
</column-descriptor>
...
<column-descriptor>
<column-index>10</column-index>
<field-type>FK_FIELD</field-type>
<entity-type>org.yes.cart.domain.entity.Carrier</entity-type>
<name>carrier</name>
<value-regex>(.{0,255})(.*)</value-regex>
<value-regex-group>1</value-regex-group>
<lookup-query>select c from CarrierEntity c where c.name = {carrier}</lookup-query>
</column-descriptor>
<column-descriptor>
<column-index>11</column-index>
<field-type>FIELD</field-type>
<name>slaType</name>
<value-regex>(.{1,1})(.*)</value-regex>
<value-regex-group>1</value-regex-group>
</column-descriptor>
<column-descriptor>
<column-index>12</column-index>
<field-type>FIELD</field-type>
<data-type>INT</data-type>
<name>maxDays</name>
</column-descriptor>
<column-descriptor>
<column-index>13</column-index>
<field-type>FIELD</field-type>
<data-type>BOOLEAN</data-type>
<name>billingAddressNotRequired</name>
</column-descriptor>
...
</import-columns>
</import-descriptor> |
...
Query section can contain following queries:
| Queries | Syntax | Purpose | Notes |
|---|---|---|---|
| select-sql | HSQL | Look up existing objects to be updated/deleted | |
| insert-sql | SQL | Native insert to speed up import | Bypasses hibernate processes and thus not eligible for cache evictions or audit tracing |
| delete-sql | HSQL | Native delete to speed up import | Bypasses hibernate processes and thus not eligible for cache evictions or audit tracing |
"insert-sql" and "delete-sql" are optional. When these configurations are omitted standard Hibernate save() and delete() methods are invoked on objects retrieved by the "select-sql".
...
All queries support templating mechanism where values from CSV tuples can be used as part of the query. All that is necessary is to specify placeholders in "select-sql", "insert-sql" or "delete-sql" which is represented by column name enclosed by curly brackets.
SQL template
| Code Block | ||||
|---|---|---|---|---|
| ||||
...
<select-sql>select c from CarrierSlaEntity c where c.name = {name} and c.carrier.name = {carrier}</select-sql>
...
<column-descriptor>
<column-index>0</column-index>
<field-type>FIELD</field-type>
<name>name</name>
<value-regex>(.{0,255})(.*)</value-regex>
<value-regex-group>1</value-regex-group>
</column-descriptor>
...
<column-descriptor>
<column-index>10</column-index>
<field-type>FK_FIELD</field-type>
<entity-type>org.yes.cart.domain.entity.Carrier</entity-type>
<name>carrier</name>
<value-regex>(.{0,255})(.*)</value-regex>
<value-regex-group>1</value-regex-group>
<lookup-query>select c from CarrierEntity c where c.name = {carrier}</lookup-query>
</column-descriptor> |
...
Each column under specified index is bound to property on the target entity specified by "name". Binding type is defined by the "field-type" configuration, which will set strategy for processing data in a given column.
| field-type | Description |
|---|---|
| FIELD | Single Value field (Also used as PK if has lookup query to check for update entities). |
| FK_FIELD | Foreign key field. (Uses look up queries to look up parent objects). |
| SLAVE_INLINE_FIELD | Defines sub tuple which uses various columns to populate its object. This kind of field uses sub descriptor to define sub tuple columns. |
| SLAVE_TUPLE_FIELD | Defines sub tuple which is encoded fully inside current field and has no access to other fields. This kind of field uses sub descriptor to define sub tuple columns. |
Data types
All CSV data is assumed to be text and to give import descriptor hint for other data types "data-type" property can be specified.
| data-type | Description |
|---|---|
| STRING | java.lang.String |
| BOOLEAN | java.lang.Boolean |
| LONG | java.lang.Long |
| INT | java.lang.Integer |
| DECIMAL | java.math.BigDecimal |
| DATETIME | Date value. Default date format is: "yyyy-MM-dd hh:mm:ss". For example: 2014-01-24 16:54:00 is 24th January 2014 16:54 |
Note that all decimals must be of type java.math.BigDecimal as rounding errors in Double and Float may alter original value. Hence those low precision java types are not supported by the system.
...
Anatomy of image import descriptor
Image descriptors are somewhat specialised and contain far less configurations.
brandimages.xml import descriptor
| Code Block | ||||
|---|---|---|---|---|
| ||||
<import-descriptor xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://www.yes-cart.org/schema/import-descriptor.xsd">
<entity-type>IMAGE</entity-type>
<import-file-descriptor>
<file-encoding>UTF-8</file-encoding>
<file-name-mask>^(.*)_([.[Cookbook bulk import basics^_]]*)_[a-z](_[a-z]{2})?\.(jpe?g|png)$</file-name-mask>
<ignore-first-line>false</ignore-first-line>
<column-delimiter>;</column-delimiter>
<text-qualifier>"</text-qualifier>
</import-file-descriptor>
<select-sql>/imgvault/brand/</select-sql>
<import-columns>
<column-descriptor>
<column-index>0</column-index>
<field-type>FIELD</field-type>
<data-type>STRING</data-type>
<name>description</name>
<value-constant>This is a brand image import</value-constant>
</column-descriptor>
</import-columns>
</import-descriptor> |
...
"select-sql" defined the type of the image data object in this instance "/imgvault/brand/" which is Brand entity.
| select-sql | Image entity type |
|---|---|
| /imgvault/brand/ | brands |
| /imgvault/category/ | master catalog categories and content |
| /imgvault/product/ | products and SKU |
| /imgvault/shop/ | shop instance |
The column 0 definition is purely for readability purposes and is not used by the image service. All data objects' codes are inferred from the image file name as explained in the bulk import article.
...