'****************************************************************************
' Created by Chrissy LeMaire (clemaire@gmail.com)
' Website: http://netnerds.net/
'
' This script downloads the GeoLite CSV files from Maxmind.com
' and uses bcp to transform it into SQL Server data. Run this once
' on the second day of each month.
'
' ! - Run importMaxmindGeoIP.sql prior to running this script - !
' ! - Follow the instructions in README.txt or this won't work - !
'
' 1. You must download "unzip.exe" either from my server at
'     http://blog.netnerds.net/code/importMaxmindGeoIP.zip 
'     http://sourceforge.net/projects/gnuwin32 and save it in your 
'     PATH (ie. C:\windows)
'
' 2. You must execute the SQL statements found in the zip
'     http://blog.netnerds.net/code/importMaxmindGeoIP.zip prior to running this
'     script
'
' 3. The SQL Script creates a new database called MaxMindGeoIP
'     as well as some stored procs and UDFs. If you change anything
'     in the SQL script, make sure you update the BCP statements
'     below
'
' 4. Run this script on a machine with BCP and a trusted connection to the
'     SQL Server. If you don't have a trusted connection (a domain), change
'     the BCP parameters below (remove -T and add -u,-p).
'
' 5. Run this script at least once from the console. You may be prompted to 
'     answer yes to the question "Do you wish to run this program?"
'     for both the vbs file and unzip.exe.  Uncheck the box
'     "Always ask before opening this file" and click "Run"
'
' I have only tested this on SQL Server 2005 and Windows Server 2003
'
' "What it does"
' 1. Downloads the GeoLite CSVs from Maxmind.com to the %TEMP% directory
' 2. Unzips the files into the same directory
' 3. Creates the necessary BCP format files
' 4. Executes bcp to bulk import the file
' 5. Executes a stored procedure which adds indexes to the tables and shrinks the DB.
' 6. Deletes all downloaded/extracted/created files once it's done with them
'
' NO WARRANTIES, USE THIS AT YOUR OWN RISK, etc.
'*****************************************************************************
  strSQLServer = "localhost"
  strDBName = "maxmindGeoIP"
  
  Set objShell = CreateObject("WScript.Shell")
  Set objXMLHTTPindex = CreateObject("MSXML2.XMLHTTP")
  Set objXMLHTTPcountry = CreateObject("MSXML2.XMLHTTP")

    
  strIndexURL = "http://www.maxmind.com/download/geoip/database/GeoLiteCity_CSV/"
  strCountryURL = "http://www.maxmind.com/download/geoip/database/GeoIPCountryCSV.zip"
  
  strDownloadDirectory = objShell.ExpandEnvironmentStrings("%TEMP%") & "\"
  
    ' Fetch and parse the index of the GeoLiteCity_CSV directory. Look for the 
    ' proper zip to download
        objXMLHTTPindex.Open "GET", strIndexURL, false
        objXMLHTTPindex.Send()
                If objXMLHTTPindex.Status = 200 Then
                     strParseThis = objXMLHTTPindex.ResponseText                  
                     
                     intLastZip = InStrRev(strParseThis,".zip")+3
                     strParseThis = Left(strParseThis,intLastZip)
                     
                     intStartPosition = InStrRev(strParseThis,"HREF=""GeoLiteCity_")+6
                     intFinishPosition = InStrRev(strParseThis,".zip")+4
                     
                     strFileToDownload = Mid(strParseThis,intStartPosition,intFinishPosition)    
      
                Else
                    MsgBox "The GeoLiteCity zip failed to download."
                    Wscript.quit
                End if
                
        strFileToDownloadURL = strIndexURL & strFileToDownload
        strHDindexLocation = strDownloadDirectory & strFileToDownload
        strHDcountryLocation = strDownloadDirectory & "GeoIPCountryCSV.zip"
       
       'Now that we've got a valid URL (hopefully), let's go grab the zip file
        objXMLHTTPindex.Open "GET", strFileToDownloadURL, false
        objXMLHTTPindex.Send()
 
        'And the country CSV file...
        objXMLHTTPcountry.Open "GET", strCountryURL, false
        objXMLHTTPcountry.Send()
            
                
            If objXMLHTTPindex.Status = 200 And objXMLHTTPcountry.Status = 200 Then 'If both worked, proceed to save the file to the %TEMP% directory
                  Set objADOStream = CreateObject("ADODB.Stream")
                  Set objFSO = Createobject("Scripting.FileSystemObject")
                  Set objConn = CreateObject("ADODB.Connection")
                  strConnstring =  "Driver={SQL Server};Server=" & strSQLServer & ";Database=" & strDBName & ";Trusted_Connection=Yes;"
                  objConn.Open strConnstring
                  objConn.Execute "EXEC usp_prepGeoIPForBCPImport"
                  
                  objADOStream.Open
                  objADOStream.Type = 1 'adTypeBinary    
                  objADOStream.Write objXMLHTTPindex.ResponseBody
                  objADOStream.Position = 0    'Set the stream position to the start                     
                    If objFSO.Fileexists(strHDindexLocation) Then objFSO.DeleteFile strHDindexLocation
                  objADOStream.SaveToFile strHDindexLocation
                  objADOStream.Close
                 
                  objADOStream.Open
                  objADOStream.Type = 1 'adTypeBinary    
                  objADOStream.Write objXMLHTTPcountry.ResponseBody
                  objADOStream.Position = 0    'Set the stream position to the start                     
                    If objFSO.Fileexists(strHDcountryLocation) Then objFSO.DeleteFile strHDcountryLocation
                  objADOStream.SaveToFile strHDcountryLocation
                  objADOStream.Close
                  
                  Call objShell.Run("unzip -oja -d " & strDownloadDirectory & " " & strHDindexLocation,0,True) 'unzip, overwrite, don't create directories, auto convert line breaks
                  objFSO.DeleteFile strHDindexLocation 'delete original zip
                  
                  Call objShell.Run("unzip -oja -d " & strDownloadDirectory & " " & strHDcountryLocation,0,True) 'unzip, overwrite, don't create directories, auto convert line breaks
                  objFSO.DeleteFile strHDcountryLocation 'delete original zip
                  
                  'BCP Madness for Location
                  strLocationFile = strDownloadDirectory & "GeoLiteCity-Location.csv"
                  strLocationBCPFile = strDownloadDirectory & "bcplocation.fmt"                  
                  Call CreateLocationBCPImportFile(strLocationBCPFile)
                  Call ExecuteBCPandDeleteFiles(strDBName,strLocationFile,"geoIPCityLocation",strSQLServer,strLocationBCPFile)

                  'BCP Madness for Blocks
                  strBlocksFile = strDownloadDirectory & "GeoLiteCity-Blocks.csv"
                  strBlocksBCPFile = strDownloadDirectory & "bcpblocks.fmt"
                  Call CreateBlocksBCPImportFile(strBlocksBCPFile)
                  Call ExecuteBCPandDeleteFiles(strDBName,strBlocksFile,"GeoIPCityBlocks",strSQLServer,strBlocksBCPFile)

                  'BCP Madness for Country
                  strCountryFile = strDownloadDirectory & "GeoIPCountryWhois.csv"
                  strCountryBCPFile = strDownloadDirectory & "bcpcountry.fmt"                
                  Call CreateCountryBCPImportFile(strCountryBCPFile)
                  Call ExecuteBCPandDeleteFiles(strDBName,strCountryFile,"GeoIPCountry",strSQLServer,strCountryBCPFile)
                  
                  objConn.CommandTimeout = 1200 'because the script below, which indexes the info on 1.3 million records+, can take a long time to run. 
                  objConn.Execute "EXEC usp_CleanGeoIPAfterBCPImport"
                  objConn.Close
                  
                  Set objConn = Nothing   
                  Set objADOStream = Nothing
                  Set objFSO = Nothing  
                  Set objExec = Nothing
                End if                   

    Set objXMLHTTPindex = Nothing
    Set objXMLHTTPcountry = Nothing
    Set objShell = Nothing

