googlesqlite

go-googlesqlite

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

Features

go-googlesqlite supports database/sql driver interface. So, you can use GoogleSQL queries just by importing github.com/vantaboard/go-googlesqlite. Also, go-googlesqlite uses SQLite3 as the database engine. Since we are using modernc.org/sqlite, we can use the options ( like :memory: ) supported by sqlite ( see details ). GoogleSQL functionality is provided by go-googlesql

Installation

go get github.com/vantaboard/go-googlesqlite

NOTE

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

CGO_ENABLED=1
CXX=clang++

For a full local stack (go-googlesql, go-googlesqlite, and the Vantaboard bigquery-emulator fork as sibling repos), copy go.work.dev to go.work (or set GOWORK to that file). Local path overrides live in the workspace file, not in go.mod. Emulator Docker builds use only that module's tree, so they rely on published versions unless you change the build context.

When exercising the whole stack, run go test in each repo one at a time (not in parallel) to avoid OOM from overlapping CGO builds, and set a shared GOCACHE (and optionally GOMODCACHE) as described in the go-googlesql README so go-googlesql compile artifacts are reused.

Default: unified prebuilt go-googlesql: Use the same default tag stack as upstream task test:local: googlesql,googlesql_unified_prebuilt,googlesql_prebuilts_mod,googlesql_prebuilts_platform_pkg with libprotobuf_cgo.a and libgooglesql.a in the go-googlesql tree (see docs/prebuilt-cgo.md). From a sibling checkout, run task prebuilt:protobuf and task prebuilt:googlesql-unified in go-googlesql, then go run ./cmd/googlesql-prebuilts promote-to-mod if you produced archives locally under internal/ccall/.... Release tarballs or tagged lib/* modules already provide the default mod_platform layout.

Shell env (host): direnv with this repo’s .envrc (or source go-googlesql/scripts/go-googlesql-stack-bootstrap.sh yourself) so CGO_LDFLAGS_ALLOW, CGO_LDFLAGS, and CGO_CXXFLAGS match go-googlesql Taskfile.yml (sourcing go-googlesql/.envrc alone does not set linker allowlists). Then: go test -tags googlesql,googlesql_unified_prebuilt,googlesql_prebuilts_mod,googlesql_prebuilts_platform_pkg -p 1 -count=1 ./..., or use task test:prebuilt.

Deprecated alias: googlesql_tier_b is a compatibility name for older scripts; the supported default is googlesql_unified_prebuilt (see upstream README). Do not mix Abseil Tier B pilot tags with the default protobuf stack without reading prebuilt-absl-overlap.md.

Shared cache directory (GO_CACHE_ROOT): The Taskfile target task test:linux bind-mounts the same tree as go-googlesql: GO_CACHE_ROOT (default $HOME/.cache/go-googlesql) into gocache, gomodcache, and ccache inside the go-googlesql:dev container. Override GO_CACHE_ROOT if you need a different path; keep it identical across go-googlesql, go-googlesqlite, and bigquery-emulator so the stack shares one warm cache.

Host-native builds and tests: For incremental C++ compiles on the host, mirror go-googlesql: use CC="ccache clang" and CXX="ccache clang++", point GOCACHE, GOMODCACHE, and CCACHE_DIR at the same tree (for example under GO_CACHE_ROOT), or run task test:local from go-googlesql (prebuilts + googlesql,googlesql_unified_prebuilt). See link-only-cgo-migration.md. On Linux, install mold and keep it on PATH; the go-googlesql:dev image already sets mold for Docker-based task test:linux here.

Optional warm-up: After a cold toolchain or before a long test run, task -d ../go-googlesql docker:warm-cache pre-compiles the same -race graph as tests without executing them, so the next task test:linux in this repo is faster.

To match the go-googlesql:dev Docker cache used in go-googlesql, run task test:linux here after task -d ../go-googlesql docker:build-dev (same GO_CACHE_ROOT as go-googlesql).

CI: .github/workflows/go.yml checks out vantaboard/go-googlesql at the pinned go.mod version, runs scripts/ci-download-or-build-default-prebuilts.sh in that tree (release tarball on linux/amd64 when available, otherwise Bazel), then task build, task cover, and task lint with go-googlesql-stack-bootstrap.sh so CI uses the same default upstream tag stack and prebuilt archives as local development.

Synopsis

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

package main

import (
  "database/sql"
  "fmt"

  _ "github.com/vantaboard/go-googlesqlite"
)

func main() {
  db, err := sql.Open("googlesqlite", ":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

GoogleSQLite CLI

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

Status

A list of GoogleSQL ( Google Standard SQL ) specifications and features supported by go-googlesqlite.

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