### Install Go on Linux (Ubuntu)

Source: https://github.com/nao1215/filesql/blob/main/CONTRIBUTING.md

Provides instructions for installing Go on Ubuntu Linux, offering both snap installation and manual download/installation methods. It also includes steps to add Go to the system's PATH.

```bash
# Using snap
sudo snap install go --classic

# Or download from official site
wget https://go.dev/dl/go1.24.0.linux-amd64.tar.gz
sudo tar -C /usr/local -xzf go1.24.0.linux-amd64.tar.gz
echo 'export PATH=$PATH:/usr/local/go/bin' >> ~/.profile
source ~/.profile
```

--------------------------------

### Install Ent Dependency

Source: https://github.com/nao1215/filesql/blob/main/examples/ent/README.md

Installs the Ent ORM library using Go modules. This is a prerequisite for running the Ent integration example and generating Ent code.

```bash
go get entgo.io/ent
```

--------------------------------

### Run Multi-Format Example

Source: https://github.com/nao1215/filesql/blob/main/examples/multi-format/README.md

Command to navigate to the example directory and execute the multi-format demonstration using Go. This command initiates the filesql example that processes various file types.

```bash
cd examples/multi-format
go run main.go
```

--------------------------------

### Generate SQLC Code

Source: https://github.com/nao1215/filesql/blob/main/examples/sqlc/README.md

This snippet shows how to install sqlc and generate Go code from SQL schema and query definitions. Ensure you have Go installed and are in the project's examples/sqlc directory. The generated code provides type-safe database access.

```bash
# Install sqlc if not already installed
go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest

# Generate code
sqlc generate
```

--------------------------------

### Install FileSQL Development Tools

Source: https://github.com/nao1215/filesql/blob/main/CONTRIBUTING.md

Installs necessary development tools required for contributing to the filesql project using the provided Makefile.

```bash
# Install necessary development tools
make tools
```

--------------------------------

### Run Ent Integration Example

Source: https://github.com/nao1215/filesql/blob/main/examples/ent/README.md

Executes the main application code for the Ent integration example. This command assumes you are in the 'examples/ent' directory and have already set up the Go modules.

```bash
cd examples/ent
go mod tidy
go run main.go
```

--------------------------------

### Install Go on macOS using Homebrew

Source: https://github.com/nao1215/filesql/blob/main/CONTRIBUTING.md

Installs the Go programming language on macOS using the Homebrew package manager. Ensure Homebrew is installed before running this command.

```bash
brew install go
```

--------------------------------

### Run sqlc Integration Example

Source: https://github.com/nao1215/filesql/blob/main/examples/sqlc/README.md

This command executes the main Go application that utilizes sqlc-generated code with filesql. Navigate to the examples/sqlc directory before running this command. The application demonstrates CRUD operations and querying data from CSV files.

```bash
cd examples/sqlc
go run main.go
```

--------------------------------

### Go Unit Test Example for File Parsing

Source: https://github.com/nao1215/filesql/blob/main/CONTRIBUTING.md

Demonstrates a unit test in Go for a `ParseCSV` function, including setting up test data, asserting no errors, and comparing the result with an expected table structure. It utilizes `t.Parallel()` for concurrent test execution.

```go
func TestFile_Parse(t *testing.T) {
    t.Parallel()    
    t.Run("should parse CSV file correctly", func(t *testing.T) {
        // Clear input and expected values for test case
        input := "name,age\nAlice,30"
        expected := &Table{...} // Placeholder for actual Table struct
        
        result, err := ParseCSV(input)
        assert.NoError(t, err)
        assert.Equal(t, expected, result)
    })
}
```

--------------------------------

### Open - Simple File Opening

Source: https://context7.com/nao1215/filesql/llms.txt

The `Open` function allows you to open a single file and treat it as a SQL table. The table name is automatically derived from the filename. This is a simple way to get started with querying files.

