Suggestion on Implementation of ODBC adapter in Go for several interfaces

Can anybody please suggest how to go about implemented an ODBC adapter for several interfaces like Oracle, DB2, Paradox, Access, FoxPro or any others with Golang.
Will appreciate any material that will guide me and help me understand what I need to do to realize this task.
Thanks.

odbc

odbc driver written in go. Implements database driver interface as used by standard database/sql package. It calls into odbc dll on Windows, and uses cgo (unixODBC) everywhere else.

1 Like

I would “go” (pun intended) with something like the adapter pattern, but this is probably because my many years with OOP

But even in Go I think the approach makes sense, especially if you want to support multiple driver from the same package.

1 Like

Thank you for this. I am actually to write the adapter to support multiple drivers from the package. I am a 2 weeks Gopher and hoping to get some guardians on how to go about this.

I began Go-ofing around back in September last year, but I am happy to help. Just ping me https://github.com/kristiannissen

1 Like

Thanks a million. I will do a lot with a mentor

Am suppose to create an ODBC adaptor by implementing the following interface:


import "io"

// Shape - data shape
type Shape struct {
	X int `json:"x"`
	Y int `json:"y"`
}

// DatasourceProvider - data source provider interface
type DatasourceProvider interface {
	// List - data source listing
	List() ([]Table, error)
	// Select - select data source table
	Select(selector *Selector, options ...Option) (Table, error)
}

// Table - table data interface
type Table interface {
	// GetName - returns name
	GetName() string
	// GetType - return type
	GetType() string
	// GetSelector - returns selector
	GetSelector() *Selector
	// Open - open table
	Open() error
	// Close - close table
	Close()
	// GetShape - returns data shape
	GetShape() (*Shape, error)
	// GetHeaders - returns headers
	GetHeaders() ([]string, error)
	// GetSynteticHeaders - returns syntetic headers
	GetSynteticHeaders() ([]string, error)
	// GetTypes - returns types
	GetTypes() ([]*DataTypeWithOption, error)
	// TrySetType - attempt setting type to column
	TrySetType(index int, columnType DataType, options ...Option) error
	// SetTypes - setting types to column
	SetTypes(types []*DataTypeWithOption)
	// GetPlainSample - returns plain sample
	GetPlainSample() ([][]string, error)
	// GetSample - returns sample
	GetSample() ([][]interface{}, error)
	// GetData - returns data stream
	GetData() (chan *DataRow, error)
}

// TableProvider - table data provider
type TableProvider interface {
	// Open - open table
	Open() error
	// Close - close table
	Close()
	// GetShape - returns data shape
	GetShape() (*Shape, error)
	// GetHeaders - returns headers
	GetHeaders() ([]string, error)
	// GetSynteticHeaders - returns syntetic headers
	GetSynteticHeaders() ([]string, error)
	// GetTypes - returns types
	GetTypes() ([]*DataTypeWithOption, error)
	// TrySetType - attempt setting type to column
	TrySetType(index int, columnType DataType, options ...Option) error
	// SetTypes - setting types to column
	SetTypes(types []*DataTypeWithOption)
	// GetPlainSample - returns plain sample
	GetPlainSample() ([][]string, error)
	// GetSample - returns sample
	GetSample() ([][]interface{}, error)
	// GetData - returns data stream
	GetData() (chan *DataRow, error)
}

// FileReader - file reader interface
type FileReader interface {
	io.ReadSeeker
	io.ReadCloser
}

// Option - options interface
type Option interface {
	Key() string
	Value() interface{}
}

What are the steps am suppose to take?

Do you have the interface methods from somewhere? What is the shape for?

1 Like

Actually this is an example I am suppose to look at to get an idea


import (
	"encoding/json"
	"fmt"
)

// DataType - data type
type DataType int

const (
	// DataTypeString - data type string
	DataTypeString DataType = iota
	// DataTypeFloat - data type float
	DataTypeFloat DataType = iota
	// DataTypeInteger - data type integer
	DataTypeInteger DataType = iota
	// DataTypeUnsignedInteger - data type unsigned integer
	DataTypeUnsignedInteger DataType = iota
	// DataTypeDateTime - data type datetime
	DataTypeDateTime DataType = iota
	// DataTypeDate - data type date
	DataTypeDate DataType = iota

	// OptionDateTimeLayout - option date/time layout
	OptionDateTimeLayout = "layout"

	// OptionFloatFormat - option float format
	OptionFloatFormat = "format"
)

