zetasqlite

README

go-zetasqlite

A database driver library that interprets ZetaSQL queries and runs them using SQLite3

Features

go-zetasqlite supports database/sql driver interface. So, you can use ZetaSQL queries just by importing github.com/goccy/go-zetasqlite. Also, go-zetasqlite uses SQLite3 as the database engine. Since we are using go-sqlite3, we can use the options ( like :memory: ) supported by go-sqlite3 ( see details ). ZetaSQL functionality is provided by go-zetasql

Installation

go get github.com/goccy/go-zetasqlite

NOTE

Since this library uses go-zetasql, the following environment variables must be enabled in order to build. See here for details.

CGO_ENABLED=1
CXX=clang++

Synopsis

You can pass ZetaSQL queries to Query/Exec function of database/sql package.

package main

import (
  "database/sql"
  "fmt"

  _ "github.com/goccy/go-zetasqlite"
)

func main() {
  db, err := sql.Open("zetasqlite", ":memory:")
  if err != nil {
    panic(err)
  }
  defer db.Close()

  rows, err := db.Query(`SELECT * FROM UNNEST([?, ?, ?])`, 1, 2, 3)
  if err != nil {
    panic(err)
  }
  var ids []int64
  for rows.Next() {
    var id int64
    if err := rows.Scan(&id); err != nil {
      panic(err)
    }
    ids = append(ids, id)
  }
  fmt.Println(ids) // [1 2 3]
}

Status

A list of ZetaSQL specifications and features supported by go-zetasqlite.

Types

• INT64 ( INT, SMALLINT, INTEGER, BIGINT, TINYINT, BYTEINT )
• NUMERIC ( DECIMAL )
• BIGNUMERIC ( BIGDECIMAL )
• FLOAT64 ( FLOAT )
• BOOL ( BOOLEAN )
• STRING
• BYTES
• DATE
• TIME
• DATETIME
• TIMESTAMP
• INTERVAL
• ARRAY
• STRUCT
• JSON
• RECORD
• GEOGRAPHY

Statements

• SELECT
• UPDATE
• INSERT
• DELETE
• DROP
• TRUNCATE
• MERGE
• BEGIN-END
• BEGIN TRANSACTION
• COMMIT TRANSACTION
• CREATE TABLE
• CREATE FUNCTION
• CREATE TEMPORARY TABLE
• CREATE TEMPORARY FUNCTION

Standard SQL Features

Operator precedence

• Field access operator
• Array subscript operator
• JSON subscript operator
• Unary operators ( +, -, ~ )
• Multiplication ( * )
• Division ( / )
• Concatenation operator ( || )
• Addition ( + )
• Subtraction ( - )
• Bitwise operators ( <<, >>, &, | )
• Comparison operators ( =, <, >, <=, >=, !=, <>)
• [NOT] LIKE
• [NOT] BETWEEN
• [NOT] IN
• IS [NOT] NULL
• IS [NOT] TRUE
• IS [NOT] FALSE
• NOT
• AND
• OR
• [NOT] EXISTS
• IS [NOT] DISTINCT FROM

Conditional expressions

• CASE-WHEN
• COALESCE
• IF
• IFNULL
• NULLIF

Other clauses

• OVER
• WINDOW
• WITH
• UNION
• HAVING
• ORDER BY
• GROUP BY - ROLLUP
• INNER/LEFT/RIGHT/FULL/CROSS JOIN
• QUALIFY
• EXCEPT
• REPLACE

Aggregate functions

• ANY_VALUE
• ARRAY_AGG
• ARRAY_CONCAT_AGG
• AVG
• BIT_AND
• BIT_OR
• BIT_XOR
• COUNT
• COUNTIF
• LOGICAL_AND
• LOGICAL_OR
• MAX
• MIN
• STRING_AGG
• SUM

Statistical aggregate functions

• CORR
• COVAR_POP
• COVAR_SAMP
• STDDEV_POP
• STDDEV_SAMP
• STDDEV
• VAR_POP
• VAR_SAMP
• VARIANCE

Approximate aggregate functions

• APPROX_COUNT_DISTINCT
• APPROX_QUANTILES
• APPROX_TOP_COUNT
• APPROX_TOP_SUM

HyperLogLog++ functions

• HLL_COUNT.INIT
• HLL_COUNT.MERGE
• HLL_COUNT.MERGE_PARTIAL
• HLL_COUNT.EXTRACT

Numbering functions

• RANK
• DENSE_RANK
• PERCENT_RANK
• CUME_DIST
• NTILE
• ROW_NUMBER

Bit functions

• BIT_COUNT

Conversion functions

• CAST AS ARRAY
• CAST AS BIGNUMERIC
• CAST AS BOOL
• CAST AS BYTES
• CAST AS DATE
• CAST AS DATETIME
• CAST AS FLOAT64
• CAST AS INT64
• CAST AS INTERVAL
• CAST AS NUMERIC
• CAST AS STRING
• CAST AS STRUCT
• CAST AS TIME
• CAST AS TIMESTAMP
• PARSE_BIGNUMERIC
• PARSE_NUMERIC
• SAFE_CAST
• Format clause for CAST