```APIDOC
## Open - Simple File Opening

### Description
Opens a single file and makes it queryable via SQL. The table name is inferred from the filename (e.g., `employees.csv` becomes the `employees` table).

### Method
`filesql.Open(filePath string)`

### Parameters
#### Path Parameters
- **filePath** (string) - Required - The path to the file to be opened.

### Request Example
```go
package main

import (
    "database/sql"
    "log"

    "github.com/nao1215/filesql"
)

func main() {
    db, err := filesql.Open("employees.csv")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // ... execute queries ...
}
```

### Response
#### Success Response (200)
- **db** (*sql.DB) - A database handle that can be used to query the file.

#### Response Example
(The `filesql.Open` function returns a standard `*sql.DB` object, no specific FileSQL response format for opening.)
```

--------------------------------

### Run FileSQL Tests and Linters

Source: https://github.com/nao1215/filesql/blob/main/CONTRIBUTING.md

Executes the test suite and runs the linter to verify code quality and correctness in the filesql project.

```bash
# Run tests
make test

# Run linter
make lint
```

--------------------------------

### Check Test Coverage in FileSQL

Source: https://github.com/nao1215/filesql/blob/main/CONTRIBUTING.md

Calculates and displays the test coverage percentage for all Go packages within the filesql project. A minimum coverage of 80% is recommended.

```bash
go test -cover ./...
```

--------------------------------

### Clone and Navigate FileSQL Project

Source: https://github.com/nao1215/filesql/blob/main/CONTRIBUTING.md

Clones the filesql project repository from GitHub and changes the current directory to the project's root.

```bash
git clone https://github.com/nao1215/filesql.git
cd filesql
```

--------------------------------

### Generate Ent Code

Source: https://github.com/nao1215/filesql/blob/main/examples/ent/README.md

This command generates Go code for the Ent ORM based on schema definitions. It's essential for creating the client and entity types needed to interact with the database.

```bash
go generate ./ent
```

--------------------------------

### Enable Automatic Save on Transaction Commit in Go

Source: https://context7.com/nao1215/filesql/llms.txt

This Go example illustrates how to configure FileSQL to automatically persist database changes after each transaction commit. It shows setting up the builder, enabling auto-save on commit to a specified output directory, initiating a transaction, executing multiple statements, and committing the transaction, which triggers the automatic save.

```go
package main

import (
    "context"
    "log"
    "time"

    "github.com/nao1215/filesql"
)

func main() {
    ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
    defer cancel()

    // Configure auto-save after each transaction commit
    builder := filesql.NewBuilder().
        AddPath("transactions.csv").
        EnableAutoSaveOnCommit("./output") // Empty string "" overwrites original

    validatedBuilder, err := builder.Build(ctx)
    if err != nil {
        log.Fatal(err)
    }

    db, err := validatedBuilder.Open(ctx)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Start transaction
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        log.Fatal(err)
    }

    // Execute multiple statements in transaction
    _, err = tx.ExecContext(ctx, "UPDATE transactions SET status = 'completed' WHERE id = 1")
    if err != nil {
        tx.Rollback()
        log.Fatal(err)
    }

    _, err = tx.ExecContext(ctx, "INSERT INTO transactions (id, amount, status) VALUES (99, 250.50, 'pending')")
    if err != nil {
        tx.Rollback()
        log.Fatal(err)
    }

    // Commit triggers automatic save to ./output/transactions.csv
    if err = tx.Commit(); err != nil {
        log.Fatal(err)
    }
}
```

--------------------------------

### Query and Export XLSX Files with FileSQL in Go

Source: https://context7.com/nao1215/filesql/llms.txt

This Go code snippet demonstrates how to open an XLSX file, treat each sheet as a table, query individual sheets, perform joins across sheets, and export the data back to an XLSX file. It uses the 'github.com/nao1215/filesql' library. Ensure the library is installed.

