PageRenderTime 42ms CodeModel.GetById 20ms RepoModel.GetById 0ms app.codeStats 0ms

/include/database/SqlsrvHelper.php

https://github.com/vincentamari/SuperSweetAdmin
PHP | 323 lines | 209 code | 29 blank | 85 comment | 37 complexity | 192ad146c32328a5f6a0143d07253fd2 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 SQL Server database using the php_sqlsrv extension. 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/MssqlHelper.php');
  49. class SqlsrvHelper extends MssqlHelper
  50. {
  51. /**
  52. * @see DBHelper::getColumnType()
  53. */
  54. public function getColumnType(
  55. $type,
  56. $name = '',
  57. $table = ''
  58. )
  59. {
  60. $columnType = parent::getColumnType($type,$name,$table);
  61. if ( in_array($columnType,array('char','varchar')) && !preg_match('/(_id$|^id$)/', $name))
  62. $columnType = 'n'.$columnType;
  63. if ( in_array($columnType,array('text','ntext','image')) ) {
  64. $columnType = 'nvarchar(max)';
  65. }
  66. return $columnType;
  67. }
  68. /**
  69. * @see DBHelper::massageValue()
  70. */
  71. public function massageValue(
  72. $val,
  73. $fieldDef
  74. )
  75. {
  76. $type = $this->getFieldType($fieldDef);
  77. switch ($type) {
  78. case 'int':
  79. case 'double':
  80. case 'float':
  81. case 'uint':
  82. case 'ulong':
  83. case 'long':
  84. case 'short':
  85. case 'tinyint':
  86. return $val;
  87. break;
  88. }
  89. $qval = $this->quote($val);
  90. switch ($type) {
  91. case 'varchar':
  92. case 'nvarchar':
  93. case 'char':
  94. case 'nchar':
  95. case 'enum':
  96. case 'multienum':
  97. case 'id':
  98. return $qval;
  99. break;
  100. case 'date':
  101. return "$qval";
  102. break;
  103. case 'datetime':
  104. return $qval;
  105. break;
  106. case 'time':
  107. return "$qval";
  108. break;
  109. case 'text':
  110. case 'ntext':
  111. case 'blob':
  112. case 'longblob':
  113. case 'clob':
  114. case 'longtext':
  115. case 'image':
  116. return $qval;
  117. break;
  118. }
  119. return $val;
  120. }
  121. /**
  122. * Detect if no clustered index has been created for a table; if none created then just pick the first index and make it that
  123. *
  124. * @see MssqlHelper::indexSQL()
  125. */
  126. public function indexSQL(
  127. $tableName,
  128. $fieldDefs,
  129. $indices
  130. )
  131. {
  132. if ( $this->doesTableHaveAClusteredIndexDefined($tableName) ) {
  133. return parent::indexSQL($tableName, $fieldDefs, $indices);
  134. }
  135. // check to see if one of the passed in indices is a primary one; if so we can bail as well
  136. foreach ( $indices as $index ) {
  137. if ( $index['type'] == 'primary' ) {
  138. return parent::indexSQL($tableName, $fieldDefs, $indices);
  139. }
  140. }
  141. // Change the first index listed to be a clustered one instead ( so we have at least one for the table )
  142. if ( isset($indices[0]) ) {
  143. $indices[0]['type'] = 'clustered';
  144. }
  145. return parent::indexSQL($tableName, $fieldDefs, $indices);
  146. }
  147. /**
  148. * @see DBHelper::get_columns()
  149. */
  150. public function get_columns(
  151. $tablename
  152. )
  153. {
  154. //find all unique indexes and primary keys.
  155. $result = $this->db->query("sp_columns_90 $tablename");
  156. $columns = array();
  157. while (($row=$this->db->fetchByAssoc($result)) !=null) {
  158. $column_name = strtolower($row['COLUMN_NAME']);
  159. $columns[$column_name]['name']=$column_name;
  160. $columns[$column_name]['type']=strtolower($row['TYPE_NAME']);
  161. if ( $row['TYPE_NAME'] == 'decimal' ) {
  162. $columns[$column_name]['len']=strtolower($row['PRECISION']);
  163. $columns[$column_name]['len'].=','.strtolower($row['SCALE']);
  164. }
  165. elseif ( in_array($row['TYPE_NAME'],array('nchar','nvarchar')) ) {
  166. $columns[$column_name]['len']=strtolower($row['PRECISION']);
  167. if ( $row['TYPE_NAME'] == 'nvarchar' && $row['PRECISION'] == '0' ) {
  168. $columns[$column_name]['len']='max';
  169. }
  170. }
  171. elseif ( !in_array($row['TYPE_NAME'],array('datetime','text')) ) {
  172. $columns[$column_name]['len']=strtolower($row['LENGTH']);
  173. }
  174. if ( stristr($row['TYPE_NAME'],'identity') ) {
  175. $columns[$column_name]['auto_increment'] = '1';
  176. $columns[$column_name]['type']=str_replace(' identity','',strtolower($row['TYPE_NAME']));
  177. }
  178. if (!empty($row['IS_NULLABLE']) && $row['IS_NULLABLE'] == 'NO' && (empty($row['KEY']) || !stristr($row['KEY'],'PRI')))
  179. $columns[strtolower($row['COLUMN_NAME'])]['required'] = 'true';
  180. $column_def = 0;
  181. if ( strtolower($tablename) == 'relationships' ) {
  182. $column_def = $this->db->getOne("select cdefault from syscolumns where id = object_id('relationships') and name = '$column_name'");
  183. }
  184. if ( $column_def != 0 ) {
  185. $matches = array();
  186. $row['COLUMN_DEF'] = html_entity_decode($row['COLUMN_DEF'],ENT_QUOTES);
  187. if ( preg_match("/\([\(|'](.*)[\)|']\)/i",$row['COLUMN_DEF'],$matches) )
  188. $columns[$column_name]['default'] = $matches[1];
  189. elseif ( preg_match("/\(N'(.*)'\)/i",$row['COLUMN_DEF'],$matches) )
  190. $columns[$column_name]['default'] = $matches[1];
  191. else
  192. $columns[$column_name]['default'] = $row['COLUMN_DEF'];
  193. }
  194. }
  195. return $columns;
  196. }
  197. /**
  198. * @see DBHelper::get_indices()
  199. */
  200. public function get_indices(
  201. $tableName
  202. )
  203. {
  204. //find all unique indexes and primary keys.
  205. $query = <<<EOSQL
  206. SELECT sys.tables.object_id, sys.tables.name as table_name, sys.columns.name as column_name,
  207. sys.indexes.name as index_name, sys.indexes.is_unique, sys.indexes.is_primary_key
  208. FROM sys.tables, sys.indexes, sys.index_columns, sys.columns
  209. WHERE (sys.tables.object_id = sys.indexes.object_id
  210. AND sys.tables.object_id = sys.index_columns.object_id
  211. AND sys.tables.object_id = sys.columns.object_id
  212. AND sys.indexes.index_id = sys.index_columns.index_id
  213. AND sys.index_columns.column_id = sys.columns.column_id)
  214. AND sys.tables.name = '$tableName'
  215. EOSQL;
  216. $result = $this->db->query($query);
  217. $indices = array();
  218. while (($row=$this->db->fetchByAssoc($result)) != null) {
  219. $index_type = 'index';
  220. if ($row['is_primary_key'] == '1')
  221. $index_type = 'primary';
  222. elseif ($row['is_unique'] == 1 )
  223. $index_type = 'unique';
  224. $name = strtolower($row['index_name']);
  225. $indices[$name]['name'] = $name;
  226. $indices[$name]['type'] = $index_type;
  227. $indices[$name]['fields'][] = strtolower($row['column_name']);
  228. }
  229. return $indices;
  230. }
  231. /**
  232. * protected function to return true if the given tablename has any clustered indexes defined.
  233. *
  234. * @param string $tableName
  235. * @return bool
  236. */
  237. protected function doesTableHaveAClusteredIndexDefined($tableName)
  238. {
  239. $query = <<<EOSQL
  240. SELECT IST.TABLE_NAME
  241. FROM INFORMATION_SCHEMA.TABLES IST
  242. WHERE objectProperty(object_id(IST.TABLE_NAME), 'IsUserTable') = 1
  243. AND objectProperty(object_id(IST.TABLE_NAME), 'TableHasClustIndex') = 1
  244. AND IST.TABLE_NAME = '{$tableName}'
  245. EOSQL;
  246. $result = $this->db->getOne($query);
  247. if ( !$result ) {
  248. return false;
  249. }
  250. return true;
  251. }
  252. /**
  253. * protected function to return true if the given tablename has any fulltext indexes defined.
  254. *
  255. * @param string $tableName
  256. * @return bool
  257. */
  258. protected function doesTableHaveAFulltextIndexDefined($tableName)
  259. {
  260. $query = <<<EOSQL
  261. SELECT 1
  262. FROM sys.fulltext_indexes i
  263. JOIN sys.objects o ON i.object_id = o.object_id
  264. WHERE o.name = '{$tableName}'
  265. EOSQL;
  266. $result = $this->db->getOne($query);
  267. if ( !$result ) {
  268. return false;
  269. }
  270. return true;
  271. }
  272. /**
  273. * Override method to add support for detecting and dropping fulltext indices.
  274. *
  275. * @see DBHelper::changeColumnSQL()
  276. * @see MssqlHelper::changeColumnSQL()
  277. */
  278. protected function changeColumnSQL(
  279. $tablename,
  280. $fieldDefs,
  281. $action,
  282. $ignoreRequired = false
  283. )
  284. {
  285. $sql = '';
  286. if ( $this->doesTableHaveAFulltextIndexDefined($tablename) ) {
  287. $sql .= "DROP FULLTEXT INDEX ON {$tablename}";
  288. }
  289. $sql .= parent::changeColumnSQL($tablename, $fieldDefs, $action, $ignoreRequired);
  290. return $sql;
  291. }
  292. }
  293. ?>