tursodatabase / libsql Goto Github PK

It would be more ergonomic for many developers, me included, to have a test suite based on the Rust toolchain. We can start with a basic set of tests which just bind sqlite3.h sqlite3.c into Rust, and start the habit of implementing new tests in Rust.

Described by @xfbs in #8 (comment)

The idea is to allow people to code more complex check expressions in any language compilable to WebAssembly and have an interface which makes it easy to register such checks. The code sample proposed in discussion above was:

#[libsql::constraint]
fn check_is_ipv4(data: &[u8]) -> Result<(), ConstraintError> {
    Ipv4Addr::try_from(&data)?;
    Ok(())
}

and it's a great example of why this can be useful - users can rely on standard library implementations of things like IP validation and get a working CHECK expression.

This is an umbrella issue, because it has a few prerequisites, some shared with #1, in arbitrary order:

• Define a Wasm<->libSQL ABI, which specifies how libSQL types should be expressed in WebAssembly functions:
  #16
• Create helper libraries for handling this type translation in a few popular languages - with Rust leading the way:
  #15
• Add a user-defined helper function (registered with sqlite3_create_function) which runs a WebAssembly function - e.g. it could take the WebAssembly module body as a blob in the first parameter, then the function name, and then forward the rest of the parameters to the WebAssembly model as is:
  #17
• Add syntactic sugar for calling a Wasm function without using the runner above - e.g. creating a system table for storing compiled functions indexed by their names, adding a CREATE FUNCTION SQL command for compiling and registering it, and modifying the path for looking up user-defined functions to take a peek into ones created with CREATE FUNCTION:
  #18

https://github.com/V-Sekai/godot-mvsqlite/actions/runs/4049533440/jobs/6966010565

It's hard to debug this with warnings as errors.

Collapsed log

sqlite3.c
modules\mvsqlite\thirdparty\libsql\sqlite3.c(13599): error C2059: syntax error: ';'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(13601): error C2059: syntax error: '}'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64325): error C2037: left of 'nWiData' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64328): error C2037: left of 'apWiData' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64328): error C2198: 'sqlite3Realloc': too few arguments for call
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64333): error C2037: left of 'nWiData' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64334): error C2037: left of 'nWiData' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64333): error C2168: 'memset': too few actual parameters for intrinsic function
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64335): error C2037: left of 'apWiData' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64336): error C2037: left of 'nWiData' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64341): error C2037: left of 'exclusiveMode' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64342): error C2037: left of 'apWiData' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64343): error C2037: left of 'apWiData' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64345): error C2037: left of 'pDbFd' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64346): error C2037: left of 'writeLock' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64346): error C2037: left of 'apWiData' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64345): error C2198: 'sqlite3OsShmMap': too few arguments for call
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64355): error C2037: left of 'readOnly' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64362): error C2037: left of 'apWiData' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64371): error C2037: left of 'nWiData' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64371): error C2037: left of 'apWiData' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64382): error C2037: left of 'apWiData' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64390): error C2037: left of 'apWiData' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64458): error C2037: left of 'exclusiveMode' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64459): error C2037: left of 'pDbFd' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64459): error C2198: 'sqlite3OsShmBarrier': too few arguments for call
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64486): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64487): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64488): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64488): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64488): error C2198: 'walChecksumBytes': too few arguments for call
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64490): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64490): error C2168: 'memcpy': too few actual parameters for intrinsic function
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64492): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64492): error C2168: 'memcpy': too few actual parameters for intrinsic function
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64516): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64520): error C2037: left of 'iReCksum' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64521): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64521): error C2168: 'memcpy': too few actual parameters for intrinsic function
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64523): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64525): error C2037: left of 'szPage' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64525): error C2198: 'walChecksumBytes': too few arguments for call
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64547): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64554): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64554): error C2168: 'memcmp': too few actual parameters for intrinsic function
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64570): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64572): error C2037: left of 'szPage' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64572): error C2198: 'walChecksumBytes': too few arguments for call
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64620): error C2037: left of 'exclusiveMode' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64621): error C2037: left of 'pDbFd' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64621): error C2198: 'sqlite3OsShmLock': too few arguments for call
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64629): error C2037: left of 'exclusiveMode' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64630): error C2037: left of 'pDbFd' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64630): error C2198: 'sqlite3OsShmLock': too few arguments for call
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64636): error C2037: left of 'exclusiveMode' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64637): error C2037: left of 'pDbFd' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64637): error C2198: 'sqlite3OsShmLock': too few arguments for call
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64645): error C2037: left of 'exclusiveMode' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64646): error C2037: left of 'pDbFd' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64646): error C2198: 'sqlite3OsShmLock': too few arguments for call
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64740): error C2037: left of 'apWiData' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64742): error C2037: left of 'apWiData' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64768): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64776): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64776): error C2198: 'walFramePage': too few arguments for call
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64782): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64924): error C2037: left of 'ckptLock' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64932): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64932): error C2168: 'memset': too few actual parameters for intrinsic function
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64934): error C2037: left of 'pWalFd' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64934): error C2198: 'sqlite3OsFileSize': too few arguments for call
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64953): error C2037: left of 'pWalFd' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64953): error C2198: 'sqlite3OsRead': too few arguments for call
scons: *** [modules\mvsqlite\thirdparty\libsql\sqlite3.windows.template_debug.x86_64.obj] Error 2
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64972): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64973): error C2037: left of 'szPage' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64974): error C2037: left of 'nCkpt' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64975): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64975): error C2168: 'memcpy': too few actual parameters for intrinsic function
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64978): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64979): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64978): error C2198: 'walChecksumBytes': too few arguments for call
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64981): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(64982): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(65016): error C2037: left of 'apWiData' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(65024): error C2037: left of 'pWalFd' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(65024): error C2198: 'sqlite3OsRead': too few arguments for call
modules\mvsqlite\thirdparty\libsql\sqlite3.c(65033): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(65034): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(65035): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(65038): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(65039): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(65042): error C2037: left of 'apWiData' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(65083): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(65084): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(65093): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(65098): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(65099): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(65114): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(65117): error C2037: left of 'hdr' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(65117): error C2037: left of 'zWalName' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(65132): error C2037: left of 'exclusiveMode' specifies undefined struct/union 'libsql_wal'
modules\mvsqlite\thirdparty\libsql\sqlite3.c(65132): fatal error C1003: error count exceeds 100; stopping compilation
scons: building terminated because of errors.

