Skip to content

mithrandie/csvq-driver

Repository files navigation

csvq driver

A go database/sql driver for csvq.

GitHub tag (latest SemVer) Test codecov License: MIT

csvq-driver allows you to operate csv files and other supported formats using csvq statements in Go. You can use the statements in the same way as other relational database systems.

Reference Manual - csvq

Requirements

Go 1.18 or later (ref. Getting Started - The Go Programming Language)

Supported features

  • Query
    • Only the result-sets in the top-level scope can be retrieved. You cannot refer the results of child scopes such as inside of IF, WHILE and user-defined functions.
  • Exec
    • Only the last result in the top-level scope can be retrieved.
    • LastInsertId is not supported.
  • Prepared Statement
    • Ordinal placeholders
    • Named placeholders
  • Transaction
    • Isolation Level is the default only.
    • Read-only transaction is not supported.
    • If you do not use the database/sql transaction feature, all execusions will commit at the end of the execusion automatically.

Usage

Data Source Name

A data source name to call sql.Open is a directory path. It is the same as "--repository" option of the csvq command.

Some parameters can be specified in a DSN string with a directory path. A directory path and parameters are separated by a question mark, and parameters are separated by ampersands.

db, err := sql.Open("csvq", "/path/to/data/directory?Param1=Value&Param2=Value")

Parameters that can be specified

Name Type Default
Timezone string "Local"
DatetimeFormat string empty string
AnsiQuotes bool false

Parameter names are case-insensitive.

See: csvq > Reference Manual > Command Usage > Options

Error Handling

If a received error is a returned error from csvq, you can cast the error to github.com/mithrandie/csvq/lib/query.Error interface. The error interface has the following functions.

function description
Message() string Error message
Code() int Error code. The same as the exit code of the csvq command
Number() int Error number
Line() int Line number where the error occurred in the passed statement
Char() int Column number where the error occurred in the passed statement
Source() string File or statement name where the error occurred

Example

package main

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

	"github.com/mithrandie/csvq/lib/query"

	_ "github.com/mithrandie/csvq-driver"
)

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

	db, err := sql.Open("csvq", "/path/to/data/directory")
	if err != nil {
		panic(err)
	}
	defer func() {
		if err := db.Close(); err != nil {
			panic(err)
		}
	}()
	
	queryString := "SELECT id, first_name, country_code FROM `users.csv` WHERE id = '12'"
	r := db.QueryRowContext(ctx, queryString)

	var (
		id          int
		firstName   string
		countryCode string
	)

	if err := r.Scan(&id, &firstName, &countryCode); err != nil {
		if err == sql.ErrNoRows {
			fmt.Println("No Rows.")
		} else if csvqerr, ok := err.(query.Error); ok {
			panic(fmt.Sprintf("Unexpected error: Number: %d  Message: %s", csvqerr.Number(), csvqerr.Message()))
		} else {
			panic("Unexpected error: " + err.Error())
		}
	} else {
		fmt.Printf("Result: [id]%3d  [first_name]%10s  [country_code]%3s\n", id, firstName, countryCode)
	}
}

See: https://github.com/mithrandie/csvq-driver/blob/master/example/csvq-driver/csvq-driver-example.go

Replace I/O

You can replace input/output interface of the csvq to pass the data from inside your go programs or retrive the result-sets as a another format.

function in the csvq-driver default description
SetStdin(r io.ReadCloser) error

SetStdinContext(ctx context.Context, r io.ReadCloser) error
os.Stdin Replace input interface. Passed data can be refered as a temporary table named "STDIN".
SetStdout(w io.WriteCloser) query.Discard Replace output interface. Logs and result-sets of select queries are written to stdout.
SetOutFile(w io.Writer) nil Put a writer for result-sets of select queries to write instead of stdout.

The following structs are available for replacement.

struct name initializer
query.Input query.NewInput(r io.Reader)
query.Output query.NewOutput()

"query" means the package "github.com/mithrandie/csvq/lib/query".

See: https://github.com/mithrandie/csvq-driver/blob/master/example/replace-io/csvq-replace-io-example.go

About

A go database/sql driver for csvq

Resources

License

Stars

Watchers

Forks

Packages

No packages published

Languages