Skip to content

OUTPUT_STRUCTURE.md

Latest commit

 

History

History
131 lines (109 loc) · 4.66 KB

OUTPUT_STRUCTURE.md

File metadata and controls

131 lines (109 loc) · 4.66 KB

Output Structure - Schema vs Programmability

Overview

The migration tool now organizes output into two clear categories:

  • Schema - Structural DDL (deployed once during migration)
  • Programmability - Code objects (changed frequently during development)

Complete Output Structure

Output/
└── <DatabaseName>/
    ├── Schema/                          # Structural DDL (deployed once)
    │   ├── 01_extensions.sql            # CREATE EXTENSION (auto-detected: uuid-ossp, pgcrypto, ltree, etc.)
    │   ├── 02_schemas.sql               # CREATE SCHEMA
    │   ├── 03_sequences.sql             # CREATE SEQUENCE
    │   ├── 04_tables.sql                # All CREATE TABLE (no constraints)
    │   ├── 05_primary_keys.sql          # All PRIMARY KEY constraints
    │   ├── 06_unique_constraints.sql    # All UNIQUE constraints
    │   ├── 07_check_constraints.sql     # All CHECK constraints
    │   ├── 08_default_constraints.sql   # All DEFAULT constraints
    │   ├── 09_foreign_keys.sql          # All FOREIGN KEY constraints
    │   └── 10_indexes.sql               # All CREATE INDEX
    │
    └── Programmability/                 # Code objects (changed frequently)
        ├── 11_views.sql                 # CONSOLIDATED - All views
        ├── 12_functions.sql             # CONSOLIDATED - All functions
        ├── 13_procedures.sql            # CONSOLIDATED - All procedures
        ├── 14_triggers.sql              # CONSOLIDATED - All triggers
        │
        ├── Views/                       # INDIVIDUAL FILES (version control)
        │   ├── HumanResources/
        │   │   ├── vEmployee.sql
        │   │   └── vEmployeeDepartment.sql
        │   └── Sales/
        │       └── vSalesPerson.sql
        │
        ├── Functions/
        │   └── dbo/
        │       ├── ufnGetStock.sql
        │       └── ufnGetAccountingEndDate.sql
        │
        ├── StoredProcedures/
        │   ├── dbo/
        │   │   └── uspGetBillOfMaterials.sql
        │   └── HumanResources/
        │       └── uspUpdateEmployeeHireInfo.sql
        │
        └── Triggers/
            └── HumanResources/
                └── dEmployee.sql

Deployment Order

1. Deploy Schema (Before Data Load)

cd Output/<DatabaseName>/Schema
for i in {01..08}; do
    psql -d targetdb -f ${i}_*.sql
done

2. Load Data

Use pg_dump, AWS DMS, Azure DMS, or custom ETL

3. Deploy Post-Data Schema (After Data Load)

cd Output/<DatabaseName>/Schema
psql -d targetdb -f 09_foreign_keys.sql    # After data to avoid FK violations
psql -d targetdb -f 10_indexes.sql         # After data for performance

4. Deploy Programmability

Option A: Use Consolidated Files (Simple)

cd Output/<DatabaseName>/Programmability
for i in {11..14}; do
    psql -d targetdb -f ${i}_*.sql
done

Option B: Use Individual Files (Selective)

cd Output/<DatabaseName>/Programmability

# Deploy specific objects
psql -d targetdb -f Views/HumanResources/vEmployee.sql
psql -d targetdb -f Functions/dbo/ufnGetStock.sql

Benefits of This Structure

Schema Folder (01-10.sql)

✅ Numbered deployment order - Run files sequentially ✅ Consolidated files - All tables in one file, all PKs in one file ✅ Clear dependencies - PKs before FKs, data before indexes ✅ One-time deployment - Run once during migration

Programmability Folder (11-14.sql + Individual Files)

✅ Two formats: Consolidated for deployment, Individual for version control ✅ Easy deployment - Run 4 numbered files (11-14) ✅ Granular control - Individual files organized by schema ✅ Version control friendly - Track changes to individual objects ✅ Selective deployment - Deploy only changed objects

When to Use Each Format

Use Consolidated Files (11-14.sql) When:

  • Initial migration deployment
  • Deploying to new environment
  • Simple "deploy everything" scenario
  • Testing full migration

Use Individual Files When:

  • Updating specific objects during development
  • Fixing bugs in single function/procedure
  • Version controlling changes
  • Multiple developers working on different objects
  • Code reviews on specific objects

File Naming Conventions

  • Schema files: 01-10 prefix for deployment order
  • Programmability consolidated: 11-14 prefix (continues numbering)
  • Individual files: Named after object, organized by schema folder
  • All identifiers: lowercase, unquoted (PostgreSQL standard)