qbit-mobile-scanners/examples/echo/src/main/resources/db/echo-h2.sql

-- Copyright (c) 2026 Kingsrook, LLC. SPDX-License-Identifier: Apache-2.0
-- Host migration input, not automatically executed by metadata registration.
CREATE TABLE IF NOT EXISTS echo_run (
   id VARCHAR(36) PRIMARY KEY, actorId VARCHAR(200) NOT NULL, deviceId VARCHAR(36) NOT NULL,
   publicSessionId VARCHAR(36) NOT NULL, printerRecordId VARCHAR(200) NOT NULL, mediaId VARCHAR(200) NOT NULL,
   mediaRevision VARCHAR(64) NOT NULL DEFAULT '', widthMicrons INT NOT NULL DEFAULT 0, heightMicrons INT NOT NULL DEFAULT 0,
   dpiX INT NOT NULL DEFAULT 0, dpiY INT NOT NULL DEFAULT 0,
   state VARCHAR(30) NOT NULL, pendingEventId VARCHAR(36)
);
-- Preserving migration: the document chosen once at setup. Existing rows keep NULL and are never cleared.
ALTER TABLE echo_run ADD COLUMN IF NOT EXISTS templateId INT;
ALTER TABLE echo_run ADD COLUMN IF NOT EXISTS templateRevisionId INT;
CREATE TABLE IF NOT EXISTS echo_scan (
   id VARCHAR(64) PRIMARY KEY, runId VARCHAR(36) NOT NULL REFERENCES echo_run(id),
   eventId VARCHAR(36) NOT NULL, actorId VARCHAR(200) NOT NULL, deviceId VARCHAR(36) NOT NULL, mobileScanId VARCHAR(64) NOT NULL,
   barcode VARCHAR(4096) NOT NULL, symbology VARCHAR(200) NOT NULL, rendererSymbology VARCHAR(100) NOT NULL,
   scanSource VARCHAR(200) NOT NULL, capturedAt VARCHAR(40) NOT NULL, receivedAt VARCHAR(40) NOT NULL, state VARCHAR(30) NOT NULL,
   printJobId VARCHAR(200), UNIQUE(runId, eventId)
   ,templateId INT, templateRevisionId INT, format VARCHAR(10), topMicrons INT, rightMicrons INT, bottomMicrons INT, leftMicrons INT
);
-- Preserving migration: the scanner's AIM identifier (for example ]E0), when the capture carried one. Existing rows keep NULL
-- (no AIM was recorded) and are never rewritten. Other databases need the equivalent additive nullable VARCHAR(3) column.
ALTER TABLE echo_scan ADD COLUMN IF NOT EXISTS aimIdentifier VARCHAR(3);
-- Preserving migration: copies printed per scan, chosen once at setup. Existing runs keep exactly one copy (their prior behaviour).
-- Other databases need the equivalent additive INT NOT NULL DEFAULT 1 column.
ALTER TABLE echo_run ADD COLUMN IF NOT EXISTS copies INT NOT NULL DEFAULT 1;