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 //print_r(['query' => $query, 'args' => $args]);
195 $statement = $this->prepare($query);
196 $nb = $statement->paramCount();
200 if (is_array($args) && count($args) == 1 && is_array(current($args)))
202 $args = current($args);
205 if (count($args) != $nb)
207 throw new \
LengthException('Arguments error: '.count($args).' supplied, but '.$nb.' are required by query.');
212 if (is_int(key($args)))
214 foreach ($args as $i=>$arg)
216 $statement->bindValue((int)$i+
1, $arg, $this->_getArgType($arg, $i+
1));
221 foreach ($args as $key=>$value)
225 throw new \
InvalidArgumentException(__FUNCTION__
. ' requires argument to be a named-associative array, but key '.$key.' is an integer.');
228 $statement->bindValue(':'.$key, $value, $this->_getArgType($value, $key));
234 return $statement->execute();
236 catch (\Exception
$e)
238 throw new \
Exception($e->getMessage() . "\n" . $query . "\n" . json_encode($args, true));
242 public function simpleStatementFetch($query, $mode = SQLITE3_BOTH
)
244 if ($mode != SQLITE3_BOTH
&& $mode != SQLITE3_ASSOC
&& $mode != SQLITE3_NUM
)
246 throw new \
InvalidArgumentException('Mode argument should be either SQLITE3_BOTH, SQLITE3_ASSOC or SQLITE3_NUM.');
249 $args = array_slice(func_get_args(), 2);
250 return $this->fetchResult($this->simpleStatement($query, $args), $mode);
253 public function simpleStatementFetchAssoc($query)
255 $args = array_slice(func_get_args(), 1);
256 return $this->fetchResultAssoc($this->simpleStatement($query, $args));
259 public function simpleStatementFetchAssocKey($query, $mode = SQLITE3_BOTH
)
261 if ($mode != SQLITE3_BOTH
&& $mode != SQLITE3_ASSOC
&& $mode != SQLITE3_NUM
)
263 throw new \
InvalidArgumentException('Mode argument should be either SQLITE3_BOTH, SQLITE3_ASSOC or SQLITE3_NUM.');
266 $args = array_slice(func_get_args(), 2);
267 return $this->fetchResultAssocKey($this->simpleStatement($query, $args), $mode);
270 public function escapeAuto($value, $name = '')
272 $type = $this->_getArgType($value, $name);
277 return floatval($value);
278 case SQLITE3_INTEGER
:
279 return intval($value);
283 return '\'' . $this->escapeString($value) . '\'';
288 * Simple INSERT query
290 public function simpleInsert($table, $fields)
292 $fields_names = array_keys($fields);
293 return $this->simpleStatement('INSERT INTO '.$table.' ('.implode(', ', $fields_names).')
294 VALUES (:'.implode(', :', $fields_names).');', $fields);
297 public function simpleUpdate($table, $fields, $where)
302 $query = 'UPDATE '.$table.' SET ';
304 foreach ($fields as $key=>$value)
306 $query .= $key . ' = :'.$key.', ';
309 $query = substr($query, 0, -2);
310 $query .= ' WHERE '.$where.';';
311 return $this->simpleStatement($query, $fields);
315 * Formats and escapes a statement and then returns the result of exec()
317 public function simpleExec($query)
319 return $this->simpleStatement($query, array_slice(func_get_args(), 1));
322 public function simpleQuerySingle($query, $all_columns = false)
324 $res = $this->simpleStatement($query, array_slice(func_get_args(), 2));
326 $row = $res->fetchArray($all_columns ? SQLITE3_ASSOC
: SQLITE3_NUM
);
340 public function queryFetch($query, $mode = SQLITE3_BOTH
)
342 return $this->fetchResult($this->query($query), $mode);
345 public function queryFetchAssoc($query)
347 return $this->fetchResultAssoc($this->query($query));
350 public function queryFetchAssocKey($query, $mode = SQLITE3_BOTH
)
352 return $this->fetchResultAssocKey($this->query($query), $mode);
355 public function fetchResult($result, $mode = \SQLITE3_BOTH
)
359 while ($row = $result->fetchArray($mode))
365 unset($result, $row);
370 protected function fetchResultAssoc($result)
374 while ($row = $result->fetchArray(SQLITE3_NUM
))
376 $out[$row[0]] = $row[1];
380 unset($result, $row);
385 protected function fetchResultAssocKey($result, $mode = \SQLITE3_BOTH
)
389 while ($row = $result->fetchArray($mode))
391 $key = current($row);
396 unset($result, $row);
401 public function countRows($result)
405 while ($result->fetchArray(SQLITE3_NUM
))