DROP PROCEDURE IF EXISTS `cSpatials_Country`;
## Written by Richie Bartlett (LoreZyra)
## Stored Procedure for MySQL 5.0
## Purpose: to process imported csv data in GeoIPCountryWhois from MaxMind.com - CountryBlocks;
## Will automatically create the spatial data to enhance searching performance;
## Version: 1.2
## NOTE: error handling NOT supported in MySQL 5.0, but MySQL 5.5 does support...
DELIMITER //
CREATE DEFINER=`rirodev1`@`localhost` PROCEDURE rblogger0.`cSpatials_Country`(
#IN tableFrom nVARCHAR(255),
#IN tableTo nVARCHAR(255),
OUT outMsg VARCHAR(255)
) COMMENT 'process csv table from GeoIPCountryWhois.csv [creates spatial data]'
BEGIN
#DECLARE err int;
DECLARE g polygon;
DECLARE gt VARCHAR(255);
DECLARE done INT DEFAULT 0;
DECLARE IPStart INT;
DECLARE IPEnd INT;
DECLARE blnFlag1 TINYINT(1);
DECLARE blnFlag2 TINYINT(1);
DECLARE ISO CHAR(2);
DECLARE strText VARCHAR(255);
DECLARE cur1 CURSOR FOR SELECT `start`,`end`,`cc`FROM rblogger0.`csv`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET outMsg:='Error: required tables dont exist!';#DEFAULT err msg
#SET blnFlag1:=table_exist('rblogger0',@tableFrom);#csv
#SET blnFlag2:=table_exist('rblogger0',@tableTo); #geoCountryIP
SET blnFlag1:=table_exist('rblogger0','csv');
SET blnFlag2:=table_exist('rblogger0','geoCountryIP');
#IF blnFlag2=FALSE AND @tableFrom<>@tableTo THEN
IF blnFlag2=FALSE THEN
## auto create missing table!
CREATE TABLE `geoCountryIP` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`ipPoly` POLYGON NOT NULL, SPATIAL INDEX(ipPoly),
`IPnumStart` int( 16 ) unsigned NOT NULL COMMENT 'calculated IP number',
INDEX(`IPnumStart`),
`IPnumEnd` int( 16 ) unsigned NOT NULL COMMENT 'calculated IP number',
`ISO3166` char( 2 ) collate utf8_unicode_ci default NULL
) ENGINE = MyISAM DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;
SET blnFlag2:=1;
SET outMsg:='Note: created destination Table';
END IF;#table_exist
#IF blnFlag1=TRUE AND blnFlag2=TRUE AND @tableFrom<>@tableTo THEN
IF blnFlag1=TRUE AND blnFlag2=TRUE THEN
#TRUNCATE TABLE @tableTo; #rblogger0.geoCountryIP2;#empty input table
TRUNCATE TABLE `rblogger0`.`geoCountryIP`;#empty input table
OPEN cur1;
START TRANSACTION;
read_loop: LOOP
FETCH cur1 INTO IPStart,IPEnd,ISO;
IF done THEN LEAVE read_loop; END IF;
SELECT CONCAT('POLYGON(LINESTRING(POINT(',IPStart,',-1), POINT(',IPEnd,',-1), POINT(',IPEnd,',1), POINT(',IPStart,',1), POINT(',IPStart,',-1)))') INTO gt;
#SELECT GeomFromText(gt) INTO g;
IF gt IS NULL THEN LEAVE read_loop; END IF;
IF gt IS NOT NULL THEN
SELECT GeomFromWKB(POLYGON(LINESTRING(POINT(IPStart,-1), POINT(IPEnd,-1), POINT(IPEnd,1), POINT(IPStart,1), POINT(IPStart,-1)))) INTO g;
#INSERT INTO @tableTo values('', g, IPStart, IPEnd, ISO);
INSERT INTO `geoCountryIP` values('', g, IPStart, IPEnd, ISO);
END IF;
#SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END;
#SELECT @err = coalesce(nullif(@err, 0), @@error)
#IF @err <> 0 THEN LEAVE read_loop; END IF;
END LOOP;
CLOSE cur1;
COMMIT;# TRANSACTION;
#SET strText:='Processed import from GeoIPCountryWhois.csv';
SELECT CONCAT(strText,' ',NOW()) INTO strText;
#ALTER TABLE @tableTo COMMENT = strText;
ALTER TABLE `geoCountryIP` COMMENT = 'Processed import from GeoIPCountryWhois.csv';
DROP TABLE `csv`;
END IF;#table_exist
SET outMsg:=gt;
END; # // #PROCEDURE
DELIMITER;
########################################################################
CALL cSpatials_Country(
'csv','geoCountryIP',@returnMsg
);
CALL cSpatials_Country(@returnMsg);
SELECT @returnMsg;
########################################################################
DROP PROCEDURE IF EXISTS `cSpatials_IPblocks`;
## Written by Richie Bartlett (LoreZyra)
## Stored Procedure for MySQL 5.0
## Purpose: to process imported csv data in GEOipTABLE from MaxMind.com - IPBlocks;
## Will automatically create the spatial data to enhance searching performance;
## Version: 1.0
## NOTE: error handling NOT supported in MySQL 5.0, but MySQL 5.5 does support...
DELIMITER //
CREATE DEFINER=`rirodev1`@`localhost` PROCEDURE rblogger0.`cSpatials_IPblocks`(
OUT outMsg VARCHAR(255)
) COMMENT 'process csv table from GeoLiteCity-Blocks.csv [creates spatial data]'
BEGIN
#DECLARE err int;
DECLARE g polygon;
DECLARE gt VARCHAR(255);
DECLARE done INT DEFAULT 0;
DECLARE IPStart INT;
DECLARE IPEnd INT;
DECLARE blnFlag1 TINYINT(1);
DECLARE blnFlag2 TINYINT(1);
DECLARE Loc INT;
DECLARE strText VARCHAR(255);
DECLARE cur1 CURSOR FOR SELECT `start`,`end`,`ci` FROM rblogger0.`ip`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET outMsg:='Error: required tables dont exist!';#DEFAULT err msg
SET blnFlag1:=table_exist('rblogger0','ip');
SET blnFlag2:=table_exist('rblogger0','geoIPblocks');
IF blnFlag2=FALSE THEN
## auto create missing table!
CREATE TABLE `geoIPblocks` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`ipPoly` POLYGON NOT NULL, SPATIAL INDEX(ipPoly),
`IPnumStart` int( 16 ) unsigned NOT NULL COMMENT 'calculated IP number',
`IPnumEnd` int( 16 ) unsigned NOT NULL COMMENT 'calculated IP number',
INDEX(`IPnumStart`),
`locID` bigint( 20 ) default 0 NOT NULL COMMENT 'ref to geoCityLoc.id'
) ENGINE = MyISAM DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;
SET blnFlag2:=1;
SET outMsg:='Note: created destination Table';
END IF;#table_exist
IF blnFlag1=TRUE AND blnFlag2=TRUE THEN
TRUNCATE TABLE `rblogger0`.`geoIPblocks`;#empty input table
OPEN cur1;
START TRANSACTION;
read_loop: LOOP
FETCH cur1 INTO IPStart,IPEnd,Loc;
IF done THEN LEAVE read_loop; END IF;
SELECT CONCAT('POLYGON(LINESTRING(POINT(',IPStart,',-1), POINT(',IPEnd,',-1), POINT(',IPEnd,',1), POINT(',IPStart,',1), POINT(',IPStart,',-1)))') INTO gt;
#SELECT GeomFromText(gt) INTO g;
IF gt IS NULL THEN LEAVE read_loop; END IF;
IF gt IS NOT NULL THEN
SELECT GeomFromWKB(POLYGON(LINESTRING(POINT(IPStart,-1), POINT(IPEnd,-1), POINT(IPEnd,1), POINT(IPStart,1), POINT(IPStart,-1)))) INTO g;
INSERT INTO `geoIPblocks` values('', g, IPStart, IPEnd, Loc);
END IF;
#SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END;
#SELECT @err = coalesce(nullif(@err, 0), @@error)
#IF @err <> 0 THEN LEAVE read_loop; END IF;
END LOOP;
CLOSE cur1;
COMMIT;# TRANSACTION;
ALTER TABLE `geoIPblocks` COMMENT = 'Processed import from GeoLiteCity-Blocks.csv';
DROP TABLE `ip`;
END IF;#table_exist
SET outMsg:=gt;
END; # // #PROCEDURE
DELIMITER;
########################################################################
CALL cSpatials_IPblocks(@returnMsg);
SELECT @returnMsg;
########################################################################
DROP FUNCTION IF EXISTS `table_exist`;
## Written by Richie Bartlett (LoreZyra)
## Stored Function for MySQL 5.0
## Purpose: to determine if a table exists;
## Version: 1.0
DELIMITER //
CREATE FUNCTION `table_exist`
(
s_database_name nvarchar(255),
s_table_name nvarchar(255)
)
RETURNS TINYINT
BEGIN
DECLARE existFlag TINYINT DEFAULT 0;
IF EXISTS (
SELECT 1 FROM Information_schema.tables
WHERE TABLE_TYPE ='BASE TABLE' AND TABLE_NAME = s_table_name AND TABLE_SCHEMA = s_database_name
) THEN SET existFlag:=1;
end if;
RETURN existFlag;
END//
DELIMITER ;
########################################################################
Set @tableFlag:=table_exist('rblogger0','geoCountryIP');
SELECT @tableFlag;
########################################################################
CREATE TABLE `rblogger0`.`geoCityLoc` (
`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`ISO3166` VARCHAR( 4 ) NULL ,
`ISO3166_2` VARCHAR( 2 ) NULL ,
`cityName` VARCHAR( 255 ) NULL ,
`PostalCode` VARCHAR( 16 ) NULL ,
`lat` DECIMAL( 11, 7 ) NOT NULL DEFAULT '0',
`lng` DECIMAL( 11, 7 ) NOT NULL DEFAULT '0',
`metroCode` VARCHAR( 16 ) NULL ,
`areaCode` VARCHAR( 16 ) NULL
) ENGINE = MYISAM COMMENT = 'GeoLiteCity-Location.csv'
########################################################################
DROP PROCEDURE IF EXISTS `prepMaxMindImport`;
## Written by Richie Bartlett (LoreZyra)
## Stored PROCEDURE for MySQL 5.0
## Purpose: prepare to import CSV data from MaxMind.com;
## Version: 1.0
DELIMITER //
CREATE DEFINER=`rirodev1`@`localhost` PROCEDURE rblogger0.`prepMaxMindImport`(
) COMMENT 'Prepare tables for csv data from MaxMind.com'
BEGIN
DROP TABLE IF EXISTS `csv`;
CREATE TABLE `csv` (
`start_ip` char(15)NOT NULL,
`end_ip` char(15)NOT NULL,
`start` int(10) unsigned NOT NULL,
`end` int(10) unsigned NOT NULL,
`cc` char(2) NOT NULL,
`cn` varchar(64) NOT NULL
) COMMENT 'GeoIPCountryWhois.csv';
DROP TABLE IF EXISTS `ip`;
CREATE TABLE `ip` (
`start` int(10) unsigned NOT NULL,
`end` int(10) unsigned NOT NULL,
`ci` tinyint(3) unsigned NOT NULL
) COMMENT 'GeoLiteCity-Blocks.csv';
END // #PROCEDURE
DELIMITER;
########################################################################
DROP PROCEDURE IF EXISTS `resetWebAnalytics`;
## Written by Richie Bartlett (LoreZyra)
## Stored PROCEDURE for MySQL 5.0
## Purpose: prepare to import CSV data from MaxMind.com;
## Version: 1.0
DELIMITER //
CREATE DEFINER=`rirodev1`@`localhost` PROCEDURE rblogger0.`resetWebAnalytics`(
) COMMENT 'dumps/resets all web tracking data'
BEGIN
TRUNCATE TABLE `rblogger0`.`siteReferrer`;
TRUNCATE TABLE `rblogger0`.`siteTracker`;
TRUNCATE TABLE `rblogger0`.`site_stats_date`;
TRUNCATE TABLE `rblogger0`.`site_stats_hour`;
TRUNCATE TABLE `rblogger0`.`site_stats_month`;
TRUNCATE TABLE `rblogger0`.`site_stats_year`;
TRUNCATE TABLE `rblogger0`.`site_stat_Counter`;
END // #PROCEDURE
DELIMITER;
Richie Bartlett, Jr.
createSpatials.sql
-
990
-
8310
-
0
-
0
-
1
This license allows reusers to distribute, remix, adapt, and build upon the material in any medium or format, so long as attribution is given to the creator. The license allows for commercial use. If you remix, adapt, or build upon the material, you must license the modified material under identical terms.
- 1.1k
- 1.1k
