source: extensions/EStat/lib/statDB.class.inc.php @ 17737

<?php

include_once(PHPWG_PLUGINS_PATH.'GrumPluginClasses/classes/GPCCompress.class.inc.php');

define('ASDF_DELETE_PACKED',   0x01);
define('ASDF_DELETE_UNPACKED', 0x02);

define('ASDF_EXIST_NO',       0x00);
define('ASDF_EXIST_PACKED',   0x01);
define('ASDF_EXIST_UNPACKED', 0x02);
define('ASDF_EXIST_ALL', ASDF_EXIST_PACKED|ASDF_EXIST_UNPACKED);

define('ASDF_FILE_EXT', '.db');
define('ASDF_FILE_EXT_ZIP', ASDF_FILE_EXT.'.gz');
define('ASDF_FILE_ROOT_MONTH', 'M');
define('ASDF_FILE_ROOT_GLOBAL', 'G');


define('ASDF_DB_INFO',      0x01);
define('ASDF_DB_NUM_ROWS',  0x02);
define('ASDF_DB_LIST',  0x03);

define('ASDF_DB_TYPE_TABLE', 0x01);
define('ASDF_DB_TYPE_INDEX', 0x02);
define('ASDF_DB_TYPE_TRIGGER', 0x03);
define('ASDF_DB_TYPE_LOG', 0x04);
define('ASDF_DB_TYPE_NFO', 0x05);

define('ASDF_TRANSAC_COMMIT', 0x01);
define('ASDF_TRANSAC_ROLLBACK', 0x02);

define('ASDF_GET_ROWS', 0x01);
define('ASDF_GET_COUNT', 0x02);

define('ASDF_BUILD_DONE', 0x01);
define('ASDF_BUILD_MISSING', 0x02);
define('ASDF_BUILD_ALL', ASDF_BUILD_DONE|ASDF_BUILD_MISSING);

define('ASDF_OPEN_READ', 0x01);
define('ASDF_OPEN_WRITE', 0x02);

define('ASDF_CLOSE_DO_NOTHING', 0x00);
define('ASDF_CLOSE_DO_PACK', 0x01);
define('ASDF_CLOSE_DO_DELETE', 0x02);

/**
 * main class for SQLite files managment
 */
class StatDB
{
  protected $fileRootName='';
  protected $fileDir='';
  protected $fileName='';
  protected $dbHandle=null;
  protected $transacOpen=false;
  protected $ipCountryFile='';

  private $deleteUnpackedWhenClose=ASDF_CLOSE_DO_NOTHING;

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

  public function __destruct()
  {
   $this->close();
  }


  /**
   * return informations about the current data file
   *
   * @return Array
   */
  public function getInfo()
  {
    $returned=array(
      'dirName'       => $this->fileDir,
      'fileName'      => $this->fileName,
      'fileExist'     => $this->fileExist(),
      'open'          => ($this->dbHandle==null)?false:true,
      'unpackedFile'  => array(
                            'fileName' => $this->getFileName(ASDF_EXIST_UNPACKED),
                            'fileSize' => ($this->getFileName(ASDF_EXIST_UNPACKED, true)!='')?filesize($this->getFileName(ASDF_EXIST_UNPACKED)):0
                          ),
      'packedFile'    => array(
                            'fileName' => $this->getFileName(ASDF_EXIST_PACKED),
                            'fileSize' => ($this->getFileName(ASDF_EXIST_PACKED, true)!='')?filesize($this->getFileName(ASDF_EXIST_PACKED)):0
                          )
    );

    if($this->dbHandle!=null)
    {
      $tables=$this->getDBInfo(ASDF_DB_TYPE_TABLE, null, ASDF_DB_LIST);

      $returned['nbRecords']=array();
      foreach($tables as $table)
      {
        $returned['nbRecords'][$table] = $this->getDBInfo(ASDF_DB_TYPE_TABLE, $table, ASDF_DB_NUM_ROWS);
      }
      $returned['db']=array(
                        'integrity'    => $this->getPragma('integrity_check'),
                        'pages'        => $this->getPragma('page_count'),
                        'pageSize'     => $this->getPragma('page_size'),
                        'unusedPages'  => $this->getPragma('freelist_count'),
                        'version(s)'   => $this->getPragma('schema_version'),
                        'version(u)'   => $this->getPragma('user_version')
                      );


      $logs=$this->getDBInfo(ASDF_DB_TYPE_LOG, null, null);
      $returned['log']=array();
      foreach($logs as $log)
      {
        $returned['log'][$log['key']] = $log['value'];
      }


      $nfos=$this->getDBInfo(ASDF_DB_TYPE_NFO, null, null);
      $returned['nfo']=array();
      foreach($nfos as $nfo)
      {
        $returned['nfo'][$nfo['key']] = $nfo['value'];
      }

    }
    return($returned);
  }

