All the missing SQLite functions

SQLite Plus: all the missing SQLite functions

SQLite has very few functions compared to other DBMS. SQLite authors see this as a feature rather than a bug, because SQLite has extension mechanism in place.

There are a lot of SQLite extensions out there, but they are incomplete, inconsistent and scattered across the internet.

SQLite Plus brings them all togeher, neatly packaged by domain modules and built for Linux, Windows and macOS.

Here is what we got right now:

sqlite3-stats: statistics

Common statistical functions for SQLite. Adapted from extension-functions.c by Liam Healy.

Provides following functions:

  • mode - mode,
  • median - median (50th percentile),
  • percentile_25 - 25th percentile,
  • percentile_75 - 75th percentile,
  • percentile_90 - 90th percentile,
  • percentile_95 - 95th percentile,
  • percentile_99 - 99th percentile,
  • stddev or stddev_samp - sample standard deviation,
  • stddev_pop - population standard deviation,
  • variance or var_samp - sample variance,
  • var_pop - population variance.

CLI usage:

sqlite> .load ./sqlite3-stats.so;
sqlite> select median(value) from generate_series(1, 100);

In-app usage:

import sqlite3

connection = sqlite3.connect(":memory:")
connection.enable_load_extension(True)
connection.load_extension("./sqlite3-stats.so")
connection.execute("select median(value) from generate_series(1, 100)")
connection.close()

sqlite3-vsv: CSV files as virtual tables

Provides virtual table for working directly with CSV files, without importing data into the database. Useful for very large datasets.

Adapted from vsv.c by Keith Medcalf.

Usage:

create virtual table temp.vsv using vsv(...);
select * from vsv;

The parameters to the vsv module (the vsv(...) part) are as follows:

filename=STRING     the filename, passed to the Operating System
data=STRING         alternative data
schema=STRING       Alternate Schema to use
columns=N           columns parsed from the VSV file
header=BOOL         whether or not a header row is present
skip=N              number of leading data rows to skip
rsep=STRING         record separator
fsep=STRING         field separator
validatetext=BOOL   validate UTF-8 encoding of text fields
affinity=AFFINITY    affinity to apply to each returned value
nulls=BOOL          empty fields are returned as NULL

Defaults:

filename / data     nothing.  You must provide one or the other
                    it is an error to provide both or neither

schema              nothing.  If not provided then one will be
                    generated for you from the header, or if no
                    header is available then autogenerated using
                    field names manufactured as cX where X is the
                    column number

columns             nothing.  If not specified then the number of
                    columns is determined by counting the fields
                    in the first record of the VSV file (which
                    will be the header row if header is specified),
                    the number of columns is not parsed from the
                    schema even if one is provided

header=no           no header row in the VSV file
skip=0              do not skip any data rows in the VSV file
fsep=','            default field separator is a comma
rsep='\n'           default record separator is a newline
validatetext=no     do not validate text field encoding
affinity=none       do not apply affinity to each returned value
nulls=off           empty fields returned as zero-length

Parameter types:

  • STRING means a quoted string
  • N means a whole number not containing a sign
  • BOOL means something that evaluates as true or false. Case insensitive: yes, no, true, false, 1, 0. Defaults to true
  • AFFINITY means an SQLite3 type specification. Case insensitive: none, blob, text, integer, real, numeric
  • STRING means a quoted string. The quote character may be either a single quote or a double quote. Two quote characters in a row will be replaced with a single quote character. STRINGS do not need to be quoted if it is obvious where they begin and end (that is, they do not contain a comma). Leading and trailing spaces will be trimmed from unquoted strings.

The separator string containing exactly one character, or a valid escape sequence. Recognized escape sequences are:

\t horizontal tab, ascii character 9 (0x09)
\n linefeed, ascii character 10 (0x0a)
\v vertical tab, ascii character 11 (0x0b)
\f form feed, ascii character 12 (0x0c)
\xhh specific byte where hh is hexadecimal

The validatetext setting will cause the validity of the field encoding (not its contents) to be verified. It effects how fields that are supposed to contain text will be returned to the SQLite3 library in order to prevent invalid utf8 data from being stored or processed as if it were valid utf8 text.

The nulls option will cause fields that do not contain anything to return NULL rather than an empty result. Two separators side-by-each with no intervening characters at all will be returned as NULL if nulls is true and if nulls is false or the contents are explicity empty ("") then a 0 length blob (if affinity=blob) or 0 length text string.

