The Incubator #27
Comments
evalDinamically runs arbitrary SQL statements. Merged into the main set 🎉 |
cbrtCube root function. Created by Anton Zhiyanov, MIT License. sqlite> .load dist/cbrt
sqlite> select cbrt(27);
3.0 |
pivotvtabCreates 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: This looks like a job for 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 │
└──────┴───────┴───────┴───────┴───────┘ |
pearsonReturns 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.0 |
envfuncsReturns the value of the environment variable. Created by John Howie, BSD-3-Clause License. sqlite> .load dist/envfuncs
sqlite> select getenv('USER');
antonz |
cronCompares 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 * * *');
1 |
fcmpFloating 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: Rounding: |
isodateAdditional 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');
1640176485 |
math2Even more math functions and bit arithmetics. Created by Keith Medcalf, Public Domain. sqlite> select round(m_e(), 3)
2.718Constants: Bit arithmetics: Other functions: |
besttypeImplements
Created by Keith Medcalf, Public Domain. sqlite> .load dist/besttype
sqlite> select tobesttype('42.13');
42.13 |
recsizeЕ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);
7 |
stats2Even 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): Weighted aggregate functions (also available as weighted window aggregates): Other aggregate window functions: Other aggregate non-window functions: |
compressCompress / 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 world |
sqlarCompress / 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);
1 |
zipfileRead 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 file |
uintNatural 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;
1 |
classifierBinary 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.225364243341812 |
bloomBloom 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');
0 |
spellfixProvides 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';
awesome |
stats3And 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.6231004740974All functions are also available as window aggregates. |
text2Even more string functions:
Available in the main set since 0.20.0 🎉 |
arrayOne-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:
|
defineCreate scalar and table-valued functions from SQL. Merged into the main set 🎉 |
btreeinfoShows 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 │
└───────┴───────────────┴──────────┴────────┴───────┴───────┘ |
closureNavigate 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');-- 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. |
dbdumpExport database or table structure and contents into a single UTF-8 string. Created by D. Richard Hipp, Public Domain.
.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; |
decimalArbitrary-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:
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 The 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');
1 |
ieee754Converts a floating-point number F between its binary64 representation and the M×2^E format ( Created by D. Richard Hipp, Public Domain.
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.25 |
memstatLists 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 Depending on which parameter is being interrogated, one of the |
prefixesGenerates 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
|
stmtLists 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 │
└──────────────────────────────────────────────┴──────┴─────┴──────┘ |
unionvtabUnions multiple similar tables into one. Created by D. Richard Hipp, Public Domain. There are two types of unions — The source tables must have the following characteristics:
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; |
zorderMaps 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:
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);
4 |
interpolateInterpolates 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. |
json1This is the 'native' SQLite JSON1 extension. |
|
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.
The text was updated successfully, but these errors were encountered: