ot_query.rb

module OtMonkeyPatches
  module OtQueryPatches
    module InstanceMethods
    end
    
    module ClassMethods
      # Allows for a shorter syntax on queries:
      #   OtQuery.execute("SELECT name from '..\cube'") { |row|
      #      p row
      #   }
      #
      # Returns the value of the last row executed:
      #   max_node_nr = OtQuery.execute("SELECT max(nodenr) FROM node")[0]
      #
      # Splatting also works when using more than one column:
      #   OtQuery.execute("SELECT cubenr,name from '..\cube'") { |cubenr,name|
      #     p [cubenr,name]
      #   }
      #

      def level2_table_current_variant(table_name)
        (sub_variant_name, variant_name) = $Ot.currentVariant
        if sub_variant_name.empty?
          qualified_table(table_name, variant_name)
        else
          level2_table(table_name, variant_name, sub_variant_name)
        end
      end

      def level1_table_current_variant(table_name)
        # TODO: Andrew M - extract top_level_variant?
        top_level_variant = $Ot.currentVariant[0].empty? ? $Ot.currentVariant[1] : $Ot.currentVariant[0]
        qualified_table(table_name, top_level_variant)
      end

      def level2_table(table_name, variant_name, sub_variant_name)
        schema = sub_variant_name.empty? ? variant_name : "#{sub_variant_name}/#{variant_name}"
        qualified_table(table_name, schema)
      end

      def qualified_table(table_name, schema)
        "\"#{schema.downcase}\".\"#{table_name}\""
      end
      alias :level1_table :qualified_table

      def execute_to_hash(sql, key_cols = [0], default = nil)
        h = default ? Hash.new_with_default(default) : Hash.new
        data_rows = nil
        execute(sql) { |row|
          key = key_cols.map { |i| row[i] }
          key = key.size == 1 ? key.first : key
          data_rows ||= (0..row.size-1).to_a - key_cols
          data = data_rows.map { |i| row[i] }
          h[key] = data.size == 1 ? data.first : data
        }
        return h
      end

      def execute_to_h(_sql, _key_cols = nil, _default = nil)
        raise "This was deprecated on 2016-02-09 by Andrew M. Don't be lazy - use `execute_to_hash`, it's only 3 more characters :p"
      end

      def execute_to_hsh(_sql, _key_cols = nil, _default = nil)
        raise "Don't like vowels huh? This was deprecated on 2016-02-09 by Andrew M. Use `execute_to_hash`"
      end

      # Allows for splitting up an sql query with a long set of values in an 'in' clause
      # Expects that sql contains "IN_VALUES" which will be replaced with a bracketed
      # array of values
      # eg:
      #
      #   # Performing operation on a set of centroids
      #   in_values = (1..3875).to_a - (2000..2200).to_a
      #   OtQuery.execute_with_stagger("UPDATE cube SET something='foo' WHERE centroidnr IN IN_VALUES")
      def execute_with_stagger(sql, opts={})
        in_array = opts[:in]
        section_size = opts[:size]
        section_size ||= 100
        0.step(in_array.size,section_size) { |i|
          section = in_array.slice(i..(i+section_size-1))
          next if section.nil? || section.empty?
          q = OtQuery.new
          q.sql = sql.sub('IN_VALUES',"(#{section.join(',')})")
          q.execute
        }
      end
    end
  end
end

require 'selekt'
class PostGresSQLSanitiser
  TABLE_LEVELS = {
    :level0 => %w[area2centroid combination combination2pmtu control2object count2link cube cube1_1data1 cube4_1data1 cube4data1 cube6_1data1 dataset datasetrecord dimension fare fare1_1data1 fare1_2data1 line mode2mode name otmetadata picture point polygon polyline polylinepoints selection selectionobjects serie stop2node triline type type3data1],
    :level1 => %w[centroid link node stop transitline area control8data1 link5_1data2 screenline1data1 transitline1data1 area1data1 count link5_1data3 screenline2data1 transitline2data1 area2data1 count1data1 link5_2data1 screenline6data1 transitline3_2data1 centroid1data1 count2data1 node1_1data1 stop1data1 transitline3data1 centroid2data1 count6data1 node1data1 stop2data1 transitline5data1 centroid5_2data1 cube5data1 node1data2 stop3_1data1 turn centroid5data1 link1_1data1 node2data1 stop3_1data2 turn1_3data1 control link1_1data2 node5data1 stop3_2data1 turn1data1 control1data1 link1data1 route stop3data1 turn2data1 control2data1 link2_1data1 route1data1 stop5_4data1 turn3data1 control3data1 link3_1data1 route2data1 transitline1_1data1 turn5data1 control5data1 link5_1data1 screenline transitline1_3data1],
    :level2 => %w[centroid5_2data1 centroid5data1 control5data1 control8data1 cube5data1 link5_1data1 link51data2 link5_1data3 link5_2data1 node5data1 stop5_4data1 transitline5data1 turn5data1]
  }
  TABLES_BY_LEVEL = TABLE_LEVELS.each_with_object({}) { |(level, tables), h|
    tables.each { |t| h[t.to_sym] = level }
  }

  def self.get_table_level(table)
    TABLES_BY_LEVEL.fetch(table.downcase.to_sym)
  end

  def self.sanitize(sql)
    query = Selekt.parse(sql)
    new_sql = sql
    query.tables.map(&:text_value).each { |paradox_table_name|
      postgres_table_name = get_table_replace(paradox_table_name)
      new_sql = new_sql.gsub(paradox_table_name, postgres_table_name)
    }

    new_sql = new_sql.gsub('""', '"')
    if sql != new_sql
      $Ot.with_font('Courier', 'purple') do
        Logger.log("Auto-replacing SQL to work with OT v8")
        p "                 from this: #{sql}"
        p "                   to this: #{new_sql}"
      end
    end
    new_sql
  end

  def self.get_table_replace(table_name)
    return table_name unless table_name =~ /\'/
    tokens = table_name.gsub("'", '').gsub('\\', '/').gsub($Ot.projectDirectory, '').split('/')
    tname = tokens.last.gsub('"', '').gsub("'", '').gsub(/\.db$/i, '').downcase

    case get_table_level(tname)
    when :level0 then "\"#{tname}\""
    when :level1 then OtQuery.level1_table_current_variant(tname)
    when :level2 then table_name
    end
  end
end

class OtQuery
  def self.execute(sql, opts = {})
    sql = PostGresSQLSanitiser.sanitize(sql)
    qry = self.new
    qry.sql = sql
    puts "OtQuery#execute: #{sql}" if $DEBUG
    if sql =~ /^\s*select/i then
      qry.open
      lastrow = nil
      field_names = qry.fields.map { |x| x.first.to_sym }
      yield field_names if opts[:headers]
      qry.each { |row|
        row = Hash[*field_names.zip(row).flatten] if opts[:row_as_hash]
        if block_given? then
          lastrow = yield row
        else
          lastrow = row
        end
      }
      qry.close
      lastrow
    else
      qry.execute
    end
  end

  def self.execute_to_a(sql, opts = {})
    a = []
    execute(sql, opts) { |r|
      a << r
    }
    a
  end
end