224 lines
9.9 KiB
Ruby
224 lines
9.9 KiB
Ruby
require 'ipaddr'
|
|
=begin
|
|
|
|
module JamRuby
|
|
class JamIsp < ApplicationRecord
|
|
|
|
# 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
|