SQLite Forum

Timeline
Login

2 forum posts by user mattkuine

2020-10-14
12:43 Reply: Error: near line 1: no such module: fts5 (artifact: 9b0a4b210c user: mattkuine)

FTS5 is a core SQLite module which is always included in the source code. You can download Fossil and run the command fossil clone https://www.sqlite.org/cgi/src to download the module.

08:58 Post: Lack of abstraction ability in SQLite (artifact: 1042988a22 user: mattkuine)

SQLite's query language is so powerful it's even Turing-complete with recursive common table expressions. However it's completely lacking in abstraction power.

  • Recursive common table expressions (CTEs) but no recursive top-level tables.
  • There is a way to add custom functions via the C API but no way to write custom functions in SQL (to reuse commonly used code, etc.) or a built-in procedural language.
  • There are virtual tables but no way to create writable views (creating triggers over views is impossible)
  • There is a bytecode engine with powerful features such as coroutines but it lacks a stack (to have reentrancy, continuations, and abstractions) and is not exposed as an API to applications.
  • There is no way to create a "virtual database" that handles reads and writes its own way.

The C API is lacking as well:

  • There is no way to create and register new storage engines. I can only write new VFS layers that allow SQLite to write databases in the SQLite3 B-tree format to new file systems even though the SQLite bytecode refers not to B-tree leaves, branches and nodes but to SQL database tables and rows. This makes it impossible for SQLite to read from and write to CSVs, Excel files, etc. without the special "virtual table" feature. (Ideally, the "virtual table" feature shouldn't be special.)
  • SQLite doesn't support the CREATE PROCEDURE syntax even just to pass the code to an external interpreter to run.

I think adding these features to SQLite, which is already modular by design, isn't difficult. SQLite already implements many programming features such as streaming logic, relational logic, authorization, table-valued functions, etc. and I think it can be made into a general-purpose, multi-paradigm (stack based, functional, logic, procedural, object-oriented, etc.) programming environment and operating system for developing portable applications that run anywhere if these features are added.

SQLite is a modular, well-designed, extensible data storage and processing library and if it is a little bit more modular and extensible it would take over the world.