// String - string representation
func (inst DataType) String() string {
	switch inst {
	case DataTypeDateTime:
		return "datetime"
	case DataTypeDate:
		return "date"
	case DataTypeFloat:
		return "float"
	case DataTypeInteger:
		return "integer"
	case DataTypeUnsignedInteger:
		return "unsigned-integer"
	default:
		return "string"
	}
}

// DataTypeFromString - data type from string
func DataTypeFromString(value string) DataType {
	switch value {
	case "datetime":
		return DataTypeDateTime
	case "date":
		return DataTypeDate
	case "float":
		return DataTypeFloat
	case "integer":
		return DataTypeInteger
	case "unsigned-integer":
		return DataTypeUnsignedInteger
	default:
		return DataTypeString
	}
}

// Clickhouse - clickhouse type
func (inst DataType) Clickhouse() string {
	switch inst {
	case DataTypeString:
		return "LowCardinality(String)"
	case DataTypeDateTime:
		return "DateTime64"
	case DataTypeDate:
		return "Date"
	case DataTypeFloat:
		return "Float64"
	case DataTypeInteger:
		return "Int64"
	case DataTypeUnsignedInteger:
		return "UInt64"
	default:
		return ""
	}
}

// dataTypeOption - data type option struct
type dataTypeOption struct {
	key   string
	value interface{}
}

// Key - data type option key getting
func (inst *dataTypeOption) Key() string {
	return inst.key
}

// Value - data type option value getting
func (inst *dataTypeOption) Value() interface{} {
	return inst.value
}

// DataTypeOption - data type options
func DataTypeOption(key string, value interface{}) Option {
	return &dataTypeOption{key: key, value: value}
}

// DataTypeWithOption - data type with options
type DataTypeWithOption struct {
	dataType DataType
	options  map[string]Option
}

// NewDataTypeWithOption - data type with options constructor
func NewDataTypeWithOption(dataType DataType, options ...Option) *DataTypeWithOption {
	inst := &DataTypeWithOption{
		dataType: dataType,
		options:  make(map[string]Option),
	}
	for _, option := range options {
		inst.options[option.Key()] = option
	}
	return inst
}

// Type - data type getting
func (inst *DataTypeWithOption) Type() DataType {
	return inst.dataType
}

// Options - data type optinos getting
func (inst *DataTypeWithOption) Options() []Option {
	options := []Option{}
	for _, opt := range inst.options {
		options = append(options, opt)
	}
	return options
}

// String - string representation
func (inst *DataTypeWithOption) String() string {
	optionsString := ""
	for key, value := range inst.options {
		optionsString += fmt.Sprintf("%s=%v, ", key, value)
	}
	optionsString = trimSuffix(optionsString, ", ")
	if len(optionsString) > 0 {
		return fmt.Sprintf("%s (%s)", inst.dataType.String(), optionsString)
	}
	return fmt.Sprintf("%s", inst.dataType.String())
}

// MarshalJSON - json marshaling
func (inst *DataTypeWithOption) MarshalJSON() ([]byte, error) {
	type optionStruct struct {
		Key   string      `json:"key"`
		Value interface{} `json:"value"`
	}

	type proxyStruct struct {
		Type    string         `json:"type"`
		Options []optionStruct `json:"options"`
	}

	proxy := proxyStruct{
		Type:    inst.dataType.String(),
		Options: []optionStruct{},
	}
	for _, opt := range inst.options {
		proxy.Options = append(proxy.Options, optionStruct{Key: opt.Key(), Value: opt.Value()})
	}

	return json.Marshal(proxy)
}

// UnmarshalJSON - json unmarshaling
func (inst *DataTypeWithOption) UnmarshalJSON(data []byte) error {
	type optionStruct struct {
		Key   string      `json:"key"`
		Value interface{} `json:"value"`
	}

	type proxyStruct struct {
		Type    string         `json:"type"`
		Options []optionStruct `json:"options"`
	}

	proxy := proxyStruct{}
	err := json.Unmarshal(data, &proxy)
	if err != nil {
		return err
	}
	inst.dataType = DataTypeFromString(proxy.Type)
	for k := range inst.options {
		delete(inst.options, k)
	}
	for _, opt := range proxy.Options {
		inst.options[opt.Key] = &dataTypeOption{key: opt.Key, value: opt.Value}
	}

	return nil
}

// Clickhouse - clickhouse type
func (inst *DataTypeWithOption) Clickhouse() string {
	return inst.dataType.Clickhouse()
}

// DataRow - data row
type DataRow struct {
	Row   []interface{}
	Error error
}
package datastream

