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         //print_r(['query' => $query, 'args' => $args]);
 195         $statement = $this->prepare($query);
 196         $nb = $statement->paramCount();
 197
 198         if (!empty($args))
 199         {
 200             if (is_array($args) && count($args) == 1 && is_array(current($args)))
 201             {
 202                 $args = current($args);
 203             }
 204
 205             if (count($args) != $nb)
 206             {
 207                 throw new \LengthException('Arguments error: '.count($args).' supplied, but '.$nb.' are required by query.');
 208             }
 209
 210             reset($args);
 211
 212             if (is_int(key($args)))
 213             {
 214                 foreach ($args as $i=>$arg)
 215                 {
 216                     $statement->bindValue((int)$i+1, $arg, $this->_getArgType($arg, $i+1));
 217                 }
 218             }
 219             else
 220             {
 221                 foreach ($args as $key=>$value)
 222                 {
 223                     if (is_int($key))
 224                     {
 225                         throw new \InvalidArgumentException(__FUNCTION__ . ' requires argument to be a named-associative array, but key '.$key.' is an integer.');
 226                     }
 227
 228                     $statement->bindValue(':'.$key, $value, $this->_getArgType($value, $key));
 229                 }
 230             }
 231         }
 232
 233         try {
 234             return $statement->execute();
 235         }
 236         catch (\Exception $e)
 237         {
 238             throw new \Exception($e->getMessage() . "\n" . $query . "\n" . json_encode($args, true));
 239         }
 240     }
 241
 242     public function simpleStatementFetch($query, $mode = SQLITE3_BOTH)
 243     {
 244         if ($mode != SQLITE3_BOTH && $mode != SQLITE3_ASSOC && $mode != SQLITE3_NUM)
 245         {
 246             throw new \InvalidArgumentException('Mode argument should be either SQLITE3_BOTH, SQLITE3_ASSOC or SQLITE3_NUM.');
 247         }
 248
 249         $args = array_slice(func_get_args(), 2);
 250         return $this->fetchResult($this->simpleStatement($query, $args), $mode);
 251     }
 252
 253     public function simpleStatementFetchAssoc($query)
 254     {
 255         $args = array_slice(func_get_args(), 1);
 256         return $this->fetchResultAssoc($this->simpleStatement($query, $args));
 257     }
 258
 259     public function simpleStatementFetchAssocKey($query, $mode = SQLITE3_BOTH)
 260     {
 261         if ($mode != SQLITE3_BOTH && $mode != SQLITE3_ASSOC && $mode != SQLITE3_NUM)
 262         {
 263             throw new \InvalidArgumentException('Mode argument should be either SQLITE3_BOTH, SQLITE3_ASSOC or SQLITE3_NUM.');
 264         }
 265
 266         $args = array_slice(func_get_args(), 2);
 267         return $this->fetchResultAssocKey($this->simpleStatement($query, $args), $mode);
 268     }
 269
 270     public function escapeAuto($value, $name = '')
 271     {
 272         $type = $this->_getArgType($value, $name);
 273
 274         switch ($type)
 275         {
 276             case SQLITE3_FLOAT:
 277                 return floatval($value);
 278             case SQLITE3_INTEGER:
 279                 return intval($value);
 280             case SQLITE3_NULL:
 281                 return 'NULL';
 282             case SQLITE3_TEXT:
 283                 return '\'' . $this->escapeString($value) . '\'';
 284         }
 285     }
 286
 287     /**
 288      * Simple INSERT query
 289      */
 290     public function simpleInsert($table, $fields)
 291     {
 292         $fields_names = array_keys($fields);
 293         return $this->simpleStatement('INSERT INTO '.$table.' ('.implode(', ', $fields_names).')
 294             VALUES (:'.implode(', :', $fields_names).');', $fields);
 295     }
 296
 297     public function simpleUpdate($table, $fields, $where)
 298     {
 299         if (empty($fields))
 300             return false;
 301
 302         $query = 'UPDATE '.$table.' SET ';
 303
 304         foreach ($fields as $key=>$value)
 305         {
 306             $query .= $key . ' = :'.$key.', ';
 307         }
 308
 309         $query = substr($query, 0, -2);
 310         $query .= ' WHERE '.$where.';';
 311         return $this->simpleStatement($query, $fields);
 312     }
 313
 314     /**
 315      * Formats and escapes a statement and then returns the result of exec()
 316      */
 317     public function simpleExec($query)
 318     {
 319         return $this->simpleStatement($query, array_slice(func_get_args(), 1));
 320     }
 321
 322     public function simpleQuerySingle($query, $all_columns = false)
 323     {
 324         $res = $this->simpleStatement($query, array_slice(func_get_args(), 2));
 325
 326         $row = $res->fetchArray($all_columns ? SQLITE3_ASSOC : SQLITE3_NUM);
 327
 328         if (!$all_columns)
 329         {
 330             if (isset($row[0]))
 331                 return $row[0];
 332             return false;
 333         }
 334         else
 335         {
 336             return $row;
 337         }
 338     }
 339
 340     public function queryFetch($query, $mode = SQLITE3_BOTH)
 341     {
 342         return $this->fetchResult($this->query($query), $mode);
 343     }
 344
 345     public function queryFetchAssoc($query)
 346     {
 347         return $this->fetchResultAssoc($this->query($query));
 348     }
 349
 350     public function queryFetchAssocKey($query, $mode = SQLITE3_BOTH)
 351     {
 352         return $this->fetchResultAssocKey($this->query($query), $mode);
 353     }
 354
 355     public function fetchResult($result, $mode = \SQLITE3_BOTH)
 356     {
 357         $out = [];
 358
 359         while ($row = $result->fetchArray($mode))
 360         {
 361             $out[] = $row;
 362         }
 363
 364         $result->finalize();
 365         unset($result, $row);
 366
 367         return $out;
 368     }
 369
 370     protected function fetchResultAssoc($result)
 371     {
 372         $out = [];
 373
 374         while ($row = $result->fetchArray(SQLITE3_NUM))
 375         {
 376             $out[$row[0]] = $row[1];
 377         }
 378
 379         $result->finalize();
 380         unset($result, $row);
 381
 382         return $out;
 383     }
 384
 385     protected function fetchResultAssocKey($result, $mode = \SQLITE3_BOTH)
 386     {
 387         $out = [];
 388
 389         while ($row = $result->fetchArray($mode))
 390         {
 391             $key = current($row);
 392             $out[$key] = $row;
 393         }
 394
 395         $result->finalize();
 396         unset($result, $row);
 397
 398         return $out;
 399     }
 400
 401     public function countRows($result)
 402     {
 403         $i = 0;
 404
 405         while ($result->fetchArray(SQLITE3_NUM))
 406             $i++;
 407
 408         return $i;
 409     }
 410 }
 411
 412 ?>