<?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_LOCK',     0x04);
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_EXT_LOCK', '.lock');
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='';
  protected $lckFileNumber=0;
  //protected $logs=''; for debug
  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
   * @param Boolean $force: if true, $force the filename affectation even if file doesn't exist
   * @return String: file name set
   */
  public function setIpCountryFile($file, $force=false)
  {
    if($force or 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 (if there's no lock
   * files linked to the uncompressed file); a new lock file is created
   *
   * @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);
    $this->createLockFile();
    $this->waitForUnpackedFile();

    /*
     * 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
      }
    }
    else
    {
      if($this->lckFileNumber>1 and $this->getFileName(ASDF_EXIST_PACKED, true)!='' and $mode==ASDF_OPEN_READ)
        $this->deleteUnpackedWhenClose=ASDF_CLOSE_DO_DELETE;
    }

    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();
      }

      $this->deleteLockFile();
      if(($this->deleteUnpackedWhenClose&ASDF_CLOSE_DO_DELETE)==ASDF_CLOSE_DO_DELETE and
          !$this->fileIsLocked())
      {
        $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)
    {
      $unpackedFile=$this->getFileName(ASDF_EXIST_UNPACKED);

      // create a temp file
      $fHandle=fopen($unpackedFile, 'w');
      fclose($fHandle);

      $returned=GPCCompress::gunzip($this->getFileName(ASDF_EXIST_PACKED), $unpackedFile.'.tmp');

      unlink($unpackedFile);
      if($returned)
      {
        rename($unpackedFile.'.tmp', $unpackedFile);
      }

      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;
      case ASDF_EXIST_LOCK:
        $fileName=$this->fileDir.$this->fileName.$this->fileRootName.'-'.$this->lckFileNumber.ASDF_FILE_EXT_LOCK;
        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);
  }

  /**
   * how the file lock system works:
   *
   * the lock system is used only when for uncompressed files, allowing to know
   * if an uncompressed file can be deleted or not; locks function are called only
   * in READ mode by function open() and close()
   *
   * createLockFile()
   *  1/ Search for existing lock files
   *  2/ If files are found, increase the $lckFileNumber with number of existing files + 1
   *  3/ Create the lock file
   *
   * deleteLockFile()
   *  delete the lock file associated with the current $lckFileNumber
   *
   * fileIsLocked()
   *  return true if at least, one lock exists
   *
   *  ......................................
   *          ......................................
   *                   ..............
   * -+-------+--------+------------+------+-------+-->
   * (A)     (B)      (C)          (D)    (E)     (F)
   *  ^       ^        ^            ^      ^       ^
   *  p1      p2       p3           p3     p1      p2
   *  start   start    start        end    end     end
   *
   * (A) process 1 start: createLockFile() => lock file '1' is created
   * (B) process 2 start: createLockFile() => lock file '2' is created
   * (C) process 3 start: createLockFile() => lock file '3' is created
   * (D) process 3 end:   deleteLockFile() => lock file '3' is deleted
   *                                          lock files '1' & '2' still exist (uncompressed SQLite file can't be deleted)
   * (E) process 1 end:   deleteLockFile() => lock file '1' is deleted
   *                                          lock file '2' still exist (uncompressed SQLite file can't be deleted)
   * (F) process 2 end:   deleteLockFile() => lock file '2' is deleted
   *                                          there's no lock files (uncompressed SQLite file can be deleted)
   *
   * @return Boolean: true if the lock file was created, otherwise false
   */
  protected function createLockFile()
  {
    $this->lckFileNumber=$this->getNumberOfLockFiles()+1;
    $fHandle=fopen($this->getFileName(ASDF_EXIST_LOCK), 'w');
    if($fHandle)
    {
      fclose($fHandle);
      return(true);
    }
    $this->lckFileNumber=0;
    return(false);
  }

  /**
   * delete the current lock file
   * return true if file is deleted (or file doesn't exists => there's no more file to delete...), otherwise return false
   *
   * the function also delete all lock files older than current timestamp+max_execution_time (if
   * the file still exist, it means that a problem has occured at a moment)
   *
   * @return Boolean
   */
  protected function deleteLockFile()
  {
    $returned=true;
    $file=$this->getFileName(ASDF_EXIST_LOCK);
    if(file_exists($file)) $returned=unlink($file);

    if($returned) $this->lckFileNumber=0;

    $regExp="/\\".ASDF_FILE_EXT_LOCK.'/i';
    scandir(dirName($this->fileDir));
    $files=scandir($this->fileDir);
    $timeOut=time()-ini_get('max_execution_time');

    foreach($files as $file)
    {
      if(preg_match($regExp, $file, $result)>0)
      {
        if(filemtime($this->fileDir.$file)<$timeOut)
        {
          unlink($this->fileDir.$file);
        }
      }
    }


    return($returned);
  }

  /**
   * return true if a lock file exists
   *
   * @return Boolean
   */
  protected function fileIsLocked()
  {
    if($this->getNumberOfLockFiles()>0) return(true);
    return(false);
  }

  /**
   * return true the number of existing lock files
   *
   * @return Integer: number of existing lock files
   */
  protected function getNumberOfLockFiles()
  {
    $returned=0;
    $regExp='/'.$this->fileName.$this->fileRootName."-(\d+)\\".ASDF_FILE_EXT_LOCK.'/i';
    $files=scandir($this->fileDir);
    foreach($files as $file)
    {
      if(preg_match($regExp, $file, $result)>0)
      {
        $tmp=$result[1]*1;
        if($tmp>$returned) $returned=$tmp;
      }
    }
    return($returned);
  }

  /**
   * if the unpacked exist with a '.tmp' extension, assume that an unpacking process
   * is running and loop until the file doesn't exist anymore
   *
   * @param Integer $maxWait: maximum wait time in millisecond...
   * @return Boolean: true if file was unpacked in the waiting time, otherwise false
   */
  protected function waitForUnpackedFile($maxWait=2000)
  {
    $tmpFile=$this->getFileName(ASDF_EXIST_UNPACKED).'.tmp';
    $maxTimer=microtime(true)+$maxWait/1000;
    while(file_exists($tmpFile))
    {
      if(microtime(true)>=$maxTimer) return(false);
      usleep(5000); //wait 5milliseconds before next check
    }
    return(true);
  }

  /**
   * 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':
          case 'not between':
            if($field=='IPadress')
            {
              $IPList[$num]=$param['minValue'];
              $param['minValue']=':IP'.$num;
              $num++;
              $IPList[$num]=$param['maxValue'];
              $param['maxValue']=':IP'.$num;
              $num++;
            }
            $where[]=' '.$field.' '.strtoupper($param['operator']).' '.$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);
  }


  /**
   * to be used with IP filter: replace the given operator with 'between' or 'not between'
   *
   * @param Array $value
   * @return Array
   */
  protected function checkIPFilter($value)
  {
    if(is_array($value))
    {
      if(isset($value['operator']))
      {
        if($value['operator']=='=' or
           $value['operator']=='>' or
           $value['operator']=='>=' or
           $value['operator']=='<' or
           $value['operator']=='<=' or
           $value['operator']=='like')
        {
          $value['operator']='between';
          $value['minValue']=$value['value'];
          $value['maxValue']=$value['value'];
          $value['value']=null;
        }
        elseif($value['operator']=='!=' or
               $value['operator']=='not like')
        {
          $value['operator']='not between';
          $value['minValue']=$value['value'];
          $value['maxValue']=$value['value'];
          $value['value']=null;
        }
      }
    }
    return($value);
  } // checkIPFilter


  /**
   * to be used with Date filter: replace the given operator with 'between' or 'not between'
   *
   * @param Array $value
   * @return Array
   */
  protected function checkDateFilter($value)
  {
    if(is_array($value))
    {
      if(isset($value['operator']))
      {
        if($value['operator']=='=' or
           $value['operator']=='like')
        {
          $value['operator']='between';
          $value['minValue']=$value['value'];
          $value['maxValue']=$value['value'];
          $value['value']=null;
        }
        elseif($value['operator']=='!=' or
               $value['operator']=='not like')
        {
          $value['operator']='not between';
          $value['minValue']=$value['value'];
          $value['maxValue']=$value['value'];
          $value['value']=null;
        }
      }
    }
    return($value);
  } // checkDateFilter


  /**
   * 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 '!=':
          case 'like':
          case 'not like':
            if(isset($value['value']))
            {
              switch($format)
              {
                case 'date':
                  switch($value['operator'])
                  {
                    case '>':
                    case '<=':
                      $value['value']=$this->checkValueDate($value['value'], 'E');
                      break;
                    case '<':
                    case '>=':
                    default:
                      $value['value']=$this->checkValueDate($value['value'], 'B');
                      break;
                  }
                  if($value['value']==null) return(null);
                  $value['value']=$value['value'];
                  break;
                case 'IP':
                  // for IP, replace internaly the operator with a 'between'
                  $value['value']=$this->checkValueIP($value['value'], 0);
                  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, 0);
                    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':
          case 'not between':
            if(isset($value['minValue']) and  isset($value['maxValue']))
            {
              switch($format)
              {
                case 'date':
                  $value['minValue']=$this->checkValueDate($value['minValue'], 'B');
                  if($value['minValue']==null) return(null);
                  $value['maxValue']=$this->checkValueDate($value['maxValue'], 'E');
                  if($value['maxValue']==null) return(null);
                  break;
                case 'IP':
                  $value['minValue']=$this->checkValueIP($value['minValue'], 0);
                  if($value['minValue']==null) return(null);
                  $value['maxValue']=$this->checkValueIP($value['maxValue'], 255);
                  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) if $mode='B'
   *  or  (YYYY-MM-DD => YYYY-MM-DD 23:59:59) if $mode='E'
   *  or  null if not valid
   *
   * @param String $value
   * @param String $mode: 'B' for begin or 'E' for end
   * @return String
   */
  protected function checkValueDate($value, $mode='B')
  {
    $returned=null;
    $result=array();
    if(preg_match('/^(\d{4}-\d{2}-\d{2})(?:(\s{1}\d{2}:\d{2})(:\d{2})?)?$/', trim($value), $result)>0)
    {
      $returned=$result[1];
      if(isset($result[2]))
      {
        $returned.=$result[2];
        if(isset($result[3]))
        {
          $returned.=$result[3];
        }
        else
        {
          $returned.=($mode=='B')?':00':':59';
        }
      }
      else
      {
        $returned.=($mode=='B')?' 00:00:00':' 23:59:59';
      }
    }
    return(strtotime($returned));
  }

  /**
   * check if IP is Ok (IPv4 or IPv6)
   * return the IP or null
   *
   * @param String $value
   * @param Integer $completeCode
   * @return String
   */
  protected function checkValueIP($value, $completeCode=0)
  {
    $returned=self::IPBinaryEncode($value, $completeCode);
    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
   * @param Integer $completeCode
   * @return String: a binary string
   */
  static public function IPBinaryEncode($IP, $completeCode=0)
  {
    $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);
      while(count($tmp)<4) $tmp[]=$completeCode;

      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);
  }


/*
  for debug

  private function log($data)
  {
    if($this->logs=='') return(false);
    $tH=fopen(dirName($this->fileDir).'/logs.log', 'a');
    if($tH)
    {
      $time=microtime(true);
      fwrite($tH, sprintf("%s %f - %s\n", $this->logs, $time, $data));

      fclose($tH);
    }
  }
*/


} // StatDB


/*
 used to display binary values when debug is necessary...

function toHexDump($data)
  {
    $returned="";
    $maxItems=strlen($data);

    $tmp=array();
    for($i=0;$i<$maxItems;$i++)
    {
      $tmp[]=sprintf("%02x", ord($data{$i}));
    }
    $returned.=implode(" ", $tmp);
    return($returned);
  }
*/


?>