  /**
   * set the directory
   *
   * @param String $directory: the directory
   * @return String: directory set
   */
  public function setDirectory($directory)
  {
    if(substr($directory,-1)!='/') $directory.='/';

    // check if directory is valid
    if(file_exists($directory) and !$this->isOpen())
    {
      $this->fileDir=$directory;
    }
    return($this->fileDir);
  }

  /**
   * set the file name
   *
   * @param String $fileName: file name
   * @return String: file name
   */
  public function setFileName($fileName)
  {
    if($this->fileName!=$fileName)
    {
      if($this->close()) $this->fileName=$fileName;
    }
    return($this->fileName);
  }

  /**
   * return information, if set file exists or not
   *
   * @return Integer : 0x00 not exist (ASDF_EXIST_NO)
   *                   0x01 exist, packed (ASDF_EXIST_PACKED)
   *                   0x02 exist, unpacked ASDF_EXIST_UNPACKED
   */
  public function fileExist()
  {
    $returned=ASDF_EXIST_NO;
    if($this->getFileName(ASDF_EXIST_UNPACKED)!='')
      $returned=$returned | ASDF_EXIST_UNPACKED;

    if($this->getFileName(ASDF_EXIST_PACKED)!='')
      $returned=$returned | ASDF_EXIST_PACKED;

    return($returned);
  }

  /**
   * set the sqlite file name to retrieve IP country values
   * $file must exist
   *
   * @param String $file
   * @return String: file name set
   */
  public function setIpCountryFile($file)
  {
    if(file_exists($file))
      $this->ipCountryFile=$file;
    return($this->ipCountryFile);
  }

  /**
   * return the sqlite file name used to retrieve IP country values
   *
   * @return String
   */
  public function getIpCountryFile()
  {
    return($this->ipCountryFile);
  }


  /**
   * open the filename
   * if $mode=ASDF_OPEN_WRITE and file doesn't exist, create it; if a packed file
   * exist, try to unpack the packed file; in this case the unpacked file will be
   * packed and removed automatically when the process will close.
   *
   * if $mode=ASDF_OPEN_READ, and unpacked file doesn't exist, try to unpack
   * packed file (if exists); in this case the unpacked file will be removed
   * automatically when the process will close.
   *
   * @param Integer $mode: READ or WRITE
   * @return Boolean: true if opened, otherwise false
   */
  public function open($mode=ASDF_OPEN_READ)
  {
    $dbFile=$this->getFileName(ASDF_EXIST_UNPACKED, true);

    /*
     * encountered bug: sometime, an unpacked file stay with a zero size length
     * in this case, assume to unpack packed file
     */
    if($dbFile!='' and filesize($dbFile)==0)
    {
      unlink($dbFile);
      $dbFile='';
    }

    if($dbFile=='')
    {
      // file doesn't exist, check if a packed file exist
      $dbFileP=$this->getFileName(ASDF_EXIST_PACKED, true);
      if($dbFileP=='' and $mode==ASDF_OPEN_READ) return(false); //no packed file to open for READ, exit

      if($dbFileP!='')
      {
        $this->unpack(); //unpack the file
        $this->deleteUnpackedWhenClose=ASDF_CLOSE_DO_DELETE; // the unpacked file will be deleted when the process will close
      }
    }

    switch($mode)
    {
      case ASDF_OPEN_READ:
        $dbFile=$this->getFileName(ASDF_EXIST_UNPACKED, true);
        if($dbFile=='') return(false); //big problem with unpacked file!?
        $this->dbHandle=new SQLite3($dbFile, SQLITE3_OPEN_READONLY);
        if($this->dbHandle) return(true);
        break;
      case ASDF_OPEN_WRITE:
        $dbFile=$this->getFileName(ASDF_EXIST_UNPACKED);
        //if file was unpacked, pack it before deleting
        if($this->deleteUnpackedWhenClose==ASDF_CLOSE_DO_DELETE) $this->deleteUnpackedWhenClose=$this->deleteUnpackedWhenClose|ASDF_CLOSE_DO_PACK;
        $this->dbHandle=new SQLite3($dbFile, SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE);

        if($this->dbHandle) $this->checkSchema();
        return(true);
        break;
    }

    return(false);
  }