import (
	"bufio"
	"encoding/csv"
	"fmt"
	"io"
	"math"
	"os"
	"strconv"
	"strings"
	"time"
)

const (
	// DefaultCSVDelimiter - default csv delimiter
	DefaultCSVDelimiter rune = ','
	// DefaultCSVComment - default csv comment
	DefaultCSVComment rune = '#'

	// OptionCSVDelimiter - option csv delimiter
	OptionCSVDelimiter string = "csv-delimiter"
	// OptionCSVComment - option csv comment
	OptionCSVComment string = "csv-comment"
)

// FileCSV - file csv
type FileCSV struct {
	filename string
	file     FileReader
	types    []*DataTypeWithOption

	delimiter   rune
	comment     rune
	withHeaders bool
	sampleSize  int

	withOrderColumn bool
}

// NewFileCSV - file csv constructor
func NewFileCSV(filename string, types []*DataTypeWithOption, options ...Option) *FileCSV {
	inst := &FileCSV{
		filename:    filename,
		types:       types,
		delimiter:   DefaultCSVDelimiter,
		comment:     DefaultCSVComment,
		withHeaders: DefaultWithHeaders,
		sampleSize:  DefaultSampleSize,
	}
	for _, option := range options {
		switch option.Key() {
		case OptionCSVDelimiter:
			inst.delimiter = rune(option.Value().(string)[0])
		case OptionCSVComment:
			inst.comment = rune(option.Value().(string)[0])
		case OptionWithHeaders:
			inst.withHeaders = option.Value().(bool)
		case OptionSampleSize:
			v, ok := option.Value().(int)
			if !ok {
				v2, ok2 := option.Value().(float64)
				if !ok2 {
					continue
				}
				inst.sampleSize = int(v2)
			} else {
				inst.sampleSize = v
			}
		case OptionWithOrderColumn:
			inst.withOrderColumn = option.Value().(bool)
		}
	}

	return inst
}

// Open - open file
func (inst *FileCSV) Open() error {
	file, err := os.Open(inst.filename)
	if err != nil {
		return err
	}
	inst.file = file

	return nil
}

// Close - close file
func (inst *FileCSV) Close() {
	inst.file.Close()
	inst.file = nil
	inst.types = nil
}

// reader - csv reader getting
func (inst *FileCSV) reader(fromBegin bool) *csv.Reader {
	if fromBegin {
		inst.file.Seek(0, 0)
		bufReader := bufio.NewReader(inst.file)
		buf, err := bufReader.Peek(3)
		if err == nil {
			if buf[0] == 0xef && buf[1] == 0xbb && buf[2] == 0xbf {
				inst.file.Seek(3, 0)
			} else {
				inst.file.Seek(0, 0)
			}
		}
	}
	reader := csv.NewReader(inst.file)
	reader.Comma = inst.delimiter
	reader.Comment = inst.comment
	reader.FieldsPerRecord = -1
	return reader
}

// GetShape - getting data shape
func (inst *FileCSV) GetShape() (*Shape, error) {
	inst.file.Seek(0, 0)

	shape := &Shape{}

	reader := inst.reader(true)

	firstMet := false
	var firstRecord []string

	maxRow := 0
	for {
		record, err := reader.Read()
		if err == io.EOF {
			break
		}
		if err != nil {
			return shape, err
		}
		if !firstMet {
			firstMet = true
			firstRecord = record
		}
		maxRow++
	}
	if inst.withHeaders {
		maxRow--
	}
	shape.X = maxRow
	maxCol := len(firstRecord)
	shape.Y = maxCol

	return shape, nil
}

// GetHeaders - returns headers
func (inst *FileCSV) GetHeaders() ([]string, error) {
	reader := inst.reader(true)
	headers, err := reader.Read()
	if err != nil {
		return nil, err
	}
	if !inst.withHeaders {
		headers = make([]string, len(headers))
	}
	if inst.withOrderColumn {
		headers = append([]string{fieldDataOrder.HeaderName}, headers...)
	}

	return headers, nil
}

// GetSynteticHeaders - returns syntetic headers
func (inst *FileCSV) GetSynteticHeaders() ([]string, error) {
	reader := inst.reader(true)
	record, err := reader.Read()
	if err != nil {
		return nil, err
	}
	headers := []string{}
	i := 0
	for range record {
		headers = append(headers, fmt.Sprintf("col%d", i+1))
		i++
	}
	if inst.withOrderColumn {
		headers = append([]string{fieldDataOrder.ColumnName}, headers...)
	}
	return headers, nil
}

