29require_once DOL_DOCUMENT_ROOT.
'/core/db/DoliDB.class.php';
53 const WEEK_MONDAY_FIRST = 1;
55 const WEEK_FIRST_WEEKDAY = 4;
74 if (!empty($conf->db->character_set)) {
75 $this->forcecharset = $conf->db->character_set;
77 if (!empty($conf->db->dolibarr_main_db_collation)) {
78 $this->forcecollate = $conf->db->dolibarr_main_db_collation;
81 $this->database_user = $user;
82 $this->database_host = $host;
83 $this->database_port = $port;
85 $this->transaction_opened = 0;
109 $this->db = $this->
connect($host, $user, $pass, $name, $port);
112 $this->connected =
true;
114 $this->database_selected =
true;
115 $this->database_name = $name;
128 $this->connected =
false;
130 $this->database_selected =
false;
131 $this->database_name =
'';
133 dol_syslog(get_class($this).
"::DoliDBSqlite3 : Error Connect ".$this->
error, LOG_ERR);
148 if (preg_match(
'/^--\s\$Id/i', $line)) {
152 if (preg_match(
'/^#/i', $line) || preg_match(
'/^$/i', $line) || preg_match(
'/^--/i', $line)) {
156 if (
$type ==
'auto') {
157 if (preg_match(
'/ALTER TABLE/i', $line)) {
159 } elseif (preg_match(
'/CREATE TABLE/i', $line)) {
161 } elseif (preg_match(
'/DROP TABLE/i', $line)) {
166 if (
$type ==
'dml') {
167 $line = preg_replace(
'/\s/',
' ', $line);
170 if (preg_match(
'/(ISAM|innodb)/i', $line)) {
171 $line = preg_replace(
'/\)[\s\t]*type[\s\t]*=[\s\t]*(MyISAM|innodb);/i',
');', $line);
172 $line = preg_replace(
'/\)[\s\t]*engine[\s\t]*=[\s\t]*(MyISAM|innodb);/i',
');', $line);
173 $line = preg_replace(
'/,$/',
'', $line);
177 if (preg_match(
'/[\s\t\(]*(\w*)[\s\t]+int.*auto_increment/i', $line, $reg)) {
178 $newline = preg_replace(
'/([\s\t\(]*)([a-zA-Z_0-9]*)[\s\t]+int.*auto_increment[^,]*/i',
'\\1 \\2 integer PRIMARY KEY AUTOINCREMENT', $line);
184 $line = str_replace(
'tinyint',
'smallint', $line);
187 $line = preg_replace(
'/(int\w+|smallint)\s+unsigned/i',
'\\1', $line);
190 $line = preg_replace(
'/\w*blob/i',
'text', $line);
193 $line = preg_replace(
'/tinytext/i',
'text', $line);
194 $line = preg_replace(
'/mediumtext/i',
'text', $line);
198 $line = preg_replace(
'/datetime not null/i',
'datetime', $line);
199 $line = preg_replace(
'/datetime/i',
'timestamp', $line);
202 $line = preg_replace(
'/^double/i',
'numeric', $line);
203 $line = preg_replace(
'/(\s*)double/i',
'\\1numeric', $line);
205 $line = preg_replace(
'/^float/i',
'numeric', $line);
206 $line = preg_replace(
'/(\s*)float/i',
'\\1numeric', $line);
209 if (preg_match(
'/unique index\s*\((\w+\s*,\s*\w+)\)/i', $line)) {
210 $line = preg_replace(
'/unique index\s*\((\w+\s*,\s*\w+)\)/i',
'UNIQUE\(\\1\)', $line);
214 $line = preg_replace(
'/AFTER [a-z0-9_]+/i',
'', $line);
217 $line = preg_replace(
'/ALTER TABLE [a-z0-9_]+ DROP INDEX/i',
'DROP INDEX', $line);
220 if (preg_match(
'/ALTER TABLE ([a-z0-9_]+) CHANGE(?: COLUMN)? ([a-z0-9_]+) ([a-z0-9_]+)(.*)$/i', $line, $reg)) {
221 $line =
"-- ".$line.
" replaced by --\n";
222 $line .=
"ALTER TABLE ".$reg[1].
" RENAME COLUMN ".$reg[2].
" TO ".$reg[3];
226 if (preg_match(
'/ALTER TABLE ([a-z0-9_]+) MODIFY(?: COLUMN)? ([a-z0-9_]+) (.*)$/i', $line, $reg)) {
227 $line =
"-- ".$line.
" replaced by --\n";
229 $newreg3 = preg_replace(
'/ DEFAULT NULL/i',
'', $newreg3);
230 $newreg3 = preg_replace(
'/ NOT NULL/i',
'', $newreg3);
231 $newreg3 = preg_replace(
'/ NULL/i',
'', $newreg3);
232 $newreg3 = preg_replace(
'/ DEFAULT 0/i',
'', $newreg3);
233 $newreg3 = preg_replace(
'/ DEFAULT \'[0-9a-zA-Z_@]*\'/i',
'', $newreg3);
234 $line .=
"ALTER TABLE ".$reg[1].
" ALTER COLUMN ".$reg[2].
" TYPE ".$newreg3;
240 if (preg_match(
'/ALTER\s+TABLE\s*(.*)\s*ADD\s+PRIMARY\s+KEY\s*(.*)\s*\((.*)$/i', $line, $reg)) {
241 $line =
"-- ".$line.
" replaced by --\n";
242 $line .=
"CREATE UNIQUE INDEX ".$reg[2].
" ON ".$reg[1].
"(".$reg[3];
247 if (preg_match(
'/ALTER\s+TABLE\s*(.*)\s*DROP\s+FOREIGN\s+KEY\s*(.*)$/i', $line, $reg)) {
248 $line =
"-- ".$line.
" replaced by --\n";
249 $line .=
"ALTER TABLE ".$reg[1].
" DROP CONSTRAINT ".$reg[2];
254 if (preg_match(
'/ALTER\s+TABLE\s*(.*)\s*ADD\s+(UNIQUE INDEX|INDEX|UNIQUE)\s+(.*)\s*\(([\w,\s]+)\)/i', $line, $reg)) {
255 $fieldlist = $reg[4];
257 $tablename = $reg[1];
258 $line =
"-- ".$line.
" replaced by --\n";
259 $line .=
"CREATE ".(preg_match(
'/UNIQUE/', $reg[2]) ?
'UNIQUE ' :
'').
"INDEX ".$idxname.
" ON ".$tablename.
" (".$fieldlist.
")";
261 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)) {
263 dol_syslog(get_class().
'::query line emptied');
274 if (preg_match(
'/DELETE FROM ([a-z_]+) USING ([a-z_]+), ([a-z_]+)/i', $line, $reg)) {
275 if ($reg[1] == $reg[2]) {
276 $line = preg_replace(
'/DELETE FROM ([a-z_]+) USING ([a-z_]+), ([a-z_]+)/i',
'DELETE FROM \\1 USING \\3', $line);
281 $line = preg_replace(
'/FROM\s*\((([a-z_]+)\s+as\s+([a-z_]+)\s*)\)/i',
'FROM \\1', $line);
285 $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);
289 $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);
308 dol_syslog(get_class($this).
"::select_db database=".$database, LOG_DEBUG);
326 public function connect($host, $login, $passwd, $name, $port = 0)
328 global $main_data_dir;
330 dol_syslog(get_class($this).
"::connect name=".$name, LOG_DEBUG);
332 $dir = $main_data_dir;
334 $dir = DOL_DATA_ROOT;
338 $database_name = $dir.
'/database_'.$name.
'.sdb';
342 $this->db =
new SQLite3($database_name);
345 $this->
error = self::LABEL.
' '.$e->getMessage().
' current dir='.$database_name;
361 $tmp = $this->db->version();
362 return $tmp[
'versionString'];
372 return 'sqlite3 php driver';
385 if ($this->transaction_opened > 0) {
386 dol_syslog(get_class($this).
"::close Closing a connection with an opened transaction depth=".$this->transaction_opened, LOG_ERR);
388 $this->connected =
false;
406 public function query($query, $usesavepoint = 0,
$type =
'auto', $result_mode = 0)
408 global $conf, $dolibarr_main_db_readonly;
412 $query = trim($query);
418 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)) {
423 $foreignFields = $reg[5];
424 $foreignTable = $reg[4];
425 $localfields = $reg[3];
426 $constraintname = trim($reg[2]);
427 $tablename = trim($reg[1]);
429 $descTable = $this->db->querySingle(
"SELECT sql FROM sqlite_master WHERE name='".$this->
escape($tablename).
"'");
432 $this->
query(
"ALTER TABLE ".$tablename.
" RENAME TO tmp_".$tablename);
437 $descTable = substr($descTable, 0, strlen($descTable) - 1);
438 $descTable .=
", CONSTRAINT ".$constraintname.
" FOREIGN KEY (".$localfields.
") REFERENCES ".$foreignTable.
"(".$foreignFields.
")";
444 $this->
query($descTable);
447 $this->
query(
"INSERT INTO ".$tablename.
" SELECT * FROM tmp_".$tablename);
450 $this->
query(
"DROP TABLE tmp_".$tablename);
459 if (!in_array($query, array(
'BEGIN',
'COMMIT',
'ROLLBACK'))) {
460 $SYSLOG_SQL_LIMIT = 10000;
461 dol_syslog(
'sql='.substr($query, 0, $SYSLOG_SQL_LIMIT), LOG_DEBUG);
467 if (!empty($dolibarr_main_db_readonly)) {
468 if (preg_match(
'/^(INSERT|UPDATE|REPLACE|DELETE|CREATE|ALTER|TRUNCATE|DROP)/i', $query)) {
469 $this->
lasterror =
'Application in read-only mode';
479 $ret = $this->db->query($query);
481 $this->queryString = $query;
484 $this->
error = $this->db->lastErrorMsg();
487 if (!preg_match(
"/^COMMIT/i", $query) && !preg_match(
"/^ROLLBACK/i", $query)) {
489 if (!is_object($ret) || $this->
error) {
494 dol_syslog(get_class($this).
"::query SQL Error query: ".$query, LOG_ERR);
496 $errormsg = get_class($this).
"::query SQL Error message: ".$this->lasterror;
498 if (preg_match(
'/[0-9]/', $this->
lasterrno)) {
499 $errormsg .=
' ('.$this->lasterrno.
')';
502 if ($conf->global->SYSLOG_LEVEL < LOG_DEBUG) {
503 dol_syslog(get_class($this).
"::query SQL Error query: ".$query, LOG_ERR);
505 dol_syslog(get_class($this).
"::query SQL Error message: ".$errormsg, LOG_ERR);
508 $this->_results = $ret;
525 if (!is_object($resultset)) {
526 $resultset = $this->_results;
529 $ret = $resultset->fetchArray(SQLITE3_ASSOC);
531 return (
object) $ret;
548 if (!is_object($resultset)) {
549 $resultset = $this->_results;
552 $ret = $resultset->fetchArray(SQLITE3_ASSOC);
567 if (!is_bool($resultset)) {
568 if (!is_object($resultset)) {
569 $resultset = $this->_results;
571 return $resultset->fetchArray(SQLITE3_NUM);
591 if (!is_object($resultset)) {
592 $resultset = $this->_results;
594 if (preg_match(
"/^SELECT/i", $resultset->queryString)) {
595 return $this->db->querySingle(
"SELECT count(*) FROM (".$resultset->queryString.
") q");
613 if (!is_object($resultset)) {
614 $resultset = $this->_results;
616 if (preg_match(
"/^SELECT/i", $this->queryString)) {
621 return $this->db->changes();
631 public function free($resultset =
null)
634 if (!is_object($resultset)) {
635 $resultset = $this->_results;
638 if ($resultset && is_object($resultset)) {
639 $resultset->finalize();
651 return Sqlite3::escapeString($stringtoencode);
662 return str_replace(array(
'\\',
'_',
'%'), array(
'\\\\',
'\_',
'\%'), (
string) $stringtoencode);
672 if (!$this->connected) {
674 return 'DB_ERROR_FAILED_TO_CONNECT';
709 $errno = $this->db->lastErrorCode();
710 if ($errno ==
'HY000' || $errno == 0) {
711 if (preg_match(
'/table.*already exists/i', $this->
error)) {
712 return 'DB_ERROR_TABLE_ALREADY_EXISTS';
713 } elseif (preg_match(
'/index.*already exists/i', $this->
error)) {
714 return 'DB_ERROR_KEY_NAME_ALREADY_EXISTS';
715 } elseif (preg_match(
'/syntax error/i', $this->
error)) {
716 return 'DB_ERROR_SYNTAX';
719 if ($errno ==
'23000') {
720 if (preg_match(
'/column.* not unique/i', $this->
error)) {
721 return 'DB_ERROR_RECORD_ALREADY_EXISTS';
722 } elseif (preg_match(
'/PRIMARY KEY must be unique/i', $this->
error)) {
723 return 'DB_ERROR_RECORD_ALREADY_EXISTS';
730 return ($errno ?
'DB_ERROR_'.$errno :
'0');
741 if (!$this->connected) {
743 return 'Not connected. Check setup parameters in conf/conf.php file and your sqlite version';
760 return $this->db->lastInsertRowId();
771 public function encrypt($fieldorvalue, $withQuotes = 1)
776 $cryptType = (!empty($conf->db->dolibarr_main_db_encryption) ? $conf->db->dolibarr_main_db_encryption : 0);
779 $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey :
'');
781 $escapedstringwithquotes = ($withQuotes ?
"'" :
"").$this->
escape($fieldorvalue).($withQuotes ?
"'" :
"");
783 if ($cryptType && !empty($cryptKey)) {
784 if ($cryptType == 2) {
785 $escapedstringwithquotes =
"AES_ENCRYPT(".$escapedstringwithquotes.
", '".$this->
escape($cryptKey).
"')";
786 } elseif ($cryptType == 1) {
787 $escapedstringwithquotes =
"DES_ENCRYPT(".$escapedstringwithquotes.
", '".$this->
escape($cryptKey).
"')";
791 return $escapedstringwithquotes;
805 $cryptType = ($conf->db->dolibarr_main_db_encryption ? $conf->db->dolibarr_main_db_encryption : 0);
808 $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey) ? $conf->db->dolibarr_main_db_cryptkey :
'');
812 if ($cryptType && !empty($cryptKey)) {
813 if ($cryptType == 2) {
814 $return =
'AES_DECRYPT('.$value.
',\''.$cryptKey.
'\')
';
815 } elseif ($cryptType == 1) {
816 $return = 'DES_DECRYPT(
'.$value.',\
''.$cryptKey.
'\')
';
824 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
830 public function DDLGetConnectId()
837 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
849 public function DDLCreateDb($database, $charset = '
', $collation = '', $owner = '')
852 if (empty($charset)) {
853 $charset = $this->forcecharset;
855 if (empty($collation)) {
856 $collation = $this->forcecollate;
859 // ALTER DATABASE dolibarr_db DEFAULT CHARACTER SET latin DEFAULT COLLATE latin1_swedish_ci
860 $sql = "CREATE DATABASE ".$this->escape($database);
861 $sql .= " DEFAULT CHARACTER SET ".$this->escape($charset)." DEFAULT COLLATE ".$this->escape($collation);
863 dol_syslog($sql, LOG_DEBUG);
864 $ret = $this->query($sql);
869 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
877 public function DDLListTables($database, $table = '
')
880 $listtables = array();
884 $tmptable = preg_replace('/[^a-z0-9\.\-\_%]/i
', '', $table);
886 $like = "LIKE '".$this->escape($tmptable)."'";
888 $tmpdatabase = preg_replace('/[^a-z0-9\.\-\_]/i
', '', $database);
890 $sql = "SHOW TABLES FROM ".$tmpdatabase." ".$like.";";
892 $result = $this->query($sql);
894 while ($row = $this->fetch_row($result)) {
895 $listtables[] = $row[0];
901 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
909 public function DDLListTablesFull($database, $table = '
')
912 $listtables = array();
916 $tmptable = preg_replace('/[^a-z0-9\.\-\_%]/i
', '', $table);
918 $like = "LIKE '".$this->escape($tmptable)."'";
920 $tmpdatabase = preg_replace('/[^a-z0-9\.\-\_]/i
', '', $database);
922 $sql = "SHOW FULL TABLES FROM ".$tmpdatabase." ".$like.";";
924 $result = $this->query($sql);
926 while ($row = $this->fetch_row($result)) {
927 $listtables[] = $row;
933 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
941 public function DDLInfoTable($table)
944 $infotables = array();
946 $tmptable = preg_replace('/[^a-z0-9\.\-\_]/i
', '', $table);
948 $sql = "SHOW FULL COLUMNS FROM ".$tmptable.";";
950 dol_syslog($sql, LOG_DEBUG);
951 $result = $this->query($sql);
953 while ($row = $this->fetch_row($result)) {
954 $infotables[] = $row;
960 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
973 public function DDLCreateTable($table, $fields, $primary_key, $type, $unique_keys = null, $fulltext_keys = null, $keys = null)
976 // @TODO: $fulltext_keys parameter is unused
981 // Keys found into the array $fields: type,value,attribute,null,default,extra
982 // ex. : $fields['
rowid'] = array(
983 // 'type'=>'int' or 'integer
',
985 // 'null'=>'not
null',
986 // 'extra
'=> 'auto_increment
'
988 $sql = "CREATE TABLE ".$this->sanitize($table)."(";
990 $sqlfields = array();
991 foreach ($fields as $field_name => $field_desc) {
992 $sqlfields[$i] = $this->sanitize($field_name)." ";
993 $sqlfields[$i] .= $this->sanitize($field_desc['type']);
994 if (!is_null($field_desc['value
']) && $field_desc['value
'] !== '') {
995 $sqlfields[$i] .= "(".$this->sanitize($field_desc['value
']).")";
997 if (!is_null($field_desc['attribute
']) && $field_desc['attribute
'] !== '') {
998 $sqlfields[$i] .= " ".$this->sanitize($field_desc['attribute
']);
1000 if (!is_null($field_desc['default']) && $field_desc['default'] !== '') {
1001 if (in_array($field_desc['type'], array('tinyint
', 'smallint
', 'int', 'double'))) {
1002 $sqlfields[$i] .= " DEFAULT ".((float) $field_desc['default']);
1003 } elseif ($field_desc['default'] == 'null' || $field_desc['default'] == 'CURRENT_TIMESTAMP
') {
1004 $sqlfields[$i] .= " DEFAULT ".$this->sanitize($field_desc['default']);
1006 $sqlfields[$i] .= " DEFAULT '".$this->escape($field_desc['default'])."'";
1009 if (!is_null($field_desc['null']) && $field_desc['null'] !== '') {
1010 $sqlfields[$i] .= " ".$this->sanitize($field_desc['null'], 0, 0, 1);
1012 if (!is_null($field_desc['extra
']) && $field_desc['extra
'] !== '') {
1013 $sqlfields[$i] .= " ".$this->sanitize($field_desc['extra
'], 0, 0, 1);
1017 if ($primary_key != "") {
1018 $pk = "PRIMARY KEY(".$this->sanitize($primary_key).")";
1021 if (is_array($unique_keys)) {
1023 foreach ($unique_keys as $key => $value) {
1024 $sqluq[$i] = "UNIQUE KEY '".$this->sanitize($key)."' ('".$this->escape($value)."')";
1028 if (is_array($keys)) {
1030 foreach ($keys as $key => $value) {
1031 $sqlk[$i] = "KEY ".$this->sanitize($key)." (".$value.")";
1035 $sql .= implode(',
', $sqlfields);
1036 if ($primary_key != "") {
1039 if ($unique_keys != "") {
1040 $sql .= ",".implode(',
', $sqluq);
1042 if (is_array($keys)) {
1043 $sql .= ",".implode(',
', $sqlk);
1046 //$sql .= " engine=".$this->sanitize($type);
1048 if (!$this->query($sql)) {
1055 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1062 public function DDLDropTable($table)
1065 $tmptable = preg_replace('/[^a-z0-9\.\-\_]/i
', '', $table);
1067 $sql = "DROP TABLE ".$tmptable;
1069 if (!$this->query($sql)) {
1076 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1084 public function DDLDescTable($table, $field = "")
1087 $sql = "DESC ".$table." ".$field;
1089 dol_syslog(get_class($this)."::DDLDescTable ".$sql, LOG_DEBUG);
1090 $this->_results = $this->query($sql);
1091 return $this->_results;
1094 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1104 public function DDLAddField($table, $field_name, $field_desc, $field_position = "")
1107 // cles recherchees dans le tableau des descriptions (field_desc) : type,value,attribute,null,default,extra
1108 // ex. : $field_desc = array('
type'=>'int','value
'=>'11
','null'=>'not
null','extra
'=> 'auto_increment
');
1109 $sql = "ALTER TABLE ".$table." ADD ".$field_name." ";
1110 $sql .= $field_desc['type'];
1111 if (isset($field_desc['value
']) && preg_match("/^[^\s]/i", $field_desc['value
'])) {
1112 if (!in_array($field_desc['type'], array('date
', 'datetime
'))) {
1113 $sql .= "(".$field_desc['value
'].")";
1116 if (isset($field_desc['attribute
']) && preg_match("/^[^\s]/i", $field_desc['attribute
'])) {
1117 $sql .= " ".$this->sanitize($field_desc['attribute
']);
1119 if (isset($field_desc['null']) && preg_match("/^[^\s]/i", $field_desc['null'])) {
1120 $sql .= " ".$this->sanitize($field_desc['null'], 0, 0, 1);
1122 if (isset($field_desc['default']) && preg_match("/^[^\s]/i", $field_desc['default'])) {
1123 if (in_array($field_desc['type'], array('tinyint
', 'smallint
', 'int', 'double'))) {
1124 $sql .= " DEFAULT ".((float) $field_desc['default']);
1125 } elseif ($field_desc['default'] == 'null' || $field_desc['default'] == 'CURRENT_TIMESTAMP
') {
1126 $sql .= " DEFAULT ".$this->sanitize($field_desc['default']);
1128 $sql .= " DEFAULT '".$this->escape($field_desc['default'])."'";
1131 if (isset($field_desc['extra
']) && preg_match("/^[^\s]/i", $field_desc['extra
'])) {
1132 $sql .= " ".$this->sanitize($field_desc['extra
'], 0, 0, 1);
1134 $sql .= " ".$this->sanitize($field_position, 0, 0, 1);
1136 dol_syslog(get_class($this)."::DDLAddField ".$sql, LOG_DEBUG);
1137 if (!$this->query($sql)) {
1143 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1152 public function DDLUpdateField($table, $field_name, $field_desc)
1155 $sql = "ALTER TABLE ".$this->sanitize($table);
1156 $sql .= " MODIFY COLUMN ".$this->sanitize($field_name)." ".$this->sanitize($field_desc['
type']);
1157 if ($field_desc['type'] == 'tinyint
' || $field_desc['type'] == 'int' || $field_desc['type'] == 'varchar
') {
1158 $sql .= "(".$this->sanitize($field_desc['value
']).")";
1161 dol_syslog(get_class($this)."::DDLUpdateField ".$sql, LOG_DEBUG);
1162 if (!$this->query($sql)) {
1168 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1176 public function DDLDropField($table, $field_name)
1179 $tmp_field_name = preg_replace('/[^a-z0-9\.\-\_]/i
', '', $field_name);
1181 $sql = "ALTER TABLE ".$this->sanitize($table)." DROP COLUMN `".$this->sanitize($tmp_field_name)."`";
1182 if (!$this->query($sql)) {
1183 $this->error = $this->lasterror();
1190 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1200 public function DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name)
1203 $sql = "INSERT INTO user ";
1204 $sql .= "(Host,User,password,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Index_Priv,Alter_priv,Lock_tables_priv)";
1205 $sql .= " VALUES ('
".$this->escape($dolibarr_main_db_host)."','".$this->escape($dolibarr_main_db_user)."',password('".addslashes($dolibarr_main_db_pass)."')";
1206 $sql .= ",'Y
','Y
','Y
','Y
','Y
','Y
','Y
','Y
','Y
')";
1208 dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log
1209 $resql = $this->query($sql);
1214 $sql = "INSERT INTO db ";
1215 $sql .= "(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Index_Priv,Alter_priv,Lock_tables_priv)";
1216 $sql .= " VALUES ('".$this->escape($dolibarr_main_db_host)."','".$this->escape($dolibarr_main_db_name)."','".addslashes($dolibarr_main_db_user)."'";
1217 $sql .= ",'Y
','Y
','Y
','Y
','Y
','Y
','Y
','Y
','Y
')";
1219 dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG);
1220 $resql = $this->query($sql);
1225 $sql = "FLUSH Privileges";
1227 dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG);
1228 $resql = $this->query($sql);
1240 public function getDefaultCharacterSetDatabase()
1250 public function getListOfCharacterSet()
1254 $liste[$i]['charset
'] = 'UTF-8
';
1264 public function getDefaultCollationDatabase()
1274 public function getListOfCollation()
1278 $liste[$i]['charset
'] = 'UTF-8
';
1288 public function getPathOfDump()
1290 // FIXME: not for SQLite
1291 $fullpathofdump = '/pathtomysqldump/mysqldump
';
1293 $resql = $this->query('SHOW VARIABLES LIKE \
'basedir\'');
1296 $basedir = $liste[
'Value'];
1297 $fullpathofdump = $basedir.(preg_match(
'/\/$/', $basedir) ?
'' :
'/').
'bin/mysqldump';
1299 return $fullpathofdump;
1310 $fullpathofimport =
'/pathtomysql/mysql';
1312 $resql = $this->
query(
'SHOW VARIABLES LIKE \'basedir\'');
1315 $basedir = $liste[
'Value'];
1316 $fullpathofimport = $basedir.(preg_match(
'/\/$/', $basedir) ?
'' :
'/').
'bin/mysql';
1318 return $fullpathofimport;
1331 if (!isset($pragmas)) {
1336 'application_id',
'auto_vacuum',
'automatic_index',
'busy_timeout',
'cache_size',
1337 'cache_spill',
'case_sensitive_like',
'checkpoint_fullsync',
'collation_list',
1338 'compile_options',
'data_version',
1339 'defer_foreign_keys',
'encoding',
'foreign_key_check',
'freelist_count',
1340 'full_column_names',
'fullsync',
'ingore_check_constraints',
'integrity_check',
1341 'journal_mode',
'journal_size_limit',
'legacy_file_format',
'locking_mode',
1342 'max_page_count',
'page_count',
'page_size',
'parser_trace',
1343 'query_only',
'quick_check',
'read_uncommitted',
'recursive_triggers',
1344 'reverse_unordered_selects',
'schema_version',
'user_version',
1345 'secure_delete',
'short_column_names',
'shrink_memory',
'soft_heap_limit',
1346 'synchronous',
'temp_store',
'threads',
1347 'vdbe_addoptrace',
'vdbe_debug',
'vdbe_listing',
'vdbe_trace',
1348 'wal_autocheckpoint',
1353 foreach ($pragmas as $var) {
1354 $sql =
"PRAGMA $var";
1355 $resql = $this->
query($sql);
1359 $result[$var] = $obj[0];
1362 $result[$var] =
'FAIL';
1404 $newname = preg_replace(
'/_/',
'', $name);
1405 $localname = __CLASS__.
'::db'.$newname;
1406 $reflectClass =
new ReflectionClass(__CLASS__);
1407 $reflectFunction = $reflectClass->getMethod(
'db'.$newname);
1408 if ($arg_count < 0) {
1409 $arg_count = $reflectFunction->getNumberOfParameters();
1411 if (!$this->db->createFunction($name, $localname, $arg_count)) {
1412 $this->
error =
"unable to create custom function '$name'";
1430 if ($y == 0 && $month == 0) {
1433 $num = (365 * $y + 31 * ($month - 1) + $day);
1437 $num -= floor(($month * 4 + 23) / 10);
1439 $temp = floor(($y / 100 + 1) * 3 / 4);
1440 return (
int) ($num + floor($y / 4) - $temp);
1454 $ret = (int) floor(($daynr + 5 + ($sunday_first_day_of_week ? 1 : 0)) % 7);
1468 return (($year & 3) == 0 && ($year % 100 || ($year % 400 == 0 && $year)) ? 366 : 365);
1482 private static function calc_week($year, $month, $day, $week_behaviour, &$calc_year)
1487 $monday_first = ($week_behaviour & self::WEEK_MONDAY_FIRST) ? 1 : 0;
1488 $week_year = ($week_behaviour & self::WEEK_YEAR) ? 1 : 0;
1489 $first_weekday = ($week_behaviour & self::WEEK_FIRST_WEEKDAY) ? 1 : 0;
1494 if ($month == 1 && $day <= 7 - $weekday) {
1495 if (!$week_year && (($first_weekday && $weekday != 0) || (!$first_weekday && $weekday >= 4))) {
1501 $weekday = ($weekday + 53 * 7 - $days) % 7;
1504 if (($first_weekday && $weekday != 0) || (!$first_weekday && $weekday >= 4)) {
1505 $days = $daynr - ($first_daynr + (7 - $weekday));
1507 $days = $daynr - ($first_daynr - $weekday);
1510 if ($week_year && $days >= 52 * 7) {
1512 if ((!$first_weekday && $weekday < 4) || ($first_weekday && $weekday == 0)) {
1517 return (
int) floor($days / 7 + 1);
Class to manage Dolibarr database access.
lastqueryerror()
Return last query in error.
lasterror()
Return last error label.
lasterrno()
Return last error code.
lastquery()
Return last request executed with query()
Class to manage Dolibarr database access for a SQLite database.
fetch_object($resultset)
Returns the current line (as an object) for the resultset cursor.
escape($stringtoencode)
Escape a string to insert data.
fetch_array($resultset)
Return datas as an array.
query($query, $usesavepoint=0, $type='auto', $result_mode=0)
Execute a SQL request and return the resultset.
error()
Renvoie le texte de l'erreur mysql de l'operation precedente.
fetch_row($resultset)
Return datas as an array.
getPathOfRestore()
Return full path of restore program.
errno()
Renvoie le code erreur generique de l'operation precedente.
const LABEL
Database label.
close()
Close database connection.
static calc_days_in_year($year)
calc_days_in_year
static calc_daynr($year, $month, $day)
calc_daynr
escapeforlike($stringtoencode)
Escape a string to insert data into a like.
static calc_weekday($daynr, $sunday_first_day_of_week)
calc_weekday
encrypt($fieldorvalue, $withQuotes=1)
Encrypt sensitive data in database Warning: This function includes the escape and add the SQL simple ...
last_insert_id($tab, $fieldid='rowid')
Get last ID after an insert INSERT.
affected_rows($resultset)
Return number of lines for result of a SELECT.
decrypt($value)
Decrypt sensitive data in database.
static calc_week($year, $month, $day, $week_behaviour, &$calc_year)
calc_week
getDriverInfo()
Return version of database client driver.
addCustomFunction($name, $arg_count=-1)
Add a custom function in the database engine (STORED PROCEDURE) Notes:
select_db($database)
Select a database.
connect($host, $login, $passwd, $name, $port=0)
Connection to server.
num_rows($resultset)
Return number of lines for result of a SELECT.
convertSQLFromMysql($line, $type='ddl')
Convert a SQL request in Mysql syntax to native syntax.
getVersion()
Return version of database server.
getServerParametersValues($filter='')
Return value of server parameters.
free($resultset=null)
Free last resultset used.
__construct($type, $host, $user, $pass, $name='', $port=0)
Constructor.
const VERSIONMIN
Version min database.
getServerStatusValues($filter='')
Return value of server status.
print $script_file $mode $langs defaultlang(is_numeric($duration_value) ? " delay=". $duration_value :"").(is_numeric($duration_value2) ? " after cd cd cd description as description
Only used if Module[ID]Desc translation string is not found.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
if(preg_match('/crypted:/i', $dolibarr_main_db_pass)||!empty($dolibarr_main_db_encrypted_pass)) $conf db type
publicphonebutton2 phonegreen basiclayout basiclayout TotalHT VATCode TotalVAT TotalLT1 TotalLT2 TotalTTC TotalHT clearboth nowraponall TAKEPOS_SHOW_SUBPRICE right right right takeposterminal SELECT e rowid