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
;
25 use Wikimedia\Rdbms\DBQueryError
;
26 use Wikimedia\Rdbms\DBConnectionError
;
29 * Class for setting up the MediaWiki database using Microsoft SQL Server.
34 class MssqlInstaller
extends DatabaseInstaller
{
36 protected $globalNames = [
43 'wgDBWindowsAuthentication',
46 protected $internalDefaults = [
47 '_InstallUser' => 'sa',
48 '_InstallWindowsAuthentication' => 'sqlauth',
49 '_WebWindowsAuthentication' => 'sqlauth',
52 // SQL Server 2005 RTM
53 // @todo Are SQL Express version numbers different?)
54 public $minimumVersion = '9.00.1399';
56 // These are schema-level privs
57 // Note: the web user will be created will full permissions if possible, this permission
58 // list is only used if we are unable to grant full permissions.
59 public $webUserPrivs = [
70 public function getName() {
77 public function isCompiled() {
78 return self
::checkExtension( 'sqlsrv' );
84 public function getConnectForm() {
85 if ( $this->getVar( '_InstallWindowsAuthentication' ) == 'windowsauth' ) {
86 $displayStyle = 'display: none;';
88 $displayStyle = 'display: block;';
91 return $this->getTextBox(
95 $this->parent
->getHelpBox( 'config-db-host-help' )
97 Html
::openElement( 'fieldset' ) .
98 Html
::element( 'legend', [], wfMessage( 'config-db-wiki-settings' )->text() ) .
99 $this->getTextBox( 'wgDBname', 'config-db-name', [ 'dir' => 'ltr' ],
100 $this->parent
->getHelpBox( 'config-db-name-help' ) ) .
101 $this->getTextBox( 'wgDBmwschema', 'config-db-schema', [ 'dir' => 'ltr' ],
102 $this->parent
->getHelpBox( 'config-db-schema-help' ) ) .
103 $this->getTextBox( 'wgDBprefix', 'config-db-prefix', [ 'dir' => 'ltr' ],
104 $this->parent
->getHelpBox( 'config-db-prefix-help' ) ) .
105 Html
::closeElement( 'fieldset' ) .
106 Html
::openElement( 'fieldset' ) .
107 Html
::element( 'legend', [], wfMessage( 'config-db-install-account' )->text() ) .
108 $this->getRadioSet( [
109 'var' => '_InstallWindowsAuthentication',
110 'label' => 'config-mssql-auth',
111 'itemLabelPrefix' => 'config-mssql-',
112 'values' => [ 'sqlauth', 'windowsauth' ],
115 'class' => 'showHideRadio',
116 'rel' => 'dbCredentialBox',
119 'class' => 'hideShowRadio',
120 'rel' => 'dbCredentialBox',
123 'help' => $this->parent
->getHelpBox( 'config-mssql-install-auth' )
125 Html
::openElement( 'div', [ 'id' => 'dbCredentialBox', 'style' => $displayStyle ] ) .
128 'config-db-username',
130 $this->parent
->getHelpBox( 'config-db-install-username' )
132 $this->getPasswordBox(
134 'config-db-password',
136 $this->parent
->getHelpBox( 'config-db-install-password' )
138 Html
::closeElement( 'div' ) .
139 Html
::closeElement( 'fieldset' );
142 public function submitConnectForm() {
143 // Get variables from the request.
144 $newValues = $this->setVarsFromRequest( [
152 $status = Status
::newGood();
153 if ( !strlen( $newValues['wgDBserver'] ) ) {
154 $status->fatal( 'config-missing-db-host' );
156 if ( !strlen( $newValues['wgDBname'] ) ) {
157 $status->fatal( 'config-missing-db-name' );
158 } elseif ( !preg_match( '/^[a-z0-9_]+$/i', $newValues['wgDBname'] ) ) {
159 $status->fatal( 'config-invalid-db-name', $newValues['wgDBname'] );
161 if ( !preg_match( '/^[a-z0-9_]*$/i', $newValues['wgDBmwschema'] ) ) {
162 $status->fatal( 'config-invalid-schema', $newValues['wgDBmwschema'] );
164 if ( !preg_match( '/^[a-z0-9_]*$/i', $newValues['wgDBprefix'] ) ) {
165 $status->fatal( 'config-invalid-db-prefix', $newValues['wgDBprefix'] );
167 if ( !$status->isOK() ) {
171 // Check for blank schema and remap to dbo
172 if ( $newValues['wgDBmwschema'] === '' ) {
173 $this->setVar( 'wgDBmwschema', 'dbo' );
177 $this->setVarsFromRequest( [
180 '_InstallWindowsAuthentication'
184 $status = $this->getConnection();
185 if ( !$status->isOK() ) {
189 * @var $conn Database
191 $conn = $status->value
;
194 $version = $conn->getServerVersion();
195 if ( version_compare( $version, $this->minimumVersion
) < 0 ) {
196 return Status
::newFatal( 'config-mssql-old', $this->minimumVersion
, $version );
205 public function openConnection() {
206 global $wgDBWindowsAuthentication;
207 $status = Status
::newGood();
208 $user = $this->getVar( '_InstallUser' );
209 $password = $this->getVar( '_InstallPassword' );
211 if ( $this->getVar( '_InstallWindowsAuthentication' ) == 'windowsauth' ) {
212 // Use Windows authentication for this connection
213 $wgDBWindowsAuthentication = true;
215 $wgDBWindowsAuthentication = false;
219 $db = Database
::factory( 'mssql', [
220 'host' => $this->getVar( 'wgDBserver' ),
221 'port' => $this->getVar( 'wgDBport' ),
223 'password' => $password,
226 'schema' => $this->getVar( 'wgDBmwschema' ),
227 'tablePrefix' => $this->getVar( 'wgDBprefix' ) ] );
228 $db->prepareStatements( false );
229 $db->scrollableCursor( false );
230 $status->value
= $db;
231 } catch ( DBConnectionError
$e ) {
232 $status->fatal( 'config-connection-error', $e->getMessage() );
238 public function preUpgrade() {
239 global $wgDBuser, $wgDBpassword;
241 $status = $this->getConnection();
242 if ( !$status->isOK() ) {
243 $this->parent
->showStatusError( $status );
248 * @var $conn Database
250 $conn = $status->value
;
251 $conn->selectDB( $this->getVar( 'wgDBname' ) );
253 # Normal user and password are selected after this step, so for now
254 # just copy these two
255 $wgDBuser = $this->getVar( '_InstallUser' );
256 $wgDBpassword = $this->getVar( '_InstallPassword' );
260 * Return true if the install user can create accounts
264 public function canCreateAccounts() {
265 $status = $this->getConnection();
266 if ( !$status->isOK() ) {
269 /** @var $conn Database */
270 $conn = $status->value
;
272 // We need the server-level ALTER ANY LOGIN permission to create new accounts
273 $res = $conn->query( "SELECT permission_name FROM sys.fn_my_permissions( NULL, 'SERVER' )" );
275 'ALTER ANY LOGIN' => false,
276 'CONTROL SERVER' => false,
279 foreach ( $res as $row ) {
280 $serverPrivs[$row->permission_name
] = true;
283 if ( !$serverPrivs['ALTER ANY LOGIN'] ) {
287 // Check to ensure we can grant everything needed as well
288 // We can't actually tell if we have WITH GRANT OPTION for a given permission, so we assume we do
289 // and just check for the permission
290 // https://technet.microsoft.com/en-us/library/ms178569.aspx
291 // The following array sets up which permissions imply whatever permissions we specify
293 // schema database server
294 'DELETE' => [ 'DELETE', 'CONTROL SERVER' ],
295 'EXECUTE' => [ 'EXECUTE', 'CONTROL SERVER' ],
296 'INSERT' => [ 'INSERT', 'CONTROL SERVER' ],
297 'SELECT' => [ 'SELECT', 'CONTROL SERVER' ],
298 'UPDATE' => [ 'UPDATE', 'CONTROL SERVER' ],
301 $grantOptions = array_flip( $this->webUserPrivs
);
303 // Check for schema and db-level permissions, but only if the schema/db exists
304 $schemaPrivs = $dbPrivs = [
312 $dbPrivs['ALTER ANY USER'] = false;
314 if ( $this->databaseExists( $this->getVar( 'wgDBname' ) ) ) {
315 $conn->selectDB( $this->getVar( 'wgDBname' ) );
316 $res = $conn->query( "SELECT permission_name FROM sys.fn_my_permissions( NULL, 'DATABASE' )" );
318 foreach ( $res as $row ) {
319 $dbPrivs[$row->permission_name
] = true;
322 // If the db exists, we need ALTER ANY USER privs on it to make a new user
323 if ( !$dbPrivs['ALTER ANY USER'] ) {
327 if ( $this->schemaExists( $this->getVar( 'wgDBmwschema' ) ) ) {
328 // wgDBmwschema is validated to only contain alphanumeric + underscore, so this is safe
329 $res = $conn->query( "SELECT permission_name FROM sys.fn_my_permissions( "
330 . "'{$this->getVar( 'wgDBmwschema' )}', 'SCHEMA' )" );
332 foreach ( $res as $row ) {
333 $schemaPrivs[$row->permission_name
] = true;
338 // Now check all the grants we'll need to be doing to see if we can
339 foreach ( $this->webUserPrivs
as $permission ) {
340 if ( ( isset( $schemaPrivs[$permission] ) && $schemaPrivs[$permission] )
341 ||
( isset( $dbPrivs[$implied[$permission][0]] )
342 && $dbPrivs[$implied[$permission][0]] )
343 ||
( isset( $serverPrivs[$implied[$permission][1]] )
344 && $serverPrivs[$implied[$permission][1]] )
346 unset( $grantOptions[$permission] );
350 if ( count( $grantOptions ) ) {
351 // Can't grant everything
361 public function getSettingsForm() {
362 if ( $this->canCreateAccounts() ) {
363 $noCreateMsg = false;
365 $noCreateMsg = 'config-db-web-no-create-privs';
368 $wrapperStyle = $this->getVar( '_SameAccount' ) ?
'display: none' : '';
369 $displayStyle = $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth'
372 $s = Html
::openElement( 'fieldset' ) .
373 Html
::element( 'legend', [], wfMessage( 'config-db-web-account' )->text() ) .
375 '_SameAccount', 'config-db-web-account-same',
376 [ 'class' => 'hideShowRadio', 'rel' => 'dbOtherAccount' ]
378 Html
::openElement( 'div', [ 'id' => 'dbOtherAccount', 'style' => $wrapperStyle ] ) .
379 $this->getRadioSet( [
380 'var' => '_WebWindowsAuthentication',
381 'label' => 'config-mssql-auth',
382 'itemLabelPrefix' => 'config-mssql-',
383 'values' => [ 'sqlauth', 'windowsauth' ],
386 'class' => 'showHideRadio',
387 'rel' => 'dbCredentialBox',
390 'class' => 'hideShowRadio',
391 'rel' => 'dbCredentialBox',
394 'help' => $this->parent
->getHelpBox( 'config-mssql-web-auth' )
396 Html
::openElement( 'div', [ 'id' => 'dbCredentialBox', 'style' => $displayStyle ] ) .
397 $this->getTextBox( 'wgDBuser', 'config-db-username' ) .
398 $this->getPasswordBox( 'wgDBpassword', 'config-db-password' ) .
399 Html
::closeElement( 'div' );
401 if ( $noCreateMsg ) {
402 $s .= $this->parent
->getWarningBox( wfMessage( $noCreateMsg )->plain() );
404 $s .= $this->getCheckBox( '_CreateDBAccount', 'config-db-web-create' );
407 $s .= Html
::closeElement( 'div' ) . Html
::closeElement( 'fieldset' );
415 public function submitSettingsForm() {
416 $this->setVarsFromRequest( [
421 '_WebWindowsAuthentication'
424 if ( $this->getVar( '_SameAccount' ) ) {
425 $this->setVar( '_WebWindowsAuthentication', $this->getVar( '_InstallWindowsAuthentication' ) );
426 $this->setVar( 'wgDBuser', $this->getVar( '_InstallUser' ) );
427 $this->setVar( 'wgDBpassword', $this->getVar( '_InstallPassword' ) );
430 if ( $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth' ) {
431 $this->setVar( 'wgDBuser', '' );
432 $this->setVar( 'wgDBpassword', '' );
433 $this->setVar( 'wgDBWindowsAuthentication', true );
435 $this->setVar( 'wgDBWindowsAuthentication', false );
438 if ( $this->getVar( '_CreateDBAccount' )
439 && $this->getVar( '_WebWindowsAuthentication' ) == 'sqlauth'
440 && strval( $this->getVar( 'wgDBpassword' ) ) == ''
442 return Status
::newFatal( 'config-db-password-empty', $this->getVar( 'wgDBuser' ) );
445 // Validate the create checkbox
446 $canCreate = $this->canCreateAccounts();
448 $this->setVar( '_CreateDBAccount', false );
451 $create = $this->getVar( '_CreateDBAccount' );
455 // Test the web account
456 $user = $this->getVar( 'wgDBuser' );
457 $password = $this->getVar( 'wgDBpassword' );
459 if ( $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth' ) {
460 $user = 'windowsauth';
461 $password = 'windowsauth';
465 Database
::factory( 'mssql', [
466 'host' => $this->getVar( 'wgDBserver' ),
468 'password' => $password,
471 'tablePrefix' => $this->getVar( 'wgDBprefix' ),
472 'schema' => $this->getVar( 'wgDBmwschema' ),
474 } catch ( DBConnectionError
$e ) {
475 return Status
::newFatal( 'config-connection-error', $e->getMessage() );
479 return Status
::newGood();
482 public function preInstall() {
483 # Add our user callback to installSteps, right before the tables are created.
486 'callback' => [ $this, 'setupUser' ],
488 $this->parent
->addInstallStep( $callback, 'tables' );
494 public function setupDatabase() {
495 $status = $this->getConnection();
496 if ( !$status->isOK() ) {
499 /** @var Database $conn */
500 $conn = $status->value
;
501 $dbName = $this->getVar( 'wgDBname' );
502 $schemaName = $this->getVar( 'wgDBmwschema' );
503 if ( !$this->databaseExists( $dbName ) ) {
505 "CREATE DATABASE " . $conn->addIdentifierQuotes( $dbName ),
509 $conn->selectDB( $dbName );
510 if ( !$this->schemaExists( $schemaName ) ) {
512 "CREATE SCHEMA " . $conn->addIdentifierQuotes( $schemaName ),
516 if ( !$this->catalogExists( $schemaName ) ) {
518 "CREATE FULLTEXT CATALOG " . $conn->addIdentifierQuotes( $schemaName ),
522 $this->setupSchemaVars();
530 public function setupUser() {
531 $dbUser = $this->getVar( 'wgDBuser' );
532 if ( $dbUser == $this->getVar( '_InstallUser' )
533 ||
( $this->getVar( '_InstallWindowsAuthentication' ) == 'windowsauth'
534 && $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth' ) ) {
535 return Status
::newGood();
537 $status = $this->getConnection();
538 if ( !$status->isOK() ) {
542 $this->setupSchemaVars();
543 $dbName = $this->getVar( 'wgDBname' );
544 $this->db
->selectDB( $dbName );
545 $password = $this->getVar( 'wgDBpassword' );
546 $schemaName = $this->getVar( 'wgDBmwschema' );
548 if ( $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth' ) {
549 $dbUser = 'windowsauth';
550 $password = 'windowsauth';
553 if ( $this->getVar( '_CreateDBAccount' ) ) {
556 $tryToCreate = false;
559 $escUser = $this->db
->addIdentifierQuotes( $dbUser );
560 $escDb = $this->db
->addIdentifierQuotes( $dbName );
561 $escSchema = $this->db
->addIdentifierQuotes( $schemaName );
562 $grantableNames = [];
563 if ( $tryToCreate ) {
564 $escPass = $this->db
->addQuotes( $password );
566 if ( !$this->loginExists( $dbUser ) ) {
569 $this->db
->selectDB( 'master' );
570 $logintype = $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth'
572 : "WITH PASSWORD = $escPass";
573 $this->db
->query( "CREATE LOGIN $escUser $logintype" );
574 $this->db
->selectDB( $dbName );
575 $this->db
->query( "CREATE USER $escUser FOR LOGIN $escUser WITH DEFAULT_SCHEMA = $escSchema" );
577 $grantableNames[] = $dbUser;
578 } catch ( DBQueryError
$dqe ) {
579 $this->db
->rollback();
580 $status->warning( 'config-install-user-create-failed', $dbUser, $dqe->getMessage() );
582 } elseif ( !$this->userExists( $dbUser ) ) {
585 $this->db
->selectDB( $dbName );
586 $this->db
->query( "CREATE USER $escUser FOR LOGIN $escUser WITH DEFAULT_SCHEMA = $escSchema" );
588 $grantableNames[] = $dbUser;
589 } catch ( DBQueryError
$dqe ) {
590 $this->db
->rollback();
591 $status->warning( 'config-install-user-create-failed', $dbUser, $dqe->getMessage() );
594 $status->warning( 'config-install-user-alreadyexists', $dbUser );
595 $grantableNames[] = $dbUser;
599 // Try to grant to all the users we know exist or we were able to create
600 $this->db
->selectDB( $dbName );
601 foreach ( $grantableNames as $name ) {
603 // First try to grant full permissions
605 'BACKUP DATABASE', 'BACKUP LOG', 'CREATE FUNCTION', 'CREATE PROCEDURE',
606 'CREATE TABLE', 'CREATE VIEW', 'CREATE FULLTEXT CATALOG', 'SHOWPLAN'
608 $fullPrivList = implode( ', ', $fullPrivArr );
610 $this->db
->query( "GRANT $fullPrivList ON DATABASE :: $escDb TO $escUser", __METHOD__
);
611 $this->db
->query( "GRANT CONTROL ON SCHEMA :: $escSchema TO $escUser", __METHOD__
);
613 } catch ( DBQueryError
$dqe ) {
614 // If that fails, try to grant the limited subset specified in $this->webUserPrivs
616 $privList = implode( ', ', $this->webUserPrivs
);
617 $this->db
->rollback();
619 $this->db
->query( "GRANT $privList ON SCHEMA :: $escSchema TO $escUser", __METHOD__
);
621 } catch ( DBQueryError
$dqe ) {
622 $this->db
->rollback();
623 $status->fatal( 'config-install-user-grant-failed', $dbUser, $dqe->getMessage() );
625 // Also try to grant SHOWPLAN on the db, but don't fail if we can't
626 // (just makes a couple things in mediawiki run slower since
627 // we have to run SELECT COUNT(*) instead of getting the query plan)
629 $this->db
->query( "GRANT SHOWPLAN ON DATABASE :: $escDb TO $escUser", __METHOD__
);
630 } catch ( DBQueryError
$dqe ) {
638 public function createTables() {
639 $status = parent
::createTables();
641 // Do last-minute stuff like fulltext indexes (since they can't be inside a transaction)
642 if ( $status->isOK() ) {
643 $searchindex = $this->db
->tableName( 'searchindex' );
644 $schema = $this->db
->addIdentifierQuotes( $this->getVar( 'wgDBmwschema' ) );
646 $this->db
->query( "CREATE FULLTEXT INDEX ON $searchindex (si_title, si_text) "
647 . "KEY INDEX si_page ON $schema" );
648 } catch ( DBQueryError
$dqe ) {
649 $status->fatal( 'config-install-tables-failed', $dqe->getMessage() );
656 public function getGlobalDefaults() {
657 // The default $wgDBmwschema is null, which breaks Postgres and other DBMSes that require
658 // the use of a schema, so we need to set it here
659 return array_merge( parent
::getGlobalDefaults(), [
660 'wgDBmwschema' => 'mediawiki',
665 * Try to see if the login exists
666 * @param string $user Username to check
669 private function loginExists( $user ) {
670 $res = $this->db
->selectField( 'sys.sql_logins', 1, [ 'name' => $user ] );
675 * Try to see if the user account exists
676 * We assume we already have the appropriate database selected
677 * @param string $user Username to check
680 private function userExists( $user ) {
681 $res = $this->db
->selectField( 'sys.sysusers', 1, [ 'name' => $user ] );
686 * Try to see if a given database exists
687 * @param string $dbName Database name to check
690 private function databaseExists( $dbName ) {
691 $res = $this->db
->selectField( 'sys.databases', 1, [ 'name' => $dbName ] );
696 * Try to see if a given schema exists
697 * We assume we already have the appropriate database selected
698 * @param string $schemaName Schema name to check
701 private function schemaExists( $schemaName ) {
702 $res = $this->db
->selectField( 'sys.schemas', 1, [ 'name' => $schemaName ] );
707 * Try to see if a given fulltext catalog exists
708 * We assume we already have the appropriate database selected
709 * @param string $catalogName Catalog name to check
712 private function catalogExists( $catalogName ) {
713 $res = $this->db
->selectField( 'sys.fulltext_catalogs', 1, [ 'name' => $catalogName ] );
718 * Get variables to substitute into tables.sql and the SQL patch files.
722 public function getSchemaVars() {
724 'wgDBname' => $this->getVar( 'wgDBname' ),
725 'wgDBmwschema' => $this->getVar( 'wgDBmwschema' ),
726 'wgDBuser' => $this->getVar( 'wgDBuser' ),
727 'wgDBpassword' => $this->getVar( 'wgDBpassword' ),
731 public function getLocalSettings() {
732 $schema = LocalSettingsGenerator
::escapePhpString( $this->getVar( 'wgDBmwschema' ) );
733 $prefix = LocalSettingsGenerator
::escapePhpString( $this->getVar( 'wgDBprefix' ) );
734 $windowsauth = $this->getVar( 'wgDBWindowsAuthentication' ) ?
'true' : 'false';
736 return "# MSSQL specific settings
737 \$wgDBWindowsAuthentication = {$windowsauth};
738 \$wgDBmwschema = \"{$schema}\";
739 \$wgDBprefix = \"{$prefix}\";";