// GetTypes - returns types
func (inst *FileCSV) GetTypes() ([]*DataTypeWithOption, error) {
	if len(inst.types) > 0 {
		return inst.types, nil
	}

	reader := inst.reader(true)
	record, err := reader.Read()
	if err != nil {
		return nil, err
	}
	types := []*DataTypeWithOption{}
	for range record {
		types = append(types, NewDataTypeWithOption(DataTypeString))
	}
	if inst.withOrderColumn {
		types = append([]*DataTypeWithOption{fieldDataOrder.Type}, types...)
	}

	inst.types = types
	return types, nil
}

// tryColumnType - attempt to apply type to column
func (inst *FileCSV) tryColumnType(value string, columnType DataType, options ...Option) error {
	var err error
	switch columnType {
	case DataTypeInteger:
		_, err = strconv.ParseInt(value, 10, 64)
	case DataTypeUnsignedInteger:
		_, err = strconv.ParseUint(value, 10, 64)
	case DataTypeFloat:
		format := DefaultFloatFormat
		for _, option := range options {
			if option.Key() == OptionFloatFormat {
				format = option.Value().(string)
			}
		}
		switch format {
		case ExcelFloatFormat:
			val := strings.Replace(value, ",", ".", -1)
			_, err = strconv.ParseFloat(val, 64)
		case CommaSeparatedFloatFormat:
			val := strings.Replace(value, ",", "", -1)
			_, err = strconv.ParseFloat(val, 64)
		case ScientificFloatFormat:
			pos := strings.IndexAny(value, "eE")
			if pos < 0 {
				_, err = strconv.ParseFloat(value, 64)
			} else {
				_, err2 := strconv.ParseFloat(value[0:pos], 64)
				if err2 != nil {
					err = err2
				} else {
					_, err = strconv.ParseInt(value[(pos+1):], 10, 64)
				}
			}
		default:
			_, err = strconv.ParseFloat(value, 64)
		}
	case DataTypeDate:
		layout := DefaultDateLayout
		for _, option := range options {
			if option.Key() == OptionDateTimeLayout {
				layout = option.Value().(string)
			}
		}
		_, err = time.Parse(layout, value)
	case DataTypeDateTime:
		layout := DefaultDateTimeLayout
		for _, option := range options {
			if option.Key() == OptionDateTimeLayout {
				layout = option.Value().(string)
			}
		}
		_, err = time.Parse(layout, value)
	default:
		err = nil
	}

	if err != nil {
		return err
	}
	return nil
}

// applyColumnType - applying type to column
func (inst *FileCSV) applyColumnType(value string, columnType DataType, options ...Option) (interface{}, error) {
	var newValue interface{}

	var err error
	switch columnType {
	case DataTypeInteger:
		newValue, err = strconv.ParseInt(value, 10, 64)
	case DataTypeUnsignedInteger:
		newValue, err = strconv.ParseUint(value, 10, 64)
	case DataTypeFloat:
		format := DefaultFloatFormat
		for _, option := range options {
			if option.Key() == OptionFloatFormat {
				format = option.Value().(string)
			}
		}
		switch format {
		case ExcelFloatFormat:
			val := strings.Replace(value, ",", ".", -1)
			newValue, err = strconv.ParseFloat(val, 64)
		case CommaSeparatedFloatFormat:
			val := strings.Replace(value, ",", "", -1)
			newValue, err = strconv.ParseFloat(val, 64)
		case ScientificFloatFormat:
			pos := strings.IndexAny(value, "eE")
			if pos < 0 {
				newValue, err = strconv.ParseFloat(value, 64)
			} else {
				baseVal, err2 := strconv.ParseFloat(value[0:pos], 64)
				if err2 != nil {
					err = err2
				} else {
					expVal, err2 := strconv.ParseInt(value[(pos+1):], 10, 64)
					if err2 != nil {
						err = err2
					}
					newValue = baseVal * math.Pow10(int(expVal))
				}
			}
		default:
			newValue, err = strconv.ParseFloat(value, 64)
		}
	case DataTypeDateTime:
		layout := DefaultDateTimeLayout
		for _, option := range options {
			if option.Key() == OptionDateTimeLayout {
				layout = option.Value().(string)
			}
		}
		newValue, err = time.Parse(layout, value)
	case DataTypeDate:
		layout := DefaultDateLayout
		for _, option := range options {
			if option.Key() == OptionDateTimeLayout {
				layout = option.Value().(string)
			}
		}
		newValue, err = time.Parse(layout, value)
	default:
		newValue, err = value, nil
	}

	if err != nil {
		return newValue, err
	}
	return newValue, nil
}

