/*
Created by Chrissy LeMaire (clemaire@gmail.com)
Website: http://netnerds.net/

! - Run this prior to running importMaxmindGeoIP.vbs - !

This script
1. Creates a database named maxmindGeoIP. IF YOU ALREADY HAVE A DB NAMED MAXMINDGEOIP, THIS SCRIPT WILL DROP IT.
2. Creates a 'bcp prep' stored procedure which creates the necessary tables
    - This stored proc is run each time the importMaxmindGeoIP.vbs file is run
3. Creates another bcp related stored procedure which creates the necessary indexes, defrags the indexes and
   shrinks the database. The database is usually about the same size as the CSV (~65MB as of Jan07) 
    - This stored proc is run each time the importMaxmindGeoIP.vbs file is run
4. Creates a few sample stored procedures and user-defined functions
5. Allows guest accounts to connect to and read the db.
6. The bottom of this file contains example usage such as
    -Select '208.65.15.0' as IP,maxmindGeoIP.dbo.IPtoLocation('204.65.15.0') as ipLocation
7. Remember that SQL UDFs and Stored Procedures are pre-compiled so the results will take a little longer the 
   first time the function or procedure is run.   

NO WARRANTIES, USE THIS AT YOUR OWN RISK, etc.
*/
USE master
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'maxmindGeoIP')
BEGIN
ALTER DATABASE maxmindGeoIP SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE maxmindGeoIP
END
GO
CREATE DATABASE maxmindGeoIP
GO
IF DATABASEPROPERTYEX('maxmindGeoIP', 'Recovery') != 'SIMPLE'
    ALTER DATABASE maxmindGeoIP 
        SET RECOVERY SIMPLE -- The Full recovery model is unncessary for 
                            -- data which is imported monthly. 
GO
USE maxmindGeoIP
GO

CREATE PROC usp_prepGeoIPForBCPImport 
AS
-- EXEC sp_dboption 'maxmindGeoIP', 'read only', 'FALSE'

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.geoIPCityLocation') AND type in (N'U'))
DROP TABLE dbo.geoIPCityLocation

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.geoIPCityBlocks') AND type in (N'U'))
DROP TABLE dbo.geoIPCityBlocks

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.geoIPCountry') AND type in (N'U'))
DROP TABLE dbo.geoIPCountry

CREATE TABLE dbo.geoIPCityLocation(
    locID int NOT NULL,
    country nvarchar(255) NULL,
    region varchar(255)  NULL,
    city nvarchar(255) NULL,
    postalCode varchar(20) NULL,
    latitude varchar(255) NULL,
    longitude varchar(255) NULL,
    dmaCode varchar(255) NULL,
    areaCode varchar(255) NULL
    )
CREATE TABLE dbo.geoIPCityBlocks(
    startIpNum bigint NULL,
    locID int NULL
    )
CREATE TABLE dbo.geoIPCountry(
    startIp varchar(20) NULL,
    endIp varchar(20) NULL,
    startIpNum bigint NULL,
    countryCode char(2) NULL,
    country nvarchar(50) NULL
)
GO


CREATE PROC usp_CleanGeoIPAfterBCPImport 
AS
-- We can index the data extensively because it's read only
    CREATE CLUSTERED INDEX IDX_countryStarIPtNum ON dbo.GeoIPCountry 
    (
        startIpNum ASC
    ) WITH FILLFACTOR = 100

    CREATE NONCLUSTERED INDEX IDX_CountryCode ON dbo.GeoIPCountry 
    (
        countryCode ASC, country ASC
     ) WITH FILLFACTOR = 100

    CREATE CLUSTERED INDEX IDX_blocksStartIpNum ON dbo.GeoIPCityBlocks 
    (
        startIPNum ASC
        
    ) WITH FILLFACTOR = 100

    CREATE CLUSTERED INDEX IDX_LocationID ON dbo.geoIPCityLocation 
    (
        locid ASC
    ) WITH FILLFACTOR = 100

DBCC INDEXDEFRAG (maxmindgeoip,GeoIPCityBlocks)
DBCC INDEXDEFRAG (maxmindgeoip,geoIPCityLocation)
DBCC INDEXDEFRAG (maxmindgeoip,GeoIPCountry)

-- Bare minimum for guest access
GRANT CONNECT TO [guest] 
GRANT EXECUTE ON dbo.IPtoLocation TO [public]
GRANT EXECUTE ON dbo.usp_IPtoLocation TO [public]

-- If you want other accounts to be able to directly access data 
-- and create their own stored procs, etc.
GRANT EXECUTE ON dbo.IPtoNumeric TO [public]
GRANT EXECUTE ON dbo.CcodeToCountry TO [public]
GRANT SELECT ON dbo.geoIPCityLocation TO [public]
GRANT SELECT ON dbo.GeoIPCityBlocks TO [public]
GRANT SELECT ON dbo.GeoIPCountry TO [public]

DBCC SHRINKDATABASE (maxmindGeoIP)
-- EXEC sp_dboption 'maxmindGeoIP', 'read only', 'TRUE'

GO

/* PRIMARY FUNCTIONS / STORED PROCEDURES */
CREATE FUNCTION dbo.IPtoLocation  (@strIP varchar(15))  
RETURNS nvarchar(255)
AS  
BEGIN
DECLARE @intIPNum bigint
DECLARE @strLocation nvarchar(255)
DECLARE @strCountry nvarchar(55)
        IF (LEN(@strIP)-LEN(REPLACE(@strIP,'.','')))/LEN('.') = 3 -- if there 3 periods      
        SET @intIPNum = (16777216 * CAST(PARSENAME(@strIP,4) as bigint)  + 65536 * PARSENAME(@strIP,3) + 256 * PARSENAME(@strIP,2) + PARSENAME(@strIP,1))

        SET @strLocation = (SELECT top 1 city + '.' + region + '.' + country from
		geoIPCityLocation WHERE locid = (SELECT TOP 1 locid FROM GeoIPCityBlocks
		WHERE @intIPNum >= startIpNum ORDER BY startIpNum DESC) AND city IS NOT NULL)

        IF PARSENAME(@strLocation,1) not in ('US','CA')
        SET @strLocation = PARSENAME(@strLocation,3) + ', ' + dbo.CCodeToCountry(PARSENAME(@strLocation,1))
        ELSE
        SET @strLocation = PARSENAME(@strLocation,3) + ', ' + PARSENAME(@strLocation,2)  + ' ' + PARSENAME(@strLocation,1)
        
        IF @strLocation IS NULL 
        BEGIN
            SET @strCountry = (SELECT TOP 1 country FROM GeoIPCountry WHERE @intIPNum >= startIpNum ORDER BY startIpNum DESC)
            If @strCountry is NULL
            SET @strLocation = 'Unknown or Unassigned'
            ELSE
            SET @strLocation = @strCountry
        END
        
RETURN @strLocation
END
GO

CREATE PROC dbo.usp_IPtoLocation  (@strIP varchar(15))  
AS  
BEGIN
DECLARE @intIPNum bigint
DECLARE @strLocation nvarchar(255)
DECLARE @strCountry nvarchar(55)

        IF (LEN(@strIP)-LEN(REPLACE(@strIP,'.','')))/LEN('.') = 3 -- if there 3 periods     
        SET @intIPNum = (16777216 * CAST(PARSENAME(@strIP,4) as bigint)  + 65536 * PARSENAME(@strIP,3) + 256 * PARSENAME(@strIP,2) + PARSENAME(@strIP,1))

        SET @strLocation = (SELECT top 1 city + '.' + region + '.' + country from 
		geoIPCityLocation WHERE locid = (SELECT top 1 locid FROM GeoIPCityBlocks
		WHERE @intIPNum >= startIpNum ORDER BY startIpNum DESC) AND city IS NOT NULL)

        IF PARSENAME(@strLocation,1) not in ('US','CA')
        SET @strLocation = PARSENAME(@strLocation,3) + ', ' + dbo.CCodeToCountry(PARSENAME(@strLocation,1)) 
        ELSE
        SET @strLocation = PARSENAME(@strLocation,3) + ', ' + PARSENAME(@strLocation,2)  + ' ' + PARSENAME(@strLocation,1) 

        IF @strLocation IS NULL
        BEGIN
            SET @strCountry = (SELECT top 1 country FROM GeoIPCountry WHERE @intIPNum >= startIpNum ORDER BY startIpNum DESC)
            IF @strCountry is NULL
            SET @strLocation = 'Unknown or Unassigned'
            ELSE
            SET @strLocation = @strCountry
        END        
SELECT @strIP as ipAddr,@strLocation as Location
END
GO


/* SUPPORTING FUNCTIONS AND COUNTRY DATA */
CREATE      FUNCTION dbo.IPtoNumeric  (@strIP varchar(15))  
RETURNS bigint
AS  
BEGIN
DECLARE @intIPNum varchar(255)
        IF (LEN(@strIP)-LEN(REPLACE(@strIP,'.','')))/LEN('.') = 3   -- if there 3 periods   
        SET @intIPNum = (16777216 * CAST(PARSENAME(@strIP,4) as bigint) + 65536 * PARSENAME(@strIP,3) + 256 * PARSENAME(@strIP,2) + PARSENAME(@strIP,1))
RETURN @intIPNum
END
GO

CREATE FUNCTION dbo.CCodeToCountry  (@strCCode varchar(2))  
RETURNS nvarchar(42)
AS  
BEGIN
DECLARE @Country nvarchar(42)
          
        SET @Country = (select distinct country from GeoIPCountry where countryCode = @strCCode)
        IF LEN(@Country) = 0 SET @Country = @strCCode

RETURN @Country
END
GO

/* EXAMPLE USAGE
SELECT maxmindGeoIP.dbo.IPtoLocation('24.0.175.222') as ipLocation
GO
Select maxmindGeoIP.dbo.IPtoNumeric('24.0.175.222') as numericIP
GO
EXEC maxmindGeoIP.dbo.usp_IPtoLocation '208.65.15.0'
GO

--------- Check one subnet ----------
    DECLARE @i int
    DECLARE @ipaddr varchar(15)
    DECLARE @startTime datetime
    
    SET @startTime = getDate()
    SET @i = 0
    
    While @i < 50
    BEGIN

        SET @ipaddr = '204.4.'+ CAST(@i as varchar(3)) + '.0'
        PRINT @ipaddr + ' '+ maxmindGeoIP.dbo.IPtoLocation(@ipaddr) 
        SELECT @i = @i+1
    END

PRINT 'Your query took '+CAST(DATEDIFF(ms,@startTime,GETDATE()) AS varchar)+' milliseconds to run.'
GO

*/