66 $this->mUseWindowsAuth =
$params[
'UseWindowsAuth'];
80 public function open( $server,
$user, $password, $dbName ) {
81 # Test for driver support, to avoid suppressed fatal error
82 if ( !function_exists(
'sqlsrv_connect' ) ) {
85 "Microsoft SQL Server Native (sqlsrv) functions missing.
86 You can download the driver from: http://go.microsoft.com/fwlink/?LinkId=123470\n"
90 # e.g. the class is being loaded
91 if ( !strlen(
$user ) ) {
96 $this->mServer = $server;
98 $this->mPassword = $password;
99 $this->mDBname = $dbName;
101 $connectionInfo = [];
104 $connectionInfo[
'Database'] = $dbName;
109 if ( !$this->mUseWindowsAuth ) {
110 $connectionInfo[
'UID'] =
$user;
111 $connectionInfo[
'PWD'] = $password;
114 MediaWiki\suppressWarnings();
115 $this->mConn = sqlsrv_connect( $server, $connectionInfo );
116 MediaWiki\restoreWarnings();
118 if ( $this->mConn ===
false ) {
122 $this->mOpened =
true;
133 return sqlsrv_close( $this->mConn );
145 } elseif (
$result ===
true ) {
164 if ( preg_match(
'/\bLIMIT\s*/i', $sql ) ) {
170 if ( preg_match(
'#\bEXTRACT\s*?\(\s*?EPOCH\s+FROM\b#i', $sql,
$matches ) ) {
172 $sql = str_replace(
$matches[0],
"DATEDIFF(s,CONVERT(datetime,'1/1/1970'),", $sql );
181 if ( $this->mScrollableCursor ) {
182 $scrollArr = [
'Scrollable' => SQLSRV_CURSOR_STATIC ];
187 if ( $this->mPrepareStatements ) {
189 $stmt = sqlsrv_prepare( $this->mConn, $sql, [], $scrollArr );
192 $stmt = sqlsrv_query( $this->mConn, $sql, [], $scrollArr );
199 if ( $this->mIgnoreDupKeyErrors ) {
202 $ignoreErrors[] =
'2601';
203 $ignoreErrors[] =
'2627';
204 $ignoreErrors[] =
'3621';
208 $errors = sqlsrv_errors();
211 foreach ( $errors
as $err ) {
212 if ( !in_array( $err[
'code'], $ignoreErrors ) ) {
223 $this->mAffectedRows = sqlsrv_rows_affected( $stmt );
233 sqlsrv_free_stmt(
$res );
242 return $res->fetchObject();
250 return $res->fetchRow();
264 if (
$ret ===
false ) {
267 $ret = (int)sqlsrv_has_rows(
$res );
282 return sqlsrv_num_fields(
$res );
295 return sqlsrv_field_metadata(
$res )[$n][
'Name'];
312 return $res->seek( $row );
320 $retErrors = sqlsrv_errors( SQLSRV_ERR_ALL );
321 if ( $retErrors !=
null ) {
322 foreach ( $retErrors
as $arrError ) {
326 $strRet =
"No errors found";
337 return '[SQLSTATE ' .
338 $err[
'SQLSTATE'] .
'][Error Code ' . $err[
'code'] .
']' . $err[
'message'];
345 $err = sqlsrv_errors( SQLSRV_ERR_ALL );
346 if ( $err !==
null && isset( $err[0] ) ) {
347 return $err[0][
'code'];
382 if ( isset(
$options[
'EXPLAIN'] ) ) {
384 $this->mScrollableCursor =
false;
385 $this->mPrepareStatements =
false;
386 $this->
query(
"SET SHOWPLAN_ALL ON" );
388 $this->
query(
"SET SHOWPLAN_ALL OFF" );
390 if ( isset(
$options[
'FOR COUNT'] ) ) {
392 $this->
query(
"SET SHOWPLAN_ALL OFF" );
396 'COUNT(*) AS EstimateRows',
405 $this->mScrollableCursor =
true;
406 $this->mPrepareStatements =
true;
410 $this->mScrollableCursor =
true;
411 $this->mPrepareStatements =
true;
433 if ( isset(
$options[
'EXPLAIN'] ) ) {
440 if ( strpos( $sql,
'MAX(' ) !==
false || strpos( $sql,
'MIN(' ) !==
false ) {
442 if ( is_array( $table ) ) {
443 foreach ( $table
as $t ) {
450 foreach ( $bitColumns
as $col => $info ) {
452 "MAX({$col})" =>
"MAX(CAST({$col} AS tinyint))",
453 "MIN({$col})" =>
"MIN(CAST({$col} AS tinyint))",
455 $sql = str_replace( array_keys( $replace ), array_values( $replace ), $sql );
462 public function deleteJoin( $delTable, $joinTable, $delVar, $joinVar, $conds,
465 $this->mScrollableCursor =
false;
467 parent::deleteJoin( $delTable, $joinTable, $delVar, $joinVar, $conds,
$fname );
468 }
catch ( Exception
$e ) {
469 $this->mScrollableCursor =
true;
472 $this->mScrollableCursor =
true;
475 public function delete( $table, $conds,
$fname = __METHOD__ ) {
476 $this->mScrollableCursor =
false;
478 parent::delete( $table, $conds,
$fname );
479 }
catch ( Exception
$e ) {
480 $this->mScrollableCursor =
true;
483 $this->mScrollableCursor =
true;
511 if ( isset( $row[
'EstimateRows'] ) ) {
512 $rows = (int)$row[
'EstimateRows'];
528 # This does not return the same info as MYSQL would, but that's OK
529 # because MediaWiki never uses the returned value except to check for
530 # the existence of indexes.
531 $sql =
"sp_helpindex '" . $this->
tableName( $table ) .
"'";
539 foreach (
$res as $row ) {
540 if ( $row->index_name == $index ) {
541 $row->Non_unique = !stristr( $row->index_description,
"unique" );
542 $cols = explode(
", ", $row->index_keys );
543 foreach ( $cols
as $col ) {
544 $row->Column_name = trim( $col );
547 } elseif ( $index ==
'PRIMARY' && stristr( $row->index_description,
'PRIMARY' ) ) {
548 $row->Non_unique = 0;
549 $cols = explode(
", ", $row->index_keys );
550 foreach ( $cols
as $col ) {
551 $row->Column_name = trim( $col );
576 # No rows to insert, easy just return now
577 if ( !
count( $arrToInsert ) ) {
587 if ( !( isset( $arrToInsert[0] ) && is_array( $arrToInsert[0] ) ) ) {
588 $arrToInsert = [ 0 => $arrToInsert ];
594 $tableRawArr = explode(
'.', preg_replace(
'#\[([^\]]*)\]#',
'$1', $table ) );
595 $tableRaw = array_pop( $tableRawArr );
597 "SELECT NAME AS idColumn FROM SYS.IDENTITY_COLUMNS " .
598 "WHERE OBJECT_NAME(OBJECT_ID)='{$tableRaw}'"
600 if (
$res && sqlsrv_has_rows(
$res ) ) {
602 $identityArr = sqlsrv_fetch_array(
$res, SQLSRV_FETCH_ASSOC );
603 $identity = array_pop( $identityArr );
605 sqlsrv_free_stmt(
$res );
612 if ( in_array(
'IGNORE',
$options ) ) {
614 $this->mIgnoreDupKeyErrors =
true;
618 foreach ( $arrToInsert
as $a ) {
623 $identityClause =
'';
628 foreach ( $a
as $k => $v ) {
629 if ( $k == $identity ) {
630 if ( !is_null( $v ) ) {
633 $sqlPre =
"SET IDENTITY_INSERT $table ON;";
634 $sqlPost =
";SET IDENTITY_INSERT $table OFF;";
644 $identityClause =
"OUTPUT INSERTED.$identity ";
647 $keys = array_keys( $a );
650 $sql = $sqlPre .
'INSERT ' . implode(
' ',
$options ) .
651 " INTO $table (" . implode(
',',
$keys ) .
") $identityClause VALUES (";
655 if ( isset( $binaryColumns[$key] ) ) {
663 if ( is_null(
$value ) ) {
665 } elseif ( is_array(
$value ) || is_object(
$value ) ) {
675 $sql .=
')' . $sqlPost;
678 $this->mScrollableCursor =
false;
681 }
catch ( Exception
$e ) {
682 $this->mScrollableCursor =
true;
683 $this->mIgnoreDupKeyErrors =
false;
686 $this->mScrollableCursor =
true;
690 $row =
$ret->fetchObject();
691 if ( is_object( $row ) ) {
692 $this->mInsertId = $row->$identity;
696 if ( $this->mAffectedRows == -1 ) {
697 $this->mAffectedRows = 1;
703 $this->mIgnoreDupKeyErrors =
false;
724 $insertOptions = [], $selectOptions = []
726 $this->mScrollableCursor =
false;
728 $ret = parent::nativeInsertSelect(
737 }
catch ( Exception
$e ) {
738 $this->mScrollableCursor =
true;
741 $this->mScrollableCursor =
true;
776 $sql =
"UPDATE $opts $table SET " . $this->
makeList( $values,
LIST_SET, $binaryColumns );
778 if ( $conds !== [] && $conds !==
'*' ) {
782 $this->mScrollableCursor =
false;
784 $this->
query( $sql );
785 }
catch ( Exception
$e ) {
786 $this->mScrollableCursor =
true;
789 $this->mScrollableCursor =
true;
810 if ( !is_array( $a ) ) {
811 throw new DBUnexpectedError( $this, __METHOD__ .
' called with incorrect parameters' );
818 foreach ( array_keys( $a )
as $field ) {
819 if ( !isset( $binaryColumns[$field] ) ) {
823 if ( is_array( $a[$field] ) ) {
824 foreach ( $a[$field]
as &$v ) {
829 $a[$field] =
new MssqlBlob( $a[$field] );
834 return parent::makeList( $a, $mode );
844 $sql =
"SELECT CHARACTER_MAXIMUM_LENGTH,DATA_TYPE FROM INFORMATION_SCHEMA.Columns
845 WHERE TABLE_NAME = '$table' AND COLUMN_NAME = '$field'";
849 if ( strtolower( $row[
'DATA_TYPE'] ) !=
'text' ) {
850 $size = $row[
'CHARACTER_MAXIMUM_LENGTH'];
867 if ( $offset ===
false || $offset == 0 ) {
868 if ( strpos( $sql,
"SELECT" ) ===
false ) {
869 return "TOP {$limit} " . $sql;
871 return preg_replace(
'/\bSELECT(\s+DISTINCT)?\b/Dsi',
872 'SELECT$1 TOP ' .
$limit, $sql, 1 );
876 $select = $orderby = [];
877 $s1 = preg_match(
'#SELECT\s+(.+?)\s+FROM#Dis', $sql, $select );
878 $s2 = preg_match(
'#(ORDER BY\s+.+?)(\s*FOR XML .*)?$#Dis', $sql, $orderby );
880 $first = $offset + 1;
883 $sub2 =
'sub_' . ( $this->mSubqueryId + 1 );
884 $this->mSubqueryId += 2;
887 throw new DBUnexpectedError( $this,
"Attempting to LIMIT a non-SELECT query\n" );
891 $overOrder =
'ORDER BY (SELECT 1)';
893 if ( !isset( $orderby[2] ) || !$orderby[2] ) {
895 $sql = str_replace( $orderby[1],
'', $sql );
897 $overOrder = $orderby[1];
898 $postOrder =
' ' . $overOrder;
900 $sql =
"SELECT {$select[1]}
902 SELECT ROW_NUMBER() OVER({$overOrder}) AS rowNumber, *
903 FROM ({$sql}) {$sub1}
905 WHERE rowNumber BETWEEN {$first} AND {$last}{$postOrder}";
923 $pattern =
'/\bLIMIT\s+((([0-9]+)\s*,\s*)?([0-9]+)(\s+OFFSET\s+([0-9]+))?)/i';
924 if ( preg_match( $pattern, $sql,
$matches ) ) {
929 $sql = str_replace(
$matches[0],
'', $sql );
931 return $this->
limitResult( $sql, $row_count, $offset );
941 return "[{{int:version-db-mssql-url}} MS SQL Server]";
948 $server_info = sqlsrv_server_info( $this->mConn );
950 if ( isset( $server_info[
'SQLServerVersion'] ) ) {
951 $version = $server_info[
'SQLServerVersion'];
963 list( $db, $schema, $table ) = $this->
tableName( $table,
'split' );
965 if ( $db !==
false ) {
967 $this->queryLogger->error(
"Attempting to call tableExists on a remote table" );
971 if ( $schema ===
false ) {
975 $res = $this->
query(
"SELECT 1 FROM INFORMATION_SCHEMA.TABLES
976 WHERE TABLE_TYPE = 'BASE TABLE'
977 AND TABLE_SCHEMA = '$schema' AND TABLE_NAME = '$table'" );
979 if (
$res->numRows() ) {
994 list( $db, $schema, $table ) = $this->
tableName( $table,
'split' );
996 if ( $db !==
false ) {
998 $this->queryLogger->error(
"Attempting to call fieldExists on a remote table" );
1002 $res = $this->
query(
"SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
1003 WHERE TABLE_SCHEMA = '$schema' AND TABLE_NAME = '$table' AND COLUMN_NAME = '$field'" );
1005 if (
$res->numRows() ) {
1013 list( $db, $schema, $table ) = $this->
tableName( $table,
'split' );
1015 if ( $db !==
false ) {
1017 $this->queryLogger->error(
"Attempting to call fieldInfo on a remote table" );
1021 $res = $this->
query(
"SELECT * FROM INFORMATION_SCHEMA.COLUMNS
1022 WHERE TABLE_SCHEMA = '$schema' AND TABLE_NAME = '$table' AND COLUMN_NAME = '$field'" );
1024 $meta =
$res->fetchRow();
1037 sqlsrv_begin_transaction( $this->mConn );
1038 $this->mTrxLevel = 1;
1046 sqlsrv_commit( $this->mConn );
1047 $this->mTrxLevel = 0;
1056 sqlsrv_rollback( $this->mConn );
1057 $this->mTrxLevel = 0;
1067 return str_replace(
"'",
"''",
$s );
1077 } elseif (
$s instanceof
Blob ) {
1081 return $blob->fetch();
1083 if ( is_bool(
$s ) ) {
1086 return parent::addQuotes(
$s );
1096 return '[' .
$s .
']';
1104 return strlen(
$name ) &&
$name[0] ==
'[' && substr(
$name, -1, 1 ) ==
']';
1114 return str_replace( [ $escapeChar,
'%',
'_',
'[',
']',
'^' ],
1115 [
"{$escapeChar}{$escapeChar}",
"{$escapeChar}%",
"{$escapeChar}_",
1116 "{$escapeChar}[",
"{$escapeChar}]",
"{$escapeChar}^" ],
1126 $this->mDBname = $db;
1127 $this->
query(
"USE $db" );
1129 }
catch ( Exception
$e ) {
1145 if ( is_numeric( $key ) ) {
1146 $noKeyOptions[$option] =
true;
1154 if ( isset( $noKeyOptions[
'DISTINCT'] ) || isset( $noKeyOptions[
'DISTINCTROW'] ) ) {
1155 $startOpts .=
'DISTINCT';
1158 if ( isset( $noKeyOptions[
'FOR XML'] ) ) {
1160 $tailOpts .=
" FOR XML PATH('')";
1164 return [ $startOpts,
'', $tailOpts,
'',
'' ];
1176 return implode(
' + ', $stringList );
1200 $this->mSubqueryId++;
1202 $delimLen = strlen( $delim );
1203 $fld =
"{$field} + {$this->addQuotes( $delim )}";
1204 $sql =
"(SELECT LEFT({$field}, LEN({$field}) - {$delimLen}) FROM ("
1205 . $this->
selectSQLText( $table, $fld, $conds,
null, [
'FOR XML' ], $join_conds )
1206 .
") {$gcsq} ({$field}))";
1218 $tableRawArr = explode(
'.', preg_replace(
'#\[([^\]]*)\]#',
'$1', $table ) );
1219 $tableRaw = array_pop( $tableRawArr );
1221 if ( $this->mBinaryColumnCache ===
null ) {
1225 return isset( $this->mBinaryColumnCache[$tableRaw] )
1226 ? $this->mBinaryColumnCache[$tableRaw]
1235 $tableRawArr = explode(
'.', preg_replace(
'#\[([^\]]*)\]#',
'$1', $table ) );
1236 $tableRaw = array_pop( $tableRawArr );
1238 if ( $this->mBitColumnCache ===
null ) {
1242 return isset( $this->mBitColumnCache[$tableRaw] )
1243 ? $this->mBitColumnCache[$tableRaw]
1248 $res = $this->
select(
'INFORMATION_SCHEMA.COLUMNS',
'*',
1250 'TABLE_CATALOG' => $this->mDBname,
1251 'TABLE_SCHEMA' => $this->mSchema,
1252 'DATA_TYPE' => [
'varbinary',
'binary',
'image',
'bit' ]
1255 $this->mBinaryColumnCache = [];
1256 $this->mBitColumnCache = [];
1257 foreach (
$res as $row ) {
1258 if ( $row->DATA_TYPE ==
'bit' ) {
1259 $this->mBitColumnCache[$row->TABLE_NAME][$row->COLUMN_NAME] = $row;
1261 $this->mBinaryColumnCache[$row->TABLE_NAME][$row->COLUMN_NAME] = $row;
1272 # Replace reserved words with better ones
1289 if ( $format ==
'split' ) {
1292 $table = explode(
'.', $table );
1293 while (
count( $table ) < 3 ) {
1294 array_unshift( $table,
false );
1307 public function dropTable( $tableName, $fName = __METHOD__ ) {
1308 if ( !$this->
tableExists( $tableName, $fName ) ) {
1313 $sql =
"DROP TABLE " . $this->
tableName( $tableName );
1315 return $this->
query( $sql, $fName );
1327 $this->mPrepareStatements =
$value;
1342 $this->mScrollableCursor =
$value;