Project: AdServing
/**
 * Mad-Advertisement 
 * Copyright (C) 2011 Thorsten Marx <thmarx@gmx.net> 
 * 
 * This program is free software: you can redistribute it and/or modify it under 
 * the terms of the GNU General Public License as published by the Free Software 
 * Foundation, either version 3 of the License, or (at your option) any later 
 * version. 
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT 
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS 
 * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more 
 * details. 
 * 
 * You should have received a copy of the GNU General Public License along with 
 * this program. If not, see <http://www.gnu.org/licenses/>. 
 */
package net.mad.ads.services.geo; 
 
 
import java.io.BufferedReader; 
 
import java.io.FileReader; 
import java.sql.Connection; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.sql.Statement; 
import java.util.Scanner; 
 
import net.mad.ads.services.geo.helper.ValidateIP; 
 
import org.hsqldb.jdbc.pool.JDBCPooledDataSource; 
 
 
 
import au.com.bytecode.opencsv.CSVReader; 
import biz.source_code.miniConnectionPoolManager.MiniConnectionPoolManager; 
 
 
 
/**
 * Reader zum einlesen 
 *  
 * "ip_start";"country_code";"country_name";"region_code";"region_name";"city"; 
 * "zipcode";"latitude";"longitude";"metrocode" 
 *  
 * @author tmarx 
 *  
 */ 
public class IpinfoLocationDB implements IPLocationDB { 
  
  
 private static final String jdbc_class_hsql = "org.hsqldb.jdbc.JDBCDriver"; 
 private static final String jdbc_url_hsql = "jdbc:hsqldb:"; 
  
// private Connection conn = null; 
  
 private String db; 
  
 private MiniConnectionPoolManager poolMgr = null; 
  
 public IpinfoLocationDB() { 
 } 
  
 public void open(String db) throws ClassNotFoundException, SQLException { 
  this.db = db; 
  Class.forName(jdbc_class_hsql); 
  // conn = DriverManager.getConnection(jdbc_url_hsql + db, "sa", 
  // "citydb"); 
 
  JDBCPooledDataSource datasource = new JDBCPooledDataSource(); 
//  datasource.setUrl(jdbc_url_hsql + db); 
  datasource.setUser("sa"); 
  datasource.setPassword(""); 
  datasource.setDatabase(jdbc_url_hsql + db); 
 
  poolMgr = new MiniConnectionPoolManager(datasource, 50); 
 } 
 
 /**
  * Falls die Datenbank von verschiedenen Services verwendet wird, kann eine 
  * Connection �bergeben werden 
  *  
  * @param con 
  */ 
 public void open(MiniConnectionPoolManager poolmgr) { 
  this.poolMgr = poolmgr; 
 } 
 
 public void close() throws SQLException { 
  poolMgr.dispose(); 
 } 
 
 public void importCountry(String filename) { 
  Connection conn = null; 
  try { 
   conn = poolMgr.getConnection(); 
   conn.setAutoCommit(true); 
   Statement stat = conn.createStatement(); 
    
    
   StringBuilder builder = new StringBuilder(); 
   builder.append("CREATE CACHED TABLE IP_COUNTRY ("); 
      builder.append("ipFROM BIGINT NOT NULL ,"); 
      builder.append("ipTO BIGINT NOT NULL ,"); 
      builder.append("countrySHORT VARCHAR(2),"); 
      builder.append("countryLONG VARCHAR(64),"); 
      builder.append("ipREGION VARCHAR(128),"); 
      builder.append("ipCITY VARCHAR(128),"); 
      builder.append("ipLATITUDE DOUBLE,"); 
      builder.append("ipLONGITUDE DOUBLE,"); 
      builder.append("ipZIPCODE VARCHAR(10),"); 
      builder.append("ipTIMEZONE VARCHAR(8),"); 
      builder.append("PRIMARY KEY(ipFROM, ipTO)"); 
      builder.append(")"); 
       
//      stat.execute("CREATE CACHED TABLE IP_COUNTRY(ip_from BIGINT PRIMARY KEY, ip_to BIGINT, country_code VARCHAR(4), region_name VARCHAR(100), city_name VARCHAR(50), city_zip VARCHAR(20), latitude VARCHAR(30), longitude VARCHAR(30), timezone VARCHAR(8) )"); 
      stat.execute(builder.toString()); 
   stat.close(); 
       
   builder = new StringBuilder(); 
   builder.append("INSERT INTO IP_COUNTRY ("); 
   builder.append("ipFROM,"); 
      builder.append("ipTO,"); 
      builder.append("countrySHORT,"); 
      builder.append("countryLONG,"); 
      builder.append("ipREGION,"); 
      builder.append("ipCITY,"); 
      builder.append("ipLATITUDE,"); 
      builder.append("ipLONGITUDE,"); 
      builder.append("ipZIPCODE,"); 
      builder.append("ipTIMEZONE) "); 
      builder.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); 
    
//   PreparedStatement ps = conn.prepareStatement("INSERT INTO IP_COUNTRY (ip_from, ip_to, country_code, region_name, city_name, city_zip, latitude, longitude, timezone) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"); 
      PreparedStatement ps = conn.prepareStatement(builder.toString()); 
    
   BufferedReader br = new BufferedReader(new FileReader(filename)); 
   CSVReader reader = new CSVReader(br, ',', '\"'); 
    
//   Scanner scanner = new Scanner(new FileReader(filename)); 
//   boolean firstLine = true; 
   int count = 0; 
   String [] values; 
   while ((values = reader.readNext()) != null) { 
    ps.setLong(1, Long.valueOf(values[0])); 
    ps.setLong(2, Long.valueOf(values[1])); 
    ps.setString(3, values[2]); 
    ps.setString(4, values[3]); 
    ps.setString(5, values[4]); 
    ps.setString(6, values[5]); 
    ps.setDouble(7, values[6].equals("-") ? 0 : Double.valueOf(values[6])); 
    ps.setDouble(8, values[7].equals("-") ? 0 : Double.valueOf(values[7])); 
    ps.setString(9, values[8]); 
    ps.setString(10, values[9]); 
 
    ps.execute(); 
    count++; 
   } 
   System.out.println(count + " Eintr�ge importiert"); 
    
   ps.close(); 
  } catch (Exception e) { 
   e.printStackTrace(); 
  } finally { 
   try { 
    conn.close(); 
   } catch (Exception e) { 
    e.printStackTrace(); 
   } 
  } 
 } 
  
 public Location searchIp(String ip) { 
  Connection conn = null; 
  Statement stat = null; 
  try { 
   conn = poolMgr.getConnection(); 
   stat = conn.createStatement(); 
          
         long inetAton = ValidateIP.ip2long(ip); 
          
         String query = "SELECT * FROM IP_COUNTRY WHERE ipFROM <= " + inetAton + " ORDER BY ipFROM DESC LIMIT 1"; 
          
//         String query = "SELECT * FROM IP_COUNTRY WHERE " + inetAton + " BETWEEN ipFROM AND ipTO"; 
          
         ResultSet result = stat.executeQuery(query); 
          
         while (result.next()) { 
          String c = result.getString("countrySHORT"); 
          String rn = result.getString("ipREGION"); 
          String cn = result.getString("ipCITY"); 
           
          String lat = result.getString("ipLATITUDE"); 
          String lng = result.getString("ipLONGITUDE"); 
           
          Location loc = new Location(c, rn, cn, lat, lng); 
          return loc; 
         } 
          
         return Location.UNKNOWN; 
  } catch (Exception e) { 
   e.printStackTrace(); 
  } finally { 
   try { 
    conn.close(); 
    stat.close(); 
   } catch (Exception e) { 
    e.printStackTrace(); 
   } 
  } 
   
  return null; 
 } 
  
 public String mtrim (String text) { 
  if (text.startsWith("\"")) { 
   text = text.substring(1); 
  } 
  if (text.endsWith("\"")) { 
   text = text.substring(0, text.length()-1); 
  } 
  return text; 
 } 
}