For the affinity setting, the following processing is applied to each value returned by the VSV virtual table:

  • none no affinity is applied, all fields will be returned as text just like in the original csv module, embedded nulls will terminate the text. if validatetext is in effect then an error will be thrown if the field does not contain validly encoded text or contains embedded nulls
  • blob all fields will be returned as blobs validatetext has no effect
  • text all fields will be returned as text just like in the original csv module, embedded nulls will terminate the text. if validatetext is in effect then a blob will be returned if the field does not contain validly encoded text or the field contains embedded nulls
  • integer if the field data looks like an integer, (regex "^ _(+|-)?\d+ _$"), then an integer will be returned as provided by the compiler and platform runtime strtoll function otherwise the field will be processed as text as defined above
  • real if the field data looks like a number, (regex "^ (+|-)?(\d+.?\d|\d*.?\d+)(eE?\d+)? *$") then a double will be returned as provided by the compiler and platform runtime strtold function otherwise the field will be processed as text as defined above
  • numeric if the field looks like an integer (see integer above) that integer will be returned; if the field looks like a number (see real above) then the number will returned as an integer if it has no fractional part; otherwise a double will be returned
Owner
Anton Zhiyanov
Common sense driven development
Anton Zhiyanov
Comments
  • [array: unnest] crash & no solution

    [array: unnest] crash & no solution

    SELECT value 
    FROM unnest(array(0,1,2,3,4,5))
    WHERE value < 3;
    

    Parse error: no query solution (not sure what the behavior should be. I hoped for an other result)

    SELECT value
    FROM unnest (1);
    

    crashes SQLite without error

  • regexp_replace error

    regexp_replace error

    This query in SQLiteSpy and the equivalent in Notepad++ SELECT regexp_replace('abcde','[bd]','_') yields the result: a_c_e as I would expect.

    The extension when added to SQLite Expert Personal 64 gives a different result: a_cde

    Is the app wrong or the extension?

  • PostScript tokens out

    PostScript tokens out

    Here is a extension for making PostScript tokens (public domain):

    jar:http://www.dessus.com/files/sqlite3extensions.zip!/pstoken.c

    Note, that PostScript does not distinguish text/blobs; they are the same type in PostScript. However, this extension uses a different syntax for them.

  • sqlite3-unicode gcc warnings

    sqlite3-unicode gcc warnings

    If you add -Wall gcc complains about a few things, see below. Might be good to fix?

    $ gcc -Wall -fPIC -shared src/sqlite3-unicode.c -o dist/unicode.so
    src/sqlite3-unicode.c:22: warning: ignoring #pragma warning  [-Wunknown-pragmas]
       22 | #pragma warning(disable : 4305 13 90)
          | 
    src/sqlite3-unicode.c:31:32: warning: multi-line comment [-Wcomment]
       31 | #define SQLITE3_UNICODE_UNACC  // ~ 30KB increase \
          |                                ^
    src/sqlite3-unicode.c: In function ‘sqlite3StrNICmp’:
    src/sqlite3-unicode.c:5278:12: warning: operation on ‘ua’ may be undefined [-Wsequence-point]
     5278 |         ua = GlogUpperToLower(ua);
          |            ^
    src/sqlite3-unicode.c:5279:12: warning: operation on ‘ub’ may be undefined [-Wsequence-point]
     5279 |         ub = GlogUpperToLower(ub);
          |            ^
    src/sqlite3-unicode.c: In function ‘sqlite3StrNICmp16’:
    src/sqlite3-unicode.c:5292:12: warning: operation on ‘ua’ may be undefined [-Wsequence-point]
     5292 |         ua = GlogUpperToLower(ua);
          |            ^
    src/sqlite3-unicode.c:5293:12: warning: operation on ‘ub’ may be undefined [-Wsequence-point]
     5293 |         ub = GlogUpperToLower(ub);
          |            ^
    At top level:
    src/sqlite3-unicode.c:4917:33: warning: ‘likeInfoAlt’ defined but not used [-Wunused-const-variable=]
     4917 | static const struct compareInfo likeInfoAlt = {'%', '_', 0, 0};
          |                                 ^~~~~~~~~~~
    src/sqlite3-unicode.c:4911:33: warning: ‘globInfo’ defined but not used [-Wunused-const-variable=]
     4911 | static const struct compareInfo globInfo = {'*', '?', '[', 0};
          |                                 ^~~~~~~~
    src/sqlite3-unicode.c:4340:36: warning: ‘sqlite3UpperToLower’ defined but not used [-Wunused-const-variable=]
     4340 | SQLITE_PRIVATE const unsigned char sqlite3UpperToLower[] = {
          |                                    ^~~~~~~~~~~~~~~~~~~
    
  • The Incubator

    The Incubator

    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.

    See the full extension list below and vote for your favorites! We'll refactor and merge popular ones into the main set.

    • array: one-dimensional arrays.
    • besttype: convert string value to numeric.
    • bloom: a fast way to tell if a value is already in a table.
    • btreeinfo, memstat, recsize and stmt: various database introspection features.
    • cbrt and math2: additional math functions and bit arithmetics.
    • classifier: binary classifier via logistic regression.
    • closure: navigate hierarchic tables with parent/child relationships.
    • compress and sqlar: compress / uncompress data.
    • cron: match dates against cron patterns.
    • dbdump: export database as SQL.
    • decimal, fcmp and ieee754: decimal and floating-point arithmetic.
    • envfuncs: read environment variables.
    • interpolate: interpolate missing values for timestamped measurements.
    • isodate: additional date and time functions.
    • lines: read files line-by-line
    • path: parsing and querying paths
    • pearson: Pearson correlation coefficient between two data sets.
    • pivotvtab: pivot tables.
    • prefixes: generate string prefixes.
    • rotate: string obfuscation.
    • spellfix: search a large vocabulary for close matches.
    • stats2 and stats3: additional math statistics functions.
    • text2: additional string functions.
    • uint: natural string sorting and comparison.
    • unhex: reverse for hex().
    • unionvtab: union similar tables into one.
    • xmltojson: convert XML to JSON string.
    • zipfile: read and write zip files.
    • zorder: map multidimensional data to a single dimension.

    Meanwhile, you can download compiled incubator extensions as they are and use them if you like. Or add a new extension.

    P.S. This issue is for extension announcements only, so please don't comment on it. Happy to discuss any extension-related questions in separate issues.

  • Converting number between numeric base number systems

    Converting number between numeric base number systems

    It would be much useful for me to benefit from an equivalent function to the CONV() one in MySQL, which converts numbers from one numeric base number system to another.

    Hope you'll consider adding such function, and in any case, thank you very much for investing your time and efforts into this nice project ! Cheers

✔️The smallest header-only GUI library(4 KLOC) for all platforms
✔️The smallest header-only GUI library(4 KLOC) for all platforms

Welcome to GUI-lite The smallest header-only GUI library (4 KLOC) for all platforms. 中文 Lightweight ✂️ Small: 4,000+ lines of C++ code, zero dependenc

Dec 4, 2022
DB Browser for SQLite (DB4S) is a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite.
DB Browser for SQLite (DB4S) is a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite.

DB Browser for SQLite What it is DB Browser for SQLite (DB4S) is a high quality, visual, open source tool to create, design, and edit database files c

Dec 2, 2022
React-native-quick-sqlite - ⚡️ The fastest SQLite implementation for react-native.
React-native-quick-sqlite - ⚡️ The fastest SQLite implementation for react-native.

React Native Quick SQLite The **fastest** SQLite implementation for react-native. Copy typeORM patch-package from example dir npm i react-nati

Nov 29, 2022
An open source standard C library that includes useful functions && (Reimplementation of libc functions + own functions).
An open source standard C library that includes useful functions && (Reimplementation of libc functions + own functions).

?? LIBFT-42 : Artistic view of LIBC: ?? HOW DOES IT FEEL HAVING YOUR OWN LIB: SUBJECT : ENGLISH PDF ℹ️ What is LIBFT : This project aims to code a C l

Nov 4, 2022
The missing bridge between Java and native C++

JavaCPP Commercial support: Introduction JavaCPP provides efficient access to native C++ inside Java, not unlike the way some C/C++ compilers interact

Nov 26, 2022
📦 CMake's missing package manager. A small CMake script for setup-free, cross-platform, reproducible dependency management.
📦 CMake's missing package manager. A small CMake script for setup-free, cross-platform, reproducible dependency management.

Setup-free CMake dependency management CPM.cmake is a CMake script that adds dependency management capabilities to CMake. It's built as a thin wrapper

Dec 5, 2022
⛵ The missing small and fast image decoding library for humans (not for machines).
⛵ The missing small and fast image decoding library for humans (not for machines).

Squirrel Abstract Image Library The missing fast and easy-to-use image decoding library for humans (not for machines). Target Audience • Features • Im

Nov 23, 2022
📦 CMake's missing package manager. A small CMake script for setup-free, cross-platform, reproducible dependency management.
📦 CMake's missing package manager. A small CMake script for setup-free, cross-platform, reproducible dependency management.

Setup-free CMake dependency management CPM.cmake is a CMake script that adds dependency management capabilities to CMake. It's built as a thin wrapper

Dec 5, 2022
Libft is an individual project at 42 that requires us to re-create some standard C library functions including some additional ones that can be used later to build a library of useful functions for the rest of the program.

?? Index What is Libft? List of Functions Technologies ✨ What is Libft? Libft is an individual project at 42 that requires us to re-create some standa

Jan 17, 2022
Libft is an individual project at 42 that requires us to re-create some standard C library functions including some additional ones that can be used later to build a library of useful functions for the rest of the program.
Libft is an individual project at 42 that requires us to re-create some standard C library functions including some additional ones that can be used later to build a library of useful functions for the rest of the program.

Libft is an individual project at 42 that requires us to re-create some standard C library functions including some additional ones that can be used later to build a library of useful functions for the rest of the program.

Apr 5, 2022
❤️ SQLite ORM light header only library for modern C++
❤️ SQLite ORM light header only library for modern C++

SQLite ORM SQLite ORM light header only library for modern C++ Status Branch Travis Appveyor master dev Advantages No raw string queries Intuitive syn

Nov 29, 2022
Query C++ codebases using SQLite

ClangQL: query C++ codebases using SQLite and clangd What is it? ClangQL is a proof-of-concept SQLite extension for querying C++ codebases that have b

Nov 18, 2022
An SQLite binding for node.js with built-in encryption, focused on simplicity and (async) performance

Description An SQLite (more accurately SQLite3MultipleCiphers) binding for node.js focused on simplicity and (async) performance. When dealing with en

May 15, 2022
Yet another SQLite wrapper for Nim

Yet another SQLite wrapper for Nim Features: Design for ARC/ORC, you don’t need to close the connection manually Use importdb macro to create helper f

Jul 4, 2022
Armazena a tabela nutricional dos alimentos em um banco de dados (SQLITE), salva as quantidades em um arquivo EXCEL, informando se a meta diária foi batida.

QT-Controle-de-Dieta Armazena a tabela nutricional dos alimentos em um banco de dados (SQLITE), salva as quantidades em um arquivo EXCEL, informando s

Oct 26, 2021
A friendly and lightweight C++ database library for MySQL, PostgreSQL, SQLite and ODBC.

QTL QTL is a C ++ library for accessing SQL databases and currently supports MySQL, SQLite, PostgreSQL and ODBC. QTL is a lightweight library that con

Dec 2, 2022
Fork of sqlite4java with updated SQLite and very basic compiler hardening enabled.

Download latest version: sqlite4java-392 with SQLite 3.8.7, Windows/Linux/Mac OS X/Android binaries OSGi bundle 1.0.392 with sqlite4java-392 Files for

Oct 26, 2022
An updated fork of sqlite_protobuf, a SQLite extension for extracting values from serialized Protobuf messages.

This fork of sqlite_protobuf fixes some issues (e.g., #15) and removes the test suite that we do not use. It also comes with proto_table, a C library

Oct 19, 2022
Serverless SQLite database read from and write to Object Storage Service, run on FaaS platform.

serverless-sqlite Serverless SQLite database read from and write to Object Storage Service, run on FaaS platform. NOTES: This repository is still in t

May 12, 2022
Verneuil is a VFS extension for SQLite that asynchronously replicates databases to S3-compatible blob stores.
Verneuil is a VFS extension for SQLite that asynchronously replicates databases to S3-compatible blob stores.

Verneuil: streaming replication for sqlite Verneuil1 [vɛʁnœj] is a VFS (OS abstraction layer) for sqlite that accesses local database files like the d

Nov 27, 2022