dolibarr 18.0.6
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 *
6 * This program is free software; you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation; either version 3 of the License, or
9 * (at your option) any later version.
10 *
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
15 *
16 * You should have received a copy of the GNU General Public License
17 * along with this program. If not, see <https://www.gnu.org/licenses/>.
18 */
19
25require_once DOL_DOCUMENT_ROOT.'/core/db/Database.interface.php';
26
30abstract class DoliDB implements Database
31{
33 public $db;
35 public $type;
37 public $forcecharset = 'utf8';
39 public $forcecollate = 'utf8_unicode_ci';
40
42 private $_results;
43
45 public $connected;
47 public $database_selected;
49 public $database_name;
51 public $database_user;
53 public $database_host;
55 public $database_port;
57 public $transaction_opened;
59 public $lastquery;
61 public $lastqueryerror;
63 public $lasterror;
65 public $lasterrno;
66
68 public $prefix_db;
69
71 public $ok;
73 public $error;
74
75
76
83 public function prefix()
84 {
85 return (empty($this->prefix_db) ? MAIN_DB_PREFIX : $this->prefix_db);
86 }
87
96 public function ifsql($test, $resok, $resko)
97 {
98 //return 'IF('.$test.','.$resok.','.$resko.')'; // Not sql standard
99 return '(CASE WHEN '.$test.' THEN '.$resok.' ELSE '.$resko.' END)';
100 }
101
108 public function hintindex($nameofindex)
109 {
110 return '';
111 }
112
113
122 public function regexpsql($subject, $pattern, $sqlstring = false)
123 {
124 if ($sqlstring) {
125 return "(". $subject ." REGEXP '" . $pattern . "')";
126 }
127
128 return "('". $subject ."' REGEXP '" . $pattern . "')";
129 }
130
131
140 public function idate($param, $gm = 'tzserver')
141 {
142 // TODO $param should be gmt, so we should have default $gm to 'gmt' instead of default 'tzserver'
143 return dol_print_date($param, "%Y-%m-%d %H:%M:%S", $gm);
144 }
145
151 public function lasterrno()
152 {
153 return $this->lasterrno;
154 }
155
165 public function sanitize($stringtosanitize, $allowsimplequote = 0, $allowsequals = 0, $allowsspace = 0)
166 {
167 return preg_replace('/[^a-z0-9_\-\.,'.($allowsequals ? '=' : '').($allowsimplequote ? "\'" : '').($allowsspace ? ' ' : '').']/i', '', $stringtosanitize);
168 }
169
176 public function begin($textinlog = '')
177 {
178 if (!$this->transaction_opened) {
179 $ret = $this->query("BEGIN");
180 if ($ret) {
181 $this->transaction_opened++;
182 dol_syslog("BEGIN Transaction".($textinlog ? ' '.$textinlog : ''), LOG_DEBUG);
183 dol_syslog('', 0, 1);
184 }
185 return $ret;
186 } else {
187 $this->transaction_opened++;
188 dol_syslog('', 0, 1);
189 return 1;
190 }
191 }
192
199 public function commit($log = '')
200 {
201 dol_syslog('', 0, -1);
202 if ($this->transaction_opened <= 1) {
203 $ret = $this->query("COMMIT");
204 if ($ret) {
205 $this->transaction_opened = 0;
206 dol_syslog("COMMIT Transaction".($log ? ' '.$log : ''), LOG_DEBUG);
207 return 1;
208 } else {
209 return 0;
210 }
211 } else {
212 $this->transaction_opened--;
213 return 1;
214 }
215 }
216
223 public function rollback($log = '')
224 {
225 dol_syslog('', 0, -1);
226 if ($this->transaction_opened <= 1) {
227 $ret = $this->query("ROLLBACK");
228 $this->transaction_opened = 0;
229 dol_syslog("ROLLBACK Transaction".($log ? ' '.$log : ''), LOG_DEBUG);
230 return $ret;
231 } else {
232 $this->transaction_opened--;
233 return 1;
234 }
235 }
236
244 public function plimit($limit = 0, $offset = 0)
245 {
246 global $conf;
247 if (empty($limit)) {
248 return "";
249 }
250 if ($limit < 0) {
251 $limit = $conf->liste_limit;
252 }
253 if ($offset > 0) {
254 return " LIMIT ".((int) $offset).",".((int) $limit)." ";
255 } else {
256 return " LIMIT ".((int) $limit)." ";
257 }
258 }
259
265 public function getVersionArray()
266 {
267 return preg_split("/[\.,-]/", $this->getVersion());
268 }
269
275 public function lastquery()
276 {
277 return $this->lastquery;
278 }
279
287 public function order($sortfield = '', $sortorder = '')
288 {
289 if (!empty($sortfield)) {
290 $oldsortorder = '';
291 $return = '';
292 $fields = explode(',', $sortfield);
293 $orders = explode(',', $sortorder);
294 $i = 0;
295 foreach ($fields as $val) {
296 if (!$return) {
297 $return .= ' ORDER BY ';
298 } else {
299 $return .= ', ';
300 }
301
302 $return .= preg_replace('/[^0-9a-z_\.]/i', '', $val); // Add field
303
304 $tmpsortorder = (empty($orders[$i]) ? '' : trim($orders[$i]));
305
306 // Only ASC and DESC values are valid SQL
307 if (strtoupper($tmpsortorder) === 'ASC') {
308 $oldsortorder = 'ASC';
309 $return .= ' ASC';
310 } elseif (strtoupper($tmpsortorder) === 'DESC') {
311 $oldsortorder = 'DESC';
312 $return .= ' DESC';
313 } else {
314 $return .= ' '.($oldsortorder ? $oldsortorder : 'ASC');
315 }
316
317 $i++;
318 }
319 return $return;
320 } else {
321 return '';
322 }
323 }
324
330 public function lasterror()
331 {
332 return $this->lasterror;
333 }
334
344 public function jdate($string, $gm = 'tzserver')
345 {
346 // TODO $string should be converted into a GMT timestamp, so param gm should be set to true by default instead of false
347 if ($string == 0 || $string == "0000-00-00 00:00:00") {
348 return '';
349 }
350 $string = preg_replace('/([^0-9])/i', '', $string);
351 $tmp = $string.'000000';
352 $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);
353 return $date;
354 }
355
361 public function lastqueryerror()
362 {
363 return $this->lastqueryerror;
364 }
365
374 public function getRow($sql)
375 {
376 $sql .= ' LIMIT 1';
377
378 $res = $this->query($sql);
379 if ($res) {
380 $obj = $this->fetch_object($res);
381 if ($obj) {
382 return $obj;
383 } else {
384 return 0;
385 }
386 }
387
388 return false;
389 }
390
400 public function getRows($sql)
401 {
402 $res = $this->query($sql);
403 if ($res) {
404 $results = array();
405 if ($this->num_rows($res) > 0) {
406 while ($obj = $this->fetch_object($res)) {
407 $results[] = $obj;
408 }
409 }
410 return $results;
411 }
412
413 return false;
414 }
415}
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...
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.
prefix()
Return the DB prefix found into prefix_db (if it was set manually by doing $dbhandler->prefix_db=....
hintindex($nameofindex)
Return SQL string to force an index.
sanitize($stringtosanitize, $allowsimplequote=0, $allowsequals=0, $allowsspace=0)
Sanitize a string for SQL forging.
getRows($sql)
Return all results from query as an array of objects Note : This method executes a given SQL query an...
order($sortfield='', $sortorder='')
Define sort criteria of request.
regexpsql($subject, $pattern, $sqlstring=false)
Format a SQL REGEXP.
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 informations (by default a local PHP server timestamp) Re...
dol_print_date($time, $format='', $tzoutput='auto', $outputlangs='', $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.
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.