```go
package main

import (
    "context"
    "fmt"
    "log"

    "github.com/nao1215/filesql"
)

func main() {
    ctx := context.Background()

    // Open Excel file with multiple sheets
    // Each sheet becomes a separate table: {filename}_{sheetname}
    // Example: sales.xlsx with sheets "Q1", "Q2", "Q3"
    // Creates tables: sales_Q1, sales_Q2, sales_Q3
    db, err := filesql.OpenContext(ctx, "sales.xlsx")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Query individual sheets
    rows, err := db.Query("SELECT * FROM sales_Q1 WHERE revenue > 10000")
    if err != nil {
        log.Fatal(err)
    }
    rows.Close()

    // JOIN across sheets
    joinRows, err := db.Query(`
        SELECT
            q1.product,
            q1.revenue as q1_revenue,
            q2.revenue as q2_revenue,
            (q1.revenue + q2.revenue) as total_h1
        FROM sales_Q1 q1
        JOIN sales_Q2 q2 ON q1.product = q2.product
        ORDER BY total_h1 DESC
    `)
    if err != nil {
        log.Fatal(err)
    }
    defer joinRows.Close()

    fmt.Println("Product performance (H1):")
    for joinRows.Next() {
        var product string
        var q1Rev, q2Rev, totalH1 float64
        if err := joinRows.Scan(&product, &q1Rev, &q2Rev, &totalH1); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("  %s: Q1=$%.2f, Q2=$%.2f, Total=$%.2f\n",
            product, q1Rev, q2Rev, totalH1)
    }

    // Export back to Excel - table names become sheet names
    xlsxOptions := filesql.NewDumpOptions().WithFormat(filesql.OutputFormatXLSX)
    if err := filesql.DumpDatabase(db, "./output", xlsxOptions); err != nil {
        log.Fatal(err)
    }
}
```

--------------------------------

### Query and Export Parquet Files with FileSQL in Go

Source: https://context7.com/nao1215/filesql/llms.txt

This Go code snippet demonstrates how to open a Parquet file, execute SQL queries for analytics, and export the data back to Parquet format using the 'github.com/nao1215/filesql' library. Parquet files are efficient for columnar data analysis. Ensure the library is installed.

```go
package main

import (
    "context"
    "fmt"
    "log"

    "github.com/nao1215/filesql"
)

func main() {
    ctx := context.Background()

    // Open Parquet file (columnar format, efficient for analytics)
    db, err := filesql.OpenContext(ctx, "analytics.parquet")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Query parquet data with complex analytics
    rows, err := db.Query(`
        SELECT
            event_date,
            event_type,
            COUNT(*) as event_count,
            COUNT(DISTINCT user_id) as unique_users,
            AVG(session_duration) as avg_duration
        FROM analytics
        WHERE event_date BETWEEN '2024-01-01' AND '2024-12-31'
        GROUP BY event_date, event_type
        ORDER BY event_date, event_count DESC
    `)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    fmt.Println("Analytics Summary:")
    for rows.Next() {
        var eventDate, eventType string
        var eventCount, uniqueUsers int
        var avgDuration float64
        if err := rows.Scan(&eventDate, &eventType, &eventCount, &uniqueUsers, &avgDuration); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("%s | %s: %d events, %d users, %.1fs avg\n",
            eventDate, eventType, eventCount, uniqueUsers, avgDuration)
    }

    // Export to Parquet format (uses Arrow's columnar format)
    // Note: Parquet compression is built-in, external compression not supported
    parquetOptions := filesql.NewDumpOptions().WithFormat(filesql.OutputFormatParquet)
    if err := filesql.DumpDatabase(db, "./output", parquetOptions); err != nil {
        log.Fatal(err)
    }
}
```

--------------------------------

### Load Files from Directory (Go)

Source: https://github.com/nao1215/filesql/blob/main/README.md

Demonstrates how to load all supported files from a specified directory, including recursively searching subdirectories. It also shows how to query the available tables, which correspond to the filenames.