Following an interesting thread: https://sqlite.org/forum/forumpost/abf074d0d5ef87e1, we should consider extending the API in order to allow extension/user-defined function developers to provide destructors for their modules. Such destructors would automatically get called once the database connection to which they're bound gets closed.

SQLite allows creating application-defined SQL functions by implementing and registering them in C: https://www.sqlite.org/appfunc.html

With WebAssembly emerging as the default, secure, and fast way of implementing user-defined functions, we should consider making this interface as Wasm-friendly as possible.

The first iteration could simply extend the existing C API with helper functions, which facilitate creating new functions in Wasm.

Later, we should consider extending SQLite's dialect with CREATE FUNCTION, widely known in the SQL world. This is not strictly necessary, because all kinds of functions can be registered directly via the C API, but it might be much more developer-friendly to provide a way of creating functions from SQL level, without having to recompile the database. Also, not everyone is fluent in C.

Important note 1: runtime

WebAssembly programs need to be run on a virtual machine. Candidates (feel free to post more suggestions):

1. Wasmtime - my personal runtime of choice - I already have experience in using it, it's straightforward, fast, and quite feature-complete.

• Wasmtime comes with C/C++ bindings in the form of libwasmtime.a library, which is convenient for binding with C; using it is a good candidate for a proof-of-concept implementation, because it's just a single header and a library you link with.
• ... but in the long term we should consider implementing everything in Rust, Wasmtime's native language, and only then bind it to the existing C codebase; The original code has features that C bindings lack - the most important one being async support, but also some configuration options and tuning.
  Sample code using libwasmtime.a to register a hardcoded wasm function into sqlite: https://gist.github.com/psarna/93cdc1c2b4de36c98db8af5bc77b1351

2. Wasmer - haven't tried it yet, but it has gained considerable traction and is reportedly faster than Wasmtime. I'm pretty sure it lacks async support, but maybe we don't need it for the time being.

Important note 2: ABI

