Skip to content

Latest commit

 

History

History
225 lines (181 loc) · 4.59 KB

api.md

File metadata and controls

225 lines (181 loc) · 4.59 KB

SQLMapper API Documentation

Overview

SQLMapper provides a comprehensive API for converting SQL schemas between different database systems. This document outlines the main components and their usage.

Table of Contents

Parser API

Creating a Parser

parser := mysql.NewMySQL()    // For MySQL
parser := postgres.NewPostgreSQL()  // For PostgreSQL
parser := sqlite.NewSQLite()  // For SQLite
parser := oracle.NewOracle()  // For Oracle
parser := sqlserver.NewSQLServer()  // For SQL Server

Each parser implements the Parser interface:

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

Parsing SQL

// Parse SQL content into a schema
schema, err := parser.Parse(sqlContent)
if err != nil {
    log.Fatal(err)
}

Generating SQL

// Generate SQL from a schema
sql, err := parser.Generate(schema)
if err != nil {
    log.Fatal(err)
}

Schema API

The Schema structure represents a complete database schema:

type Schema struct {
    Name        string
    Tables      []Table
    Views       []View
    Triggers    []Trigger
    Sequences   []Sequence
    Functions   []Function
    Procedures  []Procedure
    // ... other fields
}

Table Structure

type Table struct {
    Name        string
    Schema      string
    Columns     []Column
    Indexes     []Index
    Constraints []Constraint
    Comment     string
}

Column Structure

type Column struct {
    Name          string
    DataType      string
    Length        int
    Scale         int
    IsNullable    bool
    DefaultValue  string
    AutoIncrement bool
    IsPrimaryKey  bool
    IsUnique      bool
    Comment       string
}

Error Handling

SQLMapper provides specific error types for different scenarios:

var (
    ErrEmptyContent     = errors.New("empty content")
    ErrInvalidSQL       = errors.New("invalid SQL syntax")
    ErrUnsupportedType  = errors.New("unsupported data type")
    ErrParserNotFound   = errors.New("parser not found for database type")
)

Examples

Basic Usage

package main

import (
    "fmt"
    "github.com/mstgnz/sqlmapper/mysql"
    "github.com/mstgnz/sqlmapper/postgres"
)

func main() {
    // MySQL input
    mysqlSQL := `
    CREATE TABLE users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(255) UNIQUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );`

    // Create parsers
    mysqlParser := mysql.NewMySQL()
    pgParser := postgres.NewPostgreSQL()

    // Parse MySQL
    schema, err := mysqlParser.Parse(mysqlSQL)
    if err != nil {
        panic(err)
    }

    // Generate PostgreSQL
    pgSQL, err := pgParser.Generate(schema)
    if err != nil {
        panic(err)
    }

    fmt.Println(pgSQL)
}

CLI Usage

# Convert MySQL to PostgreSQL
sqlmapper --file=dump.sql --to=postgres

# Convert PostgreSQL to SQLite
sqlmapper --file=schema.sql --to=sqlite

Advanced Usage

package main

import (
    "github.com/mstgnz/sqlmapper"
    "github.com/mstgnz/sqlmapper/mysql"
)

func main() {
    parser := mysql.NewMySQL()
    
    // Parse complex schema
    schema, err := parser.Parse(complexSQL)
    if err != nil {
        panic(err)
    }

    // Modify schema
    for i, table := range schema.Tables {
        // Add timestamps to all tables
        schema.Tables[i].Columns = append(table.Columns,
            sqlmapper.Column{
                Name: "created_at",
                DataType: "TIMESTAMP",
                DefaultValue: "CURRENT_TIMESTAMP",
            },
            sqlmapper.Column{
                Name: "updated_at",
                DataType: "TIMESTAMP",
                DefaultValue: "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
            },
        )
    }

    // Generate modified SQL
    sql, err := parser.Generate(schema)
    if err != nil {
        panic(err)
    }
}

Best Practices

  1. Always check for errors when parsing and generating SQL
  2. Use appropriate parsers for source and target databases
  3. Validate schema modifications before generating SQL
  4. Handle database-specific features carefully
  5. Test conversions with sample data

Limitations

  • Some database-specific features may not be perfectly converted
  • Complex stored procedures might need manual adjustment
  • Custom data types may require special handling
  • Performance may vary with large schemas </rewritten_file>