3 * Microsoft SQL Server-specific installer.
5 * This program is free software; you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation; either version 2 of the License, or
8 * (at your option) any later version.
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
15 * You should have received a copy of the GNU General Public License along
16 * with this program; if not, write to the Free Software Foundation, Inc.,
17 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
18 * http://www.gnu.org/copyleft/gpl.html
24 use Wikimedia\Rdbms\Database
;
27 * Class for setting up the MediaWiki database using Microsoft SQL Server.
32 class MssqlInstaller
extends DatabaseInstaller
{
34 protected $globalNames = [
41 'wgDBWindowsAuthentication',
44 protected $internalDefaults = [
45 '_InstallUser' => 'sa',
46 '_InstallWindowsAuthentication' => 'sqlauth',
47 '_WebWindowsAuthentication' => 'sqlauth',
50 // SQL Server 2005 RTM
51 // @todo Are SQL Express version numbers different?)
52 public $minimumVersion = '9.00.1399';
54 // These are schema-level privs
55 // Note: the web user will be created will full permissions if possible, this permission
56 // list is only used if we are unable to grant full permissions.
57 public $webUserPrivs = [
68 public function getName() {
75 public function isCompiled() {
76 return self
::checkExtension( 'sqlsrv' );
82 public function getConnectForm() {
83 if ( $this->getVar( '_InstallWindowsAuthentication' ) == 'windowsauth' ) {
84 $displayStyle = 'display: none;';
86 $displayStyle = 'display: block;';
89 return $this->getTextBox(
93 $this->parent
->getHelpBox( 'config-db-host-help' )
95 Html
::openElement( 'fieldset' ) .
96 Html
::element( 'legend', [], wfMessage( 'config-db-wiki-settings' )->text() ) .
97 $this->getTextBox( 'wgDBname', 'config-db-name', [ 'dir' => 'ltr' ],
98 $this->parent
->getHelpBox( 'config-db-name-help' ) ) .
99 $this->getTextBox( 'wgDBmwschema', 'config-db-schema', [ 'dir' => 'ltr' ],
100 $this->parent
->getHelpBox( 'config-db-schema-help' ) ) .
101 $this->getTextBox( 'wgDBprefix', 'config-db-prefix', [ 'dir' => 'ltr' ],
102 $this->parent
->getHelpBox( 'config-db-prefix-help' ) ) .
103 Html
::closeElement( 'fieldset' ) .
104 Html
::openElement( 'fieldset' ) .
105 Html
::element( 'legend', [], wfMessage( 'config-db-install-account' )->text() ) .
106 $this->getRadioSet( [
107 'var' => '_InstallWindowsAuthentication',
108 'label' => 'config-mssql-auth',
109 'itemLabelPrefix' => 'config-mssql-',
110 'values' => [ 'sqlauth', 'windowsauth' ],
113 'class' => 'showHideRadio',
114 'rel' => 'dbCredentialBox',
117 'class' => 'hideShowRadio',
118 'rel' => 'dbCredentialBox',
121 'help' => $this->parent
->getHelpBox( 'config-mssql-install-auth' )
123 Html
::openElement( 'div', [ 'id' => 'dbCredentialBox', 'style' => $displayStyle ] ) .
126 'config-db-username',
128 $this->parent
->getHelpBox( 'config-db-install-username' )
130 $this->getPasswordBox(
132 'config-db-password',
134 $this->parent
->getHelpBox( 'config-db-install-password' )
136 Html
::closeElement( 'div' ) .
137 Html
::closeElement( 'fieldset' );
140 public function submitConnectForm() {
141 // Get variables from the request.
142 $newValues = $this->setVarsFromRequest( [
150 $status = Status
::newGood();
151 if ( !strlen( $newValues['wgDBserver'] ) ) {
152 $status->fatal( 'config-missing-db-host' );
154 if ( !strlen( $newValues['wgDBname'] ) ) {
155 $status->fatal( 'config-missing-db-name' );
156 } elseif ( !preg_match( '/^[a-z0-9_]+$/i', $newValues['wgDBname'] ) ) {
157 $status->fatal( 'config-invalid-db-name', $newValues['wgDBname'] );
159 if ( !preg_match( '/^[a-z0-9_]*$/i', $newValues['wgDBmwschema'] ) ) {
160 $status->fatal( 'config-invalid-schema', $newValues['wgDBmwschema'] );
162 if ( !preg_match( '/^[a-z0-9_]*$/i', $newValues['wgDBprefix'] ) ) {
163 $status->fatal( 'config-invalid-db-prefix', $newValues['wgDBprefix'] );
165 if ( !$status->isOK() ) {
169 // Check for blank schema and remap to dbo
170 if ( $newValues['wgDBmwschema'] === '' ) {
171 $this->setVar( 'wgDBmwschema', 'dbo' );
175 $this->setVarsFromRequest( [
178 '_InstallWindowsAuthentication'
182 $status = $this->getConnection();
183 if ( !$status->isOK() ) {
187 * @var $conn Database
189 $conn = $status->value
;
192 $version = $conn->getServerVersion();
193 if ( version_compare( $version, $this->minimumVersion
) < 0 ) {
194 return Status
::newFatal( 'config-mssql-old', $this->minimumVersion
, $version );
203 public function openConnection() {
204 global $wgDBWindowsAuthentication;
205 $status = Status
::newGood();
206 $user = $this->getVar( '_InstallUser' );
207 $password = $this->getVar( '_InstallPassword' );
209 if ( $this->getVar( '_InstallWindowsAuthentication' ) == 'windowsauth' ) {
210 // Use Windows authentication for this connection
211 $wgDBWindowsAuthentication = true;
213 $wgDBWindowsAuthentication = false;
217 $db = Database
::factory( 'mssql', [
218 'host' => $this->getVar( 'wgDBserver' ),
219 'port' => $this->getVar( 'wgDBport' ),
221 'password' => $password,
224 'schema' => $this->getVar( 'wgDBmwschema' ),
225 'tablePrefix' => $this->getVar( 'wgDBprefix' ) ] );
226 $db->prepareStatements( false );
227 $db->scrollableCursor( false );
228 $status->value
= $db;
229 } catch ( DBConnectionError
$e ) {
230 $status->fatal( 'config-connection-error', $e->getMessage() );
236 public function preUpgrade() {
237 global $wgDBuser, $wgDBpassword;
239 $status = $this->getConnection();
240 if ( !$status->isOK() ) {
241 $this->parent
->showStatusError( $status );
246 * @var $conn Database
248 $conn = $status->value
;
249 $conn->selectDB( $this->getVar( 'wgDBname' ) );
251 # Normal user and password are selected after this step, so for now
252 # just copy these two
253 $wgDBuser = $this->getVar( '_InstallUser' );
254 $wgDBpassword = $this->getVar( '_InstallPassword' );
258 * Return true if the install user can create accounts
262 public function canCreateAccounts() {
263 $status = $this->getConnection();
264 if ( !$status->isOK() ) {
267 /** @var $conn Database */
268 $conn = $status->value
;
270 // We need the server-level ALTER ANY LOGIN permission to create new accounts
271 $res = $conn->query( "SELECT permission_name FROM sys.fn_my_permissions( NULL, 'SERVER' )" );
273 'ALTER ANY LOGIN' => false,
274 'CONTROL SERVER' => false,
277 foreach ( $res as $row ) {
278 $serverPrivs[$row->permission_name
] = true;
281 if ( !$serverPrivs['ALTER ANY LOGIN'] ) {
285 // Check to ensure we can grant everything needed as well
286 // We can't actually tell if we have WITH GRANT OPTION for a given permission, so we assume we do
287 // and just check for the permission
288 // https://technet.microsoft.com/en-us/library/ms178569.aspx
289 // The following array sets up which permissions imply whatever permissions we specify
291 // schema database server
292 'DELETE' => [ 'DELETE', 'CONTROL SERVER' ],
293 'EXECUTE' => [ 'EXECUTE', 'CONTROL SERVER' ],
294 'INSERT' => [ 'INSERT', 'CONTROL SERVER' ],
295 'SELECT' => [ 'SELECT', 'CONTROL SERVER' ],
296 'UPDATE' => [ 'UPDATE', 'CONTROL SERVER' ],
299 $grantOptions = array_flip( $this->webUserPrivs
);
301 // Check for schema and db-level permissions, but only if the schema/db exists
302 $schemaPrivs = $dbPrivs = [
310 $dbPrivs['ALTER ANY USER'] = false;
312 if ( $this->databaseExists( $this->getVar( 'wgDBname' ) ) ) {
313 $conn->selectDB( $this->getVar( 'wgDBname' ) );
314 $res = $conn->query( "SELECT permission_name FROM sys.fn_my_permissions( NULL, 'DATABASE' )" );
316 foreach ( $res as $row ) {
317 $dbPrivs[$row->permission_name
] = true;
320 // If the db exists, we need ALTER ANY USER privs on it to make a new user
321 if ( !$dbPrivs['ALTER ANY USER'] ) {
325 if ( $this->schemaExists( $this->getVar( 'wgDBmwschema' ) ) ) {
326 // wgDBmwschema is validated to only contain alphanumeric + underscore, so this is safe
327 $res = $conn->query( "SELECT permission_name FROM sys.fn_my_permissions( "
328 . "'{$this->getVar( 'wgDBmwschema' )}', 'SCHEMA' )" );
330 foreach ( $res as $row ) {
331 $schemaPrivs[$row->permission_name
] = true;
336 // Now check all the grants we'll need to be doing to see if we can
337 foreach ( $this->webUserPrivs
as $permission ) {
338 if ( ( isset( $schemaPrivs[$permission] ) && $schemaPrivs[$permission] )
339 ||
( isset( $dbPrivs[$implied[$permission][0]] )
340 && $dbPrivs[$implied[$permission][0]] )
341 ||
( isset( $serverPrivs[$implied[$permission][1]] )
342 && $serverPrivs[$implied[$permission][1]] )
344 unset( $grantOptions[$permission] );
348 if ( count( $grantOptions ) ) {
349 // Can't grant everything
359 public function getSettingsForm() {
360 if ( $this->canCreateAccounts() ) {
361 $noCreateMsg = false;
363 $noCreateMsg = 'config-db-web-no-create-privs';
366 $wrapperStyle = $this->getVar( '_SameAccount' ) ?
'display: none' : '';
367 $displayStyle = $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth'
370 $s = Html
::openElement( 'fieldset' ) .
371 Html
::element( 'legend', [], wfMessage( 'config-db-web-account' )->text() ) .
373 '_SameAccount', 'config-db-web-account-same',
374 [ 'class' => 'hideShowRadio', 'rel' => 'dbOtherAccount' ]
376 Html
::openElement( 'div', [ 'id' => 'dbOtherAccount', 'style' => $wrapperStyle ] ) .
377 $this->getRadioSet( [
378 'var' => '_WebWindowsAuthentication',
379 'label' => 'config-mssql-auth',
380 'itemLabelPrefix' => 'config-mssql-',
381 'values' => [ 'sqlauth', 'windowsauth' ],
384 'class' => 'showHideRadio',
385 'rel' => 'dbCredentialBox',
388 'class' => 'hideShowRadio',
389 'rel' => 'dbCredentialBox',
392 'help' => $this->parent
->getHelpBox( 'config-mssql-web-auth' )
394 Html
::openElement( 'div', [ 'id' => 'dbCredentialBox', 'style' => $displayStyle ] ) .
395 $this->getTextBox( 'wgDBuser', 'config-db-username' ) .
396 $this->getPasswordBox( 'wgDBpassword', 'config-db-password' ) .
397 Html
::closeElement( 'div' );
399 if ( $noCreateMsg ) {
400 $s .= $this->parent
->getWarningBox( wfMessage( $noCreateMsg )->plain() );
402 $s .= $this->getCheckBox( '_CreateDBAccount', 'config-db-web-create' );
405 $s .= Html
::closeElement( 'div' ) . Html
::closeElement( 'fieldset' );
413 public function submitSettingsForm() {
414 $this->setVarsFromRequest( [
419 '_WebWindowsAuthentication'
422 if ( $this->getVar( '_SameAccount' ) ) {
423 $this->setVar( '_WebWindowsAuthentication', $this->getVar( '_InstallWindowsAuthentication' ) );
424 $this->setVar( 'wgDBuser', $this->getVar( '_InstallUser' ) );
425 $this->setVar( 'wgDBpassword', $this->getVar( '_InstallPassword' ) );
428 if ( $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth' ) {
429 $this->setVar( 'wgDBuser', '' );
430 $this->setVar( 'wgDBpassword', '' );
431 $this->setVar( 'wgDBWindowsAuthentication', true );
433 $this->setVar( 'wgDBWindowsAuthentication', false );
436 if ( $this->getVar( '_CreateDBAccount' )
437 && $this->getVar( '_WebWindowsAuthentication' ) == 'sqlauth'
438 && strval( $this->getVar( 'wgDBpassword' ) ) == ''
440 return Status
::newFatal( 'config-db-password-empty', $this->getVar( 'wgDBuser' ) );
443 // Validate the create checkbox
444 $canCreate = $this->canCreateAccounts();
446 $this->setVar( '_CreateDBAccount', false );
449 $create = $this->getVar( '_CreateDBAccount' );
453 // Test the web account
454 $user = $this->getVar( 'wgDBuser' );
455 $password = $this->getVar( 'wgDBpassword' );
457 if ( $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth' ) {
458 $user = 'windowsauth';
459 $password = 'windowsauth';
463 Database
::factory( 'mssql', [
464 'host' => $this->getVar( 'wgDBserver' ),
466 'password' => $password,
469 'tablePrefix' => $this->getVar( 'wgDBprefix' ),
470 'schema' => $this->getVar( 'wgDBmwschema' ),
472 } catch ( DBConnectionError
$e ) {
473 return Status
::newFatal( 'config-connection-error', $e->getMessage() );
477 return Status
::newGood();
480 public function preInstall() {
481 # Add our user callback to installSteps, right before the tables are created.
484 'callback' => [ $this, 'setupUser' ],
486 $this->parent
->addInstallStep( $callback, 'tables' );
492 public function setupDatabase() {
493 $status = $this->getConnection();
494 if ( !$status->isOK() ) {
497 /** @var Database $conn */
498 $conn = $status->value
;
499 $dbName = $this->getVar( 'wgDBname' );
500 $schemaName = $this->getVar( 'wgDBmwschema' );
501 if ( !$this->databaseExists( $dbName ) ) {
503 "CREATE DATABASE " . $conn->addIdentifierQuotes( $dbName ),
507 $conn->selectDB( $dbName );
508 if ( !$this->schemaExists( $schemaName ) ) {
510 "CREATE SCHEMA " . $conn->addIdentifierQuotes( $schemaName ),
514 if ( !$this->catalogExists( $schemaName ) ) {
516 "CREATE FULLTEXT CATALOG " . $conn->addIdentifierQuotes( $schemaName ),
520 $this->setupSchemaVars();
528 public function setupUser() {
529 $dbUser = $this->getVar( 'wgDBuser' );
530 if ( $dbUser == $this->getVar( '_InstallUser' )
531 ||
( $this->getVar( '_InstallWindowsAuthentication' ) == 'windowsauth'
532 && $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth' ) ) {
533 return Status
::newGood();
535 $status = $this->getConnection();
536 if ( !$status->isOK() ) {
540 $this->setupSchemaVars();
541 $dbName = $this->getVar( 'wgDBname' );
542 $this->db
->selectDB( $dbName );
543 $password = $this->getVar( 'wgDBpassword' );
544 $schemaName = $this->getVar( 'wgDBmwschema' );
546 if ( $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth' ) {
547 $dbUser = 'windowsauth';
548 $password = 'windowsauth';
551 if ( $this->getVar( '_CreateDBAccount' ) ) {
554 $tryToCreate = false;
557 $escUser = $this->db
->addIdentifierQuotes( $dbUser );
558 $escDb = $this->db
->addIdentifierQuotes( $dbName );
559 $escSchema = $this->db
->addIdentifierQuotes( $schemaName );
560 $grantableNames = [];
561 if ( $tryToCreate ) {
562 $escPass = $this->db
->addQuotes( $password );
564 if ( !$this->loginExists( $dbUser ) ) {
567 $this->db
->selectDB( 'master' );
568 $logintype = $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth'
570 : "WITH PASSWORD = $escPass";
571 $this->db
->query( "CREATE LOGIN $escUser $logintype" );
572 $this->db
->selectDB( $dbName );
573 $this->db
->query( "CREATE USER $escUser FOR LOGIN $escUser WITH DEFAULT_SCHEMA = $escSchema" );
575 $grantableNames[] = $dbUser;
576 } catch ( DBQueryError
$dqe ) {
577 $this->db
->rollback();
578 $status->warning( 'config-install-user-create-failed', $dbUser, $dqe->getText() );
580 } elseif ( !$this->userExists( $dbUser ) ) {
583 $this->db
->selectDB( $dbName );
584 $this->db
->query( "CREATE USER $escUser FOR LOGIN $escUser WITH DEFAULT_SCHEMA = $escSchema" );
586 $grantableNames[] = $dbUser;
587 } catch ( DBQueryError
$dqe ) {
588 $this->db
->rollback();
589 $status->warning( 'config-install-user-create-failed', $dbUser, $dqe->getText() );
592 $status->warning( 'config-install-user-alreadyexists', $dbUser );
593 $grantableNames[] = $dbUser;
597 // Try to grant to all the users we know exist or we were able to create
598 $this->db
->selectDB( $dbName );
599 foreach ( $grantableNames as $name ) {
601 // First try to grant full permissions
603 'BACKUP DATABASE', 'BACKUP LOG', 'CREATE FUNCTION', 'CREATE PROCEDURE',
604 'CREATE TABLE', 'CREATE VIEW', 'CREATE FULLTEXT CATALOG', 'SHOWPLAN'
606 $fullPrivList = implode( ', ', $fullPrivArr );
608 $this->db
->query( "GRANT $fullPrivList ON DATABASE :: $escDb TO $escUser", __METHOD__
);
609 $this->db
->query( "GRANT CONTROL ON SCHEMA :: $escSchema TO $escUser", __METHOD__
);
611 } catch ( DBQueryError
$dqe ) {
612 // If that fails, try to grant the limited subset specified in $this->webUserPrivs
614 $privList = implode( ', ', $this->webUserPrivs
);
615 $this->db
->rollback();
617 $this->db
->query( "GRANT $privList ON SCHEMA :: $escSchema TO $escUser", __METHOD__
);
619 } catch ( DBQueryError
$dqe ) {
620 $this->db
->rollback();
621 $status->fatal( 'config-install-user-grant-failed', $dbUser, $dqe->getText() );
623 // Also try to grant SHOWPLAN on the db, but don't fail if we can't
624 // (just makes a couple things in mediawiki run slower since
625 // we have to run SELECT COUNT(*) instead of getting the query plan)
627 $this->db
->query( "GRANT SHOWPLAN ON DATABASE :: $escDb TO $escUser", __METHOD__
);
628 } catch ( DBQueryError
$dqe ) {
636 public function createTables() {
637 $status = parent
::createTables();
639 // Do last-minute stuff like fulltext indexes (since they can't be inside a transaction)
640 if ( $status->isOK() ) {
641 $searchindex = $this->db
->tableName( 'searchindex' );
642 $schema = $this->db
->addIdentifierQuotes( $this->getVar( 'wgDBmwschema' ) );
644 $this->db
->query( "CREATE FULLTEXT INDEX ON $searchindex (si_title, si_text) "
645 . "KEY INDEX si_page ON $schema" );
646 } catch ( DBQueryError
$dqe ) {
647 $status->fatal( 'config-install-tables-failed', $dqe->getText() );
654 public function getGlobalDefaults() {
655 // The default $wgDBmwschema is null, which breaks Postgres and other DBMSes that require
656 // the use of a schema, so we need to set it here
657 return array_merge( parent
::getGlobalDefaults(), [
658 'wgDBmwschema' => 'mediawiki',
663 * Try to see if the login exists
664 * @param string $user Username to check
667 private function loginExists( $user ) {
668 $res = $this->db
->selectField( 'sys.sql_logins', 1, [ 'name' => $user ] );
673 * Try to see if the user account exists
674 * We assume we already have the appropriate database selected
675 * @param string $user Username to check
678 private function userExists( $user ) {
679 $res = $this->db
->selectField( 'sys.sysusers', 1, [ 'name' => $user ] );
684 * Try to see if a given database exists
685 * @param string $dbName Database name to check
688 private function databaseExists( $dbName ) {
689 $res = $this->db
->selectField( 'sys.databases', 1, [ 'name' => $dbName ] );
694 * Try to see if a given schema exists
695 * We assume we already have the appropriate database selected
696 * @param string $schemaName Schema name to check
699 private function schemaExists( $schemaName ) {
700 $res = $this->db
->selectField( 'sys.schemas', 1, [ 'name' => $schemaName ] );
705 * Try to see if a given fulltext catalog exists
706 * We assume we already have the appropriate database selected
707 * @param string $catalogName Catalog name to check
710 private function catalogExists( $catalogName ) {
711 $res = $this->db
->selectField( 'sys.fulltext_catalogs', 1, [ 'name' => $catalogName ] );
716 * Get variables to substitute into tables.sql and the SQL patch files.
720 public function getSchemaVars() {
722 'wgDBname' => $this->getVar( 'wgDBname' ),
723 'wgDBmwschema' => $this->getVar( 'wgDBmwschema' ),
724 'wgDBuser' => $this->getVar( 'wgDBuser' ),
725 'wgDBpassword' => $this->getVar( 'wgDBpassword' ),
729 public function getLocalSettings() {
730 $schema = LocalSettingsGenerator
::escapePhpString( $this->getVar( 'wgDBmwschema' ) );
731 $prefix = LocalSettingsGenerator
::escapePhpString( $this->getVar( 'wgDBprefix' ) );
732 $windowsauth = $this->getVar( 'wgDBWindowsAuthentication' ) ?
'true' : 'false';
734 return "# MSSQL specific settings
735 \$wgDBWindowsAuthentication = {$windowsauth};
736 \$wgDBmwschema = \"{$schema}\";
737 \$wgDBprefix = \"{$prefix}\";";