dolibarr 20.0.0
DoliDB.class.php
Go to the documentation of this file.
1<?php
2/*
3 * Copyright (C) 2013-2015 Raphaël Doursenaud <rdoursenaud@gpcsolutions.fr>
4 * Copyright (C) 2014-2015 Laurent Destailleur <eldy@users.sourceforge.net>
5 * Copyright (C) 2024 MDW <mdeweerd@users.noreply.github.com>
6 * Copyright (C) 2024 Frédéric France <frederic.france@free.fr>
7 *
8 * This program is free software; you can redistribute it and/or modify
9 * it under the terms of the GNU General Public License as published by
10 * the Free Software Foundation; either version 3 of the License, or
11 * (at your option) any later version.
12 *
13 * This program is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 * GNU General Public License for more details.
17 *
18 * You should have received a copy of the GNU General Public License
19 * along with this program. If not, see <https://www.gnu.org/licenses/>.
20 */
21
27require_once DOL_DOCUMENT_ROOT.'/core/db/Database.interface.php';
28
29
33abstract class DoliDB implements Database
34{
39
41 public $db;
43 public $type;
45 public $forcecharset = 'utf8';
47 public $forcecollate = 'utf8_unicode_ci';
48
50 private $_results;
51
53 public $connected;
55 public $database_selected;
57 public $database_name;
59 public $database_user;
61 public $database_host;
63 public $database_port;
65 public $transaction_opened;
67 public $lastquery;
69 public $lastqueryerror;
71 public $lasterror;
73 public $lasterrno;
74
76 public $prefix_db;
77
79 public $ok;
81 public $error;
82
83
84
91 public function prefix()
92 {
93 return (empty($this->prefix_db) ? MAIN_DB_PREFIX : $this->prefix_db);
94 }
95
104 public function ifsql($test, $resok, $resko)
105 {
106 //return 'IF('.$test.','.$resok.','.$resko.')'; // Not sql standard
107 return '(CASE WHEN '.$test.' THEN '.$resok.' ELSE '.$resko.' END)';
108 }
109
116 public function stddevpop($nameoffield)
117 {
118 return 'STDDEV_POP('.$nameoffield.')';
119 }
120
127 public function hintindex($nameofindex)
128 {
129 return '';
130 }
131
132
141 public function regexpsql($subject, $pattern, $sqlstring = 0)
142 {
143 if ($sqlstring) {
144 return "(". $subject ." REGEXP '" . $this->escape($pattern) . "')";
145 }
146
147 return "('". $this->escape($subject) ."' REGEXP '" . $this->escape($pattern) . "')";
148 }
149
150
159 public function idate($param, $gm = 'tzserver')
160 {
161 // TODO $param should be gmt, so we should have default $gm to 'gmt' instead of default 'tzserver'
162 return dol_print_date($param, "%Y-%m-%d %H:%M:%S", $gm);
163 }
164
170 public function lasterrno()
171 {
172 return $this->lasterrno;
173 }
174
185 public function sanitize($stringtosanitize, $allowsimplequote = 0, $allowsequals = 0, $allowsspace = 0, $allowschars = 1)
186 {
187 return preg_replace('/[^0-9_\-\.,'.($allowschars ? 'a-z' : '').($allowsequals ? '=' : '').($allowsimplequote ? "\'" : '').($allowsspace ? ' ' : '').']/i', '', $stringtosanitize);
188 }
189
196 public function begin($textinlog = '')
197 {
198 if (!$this->transaction_opened) {
199 $ret = $this->query("BEGIN");
200 if ($ret) {
201 $this->transaction_opened++;
202 dol_syslog("BEGIN Transaction".($textinlog ? ' '.$textinlog : ''), LOG_DEBUG);
203 dol_syslog('', 0, 1);
204 return 1;
205 } else {
206 return 0;
207 }
208 } else {
209 $this->transaction_opened++;
210 dol_syslog('', 0, 1);
211 return 1;
212 }
213 }
214
221 public function commit($log = '')
222 {
223 dol_syslog('', 0, -1);
224 if ($this->transaction_opened <= 1) {
225 $ret = $this->query("COMMIT");
226 if ($ret) {
227 $this->transaction_opened = 0;
228 dol_syslog("COMMIT Transaction".($log ? ' '.$log : ''), LOG_DEBUG);
229 return 1;
230 } else {
231 return 0;
232 }
233 } else {
234 $this->transaction_opened--;
235 return 1;
236 }
237 }
238
245 public function rollback($log = '')
246 {
247 dol_syslog('', 0, -1);
248 if ($this->transaction_opened <= 1) {
249 $ret = $this->query("ROLLBACK");
250 $this->transaction_opened = 0;
251 dol_syslog("ROLLBACK Transaction".($log ? ' '.$log : ''), LOG_DEBUG);
252 return $ret;
253 } else {
254 $this->transaction_opened--;
255 return 1;
256 }
257 }
258
266 public function plimit($limit = 0, $offset = 0)
267 {
268 global $conf;
269 if (empty($limit)) {
270 return "";
271 }
272 if ($limit < 0) {
273 $limit = $conf->liste_limit;
274 }
275 if ($offset > 0) {
276 return " LIMIT ".((int) $offset).",".((int) $limit)." ";
277 } else {
278 return " LIMIT ".((int) $limit)." ";
279 }
280 }
281
287 public function getVersionArray()
288 {
289 return preg_split("/[\.,-]/", $this->getVersion());
290 }
291
297 public function lastquery()
298 {
299 return $this->lastquery;
300 }
301
309 public function order($sortfield = '', $sortorder = '')
310 {
311 if (!empty($sortfield)) {
312 $oldsortorder = '';
313 $return = '';
314 $fields = explode(',', $sortfield);
315 $orders = (!empty($sortorder) ? explode(',', $sortorder) : array());
316 $i = 0;
317 foreach ($fields as $val) {
318 if (!$return) {
319 $return .= ' ORDER BY ';
320 } else {
321 $return .= ', ';
322 }
323
324 $return .= preg_replace('/[^0-9a-z_\.]/i', '', $val); // Add field
325
326 $tmpsortorder = (empty($orders[$i]) ? '' : trim($orders[$i]));
327
328 // Only ASC and DESC values are valid SQL
329 if (strtoupper($tmpsortorder) === 'ASC') {
330 $oldsortorder = 'ASC';
331 $return .= ' ASC';
332 } elseif (strtoupper($tmpsortorder) === 'DESC') {
333 $oldsortorder = 'DESC';
334 $return .= ' DESC';
335 } else {
336 $return .= ' '.($oldsortorder ? $oldsortorder : 'ASC');
337 }
338
339 $i++;
340 }
341 return $return;
342 } else {
343 return '';
344 }
345 }
346
352 public function lasterror()
353 {
354 return $this->lasterror;
355 }
356
366 public function jdate($string, $gm = 'tzserver')
367 {
368 // TODO $string should be converted into a GMT timestamp, so param gm should be set to true by default instead of false
369 if ($string == 0 || $string == "0000-00-00 00:00:00") {
370 return '';
371 }
372 $string = preg_replace('/([^0-9])/i', '', $string);
373 $tmp = $string.'000000';
374 $date = dol_mktime((int) substr($tmp, 8, 2), (int) substr($tmp, 10, 2), (int) substr($tmp, 12, 2), (int) substr($tmp, 4, 2), (int) substr($tmp, 6, 2), (int) substr($tmp, 0, 4), $gm);
375 return $date;
376 }
377
383 public function lastqueryerror()
384 {
385 return $this->lastqueryerror;
386 }
387
396 public function getRow($sql)
397 {
398 $sql .= ' LIMIT 1';
399
400 $resql = $this->query($sql);
401 if ($resql) {
402 $obj = $this->fetch_object($resql);
403 if ($obj) {
404 $this->free($resql);
405 return $obj;
406 } else {
407 return 0;
408 }
409 }
410
411 return false;
412 }
413
422 public function getRows($sql)
423 {
424 if (!preg_match('/LIMIT \d+(?:(?:,\ *\d*)|(?:\ +OFFSET\ +\d*))?\ *;?$/', $sql)) {
425 trigger_error(__CLASS__ .'::'.__FUNCTION__.'() query must have a LIMIT clause', E_USER_ERROR);
426 }
427
428 $resql = $this->query($sql);
429 if ($resql) {
430 $results = array();
431 if ($this->num_rows($resql) > 0) {
432 while ($obj = $this->fetch_object($resql)) {
433 $results[] = $obj;
434 }
435 }
436 $this->free($resql);
437 return $results;
438 }
439
440 return false;
441 }
442}
Class to manage Dolibarr database access.
commit($log='')
Validate a database transaction.
idate($param, $gm='tzserver')
Convert (by PHP) a GM Timestamp date into a string date with PHP server TZ to insert into a date fiel...
const LABEL
Force subclass to implement LABEL - description of DB type.
ifsql($test, $resok, $resko)
Format a SQL IF.
getVersionArray()
Return version of database server into an array.
lastqueryerror()
Return last query in error.
begin($textinlog='')
Start transaction.
rollback($log='')
Cancel a transaction and go back to initial data values.
lasterror()
Return last error label.
getRow($sql)
Return first result from query as object Note : This method executes a given SQL query and retrieves ...
lasterrno()
Return last error code.
lastquery()
Return last request executed with query()
plimit($limit=0, $offset=0)
Define limits and offset of request.
stddevpop($nameoffield)
Return SQL string to aggregate using the Standard Deviation of population.
prefix()
Return the DB prefix found into prefix_db (if it was set manually by doing $dbhandler->prefix_db=....
sanitize($stringtosanitize, $allowsimplequote=0, $allowsequals=0, $allowsspace=0, $allowschars=1)
Sanitize a string for SQL forging.
hintindex($nameofindex)
Return SQL string to force an index.
regexpsql($subject, $pattern, $sqlstring=0)
Format a SQL REGEXP.
getRows($sql)
Return all results from query as an array of objects.
order($sortfield='', $sortorder='')
Define sort criteria of request.
const VERSIONMIN
Force subclass to implement VERSIONMIN - required DB version.
jdate($string, $gm='tzserver')
Convert (by PHP) a PHP server TZ string date into a Timestamps date (GMT if gm=true) 19700101020000 -...
dol_mktime($hour, $minute, $second, $month, $day, $year, $gm='auto', $check=1)
Return a timestamp date built from detailed information (by default a local PHP server timestamp) Rep...
dol_print_date($time, $format='', $tzoutput='auto', $outputlangs=null, $encodetooutput=false)
Output date in a string format according to outputlangs (or langs if not defined).
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Class to manage Dolibarr database access for an SQL database.
query($query, $usesavepoint=0, $type='auto', $result_mode=0)
Execute a SQL request and return the resultset.
getVersion()
Return version of database server.
escape($stringtoencode)
Escape a string to insert data.
fetch_object($resultset)
Returns the current line (as an object) for the resultset cursor.
num_rows($resultset)
Return number of lines for result of a SELECT.
free($resultset=null)
Free last resultset used.