- Notifications
You must be signed in to change notification settings - Fork4
A standard library for mattn/go-sqlite3 including best-effort date parsing, url parsing, math/string functions, and stats aggregation functions
License
multiprocessio/go-sqlite3-stdlib
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
As an alternative to compiling C extensions likeextension-functions.c andsqlean intomattn/go-sqlite3, this packageimplements many of these functions (and more from PostgreSQL) in Go.
These are in addition toall builtinfunctions provided bySQLite.
Continue reading for all functions, notes and examples.
This library is used inDataStation anddsq to simplify and powerdata analysis in SQL.
Read theDataStation blogpostto better understand the background.
package mainimport ("fmt""database/sql"_"github.com/mattn/go-sqlite3"stdlib"github.com/multiprocessio/go-sqlite3-stdlib")funcmain() {stdlib.Register("sqlite3_ext")db,err:=sql.Open("sqlite3_ext",":memory:")iferr!=nil {panic(err)}varsstringerr=db.QueryRow("SELECT repeat('x', 2)").Scan(&s)iferr!=nil {panic(err)}fmt.Println(s)}
Alternatively if you want to be able to add your own additionalextensions you can just use theConnectHook:
package mainimport ("database/sql""fmt"sqlite3"github.com/mattn/go-sqlite3"stdlib"github.com/multiprocessio/go-sqlite3-stdlib")funcmain() {sql.Register("sqlite3_ext",&sqlite3.SQLiteDriver{ConnectHook:stdlib.ConnectHook,})db,err:=sql.Open("sqlite3_ext",":memory:")iferr!=nil {panic(err)}varsstringerr=db.QueryRow("SELECT repeat('x', 2)").Scan(&s)iferr!=nil {panic(err)}fmt.Println(s)}
| Name(s) | Notes | Example |
|---|---|---|
| repeat, replicate | repeat('f', 5) = 'fffff' | |
| strpos, charindex | 0-indexed position of substring in string | strpos('abc', 'b') = 1 |
| reverse | reverse('abc') = 'cba' | |
| lpad | Omit the third argument to default to padding with spaces | lpad('22', 3, '0') = '022' |
| rpad | Omit the third argument to default to padding with spaces | rpad('22', 3, '0') = '220' |
| len | Shorthand forlength | len('my string') = '9' |
| split_part | Split string an take nth split piece | split('1,2,3', ',', 0) = '1',split('1,2,3', ',' -1) = '3' |
| regexp | Go's regexp package, not PCRE | x REGEXP '[a-z]+$',REGEXP('[a-z]+$', x) |
| regexp_count | Number of times the regexp matches in string | regexp_count('abc1', '[a-z]1') = '1' |
| regexp_split_part | Regexp equivalent ofsplit_part | regexp_split_part('ab12', '[a-z]1', 0) = 'a' |
Most of these are implemented as bindings togonum.
| Name(s) | Notes | Example |
|---|---|---|
| stddev, stdev, stddev_pop | stddev(n) | |
| mode | mode(n) | |
| median | median(n) | |
| percentile, perc | Discrete | perc(response_time, 95) |
| percentile_25, perc_25, percentile_50, perc_50, percentile_75, perc_75, percentile_90, perc_90, percentile_95, perc_95, percentile_99, perc_99 | Discrete | perc_99(response_time) |
| percentile_cont, perc_cont | Continuous | perc_cont(response_time, 95) |
| percentile_cont_25, perc_cont_25, percentile_cont_50, perc_cont_50, percentile_cont_75, perc_cont_75, percentile_cont_90, perc_cont_90, percentile_cont_95, perc_cont_95, percentile_cont_99, perc_cont_99 | Continuous | perc_cont_99(response_time) |
| Name(s) | Notes | Example |
|---|---|---|
| url_scheme | url_scheme('https://x.com:90/home.html') = 'https' | |
| url_host | url_host('https://x.com:90/home.html') = 'x.com:90' | |
| url_port | url_port('https://x.com:90/home.html') = '90' | |
| url_path | url_path('https://x.com/some/path.html?p=123') = '/some/path.html' | |
| url_param | url_param('https://x.com/home.html?p=123&z=%5B1%2C2%5D#section-1', 'z') = '[1,2]' | |
| url_fragment | url_fragment('https://x.com/home.html?p=123&z=%5B1%2C2%5D#section-1') = 'section-1' |
Best effort family of date parsing (usesdateparse) and date partretrieval. Results will differ depending on your computer's timezone.
| Name(s) | Notes | Example |
|---|---|---|
| date_year | date_year('2021-04-05') = 2021 | |
| date_month | January is 1, not 0 | date_month('May 6, 2021') = 5 |
| date_day | date_day('May 6, 2021') = 6 | |
| date_yearday | Day offset in year | date_yearday('May 6, 2021') = 127 |
| date_hour | 24-hour | date_hour('May 6, 2021 4:50 PM') = 16 |
| date_minute | date_minute('May 6, 2021 4:50') = 50 | |
| date_second | date_second('May 6, 2021 4:50:20') = 20 | |
| date_unix | date_unix('May 6, 2021 4:50:20') = 1588740620 | |
| date_rfc3339 | date_rfc3339('May 6, 2021 4:50:20') = 2020-05-06T04:50:20Z |
| Name(s) | Notes | Example |
|---|---|---|
| acos | acos(n) | |
| acosh | acosh(n) | |
| asin | asin(n) | |
| asinh | asinh(n) | |
| atan | atan(n) | |
| atanh | atanh(n) | |
| ceil, ceiling | ceil(n) | |
| cos | ceil(n) | |
| cosh | cosh(n) | |
| degrees | degrees(radians) | |
| exp | e^n | exp(n) |
| floor | floor(n) | |
| ln, log | log(x) | |
| log10 | log10(x) | |
| log2 | log2(x) | |
| mod | mod(num, denom) | |
| pi | pi() | |
| pow, power | pow(base, exp) | |
| radians | radians(degrees) | |
| sin | sin(n) | |
| sinh | sinh(n) | |
| sqrt | sqrt(n) | |
| tan | tan(n) | |
| tanh | tanh(n) | |
| trunc, truncate | Rounds up to zero if negative, down to zero if positive. | trunc(-10.9) = -10,trunc(10.4) = 10.0 |
| Name(s) | Notes | Example |
|---|---|---|
| base64 | Convert string to base64 | base64(s) |
| from_base64 | Convert string from base64 | from_base64(s) |
| base32 | Convert string to base32 | base32(s) |
| from_base32 | Convert string from base32 | from_base32(s) |
| md5 | Hex md5 sum of string | md5(s) |
| sha1 | Hex sha1 sum of string | sha1(s) |
| sha256 | Hex sha256 sum of string | sha256(s) |
| sha512 | Hex sha512 sum of string | sha512(s) |
| sha3_256 | Hex sha3_256 sum of string | sha3_256(s) |
| sha3_512 | Hex sha3_512 sum of string | sha3_512(s) |
| blake2b_256 | Hex blake2b_256 sum of string | blake2b_256(s) |
| blake2b_512 | Hex blake2b_512 sum of string | blake2b_512(s) |
There is 95% test coverage and automated tests on Windows, macOS andLinux.
Seedsq (a command-line toolfor executing SQL on data files) andDataStation, a GUIapplication for querying and building reports with data fromdatabases, servers, and files.
Join the#dev channel on the Multiprocess LabsDiscord.
If you have an idea for a new function, say so on the Discord channelor open an issue here.
Make sure the function doesn't already exist in dsq (or the sqlite3CLI).
This software is licensed under an Apache 2.0 license.
About
A standard library for mattn/go-sqlite3 including best-effort date parsing, url parsing, math/string functions, and stats aggregation functions
Topics
Resources
License
Uh oh!
There was an error while loading.Please reload this page.
Stars
Watchers
Forks
Releases
Packages0
Uh oh!
There was an error while loading.Please reload this page.