```go
ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
def cancel()

// Load all supported files from a directory (recursive)
db, err := filesql.OpenContext(ctx, "/path/to/data/directory")
if err != nil {
    log.Fatal(err)
}
deferr db.Close()

// See what tables are available
rows, err := db.QueryContext(ctx, "SELECT name FROM sqlite_master WHERE type='table'")
```

--------------------------------

### Open and Query CSV File (Go)

Source: https://github.com/nao1215/filesql/blob/main/README.md

Demonstrates how to open a CSV file as a database and query its contents using `OpenContext` for proper timeout handling. It handles potential errors during file opening, querying, and row scanning, and processes the results.

```go
package main

import (
    "context"
    "fmt"
    "log"
    "time"
    
    "github.com/nao1215/filesql"
)

func main() {
    // Create context with timeout for large file operations
    ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
    defer cancel()
    
    // Open a CSV file as a database
    db, err := filesql.OpenContext(ctx, "data.csv")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    
    // Query the data (table name = filename without extension)
    rows, err := db.QueryContext(ctx, "SELECT * FROM data WHERE age > 25")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    
    // Process results
    for rows.Next() {
        var name string
        var age int
        if err := rows.Scan(&name, &age); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("Name: %s, Age: %d\n", name, age)
    }
}
```

--------------------------------

### FilesQL Builder Pattern with Embedded Files (Go)

Source: https://github.com/nao1215/filesql/blob/main/README.md

Illustrates the use of the builder pattern for advanced configuration, including adding local files and embedded files using `embed.FS`. It also shows how to set a default chunk size for processing large datasets.

```go
package main

import (
    "context"
    "embed"
    "log"
    
    "github.com/nao1215/filesql"
)

//go:embed data/*.csv
var embeddedFiles embed.FS

func main() {
    ctx := context.Background()
    
    // Configure data sources with builder
    validatedBuilder, err := filesql.NewBuilder().
        AddPath("local_file.csv").      // Local file
        AddFS(embeddedFiles).           // Embedded files
        SetDefaultChunkSize(5000). // 5000 rows per chunk
        Build(ctx)
    if err != nil {
        log.Fatal(err)
    }
    
    db, err := validatedBuilder.Open(ctx)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    
    // Query across all data sources
    rows, err := db.Query("SELECT name FROM sqlite_master WHERE type='table'")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
}
```

--------------------------------

### Open Multiple Files and Join Data (Go)

Source: https://github.com/nao1215/filesql/blob/main/README.md

Shows how to open multiple files of different formats (CSV, TSV, LTSV.GZ, Parquet) simultaneously and perform JOIN operations across them. This enables complex data analysis by combining information from various sources.

```go
ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
def cancel()

// Open multiple files at once (including Parquet)
db, err := filesql.OpenContext(ctx, "users.csv", "orders.tsv", "logs.ltsv.gz", "analytics.parquet")
if err != nil {
    log.Fatal(err)
}
deferr db.Close()

// Join data across different file formats
rows, err := db.QueryContext(ctx, `
    SELECT u.name, o.order_date, l.event, a.metrics
    FROM users u
    JOIN orders o ON u.id = o.user_id
    JOIN logs l ON u.id = l.user_id
    JOIN analytics a ON u.id = a.user_id
    WHERE o.order_date > '2024-01-01'
`)
```

--------------------------------

### Manually Export Data with filesql (Go)

Source: https://github.com/nao1215/filesql/blob/main/README.md

Demonstrates how to manually export data from a filesql database to a specified directory. It shows basic export, export with custom format and compression (TSV, GZ), and export to Parquet format. Note that Parquet export relies on Parquet's built-in compression.

