support SQLite plugins

[ghost]

Problem

Is it possible to support sqlite plugins (.so/.dll)?

Context

I wanted use FTS5 from sqlite to replace postgresql's built-in FTS, which does not have BM25 ranking support.

The idea was to use something like

-- Create a table. And an external content fts5 table to index it.
CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);
CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a', tokenize='myowntokenizer');

-- Triggers to keep the FTS index up to date.
CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;
CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
END;
CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;

to sync data from postgresql table to a sqlite foreign table, but I found that I cannot load the customized tokenizer (myowntokenizer.so/myowntokenizer.dll) on the sqlite side.

[t-kataym]

Do you expect sqlite_fdw to call load_extension()? If you mean so, I think it is feasible technically.

But we have no plan to develop such feature now. Your contribution is welcome.

[mkgrgis]

@nick008a, for PostgreSQL SQLite runtime plugins is outer third-party binary code with execution rights. This can be a security issue. Maybe using SQLite C-code with included functions from most popular plugins will be better.

[ghost]

Do you expect sqlite_fdw to call load_extension()?

Yes.

This can be a security issue.

I know, but I think there should be an option to enable this.

[mkgrgis]

@nick008a , You can write a function for PostgreSQL database superuser like in this places https://github.com/pgspider/sqlite_fdw/blob/dbb7a390c2c6b2aeaa543777391f1dbc7e2d0401/sqlite_fdw--1.0--1.1.sql#L6 https://github.com/pgspider/sqlite_fdw/blob/dbb7a390c2c6b2aeaa543777391f1dbc7e2d0401/connection.c#L64 https://github.com/pgspider/sqlite_fdw/blob/dbb7a390c2c6b2aeaa543777391f1dbc7e2d0401/connection.c#L694 About SQLite C call see in https://www.sqlite.org/c3ref/load_extension.html

Please also add testing case: SQLite query with function from some extension -> not exist, load extension -> query OK.

[mkgrgis]

@nick008a , have you got any results or problems with implementation?

[mkgrgis]

@t-kataym , I have implementation of plugin support. There is user check if superuser or owner of the foreign server. For testing there is useful set and official documentation about compilation. Could you please explain me how should I check the implementation with current testing environment? Look like we should include some simple extension in test.sh chain or not? How can I represent a SQLite extension in testing environment? Binary file is a bad idea because different processor architectures, but I have no idea how to add compilation process of an extension only for testing. Have you got any ideas?

[jopoly]

@mkgrgis I want to describe my understanding of your comment:

1. You have finished the source code implementation but do not know how to implement test code, including the script files, sql files, and expected files.
2. You want to use the extensions in https://github.com/nalgeon/sqlean to execute test and do not want to download and use binary files from this repository. It means we need to build binary files from source code.

If my understanding is true, I have some suggestions:

1. You may follow these steps to add test code:
   • Create an init.sh file that includes the steps to build binary files from source code of SQLite extensions.
   • Update test.sh to invoke the init.sh before executing make check.
   • Add sql query to sqlite_fdw.sql and sqlite_fdw.out to verify the loading extension result. I think you use the SQLite C Interface (sqlite3_load_extension() function) to implement this feature, and the implementation is similar to sqlite_fdw_get_connections function. If it returns the result of loading extension, we may verify it.
2. Because we build binary files from source code of extensions, we should add source code of extensions into sqlite_fdw repository. So, it won't require network communication during testing.
3. We can consider choosing a simple extension instead of executing test with all extensions from https://github.com/nalgeon/sqlean

[mkgrgis]

If my understanding is true, I have some suggestions:

Yes, @jopoly. Your understanding is completely true. Thanks for suggestions! In my current plans plugins support is after GIS, macaddr and attached schema support. Your idea with special test extension instead of one of sqlean extensions sounds fine. Maybe some UUID functions will be tested in a special test extension.

[jopoly]

@mkgrgis Thank you for your effort. We will keep this issue open. When you create the Pull Request, we will review and confirm it.