  /**
   * close the file
   *
   * @return Boolean : true if file is closed
   */
  public function close()
  {
    if($this->dbHandle==null) return(true);
    $this->stopTransac();

    if($this->dbHandle->close())
    {
      if(($this->deleteUnpackedWhenClose&ASDF_CLOSE_DO_PACK)==ASDF_CLOSE_DO_PACK)
      {
        $this->pack();
      }

      if(($this->deleteUnpackedWhenClose&ASDF_CLOSE_DO_DELETE)==ASDF_CLOSE_DO_DELETE)
      {
        $this->delete(ASDF_DELETE_UNPACKED);
      }

      $this->deleteUnpackedWhenClose=ASDF_CLOSE_DO_NOTHING;
      $this->dbHandle=null;
      return(true);
    }
    return(false);
  }

  /**
   * pack the data file in a .gz file
   *
   * @return Bool : false if a problem happened
   */
  public function pack()
  {
    $files=array();

    $file=$this->getFileName(ASDF_EXIST_UNPACKED, true);

    if($file!='')
      return(GPCCompress::gzip($file, $this->getFileName(ASDF_EXIST_PACKED)));
    return(false);
  }

  /**
   * unpack the .gz file
   *
   * @return Boolean : false if an error occurs
   */
  public function unpack()
  {
    if($this->fileExist() & ASDF_EXIST_PACKED==ASDF_EXIST_PACKED)
    {
      $returned=GPCCompress::gunzip($this->getFileName(ASDF_EXIST_PACKED), $this->getFileName(ASDF_EXIST_UNPACKED));
      /*
      $dir=dirName(dirName($this->getFileName(ASDF_EXIST_UNPACKED)));
      $tH=fopen($dir.'/logs.log', 'a');
      if($tH)
      {
        $dest=$this->getFileName(ASDF_EXIST_UNPACKED);
        fwrite($tH, sprintf("%s - %30s - %10d - %s\n", date('Y-m-d H:i:s'), $dest, filesize($dest), $returned?'y':'n'));
        fclose($tH);
      }
*/
      return($returned);
    }
  }

  /**
   * delete the file
   *
   * $mode can take this option
   *  - ASDF_DELETE_PACKED : delete packed file if exists
   *  - ASDF_DELETE_UNPACKED : delete unpacked file if exists
   * option can be combined with a pipe
   *
   * @param Integer $mode : delete mode to apply
   * @return Integer :
   */
  public function delete($mode)
  {
    $returned=0;
    if(($mode & ASDF_DELETE_PACKED)==ASDF_DELETE_PACKED)
    {
      if($this->getFileName(ASDF_EXIST_PACKED, true)!='')
      {
        if(unlink($this->getFileName(ASDF_EXIST_PACKED))) $returned=ASDF_DELETE_PACKED;
      }
    }

    if(($mode & ASDF_DELETE_UNPACKED)==ASDF_DELETE_UNPACKED)
    {
      $ok=true;
      if($this->getFileName(ASDF_EXIST_UNPACKED, true)!='') $ok=unlink($this->getFileName(ASDF_EXIST_UNPACKED));
      if($ok) $returned=$returned | ASDF_DELETE_UNPACKED;
    }

    return($returned);
  }

  /**
   * return true is the file is open
   *
   * @return Boolean
   */
  public function isOpen()
  {
    return($this->dbHandle!=null);
  }

  /**
   * return the file name
   *
   * @return String
   */
  public function getName()
  {
    return($this->fileName);
  }
  /**
   * return the directory
   *
   * @return String
   */
  public function getDirectory()
  {
    return($this->fileDir);
  }

  /**
   * return the physical file name
   *
   * @param Integer $mode: ask for PACKED or UNPACKED file name
   * @return String: file name if file exists, otherwise ''
   */
  public function getFileName($mode=ASDF_EXIST_UNPACKED, $onlyIfExist=false)
  {
    $fileName='';
    switch($mode)
    {
      case ASDF_EXIST_PACKED:
        $fileName=$this->fileDir.$this->fileName.$this->fileRootName.ASDF_FILE_EXT_ZIP;
        break;
      case ASDF_EXIST_UNPACKED:
        $fileName=$this->fileDir.$this->fileName.$this->fileRootName.ASDF_FILE_EXT;
        break;
    }

    if($onlyIfExist)
    {
      if($fileName!='' and file_exists($fileName)) return($fileName);
      return('');
    }
    return($fileName);
  }

  /**
   * begin a transaction
   *
   * usefull for performances when mass update are needed
   *
   * @return Boolean: true if the transaction is opened, otherwise false
   */
  public function startTransac()
  {
    if($this->dbHandle==null or $this->transacOpen) return(false);

    if($this->dbHandle->exec("BEGIN TRANSACTION"))
    {
      $this->transacOpen=true;
      return(true);
    }
    return(false);
  }

  /**
   * stop an opened transaction
   *
   * @param Integer $mode: COMMIT (ASDF_TRANSAC_COMMIT) or ROLLBACK (ASDF_TRANSAC_ROLLBACK) the current transaction
   * @return Boolean: true if the transaction is opened, otherwise false
   */
  public function stopTransac($mode=ASDF_TRANSAC_COMMIT)
  {
    if($this->dbHandle==null or !$this->transacOpen) return(false);

    $returned=false;

    switch($mode)
    {
      case ASDF_TRANSAC_COMMIT:
        $returned=$this->dbHandle->exec("COMMIT TRANSACTION");
        break;
      case ASDF_TRANSAC_ROLLBACK:
        $returned=$this->dbHandle->exec("ROLLBACK TRANSACTION");
        break;
    }

    if($returned) $this->transacOpen=false;
    return($returned);
  }


  /**
   * set a value in the infos tables
   * if key doesn't exist, insert it
   *
   * @param String $domain: domain to update
   * @param String $key: key to update
   * @param String $value: value to set
   * @return Boolean
   */
  public function setInfoProperty($domain, $key, $value)
  {
    if($this->dbHandle==null) return(false);
    $sql="REPLACE INTO info (domain, key, value)
            VALUES('".$this->dbHandle->escapeString($domain)."',
                   '".$this->dbHandle->escapeString($key)."',
                   '".$this->dbHandle->escapeString($value)."');";
    return($this->dbHandle->exec($sql));
  }

  /**
   * get a value from the info tables
   * if key doesn't exist, return null by default
   *
   * @param String $domain: domain to update
   * @param String $key: key to update
   * @param String $value: default value if not found
   * @return Boolean
   */
  public function getInfoProperty($domain, $key, $value=null)
  {
    if($this->dbHandle==null) return($value);
    $sql="SELECT value
          FROM info
          WHERE domain = '".$this->dbHandle->escapeString($domain)."'
            AND key = '".$this->dbHandle->escapeString($key)."';";
    $returned=$this->dbHandle->querySingle($sql);
    if($returned==null) $returned=$value;
    return($returned);
  }

