SQLite Release 3.40.0 | Hacker News Reader

SQLite Release 3.40.0

sqlite.org

119 pointsthefilmore16 comments

This contains the beta release of the official WASM build of SQLite https://sqlite.org/wasm/doc/trunk/index.md

SQLite in the browser with WASM/JS - https://news.ycombinator.com/item?id=33374402 - Oct 2022 (198 comments)

> b. Extract the values of expressions contained within expression indexes where practical, rather than recomputing the expression. > c. The NOT NULL and IS NULL operators (and their equivalents) avoid loading the content of large strings and BLOB values from disk. > d. Avoid materializing a view on which a full scan is performed exactly once. Use and discard the rows of the view as they are computed. > e. Allow flattening of a subquery that is the right-hand operand of a LEFT JOIN in an aggregate query.

Gulp. When writing SQL I tend to assume these kinds of things have always been done... I should probably favor my own logical optimizations when I can.

(Of course, nothing beats measuring/profiling if you really need to optimize.)

Does the WASM port include full-text search (FTS)?

Not by default, you can test it out here[1].

I documented how to compile an extension into the wasm build here[2], I assume something like that would work for fts? But I'm not sure exactly.

edit: it does build with fts4 by default, just not fts5; I'm not sure why

edit 2: to build with an apparently-functional fts5, apply this diff followed by "make" from the ext/wasm directory; the wasm files you want are in the ext/wasm/jswasm directory after the build completes

    diff --git ext/wasm/GNUmakefile ext/wasm/GNUmakefile
    index 039dff410..554cf9cda 100644
    --- ext/wasm/GNUmakefile
    +++ ext/wasm/GNUmakefile
    @@ -111,6 +111,7 @@ sqlite3.c := $(dir.top)/sqlite3.c
     sqlite3.h := $(dir.top)/sqlite3.h
     SQLITE_OPT = \
       -DSQLITE_ENABLE_FTS4 \
    +  -DSQLITE_ENABLE_FTS5 \
       -DSQLITE_ENABLE_RTREE \
       -DSQLITE_ENABLE_EXPLAIN_COMMENTS \
       -DSQLITE_ENABLE_UNKNOWN_SQL_FUNCTION \

I don't know why it's not in the default list of flags.

[1]: https://sqlite.org/fiddle/

[2]: https://github.com/llimllib/wasm_sqlite_with_stats/tree/buil...

Huh, I wonder why FTS4 and not FTS5? I could understand omitting both of them for reasons of size, but having the older version but not the new one feels surprising to me.

> Huh, I wonder why FTS4 and not FTS5? I could understand omitting both of them for reasons of size, but having the older version but not the new one feels surprising to me.

It was an oversight on my part. It will be fixed in the trunk momentarily.

Nice! The wasm build is great, thanks for all your work on it

> Does the WASM port include full-text search (FTS)?

It inadvertently includes FTS4 instead of 5. That was my fault and it will be changed to include 5 instead Real Soon Now.

I love using SQLite. It is often my first & only choice when reaching for an embedded database. That being said, are there any compelling alternatives for embedded databases? I'm only aware of DuckDB & Apache Parquet at the moment.

>That being said, are there any compelling alternatives for embedded databases?

https://firebirdsql.org/

H2 for java

I'm using dataset for python, it's a thin layer to use SQLite without queries. You can't do joins.

It's pretty great, although killing the python process generate threading errors...

SQLite and Minix3 is probably the most used Software on earth ;)

For the unaware, on which device ?

Intel ME is a "hidden processor" on every modern Intel x86 computer, and the modern versions of Intel ME are based on MINIX. https://en.wikipedia.org/wiki/Intel_Management_Engine

SQLite is used by e.g. browsers, phones, and many desktop apps.

Reply on news.ycombinator.com