Skip to content

Database Schema Documentation

Overview

The Essert.MF system uses 8 separate MySQL/MariaDB databases to organize data by domain: - db_productparameter - Product parameter configurations (63 tables) - db_process - Manufacturing process data (1 table) - db_statistics - Measurement and statistics data (23 tables) - db_changelogs - Historical change tracking (70 tables) - db_essert - Core system configuration (3 tables) - db_robots - Robot control and positioning (5 tables) - db_wpc - Work Piece Carrier management (4 tables) - db_systemparameter - System-level machine parameters (8 tables)

⚠️ CRITICAL: UID AUTO_INCREMENT Pattern

Database Constraint

ALL primary key UID columns across ALL databases use AUTO_INCREMENT.

This is a database-level constraint that must be respected in all repository code.

-- Example from tbl_th_process
CREATE TABLE `tbl_th_process` (
  `UID` bigint(20) NOT NULL AUTO_INCREMENT,
  `ProcessName` varchar(45) NOT NULL DEFAULT '',
  `Timestamp` datetime NOT NULL DEFAULT '2022-01-01 12:00:00',
  `Creator` varchar(30) NOT NULL DEFAULT '',
  `CRC` bigint(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (`UID`),
  UNIQUE KEY `UID_UNIQUE` (`UID`)
) ENGINE=InnoDB AUTO_INCREMENT=95;

Affected Tables (All Databases)

db_productparameter: (63 tables total) - TH (TrayHandling): tbl_th_process, tbl_th_mapping, tbl_th_shape, tbl_th_positiondata, tbl_th_shiftdata, tbl_th_mapping_positiondata, tbl_th_mapping_shiftdata - SC (Steep Conveyor): tbl_sc_setup, tbl_sc_mapping - EGP (Schunk EGP Gripper): tbl_egp_setup, tbl_egp_mapping, tbl_egp_movements, tbl_egp_positionname - EGK (Schunk EGK Gripper): tbl_egk_setup, tbl_egk_mapping, tbl_egk_movements, tbl_egk_positionname - RFL (Refill Logic): tbl_rfl_setup, tbl_rfl_mapping - XGX (Keyence XGX Vision): tbl_xgx_setup, tbl_xgx_mapping - IV3 (Keyence IV3 Vision): tbl_iv3_setup, tbl_iv3_mapping - Kann (KocoMotion Stepper): tbl_kann_setup, tbl_kann_mapping, tbl_kann_movements, tbl_kann_positionname - HP (RNA Vibration Hopper): tbl_hp_setup, tbl_hp_mapping - ES (Essert Shaker): tbl_es_clips, tbl_es_mapping, tbl_es_sequences - VS (Vision Sequences): tbl_vs_clips, tbl_vs_mapping, tbl_vs_sequences - CMMT (FESTO Servo CMMT-AS): tbl_cmmt_setup, tbl_cmmt_mapping, tbl_cmmt_movements, tbl_cmmt_positionname - CL (Circulation Logic): tbl_cl_setup, tbl_cl_mapping - EL (Essert Lighting): tbl_el_setup, tbl_el_mapping - Lighting: tbl_lighting_setup, tbl_lighting_mapping - GJ (Gripperjaws): tbl_gj_setup, tbl_gj_mapping - DistanceSensor: tbl_distancesensor_setup, tbl_distancesensor_mapping, tbl_distancesensor_distances, tbl_distancesensor_distancename - Pickit3D (3D Vision): tbl_pickit3d_setup, tbl_pickit3d_mapping - SchmalzSCPSi (Vacuum): tbl_schmalzscpsi_setup, tbl_schmalzscpsi_mapping - MFConfig: tbl_mfconfig_config, tbl_mfconfig_mapping, tbl_mfconfig_setup - MFProcess: tbl_mfprocess_mapping - Process: tbl_process_mapping - Product: tbl_productnames, tbl_versions - Unspecified: tbl_unspecified

db_wpc: (4 tables total) - tbl_wpc, tbl_mapping_wpc, tbl_mapping_version, tbl_groups

db_statistics: (23 tables total) - Measurement tables: tbl_camera, tbl_cmmt, tbl_consumption, tbl_cycletime, tbl_egp, tbl_force, tbl_kann, tbl_length, tbl_posunit, tbl_pressure, tbl_shock, tbl_temperature, tbl_valve, tbl_weight - Message/tracking tables: tbl_currentmessages, tbl_listignoremessages, tbl_listmessageclass, tbl_listmessages, tbl_listnokreasons, tbl_messagearchive - List tables: tbl_listordernumbers, tbl_listserialnumbers, tbl_listprocesses

db_robots: (5 tables total) - tbl_limits, tbl_listcoordinatesystems, tbl_listtools, tbl_masterdata, tbl_positiondata

db_essert: (3 tables total) - tbl_arwid, tbl_variables, tbl_version

db_process: (1 table) - tbl_processdata

db_changelogs: (70 tables total) - Product parameter changelogs (tbl_pp_*): Mirrors of all db_productparameter tables with tbl_pp_ prefix (57 tables) - Robot changelogs: tbl_robots_limits, tbl_robots_masterdata, tbl_robots_positiondata - System parameter changelogs (tbl_sp_*): tbl_sp_assemblyunit, tbl_sp_cmmt, tbl_sp_posaxis, tbl_sp_positioningunit, tbl_sp_robots, tbl_sp_traystacker, tbl_sp_unspecified, tbl_sp_wpcopener - WPC changelogs: tbl_wpc_mapping_version, tbl_wpc_mapping_wpc, tbl_wpc_wpc

db_systemparameter: (8 tables total) - System-level machine configuration - tbl_assemblyunit - Assembly unit parameters (reference direction, port, end position, speed/force limits) - tbl_cmmt - FESTO CMMT servo system parameters (scaling, position limits, speeds, reference offset) - tbl_posaxis - Positioning axis parameters (scaling, position limits, speeds, reference offset) - tbl_positioningunit - Positioning unit parameters (open/stop/index positions, velocities, holding torques) - tbl_robots - Robot speed parameters (fast/slow speeds) - tbl_traystacker - Tray stacker parameters (stack height limits, tray count, cart level correction) - tbl_unspecified - Dynamic project-specific system parameters - tbl_wpcopener - WPC opener parameters (open/release/pre-positions, tolerance)

Essentially: If a table has a UID column, it uses AUTO_INCREMENT.

Correct Implementation Pattern

✅ CORRECT: Let Database Generate UID

public virtual async Task<long> CreateSetupWithCrcAsync(TSetupDto setup)
{
    var setupEntity = Mapper.Map<TSetup>(setup);

    // Set Uid = 0 or leave default (database ignores and generates new UID)
    var entry = ProductContext.Entry(setupEntity);
    entry.Property("Timestamp").CurrentValue = DateTime.UtcNow;

    // Phase 1: Insert to trigger AUTO_INCREMENT
    SetupDbSet.Add(setupEntity);
    await ProductContext.SaveChangesAsync();

    // Phase 2: Read back the auto-generated UID
    var uid = (long)entry.Property("Uid").CurrentValue;

    // Phase 3: Calculate CRC with actual UID
    var crc = CalculateSetupCrc(setupEntity);
    entry.Property("Crc").CurrentValue = (long)crc;
    await ProductContext.SaveChangesAsync();

    return uid;
}

❌ WRONG: Manual UID Calculation

// DO NOT DO THIS - Has race conditions!
public virtual async Task<long> CreateSetupWithCrcAsync(TSetupDto setup)
{
    // ❌ Race condition: Two concurrent requests can get same maxUid
    var maxUid = await SetupDbSet.MaxAsync(s => (long?)s.Uid) ?? 0;
    var newUid = maxUid + 1;

    setupEntity.Uid = newUid;  // ❌ Fights against AUTO_INCREMENT

    SetupDbSet.Add(setupEntity);
    await ProductContext.SaveChangesAsync();

    return newUid;  // ❌ May not match actual database UID if AUTO_INCREMENT counter is different
}

Why Manual UID Calculation Fails

  1. Race Condition: Between MaxAsync() and SaveChangesAsync(), another request can insert with the same UID
  2. AUTO_INCREMENT Counter Desync: Database has its own counter that may be ahead of MAX(UID) if rows were deleted
  3. Performance: Extra MAX(UID) query adds latency to every insert
  4. Duplicate Key Errors: Database rejects insert if manually-set UID conflicts
  5. Complexity: Unnecessary code that duplicates built-in database functionality

Why Two-Phase Insert is Required

CRC calculation includes the UID in the hash, so we can't calculate CRC before knowing the UID:

// CRC calculation example (from ThMappingRepository)
protected override uint CalculateSetupCrc(ThProcess process)
{
    var content = CrcService.BuildCrcContent("EsrtRbtx-",
        process.Uid,              // ← UID is part of CRC calculation
        process.ProcessName,
        process.RowMirroring,
        // ... other fields
        process.Timestamp,
        process.Creator);
    return CrcService.CalculateCrc32(content);
}

Process: 1. Insert without CRC → Database generates UID 2. Read UID back from entity 3. Calculate CRC using actual UID 4. Update CRC field

Benefits of AUTO_INCREMENT

✅ Thread-Safe: Database handles concurrency automatically ✅ Performant: No extra MAX query needed ✅ Reliable: Single source of truth for UID generation ✅ Simple: Less code, fewer bugs ✅ Atomic: UID generation and insert happen in one database operation

Common Column Patterns

All tables follow similar patterns:

`UID` bigint(20) NOT NULL AUTO_INCREMENT        -- Primary key (auto-generated)
`Timestamp` datetime NOT NULL                   -- Created/modified timestamp
`Creator` varchar(30) NOT NULL DEFAULT ''       -- User/system that created record
`CRC` bigint(20) NOT NULL DEFAULT 0            -- Data integrity checksum

CRC Calculation Pattern

CRC is calculated AFTER UID is known and includes: - UID (auto-generated) - All business data fields - Timestamp - Creator

This ensures data integrity and tamper detection.

Testing Considerations

When writing tests: - ✅ Let repository create entities (UIDs auto-generated) - ✅ Use actual database for integration tests - ❌ Don't mock UID generation - ❌ Don't hardcode expected UIDs

// Good: Let repository handle UID
var setupUid = await repository.CreateSetupWithCrcAsync(setupDto);
Assert.That(setupUid > 0);  // Verify UID was generated

// Bad: Hardcoded UID expectations
var setupUid = await repository.CreateSetupWithCrcAsync(setupDto);
Assert.That(setupUid == 42);  // ❌ Fragile - depends on database state

References

  • Implementation: Essert.MF.Infrastructure/Repositories/ParameterMappingRepository.cs
  • Architecture: CLAUDE.md - "UID AUTO_INCREMENT Pattern" section
  • Testing: Essert.MF.API.Rest.Tests/Controllers/*ControllerTests.cs

History

2025-01-10: Refactored from manual UID calculation to AUTO_INCREMENT pattern - Fixed race conditions in concurrent requests - Improved performance (removed MAX query) - Simplified repository code - All 19 TH parameter tests passing with new approach