PageRenderTime 60ms CodeModel.GetById 30ms RepoModel.GetById 0ms app.codeStats 0ms

/include/database/MysqlManager.php

https://github.com/mikmagic/sugarcrm_dev
PHP | 558 lines | 328 code | 69 blank | 161 comment | 69 complexity | 2fb21197c234df9ffca41ff9ab486a8a MD5 | raw file
Possible License(s): MPL-2.0-no-copyleft-exception, LGPL-2.1, BSD-3-Clause, AGPL-3.0
  1. <?php
  2. if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
  3. /*********************************************************************************
  4. * SugarCRM Community Edition is a customer relationship management program developed by
  5. * SugarCRM, Inc. Copyright (C) 2004-2011 SugarCRM Inc.
  6. *
  7. * This program is free software; you can redistribute it and/or modify it under
  8. * the terms of the GNU Affero General Public License version 3 as published by the
  9. * Free Software Foundation with the addition of the following permission added
  10. * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
  11. * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
  12. * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
  13. *
  14. * This program is distributed in the hope that it will be useful, but WITHOUT
  15. * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
  16. * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
  17. * details.
  18. *
  19. * You should have received a copy of the GNU Affero General Public License along with
  20. * this program; if not, see http://www.gnu.org/licenses or write to the Free
  21. * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
  22. * 02110-1301 USA.
  23. *
  24. * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
  25. * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
  26. *
  27. * The interactive user interfaces in modified source and object code versions
  28. * of this program must display Appropriate Legal Notices, as required under
  29. * Section 5 of the GNU Affero General Public License version 3.
  30. *
  31. * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
  32. * these Appropriate Legal Notices must retain the display of the "Powered by
  33. * SugarCRM" logo. If the display of the logo is not reasonably feasible for
  34. * technical reasons, the Appropriate Legal Notices must display the words
  35. * "Powered by SugarCRM".
  36. ********************************************************************************/
  37. /*********************************************************************************
  38. * Description: This file handles the Data base functionality for the application.
  39. * It acts as the DB abstraction layer for the application. It depends on helper classes
  40. * which generate the necessary SQL. This sql is then passed to PEAR DB classes.
  41. * The helper class is chosen in DBManagerFactory, which is driven by 'db_type' in 'dbconfig' under config.php.
  42. *
  43. * All the functions in this class will work with any bean which implements the meta interface.
  44. * The passed bean is passed to helper class which uses these functions to generate correct sql.
  45. *
  46. * The meta interface has the following functions:
  47. * getTableName() Returns table name of the object.
  48. * getFieldDefinitions() Returns a collection of field definitions in order.
  49. * getFieldDefintion(name) Return field definition for the field.
  50. * getFieldValue(name) Returns the value of the field identified by name.
  51. * If the field is not set, the function will return boolean FALSE.
  52. * getPrimaryFieldDefinition() Returns the field definition for primary key
  53. *
  54. * The field definition is an array with the following keys:
  55. *
  56. * name This represents name of the field. This is a required field.
  57. * type This represents type of the field. This is a required field and valid values are:
  58. * � int
  59. * � long
  60. * � varchar
  61. * � text
  62. * � date
  63. * � datetime
  64. * � double
  65. * � float
  66. * � uint
  67. * � ulong
  68. * � time
  69. * � short
  70. * � enum
  71. * length This is used only when the type is varchar and denotes the length of the string.
  72. * The max value is 255.
  73. * enumvals This is a list of valid values for an enum separated by "|".
  74. * It is used only if the type is �enum�;
  75. * required This field dictates whether it is a required value.
  76. * The default value is �FALSE�.
  77. * isPrimary This field identifies the primary key of the table.
  78. * If none of the fields have this flag set to �TRUE�,
  79. * the first field definition is assume to be the primary key.
  80. * Default value for this field is �FALSE�.
  81. * default This field sets the default value for the field definition.
  82. *
  83. *
  84. * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
  85. * All Rights Reserved.
  86. * Contributor(s): ______________________________________..
  87. ********************************************************************************/
  88. //Technically we can port all the functions in the latest bean to this file
  89. // that is what PEAR is doing anyways.
  90. class MysqlManager extends DBManager
  91. {
  92. /**
  93. * @see DBManager::$dbType
  94. */
  95. public $dbType = 'mysql';
  96. /**
  97. * @see DBManager::$backendFunctions
  98. */
  99. protected $backendFunctions = array(
  100. 'free_result' => 'mysql_free_result',
  101. 'close' => 'mysql_close',
  102. 'row_count' => 'mysql_num_rows',
  103. 'affected_row_count' => 'mysql_affected_rows',
  104. );
  105. /**
  106. * @see DBManager::checkError()
  107. */
  108. public function checkError(
  109. $msg = '',
  110. $dieOnError = false
  111. )
  112. {
  113. if (parent::checkError($msg, $dieOnError))
  114. return true;
  115. if (mysql_errno($this->getDatabase())) {
  116. if ($this->dieOnError || $dieOnError){
  117. $GLOBALS['log']->fatal("MySQL error ".mysql_errno($this->database).": ".mysql_error($this->database));
  118. sugar_die ($GLOBALS['app_strings']['ERR_DB_FAIL']);
  119. }
  120. else {
  121. $this->last_error = $msg."MySQL error ".mysql_errno($this->database).": ".mysql_error($this->database);
  122. $GLOBALS['log']->error("MySQL error ".mysql_errno($this->database).": ".mysql_error($this->database));
  123. }
  124. return true;
  125. }
  126. return false;
  127. }
  128. /**
  129. * Parses and runs queries
  130. *
  131. * @param string $sql SQL Statement to execute
  132. * @param bool $dieOnError True if we want to call die if the query returns errors
  133. * @param string $msg Message to log if error occurs
  134. * @param bool $suppress Flag to suppress all error output unless in debug logging mode.
  135. * @param bool $autofree True if we want to push this result into the $lastResult array.
  136. * @return resource result set
  137. */
  138. public function query(
  139. $sql,
  140. $dieOnError = false,
  141. $msg = '',
  142. $suppress = false,
  143. $autofree = false
  144. )
  145. {
  146. parent::countQuery($sql);
  147. $GLOBALS['log']->info('Query:' . $sql);
  148. $this->checkConnection();
  149. //$this->freeResult();
  150. $this->query_time = microtime(true);
  151. $this->lastsql = $sql;
  152. if ($suppress==true) {
  153. }
  154. else {
  155. $result = mysql_query($sql, $this->database);
  156. }
  157. $this->lastmysqlrow = -1;
  158. $this->query_time = microtime(true) - $this->query_time;
  159. $GLOBALS['log']->info('Query Execution Time:'.$this->query_time);
  160. $this->checkError($msg.' Query Failed:' . $sql . '::', $dieOnError);
  161. if($autofree)
  162. $this->lastResult[] =& $result;
  163. return $result;
  164. }
  165. /**
  166. * @see DBManager::limitQuery()
  167. */
  168. public function limitQuery(
  169. $sql,
  170. $start,
  171. $count,
  172. $dieOnError = false,
  173. $msg = '')
  174. {
  175. if ($start < 0)
  176. $start = 0;
  177. $GLOBALS['log']->debug('Limit Query:' . $sql. ' Start: ' .$start . ' count: ' . $count);
  178. $sql = "$sql LIMIT $start,$count";
  179. $this->lastsql = $sql;
  180. if(!empty($GLOBALS['sugar_config']['check_query'])){
  181. $this->checkQuery($sql);
  182. }
  183. return $this->query($sql, $dieOnError, $msg);
  184. }
  185. /**
  186. * @see DBManager::checkQuery()
  187. */
  188. protected function checkQuery(
  189. $sql
  190. )
  191. {
  192. $result = $this->query('EXPLAIN ' . $sql);
  193. $badQuery = array();
  194. while ($row = $this->fetchByAssoc($result)) {
  195. if (empty($row['table']))
  196. continue;
  197. $badQuery[$row['table']] = '';
  198. if (strtoupper($row['type']) == 'ALL')
  199. $badQuery[$row['table']] .= ' Full Table Scan;';
  200. if (empty($row['key']))
  201. $badQuery[$row['table']] .= ' No Index Key Used;';
  202. if (!empty($row['Extra']) && substr_count($row['Extra'], 'Using filesort') > 0)
  203. $badQuery[$row['table']] .= ' Using FileSort;';
  204. if (!empty($row['Extra']) && substr_count($row['Extra'], 'Using temporary') > 0)
  205. $badQuery[$row['table']] .= ' Using Temporary Table;';
  206. }
  207. if ( empty($badQuery) )
  208. return true;
  209. foreach($badQuery as $table=>$data ){
  210. if(!empty($data)){
  211. $warning = ' Table:' . $table . ' Data:' . $data;
  212. if(!empty($GLOBALS['sugar_config']['check_query_log'])){
  213. $GLOBALS['log']->fatal($sql);
  214. $GLOBALS['log']->fatal('CHECK QUERY:' .$warning);
  215. }
  216. else{
  217. $GLOBALS['log']->warn('CHECK QUERY:' .$warning);
  218. }
  219. }
  220. }
  221. return false;
  222. }
  223. /**
  224. * @see DBManager::describeField()
  225. */
  226. protected function describeField(
  227. $name,
  228. $tablename
  229. )
  230. {
  231. global $table_descriptions;
  232. if(isset($table_descriptions[$tablename])
  233. && isset($table_descriptions[$tablename][$name]))
  234. return $table_descriptions[$tablename][$name];
  235. $table_descriptions[$tablename] = array();
  236. $sql = "DESCRIBE $tablename";
  237. $result = $this->query($sql);
  238. while ($row = $this->fetchByAssoc($result) ){
  239. $table_descriptions[$tablename][$row['Field']] = $row;
  240. if(empty($table_descriptions[$tablename][$row['Field']]['Null']))
  241. $table_descriptions[$tablename][$row['Field']]['Null'] = 'NO';
  242. }
  243. if(isset($table_descriptions[$tablename][$name]))
  244. return $table_descriptions[$tablename][$name];
  245. return array();
  246. }
  247. /**
  248. * @see DBManager::getFieldsArray()
  249. */
  250. public function getFieldsArray(
  251. &$result,
  252. $make_lower_case=false)
  253. {
  254. $field_array = array();
  255. if(! isset($result) || empty($result))
  256. return 0;
  257. $i = 0;
  258. while ($i < mysql_num_fields($result)) {
  259. $meta = mysql_fetch_field($result, $i);
  260. if (!$meta)
  261. return 0;
  262. if($make_lower_case == true)
  263. $meta->name = strtolower($meta->name);
  264. $field_array[] = $meta->name;
  265. $i++;
  266. }
  267. return $field_array;
  268. }
  269. /**
  270. * @see DBManager::fetchByAssoc()
  271. */
  272. public function fetchByAssoc(
  273. &$result,
  274. $rowNum = -1,
  275. $encode = true
  276. )
  277. {
  278. if (!$result)
  279. return false;
  280. if ($result && $rowNum > -1){
  281. if ($this->getRowCount($result) > $rowNum)
  282. mysql_data_seek($result, $rowNum);
  283. $this->lastmysqlrow = $rowNum;
  284. }
  285. $row = mysql_fetch_assoc($result);
  286. if ($encode && $this->encode && is_array($row))
  287. return array_map('to_html', $row);
  288. return $row;
  289. }
  290. /**
  291. * @see DBManager::getTablesArray()
  292. */
  293. public function getTablesArray()
  294. {
  295. global $sugar_config;
  296. $GLOBALS['log']->debug('Fetching table list');
  297. if ($this->getDatabase()) {
  298. $tables = array();
  299. $r = $this->query('SHOW TABLES');
  300. if (is_resource($r) || $r instanceOf mysqli_result ) {
  301. while ($a = $this->fetchByAssoc($r)) {
  302. $row = array_values($a);
  303. $tables[]=$row[0];
  304. }
  305. return $tables;
  306. }
  307. }
  308. return false; // no database available
  309. }
  310. /**
  311. * @see DBManager::version()
  312. */
  313. public function version()
  314. {
  315. return $this->getOne("SELECT version() version");
  316. }
  317. /**
  318. * @see DBManager::tableExists()
  319. */
  320. public function tableExists(
  321. $tableName
  322. )
  323. {
  324. $GLOBALS['log']->info("tableExists: $tableName");
  325. if ($this->getDatabase()) {
  326. $result = $this->query("SHOW TABLES LIKE '".$tableName."'");
  327. return ($this->getRowCount($result) == 0) ? false : true;
  328. }
  329. return false;
  330. }
  331. /**
  332. * @see DBManager::quote()
  333. */
  334. public function quote(
  335. $string,
  336. $isLike = true
  337. )
  338. {
  339. return mysql_real_escape_string(parent::quote($string), $this->getDatabase());
  340. }
  341. /**
  342. * @see DBManager::quoteForEmail()
  343. */
  344. public function quoteForEmail(
  345. $string,
  346. $isLike = true
  347. )
  348. {
  349. return mysql_real_escape_string($string, $this->getDatabase());
  350. }
  351. /**
  352. * @see DBManager::connect()
  353. */
  354. public function connect(
  355. array $configOptions = null,
  356. $dieOnError = false
  357. )
  358. {
  359. global $sugar_config;
  360. if(is_null($configOptions))
  361. $configOptions = $sugar_config['dbconfig'];
  362. if ($sugar_config['dbconfigoption']['persistent'] == true) {
  363. $this->database = @mysql_pconnect(
  364. $configOptions['db_host_name'],
  365. $configOptions['db_user_name'],
  366. $configOptions['db_password']
  367. );
  368. }
  369. if (!$this->database) {
  370. $this->database = mysql_connect(
  371. $configOptions['db_host_name'],
  372. $configOptions['db_user_name'],
  373. $configOptions['db_password']
  374. );
  375. if(empty($this->database)) {
  376. $GLOBALS['log']->fatal("Could not connect to server ".$configOptions['db_host_name']." as ".$configOptions['db_user_name'].":".mysql_error());
  377. sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
  378. }
  379. // Do not pass connection information because we have not connected yet
  380. if($this->database && $sugar_config['dbconfigoption']['persistent'] == true){
  381. $_SESSION['administrator_error'] = "<b>Severe Performance Degradation: Persistent Database Connections "
  382. . "not working. Please set \$sugar_config['dbconfigoption']['persistent'] to false "
  383. . "in your config.php file</b>";
  384. }
  385. }
  386. if(!@mysql_select_db($configOptions['db_name'])) {
  387. $GLOBALS['log']->fatal( "Unable to select database {$configOptions['db_name']}: " . mysql_error($this->database));
  388. sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
  389. }
  390. // cn: using direct calls to prevent this from spamming the Logs
  391. $charset = "SET CHARACTER SET utf8";
  392. if(isset($sugar_config['dbconfigoption']['collation']) && !empty($sugar_config['dbconfigoption']['collation']))
  393. $charset .= " COLLATE {$sugar_config['dbconfigoption']['collation']}";
  394. mysql_query($charset, $this->database); // no quotes around "[charset]"
  395. mysql_query("SET NAMES 'utf8'", $this->database);
  396. if($this->checkError('Could Not Connect:', $dieOnError))
  397. $GLOBALS['log']->info("connected to db");
  398. $GLOBALS['log']->info("Connect:".$this->database);
  399. }
  400. /**
  401. * @see DBManager::repairTableParams()
  402. *
  403. * For MySQL, we can write the ALTER TABLE statement all in one line, which speeds things
  404. * up quite a bit. So here, we'll parse the returned SQL into a single ALTER TABLE command.
  405. */
  406. public function repairTableParams(
  407. $tablename,
  408. $fielddefs,
  409. $indices,
  410. $execute = true,
  411. $engine = null
  412. )
  413. {
  414. $sql = parent::repairTableParams($tablename,$fielddefs,$indices,false,$engine);
  415. if ( $sql == '' )
  416. return '';
  417. if ( stristr($sql,'create table') )
  418. {
  419. if ($execute) {
  420. $msg = "Error creating table: ".$tablename. ":";
  421. $this->query($sql,true,$msg);
  422. }
  423. return $sql;
  424. }
  425. // first, parse out all the comments
  426. $match = array();
  427. preg_match_all("!/\*.*?\*/!is", $sql, $match);
  428. $commentBlocks = $match[0];
  429. $sql = preg_replace("!/\*.*?\*/!is",'', $sql);
  430. // now, we should only have alter table statements
  431. // let's replace the 'alter table name' part with a comma
  432. $sql = preg_replace("!alter table $tablename!is",', ', $sql);
  433. // re-add it at the beginning
  434. $sql = substr_replace($sql,'',strpos($sql,','),1);
  435. $sql = str_replace(";","",$sql);
  436. $sql = str_replace("\n","",$sql);
  437. $sql = "ALTER TABLE $tablename $sql";
  438. if ( $execute )
  439. $this->query($sql,'Error with MySQL repair table');
  440. // and re-add the comments at the beginning
  441. $sql = implode("\n",$commentBlocks) . "\n". $sql . "\n";
  442. return $sql;
  443. }
  444. /**
  445. * @see DBManager::convert()
  446. */
  447. public function convert(
  448. $string,
  449. $type,
  450. array $additional_parameters = array(),
  451. array $additional_parameters_oracle_only = array()
  452. )
  453. {
  454. // convert the parameters array into a comma delimited string
  455. $additional_parameters_string = '';
  456. if (!empty($additional_parameters))
  457. $additional_parameters_string = ','.implode(',',$additional_parameters);
  458. switch ($type) {
  459. case 'today': return "CURDATE()";
  460. case 'left': return "LEFT($string".$additional_parameters_string.")";
  461. case 'date_format': return "DATE_FORMAT($string".$additional_parameters_string.")";
  462. case 'datetime': return "DATE_FORMAT($string, '%Y-%m-%d %H:%i:%s')";
  463. case 'IFNULL': return "IFNULL($string".$additional_parameters_string.")";
  464. case 'CONCAT': return "CONCAT($string,".implode(",",$additional_parameters).")";
  465. case 'text2char': return "$string";
  466. }
  467. return "$string";
  468. }
  469. /**
  470. * @see DBManager::concat()
  471. */
  472. public function concat(
  473. $table,
  474. array $fields
  475. )
  476. {
  477. $ret = '';
  478. foreach ( $fields as $index => $field )
  479. if (empty($ret))
  480. $ret = "CONCAT(". db_convert($table.".".$field,'IFNULL', array("''"));
  481. else
  482. $ret.= ",' ',".db_convert($table.".".$field,'IFNULL', array("''"));
  483. if (!empty($ret)) {
  484. $ret = "TRIM($ret))";
  485. }
  486. return $ret;
  487. }
  488. }