SQLite Forum

Lack of abstraction ability in SQLite
>  - 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.

Database procedures are especially useful in client/server database models, where they avoid the dataflow bottleneck between client and server by running procedures in the server. Dataflow between a host program and the SQLite library is orders of magnitude faster.

Think of SQLite not as a replacement for Oracle but as a [replacement for fopen()](https://www.sqlite.org/about.html) .

Reuse of commonly used code can easily be obtained by using functions or macros in the host language, or by using some other macro processor (m4, anyone?).

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

You can create INSTEAD OF triggers on views and make them writable that way.

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

It is good enough to serve its sole purpose: a database engine.

It is, and should be, a black box, only exposed through higher abstractions like the C-API and SQL.

> - There is no way to create a "virtual database" that handles reads and writes its own way.

Couldn't that be achieved with virtual tables, and a :memory: database for just the schema? 

>The C API is lacking as well: 
> - There is no way to create and register new storage engines. ... 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.)

IMHO virtual tables are not more special than alternative storage engines.
And good luck implementing an Excel storage engine.
SQLite can be used the other way around, as a storage engine for a spreadsheet program, e.g. with an ODBC driver.

> - SQLite doesn't support the `CREATE PROCEDURE` syntax even just to pass the code to an external interpreter to run.

SQLite intents to replace fopen() in order to manage a structured data storage, accessible with SQL. As a database engine library, the expectation is that all procedural code is handled by the host program.

> I think adding these features to SQLite, which is already modular by design, isn't difficult. ...

I think the strength of SQLite is that it is faithful to its scope and only implements features that can be maintained by the small team.

> SQLite is a modular, well-designed, extensible data storage and processing library

I fully agree.

> and if it is a little bit more modular and extensible it would take over the world.

It already has [taken over the world](https://www.sqlite.org/index.html). Anyone is free to fork the code and add features, and people have done that to some extent, but the results were never as successful as core SQLite itself.

However, I understand your enthusiasm about the concept, quality of implementation, and excellent support for SQLite, and your desire to apply that quality to a bigger scope.

Just my EUR 0.02

Kees Nuyt