WebAssembly programs are usually not written by hand, but instead coded in other languages, like Rust, C++, AssemblyScript, etc. WebAssembly standard specifies a very concise list of supported types, so in order to be able to run Wasm-based user-defined functions in sqlite3, we need to be able to translate between sqlite's types and WebAssembly. The most dev-friendly way of doing so is by defining an ABI (e.g. here's one for C: https://github.com/WebAssembly/tool-conventions/blob/main/BasicCABI.md). We could, for instance, decide that each sqlite type should be passed in its serialized form (sqlite3_value). Then, people coding functions in WebAssembly should either deserialize and serialize by hand, or by using our helper library which automatically takes care of type translation. Customarily, this library for Rust language can be called bindgen, and for lack of better example, here's one from the crypto world: https://docs.near.org/sdk/rust/contract-structure/near-bindgen . Since sqlite types are very well defined, we could create a mini-SDK for implementing Wasm user-defined functions in Rust, by exposing a #[libsql_bindgen] macro that automatically sprinkles vanilla Rust code with type serialization/deserialization.

Given that a fair part of sqlite userbase is Web developers, we should also consider providing a mini SDK with helper functions for AssemblyScript - a language designed specifically for compiling it into WebAssembly and integrating the resulting code with TypeScript/javascript.

Requires #3. Once the interface is ready for asynchronous I/O, the next natural step is to provide a VFS implementation based on io_uring, probably in poll mode to be in line with the existing sqlite3 semantics of returning SQLITE_BUSY if the statement is not ready to proceed just yet.

There are potentially interesting use cases for offloading write-ahead log to a different subsystem, e.g. keeping it in external storage, while keeping the original B-Tree and pager implementation intact.

Since WAL API is rather short and self-contained: https://github.com/libsql/libsql/blob/63868b1dbb8ece630d0c6a6deccb10b2ba58d1ee/src/wal.h#L29-L47

, perhaps it could simply be abstracted the way vfs is currently abstracted in the code base - with a virtual functions table.

libSQL already diverged in the extension management - the extension API structure contains additional fields related to virtual WAL, not present in SQLite. Because of that, compiling an extension with libSQL header and running it on SQLite, or vice versa, is not defined behavior. We should document that we highly recommend (and actually just require) dynamically loaded extensions to be recompiled with a libSQL header if they are to be used with libSQL.

https://github.com/nalgeon/sqlean is a great set of extensions for SQLite. We should consider if/how to integrate with it - e.g. by adding it as a submodule and hooking it into the build system, so that it's easy to opt-in for the features provided by sqlean.

Table-valued functions are described here: https://www.sqlite.org/vtab.html#tabfunc2

Now that we support dynamic user-defined functions in WebAssembly, we should consider going a step further and allowing users to dynamically create table-valued functions, using (and maybe extending) the existing CREATE VIRTUAL TABLE interface. The rough idea is to simply have a functor that keeps its state in a virtual table, and uses a user-defined function to generate data.

Tribute to @fire for coming up with the idea and patiently explaining it to me multiple times until I grasped it 😇

One of the annoying differences between SQLite and PostgreSQL is that the latter supports positional parameters for prepared statements with the $<number> syntax. Let's add opt-in support to libSQL to support this syntax to make it easier to write SQL that's portable across SQLite and PostgreSQL.

We already have the libSQL manifest at https://libsql.org, no need to repeat it in README.md.

Let's improve the README as follows:

• Brief explanation of the project, link to manifest.
• Explicitly list out features that are unique to libSQL (link to doc/libsql_extensions.md)
• Build & test instructions
• Link to community Discord.

Since libsql is a sqlite derivative, I am trying to explore ways of embedding @losfair's amazing work with libsql.

https://github.com/losfair/mvsqlite

Due to the fact that our WebAssembly runtime is implemented in Rust, it's also not part of the customary sqlite3.c amalgamation file. Instead, users are expected to either:

• integrate directly with our Rust bindings crate, if libSQL is integrated into a Rust project, or
• statically link with a .libs/libwblibsql.a library, either compiled from source or precompiled, or
• link dynamically by using the dynamic flavor of the library, or building from source with ./configure --enable-wasm-runtime