Sub ExecuteBCPandDeleteFiles(strDBName,strCSVFile,strSQLtable,strSQLServer,strBCPFile)
    Set objShellCSV = CreateObject("WScript.Shell")
    Set objFSOCSV = Createobject("Scripting.FileSystemObject") 
    
    'Ensure we get the last record and do not error out  
    Set objAddCrLf = objFSOCSV.OpenTextFile(strCSVFile,8)
        objAddCrLf.WriteLine ""
        objAddCrLf.Close
    Set objAddCrLf = Nothing
      
    Call objShellCSV.Run("bcp " & strDBName & ".dbo." & strSQLtable & " in " & strCSVFile & " -F 3 -S " & strSQLServer & " -T -f " & strBCPFile,0,True)
    objFSOCSV.DeleteFile strCSVFile
    objFSOCSV.DeleteFile strBCPFile
    
    Set objShellCSV = Nothing
    Set objFSOCSV = Nothing               
End Sub

Sub CreateLocationBCPImportFile(strLocationBCPFile)
'I made this compatible for SQL 7 and above but haven't tested it
'on anything other than SQL 2005. BCP sucks with BIGINT for some reason
'so we'll just set those columns to SQLCHAR and let SQL Server do an implicit conversion

    Set objFSOBCP = CreateObject("Scripting.FileSystemObject")      
    Set objBCPImportFile = objFSOBCP.CreateTextFile(strLocationBCPFile,True)
    
    objBCPImportFile.WriteLine "7.0"
    objBCPImportFile.WriteLine "9"
    objBCPImportFile.WriteLine "1       SQLCHAR    0       255       "",\""""    1     locId"
    objBCPImportFile.WriteLine "2       SQLCHAR    0       255       ""\"",\""""    2     country"
    objBCPImportFile.WriteLine "3       SQLCHAR    0       255       ""\"",\""""    3     region"
    objBCPImportFile.WriteLine "4       SQLCHAR    0       255       ""\"",\""""    4     city"
    objBCPImportFile.WriteLine "5       SQLCHAR    0       255       ""\"",""     5     postalCode"
    objBCPImportFile.WriteLine "6       SQLCHAR    0       255       "",""    6     latitude"
    objBCPImportFile.WriteLine "7       SQLCHAR    0       255       "",""    7     longitude"
    objBCPImportFile.WriteLine "8       SQLCHAR    0       255       "",""    8     dmaCode"
    objBCPImportFile.WriteLine "9       SQLCHAR    0       255       ""\n""   9     areaCode"
    
    Set objBCPImportFile = Nothing
    Set objFSOBCP = Nothing
End Sub

Sub CreateBlocksBCPImportFile(strBlocksBCPFile)
    Set objFSOBCP = CreateObject("Scripting.FileSystemObject")      
    Set objBCPImportFile = objFSOBCP.CreateTextFile(strBlocksBCPFile,2)
'Stripping the quotes out of this one was a trip. BCP is geh-toe.
    
    objBCPImportFile.WriteLine "7.0"
    objBCPImportFile.WriteLine "5"
    objBCPImportFile.WriteLine "1       SQLCHAR    0       255       ""\""""     0     firstQuote"
    objBCPImportFile.WriteLine "2       SQLCHAR    0       255       ""\"",\""""     1     startIpNum"
    objBCPImportFile.WriteLine "3       SQLCHAR    0       255       ""\"",\""""     0     endIpNum"
    objBCPImportFile.WriteLine "4       SQLCHAR    0       255       ""\""""    2     locId"
    objBCPImportFile.WriteLine "5       SQLCHAR    0       255       ""\n""    0     lastQuote"
    Set objBCPImportFile = Nothing
    Set objFSOBCP = Nothing
