dolibarr  7.0.0-beta
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@capnetworks.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 
148  function select_db($database)
149  {
150  return @mssql_select_db($database, $this->db);
151  }
152 
164  function connect($host, $login, $passwd, $name, $port=0)
165  {
166  dol_syslog(get_class($this)."::connect host=$host, port=$port, login=$login, passwd=--hidden--, name=$name");
167  $newhost=$host;
168  if ($port) $newhost.=':'.$port;
169  $this->db = @mssql_connect($newhost, $login, $passwd);
170  //force les enregistrement en latin1 si la base est en utf8 par defaut
171  // Supprime car plante sur mon PHP-Mysql. De plus, la base est forcement en latin1 avec
172  // les nouvelles version de Dolibarr car force par l'install Dolibarr.
173  //$this->query('SET NAMES '.$this->forcecharset);
174  //print "Resultat fonction connect: ".$this->db;
175  $set_options=array('SET ANSI_PADDING ON;',
176  "SET ANSI_NULLS ON;",
177  "SET ANSI_WARNINGS ON;",
178  "SET ARITHABORT ON;",
179  "SET CONCAT_NULL_YIELDS_NULL ON;",
180  "SET QUOTED_IDENTIFIER ON;"
181  );
182  mssql_query(implode(' ',$set_options),$this->db);
183 
184  return $this->db;
185  }
186 
192  function getVersion()
193  {
194  $resql=$this->query("SELECT @@VERSION");
195  if ($resql)
196  {
197  $version=$this->fetch_array($resql);
198  return $version['computed'];
199  }
200  else return '';
201  }
202 
208  function getDriverInfo()
209  {
210  return 'php mssql driver';
211  }
212 
219  function close()
220  {
221  if ($this->db)
222  {
223  if ($this->transaction_opened > 0) dol_syslog(get_class($this)."::close Closing a connection with an opened transaction depth=".$this->transaction_opened,LOG_ERR);
224  $this->connected=false;
225  return mssql_close($this->db);
226  }
227  return false;
228  }
229 
230 
236  function begin()
237  {
238 
239  $res=mssql_query('select @@TRANCOUNT');
240  $this->transaction_opened=mssql_result($res, 0, 0);
241 
242  if ($this->transaction_opened == 0)
243  {
244  //return 1; //There is a mess with auto_commit and 'SET IMPLICIT_TRANSACTIONS ON' generate also a mess
245  $ret=mssql_query("SET IMPLICIT_TRANSACTIONS OFF;BEGIN TRANSACTION;",$this->db);
246  if ($ret)
247  {
248  dol_syslog("BEGIN Transaction",LOG_DEBUG);
249  }
250  return $ret;
251  }
252  else
253  {
254  return true;
255  }
256  }
257 
264  function commit($log='')
265  {
266  $res=mssql_query('select @@TRANCOUNT');
267  $this->transaction_opened=mssql_result($res, 0, 0);
268 
269  if ($this->transaction_opened == 1)
270  {
271  //return 1; //There is a mess with auto_commit and 'SET IMPLICIT_TRANSACTION ON' generate also a mess
272  $ret=mssql_query("COMMIT TRANSACTION",$this->db);
273  if ($ret)
274  {
275  dol_syslog("COMMIT Transaction",LOG_DEBUG);
276  return true;
277  }
278  else
279  {
280  return false;
281  }
282  }
283  elseif ($this->transaction_opened > 1)
284  {
285  return true;
286  }
287  trigger_error("Commit requested but no transaction remain");
288  return false;
289  }
290 
297  function rollback($log='')
298  {
299  $res=mssql_query('select @@TRANCOUNT');
300  $this->transaction_opened=mssql_result($res, 0, 0);
301 
302  if ($this->transaction_opened == 1)
303  {
304  $ret=mssql_query("ROLLBACK TRANSACTION",$this->db);
305  dol_syslog("ROLLBACK Transaction".($log?' '.$log:''),LOG_DEBUG);
306  return $ret;
307  }
308  elseif ($this->transaction_opened > 1)
309  {
310  return true;
311  }
312  trigger_error("Rollback requested but no transaction remain");
313  return false;
314  }
315 
325  function query($query,$usesavepoint=0,$type='auto')
326  {
327  $query = trim($query);
328 
329  if (preg_match('/^--/',$query)) return true;
330 
331  // Conversion syntaxe MySql vers MSDE.
332  $query = str_ireplace("now()", "getdate()", $query);
333  // Erreur SQL: cannot update timestamp field
334  $query = str_ireplace(", tms = tms", "", $query);
335 
336  $query=preg_replace("/([. ,\t(])(percent|file|public)([. ,=\t)])/","$1[$2]$3",$query);
337 
338  if ($type=="auto" || $type='dml')
339  {
340  $query=preg_replace('/AUTO_INCREMENT/i','IDENTITY',$query);
341  $query=preg_replace('/double/i','float',$query);
342  $query=preg_replace('/float\((.*)\)/','numeric($1)',$query);
343  $query=preg_replace('/([ \t])unsigned|IF NOT EXISTS[ \t]/i','$1',$query);
344  $query=preg_replace('/([ \t])(MEDIUM|TINY|LONG){0,1}TEXT([ \t,])/i',"$1VARCHAR(MAX)$3",$query);
345 
346  $matches=array();
347  $original_query='';
348  if (preg_match('/ALTER TABLE\h+(\w+?)\h+ADD\h+(?:(UNIQUE)|INDEX)\h+(?:INDEX)?\h*(\w+?)\h*\((.+)\)/is', $query,$matches))
349  {
350  $original_query=$query;
351  $query="CREATE ".trim($matches[2])." INDEX [".trim($matches[3])."] ON [".trim($matches[1])."] (".trim($matches[4]).")";
352  if ($matches[2]) {
353  //check if columun is nullable cause Sql server only allow 1 null value if unique index.
354  $fields=explode(",",trim($matches[4]));
355  $fields_clear=array_map('trim',$fields);
356  $infos=$this->GetFieldInformation(trim($matches[1]), $fields_clear);
357  $query_comp=array();
358  foreach($infos as $fld) {
359  if ($fld->IS_NULLABLE == 'YES') {
360  $query_comp[]=$fld->COLUMN_NAME." IS NOT NULL";
361  }
362  }
363  if (! empty($query_comp))
364  $query.=" WHERE ".implode(" AND ",$query_comp);
365  }
366  }
367  else
368  {
369  if (preg_match('/ALTER TABLE\h+(\w+?)\h+ADD\h+PRIMARY\h+KEY\h+(\w+?)\h*\((.+)\)/is', $query, $matches))
370  {
371  $original_query=$query;
372  $query="ALTER TABLE [".$matches[1]."] ADD CONSTRAINT [".$matches[2]."] PRIMARY KEY CLUSTERED (".$matches[3].")";
373  }
374  }
375 
376  }
377 
378  if ($type=="auto" || $type='ddl')
379  {
380  $itemfound = stripos($query, " limit ");
381  if ($itemfound !== false) {
382  // Extraire le nombre limite
383  $number = stristr($query, " limit ");
384  $number = substr($number, 7);
385  // Inserer l'instruction TOP et le nombre limite
386  $query = str_ireplace("select ", "select top ".$number." ", $query);
387  // Supprimer l'instruction MySql
388  $query = str_ireplace(" limit ".$number, "", $query);
389  }
390 
391  $itemfound = stripos($query, " week(");
392  if ($itemfound !== false) {
393  // Recreer une requete sans instruction Mysql
394  $positionMySql = stripos($query, " week(");
395  $newquery = substr($query, 0, $positionMySql);
396 
397  // Recuperer la date passee en parametre
398  $extractvalue = stristr($query, " week(");
399  $extractvalue = substr($extractvalue, 6);
400  $positionMySql = stripos($extractvalue, ")");
401  // Conserver la fin de la requete
402  $endofquery = substr($extractvalue, $positionMySql);
403  $extractvalue = substr($extractvalue, 0, $positionMySql);
404 
405  // Remplacer l'instruction MySql en Sql Server
406  // Inserer la date en parametre et le reste de la requete
407  $query = $newquery." DATEPART(week, ".$extractvalue.$endofquery;
408  }
409  if (preg_match('/^insert\h+(?:INTO)?\h*(\w+?)\h*\(.*\b(?:row)?id\b.*\)\h+VALUES/i',$query,$matches))
410  {
411  //var_dump($query);
412  //var_dump($matches);
413  //if (stripos($query,'llx_c_departements') !== false) var_dump($query);
414  $sql='SET IDENTITY_INSERT ['.trim($matches[1]).'] ON;';
415  @mssql_query($sql, $this->db);
416  $post_query='SET IDENTITY_INSERT ['.trim($matches[1]).'] OFF;';
417 
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 
468  function fetch_object($resultset)
469  {
470  // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion
471  if (! is_resource($resultset)) { $resultset=$this->_results; }
472  return mssql_fetch_object($resultset);
473  }
474 
481  function fetch_array($resultset)
482  {
483  // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion
484  if (! is_resource($resultset)) { $resultset=$this->_results; }
485  return mssql_fetch_array($resultset);
486  }
487 
488 
495  function fetch_row($resultset)
496  {
497  // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion
498  if (! is_resource($resultset)) { $resultset=$this->_results; }
499  return @mssql_fetch_row($resultset);
500  }
501 
509  function num_rows($resultset)
510  {
511  // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion
512  if (! is_resource($resultset)) { $resultset=$this->_results; }
513  return mssql_num_rows($resultset);
514  }
515 
523  function affected_rows($resultset)
524  {
525  // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion
526  if (! is_resource($resultset)) { $resultset=$this->_results; }
527  // mssql necessite un link de base pour cette fonction contrairement
528  // a pqsql qui prend un resultset
529  $rsRows = mssql_query("select @@rowcount as rows", $this->db);
530  return mssql_result($rsRows, 0, "rows");
531  //return mssql_affected_rows($this->db);
532  }
533 
534 
541  function free($resultset=null)
542  {
543  // Si le resultset n'est pas fourni, on prend le dernier utilise sur cette connexion
544  if (! is_resource($resultset)) { $resultset=$this->_results; }
545  // Si resultset en est un, on libere la memoire
546  if (is_resource($resultset)) mssql_free_result($resultset);
547  }
548 
555  function escape($stringtoencode)
556  {
557  return addslashes($stringtoencode);
558  }
559 
560 
568  function idate($param)
569  {
570  return dol_print_date($param,"%Y-%m-%d %H:%M:%S");
571  }
572 
578  function errno()
579  {
580  if (! $this->connected)
581  {
582  // Si il y a eu echec de connexion, $this->db n'est pas valide.
583  return 'DB_ERROR_FAILED_TO_CONNECT';
584  }
585  else
586  {
587  // Constants to convert a MSSql error code to a generic Dolibarr error code
588  $errorcode_map = array(
589  1004 => 'DB_ERROR_CANNOT_CREATE',
590  1005 => 'DB_ERROR_CANNOT_CREATE',
591  1006 => 'DB_ERROR_CANNOT_CREATE',
592  1007 => 'DB_ERROR_ALREADY_EXISTS',
593  1008 => 'DB_ERROR_CANNOT_DROP',
594  1025 => 'DB_ERROR_NO_FOREIGN_KEY_TO_DROP',
595  1046 => 'DB_ERROR_NODBSELECTED',
596  1048 => 'DB_ERROR_CONSTRAINT',
597  2714 => 'DB_ERROR_TABLE_ALREADY_EXISTS',
598  1051 => 'DB_ERROR_NOSUCHTABLE',
599  1054 => 'DB_ERROR_NOSUCHFIELD',
600  1060 => 'DB_ERROR_COLUMN_ALREADY_EXISTS',
601  1061 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS',
602  2627 => 'DB_ERROR_RECORD_ALREADY_EXISTS',
603  102 => 'DB_ERROR_SYNTAX',
604  8120 => 'DB_ERROR_GROUP_BY_SYNTAX',
605  1068 => 'DB_ERROR_PRIMARY_KEY_ALREADY_EXISTS',
606  1075 => 'DB_ERROR_CANT_DROP_PRIMARY_KEY',
607  1091 => 'DB_ERROR_NOSUCHFIELD',
608  1100 => 'DB_ERROR_NOT_LOCKED',
609  1136 => 'DB_ERROR_VALUE_COUNT_ON_ROW',
610  1146 => 'DB_ERROR_NOSUCHTABLE',
611  1216 => 'DB_ERROR_NO_PARENT',
612  1217 => 'DB_ERROR_CHILD_EXISTS',
613  1451 => 'DB_ERROR_CHILD_EXISTS',
614  1913 => 'DB_ERROR_KEY_NAME_ALREADY_EXISTS'
615  );
616 
617  if (isset($errorcode_map[$this->lasterrno]))
618  {
619  return $errorcode_map[$this->lasterrno];
620  }
621  $errno=$this->lasterrno;
622  return ($errno?'DB_ERROR_'.$errno:'0');
623  }
624  }
625 
631  function error()
632  {
633  if (! $this->connected) {
634  // Si il y a eu echec de connexion, $this->db n'est pas valide pour mssql_get_last_message.
635  return 'Not connected. Check setup parameters in conf/conf.php file and your mssql client and server versions';
636  }
637  else {
638  return mssql_get_last_message();
639  }
640  }
641 
649  function last_insert_id($tab,$fieldid='rowid')
650  {
651  $res = $this->query("SELECT @@IDENTITY as id");
652  if ($res && $data = $this->fetch_array($res))
653  {
654  return $data["id"];
655  }
656  else
657  {
658  return -1;
659  }
660  }
661 
670  function encrypt($fieldorvalue, $withQuotes=0)
671  {
672  global $conf;
673 
674  // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption)
675  $cryptType = ($conf->db->dolibarr_main_db_encryption?$conf->db->dolibarr_main_db_encryption:0);
676 
677  //Encryption key
678  $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey)?$conf->db->dolibarr_main_db_cryptkey:'');
679 
680  $return = $fieldorvalue;
681  return ($withQuotes?"'":"").$this->escape($return).($withQuotes?"'":"");
682  }
683 
690  function decrypt($value)
691  {
692  global $conf;
693 
694  // Type of encryption (2: AES (recommended), 1: DES , 0: no encryption)
695  $cryptType = ($conf->db->dolibarr_main_db_encryption?$conf->db->dolibarr_main_db_encryption:0);
696 
697  //Encryption key
698  $cryptKey = (!empty($conf->db->dolibarr_main_db_cryptkey)?$conf->db->dolibarr_main_db_cryptkey:'');
699 
700  $return = $value;
701  return $return;
702  }
703 
704 
710  function DDLGetConnectId()
711  {
712  $resql=$this->query('SELECT CONNECTION_ID()');
713  if ($resql)
714  {
715  $row=$this->fetch_row($resql);
716  return $row[0];
717  }
718  else return '?';
719  }
720 
732  function DDLCreateDb($database,$charset='',$collation='',$owner='')
733  {
734  /*if (empty($charset)) $charset=$this->forcecharset;
735  if (empty($collation)) $collation=$this->forcecollate;
736  */
737 
738  $sql = 'CREATE DATABASE '.$this->EscapeFieldName($database);
739  //TODO: Check if we need to force a charset
740  //$sql.= ' DEFAULT CHARACTER SET '.$charset.' DEFAULT COLLATE '.$collation;
741  $ret=$this->query($sql);
742 
743  $this->select_db($database);
744  $sql="CREATE USER [$owner] FOR LOGIN [$owner]";
745  mssql_query($sql,$this->db);
746  $sql="ALTER ROLE [db_owner] ADD MEMBER [$owner]";
747  mssql_query($sql,$this->db);
748 
749  $sql="ALTER DATABASE [$database] SET ANSI_NULL_DEFAULT ON;";
750  @mssql_query($sql,$this->db);
751  $sql="ALTER DATABASE [$database] SET ANSI_NULL ON;";
752  @mssql_query($sql,$this->db);
753 
754  return $ret;
755  }
756 
764  function DDLListTables($database,$table='')
765  {
766  $this->_results = mssql_list_tables($database, $this->db);
767  return $this->_results;
768  }
769 
776  function DDLInfoTable($table)
777  {
778 
779  // FIXME: Dummy method
780  // TODO: Implement
781  // May help: https://stackoverflow.com/questions/600446/sql-server-how-do-you-return-the-column-names-from-a-table
782 
783  $infotables=array();
784  return $infotables;
785  }
786 
799  function DDLCreateTable($table,$fields,$primary_key,$type,$unique_keys=null,$fulltext_keys=null,$keys=null)
800  {
801  // FIXME: $fulltext_keys parameter is unused
802 
803  // cles recherchees dans le tableau des descriptions (fields) : type,value,attribute,null,default,extra
804  // ex. : $fields['rowid'] = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment');
805  $sql = "create table ".$table."(";
806  $i=0;
807  foreach($fields as $field_name => $field_desc)
808  {
809  $sqlfields[$i] = $field_name." ";
810  $sqlfields[$i] .= $field_desc['type'];
811  if( preg_match("/^[^\s]/i",$field_desc['value']))
812  $sqlfields[$i] .= "(".$field_desc['value'].")";
813  else if( preg_match("/^[^\s]/i",$field_desc['attribute']))
814  $sqlfields[$i] .= " ".$field_desc['attribute'];
815  else if( preg_match("/^[^\s]/i",$field_desc['default']))
816  {
817  if(preg_match("/null/i",$field_desc['default']))
818  $sqlfields[$i] .= " default ".$field_desc['default'];
819  else
820  $sqlfields[$i] .= " default '".$field_desc['default']."'";
821  }
822  else if( preg_match("/^[^\s]/i",$field_desc['null']))
823  $sqlfields[$i] .= " ".$field_desc['null'];
824 
825  else if( preg_match("/^[^\s]/i",$field_desc['extra']))
826  $sqlfields[$i] .= " ".$field_desc['extra'];
827  $i++;
828  }
829  if($primary_key != "")
830  $pk = "primary key(".$primary_key.")";
831 
832  if(is_array($unique_keys))
833  {
834  $i = 0;
835  foreach($unique_keys as $key => $value)
836  {
837  $sqluq[$i] = "UNIQUE KEY '".$key."' ('".$value."')";
838  $i++;
839  }
840  }
841  if(is_array($keys))
842  {
843  $i = 0;
844  foreach($keys as $key => $value)
845  {
846  $sqlk[$i] = "KEY ".$key." (".$value.")";
847  $i++;
848  }
849  }
850  $sql .= implode(',',$sqlfields);
851  if($primary_key != "")
852  $sql .= ",".$pk;
853  if(is_array($unique_keys))
854  $sql .= ",".implode(',',$sqluq);
855  if(is_array($keys))
856  $sql .= ",".implode(',',$sqlk);
857  $sql .=") type=".$type;
858 
859  dol_syslog($sql);
860  if(! $this -> query($sql))
861  return -1;
862  else
863  return 1;
864  }
865 
872  function DDLDropTable($table)
873  {
874  $sql = "DROP TABLE ".$table;
875 
876  if (! $this->query($sql))
877  return -1;
878  else
879  return 1;
880  }
881 
889  function DDLDescTable($table,$field="")
890  {
891  $sql="DESC ".$table." ".$field;
892 
893  dol_syslog($sql);
894  $this->_results = $this->query($sql);
895  return $this->_results;
896  }
897 
907  function DDLAddField($table,$field_name,$field_desc,$field_position="")
908  {
909  // cles recherchees dans le tableau des descriptions (field_desc) : type,value,attribute,null,default,extra
910  // ex. : $field_desc = array('type'=>'int','value'=>'11','null'=>'not null','extra'=> 'auto_increment');
911  $sql= "ALTER TABLE ".$table." ADD ".$field_name." ";
912  $sql .= $field_desc['type'];
913  if( preg_match("/^[^\s]/i",$field_desc['value']))
914  $sql .= "(".$field_desc['value'].")";
915  if( preg_match("/^[^\s]/i",$field_desc['attribute']))
916  $sql .= " ".$field_desc['attribute'];
917  if( preg_match("/^[^\s]/i",$field_desc['null']))
918  $sql .= " ".$field_desc['null'];
919  if( preg_match("/^[^\s]/i",$field_desc['default']))
920  if(preg_match("/null/i",$field_desc['default']))
921  $sql .= " default ".$field_desc['default'];
922  else
923  $sql .= " default '".$field_desc['default']."'";
924  if( preg_match("/^[^\s]/i",$field_desc['extra']))
925  $sql .= " ".$field_desc['extra'];
926  $sql .= " ".$field_position;
927 
928  if(! $this -> query($sql))
929  return -1;
930  else
931  return 1;
932  }
933 
942  function DDLUpdateField($table,$field_name,$field_desc)
943  {
944  $sql = "ALTER TABLE ".$table;
945  $sql .= " MODIFY COLUMN ".$field_name." ".$field_desc['type'];
946  if ($field_desc['type'] == 'tinyint' || $field_desc['type'] == 'int' || $field_desc['type'] == 'varchar') {
947  $sql.="(".$field_desc['value'].")";
948  }
949 
950  dol_syslog($sql,LOG_DEBUG);
951  if (! $this->query($sql))
952  return -1;
953  else
954  return 1;
955  }
956 
964  function DDLDropField($table,$field_name)
965  {
966  $sql= "ALTER TABLE ".$table." DROP COLUMN `".$field_name."`";
967  dol_syslog($sql,LOG_DEBUG);
968  if (! $this->query($sql))
969  {
970  $this->error=$this->lasterror();
971  return -1;
972  }
973  else return 1;
974  }
975 
985  function DDLCreateUser($dolibarr_main_db_host,$dolibarr_main_db_user,$dolibarr_main_db_pass,$dolibarr_main_db_name)
986  {
987  $sql = "CREATE LOGIN ".$this->EscapeFieldName($dolibarr_main_db_user)." WITH PASSWORD='$dolibarr_main_db_pass'";
988  dol_syslog(get_class($this)."::DDLCreateUser", LOG_DEBUG); // No sql to avoid password in log
989  $resql=$this->query($sql);
990  if (! $resql)
991  {
992  if ($this->lasterrno != '15025')
993  {
994  return -1;
995  }
996  else
997  {
998  // If user already exists, we continue to set permissions
999  dol_syslog(get_class($this)."::DDLCreateUser sql=".$sql, LOG_WARNING);
1000  }
1001  }
1002  $sql="SELECT name from sys.databases where name='".$dolibarr_main_db_name."'";
1003  $ressql=$this->query($sql);
1004  if (! $ressql)
1005  {
1006  dol_syslog(get_class($this)."::DDLCreateUser sql=".$sql, LOG_WARNING);
1007  return -1;
1008  }
1009  else
1010  {
1011  if ($num)
1012  {
1013  $this->select_db($dolibarr_main_db_name);
1014  $sql="CREATE USER [$dolibarr_main_db_user] FOR LOGIN [$dolibarr_main_db_user]";
1015  $this->query($sql);
1016  $sql="ALTER ROLE [db_owner] ADD MEMBER [$dolibarr_main_db_user]";
1017  $this->query($sql);
1018  }
1019  }
1020  return 1;
1021  }
1022 
1029  {
1030  // FIXME: Dummy method
1031  // TODO: Implement
1032 
1033  return '';
1034  }
1035 
1042  {
1043  // FIXME: Dummy method
1044  // TODO: Implement
1045 
1046  return '';
1047  }
1048 
1055  {
1056  $resql=$this->query("SELECT SERVERPROPERTY('collation')");
1057  if (!$resql)
1058  {
1059  return $this->forcecollate;
1060  }
1061  $liste=$this->fetch_array($resql);
1062  return $liste['computed'];
1063  }
1064 
1071  {
1072  // FIXME: Dummy method
1073  // TODO: Implement
1074 
1075  return array();
1076  }
1077 
1083  function getPathOfDump()
1084  {
1085  // FIXME: Dummy method
1086  // TODO: Implement
1087 
1088  return '';
1089  }
1090 
1096  function getPathOfRestore()
1097  {
1098  // FIXME: Dummy method
1099  // TODO: Implement
1100 
1101  return '';
1102  }
1103 
1110  function getServerParametersValues($filter='')
1111  {
1112  // FIXME: Dummy method
1113  // TODO: Implement
1114  // May help: SELECT SERVERPROPERTY
1115 
1116  $result=array();
1117  return $result;
1118  }
1119 
1126  function getServerStatusValues($filter='')
1127  {
1128  // FIXME: Dummy method
1129  // TODO: Implement
1130  // May help: http://www.experts-exchange.com/Database/MS-SQL-Server/Q_20971756.html
1131 
1132  return array();
1133  }
1134 
1141  function EscapeFieldName($fieldname) {
1142  return "[".$fieldname."]";
1143  }
1144 
1145 
1153  function GetFieldInformation($table,$fields) {
1154  $sql="SELECT * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='".$this->escape($table)."' AND COLUMN_NAME";
1155  if (is_array($fields))
1156  {
1157  $where=" IN ('".implode("','",$fields)."')";
1158  }
1159  else
1160  {
1161  $where="='".$this->escape($fields)."'";
1162  }
1163  $result=array();
1164  $ret=mssql_query($sql.$where,$this->db);
1165  if ($ret)
1166  {
1167  while($obj=mssql_fetch_object($ret))
1168  {
1169  $result[]=$obj;
1170  }
1171  }
1172  else
1173  return false;
1174 
1175  return $result;
1176  }
1177 
1178 }
1179 
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.
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'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'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.
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
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'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) ...