@isoftdata/universal-object-htp-utility - npm Package Compare versions

Comparing version 3.20.0 to 3.20.1

pnpm-workspace.yaml

+allowBuilds:
+  esbuild: true

tests/attachExistingImageListIds.test.ts

+import assert from 'assert'
+import { attachExistingImageListIds } from '../inventoryFile/helpers.js'
+
+// attachExistingImageListIds answers the question:
+//   Given the photos attached to a part in the universal object (incoming)
+//   and the photos currently stored in HTP's partimagelist table (existing),
+//   what should we hand to the ON DUPLICATE KEY UPDATE upsert so that:
+//     - a photo already in HTP gets its rank UPDATED (not duplicated), and
+//     - a genuinely new photo gets INSERTED?
+//
+// partimagelist's primary key is the auto-increment partimagelistid. The upsert only
+// collides-and-updates when the inserted row carries that id. So for rows that already
+// exist we must attach the existing partimagelistid; new rows get null so MySQL
+// auto-increments a fresh id. Rows are matched on (productcode, inventoryid, fileid).
+
+type ConvertedImageListRow = {
+    productcode: number
+    inventoryid: number
+    fileid: number
+    imagelocation: string
+    rank: number
+}
+
+type DbImageListRow = ConvertedImageListRow & {
+    partimagelistid: number
+    dirty: 'False' | 'True'
+}
+
+const PRODUCT = 1
+const intakeManifoldId = 5001
+
+const frontViewFileId = 201
+const sideViewFileId = 202
+const castingNumberFileId = 203
+
+const frontView: ConvertedImageListRow = {
+    productcode: PRODUCT, inventoryid: intakeManifoldId, fileid: frontViewFileId,
+    imagelocation: `&productcode=${PRODUCT}&id=${frontViewFileId}`, rank: 1
+}
+const sideView: ConvertedImageListRow = {
+    productcode: PRODUCT, inventoryid: intakeManifoldId, fileid: sideViewFileId,
+    imagelocation: `&productcode=${PRODUCT}&id=${sideViewFileId}`, rank: 2
+}
+const castingNumber: ConvertedImageListRow = {
+    productcode: PRODUCT, inventoryid: intakeManifoldId, fileid: castingNumberFileId,
+    imagelocation: `&productcode=${PRODUCT}&id=${castingNumberFileId}`, rank: 3
+}
+
+const frontViewInDb: DbImageListRow     = { ...frontView,     partimagelistid: 2001, dirty: 'False' }
+const sideViewInDb: DbImageListRow      = { ...sideView,      partimagelistid: 2002, dirty: 'False' }
+const castingNumberInDb: DbImageListRow = { ...castingNumber, partimagelistid: 2003, dirty: 'False' }
+
+// ==== Tests ====
+
+// This is the bug this function exists to fix:
+// the front view's rank changed (1 -> 5) but the photo already exists in HTP.
+// It must carry the existing partimagelistid so the upsert UPDATES rather than skips or duplicates.
+{
+    const frontViewReranked: ConvertedImageListRow = { ...frontView, rank: 5 }
+    const rows = attachExistingImageListIds([frontViewReranked], [frontViewInDb])
+
+    assert.strictEqual(rows.length, 1)
+    assert.strictEqual(rows[0].partimagelistid, 2001, 'existing PK must be attached so ON DUPLICATE KEY UPDATE fires')
+    assert.strictEqual(rows[0].rank, 5, 'the new rank must be carried through to the upsert')
+    console.log('PASS: rank change on an existing photo carries the existing partimagelistid')
+}
+
+// A genuinely new photo has no matching DB row, so it gets null and MySQL auto-increments.
+{
+    const rows = attachExistingImageListIds([castingNumber], [frontViewInDb, sideViewInDb])
+
+    assert.strictEqual(rows.length, 1)
+    assert.strictEqual(rows[0].partimagelistid, null, 'new photo gets null so the PK auto-increments')
+    assert.deepStrictEqual(
+        { ...rows[0] }, { partimagelistid: null, ...castingNumber },
+        'new row is unchanged apart from the null id'
+    )
+    console.log('PASS: new photo gets a null partimagelistid')
+}
+
+// Mixed batch: existing rows keep their ids, new rows get null, order is preserved.
+{
+    const rows = attachExistingImageListIds(
+        [frontView, sideView, castingNumber],   // castingNumber is new
+        [frontViewInDb, sideViewInDb]
+    )
+
+    assert.deepStrictEqual(
+        rows.map(row => row.partimagelistid),
+        [2001, 2002, null],
+        'each incoming row is paired with its existing PK, or null when new'
+    )
+    console.log('PASS: mixed batch attaches ids per-row and preserves order')
+}
+
+// Identity is (productcode, inventoryid, fileid): the same fileid on a different part
+// must NOT borrow the other part's partimagelistid.
+{
+    const axleId = 6001
+    const frontViewOnAxle: ConvertedImageListRow = {
+        productcode: PRODUCT, inventoryid: axleId, fileid: frontViewFileId,
+        imagelocation: `&productcode=${PRODUCT}&id=${frontViewFileId}`, rank: 1
+    }
+    const rows = attachExistingImageListIds([frontViewOnAxle], [frontViewInDb])
+
+    assert.strictEqual(rows[0].partimagelistid, null, 'same fileid on a different inventoryid is a distinct row')
+    console.log('PASS: same fileid on a different part does not reuse the other row\'s PK')
+}
+
+// Empty incoming yields empty output (guards the callers length check).
+{
+    const rows = attachExistingImageListIds([], [frontViewInDb])
+    assert.strictEqual(rows.length, 0)
+    console.log('PASS: empty incoming yields empty output')
+}
+
+console.log('\nAll attachExistingImageListIds tests passed')

