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.
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.
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
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
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.
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.
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 ********************************************************************************/
38 /*********************************************************************************
40 * Description: This file handles the Data base functionality for the application specific
41 * to SQL Server database using the php_sqlsrv extension. It is called by the DBManager class to generate various sql statements.
43 * All the functions in this class will work with any bean which implements the meta interface.
44 * Please refer the DBManager documentation for the details.
46 * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
47 * All Rights Reserved.
48 * Contributor(s): ______________________________________..
49 ********************************************************************************/
50 require_once('include/database/MssqlHelper.php');
52 class SqlsrvHelper extends MssqlHelper
55 * @see DBHelper::getColumnType()
57 public function getColumnType(
63 $columnType = parent::getColumnType($type,$name,$table);
65 if ( in_array($columnType,array('char','varchar')) && !preg_match('/(_id$|^id$)/', $name))
66 $columnType = 'n'.$columnType;
68 if ( in_array($columnType,array('text','ntext','image')) ) {
69 $columnType = 'nvarchar(max)';
76 * @see DBHelper::massageValue()
78 public function massageValue(
83 $type = $this->getFieldType($fieldDef);
98 $qval = $this->quote($val);
134 * Detect if no clustered index has been created for a table; if none created then just pick the first index and make it that
136 * @see MssqlHelper::indexSQL()
138 public function indexSQL(
144 if ( $this->doesTableHaveAClusteredIndexDefined($tableName) ) {
145 return parent::indexSQL($tableName, $fieldDefs, $indices);
148 // check to see if one of the passed in indices is a primary one; if so we can bail as well
149 foreach ( $indices as $index ) {
150 if ( $index['type'] == 'primary' ) {
151 return parent::indexSQL($tableName, $fieldDefs, $indices);
155 // Change the first index listed to be a clustered one instead ( so we have at least one for the table )
156 if ( isset($indices[0]) ) {
157 $indices[0]['type'] = 'clustered';
160 return parent::indexSQL($tableName, $fieldDefs, $indices);
164 * @see DBHelper::get_columns()
166 public function get_columns(
170 //find all unique indexes and primary keys.
171 $result = $this->db->query("sp_columns_90 $tablename");
174 while (($row=$this->db->fetchByAssoc($result)) !=null) {
175 $column_name = strtolower($row['COLUMN_NAME']);
176 $columns[$column_name]['name']=$column_name;
177 $columns[$column_name]['type']=strtolower($row['TYPE_NAME']);
178 if ( $row['TYPE_NAME'] == 'decimal' ) {
179 $columns[$column_name]['len']=strtolower($row['PRECISION']);
180 $columns[$column_name]['len'].=','.strtolower($row['SCALE']);
182 elseif ( in_array($row['TYPE_NAME'],array('nchar','nvarchar')) ) {
183 $columns[$column_name]['len']=strtolower($row['PRECISION']);
184 if ( $row['TYPE_NAME'] == 'nvarchar' && $row['PRECISION'] == '0' ) {
185 $columns[$column_name]['len']='255';
188 elseif ( !in_array($row['TYPE_NAME'],array('datetime','text')) ) {
189 $columns[$column_name]['len']=strtolower($row['LENGTH']);
191 if ( stristr($row['TYPE_NAME'],'identity') ) {
192 $columns[$column_name]['auto_increment'] = '1';
193 $columns[$column_name]['type']=str_replace(' identity','',strtolower($row['TYPE_NAME']));
196 if (!empty($row['IS_NULLABLE']) && $row['IS_NULLABLE'] == 'NO' && (empty($row['KEY']) || !stristr($row['KEY'],'PRI')))
197 $columns[strtolower($row['COLUMN_NAME'])]['required'] = 'true';
200 if ( strtolower($tablename) == 'relationships' ) {
201 $column_def = $this->db->getOne("select cdefault from syscolumns where id = object_id('relationships') and name = '$column_name'");
203 if ( $column_def != 0 ) {
205 $row['COLUMN_DEF'] = html_entity_decode($row['COLUMN_DEF'],ENT_QUOTES);
206 if ( preg_match("/\([\(|'](.*)[\)|']\)/i",$row['COLUMN_DEF'],$matches) )
207 $columns[$column_name]['default'] = $matches[1];
208 elseif ( preg_match("/\(N'(.*)'\)/i",$row['COLUMN_DEF'],$matches) )
209 $columns[$column_name]['default'] = $matches[1];
211 $columns[$column_name]['default'] = $row['COLUMN_DEF'];
218 * @see DBHelper::get_indices()
220 public function get_indices(
224 //find all unique indexes and primary keys.
226 SELECT sys.tables.object_id, sys.tables.name as table_name, sys.columns.name as column_name,
227 sys.indexes.name as index_name, sys.indexes.is_unique, sys.indexes.is_primary_key
228 FROM sys.tables, sys.indexes, sys.index_columns, sys.columns
229 WHERE (sys.tables.object_id = sys.indexes.object_id
230 AND sys.tables.object_id = sys.index_columns.object_id
231 AND sys.tables.object_id = sys.columns.object_id
232 AND sys.indexes.index_id = sys.index_columns.index_id
233 AND sys.index_columns.column_id = sys.columns.column_id)
234 AND sys.tables.name = '$tableName'
236 $result = $this->db->query($query);
239 while (($row=$this->db->fetchByAssoc($result)) != null) {
240 $index_type = 'index';
241 if ($row['is_primary_key'] == '1')
242 $index_type = 'primary';
243 elseif ($row['is_unique'] == 1 )
244 $index_type = 'unique';
245 $name = strtolower($row['index_name']);
246 $indices[$name]['name'] = $name;
247 $indices[$name]['type'] = $index_type;
248 $indices[$name]['fields'][] = strtolower($row['column_name']);
254 * protected function to return true if the given tablename has any clustered indexes defined.
256 * @param string $tableName
259 protected function doesTableHaveAClusteredIndexDefined($tableName)
262 SELECT IST.TABLE_NAME
263 FROM INFORMATION_SCHEMA.TABLES IST
264 WHERE objectProperty(object_id(IST.TABLE_NAME), 'IsUserTable') = 1
265 AND objectProperty(object_id(IST.TABLE_NAME), 'TableHasClustIndex') = 1
266 AND IST.TABLE_NAME = '{$tableName}'
269 $result = $this->db->getOne($query);
278 * protected function to return true if the given tablename has any fulltext indexes defined.
280 * @param string $tableName
283 protected function doesTableHaveAFulltextIndexDefined($tableName)
287 FROM sys.fulltext_indexes i
288 JOIN sys.objects o ON i.object_id = o.object_id
289 WHERE o.name = '{$tableName}'
292 $result = $this->db->getOne($query);
301 * Override method to add support for detecting and dropping fulltext indices.
303 * @see DBHelper::changeColumnSQL()
304 * @see MssqlHelper::changeColumnSQL()
306 protected function changeColumnSQL(
310 $ignoreRequired = false
314 if ( $this->doesTableHaveAFulltextIndexDefined($tablename) ) {
315 $sql .= "DROP FULLTEXT INDEX ON {$tablename}";
318 $sql .= parent::changeColumnSQL($tablename, $fieldDefs, $action, $ignoreRequired);
324 * @see DBHelper::massageFieldDef()
326 public function massageFieldDef(
331 parent::massageFieldDef($fieldDef,$tablename);
333 if ($fieldDef['type'] == 'bit')
334 $fieldDef['len'] = '1';