source: extensions/EStat/lib/statDBCountry.class.inc.php @ 22483

<?php

require_once('statDB.class.inc.php');

/**
 * specific class used to build SQLite country database managment from a binary
 * encoded file
 *
 * binary file format is:
 *  - header signature:  4 bytes = 'IPv4' or 'IPv6'
 *  - file version:      2 bytes = integer value, file version
 *  - number of records: 4 bytes = integer value
 *  - records:           10 bytes if 'IPv4'
 *                       34 bytes if 'IPv6'
 *
 *  a record is range of IP and a country code
 *  - begin IP range: 4 bytes if 'IPv4', 16 bytes if 'IPv6'
 *  - end IP range: 4 bytes if 'IPv4', 16 bytes if 'IPv6'
 *  - country code: 2 bytes (ISO 3166 - http://www.iso.org/iso/country_names_and_code_elements)
 */
class StatDBCountry extends StatDB
{
  const HEADER_SIGNATURE_SIZE=4;
  const VERSION_SIZE=2;
  const NBRECORDS_SIZE=4;
  const HEADER_SIZE=10;
  const COUNTRY_SIZE=2;

  /**
   * constructor
   *
   * @param String $directory: directory where the sqlite file is saved
   * @param String $fileName: file name (without extension)
   */
  public function __construct($directory='', $fileName='')
  {
    parent::__construct($directory, $fileName);
  }


  /**
   * return country for the given IP list
   *
   * @param String|Array $ipList: array of IP or a unique IP
   * @param Boolean $binary: if true (default value), IP must be given as binary
   * @return String|Array: associative array of IP=>country, or a country (depends of $ipList)
   *                        IP format returned is accorded with the $binary value
   */
  public function getIpCountry($ipList, $binary=true)
  {
    if(is_array($ipList))
    {
      $returned=array();

      // encode IP in binary format if needed
      if(!$binary)
      {
        foreach($ipList as $key=>$IP)
        {
          $ipList[self::IPBinaryEncode($IP)]='--';
        }
      }
    }
    else
    {
      $returned='--';

      if(!$binary)
        $ipList=self::IPBinaryEncode($ipList);

      // build a list with a unique item...
      $ipList=array($ipList=>'--');
    }

    if($this->dbHandle==null) return($returned);

    foreach($ipList as $IP=>$val)
    {
      $sql="SELECT country
            FROM ipList
            WHERE :ip BETWEEN rangeStart AND rangeEnd;";

      $sp=$this->dbHandle->prepare($sql);
      $sp->bindValue(':ip', $IP, SQLITE3_BLOB);
      $result=$sp->execute();

      if($result)
      {
        if(!$binary) $IP=self::IPBinaryDecode($IP);
        while($row=$result->fetchArray(SQLITE3_ASSOC))
        {
          if(is_array($returned))
          {
            $returned[$IP]=$row['country'];
          }
          else
          {
            $returned=$row['country'];
          }
        }
      }
    }

    return($returned);
  }


  /**
   * return an array with the following info:
   *  'version' => (integer) the version of the binary file
   *  'nbIP' => (integer) the number of IP range in the binary file
   * return a negative value for file version if an error occurs
   *
   * @param String $fileName
   * @return Array
   */
  public function getIpFileInfo($fileName)
  {
    $returned=array(
        'fileVersion' => -4,
        'nbRecords'   => 0
      );


    if(!file_exists($fileName))
    {
      $returned['fileVersion']=-2;
      return($returned);
    }

    $fHandle=fopen($fileName, 'r');
    if($fHandle)
    {
      $ipFormat=fread($fHandle, self::HEADER_SIGNATURE_SIZE);
      switch($ipFormat)
      {
        case 'IPv4':
        case 'IPv6':
          $fileVersion=self::toUShort(fread($fHandle, self::VERSION_SIZE));
          $nbRecords=self::toULong(fread($fHandle, self::NBRECORDS_SIZE));
          break;
        default:
          $fileVersion=-3;
          break;
      }
      fclose($fHandle);
    }
    return(
      array(
        'fileVersion' => $fileVersion,
        'nbRecords'   => $nbRecords
      )
    );
  }


  /**
   * load a binary file into the database
   *
   * @param String $fileName: file data to load in the database
   * @param Integer $start: offset from loading starts
   * @param Integer $numIp: number of IP to load (0=all)
   * @return Integer: negative: error
   *                  positive: number of IP loaded
   */
  public function loadIpFile($fileName, $start=0, $numIp=0)
  {
    $recordSize=0;
    $IPSize=0;
    $truncateTable=($start==0 or $numIp==0);
    $completeIP='';

    if($this->dbHandle==null) return(-1);
    if(!file_exists($fileName)) return(-2);

    $fHandle=fopen($fileName, 'r');
    if($fHandle)
    {
      // check record size from IP binary format
      $ipFormat=fread($fHandle, self::HEADER_SIGNATURE_SIZE);
      switch($ipFormat)
      {
        case 'IPv4':
          $IPSize=4;
          $completeIP="\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00";
          break;
        case 'IPv6':
          $IPSize=16;
          break;
        default:
          fclose($fHandle);
          return(-3);
          break;
      }

      $fileVersion=self::toUShort(fread($fHandle, self::VERSION_SIZE));

      $recordSize=2*$IPSize+self::COUNTRY_SIZE;


      // calculate start reading position & buffer size
      if($numIp<=0)
      {
        $bufferSize=filesize($fileName)-self::HEADER_SIZE;
      }
      else
      {
        $bufferSize=$numIp*$recordSize;
      }





      $start=self::HEADER_SIZE+$start*$recordSize;


      fseek($fHandle, $start);
      $buffer=fread($fHandle, $bufferSize);
      fclose($fHandle);

      $buffer=str_split($buffer, $recordSize);

      //start transaction
      $this->startTransac();

      if($truncateTable) $this->truncateTable();

      if($this->getInfoProperty('log', 'startImport')==null)
        $this->setInfoProperty('log', 'startImport', date('Y-m-d H:i:s'));

      $this->setInfoProperty('nfo', 'ipFileVersion', $fileVersion);

      foreach($buffer as $record)
      {
        $sql="INSERT INTO ipList (rangeStart, rangeEnd, country)
                VALUES(:ips, :ipe, '".$this->dbHandle->escapeString(substr($record, -self::COUNTRY_SIZE))."')";

        $sp=$this->dbHandle->prepare($sql);
        $sp->bindValue(':ips', $completeIP.substr($record, 0, $IPSize), SQLITE3_BLOB);
        $sp->bindValue(':ipe', $completeIP.substr($record, $IPSize, $IPSize), SQLITE3_BLOB);
        $sp->execute();
      }

      if($numIp==0 or count($buffer)<$numIp)
      {
        // file is completely loaded !
        $this->buildInfos();
        $this->setInfoProperty('log', 'stopImport', date('Y-m-d H:i:s'));
      }
      else
      {
        $sql="
          REPLACE INTO info (domain, key, value)
          SELECT 'nfo', 'numberOfIP', COUNT(*)
          FROM ipList;
        ";
        $this->dbHandle->exec($sql);
      }

      // commit all changes
      $this->stopTransac();

      return(count($buffer));
    }

    return(-4);
  }


  /**
   * delete all the content of the ipList table
   *
   * @return Boolean
   */
  protected function truncateTable()
  {
    $value=$this->getInfoProperty('nfo', 'numberOfIP').'/'.$this->getInfoProperty('nfo', 'numberOfCountry');

    $sql="
      INSERT INTO info (domain, key, value)
        VALUES('log', 'truncate(".date('Y-m-d H:i:s').")', '$value');

      DELETE FROM ipList;
    ";
    return($this->dbHandle->exec($sql));
  }


  /**
   * set the following info in 'nfo' domain:
   *  - 'numberOfIP'
   *  - 'numberOfCountry'
   *  - 'importDate'
   */
  protected function buildInfos()
  {
    if($this->dbHandle==null) return(false);

    $sql="
        REPLACE INTO info (domain, key, value)
          SELECT 'nfo', 'numberOfIP', COUNT(*)
          FROM ipList;

        REPLACE INTO info (domain, key, value)
          SELECT 'nfo', 'numberOfCountry', COUNT(DISTINCT country)
          FROM ipList;

        REPLACE INTO info (domain, key, value)
          VALUES ('nfo', 'importDate', '".date('Y-m-d')."');
    ";

    return($this->dbHandle->exec($sql));
  }


  /**
   * check the database schema; update it if needed
   *
   * @return Boolean: true if OK, otherwise false
   */
  protected function checkSchema()
  {
    if(!parent::checkSchema()) return(false);

    $version=$this->getInfoProperty('nfo', 'version');

    switch($version)
    {
      case '00.00.00':
        // version 00.00.00
        //  file is just created...
        $result=$this->getDBInfo(ASDF_DB_TYPE_TABLE, 'files', ASDF_DB_INFO);
        if(count($result)==0)
        {
          $this->dbHandle->exec(
              "CREATE TABLE 'ipList' (
                  'rangeStart' BLOB,
                  'rangeEnd' BLOB,
                  'country' TEXT
               );"
            );
          $this->dbHandle->exec("CREATE UNIQUE INDEX 'byRange' on iplist (rangeStart ASC)");
        }

        $this->setInfoProperty('nfo', 'version', '01.00.00');
        $this->setInfoProperty('nfo', 'ipFileVersion', 0);
        return(true);
        break;
      default:
        break;
    }
    return(false);
  }

} // class


?>