End Sub

Sub CreateCountryBCPImportFile(strCountryBCPFile)
    Set objFSOBCP = CreateObject("Scripting.FileSystemObject")      
    Set objBCPImportFile = objFSOBCP.CreateTextFile(strCountryBCPFile,2)
    
    objBCPImportFile.WriteLine "7.0"
    objBCPImportFile.WriteLine "8"
    objBCPImportFile.WriteLine "1       SQLCHAR    0       255       ""\""""     0     firstQuote"
    objBCPImportFile.WriteLine "2       SQLCHAR    0       255       ""\"",\""""     1     startIp"
    objBCPImportFile.WriteLine "3       SQLCHAR    0       255       ""\"",\""""     2     endIp"
    objBCPImportFile.WriteLine "4       SQLCHAR    0       255       ""\"",\""""     3     startIpNum"
    objBCPImportFile.WriteLine "5       SQLCHAR    0       255       ""\"",\""""     0     endIpNum"
    objBCPImportFile.WriteLine "6       SQLCHAR    0       255       ""\"",\""""     4     countryCode"
    objBCPImportFile.WriteLine "7       SQLCHAR    0       255       ""\""""    5     country"
    objBCPImportFile.WriteLine "8       SQLCHAR    0       255       ""\n""    0     lastQuote"
    Set objBCPImportFile = Nothing
    Set objFSOBCP = Nothing
End Sub