  /**
   * check, create & update the schema of the database
   *
   * automatically called when the file is opened
   *
   * the table 'info' is created by default
   */
  protected function checkSchema()
  {
    if($this->dbHandle)
    {
      $result=$this->getDBInfo(ASDF_DB_TYPE_TABLE, 'info', ASDF_DB_INFO);
      if(count($result)==0)
      {
        $this->dbHandle->exec(
            "CREATE TABLE 'info' (
                'domain' TEXT,
                'key' TEXT,
                'value' TEXT
            );"
          );
        $this->dbHandle->exec("CREATE UNIQUE INDEX iInfo ON info ('domain', 'key');");

        $this->dbHandle->exec("
          INSERT INTO info (domain, key, value) VALUES ('nfo', 'create', '".date('Y-m-d H:i:s')."');
          INSERT INTO info (domain, key, value) VALUES ('nfo', 'version', '00.00.00');

          INSERT INTO info (domain, key, value) VALUES ('log', 'buildStatPeriod-count', '0');
        ");
      }
      return(true);
    }
    return(false);
  }

  /**
   * return table infos
   *
   * $mode=ASDF_DB_INFO
   *    return the table structure, one row per field
   *        'name' => field name
   *        'type' => field type
   *
   * $mode=ASDF_DB_NUM_ROWS
   *    return the number of rows in the table
   *
   * @param Integer $type: ASDF_DB_TYPE_TABLE, ASDF_DB_TYPE_INDEX, ASDF_DB_TYPE_TRIGGER
   * @param String $name
   * @param Integer $mode : ASDF_DB_INFO, ASDF_DB_NUM_ROWS
   * @return :
   */
  protected function getDBInfo($type, $name, $mode=0)
  {
    if($this->dbHandle==null) return(null);

    switch($type)
    {
      case ASDF_DB_TYPE_TABLE:
          switch($mode)
          {
            case ASDF_DB_INFO:
                $returned=array();
                $result=$this->dbHandle->query("PRAGMA table_info('$name')");
                if($result)
                {
                  while($row=$result->fetchArray(SQLITE3_ASSOC))
                  {
                    $returned[]=array(
                      'name' => $row['name'],
                      'type' => $row['type']
                    );
                  }
                }
              break;
            case ASDF_DB_NUM_ROWS:
                $returned=$this->dbHandle->querySingle("SELECT COUNT(*) FROM $name");
              break;
            case ASDF_DB_LIST:
                $returned=array();
                $result=$this->dbHandle->query("SELECT name FROM sqlite_master WHERE type='table';");
                if($result)
                {
                  while($row=$result->fetchArray(SQLITE3_ASSOC))
                  {
                    $returned[]=$row['name'];
                  }
                }
              break;
          }
        break;
      case ASDF_DB_TYPE_INDEX:
          $returned=$this->dbHandle->querySingle("SELECT COUNT(*) FROM sqlite_master WHERE type = 'index' AND name='$name'");
        break;
      case ASDF_DB_TYPE_TRIGGER:
          $returned=$this->dbHandle->querySingle("SELECT COUNT(*) FROM sqlite_master WHERE type = 'trigger' AND name='$name'");
        break;
      case ASDF_DB_TYPE_LOG:
          $returned=array();
          if(count($this->getDBInfo(ASDF_DB_TYPE_TABLE, 'info', ASDF_DB_INFO))==0) return($returned);
          $result=$this->dbHandle->query("SELECT key, value FROM info WHERE domain='log';");
          if($result)
          {
            while($row=$result->fetchArray(SQLITE3_ASSOC))
            {
              $returned[]=$row;
            }
          }
        break;
      case ASDF_DB_TYPE_NFO:
          $returned=array();
          if(count($this->getDBInfo(ASDF_DB_TYPE_TABLE, 'info', ASDF_DB_INFO))==0) return($returned);
          $result=$this->dbHandle->query("SELECT key, value FROM info WHERE domain='nfo';");
          if($result)
          {
            while($row=$result->fetchArray(SQLITE3_ASSOC))
            {
              $returned[]=$row;
            }
          }
        break;
    }

    return($returned);
  }

