#!/usr/bin/env ruby

# msqldump.rb written by itacchi (DATE Ken) 2000-
# That's sample program for Ruby/mSQL.
# msqldump.rb is a `msqldump' clone.

require 'getopts'
require 'msql'


class OptionError < StandardError; end

class MsqlDump

  def initialize(host, database, verbose=false)
    @verbose = verbose
    connect(host, database)
  end
  attr_accessor :verbose, :with_column

  def dump(table=nil, where_clause=nil, table_only=false, with_column=false)
    target_tables = []

    if table
      target_tables.push table
    else
      @client.list_tables.each_row do |table|
        target_tables.push table[0]
      end
    end

    target_tables.each do |table|
      get_table_structure(table)
      dump_table(table, where_clause, with_column) unless table_only
    end

  end


  def dump_end
    disconect
    print "\n"
  end


  private
  def connect(host, database)
    STDERR.printf "Connecting to %s...\n", host ? host : "localhost" if @verbose
    @client = Msql::connect host

    STDERR.print "Selecting data base #{database}...\n" if @verbose
    @client.select_db database
  end


  def disconect
    STDERR.print "Disconnecting from #{@client.get_host}...\n" if @verbose
    @client.close
  end


  def dump_table(table, where_clause, with_column=false)
    STDERR.print "Sending SELECT query...\n" if @verbose
    printf "\n#\n# Dumping data for table '#{table}'\n#\n\n"

    if where_clause
      query = "SELECT * FROM #{table} where #{where_clause}"
    else
      query = "SELECT * FROM #{table} "
    end

    @client.query query
    res = @client.store_result

    STDERR.print "Retrieved #{res.num_rows} rows. Processing...\n" if @verbose

    res.each_row {|row|
       printf "INSERT INTO #{table} %s VALUES (", with_column ? @column_def : ""
       res.field_seek 0

       res.each_field_with_index {|field, i|
         print "," if i != 0
         if row[i]
           case field.type
           when MsqlField::CHAR_TYPE, MsqlField::TEXT_TYPE,
                MsqlField::DATE_TYPE, MsqlField::TIME_TYPE
             print "\'#{row[i].gsub(/\\{1,2}/, '\\\\')}\'"
           else
             print "#{row[i]}"
           end
         else
           print "NULL"
         end
       }

      print ")\\g\n"
    }

  end


  def get_table_structure(table)
    STDERR.print "Retrieving table structure for table #{table}...\n" if @verbose

  # table
    table_res = @client.list_fields(table)
    print "\n#\n# Table structure for table '#{table}'\n#\n"
    print "CREATE TABLE #{table} (\n"

    @column_def = "("
    table_res.each_field_with_index do |field, i|
      break if field.type > MsqlField::LAST_REAL_TYPE
      if i != 0
        @column_def += ", "
        print ",\n"
      end
      @column_def += field.name
      print "  #{field}"
    end
    print "\n) \\g\n\n"
    @column_def += ")"

    table_res.field_seek 0

  # index
    table_res.each_field do |field|
      next if field.type != MsqlField::IDX_TYPE

      index_res = @client.list_index table, field.name
      row = index_res.fetch_row

      type = row[0] == "avl" ? "" : row[0]

      printf "CREATE %s %s INDEX %s ON %s (",
        field.is_unique? ? "UNIQUE" : "",
        type, field.name, table

      index_res.each_row_with_index do |row, i|
        print (i == 0 ? "\n" : ",\n")
        print "\t#{row[0]}"
      end

      print "\n) \\g\n\n"
    end


  # sequence
    if seq = @client.get_sequence_info(table)
      print "CREATE SEQUENCE ON #{table} STEP #{seq.step} VALUE #{seq.value} \\g\n\n"
    end
  end

end




# print usage
def usage
  print "\n\nusage: \t%s [-h host] [-f conf] [-v] [-t] [-c] [-w where_clause] database [table]\n\n"
  print "\tProduce an ASCII dump of a database table or an entire database\n\n"
end



# --- main process

# parse option
opts = getopts("vtc", "h:", "f:", "w:")

# parse database name and table name
database = ARGV.shift
table    = ARGV.shift


verbose = $OPT_v
host    = $OPT_h
conf    = $OPT_f
where   = $OPT_w
is_only_table_structure   = $OPT_t
is_insert_with_column_def = $OPT_c


begin

  # illegal option or no database
  raise OptionError unless opts and database


  # too many args...
  raise OptionError if ARGV.size != 0


  # When option -f exists, load specified config file.
  if conf
    Msql::load_config_file(conf)
  end

  # print header message
  printf "#\n# mSQL Dump  (requires mSQL 2.0 Beta 5 or newer)\n#\n"
  printf "# Host: %s    Database: %s\n", host ? host : "localhost", database
  printf "#--------------------------------------------------------\n\n"

  # create dumper instance
  dump = MsqlDump.new host, database, verbose

  # dump database
  dump.dump table, where, is_only_table_structure, is_insert_with_column_def

  # end process
  dump.dump_end

rescue OptionError
  usage
end