These steps might be obvious enough for libSQL maintainers, but aren't for users, so they need to be clearly documented and easily available.

Refs #116

LibSQL would like to remain compatible as long as possible with sqlite. However, PRs such as #87 tend to break compatibility. The feature it offers is great though. This is why i suggest having an "extra" branch where releases containing incompatible changes are merged so that people get to enjoy a power version of sqlite.

SQL Server, for example, has a neat feature, which is useful for data protection:

https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver16

What you can do is attach a "masking policy" to a column in a table. For example, email addresses (that are personal information) could be masked by having a MASKED WITH augmentation on a SQL table:

CREATE TABLE users(
    email TEXT MASKED WITH (FUNCTION = 'email()') NOT NULL,
);

Queries to the table would mask out the email address with an anonymized "[email protected]" version, for example. However, users could still see their own email addresses with another SQL extension that SQL Server has:

EXECUTE AS USER = 'penberg';  

Dynamic data masking is particularly useful in scenarios where database is replicated and you want the extra layer of protection while still serving queries. Please note that there's bound to be some overlap with LumoSQL security features here.

SQLite is a RDBMS.

To me, the name libSQL suggests a lib (library). Is libSQL a library or a RDBMS?

IMHO, I think this should be mentioned in the msin description or in a FAQ.

To me, a name create certain expectations. SQLite suggests something lite. If you saw the name libSQL for the first time, what would think of?

Thank you.

Suggested in #41 and our Discord channel.

We should consider allowing to just alter existing columns. While it's possible in SQLite: https://stackoverflow.com/a/31140916/11876830, it's non-idiomatic and feels hacky. Implementing a full-fledged ALTER TABLE statement could make libSQL more dev friendly.

Happy new year! And congratulations on the successful incorporation of Wasm UDFs into the libsql project! As we had discussed in the PR, we would like to explore adding WasmEdge as an option for UDFs in libsql. The rationales are as follows.

• WasmEdge is one of the smallest and fastest Wasm runtimes.
• WasmEdge supports rich APIs in the Wasm runtime beyond the WASI spec, such as async sockets. That enables more complex UDFs.
• Docker starts to bundle and ship WasmEdge in every copy of Docker Desktop from 4.15. That allows over 10M Docker developers to create and test Wasm functions using WasmEdge APIs.
• Fedora and Red Hat EPEL ship with WasmEdge. They could further reduce the size of libsql’s install package.

WasmEdge supports a comprehensive Rust SDK and C SDK to make integration easier. I think we could provide a compile time or runtime flag to switch between supported Wasm runtimes. Please let me know what you think! Thanks.

There is another lib with the exact same name.
https://github.com/cooldogedev/libSQL

I noticed that when I read an article about libSQL and I couldn't find the link to it, so I searched for "github libSQL" and the first result was the other lib.

I was wondering how people will find your library if there is another one with the exact same name and spelling.

Thank you.

sqlite has no access restrictions, it is assumed that anyone with access to the OS, has access to the data. In a distributed system, we might want to add some security features, like authentication, authorization. RBAC etc.

We intend to add Rust to implement new capabilities, but not exclusively so.

rustup update stable-gnu
rustup set default-host x86_64-pc-windows-gnu
scoop install mingw
scoop install llvm
scoop install protobuf
cargo build  2> error.log

error.log

SQLite is public domain, both as source and as documentation.

I'm not a lawyer, but as far as I know, the Apache license is more restrictive than the public domain one, and it applies to the software, not also to the documentation.

