zetasqlite

package module
v0.19.3 Latest Latest
Warning

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

 Go to latest Published: Jul 1, 2024 License: MIT Imports: 12 Imported by: 5

README

go-zetasqlite

Go GoDoc codecov

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]
}

Tools

ZetaSQLite CLI

You can execute ZetaSQL queries interactively by using the tools provided by cmd/zetasqlite-cli. See here for details

Status

A list of ZetaSQL ( Google Standard SQL ) 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

Expressions

Operators
  • 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 expr
  • CASE
  • COALESCE
  • IFNULL
  • NULLIF
Subqueries
  • Expression subqueries
    • Scalar subqueries
    • ARRAY subqueries
    • IN subqueries
    • EXISTS subqueries
  • Table subqueries
  • Correlated subqueries
  • Volatile subqueries

Query

  • SELECT statement
    • SELECT *
    • SELECT expression
    • SELECT expression.*
    • SELECT * EXCEPT
    • SELECT * REPLACE
    • SELECT DISTINCT
    • SELECT ALL
    • SELECT AS STRUCT
    • SELECT AS VALUE
  • FROM clause
  • UNNEST operator
    • UNNEST and STRUCTs
    • Explicit and implicit UNNEST
    • UNNEST and NULLs
    • UNNEST and WITH OFFSET
  • PIVOT operator
  • UNPIVOT operator
  • TABLESAMPLE operator
  • JOIN operation
    • INNER JOIN
    • CROSS JOIN
    • Comma cross join (,)
    • FULL OUTER JOIN
    • LEFT OUTER JOIN
    • RIGHT OUTER JOIN
    • ON clause
    • USING clause
    • ON and USING equivalency
    • Join operations in a sequence
    • Correlated join operation
  • WHERE clause
  • GROUP BY clause
  • HAVING clause
    • Mandatory aggregation
  • ORDER BY clause
  • QUALIFY clause
  • WINDOW clause
  • Set operators
    • UNION
    • INTERSECT
    • EXCEPT
  • LIMIT and OFFSET clauses
  • WITH clause
    • RECURSIVE keyword
    • Non-recursive CTEs
    • Recursive CTEs
    • CTE rules and constraints
    • CTE visibility
  • Using aliases
    • Explicit aliases
    • Implicit aliases
    • Alias visibility
    • Duplicate aliases
    • Ambiguous aliases
    • Range variables
  • Value tables
    • Return query results as a value table
    • Create a table with a value table
    • Use a set operation on a value table
  • Queries for wildcard table

Statements

DDL ( Data Definition Language )
  • CREATE SCHEMA
  • CREATE TABLE
  • CREATE TABLE LIKE
  • CREATE TABLE COPY
  • CREATE SNAPSHOT TABLE
  • CREATE TABLE CLONE
  • CREATE VIEW
  • CREATE MATERIALIZED VIEW
  • CREATE EXTERNAL TABLE
  • CREATE FUNCTION
  • CREATE TABLE FUNCTION
  • CREATE PROCEDURE
  • CREATE ROW ACCESS POLICY
  • CREATE CAPACITY
  • CREATE RESERVATION
  • CREATE ASSIGNMENT
  • CREATE SEARCH INDEX
  • ALTER SCHEMA SET DEFAULT COLLATE
  • ALTER SCHEMA SET OPTIONS
  • ALTER TABLE SET OPTIONS
  • ALTER TABLE ADD COLUMN
  • ALTER TABLE RENAME TO
  • ALTER TABLE RENAME COLUMN
  • ALTER TABLE DROP COLUMN
  • ALTER TABLE SET DEFAULT COLLATE
  • ALTER COLUMN SET OPTIONS
  • ALTER COLUMN DROP NOT NULL
  • ALTER COLUMN SET DATA TYPE
  • ALTER COLUMN SET DEFAULT
  • ALTER COLUMN DROP DEFAULT
  • ALTER VIEW SET OPTIONS
  • ALTER MATERIALIZED VIEW SET OPTIONS
  • ALTER ORGANIZATION SET OPTIONS
  • ALTER PROJECT SET OPTIONS
  • ALTER BI_CAPACITY SET OPTIONS
  • DROP SCHEMA
  • DROP TABLE
  • DROP SNAPSHOT TABLE
  • DROP EXTERNAL TABLE
  • DROP VIEW
  • DROP MATERIALIZED VIEW
  • DROP FUNCTION
  • DROP TABLE FUNCTION
  • DROP PROCEDURE
  • DROP ROW ACCESS POLICY
  • DROP CAPACITY
  • DROP RESERVATION
  • DROP ASSIGNMENT
  • DROP SEARCH INDEX
