csvsql

package module
v0.0.0-...-38fd917 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jan 7, 2025 License: MIT Imports: 9 Imported by: 0

README

CSVSQL

Go Reference Go Report Card MIT License

CSVSQL is a powerful Go library that enables SQL-like querying capabilities over CSV files. It provides a fluent query builder interface and supports various SQL operations including SELECT, WHERE, JOIN, and UNION.

🌟 Features

  • 🔍 SQL-like Query Interface: Familiar SQL syntax for querying CSV files
  • 📁 Multiple File Formats:
    • CSV files
    • Excel (XLSX) files
  • 🔄 Rich Query Operations:
    • JOIN operations (INNER, LEFT, RIGHT)
      • Standard column equality joins
      • Custom join conditions with OnFunc
    • WHERE clauses with multiple conditions
      • Standard comparison operators
      • Custom filtering with WhereFunc
    • SELECT operations
      • Standard column selection
      • Custom computed columns with SelectCustom
    • UNION and UNION ALL
    • Column and table aliasing
    • Wildcard selects (SELECT * and table.*)
    • Export query results to CSV
  • 🎯 Advanced Filtering:
    • Support for custom filtering functions
    • Multiple comparison operators
    • LIKE pattern matching
    • Complex conditions with AND/OR
  • 🔒 Type Safety: Type-safe query building with compile-time checks
  • 🚀 Performance: Efficient memory usage and optimized operations
  • 🛡️ Error Handling: Comprehensive error checking and descriptive messages

📦 Installation

go get github.com/gobylor/csvsql

🚀 Quick Start

Basic Setup
package main

import (
    "fmt"
    "github.com/gobylor/csvsql"
)

func main() {
    eng := csvsql.NewEngine()
    eng.CreateTable("users", "data/users.csv")
    eng.CreateTable("orders", "data/orders.xlsx")
}
Basic Query
query, _ := csvsql.NewQuery().
    Select("name", "age", "email").
    From("users").
    Where("age", ">", "25").
    Build()

results, _ := eng.ExecuteQuery(query)
for i, row := range results {
    fmt.Println(row)
}

// Export results to CSV
err := eng.ExportToCSV(query, "output.csv")
if err != nil {
    log.Fatal(err)
}
Using Wildcards
// Select all columns from all involved tables
query, _ := csvsql.NewQuery().
    Select("*").
    From("users").
    InnerJoin("orders").
    On("users", "id", "=", "orders", "user_id").
    Build()

// Select all columns from a specific table
query, _ = csvsql.NewQuery().
    Select("users.*", "orders.amount").
    From("users").
    InnerJoin("orders").
    On("users", "id", "=", "orders", "user_id").
    Build()
Join Operations
// Inner Join
query, _ := csvsql.NewQuery().
    Select("users.name", "orders.product").
    From("users").
    InnerJoin("orders").
    On("users", "id", "=", "orders", "user_id").
    Build()

// Left Join
query, _ = csvsql.NewQuery().
    Select("users.name", "orders.product").
    From("users").
    LeftJoin("orders").
    On("users", "id", "=", "orders", "user_id").
    Build()

// Right Join
query, _ = csvsql.NewQuery().
    Select("users.name", "orders.product").
    From("users").
    RightJoin("orders").
    On("users", "id", "=", "orders", "user_id").
    Build()
Custom Column Computation
// Basic custom column computation
query, _ := csvsql.NewQuery().
    Select("name", "age").
    SelectCustom("age_category", func(row map[string][]string, tables map[string]*csvsql.Table) (string, error) {
        age := csvsql.GetRow(row, tables, "users").Get("age").MustInt()
        
        switch {
        case age < 25:
            return "Young", nil
        case age < 50:
            return "Middle-aged", nil
        default:
            return "Senior", nil
        }
    }).
    From("users").
    Build()

// Complex computed columns
query, _ = csvsql.NewQuery().
    Select("users.name").
    SelectCustom("total_spending", func(row map[string][]string, tables map[string]*csvsql.Table) (string, error) {
        orders := csvsql.GetRow(row, tables, "orders")
        amount := orders.Get("amount").MustFloat()
        tax := orders.Get("tax").MustFloat()
        
        return fmt.Sprintf("%.2f", amount + tax), nil
    }).
    From("users").
    InnerJoin("orders").
    On("users", "id", "=", "orders", "user_id").
    Build()
Advanced Filtering with Custom Functions
// Basic custom filtering
query, _ := csvsql.NewQuery().
    Select("name", "email", "registration_date").
    From("users").
    WhereFunc(func(row map[string][]string, tables map[string]*csvsql.Table) (bool, error) {
        users := csvsql.GetRow(row, tables, "users")
        email := users.Get("email").Must()
        return strings.Contains(email, "@gmail.com"), nil
    }).
    Build()

// Complex filtering with multiple conditions
query, _ = csvsql.NewQuery().
    Select("users.name", "orders.product", "orders.amount").
    From("users").
    InnerJoin("orders").
    On("users", "id", "=", "orders", "user_id").
    WhereFunc(func(row map[string][]string, tables map[string]*csvsql.Table) (bool, error) {
        users := csvsql.GetRow(row, tables, "users")
        orders := csvsql.GetRow(row, tables, "orders")
        
        // Complex filtering logic
        userType := users.Get("user_type").Must()
        orderAmount := orders.Get("amount").MustFloat()
        orderDate := orders.Get("order_date").MustDate()
        
        isVIP := userType == "VIP"
        isHighValue := orderAmount > 1000
        isRecent := orderDate.After(time.Now().AddDate(0, -3, 0))
        
        return isVIP && isHighValue && isRecent, nil
    }).
    Build()

The custom function features allow you to:

  • Create computed columns with complex logic
  • Implement advanced filtering conditions
  • Access and manipulate data from multiple tables
  • Perform type-safe operations with built-in conversion methods
  • Combine multiple conditions in sophisticated ways
Advanced Filtering
// Multiple conditions with AND
query, _ := csvsql.NewQuery().
    Select("name", "age", "email").
    From("users").
    Where("age", ">", "30").
    And(csvsql.NewQuery().Where("age", "<", "50")).
    Build()

// Pattern matching with LIKE
query, _ = csvsql.NewQuery().
    Select("name", "email").
    From("users").
    Where("email", "LIKE", "%@gmail.com").
    Build()

// Custom filtering function
query, _ = csvsql.NewQuery().
    Select("name", "email", "registration_date").
    From("users").
    WhereFunc(func(row map[string][]string, tables map[string]*csvsql.Table) (bool, error) {
        users := csvsql.GetRow(row, tables, "users")
        
        email := users.Get("email").Must()
        regDate := users.Get("registration_date").MustDate()
        
        isGmail := strings.Contains(email, "@gmail.com")
        isBeforeQ2_2023 := regDate.Before(time.Date(2023, 4, 1, 0, 0, 0, 0, time.UTC))
        
        return isGmail && isBeforeQ2_2023, nil
    }).
    Build()
UNION Operations
// UNION (removes duplicates)
highValue := csvsql.NewQuery().
    Select("users.name", "orders.amount").
    From("users").
    InnerJoin("orders").
    On("users", "id", "=", "orders", "user_id").
    Where("orders.amount", ">", "500")

lowValue := csvsql.NewQuery().
    Select("users.name", "orders.amount").
    From("users").
    InnerJoin("orders").
    On("users", "id", "=", "orders", "user_id").
    Where("orders.amount", "<", "100")

query, _ := highValue.Union(lowValue).Build()

// UNION ALL (keeps duplicates)
query, _ = highValue.UnionAll(lowValue).Build()
Custom Join Conditions
// Join with custom condition function
query, _ := csvsql.NewQuery().
    Select("users.name", "orders.product", "orders.amount").
    From("users").
    InnerJoin("orders").
    OnFunc(func(row map[string][]string, tables map[string]*csvsql.Table) (bool, error) {
        users := csvsql.GetRow(row, tables, "users")
        orders := csvsql.GetRow(row, tables, "orders")
        
        // Get values with type conversion using the fluent API
        userId := users.Get("id").Must()
        orderUserId := orders.Get("user_id").Must()
        orderAmount := orders.Get("amount").MustFloat()
        
        // Custom join condition: match user_id and amount > 100
        return userId == orderUserId && orderAmount > 100, nil
    }).
    Build()

// Complex join conditions with multiple criteria
query, _ = csvsql.NewQuery().
    Select("users.name", "orders.product", "inventory.stock").
    From("users").
    InnerJoin("orders").
    OnFunc(func(row map[string][]string, tables map[string]*csvsql.Table) (bool, error) {
        users := csvsql.GetRow(row, tables, "users")
        orders := csvsql.GetRow(row, tables, "orders")
        
        // Complex business logic for joining
        userId := users.Get("id").Must()
        orderUserId := orders.Get("user_id").Must()
        orderDate := orders.Get("order_date").MustDate()
        userType := users.Get("user_type").Must()
        
        // Join only VIP users' orders from the last month
        isVIP := userType == "VIP"
        isRecentOrder := orderDate.After(time.Now().AddDate(0, -1, 0))
        
        return userId == orderUserId && isVIP && isRecentOrder, nil
    }).
    Build()

The OnFunc feature allows you to:

  • Define complex join conditions with custom logic
  • Access and compare multiple columns from both tables
  • Implement business-specific joining rules
  • Perform type conversions and data validation during joins
  • Combine multiple conditions in a single join criteria

🛠️ Supported Operations

Column Selection
  • Regular columns: Select("name", "age")
  • All columns: Select("*")
  • Table-specific columns: Select("users.*")
  • Mixed selection: Select("users.*", "orders.amount")
  • Custom computed columns: SelectCustom("age_category", computeFunc)
Data Access
  • Safe access: row.Get("column")
  • String values: row.Get("column").Must() or row.Get("column").String()
  • Integer values: row.Get("column").MustInt() or row.Get("column").Int()
  • Float values: row.Get("column").MustFloat() or row.Get("column").Float()
  • Date values: row.Get("column").MustDate() or row.Get("column").Date()
  • DateTime values: row.Get("column").MustDateTime() or row.Get("column").DateTime()
  • Custom time format: row.Get("column").MustTime(layout) or row.Get("column").Time(layout)
  • Boolean values: row.Get("column").MustBool() or row.Get("column").Bool()
Comparison Operators
  • = Equal
  • != Not Equal
  • > Greater Than
  • >= Greater Than or Equal
  • < Less Than
  • <= Less Than or Equal
  • LIKE Pattern Matching (supports % and _ wildcards)
Logical Operators
  • AND
  • OR
Join Types
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
Set Operations
  • UNION (removes duplicates)
  • UNION ALL (keeps duplicates)
Pattern Matching
  • % matches any sequence of characters
  • _ matches any single character

📊 Data Types

Supported data types for comparisons:

  • String
  • Integer
  • Float
  • Date (YYYY-MM-DD format)

🤝 Contributing

We welcome contributions! Here's how you can help:

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

For major changes, please open an issue first to discuss what you would like to change.

📄 License

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

Documentation

Index

Constants

View Source 
const (
	DateFormat     = "2006-01-02"
	DateTimeFormat = "2006-01-02 15:04:05"
)

Common date formats

Variables

This section is empty.

Functions

This section is empty.

Types

type ComparisonOperator 

type ComparisonOperator string
const (
	Equal            ComparisonOperator = "="
	NotEqual         ComparisonOperator = "!="
	GreaterThan      ComparisonOperator = ">"
	GreaterThanEqual ComparisonOperator = ">="
	LessThan         ComparisonOperator = "<"
	LessThanEqual    ComparisonOperator = "<="
)

func (ComparisonOperator) Evaluate 

func (op ComparisonOperator) Evaluate(left, right string) (bool, error)

func (ComparisonOperator) String 

func (op ComparisonOperator) String() string

type CompositeCondition 

type CompositeCondition struct {
	Left     Condition
	Right    Condition
	Operator LogicalOperator
}

func NewCompositeCondition 

func NewCompositeCondition(left, right Condition, operator string) (*CompositeCondition, error)

func (*CompositeCondition) Evaluate 

func (c *CompositeCondition) Evaluate(row map[string][]string, tables map[string]*Table) (bool, error)

func (*CompositeCondition) Type 

func (c *CompositeCondition) Type() string

type CompositeJoinCondition 

type CompositeJoinCondition struct {
	Left     JoinConditionEvaluator
	Right    JoinConditionEvaluator
	Operator LogicalOperator
}

func (*CompositeJoinCondition) EvaluateJoin 

func (c *CompositeJoinCondition) EvaluateJoin(row map[string][]string, tables map[string]*Table) (bool, error)

type Condition 

type Condition interface {
	Evaluate(row map[string][]string, tables map[string]*Table) (bool, error)
	Type() string
}

type CustomCondition 

type CustomCondition func(row map[string][]string, tables map[string]*Table) (bool, error)

func (*CustomCondition) Evaluate 

func (fn *CustomCondition) Evaluate(row map[string][]string, tables map[string]*Table) (bool, error)

func (*CustomCondition) Type 

func (fn *CustomCondition) Type() string

type CustomJoinCondition 

type CustomJoinCondition func(row map[string][]string, tables map[string]*Table) (bool, error)

func (CustomJoinCondition) EvaluateJoin 