  /**
   * return pragma info
   *
   * @param String $pragma: a sqlite pragma command
   * @return: if single data is returned, return the data otherwise return an array
   */
  protected function getPragma($pragma)
  {
    $returned=array();

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

    $sql="PRAGMA ".$pragma;
    $result=$this->dbHandle->query($sql);
    if($result)
    {
      while($row=$result->fetchArray(SQLITE3_ASSOC))
      {
        $returned[]=$row;
      }
      if(count($returned)==1) $returned=$returned[0][$pragma];
    }
    return($returned);
  }



  /**
   * build a where clause from a filter array
   * used by functions:
   *  . getLogs()
   *  . getStatIP()
   *  . getStatCat()
   *  . getStatImages()
   *
   * return a string:
   *  . empty '' string if there's no item to filter
   *  . a ready to use ' WHERE ' clause string
   *
   * @param Array $filter: a valid filter
   * @param Array &$IPList: an array, used to return a ':IPn' list for requests
   *                        filtering IP adress
   * @return String
   */
  protected function buildWhereClause($filter, &$IPList)
  {
    $where=array();
    $IPList=array();
    $num=0;

    if(!is_array($filter)) return('');

    foreach($filter as $field => $param)
    {
      if(is_array($param))
      {
        switch($param['operator'])
        {
          case '=':
          case '>':
          case '>=':
          case '<':
          case '<=':
          case '!=':
            if($field=='IPadress')
            {
              $IPList[$num]=$param['value'];
              $param['value']=':IP'.$num;
              $num++;
            }
            $where[]=' '.$field.' '.$param['operator']." ".$param['value']." ";
            break;
          case 'in':
            if($field=='IPadress')
            {
              foreach($param['value'] as $key=>$ipAdress)
              {
                $IPList[$num]=$ipAdress;
                $param[$key]['value']=':IP'.$num;
                $num++;
              }
            }
            $where[]=' '.$field." IN (".implode(',', $param['value']).") ";
            break;
          case 'between':
            if($field=='IPadress')
            {
              $IPList[$num]=$param['minValue'];
              $param['minValue']=':IP'.$num;
              $num++;
              $IPList[$num]=$param['maxValue'];
              $param['maxValue']=':IP'.$num;
              $num++;
            }
            $where[]=' '.$field." BETWEEN ".$param['minValue']." AND ".$param['maxValue']." ";
            break;
        }
      }
    }

    if(count($where)>0)
    {
      $where=" WHERE ".implode(' AND ', $where)." ";
    }
    else
    {
      $where='';
    }

    return($where);
  }



  /**
   * build an ORDER BY clause
   * used by functions:
   *  . getLogs()
   *  . getStatIP()
   *  . getStatCat()
   *  . getStatImages()
   *
   * return a string:
   *  . empty '' string if there's no limits
   *  . a ready to use ' ORDER BY ' clause string
   *
   * @param Array $filter: a valid order by list
   * @return String
   */
  protected function buildOrderByClause($orderBy)
  {
    $order=array();
    foreach($orderBy as $sort)
    {
      $order[]=' '.$sort['id'].' '.($sort['direction']=='A'?'ASC':'DESC');
    }

    if(count($order)>0)
    {
      $orderBy=" ORDER BY ".implode(',', $order);
    }
    else
    {
      $orderBy='';
    }

    return($orderBy);
  }



