sqlite> .import myfile.csv mytable
sqlite> select ...
It looks like textql loads everything into an in-memory SQLite instance, whereas I'd really like to see an approach that uses the SQLite's virtual table mechanism (https://sqlite.org/vtab.html), which would avoid the loading step and perhaps make streaming processing possible.
The streaming would make it memory efficient, and possibly able to handle some big data - maybe not true "Big Data", but certainly 10s of gigabytes.
Anyone want to take this idea into a GoFundMe site?
Projection (mapping), a join against a fully loaded other side as well as filtering work.
Aggregation can consume an indefinite stream with limited working set if the cardinality of the grouping key isn't large.
And of course you can combine these in nested and unioned operations, computing across multiple indefinite streams concurrently and with limited working set.
It would be tricky to make work effecively without hinting for things like joins, for sure; join order is one of the hardest bits a query engine optimizes.
I usually have to develop a database in Windows and Access. One more tool to work in Linux is a good idea.
I used to use awk and sed before.
Key differences between textql and sqlite importing
sqlite import will not accept stdin, breaking unix pipes. textql will happily do so.
textql supports quote escaped delimiters, sqlite does not.
textql leverages the sqlite in memory database feature as much as possible and only touches disk if asked.
I don't think OP needs to pussy foot around some stranger on an internet message board. It's free shit.
The Readme is describing the differences in importing. I'm not sure how your distinction makes this any more clear.
> sqlite import will not accept stdin, breaking unix pipes. textql will happily do so.
> textql supports quote escaped delimiters, sqlite does not.
> textql leverages the sqlite in memory database feature as much as possible and only touches disk if asked.
#!/usr/bin/env rc
row_headers=''
output_mode=column
fn usage {
echo $0 usage
echo -o <sqlite output mode> '#' which output mode to use
echo -r '#' if present, output row headers
echo -h '#' this message
}
while(~ $1 -*) {
switch($1) {
case -o
output_mode=$2
shift
case -r
row_headers='.headers on'
case -h
usage
exit 0
case *
echo Bad args: $*
usage
exit 1
}
shift
}
test $#* -ne 1 && echo Wrong number of arguments && usage && exit 1
sql_statement=$1
sqlite_bin=''
{ which sqlite > /dev/null >[2=1] && sqlite_bin=sqlite } || { which sqlite3 >/dev/null >[2=1] && sqlite_bin=sqlite3 }
table_name=atable
stdin_file=/proc/$pid/fd/0
{
echo .mode csv
echo .import $stdin_file $table_name
echo $row_headers
echo .mode $output_mode
echo $sql_statement
} | $sqlite_bin