sqlmapper

README

SQLMapper

SQLMapper is a powerful Go library that enables SQL dump conversion between different database systems. It provides a comprehensive solution for database schema migration, comparison, and conversion tasks.

Features

• Multi-Database Support:

  • MySQL
  • PostgreSQL
  • SQLite
  • Oracle
  • SQL Server

• Core Functionalities:

  • SQL dump parsing and conversion
  • Schema migration support
  • Database schema comparison
  • Structured logging system
  • Thread-safe operations

Installation

go get github.com/mstgnz/sqlmapper

Quick Start

Basic Usage

import "github.com/mstgnz/sqlmapper"

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

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

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

Schema Comparison

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

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

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

Supported Database Objects

• Tables with columns, indexes, and constraints
• Stored procedures and functions
• Triggers
• Views (including materialized views)
• Sequences
• Extensions
• Permissions
• User-defined types
• Partitions
• Database links
• Tablespaces
• Roles and users
• Clusters
• And more...

Development

Prerequisites

• Go 1.16 or higher
• Docker (for running test databases)

Running Tests

# Start test databases
docker-compose up -d

# Run tests
go test ./...

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

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

Support

For support and questions, please open an issue in the GitHub repository.

Documentation

For detailed documentation and examples, visit our GitHub repository.

Documentation

Overview

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

SQLPORTER (Parser, Mapper, Converter, Migrator, etc.) 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/sqlmapper"

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

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

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

Migration Support:

The package provides migration support through the migration package:

import "github.com/mstgnz/sqlmapper/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/sqlmapper/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/sqlmapper/logger"

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

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/sqlmapper

Index

• type Cluster
• type Column
• type Constraint
• type DatabaseLink
• type DatabaseType
• type Extension
• type Function
• type Index
• type MaterializedViewLog
• type Parameter
• type Parser
• type Partition
• type Permission
• type Procedure
• type Role
• type Row
• type Schema
• type Sequence
• type StorageClause
• type SubPartition
• type Table
• type Tablespace
• type Trigger
• type Type
• type User
• type UserDefinedType
• type View

Types

type Cluster

type Cluster struct {
	Name       string
	Schema     string
	TableSpace string
	Key        []string
	Tables     []string
	Size       int
	HashKeys   int
	Storage    *StorageClause
}

Cluster represents Oracle cluster information

type Column

type Column struct {
	Name            string
	DataType        string
	Length          int
	Scale           int
	Precision       int
	IsNullable      bool `default:"true"`
	DefaultValue    string
	AutoIncrement   bool
	IsPrimaryKey    bool
	IsUnique        bool
	Comment         string
	Order           int
	CheckExpression string
}

Column represents a table column

type Constraint

type Constraint struct {
	Name            string
	Type            string // PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK
	Columns         []string
	RefTable        string
	RefColumns      []string
	UpdateRule      string
	DeleteRule      string
	CheckExpression string
	Deferrable      bool
	Initially       string // IMMEDIATE, DEFERRED
}

Constraint represents a table constraint

type DatabaseLink

type DatabaseLink struct {
	Name        string
	Owner       string
	ConnectInfo string
	Public      bool
}

DatabaseLink represents database link information

type DatabaseType

type DatabaseType string

DatabaseType represents the supported database types

const (
	MySQL      DatabaseType = "mysql"
	PostgreSQL DatabaseType = "postgresql"
	SQLServer  DatabaseType = "sqlserver"
	Oracle     DatabaseType = "oracle"
	SQLite     DatabaseType = "sqlite"
)

type Extension

type Extension struct {
	Name    string
	Version string
	Schema  string
}

Extension represents a database extension

type Function

type Function struct {
	Name       string
	Schema     string
	Parameters []Parameter
	Returns    string
	Body       string
	Language   string
	IsProc     bool
}

Function represents a database function

type Index

type Index struct {
	Name        string
	Columns     []string
	IsUnique    bool
	Type        string // BTREE, HASH etc.
	Condition   string // WHERE clause
	TableSpace  string
	Storage     *StorageClause
	Compression bool
}

Index represents a table index

type MaterializedViewLog

type MaterializedViewLog struct {
	Name           string
	Schema         string
	TableName      string
	Columns        []string
	RowID          bool
	PrimaryKey     bool
	SequenceNumber bool
	CommitSCN      bool
	Storage        *StorageClause
}

MaterializedViewLog represents materialized view log information

type Parameter

type Parameter struct {
	Name      string
	DataType  string
	Direction string // IN, OUT, INOUT
	Default   string
}

Parameter represents a procedure or function parameter

type Parser

type Parser interface {
	Parse(content string) (*Schema, error)
	Generate(schema *Schema) (string, error)
}

Parser represents an interface for database dump operations

type Partition

type Partition struct {
	Name          string
	Type          string // RANGE, LIST, HASH
	SubPartitions []SubPartition
	Expression    string
	Values        []string
	TableSpace    string
	Storage       *StorageClause
}

Partition represents table partition information

type Permission

type Permission struct {
	Type       string // GRANT, REVOKE
	Privileges []string
	Object     string
	Grantee    string
	WithGrant  bool
}

Permission represents a database permission

type Procedure

type Procedure struct {
	Name          string
	Schema        string
	Parameters    []Parameter
	Body          string
	Language      string
	Security      string // DEFINER, INVOKER
	SQLSecurity   string
	Deterministic bool
	Comment       string
}

Procedure represents a stored procedure

type Role

type Role struct {
	Name        string
	Password    string
	Permissions []Permission
	Members     []string
	System      bool
}

Role represents database role information

type Row

type Row struct {
	Values map[string]interface{}
}

Row represents table data

type Schema

type Schema struct {
	Name             string
	Tables           []Table
	Procedures       []Procedure
	Functions        []Function
	Triggers         []Trigger
	Views            []View
	Sequences        []Sequence
	Extensions       []Extension
	Permissions      []Permission
	UserDefinedTypes []UserDefinedType
	Partitions       map[string][]Partition // table_name -> partitions
	DatabaseLinks    []DatabaseLink
	Tablespaces      []Tablespace
	Roles            []Role
	Users            []User
	Clusters         []Cluster
	MaterializedLogs []MaterializedViewLog
	Types            []Type
}

Schema represents a database schema

type Sequence

type Sequence struct {
	Name        string
	Schema      string
	IncrementBy int
	MinValue    int
	MaxValue    int
	StartValue  int
	Cache       int
	Cycle       bool
}

Sequence represents a database sequence

type StorageClause

type StorageClause struct {
	Initial     int64
	Next        int64
	MinExtents  int
	MaxExtents  int
	Pctincrease int
	Buffer      int
	TableSpace  string
	Logging     bool
}

StorageClause represents storage properties

type SubPartition

type SubPartition struct {
	Name       string
	Type       string
	Expression string
	Values     []string
	TableSpace string
	Storage    *StorageClause
}

SubPartition represents table sub-partition information

type Table

type Table struct {
	Name        string
	Schema      string
	Columns     []Column
	Indexes     []Index
	Constraints []Constraint
	Data        []Row
	TableSpace  string
	Storage     *StorageClause
	Temporary   bool
	Comment     string
}

Table represents a database table

type Tablespace

type Tablespace struct {
	Name        string
	Type        string // PERMANENT, TEMPORARY
	Status      string
	Autoextend  bool
	MaxSize     int64
	InitialSize int64
	DataFile    string
	BlockSize   int
	Logging     bool
}

Tablespace represents tablespace information

type Trigger

type Trigger struct {
	Name       string
	Schema     string
	Table      string
	Timing     string
	Event      string
	Body       string
	Condition  string
	ForEachRow bool
}

Trigger represents a database trigger

type Type

type Type struct {
	Name       string
	Schema     string
	Kind       string // ENUM, COMPOSITE, DOMAIN, etc.
	Definition string
}

Type represents a database type

type User

type User struct {
	Name        string
	Password    string
	DefaultRole string
	Roles       []string
	Permissions []Permission
	Profile     string
	Status      string
	TableSpace  string
	TempSpace   string
}

User represents database user information

type UserDefinedType

type UserDefinedType struct {
	Name       string
	Schema     string
	BaseType   string
	Properties map[string]interface{}
}

UserDefinedType represents custom data types

type View

type View struct {
	Name           string
	Schema         string
	Definition     string
	IsMaterialized bool
}

View represents a database view