SQLean: all the missing SQLite functions

Related tags

Database sqlean
Overview

SQLean: 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.

sqlean brings them all together, neatly packaged by domain modules and built for Linux, Windows and macOS.

Here is what we've got right now:

  • crypto: secure hashes
  • json1: JSON functions
  • math: math functions
  • re: regular expressions
  • stats: math statistics
  • text: string functions
  • unicode: Unicode support
  • vsv: CSV files as virtual tables

Download

There are precompiled binaries for every OS:

  • *.dll - for Windows
  • *.so - for Linux
  • *.dylib - for macOS

Binaries are 64-bit and require a 64-bit SQLite version. If you are using SQLite shell on Windows (sqlite.exe), its 64-bit version is available at https://github.com/nalgeon/sqlite.

Usage

CLI usage:

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

IDE usage:

select load_extension('/path/to/extension/stats');
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("./stats.so")
connection.execute("select median(value) from generate_series(1, 100)")
connection.close()

You can specify any other supported extension instead of stats.

Issues
  • regexp_replace() returning incorrect pointer?

    regexp_replace() returning incorrect pointer?

    Hello, I am trying to use the re extension for RegEx (on Windows, compiling for 32-bit). Functions all work, except for the regexp_replace function. It returns correctly when it doesn't find anything to replace, but there is a strange effect when something is replaced:

    My Test case is select regexp_replace('The current year is AD 2025', '[0-9]+', '2050')

    The result varies on each call. For example, here are ten calls: mainThe current year is AD sqlThe current year is AD 2 The current year is AD 2050 The current year is AD 2050 sqlite_masterThe current ye tbl_name='AAA' AND type!='t mainThe current year is AD sqlite_masterThe current ye The current year is AD 2050 TEXTThe current year is AD

    It behaves as if the call I make to sqlite_column_text() returns a pointer that is a variable number of bytes smaller than the actual start of the result string. I thus read some other bit of memory before the actual value starts, and loss the end of the value (I copy a number of bytes returned by sqlite3_column_bytes()).

    Any ideas of what might be wrong?

    -- James Powell

    opened by drjdpowell 1
  • 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[] = {
          |                                    ^~~~~~~~~~~~~~~~~~~
    
    opened by ejurgensen 0
  • 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.
    • define: create scalar and table-valued functions from SQL.
    • envfuncs: read environment variables.
    • eval: run arbitrary SQL statements.
    • interpolate: interpolate missing values for timestamped measurements.
    • isodate: additional date and time functions.
    • 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.

    opened by nalgeon 38
  • 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

    opened by ngirard 2
Releases(0.15.2)
Owner
Anton Zhiyanov
Common sense driven development
Anton Zhiyanov
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

Oscar Franco 280 Jun 22, 2022
❤️ 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

Yevgeniy Zakharov 1.6k Jun 27, 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

mscdex 14 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

Code Hz 7 May 10, 2022
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

null 155 Jun 26, 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

GrapheneOS 5 Jun 8, 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

Backtrace Labs 17 Jun 27, 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

老雷 7 May 12, 2022
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

Backtrace Labs 264 Jun 24, 2022
C++ ORM for SQLite

Hiberlite ORM C++ object-relational mapping with API inspired by the awesome Boost.Serialization - that means almost no API to learn. Usage Just compi

Paul Korzhyk 625 Jun 24, 2022
The C++14 wrapper around sqlite library

sqlite modern cpp wrapper This library is a lightweight modern wrapper around sqlite C api . #include<iostream> #include <sqlite_modern_cpp.h> using n

null 683 Jun 17, 2022
Unofficial git mirror of SQLite sources (see link for build instructions)

SQLite Source Repository This repository contains the complete source code for the SQLite database engine. Some test scripts are also included. Howeve

null 2k Jul 3, 2022
A hook for Project Zomboid that intercepts files access for savegames and puts them in an SQLite DB instead.

ZomboidDB This project consists of a library and patcher that results in file calls for your savegame(s) being transparently intercepted and redirecte

Oliver 6 May 6, 2022
Lightweight C++ wrapper for SQLite

NLDatabase Lightweight C++ wrapper for SQLite. Requirements C++11 compiler SQLite 3 Usage Let's open a database file and read some rows: #include "NLD

Raven 0 Sep 20, 2019
Writing a sqlite clone from scratch in C++

如何用C++实现一个简易数据库 基于cstack/db_tutorial C语言版本 KCNyu 2022/2/2 作为笔者写的第一个系列型教程,还是选择基于前人的教程经验以及添加一些自己个人的探索。也许有很多纰漏之处,希望大家指正。 1. 数据库是什么? 数据库是“按照数据结构来组织、存储和管理数

shengyu.li 31 Jun 24, 2022
xxhash functions for PostgreSQL

pg_xxhash PostgreSQL ❤️ xxhash Tested with xxhash 0.8.1 and PostgreSQL 14.1 on Linux and macOS. Think twice before even considering to use it in any s

Igor Hatarist 5 Mar 11, 2022
YugabyteDB is a high-performance, cloud-native distributed SQL database that aims to support all PostgreSQL features

YugabyteDB is a high-performance, cloud-native distributed SQL database that aims to support all PostgreSQL features. It is best to fit for cloud-native OLTP (i.e. real-time, business-critical) applications that need absolute data correctness and require at least one of the following: scalability, high tolerance to failures, or globally-distributed deployments.

yugabyte 6.6k Jul 1, 2022
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

Anton Zhiyanov 1.3k Jul 3, 2022
✔️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

null 6.3k Jun 27, 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 What it is DB Browser for SQLite (DB4S) is a high quality, visual, open source tool to create, design, and edit database files c

null 16.7k Jun 27, 2022
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

Oscar Franco 280 Jun 22, 2022
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

Abdessamad Laamimi 10 Mar 24, 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

Bytedeco 3.9k Jun 30, 2022
📦 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

CPM.cmake 1.2k Jun 28, 2022
⛵ 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

Dmitry Baryshev 169 Jun 26, 2022
📦 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

CPM.cmake 1.2k Jun 27, 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

Paulo Rafael Ramalho 7 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.

Paulo Rafael Ramalho 0 Apr 5, 2022
❤️ 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

Yevgeniy Zakharov 1.6k Jun 27, 2022