jam-cloud/ruby/lib/jam_ruby/models/jam_isp.rb

require 'ipaddr'
=begin

module JamRuby
  class JamIsp < ActiveRecord::Base

    # index names created on the copied table used during import.
    # they do not exist except during import

    GEOIPISP_INDEX_NAME = 'geoipisp_company_ndx'
    COPIED_GEOIPISP_INDEX_NAME = 'geoipisp_copied_company_ndx'

    JAMCOMPANY_UNIQUE_INDEX = 'jamcompany_company_ndx'
    COPIED_JAMCOMPANY_UNIQUE_INDEX = 'jamcompany_copied_company_ndx'

    JAMCOMPANY_PRIMARY_KEY_NAME = 'jamcompany_pkey'
    COPIED_JAMCOMPANY_PRIMARY_KEY_NAME = 'jamcompany_copied_pkey'

    COPIED_JAMCOMPANY_COID_SEQUENCE = 'jamcompany_copied_coid_seq'
    JAMCOMPANY_COID_SEQUENCE = 'jamcompany_coid_seq'

    JAMISP_GEOM_INDEX_NAME = 'jamisp_geom_gix'
    COPIED_JAMISP_GEOM_INDEX_NAME = 'jamisp_copied_geom_gix'

    JAMISP_COID_INDEX_NAME = 'jamisp_coid_ndx'
    COPIED_JAMISP_COID_INDEX_NAME = 'jamisp_copied_coid_ndx'

    COMPANY_TABLE = 'jamcompany'
    GEOIPISP_TABLE = 'geoipisp'

    @@log = Logging.logger[JamIsp]

    belongs_to :jam_company, class_name: 'JamRuby::JamCompany', foreign_key: 'coid'

    self.table_name = 'jamisp'

    def self.ip_to_num(ip_addr)
      begin
        i = IPAddr.new(ip_addr)
        return i.to_i if i.ipv4?
        nil
      rescue IPAddr::InvalidAddressError
        nil
      end
    end

    def self.lookup(ipnum)
      JamIsp.select(:coid)
      .where('geom && ST_MakePoint(?, 0) AND ? BETWEEN beginip AND endip', ipnum, ipnum)
      .limit(1)
      .first
    end

    def self.createx(beginip, endip, coid)
      c = connection.raw_connection
      c.exec_params("insert into #{self.table_name} (beginip, endip, coid, geom) values($1::bigint, $2::bigint, $3, ST_MakeEnvelope($1::bigint, -1, $2::bigint, 1))",
                    [beginip, endip, coid])
    end

    def self_delete()
      raise "mother trucker"
    end

    def self.delete_all()
      raise "mother trucker"
    end

    def self.import_from_max_mind(options)

      file = options[:file]
      use_copy = options[:use_copy]

      # File Geo-124
      # Format:
      # startIpNum,endIpNum,isp

      start = Time.now

      copied_table_name = Database.copy_table(GEOIPISP_TABLE)
      copied_jamcompany_table_name = Database.copy_table(COMPANY_TABLE)
      copied_jamisp_table_name = Database.copy_table(self.table_name)

      if use_copy
        Database.copy(copied_table_name, file)
      else
        File.open(file, 'r:ISO-8859-1') do |io|
          #s = io.gets.strip # eat the copyright line. gah, why do they have that in their file??
          #unless s.eql? 'Copyright (c) 2012 MaxMind LLC.  All Rights Reserved.'
          #  puts s
          #  puts 'Copyright (c) 2012 MaxMind LLC.  All Rights Reserved.'
          #  raise 'file does not start with expected copyright (line 1): Copyright (c) 2012 MaxMind LLC.  All Rights Reserved.'
          #end

          #s = io.gets.strip # eat the headers line
          #unless s.eql? 'locId,country,region,city,postalCode,latitude,longitude,metroCode,areaCode'
          #  puts s
          #  puts 'locId,country,region,city,postalCode,latitude,longitude,metroCode,areaCode'
          #  raise 'file does not start with expected header (line 2): locId,country,region,city,postalCode,latitude,longitude,metroCode,areaCode'
          #end

          saved_level = ActiveRecord::Base.logger ? ActiveRecord::Base.logger.level : 0
          count = 0

          stmt = "INSERT INTO #{copied_table_name} (beginip, endip, company) VALUES"

          vals = ''
          sep = ''
          i = 0
          n = 20

          csv = ::CSV.new(io, {encoding: 'ISO-8859-1', headers: false})
          csv.each do |row|
            raise "file does not have expected number of columns (3): #{row.length}" unless row.length == 3

            beginip = ip_address_to_int(strip_quotes(row[0]))
            endip = ip_address_to_int(strip_quotes(row[1]))
            company = row[2]

            vals = vals+sep+"(#{beginip}, #{endip}, #{quote_value(company, nil)})"
            sep = ','
            i += 1

            if count == 0 or i >= n then
              GeoIpLocations.connection.execute stmt+vals
              count += i
              vals = ''
              sep = ''
              i = 0

              if ActiveRecord::Base.logger and ActiveRecord::Base.logger.level > 1 then
                ActiveRecord::Base.logger.debug "... logging inserts into #{copied_table_name} suspended ..."
                ActiveRecord::Base.logger.level = 1
              end

              if ActiveRecord::Base.logger and count%10000 < n then
                ActiveRecord::Base.logger.level = saved_level
                ActiveRecord::Base.logger.debug "... inserted #{count} into #{copied_table_name} ..."
                ActiveRecord::Base.logger.level = 1
              end
            end
          end

          if i > 0 then
            GeoIpLocations.connection.execute stmt+vals
            count += i
          end

          if ActiveRecord::Base.logger then
            ActiveRecord::Base.logger.level = saved_level
            ActiveRecord::Base.logger.debug "loaded #{count} records into #{copied_table_name}"
          end
        end
      end


      # add index to copied geoipisp table
      GeoIpLocations.connection.execute("CREATE INDEX #{COPIED_GEOIPISP_INDEX_NAME} ON #{copied_table_name} (company)").check

      # add sequence to copied_jamcompany table
      GeoIpLocations.connection.execute("ALTER TABLE #{copied_jamcompany_table_name} ALTER COLUMN coid DROP DEFAULT").check
      GeoIpLocations.connection.execute("CREATE SEQUENCE #{COPIED_JAMCOMPANY_COID_SEQUENCE} START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1").check
      GeoIpLocations.connection.execute("ALTER SEQUENCE #{COPIED_JAMCOMPANY_COID_SEQUENCE} OWNED BY #{copied_jamcompany_table_name}.coid").check
      GeoIpLocations.connection.execute("ALTER TABLE ONLY #{copied_jamcompany_table_name} ALTER COLUMN coid SET DEFAULT nextval('#{COPIED_JAMCOMPANY_COID_SEQUENCE}'::regclass)").check

      sts = GeoIpLocations.connection.execute("INSERT INTO #{copied_jamcompany_table_name} (company) SELECT DISTINCT company FROM #{copied_table_name} ORDER BY company").check
      ActiveRecord::Base.logger.debug "INSERT INTO #{copied_table_name} returned sts #{sts.cmd_status}" if ActiveRecord::Base.logger
      sts.check

      # add unique index to copied jamcompany table
      GeoIpLocations.connection.execute("CREATE UNIQUE INDEX #{COPIED_JAMCOMPANY_UNIQUE_INDEX} ON #{copied_jamcompany_table_name} (company)").check
      # add primary index to copied jamcompany table
      GeoIpLocations.connection.execute("CREATE UNIQUE INDEX #{COPIED_JAMCOMPANY_PRIMARY_KEY_NAME} ON #{copied_jamcompany_table_name} USING btree (coid)").check
      GeoIpLocations.connection.execute("ALTER TABLE #{copied_jamcompany_table_name} ADD CONSTRAINT #{COPIED_JAMCOMPANY_PRIMARY_KEY_NAME} PRIMARY KEY USING INDEX #{COPIED_JAMCOMPANY_PRIMARY_KEY_NAME}").check

      sts = GeoIpLocations.connection.execute "INSERT INTO #{copied_jamisp_table_name} (beginip, endip, coid) SELECT x.beginip, x.endip, y.coid FROM #{copied_table_name} x, #{copied_jamcompany_table_name} y WHERE x.company = y.company"
      ActiveRecord::Base.logger.debug "INSERT INTO #{copied_jamisp_table_name} returned sts #{sts.cmd_status}" if ActiveRecord::Base.logger
      sts.check

      sts = GeoIpLocations.connection.execute "ALTER TABLE #{copied_jamisp_table_name} DROP COLUMN geom"
      ActiveRecord::Base.logger.debug "DROP COLUMN geom returned sts #{sts.cmd_status}" if ActiveRecord::Base.logger
      #sts.check [we don't care]

      sts = GeoIpLocations.connection.execute "ALTER TABLE #{copied_jamisp_table_name} ADD COLUMN geom geometry(polygon)"
      ActiveRecord::Base.logger.debug "ADD COLUMN geom returned sts #{sts.cmd_status}" if ActiveRecord::Base.logger
      sts.check

      sts = GeoIpLocations.connection.execute "UPDATE #{copied_jamisp_table_name} SET geom = ST_MakeEnvelope(beginip, -1, endip, 1)"
      ActiveRecord::Base.logger.debug "SET geom returned sts #{sts.cmd_tuples}" if ActiveRecord::Base.logger
      sts.check

      # recreate indexes on jamisp
      sts = GeoIpLocations.connection.execute "CREATE INDEX #{COPIED_JAMISP_GEOM_INDEX_NAME} ON #{copied_jamisp_table_name} USING GIST (geom)"
      ActiveRecord::Base.logger.debug "CREATE INDEX #{COPIED_JAMISP_GEOM_INDEX_NAME} returned sts #{sts.cmd_status}" if ActiveRecord::Base.logger
      sts.check

      GeoIpLocations.connection.execute("CREATE INDEX #{COPIED_JAMISP_COID_INDEX_NAME} ON #{copied_jamisp_table_name} (coid)").check

      elapsed = Time.now - start
      @@log.debug("#{copied_jamisp_table_name} import took #{elapsed} seconds")
    end

    def self.after_maxmind_import
      # handle jamisp
      self.connection.execute("DROP TABLE #{self.table_name}").check
      self.connection.execute("ALTER INDEX #{COPIED_JAMISP_GEOM_INDEX_NAME} RENAME TO #{JAMISP_GEOM_INDEX_NAME}").check
      self.connection.execute("ALTER INDEX #{COPIED_JAMISP_COID_INDEX_NAME} RENAME TO #{JAMISP_COID_INDEX_NAME}").check
      self.connection.execute("ALTER TABLE #{self.table_name}_copied RENAME TO #{self.table_name}").check

      # handle geoipisp
      self.connection.execute("DROP TABLE #{GEOIPISP_TABLE}").check
      self.connection.execute("ALTER INDEX #{COPIED_GEOIPISP_INDEX_NAME} RENAME TO #{GEOIPISP_INDEX_NAME}").check
      self.connection.execute("ALTER TABLE #{GEOIPISP_TABLE}_copied RENAME TO #{GEOIPISP_TABLE}").check

      # handle jamcompany
      self.connection.execute("DROP TABLE #{COMPANY_TABLE}").check
      self.connection.execute("ALTER INDEX #{COPIED_JAMCOMPANY_UNIQUE_INDEX} RENAME TO #{JAMCOMPANY_UNIQUE_INDEX}").check
      self.connection.execute("ALTER INDEX #{COPIED_JAMCOMPANY_PRIMARY_KEY_NAME} RENAME TO #{JAMCOMPANY_PRIMARY_KEY_NAME}").check
      self.connection.execute("ALTER SEQUENCE #{COPIED_JAMCOMPANY_COID_SEQUENCE} RENAME TO #{JAMCOMPANY_COID_SEQUENCE}").check
      self.connection.execute("ALTER TABLE #{COMPANY_TABLE}_copied RENAME TO #{COMPANY_TABLE}").check
    end
  end
end
=end