MySQL Trigger Command Line Editor in Ruby

mysql-triggers.rb

#!/usr/bin/ruby
require 'rubygems'
require 'optparse'
require 'ostruct'

# >> Helper functions

# deep copy
  def dcopy(o)
    return Marshal.load(Marshal.dump(o))
  end

# takes a list of strings and finds longest prefix
  def find_longest_prefix(a)
    return false if a.empty?
    begin
      return a[0] if a.length == 1
      min_length = a.map{ |item| item.length }.min
      s = a[0]
      for n in 0...min_length
        prefix = s[0..n]
	if a.map{ |item| item[0..n] == prefix}.include?(false)
	  return prefix[0...n]
	end    
      end
      return false
    rescue
      return nil
    end
  end

#get command line arguments
  def get_parameters()
    parameters = OpenStruct.new
    parameters.host = 'localhost'
    parameters.database = ''
    parameters.user = 'root'
    parameters.password = ''
    parameters.verbose = false

    optparse = OptionParser.new do |opts|
      opts.banner = "Usage: ./mysql-triggers.rb [options]"
      opts.on('-h', '--host [HOST]', "MySQL Host") do |host|
	parameters.host = host
      end
      opts.on('-u', '--user [USER]', 'MySQL User') do |user|
	parameters.user = user
      end
      opts.on('-p', '--password [PASSWORD]', 'MySQL Password') do |password|
	parameters.password = password
      end
      opts.on('-b', '--database [DATABASE]', 'MySQL Database') do |database|
	parameters.database = database
      end
      opts.on('-v', '--verbose', 'Run verbosely') do |v|
	parameters.verbose = v
      end
    end

    begin
      optparse.parse!
    rescue
      puts "Error in option parsing. Command line arguments ignored."
    end
    return parameters
  end

  def mark_substring(s, positions, wrappers)
    wrap_close = positions[1] + wrappers[0].length + 1
    wrap_close = s.length + wrappers[0].length if positions[1] == s.length
    return s.insert(positions[0], wrappers[0]).insert(wrap_close, wrappers[1])
  end

# takes a list for keys and a list for the values, combines them and returns a hash
  def array_combine(keys, values)
    a = keys.zip(values)
    return Hash[ keys.zip(values).map { |k, v| [ k, v ] } ]
  end

  def create_choices_abbr(l, wrappers)
    prefix = find_longest_prefix(l)
    if prefix == false
      prefix_length = 1
    else
      prefix_length = prefix.length
    end
    return array_combine( l.collect { |item| item[0..prefix_length] }, l.collect { |item| mark_substring(item, [0, prefix_length], wrappers ) } )
  end

  def get_user_input(trigger_part, properties, wrappers = [ '[', ']' ] )
    choices = properties['choices']
    print properties['message']
    if not choices.empty?
      if properties['restrict']
	choices = create_choices_abbr(choices, wrappers)
      else
	choices = array_combine(choices, choices)
      end
      print '(' + choices.values.join(',') + ')'
    end
    print "\n>>> "
    input_lines = []
    begin
      while true do
	input_lines.push( STDIN.gets.chomp )
	if properties.has_key?('multiline') and properties['multiline'] and not input_lines[-1].empty?
	  print "... "
	else
	  break
	end
      end 
    rescue Exception => e
      abort('Program terminated!')
    end
    if input_lines[-1].empty?
      if not properties["default"].nil? and not properties['choices'].empty?
	print "*Default value selected: #{properties['choices'][properties['default']]}\n"
	return properties['choices'][properties['default']] 
      end
    else
      if properties['restrict']
	key = input_lines[-1]
	if $Restriction_Functions.has_key?(trigger_part)
	  key = $Restriction_Functions[trigger_part].call(key)
	end
        return false if not choices.include?(key)
	r_wrappers = Regexp.new '[' + wrappers.collect { |w| Regexp.escape(w).gsub(/\\/, '\\\\') }.join() + ']'
	input_lines[-1] = choices[key].gsub(r_wrappers, '')
	print "*Value from abbreviation selected: #{input_lines[-1]}\n"
      end	   
    end
    if input_lines.empty? or (input_lines.length == 1 and input_lines[-1].empty?)
      return false
    else
      return input_lines.join("\n")
    end
  end

# << Helper functions
	  
# -- Dependencies --
# apt-get install ruby-mysql rubygems

# Documentation -> http://dev.mysql.com/doc/refman/5.6/en/create-trigger.html
  trigger_template = "CREATE DEFINER = %s \n"\
  "TRIGGER %s %s %s ON %s \n"\
  "FOR EACH ROW \n"\
  "%s\n"
  
  $Restriction_Functions = {
    'trigger_time'  => lambda { |s| s.upcase },
    'trigger_event' => lambda { |s| s.upcase }
  }

  options = {
    "definer" => { 
      "message" => 'User that defines the trigger', 
      "choices" => [ 'user', 'CURRENT_USER' ], 
      "default" => 1,
      "multiline" => false,
      "restrict" => false
      },
    "trigger_time"  => { 
      "message" => 'When to execute the trigger', 
      "choices" => [ 'BEFORE', 'AFTER' ], 
      "default" => nil,
      "multiline" => false, 
      "restrict" => true
      },
    "trigger_event" => { 
      "message" => 'The table operation', 
      "choices" => [ 'INSERT', 'UPDATE', 'DELETE' ], 
      "default" => nil,
      "multiline" => false,
      "restrict" => true
      },  
    "trigger_name"  => { 
      "message" => 'A name for the trigger', 
      "choices" => [],
      "default" => nil,
      "multiline" => false,
      "restrict" => false
      },
    "table_name"    => { 
      "message"  => 'Table name', 
      "choices"  => [],
      "default"  => nil,
      "multiline" => false,
      "restrict" => false
      }, 
    "trigger_body"  => { 
      "message" => 'Trigger code. Hit Enter twice to submit it.', 
      "choices" => [],
      "default" => nil , 
      "multiline" => true,
      "restrict" => false
      }
  }
	
  sequence = [ 
    "definer", 
    "trigger_name", 
    "trigger_time", 
    "trigger_event", 
    "table_name", 
    "trigger_body" 
    ]

  parameters = get_parameters()

  if not parameters.database.empty?
    require 'mysql'
    begin
      options['table_name']['choices'] = []
      _DB = Mysql.new parameters.host, parameters.user, parameters.password, parameters.database
      _DB.list_tables.each do |table|
	options['table_name']['choices'].push(table)
      end    
    rescue Mysql::Error => e
      puts e.errno
      puts e.error    
    ensure
      _DB.close if _DB
    end
    options['table_name']['restrict'] = true  
  end  

  trigger_params = []
  sequence.each do |trigger_part|
    input = false
    while input == false do
      properties = dcopy(options[trigger_part])
      input = get_user_input(trigger_part, properties)
      if input != false
	trigger_params.push( input )
	options[trigger_part]['value'] = input
      end
    end
  end

  trigger_sql = trigger_template % trigger_params
  print trigger_sql

  if not parameters.database.empty? 
    confirm = ['y', 'n']
    print 'Install trigger?'
    trigger_sql = trigger_sql.chomp.chomp(';')
    while not confirm.include?(answer = STDIN.gets.chomp.downcase)
      break if answer == 'n'    
      begin
	_DB = Mysql.new parameters.host, parameters.user, parameters.password, parameters.database
	_DB.query trigger_sql
      rescue Mysql::Error => e
	puts e.errno
	puts e.error
	abort('Program terminated.')
      ensure
	_DB.close if _DB
      end      
      print 'Trigger installed successfully!'
    end
  end