DML ( Data Manipulation Language )
  • INSERT
  • DELETE
  • TRUNCATE TABLE
  • UPDATE
  • MERGE
DCL ( Data Control Language )
  • GRANT
  • REVOKE
Procedural Language
  • DECLARE
  • SET
  • EXECUTE IMMEDIATE
  • BEGIN...END
  • BEGIN...EXCEPTION...END
  • CASE
  • CASE search_expression
  • IF
  • Labels
  • Loops
    • LOOP
    • REPEATE
    • WHILE
    • BREAK
    • LEAVE
    • CONTINUE
    • ITERATE
    • FOR...IN
  • Transactions
    • BEGIN TRANSACTION
    • COMMIT TRANSACTION
    • ROLLBACK TRANSACTION
  • RAISE
  • RETURN
  • CALL
Debugging Statements
  • ASSERT
Other Statements
  • EXPORT DATA
  • LOAD DATA

User Defined Functions

  • User Defined Function

  • Templated Argument Function

    • If the return type is not specified, templated argument function supports only some types of patterns.
      • ANY -> ANY
      • ARRAY<ANY> -> ANY
      • ANY -> ARRAY<ANY>
      • If the return type is always fixed, only some types are supported, such as INT64 / DOUBLE

  • JavaScript UDF

Functions

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
  • S2_CELLIDFROMPOINT
  • S2_COVERINGCELLIDS
  • ST_ANGLE
  • ST_AREA
  • ST_ASBINARY
  • ST_ASGEOJSON
  • ST_ASTEXT
  • ST_AZIMUTH
  • ST_BOUNDARY
  • ST_BOUNDINGBOX
  • ST_BUFFER
  • ST_BUFFERWITHTOLERANCE
  • ST_CENTROID
  • ST_CENTROID_AGG
  • ST_CLOSESTPOINT
  • ST_CLUSTERDBSCAN
  • ST_CONTAINS
  • ST_CONVEXHULL
  • ST_COVEREDBY
  • ST_COVERS
  • ST_DIFFERENCE
  • ST_DIMENSION
  • ST_DISJOINT
  • ST_DISTANCE
  • ST_DUMP
  • ST_DWITHIN
  • ST_ENDPOINT
  • ST_EQUALS
  • ST_EXTENT
  • ST_EXTERIORRING
  • ST_GEOGFROM
  • ST_GEOGFROMGEOJSON
  • ST_GEOGFROMTEXT
  • ST_GEOGFROMWKB
  • ST_GEOGPOINT
  • ST_GEOGPOINTFROMGEOHASH
  • ST_GEOHASH
  • ST_GEOMETRYTYPE
  • ST_INTERIORRINGS
  • ST_INTERSECTION
  • ST_INTERSECTS
  • ST_INTERSECTSBOX
  • ST_ISCLOSED
  • ST_ISCOLLECTION
  • ST_ISEMPTY
  • ST_ISRING
  • ST_LENGTH
  • ST_MAKELINE
  • ST_MAKEPOLYGON
  • ST_MAKEPOLYGONORIENTED
  • ST_MAXDISTANCE
  • ST_NPOINTS
  • ST_NUMGEOMETRIES
  • ST_NUMPOINTS
  • ST_PERIMETER
  • ST_POINTN
  • ST_SIMPLIFY
  • ST_SNAPTOGRID
  • ST_STARTPOINT
  • ST_TOUCHES
  • ST_UNION
  • ST_UNION_AGG
  • ST_WITHIN
  • ST_X
  • ST_Y
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

