# SQL DDL Management Tools

A suite of Python scripts for analyzing, cross-referencing, and managing SQL DDL files and standalone SQL objects in the Axium Services codebase.

## Overview

This toolkit consists of three interconnected scripts that work together to help manage large SQL DDL files and their relationship with standalone SQL files distributed across the codebase:

1. **`extract_sql_objects.py`** - Extracts all database objects from a master DDL file
2. **`cross_reference_sql_objects.py`** - Identifies which objects have standalone SQL files
3. **`remove_matched_objects.py`** - Removes matched objects from the DDL to create a cleaned version

> **Note**: These scripts were created based on the support scripts repository structure from [https://gitlab.com/scheinone/exan/axium-support/services](https://gitlab.com/scheinone/exan/axium-support/services). Version numbering, release naming conventions (e.g., `Rel2025.03`), and directory structures follow the standards established in that repository.

## Table of Contents

- [Installation](#installation)
- [Quick Start](#quick-start)
- [Complete Workflow](#complete-workflow)
- [Script Documentation](#script-documentation)
  - [1. Extract SQL Objects](#1-extract-sql-objects)
  - [2. Cross-Reference SQL Objects](#2-cross-reference-sql-objects)
  - [3. Remove Matched Objects](#3-remove-matched-objects)
- [Use Cases](#use-cases)
- [Advanced Usage](#advanced-usage)
- [Output Formats](#output-formats)
- [Troubleshooting](#troubleshooting)

---

## Installation

### Requirements

- Python 3.6 or higher
- No external dependencies (uses only Python standard library)

### Setup

1. Ensure Python is installed:
   ```bash
   python --version
   ```

2. All scripts are ready to use without installation. Simply navigate to the directory containing the scripts.

---

## Quick Start

### Basic Workflow (3 Commands)

```bash
# Step 1: Extract all objects from the master DDL
python extract_sql_objects.py

# Step 2: Find which objects have standalone SQL files
python cross_reference_sql_objects.py

# Step 3: Remove matched objects from the DDL
python remove_matched_objects.py
```

This will:
- Extract all database objects from `SQL-Golden-Axium-DDL.ddl`
- Scan the current directory for standalone SQL files
- Create a cleaned DDL with only unmatched objects

---

## Complete Workflow

### Scenario: Analyzing SQL Objects Across the Codebase

**Goal**: Identify which database objects from the master DDL have standalone SQL files, and create a cleaned DDL containing only objects without standalone versions.

#### Step 1: Extract Objects from Master DDL

```bash
python extract_sql_objects.py -i SQL-Golden-Axium-DDL.ddl -o extracted_objects.json
```

**Output**: `extracted_objects.json`
```json
{
  "metadata": {
    "source_file": "SQL-Golden-Axium-DDL.ddl",
    "extraction_date": "2026-01-23T10:30:00",
    "total_objects": 496
  },
  "summary": {
    "packages": 61,
    "package_bodies": 60,
    "functions": 88,
    "procedures": 67,
    "triggers": 220
  },
  "objects": {
    "packages": ["cursor_pkg", "ax_pkg", "alloc_pkg", ...],
    "package_bodies": ["alloc_pkg", "ar_dept_pkg", ...],
    "functions": ["appointneeds", "appointneedsexam", ...],
    "procedures": ["appoint_estimate_proc", ...],
    "triggers": ["apnote_trg", "appoint_bookingorder", ...]
  }
}
```

#### Step 2: Cross-Reference with Standalone Files

```bash
python cross_reference_sql_objects.py \
  -i extracted_objects.json \
  -o matched_objects.json \
  -d ./Dev/SQL
```

**Output**: `matched_objects.json`
```json
{
  "metadata": {
    "source_json": "extracted_objects.json",
    "scan_directory": "C:/exangit/axium/services/Dev/SQL",
    "total_sql_files_scanned": 58307,
    "total_matched_objects": 74
  },
  "summary": {
    "packages": 21,
    "package_bodies": 5,
    "functions": 24,
    "procedures": 7,
    "triggers": 17
  },
  "matched_objects": {
    "packages": [
      {
        "name": "OVERNIGHT_PKG",
        "total_files": 46,
        "schools": ["ACTA", "ALB", "BUFF", ...],
        "school_count": 45,
        "generic_count": 1
      },
      ...
    ]
  },
  "file_mappings": [
    {
      "file": "Dev/SQL/Packages/OverNight/OverNight_pkg_ACTA.sql",
      "object_type": "package",
      "object_name": "OVERNIGHT_PKG",
      "school": "ACTA"
    },
    ...
  ]
}
```

#### Step 3: Remove Matched Objects from DDL

```bash
python remove_matched_objects.py \
  -d SQL-Golden-Axium-DDL.ddl \
  -i matched_objects.json \
  -o cleaned_ddl.sql
```

**Output**: `cleaned_ddl.sql` (a DDL file with 74 objects removed)

**Console Output**:
```
============================================================
DDL OBJECT REMOVAL TOOL
============================================================
Input DDL:  SQL-Golden-Axium-DDL.ddl
Input JSON: matched_objects.json
Output DDL: cleaned_ddl.sql
============================================================

Loading matched objects from JSON...
Loaded 74 matched objects to remove
Auto-paired 20 package spec(s)/body(ies) to prevent orphans
Total objects to remove: 94

Processing DDL file...

============================================================
REMOVAL SUMMARY
============================================================
Total objects found:     496
Objects removed:         94
Objects retained:        402

Removed by type:
  Packages:              23
  Package Bodies:        23
  Functions:             24
  Procedures:            7
  Triggers:              17

Retained by type:
  Packages:              38
  Package Bodies:        37
  Functions:             64
  Procedures:            60
  Triggers:              203
============================================================

Cleaned DDL written to: cleaned_ddl.sql

Validating cleaned DDL for orphaned packages...

[INFO] Found 1 spec-only package(s) (may be intentional):
  - PACKAGE CURSOR_PKG (specification exists without body)

Spec-only packages are valid if they only define types/constants.
```

---

## Script Documentation

### 1. Extract SQL Objects

**Purpose**: Parse a SQL DDL file and extract all database objects into a structured JSON format.

**Command**:
```bash
python extract_sql_objects.py [OPTIONS]
```

**Options**:
| Option | Default | Description |
|--------|---------|-------------|
| `-i, --input` | `SQL-Golden-Axium-DDL.ddl` | Path to input DDL file |
| `-o, --output` | `extracted_sql_objects.json` | Path to output JSON file |

**Object Types Extracted**:
- **PACKAGE** - Package specifications (interface/declarations)
- **PACKAGE BODY** - Package implementations (actual code)
- **FUNCTION** - Standalone function definitions
- **PROCEDURE** - Standalone procedure definitions
- **TRIGGER** - Database triggers (event-driven code)

**Examples**:
```bash
# Basic usage with defaults
python extract_sql_objects.py

# Custom input file
python extract_sql_objects.py -i my_ddl.sql

# Custom input and output
python extract_sql_objects.py -i my_ddl.sql -o my_objects.json
```

---

### 2. Cross-Reference SQL Objects

**Purpose**: Scan directories for standalone SQL files and identify which objects from the DDL have corresponding individual files.

**Command**:
```bash
python cross_reference_sql_objects.py [OPTIONS]
```

**Options**:
| Option | Default | Description |
|--------|---------|-------------|
| `-i, --input` | `extracted_sql_objects.json` | Input JSON from extract_sql_objects.py |
| `-o, --output` | `matched_sql_objects.json` | Output JSON file path |
| `-d, --directory` | `.` (current) | Directory to search recursively for SQL files |
| `--detail-level` | `standard` | Output detail level: minimal/standard/full |
| `--exclude-releases` | None | Exclude release directories (e.g., `7*`, `2024*`) |
| `--only-releases` | None | Only include specific releases |
| `--exclude-schools` | None | Exclude specific school codes |
| `--only-schools` | None | Only include specific school codes |
| `--exclude-custom` | False | Exclude all "Custom *" directories |
| `--exclude-patterns` | None | Exclude paths matching glob patterns |
| `--min-schools N` | None | Only show objects used by at least N schools |
| `--max-schools N` | None | Only show objects used by at most N schools |
| `--only-generic` | False | Only show objects with generic files |
| `--only-school-specific` | False | Only show school-specific objects |
| `--verbose` | False | Show detailed filtering information |

**Detail Levels**:

1. **Minimal** (`--detail-level minimal`):
   ```json
   "matched_objects": {
     "packages": ["OVERNIGHT_PKG", "ARASAT_PKG", ...]
   }
   ```

2. **Standard** (default):
   ```json
   "matched_objects": {
     "packages": [
       {
         "name": "OVERNIGHT_PKG",
         "total_files": 46,
         "schools": ["ACTA", "ALB", ...],
         "school_count": 45,
         "generic_count": 1
       }
     ]
   }
   ```

3. **Full** (`--detail-level full`):
   ```json
   "matched_objects": {
     "packages": [
       {
         "name": "OVERNIGHT_PKG",
         "total_files": 46,
         "schools": ["ACTA", "ALB", ...],
         "school_count": 45,
         "generic_count": 1,
         "files": [
           "Dev/SQL/Packages/OverNight/OverNight_pkg_ACTA.sql",
           "Dev/SQL/Packages/OverNight/OverNight_pkg_ALB.sql",
           ...
         ]
       }
     ]
   }
   ```

**Examples**:

```bash
# Basic usage
python cross_reference_sql_objects.py

# Search specific directory
python cross_reference_sql_objects.py -d ./Dev/SQL

# Exclude old releases
python cross_reference_sql_objects.py --exclude-releases "6*" "7.0*"

# Only include specific release
python cross_reference_sql_objects.py --only-releases "2025*"

# Exclude specific schools
python cross_reference_sql_objects.py --exclude-schools ACTA BUFF

# Only show objects used by 2 or fewer schools
python cross_reference_sql_objects.py --max-schools 2

# Only show generic (non-school-specific) objects
python cross_reference_sql_objects.py --only-generic

# Full detail with verbose output
python cross_reference_sql_objects.py --detail-level full --verbose

# Complex filtering: Only 2025 release, exclude custom, max 5 schools
python cross_reference_sql_objects.py \
  --only-releases "2025*" \
  --exclude-custom \
  --max-schools 5 \
  --verbose
```

---

### 3. Remove Matched Objects

**Purpose**: Remove matched database objects from a DDL file to create a cleaned version containing only objects without standalone files.

**Command**:
```bash
python remove_matched_objects.py [OPTIONS]
```

**Options**:
| Option | Default | Description |
|--------|---------|-------------|
| `-d, --ddl` | `SQL-Golden-Axium-DDL.ddl` | Input DDL file path |
| `-i, --input` | `matched_sql_objects.json` | Cross-reference JSON from step 2 |
| `-o, --output` | `cleaned_ddl.sql` | Output cleaned DDL file path |
| `--dry-run` | False | Preview what would be removed (no output file) |
| `--verbose` | False | Show detailed progress information |

**Auto-Pairing Feature**:

The script automatically pairs package specifications with their bodies to prevent orphaned objects:

- When a **PACKAGE** spec is in the removal list, its **PACKAGE BODY** is automatically added
- When a **PACKAGE BODY** is in the removal list, its **PACKAGE** spec is automatically added
- This ensures packages are always removed as complete units
- Prevents compilation errors from orphaned bodies or specs
- The pairing is reported in the console output

**Validation**:

After creating the cleaned DDL, the script validates it for orphaned packages:

- **Critical orphans** (bodies without specs): Reported as `[ERROR]` - will cause compilation errors
- **Spec-only packages** (specs without bodies): Reported as `[INFO]` - may be intentional (type-only packages)
- **No orphans**: Reported as `[OK]` - DDL is valid

**Examples**:

```bash
# Basic usage with defaults
python remove_matched_objects.py

# Custom files
python remove_matched_objects.py \
  -d my_ddl.sql \
  -i my_matched.json \
  -o my_cleaned.sql

# Dry-run mode (preview only)
python remove_matched_objects.py --dry-run --verbose

# Process with verbose output
python remove_matched_objects.py --verbose
```

---

## Use Cases

### Use Case 1: Audit Code Organization

**Goal**: Understand which objects exist in both the master DDL and as standalone files.

```bash
# Extract all objects
python extract_sql_objects.py -o all_objects.json

# Find matches across entire codebase
python cross_reference_sql_objects.py \
  -i all_objects.json \
  -o audit_results.json \
  --detail-level full \
  --verbose

# Review audit_results.json to see:
# - Which objects have standalone files
# - How many schools use each object
# - Exact file locations
```

### Use Case 2: Create School-Specific DDL

**Goal**: Create a DDL containing only objects used by specific schools.

```bash
# Extract objects
python extract_sql_objects.py

# Find objects for specific schools
python cross_reference_sql_objects.py \
  --only-schools ACTA BUFF MICH \
  -o school_specific.json

# Remove non-school objects from DDL
python remove_matched_objects.py \
  -i school_specific.json \
  -o school_specific_ddl.sql
```

### Use Case 3: Identify Generic vs. School-Specific Objects

**Goal**: Separate generic objects from school-specific ones.

```bash
# Find generic objects
python cross_reference_sql_objects.py \
  --only-generic \
  -o generic_objects.json

# Find school-specific objects
python cross_reference_sql_objects.py \
  --only-school-specific \
  -o school_objects.json
```

### Use Case 4: Prepare Release-Specific DDL

**Goal**: Create a DDL for a specific release version.

```bash
# Extract all objects
python extract_sql_objects.py

# Find objects in release 2025.03
python cross_reference_sql_objects.py \
  -d ./Rel2025.03 \
  --only-releases "2025.03" \
  -o release_2025_03.json

# Create release-specific DDL
python remove_matched_objects.py \
  -i release_2025_03.json \
  -o ddl_release_2025_03.sql
```

### Use Case 5: Clean Up Redundant Objects

**Goal**: Reduce DDL size by removing objects that exist as standalone files.

```bash
# Complete workflow
python extract_sql_objects.py
python cross_reference_sql_objects.py
python remove_matched_objects.py

# Result: cleaned_ddl.sql contains only unique objects
```

---

## Advanced Usage

### Combining Filters

You can combine multiple filters for precise control:

```bash
# Find objects in 2025 releases, excluding custom directories,
# used by 2-5 schools, excluding ACTA and BUFF
python cross_reference_sql_objects.py \
  --only-releases "2025*" \
  --exclude-custom \
  --min-schools 2 \
  --max-schools 5 \
  --exclude-schools ACTA BUFF \
  --verbose
```

### Wildcard Patterns

Release and pattern filters support wildcards:

```bash
# Match all 7.x releases
--exclude-releases "7*"

# Match specific versions
--only-releases "2024.03" "2025*"

# Exclude patterns
--exclude-patterns "**/Archive/**" "**/Backup/**"
```

### School Code Detection

The scripts automatically detect school codes from:
- Folder names: `Dev/SQL/Packages/OverNight/ACTA/...`
- File suffixes: `OverNight_pkg_ACTA.sql`

School codes are:
- 2-6 uppercase letters
- Excluded common terms: SQL, PKG, BODY, TRG, FNC, PROC, DEV, PROD, TEST

---

## Output Formats

### JSON Structure

All JSON outputs follow a consistent structure:

```json
{
  "metadata": {
    "source_file": "...",
    "scan_date": "ISO timestamp",
    "total_objects": 123
  },
  "summary": {
    "packages": 10,
    "package_bodies": 10,
    "functions": 15,
    "procedures": 10,
    "triggers": 5
  },
  "matched_objects": { /* varies by detail level */ },
  "file_mappings": [ /* array of file-to-object mappings */ ]
}
```

### File Mappings

The `file_mappings` array provides detailed information:

```json
{
  "file": "relative/path/to/file.sql",
  "object_type": "package",
  "object_name": "OVERNIGHT_PKG",
  "school": "ACTA"  // or null if generic
}
```

---

## Troubleshooting

### Issue: Script can't find DDL file

**Error**: `FileNotFoundError: DDL file not found`

**Solution**: 
```bash
# Check if file exists
ls -la SQL-Golden-Axium-DDL.ddl

# Use absolute path
python extract_sql_objects.py -i "C:/full/path/to/ddl.sql"
```

### Issue: No objects matched

**Problem**: Cross-reference finds 0 matches

**Solutions**:
1. Check directory path:
   ```bash
   python cross_reference_sql_objects.py -d ./Dev/SQL --verbose
   ```

2. Verify object names are in DDL:
   ```bash
   # Check extracted objects
   python -c "import json; print(json.load(open('extracted_sql_objects.json'))['summary'])"
   ```

3. Check case sensitivity (scripts handle this automatically)

### Issue: Encoding errors

**Error**: `UnicodeDecodeError`

**Solution**: Scripts use UTF-8 by default. If files use different encoding:
- Convert files to UTF-8
- Or modify script to use different encoding

### Issue: Memory errors with large DDL

**Problem**: Script runs out of memory

**Solution**: The `remove_matched_objects.py` script processes line-by-line, so this shouldn't occur. If it does:
- Close other applications
- Use a machine with more RAM
- Process in smaller chunks

### Issue: Filters not working as expected

**Problem**: Objects still appear despite filters

**Solution**:
1. Use `--verbose` to see what's being filtered:
   ```bash
   python cross_reference_sql_objects.py --exclude-schools ACTA --verbose
   ```

2. Check filter syntax:
   - Wildcards: `"7*"` not `7*` (use quotes)
   - Multiple values: `--exclude-schools ACTA BUFF` (space-separated)

### Issue: Orphaned package warnings

**Warning**: `[INFO] Found 1 spec-only package(s)`

**Explanation**: This is informational, not an error. Some packages are intentionally spec-only (they only define types or constants without implementation).

**Example**: `cursor_pkg` only defines a `weakcursor` type - this is valid SQL.

**Action**: Review the package. If it's a type-only package, ignore the warning.

---

**Error**: `[ERROR] Found N CRITICAL orphan(s) - bodies without specs`

**Explanation**: A package body exists without its specification. This WILL cause compilation errors.

**Solution**: This should not happen with auto-pairing enabled. If you see this:
1. Check if the input JSON was manually edited
2. Verify the original DDL has both spec and body
3. Report as a bug if auto-pairing failed

---

## Performance Notes

- **Extract**: ~1-2 seconds for 237K line DDL
- **Cross-reference**: ~8-20 seconds for 58K files
- **Remove**: ~1-2 seconds for 237K line DDL

Total workflow: **~10-25 seconds**

---

## Technical Details

### Supported Database Objects

| Type | Detection Pattern | Terminator |
|------|------------------|------------|
| PACKAGE | `CREATE OR REPLACE PACKAGE name IS` | `END;` |
| PACKAGE BODY | `CREATE OR REPLACE PACKAGE BODY name IS` | `END;` |
| FUNCTION | `CREATE OR REPLACE FUNCTION name(...)` | `END$function$;` |
| PROCEDURE | `CREATE OR REPLACE PROCEDURE name(...)` | `END$procedure$;` |
| TRIGGER | `CREATE TRIGGER name ...` | `END$trigger$;` |

### Regex Patterns

All scripts use the same regex pattern for consistency:

```python
r'^CREATE\s+(?:OR\s+REPLACE\s+)?(PACKAGE\s+BODY|PACKAGE|FUNCTION|PROCEDURE|TRIGGER)\s+([^\s(]+)'
```

This pattern:
- Matches at line start (`^`)
- Handles optional `OR REPLACE`
- Distinguishes `PACKAGE BODY` from `PACKAGE`
- Captures object type and name
- Case-insensitive matching

### Auto-Pairing Mechanism

The `remove_matched_objects.py` script implements automatic package pairing:

**Algorithm**:
1. Load matched objects from cross-reference JSON
2. For each PACKAGE in the removal set, add its PACKAGE BODY
3. For each PACKAGE BODY in the removal set, add its PACKAGE
4. Process DDL with the expanded removal set
5. Validate output for any remaining orphans

**Why It's Needed**:
- Oracle/PostgreSQL require package specs and bodies to exist together
- Removing only a spec leaves an orphaned body → compilation error
- Removing only a body leaves an orphaned spec → may cause errors
- Auto-pairing ensures packages are always removed as complete units

**Edge Cases Handled**:
- **Spec-only packages**: Detected and reported as `[INFO]` (not errors)
- **Already paired**: No duplicates added if both are already in the set
- **Missing counterpart**: If a package has no body in the DDL, only the spec is removed

---

## File Structure

```
services/
├── SQL-Golden-Axium-DDL.ddl          # Master DDL file
├── extract_sql_objects.py            # Script 1: Extract objects
├── cross_reference_sql_objects.py    # Script 2: Cross-reference
├── remove_matched_objects.py         # Script 3: Remove objects
├── README.md                         # This file
├── extracted_sql_objects.json        # Output from script 1
├── matched_sql_objects.json          # Output from script 2
├── cleaned_ddl.sql                   # Output from script 3
└── Dev/                              # SQL files directory
    └── SQL/
        ├── Packages/
        ├── Functions/
        ├── Procedures/
        └── ...
```

---

## Contributing

When modifying these scripts:

1. Maintain consistent regex patterns across all scripts
2. Preserve UTF-8 encoding support
3. Keep case-insensitive matching
4. Update this README with any new features
5. Test with the complete workflow

---

## Author

Generated for Axium Services  
Date: 2026-01-23

---

## License

Internal use only - Axium Services
