sqlporter

README

SQLPORTER (Parser, Mapper, Converter, Migrator, etc.)

A high-performance SQL parser and converter library written in Go, designed to handle large-scale database schema migrations and transformations.

Features

• Multi-Database Support: Parse and convert SQL schemas between different database systems

  • PostgreSQL
  • MySQL
  • SQLite
  • SQL Server
  • Oracle

• High Performance:

  • Memory-optimized processing
  • Concurrent execution with worker pools
  • Batch processing capabilities
  • Streaming support for large files

• Advanced Parsing:

  • CREATE TABLE statements
  • ALTER TABLE operations
  • DROP TABLE commands
  • INDEX management
  • Constraints handling
  • Data type mappings

• Memory Management:

  • Efficient buffer pooling
  • Garbage collection optimization
  • Memory usage monitoring
  • Resource cleanup

Requirements

• Go 1.23 or higher
• No external database dependencies required

Installation

go get github.com/mstgnz/sqlporter

Quick Start

package main

import (
    "context"
    "fmt"
    "github.com/mstgnz/sqlporter/parser"
    "time"
)

func main() {
    // Create context with timeout
    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Minute)
    defer cancel()

    // Initialize parser with memory optimization
    memOptimizer := parser.NewMemoryOptimizer(1024, 0.8) // 1GB max memory
    go memOptimizer.MonitorMemory(ctx)

    // Create PostgreSQL parser
    postgresParser := parser.NewPostgresParser()

    // Parse SQL
    sql := `CREATE TABLE users (
        id INT PRIMARY KEY,
        username VARCHAR(50) NOT NULL,
        email VARCHAR(100) UNIQUE,
        created_at TIMESTAMP
    )`

    entity, err := postgresParser.Parse(sql)
    if err != nil {
        panic(err)
    }

    fmt.Printf("Parsed table: %s\n", entity.Tables[0].Name)
}

Advanced Usage

Worker Pool

wp := parser.NewWorkerPool(parser.WorkerConfig{
    Workers:      4,
    QueueSize:    1000,
    MemOptimizer: memOptimizer,
    ErrHandler: func(err error) {
        log.Printf("Worker error: %v", err)
    },
})

wp.Start(ctx)
defer wp.Stop()

Batch Processing

bp := parser.NewBatchProcessor(parser.BatchConfig{
    BatchSize:    100,
    Workers:      4,
    Timeout:      30 * time.Second,
    MemOptimizer: memOptimizer,
    ErrorHandler: func(err error) {
        log.Printf("Batch error: %v", err)
    },
})

Stream Processing

sp := parser.NewStreamParser(parser.StreamParserConfig{
    Workers:      4,
    BatchSize:    1024 * 1024, // 1MB
    BufferSize:   32 * 1024,   // 32KB
    Timeout:      30 * time.Second,
    MemOptimizer: memOptimizer,
})

Performance Optimization

The library includes several features for optimizing performance:

1. Memory Management:

   • Buffer pooling
   • GC threshold control
   • Memory usage monitoring

2. Concurrent Processing:

   • Worker pools
   • Batch processing
   • Stream parsing

3. Resource Management:

   • Automatic cleanup
   • Resource pooling
   • Timeout handling

Contributing

1. Fork the repository
2. Create your feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add some amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

License

This project is licensed under the MIT License - see the LICENSE file for details.

Acknowledgments

• Thanks to all contributors who have helped with the development
• Special thanks to the Go community for their excellent tools and libraries

Contact

• GitHub: @mstgnz
• Project Link: https://github.com/mstgnz/sqlporter

Documentation

Overview

Package sqlporter provides SQL dump conversion functionality between different database systems.

SDC (SQL Dump Converter) is a powerful Go library that allows you to convert SQL dump files between different database systems. This library is particularly useful when you need to migrate a database schema from one system to another.

Basic Usage:

import "github.com/mstgnz/sqlporter"

// Create a MySQL parser
parser := sqlporter.NewMySQLParser()

// Parse MySQL dump
entity, err := parser.Parse(mysqlDump)
if err != nil {
	// handle error
}

// Convert to PostgreSQL
pgParser := sqlporter.NewPostgresParser()
pgSQL, err := pgParser.Convert(entity)

Migration Support:

The package provides migration support through the migration package:

import "github.com/mstgnz/sqlporter/migration"

// Create migration manager
manager := migration.NewMigrationManager(driver)

// Apply migrations
err := manager.Apply(context.Background())

Schema Comparison:

Compare database schemas using the schema package:

import "github.com/mstgnz/sqlporter/schema"

// Create schema comparer
comparer := schema.NewSchemaComparer(sourceTables, targetTables)

// Find differences
differences := comparer.Compare()

Database Support:

The package supports the following databases:

• MySQL
• PostgreSQL
• SQLite
• Oracle
• SQL Server

Each database has its own parser implementation that handles the specific syntax and data types of that database system.

Error Handling:

All operations that can fail return an error as the last return value. Errors should be checked and handled appropriately:

if err != nil {
	switch {
	case errors.IsConnectionError(err):
		// handle connection error
	case errors.IsQueryError(err):
		// handle query error
	default:
		// handle other errors
	}
}

Logging:

The package provides a structured logging system:

import "github.com/mstgnz/sqlporter/logger"

log := logger.NewLogger(logger.Config{
	Level:  logger.INFO,
	Prefix: "[SDC] ",
})

log.Info("Starting conversion", map[string]interface{}{
	"source": "mysql",
	"target": "postgres",
})

Configuration:

Most components can be configured through their respective Config structs:

config := db.Config{
	Driver:   "postgres",
	Host:     "localhost",
	Port:     5432,
	Database: "mydb",
	Username: "user",
	Password: "pass",
}

Thread Safety:

All public APIs in this package are thread-safe and can be used concurrently.

For more information and examples, visit: https://github.com/mstgnz/sqlporter

Index

• type AlterTable
• type Argument
• type Column
• type Constraint
• type DataType
• type DropIndex
• type DropTable
• type Entity
• type ForeignKey
• type Function
• type Index
• type Like
• type Partition
• type Privilege
• type Procedure
• type Schema
• type Sequence
• type Table
• type Trigger
• type View

Types

type AlterTable

type AlterTable struct {
	Schema     string      // Schema name
	Table      string      // Table name
	Action     string      // Action to perform
	Column     *Column     // Column to alter
	NewName    string      // New name for rename operations
	Constraint *Constraint // Constraint to add/modify
}

AlterTable represents an ALTER TABLE statement structure

type Argument

type Argument struct {
	Name     string    // Argument name
	DataType *DataType // Argument data type
	Mode     string    // Argument mode (IN/OUT/INOUT)
	Default  string    // Default value
}

Argument represents a function/procedure argument structure

type Column

type Column struct {
	Name          string      // Column name
	DataType      *DataType   // Column data type information
	Length        int         // Type length
	Precision     int         // Numeric precision
	Scale         int         // Numeric scale
	IsNullable    bool        // Whether NULL values are allowed (parser compatibility)
	Nullable      bool        // Whether NULL values are allowed
	Default       string      // Default value expression
	AutoIncrement bool        // Whether column auto-increments
	PrimaryKey    bool        // Whether column is part of primary key
	Unique        bool        // Whether column has unique constraint
	Check         string      // Check constraint expression
	ForeignKey    *ForeignKey // Foreign key reference
	Comment       string      // Column comment or description
	Collation     string      // Column-specific collation
	Sparse        bool        // Whether this is a sparse column
	Computed      bool        // Whether this is a computed column
	ComputedExpr  string      // Expression for computed column
	Identity      bool        // Whether this is an identity column
	IdentitySeed  int64       // Identity seed value
	IdentityIncr  int64       // Identity increment value
	FileStream    bool        // Whether this is a FileStream column
	FileGroup     string      // FileGroup for the column
	RowGUIDCol    bool        // Whether this is a rowguid column
	Persisted     bool        // Whether computed column is persisted
	Extra         string      // Additional column attributes (parser compatibility)
}

