ADR-004: Enhanced Table Creation with Standard Column Templates
Status
In Progress (2025-12-07)
Phase 1: COMPLETE ✅
- [x] Column template definitions (JSON files)
- [x] ColumnTemplate enum
- [x] ColumnDefinition model
- [x] ColumnTemplateLoader service
- [x] TableOptions class
- [x] Unit tests (57 tests)
- [x] Enhance AddCommand with new options
- [x] API integration for column creation
Phase 3: COMPLETE ✅
- [x] ADWindow model for window definitions
- [x] ADTab model for tab definitions
- [x] ADField model with factory method fromColumn()
- [x] WindowGenerator service with preview capability
- [x] WindowOptions builder for configuration
- [x] IdempiereClient methods for window/tab/field CRUD
- [x] Field visibility rules (key, housekeeping, UUID columns hidden)
- [x] Document column handling (DocStatus, DocAction, etc.)
- [x] Text/Memo columns with multi-line display
- [x]
--create-windowoption for AddCommand - [x]
--window-typeoption (M/T/Q) - [x] Unit tests (79 tests)
Phase 4: COMPLETE ✅
- [x] SyncCommand with table/column subcommands
- [x] Three output formats: REST API, SQL migration, 2Pack XML
- [x] DdlGenerator for PostgreSQL DDL generation
- [x] PackOutGenerator for 2Pack XML generation
- [x]
--formatoption (rest, sql, pack) - [x]
--outputoption for file output - [x]
--dry-runfor preview mode - [x]
--show-ddlfor DDL reference - [x] Unit tests (52 tests)
Phase 5: COMPLETE ✅
- [x] IMPORT column template (I_IsImported, I_ErrorMsg, Processed, Processing)
- [x]
--importoption for AddCommand - [x]
--target-tableoption for mapping columns - [x] ColumnTemplate.IMPORT enum value
- [x] Import columns JSON template
- [x] Unit tests (11 new tests)
Phase 6: PROPOSED (Definitive Architecture - Create, Process, Distribute)
- [ ] iDempiere core class integration (MTable, MColumn for dry-run)
- [ ] Migration script with
register_migration_script()header - [ ] 2Pack with exact
dict/PackOut.xmlstructure - [ ]
--applyflag withad_migrationscripttracking - [ ] REST queue with checkpoint/resume
- [ ] Format selection logic (
--format migration|pack|rest)
Context
After analyzing iDempiere's native table creation capabilities (via CopyColumnsFromTable, TableCreateColumns, and the Table & Column Window), we identified significant gaps in our CLI's add table command.
Current CLI limitations:
- Only creates table definition in AD_Table
- No automatic standard columns (AD_Client_ID, AD_Org_ID, etc.)
- No document column support
- No accounting column support
- No window/tab/field generation
- Limited table configuration flags
iDempiere's capabilities:
- Copy columns from template tables
- Auto-generate all standard housekeeping columns
- Document workflow columns for document tables
- Accounting columns for posted documents
- Create Window/Tab/Field from table
- Full table configuration (changelog, security, etc.)
Decision
Enhance the add table command to support iDempiere's full table creation workflow with column templates, configuration flags, and optional window generation.
Implementation Plan
Phase 1: Standard Column Templates (Priority: HIGH)
1.1 Define Column Template Groups
public enum ColumnTemplate {
// Mandatory for ALL tables
STANDARD, // _ID, _UU, AD_Client_ID, AD_Org_ID, IsActive, Created/By, Updated/By
// Document tables
DOCUMENT, // DocumentNo, DocStatus, DocAction, Processing, Processed, etc.
// Accounting-enabled documents
ACCOUNTING, // DateAcct, Posted, C_Currency_ID, ProcessedOn
// Common optional columns
DESCRIPTION, // Name, Description, Help
VALUE_NAME, // Value (search key), Name
TREE, // Parent_ID, SeqNo for hierarchical data
IMPORT // I_IsImported, I_ErrorMsg, Processed for import tables
}
1.2 Standard Columns Definition
{
"STANDARD": [
{"name": "{Table}_ID", "type": "ID", "mandatory": true, "key": true},
{"name": "{Table}_UU", "type": "UUID", "mandatory": true, "unique": true},
{"name": "AD_Client_ID", "type": "TableDir", "reference": "AD_Client", "mandatory": true},
{"name": "AD_Org_ID", "type": "TableDir", "reference": "AD_Org", "mandatory": true},
{"name": "IsActive", "type": "YesNo", "mandatory": true, "default": "Y"},
{"name": "Created", "type": "DateTime", "mandatory": true, "default": "SYSDATE"},
{"name": "CreatedBy", "type": "Table", "reference": "AD_User", "mandatory": true},
{"name": "Updated", "type": "DateTime", "mandatory": true, "default": "SYSDATE"},
{"name": "UpdatedBy", "type": "Table", "reference": "AD_User", "mandatory": true}
],
"DOCUMENT": [
{"name": "DocumentNo", "type": "String", "length": 30, "mandatory": true},
{"name": "C_DocType_ID", "type": "TableDir", "reference": "C_DocType", "mandatory": true},
{"name": "C_DocTypeTarget_ID", "type": "Table", "reference": "C_DocType", "mandatory": false},
{"name": "DocStatus", "type": "List", "reference": "_Document Status", "mandatory": true, "default": "DR"},
{"name": "DocAction", "type": "Button", "reference": "_Document Action", "mandatory": true, "default": "CO"},
{"name": "Processing", "type": "Button", "mandatory": false},
{"name": "Processed", "type": "YesNo", "mandatory": true, "default": "N"},
{"name": "IsApproved", "type": "YesNo", "mandatory": true, "default": "N"}
],
"ACCOUNTING": [
{"name": "DateAcct", "type": "Date", "mandatory": true},
{"name": "C_Currency_ID", "type": "TableDir", "reference": "C_Currency", "mandatory": true},
{"name": "Posted", "type": "Button", "reference": "_Posted Status", "mandatory": true, "default": "N"},
{"name": "ProcessedOn", "type": "Number", "mandatory": false}
],
"DESCRIPTION": [
{"name": "Name", "type": "String", "length": 60, "mandatory": true, "identifier": true},
{"name": "Description", "type": "String", "length": 255, "mandatory": false},
{"name": "Help", "type": "Text", "mandatory": false}
],
"VALUE_NAME": [
{"name": "Value", "type": "String", "length": 40, "mandatory": true, "identifier": true},
{"name": "Name", "type": "String", "length": 60, "mandatory": true, "identifier": true}
]
}
1.3 Enhanced CLI Command
# Basic table with standard columns (default behavior)
idempiere-cli add table XX_MyTable
# Document table
idempiere-cli add table XX_MyDocument --document
# Document with accounting
idempiere-cli add table XX_MyInvoice --document --accounting
# Table with name/description fields
idempiere-cli add table XX_MyMaster --with-name --with-description
# Full options
idempiere-cli add table XX_MyTable \
--description "My custom table" \
--document \
--accounting \
--with-name \
--with-description \
--changelog \
--high-volume \
--access-level 3 \
--entity-type "D"
Phase 2: Table Configuration Flags (Priority: HIGH)
2.1 New Command Options
| Option | AD_Table Field | Description |
|---|---|---|
--changelog |
IsChangeLog | Enable audit trail |
--high-volume |
IsHighVolume | Use search UI |
--no-delete |
IsDeleteable=N | Prevent record deletion |
--security |
IsSecurityEnabled | Enable role-based access |
--view |
IsView | Mark as database view |
--partition |
IsPartition | Partitioned table |
--drill |
IsShowInDrillOptions | Show in drill assistant |
--access-level <n> |
AccessLevel | 1-7 access level |
--entity-type <type> |
EntityType | D, U, C, etc. |
--replication <type> |
ReplicationType | L, M, R |
2.2 Access Level Reference
1 = Organization
2 = Client only
3 = Client + Organization (most common)
4 = System only
6 = System + Client
7 = All (System + Client + Org)
Phase 3: Window/Tab/Field Generation (Priority: MEDIUM)
3.1 Create Window from Table
# Create window after table
idempiere-cli add table XX_MyTable --create-window
# Or separately
idempiere-cli add window XX_MyTable \
--name "My Table" \
--description "Window for My Table" \
--window-type "M" # Maintain, Query, Transaction
3.2 Window Generation Logic
- Create AD_Window record
- Create AD_Tab record linked to table
- Create AD_Field records for each column
- Set reasonable defaults:
- Key fields: not displayed
- Standard columns: placed in standard locations
- Document fields: proper sequence
- Button fields: correct display logic
Phase 4: Sync with Multiple Output Formats (Priority: MEDIUM)
The CLI supports three synchronization strategies to cover different use cases:
| Approach | Offline Generate | Offline Apply | Best For |
|---|---|---|---|
| REST API | ❌ | ❌ | Development with running iDempiere |
| 2Pack XML | ✅ | ✅ | Plugin distribution, deployment |
| Migration SQL | ✅ | ✅ | Offline preview, DBA workflows |
4.1 Sync Command with Output Formats
# Execute sync via REST API (default, requires running iDempiere)
idempiere-cli sync table XX_MyTable
# Generate 2Pack XML for plugin packaging
idempiere-cli sync table XX_MyTable --format 2pack --output ./META-INF/2Pack.zip
# Generate migration SQL for offline apply
idempiere-cli sync table XX_MyTable --format sql --output ./migration/
# Preview DDL without executing (offline, no iDempiere needed)
idempiere-cli sync table XX_MyTable --dry-run
# Sync specific column
idempiere-cli sync column XX_MyTable.XX_MyColumn
idempiere-cli sync column XX_MyTable.XX_MyColumn --format sql
4.2 REST API Sync (--format rest)
Uses iDempiere's built-in sync processes:
- Process 291 = Table Sync (creates table + all columns)
- Process 306 = Column Sync (creates single column)
Requirements:
- Running iDempiere server
- Configured API connection (
idempiere-cli config)
Reference: Synchronize Changes with Database
4.3 2Pack XML Generation (--format pack)
What is 2Pack?
2Pack is iDempiere's packaging mechanism for distributing Application Dictionary changes. It's the preferred approach for plugin development because:
- No centralized ID management required (UUIDs used instead)
- Self-contained packages that can be distributed independently
- Supports wide scope: windows, tables, columns, data, processes, reports, forms, etc.
- Auto-installs via
AdempiereActivatoron plugin startup
When to use 2Pack:
- Plugin development
- Distributing customizations to clients
- Sharing AD changes between environments without core modifications
Generates PackOut.xml compatible with iDempiere's 2Pack system:
- Can be placed in plugin's
META-INF/2Pack.zip - Auto-installs via
AdempiereActivatoron plugin startup - Supports incremental updates via
Incremental2PackActivator
Output Structure:
2Pack.zip
└── dict/
└── PackOut.xml # Contains table, column, window definitions
iDempiere 2Pack References:
- 2Pack - Pack In & Pack Out - Official 2Pack documentation
- Developing Plug-Ins - 2Pack - How to use 2Pack in plugins
- NF5.1 Automatic External Packin - Auto-install mechanism
- PackOut (Window ID-270) - Pack Out window documentation
Source Code References:
- TableElementHandler.java - Table XML handler
- ColumnElementHandler.java - Column XML handler
- WindowElementHandler.java - Window XML handler
- PackOut.java - Main PackOut implementation
4.4 DDL SQL Generation (--format sql)
⚠️ Important: DDL vs Migration Scripts
The --format sql option generates raw DDL for preview and manual application. This is NOT the same as iDempiere's official Migration Script workflow used for core development.
| Feature | CLI DDL (--format sql) |
iDempiere Migration Scripts |
|---|---|---|
| Purpose | Preview, offline review, manual DBA work | Core development contributions |
| ID Management | Uses MAX(ID)+1 |
Centralized ID Management via JIRA |
| Naming | 001_create_table_xxx.sql |
IDEMPIERE-XXXX_description.sql |
| Application | Manual psql execution |
RSync2DB.sh or Database Migration window |
| Tracking | None | ad_migrationscript table |
| Translations | Not included | Properly handled |
What this format generates:
- CREATE TABLE with all columns
- Primary key, foreign key, unique constraints
- Default values and NOT NULL constraints
- INSERT statements for AD_Table, AD_Column records
Output Structure:
migration/postgresql/
├── 001_create_table_XX_MyTable.sql
├── 002_insert_ad_table.sql
└── 003_insert_ad_columns.sql
When to use DDL format:
- Preview what SQL would be generated
- Offline review before sync
- Manual DBA workflows
- Learning/understanding the schema
4.5 iDempiere Migration Script Workflow (Core Development)
For core iDempiere contributions, developers must use the official Migration Script workflow:
Workflow:
- Enable migration script generation in your development environment
- Create JIRA ticket (e.g., IDEMPIERE-6000) to get centralized ID allocation
- Make AD changes - migration scripts auto-generated with allocated IDs
- Run
RSync2DB.shto apply scripts via Database Migration window - Commit scripts to
migration/iXX.Xz/postgresql/folder
Why Centralized ID Management?
iDempiere uses Centralized ID Management to prevent ID conflicts when multiple developers contribute to core:
- JIRA ticket allocates unique ID ranges
- Scripts use allocated IDs instead of
MAX(ID)+1 - Ensures consistent IDs across all installations
- Required for translations, references, and data integrity
Migration Script Application:
# Apply outstanding migration scripts
cd $IDEMPIERE_HOME/utils
./RSync2DB.sh
# Or via Database Migration window (Window ID-53071)
Without running RSync2DB.sh, changes won't be properly applied:
- Translations won't work
- Cross-references may break
ad_migrationscripttracking incomplete
iDempiere Migration References:
- Generating Migration Scripts - Official migration guide
- Centralized ID Management - ID allocation for core development
- Database Migration (Window ID-53071) - Migration window documentation
- iDempiere Migration Folder - Official migration scripts repository
Migration Script Conventions:
migration/
├── i10.0z/ # Version-specific migrations
│ ├── postgresql/ # PostgreSQL scripts
│ │ └── IDEMPIERE-XXXX_description.sql
│ └── oracle/ # Oracle scripts (optional)
│ └── IDEMPIERE-XXXX_description.sql
└── processes_post_migration/ # Post-migration processes
Source Code References:
- MColumn.syncDatabase() - Column sync logic
- MTable.syncDatabase() - Table sync logic
- TableSync Process - Process 291 implementation
- RSync2DB.sh - Migration application script
4.6 Implementation Classes
src/main/java/org/idempiere/cli/
├── commands/
│ ├── SyncCommand.java # REST-only database sync
│ └── ExportCommand.java # 2Pack XML and SQL export
├── generator/
│ └── output/
│ ├── PackOutGenerator.java # 2Pack XML generation
│ └── DdlGenerator.java # PostgreSQL DDL generation
Architecture Notes:
synccommand = REST API sync only (iDempiere Process 291/306)exportcommand = Generate output files (2Pack XML, SQL DDL)- Clear separation: sync is runtime, export is file generation
Phase 5: Import Table Support (Priority: LOW)
5.1 Import Table Template
idempiere-cli add table I_MyImport --import \
--target-table XX_MyTable
Generates columns:
- All target table columns (prefixed)
I_IsImported- Import statusI_ErrorMsg- Error messageProcessed- Processing flagProcessing- Process button
File Structure
src/main/java/org/idempiere/cli/
├── commands/
│ └── AddCommand.java # Enhanced add table subcommand
├── generator/
│ ├── table/
│ │ ├── TableGenerator.java # Main table generator
│ │ ├── ColumnTemplate.java # Column template enum
│ │ ├── ColumnTemplateLoader.java # Load JSON templates
│ │ ├── StandardColumns.java # Standard column definitions
│ │ └── WindowGenerator.java # Window/Tab/Field generator
│ └── schema/
│ └── table-schema.json # Schema for table options
└── api/
└── model/
├── ADTable.java # Enhanced with all flags
└── ADColumn.java # Enhanced column model
src/main/resources/
├── generators/
│ └── table/
│ ├── schema.json # Table generator schema
│ └── templates/
│ ├── standard-columns.json
│ ├── document-columns.json
│ └── accounting-columns.json
└── templates/
└── ddl/
├── create-table.sql.qute
└── create-column.sql.qute
API Payload Examples
Create Table with Document Columns
POST /api/v1/models/AD_Table
{
"TableName": "XX_MyDocument",
"Name": "My Document",
"Description": "Custom document table",
"AccessLevel": "3",
"EntityType": "U",
"IsChangeLog": true,
"IsDeleteable": true,
"IsHighVolume": false,
"IsSecurityEnabled": false,
"IsView": false,
"ReplicationType": "L"
}
Create Standard Columns
POST /api/v1/models/AD_Column (batch)
[
{
"AD_Table_ID": "@XX_MyDocument_ID@",
"ColumnName": "XX_MyDocument_ID",
"Name": "My Document",
"AD_Reference_ID": 13,
"IsKey": true,
"IsMandatory": true,
"IsUpdateable": false
},
{
"AD_Table_ID": "@XX_MyDocument_ID@",
"ColumnName": "XX_MyDocument_UU",
"Name": "XX_MyDocument_UU",
"AD_Reference_ID": 10,
"FieldLength": 36,
"IsMandatory": true
},
// ... more columns
]
Implementation Priority
| Phase | Feature | Effort | Impact | Priority |
|---|---|---|---|---|
| 1 | Standard column templates | Medium | High | P0 |
| 2 | Table configuration flags | Low | High | P0 |
| 3 | Window/Tab/Field generation | High | High | P1 |
| 4 | Column sync with database | Medium | Medium | P1 |
| 5 | Import table support | Low | Low | P2 |
Recommended order: Phase 1 + 2 (together) → Phase 3 → Phase 4 → Phase 5
Migration Notes
Backward Compatibility
- Existing
add tablecommand continues to work - New flags are optional with sensible defaults
--no-standard-columnsflag to skip standard columns if needed
Default Behavior Change
Before (v1.3.0):
idempiere-cli add table XX_MyTable
# Creates: Just AD_Table record, no columns
After (v1.4.0):
idempiere-cli add table XX_MyTable
# Creates: AD_Table + 9 standard columns + syncs to DB
To get old behavior:
idempiere-cli add table XX_MyTable --no-standard-columns --no-sync
Success Criteria
add tablewith--documentcreates all 17+ columns correctlyadd tablewith--create-windowproduces usable Window/Tab/Fields- Generated DDL matches iDempiere's native table creation
- Column references (FK) are correctly set up
- All standard column defaults work (SYSDATE, Y/N, etc.)
Phase 6: Definitive Architecture - Create, Process, Distribute (Priority: HIGH)
Status: PROPOSED (2025-12-07)
This phase defines the definitive architecture for AD element creation, validated against iDempiere's actual implementation patterns.
6.1 Architecture Overview
┌─────────────────────────────────────────────────────────────────────────┐
│ A. ENTRY PATHS │
│ (Any method reaches shared logic layer) │
├─────────────────────────────────────────────────────────────────────────┤
│ CLI Command │ MCP Tools │ LangChain4j │ REST API (future) │
│ (Picocli) │ (AI Agent) │ (Embedded) │ (server api) │
└───────┬────────┴──────┬──────┴───────┬───────┴──────────┬───────────────┘
│ │ │ │
└───────────────┴──────────────┴──────────────────┘
│
┌───────────▼───────────┐
│ Shared Tool Logic │
│ (org.idempiere.cli. │
│ ai.shared) │
└───────────┬───────────┘
│
┌───────────────────────────────▼─────────────────────────────────────────┐
│ B. CREATE (BUILD PHASE) │
│ Build in-memory objects, validate, preview (dry-run) │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ Option 1: JSON Templates (Current - for simple cases) │ │
│ │ ColumnTemplateLoader → ColumnDefinition → ADColumn model │ │
│ │ ✓ No DB connection needed │ │
│ │ ✗ Limited DDL accuracy (no FK names, constraints) │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ Option 2: iDempiere Core Classes (Proposed - for accuracy) │ │
│ │ │ │
│ │ COMPARISON TO iDEMPIERE: │ │
│ │ ┌──────────────────────┬──────────────────────────────────┐ │ │
│ │ │ iDempiere (UI) │ CLI (Proposed) │ │ │
│ │ ├──────────────────────┼──────────────────────────────────┤ │ │
│ │ │ Table Window ID-100 │ TableBuilder.build() │ │ │
│ │ │ → MTable instance │ → MTable instance (in-memory) │ │ │
│ │ │ → MColumn instances │ → MColumn instances (in-memory) │ │ │
│ │ │ → getSQLDDL() │ → getSQLDDL() (same method!) │ │ │
│ │ │ → getSQLAdd() │ → getSQLAdd() (same method!) │ │ │
│ │ └──────────────────────┴──────────────────────────────────┘ │ │
│ │ │ │
│ │ Benefits: │ │
│ │ ✓ Exact DDL match with iDempiere native │ │
│ │ ✓ Proper FK constraint names │ │
│ │ ✓ Correct data type mappings │ │
│ │ ✓ Validation rules honored │ │
│ │ │ │
│ │ Constraint: Some lookups need DB (AD_Reference names) │ │
│ │ Solution: Embedded reference cache for common types │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ DRY-RUN MODE: │
│ --dry-run → Preview DDL without any DB/API connection │
│ --dry-run --full → Preview with DB for complete FK resolution │
│ │
└───────────────────────────────┬─────────────────────────────────────────┘
│
┌───────────────────────────────▼─────────────────────────────────────────┐
│ C. OUTPUT FORMATS │
│ Three formats matching iDempiere's distribution patterns │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ C1. MIGRATION SCRIPT (--format migration) │ │
│ │ │ │
│ │ MATCHES iDEMPIERE: "Log Migration Script" preference flow │ │
│ │ │ │
│ │ Generated format (exact iDempiere pattern): │ │
│ │ ┌────────────────────────────────────────────────────────────┐ │ │
│ │ │ SELECT register_migration_script('202412071200_XXX.sql') │ │ │
│ │ │ FROM dual; │ │ │
│ │ │ │ │ │
│ │ │ -- Timestamp: Dec 07, 2024 12:00:00 PM CET │ │ │
│ │ │ -- PLUGIN-001 Add custom table XX_MyTable │ │ │
│ │ │ │ │ │
│ │ │ INSERT INTO AD_Table (AD_Table_ID, ...) VALUES (...); │ │ │
│ │ │ INSERT INTO AD_Column (AD_Column_ID, ...) VALUES (...); │ │ │
│ │ │ CREATE TABLE XX_MyTable (...); │ │ │
│ │ └────────────────────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ Output: migration-local/iX.Xz/postgresql/YYYYMMDDHHMM_ID.sql │ │
│ │ │ │
│ │ Reference: [Generating Migration Scripts] │ │
│ │ (https://wiki.idempiere.org/en/Generating_Migration_Scripts) │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ C2. PACKOUT / 2PACK (--format pack) │ │
│ │ │ │
│ │ MATCHES iDEMPIERE: Pack Out Window (ID-270) flow │ │
│ │ │ │
│ │ Generated structure (exact iDempiere pattern): │ │
│ │ ┌────────────────────────────────────────────────────────────┐ │ │
│ │ │ 2Pack_mypackage_1.0.0.zip │ │ │
│ │ │ ├── dict/ │ │ │
│ │ │ │ └── PackOut.xml ← AD definitions │ │ │
│ │ │ └── doc/ │ │ │
│ │ │ └── mypackage.xml ← Package metadata │ │ │
│ │ └────────────────────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ Uses PIPO2 handlers: │ │
│ │ - TableElementHandler (table definitions) │ │
│ │ - ColumnElementHandler (column definitions) │ │
│ │ - WindowElementHandler (window definitions) │ │
│ │ │ │
│ │ Reference: [2Pack - Pack In & Pack Out] │ │
│ │ (https://wiki.idempiere.org/en/2Pack) │ │
│ │ Reference: [NF1.0 2Pack Revamped] │ │
│ │ (https://wiki.idempiere.org/en/NF1.0_2Pack_Revamped) │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ C3. REST API (--format rest) │ │
│ │ │ │
│ │ MATCHES iDEMPIERE: REST Web Services │ │
│ │ │ │
│ │ Request sequence: │ │
│ │ 1. POST /api/v1/auth/tokens → JWT token │ │
│ │ 2. POST /api/v1/models/AD_Table → Create table record │ │
│ │ 3. POST /api/v1/models/AD_Column (×N) → Create columns │ │
│ │ 4. POST /api/v1/processes/291 → Sync table (Process 291) │ │
│ │ │ │
│ │ Reference: [REST Web Services] │ │
│ │ (https://wiki.idempiere.org/en/REST_Web_Services) │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
└───────────────────────────────┬─────────────────────────────────────────┘
│
┌───────────────────────────────▼─────────────────────────────────────────┐
│ D. DISTRIBUTION / APPLY │
│ Each format has its own apply mechanism │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ D1. MIGRATION SCRIPT DISTRIBUTION │ │
│ │ │ │
│ │ MATCHES iDEMPIERE: RSync2DB.sh / Database Migration Window │ │
│ │ │ │
│ │ DEFAULT: Save to migration-local/iX.Xz/postgresql/ │ │
│ │ │ │
│ │ --apply flag workflow: │ │
│ │ ┌────────────────────────────────────────────────────────────┐ │ │
│ │ │ 1. register_migration_script() function called │ │ │
│ │ │ → INSERT INTO ad_migrationscript (name, status='IP') │ │ │
│ │ │ │ │ │
│ │ │ 2. Execute SQL statements via JDBC │ │ │
│ │ │ → CREATE TABLE, INSERT AD_Table, INSERT AD_Column │ │ │
│ │ │ │ │ │
│ │ │ 3. Update tracking │ │ │
│ │ │ → UPDATE ad_migrationscript SET status='CO' │ │ │
│ │ │ │ │ │
│ │ │ 4. Post-migration (optional) │ │ │
│ │ │ → Run processes_post_migration/ scripts │ │ │
│ │ └────────────────────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ ad_migrationscript table tracking: │ │
│ │ ┌────────────────┬─────────────────────────────────────────┐ │ │
│ │ │ Column │ Purpose │ │ │
│ │ ├────────────────┼─────────────────────────────────────────┤ │ │
│ │ │ name │ Script identifier (YYYYMMDDHHMM_ID) │ │ │
│ │ │ filename │ Full path to script │ │ │
│ │ │ status │ IP=In Progress, CO=Complete, ER=Error │ │ │
│ │ │ projectname │ Plugin/project name │ │ │
│ │ │ releaseno │ iDempiere version │ │ │
│ │ │ script │ Script content (binary) │ │ │
│ │ └────────────────┴─────────────────────────────────────────┘ │ │
│ │ │ │
│ │ Reference: [Migration Scripts Window (ID-53019)] │ │
│ │ (https://wiki.idempiere.org/en/Migration_Scripts_(Window_ID-53019)) │
│ │ Reference: [Applying additional Migration Scripts] │ │
│ │ (https://wiki.idempiere.org/en/Applying_additional_Migration_Scripts) │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ D2. PACKOUT / 2PACK DISTRIBUTION │ │
│ │ │ │
│ │ MATCHES iDEMPIERE: Plugin Activator pattern │ │
│ │ │ │
│ │ Save to plugin: │ │
│ │ └── META-INF/ │ │
│ │ └── 2Pack_myplugin_1.0.0.zip │ │
│ │ │ │
│ │ Activator options (matches iDempiere exactly): │ │
│ │ ┌────────────────────────────┬─────────────────────────────┐ │ │
│ │ │ Activator Class │ Behavior │ │ │
│ │ ├────────────────────────────┼─────────────────────────────┤ │ │
│ │ │ AdempiereActivator │ Single 2Pack.zip │ │ │
│ │ │ Version2PackActivator │ Version-specific install │ │ │
│ │ │ Incremental2PackActivator │ ALL 2Pack_*.zip files │ │ │
│ │ └────────────────────────────┴─────────────────────────────┘ │ │
│ │ │ │
│ │ Version naming: 2Pack_name_X.X.X.zip (3-digit required) │ │
│ │ │ │
│ │ --apply flag: Direct PackIn via REST API (Process 194) │ │
│ │ │ │
│ │ Reference: [Developing Plug-Ins - 2Pack] │ │
│ │ (https://wiki.idempiere.org/en/Developing_Plug-Ins_-_2Pack_-_Pack_In/Out) │
│ │ Reference: [NF5.1 Automatic External Packin] │ │
│ │ (https://wiki.idempiere.org/en/NF5.1_Automatic_External_Packin) │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ D3. REST API DISTRIBUTION (Queue-Managed) │ │
│ │ │ │
│ │ IMPORTANT: REST is stateless, no native transactions │ │
│ │ │ │
│ │ Problem: Multiple requests = partial state on failure │ │
│ │ ┌────────────────────────────────────────────────────────────┐ │ │
│ │ │ POST AD_Table ✓ → POST Column1 ✓ → POST Column2 ✗ → FAIL! │ │ │
│ │ │ Result: Table exists, Column1 exists, Column2 missing │ │ │
│ │ └────────────────────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ Solution: Request Queue with Checkpoint │ │
│ │ ┌────────────────────────────────────────────────────────────┐ │ │
│ │ │ RestRequestQueue: │ │ │
│ │ │ [T1:pending] → [C1:pending] → [C2:pending] → [Sync:pend] │ │ │
│ │ │ │ │ │
│ │ │ Execution: │ │ │
│ │ │ [T1:done] → [C1:done] → [C2:FAILED] → checkpoint saved │ │ │
│ │ │ │ │ │
│ │ │ Resume (--resume): │ │ │
│ │ │ Load checkpoint → Skip done → Retry C2 → Continue │ │ │
│ │ └────────────────────────────────────────────────────────────┘ │ │
│ │ │ │
│ │ Flags: │ │
│ │ --continue-on-error Continue after failures (default: stop) │ │
│ │ --resume Resume from last checkpoint │ │
│ │ --checkpoint <file> Custom checkpoint file │ │
│ │ │ │
│ │ Alternative: Use Composite Service for atomicity │ │
│ │ Reference: [Web Services First Steps] │ │
│ │ (https://wiki.idempiere.org/en/Web_Services_First_Steps) │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────┘
6.2 Format Selection Logic
# Explicit format selection
idempiere-cli dict add table XX_MyTable --format migration
idempiere-cli dict add table XX_MyTable --format pack
idempiere-cli dict add table XX_MyTable --format rest
# Intelligent defaults based on context
idempiere-cli dict add table XX_MyTable # REST (if API configured)
idempiere-cli dict add table XX_MyTable --offline # Migration (no server)
idempiere-cli dict add table XX_MyTable --plugin myPlugin # PackOut (plugin context)
6.3 Comparison: CLI vs iDempiere Native
| Operation | iDempiere Native | CLI Equivalent | Match Level |
|---|---|---|---|
| Create table via UI | Table Window ID-100 | dict add table |
✅ Equivalent |
| Sync to DB | Process 291 | --format rest → Process 291 |
✅ Exact |
| Generate migration | "Log Migration Script" pref | --format migration |
✅ Exact format |
| Export 2Pack | Pack Out Window ID-270 | --format pack |
✅ Exact structure |
| Apply migration | RSync2DB.sh | --apply flag |
✅ Same tracking |
| Install 2Pack | Activator on startup | Plugin META-INF/ | ✅ Same mechanism |
6.4 Implementation Classes (Phase 6)
src/main/java/org/idempiere/cli/
├── core/ # NEW: iDempiere core integration
│ ├── TableBuilder.java # Build MTable in-memory
│ ├── ColumnBuilder.java # Build MColumn in-memory
│ └── CoreDdlGenerator.java # Use MColumn.getSQLDDL()
├── distribution/ # NEW: Distribution handlers
│ ├── MigrationScriptDistributor.java
│ ├── PackOutDistributor.java
│ └── RestQueueDistributor.java
└── queue/ # NEW: REST queue management
├── RestRequestQueue.java
├── RequestCheckpoint.java
└── QueueExecutor.java
6.5 Success Criteria (Phase 6)
- ✅
--dry-rungenerates DDL usingMColumn.getSQLDDL()(exact match) - ✅
--format migrationproduces scripts withregister_migration_script()header - ✅
--format packgenerates 2Pack.zip with correctdict/PackOut.xmlstructure - ✅
--applyon migration tracks inad_migrationscripttable - ✅ REST queue supports checkpoint/resume on failure
- ✅ All formats can be applied both offline and online
References
Source Code
- CreateTable.java - Primary reference for this ADR. Studied to understand standard columns, document columns, and table flag implementations.
- MColumn.java - Column model and sync logic
- MTable.java - Table model and DDL generation
- TableElementHandler.java - 2Pack table handler
- ColumnElementHandler.java - 2Pack column handler
- PackOut.java - Main PackOut implementation
Documentation
- Table and Column Window (ID-100) - UI table creation
- Generating Migration Scripts - Migration script workflow
- Migration Scripts Window (ID-53019) - Tracking table
- Applying additional Migration Scripts - Apply workflow
- 2Pack - Pack In & Pack Out - 2Pack overview
- Pack Out 2Pack - Export workflow
- Developing Plug-Ins - 2Pack - Plugin integration
- NF1.0 2Pack Revamped - UUID handling
- NF5.1 Automatic External Packin - Auto-install
- REST Web Services - REST API
- Web Services First Steps - Composite services
- Centralized ID Management - Core ID allocation
- Create Custom Table and Window - Tutorial
- iDempiere Process API - Process JavaDoc