SQLite Forum

Lack of abstraction ability in SQLite
> - Recursive common table expressions (CTEs) but no recursive top-level tables.

I doubt that recursive top-level tables are useful, but you can use views if needed.

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

For stored procedures, you could instead use views with INSTEAD OF triggers; this is what I have done. A "CREATE PROCEDURE" command is unnecessary. (Note that despite what the documentation says, WITH can be used in a trigger as long as it does not occur as the first word in a command, and I find this feature useful).

For functions, you could implement this in an application code too (by parsing the first few words of a SQL code and checking if they are "CREATE FUNCTION", and then handling it by yourself). This is not possible to implement in an extension, though; to allow it in extensions would require a "syntax hook" (which would just pass any SQL code with syntax errors to the hook function that you defined, if there is one; the extension code needs to do anything else to handle it).

For a somewhat different reason, the ability to define "macro functions" would also be useful; a C API to do this may be be given arguments whose values are known at parsing time (basically, only literal values), and then returns a string with a SQL expression with host parameters; the arguments to the function are then substituted where the host parameters appear in the returned code, and then that expression is used instead of the original function call when the statement is compiled. (For example, if you write `F(X+Y,Z)` and the macro function `F` is defined as `(?1*?2)` then it is just as though you had written `((X+Y)*Z)` instead. This example isn't very useful, but if your macro uses the date/time functions, LIKELIHOOD function, expanding to multiple terms in a WHERE clause, etc, then it becomes much more useful.)

> - There are virtual tables but no way to create writable views (creating triggers over views is impossible)

Creating triggers over views is not impossible. You can use INSTEAD OF triggers to make writable views.

(Still, I don't like that you cannot use the ALTER TABLE command to rename views. I once tried to fix the code to allow this, and it seemed to work, but I have not thoroughly tested this.)

> - 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 are good reasons to not expose it as an API to applications. (I have an idea to "kind of" expose it though: Make it a test control (which, like all test controls, are subject to change), and provide macros with the major and minor VDBE version numbers. If anyone uses this feature, they have no support, and if an upgrade of SQLite breaks their program (the macros I mentioned would make this detectable at compile time), that is their own fault for using this feature, and the writer of the program that used this feature now has to fix it themself, since it is not a problem with SQLite itself; the SQLite developers will not fix it.)

The features that the bytecode engine doesn't have are things that it doesn't need, so there is no point to add it.

> - There is no way to create a "virtual database" that handles reads and writes its own way.
> - 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.)

These two are similar. I think SQLite4 allows custom storage engines, but SQLite3 does not, and perhaps this should be corrected (eventually).

Note that a VFS could construct a database file itself (or parse any writes to it by itself, although only after a transaction has been committed), since the file format is public, although this is really messy, and it is often easier to use a virtual table or a temporary database.

(Actually, there are many deficiencies in the virtual table mechanism too, although they have made many improvements to it over time.)