```go
ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
defers cancel()

db, err := filesql.OpenContext(ctx, "data.csv")
if err != nil {
    log.Fatal(err)
}
defers db.Close()

// Make modifications
db.Exec("UPDATE data SET status = 'processed'")

// Manually export changes
err = filesql.DumpDatabase(db, "./output")
if err != nil {
    log.Fatal(err)
}

// Or with custom format and compression
options := filesql.NewDumpOptions().
    WithFormat(filesql.OutputFormatTSV).
    WithCompression(filesql.CompressionGZ)
err = filesql.DumpDatabase(db, "./output", options)

// Export to Parquet format
parquetOptions := filesql.NewDumpOptions().
    WithFormat(filesql.OutputFormatParquet)
// Note: Parquet export is implemented, but external compression is not supported (use Parquet's built-in compression)

```

--------------------------------

### Recommended Concurrent Access Pattern for filesql (Go)

Source: https://github.com/nao1215/filesql/blob/main/README.md

Illustrates the recommended and discouraged patterns for handling concurrent access to filesql databases. It emphasizes that filesql is not thread-safe and advocates for using separate database instances per goroutine to avoid race conditions and data corruption.

```go
// ✅ GOOD: Separate database instances per goroutine
func processFileConcurrently(filename string) error {
    db, err := filesql.Open(filename)  // Each goroutine gets its own instance
    if err != nil {
        return err
    }
    defer db.Close()
    
    // Safe to use within this goroutine
    return processData(db)
}

// ❌ BAD: Sharing database instance across goroutines
var sharedDB *sql.DB  // This will cause race conditions

```

--------------------------------

### Manage FileSQL Operations with Context and Cancellation

Source: https://github.com/nao1215/filesql/blob/main/README.md

Shows how to use Go's context package to set timeouts and enable cancellation for FileSQL operations. This is crucial for handling potentially large files or network requests to prevent indefinite blocking.

```go
import (
    "context"
    "time"
)

// Set timeout for large file operations
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Minute)
def cancel() {
    cancel()
}

db, err := filesql.OpenContext(ctx, "huge_dataset.csv.gz")
if err != nil {
    log.Fatal(err)
}
defers db.Close()

// Query with context for cancellation support
rows, err := db.QueryContext(ctx, "SELECT * FROM huge_dataset WHERE status = 'active'")
```

--------------------------------

### Load Data from HTTP Response (Go)

Source: https://github.com/nao1215/filesql/blob/main/README.md

Demonstrates how to load data from an HTTP response body directly into FilesQL using `AddReader`. This is useful for querying data from network sources without needing to download the files first.

```go
import (
    "net/http"
    "github.com/nao1215/filesql"
)

// Load data from HTTP response
resp, err := http.Get("https://example.com/data.csv")
if err != nil {
    log.Fatal(err)
}
deferr resp.Close()

validatedBuilder, err := filesql.NewBuilder().
    AddReader(resp.Body, "remote_data", filesql.FileTypeCSV).
    Build(ctx)
if err != nil {
    log.Fatal(err)
}

db, err := validatedBuilder.Open(ctx)
if err != nil {
    log.Fatal(err)
}
deferr db.Close()

// Query remote data
rows, err := db.QueryContext(ctx, "SELECT * FROM remote_data LIMIT 10")
```

--------------------------------

### OpenContext - File Opening with Context Support

Source: https://context7.com/nao1215/filesql/llms.txt

The `OpenContext` function provides context support for opening files, allowing for timeouts and cancellation. It can also open multiple files simultaneously, supporting various formats and automatic decompression.

```APIDOC
## OpenContext - File Opening with Context Support

### Description
Opens one or more files with context support, enabling cancellation and timeouts. Supports multiple file formats (CSV, TSV, LTSV, Parquet, Excel) and automatic decompression of compressed files.

### Method
`filesql.OpenContext(ctx context.Context, filePaths ...string)`

### Parameters
#### Path Parameters
- **ctx** (context.Context) - Required - The context for the operation.
- **filePaths** ([]string) - Required - A variadic list of file paths to be opened.

### Request Example
```go
package main

import (
    "context"
    "log"
    "time"

    "github.com/nao1215/filesql"
)

