5 function str_replace_first ($search, $replace, $subject)
7 $pos = strpos($subject, $search);
11 $subject = substr_replace($subject, $replace, $pos, strlen($search));
17 class DB
extends \SQLite3
19 static protected $_instance = null;
21 protected $_running_sum = 0.0;
23 protected $_transaction = 0;
25 const NUM
= \SQLITE3_NUM
;
26 const ASSOC
= \SQLITE3_ASSOC
;
27 const BOTH
= \SQLITE3_BOTH
;
29 static public function getInstance($create = false)
31 return self
::$_instance ?
: self
::$_instance = new DB($create);
34 private function __clone()
38 public function __construct($create = false)
40 $flags = SQLITE3_OPEN_READWRITE
;
44 $flags |
= SQLITE3_OPEN_CREATE
;
47 parent
::__construct(DB_FILE
, $flags);
49 $this->enableExceptions(true);
51 // Activer les contraintes des foreign keys
52 $this->exec('PRAGMA foreign_keys = ON;');
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']);
60 public function sql_running_sum($data)
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
))
66 $this->_running_sum
= 0.0;
67 return $this->_running_sum
;
70 $this->_running_sum +
= $data;
71 return $this->_running_sum
;
74 public function resetRunningSum()
76 $this->_running_sum
= null;
79 public function sql_rank($aMatchInfo)
81 $iSize = 4; // byte size
82 $iPhrase = (int) 0; // Current phrase //
83 $score = (double)0.0; // Value to return //
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.
91 $aMatchInfo = (string) func_get_arg(0);
92 $nPhrase = ord(substr($aMatchInfo, 0, $iSize));
93 $nCol = ord(substr($aMatchInfo, $iSize, $iSize));
95 if (func_num_args() > (1 +
$nCol))
97 throw new \
Exception("Invalid number of arguments : ".$nCol);
100 // Iterate through each phrase in the users query. //
101 for ($iPhrase = 0; $iPhrase < $nPhrase; $iPhrase++
)
103 $iCol = (int) 0; // Current column //
105 /* Now iterate through each column in the users query. For each column,
106 ** increment the relevancy score by:
108 ** (<hit count> / <global hit count>) * <column weight>
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.
114 $aPhraseinfo = substr($aMatchInfo, (2 +
$iPhrase * $nCol * 3) * $iSize);
116 for ($iCol = 0; $iCol < $nCol; $iCol++
)
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;
124 $score +
= ((double)$nHitCount / (double)$nGlobalHitCount) * $weight;
132 public function escape($str)
134 return $this->escapeString($str);
137 public function e($str)
139 return $this->escapeString($str);
142 public function begin()
144 if (!$this->_transaction
)
146 $this->exec('BEGIN;');
149 $this->_transaction++
;
151 return $this->_transaction
== 1 ?
true : false;
154 public function commit()
156 if ($this->_transaction
== 1)
161 if ($this->_transaction
> 0)
163 $this->_transaction
--;
166 return $this->_transaction ?
false : true;
169 public function rollback()
171 $this->exec('ROLLBACK;');
172 $this->_transaction
= 0;
176 protected function _getArgType($arg, $name = '')
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))
186 elseif (is_string($arg))
189 throw new \
InvalidArgumentException('Argument '.$name.' is of invalid type '.gettype($arg));
192 public function simpleStatement($query, $args = [])
194 $statement = $this->prepare($query);
195 $nb = $statement->paramCount();
199 if (is_array($args) && count($args) == 1 && is_array(current($args)))
201 $args = current($args);
204 if (count($args) != $nb)
206 throw new \
LengthException('Arguments error: '.count($args).' supplied, but '.$nb.' are required by query.');
211 if (is_int(key($args)))
213 foreach ($args as $i=>$arg)
215 $statement->bindValue((int)$i+
1, $arg, $this->_getArgType($arg, $i+
1));
220 foreach ($args as $key=>$value)
224 throw new \
InvalidArgumentException(__FUNCTION__
. ' requires argument to be a named-associative array, but key '.$key.' is an integer.');
227 $statement->bindValue(':'.$key, $value, $this->_getArgType($value, $key));
233 return $statement->execute();
235 catch (\Exception
$e)
237 throw new \
Exception($e->getMessage() . "\n" . $query . "\n" . json_encode($args, true));
241 public function simpleStatementFetch($query, $mode = SQLITE3_BOTH
)
243 if ($mode != SQLITE3_BOTH
&& $mode != SQLITE3_ASSOC
&& $mode != SQLITE3_NUM
)
245 throw new \
InvalidArgumentException('Mode argument should be either SQLITE3_BOTH, SQLITE3_ASSOC or SQLITE3_NUM.');
248 $args = array_slice(func_get_args(), 2);
249 return $this->fetchResult($this->simpleStatement($query, $args), $mode);
252 public function simpleStatementFetchAssoc($query)
254 $args = array_slice(func_get_args(), 1);
255 return $this->fetchResultAssoc($this->simpleStatement($query, $args));
258 public function simpleStatementFetchAssocKey($query, $mode = SQLITE3_BOTH
)
260 if ($mode != SQLITE3_BOTH
&& $mode != SQLITE3_ASSOC
&& $mode != SQLITE3_NUM
)
262 throw new \
InvalidArgumentException('Mode argument should be either SQLITE3_BOTH, SQLITE3_ASSOC or SQLITE3_NUM.');
265 $args = array_slice(func_get_args(), 2);
266 return $this->fetchResultAssocKey($this->simpleStatement($query, $args), $mode);
269 public function escapeAuto($value, $name = '')
271 $type = $this->_getArgType($value, $name);
276 return floatval($value);
277 case SQLITE3_INTEGER
:
278 return intval($value);
282 return '\'' . $this->escapeString($value) . '\'';
287 * Simple INSERT query
289 public function simpleInsert($table, $fields)
291 $fields_names = array_keys($fields);
292 return $this->simpleStatement('INSERT INTO '.$table.' ('.implode(', ', $fields_names).')
293 VALUES (:'.implode(', :', $fields_names).');', $fields);
296 public function simpleUpdate($table, $fields, $where)
301 $query = 'UPDATE '.$table.' SET ';
303 foreach ($fields as $key=>$value)
305 $query .= $key . ' = :'.$key.', ';
308 $query = substr($query, 0, -2);
309 $query .= ' WHERE '.$where.';';
310 return $this->simpleStatement($query, $fields);
314 * Formats and escapes a statement and then returns the result of exec()
316 public function simpleExec($query)
318 return $this->simpleStatement($query, array_slice(func_get_args(), 1));
321 public function simpleQuerySingle($query, $all_columns = false)
323 $res = $this->simpleStatement($query, array_slice(func_get_args(), 2));
325 $row = $res->fetchArray($all_columns ? SQLITE3_ASSOC
: SQLITE3_NUM
);
339 public function queryFetch($query, $mode = SQLITE3_BOTH
)
341 return $this->fetchResult($this->query($query), $mode);
344 public function queryFetchAssoc($query)
346 return $this->fetchResultAssoc($this->query($query));
349 public function queryFetchAssocKey($query, $mode = SQLITE3_BOTH
)
351 return $this->fetchResultAssocKey($this->query($query), $mode);
354 public function fetchResult($result, $mode = \SQLITE3_BOTH
)
358 while ($row = $result->fetchArray($mode))
364 unset($result, $row);
369 protected function fetchResultAssoc($result)
373 while ($row = $result->fetchArray(SQLITE3_NUM
))
375 $out[$row[0]] = $row[1];
379 unset($result, $row);
384 protected function fetchResultAssocKey($result, $mode = \SQLITE3_BOTH
)
388 while ($row = $result->fetchArray($mode))
390 $key = current($row);
395 unset($result, $row);
400 public function countRows($result)
404 while ($result->fetchArray(SQLITE3_NUM
))