create database eprint2; use eprint2; CREATE TABLE `lastproc` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `timeinsert` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `lastproc` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=3958 DEFAULT CHARSET=latin1; CREATE TABLE `view` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `uniquebits` varchar(200) DEFAULT NULL, `archive_name` varchar(50) NOT NULL DEFAULT '', `ip` varchar(100) NOT NULL DEFAULT '', `request_date` date NOT NULL DEFAULT '0000-00-00', `archiveid` int(10) NOT NULL DEFAULT '0', `country_code` varchar(4) DEFAULT NULL, `country_name` varchar(100) DEFAULT NULL, `view_type` ENUM('abstract','download') DEFAULT NULL, `eprint_name` varchar(200) DEFAULT NULL, `latitude` DECIMAL(6,4)DEFAULT NULL, `longitude` DECIMAL(7,4)DEFAULT NULL, `city` varchar(100) DEFAULT NULL, `region` char(2) DEFAULT NULL, `other` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=732629 DEFAULT CHARSET=latin1; flush privileges; grant all privileges on eprint2.* to eprint2priv identified by 'private'; grant select on eprint2.* to eprint2pub identified by 'public'; flush privileges; CREATE TRIGGER `eprint2`.`Location_Finder` BEFORE INSERT ON eprint2.view FOR EACH ROW BEGIN declare ip, ip_1, ip_2, ip_3,ip_4 varchar(100); declare ipd, ipd_1, ipd_2, ipd_3, ipd_4 int(10) unsigned; declare city_n, country_n varchar(100); declare region_n char(2); declare long_n decimal(7,4); declare lat_n decimal(6,4); set ip = new.ip; set ip_1 = SUBSTRING(ip, 1, INSTR(ip, '.') - 1); set ip = SUBSTRING(ip, INSTR(ip, '.') + 1); set ip_2 = SUBSTRING(ip, 1, INSTR(ip, '.') - 1); set ip = SUBSTRING(ip, INSTR(ip, '.') + 1); set ip_3 = SUBSTRING(ip, 1, INSTR(ip, '.') - 1); set ip_4 = SUBSTRING(ip, INSTR(ip, '.') + 1); set ipd_1 = CONVERT(ip_1, unsigned); set ipd_2 = CONVERT(ip_2, unsigned); set ipd_3 = CONVERT(ip_3, unsigned); set ipd_4 = CONVERT(ip_4, unsigned); set ipd_1 = ipd_1 * POW(256, 3); set ipd_2 = ipd_2 * POW(256, 2); set ipd_3 = ipd_3 * 256; set ipd = ipd_1 + ipd_2 + ipd_3 + ipd_4; select geoip.Location.city, geoip.location.region, geoip.country.country, geoip.Location.latitude, geoip.Location.longitude into city_n, region_n, country_n, lat_n, long_n from (geoip.Location inner join geoip.country on geoip.Location.country = geoip.country.cc) where geoip.Location.locId = (select geoip.Blocks.locId from geoip.Blocks where geoip.Blocks.start_ip = (select max(geoip.Blocks.start_ip) from geoip.Blocks where geoip.Blocks.start_ip < ipd)); if(new.country_code like 'X%') then set new.other = new.country_name; else set new.other = 'Normal Usage'; end if; set new.city = city_n; set new.region = region_n; set new.country_name = country_n; set new.longitude = long_n; set new.latitude = lat_n; END; commit; use eprint2;