4 ms·

>What about this is "virtual" and not a regular table? The article's first sentence describes what makes it "virtual". Note the "as if" in the fragment: >, an

by jasode 5y ago

>What about this is "virtual" and not a regular table? The article's first sentence describes what makes it "virtual". Note the "as if" in the fragment: >, and returns that content _as_ _if_ it were rows and columns of an SQL table. In other words, instead of creating a real SQLite empty table and then doing bulk INSERTs or ".import" from the csv file, you treat the existing the .csv on disk _as_if_ you did that. The csv is treated as a "virtual" table you can execute SQL queries against. Compare that to the alternative way of using SQLite ".import" command : https://stackoverflow.com/questions/14947916/import-csv-to-sqlite https://stackoverflow.com/questions/14947916/import-csv-to-s...

tantalor 5y ago
Is that an important difference? Who cares how the table is created. Is it a table or not?
- jasode 5y ago
  >Is that an important difference? Who cares how the table is created. You seem to be argumentative instead of trying to understand why they called it "virtual". I'll try with another example. Let's pretend you have a 10 gigabyte csv file on disk:: Using a real SQLite table: CREATE TABLE t1 (...); -- an empty db file of 8192 bytes .import thefile.csv t1 -- total disk space consumed now 20 GB because .db file is now +10 GB larger SELECT FROM t1; -- SQLite reads from .db file and not the .csv file Using a "virtual" SQLite table: CREATE VIRTUAL TABLE temp.t1 USING csv(filename='thefile.csv'); -- disk space stays the same SELECT from t1; -- SQLite directly reads from csv disk file >Is it a table or not? At the risk of stating the obvious, the extra "VIRTUAL" keyword in between "CREATE" and "TABLE" thus makes a virtual table. It is a "table" in the sense that it "acts like a table" via an interface for SQL queries. However, it's not a real table in the sense that there's no data blocks within the .db that represents that table. It's virtual. I'm not understanding what annoys you about their description.
- giraffe_lady 5y ago
  I think you might be getting mixed up between the query language and the implementation. Virtual tables define an interface that you can use to write an extension allowing you to query resources using sql semantics. The underlying representation of those resources doesn't change. So yeah the difference matters depending on your use. I don't think you can add indexes for example, unless they are specifically supported by the underlying resource, or implemented in the virtual table extension you're using. It's not a table.

by jasode 5y ago

tantalor 5y ago

Is that an important difference? Who cares how the table is created. Is it a table or not?

jasode 5y ago
>Is that an important difference? Who cares how the table is created. You seem to be argumentative instead of trying to understand why they called it "virtual". I'll try with another example. Let's pretend you have a 10 gigabyte csv file on disk:: Using a real SQLite table: CREATE TABLE t1 (...); -- an empty db file of 8192 bytes .import thefile.csv t1 -- total disk space consumed now 20 GB because .db file is now +10 GB larger SELECT FROM t1; -- SQLite reads from .db file and not the .csv file Using a "virtual" SQLite table: CREATE VIRTUAL TABLE temp.t1 USING csv(filename='thefile.csv'); -- disk space stays the same SELECT from t1; -- SQLite directly reads from csv disk file >Is it a table or not? At the risk of stating the obvious, the extra "VIRTUAL" keyword in between "CREATE" and "TABLE" thus makes a virtual table. It is a "table" in the sense that it "acts like a table" via an interface for SQL queries. However, it's not a real table in the sense that there's no data blocks within the .db that represents that table. It's virtual. I'm not understanding what annoys you about their description.
giraffe_lady 5y ago
I think you might be getting mixed up between the query language and the implementation. Virtual tables define an interface that you can use to write an extension allowing you to query resources using sql semantics. The underlying representation of those resources doesn't change. So yeah the difference matters depending on your use. I don't think you can add indexes for example, unless they are specifically supported by the underlying resource, or implemented in the virtual table extension you're using. It's not a table.