  /**
   * build a LIMIT clause
   * used by functions:
   *  . getLogs()
   *  . getStatIP()
   *  . getStatCat()
   *  . getStatImages()
   *
   * return a string:
   *  . empty '' string if there's no limits
   *  . a ready to use ' LIMIT x,y ' clause string
   *
   * @param Integer $page: page number
   * @param Integer $nbItemsPage: number of items per page
   * @return String
   */
  protected function buildLimitClause($page, $nbItemsPage)
  {
    if($page<=0 or $nbItemsPage<=0)
    {
      $limit='';
    }
    else
    {
      $limit=' LIMIT '.(($page-1)*$nbItemsPage).', '.$nbItemsPage;
    }

    return($limit);
  }



  /**
   * check if value is a valid operator array
   * if not, return a null value otherwise return the operator
   *
   * validity of value are checked according to the format; valid format are:
   *  - 'date'    => 'YYYY-MM-DD (HH:II(:SS))'
   *  - 'IP'      => 'xxx.xxx.xxx.xxx' for IPv4, 'xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx' for IPv6
   *  - 'string'  => a string
   *  - 'integer' => any positive integer value
   *
   * @param Array $value
   * @param String $format
   * @return Array:
   */
  protected function getOperator($value, $format)
  {
    $returned=null;
    if($value==null) return(null);
    if(is_array($value))
    {
      if(isset($value['operator']))
      {
        switch($value['operator'])
        {
          case '=':
          case '>':
          case '>=':
          case '<':
          case '<=':
          case '!=':
            if(isset($value['value']))
            {
              switch($format)
              {
                case 'date':
                  $value['value']=$this->checkValueDate($value['value']);
                  if($value['value']==null) return(null);
                  $value['value']=$value['value'];
                  break;
                case 'IP':
                  $value['value']=$this->checkValueIP($value['value']);
                  if($value['value']==null) return(null);
                  break;
                case 'string':
                  if(!is_string($value['value']) or $value['value']=='') return(null);
                  $value['value']="'".$value['value']."'";
                  break;
                case 'integer':
                  if(!is_int($value['value']) or $value['value']<0) return(null);
                  break;
              }
              $returned=$value;
            }
            break;
          case 'in':
            if(isset($value['value']) and is_array($value['value']))
            {
              $returned=array();
              foreach($value['value'] as $val)
              {
                switch($format)
                {
                  case 'date':
                    $val=$this->checkValueDate($val);
                    if($val!=null) $returned[]=$val;
                    break;
                  case 'IP':
                    $val=$this->checkValueIP($val);
                    if($val!=null) $returned[]=$val;
                    break;
                  case 'string':
                    if(is_string($val) and $val!='') $returned[]="'".$val."'";
                    break;
                  case 'integer':
                    if(is_int($val*1) and $val>=0) $returned[]=$val;
                    break;
                }
              }
              if(count($returned)==0) return(null);
              $value['value']=$returned;
              $returned=$value;
            }
            break;
          case 'between':
            if(isset($value['minValue']) and  isset($value['maxValue']))
            {
              switch($format)
              {
                case 'date':
                  $value['minValue']=$this->checkValueDate($value['minValue']);
                  if($value['minValue']==null) return(null);
                  $value['maxValue']=$this->checkValueDate($value['maxValue']);
                  if($value['maxValue']==null) return(null);
                  break;
                case 'IP':
                  $value['minValue']=$this->checkValueIP($value['minValue']);
                  if($value['minValue']==null) return(null);
                  $value['maxValue']=$this->checkValueIP($value['maxValue']);
                  if($value['maxValue']==null) return(null);
                  break;
                case 'string':
                  if(!is_string($value['minValue']) or $value['minValue']=='' or
                     !is_string($value['maxValue']) or $value['maxValue']=='') return(null);

                  $value['minValue']="'".$value['minValue']."'";
                  $value['maxValue']="'".$value['maxValue']."'";
                  break;
                case 'integer':
                  if(!is_int($value['minValue']) or $value['minValue']<0 or
                     !is_int($value['maxValue']) or $value['maxValue']<0) return(null);
                  break;
              }
              if($value['minValue']>$value['maxValue']) return(null);
              $returned=$value;
            }
            break;
        }
      }
    }
    return($returned);
  }