func main() {
    ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
    defer cancel()

    db, err := filesql.OpenContext(ctx, "users.csv", "orders.tsv")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // ... execute queries ...
}
```

### Response
#### Success Response (200)
- **db** (*sql.DB) - A database handle that can be used to query the opened files.

#### Response Example
(The `filesql.OpenContext` function returns a standard `*sql.DB` object, no specific FileSQL response format for opening.)
```

--------------------------------

### Load Data from io.Reader with FileSQL

Source: https://context7.com/nao1215/filesql/llms.txt

Demonstrates loading data into FileSQL from various io.Reader sources, such as HTTP responses or in-memory strings. It shows how to build a database with multiple tables from different readers and perform JOIN operations. Dependencies include the 'filesql' package and standard Go libraries for context, HTTP requests, and string manipulation.

```go
package main

import (
    "context"
    "fmt"
    "log"
    "net/http"
    "strings"

    "github.com/nao1215/filesql"
)

func main() {
    ctx := context.Background()

    // Example 1: Load CSV data from HTTP response
    resp, err := http.Get("https://example.com/data.csv")
    if err != nil {
        log.Fatal(err)
    }
    defer resp.Body.Close()

    builder := filesql.NewBuilder().
        AddReader(resp.Body, "remote_data", filesql.FileTypeCSV)

    validatedBuilder, err := builder.Build(ctx)
    if err != nil {
        log.Fatal(err)
    }

    db, err := validatedBuilder.Open(ctx)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Query remote data
    rows, err := db.QueryContext(ctx, "SELECT * FROM remote_data LIMIT 10")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    // Example 2: Load multiple readers (users + orders)
    usersCSV := `user_id,name,email
1,Alice,alice@example.com
2,Bob,bob@example.com`

    ordersCSV := `order_id,user_id,amount,status
101,1,250.00,completed
102,2,180.00,pending
103,1,320.00,completed`

    builder2 := filesql.NewBuilder().
        AddReader(strings.NewReader(usersCSV), "users", filesql.FileTypeCSV).
        AddReader(strings.NewReader(ordersCSV), "orders", filesql.FileTypeCSV).
        SetDefaultChunkSize(2500) // Rows per chunk for streaming

    validatedBuilder2, err := builder2.Build(ctx)
    if err != nil {
        log.Fatal(err)
    }

    db2, err := validatedBuilder2.Open(ctx)
    if err != nil {
        log.Fatal(err)
    }
    defer db2.Close()

    // JOIN across readers
    rows2, err := db2.Query("
        SELECT u.name, SUM(o.amount) as total
        FROM users u
        JOIN orders o ON u.user_id = o.user_id
        GROUP BY u.user_id, u.name
        ORDER BY total DESC
    ")
    if err != nil {
        log.Fatal(err)
    }
    defer rows2.Close()

    fmt.Println("Customer spending:")
    for rows2.Next() {
        var name string
        var total float64
        if err := rows2.Scan(&name, &total); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("  %s: $%.2f\n", name, total)
    }
}

```

--------------------------------

### Run filesql Benchmarks (Bash)

Source: https://github.com/nao1215/filesql/blob/main/README.md

Provides the command to run the benchmarks for the filesql library directly from the terminal. This allows users to test the performance of the library on their own system.

```bash
make benchmark

```

--------------------------------

### Configure Multiple Data Sources with Builder Pattern in Go

Source: https://context7.com/nao1215/filesql/llms.txt

This snippet shows how to configure multiple data sources (local files, embedded files, io.Readers) for FileSQL using its builder pattern. It demonstrates adding individual files, multiple files, embedded file systems, and data from an io.Reader. The builder also allows setting a default chunk size for processing data. Input is validated before opening the database connection.

```go
package main

import (
    "context"
    "embed"
    "fmt"
    "log"
    "strings"

    "github.com/nao1215/filesql"
)

//go:embed data/*.csv
var embeddedFiles embed.FS

func main() {
    ctx := context.Background()

    // CSV data from io.Reader (network, API response, etc.)
    csvData := `id,product,price,category
1,Laptop,999,Electronics
2,Mouse,25,Electronics
3,Desk,299,Furniture`

    // Configure multiple data sources with builder pattern
    builder := filesql.NewBuilder().
        AddPath("local_users.csv").                                    // Local file
        AddPaths("orders.tsv", "logs.ltsv").                          // Multiple files
        AddFS(embeddedFiles).                                         // Embedded filesystem
        AddReader(strings.NewReader(csvData), "products", filesql.FileTypeCSV). // io.Reader
        SetDefaultChunkSize(5000)                                     // 5000 rows per chunk

    // Build validates inputs
    validatedBuilder, err := builder.Build(ctx)
    if err != nil {
        log.Fatal(err)
    }

    // Open creates database connection
    db, err := validatedBuilder.Open(ctx)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Query across all data sources
    rows, err := db.Query(`
        SELECT name FROM sqlite_master
        WHERE type='table'
        ORDER BY name
    `)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    fmt.Println("Available tables:")
    for rows.Next() {
        var tableName string
        if err := rows.Scan(&tableName); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("  - %s\n", tableName)
    }
}
```

--------------------------------

### Execute Complex SQL Query with FileSQL

Source: https://github.com/nao1215/filesql/blob/main/README.md

Demonstrates how to execute a complex SQL query involving common table expressions (CTEs), window functions, and joins using FileSQL in Go. This snippet utilizes context for timeouts and handles potential errors during database operations.

```go
ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
def cancel() {
    cancel()
}

db, err := filesql.OpenContext(ctx, "employees.csv", "departments.csv")
if err != nil {
    log.Fatal(err)
}
defers db.Close()

// Use advanced SQLite features
query := ""
    WITH dept_stats AS (
        SELECT 
            department_id,
            AVG(salary) as avg_salary,
            COUNT(*) as emp_count
        FROM employees
        GROUP BY department_id
    )
    SELECT 
        e.name,
        e.salary,
        d.name as department,
        ds.avg_salary as dept_avg,
        RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) as salary_rank
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    JOIN dept_stats ds ON e.department_id = ds.department_id
    WHERE e.salary > ds.avg_salary * 0.8
    ORDER BY d.name, salary_rank
"

rows, err := db.QueryContext(ctx, query)
```

--------------------------------

### Go: Preprocess and Validate CSV Data with fileprep

Source: https://github.com/nao1215/filesql/blob/main/README.md

This Go code snippet demonstrates how to define a struct with fileprep's preprocessing (`prep`) and validation (`validate`) tags. It processes CSV data, performs cleaning (trimming, lowercasing, default values, uppercasing), and validation (required, email format, numeric range, allowed values). The preprocessed data is then passed to filesql for querying.

```go
package main

import (
	"context"
	"fmt"
	"log"
	"strings"

	"github.com/nao1215/fileframe/filesql"
	"github.com/nao1215/fileprep"
)

// Define struct with preprocessing and validation tags
type User struct {
    // Name: trim whitespace, require non-empty
    Name  string `prep:"trim" validate:"required"`
    // Email: trim, convert to lowercase, validate email format
    Email string `prep:"trim,lowercase" validate:"required,email"`
    // Age: set default if empty, validate range 0-150
    Age   string `prep:"default=0" validate:"numeric,gte=0,lte=150"`
    // Role: trim, uppercase, must be one of the allowed values
    Role  string `prep:"trim,uppercase" validate:"oneof=ADMIN USER GUEST"`
}

func main() {
    // CSV data with messy input
    csvData := `name,email,age,role
  John Doe  ,JOHN@EXAMPLE.COM,25,admin
