Skizzerz has uploaded a new change for review.
https://gerrit.wikimedia.org/r/285139
Change subject: Update Microsoft SQL Server schema
......................................................................
Update Microsoft SQL Server schema
The MSSQL schema is now brought in-line with the MySQL schema.
Additionally, various issues that prevented successful installation via
MSSQL were fixed, notably with respect to creating bits of the database
should other bits already exist.
Additional MSSQL bugfixes will come in separate patches, as they are less
related to the schema/install process.
Change-Id: If3eea625499d3cb14abba40f528208173067a53a
---
M includes/db/DatabaseMssql.php
M includes/installer/MssqlInstaller.php
M includes/installer/MssqlUpdater.php
A maintenance/mssql/archives/patch-add-cl_collation_ext_index.sql
A maintenance/mssql/archives/patch-bot_passwords.sql
A maintenance/mssql/archives/patch-drop-rc_cur_time.sql
A maintenance/mssql/archives/patch-drop-user_options.sql
A maintenance/mssql/archives/patch-il_from_namespace.sql
A maintenance/mssql/archives/patch-kill-cl_collation_index.sql
A maintenance/mssql/archives/patch-pl_from_namespace.sql
A maintenance/mssql/archives/patch-pp_sortkey.sql
A maintenance/mssql/archives/patch-tl_from_namespace.sql
M maintenance/mssql/tables.sql
13 files changed, 111 insertions(+), 23 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/mediawiki/core
refs/changes/39/285139/1
diff --git a/includes/db/DatabaseMssql.php b/includes/db/DatabaseMssql.php
index ce34537..a63a221 100644
--- a/includes/db/DatabaseMssql.php
+++ b/includes/db/DatabaseMssql.php
@@ -276,7 +276,15 @@
$res = $res->result;
}
- return sqlsrv_num_rows( $res );
+ $ret = sqlsrv_num_rows( $res );
+
+ if ( $ret === false ) {
+ // we cannot get an amount of rows from this cursor type
+ // has_rows returns bool true/false if the result has
rows
+ $ret = (int)sqlsrv_has_rows( $res );
+ }
+
+ return $ret;
}
/**
@@ -696,6 +704,12 @@
$row = $ret->fetchObject();
if ( is_object( $row ) ) {
$this->mInsertId = $row->$identity;
+
+ // it seems that mAffectedRows is -1
sometimes when OUTPUT INSERTED.identity is used
+ // if we got an identity back, we know
for sure a row was affected, so adjust that here
+ if ( $this->mAffectedRows == -1 ) {
+ $this->mAffectedRows = 1;
+ }
}
}
}
diff --git a/includes/installer/MssqlInstaller.php
b/includes/installer/MssqlInstaller.php
index c6b8960..62cd883 100644
--- a/includes/installer/MssqlInstaller.php
+++ b/includes/installer/MssqlInstaller.php
@@ -500,19 +500,19 @@
"CREATE DATABASE " .
$conn->addIdentifierQuotes( $dbName ),
__METHOD__
);
- $conn->selectDB( $dbName );
- if ( !$this->schemaExists( $schemaName ) ) {
- $conn->query(
- "CREATE SCHEMA " .
$conn->addIdentifierQuotes( $schemaName ),
- __METHOD__
- );
- }
- if ( !$this->catalogExists( $schemaName ) ) {
- $conn->query(
- "CREATE FULLTEXT CATALOG " .
$conn->addIdentifierQuotes( $schemaName ),
- __METHOD__
- );
- }
+ }
+ $conn->selectDB( $dbName );
+ if ( !$this->schemaExists( $schemaName ) ) {
+ $conn->query(
+ "CREATE SCHEMA " . $conn->addIdentifierQuotes(
$schemaName ),
+ __METHOD__
+ );
+ }
+ if ( !$this->catalogExists( $schemaName ) ) {
+ $conn->query(
+ "CREATE FULLTEXT CATALOG " .
$conn->addIdentifierQuotes( $schemaName ),
+ __METHOD__
+ );
}
$this->setupSchemaVars();
diff --git a/includes/installer/MssqlUpdater.php
b/includes/installer/MssqlUpdater.php
index bdaf4c8..48e8c7d 100644
--- a/includes/installer/MssqlUpdater.php
+++ b/includes/installer/MssqlUpdater.php
@@ -69,6 +69,16 @@
[ 'dropTable', 'msg_resource_links' ],
[ 'dropTable', 'msg_resource' ],
[ 'addField', 'watchlist', 'wl_id',
'patch-watchlist-wl_id.sql' ],
+ [ 'dropField', 'mwuser', 'user_options',
'patch-drop-user_options.sql' ],
+ [ 'addTable', 'bot_passwords',
'patch-bot_passwords.sql' ],
+ [ 'addField', 'pagelinks', 'pl_from_namespace',
'patch-pl_from_namespace.sql' ],
+ [ 'addField', 'templatelinks', 'tl_from_namespace',
'patch-tl_from_namespace.sql' ],
+ [ 'addField', 'imagelinks', 'il_from_namespace',
'patch-il_from_namespace.sql' ],
+ [ 'dropIndex', 'categorylinks', 'cl_collation',
'patch-kill-cl_collation_index.sql' ],
+ [ 'addIndex', 'categorylinks', 'cl_collation_ext',
+ 'patch-add-cl_collation_ext_index.sql' ],
+ [ 'dropField', 'recentchanges', 'rc_cur_time',
'patch-drop-rc_cur_time.sql' ],
+ [ 'addField', 'page_props', 'pp_sortkey',
'patch-pp_sortkey.sql' ],
];
}
diff --git a/maintenance/mssql/archives/patch-add-cl_collation_ext_index.sql
b/maintenance/mssql/archives/patch-add-cl_collation_ext_index.sql
new file mode 100644
index 0000000..8137dc6
--- /dev/null
+++ b/maintenance/mssql/archives/patch-add-cl_collation_ext_index.sql
@@ -0,0 +1,2 @@
+-- @since 1.27
+CREATE INDEX /*i*/cl_collation_ext ON /*_*/categorylinks (cl_collation, cl_to,
cl_type, cl_from);
diff --git a/maintenance/mssql/archives/patch-bot_passwords.sql
b/maintenance/mssql/archives/patch-bot_passwords.sql
new file mode 100644
index 0000000..4246b85
--- /dev/null
+++ b/maintenance/mssql/archives/patch-bot_passwords.sql
@@ -0,0 +1,13 @@
+--
+-- This table contains a user's bot passwords: passwords that allow access to
+-- the account via the API with limited rights.
+--
+CREATE TABLE /*_*/bot_passwords (
+ bp_user int NOT NULL REFERENCES /*_*/mwuser(user_id) ON DELETE CASCADE,
+ bp_app_id nvarchar(32) NOT NULL,
+ bp_password nvarchar(255) NOT NULL,
+ bp_token nvarchar(255) NOT NULL,
+ bp_restrictions nvarchar(max) NOT NULL,
+ bp_grants nvarchar(max) NOT NULL,
+ PRIMARY KEY (bp_user, bp_app_id)
+);
diff --git a/maintenance/mssql/archives/patch-drop-rc_cur_time.sql
b/maintenance/mssql/archives/patch-drop-rc_cur_time.sql
new file mode 100644
index 0000000..f1bc9e8
--- /dev/null
+++ b/maintenance/mssql/archives/patch-drop-rc_cur_time.sql
@@ -0,0 +1,2 @@
+-- rc_cur_time is no longer used, delete the field
+ALTER TABLE /*$wgDBprefix*/recentchanges DROP COLUMN rc_cur_time;
\ No newline at end of file
diff --git a/maintenance/mssql/archives/patch-drop-user_options.sql
b/maintenance/mssql/archives/patch-drop-user_options.sql
new file mode 100644
index 0000000..b162656
--- /dev/null
+++ b/maintenance/mssql/archives/patch-drop-user_options.sql
@@ -0,0 +1 @@
+ALTER TABLE /*_*/mwuser DROP COLUMN user_options;
\ No newline at end of file
diff --git a/maintenance/mssql/archives/patch-il_from_namespace.sql
b/maintenance/mssql/archives/patch-il_from_namespace.sql
new file mode 100644
index 0000000..e4ac98f
--- /dev/null
+++ b/maintenance/mssql/archives/patch-il_from_namespace.sql
@@ -0,0 +1,4 @@
+ALTER TABLE /*_*/imagelinks
+ ADD il_from_namespace int NOT NULL default 0;
+
+CREATE INDEX /*i*/il_backlinks_namespace ON /*_*/imagelinks
(il_from_namespace,il_to,il_from);
\ No newline at end of file
diff --git a/maintenance/mssql/archives/patch-kill-cl_collation_index.sql
b/maintenance/mssql/archives/patch-kill-cl_collation_index.sql
new file mode 100644
index 0000000..7f75a62
--- /dev/null
+++ b/maintenance/mssql/archives/patch-kill-cl_collation_index.sql
@@ -0,0 +1,7 @@
+--
+-- Kill cl_collation index.
+-- @since 1.27
+--
+
+DROP INDEX /*i*/cl_collation ON /*_*/categorylinks;
+
diff --git a/maintenance/mssql/archives/patch-pl_from_namespace.sql
b/maintenance/mssql/archives/patch-pl_from_namespace.sql
new file mode 100644
index 0000000..4828331
--- /dev/null
+++ b/maintenance/mssql/archives/patch-pl_from_namespace.sql
@@ -0,0 +1,4 @@
+ALTER TABLE /*_*/pagelinks
+ ADD pl_from_namespace int NOT NULL default 0;
+
+CREATE INDEX /*i*/pl_backlinks_namespace ON /*_*/pagelinks
(pl_from_namespace,pl_namespace,pl_title,pl_from);
diff --git a/maintenance/mssql/archives/patch-pp_sortkey.sql
b/maintenance/mssql/archives/patch-pp_sortkey.sql
new file mode 100644
index 0000000..b13b605
--- /dev/null
+++ b/maintenance/mssql/archives/patch-pp_sortkey.sql
@@ -0,0 +1,8 @@
+-- Add a 'sortkey' field to page_props so pages can be efficiently
+-- queried by the numeric value of a property.
+
+ALTER TABLE /*_*/page_props
+ ADD pp_sortkey float DEFAULT NULL;
+
+CREATE UNIQUE INDEX /*i*/pp_propname_sortkey_page
+ ON /*_*/page_props ( pp_propname, pp_sortkey, pp_page );
diff --git a/maintenance/mssql/archives/patch-tl_from_namespace.sql
b/maintenance/mssql/archives/patch-tl_from_namespace.sql
new file mode 100644
index 0000000..9655165
--- /dev/null
+++ b/maintenance/mssql/archives/patch-tl_from_namespace.sql
@@ -0,0 +1,4 @@
+ALTER TABLE /*_*/templatelinks
+ ADD tl_from_namespace int NOT NULL default 0;
+
+CREATE INDEX /*i*/tl_backlinks_namespace ON /*_*/templatelinks
(tl_from_namespace,tl_namespace,tl_title,tl_from);
diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql
index 86bd735..f6387ff 100644
--- a/maintenance/mssql/tables.sql
+++ b/maintenance/mssql/tables.sql
@@ -38,7 +38,6 @@
user_newpassword NVARCHAR(255) NOT NULL DEFAULT '',
user_newpass_time varchar(14) NULL DEFAULT NULL,
user_email NVARCHAR(255) NOT NULL DEFAULT '',
- user_options NVARCHAR(MAX) NOT NULL DEFAULT '',
user_touched varchar(14) NOT NULL DEFAULT '',
user_token NCHAR(32) NOT NULL DEFAULT '',
user_email_authenticated varchar(14) DEFAULT NULL,
@@ -100,6 +99,20 @@
);
CREATE UNIQUE CLUSTERED INDEX /*i*/user_properties_user_property ON
/*_*/user_properties (up_user,up_property);
CREATE INDEX /*i*/user_properties_property ON /*_*/user_properties
(up_property);
+
+--
+-- This table contains a user's bot passwords: passwords that allow access to
+-- the account via the API with limited rights.
+--
+CREATE TABLE /*_*/bot_passwords (
+ bp_user int NOT NULL REFERENCES /*_*/mwuser(user_id) ON DELETE CASCADE,
+ bp_app_id nvarchar(32) NOT NULL,
+ bp_password nvarchar(255) NOT NULL,
+ bp_token nvarchar(255) NOT NULL,
+ bp_restrictions nvarchar(max) NOT NULL,
+ bp_grants nvarchar(max) NOT NULL,
+ PRIMARY KEY (bp_user, bp_app_id)
+);
--
@@ -218,11 +231,13 @@
--
CREATE TABLE /*_*/pagelinks (
pl_from INT NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
+ pl_from_namespace int NOT NULL DEFAULT 0,
pl_namespace INT NOT NULL DEFAULT 0,
pl_title NVARCHAR(255) NOT NULL DEFAULT '',
);
CREATE UNIQUE INDEX /*i*/pl_from ON /*_*/pagelinks
(pl_from,pl_namespace,pl_title);
CREATE UNIQUE INDEX /*i*/pl_namespace ON /*_*/pagelinks
(pl_namespace,pl_title,pl_from);
+CREATE INDEX /*i*/pl_backlinks_namespace ON /*_*/pagelinks
(pl_from_namespace,pl_namespace,pl_title,pl_from);
--
@@ -230,12 +245,14 @@
--
CREATE TABLE /*_*/templatelinks (
tl_from int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
+ tl_from_namespace int NOT NULL default 0,
tl_namespace int NOT NULL default 0,
tl_title nvarchar(255) NOT NULL default ''
);
CREATE UNIQUE INDEX /*i*/tl_from ON /*_*/templatelinks
(tl_from,tl_namespace,tl_title);
CREATE UNIQUE INDEX /*i*/tl_namespace ON /*_*/templatelinks
(tl_namespace,tl_title,tl_from);
+CREATE INDEX /*i*/tl_backlinks_namespace ON /*_*/templatelinks
(tl_from_namespace,tl_namespace,tl_title,tl_from);
--
@@ -246,6 +263,7 @@
CREATE TABLE /*_*/imagelinks (
-- Key to page_id of the page containing the image / media link.
il_from int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
+ il_from_namespace int NOT NULL default 0,
-- Filename of target image.
-- This is also the page_title of the file's description page;
@@ -255,6 +273,7 @@
CREATE UNIQUE INDEX /*i*/il_from ON /*_*/imagelinks (il_from,il_to);
CREATE UNIQUE INDEX /*i*/il_to ON /*_*/imagelinks (il_to,il_from);
+CREATE INDEX /*i*/il_backlinks_namespace ON /*_*/imagelinks
(il_from_namespace,il_to,il_from);
--
-- Track category inclusions *used inline*
@@ -313,8 +332,8 @@
-- Used by the API (and some extensions)
CREATE INDEX /*i*/cl_timestamp ON /*_*/categorylinks (cl_to,cl_timestamp);
--- FIXME: Not used, delete this
-CREATE INDEX /*i*/cl_collation ON /*_*/categorylinks (cl_collation);
+-- Used when updating collation (e.g. updateCollation.php)
+CREATE INDEX /*i*/cl_collation_ext ON /*_*/categorylinks (cl_collation, cl_to,
cl_type, cl_from);
--
-- Track all existing categories. Something is a category if 1) it has an en-
@@ -375,6 +394,9 @@
CREATE INDEX /*i*/el_from ON /*_*/externallinks (el_from);
CREATE INDEX /*i*/el_index ON /*_*/externallinks (el_index);
+-- el_to index intentionally not added; we cannot index nvarchar(max) columns,
+-- but we also cannot restrict el_to to a smaller column size as the external
+-- link may be larger.
--
-- Track interlanguage links
@@ -766,11 +788,6 @@
rc_id int NOT NULL PRIMARY KEY IDENTITY,
rc_timestamp varchar(14) not null default '',
- -- This is no longer used
- -- Field kept in database for downgrades
- -- @todo: add drop patch with 1.24
- rc_cur_time varchar(14) NOT NULL default '',
-
-- As in revision
rc_user int NOT NULL default 0 REFERENCES /*_*/mwuser(user_id),
rc_user_text nvarchar(255) NOT NULL,
@@ -1157,11 +1174,13 @@
CREATE TABLE /*_*/page_props (
pp_page int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
pp_propname nvarchar(60) NOT NULL,
- pp_value nvarchar(max) NOT NULL
+ pp_value nvarchar(max) NOT NULL,
+ pp_sortkey float DEFAULT NULL
);
CREATE UNIQUE INDEX /*i*/pp_page_propname ON /*_*/page_props
(pp_page,pp_propname);
CREATE UNIQUE INDEX /*i*/pp_propname_page ON /*_*/page_props
(pp_propname,pp_page);
+CREATE UNIQUE INDEX /*i*/pp_propname_sortkey_page ON /*_*/page_props
(pp_propname,pp_sortkey,pp_page);
-- A table to log updates, one text key row per update.
--
To view, visit https://gerrit.wikimedia.org/r/285139
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: If3eea625499d3cb14abba40f528208173067a53a
Gerrit-PatchSet: 1
Gerrit-Project: mediawiki/core
Gerrit-Branch: master
Gerrit-Owner: Skizzerz <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
