Support virtual sqlite3 tables in dbschema.json

[trenta3]

Sqlite has two very useful extensions: Full Text Search and Spellfix search that could make searching on Zites more meaningful and very fast.

Looking briefly at files in src/Db, I think that it would be a very short change to enable virtual tables: One could just modify a couple of lines in the function createTable@DbCursor.py.

So a new virtual table using fts5 could be defined as

"fts_posts": {
    "virtual": true,
    "payload": fts5(title, body, uuid UNINDEXED)",
    "schema_changed": 1
}

that would be translated into CREATE VIRTUAL TABLE fts_posts USING fts5(title, body, uuid UNINDEXED). Update and delete queries works as in a standard table.

It is indeed true that such extensions are optional, but since ZeroNet ships with its built version of sqlite3 I think we can make sure such extensions are enabled.

[HelloZeroNet]

I'm afraid these modules are not enabled in python by default. Using these modules would make it install from repository much harder.

[trenta3]

How about adding The Amalgamation (whole source code in a single .c file) to the repository? This way who downloads the repository only has to have a C compiler (which is very common I think and the build instructions are only for Linux OSes, which usually has a C compiler already installed or an apt-get/ipkg line away) and installing from repository should only be a matter of adding a couple line of instructions for the C compiler in the build phase (that should be equal for every linux distribution and for the Dockerfile and for the Vagrantfile).

If you possibly like such an idea, I might send a PR in the next days and we can talk about it in more detail.

[purplesyringa]

ZeroNet is for Windows as well.

[trenta3]

@imachug Yes, but for Windows and Mac there are no "install from repo" instructions, there are only prebuilt package so that it would be additional burden on developers only, not on end users.

We can discuss whether it is worthwhile, but I see the added benefits of:

• Full text search with spellfix1 and fts5
• Import of comma separated value files for "more compact" information storage (no need to repeat headers name) with the extension csv
• R-tree for multidimensional range queries (for geospatial system, e.g. an analogue of Google Maps)

Moreover this issue is about enabling the syntax of virtual tables, which requires no extra compiling and is a two line change.

[ghost]

I was recently talking with someone (I believe @imachug) about fts. I would like this feature along with Virtual tables and SQL Procedures.

[HelloZeroNet]

Import of comma separated value files for "more compact" information storage (no need to repeat headers name) with the extension csv

It could be a good idea, but not sure if it's significantly smaller than a gzipped json file