SQLite Forum

A flat-file interface for SQLite?
Login

A flat-file interface for SQLite?

(1) By nat-418 on 2021-11-20 08:28:32 [link] [source]

Hello, I would like to use a directory of text files to interface with SQLite. For example, imagine a table with the columns id, filename, created, modified, author, and contents. A user could then work with the SQLite configured to look in some directory, and DB users / applications could benefit from all the features of SQLite like full-text search etc.

Is this possible? Is it a bad idea for some reason? I am imagining a kind of notebook application where the flat files are Markdown format.

(2) By MBL (UserMBL) on 2021-11-20 13:16:59 in reply to 1 [source]

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.

(4.1) By nat-418 on 2021-11-21 09:33:06 edited from 4.0 in reply to 2 [link] [source]

Thank you for the detailed reply. I have used SQLite for CRUD apps but never gotten creative with it. After hearing Hipp propose more uses for it I figured I might hack something together.

(6.1) By nat-418 on 2021-11-21 09:33:53 edited from 6.0 in reply to 4.0 [link] [source]

Deleted

(7) By anonymous on 2021-11-20 17:54:33 in reply to 6.0 [link] [source]

If you just need a took for doing this then try out also LogParser

(8.1) By nat-418 on 2021-11-20 21:53:00 edited from 8.0 in reply to 7 [link] [source]

Deleted

(3) By Stephan Beal (stephan) on 2021-11-20 14:31:08 in reply to 1 [link] [source]

Is this possible?

To add to what MBL said, see:

https://www.sqlite.org/vtab.html

in particular, there's a link at the bottom of section 1 to more virtual tables:

https://www.sqlite.org/vtablist.html

one of which may already do what you want or provide a good starting point.

(5) By nat-418 on 2021-11-20 14:46:00 in reply to 3 [link] [source]

Thanks for these links. I think fsdir will be where I start.