dolibarr  7.0.0-beta
sqlite3.class.php
1 <?php
2 /* Copyright (C) 2001 Fabien Seisen <seisen@linuxfr.org>
3  * Copyright (C) 2002-2005 Rodolphe Quiedeville <rodolphe@quiedeville.org>
4  * Copyright (C) 2004-2011 Laurent Destailleur <eldy@users.sourceforge.net>
5  * Copyright (C) 2006 Andre Cianfarani <acianfa@free.fr>
6  * Copyright (C) 2005-2009 Regis Houssin <regis.houssin@capnetworks.com>
7  * Copyright (C) 2015 Raphaël Doursenaud <rdoursenaud@gpcsolutions.fr>
8  *
9  * This program is free software; you can redistribute it and/or modify
10  * it under the terms of the GNU General Public License as published by
11  * the Free Software Foundation; either version 3 of the License, or
12  * (at your option) any later version.
13  *
14  * This program is distributed in the hope that it will be useful,
15  * but WITHOUT ANY WARRANTY; without even the implied warranty of
16  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17  * GNU General Public License for more details.
18  *
19  * You should have received a copy of the GNU General Public License
20  * along with this program. If not, see <http://www.gnu.org/licenses/>.
21  */
22 
28 require_once DOL_DOCUMENT_ROOT .'/core/db/DoliDB.class.php';
29 
33 class DoliDBSqlite3 extends DoliDB
34 {
36  public $type='sqlite3';
38  const LABEL='Sqlite3';
40  const VERSIONMIN='3.0.0';
42  private $_results;
43 
44  const WEEK_MONDAY_FIRST=1;
45  const WEEK_YEAR = 2;
46  const WEEK_FIRST_WEEKDAY=4;
47 
48 
60  function __construct($type, $host, $user, $pass, $name='', $port=0)
61  {
62  global $conf;
63 
64  // Note that having "static" property for "$forcecharset" and "$forcecollate" will make error here in strict mode, so they are not static
65  if (! empty($conf->db->character_set)) $this->forcecharset=$conf->db->character_set;
66  if (! empty($conf->db->dolibarr_main_db_collation)) $this->forcecollate=$conf->db->dolibarr_main_db_collation;
67 
68  $this->database_user=$user;
69  $this->database_host=$host;
70  $this->database_port=$port;
71 
72  $this->transaction_opened=0;
73 
74  //print "Name DB: $host,$user,$pass,$name<br>";
75 
76  /*if (! function_exists("sqlite_query"))
77  {
78  $this->connected = false;
79  $this->ok = false;
80  $this->error="Sqlite PHP functions for using Sqlite driver are not available in this version of PHP. Try to use another driver.";
81  dol_syslog(get_class($this)."::DoliDBSqlite3 : Sqlite PHP functions for using Sqlite driver are not available in this version of PHP. Try to use another driver.",LOG_ERR);
82  return $this->ok;
83  }*/
84 
85  /*if (! $host)
86  {
87  $this->connected = false;
88  $this->ok = false;
89  $this->error=$langs->trans("ErrorWrongHostParameter");
90  dol_syslog(get_class($this)."::DoliDBSqlite3 : Erreur Connect, wrong host parameters",LOG_ERR);
91  return $this->ok;
92  }*/
93 
94  // Essai connexion serveur
95  // We do not try to connect to database, only to server. Connect to database is done later in constrcutor
96  $this->db = $this->connect($host, $user, $pass, $name, $port);
97 
98  if ($this->db)
99  {
100  $this->connected = true;
101  $this->ok = true;
102  $this->database_selected = true;
103  $this->database_name = $name;
104 
105  $this->addCustomFunction('IF');
106  $this->addCustomFunction('MONTH');
107  $this->addCustomFunction('CURTIME');
108  $this->addCustomFunction('CURDATE');
109  $this->addCustomFunction('WEEK', 1);
110  $this->addCustomFunction('WEEK', 2);
111  $this->addCustomFunction('WEEKDAY');
112  $this->addCustomFunction('date_format');
113  //$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
114  }
115  else
116  {
117  // host, login ou password incorrect
118  $this->connected = false;
119  $this->ok = false;
120  $this->database_selected = false;
121  $this->database_name = '';
122  //$this->error=sqlite_connect_error();
123  dol_syslog(get_class($this)."::DoliDBSqlite3 : Error Connect ".$this->error,LOG_ERR);
124  }
125 
126  return $this->ok;
127  }
128 
129 
137  static function convertSQLFromMysql($line,$type='ddl')
138  {
139  // Removed empty line if this is a comment line for SVN tagging
140  if (preg_match('/^--\s\$Id/i',$line)) {
141  return '';
142  }
143  // Return line if this is a comment
144  if (preg_match('/^#/i',$line) || preg_match('/^$/i',$line) || preg_match('/^--/i',$line))
145  {
146  return $line;
147  }
148  if ($line != "")
149  {
150  if ($type == 'auto')
151  {
152  if (preg_match('/ALTER TABLE/i',$line)) $type='dml';
153  else if (preg_match('/CREATE TABLE/i',$line)) $type='dml';
154  else if (preg_match('/DROP TABLE/i',$line)) $type='dml';
155  }
156 
157  if ($type == 'dml')
158  {
159  $line=preg_replace('/\s/',' ',$line); // Replace tabulation with space
160 
161  // we are inside create table statement so lets process datatypes
162  if (preg_match('/(ISAM|innodb)/i',$line)) { // end of create table sequence
163  $line=preg_replace('/\)[\s\t]*type[\s\t]*=[\s\t]*(MyISAM|innodb);/i',');',$line);
164  $line=preg_replace('/\)[\s\t]*engine[\s\t]*=[\s\t]*(MyISAM|innodb);/i',');',$line);
165  $line=preg_replace('/,$/','',$line);
166  }
167 
168  // Process case: "CREATE TABLE llx_mytable(rowid integer NOT NULL AUTO_INCREMENT PRIMARY KEY,code..."
169  if (preg_match('/[\s\t\(]*(\w*)[\s\t]+int.*auto_increment/i',$line,$reg)) {
170  $newline=preg_replace('/([\s\t\(]*)([a-zA-Z_0-9]*)[\s\t]+int.*auto_increment[^,]*/i','\\1 \\2 integer PRIMARY KEY AUTOINCREMENT',$line);
171  //$line = "-- ".$line." replaced by --\n".$newline;
172  $line=$newline;
173  }
174 
175  // tinyint type conversion
176  $line=str_replace('tinyint','smallint',$line);
177 
178  // nuke unsigned
179  $line=preg_replace('/(int\w+|smallint)\s+unsigned/i','\\1',$line);
180 
181  // blob -> text
182  $line=preg_replace('/\w*blob/i','text',$line);
183 
184  // tinytext/mediumtext -> text
185  $line=preg_replace('/tinytext/i','text',$line);
186  $line=preg_replace('/mediumtext/i','text',$line);
187 
188  // change not null datetime field to null valid ones
189  // (to support remapping of "zero time" to null
190  $line=preg_replace('/datetime not null/i','datetime',$line);
191  $line=preg_replace('/datetime/i','timestamp',$line);
192 
193  // double -> numeric
194  $line=preg_replace('/^double/i','numeric',$line);
195  $line=preg_replace('/(\s*)double/i','\\1numeric',$line);
196  // float -> numeric
197  $line=preg_replace('/^float/i','numeric',$line);
198  $line=preg_replace('/(\s*)float/i','\\1numeric',$line);
199 
200  // unique index(field1,field2)
201  if (preg_match('/unique index\s*\((\w+\s*,\s*\w+)\)/i',$line))
202  {
203  $line=preg_replace('/unique index\s*\((\w+\s*,\s*\w+)\)/i','UNIQUE\(\\1\)',$line);
204  }
205 
206  // We remove end of requests "AFTER fieldxxx"
207  $line=preg_replace('/AFTER [a-z0-9_]+/i','',$line);
208 
209  // We remove start of requests "ALTER TABLE tablexxx" if this is a DROP INDEX
210  $line=preg_replace('/ALTER TABLE [a-z0-9_]+ DROP INDEX/i','DROP INDEX',$line);
211 
212  // Translate order to rename fields
213  if (preg_match('/ALTER TABLE ([a-z0-9_]+) CHANGE(?: COLUMN)? ([a-z0-9_]+) ([a-z0-9_]+)(.*)$/i',$line,$reg))
214  {
215  $line = "-- ".$line." replaced by --\n";
216  $line.= "ALTER TABLE ".$reg[1]." RENAME COLUMN ".$reg[2]." TO ".$reg[3];
217  }
218 
219  // Translate order to modify field format
220  if (preg_match('/ALTER TABLE ([a-z0-9_]+) MODIFY(?: COLUMN)? ([a-z0-9_]+) (.*)$/i',$line,$reg))
221  {
222  $line = "-- ".$line." replaced by --\n";
223  $newreg3=$reg[3];
224  $newreg3=preg_replace('/ DEFAULT NULL/i','',$newreg3);
225  $newreg3=preg_replace('/ NOT NULL/i','',$newreg3);
226  $newreg3=preg_replace('/ NULL/i','',$newreg3);
227  $newreg3=preg_replace('/ DEFAULT 0/i','',$newreg3);
228  $newreg3=preg_replace('/ DEFAULT \'[0-9a-zA-Z_@]*\'/i','',$newreg3);
229  $line.= "ALTER TABLE ".$reg[1]." ALTER COLUMN ".$reg[2]." TYPE ".$newreg3;
230  // TODO Add alter to set default value or null/not null if there is this in $reg[3]
231  }
232 
233  // alter table add primary key (field1, field2 ...) -> We create a unique index instead as dynamic creation of primary key is not supported
234  // ALTER TABLE llx_dolibarr_modules ADD PRIMARY KEY pk_dolibarr_modules (numero, entity);
235  if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+PRIMARY\s+KEY\s*(.*)\s*\((.*)$/i',$line,$reg))
236  {
237  $line = "-- ".$line." replaced by --\n";
238  $line.= "CREATE UNIQUE INDEX ".$reg[2]." ON ".$reg[1]."(".$reg[3];
239  }
240 
241  // Translate order to drop foreign keys
242  // ALTER TABLE llx_dolibarr_modules DROP FOREIGN KEY fk_xxx;
243  if (preg_match('/ALTER\s+TABLE\s*(.*)\s*DROP\s+FOREIGN\s+KEY\s*(.*)$/i',$line,$reg))
244  {
245  $line = "-- ".$line." replaced by --\n";
246  $line.= "ALTER TABLE ".$reg[1]." DROP CONSTRAINT ".$reg[2];
247  }
248 
249  // alter table add [unique] [index] (field1, field2 ...)
250  // ALTER TABLE llx_accountingaccount ADD INDEX idx_accountingaccount_fk_pcg_version (fk_pcg_version)
251  if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+(UNIQUE INDEX|INDEX|UNIQUE)\s+(.*)\s*\(([\w,\s]+)\)/i',$line,$reg))
252  {
253  $fieldlist=$reg[4];
254  $idxname=$reg[3];
255  $tablename=$reg[1];
256  $line = "-- ".$line." replaced by --\n";
257  $line.= "CREATE ".(preg_match('/UNIQUE/',$reg[2])?'UNIQUE ':'')."INDEX ".$idxname." ON ".$tablename." (".$fieldlist.")";
258  }
259  if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+CONSTRAINT\s+(.*)\s*FOREIGN\s+KEY\s*\(([\w,\s]+)\)\s*REFERENCES\s+(\w+)\s*\(([\w,\s]+)\)/i',$line, $reg)) {
260  // Pour l'instant les contraintes ne sont pas créées
261  dol_syslog(get_class().'::query line emptied');
262  $line = 'SELECT 0;';
263 
264  }
265 
266  //if (preg_match('/rowid\s+.*\s+PRIMARY\s+KEY,/i', $line)) {
267  //preg_replace('/(rowid\s+.*\s+PRIMARY\s+KEY\s*,)/i', '/* \\1 */', $line);
268  //}
269  }
270 
271  // Delete using criteria on other table must not declare twice the deleted table
272  // DELETE FROM tabletodelete USING tabletodelete, othertable -> DELETE FROM tabletodelete USING othertable
273  if (preg_match('/DELETE FROM ([a-z_]+) USING ([a-z_]+), ([a-z_]+)/i',$line,$reg))
274  {
275  if ($reg[1] == $reg[2]) // If same table, we remove second one
276  {
277  $line=preg_replace('/DELETE FROM ([a-z_]+) USING ([a-z_]+), ([a-z_]+)/i','DELETE FROM \\1 USING \\3', $line);
278  }
279  }
280 
281  // Remove () in the tables in FROM if one table
282  $line=preg_replace('/FROM\s*\((([a-z_]+)\s+as\s+([a-z_]+)\s*)\)/i','FROM \\1',$line);
283  //print $line."\n";
284 
285  // Remove () in the tables in FROM if two table
286  $line=preg_replace('/FROM\s*\(([a-z_]+\s+as\s+[a-z_]+)\s*,\s*([a-z_]+\s+as\s+[a-z_]+\s*)\)/i','FROM \\1, \\2',$line);
287  //print $line."\n";
288 
289  // Remove () in the tables in FROM if two table
290  $line=preg_replace('/FROM\s*\(([a-z_]+\s+as\s+[a-z_]+)\s*,\s*([a-z_]+\s+as\s+[a-z_]+\s*),\s*([a-z_]+\s+as\s+[a-z_]+\s*)\)/i','FROM \\1, \\2, \\3',$line);
291  //print $line."\n";
292 
293  //print "type=".$type." newline=".$line."<br>\n";
294  }
295 
296  return $line;
297  }
298 
305  function select_db($database)
306  {
307  dol_syslog(get_class($this)."::select_db database=".$database, LOG_DEBUG);
308  // sqlite_select_db() does not exist
309  //return sqlite_select_db($this->db,$database);
310  return true;
311  }
312 
313 
325  function connect($host, $login, $passwd, $name, $port=0)
326  {
327  global $main_data_dir;
328 
329  dol_syslog(get_class($this)."::connect name=".$name,LOG_DEBUG);
330 
331  $dir=$main_data_dir;
332  if (empty($dir)) $dir=DOL_DATA_ROOT;
333  // With sqlite, port must be in connect parameters
334  //if (! $newport) $newport=3306;
335  $database_name = $dir.'/database_'.$name.'.sdb';
336  try {
337  /*** connect to SQLite database ***/
338  //$this->db = new PDO("sqlite:".$dir.'/database_'.$name.'.sdb');
339  $this->db = new SQLite3($database_name);
340  //$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
341  }
342  catch(Exception $e)
343  {
344  $this->error= self::LABEL.' '.$e->getMessage().' current dir='.$database_name;
345  return '';
346  }
347 
348  //print "Resultat fonction connect: ".$this->db;
349  return $this->db;
350  }
351 
352 
358  function getVersion()
359  {
360  $tmp=$this->db->version();
361  return $tmp['versionString'];
362  }
363 
369  function getDriverInfo()
370  {
371  return 'sqlite3 php driver';
372  }
373 
374 
381  function close()
382  {
383  if ($this->db)
384  {
385  if ($this->transaction_opened > 0) dol_syslog(get_class($this)."::close Closing a connection with an opened transaction depth=".$this->transaction_opened,LOG_ERR);
386  $this->connected=false;
387  $this->db->close();
388  unset($this->db); // Clean this->db
389  return true;
390  }
391  return false;
392  }
393 
403  function query($query,$usesavepoint=0,$type='auto')
404  {
405  $ret=null;
406  $query = trim($query);
407  $this->error = 0;
408 
409  // Convert MySQL syntax to SQLite syntax
410  if (preg_match('/ALTER\s+TABLE\s*(.*)\s*ADD\s+CONSTRAINT\s+(.*)\s*FOREIGN\s+KEY\s*\(([\w,\s]+)\)\s*REFERENCES\s+(\w+)\s*\(([\w,\s]+)\)/i',$query, $reg)) {
411  // Ajout d'une clef étrangère à la table
412  // procédure de remplacement de la table pour ajouter la contrainte
413  // Exemple : ALTER TABLE llx_adherent ADD CONSTRAINT adherent_fk_soc FOREIGN KEY (fk_soc) REFERENCES llx_societe (rowid)
414  // -> CREATE TABLE ( ... ,CONSTRAINT adherent_fk_soc FOREIGN KEY (fk_soc) REFERENCES llx_societe (rowid))
415  $foreignFields = $reg[5];
416  $foreignTable = $reg[4];
417  $localfields = $reg[3];
418  $constraintname=trim($reg[2]);
419  $tablename=trim($reg[1]);
420 
421  $descTable = $this->db->querySingle("SELECT sql FROM sqlite_master WHERE name='" . $tablename . "'");
422 
423  // 1- Renommer la table avec un nom temporaire
424  $this->query('ALTER TABLE ' . $tablename . ' RENAME TO tmp_' . $tablename);
425 
426  // 2- Recréer la table avec la contrainte ajoutée
427 
428  // on bricole la requete pour ajouter la contrainte
429  $descTable = substr($descTable, 0, strlen($descTable) - 1);
430  $descTable .= ", CONSTRAINT " . $constraintname . " FOREIGN KEY (" . $localfields . ") REFERENCES " .$foreignTable . "(" . $foreignFields . ")";
431 
432  // fermeture de l'instruction
433  $descTable .= ')';
434 
435  // Création proprement dite de la table
436  $this->query($descTable);
437 
438  // 3- Transférer les données
439  $this->query('INSERT INTO ' . $tablename . ' SELECT * FROM tmp_' . $tablename);
440 
441  // 4- Supprimer la table temporaire
442  $this->query('DROP TABLE tmp_' . $tablename);
443 
444  // dummy statement
445  $query="SELECT 0";
446 
447  } else {
448  $query=$this->convertSQLFromMysql($query,$type);
449  }
450  //print "After convertSQLFromMysql:\n".$query."<br>\n";
451 
452  dol_syslog('sql='.$query, LOG_DEBUG);
453 
454  // Ordre SQL ne necessitant pas de connexion a une base (exemple: CREATE DATABASE)
455  try {
456  //$ret = $this->db->exec($query);
457  $ret = $this->db->query($query); // $ret is a Sqlite3Result
458  if ($ret) {
459  $ret->queryString = $query;
460  }
461  }
462  catch(Exception $e)
463  {
464  $this->error=$this->db->lastErrorMsg();
465  }
466 
467  if (! preg_match("/^COMMIT/i",$query) && ! preg_match("/^ROLLBACK/i",$query))
468  {
469  // Si requete utilisateur, on la sauvegarde ainsi que son resultset
470  if (! is_object($ret) || $this->error)
471  {
472  $this->lastqueryerror = $query;
473  $this->lasterror = $this->error();
474  $this->lasterrno = $this->errno();
475 
476  dol_syslog(get_class($this)."::query SQL Error query: ".$query, LOG_ERR);
477 
478  $errormsg = get_class($this)."::query SQL Error message: ".$this->lasterror;
479 
480  if (preg_match('/[0-9]/',$this->lasterrno)) {
481  $errormsg .= ' ('.$this->lasterrno.')';
482  }
483 
484  dol_syslog($errormsg, LOG_ERR);
485  }
486  $this->lastquery=$query;
487  $this->_results = $ret;
488  }
489 
490  return $ret;
491  }
492 
499  function fetch_object($resultset)
500  {
501  // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion
502  if (! is_object($resultset)) { $resultset=$this->_results; }
503  //return $resultset->fetch(PDO::FETCH_OBJ);
504  $ret = $resultset->fetchArray(SQLITE3_ASSOC);
505  if ($ret) {
506  return (object) $ret;
507  }
508  return false;
509  }
510 
511 
518  function fetch_array($resultset)
519  {
520  // If resultset not provided, we take the last used by connexion
521  if (! is_object($resultset)) { $resultset=$this->_results; }
522  //return $resultset->fetch(PDO::FETCH_ASSOC);
523  $ret = $resultset->fetchArray(SQLITE3_ASSOC);
524  return $ret;
525  }
526 
533  function fetch_row($resultset)
534  {
535  // If resultset not provided, we take the last used by connexion
536  if (! is_bool($resultset))
537  {
538  if (! is_object($resultset)) { $resultset=$this->_results; }
539  return $resultset->fetchArray(SQLITE3_NUM);
540  }
541  else
542  {
543  // si le curseur est un booleen on retourne la valeur 0
544  return false;
545  }
546  }
547 
555  function num_rows($resultset)
556  {
557  // FIXME: SQLite3Result does not have a queryString member
558 
559  // If resultset not provided, we take the last used by connexion
560  if (! is_object($resultset)) { $resultset=$this->_results; }
561  if (preg_match("/^SELECT/i", $resultset->queryString)) {
562  return $this->db->querySingle("SELECT count(*) FROM (" . $resultset->queryString . ") q");
563  }
564  return 0;
565  }
566 
574  function affected_rows($resultset)
575  {
576  // FIXME: SQLite3Result does not have a queryString member
577 
578  // If resultset not provided, we take the last used by connexion
579  if (! is_object($resultset)) { $resultset=$this->_results; }
580  if (preg_match("/^SELECT/i", $resultset->queryString)) {
581  return $this->num_rows($resultset);
582  }
583  // mysql necessite un link de base pour cette fonction contrairement
584  // a pqsql qui prend un resultset
585  return $this->db->changes();
586  }
587 
588 
595  function free($resultset=null)
596  {
597  // If resultset not provided, we take the last used by connexion
598  if (! is_object($resultset)) { $resultset=$this->_results; }
599  // Si resultset en est un, on libere la memoire
600  if ($resultset && is_object($resultset)) $resultset->finalize();
601  }
602 
609  function escape($stringtoencode)
610  {
611  return Sqlite3::escapeString($stringtoencode);
612  }
613 
619  function errno()
620  {
621  if (! $this->connected) {
622  // Si il y a eu echec de connexion, $this->db n'est pas valide.
623  return 'DB_ERROR_FAILED_TO_CONNECT';
624  }
625  else {
626  // Constants to convert error code to a generic Dolibarr error code
627  /*$errorcode_map = array(
628  1004 => 'DB_ERROR_CANNOT_CREATE',
629  1005 => 'DB_ERROR_CANNOT_CREATE',
630  1006 => 'DB_ERROR_CANNOT_CREATE',
631  1007 => 'DB_ERROR_ALREADY_EXISTS',
632  1008 => 'DB_ERROR_CANNOT_DROP',
633  1025 => 'DB_ERROR_NO_FOREIGN_KEY_TO_DROP',
634  1044 => 'DB_ERROR_ACCESSDENIED',
635  1046 => 'DB_ERROR_NODBSELECTED',
636  1048 => 'DB_ERROR_CONSTRAINT',
637  'HY000' => 'DB_ERROR_TABLE_ALREADY_EXISTS',
638  1051 => 'DB_ERROR_NOSUCHTABLE',
639  1054 => 'DB_ERROR_NOSUCHFIELD',
640  1060 => 'DB_ERROR_COLUMN_ALREADY_EXISTS',
641  1061 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS',
642  1062 => 'DB_ERROR_RECORD_ALREADY_EXISTS',
643  1064 => 'DB_ERROR_SYNTAX',
644  1068 => 'DB_ERROR_PRIMARY_KEY_ALREADY_EXISTS',
645  1075 => 'DB_ERROR_CANT_DROP_PRIMARY_KEY',
646  1091 => 'DB_ERROR_NOSUCHFIELD',
647  1100 => 'DB_ERROR_NOT_LOCKED',
648  1136 => 'DB_ERROR_VALUE_COUNT_ON_ROW',
649  1146 => 'DB_ERROR_NOSUCHTABLE',
650  1216 => 'DB_ERROR_NO_PARENT',
651  1217 => 'DB_ERROR_CHILD_EXISTS',
652  1451 => 'DB_ERROR_CHILD_EXISTS'
653  );
654 
655  if (isset($errorcode_map[$this->db->errorCode()]))
656  {
657  return $errorcode_map[$this->db->errorCode()];
658  }*/
659  $errno=$this->db->lastErrorCode();
660  if ($errno=='HY000' || $errno == 0)
661  {
662  if (preg_match('/table.*already exists/i',$this->error)) return 'DB_ERROR_TABLE_ALREADY_EXISTS';
663  elseif (preg_match('/index.*already exists/i',$this->error)) return 'DB_ERROR_KEY_NAME_ALREADY_EXISTS';
664  elseif (preg_match('/syntax error/i',$this->error)) return 'DB_ERROR_SYNTAX';
665  }
666  if ($errno=='23000')
667  {
668  if (preg_match('/column.* not unique/i',$this->error)) return 'DB_ERROR_RECORD_ALREADY_EXISTS';
669  elseif (preg_match('/PRIMARY KEY must be unique/i',$this->error)) return 'DB_ERROR_RECORD_ALREADY_EXISTS';
670  }
671  if ($errno > 1) {
672  // TODO Voir la liste des messages d'erreur
673  }
674 
675  return ($errno?'DB_ERROR_'.$errno:'0');
676  }
677  }
678 
684  function error()
685  {
686  if (! $this->connected) {
687  // Si il y a eu echec de connexion, $this->db n'est pas valide pour sqlite_error.
688  return 'Not connected. Check setup parameters in conf/conf.php file and your sqlite version';
689  }
690  else {
691  return $this->error;
692  }
693  }
694 
702  function last_insert_id($tab,$fieldid='rowid')
703  {
704  return $this->db->lastInsertRowId();
705  }
706 
715  function encrypt($fieldorvalue, $withQuotes=0)
716  {
717  global $conf;
718 
719  // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption)
720  $cryptType = ($conf->db->dolibarr_main_db_encryption?$conf->db->dolibarr_main_db_encryption:0);
721 
722  //Encryption key
723  $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey)?$conf->db->dolibarr_main_db_cryptkey:'');
724 
725  $return = ($withQuotes?"'":"").$this->escape($fieldorvalue).($withQuotes?"'":"");
726 
727  if ($cryptType && !empty($cryptKey))
728  {
729  if ($cryptType == 2)
730  {
731  $return = 'AES_ENCRYPT('.$return.',\''.$cryptKey.'\')';
732  }
733  else if ($cryptType == 1)
734  {
735  $return = 'DES_ENCRYPT('.$return.',\''.$cryptKey.'\')';
736  }
737  }
738 
739  return $return;
740  }
741 
748  function decrypt($value)
749  {
750  global $conf;
751 
752  // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption)
753  $cryptType = ($conf->db->dolibarr_main_db_encryption?$conf->db->dolibarr_main_db_encryption:0);
754 
755  //Encryption key
756  $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey)?$conf->db->dolibarr_main_db_cryptkey:'');
757 
758  $return = $value;
759 
760  if ($cryptType && !empty($cryptKey))
761  {
762  if ($cryptType == 2)
763  {
764  $return = 'AES_DECRYPT('.$value.',\''.$cryptKey.'\')';
765  }
766  else if ($cryptType == 1)
767  {
768  $return = 'DES_DECRYPT('.$value.',\''.$cryptKey.'\')';
769  }
770  }
771 
772  return $return;
773  }
774 
775 
781  function DDLGetConnectId()
782  {
783  return '?';
784  }
785 
786 
798  function DDLCreateDb($database,$charset='',$collation='',$owner='')
799  {
800  if (empty($charset)) $charset=$this->forcecharset;
801  if (empty($collation)) $collation=$this->forcecollate;
802 
803  // ALTER DATABASE dolibarr_db DEFAULT CHARACTER SET latin DEFAULT COLLATE latin1_swedish_ci
804  $sql = 'CREATE DATABASE '.$database;
805  $sql.= ' DEFAULT CHARACTER SET '.$charset.' DEFAULT COLLATE '.$collation;
806 
807  dol_syslog($sql,LOG_DEBUG);
808  $ret=$this->query($sql);
809  if (! $ret)
810  {
811  // We try again for compatibility with Mysql < 4.1.1
812  $sql = 'CREATE DATABASE '.$database;
813  $ret=$this->query($sql);
814  dol_syslog($sql,LOG_DEBUG);
815  }
816  return $ret;
817  }
818 
826  function DDLListTables($database, $table='')
827  {
828  $listtables=array();
829 
830  $like = '';
831  if ($table) $like = "LIKE '".$table."'";
832  $sql="SHOW TABLES FROM ".$database." ".$like.";";
833  //print $sql;
834  $result = $this->query($sql);
835  if ($result)
836  {
837  while($row = $this->fetch_row($result))
838  {
839  $listtables[] = $row[0];
840  }
841  }
842  return $listtables;
843  }
844 
852  function DDLInfoTable($table)
853  {
854  $infotables=array();
855 
856  $sql="SHOW FULL COLUMNS FROM ".$table.";";
857 
858  dol_syslog($sql,LOG_DEBUG);
859  $result = $this->query($sql);
860  if ($result)
861  {
862  while($row = $this->fetch_row($result))
863  {
864  $infotables[] = $row;
865  }
866  }
867  return $infotables;
868  }
869 
882  function DDLCreateTable($table,$fields,$primary_key,$type,$unique_keys=null,$fulltext_keys=null,$keys=null)
883  {
884  // FIXME: $fulltext_keys parameter is unused
885 
886  // cles recherchees dans le tableau des descriptions (fields) : type,value,attribute,null,default,extra
887  // ex. : $fields['rowid'] = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment');
888  $sql = "create table ".$table."(";
889  $i=0;
890  foreach($fields as $field_name => $field_desc)
891  {
892  $sqlfields[$i] = $field_name." ";
893  $sqlfields[$i] .= $field_desc['type'];
894  if( preg_match("/^[^\s]/i",$field_desc['value']))
895  $sqlfields[$i] .= "(".$field_desc['value'].")";
896  else if( preg_match("/^[^\s]/i",$field_desc['attribute']))
897  $sqlfields[$i] .= " ".$field_desc['attribute'];
898  else if( preg_match("/^[^\s]/i",$field_desc['default']))
899  {
900  if(preg_match("/null/i",$field_desc['default']))
901  $sqlfields[$i] .= " default ".$field_desc['default'];
902  else
903  $sqlfields[$i] .= " default '".$field_desc['default']."'";
904  }
905  else if( preg_match("/^[^\s]/i",$field_desc['null']))
906  $sqlfields[$i] .= " ".$field_desc['null'];
907 
908  else if( preg_match("/^[^\s]/i",$field_desc['extra']))
909  $sqlfields[$i] .= " ".$field_desc['extra'];
910  $i++;
911  }
912  if($primary_key != "")
913  $pk = "primary key(".$primary_key.")";
914 
915  if(is_array($unique_keys))
916  {
917  $i = 0;
918  foreach($unique_keys as $key => $value)
919  {
920  $sqluq[$i] = "UNIQUE KEY '".$key."' ('".$value."')";
921  $i++;
922  }
923  }
924  if(is_array($keys))
925  {
926  $i = 0;
927  foreach($keys as $key => $value)
928  {
929  $sqlk[$i] = "KEY ".$key." (".$value.")";
930  $i++;
931  }
932  }
933  $sql .= implode(',',$sqlfields);
934  if($primary_key != "")
935  $sql .= ",".$pk;
936  if(is_array($unique_keys))
937  $sql .= ",".implode(',',$sqluq);
938  if(is_array($keys))
939  $sql .= ",".implode(',',$sqlk);
940  $sql .=") type=".$type;
941 
942  dol_syslog($sql,LOG_DEBUG);
943  if(! $this -> query($sql))
944  return -1;
945  return 1;
946  }
947 
954  function DDLDropTable($table)
955  {
956  $sql = "DROP TABLE ".$table;
957 
958  if (! $this->query($sql))
959  return -1;
960  else
961  return 1;
962  }
963 
971  function DDLDescTable($table,$field="")
972  {
973  $sql="DESC ".$table." ".$field;
974 
975  dol_syslog(get_class($this)."::DDLDescTable ".$sql,LOG_DEBUG);
976  $this->_results = $this->query($sql);
977  return $this->_results;
978  }
979 
989  function DDLAddField($table,$field_name,$field_desc,$field_position="")
990  {
991  // cles recherchees dans le tableau des descriptions (field_desc) : type,value,attribute,null,default,extra
992  // ex. : $field_desc = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment');
993  $sql= "ALTER TABLE ".$table." ADD ".$field_name." ";
994  $sql.= $field_desc['type'];
995  if(preg_match("/^[^\s]/i",$field_desc['value']))
996  if (! in_array($field_desc['type'],array('date','datetime')))
997  {
998  $sql.= "(".$field_desc['value'].")";
999  }
1000  if(preg_match("/^[^\s]/i",$field_desc['attribute']))
1001  $sql.= " ".$field_desc['attribute'];
1002  if(preg_match("/^[^\s]/i",$field_desc['null']))
1003  $sql.= " ".$field_desc['null'];
1004  if(preg_match("/^[^\s]/i",$field_desc['default']))
1005  {
1006  if(preg_match("/null/i",$field_desc['default']))
1007  $sql.= " default ".$field_desc['default'];
1008  else
1009  $sql.= " default '".$field_desc['default']."'";
1010  }
1011  if(preg_match("/^[^\s]/i",$field_desc['extra']))
1012  $sql.= " ".$field_desc['extra'];
1013  $sql.= " ".$field_position;
1014 
1015  dol_syslog(get_class($this)."::DDLAddField ".$sql,LOG_DEBUG);
1016  if(! $this->query($sql))
1017  {
1018  return -1;
1019  }
1020  return 1;
1021  }
1022 
1031  function DDLUpdateField($table,$field_name,$field_desc)
1032  {
1033  $sql = "ALTER TABLE ".$table;
1034  $sql .= " MODIFY COLUMN ".$field_name." ".$field_desc['type'];
1035  if ($field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int' || $field_desc['type'] == 'varchar') {
1036  $sql.="(".$field_desc['value'].")";
1037  }
1038 
1039  dol_syslog(get_class($this)."::DDLUpdateField ".$sql,LOG_DEBUG);
1040  if (! $this->query($sql))
1041  return -1;
1042  return 1;
1043  }
1044 
1052  function DDLDropField($table,$field_name)
1053  {
1054  $sql= "ALTER TABLE ".$table." DROP COLUMN `".$field_name."`";
1055  dol_syslog(get_class($this)."::DDLDropField ".$sql,LOG_DEBUG);
1056  if (! $this->query($sql))
1057  {
1058  $this->error=$this->lasterror();
1059  return -1;
1060  }
1061  return 1;
1062  }
1063 
1064 
1074  function DDLCreateUser($dolibarr_main_db_host,$dolibarr_main_db_user,$dolibarr_main_db_pass,$dolibarr_main_db_name)
1075  {
1076  $sql = "INSERT INTO user ";
1077  $sql.= "(Host,User,password,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Index_Priv,Alter_priv,Lock_tables_priv)";
1078  $sql.= " VALUES ('".$this->escape($dolibarr_main_db_host)."','".$this->escape($dolibarr_main_db_user)."',password('".addslashes($dolibarr_main_db_pass)."')";
1079  $sql.= ",'Y','Y','Y','Y','Y','Y','Y','Y','Y')";
1080 
1081  dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log
1082  $resql=$this->query($sql);
1083  if (! $resql)
1084  {
1085  return -1;
1086  }
1087 
1088  $sql = "INSERT INTO db ";
1089  $sql.= "(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Index_Priv,Alter_priv,Lock_tables_priv)";
1090  $sql.= " VALUES ('".$this->escape($dolibarr_main_db_host)."','".$this->escape($dolibarr_main_db_name)."','".addslashes($dolibarr_main_db_user)."'";
1091  $sql.= ",'Y','Y','Y','Y','Y','Y','Y','Y','Y')";
1092 
1093  dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG);
1094  $resql=$this->query($sql);
1095  if (! $resql)
1096  {
1097  return -1;
1098  }
1099 
1100  $sql="FLUSH Privileges";
1101 
1102  dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG);
1103  $resql=$this->query($sql);
1104  if (! $resql)
1105  {
1106  return -1;
1107  }
1108  return 1;
1109  }
1110 
1116  function getDefaultCharacterSetDatabase()
1117  {
1118  return 'UTF-8';
1119  }
1120 
1126  function getListOfCharacterSet()
1127  {
1128  $liste = array();
1129  $i=0;
1130  $liste[$i]['charset'] = 'UTF-8';
1131  $liste[$i]['description'] = 'UTF-8';
1132  return $liste;
1133  }
1134 
1140  function getDefaultCollationDatabase()
1141  {
1142  return 'UTF-8';
1143  }
1144 
1150  function getListOfCollation()
1151  {
1152  $liste = array();
1153  $i=0;
1154  $liste[$i]['charset'] = 'UTF-8';
1155  $liste[$i]['description'] = 'UTF-8';
1156  return $liste;
1157  }
1158 
1164  function getPathOfDump()
1165  {
1166  // FIXME: not for SQLite
1167  $fullpathofdump='/pathtomysqldump/mysqldump';
1168 
1169  $resql=$this->query('SHOW VARIABLES LIKE \'basedir\'');
1170  if ($resql)
1171  {
1172  $liste=$this->fetch_array($resql);
1173  $basedir=$liste['Value'];
1174  $fullpathofdump=$basedir.(preg_match('/\/$/',$basedir)?'':'/').'bin/mysqldump';
1175  }
1176  return $fullpathofdump;
1177  }
1178 
1184  function getPathOfRestore()
1185  {
1186  // FIXME: not for SQLite
1187  $fullpathofimport='/pathtomysql/mysql';
1188 
1189  $resql=$this->query('SHOW VARIABLES LIKE \'basedir\'');
1190  if ($resql)
1191  {
1192  $liste=$this->fetch_array($resql);
1193  $basedir=$liste['Value'];
1194  $fullpathofimport=$basedir.(preg_match('/\/$/',$basedir)?'':'/').'bin/mysql';
1195  }
1196  return $fullpathofimport;
1197  }
1198 
1205  function getServerParametersValues($filter='')
1206  {
1207  $result=array();
1208  static $pragmas;
1209  if (! isset($pragmas)) {
1210  // Définition de la liste des pragmas utilisés qui ne retournent qu'une seule valeur
1211  // indépendante de la base de données.
1212  // cf. http://www.sqlite.org/pragma.html
1213  $pragmas = array(
1214  'application_id', 'auto_vacuum', 'automatic_index', 'busy_timeout', 'cache_size',
1215  'cache_spill', 'case_sensitive_like', 'checkpoint_fullsync', 'collation_list',
1216  'compile_options', 'data_version', /*'database_list',*/
1217  'defer_foreign_keys', 'encoding', 'foreign_key_check', 'freelist_count',
1218  'full_column_names', 'fullsync', 'ingore_check_constraints', 'integrity_check',
1219  'journal_mode', 'journal_size_limit', 'legacy_file_format', 'locking_mode',
1220  'max_page_count', 'page_count', 'page_size', 'parser_trace',
1221  'query_only', 'quick_check', 'read_uncommitted', 'recursive_triggers',
1222  'reverse_unordered_selects', 'schema_version', 'user_version',
1223  'secure_delete', 'short_column_names', 'shrink_memory', 'soft_heap_limit',
1224  'synchronous', 'temp_store', /*'temp_store_directory',*/ 'threads',
1225  'vdbe_addoptrace', 'vdbe_debug', 'vdbe_listing', 'vdbe_trace',
1226  'wal_autocheckpoint',
1227  );
1228  }
1229 
1230  // TODO prendre en compte le filtre
1231  foreach($pragmas as $var) {
1232  $sql = "PRAGMA $var";
1233  $resql=$this->query($sql);
1234  if ($resql)
1235  {
1236  $obj = $this->fetch_row($resql);
1237  //dol_syslog(get_class($this)."::select_db getServerParametersValues $var=". print_r($obj, true), LOG_DEBUG);
1238  $result[$var] = $obj[0];
1239  }
1240  else {
1241  // TODO Récupérer le message
1242  $result[$var] = 'FAIL';
1243  }
1244  }
1245  return $result;
1246  }
1247 
1254  function getServerStatusValues($filter='')
1255  {
1256  $result=array();
1257  /*
1258  $sql='SHOW STATUS';
1259  if ($filter) $sql.=" LIKE '".$this->escape($filter)."'";
1260  $resql=$this->query($sql);
1261  if ($resql)
1262  {
1263  while ($obj=$this->fetch_object($resql)) $result[$obj->Variable_name]=$obj->Value;
1264  }
1265  */
1266 
1267  return $result;
1268  }
1269 
1279  private function addCustomFunction($name, $arg_count = -1)
1280  {
1281  if ($this->db)
1282  {
1283  $newname=preg_replace('/_/','',$name);
1284  $localname = __CLASS__ . '::' . 'db' . $newname;
1285  $reflectClass = new ReflectionClass(__CLASS__);
1286  $reflectFunction = $reflectClass->getMethod('db' . $newname);
1287  if ($arg_count < 0) {
1288  $arg_count = $reflectFunction->getNumberOfParameters();
1289  }
1290  if (!$this->db->createFunction($name, $localname, $arg_count))
1291  {
1292  $this->error = "unable to create custom function '$name'";
1293  }
1294  }
1295  }
1296 
1305  private static function calc_daynr($year, $month, $day) {
1306  $y = $year;
1307  if ($y == 0 && $month == 0) return 0;
1308  $num = (365* $y + 31 * ($month - 1) + $day);
1309  if ($month <= 2) {
1310  $y--; }
1311  else {
1312  $num -= floor(($month * 4 + 23) / 10);
1313  }
1314  $temp = floor(($y / 100 + 1) * 3 / 4);
1315  return $num + floor($y / 4) - $temp;
1316  }
1317 
1325  private static function calc_weekday($daynr, $sunday_first_day_of_week) {
1326  $ret = floor(($daynr + 5 + ($sunday_first_day_of_week ? 1 : 0)) % 7);
1327  return $ret;
1328  }
1329 
1336  private static function calc_days_in_year($year)
1337  {
1338  return (($year & 3) == 0 && ($year%100 || ($year%400 == 0 && $year)) ? 366 : 365);
1339  }
1340 
1351  private static function calc_week($year, $month, $day, $week_behaviour, &$calc_year) {
1352  $daynr=self::calc_daynr($year,$month,$day);
1353  $first_daynr=self::calc_daynr($year,1,1);
1354  $monday_first= ($week_behaviour & self::WEEK_MONDAY_FIRST) ? 1 : 0;
1355  $week_year= ($week_behaviour & self::WEEK_YEAR) ? 1 : 0;
1356  $first_weekday= ($week_behaviour & self::WEEK_FIRST_WEEKDAY) ? 1 : 0;
1357 
1358  $weekday=self::calc_weekday($first_daynr, !$monday_first);
1359  $calc_year=$year;
1360 
1361  if ($month == 1 && $day <= 7-$weekday)
1362  {
1363  if (!$week_year && (($first_weekday && $weekday != 0) || (!$first_weekday && $weekday >= 4)))
1364  return 0;
1365  $week_year= 1;
1366  $calc_year--;
1367  $first_daynr-= ($days=self::calc_days_in_year($calc_year));
1368  $weekday= ($weekday + 53*7- $days) % 7;
1369  }
1370 
1371  if (($first_weekday && $weekday != 0) || (!$first_weekday && $weekday >= 4)) {
1372  $days= $daynr - ($first_daynr+ (7-$weekday));
1373  }
1374  else {
1375  $days= $daynr - ($first_daynr - $weekday);
1376  }
1377 
1378  if ($week_year && $days >= 52*7)
1379  {
1380  $weekday= ($weekday + self::calc_days_in_year($calc_year)) % 7;
1381  if ((!$first_weekday && $weekday < 4) || ($first_weekday && $weekday == 0))
1382  {
1383  $calc_year++;
1384  return 1;
1385  }
1386  }
1387  return floor($days/7+1);
1388  }
1389 
1390 }
1391 
addCustomFunction($name, $arg_count=-1)
Permet le chargement d'une fonction personnalisee dans le moteur de base de donnees.
last_insert_id($tab, $fieldid='rowid')
Get last ID after an insert INSERT.
const LABEL
Database label.
lastqueryerror()
Return last query in error.
getDriverInfo()
Return version of database client driver.
select_db($database)
Select a database.
__construct($type, $host, $user, $pass, $name='', $port=0)
Constructor.
error()
Renvoie le texte de l'erreur mysql de l'operation precedente.
escape($stringtoencode)
Escape a string to insert data.
lastquery()
Return last request executed with query()
</td >< tdclass="liste_titre"align="right"></td ></tr >< trclass="liste_titre">< inputtype="checkbox"onClick="toggle(this)"/> Ref p ref Label p label Duration p duration warehouseinternal SELECT description FROM product_lang WHERE qty< br > qty qty qty StockTooLow img yes disabled img no img no< trclass="oddeven">< td >< inputtype="checkbox"class="check"name="'.$i.'"'.$disabled.'></td >< td >< inputtype="checkbox"class="check"name="choose'.$i.'"></td >< tdclass="nowrap"></td >< td >< inputtype="hidden"name="desc'.$i.'"value="'.dol_escape_htmltag($objp-> description
Only used if Module[ID]Desc translation string is not found.
Definition: replenish.php:554
encrypt($fieldorvalue, $withQuotes=0)
Encrypt sensitive data in database Warning: This function includes the escape, so it must use direct ...
static convertSQLFromMysql($line, $type='ddl')
Convert a SQL request in Mysql syntax to native syntax.
$type
Database type.
Class to manage Dolibarr database access.
static calc_days_in_year($year)
calc_days_in_year
connect($host, $login, $passwd, $name, $port=0)
Connexion to server.
lasterror()
Return last error label.
query($query, $usesavepoint=0, $type='auto')
Execute a SQL request and return the resultset.
close()
Close database connexion.
free($resultset=null)
Free last resultset used.
getServerStatusValues($filter='')
Return value of server status.
const VERSIONMIN
Version min database.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='')
Write log message into outputs.
static calc_week($year, $month, $day, $week_behaviour, &$calc_year)
calc_week
fetch_array($resultset)
Return datas as an array.
getPathOfRestore()
Return full path of restore program.
fetch_row($resultset)
Return datas as an array.
fetch_object($resultset)
Renvoie la ligne courante (comme un objet) pour le curseur resultset.
num_rows($resultset)
Return number of lines for result of a SELECT.
affected_rows($resultset)
Return number of lines for result of a SELECT.
static calc_weekday($daynr, $sunday_first_day_of_week)
calc_weekday
if(!empty($conf->facture->enabled)&&$user->rights->facture->lire) if(!empty($conf->fournisseur->enabled)&&$user->rights->fournisseur->facture->lire) if(!empty($conf->don->enabled)&&$user->rights->societe->lire) if(!empty($conf->tax->enabled)&&$user->rights->tax->charges->lire) if(!empty($conf->facture->enabled)&&!empty($conf->commande->enabled)&&$user->rights->commande->lire &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) if(!empty($conf->facture->enabled)&&$user->rights->facture->lire) if(!empty($conf->fournisseur->enabled)&&$user->rights->fournisseur->facture->lire) $resql
Social contributions to pay.
Definition: index.php:1013
lasterrno()
Return last error code.
Class to manage Dolibarr database access for a SQLite database.
getServerParametersValues($filter='')
Return value of server parameters.
static calc_daynr($year, $month, $day)
calc_daynr
type
Definition: viewcat.php:283
errno()
Renvoie le code erreur generique de l'operation precedente.
getVersion()
Return version of database server.