Created
December 28, 2018 20:07
-
-
Save raven-rock/1fb5ca15752b46324b7a92a352fbd767 to your computer and use it in GitHub Desktop.
Load TSV files into a memory-only or on-disk SQLite database, optionally indexing specified columns
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/env ruby | |
# The goal here is a script that can be called like this: | |
# | |
# sqlite-now-mem users.tsv,users,id,login,email products.tsv,products,id,name | |
# | |
# For each comma-separated tuple passed are arguments, the input file | |
# will be loaded into the table, and any following values are the | |
# names of columns for which indexes will be created. | |
# | |
# The --file=FILE flag can also be specified. If FILE doesn't exist it | |
# will be created. If it does, data will be imported into it, | |
# including if when the table already exists, rows will be appended. | |
# | |
# sqlite-now-mem --file=website.sqlite users.tsv,users,id,login,email products.tsv,products,id,name | |
require "optimist" | |
opts = Optimist::options do | |
opt :file, "SQLite database filename. Optional. If not specified, db is in memory.", :type => :string | |
end | |
p opts | |
sqlite_cmds = [ | |
".timer on", | |
".mode tab", | |
] | |
ARGV.each do |definition| | |
file, table, *index_cols = definition.split(",") | |
p [file, table, index_cols] | |
sqlite_cmds << ".import #{file} #{table}" | |
index_cols.each do |index_col| | |
sqlite_cmds << "create index index_#{table}_#{index_col} on #{table} (#{index_col});" | |
end | |
end | |
# final_command = "sqlite3 -header" + " " + sqlite_cmds.map { |cmd| %[-cmd "#{cmd}"] }.join(" ") + " " + %{-cmd ".mode column"} | |
final_system_cmd_elements = [ | |
"sqlite3", | |
"-interactive", | |
"-header", | |
*sqlite_cmds.map { |cmd| %[-cmd "#{cmd}"] }, | |
%{-cmd ".mode column"}, | |
*opts[:file], | |
] | |
puts final_system_cmd_elements.join(" \\\n ") | |
system final_system_cmd_elements.join(" \\\n ") |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment