class Object

Constants

Selector

struct for dealing with selectors

Public Instance Methods

conv(tab1, map1, tab2, map2, mode, dump) click to toggle source

convert the insert statements

# File bin/sqlconv, line 98
def conv(tab1, map1, tab2, map2, mode, dump)
  data = StringScanner.new("")
  need = grok(map1)
  rows = nil
  cols = nil
  len1 = nil
  len2 = nil
  ours = []
  posn = 0

  # statement prefix
  pref = [
    "#{mode == 'replace' ? 'replace' : 'insert'} into #{tab2 || tab1}",
    (" (#{map2})" if map2),
    " values (",
  ].compact.join

  # find source table
  data.string = dump.read # dump.read(5000) # TODO: Add streaming support
  into = data.scan_for(/insert into (['"`]?)#{tab1}\1 values /io)
  into or die "unable to find insert statements for the '#{tab1}' table"

  # if needed, output pipes header
  if mode == "pipes"
    puts map2.gsub(',','|') if map2
    lean = true
  elsif mode == "table"
    rows = []
    lean = true
  end

  # process each line
  loop do

    # parse insert statements
    if data.scan_str("(") or data.scan_str(into + "(")
      cols = data.scan_while(/('.*?(?<!\\)'|(?>[^',()]+)|,)/, 2)
      cols.empty? and die "bad sql parse: '#{line}'"
      cols.map! {|item| unescape(item)} if lean
      data.scan(/\)[;,]\s*/)
    else
      break
    end

    # perform one-time check on source column bounds
    unless len1
      len1 = cols.size
      if mode == "show"
        max = 32
        sep = "+-----+-#{'-' * max}-+"
        puts sep, "| col | %-*.*s |" % [max, max, 'data'], sep
        len1.times do |pos|
          val = cols[pos]
          val[max-3..-1] = '...' if val.size > max
          puts "| %3d | %-*.*s | " % [pos + 1, max, max, val]
        end
        puts sep
        exit
      end
      need.each do |item|
        item.text &&= escape(item.text) unless lean
        if (len2 = [item.from, item.till, 0].compact.max) > len1
          warn "selector '#{item.want}' referenced source column #{len2}, but only #{len1} are defined"
          cols &&= nil
        end
      end
      cols or exit
      len1 = cols.size
      len2 = nil # we hijacked len2, so clear it
    end

    # pluck desired columns
    ours.clear
    need.each do |item|
      (item.reps || 1).times do # repeats
        case
        when item.func # function (TODO: honor text/zero/from/till)
          case item.func
          when "rand"     then ours.push("'random number here!'")
          when "n","null" then ours.push("null")
          when "z"        then ours.push((val = cols[item.from     -1]) == "NULL" ? 0  : val)
          when /^(\d+)$/
            val = cols[item.func.to_i - 1]
            val = unescape(val) unless lean
            val = val[0, item.from]
            ours.push(val)
          else
            defined?(item.func) == "method" or die "undefined function '#{item.func}'"
            ours.push *(send item.func, *Array[cols[item.from-1]])
          end
        when item.text # literal
          ours.push(item.text)
        when item.zero # zero
          ours.push(0)
        when item.thru # range
          from = item.from || 1
          till = item.till || len1
          ours.concat case till <=> from
            when 0,1 then cols[(from-1)..(till-1)]
            when -1  then cols[(till-1)..(from-1)].reverse
          end
        when item.from || item.till # one column
          ours.push(cols[(item.from || item.till) - 1])
        else # null
          ours.push("null")
        end
      end
    end

    # perform one-time check on destination column counts
    unless len2
      if map2 and (len2 = map2.split(",").size) != ours.size
        warn "destination column mismatch (#{len2} defined but #{ours.size} generated)"
        cols &&= nil
      else
        len2 = ours.size
      end
      cols or exit
    end

    # generate output
    if mode == "pipes"
      puts ours * "|"
    elsif mode == "table"
      rows << ours.dup
    else
      puts [pref, ours * ",", ");"].join
    end
  end

  # output table
  if mode == "table"
    cols = map2 ? map2.split(',') : rows[0].size.times.map {|i| "col#{i+1}"}
    table cols, rows
  end
end
die(str) click to toggle source
# File bin/sqlconv, line 5
def die(str)
  warn str
  exit
end
escape(str) click to toggle source
# File bin/sqlconv, line 83
def escape(str)
  str =~ /\A(\d+|null)\z/i ? $1 : %|'#{str.gsub("'", "\\\\'")}'|
end
grok(want) click to toggle source

convert user request into selectors

# File bin/sqlconv, line 47
def grok(want)
  (want || "1-").strip.split(/\s*,\s*/).map do |item|
    item =~ %r!^(?:(\d+)\*)?(?:(?:( # $1: repeat
      (?:\d+(?=\())|[a-zA-Z]\w*)    # $2: function name
      (\()?)?(?:                    # $3: optional paren
      (?:(['"])(.*?)\4)? |          # $4: quote, $5: literal
      (0) |                         # $6: zero
      ((?>[1-9]\d*))?               # $7: from
        ((?<=\d)-|-(?=\d))?         # $8: thru
        ((?>[1-9]\d*))?             # $9: till
      )\)?)$
    !iox or raise "invalid selector item '#{item}'"
    Selector.new(*$~.values_at(0, 2, 5, 6, 8), *$~.values_at(1, 7, 9).map {|e| e&.to_i })
  end or raise "invalid selector '#{want}'"
end
table(cols, rows) click to toggle source

display table output

# File bin/sqlconv, line 64
def table(cols, rows)
  cols.is_a?(Array) && cols.size > 0 or return
  rows.is_a?(Array) && rows.size > 0 or return
  join = " | "
  both = [cols] + rows
  flip = both.transpose
  wide = flip.map {|row| row.map {|col| col.to_s.size }.max }
  pict = wide.map {|len| "%-#{len}.#{len}s" }.join(join)
  pict = [join, pict, join].join.strip
  line = (pict % ([""] * cols.size)).tr("| ", "+-")
  seen = -1
  puts "", line
  both.each do |vals|
    puts pict % vals
    puts line if (seen += 1) == 0
  end
  puts line, "#{seen} rows displayed", ""
end
unescape(str, nulls=false) click to toggle source
# File bin/sqlconv, line 87
def unescape(str, nulls=false)
  str =~ /\A['"]/ and return str[1..-2].gsub("|","~").gsub("''", "'")
  str == 'NULL'   and return '' unless nulls
  str
end
unescape!(str) click to toggle source
# File bin/sqlconv, line 93
def unescape!(str)
  unescape(str, true)
end