ADR-005: iDempiere Migration Script Architecture

Status

Accepted

Context

Understanding iDempiere's migration script mechanism is essential for:

Proper CLI tool design that complements (not conflicts with) iDempiere workflows
Correct DDL generation that follows iDempiere conventions
Clear documentation of when to use CLI vs iDempiere native tools

This ADR documents how iDempiere generates and applies migration scripts, serving as architectural reference for the CLI.

iDempiere Migration Script Architecture

Overview

iDempiere uses a two-phase migration system:

┌─────────────────────────────────────────────────────────────────┐
│                    PHASE 1: GENERATION                          │
│  Developer makes AD changes → SQL intercepted → Script file     │
└─────────────────────────────────────────────────────────────────┘
                                ↓
┌─────────────────────────────────────────────────────────────────┐
│                    PHASE 2: APPLICATION                         │
│  RSync2DB.sh → Find pending scripts → Execute → Track in DB     │
└─────────────────────────────────────────────────────────────────┘

Phase 1: Migration Script Generation

1.1 Enabling Generation

Migration script logging is controlled by a session preference (not persisted):

Preferences Window → &quot;Log Migration Script&quot; checkbox
                   → &quot;Migration Script Comment&quot; (optional)

Key Point: This preference resets each session - developers must re-enable it when starting new development work.

1.2 SQL Interception Mechanism

When "Log Migration Script" is enabled, iDempiere intercepts SQL statements at the database layer:

┌──────────────────┐     ┌─────────────────────┐     ┌────────────────┐
│  AD Change       │ ──→ │  SQL Generation     │ ──→ │  DB Execution  │
│  (UI/Process)    │     │  (MColumn/MTable)   │     │                │
└──────────────────┘     └─────────────────────┘     └────────────────┘
                                   │
                                   ↓ (if logging enabled)
                         ┌─────────────────────┐
                         │  Migration Script   │
                         │  File Writer        │
                         └─────────────────────┘
                                   │
                                   ↓
                         ┌─────────────────────┐
                         │  migration/iXX.Xz/  │
                         │  postgresql/        │
                         │  YYYYMMDD_JIRA.sql  │
                         └─────────────────────┘

1.3 SQL Generation Classes

The actual SQL is generated by model classes:

Class	Method	Purpose
`MColumn`	`getSQLAdd()`	ALTER TABLE ADD COLUMN
`MColumn`	`getSQLModify()`	ALTER TABLE MODIFY COLUMN
`MColumn`	`getSQLDDL()`	Column definition (type + constraints)
`MColumn`	`getForeignKeyConstraintSql()`	FK constraint DDL
`MTable`	`getSQLCreate()`	CREATE TABLE with all columns

Source Files:

1.4 Script File Format

Generated migration scripts follow this structure:

-- Migration script registration (called by application)
SELECT register_migration_script(&#39;202312151430_IDEMPIERE-6000.sql&#39;) FROM dual;

-- Timestamp: Dec 15, 2023 2:30:45 PM CET
-- IDEMPIERE-6000 Add custom table for inventory tracking

INSERT INTO AD_Table (AD_Table_ID, AD_Client_ID, AD_Org_ID, ...)
VALUES (200350, 0, 0, ...);

-- Timestamp: Dec 15, 2023 2:30:46 PM CET
ALTER TABLE XX_InventoryTrack ADD COLUMN XX_Quantity NUMERIC(10,4);

-- ... more statements with timestamps

Key Elements:

register_migration_script() header - registers script in tracking table
Timestamp comments before each statement
JIRA ticket reference in filename and comments
Centralized IDs (not MAX+1)

1.5 File Naming Convention

YYYYMMDDHHMM_IDEMPIERE-XXXX_description.sql
│           │              │
│           │              └── Optional description
│           └── JIRA ticket number (ID allocation)
└── Timestamp (ensures ordering)

Examples:

202312151430_IDEMPIERE-6000.sql
202312151445_IDEMPIERE-6000_add_columns.sql
202401081200_IDEMPIERE-6100_fix_constraint.sql

1.6 Excluded Operations

Certain operations are NOT logged to migration scripts:

SELECT statements
UPDATE AD_Sequence (sequence management)
Translation table updates (handled separately)
~45 system/logging tables (AD_Session, AD_ChangeLog, etc.)

1.7 Centralized ID Management

For core iDempiere contributions, IDs must be allocated via JIRA:

┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│  Create JIRA    │ ──→ │  Request ID     │ ──→ │  Use Allocated  │
│  Ticket         │     │  Range          │     │  IDs in Script  │
└─────────────────┘     └─────────────────┘     └─────────────────┘

Why Centralized IDs?

Prevents ID conflicts when multiple developers contribute
Ensures consistent IDs across all installations
Required for translations, cross-references, data integrity
Migration scripts can be applied in any order without conflicts

Reference: Centralized ID Management

Phase 2: Migration Script Application

2.1 Discovery Process

Scripts are discovered by scanning the migration folder structure:

$IDEMPIERE_HOME/migration/
├── i10.0z/
│   ├── postgresql/
│   │   ├── 202301011200_IDEMPIERE-5500.sql
│   │   └── 202301021300_IDEMPIERE-5501.sql
│   └── oracle/
│       └── ...
├── i11.0z/
│   └── postgresql/
│       └── ...
└── processes_post_migration/
    └── ...

2.1.1 Local Migration Folder (Partners/Plugins)

iDempiere supports a local migration folder for partner customizations:

$IDEMPIERE_HOME/migration-local/
├── i1.0z/                      # Partner plugin version
│   ├── postgresql/
│   │   ├── 202412011200_PLUGIN-001_AddCustomTable.sql
│   │   └── 202412021300_PLUGIN-002_AddProcess.sql
│   └── oracle/
│       └── ...
└── processes_post_migration/
    └── ...

Key Differences:

Folder	Purpose	ID Management
`migration/`	iDempiere core scripts	Centralized IDs (JIRA)
`migration-local/`	Partner/plugin scripts	Local IDs (MAX+1)

CLI Default: The CLI generates scripts to migration/ by default, but partners should use migration-local/ to avoid conflicts with core scripts:

# Generate to local migration folder
idempiere-cli migration-script generate table XX_MyTable -i PLUGIN-001 -o migration-local

2.2 Application Methods

Method 1: RUN_SyncDBDev.sh (Development)

cd $IDEMPIERE_REPOSITORY
bash RUN_SyncDBDev.sh

Method 2: RSync2DB.sh (Production)

cd $IDEMPIERE_HOME/utils
./RSync2DB.sh

Method 3: Database Migration Window (UI)

Window ID-53071: Database Migration
Manual script selection and execution
Status monitoring

2.3 Execution Order

Scripts are applied alphabetically by filename, which is why timestamp-based naming is critical:

1. 202301011200_IDEMPIERE-5500.sql  (first - oldest timestamp)
2. 202301021300_IDEMPIERE-5501.sql  (second)
3. 202301031400_IDEMPIERE-5502.sql  (third - newest timestamp)

2.4 Tracking Table: ad_migrationscript

Applied scripts are tracked in the ad_migrationscript table:

CREATE TABLE ad_migrationscript (
    ad_migrationscript_id  NUMERIC(10,0) NOT NULL,
    ad_client_id           NUMERIC(10,0) NOT NULL,
    ad_org_id              NUMERIC(10,0) NOT NULL,
    isactive               CHAR(1) DEFAULT &#39;Y&#39;,
    created                TIMESTAMP NOT NULL,
    createdby              NUMERIC(10,0) NOT NULL,
    updated                TIMESTAMP NOT NULL,
    updatedby              NUMERIC(10,0) NOT NULL,
    name                   VARCHAR(60) NOT NULL,      -- Script filename
    filename               VARCHAR(500),               -- Full path
    developername          VARCHAR(60),                -- Author
    projectname            VARCHAR(60),                -- Project/JIRA
    description            VARCHAR(2000),
    reference              VARCHAR(2000),              -- JIRA URL
    url                    VARCHAR(2000),
    releaseno              VARCHAR(10),                -- iDempiere version
    status                 VARCHAR(2),                 -- IP=In Progress, CO=Complete, ER=Error
    script                 BYTEA,                      -- Script content
    applyscript            CHAR(1) DEFAULT &#39;N&#39;,
    ad_migrationscript_uu  VARCHAR(36),
    CONSTRAINT ad_migrationscript_pkey PRIMARY KEY (ad_migrationscript_id)
);

Status Values:

IP - In Progress (currently executing)
CO - Complete (successfully applied)
ER - Error (failed, needs resolution)

2.5 Checking Applied Scripts

-- List all applied migration scripts
SELECT name, status, created
FROM ad_migrationscript
ORDER BY name;

-- Find pending scripts (compare with filesystem)
SELECT name FROM ad_migrationscript WHERE status = &#39;CO&#39; ORDER BY 1;

2.6 Error Handling

If a script fails:

Status set to ER in ad_migrationscript
Error logged to /tmp/SyncDB_out_[##]/ folder
Developer must:
- Review error log
- Fix the script or database state
- Re-run RSync2DB.sh

CLI Design: Two Separate Flows

Flow 1: 2Pack (PackOut/PackIn)

For exporting/importing Application Dictionary elements as XML:

┌─────────────────────────────────────────────────────────────────┐
│  FLOW 1: 2Pack                                                  │
│                                                                 │
│  CLI generate → PackOut.xml → validate → PackIn → AD applied   │
│                                                                 │
│  Commands:                                                      │
│    - idempiere-cli 2pack generate                              │
│    - idempiere-cli 2pack validate                              │
│    - idempiere-cli 2pack packin                                │
└─────────────────────────────────────────────────────────────────┘

Uses: REST API or direct SQL for reading, XML output

Flow 2: Migration Scripts

For generating SQL migration scripts for plugin tables:

┌─────────────────────────────────────────────────────────────────┐
│  FLOW 2: Migration Scripts (SEPARATE from 2Pack)               │
│                                                                 │
│  CLI reads DB → generates SQL → register_migration_script()    │
│              → apply to DB → post-migration                    │
│                                                                 │
│  Commands:                                                      │
│    - idempiere-cli migration-script init                        │
│    - idempiere-cli migration-script generate table XX_MyTable   │
│    - idempiere-cli migration-script apply                       │
│    - idempiere-cli migration-script apply-post                  │
└─────────────────────────────────────────────────────────────────┘

Uses: Direct SQL connection to PostgreSQL (NOT REST API)

CLI Migration Script Service Architecture

The MigrationScriptService uses direct SQL connection:

┌─────────────────────────────────────────────────────────────────┐
│  MigrationScriptService                                         │
│                                                                 │
│  DataSource (JDBC) → PostgreSQL:5433                           │
│       │                                                         │
│       ├── getTableByNameFromDB()  → SELECT FROM ad_table       │
│       ├── getColumnsFromDB()      → SELECT FROM ad_column      │
│       └── getColumnByNameFromDB() → SELECT FROM ad_column      │
│                                                                 │
│  Output: migration/iX.Xz/postgresql/YYYYMMDDHHMM_ISSUE.sql     │
└─────────────────────────────────────────────────────────────────┘

Configuration:

# iDempiere Database connection (IDEMPIERE_DB_* env vars)
export IDEMPIERE_DB_HOST=localhost
export IDEMPIERE_DB_PORT=5433
export IDEMPIERE_DB_NAME=idempiere
export IDEMPIERE_DB_USER=adempiere
export IDEMPIERE_DB_PASSWORD=adempiere

Key Differences: 2Pack vs Migration Scripts

Aspect	2Pack	Migration Scripts
Format	XML (PackOut.xml)	SQL files
Connection	REST API or SQL	Direct SQL only
Registration	PackIn process	register_migration_script()
Use case	AD element export/import	DDL + DML for DB changes
iDempiere core	Uses PIPO2 handlers	Uses register_migration_script()

What CLI Should NOT Do

Generate migration scripts for core iDempiere - These require:
- Centralized ID allocation via JIRA
- Review by core team
- Use iDempiere's built-in "Log Migration Script" preference
Mix 2Pack and Migration flows - They serve different purposes:
- 2Pack = AD elements as portable XML
- Migration = SQL scripts for database changes

When to Use Each Flow

Use Case	Recommended Flow
Export table + window + process	2Pack
Add new plugin table to DB	Migration Scripts
Share plugin between instances	2Pack
Version control DB changes	Migration Scripts
Core iDempiere contribution	iDempiere native (not CLI)

References

Official Documentation

Source Code

MColumn.java - Column DDL generation
MTable.java - Table DDL generation
TableSync.java - Process 291
ColumnSync.java - Process 306
Migration folder - Official scripts

ADR-004: Enhanced Table Creation - Sections 4.4-4.5 on DDL vs Migration Scripts

Decision

Document iDempiere's migration script architecture for reference
Implement migration script generation in CLI for plugin development
Support all AD element types: table, column, window, process, reference, menu, message, sysconfig
Use direct SQL connection (DataSource) for reading AD_* tables
Reserve centralized ID allocation for core iDempiere contributions only (via JIRA)

CLI vs Core Contributions

Use Case	Approach
Plugin development	CLI generates migration scripts (local IDs, MAX+1)
Core iDempiere	Use iDempiere native "Log Migration Script" + Centralized ID via JIRA

Supported Migration Types

Type	Tables	Command Example
`table`	AD_Table, AD_Column + DDL	`migration-script generate table XX_MyTable -i PLUGIN-001`
`column`	AD_Column + ALTER TABLE	`migration-script generate column XX_MyTable.MyColumn -i PLUGIN-002`
`window`	AD_Window, AD_Tab, AD_Field	`migration-script generate window "My Window" -i PLUGIN-003`
`process`	AD_Process, AD_Process_Para	`migration-script generate process "My Process" -i PLUGIN-004`
`reference`	AD_Reference, AD_Ref_List	`migration-script generate reference "My List" -i PLUGIN-005`
`menu`	AD_Menu, AD_TreeNodeMM	`migration-script generate menu "My Menu Item" -i PLUGIN-006`
`message`	AD_Message	`migration-script generate message "MyMessageKey" -i PLUGIN-007`
`sysconfig`	AD_SysConfig	`migration-script generate sysconfig "MySysConfigKey" -i PLUGIN-008`

Consequences

Positive

Clear architectural understanding for CLI development
Full migration script support for plugin developers
Consistent with iDempiere conventions (register_migration_script)
No conflict with core development (centralized IDs remain separate)

Negative

Plugin developers must use entity prefixes (XX_) to avoid ID conflicts
CLI-generated scripts use local ID allocation, not centralized

Neutral

2Pack XML remains an alternative export format for portability
Both flows (2Pack and Migration Scripts) serve different purposes