forked from Dolibarr/dolibarr
-
Notifications
You must be signed in to change notification settings - Fork 0
/
DoliDB.class.php
364 lines (337 loc) · 10.1 KB
/
DoliDB.class.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
<?php
/*
* Copyright (C) 2013-2015 Raphaël Doursenaud <rdoursenaud@gpcsolutions.fr>
* Copyright (C) 2014-2015 Laurent Destailleur <eldy@users.sourceforge.net>
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see <https://www.gnu.org/licenses/>.
*/
/**
* \file htdocs/core/db/DoliDB.class.php
* \brief Class file to manage Dolibarr database access
*/
require_once DOL_DOCUMENT_ROOT.'/core/db/Database.interface.php';
/**
* Class to manage Dolibarr database access
*/
abstract class DoliDB implements Database
{
/** @var bool|resource|SQLite3 Database handler */
public $db;
/** @var string Database type */
public $type;
/** @var string Charset used to force charset when creating database */
public $forcecharset = 'utf8';
/** @var string Collate used to force collate when creating database */
public $forcecollate = 'utf8_unicode_ci';
/** @var resource Resultset of last query */
private $_results;
/** @var bool true if connected, else false */
public $connected;
/** @var bool true if database selected, else false */
public $database_selected;
/** @var string Selected database name */
public $database_name;
/** @var string Database username */
public $database_user;
/** @var string Database host */
public $database_host;
/** @var int Database port */
public $database_port;
/** @var int >=1 if a transaction is opened, 0 otherwise */
public $transaction_opened;
/** @var string Last successful query */
public $lastquery;
/** @var string Last failed query */
public $lastqueryerror;
/** @var string Last error message */
public $lasterror;
/** @var string Last error number. For example: 'DB_ERROR_RECORD_ALREADY_EXISTS', '12345', ... */
public $lasterrno;
/** @var bool Status */
public $ok;
/** @var string */
public $error;
/**
* Format a SQL IF
*
* @param string $test Test string (example: 'cd.statut=0', 'field IS NULL')
* @param string $resok resultat si test egal
* @param string $resko resultat si test non egal
* @return string SQL string
*/
public function ifsql($test, $resok, $resko)
{
return 'IF('.$test.','.$resok.','.$resko.')';
}
/**
* Convert (by PHP) a GM Timestamp date into a string date with PHP server TZ to insert into a date field.
* Function to use to build INSERT, UPDATE or WHERE predica
*
* @param int $param Date TMS to convert
* @param mixed $gm 'gmt'=Input informations are GMT values, 'tzserver'=Local to server TZ
* @return string Date in a string YYYY-MM-DD HH:MM:SS
*/
public function idate($param, $gm = 'tzserver')
{
// TODO $param should be gmt, so we should add $gm to 'gmt' instead of default 'tzserver'
return dol_print_date($param, "%Y-%m-%d %H:%M:%S", $gm);
}
/**
* Return last error code
*
* @return string lasterrno
*/
public function lasterrno()
{
return $this->lasterrno;
}
/**
* Sanitize a string for SQL forging
*
* @param string $stringtosanitize String to escape
* @param int $allowsimplequote Allow simple quote
* @return string String escaped
*/
public function sanitize($stringtosanitize, $allowsimplequote = 0)
{
if ($allowsimplequote) {
return preg_replace('/[^a-z0-9_\-\.,\']/i', '', $stringtosanitize);
} else {
return preg_replace('/[^a-z0-9_\-\.,]/i', '', $stringtosanitize);
}
}
/**
* Start transaction
*
* @return int 1 if transaction successfuly opened or already opened, 0 if error
*/
public function begin()
{
if (!$this->transaction_opened)
{
$ret = $this->query("BEGIN");
if ($ret)
{
$this->transaction_opened++;
dol_syslog("BEGIN Transaction", LOG_DEBUG);
dol_syslog('', 0, 1);
}
return $ret;
} else {
$this->transaction_opened++;
dol_syslog('', 0, 1);
return 1;
}
}
/**
* Validate a database transaction
*
* @param string $log Add more log to default log line
* @return int 1 if validation is OK or transaction level no started, 0 if ERROR
*/
public function commit($log = '')
{
dol_syslog('', 0, -1);
if ($this->transaction_opened <= 1)
{
$ret = $this->query("COMMIT");
if ($ret)
{
$this->transaction_opened = 0;
dol_syslog("COMMIT Transaction".($log ? ' '.$log : ''), LOG_DEBUG);
return 1;
} else {
return 0;
}
} else {
$this->transaction_opened--;
return 1;
}
}
/**
* Cancel a transaction and go back to initial data values
*
* @param string $log Add more log to default log line
* @return resource|int 1 if cancelation is ok or transaction not open, 0 if error
*/
public function rollback($log = '')
{
dol_syslog('', 0, -1);
if ($this->transaction_opened <= 1)
{
$ret = $this->query("ROLLBACK");
$this->transaction_opened = 0;
dol_syslog("ROLLBACK Transaction".($log ? ' '.$log : ''), LOG_DEBUG);
return $ret;
} else {
$this->transaction_opened--;
return 1;
}
}
/**
* Define limits and offset of request
*
* @param int $limit Maximum number of lines returned (-1=conf->liste_limit, 0=no limit)
* @param int $offset Numero of line from where starting fetch
* @return string String with SQL syntax to add a limit and offset
*/
public function plimit($limit = 0, $offset = 0)
{
global $conf;
if (empty($limit)) return "";
if ($limit < 0) $limit = $conf->liste_limit;
if ($offset > 0) return " LIMIT $offset,$limit ";
else return " LIMIT $limit ";
}
/**
* Return version of database server into an array
*
* @return array Version array
*/
public function getVersionArray()
{
return preg_split("/[\.,-]/", $this->getVersion());
}
/**
* Return last request executed with query()
*
* @return string Last query
*/
public function lastquery()
{
return $this->lastquery;
}
/**
* Define sort criteria of request
*
* @param string $sortfield List of sort fields, separated by comma. Example: 't1.fielda,t2.fieldb'
* @param string $sortorder Sort order, separated by comma. Example: 'ASC,DESC';
* @return string String to provide syntax of a sort sql string
*/
public function order($sortfield = null, $sortorder = null)
{
if (!empty($sortfield))
{
$oldsortorder = '';
$return = '';
$fields = explode(',', $sortfield);
$orders = explode(',', $sortorder);
$i = 0;
foreach ($fields as $val) {
if (!$return) $return .= ' ORDER BY ';
else $return .= ', ';
if (strpos($val, 'ref') !== false ){
$return .= "lpad(".preg_replace('/[^0-9a-z_\.]/i', '', $val).", 30, '0')";
$val='';
} else {
$return .= preg_replace('/[^0-9a-z_\.]/i', '', $val); // Add field
}
$tmpsortorder = (empty($orders[$i]) ? '' : trim($orders[$i]));
// Only ASC and DESC values are valid SQL
if (strtoupper($tmpsortorder) === 'ASC') {
$oldsortorder = 'ASC';
$return .= ' ASC';
} elseif (strtoupper($tmpsortorder) === 'DESC') {
$oldsortorder = 'DESC';
$return .= ' DESC';
} else {
$return .= ' '.($oldsortorder ? $oldsortorder : 'ASC');
}
$i++;
}
return $return;
} else {
return '';
}
}
/**
* Return last error label
*
* @return string Last error
*/
public function lasterror()
{
return $this->lasterror;
}
/**
* Convert (by PHP) a PHP server TZ string date into a Timestamps date (GMT if gm=true)
* 19700101020000 -> 3600 with TZ+1 and gmt=0
* 19700101020000 -> 7200 whaterver is TZ if gmt=1
*
* @param string $string Date in a string (YYYYMMDDHHMMSS, YYYYMMDD, YYYY-MM-DD HH:MM:SS)
* @param mixed $gm 'gmt'=Input informations are GMT values, 'tzserver'=Local to server TZ
* @return int|string Date TMS or ''
*/
public function jdate($string, $gm = 'tzserver')
{
// TODO $string should be converted into a GMT timestamp, so param gm should be set to true by default instead of false
if ($string == 0 || $string == "0000-00-00 00:00:00") return '';
$string = preg_replace('/([^0-9])/i', '', $string);
$tmp = $string.'000000';
$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);
return $date;
}
/**
* Return last query in error
*
* @return string lastqueryerror
*/
public function lastqueryerror()
{
return $this->lastqueryerror;
}
/**
* Return first result from query as object
* Note : This method executes a given SQL query and retrieves the first row of results as an object. It should only be used with SELECT queries
* Dont add LIMIT to your query, it will be added by this method
* @param string $sql the sql query string
* @return bool|int|object false on failure, 0 on empty, object on success
*/
public function getRow($sql)
{
$sql .= ' LIMIT 1';
$res = $this->query($sql);
if ($res) {
$obj = $this->fetch_object($res);
if ($obj) {
return $obj;
} else {
return 0;
}
}
return false;
}
/**
* return all results from query as an array of objects
* Note : This method executes a given SQL query and retrieves all row of results as an array of objects. It should only be used with SELECT queries
* be carefull with this method use it only with some limit of results to avoid performences loss
* @param string $sql the sql query string
* @return bool| array
*/
public function getRows($sql)
{
$res = $this->query($sql);
if ($res)
{
$results = array();
if ($this->num_rows($res) > 0) {
while ($obj = $this->fetch_object($res)) {
$results[] = $obj;
}
}
return $results;
}
return false;
}
}