#! /usr/bin/env ruby

require 'readline'
require 'sequel'
require 'terminal-table'
require 'yaml'

#DB = Sequel.oracle 'cink', :user => 'dhensgen', :password => '...'
#puts DB.fetch('select count(1) from prod_sched').first

config = YAML.load(File.read("#{ENV['HOME']}/.dbsh"))[ARGV[0]]
DB = Sequel.connect(config)

File.open("#{ENV['HOME']}/.dbsh_history", 'r') do |f|
  f.each_line do |line|
    Readline::HISTORY << line.strip
  end
end

#Example ~/.dbsh:
#dbsh_test:
#  adapter: sqlite
#  database: test.sqlite3
# 
## :adapter=>'postgres', :host=>'localhost', :database=>'blog', :user=>'user', :password=>'password'

HELP = <<END
\\h             Show this help
\\d             Show tables
\\d table_name  Show table columns
\\. file_name   Execute a sql file
\\e file_name   Edit file and then execute it
\\q             Quit
END

def statement_type(statement)
  case statement
    when /\s*select/i then :select
    when /\s*insert/i then :insert
    when /\s*update/i then :update
    when /\s*delete/i then :delete
    when /\s*\\/i then :command
    else nil
  end
end

def process_statement(statement)
  case statement_type(statement)
  when :select
    dataset = DB[statement]
    table = Terminal::Table.new(:headings => dataset.columns) do |t|
      dataset.each do |row|
        t.add_row(dataset.columns.map {|col| row[col]})
      end
    end
    puts table
  when :insert
    DB[statement].insert
    puts 'Inserted row'
  when :update
    puts "Updated #{DB[statement].update} rows"
  when :delete
    puts "Deleted #{DB[statement].delete} rows"
  else
    puts 'Unknown statement type'
  end
end

def process_command(command)
  case command
  when /\\q/
    exit(0)
  when /\\\./
    process_statement(File.read(command.split(/\s+/)[1]))
  when /\\d(\s+\w+)?/
    if $1
      table = Terminal::Table.new(:headings => ['Name', 'Type']) do |t|
        DB.schema($1.strip).each do |col|
          t.add_row [col.first.to_s, col.last[:db_type]]
        end
      end
      puts table
    else
      puts DB.tables.join("\n")
    end
  when /\\e\s+(.+)?/
    if $1
      system(ENV['EDITOR'], $1)
      process_statement(File.read $1)
    else
      puts DB.tables.join("\n")
    end
  when /\\h/
    puts HELP
  else
    puts 'Unknown command'
  end
end

File.open("#{ENV['HOME']}/.dbsh_history", 'a') do |history|
  while line = Readline.readline('> ', true)
    history.write(line + "\n"); history.flush
    begin
      statement_type = statement_type(line)
      if statement_type == :command
        process_command(line)
      else
        process_statement(line)
      end
    rescue Exception => e
      exit(0) if SystemExit === e 
      puts 'Error: ' + e.message
    end
  end
end

__END__

OVERVIEW

  A clone of mysql cli with these enhancements:

MVP

X  Connect to databases by name:
X
X    $ dbsh rockwell_dev
X    ~/.dbsh:
X      rockwell_dev:
X        adapter: mysql
X        username: root
X        password:
X        database: rockwell_dev

X  Connect to different types of databases:
X
X    * mysql
X    * oracle
X    * sqlite
X    * postgres

X  Execute sql:
X
X    > select * from illustrations;
X    id  name     updated_at
X    --  -------  ----------
X    10  Filmore  2012-06-12
X
X    > delete from illustrations;
X    Deleted 1 row.

X  Quit:
X
X    > \q
X    $

X  Report errors:
X
X    > foo;
X    ERROR: What is foo?

X  Install:
X
X    $ gem install dbsh

X  Readline support:
X
X    * History
X    * Edit line

X  Execute a sql script:
X
X    > \. illustrations.sql
X    ...

X  Explore the database:
X
X    > show tables;
X    name
X    -------------
X    illustrations
X    builds
X
X    > desc illustrations;
X    name       type
X    ---------- ------------ 
X    id         integer
X    name       varchar(256)
X    updated_at datetime

X  Edit and run a sql script:
X
X    > \e illustrations.sql
X    (open script in $EDITOR, execute script if changes were made)

  Semicolons

    > select 1; select 2

  Execute a sql script from the command line:

    $ dbsh rockwell_dev < illustrations.sql
    ...

Beyond MVP:

  * Export csv
  * Color output
  * Parameterized sql scripts
  * Column filtering