I was wondering if you're willing to make libSQL public domain or at least MIT from the start. From what I understand, MIT license also applies to the documentation, and it's easier to understand, in my (and some others') opinion than Apache license.

Thank you.

will have insert with natural sort ?

I just spent 20 minutes trying to build libsql on Debian with --enable-readline, trying to figure out why I couldn't get readline to work. It wasn't until I looked at the Makefile I realized I had no readline-compatible library installed.

I think that when --enable-readline is explicitly given and libedit or readline is not installed, the configure program should return a non-zero exit code

We already have a rudimentary set of Rust tests under https://github.com/libsql/libsql/tree/main/test/rust_suite, but that only covers the new features. Ideally, the Rust test suite should increase the test coverage of the existing set of open source tests inherited from SQLite.

Contributions very much welcome, every new test case helps!

Here's an example of a simple test case: https://github.com/libsql/libsql/blob/bd68c17adf23c038fcfc44813ea2a943b423ca63/test/rust_suite/src/lib.rs#L19-L56

Right now the WebAssembly runtime is initialized with default settings only:
https://github.com/libsql/libsql/blob/bd68c17adf23c038fcfc44813ea2a943b423ca63/src/rust/wasmtime-bindings/src/lib.rs#L80-L90

It would be nice to have a way of modifying the defaults, e.g. the WebAssembly stack size, various time limits, profiling, preemptions, etc. Here's how Wasmtime can be configured: https://docs.wasmtime.dev/api/wasmtime/struct.Config.html

libsql_wasm_engine_new function does not take parameters now, but we could extend it with a configuration struct, that can be used to pass custom options, e.g. passed as preprocessor macros or a custom PRAGMA statement.

We want to add support for stored procedures.

There is a thread about this feature on the SQLite forum: https://sqlite.org/forum/info/78a60bdeec7c1ee9, many use-cases are described there, and this is also something that meta added to their custom extension of SQLite for messenger: https://engineering.fb.com/2020/03/02/data-infrastructure/messenger/

We want to follow the syntax described in https://www.w3schools.com/sql/sql_stored_procedures.asp

Unresolved questions:

• How to handle procedures that depend on a table when that table is deleted
• How should procedures interact with UDF? i.e should we allow procs and UDF to conflict? This should be ok if procs are invoked with EXEC as seems to be the standard syntax in SQL.
• How should procedures be stored? As bytecode, plain SQL statement? Serialized AST?
• How should params be handled?

Not a developer but shouldn't "make distclean" remove all artifacts created by ./configure && make ?

• See following steps

./configure && make
make distclean

[me@ipa01 libsql]$ git status
On branch main
Your branch is up to date with 'origin/main'.

Untracked files:
  (use "git add <file>..." to include in what will be committed)
        sqlite3session.h

nothing added to commit but untracked files present (use "git add" to track)
[me@ipa01 libsql]$

• One-liner fix to enable distclean to delete sqlite3session.h

[me@ipa01 libsql]$ git diff Makefile.in
diff --git a/Makefile.in b/Makefile.in
index cc4943f2c..617005561 100644
--- a/Makefile.in
+++ b/Makefile.in
@@ -1501,7 +1501,7 @@ clean:
        rm -f threadtest5

 distclean:     clean
-       rm -f config.h config.log config.status libtool Makefile sqlite3.pc \
+       rm -f config.h config.log config.status libtool Makefile sqlite3.pc sqlite3session.h \
                $(TESTPROGS)

 #
[me@ipa01 libsql]$

SQLite already exposes an interface quite friendly to asynchronous I/O, because sqlite3_step function used to proceed with sqlite3_stmt (https://www.sqlite.org/c3ref/stmt.html) state machine is capable of returning SQLITE_BUSY or other error types if it decides that an operation cannot proceed immediately. sqlite3_stmt is stateful, so we're free to keep whatever state we need, assuming that users are going to keep asking for the new step until they get an unrecoverable error or SQLITE_DONE.

Thus, it should be quite possible to modify sqlite3_stmt so that it works with asynchronous I/O, e.g. backed by io_uring.

The new flow could be based on a new implementation of vfs (https://www.sqlite.org/vfs.html), backed by io_uring or libaio for Linux, which returns SQLITE_BUSY when an operation is queued, but not yet finished. Imagine transforming the following high level flow:

1. The user needs to select rows from the database
2. sqlite3_step performs a (potentially) blocking read from the filesystem
3. sqlite3_step returns the results

Into the following flow:

1. The user needs to select rows from the database
2. sqlite3_step issues an async read, and it's not immediately ready, so:
   • the handle for this async operation is saved in sqlite3_stmt state
   • SQLITE_BUSY is returned
3. sqlite3_step is called again as a natural consequence of seeing SQLITE_BUSY (we need to triple-check it's indeed what drivers/apps do)
   • if the operation is still in progress, SQLITE_BUSY is returned
   • otherwise, sqlite3_stmt can move to the next state
4. sqlite3_step returns the results

With that design, sqlite3_step becomes capable of effectively polling the async i/o backend, e.g. io_uring. This might be beneficial for the app. Let's imagine a node.js program which uses sqlite3. This program is written asynchronously and has several fibers of execution, and one of which involves communication with the sqlite database. In the original design, the thread/process which executes the sqlite query is going to sometimes block on reads/writes. In the new design, the thread/process will instead receive SQLITE_BUSY, and try again later - and in that window of opportunity, it could safely run other asynchronous tasks, thereby improving the overall latency of the whole system.

A very naïve, hardcoded and simplified proof of concept patch which simulates returning SQLITE_BUSY for async I/O can be found here: https://gist.github.com/psarna/2363798cfe023a3a379af8d4a4d3ac44

And it works in harmony with this example, which calls sqlite3_step in a loop until it sees SQLITE_DONE, and ends up properly inserting given value into the database: https://gist.github.com/psarna/d546e327d1c431d3b5e035858441af5e . It does not pass tests, because they generally expect operations to return results instead of SQLITE_BUSY out of the blue. The async VFS flavor will be opt-in anyway, and it is going to need a separate test suite.

• make is ok

[me@ipa01 libsql]$ git log -1
commit aafe1d5457936a05bca5d312e2fd25f32afeb20b (HEAD -> main, origin/main, origin/HEAD)
Author: Doug Stevenson <[email protected]>
Date:   Tue Oct 4 12:42:01 2022 -0400

    Update README.md
[me@ipa01 libsql]$ ./configure && make
<snipped>
[me@ipa01 libsql]$ ./sqlite3 --version
3.40.0 2022-09-28 19:14:01 f25cf63471cbed1edb27591e57fead62550d4046dbdcb61312288f0f6f24alt1
[me@ipa01 libsql]$

• zlib-devel was installed but still failing to locate uncompress function

[me@ipa01 libsql]$ make test
./libtool --mode=link gcc -g -O2 -DSQLITE_OS_UNIX=1 -I. -I/home/me/github/libsql/src -I/home/me/github/libsql/ext/rtree -I/home/me/github/libsql/ext/icu -I/home/me/github/libsql/ext/fts3 -I/home/me/github/libsql/ext/async -I/home/me/github/libsql/ext/session -I/home/me/github/libsql/ext/userauth -D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_MATH_FUNCTIONS     -o sessionfuzz /home/me/github/libsql/test/sessionfuzz.c -lm
libtool: link: gcc -g -O2 -DSQLITE_OS_UNIX=1 -I. -I/home/me/github/libsql/src -I/home/me/github/libsql/ext/rtree -I/home/me/github/libsql/ext/icu -I/home/me/github/libsql/ext/fts3 -I/home/me/github/libsql/ext/async -I/home/me/github/libsql/ext/session -I/home/me/github/libsql/ext/userauth -D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_MATH_FUNCTIONS -o sessionfuzz /home/me/github/libsql/test/sessionfuzz.c  -lm
/usr/bin/ld: /tmp/cciD1uZ7.o: in function `sqlarUncompressFunc':
/home/me/github/libsql/test/sessionfuzz.c:734: undefined reference to `uncompress'
collect2: error: ld returned 1 exit status
make: *** [Makefile:692: sessionfuzz] Error 1
[me@ipa01 libsql]$ cat /etc/redhat-release
Fedora release 36 (Thirty Six)
[me@ipa01 libsql]$

In order to provide best developer experience, it should be possible to create and register WebAssembly-based user-defined functions right from the CQL interface, without having to recompile libSQL or register it at the higher code layer, e.g. from a driver.

For that purpose, there already exists a CREATE FUNCTION statement and we should consider adding it to libSQL, with WebAssembly as the supported language. In order to be more or less human-readable, this statement could accept function body in WebAssembly text format (.wat), and then compile it locally, or just in the blob form, in case somebody prefers to avoid the recompilation.

Functions created by CREATE FUNCTION could be stored in a system table, 100% cached in memory, where they can be easily inspected and verified.

Then, the existing code paths for looking up SQL functions can be extended to also peek into the table mentioned above.

One interesting thing eBPF can do for storage devices is in-kernel b-tree lookups:

https://www.asafcidon.com/uploads/5/9/7/0/59701649/xrp.pdf

Let's look at what it takes to integrate this type of (optional) storage engine optimization.

The SQL dialect in SQLite/libSQL is very similar to PostgreSQL, but there are some differences, which make porting apps between the two databases hard. For example, PostgreSQL supports dropping multiple tables in a SQL statement, whereas SQLite dialect does not. It would be great if libSQL attempted to narrow the gap between the SQL dialect differences, perhaps as a separate "PostgreSQL compatibility mode" to retain full SQLite compatibility by default. Types are perhaps the most difficult part, but I would just start with sticking to SQLite types.

This error shows up which goes indefinitely!

file.log

With an optimistic VFS, sequentially increasing ROWIDs is a primary source of contention, and causes significant INSERT slowdown in my benchmark. We should provide a table-level option to allocate ROWIDs randomly.

This is possible in upstream SQLite by having a special row with rowid == max(int64). But not all applications like this special row.

The syntax might look like the WITHOUT ROWID table one:

CREATE TABLE t (...) RANDOM ROWID;

One of the applications of user-defined functions are user-defined aggregates, which are actually built on top of UDFs - https://www.sqlite.org/appfunc.html#the_aggregate_function_callbacks . It would be a very nice boost to keep the intermediate state of computations within the Wasm instances used to run the step function, without having to store it externally and without recreating the instance on each call

WebAssembly modules are seldom coded by hand. Instead, Rust, C++, AssemblyScript and any other language compilable to Wasm is used for that purpose. In order to facilitate creating user-defined functions in Rust that work properly with libSQL types (https://www.sqlite.org/datatype3.html), we should create a crate which exposes a libsql_bindgen macro. This macro can take care of translating Rust types directly into something that libSQL understands, which would allow users to write user-defined functions for libSQL in native Rust.

Example (pseudo-Rust, subject to change):

#[libsql_bindgen]
fn append_42(input: String) -> String {
    input + "_42"
}

It would be great to have either a feature flag in the sqlite crate https://crates.io/crates/sqlite or a separate libsql crate for easy consumption of the library.

If somebody opens a database with custom virtual WAL methods, it's reasonable to assume that they're also going to use WAL journaling mode. Let's make WAL default if the db is open with custom WAL methods, which would save users from having to explicitly call PRAGMA journal_mode=wal.

For starters, it can be as simple as a single workflow that does make test.

Historically (e.g. https://sqlite-users.sqlite.narkive.com/FsbiDRYS/sqlite-dqlite-sqlite-replication-and-failover-library) sqlite requires a few patches in order to facilitate replicating the data via Raft or another consensus protocol.

This umbrella issue is here to discuss what kinds of hooks are necessary to enable replication (be it WAL-based, statement-based, or any other type). It would be great to use https://github.com/chiselstrike/chiselstore for validating the ideas, by implementing the replication layer on top of modified sqlite.

As of now, Wasm func table can only be initialized manually by calling try_initialize_wasm_func_table, and that's inconvenient. Let's lazily initialize the table on the first call to CREATE FUNCTION. (Initializing it unconditionally or eagerly will break lots of existing tests, that's why it's out of the question)

A user-defined function capable of running WebAssembly code should be defined (and registered with sqlite3_create_function).

Its final specification should be subject to discussion, but the proposed format could look like that (pseudo-code):

fn run_wasm(wasm_module: blob, function_name: string, args...)

The function would accept a blob with compiled WebAssembly as its first argument, the Wasm function name to look up and execute as the second argument, and forward the rest of the arguments as is to this Wasm function to be executed.

This function can later be a foundation for allowing users to create and register arbitrary Wasm functions right from the CQL interface (esp. with some syntactic sugar to make it pretty: #18).

An important design decision is to pick a WebAssembly runtime for executing the modules. A preliminary list to consider:

• wasmtime (https://github.com/bytecodealliance/wasmtime): feature-complete, I have some experience with it
• wasmer (https://github.com/wasmerio/wasmer) reportedly faster
• a generic interface which allows picking one, the other, or any other runtime

Right now I'm biased in favor of wasmtime, but since I saw @losfair's activity in this repository (welcome!!!) I can't resist asking - would you recommend wasmer instead? Any recommmendations for/against it?

Suggested on discord: https://discord.com/channels/1026540227218640906/1026540227218640909/1061378723468824657

Quoting:

an idea that would avoid compatibility issues at the cost of being fairly dubious is to just pass 2 vtables to the extension entrypoint (the one you get from dlopen/dlsym), one vtable for sqlite3_api_routines and one for libsql_api_routines (which would be new).
typedef int (*libsql_loadext_entry)(
sqlite3 *db,
char **pzErrMsg,
const sqlite3_api_routines *pApiSQLite,
const libsql_api_routines *pApiLibSQL
);

That would avoid potential issues once mainstream SQLite also adds a new field to sqlite3_api_routines. Users can check if their database library knows about a new field by checking the API version, but it only works fine if there's a single source of versioning, while libSQL has a separate one.

Current main branch already has code that adds a few new fields to sqlite3_api_routines for virtual WAL, but it's not part of any official libSQL release yet, so it's a good time to consider changing it.

SQLite uses a freelist to manage free pages. This is a major source of INSERT contention when using an optimistic lock-free storage layer, like mvSQLite.

Ideally we should push free page management down to the VFS layer:

// struct sqlite3_io_methods
int (*xFreePage)(sqlite3_file*, sqlite3_int64 pageNo); // free a page
int (*xAllocatePage)(sqlite3_file*, sqlite3_int64 *pageNoOut); // allocate a new page and return its id

... and maybe write some code?

your project is doomed to failure otherwise

don't be lazy, put some actual effort in

no more whiny blog posts

no more bleating tweets

show us you aren't just gasbags with an attitude

In order to add dev-friendly support for running WebAssembly functions within libSQL, we need to specify the format in which types are passed between the two environments. For instance, when a WebAssembly function is to be called on columns of types integer, real, text, blob, there needs to be a guideline on how the header of this WebAssembly function should look like.

WebAssembly has a narrow set of supported types (https://webassembly.github.io/spec/core/syntax/types.html), and there are a few fundamental questions:

• Should we try to use native Wasm types for integers and floats?
• Should we encode strings with trailing \0 or with their size encoded in the prefix?
• etc.

The most reasonable way seems to be to follow another well defined ABI - the C one: https://github.com/WebAssembly/tool-conventions/blob/main/BasicCABI.md#function-signatures

In particular, it specifies that all non-trivial types, like structs, are passed indirectly, via a pointer. I suggest we follow this practice and decide that all parameters of the WebAssembly function are expected to be simply pointers to the sqlite3_value struct. This structure also stores its type information, so the exact type can be inferred and validated within a WebAssembly function.

Ref: https://github.com/libsql/libsql/blob/aafe1d5457936a05bca5d312e2fd25f32afeb20b/src/vdbeInt.h#L208-L231

With such a specification, helper libraries (#15) could take care of translating and validating the types, while the users have a clear ABI to follow when implementing user-defined functions which work on libSQL column types.

Most people who use SQLite with Python today do so via the sqlite3 module in the Python standard library.

This is powerful and convenient, but it has some serious limitations. Most notably, it's extremely difficult to control or upgrade the version of SQLite that is provided by this library.

I made some notes on one way to do this using LDD_PRELOAD here: https://til.simonwillison.net/sqlite/ld-preload

Another way of addressing this is to use pysqlite3 by Charles Leifer: https://github.com/coleifer/pysqlite3

This is a really great option - and even includes a pysqlite3-binary wheel that can be installed directly on Linux, skipping the need to build it from scratch.

There's still room for improvement though, and it strikes me that libsql may be in a position to take advantage of this need.

Imagine if there was an official package for the latestlibsql release available on PyPI - with binary wheels for every platform, so any Python user could pip install libsql and then start using it:

import libsql

db = libsql.connect("my.db")
...

This could start out as a direct port if the existing sqlite3 package, similar to pysqlite3. But it could then become a place for shipping new features, no longer tied to the release cycle of the Python standard library:

• custom table-valued functions
• WASM extension support
• and so much more

Setting this all up would not be trivial... but I think it could help make libsql available to a very large she receptive new audience.