Constants

This section is empty.

Variables

This section is empty.

Functions

func CurrentTime added in v0.3.0 

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

CurrentTime gets the time specified by WithCurrentTime.

func TimeFromTimestampValue added in v0.6.6 

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 added in v0.3.0 

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 ChangedCatalog added in v0.16.0 

type ChangedCatalog = internal.ChangedCatalog

func ChangedCatalogFromResult added in v0.16.0 

func ChangedCatalogFromResult(result sql.Result) (*ChangedCatalog, error)

ChangedCatalogFromResult retrieve modified catalog information from sql.Result. NOTE: This API relies on the internal structure of sql.Result, so not will work for all Go versions.

func ChangedCatalogFromRows added in v0.16.0 

func ChangedCatalogFromRows(rows *sql.Rows) (*ChangedCatalog, error)

ChangedCatalogFromRows retrieve modified catalog information from sql.Rows. NOTE: This API relies on the internal structure of sql.Rows, so not will work for all Go versions.

type ChangedFunction added in v0.16.0 

type ChangedFunction = internal.ChangedFunction

type ChangedTable added in v0.16.0 

type ChangedTable = internal.ChangedTable

type ColumnSpec 

type ColumnSpec = internal.ColumnSpec

type ColumnType added in v0.6.2 

type ColumnType = internal.Type

func UnmarshalDatabaseTypeName added in v0.6.2 

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

type FunctionSpec 

type FunctionSpec = internal.FunctionSpec

type NameWithType 

type NameWithType = internal.NameWithType

type TableSpec 

type TableSpec = internal.TableSpec

type Type 

type Type = internal.Type

type ZetaSQLiteConn 

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

func (*ZetaSQLiteConn) AddNamePath 

func (c *ZetaSQLiteConn) AddNamePath(path string) error

AddNamePath add path to name path to be set as prefix. If max name path is specified, an error is returned if the number is exceeded.

func (*ZetaSQLiteConn) Begin 

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

func (*ZetaSQLiteConn) BeginTx added in v0.4.1 

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) (r driver.Result, e error)

func (*ZetaSQLiteConn) MaxNamePath added in v0.17.0 

func (c *ZetaSQLiteConn) MaxNamePath() int

MaxNamePath returns maximum value of name path.

func (*ZetaSQLiteConn) NamePath 

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

NamePath returns path to name path to be set as prefix.

func (*ZetaSQLiteConn) Prepare 

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

func (*ZetaSQLiteConn) PrepareContext added in v0.19.2 

func (c *ZetaSQLiteConn) PrepareContext(ctx context.Context, query string) (driver.Stmt, error)

func (*ZetaSQLiteConn) QueryContext 

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

func (*ZetaSQLiteConn) SetAutoIndexMode added in v0.12.0 

func (c *ZetaSQLiteConn) SetAutoIndexMode(enabled bool)

func (*ZetaSQLiteConn) SetExplainMode added in v0.12.0 

func (c *ZetaSQLiteConn) SetExplainMode(enabled bool)

func (*ZetaSQLiteConn) SetMaxNamePath added in v0.17.0 

func (c *ZetaSQLiteConn) SetMaxNamePath(num int)

SetMaxNamePath specifies the maximum value of name path. If the name path in the query is the maximum value, the name path set as prefix is not used. Effective only when a value greater than zero is specified ( default zero ).

func (*ZetaSQLiteConn) SetNamePath 

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

SetNamePath set path to name path to be set as prefix. If max name path is specified, an error is returned if the number is exceeded.

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

Source Files

View all Source files

Directories

Path Synopsis
cmd
zetasqlite-cli Module

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.