Load TSV files into a memory-only or on-disk SQLite database, optionally indexing specified columns

sqlite-now-mem.rb

#!/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    ")