func (fn CustomJoinCondition) EvaluateJoin(row map[string][]string, tables map[string]*Table) (bool, error)

type CustomSelectField 

type CustomSelectField struct {
	Name string
	Func func(row map[string][]string, tables map[string]*Table) (string, error)
}

type Engine 

type Engine struct {
	// contains filtered or unexported fields
}

func NewEngine 

func NewEngine() *Engine

func (*Engine) CreateTable 

func (e *Engine) CreateTable(alias, filepath string, sheetName ...string) error

func (*Engine) ExecuteQuery 

func (e *Engine) ExecuteQuery(q *Query) ([][]string, error)

func (*Engine) ExportToCSV 

func (e *Engine) ExportToCSV(q *Query, filepath string) error

type ErrInvalidQuery 

type ErrInvalidQuery struct {
	Message string
}

func (*ErrInvalidQuery) Error 

func (e *ErrInvalidQuery) Error() string

type FromComponent 

type FromComponent struct {
	Table string
}

func (*FromComponent) Type 

func (f *FromComponent) Type() string

func (*FromComponent) Validate 

func (f *FromComponent) Validate() error

type JoinComponent 

type JoinComponent struct {
	Table     string
	Condition JoinConditionEvaluator
	JoinType  JoinType
}

func (*JoinComponent) Type 

func (j *JoinComponent) Type() string

func (*JoinComponent) Validate 

func (j *JoinComponent) Validate() error

type JoinCondition 

type JoinCondition struct {
	LeftTable  string
	LeftCol    string
	Op         Operator
	RightTable string
	RightCol   string
}

func (*JoinCondition) EvaluateJoin 

func (jc *JoinCondition) EvaluateJoin(row map[string][]string, tables map[string]*Table) (bool, error)

type JoinConditionEvaluator 

type JoinConditionEvaluator interface {
	EvaluateJoin(row map[string][]string, tables map[string]*Table) (bool, error)
}

type JoinType 

type JoinType int
const (
	InnerJoin JoinType = iota
	LeftJoin
	RightJoin
	FullJoin
)

type JoinedRow 

type JoinedRow struct {
	// contains filtered or unexported fields
}

type LikeOperator 

type LikeOperator struct{}

func (LikeOperator) Evaluate 

func (op LikeOperator) Evaluate(value, pattern string) (bool, error)

func (LikeOperator) String 

func (op LikeOperator) String() string

type LogicalOperator 

type LogicalOperator string

LogicalOperator represents logical operators (AND, OR) 

const (
	And LogicalOperator = "AND"
	Or  LogicalOperator = "OR"
)

func (LogicalOperator) Evaluate 

func (op LogicalOperator) Evaluate(left, right string) (bool, error)

func (LogicalOperator) String 

func (op LogicalOperator) String() string

type Operator 

type Operator interface {
	Evaluate(left, right string) (bool, error)
	String() string
}

func GetOperator 

func GetOperator(op string) (Operator, error)

type Query 

type Query struct {
	Select *SelectComponent
	From   *FromComponent
	Joins  []*JoinComponent
	Where  *WhereComponent
	Union  *UnionComponent
}

type QueryBuilder 

type QueryBuilder struct {
	// contains filtered or unexported fields
}

func NewQuery 

func NewQuery() *QueryBuilder

func Where 

func Where(column, operator, value string) *QueryBuilder

func WhereFunc 

func WhereFunc(fn func(row map[string][]string, tables map[string]*Table) (bool, error)) *QueryBuilder

func (*QueryBuilder) And 

func (qb *QueryBuilder) And(other *QueryBuilder) *QueryBuilder

func (*QueryBuilder) Build 

func (qb *QueryBuilder) Build() (*Query, error)

func (*QueryBuilder) From 

func (qb *QueryBuilder) From(table string) *QueryBuilder

func (*QueryBuilder) InnerJoin 

func (qb *QueryBuilder) InnerJoin(table string) *QueryBuilder

func (*QueryBuilder) LeftJoin 

func (qb *QueryBuilder) LeftJoin(table string) *QueryBuilder

func (*QueryBuilder) On 

func (qb *QueryBuilder) On(leftTable, leftCol, operator, rightTable, rightCol string) *QueryBuilder

func (*QueryBuilder) OnFunc 

func (qb *QueryBuilder) OnFunc(fn func(row map[string][]string, tables map[string]*Table) (bool, error)) *QueryBuilder

func (*QueryBuilder) Or 

func (qb *QueryBuilder) Or(other *QueryBuilder) *QueryBuilder

func (*QueryBuilder) RightJoin 

func (qb *QueryBuilder) RightJoin(table string) *QueryBuilder

func (*QueryBuilder) Select 

func (qb *QueryBuilder) Select(columns ...string) *QueryBuilder

func (*QueryBuilder) SelectCustom 

func (qb *QueryBuilder) SelectCustom(name string, fn func(row map[string][]string, tables map[string]*Table) (string, error)) *QueryBuilder

func (*QueryBuilder) Union 

func (qb *QueryBuilder) Union(other *QueryBuilder) *QueryBuilder

func (*QueryBuilder) UnionAll 

func (qb *QueryBuilder) UnionAll(other *QueryBuilder) *QueryBuilder

func (*QueryBuilder) Where 

func (qb *QueryBuilder) Where(column, operator, value string) *QueryBuilder

func (*QueryBuilder) WhereFunc 

func (qb *QueryBuilder) WhereFunc(fn func(row map[string][]string, tables map[string]*Table) (bool, error)) *QueryBuilder

type QueryComponent 

type QueryComponent interface {
	Type() string
	Validate() error
}

type Result 

type Result struct {
	// contains filtered or unexported fields
}

func (Result) Bool 

func (r Result) Bool() (bool, error)

func (Result) Date 

func (r Result) Date() (time.Time, error)

func (Result) DateTime 

func (r Result) DateTime() (time.Time, error)

func (Result) Float 

func (r Result) Float() (float64, error)

func (Result) Int 

func (r Result) Int() (int, error)

func (Result) Must 

func (r Result) Must() string

func (Result) MustBool 

func (r Result) MustBool() bool

func (Result) MustDate 

func (r Result) MustDate() time.Time

func (Result) MustDateTime 

func (r Result) MustDateTime() time.Time

func (Result) MustFloat 

func (r Result) MustFloat() float64

func (Result) MustInt 

func (r Result) MustInt() int

func (Result) MustTime 

func (r Result) MustTime(layout string) time.Time

func (Result) String 

func (r Result) String() (string, error)

func (Result) Time 

func (r Result) Time(layout string) (time.Time, error)

type SelectComponent 

type SelectComponent struct {
	Columns       []string
	CustomColumns []CustomSelectField
}

func (*SelectComponent) Type 

func (s *SelectComponent) Type() string

func (*SelectComponent) Validate 

func (s *SelectComponent) Validate() error

type SimpleCondition 

type SimpleCondition struct {
	Column string
	Op     Operator
	Value  string
}

func NewSimpleCondition 

func NewSimpleCondition(column, operator, value string) (*SimpleCondition, error)

func (*SimpleCondition) Evaluate 

func (c *SimpleCondition) Evaluate(row map[string][]string, tables map[string]*Table) (bool, error)

func (*SimpleCondition) Type 

func (c *SimpleCondition) Type() string

type Table 

type Table struct {
	Name      string
	Headers   []string
	Rows      [][]string
	HeaderMap map[string]int
}

func NewTableFromCSV 

func NewTableFromCSV(name, filepath string) (*Table, error)

func NewTableFromXlsx 

func NewTableFromXlsx(name, filepath string, sheetName ...string) (*Table, error)

func (*Table) GetColumnIndex 

func (t *Table) GetColumnIndex(column string) (int, error)

func (*Table) GetColumnValue 

func (t *Table) GetColumnValue(rowIdx int, column string) (string, error)

type TableRow 

type TableRow struct {
	// contains filtered or unexported fields
}

func GetRow 

func GetRow(row map[string][]string, tables map[string]*Table, tableName string) *TableRow

func (*TableRow) Get 

func (r *TableRow) Get(column string) Result

func (*TableRow) MustGet 

func (r *TableRow) MustGet(column string) string

type UnionComponent 

type UnionComponent struct {
	UnionKind UnionType
	Queries   []*Query
}

func (*UnionComponent) Type 

func (u *UnionComponent) Type() string

func (*UnionComponent) Validate 

func (u *UnionComponent) Validate() error

type UnionType 

type UnionType string
const (
	Union    UnionType = "UNION"
	UnionAll UnionType = "UNION ALL"
)

type WhereComponent 

type WhereComponent struct {
	Condition Condition
}

func (*WhereComponent) Type 

func (w *WhereComponent) Type() string

func (*WhereComponent) Validate 

func (w *WhereComponent) Validate() error

Source Files

View all Source files

Directories

Path Synopsis
cmd
example

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.