dolibarr  9.0.0
mssql.class.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (C) 2002-2007 Rodolphe Quiedeville <rodolphe@quiedeville.org>
3  * Copyright (C) 2004-2008 Laurent Destailleur <eldy@users.sourceforge.net>
4  * Copyright (C) 2005-2007 Regis Houssin <regis.houssin@inodbox.com>
5  * Copyright (C) 2007 Simon Desee <simon@dedisoft.com>
6  * Copyright (C) 2015 Cedric GROSS <c.gross@kreiz-it.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 <http://www.gnu.org/licenses/>.
20  */
21 
27 require_once DOL_DOCUMENT_ROOT .'/core/db/DoliDB.class.php';
28 
32 class DoliDBMssql extends DoliDB
33 {
35  public $type='mssql';
37  const LABEL='MSSQL';
39  var $forcecharset='latin1'; // Can't be static as it may be forced with a dynamic value
41  var $forcecollate='latin1_swedish_ci'; // Can't be static as it may be forced with a dynamic value
43  const VERSIONMIN='2000';
45  private $_results;
46 
58  function __construct($type, $host, $user, $pass, $name='', $port=0)
59  {
60  global $langs;
61 
62  $this->database_user=$user;
63  $this->database_host=$host;
64  $this->database_port=$port;
65  $this->transaction_opened=0;
66 
67  if (! function_exists("mssql_connect"))
68  {
69  $this->connected = false;
70  $this->ok = false;
71  $this->error="Mssql PHP functions for using MSSql driver are not available in this version of PHP";
72  dol_syslog(get_class($this)."::DoliDBMssql : MSsql PHP functions for using MSsql driver are not available in this version of PHP",LOG_ERR);
73  return $this->ok;
74  }
75 
76  if (! $host)
77  {
78  $this->connected = false;
79  $this->ok = false;
80  $this->error=$langs->trans("ErrorWrongHostParameter");
81  dol_syslog(get_class($this)."::DoliDBMssql : Erreur Connect, wrong host parameters",LOG_ERR);
82  return $this->ok;
83  }
84 
85  // Essai connexion serveur
86  $this->db = $this->connect($host, $user, $pass, $name, $port);
87  if ($this->db)
88  {
89  // Si client connecte avec charset different de celui de la base Dolibarr
90  // (La base Dolibarr a ete forcee en this->forcecharset a l'install)
91  $this->connected = true;
92  $this->ok = true;
93  }
94  else
95  {
96  // host, login ou password incorrect
97  $this->connected = false;
98  $this->ok = false;
99  $this->error=mssql_get_last_message();
100  dol_syslog(get_class($this)."::DoliDBMssql : Erreur Connect mssql_get_last_message=".$this->error,LOG_ERR);
101  }
102 
103  // Si connexion serveur ok et si connexion base demandee, on essaie connexion base
104  if ($this->connected && $name)
105  {
106  if ($this->select_db($name))
107  {
108  $this->database_selected = true;
109  $this->database_name = $name;
110  $this->ok = true;
111  }
112  else
113  {
114  $this->database_selected = false;
115  $this->database_name = '';
116  $this->ok = false;
117  $this->error=$this->error();
118  dol_syslog(get_class($this)."::DoliDBMssql : Erreur Select_db ".$this->error,LOG_ERR);
119  }
120  }
121  else
122  {
123  // Pas de selection de base demandee, ok ou ko
124  $this->database_selected = false;
125  }
126 
127  return $this->ok;
128  }
129 
137  static function convertSQLFromMysql($line,$type='ddl')
138  {
139  return $line;
140  }
141 
142  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
149  function select_db($database)
150  {
151  // phpcs:enable
152  return @mssql_select_db($database, $this->db);
153  }
154 
166  function connect($host, $login, $passwd, $name, $port=0)
167  {
168  dol_syslog(get_class($this)."::connect host=$host, port=$port, login=$login, passwd=--hidden--, name=$name");
169  $newhost=$host;
170  if ($port) $newhost.=':'.$port;
171  $this->db = @mssql_connect($newhost, $login, $passwd);
172  //force les enregistrement en latin1 si la base est en utf8 par defaut
173  // Supprime car plante sur mon PHP-Mysql. De plus, la base est forcement en latin1 avec
174  // les nouvelles version de Dolibarr car force par l'install Dolibarr.
175  //$this->query('SET NAMES '.$this->forcecharset);
176  //print "Resultat fonction connect: ".$this->db;
177  $set_options=array('SET ANSI_PADDING ON;',
178  "SET ANSI_NULLS ON;",
179  "SET ANSI_WARNINGS ON;",
180  "SET ARITHABORT ON;",
181  "SET CONCAT_NULL_YIELDS_NULL ON;",
182  "SET QUOTED_IDENTIFIER ON;"
183  );
184  mssql_query(implode(' ',$set_options),$this->db);
185 
186  return $this->db;
187  }
188 
194  function getVersion()
195  {
196  $resql=$this->query("SELECT @@VERSION");
197  if ($resql)
198  {
199  $version=$this->fetch_array($resql);
200  return $version['computed'];
201  }
202  else return '';
203  }
204 
210  function getDriverInfo()
211  {
212  return 'php mssql driver';
213  }
214 
221  function close()
222  {
223  if ($this->db)
224  {
225  if ($this->transaction_opened > 0) dol_syslog(get_class($this)."::close Closing a connection with an opened transaction depth=".$this->transaction_opened,LOG_ERR);
226  $this->connected=false;
227  return mssql_close($this->db);
228  }
229  return false;
230  }
231 
232 
238  function begin()
239  {
240 
241  $res=mssql_query('select @@TRANCOUNT');
242  $this->transaction_opened=mssql_result($res, 0, 0);
243 
244  if ($this->transaction_opened == 0)
245  {
246  //return 1; //There is a mess with auto_commit and 'SET IMPLICIT_TRANSACTIONS ON' generate also a mess
247  $ret=mssql_query("SET IMPLICIT_TRANSACTIONS OFF;BEGIN TRANSACTION;",$this->db);
248  if ($ret)
249  {
250  dol_syslog("BEGIN Transaction",LOG_DEBUG);
251  }
252  return $ret;
253  }
254  else
255  {
256  return true;
257  }
258  }
259 
266  function commit($log='')
267  {
268  $res=mssql_query('select @@TRANCOUNT');
269  $this->transaction_opened=mssql_result($res, 0, 0);
270 
271  if ($this->transaction_opened == 1)
272  {
273  //return 1; //There is a mess with auto_commit and 'SET IMPLICIT_TRANSACTION ON' generate also a mess
274  $ret=mssql_query("COMMIT TRANSACTION",$this->db);
275  if ($ret)
276  {
277  dol_syslog("COMMIT Transaction",LOG_DEBUG);
278  return true;
279  }
280  else
281  {
282  return false;
283  }
284  }
285  elseif ($this->transaction_opened > 1)
286  {
287  return true;
288  }
289  trigger_error("Commit requested but no transaction remain");
290  return false;
291  }
292 
299  function rollback($log='')
300  {
301  $res=mssql_query('select @@TRANCOUNT');
302  $this->transaction_opened=mssql_result($res, 0, 0);
303 
304  if ($this->transaction_opened == 1)
305  {
306  $ret=mssql_query("ROLLBACK TRANSACTION",$this->db);
307  dol_syslog("ROLLBACK Transaction".($log?' '.$log:''),LOG_DEBUG);
308  return $ret;
309  }
310  elseif ($this->transaction_opened > 1)
311  {
312  return true;
313  }
314  trigger_error("Rollback requested but no transaction remain");
315  return false;
316  }
317 
327  function query($query,$usesavepoint=0,$type='auto')
328  {
329  $query = trim($query);
330 
331  if (preg_match('/^--/',$query)) return true;
332 
333  // Conversion syntaxe MySql vers MSDE.
334  $query = str_ireplace("now()", "getdate()", $query);
335  // Erreur SQL: cannot update timestamp field
336  $query = str_ireplace(", tms = tms", "", $query);
337 
338  $query=preg_replace("/([. ,\t(])(percent|file|public)([. ,=\t)])/","$1[$2]$3",$query);
339 
340  if ($type=="auto" || $type='dml')
341  {
342  $query=preg_replace('/AUTO_INCREMENT/i','IDENTITY',$query);
343  $query=preg_replace('/double/i','float',$query);
344  $query=preg_replace('/float\((.*)\)/','numeric($1)',$query);
345  $query=preg_replace('/([ \t])unsigned|IF NOT EXISTS[ \t]/i','$1',$query);
346  $query=preg_replace('/([ \t])(MEDIUM|TINY|LONG){0,1}TEXT([ \t,])/i',"$1VARCHAR(MAX)$3",$query);
347 
348  $matches=array();
349  $original_query='';
350  if (preg_match('/ALTER TABLE\h+(\w+?)\h+ADD\h+(?:(UNIQUE)|INDEX)\h+(?:INDEX)?\h*(\w+?)\h*\((.+)\)/is', $query,$matches))
351  {
352  $original_query=$query;
353  $query="CREATE ".trim($matches[2])." INDEX [".trim($matches[3])."] ON [".trim($matches[1])."] (".trim($matches[4]).")";
354  if ($matches[2]) {
355  //check if columun is nullable cause Sql server only allow 1 null value if unique index.
356  $fields=explode(",",trim($matches[4]));
357  $fields_clear=array_map('trim',$fields);
358  $infos=$this->GetFieldInformation(trim($matches[1]), $fields_clear);
359  $query_comp=array();
360  foreach($infos as $fld) {
361  if ($fld->IS_NULLABLE == 'YES') {
362  $query_comp[]=$fld->COLUMN_NAME." IS NOT NULL";
363  }
364  }
365  if (! empty($query_comp))
366  $query.=" WHERE ".implode(" AND ",$query_comp);
367  }
368  }
369  else
370  {
371  if (preg_match('/ALTER TABLE\h+(\w+?)\h+ADD\h+PRIMARY\h+KEY\h+(\w+?)\h*\((.+)\)/is', $query, $matches))
372  {
373  $original_query=$query;
374  $query="ALTER TABLE [".$matches[1]."] ADD CONSTRAINT [".$matches[2]."] PRIMARY KEY CLUSTERED (".$matches[3].")";
375  }
376  }
377  }
378 
379  if ($type=="auto" || $type='ddl')
380  {
381  $itemfound = stripos($query, " limit ");
382  if ($itemfound !== false) {
383  // Extraire le nombre limite
384  $number = stristr($query, " limit ");
385  $number = substr($number, 7);
386  // Inserer l'instruction TOP et le nombre limite
387  $query = str_ireplace("select ", "select top ".$number." ", $query);
388  // Supprimer l'instruction MySql
389  $query = str_ireplace(" limit ".$number, "", $query);
390  }
391 
392  $itemfound = stripos($query, " week(");
393  if ($itemfound !== false) {
394  // Recreer une requete sans instruction Mysql
395  $positionMySql = stripos($query, " week(");
396  $newquery = substr($query, 0, $positionMySql);
397 
398  // Recuperer la date passee en parametre
399  $extractvalue = stristr($query, " week(");
400  $extractvalue = substr($extractvalue, 6);
401  $positionMySql = stripos($extractvalue, ")");
402  // Conserver la fin de la requete
403  $endofquery = substr($extractvalue, $positionMySql);
404  $extractvalue = substr($extractvalue, 0, $positionMySql);
405 
406  // Remplacer l'instruction MySql en Sql Server
407  // Inserer la date en parametre et le reste de la requete
408  $query = $newquery." DATEPART(week, ".$extractvalue.$endofquery;
409  }
410  if (preg_match('/^insert\h+(?:INTO)?\h*(\w+?)\h*\(.*\b(?:row)?id\b.*\)\h+VALUES/i',$query,$matches))
411  {
412  //var_dump($query);
413  //var_dump($matches);
414  //if (stripos($query,'llx_c_departements') !== false) var_dump($query);
415  $sql='SET IDENTITY_INSERT ['.trim($matches[1]).'] ON;';
416  @mssql_query($sql, $this->db);
417  $post_query='SET IDENTITY_INSERT ['.trim($matches[1]).'] OFF;';
418  }
419  }
420  //print "<!--".$query."-->";
421 
422  if (! in_array($query,array('BEGIN','COMMIT','ROLLBACK'))) dol_syslog('sql='.$query, LOG_DEBUG);
423 
424  if (! $this->database_name)
425  {
426  // Ordre SQL ne necessitant pas de connexion a une base (exemple: CREATE DATABASE)
427  $ret = mssql_query($query, $this->db);
428  }
429  else
430  {
431  $ret = mssql_query($query, $this->db);
432  }
433 
434  if (!empty($post_query))
435  {
436  @mssql_query($post_query, $this->db);
437  }
438 
439  if (! preg_match("/^COMMIT/i",$query) && ! preg_match("/^ROLLBACK/i",$query))
440  {
441  // Si requete utilisateur, on la sauvegarde ainsi que son resultset
442  if (! $ret)
443  {
444  $result = mssql_query("SELECT @@ERROR as code", $this->db);
445  $row = mssql_fetch_array($result);
446 
447  $this->lastqueryerror = $query;
448  $this->lasterror = $this->error();
449  $this->lasterrno = $row["code"];
450 
451  dol_syslog(get_class($this)."::query SQL Error query: ".$query, LOG_ERR);
452  if ($original_query) dol_syslog(get_class($this)."::query SQL Original query: ".$original_query, LOG_ERR);
453  dol_syslog(get_class($this)."::query SQL Error message: ".$this->lasterror." (".$this->lasterrno.")", LOG_ERR);
454  }
455  $this->lastquery=$query;
456  $this->_results = $ret;
457  }
458 
459  return $ret;
460  }
461 
462  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
469  function fetch_object($resultset)
470  {
471  // phpcs:enable
472  // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion
473  if (! is_resource($resultset)) { $resultset=$this->_results; }
474  return mssql_fetch_object($resultset);
475  }
476 
477  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
484  function fetch_array($resultset)
485  {
486  // phpcs:enable
487  // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion
488  if (! is_resource($resultset)) { $resultset=$this->_results; }
489  return mssql_fetch_array($resultset);
490  }
491 
492 
493  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
500  function fetch_row($resultset)
501  {
502  // phpcs:enable
503  // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion
504  if (! is_resource($resultset)) { $resultset=$this->_results; }
505  return @mssql_fetch_row($resultset);
506  }
507 
508  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
516  function num_rows($resultset)
517  {
518  // phpcs:enable
519  // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion
520  if (! is_resource($resultset)) { $resultset=$this->_results; }
521  return mssql_num_rows($resultset);
522  }
523 
524  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
532  function affected_rows($resultset)
533  {
534  // phpcs:enable
535  // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion
536  if (! is_resource($resultset)) { $resultset=$this->_results; }
537  // mssql necessite un link de base pour cette fonction contrairement
538  // a pqsql qui prend un resultset
539  $rsRows = mssql_query("select @@rowcount as rows", $this->db);
540  return mssql_result($rsRows, 0, "rows");
541  //return mssql_affected_rows($this->db);
542  }
543 
544 
551  function free($resultset=null)
552  {
553  // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion
554  if (! is_resource($resultset)) { $resultset=$this->_results; }
555  // Si resultset en est un, on libere la memoire
556  if (is_resource($resultset)) mssql_free_result($resultset);
557  }
558 
565  function escape($stringtoencode)
566  {
567  return addslashes($stringtoencode);
568  }
569 
570 
578  function idate($param)
579  {
580  return dol_print_date($param,"%Y-%m-%d %H:%M:%S");
581  }
582 
588  function errno()
589  {
590  if (! $this->connected)
591  {
592  // Si il y a eu echec de connexion, $this->db n'est pas valide.
593  return 'DB_ERROR_FAILED_TO_CONNECT';
594  }
595  else
596  {
597  // Constants to convert a MSSql error code to a generic Dolibarr error code
598  $errorcode_map = array(
599  1004 => 'DB_ERROR_CANNOT_CREATE',
600  1005 => 'DB_ERROR_CANNOT_CREATE',
601  1006 => 'DB_ERROR_CANNOT_CREATE',
602  1007 => 'DB_ERROR_ALREADY_EXISTS',
603  1008 => 'DB_ERROR_CANNOT_DROP',
604  1025 => 'DB_ERROR_NO_FOREIGN_KEY_TO_DROP',
605  1046 => 'DB_ERROR_NODBSELECTED',
606  1048 => 'DB_ERROR_CONSTRAINT',
607  2714 => 'DB_ERROR_TABLE_ALREADY_EXISTS',
608  1051 => 'DB_ERROR_NOSUCHTABLE',
609  1054 => 'DB_ERROR_NOSUCHFIELD',
610  1060 => 'DB_ERROR_COLUMN_ALREADY_EXISTS',
611  1061 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS',
612  2627 => 'DB_ERROR_RECORD_ALREADY_EXISTS',
613  102 => 'DB_ERROR_SYNTAX',
614  8120 => 'DB_ERROR_GROUP_BY_SYNTAX',
615  1068 => 'DB_ERROR_PRIMARY_KEY_ALREADY_EXISTS',
616  1075 => 'DB_ERROR_CANT_DROP_PRIMARY_KEY',
617  1091 => 'DB_ERROR_NOSUCHFIELD',
618  1100 => 'DB_ERROR_NOT_LOCKED',
619  1136 => 'DB_ERROR_VALUE_COUNT_ON_ROW',
620  1146 => 'DB_ERROR_NOSUCHTABLE',
621  1216 => 'DB_ERROR_NO_PARENT',
622  1217 => 'DB_ERROR_CHILD_EXISTS',
623  1451 => 'DB_ERROR_CHILD_EXISTS',
624  1913 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS'
625  );
626 
627  if (isset($errorcode_map[$this->lasterrno]))
628  {
629  return $errorcode_map[$this->lasterrno];
630  }
631  $errno=$this->lasterrno;
632  return ($errno?'DB_ERROR_'.$errno:'0');
633  }
634  }
635 
641  function error()
642  {
643  if (! $this->connected) {
644  // Si il y a eu echec de connexion, $this->db n'est pas valide pour mssql_get_last_message.
645  return 'Not connected. Check setup parameters in conf/conf.php file and your mssql client and server versions';
646  }
647  else {
648  return mssql_get_last_message();
649  }
650  }
651 
652  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
660  function last_insert_id($tab,$fieldid='rowid')
661  {
662  // phpcs:enable
663  $res = $this->query("SELECT @@IDENTITY as id");
664  if ($res && $data = $this->fetch_array($res))
665  {
666  return $data["id"];
667  }
668  else
669  {
670  return -1;
671  }
672  }
673 
682  function encrypt($fieldorvalue, $withQuotes=0)
683  {
684  global $conf;
685 
686  // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption)
687  $cryptType = ($conf->db->dolibarr_main_db_encryption?$conf->db->dolibarr_main_db_encryption:0);
688 
689  //Encryption key
690  $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey)?$conf->db->dolibarr_main_db_cryptkey:'');
691 
692  $return = $fieldorvalue;
693  return ($withQuotes?"'":"").$this->escape($return).($withQuotes?"'":"");
694  }
695 
702  function decrypt($value)
703  {
704  global $conf;
705 
706  // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption)
707  $cryptType = ($conf->db->dolibarr_main_db_encryption?$conf->db->dolibarr_main_db_encryption:0);
708 
709  //Encryption key
710  $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey)?$conf->db->dolibarr_main_db_cryptkey:'');
711 
712  $return = $value;
713  return $return;
714  }
715 
716 
717  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
723  function DDLGetConnectId()
724  {
725  // phpcs:enable
726  $resql=$this->query('SELECT CONNECTION_ID()');
727  if ($resql)
728  {
729  $row=$this->fetch_row($resql);
730  return $row[0];
731  }
732  else return '?';
733  }
734 
735  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
747  function DDLCreateDb($database,$charset='',$collation='',$owner='')
748  {
749  // phpcs:enable
750  /*if (empty($charset)) $charset=$this->forcecharset;
751  if (empty($collation)) $collation=$this->forcecollate;
752  */
753 
754  $sql = 'CREATE DATABASE '.$this->EscapeFieldName($database);
755  //TODO: Check if we need to force a charset
756  //$sql.= ' DEFAULT CHARACTER SET '.$charset.' DEFAULT COLLATE '.$collation;
757  $ret=$this->query($sql);
758 
759  $this->select_db($database);
760  $sql="CREATE USER [$owner] FOR LOGIN [$owner]";
761  mssql_query($sql,$this->db);
762  $sql="ALTER ROLE [db_owner] ADD MEMBER [$owner]";
763  mssql_query($sql,$this->db);
764 
765  $sql="ALTER DATABASE [$database] SET ANSI_NULL_DEFAULT ON;";
766  @mssql_query($sql,$this->db);
767  $sql="ALTER DATABASE [$database] SET ANSI_NULL ON;";
768  @mssql_query($sql,$this->db);
769 
770  return $ret;
771  }
772 
773  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
781  function DDLListTables($database,$table='')
782  {
783  // phpcs:enable
784  $this->_results = mssql_list_tables($database, $this->db);
785  return $this->_results;
786  }
787 
788  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
795  function DDLInfoTable($table)
796  {
797  // phpcs:enable
798 
799  // FIXME: Dummy method
800  // TODO: Implement
801  // May help: https://stackoverflow.com/questions/600446/sql-server-how-do-you-return-the-column-names-from-a-table
802 
803  $infotables=array();
804  return $infotables;
805  }
806 
807  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
820  function DDLCreateTable($table,$fields,$primary_key,$type,$unique_keys=null,$fulltext_keys=null,$keys=null)
821  {
822  // phpcs:enable
823  // FIXME: $fulltext_keys parameter is unused
824 
825  // cles recherchees dans le tableau des descriptions (fields) : type,value,attribute,null,default,extra
826  // ex. : $fields['rowid'] = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment');
827  $sql = "create table ".$table."(";
828  $i=0;
829  foreach($fields as $field_name => $field_desc)
830  {
831  $sqlfields[$i] = $field_name." ";
832  $sqlfields[$i] .= $field_desc['type'];
833  if( preg_match("/^[^\s]/i",$field_desc['value']))
834  $sqlfields[$i] .= "(".$field_desc['value'].")";
835  else if( preg_match("/^[^\s]/i",$field_desc['attribute']))
836  $sqlfields[$i] .= " ".$field_desc['attribute'];
837  else if( preg_match("/^[^\s]/i",$field_desc['default']))
838  {
839  if(preg_match("/null/i",$field_desc['default']))
840  $sqlfields[$i] .= " default ".$field_desc['default'];
841  else
842  $sqlfields[$i] .= " default '".$field_desc['default']."'";
843  }
844  else if( preg_match("/^[^\s]/i",$field_desc['null']))
845  $sqlfields[$i] .= " ".$field_desc['null'];
846 
847  else if( preg_match("/^[^\s]/i",$field_desc['extra']))
848  $sqlfields[$i] .= " ".$field_desc['extra'];
849  $i++;
850  }
851  if($primary_key != "")
852  $pk = "primary key(".$primary_key.")";
853 
854  if(is_array($unique_keys))
855  {
856  $i = 0;
857  foreach($unique_keys as $key => $value)
858  {
859  $sqluq[$i] = "UNIQUE KEY '".$key."' ('".$value."')";
860  $i++;
861  }
862  }
863  if(is_array($keys))
864  {
865  $i = 0;
866  foreach($keys as $key => $value)
867  {
868  $sqlk[$i] = "KEY ".$key." (".$value.")";
869  $i++;
870  }
871  }
872  $sql .= implode(',',$sqlfields);
873  if($primary_key != "")
874  $sql .= ",".$pk;
875  if(is_array($unique_keys))
876  $sql .= ",".implode(',',$sqluq);
877  if(is_array($keys))
878  $sql .= ",".implode(',',$sqlk);
879  $sql .=") type=".$type;
880 
881  dol_syslog($sql);
882  if(! $this -> query($sql))
883  return -1;
884  else
885  return 1;
886  }
887 
888  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
895  function DDLDropTable($table)
896  {
897  // phpcs:enable
898  $sql = "DROP TABLE ".$table;
899 
900  if (! $this->query($sql))
901  return -1;
902  else
903  return 1;
904  }
905 
906  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
914  function DDLDescTable($table,$field="")
915  {
916  // phpcs:enable
917  $sql="DESC ".$table." ".$field;
918 
919  dol_syslog($sql);
920  $this->_results = $this->query($sql);
921  return $this->_results;
922  }
923 
924  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
934  function DDLAddField($table,$field_name,$field_desc,$field_position="")
935  {
936  // phpcs:enable
937  // cles recherchees dans le tableau des descriptions (field_desc) : type,value,attribute,null,default,extra
938  // ex. : $field_desc = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment');
939  $sql= "ALTER TABLE ".$table." ADD ".$field_name." ";
940  $sql .= $field_desc['type'];
941  if( preg_match("/^[^\s]/i",$field_desc['value']))
942  $sql .= "(".$field_desc['value'].")";
943  if( preg_match("/^[^\s]/i",$field_desc['attribute']))
944  $sql .= " ".$field_desc['attribute'];
945  if( preg_match("/^[^\s]/i",$field_desc['null']))
946  $sql .= " ".$field_desc['null'];
947  if( preg_match("/^[^\s]/i",$field_desc['default']))
948  if(preg_match("/null/i",$field_desc['default']))
949  $sql .= " default ".$field_desc['default'];
950  else
951  $sql .= " default '".$field_desc['default']."'";
952  if( preg_match("/^[^\s]/i",$field_desc['extra']))
953  $sql .= " ".$field_desc['extra'];
954  $sql .= " ".$field_position;
955 
956  if(! $this -> query($sql))
957  return -1;
958  else
959  return 1;
960  }
961 
962  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
971  function DDLUpdateField($table,$field_name,$field_desc)
972  {
973  // phpcs:enable
974  $sql = "ALTER TABLE ".$table;
975  $sql .= " MODIFY COLUMN ".$field_name." ".$field_desc['type'];
976  if ($field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int' || $field_desc['type'] == 'varchar') {
977  $sql.="(".$field_desc['value'].")";
978  }
979 
980  dol_syslog($sql,LOG_DEBUG);
981  if (! $this->query($sql))
982  return -1;
983  else
984  return 1;
985  }
986 
987  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
995  function DDLDropField($table,$field_name)
996  {
997  // phpcs:enable
998  $sql= "ALTER TABLE ".$table." DROP COLUMN `".$field_name."`";
999  dol_syslog($sql,LOG_DEBUG);
1000  if (! $this->query($sql))
1001  {
1002  $this->error=$this->lasterror();
1003  return -1;
1004  }
1005  else return 1;
1006  }
1007 
1008  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
1018  function DDLCreateUser($dolibarr_main_db_host,$dolibarr_main_db_user,$dolibarr_main_db_pass,$dolibarr_main_db_name)
1019  {
1020  // phpcs:enable
1021  $sql = "CREATE LOGIN ".$this->EscapeFieldName($dolibarr_main_db_user)." WITH PASSWORD='$dolibarr_main_db_pass'";
1022  dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log
1023  $resql=$this->query($sql);
1024  if (! $resql)
1025  {
1026  if ($this->lasterrno != '15025')
1027  {
1028  return -1;
1029  }
1030  else
1031  {
1032  // If user already exists, we continue to set permissions
1033  dol_syslog(get_class($this)."::DDLCreateUser sql=".$sql, LOG_WARNING);
1034  }
1035  }
1036  $sql="SELECT name from sys.databases where name='".$dolibarr_main_db_name."'";
1037  $ressql=$this->query($sql);
1038  if (! $ressql)
1039  {
1040  dol_syslog(get_class($this)."::DDLCreateUser sql=".$sql, LOG_WARNING);
1041  return -1;
1042  }
1043  else
1044  {
1045  if ($num)
1046  {
1047  $this->select_db($dolibarr_main_db_name);
1048  $sql="CREATE USER [$dolibarr_main_db_user] FOR LOGIN [$dolibarr_main_db_user]";
1049  $this->query($sql);
1050  $sql="ALTER ROLE [db_owner] ADD MEMBER [$dolibarr_main_db_user]";
1051  $this->query($sql);
1052  }
1053  }
1054  return 1;
1055  }
1056 
1063  {
1064  // FIXME: Dummy method
1065  // TODO: Implement
1066 
1067  return '';
1068  }
1069 
1076  {
1077  // FIXME: Dummy method
1078  // TODO: Implement
1079 
1080  return '';
1081  }
1082 
1089  {
1090  $resql=$this->query("SELECT SERVERPROPERTY('collation')");
1091  if (!$resql)
1092  {
1093  return $this->forcecollate;
1094  }
1095  $liste=$this->fetch_array($resql);
1096  return $liste['computed'];
1097  }
1098 
1105  {
1106  // FIXME: Dummy method
1107  // TODO: Implement
1108 
1109  return array();
1110  }
1111 
1117  function getPathOfDump()
1118  {
1119  // FIXME: Dummy method
1120  // TODO: Implement
1121 
1122  return '';
1123  }
1124 
1130  function getPathOfRestore()
1131  {
1132  // FIXME: Dummy method
1133  // TODO: Implement
1134 
1135  return '';
1136  }
1137 
1144  function getServerParametersValues($filter='')
1145  {
1146  // FIXME: Dummy method
1147  // TODO: Implement
1148  // May help: SELECT SERVERPROPERTY
1149 
1150  $result=array();
1151  return $result;
1152  }
1153 
1160  function getServerStatusValues($filter='')
1161  {
1162  // FIXME: Dummy method
1163  // TODO: Implement
1164  // May help: http://www.experts-exchange.com/Database/MS-SQL-Server/Q_20971756.html
1165 
1166  return array();
1167  }
1168 
1169  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
1176  function EscapeFieldName($fieldname)
1177  {
1178  // phpcs:enable
1179  return "[".$fieldname."]";
1180  }
1181 
1182 
1183  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
1191  function GetFieldInformation($table,$fields)
1192  {
1193  // phpcs:enable
1194  $sql="SELECT * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='".$this->escape($table)."' AND COLUMN_NAME";
1195  if (is_array($fields))
1196  {
1197  $where=" IN ('".implode("','",$fields)."')";
1198  }
1199  else
1200  {
1201  $where="='".$this->escape($fields)."'";
1202  }
1203  $result=array();
1204  $ret=mssql_query($sql.$where,$this->db);
1205  if ($ret)
1206  {
1207  while($obj=mssql_fetch_object($ret))
1208  {
1209  $result[]=$obj;
1210  }
1211  }
1212  else
1213  return false;
1214 
1215  return $result;
1216  }
1217 }
DDLCreateTable($table, $fields, $primary_key, $type, $unique_keys=null, $fulltext_keys=null, $keys=null)
Create a table into database.
const VERSIONMIN
Version min database.
Definition: mssql.class.php:43
decrypt($value)
Decrypt sensitive data in database.
lastqueryerror()
Return last query in error.
getDefaultCharacterSetDatabase()
Return charset used to store data in database.
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:1053
getVersion()
Return version of database server.
close()
Close database connexion.
DDLDropTable($table)
Drop a table into database.
commit($log='')
Validate a database transaction.
$forcecharset
Charset used to force charset when creating database.
Definition: mssql.class.php:39
rollback($log='')
Annulation d&#39;une transaction et retour aux anciennes valeurs.
num_rows($resultset)
Return number of lines for result of a SELECT.
lastquery()
Return last request executed with query()
$forcecollate
Collate used to force collate when creating database.
Definition: mssql.class.php:41
getPathOfRestore()
Return full path of restore program.
fetch_object($resultset)
Renvoie la ligne courante (comme un objet) pour le curseur resultset.
getServerParametersValues($filter='')
Return value of server parameters.
DDLGetConnectId()
Return connexion ID.
Classe de gestion de la database de dolibarr.
Definition: mssql.class.php:32
Class to manage Dolibarr database access.
fetch_array($resultset)
Return datas as an array.
errno()
Return generic error code of last operation.
DDLUpdateField($table, $field_name, $field_desc)
Update format of a field into a table.
$type
Database type.
Definition: mssql.class.php:35
DDLCreateDb($database, $charset='', $collation='', $owner='')
Create a new database Do not use function xxx_create_db (xxx=mysql, ...) as they are deprecated We fo...
DDLListTables($database, $table='')
List tables into a database.
lasterror()
Return last error label.
static convertSQLFromMysql($line, $type='ddl')
Convert a SQL request in Mysql syntax to native syntax.
encrypt($fieldorvalue, $withQuotes=0)
Encrypt sensitive data in database Warning: This function includes the escape, so it must use direct ...
getDefaultCollationDatabase()
Return collation used in database.
query($query, $usesavepoint=0, $type='auto')
Execute a SQL request and return the resultset.
getListOfCollation()
Return list of available collation that can be used for database.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='')
Write log message into outputs.
free($resultset=null)
Free last resultset used.
getDriverInfo()
Return version of database client driver.
affected_rows($resultset)
Renvoie le nombre de lignes dans le resultat d&#39;une requete INSERT, DELETE ou UPDATE.
error()
Return description of last error.
DDLInfoTable($table)
List information of columns into a table.
DDLAddField($table, $field_name, $field_desc, $field_position="")
Create a new field into table.
DDLDropField($table, $field_name)
Drop a field from table.
getServerStatusValues($filter='')
Return value of server status.
__construct($type, $host, $user, $pass, $name='', $port=0)
Constructor.
Definition: mssql.class.php:58
getPathOfDump()
Return full path of dump program.
getListOfCharacterSet()
Return list of available charset that can be used to store data in database.
escape($stringtoencode)
Escape a string to insert data.
begin()
Start transaction.
GetFieldInformation($table, $fields)
Get information on field.
dol_print_date($time, $format='', $tzoutput='tzserver', $outputlangs='', $encodetooutput=false)
Output date in a string format according to outputlangs (or langs if not defined).
DDLDescTable($table, $field="")
Return a pointer of line with description of a table or field.
connect($host, $login, $passwd, $name, $port=0)
Connexion to server.
idate($param)
Convert (by PHP) a GM Timestamp date into a PHP server TZ to insert into a date field.
select_db($database)
Select a database.
last_insert_id($tab, $fieldid='rowid')
Get last ID after an insert INSERT.
const LABEL
Database label.
Definition: mssql.class.php:37
fetch_row($resultset)
Return datas as an array.
lasterrno()
Return last error code.
EscapeFieldName($fieldname)
Escape a field name according to escape&#39;s syntax.
DDLCreateUser($dolibarr_main_db_host, $dolibarr_main_db_user, $dolibarr_main_db_pass, $dolibarr_main_db_name)
Create a user and privileges to connect to database (even if database does not exists yet) ...