Oracle-to-SQL Server Conversion Tool - Plugin Architecture

Overview

This project implements an extensible, plugin-based architecture for converting Oracle SQL to SQL Server T-SQL, replacing the previous hardcoded conversion logic with a flexible, maintainable system.

Architecture Overview

Conversion Tool Main Script
        │
Plugin Architecture Layer
├── Plugin Management
├── Rule Engine
├── Configuration Management
└── Error Handling

Plugin Implementations
├── Data Type Conversion
├── Function Conversion
├── DDL Conversion
└── Custom Plugins

Key Features

🔧 Plugin-Based Architecture

• Modular Design: Independent plugins for different conversion types
• Easy Extension: Add new conversions without modifying core code
• Plugin Discovery: Automatic loading and registration of plugins
• Priority System: Configurable execution order and priorities

⚙️ Rule Engine Framework

• JSON-Based Rules: Human-readable rule definitions
• Pattern Matching: Regex and function-based condition matching
• Transformation Engine: Multiple transformation types (PowerShell, Regex, Template)
• Rule Validation: Comprehensive validation and testing framework

🔧 Configuration Management

• Hierarchical Configuration: System → Global → Project → Environment → Runtime
• Environment Support: Development, Test, Production configurations
• Schema Validation: JSON Schema-based configuration validation

Documentation

📋 Core Documentation

• Plugin Architecture Specification: Complete plugin system design
• Rule Engine Design: Rule processing framework details
• Configuration Schema Specification: Configuration management system
• Migration Strategy: Transition plan from hardcoded to plugin system

Quick Start

Prerequisites

• PowerShell 5.1 or PowerShell 7+
• SQL Server connection permissions
• Oracle SQL files for conversion

Basic Usage

1. Configure the environment

   # Copy configuration template
   Copy-Item "Config\Templates\default.json" "Config\conversion.json"

2. Run conversion

   .\MainLocalScriptSqlServer.ps1 -Environment development

Project Structure

oracle-to-sqlserver-conversion/
├── MainLocalScriptSqlServer.ps1          # Main conversion script
├── README.md                             # This file
├── PluginArchitectureSpecification.md    # Plugin system design
├── RuleEngineDesign.md                   # Rule engine framework
├── ConfigurationSchemaSpecification.md   # Configuration management
├── MigrationStrategy.md                  # Migration guide
├── Plugins/                              # Plugin implementations
├── Rules/                               # Rule definitions
├── Config/                             # Configuration files
├── Tests/                              # Test suites
└── Tools/                              # Development tools

Plugin Development

Creating a New Plugin

1. Use the plugin template

   .\Tools\New-Plugin.ps1 -Name "MyCustomPlugin" -Category "Custom"

2. Implement the plugin class

   class MyCustomPlugin : ConversionPlugin {
       [string] Execute([string]$input, [hashtable]$context) {
           # Your conversion logic here
           return $input
       }
   
       [bool] CanProcess([string]$input, [hashtable]$context) {
           # Return true if this plugin can handle the input
           return $true
       }
   }

Configuration Examples

Basic Configuration

{
  "global": {
    "pluginDirectory": "./Plugins",
    "ruleDirectory": "./Rules"
  },
  "plugins": {
    "DataTypeConversionPlugin": {
      "enabled": true,
      "priority": 100
    }
  },
  "logging": {
    "level": "INFO"
  }
}

Migration Benefits

• ✅ Maintainability: Easier to modify and extend conversion logic
• ✅ Testability: Individual components can be tested in isolation
• ✅ Reliability: Better error handling and recovery mechanisms
• ✅ Performance: Optimized processing with caching and parallelization
• ✅ Flexibility: Support for custom conversions and business rules

Performance Characteristics

• Small Files (<1MB): <5 seconds conversion time
• Medium Files (1-10MB): <30 seconds conversion time
• Large Files (>10MB): <2 minutes conversion time
• Memory Usage: <500MB peak for large file processing

Error Handling

Error Types

• Plugin Errors: Issues with plugin loading or execution
• Rule Errors: Problems with rule definitions or execution
• Configuration Errors: Invalid configuration files or settings
• Conversion Errors: Issues during SQL conversion process

Recovery Mechanisms

• Graceful Degradation: Continue with alternative approaches
• Automatic Fallback: Revert to legacy conversion if needed
• Partial Recovery: Save successfully converted portions
• User Guidance: Clear error messages with resolution steps

Contributing

Development Workflow

1. Create a feature branch from main
2. Implement your changes following the plugin architecture
3. Add comprehensive tests
4. Update documentation
5. Submit a pull request

Support

Getting Help

• 📖 Documentation: Check the detailed guides in the Documentation/ folder
• 🧪 Examples: Review test files for usage examples
• 🛠️ Tools: Use the provided tools for configuration and testing

---

For detailed technical specifications, see the Plugin Architecture Specification