Alice,alice@example.com,,user`

    // Create processor and process the CSV
    processor := fileprep.NewProcessor(fileprep.FileTypeCSV)
    var users []User

    reader, result, err := processor.Process(strings.NewReader(csvData), &users)
    if err != nil {
        log.Fatal(err)
    }

    // Check validation results
    fmt.Printf("Processed: %d rows, Valid: %d rows\n", result.RowCount, result.ValidRowCount)
    if result.HasErrors() {
        for _, e := range result.ValidationErrors() {
            log.Printf("Row %d, Column %s: %s", e.Row, e.Column, e.Message)
        }
    }

    // Pass preprocessed data to filesql
    // The data is now cleaned: trimmed, lowercased emails, defaults applied
    ctx := context.Background()
    db, err := filesql.NewBuilder().
        AddReader(reader, "users", filesql.FileTypeCSV).
        Build(ctx)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Query the clean data
    rows, _ := db.QueryContext(ctx, "SELECT * FROM users WHERE role = 'ADMIN'")
    // ... process rows ...
    fmt.Println("Successfully queried data.")
}

```

--------------------------------

### Open CSV file and query with SQL in Go

Source: https://context7.com/nao1215/filesql/llms.txt

Demonstrates how to open a CSV file using filesql.Open, which derives the table name from the filename. It then executes a complex SQL query involving window functions and subqueries, processing the results. The function relies on the 'database/sql' and 'log' packages.

```go
package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"

    "github.com/nao1215/filesql"
)

func main() {
    // Open CSV file - table name derived from filename (employees.csv -> employees)
    db, err := filesql.Open("employees.csv")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Execute SQL query with JOINs, window functions, and subqueries
    rows, err := db.Query(`
        SELECT
            name,
            salary,
            RANK() OVER (ORDER BY salary DESC) as salary_rank,
            AVG(salary) OVER () as company_avg
        FROM employees
        WHERE salary > (SELECT AVG(salary) * 0.8 FROM employees)
        ORDER BY salary DESC
    `)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    // Process results
    for rows.Next() {
        var name string
        var salary, companyAvg float64
        var rank int
        if err := rows.Scan(&name, &salary, &rank, &companyAvg); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("%s: $%.0f (Rank: %d, Avg: $%.0f)\n",
            name, salary, rank, companyAvg)
    }

    if err = rows.Err(); err != nil {
        log.Fatal(err)
    }
}
```

--------------------------------

### Open multiple files with context and query in Go

Source: https://context7.com/nao1215/filesql/llms.txt

Shows how to use filesql.OpenContext with a context that includes a timeout, suitable for potentially long-running operations on large files. It demonstrates opening multiple files of different formats (CSV, TSV, LTSV, XLSX) and querying them with JOINs, supporting context cancellation. This function requires 'context', 'fmt', 'log', and 'time' packages.

```go
package main

import (
    "context"
    "fmt"
    "log"
    "time"

    "github.com/nao1215/filesql"
)

func main() {
    // Create context with timeout for large file operations
    ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
    defer cancel()

    // Open multiple files - supports CSV, TSV, LTSV, Parquet, Excel (XLSX)
    // Compressed files (.gz, .bz2, .xz, .zst) are automatically decompressed
    db, err := filesql.OpenContext(ctx, "users.csv", "orders.tsv", "logs.ltsv.gz", "sales.xlsx")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Query with context for cancellation support
    // JOIN data across different file formats
    rows, err := db.QueryContext(ctx, `
        SELECT
            u.name,
            COUNT(o.order_id) as order_count,
            SUM(o.amount) as total_spent
        FROM users u
        LEFT JOIN orders o ON u.id = o.user_id
        WHERE u.registration_date >= '2024-01-01'
        GROUP BY u.id, u.name
        HAVING total_spent > 100
        ORDER BY total_spent DESC
    `)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var name string
        var orderCount int
        var totalSpent float64
        if err := rows.Scan(&name, &orderCount, &totalSpent); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("%s: %d orders, $%.2f total\n", name, orderCount, totalSpent)
    }
}
```