dist/inventoryFile/helpers.d.ts

@@ -64,2 +64,10 @@ type FluffedAttachmentBase = {
 };
+export declare function attachExistingImageListIds<TExisting extends {
+    partimagelistid: number;
+    productcode: number;
+    inventoryid: number;
+    fileid: number | null;
+}>(incomingRows: ConvertedImageListRow[], existingRows: TExisting[]): (ConvertedImageListRow & {
+    partimagelistid: number | null;
+})[];
 type ConvertedVideoListRow = {
@@ -66,0 +74,0 @@ productcode: number;

dist/inventoryFile/helpers.js

@@ -71,8 +71,23 @@ // Only adds inventoryid and productcode to attachment rows
 }
+// Identity of an image on a part: (productcode, inventoryid, fileid).
+// Shared by the partition and id-attach helpers so both agree on what "the same image" means.
+const buildImageListKey = (row) => `${row.productcode}|${row.inventoryid}|${row.fileid}`;
 // Wrapper for partimagelist rows.
-// The composite key is (productcode, inventoryid, fileid) 
+// The composite key is (productcode, inventoryid, fileid)
 // so fileid is sufficient to identify a unique image on a part.
 export function partitionImageListRows(incomingRows, existingRows) {
-    return partitionAttachmentRows(incomingRows, existingRows, row => `${row.productcode}|${row.inventoryid}|${row.fileid}`, row => `${row.productcode}|${row.inventoryid}|${row.fileid}`);
+    return partitionAttachmentRows(incomingRows, existingRows, buildImageListKey, buildImageListKey);
 }
+// partimagelist's primary key is the auto-increment partimagelistid, so ON DUPLICATE KEY UPDATE
+// only fires when the inserted row carries that id. This attaches the existing row's
+// partimagelistid to each incoming row that already exists in the DB (matched on the composite
+// key), so the upsert collides on the PK and updates rank instead of inserting a duplicate.
+// Genuinely new rows get partimagelistid: null so MySQL auto-increments a fresh id.
+export function attachExistingImageListIds(incomingRows, existingRows) {
+    const idByKey = new Map(existingRows.map(row => [buildImageListKey(row), row.partimagelistid]));
+    return incomingRows.map(row => ({
+        partimagelistid: idByKey.get(buildImageListKey(row)) ?? null,
+        ...row,
+    }));
+}
 // Wrapper for partvideolist rows.
@@ -79,0 +94,0 @@ // The composite key is (productcode, store, partnum, videoid)

dist/inventoryFile/queries.js

 import { query, multiRowInsertQuery, write } from '../shared.js';
-import { convertManyInventoryFileRows, convertManyInventoryImageRows, convertManyPartVideoListRows, partitionFileListRows, partitionImageListRows, partitionVideoListRows } from './helpers.js';
+import { attachExistingImageListIds, convertManyInventoryFileRows, convertManyInventoryImageRows, convertManyPartVideoListRows, partitionFileListRows, partitionImageListRows, partitionVideoListRows } from './helpers.js';
 // ==== Delete Query Functions ====
@@ -195,4 +195,7 @@ // Called when an inventory row is deleted
     const convertedRows = publicRows.map(row => convertManyInventoryImageRows(row));
-    // Single pass: new rows to insert, stale rows to delete
-    const { toInsert, toDelete } = partitionImageListRows(convertedRows, existingRows);
+    // Single pass: identify stale rows to delete. We don't use toInsert here because
+    // the upsert below is idempotent (ON DUPLICATE KEY UPDATE), so every public row is
+    // sent through it — this also lets rank changes on already-existing rows be written,
+    // which a key-existence check alone would skip.
+    const { toDelete } = partitionImageListRows(convertedRows, existingRows);
     if (toDelete.length) {
@@ -210,4 +213,7 @@ const deleteRows = toDelete.map(row => ([row.productcode, row.inventoryid, row.fileid]));
     }
