PageRenderTime 44ms CodeModel.GetById 14ms RepoModel.GetById 0ms app.codeStats 0ms

/include/database/MysqlHelper.php

https://github.com/vincentamari/SuperSweetAdmin
PHP | 522 lines | 348 code | 42 blank | 132 comment | 85 complexity | a6866f89272d781eae540a54437f50d8 MD5 | raw file
Possible License(s): MPL-2.0-no-copyleft-exception, AGPL-3.0, LGPL-2.1
  1. <?php
  2. if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
  3. /*********************************************************************************
  4. * SugarCRM 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 specific
  39. * to oracle database. It is called by the DBManager class to generate various sql statements.
  40. *
  41. * All the functions in this class will work with any bean which implements the meta interface.
  42. * Please refer the DBManager documentation for the details.
  43. *
  44. * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
  45. * All Rights Reserved.
  46. * Contributor(s): ______________________________________..
  47. ********************************************************************************/
  48. require_once('include/database/DBHelper.php');
  49. class MysqlHelper extends DBHelper
  50. {
  51. /**
  52. * @see DBHelper::createTableSQL()
  53. */
  54. public function createTableSQL(
  55. SugarBean $bean
  56. )
  57. {
  58. $tablename = $bean->getTableName();
  59. $fieldDefs = $bean->getFieldDefinitions();
  60. $indices = $bean->getIndices();
  61. $engine = $this->getEngine($bean);
  62. return $this->createTableSQLParams($tablename, $fieldDefs, $indices, $engine);
  63. }
  64. /**
  65. * Generates sql for create table statement for a bean.
  66. *
  67. * @param string $tablename
  68. * @param array $fieldDefs
  69. * @param array $indices
  70. * @param string $engine optional, MySQL engine to use
  71. * @return string SQL Create Table statement
  72. */
  73. public function createTableSQLParams(
  74. $tablename,
  75. $fieldDefs,
  76. $indices,
  77. $engine = null
  78. )
  79. {
  80. if ( empty($engine) && isset($fieldDefs['engine']))
  81. $engine = $fieldDefs['engine'];
  82. if ( !$this->isEngineEnabled($engine) )
  83. $engine = '';
  84. $sql = parent::createTableSQLParams($tablename,$fieldDefs,$indices);
  85. if (!empty($engine))
  86. $sql.= " ENGINE=$engine";
  87. return $sql;
  88. }
  89. /**
  90. * Returns the name of the engine to use or null if we are to use the default
  91. *
  92. * @param object $bean SugarBean instance
  93. * @return string
  94. */
  95. private function getEngine($bean)
  96. {
  97. global $dictionary;
  98. $engine = null;
  99. if (isset($dictionary[$bean->getObjectName()]['engine'])) {
  100. $engine = $dictionary[$bean->getObjectName()]['engine'];
  101. }
  102. return $engine;
  103. }
  104. /**
  105. * Returns true if the engine given is enabled in the backend
  106. *
  107. * @param string $engine
  108. * @return bool
  109. */
  110. private function isEngineEnabled(
  111. $engine
  112. )
  113. {
  114. $engine = strtoupper($engine);
  115. $r = $this->db->query("SHOW ENGINES");
  116. while ( $row = $this->db->fetchByAssoc($r) )
  117. if ( strtoupper($row['Engine']) == $engine )
  118. return ($row['Support']=='YES' || $row['Support']=='DEFAULT');
  119. return false;
  120. }
  121. /**
  122. * @see DBHelper::getColumnType()
  123. */
  124. public function getColumnType(
  125. $type,
  126. $name = '',
  127. $table = ''
  128. )
  129. {
  130. $map = array(
  131. 'int' => 'int',
  132. 'double' => 'double',
  133. 'float' => 'float',
  134. 'uint' => 'int unsigned',
  135. 'ulong' => 'bigint unsigned',
  136. 'long' => 'bigint',
  137. 'short' => 'smallint',
  138. 'varchar' => 'varchar',
  139. 'text' => 'text',
  140. 'longtext' => 'longtext',
  141. 'date' => 'date',
  142. 'enum' => 'varchar',
  143. 'relate' => 'varchar',
  144. 'multienum'=> 'text',
  145. 'html' => 'text',
  146. 'datetime' => 'datetime',
  147. 'datetimecombo' => 'datetime',
  148. 'time' => 'time',
  149. 'bool' => 'bool',
  150. 'tinyint' => 'tinyint',
  151. 'char' => 'char',
  152. 'blob' => 'blob',
  153. 'longblob' => 'longblob',
  154. 'currency' => 'decimal(26,6)',
  155. 'decimal' => 'decimal',
  156. 'decimal2' => 'decimal',
  157. 'id' => 'char(36)',
  158. 'url'=>'varchar',
  159. 'encrypt'=>'varchar',
  160. 'file' => 'varchar',
  161. );
  162. return $map[$type];
  163. }
  164. /**
  165. * @see DBHelper::oneColumnSQLRep()
  166. */
  167. protected function oneColumnSQLRep(
  168. $fieldDef,
  169. $ignoreRequired = false,
  170. $table = '',
  171. $return_as_array = false
  172. )
  173. {
  174. $ref = parent::oneColumnSQLRep($fieldDef, $ignoreRequired, $table, true);
  175. if ( $ref['colType'] == 'int'
  176. && !empty($fieldDef['len']) )
  177. $ref['colType'] .= "(".$fieldDef['len'].")";
  178. // bug 22338 - don't set a default value on text or blob fields
  179. if ( isset($ref['default']) &&
  180. ($ref['colType'] == 'text' || $ref['colType'] == 'blob'
  181. || $ref['colType'] == 'longtext' || $ref['colType'] == 'longblob' ))
  182. $ref['default'] = '';
  183. if ( $return_as_array )
  184. return $ref;
  185. else
  186. return "{$ref['name']} {$ref['colType']} {$ref['default']} {$ref['required']} {$ref['auto_increment']}";
  187. }
  188. /**
  189. * @see DBHelper::changeColumnSQL()
  190. */
  191. protected function changeColumnSQL(
  192. $tablename,
  193. $fieldDefs,
  194. $action,
  195. $ignoreRequired = false
  196. )
  197. {
  198. if ($this->isFieldArray($fieldDefs)){
  199. foreach ($fieldDefs as $def){
  200. if ($action == 'drop')
  201. $columns[] = $def['name'];
  202. else
  203. $columns[] = $this->oneColumnSQLRep($def, $ignoreRequired);
  204. }
  205. }else{
  206. if ($action == 'drop')
  207. $columns[] = $fieldDefs['name'];
  208. else
  209. $columns[] = $this->oneColumnSQLRep($fieldDefs);
  210. }
  211. return "alter table $tablename $action column ".implode(",$action column ", $columns);
  212. }
  213. /**
  214. * @see DBHelper::deleteColumnSQL()
  215. */
  216. public function deleteColumnSQL(
  217. SugarBean $bean,
  218. $fieldDefs
  219. )
  220. {
  221. if ($this->isFieldArray($fieldDefs))
  222. foreach ($fieldDefs as $fieldDef)
  223. $columns[] = $fieldDef['name'];
  224. else
  225. $columns[] = $fieldDefs['name'];
  226. return "alter table ".$bean->getTableName()." drop column ".implode(", drop column ", $columns);
  227. }
  228. /**
  229. * @see DBHelper::keysSQL
  230. */
  231. public function keysSQL(
  232. $indices,
  233. $alter_table = false,
  234. $alter_action = ''
  235. )
  236. {
  237. // check if the passed value is an array of fields.
  238. // if not, convert it into an array
  239. if (!$this->isFieldArray($indices))
  240. $indices[] = $indices;
  241. $columns = array();
  242. foreach ($indices as $index) {
  243. if(!empty($index['db']) && $index['db'] != 'mysql')
  244. continue;
  245. if (isset($index['source']) && $index['source'] != 'db')
  246. continue;
  247. $type = $index['type'];
  248. $name = $index['name'];
  249. if (is_array($index['fields']))
  250. $fields = implode(", ", $index['fields']);
  251. else
  252. $fields = $index['fields'];
  253. switch ($type) {
  254. case 'unique':
  255. $columns[] = " UNIQUE $name ($fields)";
  256. break;
  257. case 'primary':
  258. $columns[] = " PRIMARY KEY ($fields)";
  259. break;
  260. case 'index':
  261. case 'foreign':
  262. case 'clustered':
  263. case 'alternate_key':
  264. /**
  265. * @todo here it is assumed that the primary key of the foreign
  266. * table will always be named 'id'. It must be noted though
  267. * that this can easily be fixed by referring to db dictionary
  268. * to find the correct primary field name
  269. */
  270. if ( $alter_table )
  271. $columns[] = " INDEX $name ($fields)";
  272. else
  273. $columns[] = " KEY $name ($fields)";
  274. break;
  275. case 'fulltext':
  276. if ($this->full_text_indexing_enabled())
  277. $columns[] = " FULLTEXT ($fields)";
  278. else
  279. $GLOBALS['log']->debug('MYISAM engine is not available/enabled, full-text indexes will be skipped. Skipping:',$name);
  280. break;
  281. }
  282. }
  283. $columns = implode(", $alter_action ", $columns);
  284. if(!empty($alter_action)){
  285. $columns = $alter_action . ' '. $columns;
  286. }
  287. return $columns;
  288. }
  289. /**
  290. * @see DBHelper::setAutoIncrement()
  291. */
  292. protected function setAutoIncrement(
  293. $table,
  294. $field_name
  295. )
  296. {
  297. return "auto_increment";
  298. }
  299. /**
  300. * Sets the next auto-increment value of a column to a specific value.
  301. *
  302. * @param string $table tablename
  303. * @param string $field_name
  304. */
  305. public function setAutoIncrementStart(
  306. $table,
  307. $field_name,
  308. $start_value
  309. )
  310. {
  311. $this->db->query( "ALTER TABLE $table AUTO_INCREMENT = $start_value;");
  312. return true;
  313. }
  314. /**
  315. * Returns the next value for an auto increment
  316. *
  317. * @param string $table tablename
  318. * @param string $field_name
  319. * @return string
  320. */
  321. public function getAutoIncrement(
  322. $table,
  323. $field_name
  324. )
  325. {
  326. $result = $this->db->query("SHOW TABLE STATUS LIKE '$table'");
  327. $row = $this->db->fetchByAssoc($result);
  328. if (!empty($row['Auto_increment']))
  329. return $row['Auto_increment'];
  330. return "";
  331. }
  332. /**
  333. * @see DBHelper::get_indices()
  334. */
  335. public function get_indices(
  336. $tablename
  337. )
  338. {
  339. //find all unique indexes and primary keys.
  340. $result = $this->db->query("SHOW INDEX FROM $tablename");
  341. $indices = array();
  342. while (($row=$this->db->fetchByAssoc($result)) !=null) {
  343. $index_type='index';
  344. if ($row['Key_name'] =='PRIMARY') {
  345. $index_type='primary';
  346. }
  347. elseif ( $row['Non_unique'] == '0' ) {
  348. $index_type='unique';
  349. }
  350. $name = strtolower($row['Key_name']);
  351. $indices[$name]['name']=$name;
  352. $indices[$name]['type']=$index_type;
  353. $indices[$name]['fields'][]=strtolower($row['Column_name']);
  354. }
  355. return $indices;
  356. }
  357. /**
  358. * @see DBHelper::get_columns()
  359. */
  360. public function get_columns(
  361. $tablename
  362. )
  363. {
  364. //find all unique indexes and primary keys.
  365. $result = $this->db->query("DESCRIBE $tablename");
  366. $columns = array();
  367. while (($row=$this->db->fetchByAssoc($result)) !=null) {
  368. $name = strtolower($row['Field']);
  369. $columns[$name]['name']=$name;
  370. $matches = array();
  371. preg_match_all("/(\w+)(?:\(([0-9]+,?[0-9]*)\)|)( unsigned)?/i", $row['Type'], $matches);
  372. $columns[$name]['type']=strtolower($matches[1][0]);
  373. if ( isset($matches[2][0]) && in_array(strtolower($matches[1][0]),array('varchar','char','varchar2','int','decimal','float')) )
  374. $columns[$name]['len']=strtolower($matches[2][0]);
  375. if ( stristr($row['Extra'],'auto_increment') )
  376. $columns[$name]['auto_increment'] = '1';
  377. if ($row['Null'] == 'NO' && !stristr($row['Key'],'PRI'))
  378. $columns[$name]['required'] = 'true';
  379. if (!empty($row['Default']) )
  380. $columns[$name]['default'] = $row['Default'];
  381. }
  382. return $columns;
  383. }
  384. /**
  385. * @see DBHelper::add_drop_constraint()
  386. */
  387. public function add_drop_constraint(
  388. $table,
  389. $definition,
  390. $drop = false
  391. )
  392. {
  393. $type = $definition['type'];
  394. $fields = implode(',',$definition['fields']);
  395. $name = $definition['name'];
  396. $foreignTable = isset($definition['foreignTable']) ? $definition['foreignTable'] : array();
  397. $sql = '';
  398. switch ($type){
  399. // generic indices
  400. case 'index':
  401. case 'alternate_key':
  402. if ($drop)
  403. $sql = "DROP INDEX {$name} ";
  404. else
  405. $sql = "CREATE INDEX {$name} ON {$table} ({$fields})";
  406. break;
  407. // constraints as indices
  408. case 'unique':
  409. if ($drop)
  410. $sql = "ALTER TABLE {$table} DROP INDEX $name";
  411. else
  412. $sql = "ALTER TABLE {$table} ADD CONSTRAINT UNIQUE {$name} ({$fields})";
  413. break;
  414. case 'primary':
  415. if ($drop)
  416. $sql = "ALTER TABLE {$table} DROP PRIMARY KEY";
  417. else
  418. $sql = "ALTER TABLE {$table} ADD CONSTRAINT PRIMARY KEY ({$fields})";
  419. break;
  420. case 'foreign':
  421. if ($drop)
  422. $sql = "ALTER TABLE {$table} DROP FOREIGN KEY ({$fields})";
  423. else
  424. $sql = "ALTER TABLE {$table} ADD CONSTRAINT FOREIGN KEY {$name} ({$fields}) REFERENCES {$foreignTable}({$foreignfields})";
  425. break;
  426. }
  427. return $sql;
  428. }
  429. /**
  430. * @see DBHelper::number_of_columns()
  431. */
  432. public function number_of_columns(
  433. $table_name
  434. )
  435. {
  436. $result = $this->db->query("DESCRIBE $table_name");
  437. return ($this->db->getRowCount($result));
  438. }
  439. /**
  440. * @see DBHelper::full_text_indexing_enabled()
  441. */
  442. protected function full_text_indexing_enabled(
  443. $dbname = null
  444. )
  445. {
  446. return $this->isEngineEnabled('MyISAM');
  447. }
  448. /**
  449. * @see DBHelper::massageFieldDef()
  450. */
  451. public function massageFieldDef(
  452. &$fieldDef,
  453. $tablename
  454. )
  455. {
  456. DBHelper::massageFieldDef($fieldDef,$tablename);
  457. if ( isset($fieldDef['default']) &&
  458. ($fieldDef['dbType'] == 'text'
  459. || $fieldDef['dbType'] == 'blob'
  460. || $fieldDef['dbType'] == 'longtext'
  461. || $fieldDef['dbType'] == 'longblob' ))
  462. unset($fieldDef['default']);
  463. if ($fieldDef['dbType'] == 'uint')
  464. $fieldDef['len'] = '10';
  465. if ($fieldDef['dbType'] == 'ulong')
  466. $fieldDef['len'] = '20';
  467. if ($fieldDef['dbType'] == 'bool')
  468. $fieldDef['type'] = 'tinyint';
  469. if ($fieldDef['dbType'] == 'bool' && empty($fieldDef['default']) )
  470. $fieldDef['default'] = '0';
  471. if (($fieldDef['dbType'] == 'varchar' || $fieldDef['dbType'] == 'enum') && empty($fieldDef['len']) )
  472. $fieldDef['len'] = '255';
  473. if ($fieldDef['dbType'] == 'uint')
  474. $fieldDef['len'] = '10';
  475. if ($fieldDef['dbType'] == 'int' && empty($fieldDef['len']) )
  476. $fieldDef['len'] = '11';
  477. }
  478. }
  479. ?>