// TrySetType - attempt setting type to column
func (inst *FileCSV) TrySetType(index int, columnType DataType, options ...Option) error {
	if columnType == DataTypeString {
		return nil
	}

	reader := inst.reader(true)

	hasMetFirst := false
	for {
		record, err := reader.Read()
		if err == io.EOF {
			break
		}
		if err != nil {
			return err
		}
		if !hasMetFirst {
			hasMetFirst = true
			if inst.withHeaders {
				continue
			}
		}
		value := record[index]
		err = inst.tryColumnType(value, columnType, options...)
		if err != nil {
			return err
		}
	}

	return nil
}

// SetTypes - setting types to column
func (inst *FileCSV) SetTypes(types []*DataTypeWithOption) {
	if len(types) > 0 {
		if inst.withOrderColumn {
			inst.types = append([]*DataTypeWithOption{fieldDataOrder.Type}, types...)
		} else {
			inst.types = types
		}
	}
}

// GetPlainSample - returns plain sample
func (inst *FileCSV) GetPlainSample() ([][]string, error) {
	rows := [][]string{}
	inst.file.Seek(0, 0)
	row := bufio.NewScanner(inst.file)
	size := 0
	for row.Scan() && size < inst.sampleSize {
		line := strings.TrimRight(row.Text(), "\r\n")
		rows = append(rows, []string{line})
		size++
	}
	return rows, nil
}

// GetSample - returns sample
func (inst *FileCSV) GetSample() ([][]interface{}, error) {
	sample := [][]interface{}{}

	types, err := inst.GetTypes()
	if err != nil {
		return sample, err
	}
	if inst.withOrderColumn {
		types = types[1:]
	}
	reader := inst.reader(true)

	if inst.withHeaders {
		_, err := reader.Read()
		if err == io.EOF {
			return sample, nil
		}
		if err != nil {
			return sample, err
		}
	}
	orderCounter := 0
	for i := 0; i < inst.sampleSize; i++ {
		record, err := reader.Read()
		if err == io.EOF {
			break
		}
		if err != nil {
			return sample, err
		}

		data := []interface{}{}
		if inst.withOrderColumn {
			data = append(data, orderCounter+1)
		}
		if len(record) != len(types) {
			return sample, ErrorIncorrectTypesNumber
		}
		for j, rv := range record {
			v, err := inst.applyColumnType(rv, types[j].Type(), types[j].Options()...)
			if err != nil {
				return sample, err
			}
			data = append(data, v)
		}
		sample = append(sample, data)
		orderCounter++
	}

	return sample, nil
}

// GetData - returns data stream
func (inst *FileCSV) GetData() (chan *DataRow, error) {
	dataStream := make(chan *DataRow)

	types, err := inst.GetTypes()
	if err != nil {
		return dataStream, err
	}
	reader := inst.reader(true)
	if inst.withOrderColumn {
		types = types[1:]
	}

	go func(stream chan *DataRow, types []*DataTypeWithOption, withHeaders bool) {
		hasMetFirst := false
		orderCounter := 0
		hasError := false
		for {
			dr := &DataRow{}

			record, err := reader.Read()
			if err == io.EOF {
				break
			}
			if err != nil {
				dr.Error = err
				stream <- dr
				hasError = true
				break
			}
			if len(record) != len(types) {
				dr.Error = ErrorIncorrectTypesNumber
				stream <- dr
				hasError = true
				break
			}
			if !hasMetFirst {
				hasMetFirst = true
				if inst.withHeaders {
					continue
				}
			}
			if inst.withOrderColumn {
				dr.Row = append(dr.Row, orderCounter+1)
			}
			for i, rv := range record {
				v, err := inst.applyColumnType(rv, types[i].Type(), types[i].Options()...)
				if err != nil {
					dr.Error = err
					stream <- dr
					hasError = true
					break
				}
				dr.Row = append(dr.Row, v)
			}
			if hasError {
				break
			}
			stream <- dr
			orderCounter++
		}
		close(dataStream)
	}(dataStream, types, inst.withHeaders)

	return dataStream, nil
}

If I understand the project correctly, what you want to achieve is what you find here https://www.php.net/manual/en/function.odbc-connect.php - full documentation https://www.php.net/manual/en/book.uodbc.php

That is somewhat of a project :slight_smile:

1 Like

Thanks for the prompt replies and links. Will surely take a look at them