Virtual Table implementation question

(1) By Karl Lehenbauer (lehenbauer) on 2020-12-31 18:15:31 [source]

Hi,

I am looking at implementing a virtual table to support read-only acess to large files containing rows of tab-separated key-value pairs.

It appears that all the column names have to be known at virtual table creation/connection time, because the modules appear to construct a "create table" statement that I think SQLite uses to know what to expect when interacting with the virtual table callbacks.

Is this correct? (It would be handy if it isn't because I wouldn't have to know all the column names at virtual table creation time.)

(2) By Richard Hipp (drh) on 2020-12-31 18:46:32 in reply to 1 [link] [source]

Right. SQLite needs to know the number of columnd and their names in order to run the query planner. I encountered the same limitation when I was creating the CSV virtual table.

The solution in my case was that the interface forces the application to create a separate (TEMP) virtual table for each external file to be accessed.

(3) By Karl Lehenbauer (lehenbauer) on 2020-12-31 19:02:04 in reply to 2 [link] [source]

Thank you! I was studying csv.c the most, but I looked at others like test_tclvar.c and noticed them all constructing create-table statements.

We already have some tech to read through TSV files and create a schema and to ingest the file into an SQLite database. I think a TSV-reading virtual table could still be pretty great for us. I think our schema creator can be leveraged and the virtual table implementation could discard columns and values it doesn't recognize, or map them into an "extras" column as key-value pairs, something we did in our TCL API for Cassandra.

Regards,

Karl

(4) By Gunter Hick (gunter_hick) on 2021-01-04 07:05:25 in reply to 1 [link] [source]

My csv virtual table implementation reads the first line of the given csv file to determine the field names; the resulting table is then readonly.

CREATE VIRTUAL TABLE csv_input USING csv ('input.csv');

If I need to write a csv file, the field names have to be passed as a list on creation; the resulting table is writeonly;

CREATE VIRTUAL TABLE csv_output USING csv('output.csv','<field_list>');

This is used to faciliate schema changes in other virtual tables when these arise; I have made the implementaion aware of 'SET variables' that can provide default values for new fields in the target table that are not present in the saved csv file