Column represents a database column structure. It contains all information about a column including its data type, constraints, and various database-specific features.

type Constraint

type Constraint struct {
	Name         string   // Constraint name
	Type         string   // Constraint type (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK)
	Columns      []string // Constrained columns
	RefTable     string   // Referenced table (for foreign keys)
	RefColumns   []string // Referenced columns (for foreign keys)
	OnDelete     string   // Action on delete (for foreign keys)
	OnUpdate     string   // Action on update (for foreign keys)
	Check        string   // Check constraint expression
	Clustered    bool     // Whether this is a clustered constraint
	NonClustered bool     // Whether this is a non-clustered constraint
	FileGroup    string   // FileGroup for the constraint
}

Constraint represents a table constraint. It defines various types of constraints that can be applied to a table.

type DataType

type DataType struct {
	Name      string // Base type name
	Length    int    // Type length (e.g., VARCHAR(255))
	Precision int    // Numeric precision
	Scale     int    // Numeric scale
}

DataType represents a column's data type specification. It includes all type-specific information including precision, scale, and various type modifiers.

type DropIndex

type DropIndex struct {
	Schema   string // Schema name
	Table    string // Table name
	Index    string // Index name
	IfExists bool   // Whether to use IF EXISTS clause
	Cascade  bool   // Whether to cascade the drop operation
}

DropIndex represents a DROP INDEX statement structure

type DropTable

type DropTable struct {
	Schema   string // Schema name
	Table    string // Table name
	IfExists bool   // Whether to use IF EXISTS clause
	Cascade  bool   // Whether to cascade the drop operation
}

DropTable represents a DROP TABLE statement structure

type Entity

type Entity struct {
	Tables     []*Table     // List of tables in the database
	Sequences  []*Sequence  // List of sequences in the database
	Views      []*View      // List of views in the database
	Functions  []*Function  // List of functions in the database
	Procedures []*Procedure // List of stored procedures in the database
	Triggers   []*Trigger   // List of triggers in the database
	Schemas    []*Schema    // List of schemas in the database
}

Entity represents the database structure. It contains all tables and their relationships in the database.

type ForeignKey

type ForeignKey struct {
	Constraint        // Embedded constraint properties
	Name       string // Constraint name
	Column     string // Source column
	RefTable   string // Referenced table name
	RefColumn  string // Referenced column
	OnDelete   string // Action on delete (CASCADE, SET NULL, etc.)
	OnUpdate   string // Action on update
	Clustered  bool   // Whether this is a clustered foreign key
	FileGroup  string // FileGroup for the foreign key
}

ForeignKey represents a foreign key constraint. It defines relationships between tables through their columns.

type Function

type Function struct {
	Name       string       // Function name
	Schema     string       // Schema name
	Arguments  []*Argument  // List of function arguments
	Returns    string       // Return type
	Body       string       // Function body
	Language   string       // Function language
	Owner      string       // Function owner
	Comment    string       // Function comment
	Privileges []*Privilege // List of privileges on the function
}

Function represents a database function structure

type Index

type Index struct {
	Name           string            // Index name
	Schema         string            // Schema name
	Table          string            // Table name
	Columns        []string          // Indexed columns
	Unique         bool              // Whether this is a unique index
	Clustered      bool              // Whether this is a clustered index
	NonClustered   bool              // Whether this is a non-clustered index
	FileGroup      string            // FileGroup for the index
	Filter         string            // Filter condition
	IncludeColumns []string          // Included (covered) columns
	Options        map[string]string // Index options
}

Index represents an index structure. It defines how table data is indexed for faster access.

type Like

type Like struct {
	Table         string   // Template table name
	Including     []string // Properties to include
	Excluding     []string // Properties to exclude
	WithDefaults  bool     // Include default values
	WithIndexes   bool     // Include indexes
	WithStorage   bool     // Include storage parameters
	WithComments  bool     // Include comments
	WithCollation bool     // Include collation
}

