Unique identifier matching internals

This is the developer companion to Choosing your unique identifier. It documents how Super Speedy Imports turns the configured identifier into a match key, the SQL match-existing runs, the checks that stop a run before a row can attach to the wrong item, and how to inspect what a run matched.

Two halves: the value and the match key

Every identifier has two independent halves.

The template constructor resolves both in SuperSpeedyImports_ImportTemplate::configureUniqueIdentity() (import-template-abstract.php). There are three matching paths.

Matching therefore runs against data the items already carry. A product created in wp-admin, by WooCommerce’s importer or by another import matches on its SKU, because the match key is _sku itself.

Identity configuration

The matching section of the import saves the choice in the import’s additional options as unique_id_components, a list of type:key tokens where the type is post, meta or tax. Post ID matching also saves unique_id_source, the column holding the IDs.

The presets each template offers:

Every edition also offers Post ID. The paid edition adds Custom, any combination of post fields, metadata keys and taxonomies. The metadata keys the Custom builder offers for products are SKU and the common GTIN keys that exist on the site; add more with superspeedyimports_uid_postmeta_whitelist ($keys, $template):

add_filter('superspeedyimports_uid_postmeta_whitelist', function ($keys, $template) {
    $keys['_supplier_code'] = 'Supplier code';
    return $keys;
}, 10, 2);

Older saved shapes are still read:

• unique_id_type = postmeta with a key in unique_id_value is the single metadata field with that key.
• unique_id_type = field with a column name in unique_id_value is the metadata key that column is mapped to. In the paid edition, a column mapped to no metadata key is staged and matched against the _ssi_unique_item_id key, which only items written by an import carry. The Lite edition refuses it.
• A product import with no identifier saved and _sku mapped matches on _sku.
• unique_id_type = function stops the run with “PHP-function unique identifiers are no longer supported”. A value computed from a row cannot be computed for an item already on the site, so it could only ever match items an import created.

Every metadata key or taxonomy in an identity must also be mapped in the import, or the run stops before loading, naming the field. Post ID must be chosen on its own; combined with another field it is refused, because an ID is already unique.

Loading the value: load-csv

load-csv stages each row into the batch table wp_super_speedy_imports_batch_<import_id>, with the identifier’s value in _ssi_unique_item_id. The table takes the collation of wp_posts.

For the native-key and Post ID paths the table carries two keys on the identifier:

• _ssi_uid_key, a generated column holding SHA1(_ssi_unique_item_id), with a UNIQUE index. Uniqueness covers the whole identifier, however long it is.
• ssi_uid, a plain index on the first 100 characters, used by the lookups and joins.

Identifiers are compared in full: two product URLs that differ only after their 100th character are two items. A row the table refuses although no other row holds its identifier stops the run with Import aborted — N row(s) could not be staged for import., naming the row and identifier.

Blank identifiers

blank_unique_id_policy decides what happens to a row whose identifier cell is blank. Only an empty cell is blank: a cell holding 0 is a value, so a SKU of exactly 0 is a valid identifier. The import settings label it Rows with a blank unique identifier (Abort import or Skip blank rows and continue); the matching section on the mapping screen shows the same setting as If a row has a blank match value:.

• abort (the default): the row is recorded as a fatal validation error, and at the end of load-csv the run stops with Import aborted — N row(s) have a blank unique identifier., listing up to ten rows. Nothing has been written to the site.
• skip: the row is not staged. It is recorded as a warning, “Blank unique identifier — row skipped”, which the completion screen’s Issues section and the error report list.

Under Post ID matching, a value that is not a positive whole number in plain digits (no spaces, leading zeros, fractions or exponents, at most 18446744073709551615) is a fatal validation error too, and the run stops with Import aborted — N row(s) have invalid Post IDs.

A composite identity is checked after hashing instead: a row whose every component is blank hashes to the one “all blank” value for that identity, and the same policy aborts the run or deletes those rows from the batch.

The same identifier on two rows

A unique identifier must identify exactly one item, so a repeat inside the file stops the run at the end of load-csv, before anything is written. superspeedyimports_abort_on_duplicate_identifiers() (functions/duplicate-identifiers.php) reads two sources:

1. Rows the batch table’s unique key refused, each recorded as a warning Duplicate identifier '<value>'.
2. A GROUP BY _ssi_unique_item_id over the batch, for duplicates that loaded.

The message lists the colliding identifiers. When it can read both rows from the file, it names a column whose value differs between them, preferring a mapped taxonomy column such as a colour or size, as the field to add to a composite identity.

The GROUP BY uses the batch table’s collation, while the unique key compares SHA1 bytes. On WordPress’s default case-insensitive collation, ABC and abc in one file therefore both load, then the probe stops the run with Identifier 'ABC' appears on 2 rows.

A composite identity runs its own check on the hashes, in prepareCompositeIdentity(): two rows with the same hash stop the run with Import aborted — the unique item identifier is not unique within your CSV.

Matching: match-existing

Native key

UPDATE wp_super_speedy_imports_batch_<id> it
INNER JOIN wp_postmeta pm ON it._ssi_unique_item_id = pm.meta_value AND pm.meta_key = '<match key>'
INNER JOIN wp_posts p ON pm.post_id = p.id
SET it.post_id = pm.post_id, it.is_update = 1
WHERE it.post_id IS NULL

