MediaWiki
REL1_22
|
00001 <?php 00030 class DatabaseMssql extends DatabaseBase { 00031 var $mInsertId = null; 00032 var $mLastResult = null; 00033 var $mAffectedRows = null; 00034 00035 var $mPort; 00036 00037 function cascadingDeletes() { 00038 return true; 00039 } 00040 00041 function cleanupTriggers() { 00042 return true; 00043 } 00044 00045 function strictIPs() { 00046 return true; 00047 } 00048 00049 function realTimestamps() { 00050 return true; 00051 } 00052 00053 function implicitGroupby() { 00054 return false; 00055 } 00056 00057 function implicitOrderby() { 00058 return false; 00059 } 00060 00061 function functionalIndexes() { 00062 return true; 00063 } 00064 00065 function unionSupportsOrderAndLimit() { 00066 return false; 00067 } 00068 00078 function open( $server, $user, $password, $dbName ) { 00079 # Test for driver support, to avoid suppressed fatal error 00080 if ( !function_exists( 'sqlsrv_connect' ) ) { 00081 throw new DBConnectionError( $this, "MS Sql Server Native (sqlsrv) functions missing. You can download the driver from: http://go.microsoft.com/fwlink/?LinkId=123470\n" ); 00082 } 00083 00084 global $wgDBport; 00085 00086 if ( !strlen( $user ) ) { # e.g. the class is being loaded 00087 return; 00088 } 00089 00090 $this->close(); 00091 $this->mServer = $server; 00092 $this->mPort = $wgDBport; 00093 $this->mUser = $user; 00094 $this->mPassword = $password; 00095 $this->mDBname = $dbName; 00096 00097 $connectionInfo = array(); 00098 00099 if ( $dbName ) { 00100 $connectionInfo['Database'] = $dbName; 00101 } 00102 00103 // Start NT Auth Hack 00104 // Quick and dirty work around to provide NT Auth designation support. 00105 // Current solution requires installer to know to input 'ntauth' for both username and password 00106 // to trigger connection via NT Auth. - ugly, ugly, ugly 00107 // TO-DO: Make this better and add NT Auth choice to MW installer when SQL Server option is chosen. 00108 $ntAuthUserTest = strtolower( $user ); 00109 $ntAuthPassTest = strtolower( $password ); 00110 00111 // Decide which auth scenerio to use 00112 if ( $ntAuthPassTest == 'ntauth' && $ntAuthUserTest == 'ntauth' ) { 00113 // Don't add credentials to $connectionInfo 00114 } else { 00115 $connectionInfo['UID'] = $user; 00116 $connectionInfo['PWD'] = $password; 00117 } 00118 // End NT Auth Hack 00119 00120 wfSuppressWarnings(); 00121 $this->mConn = sqlsrv_connect( $server, $connectionInfo ); 00122 wfRestoreWarnings(); 00123 00124 if ( $this->mConn === false ) { 00125 wfDebug( "DB connection error\n" ); 00126 wfDebug( "Server: $server, Database: $dbName, User: $user, Password: " . substr( $password, 0, 3 ) . "...\n" ); 00127 wfDebug( $this->lastError() . "\n" ); 00128 return false; 00129 } 00130 00131 $this->mOpened = true; 00132 return $this->mConn; 00133 } 00134 00140 protected function closeConnection() { 00141 return sqlsrv_close( $this->mConn ); 00142 } 00143 00144 protected function doQuery( $sql ) { 00145 wfDebug( "SQL: [$sql]\n" ); 00146 $this->offset = 0; 00147 00148 // several extensions seem to think that all databases support limits via LIMIT N after the WHERE clause 00149 // well, MSSQL uses SELECT TOP N, so to catch any of those extensions we'll do a quick check for a LIMIT 00150 // clause and pass $sql through $this->LimitToTopN() which parses the limit clause and passes the result to 00151 // $this->limitResult(); 00152 if ( preg_match( '/\bLIMIT\s*/i', $sql ) ) { 00153 // massage LIMIT -> TopN 00154 $sql = $this->LimitToTopN( $sql ); 00155 } 00156 00157 // MSSQL doesn't have EXTRACT(epoch FROM XXX) 00158 if ( preg_match( '#\bEXTRACT\s*?\(\s*?EPOCH\s+FROM\b#i', $sql, $matches ) ) { 00159 // This is same as UNIX_TIMESTAMP, we need to calc # of seconds from 1970 00160 $sql = str_replace( $matches[0], "DATEDIFF(s,CONVERT(datetime,'1/1/1970'),", $sql ); 00161 } 00162 00163 // perform query 00164 $stmt = sqlsrv_query( $this->mConn, $sql ); 00165 if ( $stmt == false ) { 00166 $message = "A database error has occurred. Did you forget to run maintenance/update.php after upgrading? See: http://www.mediawiki.org/wiki/Manual:Upgrading#Run_the_update_script\n" . 00167 "Query: " . htmlentities( $sql ) . "\n" . 00168 "Function: " . __METHOD__ . "\n"; 00169 // process each error (our driver will give us an array of errors unlike other providers) 00170 foreach ( sqlsrv_errors() as $error ) { 00171 $message .= $message . "ERROR[" . $error['code'] . "] " . $error['message'] . "\n"; 00172 } 00173 00174 throw new DBUnexpectedError( $this, $message ); 00175 } 00176 // remember number of rows affected 00177 $this->mAffectedRows = sqlsrv_rows_affected( $stmt ); 00178 00179 // if it is a SELECT statement, or an insert with a request to output something we want to return a row. 00180 if ( ( preg_match( '#\bSELECT\s#i', $sql ) ) || 00181 ( preg_match( '#\bINSERT\s#i', $sql ) && preg_match( '#\bOUTPUT\s+INSERTED\b#i', $sql ) ) ) { 00182 // this is essentially a rowset, but Mediawiki calls these 'result' 00183 // the rowset owns freeing the statement 00184 $res = new MssqlResult( $stmt ); 00185 } else { 00186 // otherwise we simply return it was successful, failure throws an exception 00187 $res = true; 00188 } 00189 return $res; 00190 } 00191 00192 function freeResult( $res ) { 00193 if ( $res instanceof ResultWrapper ) { 00194 $res = $res->result; 00195 } 00196 $res->free(); 00197 } 00198 00199 function fetchObject( $res ) { 00200 if ( $res instanceof ResultWrapper ) { 00201 $res = $res->result; 00202 } 00203 $row = $res->fetch( 'OBJECT' ); 00204 return $row; 00205 } 00206 00207 function getErrors() { 00208 $strRet = ''; 00209 $retErrors = sqlsrv_errors( SQLSRV_ERR_ALL ); 00210 if ( $retErrors != null ) { 00211 foreach ( $retErrors as $arrError ) { 00212 $strRet .= "SQLState: " . $arrError['SQLSTATE'] . "\n"; 00213 $strRet .= "Error Code: " . $arrError['code'] . "\n"; 00214 $strRet .= "Message: " . $arrError['message'] . "\n"; 00215 } 00216 } else { 00217 $strRet = "No errors found"; 00218 } 00219 return $strRet; 00220 } 00221 00222 function fetchRow( $res ) { 00223 if ( $res instanceof ResultWrapper ) { 00224 $res = $res->result; 00225 } 00226 $row = $res->fetch( SQLSRV_FETCH_BOTH ); 00227 return $row; 00228 } 00229 00230 function numRows( $res ) { 00231 if ( $res instanceof ResultWrapper ) { 00232 $res = $res->result; 00233 } 00234 return ( $res ) ? $res->numrows() : 0; 00235 } 00236 00237 function numFields( $res ) { 00238 if ( $res instanceof ResultWrapper ) { 00239 $res = $res->result; 00240 } 00241 return ( $res ) ? $res->numfields() : 0; 00242 } 00243 00244 function fieldName( $res, $n ) { 00245 if ( $res instanceof ResultWrapper ) { 00246 $res = $res->result; 00247 } 00248 return ( $res ) ? $res->fieldname( $n ) : 0; 00249 } 00250 00255 function insertId() { 00256 return $this->mInsertId; 00257 } 00258 00259 function dataSeek( $res, $row ) { 00260 if ( $res instanceof ResultWrapper ) { 00261 $res = $res->result; 00262 } 00263 return ( $res ) ? $res->seek( $row ) : false; 00264 } 00265 00266 function lastError() { 00267 if ( $this->mConn ) { 00268 return $this->getErrors(); 00269 } else { 00270 return "No database connection"; 00271 } 00272 } 00273 00274 function lastErrno() { 00275 $err = sqlsrv_errors( SQLSRV_ERR_ALL ); 00276 if ( $err[0] ) { 00277 return $err[0]['code']; 00278 } else { 00279 return 0; 00280 } 00281 } 00282 00283 function affectedRows() { 00284 return $this->mAffectedRows; 00285 } 00286 00300 function select( $table, $vars, $conds = '', $fname = __METHOD__, $options = array(), $join_conds = array() ) 00301 { 00302 $sql = $this->selectSQLText( $table, $vars, $conds, $fname, $options, $join_conds ); 00303 if ( isset( $options['EXPLAIN'] ) ) { 00304 sqlsrv_query( $this->mConn, "SET SHOWPLAN_ALL ON;" ); 00305 $ret = $this->query( $sql, $fname ); 00306 sqlsrv_query( $this->mConn, "SET SHOWPLAN_ALL OFF;" ); 00307 return $ret; 00308 } 00309 return $this->query( $sql, $fname ); 00310 } 00311 00325 function selectSQLText( $table, $vars, $conds = '', $fname = __METHOD__, $options = array(), $join_conds = array() ) { 00326 if ( isset( $options['EXPLAIN'] ) ) { 00327 unset( $options['EXPLAIN'] ); 00328 } 00329 return parent::selectSQLText( $table, $vars, $conds, $fname, $options, $join_conds ); 00330 } 00331 00340 function estimateRowCount( $table, $vars = '*', $conds = '', $fname = __METHOD__, $options = array() ) { 00341 $options['EXPLAIN'] = true;// http://msdn2.microsoft.com/en-us/library/aa259203.aspx 00342 $res = $this->select( $table, $vars, $conds, $fname, $options ); 00343 00344 $rows = -1; 00345 if ( $res ) { 00346 $row = $this->fetchRow( $res ); 00347 if ( isset( $row['EstimateRows'] ) ) { 00348 $rows = $row['EstimateRows']; 00349 } 00350 } 00351 return $rows; 00352 } 00353 00359 function indexInfo( $table, $index, $fname = __METHOD__ ) { 00360 # This does not return the same info as MYSQL would, but that's OK because MediaWiki never uses the 00361 # returned value except to check for the existance of indexes. 00362 $sql = "sp_helpindex '" . $table . "'"; 00363 $res = $this->query( $sql, $fname ); 00364 if ( !$res ) { 00365 return null; 00366 } 00367 00368 $result = array(); 00369 foreach ( $res as $row ) { 00370 if ( $row->index_name == $index ) { 00371 $row->Non_unique = !stristr( $row->index_description, "unique" ); 00372 $cols = explode( ", ", $row->index_keys ); 00373 foreach ( $cols as $col ) { 00374 $row->Column_name = trim( $col ); 00375 $result[] = clone $row; 00376 } 00377 } elseif ( $index == 'PRIMARY' && stristr( $row->index_description, 'PRIMARY' ) ) { 00378 $row->Non_unique = 0; 00379 $cols = explode( ", ", $row->index_keys ); 00380 foreach ( $cols as $col ) { 00381 $row->Column_name = trim( $col ); 00382 $result[] = clone $row; 00383 } 00384 } 00385 } 00386 return empty( $result ) ? false : $result; 00387 } 00388 00404 function insert( $table, $arrToInsert, $fname = __METHOD__, $options = array() ) { 00405 # No rows to insert, easy just return now 00406 if ( !count( $arrToInsert ) ) { 00407 return true; 00408 } 00409 00410 if ( !is_array( $options ) ) { 00411 $options = array( $options ); 00412 } 00413 00414 $table = $this->tableName( $table ); 00415 00416 if ( !( isset( $arrToInsert[0] ) && is_array( $arrToInsert[0] ) ) ) {// Not multi row 00417 $arrToInsert = array( 0 => $arrToInsert );// make everything multi row compatible 00418 } 00419 00420 $allOk = true; 00421 00422 // We know the table we're inserting into, get its identity column 00423 $identity = null; 00424 $tableRaw = preg_replace( '#\[([^\]]*)\]#', '$1', $table ); // strip matching square brackets from table name 00425 $res = $this->doQuery( "SELECT NAME AS idColumn FROM SYS.IDENTITY_COLUMNS WHERE OBJECT_NAME(OBJECT_ID)='{$tableRaw}'" ); 00426 if ( $res && $res->numrows() ) { 00427 // There is an identity for this table. 00428 $identity = array_pop( $res->fetch( SQLSRV_FETCH_ASSOC ) ); 00429 } 00430 unset( $res ); 00431 00432 foreach ( $arrToInsert as $a ) { 00433 // start out with empty identity column, this is so we can return it as a result of the insert logic 00434 $sqlPre = ''; 00435 $sqlPost = ''; 00436 $identityClause = ''; 00437 00438 // if we have an identity column 00439 if ( $identity ) { 00440 // iterate through 00441 foreach ( $a as $k => $v ) { 00442 if ( $k == $identity ) { 00443 if ( !is_null( $v ) ) { 00444 // there is a value being passed to us, we need to turn on and off inserted identity 00445 $sqlPre = "SET IDENTITY_INSERT $table ON;"; 00446 $sqlPost = ";SET IDENTITY_INSERT $table OFF;"; 00447 00448 } else { 00449 // we can't insert NULL into an identity column, so remove the column from the insert. 00450 unset( $a[$k] ); 00451 } 00452 } 00453 } 00454 $identityClause = "OUTPUT INSERTED.$identity "; // we want to output an identity column as result 00455 } 00456 00457 $keys = array_keys( $a ); 00458 00459 // INSERT IGNORE is not supported by SQL Server 00460 // remove IGNORE from options list and set ignore flag to true 00461 $ignoreClause = false; 00462 foreach ( $options as $k => $v ) { 00463 if ( strtoupper( $v ) == "IGNORE" ) { 00464 unset( $options[$k] ); 00465 $ignoreClause = true; 00466 } 00467 } 00468 00469 // translate MySQL INSERT IGNORE to something SQL Server can use 00470 // example: 00471 // MySQL: INSERT IGNORE INTO user_groups (ug_user,ug_group) VALUES ('1','sysop') 00472 // MSSQL: IF NOT EXISTS (SELECT * FROM user_groups WHERE ug_user = '1') INSERT INTO user_groups (ug_user,ug_group) VALUES ('1','sysop') 00473 if ( $ignoreClause ) { 00474 $prival = $a[$keys[0]]; 00475 $sqlPre .= "IF NOT EXISTS (SELECT * FROM $table WHERE $keys[0] = '$prival')"; 00476 } 00477 00478 // Build the actual query 00479 $sql = $sqlPre . 'INSERT ' . implode( ' ', $options ) . 00480 " INTO $table (" . implode( ',', $keys ) . ") $identityClause VALUES ("; 00481 00482 $first = true; 00483 foreach ( $a as $value ) { 00484 if ( $first ) { 00485 $first = false; 00486 } else { 00487 $sql .= ','; 00488 } 00489 if ( is_string( $value ) ) { 00490 $sql .= $this->addQuotes( $value ); 00491 } elseif ( is_null( $value ) ) { 00492 $sql .= 'null'; 00493 } elseif ( is_array( $value ) || is_object( $value ) ) { 00494 if ( is_object( $value ) && strtolower( get_class( $value ) ) == 'blob' ) { 00495 $sql .= $this->addQuotes( $value ); 00496 } else { 00497 $sql .= $this->addQuotes( serialize( $value ) ); 00498 } 00499 } else { 00500 $sql .= $value; 00501 } 00502 } 00503 $sql .= ')' . $sqlPost; 00504 00505 // Run the query 00506 $ret = sqlsrv_query( $this->mConn, $sql ); 00507 00508 if ( $ret === false ) { 00509 throw new DBQueryError( $this, $this->getErrors(), $this->lastErrno(), $sql, $fname ); 00510 } elseif ( $ret != null ) { 00511 // remember number of rows affected 00512 $this->mAffectedRows = sqlsrv_rows_affected( $ret ); 00513 if ( !is_null( $identity ) ) { 00514 // then we want to get the identity column value we were assigned and save it off 00515 $row = sqlsrv_fetch_object( $ret ); 00516 $this->mInsertId = $row->$identity; 00517 } 00518 sqlsrv_free_stmt( $ret ); 00519 continue; 00520 } 00521 $allOk = false; 00522 } 00523 return $allOk; 00524 } 00525 00542 function insertSelect( $destTable, $srcTable, $varMap, $conds, $fname = __METHOD__, 00543 $insertOptions = array(), $selectOptions = array() ) { 00544 $ret = parent::insertSelect( $destTable, $srcTable, $varMap, $conds, $fname, $insertOptions, $selectOptions ); 00545 00546 if ( $ret === false ) { 00547 throw new DBQueryError( $this, $this->getErrors(), $this->lastErrno(), /*$sql*/ '', $fname ); 00548 } elseif ( $ret != null ) { 00549 // remember number of rows affected 00550 $this->mAffectedRows = sqlsrv_rows_affected( $ret ); 00551 return $ret; 00552 } 00553 return null; 00554 } 00555 00560 function nextSequenceValue( $seqName ) { 00561 if ( !$this->tableExists( 'sequence_' . $seqName ) ) { 00562 sqlsrv_query( $this->mConn, "CREATE TABLE [sequence_$seqName] (id INT NOT NULL IDENTITY PRIMARY KEY, junk varchar(10) NULL)" ); 00563 } 00564 sqlsrv_query( $this->mConn, "INSERT INTO [sequence_$seqName] (junk) VALUES ('')" ); 00565 $ret = sqlsrv_query( $this->mConn, "SELECT TOP 1 id FROM [sequence_$seqName] ORDER BY id DESC" ); 00566 $row = sqlsrv_fetch_array( $ret, SQLSRV_FETCH_ASSOC );// KEEP ASSOC THERE, weird weird bug dealing with the return value if you don't 00567 00568 sqlsrv_free_stmt( $ret ); 00569 $this->mInsertId = $row['id']; 00570 return $row['id']; 00571 } 00572 00577 function currentSequenceValue( $seqName ) { 00578 $ret = sqlsrv_query( $this->mConn, "SELECT TOP 1 id FROM [sequence_$seqName] ORDER BY id DESC" ); 00579 if ( $ret !== false ) { 00580 $row = sqlsrv_fetch_array( $ret ); 00581 sqlsrv_free_stmt( $ret ); 00582 return $row['id']; 00583 } else { 00584 return $this->nextSequenceValue( $seqName ); 00585 } 00586 } 00587 00588 # Returns the size of a text field, or -1 for "unlimited" 00589 function textFieldSize( $table, $field ) { 00590 $table = $this->tableName( $table ); 00591 $sql = "SELECT CHARACTER_MAXIMUM_LENGTH,DATA_TYPE FROM INFORMATION_SCHEMA.Columns 00592 WHERE TABLE_NAME = '$table' AND COLUMN_NAME = '$field'"; 00593 $res = $this->query( $sql ); 00594 $row = $this->fetchRow( $res ); 00595 $size = -1; 00596 if ( strtolower( $row['DATA_TYPE'] ) != 'text' ) { 00597 $size = $row['CHARACTER_MAXIMUM_LENGTH']; 00598 } 00599 return $size; 00600 } 00601 00610 function limitResult( $sql, $limit, $offset = false ) { 00611 if ( $offset === false || $offset == 0 ) { 00612 if ( strpos( $sql, "SELECT" ) === false ) { 00613 return "TOP {$limit} " . $sql; 00614 } else { 00615 return preg_replace( '/\bSELECT(\s*DISTINCT)?\b/Dsi', 'SELECT$1 TOP ' . $limit, $sql, 1 ); 00616 } 00617 } else { 00618 $sql = ' 00619 SELECT * FROM ( 00620 SELECT sub2.*, ROW_NUMBER() OVER(ORDER BY sub2.line2) AS line3 FROM ( 00621 SELECT 1 AS line2, sub1.* FROM (' . $sql . ') AS sub1 00622 ) as sub2 00623 ) AS sub3 00624 WHERE line3 BETWEEN ' . ( $offset + 1 ) . ' AND ' . ( $offset + $limit ); 00625 return $sql; 00626 } 00627 } 00628 00629 // If there is a limit clause, parse it, strip it, and pass the remaining sql through limitResult() 00630 // with the appropriate parameters. Not the prettiest solution, but better than building a whole new parser. 00631 // This exists becase there are still too many extensions that don't use dynamic sql generation. 00632 function LimitToTopN( $sql ) { 00633 // Matches: LIMIT {[offset,] row_count | row_count OFFSET offset} 00634 $pattern = '/\bLIMIT\s+((([0-9]+)\s*,\s*)?([0-9]+)(\s+OFFSET\s+([0-9]+))?)/i'; 00635 if ( preg_match( $pattern, $sql, $matches ) ) { 00636 // row_count = $matches[4] 00637 $row_count = $matches[4]; 00638 // offset = $matches[3] OR $matches[6] 00639 $offset = $matches[3] or 00640 $offset = $matches[6] or 00641 $offset = false; 00642 00643 // strip the matching LIMIT clause out 00644 $sql = str_replace( $matches[0], '', $sql ); 00645 return $this->limitResult( $sql, $row_count, $offset ); 00646 } 00647 return $sql; 00648 } 00649 00650 function timestamp( $ts = 0 ) { 00651 return wfTimestamp( TS_ISO_8601, $ts ); 00652 } 00653 00657 public function getSoftwareLink() { 00658 return "[{{int:version-db-mssql-url}} MS SQL Server]"; 00659 } 00660 00664 function getServerVersion() { 00665 $server_info = sqlsrv_server_info( $this->mConn ); 00666 $version = 'Error'; 00667 if ( isset( $server_info['SQLServerVersion'] ) ) { 00668 $version = $server_info['SQLServerVersion']; 00669 } 00670 return $version; 00671 } 00672 00673 function tableExists( $table, $fname = __METHOD__, $schema = false ) { 00674 $res = sqlsrv_query( $this->mConn, "SELECT * FROM information_schema.tables 00675 WHERE table_type='BASE TABLE' AND table_name = '$table'" ); 00676 if ( $res === false ) { 00677 print "Error in tableExists query: " . $this->getErrors(); 00678 return false; 00679 } 00680 if ( sqlsrv_fetch( $res ) ) { 00681 return true; 00682 } else { 00683 return false; 00684 } 00685 } 00686 00691 function fieldExists( $table, $field, $fname = __METHOD__ ) { 00692 $table = $this->tableName( $table ); 00693 $res = sqlsrv_query( $this->mConn, "SELECT DATA_TYPE FROM INFORMATION_SCHEMA.Columns 00694 WHERE TABLE_NAME = '$table' AND COLUMN_NAME = '$field'" ); 00695 if ( $res === false ) { 00696 print "Error in fieldExists query: " . $this->getErrors(); 00697 return false; 00698 } 00699 if ( sqlsrv_fetch( $res ) ) { 00700 return true; 00701 } else { 00702 return false; 00703 } 00704 } 00705 00706 function fieldInfo( $table, $field ) { 00707 $table = $this->tableName( $table ); 00708 $res = sqlsrv_query( $this->mConn, "SELECT * FROM INFORMATION_SCHEMA.Columns 00709 WHERE TABLE_NAME = '$table' AND COLUMN_NAME = '$field'" ); 00710 if ( $res === false ) { 00711 print "Error in fieldInfo query: " . $this->getErrors(); 00712 return false; 00713 } 00714 $meta = $this->fetchRow( $res ); 00715 if ( $meta ) { 00716 return new MssqlField( $meta ); 00717 } 00718 return false; 00719 } 00720 00724 protected function doBegin( $fname = __METHOD__ ) { 00725 sqlsrv_begin_transaction( $this->mConn ); 00726 $this->mTrxLevel = 1; 00727 } 00728 00732 protected function doCommit( $fname = __METHOD__ ) { 00733 sqlsrv_commit( $this->mConn ); 00734 $this->mTrxLevel = 0; 00735 } 00736 00741 protected function doRollback( $fname = __METHOD__ ) { 00742 sqlsrv_rollback( $this->mConn ); 00743 $this->mTrxLevel = 0; 00744 } 00745 00754 private function escapeIdentifier( $identifier ) { 00755 if ( strlen( $identifier ) == 0 ) { 00756 throw new MWException( "An identifier must not be empty" ); 00757 } 00758 if ( strlen( $identifier ) > 128 ) { 00759 throw new MWException( "The identifier '$identifier' is too long (max. 128)" ); 00760 } 00761 if ( ( strpos( $identifier, '[' ) !== false ) || ( strpos( $identifier, ']' ) !== false ) ) { 00762 // It may be allowed if you quoted with double quotation marks, but that would break if QUOTED_IDENTIFIER is OFF 00763 throw new MWException( "You can't use square brackers in the identifier '$identifier'" ); 00764 } 00765 return "[$identifier]"; 00766 } 00767 00773 function initial_setup( $dbName, $newUser, $loginPassword ) { 00774 $dbName = $this->escapeIdentifier( $dbName ); 00775 00776 // It is not clear what can be used as a login, 00777 // From http://msdn.microsoft.com/en-us/library/ms173463.aspx 00778 // a sysname may be the same as an identifier. 00779 $newUser = $this->escapeIdentifier( $newUser ); 00780 $loginPassword = $this->addQuotes( $loginPassword ); 00781 00782 $this->doQuery( "CREATE DATABASE $dbName;" ); 00783 $this->doQuery( "USE $dbName;" ); 00784 $this->doQuery( "CREATE SCHEMA $dbName;" ); 00785 $this->doQuery( " 00786 CREATE 00787 LOGIN $newUser 00788 WITH 00789 PASSWORD=$loginPassword 00790 ; 00791 " ); 00792 $this->doQuery( " 00793 CREATE 00794 USER $newUser 00795 FOR 00796 LOGIN $newUser 00797 WITH 00798 DEFAULT_SCHEMA=$dbName 00799 ; 00800 " ); 00801 $this->doQuery( " 00802 GRANT 00803 BACKUP DATABASE, 00804 BACKUP LOG, 00805 CREATE DEFAULT, 00806 CREATE FUNCTION, 00807 CREATE PROCEDURE, 00808 CREATE RULE, 00809 CREATE TABLE, 00810 CREATE VIEW, 00811 CREATE FULLTEXT CATALOG 00812 ON 00813 DATABASE::$dbName 00814 TO $newUser 00815 ; 00816 " ); 00817 $this->doQuery( " 00818 GRANT 00819 CONTROL 00820 ON 00821 SCHEMA::$dbName 00822 TO $newUser 00823 ; 00824 " ); 00825 } 00826 00827 function encodeBlob( $b ) { 00828 // we can't have zero's and such, this is a simple encoding to make sure we don't barf 00829 return base64_encode( $b ); 00830 } 00831 00832 function decodeBlob( $b ) { 00833 // we can't have zero's and such, this is a simple encoding to make sure we don't barf 00834 return base64_decode( $b ); 00835 } 00836 00841 function tableNamesWithUseIndexOrJOIN( $tables, $use_index = array(), $join_conds = array() ) { 00842 $ret = array(); 00843 $retJOIN = array(); 00844 $use_index_safe = is_array( $use_index ) ? $use_index : array(); 00845 $join_conds_safe = is_array( $join_conds ) ? $join_conds : array(); 00846 foreach ( $tables as $table ) { 00847 // Is there a JOIN and INDEX clause for this table? 00848 if ( isset( $join_conds_safe[$table] ) && isset( $use_index_safe[$table] ) ) { 00849 $tableClause = $join_conds_safe[$table][0] . ' ' . $this->tableName( $table ); 00850 $tableClause .= ' ' . $this->useIndexClause( implode( ',', (array)$use_index_safe[$table] ) ); 00851 $tableClause .= ' ON (' . $this->makeList( (array)$join_conds_safe[$table][1], LIST_AND ) . ')'; 00852 $retJOIN[] = $tableClause; 00853 // Is there an INDEX clause? 00854 } elseif ( isset( $use_index_safe[$table] ) ) { 00855 $tableClause = $this->tableName( $table ); 00856 $tableClause .= ' ' . $this->useIndexClause( implode( ',', (array)$use_index_safe[$table] ) ); 00857 $ret[] = $tableClause; 00858 // Is there a JOIN clause? 00859 } elseif ( isset( $join_conds_safe[$table] ) ) { 00860 $tableClause = $join_conds_safe[$table][0] . ' ' . $this->tableName( $table ); 00861 $tableClause .= ' ON (' . $this->makeList( (array)$join_conds_safe[$table][1], LIST_AND ) . ')'; 00862 $retJOIN[] = $tableClause; 00863 } else { 00864 $tableClause = $this->tableName( $table ); 00865 $ret[] = $tableClause; 00866 } 00867 } 00868 // We can't separate explicit JOIN clauses with ',', use ' ' for those 00869 $straightJoins = !empty( $ret ) ? implode( ',', $ret ) : ""; 00870 $otherJoins = !empty( $retJOIN ) ? implode( ' ', $retJOIN ) : ""; 00871 // Compile our final table clause 00872 return implode( ' ', array( $straightJoins, $otherJoins ) ); 00873 } 00874 00875 function strencode( $s ) { # Should not be called by us 00876 return str_replace( "'", "''", $s ); 00877 } 00878 00879 function addQuotes( $s ) { 00880 if ( $s instanceof Blob ) { 00881 return "'" . $s->fetch( $s ) . "'"; 00882 } else { 00883 return parent::addQuotes( $s ); 00884 } 00885 } 00886 00887 public function addIdentifierQuotes( $s ) { 00888 // http://msdn.microsoft.com/en-us/library/aa223962.aspx 00889 return '[' . $s . ']'; 00890 } 00891 00892 public function isQuotedIdentifier( $name ) { 00893 return $name[0] == '[' && substr( $name, -1, 1 ) == ']'; 00894 } 00895 00896 function selectDB( $db ) { 00897 return ( $this->query( "SET DATABASE $db" ) !== false ); 00898 } 00899 00907 function makeSelectOptions( $options ) { 00908 $tailOpts = ''; 00909 $startOpts = ''; 00910 00911 $noKeyOptions = array(); 00912 foreach ( $options as $key => $option ) { 00913 if ( is_numeric( $key ) ) { 00914 $noKeyOptions[$option] = true; 00915 } 00916 } 00917 00918 $tailOpts .= $this->makeGroupByWithHaving( $options ); 00919 00920 $tailOpts .= $this->makeOrderBy( $options ); 00921 00922 if ( isset( $noKeyOptions['DISTINCT'] ) && isset( $noKeyOptions['DISTINCTROW'] ) ) { 00923 $startOpts .= 'DISTINCT'; 00924 } 00925 00926 // we want this to be compatible with the output of parent::makeSelectOptions() 00927 return array( $startOpts, '', $tailOpts, '' ); 00928 } 00929 00934 function getType() { 00935 return 'mssql'; 00936 } 00937 00938 function buildConcat( $stringList ) { 00939 return implode( ' + ', $stringList ); 00940 } 00941 00942 public function getSearchEngine() { 00943 return "SearchMssql"; 00944 } 00945 00951 public function getInfinity() { 00952 return '3000-01-31 00:00:00.000'; 00953 } 00954 00955 } // end DatabaseMssql class 00956 00962 class MssqlField implements Field { 00963 private $name, $tablename, $default, $max_length, $nullable, $type; 00964 function __construct( $info ) { 00965 $this->name = $info['COLUMN_NAME']; 00966 $this->tablename = $info['TABLE_NAME']; 00967 $this->default = $info['COLUMN_DEFAULT']; 00968 $this->max_length = $info['CHARACTER_MAXIMUM_LENGTH']; 00969 $this->nullable = !( strtolower( $info['IS_NULLABLE'] ) == 'no' ); 00970 $this->type = $info['DATA_TYPE']; 00971 } 00972 00973 function name() { 00974 return $this->name; 00975 } 00976 00977 function tableName() { 00978 return $this->tableName; 00979 } 00980 00981 function defaultValue() { 00982 return $this->default; 00983 } 00984 00985 function maxLength() { 00986 return $this->max_length; 00987 } 00988 00989 function isNullable() { 00990 return $this->nullable; 00991 } 00992 00993 function type() { 00994 return $this->type; 00995 } 00996 } 00997 01004 class MssqlResult { 01005 01006 public function __construct( $queryresult = false ) { 01007 $this->mCursor = 0; 01008 $this->mRows = array(); 01009 $this->mNumFields = sqlsrv_num_fields( $queryresult ); 01010 $this->mFieldMeta = sqlsrv_field_metadata( $queryresult ); 01011 01012 $rows = sqlsrv_fetch_array( $queryresult, SQLSRV_FETCH_ASSOC ); 01013 01014 foreach ( $rows as $row ) { 01015 if ( $row !== null ) { 01016 foreach ( $row as $k => $v ) { 01017 if ( is_object( $v ) && method_exists( $v, 'format' ) ) {// DateTime Object 01018 $row[$k] = $v->format( "Y-m-d\TH:i:s\Z" ); 01019 } 01020 } 01021 $this->mRows[] = $row;// read results into memory, cursors are not supported 01022 } 01023 } 01024 $this->mRowCount = count( $this->mRows ); 01025 sqlsrv_free_stmt( $queryresult ); 01026 } 01027 01028 private function array_to_obj( $array, &$obj ) { 01029 foreach ( $array as $key => $value ) { 01030 if ( is_array( $value ) ) { 01031 $obj->$key = new stdClass(); 01032 $this->array_to_obj( $value, $obj->$key ); 01033 } else { 01034 if ( !empty( $key ) ) { 01035 $obj->$key = $value; 01036 } 01037 } 01038 } 01039 return $obj; 01040 } 01041 01042 public function fetch( $mode = SQLSRV_FETCH_BOTH, $object_class = 'stdClass' ) { 01043 if ( $this->mCursor >= $this->mRowCount || $this->mRowCount == 0 ) { 01044 return false; 01045 } 01046 $arrNum = array(); 01047 if ( $mode == SQLSRV_FETCH_NUMERIC || $mode == SQLSRV_FETCH_BOTH ) { 01048 foreach ( $this->mRows[$this->mCursor] as $value ) { 01049 $arrNum[] = $value; 01050 } 01051 } 01052 switch ( $mode ) { 01053 case SQLSRV_FETCH_ASSOC: 01054 $ret = $this->mRows[$this->mCursor]; 01055 break; 01056 case SQLSRV_FETCH_NUMERIC: 01057 $ret = $arrNum; 01058 break; 01059 case 'OBJECT': 01060 $o = new $object_class; 01061 $ret = $this->array_to_obj( $this->mRows[$this->mCursor], $o ); 01062 break; 01063 case SQLSRV_FETCH_BOTH: 01064 default: 01065 $ret = $this->mRows[$this->mCursor] + $arrNum; 01066 break; 01067 } 01068 01069 $this->mCursor++; 01070 return $ret; 01071 } 01072 01073 public function get( $pos, $fld ) { 01074 return $this->mRows[$pos][$fld]; 01075 } 01076 01077 public function numrows() { 01078 return $this->mRowCount; 01079 } 01080 01081 public function seek( $iRow ) { 01082 $this->mCursor = min( $iRow, $this->mRowCount ); 01083 } 01084 01085 public function numfields() { 01086 return $this->mNumFields; 01087 } 01088 01089 public function fieldname( $nr ) { 01090 $arrKeys = array_keys( $this->mRows[0] ); 01091 return $arrKeys[$nr]; 01092 } 01093 01094 public function fieldtype( $nr ) { 01095 $i = 0; 01096 $intType = -1; 01097 foreach ( $this->mFieldMeta as $meta ) { 01098 if ( $nr == $i ) { 01099 $intType = $meta['Type']; 01100 break; 01101 } 01102 $i++; 01103 } 01104 // http://msdn.microsoft.com/en-us/library/cc296183.aspx contains type table 01105 switch ( $intType ) { 01106 case SQLSRV_SQLTYPE_BIGINT: 01107 $strType = 'bigint'; 01108 break; 01109 case SQLSRV_SQLTYPE_BINARY: 01110 $strType = 'binary'; 01111 break; 01112 case SQLSRV_SQLTYPE_BIT: 01113 $strType = 'bit'; 01114 break; 01115 case SQLSRV_SQLTYPE_CHAR: 01116 $strType = 'char'; 01117 break; 01118 case SQLSRV_SQLTYPE_DATETIME: 01119 $strType = 'datetime'; 01120 break; 01121 case SQLSRV_SQLTYPE_DECIMAL: // ($precision, $scale) 01122 $strType = 'decimal'; 01123 break; 01124 case SQLSRV_SQLTYPE_FLOAT: 01125 $strType = 'float'; 01126 break; 01127 case SQLSRV_SQLTYPE_IMAGE: 01128 $strType = 'image'; 01129 break; 01130 case SQLSRV_SQLTYPE_INT: 01131 $strType = 'int'; 01132 break; 01133 case SQLSRV_SQLTYPE_MONEY: 01134 $strType = 'money'; 01135 break; 01136 case SQLSRV_SQLTYPE_NCHAR: // ($charCount): 01137 $strType = 'nchar'; 01138 break; 01139 case SQLSRV_SQLTYPE_NUMERIC: // ($precision, $scale): 01140 $strType = 'numeric'; 01141 break; 01142 case SQLSRV_SQLTYPE_NVARCHAR: // ($charCount) 01143 $strType = 'nvarchar'; 01144 break; 01145 // case SQLSRV_SQLTYPE_NVARCHAR('max'): 01146 // $strType = 'nvarchar(MAX)'; 01147 // break; 01148 case SQLSRV_SQLTYPE_NTEXT: 01149 $strType = 'ntext'; 01150 break; 01151 case SQLSRV_SQLTYPE_REAL: 01152 $strType = 'real'; 01153 break; 01154 case SQLSRV_SQLTYPE_SMALLDATETIME: 01155 $strType = 'smalldatetime'; 01156 break; 01157 case SQLSRV_SQLTYPE_SMALLINT: 01158 $strType = 'smallint'; 01159 break; 01160 case SQLSRV_SQLTYPE_SMALLMONEY: 01161 $strType = 'smallmoney'; 01162 break; 01163 case SQLSRV_SQLTYPE_TEXT: 01164 $strType = 'text'; 01165 break; 01166 case SQLSRV_SQLTYPE_TIMESTAMP: 01167 $strType = 'timestamp'; 01168 break; 01169 case SQLSRV_SQLTYPE_TINYINT: 01170 $strType = 'tinyint'; 01171 break; 01172 case SQLSRV_SQLTYPE_UNIQUEIDENTIFIER: 01173 $strType = 'uniqueidentifier'; 01174 break; 01175 case SQLSRV_SQLTYPE_UDT: 01176 $strType = 'UDT'; 01177 break; 01178 case SQLSRV_SQLTYPE_VARBINARY: // ($byteCount) 01179 $strType = 'varbinary'; 01180 break; 01181 // case SQLSRV_SQLTYPE_VARBINARY('max'): 01182 // $strType = 'varbinary(MAX)'; 01183 // break; 01184 case SQLSRV_SQLTYPE_VARCHAR: // ($charCount) 01185 $strType = 'varchar'; 01186 break; 01187 // case SQLSRV_SQLTYPE_VARCHAR('max'): 01188 // $strType = 'varchar(MAX)'; 01189 // break; 01190 case SQLSRV_SQLTYPE_XML: 01191 $strType = 'xml'; 01192 break; 01193 default: 01194 $strType = $intType; 01195 } 01196 return $strType; 01197 } 01198 01199 public function free() { 01200 unset( $this->mRows ); 01201 } 01202 }