#!/usr/local/bin/ruby
#
# Inspired by http://blog.x180.net/2005/06/rails_and_datab.html, the
# following script converts simple MySQL DDL into DDL that SQLite will
# accept.
#
# Usage:
#   mysqlite db/development.db < db/create.sql
#
# Hint: if you are seeking to understand how this script works, start at
# the bottom.

require 'generator'

# $token is an Enumerable set of tokens from $stdin, where tokens are
# defined as any number of consecutive word-characters, or a single
# non white-space character.
$token = Generator.new do |g|
  $stdin.each do |line|
    while line != ""
      token, line = / \s* (\w+|.)? (.*) /mx . match(line).to_a[1..2]
      g.yield token if token
    end
  end
end

# assert that the next token is what you expect
def assert_next expected
  actual = $token.current
  if $token.next.downcase != expected
    raise "Syntax error: <#{expected}> expected but was <#{actual}>"
  end
end

# return the next identifier
def identifier
  assert_next "identifier" unless $token.current =~ /\w+/
  return $token.next
end

# return the sqlite equivalent of a mysql type
# * int becomes integer
# * auto_increment is removed
def type
  value = identifier()
  value = "integer" if value.downcase == "int"

  if $token.current.downcase == "("
    value += $token.next while $token.current != ")"
    value += $token.next
  end

  if $token.current.downcase.downcase == "not"
    assert_next "not"
    assert_next "null"
    value += " not null"
  end 

  $token.next if $token.current.downcase.downcase == "auto_increment"

  return value
end

# encapsulate a column in a table
class Column
  attr_accessor :name

  def initialize
    @name = identifier()
    @type = type()
  end

  def makekey
    @type += " primary key"
  end

  def to_s
    "  #{@name} #{@type}"
  end
end

# mysql has a separate clause for declaring the primary key of a table,
# sqlite achieves the same thing with a modifier on the column itself
def primary_key columns
  assert_next "primary"
  assert_next "key"
  assert_next "("

  key = identifier()
  column = columns.find {|c| c.name.downcase == key.downcase}
  raise "primary key #{key} not found" unless column
  column.makekey()

  assert_next ")"
end

# Process a create table statement
def create
  assert_next "table"
  name = identifier()
  assert_next "("

  columns = []
  while true
    if $token.current.downcase == "primary"
      primary_key columns
    else
      columns.push Column.new
    end

    break unless $token.current == ","
    assert_next ","
  end
  assert_next ")"
  assert_next ";"

  puts "create table #{name} ("
  puts columns.join(",\n")
  puts ");"
end

# Process a drop table statement
def drop
  conditional = false

  assert_next "table"

  if $token.current.downcase == "if"
    assert_next "if"
    assert_next "exists"
    conditional = true
  end

  name = identifier()
  assert_next ";"

  return if conditional and `echo .schema #{name} | sqlite test.db`.empty?

  puts "drop table #{name};"
end

# convert mysql statements to sqlite.  Create and drop statements are
# processed, nothing else is supported at this time.
def statement
  case identifier()
    when "create"
      create()
    when "drop"
      drop()
    else
      raise "Unsupported: \'%s\'" % $token.current
  end
  $stdout.flush
end 

# pump statements to sqlite.  Arguments passed to this script are suitably
# escaped and also passed onto sqlite. 
open("| sqlite #{ARGV.map{|arg| arg.dump}.join(' ')}", "w") do |$stdout|
  while $token.next?
    statement()
  end
end