  /**
   * check if date is Ok
   * return the date completed (YYYY-MM-DD => YYYY-MM-DD 00:00:00) or null if not valid
   *
   * @param String $value
   * @return String
   */
  protected function checkValueDate($value)
  {
    $returned=null;
    $result=array();
    if(preg_match('/^(\d{4}-\d{2}-\d{2})(?:(\s{1}\d{2}:\d{2})(:\d{2})?)?$/', $value, $result)>0)
    {
      $returned=$result[1];
      if(isset($result[2]))
      {
        $returned.=$result[2];
        if(isset($result[3]))
        {
          $returned.=$result[3];
        }
        else $returned.=':00';
      }
      else $returned.=' 00:00:00';
    }
    return(strtotime($returned));
  }

  /**
   * check if IP is Ok (IPv4 or IPv6)
   * return the IP or null
   *
   * @param String $value
   * @return String
   */
  protected function checkValueIP($value)
  {
    $returned=self::IPBinaryEncode($value);
    if($returned=="\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00") return(null);
    return($returned);
  }







  /**
   * return an IP adress with format 'xxx.xxx.xxx.xxx' as a binary encoded string
   *
   * @param String $IP: text IP string
   * @return String: a binary string
   */
  static public function IPBinaryEncode($IP)
  {
    $value=trim($IP);
    if(preg_match('/\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}/i',$value)>0)
    {
      //IPv4
      $tmp=explode('.',$value);
      return("\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00".chr($tmp[0]+0).chr($tmp[1]+0).chr($tmp[2]+0).chr($tmp[3]+0));
    }
    elseif(preg_match('/[a-f0-9]{0,4}:[a-f0-9]{0,4}:[a-f0-9]{0,4}:[a-f0-9]{0,4}:[a-f0-9]{0,4}:[a-f0-9]{0,4}:[a-f0-9]{0,4}:[a-f0-9]{0,4}/i',$value))
    {
      //IPv6
    }
    else
    {
      return("\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00");
    }
  }

  /**
   * return an IP adress with format 'xxx.xxx.xxx.xxx' from a binary encoded string
   *
   * @param String $IP: binary encoded string
   * @return String: a text IP string
   */
  static public function IPBinaryDecode($IP)
  {
    $returned='';
    if(strlen($IP)==16)
    {
      if(substr($IP,0,12)=="\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00")
      {
        //IPv4
        $returned=sprintf('%d.%d.%d.%d', ord(substr($IP,12,1)), ord(substr($IP,13,1)), ord(substr($IP,14,1)), ord(substr($IP,15,1)));
      }
      else
      {
        //IPv6
      }
    }
    else if(strlen($IP)==4)
    {
      $returned=sprintf('%d.%d.%d.%d', ord(substr($IP,0,1)), ord(substr($IP,1,1)), ord(substr($IP,2,1)), ord(substr($IP,3,1)));
    }
    else
    {
    }
    return($returned);
  }


  /**
    * Convert the given data into an unsigned Short integer (16bit)
    *
    * @param String $data       : representing the unsigned Short
    * @param String $endianType : the byte order
    * @return UShort
    */
  static public function toUShort($data)
  {
    if(strlen($data)>=2)
      return(ord($data{0})*256 + ord($data{1}));
    return(0);
  }

  /**
    * Convert the given data into an unsigned Long integer (32bit)
    *
    * @param String $data       : representing the unsigned Long
    * @param String $endianType : the byte order
    * @return ULong
    */
  static public function toULong($data)
  {
    if(strlen($data)>=4)
        return(ord($data{0})*16777216 +
                ord($data{1})*65536 +
                ord($data{2})*256 +
                ord($data{3}));
    return(0);
  }


}









?>