Like represents a LIKE clause specification. It defines which properties to inherit from a template table.

type Partition

type Partition struct {
	Name      string   // Partition name
	Type      string   // Partition type (RANGE/LIST/HASH)
	Columns   []string // Partitioning columns
	Values    []string // Partition values/bounds
	FileGroup string   // FileGroup for the partition
}

Partition represents table partitioning information. It defines how table data is partitioned across multiple storage units.

type Privilege

type Privilege struct {
	Grantee     string // Privilege grantee
	Privilege   string // Privilege type
	Grantor     string // Privilege grantor
	GrantOption bool   // Whether grant option is included
}

Privilege represents a privilege structure

type Procedure

type Procedure struct {
	Name       string       // Procedure name
	Schema     string       // Schema name
	Arguments  []*Argument  // List of procedure arguments
	Body       string       // Procedure body
	Language   string       // Procedure language
	Owner      string       // Procedure owner
	Comment    string       // Procedure comment
	Privileges []*Privilege // List of privileges on the procedure
}

Procedure represents a stored procedure structure

type Schema

type Schema struct {
	Name       string       // Schema name
	Owner      string       // Schema owner
	Comment    string       // Schema comment
	Privileges []*Privilege // List of privileges on the schema
}

Schema represents a database schema structure

type Sequence

type Sequence struct {
	Name       string       // Sequence name
	Schema     string       // Schema name
	Start      int64        // Start value
	Increment  int64        // Increment value
	MinValue   int64        // Minimum value
	MaxValue   int64        // Maximum value
	Cache      int64        // Cache size
	Cycle      bool         // Whether sequence should cycle
	Owner      string       // Sequence owner
	Comment    string       // Sequence comment
	Privileges []*Privilege // List of privileges on the sequence
}

Sequence represents a database sequence structure

type Table

type Table struct {
	Name        string            // Table name
	Schema      string            // Schema name (database/namespace)
	Columns     []*Column         // List of columns in the table
	Constraints []*Constraint     // List of constraints (PRIMARY KEY, FOREIGN KEY, etc.)
	Indexes     []*Index          // List of indexes on the table
	FileGroup   string            // FileGroup name (SQL Server specific)
	Options     map[string]string // Additional table options
	Comment     string            // Table comment or description
	Collation   string            // Default collation for the table
	Inherits    []string          // List of parent tables (PostgreSQL INHERITS)
	Partitions  []*Partition      // Partition information
	Like        *Like             // LIKE template specification
	Unlogged    bool              // Whether this is an unlogged table
	Temporary   bool              // Whether this is a temporary table
	IfNotExists bool              // Whether to use IF NOT EXISTS clause
	PrimaryKey  *Constraint       // Primary key constraint
	ForeignKeys []*ForeignKey     // List of foreign key constraints
	TableSpace  string            // TableSpace name
}

Table represents a database table structure. It contains all information about a table including columns, constraints, indexes, and various database-specific features.

type Trigger

type Trigger struct {
	Name       string       // Trigger name
	Schema     string       // Schema name
	Table      string       // Table name
	Event      string       // Triggering event (INSERT/UPDATE/DELETE)
	Timing     string       // Trigger timing (BEFORE/AFTER/INSTEAD OF)
	Body       string       // Trigger body/function
	Condition  string       // Conditional expression (WHEN clause)
	Owner      string       // Owner of the trigger
	Comment    string       // Trigger comment or description
	Privileges []*Privilege // List of privileges on the trigger
}

Trigger represents a trigger definition. It defines automated actions to be taken on specific table events.

type View

type View struct {
	Name         string       // View name
	Schema       string       // Schema name
	Query        string       // View query
	Materialized bool         // Whether this is a materialized view
	Owner        string       // View owner
	Comment      string       // View comment
	Privileges   []*Privilege // List of privileges on the view
}

View represents a database view structure