-    if (toInsert.length) {
-        const upsertResponse = await partImageListMultiRowUpsertQuery(htpConnection, toInsert);
+    // Upsert all public rows: attach the existing partimagelistid (PK) to rows that already
+    // exist so the upsert collides on the PK and updates rank; new rows carry null and insert.
+    if (convertedRows.length) {
+        const rowsToUpsert = attachExistingImageListIds(convertedRows, existingRows);
+        const upsertResponse = await partImageListMultiRowUpsertQuery(htpConnection, rowsToUpsert);
         responses.push(`partimagelist affected rows: ${upsertResponse?.affectedRows}`);
@@ -214,0 +220,0 @@ }

inventoryFile/helpers.ts

@@ -125,4 +125,9 @@
 
+// Identity of an image on a part: (productcode, inventoryid, fileid).
+// Shared by the partition and id-attach helpers so both agree on what "the same image" means.
+const buildImageListKey = (row: { productcode: number, inventoryid: number, fileid: number | null }): string =>
+    `${row.productcode}|${row.inventoryid}|${row.fileid}`
+
 // Wrapper for partimagelist rows.
-// The composite key is (productcode, inventoryid, fileid) 
+// The composite key is (productcode, inventoryid, fileid)
 // so fileid is sufficient to identify a unique image on a part.
@@ -133,10 +138,26 @@ export function partitionImageListRows<TExisting extends { productcode: number, inventoryid: number, fileid: number | null }>(
 ): { toInsert: ConvertedImageListRow[]; toDelete: TExisting[] } {
-    return partitionAttachmentRows(
+    return partitionAttachmentRows<ConvertedImageListRow, TExisting>(
         incomingRows,
         existingRows,
-        row => `${row.productcode}|${row.inventoryid}|${row.fileid}`,
-        row => `${row.productcode}|${row.inventoryid}|${row.fileid}`
+        buildImageListKey,
+        buildImageListKey
     )
 }
 
+// partimagelist's primary key is the auto-increment partimagelistid, so ON DUPLICATE KEY UPDATE
+// only fires when the inserted row carries that id. This attaches the existing row's
+// partimagelistid to each incoming row that already exists in the DB (matched on the composite
+// key), so the upsert collides on the PK and updates rank instead of inserting a duplicate.
+// Genuinely new rows get partimagelistid: null so MySQL auto-increments a fresh id.
+export function attachExistingImageListIds<TExisting extends { partimagelistid: number, productcode: number, inventoryid: number, fileid: number | null }>(
+    incomingRows: ConvertedImageListRow[],
+    existingRows: TExisting[]
+): (ConvertedImageListRow & { partimagelistid: number | null })[] {
+    const idByKey = new Map(existingRows.map(row => [buildImageListKey(row), row.partimagelistid]))
+    return incomingRows.map(row => ({
+        partimagelistid: idByKey.get(buildImageListKey(row)) ?? null,
+        ...row,
+    }))
+}
+
 type ConvertedVideoListRow = {
@@ -143,0 +164,0 @@ productcode: number

inventoryFile/queries.ts

 import { query, multiRowInsertQuery, write } from '../shared.js'
-import  { convertManyInventoryFileRows, convertManyInventoryImageRows, convertManyPartVideoListRows, partitionFileListRows, partitionImageListRows, partitionVideoListRows } from './helpers.js'
+import  { attachExistingImageListIds, convertManyInventoryFileRows, convertManyInventoryImageRows, convertManyPartVideoListRows, partitionFileListRows, partitionImageListRows, partitionVideoListRows } from './helpers.js'
 
@@ -201,4 +201,7 @@ // ==== Delete Query Functions ====
 
-    // Single pass: new rows to insert, stale rows to delete
-    const { toInsert, toDelete } = partitionImageListRows(convertedRows, existingRows)
+    // Single pass: identify stale rows to delete. We don't use toInsert here because
+    // the upsert below is idempotent (ON DUPLICATE KEY UPDATE), so every public row is
+    // sent through it — this also lets rank changes on already-existing rows be written,
+    // which a key-existence check alone would skip.
+    const { toDelete } = partitionImageListRows(convertedRows, existingRows)
 
@@ -217,4 +220,7 @@ if (toDelete.length) {
     }
-    if (toInsert.length) {
-        const upsertResponse = await partImageListMultiRowUpsertQuery(htpConnection, toInsert)
+    // Upsert all public rows: attach the existing partimagelistid (PK) to rows that already
+    // exist so the upsert collides on the PK and updates rank; new rows carry null and insert.
+    if (convertedRows.length) {
+        const rowsToUpsert = attachExistingImageListIds(convertedRows, existingRows)
+        const upsertResponse = await partImageListMultiRowUpsertQuery(htpConnection, rowsToUpsert)
         responses.push(`partimagelist affected rows: ${upsertResponse?.affectedRows}`)
@@ -221,0 +227,0 @@ } else {

package.json

 {
   "name": "@isoftdata/universal-object-htp-utility",
-  "version": "3.20.0",
+  "version": "3.20.1",
   "description": "Functions to convert universal objects to htp schema",
   "main": "dist/index.js",
   "types": "dist/index.d.ts",
+  "scripts": {
+    "build": "tsc",
+    "prepublishOnly": "npm run build",
+    "test": "node --import tsx/esm --test tests/*.test.ts"
+  },
   "type": "module",
@@ -20,7 +25,3 @@ "author": "",
     "typescript": "^5.9.3"
-  },
-  "scripts": {
-    "build": "tsc",
-    "test": "node --import tsx/esm --test tests/*.test.ts"
   }
-}
+}

No alert changes

Improved metrics

Total package byte prevSize

342393

2.65%

Number of package files

95

2.15%

Lines of code

7668

2.04%

No dependency changes