Before the UPDATE, superspeedyimports_abort_on_ambiguous_existing_identifiers() runs the same join grouped by value. If any value matches more than one item, the run stops with Import aborted — your site already contains items that SHARE an identifier this import matches on ('<match key>'). and lists up to six post IDs per value. Without that check the UPDATE would bind the row to whichever item the database happened to read first.

Three properties of this join matter when a row matches something unexpected:

• It is not scoped to the import or the post type. Any item on the site carrying that key and value is a candidate, including items another import created and items of another post type. For a product import, variations match by their own SKU.
• It ignores post status. Drafts, private and trashed items match.
• The comparison follows the collation. On a case-insensitive collation abc-123 matches ABC-123. Trailing spaces and leading zeros are part of the value: 00123 and 123 are different identifiers.

Post ID

matchPostIds() joins the batch value, cast to an unsigned integer, to wp_posts.ID, restricted to the import’s post type (for products, product and product_variation). A matched row takes the item’s post type and parent when the import does not map them, so a partial update never turns a variation into a parent. An ID that matches nothing is a new row and follows the import mode; a created item receives a new ID, not the one in the file.

Composite

prepareCompositeIdentity() (called at the start of match-existing) builds the hash on both sides:

SHA1(CONCAT_WS(0x1f, '<signature>', <component value>, ...))

• The signature lists the components (for example post:post_title|tax:category|), so a hash written under a different identity can never equal one written under this one.
• Components are sorted, so their order in the identity does not change the hash.
• A post field or metadata value is the value itself, blank when missing. A taxonomy is the sorted, comma-joined term_taxonomy_ids; for a hierarchical taxonomy, only the deepest assigned terms.
• On the batch side the hash goes into _ssi_uid_hash and replaces _ssi_unique_item_id. On the site side it is calculated from each candidate item’s current values into the run’s own table, wp_ssi_uid_candidates_<import id>, and nothing is written onto the items. Candidates are the items that share the first post-field or metadata value of the identity with a row of the file (a taxonomy-only identity scans the whole post type), of the import’s post type (for products, product and product_variation), in every status except auto-draft, so trashed items match, as with SKU.

Before the match, two rows sharing a hash stop the run, and so does a hash matching more than one candidate (Import aborted — the unique item identifier matches more than one existing item), including a live item and a trashed one with the same title. Each row then binds to the one candidate with its hash, and the table is dropped.

Because the hash is taken over the bytes, a composite identity is case-sensitive and whitespace-sensitive whatever the collation: Blue Shirt and blue shirt are two identities.

Composite identities add three run-time requirements:

• match-existing must be in the run. A stage list without it is refused before anything runs.
• An identity containing a taxonomy needs import-taxonomies, which resolves the terms. Only update prices & other item details and Only refresh images skip it, so they refuse such an identity, as does a command-line stage list without it.
• A WooCommerce import that groups variations into synthesised parents cannot use a composite identity. A variable-product import with real parent rows can, provided _sku is mapped or Parent is matched by is set, because variations find their parent through that field.

When insert-posts matches again

insert-posts repeats the match (with is_update = 0) before creating anything; for a composite identity it reads the _ssi_uid_hash postmeta that created and updated items carry. A row whose item appeared between match-existing and insert-posts, for example from a concurrent run, is then treated as existing and not created twice.

What the items carry afterwards

The metadata stages write the staged value to the item as _ssi_unique_item_id postmeta, alongside the ssi_updated tracking key holding the import’s ID, under the import mode’s rules for metadata. For the native-key path that is the same value as the match key (the SKU, for example); for a composite identity it is the hash, which is also in _ssi_uid_hash.

The native-key and Post ID paths never read _ssi_unique_item_id from the site (only the legacy unmapped-column shape above does), so changing the identity between runs does not orphan anything: a switch from SKU to GTIN matches every item that has a GTIN. A composite identity recalculates the hash from its candidates’ current values on every run, so an item whose title was edited on the site matches its new title, not the one it was imported with.

Diagnosing a wrong match or a duplicate

Each failure the checks above do not stop has a cause in the data.

The Data tab finds records that share or lack the matching value; Check existing data in an import’s matching section runs it for that import’s identity.

Inspecting a run

The batch table is kept after the run, so it records exactly what each row matched.

-- What did the last run match? post_id is the item, is_update = 1 means it was found by matching.
SELECT csv_row_number, _ssi_unique_item_id, post_id, is_update
FROM wp_super_speedy_imports_batch_<import_id>
ORDER BY csv_row_number;

-- Values shared by more than one item, for a native match key.
SELECT pm.meta_value, COUNT(DISTINCT pm.post_id) AS items, GROUP_CONCAT(pm.post_id) AS ids
FROM wp_postmeta pm
WHERE pm.meta_key = '_sku'
GROUP BY pm.meta_value
HAVING items > 1;

-- Which items did this import last create or update?
SELECT post_id FROM wp_postmeta WHERE meta_key = 'ssi_updated' AND meta_value = '<import_id>';

Rows refused for a blank or duplicate identifier are in wp_ssi_validation_errors_<import_id>, the same table the completion screen’s Issues section and Download error report read.

Further reading

Related products

Super Speedy ImportsEasy to use. Built for speed.

Written for Super Speedy Imports 3.63.0.