include/class.db.php

   1 <?php
   2
   3 namespace Garradin;
   4
   5 function str_replace_first ($search, $replace, $subject)
   6 {
   7     $pos = strpos($subject, $search);
   8
   9     if ($pos !== false)
  10     {
  11         $subject = substr_replace($subject, $replace, $pos, strlen($search));
  12     }
  13
  14     return $subject;
  15 }
  16
  17 class DB extends \SQLite3
  18 {
  19     static protected $_instance = null;
  20
  21     protected $_running_sum = 0.0;
  22
  23     protected $_transaction = 0;
  24
  25     const NUM = \SQLITE3_NUM;
  26     const ASSOC = \SQLITE3_ASSOC;
  27     const BOTH = \SQLITE3_BOTH;
  28
  29     static public function getInstance($create = false)
  30     {
  31         return self::$_instance ?: self::$_instance = new DB($create);
  32     }
  33
  34     private function __clone()
  35     {
  36     }
  37
  38     public function __construct($create = false)
  39     {
  40         $flags = SQLITE3_OPEN_READWRITE;
  41
  42         if ($create)
  43         {
  44             $flags |= SQLITE3_OPEN_CREATE;
  45         }
  46
  47         parent::__construct(DB_FILE, $flags);
  48
  49         $this->enableExceptions(true);
  50
  51         // Activer les contraintes des foreign keys
  52         $this->exec('PRAGMA foreign_keys = ON;');
  53
  54         $this->createFunction('transliterate_to_ascii', ['Garradin\utils', 'transliterateToAscii']);
  55         $this->createFunction('base64', 'base64_encode');
  56         $this->createFunction('rank', [$this, 'sql_rank']);
  57         $this->createFunction('running_sum', [$this, 'sql_running_sum']);
  58     }
  59
  60     public function sql_running_sum($data)
  61     {
  62         // Why is this function called two times for the first row?!
  63         // Dunno but here is a workaround
  64         if (is_null($this->_running_sum))
  65         {
  66             $this->_running_sum = 0.0;
  67             return $this->_running_sum;
  68         }
  69
  70         $this->_running_sum += $data;
  71         return $this->_running_sum;
  72     }
  73
  74     public function resetRunningSum()
  75     {
  76         $this->_running_sum = null;
  77     }
  78
  79     public function sql_rank($aMatchInfo)
  80     {
  81         $iSize = 4; // byte size
  82         $iPhrase = (int) 0;                 // Current phrase //
  83         $score = (double)0.0;               // Value to return //
  84
  85         /* Check that the number of arguments passed to this function is correct.
  86         ** If not, jump to wrong_number_args. Set aMatchinfo to point to the array
  87         ** of unsigned integer values returned by FTS function matchinfo. Set
  88         ** nPhrase to contain the number of reportable phrases in the users full-text
  89         ** query, and nCol to the number of columns in the table.
  90         */
  91         $aMatchInfo = (string) func_get_arg(0);
  92         $nPhrase = ord(substr($aMatchInfo, 0, $iSize));
  93         $nCol = ord(substr($aMatchInfo, $iSize, $iSize));
  94
  95         if (func_num_args() > (1 + $nCol))
  96         {
  97             throw new \Exception("Invalid number of arguments : ".$nCol);
  98         }
  99
 100         // Iterate through each phrase in the users query. //
 101         for ($iPhrase = 0; $iPhrase < $nPhrase; $iPhrase++)
 102         {
 103             $iCol = (int) 0; // Current column //
 104
 105             /* Now iterate through each column in the users query. For each column,
 106             ** increment the relevancy score by:
 107             **
 108             **   (<hit count> / <global hit count>) * <column weight>
 109             **
 110             ** aPhraseinfo[] points to the start of the data for phrase iPhrase. So
 111             ** the hit count and global hit counts for each column are found in
 112             ** aPhraseinfo[iCol*3] and aPhraseinfo[iCol*3+1], respectively.
 113             */
 114             $aPhraseinfo = substr($aMatchInfo, (2 + $iPhrase * $nCol * 3) * $iSize);
 115
 116             for ($iCol = 0; $iCol < $nCol; $iCol++)
 117             {
 118                 $nHitCount = ord(substr($aPhraseinfo, 3 * $iCol * $iSize, $iSize));
 119                 $nGlobalHitCount = ord(substr($aPhraseinfo, (3 * $iCol + 1) * $iSize, $iSize));
 120                 $weight = ($iCol < func_num_args() - 1) ? (double) func_get_arg($iCol + 1) : 0;
 121
 122                 if ($nHitCount > 0)
 123                 {
 124                     $score += ((double)$nHitCount / (double)$nGlobalHitCount) * $weight;
 125                 }
 126             }
 127         }
 128
 129         return $score;
 130     }
 131
 132     public function escape($str)
 133     {
 134         return $this->escapeString($str);
 135     }
 136
 137     public function e($str)
 138     {
 139         return $this->escapeString($str);
 140     }
 141
 142     public function begin()
 143     {
 144         if (!$this->_transaction)
 145         {
 146             $this->exec('BEGIN;');
 147         }
 148
 149         $this->_transaction++;
 150
 151         return $this->_transaction == 1 ? true : false;
 152     }
 153
 154     public function commit()
 155     {
 156         if ($this->_transaction == 1)
 157         {
 158             $this->exec('END;');
 159         }
 160
 161         if ($this->_transaction > 0)
 162         {
 163             $this->_transaction--;
 164         }
 165
 166         return $this->_transaction ? false : true;
 167     }
 168
 169     public function rollback()
 170     {
 171         $this->exec('ROLLBACK;');
 172         $this->_transaction = 0;
 173         return true;
 174     }
 175
 176     protected function _getArgType($arg, $name = '')
 177     {
 178         if (is_float($arg))
 179             return SQLITE3_FLOAT;
 180         elseif (is_int($arg))
 181             return SQLITE3_INTEGER;
 182         elseif (is_bool($arg))
 183             return SQLITE3_INTEGER;
 184         elseif (is_null($arg))
 185             return SQLITE3_NULL;
 186         elseif (is_string($arg))
 187             return SQLITE3_TEXT;
 188         else
 189             throw new \InvalidArgumentException('Argument '.$name.' is of invalid type '.gettype($arg));
 190     }
 191
 192     public function simpleStatement($query, $args = [])
 193     {
 194         $statement = $this->prepare($query);
 195         $nb = $statement->paramCount();
 196
 197         if (!empty($args))
 198         {
 199             if (is_array($args) && count($args) == 1 && is_array(current($args)))
 200             {
 201                 $args = current($args);
 202             }
 203
 204             if (count($args) != $nb)
 205             {
 206                 throw new \LengthException('Arguments error: '.count($args).' supplied, but '.$nb.' are required by query.');
 207             }
 208
 209             reset($args);
 210
 211             if (is_int(key($args)))
 212             {
 213                 foreach ($args as $i=>$arg)
 214                 {
 215                     $statement->bindValue((int)$i+1, $arg, $this->_getArgType($arg, $i+1));
 216                 }
 217             }
 218             else
 219             {
 220                 foreach ($args as $key=>$value)
 221                 {
 222                     if (is_int($key))
 223                     {
 224                         throw new \InvalidArgumentException(__FUNCTION__ . ' requires argument to be a named-associative array, but key '.$key.' is an integer.');
 225                     }
 226
 227                     $statement->bindValue(':'.$key, $value, $this->_getArgType($value, $key));
 228                 }
 229             }
 230         }
 231
 232         try {
 233             return $statement->execute();
 234         }
 235         catch (\Exception $e)
 236         {
 237             throw new \Exception($e->getMessage() . "\n" . $query . "\n" . json_encode($args, true));
 238         }
 239     }
 240
 241     public function simpleStatementFetch($query, $mode = SQLITE3_BOTH)
 242     {
 243         if ($mode != SQLITE3_BOTH && $mode != SQLITE3_ASSOC && $mode != SQLITE3_NUM)
 244         {
 245             throw new \InvalidArgumentException('Mode argument should be either SQLITE3_BOTH, SQLITE3_ASSOC or SQLITE3_NUM.');
 246         }
 247
 248         $args = array_slice(func_get_args(), 2);
 249         return $this->fetchResult($this->simpleStatement($query, $args), $mode);
 250     }
 251
 252     public function simpleStatementFetchAssoc($query)
 253     {
 254         $args = array_slice(func_get_args(), 1);
 255         return $this->fetchResultAssoc($this->simpleStatement($query, $args));
 256     }
 257
 258     public function simpleStatementFetchAssocKey($query, $mode = SQLITE3_BOTH)
 259     {
 260         if ($mode != SQLITE3_BOTH && $mode != SQLITE3_ASSOC && $mode != SQLITE3_NUM)
 261         {
 262             throw new \InvalidArgumentException('Mode argument should be either SQLITE3_BOTH, SQLITE3_ASSOC or SQLITE3_NUM.');
 263         }
 264
 265         $args = array_slice(func_get_args(), 2);
 266         return $this->fetchResultAssocKey($this->simpleStatement($query, $args), $mode);
 267     }
 268
 269     public function escapeAuto($value, $name = '')
 270     {
 271         $type = $this->_getArgType($value, $name);
 272
 273         switch ($type)
 274         {
 275             case SQLITE3_FLOAT:
 276                 return floatval($value);
 277             case SQLITE3_INTEGER:
 278                 return intval($value);
 279             case SQLITE3_NULL:
 280                 return 'NULL';
 281             case SQLITE3_TEXT:
 282                 return '\'' . $this->escapeString($value) . '\'';
 283         }
 284     }
 285
 286     /**
 287      * Simple INSERT query
 288      */
 289     public function simpleInsert($table, $fields)
 290     {
 291         $fields_names = array_keys($fields);
 292         return $this->simpleStatement('INSERT INTO '.$table.' ('.implode(', ', $fields_names).')
 293             VALUES (:'.implode(', :', $fields_names).');', $fields);
 294     }
 295
 296     public function simpleUpdate($table, $fields, $where)
 297     {
 298         if (empty($fields))
 299             return false;
 300
 301         $query = 'UPDATE '.$table.' SET ';
 302
 303         foreach ($fields as $key=>$value)
 304         {
 305             $query .= $key . ' = :'.$key.', ';
 306         }
 307
 308         $query = substr($query, 0, -2);
 309         $query .= ' WHERE '.$where.';';
 310         return $this->simpleStatement($query, $fields);
 311     }
 312
 313     /**
 314      * Formats and escapes a statement and then returns the result of exec()
 315      */
 316     public function simpleExec($query)
 317     {
 318         return $this->simpleStatement($query, array_slice(func_get_args(), 1));
 319     }
 320
 321     public function simpleQuerySingle($query, $all_columns = false)
 322     {
 323         $res = $this->simpleStatement($query, array_slice(func_get_args(), 2));
 324
 325         $row = $res->fetchArray($all_columns ? SQLITE3_ASSOC : SQLITE3_NUM);
 326
 327         if (!$all_columns)
 328         {
 329             if (isset($row[0]))
 330                 return $row[0];
 331             return false;
 332         }
 333         else
 334         {
 335             return $row;
 336         }
 337     }
 338
 339     public function queryFetch($query, $mode = SQLITE3_BOTH)
 340     {
 341         return $this->fetchResult($this->query($query), $mode);
 342     }
 343
 344     public function queryFetchAssoc($query)
 345     {
 346         return $this->fetchResultAssoc($this->query($query));
 347     }
 348
 349     public function queryFetchAssocKey($query, $mode = SQLITE3_BOTH)
 350     {
 351         return $this->fetchResultAssocKey($this->query($query), $mode);
 352     }
 353
 354     public function fetchResult($result, $mode = \SQLITE3_BOTH)
 355     {
 356         $out = [];
 357
 358         while ($row = $result->fetchArray($mode))
 359         {
 360             $out[] = $row;
 361         }
 362
 363         $result->finalize();
 364         unset($result, $row);
 365
 366         return $out;
 367     }
 368
 369     protected function fetchResultAssoc($result)
 370     {
 371         $out = [];
 372
 373         while ($row = $result->fetchArray(SQLITE3_NUM))
 374         {
 375             $out[$row[0]] = $row[1];
 376         }
 377
 378         $result->finalize();
 379         unset($result, $row);
 380
 381         return $out;
 382     }
 383
 384     protected function fetchResultAssocKey($result, $mode = \SQLITE3_BOTH)
 385     {
 386         $out = [];
 387
 388         while ($row = $result->fetchArray($mode))
 389         {
 390             $key = current($row);
 391             $out[$key] = $row;
 392         }
 393
 394         $result->finalize();
 395         unset($result, $row);
 396
 397         return $out;
 398     }
 399
 400     public function countRows($result)
 401     {
 402         $i = 0;
 403
 404         while ($result->fetchArray(SQLITE3_NUM))
 405             $i++;
 406
 407         return $i;
 408     }
 409 }
 410
 411 ?>