nalgeon
commented
2 years ago Closed nalgeon closed 10 months ago
nalgeon
commented
2 years ago nalgeon
commented
2 years ago Cube root function.
Created by Anton Zhiyanov, MIT License.
sqlite> .load dist/cbrt
sqlite> select cbrt(27);
3.0Creates a pivot table from a regular one.
Created by jakethaw, MIT License.
Suppose we have a table with quarterly sales for years 2018-2021:
select * from sales;
ββββββββ¬ββββββββββ¬ββββββββββ
β year β quarter β revenue β
ββββββββΌββββββββββΌββββββββββ€
β 2018 β 1 β 12000 β
β 2018 β 2 β 39600 β
β 2018 β 3 β 24000 β
β 2018 β 4 β 18000 β
β 2019 β 1 β 26400 β
β 2019 β 2 β 32400 β
β ... β ... β ... β
β 2021 β 4 β 39000 β
ββββββββ΄ββββββββββ΄ββββββββββAnd we want to transform it into the 2D table with years as rows and columns as quarters:
ββββββββ¬ββββββββ¬ββββββββ¬ββββββββ¬ββββββββ
β year β Q1 β Q2 β Q3 β Q4 β
ββββββββΌββββββββΌββββββββΌββββββββΌββββββββ€
β 2018 β 12000 β 39600 β 24000 β 18000 β
β 2019 β 26400 β 32400 β 26400 β 26400 β
β 2020 β 15000 β 25200 β 29700 β 26400 β
β 2021 β 27000 β 61200 β 42000 β 39000 β
ββββββββ΄ββββββββ΄ββββββββ΄ββββββββ΄ββββββββThis looks like a job for pivotvtab!
First we create the 'rows' (years) table:
create table years as
select value as year from generate_series(2018, 2021);Then the 'columns' (quarters) table:
create table quarters as
select value as quarter, 'Q'||value as name from generate_series(1, 4);And finally the pivot table:
.load dist/pivotvtab
create virtual table sales_by_year using pivot_vtab (
-- rows
(select year from years),
-- columns
(select quarter, name from quarters),
-- data
(select revenue from sales where year = ?1 and quarter = ?2)
);VoilΓ :
select * from sales_by_year;
ββββββββ¬ββββββββ¬ββββββββ¬ββββββββ¬ββββββββ
β year β Q1 β Q2 β Q3 β Q4 β
ββββββββΌββββββββΌββββββββΌββββββββΌββββββββ€
β 2018 β 12000 β 39600 β 24000 β 18000 β
β 2019 β 26400 β 32400 β 26400 β 26400 β
β 2020 β 15000 β 25200 β 29700 β 26400 β
β 2021 β 27000 β 61200 β 42000 β 39000 β
ββββββββ΄ββββββββ΄ββββββββ΄ββββββββ΄ββββββββReturns Pearson correlation coefficient between two data sets.
Created by Alex Wilson, MIT License.
sqlite> .load dist/pearson
sqlite> create table data as select value as x, value*2 as y from generate_series(1, 8);
sqlite> select pearson(x, y) from data;
1.0Returns the value of the environment variable.
Created by John Howie, BSD-3-Clause License.
sqlite> .load dist/envfuncs
sqlite> select getenv('USER');
antonzCompares dates against cron patterns, whether they match or not.
Created by David Schramm , MIT License.
sqlite> .load dist/cron
sqlite> select cron_match('2006-01-02 15:04:05','4 15 * * *');
1Floating point numbers comparison and rounding.
Created by Keith Medcalf, Public Domain.
sqlite> select 0.1*3 = 0.3;
0
sqlite> .load dist/fcmp
sqlite> select feq(0.1*3, 0.3);
1Floating point numbers comparison:
flt(x[, y[, u]]) -> x less than y
fle(x[, y[, u]]) -> x less or equal y
feq(x[, y[, u]]) -> x equal y
fge(x[, y[, u]]) -> x greater or equal y
fgt(x[, y[, u]]) -> x greater than y
fne(x[, y[, u]]) -> x not equal yRounding:
roundho(x) -> Half to Odd
roundhe(x) -> Half to Even
roundhu(x) -> Half Up
roundhd(x) -> Half Down
roundha(x) -> Half Away from 0
roundht(x) -> Half Towards 0
money(x) -> Money (Half to Even, 4 digits)
rounddu(x) -> Directed Up
rounddd(x) -> Directed Down
roundda(x) -> Directed Away from 0
rounddt(x) -> Directed Towards 0Additional date and time functions:
Created by Harald Hanche-Olsen and Richard Hipp, Public Domain.
sqlite> .load dist/isodate
sqlite> select iso_weekday('2021-12-22');
3
sqlite> select iso_week('2021-12-22');
51
sqlite> select iso_year('2021-12-22');
2021
sqlite> select unixepoch('2021-12-22 12:34:45');
1640176485Even more math functions and bit arithmetics.
Created by Keith Medcalf, Public Domain.
sqlite> select round(m_e(), 3)
2.718Constants:
m_e() -> Euler's number (e)
m_log2e() -> log2(e)
m_log10e() -> log10(e)
m_ln2() -> ln(2)
m_ln10() -> ln(10)
m_pi() -> Pi number
m_pi_2() -> pi/2
m_pi_4() -> pi/4
m_1_pi() -> 1/pi
m_2_pi() -> 2/pi
m_2_sqrtpi() -> 2/sqrt(pi)
m_sqrt2() -> sqrt(2)
m_sqrt1_2() -> sqrt(0.5)
m_deg2rad() -> radians(1)
m_rad2deg() -> degrees(1)Bit arithmetics:
isset(value, bit, bit, bit ...)
-> true if all bits are set in value
isclr(value, bit, bit, bit ...)
-> true if all bits are clr in value
ismaskset(value, mask)
-> true if all set bits in mask set in value
ismaskclr(value, mask)
-> true if all set bits set in mask are clr in value
bitmask(bit, bit, bit ...)
-> value of bitmask with bits set
setbits(value, bit, bit, ...)
-> value with bits set
clrbits(value, bit, bit, ...)
-> value with bits cleared
bitmask(bit)
-> aggregate function, set bits and return resulting maskOther functions:
fabs(x)
-> abs value
ldexp(x, y)
-> x * 2^y
mantissa(x), exponent(x)
-> x = mantissa * 2^exponent
trunc(x), frac(x)
-> integer and fractional parts
fromhex(hex_str)
-> hexadecimal to decimalImplements ToBestType(x) function:
Created by Keith Medcalf, Public Domain.
sqlite> .load dist/besttype
sqlite> select tobesttype('42.13');
42.13Πstimates total record size.
Created by Keith Medcalf, Public Domain.
sqlite> .load dist/recsize
sqlite> select recsize(10);
3
sqlite> select recsize(10, 20, 30);
7Even more math statistics functions.
Created by Keith Medcalf, Public Domain.
sqlite> .load dist/stats2
sqlite> select sem(value) from generate_series(1, 99);
2.88675134594813Aggregate functions (also available as window aggregates):
avg(v)
aavg(v)
gavg(v)
rms(v)
stdev(v)
stdevp(v)
var(v)
varp(v)
sem(v)
ci(v)
skew(v)
skewp(v)
kurt(v)
kurtp(v)Weighted aggregate functions (also available as weighted window aggregates):
avg(v, w)
stdev(v, w)
stdevp(v, w)
var(v, w)
varp(v, w)
sem(v, w)
ci(v, w)Other aggregate window functions:
FirstNotNull(v)
LastNotNull(v)Other aggregate non-window functions:
range(v)
median(v)
covar(x, y)Compress / uncompress data using zlib. Doesn't work on Windows.
Created by D. Richard Hipp, Public Domain.
sqlite> .load dist/compress
sqlite> select hex(compress('hello world'));
8B789CCB48CDC9C95728CF2FCA4901001A0B045D
sqlite> select uncompress(compress('hello world'));
hello worldCompress / uncompress data with zlib using the SQL Archive approach:
Doesn't work on Windows.
Created by D. Richard Hipp, Public Domain.
sqlite> .load dist/sqlar
sqlite> select length(sqlar_compress(zeroblob(1024)));
17
sqlite> select sqlar_uncompress( sqlar_compress(zeroblob(1024)), 1024 ) = zeroblob(1024);
1Read and write zip files, both in memory and on disk. Doesn't work on Windows.
Created by D. Richard Hipp, Public Domain.
sqlite> .load dist/zipfile
sqlite> create virtual table temp.zip using zipfile('test.zip');
sqlite> insert into temp.zip(name, data) values('readme.txt', 'a glorious zip file');
sqlite> select name, data from zipfile('test.zip');
readme.txt|a glorious zip fileNatural string sorting and comparison.
Created by D. Richard Hipp, Public Domain.
sqlite> .load dist/uint
sqlite> select '2' < '10' collate uint;
1
sqlite> select '01' = '1' collate uint;
1Binary classifier via logistic regression.
Created by Alex Wilson, MIT License.
sqlite> .load dist/classifier
sqlite> select train(feature1, feature2, feature3, label) from data;
sqlite> select classify(1, 1, 0);
0.763584749816848
sqlite> select classify(0, 0, 1);
0.225364243341812Bloom filter β a fast index to tell if a value is probably in a table or certainly isn't.
Created by Shawn Wagner, MIT License.
sqlite> .load dist/bloom
sqlite> create virtual table plants using bloom_filter(20);
sqlite> insert into plants values ('apple'), ('asparagus'), ('cabbage'), ('grass');
sqlite> select count(*) from plants('apple');
1
sqlite> select count(*) from plants('lemon');
0Provides a mechanism to search a large vocabulary for close matches.
Created by D. Richard Hipp, Public Domain.
sqlite> .load dist/spellfix
sqlite> create virtual table dictionary using spellfix1;
sqlite> insert into dictionary(word)
values ('similarity'), ('search'), ('is'), ('awesome');
sqlite> select word from dictionary where word match 'awesoem';
awesomeAnd even more math statistics functions.
Created by Shawn Wagner, MIT License.
sqlite> .load dist/stats3
sqlite> select geo_mean(value) from generate_series(1, 99);
37.6231004740974corr(x, y)
-> correlation coefficient
covar_samp(x, y)
covar_pop(x, y)
-> sample and population covariance
geo_mean(v)
harm_mean(v)
median(v)
mode(v)
-> mean values
q1(v)
q3(v)
-> 1st and 3rd quartile values
iqr(v)
-> interquartile range
product(v)
-> product of valuesAll functions are also available as window aggregates.
nalgeon
commented
2 years ago Even more string functions:
repeat(string, count) - repeat the string count timesconcat(string, ...) - concatenate stringsconcat_ws(sep, string, ...) - concatenate strings using sep as a separatorAvailable in the main set since 0.20.0 π
nalgeon
commented
2 years ago One-dimensional arrays for SQLite.
Supports integers, real numbers and strings (with limited max size). Uses 1-based indexing. Stores itself as a blob value.
sqlite> .load dist/array
sqlite> create table data(arr blob);
sqlite> insert into data(arr) values (array(11, 12, 13));
sqlite> select array_length(arr) from data;
3
sqlite> select array_at(arr, 2) from data;
12
sqlite> select value from data, unnest(data.arr);
11
12
13Provides a lot of features you'd expect from arrays:
intarray()
-> Creates an empty 64-bit integer array.
realarray()
-> Creates an empty 64-bit real array.
textarray(width)
-> Creates an empty text array. Each string has a maximum size of `width` bytes.
Shorter strings are fine, but longer ones will be truncated to `width` bytes.
array(value, ...)
-> Creates an array filled with provided values.
Infers array type from the first value. If the value is a string,
sets max width to the maximum size among provided strings.
array_length(arr)
-> Returns array elements count.
array_at(arr, index)
-> Returns the array element at the specified index (1-based).
array_index(arr, value)
-> Returns the index of the specified value, or 0 if there is no such value.
array_contains(arr, value)
-> Returns 1 if the value if found in the array, 0 otherwise.
array_append(arr, value)
-> Appends the value to the end of the array.
array_insert(arr, index, value)
-> Inserts the value at the specified index,
shifting following elements to the right.
array_remove_at(arr, index)
-> Removes the element at the specified index,
shifting following elements to the left.
array_remove(arr, value)
-> Removes the value from the array. If there are multiple such values,
only removes the first one.
array_clear(arr)
-> Removes all elements from the array.
array_slice(arr, start[, end])
-> Returns a slice of the array from the `start` index inclusive
to the `end` index non-inclusive (or to the end of the array).
array_concat(arr, other)
-> Concatenates two arrays.
array_agg(expr)
-> Aggregates a set of values into the array (reverse operation for unnest()).
unnest(arr)
-> Expands the array to a set of values (reverse operation for array_agg()).
Implemented as a table-valued function.Shows information about all btrees (tables and indexes) in an SQLite database file:
Created by D. Richard Hipp, Public Domain.
sqlite> .load dist/btreeinfo
sqlite> create table data as select * from generate_series(1, 9999);
sqlite> select type, name, hasrowid, nentry, npage, depth from sqlite_btreeinfo;
βββββββββ¬ββββββββββββββββ¬βββββββββββ¬βββββββββ¬ββββββββ¬ββββββββ
β type β name β hasRowid β nEntry β nPage β depth β
βββββββββΌββββββββββββββββΌβββββββββββΌβββββββββΌββββββββΌββββββββ€
β table β sqlite_schema β 1 β 2 β 1 β 1 β
β table β data β 1 β 10010 β 22 β 2 β
βββββββββ΄ββββββββββββββββ΄βββββββββββ΄βββββββββ΄ββββββββ΄ββββββββNavigate hierarchic tables with parent/child relationships.
Created by D. Richard Hipp, Public Domain.
.load dist/closure
-- create a parent/child table
create table employees (
id integer primary key,
parent_id integer,
name varchar(50)
);
create index employees_parent_idx on employees(parent_id);
insert into employees
(id, parent_id, name)
values
(11, null, 'Diane'),
(12, 11, 'Bob'),
(21, 11, 'Emma'),
(22, 21, 'Grace'),
(23, 21, 'Henry'),
(24, 21, 'Irene'),
(25, 21, 'Frank'),
(31, 11, 'Cindy'),
(32, 31, 'Dave'),
(33, 31, 'Alice');Diane
β Bob
β Emma
β Grace
β Henry
β Irene
β Frank
β Cindy
β Dave
β Alice-- create a virtual hierarchy table
create virtual table hierarchy using transitive_closure(
tablename = "employees",
idcolumn = "id",
parentcolumn = "parent_id"
);
-- select hierarchy branch rooted at Cindy
select employees.id, name from employees, hierarchy
where employees.id = hierarchy.id and hierarchy.root = 31;
ββββββ¬ββββββββ
β id β name β
ββββββΌββββββββ€
β 31 β Cindy β
β 32 β Dave β
β 33 β Alice β
ββββββ΄ββββββββThis issue is for extension announcements only, so please don't comment on it. Happy to discuss any extension-related questions in separate issues.
nalgeon
commented
2 years ago Export database or table structure and contents into a single UTF-8 string.
Created by D. Richard Hipp, Public Domain.
dbdump([schema[, table]])
schema is the schema to be dumped. Default is "main" but can also be "temp" or any attached database.table is the table to be dumped. If table is omitted, then all tables are dumped..load dist/dbdump
create table employees (id integer primary key, name text);
insert into employees (name) values ('Diane'), ('Bob');
create table expenses (year integer, month integer, expense integer);
insert into expenses values (2020, 1, 82), (2020, 2, 75), (2020, 3, 104);
select dbdump('main', 'employees');PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE employees (id integer primary key, name text);
INSERT INTO employees VALUES(1,'Diane');
INSERT INTO employees VALUES(2,'Bob');
COMMIT;Arbitrary-precision decimal arithmetic on numbers stored as text strings. Because the numbers are stored to arbitrary precision and as text, no approximations are needed. Computations can be done exactly.
Created by D. Richard Hipp, Public Domain.
There are three math functions available:
decimal_add(A,B)decimal_sub(A,B)decimal_mul(A,B)These functions respectively add, subtract, and multiply their arguments and return a new text string that is the decimal representation of the result. There is no division operator at this time.
Use the decimal_cmp(A,B) to compare two decimal values. The result will be negative, zero, or positive if A is less than, equal to, or greater than B, respectively.
The decimal_sum(X) function is an aggregate, like the built-in sum() aggregate function, except that decimal_sum() computes its result to arbitrary precision and is therefore precise.
sqlite> .load dist/decimal
sqlite> select 0.1 + 0.2 = 0.3;
0
sqlite> select decimal_add(decimal('0.1'), decimal('0.2')) = decimal('0.3');
1Converts a floating-point number F between its binary64 representation and the MΓ2^E format (F = M Γ 2^E).
Created by D. Richard Hipp, Public Domain.
ieee754(F). Converts a floating-point number to the mantissa/exponent string representation.ieee754(M,E). Converts a mantissa/exponent combination to the floating-point number.ieee754_mantissa(F). Given a floating-point number, extracts the mantissa component.ieee754_exponent(F). Given a floating-point number, extracts the exponent component.ieee754_to_blob(F). Converts a floating-point number into an 8-byte blob that is the big-endian binary64 encoding of that number.ieee754_from_blob(B). Converts an 8-byte blob into the floating-point value that the binary64 encoding represents.sqlite> .load dist/ieee754
sqlite> select ieee754(45.25);
ieee754(181,-2)
sqlite> select ieee754_mantissa(45.25);
181
sqlite> select ieee754_exponent(45.25);
-2
sqlite> select ieee754(181,-2);
45.25Lists performance characteristics of the current SQLite instance.
Created by D. Richard Hipp, Public Domain.
sqlite> .load dist/memstat
sqlite> select * from sqlite_memstat;
ββββββββββββββββββββββββββ¬βββββββββ¬βββββββββ¬βββββββββ
β name β schema β value β hiwtr β
ββββββββββββββββββββββββββΌβββββββββΌβββββββββΌβββββββββ€
β MEMORY_USED β β 105424 β 109744 β
β MALLOC_SIZE β β β 48000 β
β MALLOC_COUNT β β 255 β 281 β
β PAGECACHE_USED β β 0 β 0 β
β PAGECACHE_OVERFLOW β β 9216 β 9216 β
β PAGECACHE_SIZE β β β 4360 β
β PARSER_STACK β β β 0 β
β DB_LOOKASIDE_USED β β 70 β 96 β
β DB_LOOKASIDE_HIT β β β 240 β
β DB_LOOKASIDE_MISS_SIZE β β β 1 β
β DB_LOOKASIDE_MISS_FULL β β β 0 β
β DB_CACHE_USED β β 9256 β β
β DB_SCHEMA_USED β β 1056 β β
β DB_STMT_USED β β 6416 β β
β DB_CACHE_HIT β β 5 β β
β DB_CACHE_MISS β β 0 β β
β DB_CACHE_WRITE β β 0 β β
β DB_DEFERRED_FKS β β 0 β β
ββββββββββββββββββββββββββ΄βββββββββ΄βββββββββ΄βββββββββThe value and hiwtr columns report the current value of the measure and its "high-water mark". The high-water mark is the highest value ever seen for the measurement, at least since the last reset.
Depending on which parameter is being interrogated, one of the value or hiwtr mark measurements might be undefined. For example, only the high-water mark is meaningful for MALLOC_SIZE, and only the current value is meaningful for DB_CACHE_USED. For rows where one or the other of value or hiwtr is not meaningful, that value is returned as NULL.
nalgeon
commented
2 years ago Generates all prefixes of the input string, including an empty string and the input string itself. The order of prefixes is from longest to shortest.
Created by D. Richard Hipp, Public Domain.
sqlite> .load dist/prefixes
sqlite> select * from prefixes('hello');
hello
hell
hel
he
h
Lists all prepared statements associated with the database connection.
Created by D. Richard Hipp, Public Domain.
sqlite> .load dist/stmt
sqlite> select * from sqlite_stmt;
sqlite> select sql, busy, run, mem from sqlite_stmt;
ββββββββββββββββββββββββββββββββββββββββββββββββ¬βββββββ¬ββββββ¬βββββββ
β sql β busy β run β mem β
ββββββββββββββββββββββββββββββββββββββββββββββββΌβββββββΌββββββΌβββββββ€
β select sql, busy, run, mem from sqlite_stmt; β 1 β 1 β 6416 β
ββββββββββββββββββββββββββββββββββββββββββββββββ΄βββββββ΄ββββββ΄βββββββUnions multiple similar tables into one.
Created by D. Richard Hipp, Public Domain.
There are two types of unions β unionvtab and swarmvtab virtual tables. The difference between them is that for unionvtab, all source tables must be located in the main database or in databases ATTACHed to the main database by the user. For swarmvtab, the tables may be located in any database file on disk. The swarmvtab implementation takes care of opening and closing database files automatically.
The source tables must have the following characteristics:
_rowid_.Documentation: unionvtab, swarmvtab.
.load dist/unionvtab
create table empl_london(id integer primary key, name text);
insert into empl_london(id, name)
values (11, 'Diane'), (12, 'Bob'), (13, 'Emma'), (14, 'Henry'), (15, 'Dave');
create table empl_berlin(id integer primary key, name text);
insert into empl_berlin(id, name)
values (21, 'Grace'), (22, 'Irene'), (23, 'Frank'), (24, 'Cindy'), (25, 'Alice');
create virtual table temp.employees using unionvtab("
values
('main', 'empl_london', 10, 19),
('main', 'empl_berlin', 20, 29)
");
select * from employees;ββββββ¬ββββββββ
β id β name β
ββββββΌββββββββ€
β 11 β Diane β
β 12 β Bob β
β 13 β Emma β
β 14 β Henry β
β 15 β Dave β
β 21 β Grace β
β 22 β Irene β
β 23 β Frank β
β 24 β Cindy β
β 25 β Alice β
ββββββ΄ββββββββMaps multidimensional data to the single dimension using z-ordering (Morton codes).
Created by D. Richard Hipp, Public Domain.
Z-ordering is a technique that allows you to map multidimensional data to a single dimension. For example, imagine that you have a collection of (X, Y) coordinate pairs laid out on a 2-dimensional plane. Using Z-ordering, you could arrange those 2D pairs on a 1-dimensional line.
Importantly, values that were close together in the 2D plane would still be close to each other on the line. That allows using a single database index for range search in 2D data. See AWS article for details.
This extension provides two functions:
zorder(x0, x1, ..., xN). Generate an N+1 dimension Morton code.unzorder(Z, N, I). Extract the I-th dimension from N-dimensional Morton code Z.sqlite> .load dist/zorder
sqlite> select zorder(2, 3);
14
sqlite> select zorder(4, 5);
50
sqlite> select zorder(3, 4) between zorder(2, 3) and zorder(4, 5);
1
sqlite> select zorder(2, 2) not between zorder(2, 3) and zorder(4, 5);
1sqlite> select unzorder(zorder(3, 4), 2, 0);
3
sqlite> select unzorder(zorder(3, 4), 2, 1);
4Interpolates missing values for timestamped measurements.
Created by Steinar Midtskogen, Public Domain.
.load dist/interpolate
create table measurements(timestamp integer primary key, value real);
insert into measurements(timestamp, value) values
(100, 20), (150, null), (200, 30), (300, 40);
create virtual table temp.interpolated using interpolate(measurements);sqlite> select value from interpolated where timestamp = 100;
20
sqlite> select value from interpolated where timestamp = 150;
25
sqlite> select value from interpolated where timestamp = 190;
29See interpolate.c and interpolate.sql for documentation and samples.
nalgeon
commented
1 year ago This is the 'native' SQLite JSON1 extension. It's often compiled into SQLite build, but in case your build doesn't include it - I've compiled it separately.
sqlite> .load dist/json1
sqlite> select json_object("answer", 42);This is documentation, not a real issue, so I'm closing it.
The incubator contains SQLite extensions which haven't yet made their way to the main set. They may be untested, poorly documented, too broad, too narrow, or without a well-thought API. Think of them as candidates for the standard library.