Yes, this is possible and it is a very good idea -at least for me- ... I created my own virtual table extensions and extension functions to achieve exactly that functionality (and more), which you are asking for.
~~~
CREATE VIRTUAL TABLE logFolder_ using directory('.');
drop view if exists allLogFiles_;
CREATE VIRTUAL TABLE if not exists allLogFiles_ using fileFromList(
select FilePath from logFolder_ where Entry like 'filetype.%' order by mtime
);
CREATE VIRTUAL TABLE singleLogFile_ using file('./filetype.log.1');
select Content from singleLogFile_ where instr(Content,'Recipe');
select Content from allLogFiles_ where instr(Content,'Recipe');
~~~
With several additional extension functions I am then able to filter out what I need much easier than with only standard SQLite functions like instr and substr. For example I added a function ` match ` to use pattern matching or regular expression matching and extractions.
The ` case ` statement and the function ` coalesce ` are my best friends to create and fill a single result column with 'same' info from various text lines in different formats. The preparation work I do inside an SQL statement inside a materialized temporary table:
~~~
WITH tab as MATERIALIZED ( select .... from allLogFiles_ ) select * from tab.
~~~
Scanning Multi-Megabytes of logfiles for few filtered events takes me only few seconds... and the SQLite3 database size containing the queries remains below 100 kB with my filters of interest being stored as views.
What I am missing is the capability of SQLite3 to add temporary index information to a materialized WITH created temporary table. Going back in sequence (event info from more than just one line of text) would probably mean to scan again the materialized table (instead of using an index), which can multiply the access time because of the scanning-only method of my virtual tables. In such cases the temporary index information would speed up subsequent WITH tables very much before delivery of the final aggregated result set. Reusability of ` WITH materialized ` created tables could be significantly improved with such a capability.