Mathematical functions

• ABS
• SIGN
• IS_INF
• IS_NAN
• IEEE_DIVIDE
• RAND
• SQRT
• POW
• POWER
• EXP
• LN
• LOG
• LOG10
• GREATEST
• LEAST
• DIV
• SAFE_DIVIDE
• SAFE_MULTIPLY
• SAFE_NEGATE
• SAFE_ADD
• SAFE_SUBTRACT
• MOD
• ROUND
• TRUNC
• CEIL
• CEILING
• FLOOR
• COS
• COSH
• ACOS
• ACOSH
• SIN
• SINH
• ASIN
• ASINH
• TAN
• TANH
• ATAN
• ATANH
• ATAN2
• RANGE_BUCKET

Navigation functions

• FIRST_VALUE
• LAST_VALUE
• NTH_VALUE
• LEAD
• LAG
• PERCENTILE_CONT
• PERCENTILE_DISC

Hash functions

• FARM_FINGERPRINT
• MD5
• SHA1
• SHA256
• SHA512

String functions

• ASCII
• BYTE_LENGTH
• CHAR_LENGTH
• CHARACTER_LENGTH
• CHR
• CODE_POINTS_TO_BYTES
• CODE_POINTS_TO_STRING
• COLLATE
• CONCAT
• CONTAINS_SUBSTR
• ENDS_WITH
• FORMAT
• FROM_BASE32
• FROM_BASE64
• FROM_HEX
• INITCAP
• INSTR
• LEFT
• LENGTH
• LPAD
• LOWER
• LTRIM
• NORMALIZE
• NORMALIZE_AND_CASEFOLD
• OCTET_LENGTH
• REGEXP_CONTAINS
• REGEXP_EXTRACT
• REGEXP_EXTRACT_ALL
• REGEXP_INSTR
• REGEXP_REPLACE
• REGEXP_SUBSTR
• REPLACE
• REPEAT
• REVERSE
• RIGHT
• RPAD
• RTRIM
• SAFE_CONVERT_BYTES_TO_STRING
• SOUNDEX
• SPLIT
• STARTS_WITH
• STRPOS
• SUBSTR
• SUBSTRING
• TO_BASE32
• TO_BASE64
• TO_CODE_POINTS
• TO_HEX
• TRANSALTE
• TRIM
• UNICODE
• UPPER

JSON functions

• JSON_EXTRACT
• JSON_QUERY
• JSON_EXTRACT_SCALAR
• JSON_VALUE
• JSON_EXTRACT_ARRAY
• JSON_QUERY_ARRAY
• JSON_EXTRACT_STRING_ARRAY
• JSON_VALUE_ARRAY
• PARSE_JSON
• TO_JSON
• TO_JSON_STRING
• STRING
• BOOL
• INT64
• FLOAT64
• JSON_TYPE

Array functions

• ARRAY
• ARRAY_CONCAT
• ARRAY_LENGTH
• ARRAY_TO_STRING
• GENERATE_ARRAY
• GENERATE_DATE_ARRAY
• GENERATE_TIMESTAMP_ARRAY
• ARRAY_REVERSE

Date functions

• CURRENT_DATE
• EXTRACT
• DATE
• DATE_ADD
• DATE_SUB
• DATE_DIFF
• DATE_TRUNC
• DATE_FROM_UNIX_DATE
• FORMAT_DATE
• LAST_DAY
• PARSE_DATE
• UNIX_DATE

Datetime functions

• CURRENT_DATETIME
• DATETIME
• EXTRACT
• DATETIME_ADD
• DATETIME_SUB
• DATETIME_DIFF
• DATETIME_TRUNC
• FORMAT_DATETIME
• LAST_DAY
• PARSE_DATETIME

Time functions

• CURRENT_TIME
• TIME
• EXTRACT
• TIME_ADD
• TIME_SUB
• TIME_DIFF
• TIME_TRUNC
• FORMAT_TIME
• PARSE_TIME

Timestamp functions

• CURRENT_TIMESTAMP
• EXTRACT
• STRING
• TIMESTAMP
• TIMESTAMP_ADD
• TIMESTAMP_SUB
• TIMESTAMP_DIFF
• TIMESTAMP_TRUNC
• FORMAT_TIMESTAMP
• PARSE_TIMESTAMP
• TIMESTAMP_SECONDS
• TIMESTAMP_MILLIS
• TIMEATAMP_MICROS
• UNIX_SECONDS
• UNIX_MILLIS
• UNIX_MICROS

Interval functions

• MAKE_INTERVAL
• EXTRACT
• JUSTIFY_DAYS
• JUSTIFY_HOURS
• JUSTIFY_INTERVAL

Geography functions

Not suported yet

Security functions

• SESSION_USER

UUID functions

• GENERATE_UUID

Net functions

• NET.IP_FROM_STRING
• NET.SAFE_IP_FROM_STRING
• NET.IP_TO_STRING
• NET.IP_NET_MASK
• NET.IP_TRUNC
• NET.IPV4_FROM_INT64
• NET.IPV4_TO_INT64
• NET.HOST
• NET.PUBLIC_SUFFIX
• NET.REG_DOMAIN

Debugging functions

• ERROR

AEAD encryption functions

• KEYS.NEW_KEYSET
• KEYS.ADD_KEY_FROM_RAW_BYTES
• AEAD.DECRYPT_BYTES
• AEAD.DECRYPT_STRING
• AEAD.ENCRYPT
• DETERMINISTIC_DECRYPT_BYTES
• DETERMINISTIC_DECRYPT_STRING
• DETERMINISTIC_ENCRYPT
• KEYS.KEYSET_CHAIN
• KEYS.KEYSET_FROM_JSON
• KEYS.KEYSET_TO_JSON
• KEYS.ROTATE_KEYSET
• KEYS.KEYSET_LENGTH

License

MIT

Documentation

Index

• func CurrentTime(ctx context.Context) *time.Time
• func TimeFromTimestampValue(v string) (time.Time, error)
• func WithCurrentTime(ctx context.Context, now time.Time) context.Context
• type ColumnType
  • func UnmarshalDatabaseTypeName(typ string) (*ColumnType, error)
• type ZetaSQLiteConn
  • func (c *ZetaSQLiteConn) AddNamePath(path string)
  • func (c *ZetaSQLiteConn) Begin() (driver.Tx, error)
  • func (c *ZetaSQLiteConn) BeginTx(ctx context.Context, opts driver.TxOptions) (driver.Tx, error)
  • func (s *ZetaSQLiteConn) CheckNamedValue(value *driver.NamedValue) error
  • func (c *ZetaSQLiteConn) Close() error
  • func (c *ZetaSQLiteConn) ExecContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Result, error)
  • func (c *ZetaSQLiteConn) NamePath() []string
  • func (c *ZetaSQLiteConn) Prepare(query string) (driver.Stmt, error)
  • func (c *ZetaSQLiteConn) QueryContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Rows, error)
  • func (c *ZetaSQLiteConn) SetNamePath(path []string)
• type ZetaSQLiteDriver
  • func (d *ZetaSQLiteDriver) Open(name string) (driver.Conn, error)
• type ZetaSQLiteTx
  • func (tx *ZetaSQLiteTx) Commit() error
  • func (tx *ZetaSQLiteTx) Rollback() error

Functions

func CurrentTime

func CurrentTime(ctx context.Context) *time.Time

CurrentTime gets the time specified by WithCurrentTime.

func TimeFromTimestampValue

func TimeFromTimestampValue(v string) (time.Time, error)

TimeFromTimestampValue zetasqlite returns string values ​​by default for timestamp values. This function is a helper function to convert that value to time.Time type.

func WithCurrentTime

func WithCurrentTime(ctx context.Context, now time.Time) context.Context

WithCurrentTime use to replace the current time with the specified time. To replace the time, you need to pass the returned context as an argument to QueryContext. `CURRENT_DATE`, `CURRENT_DATETIME`, `CURRENT_TIME`, `CURRENT_TIMESTAMP` functions are targeted.

Types

type ColumnType

type ColumnType = internal.Type

func UnmarshalDatabaseTypeName

func UnmarshalDatabaseTypeName(typ string) (*ColumnType, error)

type ZetaSQLiteConn

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

func (*ZetaSQLiteConn) AddNamePath

func (c *ZetaSQLiteConn) AddNamePath(path string)

func (*ZetaSQLiteConn) Begin

func (c *ZetaSQLiteConn) Begin() (driver.Tx, error)

func (*ZetaSQLiteConn) BeginTx

func (c *ZetaSQLiteConn) BeginTx(ctx context.Context, opts driver.TxOptions) (driver.Tx, error)

func (*ZetaSQLiteConn) CheckNamedValue

func (s *ZetaSQLiteConn) CheckNamedValue(value *driver.NamedValue) error

func (*ZetaSQLiteConn) Close

func (c *ZetaSQLiteConn) Close() error

func (*ZetaSQLiteConn) ExecContext

func (c *ZetaSQLiteConn) ExecContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Result, error)

func (*ZetaSQLiteConn) NamePath

func (c *ZetaSQLiteConn) NamePath() []string

func (*ZetaSQLiteConn) Prepare

func (c *ZetaSQLiteConn) Prepare(query string) (driver.Stmt, error)

func (*ZetaSQLiteConn) QueryContext

func (c *ZetaSQLiteConn) QueryContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Rows, error)

func (*ZetaSQLiteConn) SetNamePath

func (c *ZetaSQLiteConn) SetNamePath(path []string)

type ZetaSQLiteDriver

type ZetaSQLiteDriver struct {
	ConnectHook func(*ZetaSQLiteConn) error
}

func (*ZetaSQLiteDriver) Open

func (d *ZetaSQLiteDriver) Open(name string) (driver.Conn, error)

type ZetaSQLiteTx

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

func (*ZetaSQLiteTx) Commit

func (tx *ZetaSQLiteTx) Commit() error

func (*ZetaSQLiteTx) Rollback

func (tx *ZetaSQLiteTx) Rollback() error