Page MenuHomePhabricator
Files F5204

mssql.diff
Public
Actions

Authored By
• bzimport
Nov 21 2014, 10:21 PM
Size
64 KB
Referenced Files
None
Subscribers
None

mssql.diff
View Options

Index: maintenance/mssql/tables.sql
===================================================================
--- maintenance/mssql/tables.sql (revision 45949)
+++ maintenance/mssql/tables.sql (working copy)
@@ -1,395 +1,455 @@
+BEGIN TRANSACTION;
+
CREATE TABLE /*$wgDBprefix*/user (
- user_id int NOT NULL IDENTITY(1,1),
- user_name varchar(255) NOT NULL default '',
- user_real_name varchar(255) NOT NULL default '',
- user_password text NOT NULL,
- user_newpassword text NOT NULL,
- user_newpass_time varchar(5) NULL,
- user_email text NOT NULL,
- user_options text NOT NULL,
- user_touched varchar(5) NOT NULL default '',
- user_token varchar(10) NOT NULL default '',
- user_email_authenticated varchar(5) NULL,
- user_email_token varchar(10) NULL,
- user_email_token_expires varchar(5) NULL,
- user_registration varchar(5) NULL,
- user_editcount int,
- PRIMARY KEY (user_id)
+ user_id int NOT NULL PRIMARY KEY IDENTITY(0,1),
+ user_name varchar(255) NOT NULL, /* unique */
+ user_real_name varchar(255) NULL,
+ user_password varchar(max) NULL,
+ user_newpassword varchar(max) NULL,
+ user_newpass_time datetime NULL,
+ user_email varchar(max) NULL,
+ user_options varchar(max) NULL,
+ user_touched datetime NULL,
+ user_token varchar(32) NULL,
+ user_email_authenticated datetime NULL,
+ user_email_token varchar(32) NULL,
+ user_email_token_expires datetime NULL,
+ user_registration datetime NOT NULL,
+ user_editcount int NULL
);
+CREATE INDEX /*$wgDBprefix*/user_email_token_idx ON /*$wgDBprefix*/user (user_email_token);
+-- Create a dummy user to satisfy fk contraints especially with revisions
+INSERT INTO /*$wgDBprefix*/user
+ VALUES ('Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,GETDATE(),GETDATE(),NULL);
+
CREATE TABLE /*$wgDBprefix*/user_groups (
- ug_user int NOT NULL default '0',
- ug_group varchar(5) NOT NULL default '',
- PRIMARY KEY (ug_user,ug_group)
+ ug_user int NOT NULL REFERENCES /*$wgDBprefix*/user(user_id) ON DELETE CASCADE,
+ ug_group varchar(16) NOT NULL
);
+CREATE UNIQUE INDEX /*$wgDBprefix*/user_groups_unique ON /*$wgDBprefix*/user_groups (ug_user, ug_group);
CREATE TABLE /*$wgDBprefix*/user_newtalk (
- user_id int NOT NULL default '0',
- user_ip varchar(13) NOT NULL default '',
- user_last_timestamp varchar(5) NOT NULL default ''
+ user_id int NOT NULL REFERENCES /*$wgDBprefix*/user(user_id) ON DELETE CASCADE,
+ user_ip varchar(40) NULL,
+ user_last_timestamp datetime NOT NULL,
);
+CREATE INDEX /*$wgDBprefix*/user_newtalk_id_idx ON /*$wgDBprefix*/user_newtalk (user_id);
+CREATE INDEX /*$wgDBprefix*/user_newtalk_ip_idx ON /*$wgDBprefix*/user_newtalk (user_ip);
CREATE TABLE /*$wgDBprefix*/page (
- page_id int NOT NULL IDENTITY(1,1),
- page_namespace int NOT NULL,
- page_title varchar(255) NOT NULL,
- page_restrictions text NOT NULL,
- page_counter bigint NOT NULL default '0',
- page_is_redirect tinyint NOT NULL default '0',
- page_is_new tinyint NOT NULL default '0',
- page_random real NOT NULL,
- page_touched varchar(5) NOT NULL default '',
- page_latest int NOT NULL,
- page_len int NOT NULL,
- PRIMARY KEY (page_id)
+ page_id int NOT NULL PRIMARY KEY IDENTITY(1,1),
+ page_namespace int NOT NULL,
+ page_title varchar(255) NOT NULL,
+ page_restrictions varchar(max) NULL,
+ page_counter bigint NOT NULL default 0,
+ page_is_redirect tinyint NOT NULL default 0,
+ page_is_new tinyint NOT NULL default 0,
+ page_random real NOT NULL,
+ page_touched datetime NULL,
+ page_latest int NOT NULL,
+ page_len int NOT NULL
);
+CREATE UNIQUE INDEX /*$wgDBprefix*/page_unique_name ON /*$wgDBprefix*/page (page_namespace, page_title);
+-- Indexes with WHERE clauses require SQL server 2008
+--CREATE INDEX /*$wgDBprefix*/page_main_title ON /*$wgDBprefix*/page (page_title) WHERE page_namespace = 0;
+--CREATE INDEX /*$wgDBprefix*/page_talk_title ON /*$wgDBprefix*/page (page_title) WHERE page_namespace = 1;
+--CREATE INDEX /*$wgDBprefix*/page_user_title ON /*$wgDBprefix*/page (page_title) WHERE page_namespace = 2;
+--CREATE INDEX /*$wgDBprefix*/page_utalk_title ON /*$wgDBprefix*/page (page_title) WHERE page_namespace = 3;
+--CREATE INDEX /*$wgDBprefix*/page_project_title ON /*$wgDBprefix*/page (page_title) WHERE page_namespace = 4;
+CREATE INDEX /*$wgDBprefix*/page_ns_title ON /*$wgDBprefix*/page (page_namespace, page_title);
+CREATE INDEX /*$wgDBprefix*/page_random_idx ON /*$wgDBprefix*/page (page_random);
+CREATE INDEX /*$wgDBprefix*/page_len_idx ON /*$wgDBprefix*/page (page_len);
CREATE TABLE /*$wgDBprefix*/revision (
- rev_id int NOT NULL IDENTITY(1,1),
- rev_page int NOT NULL,
- rev_text_id int NOT NULL,
- rev_comment text NOT NULL,
- rev_user int NOT NULL default '0',
- rev_user_text varchar(255) NOT NULL default '',
- rev_timestamp varchar(5) NOT NULL default '',
- rev_minor_edit tinyint NOT NULL default '0',
- rev_deleted tinyint NOT NULL default '0',
- rev_len int,
- rev_parent_id int default NULL,
- PRIMARY KEY (rev_page, rev_id)
+ rev_id int NOT NULL UNIQUE IDENTITY(1,1),
+ rev_page int NULL REFERENCES /*$wgDBprefix*/page (page_id) ON DELETE CASCADE,
+ rev_text_id int NOT NULL,
+ rev_comment varchar(max) NULL,
+ rev_user int NOT NULL REFERENCES /*$wgDBprefix*/user(user_id),
+ rev_user_text varchar(255) NOT NULL,
+ rev_timestamp datetime NOT NULL,
+ rev_minor_edit tinyint NOT NULL default 0,
+ rev_deleted tinyint NOT NULL default 0,
+ rev_len int NULL,
+ rev_parent_id int NULL
);
+CREATE UNIQUE INDEX /*$wgDBprefix*/revision_unique ON /*$wgDBprefix*/revision (rev_page, rev_id);
+CREATE INDEX /*$wgDBprefix*/rev_text_id_idx ON /*$wgDBprefix*/revision (rev_text_id);
+CREATE INDEX /*$wgDBprefix*/rev_timestamp_idx ON /*$wgDBprefix*/revision (rev_timestamp);
+CREATE INDEX /*$wgDBprefix*/rev_user_idx ON /*$wgDBprefix*/revision (rev_user);
+CREATE INDEX /*$wgDBprefix*/rev_user_text_idx ON /*$wgDBprefix*/revision (rev_user_text);
+
CREATE TABLE /*$wgDBprefix*/text (
- old_id int NOT NULL IDENTITY(1,1),
- old_text text NOT NULL,
- old_flags text NOT NULL,
- PRIMARY KEY (old_id)
+ old_id int NOT NULL PRIMARY KEY IDENTITY(1,1),
+ old_text varchar(max) NULL,
+ old_flags varchar(max) NULL
);
CREATE TABLE /*$wgDBprefix*/archive (
- ar_namespace int NOT NULL default '0',
- ar_title varchar(255) NOT NULL default '',
- ar_text text NOT NULL,
- ar_comment text NOT NULL,
- ar_user int NOT NULL default '0',
- ar_user_text varchar(255) NOT NULL,
- ar_timestamp varchar(5) NOT NULL default '',
- ar_minor_edit tinyint NOT NULL default '0',
- ar_flags text NOT NULL,
- ar_rev_id int,
- ar_text_id int,
- ar_deleted tinyint NOT NULL default '0',
- ar_len int,
- ar_page_id int,
- ar_parent_id int default NULL
+ ar_namespace int NOT NULL,
+ ar_title varchar(255) NOT NULL,
+ ar_text varchar(max) NULL,
+ ar_comment varchar(max) NULL,
+ ar_user int NULL REFERENCES /*$wgDBprefix*/user(user_id) ON DELETE SET NULL,
+ ar_user_text varchar(255) NOT NULL,
+ ar_timestamp datetime NOT NULL,
+ ar_minor_edit tinyint NOT NULL default 0,
+ ar_flags varchar(max) NULL,
+ ar_rev_id int NULL,
+ ar_text_id int NULL,
+ ar_deleted tinyint NOT NULL default 0,
+ ar_len int NULL,
+ ar_page_id int NULL,
+ ar_parent_id int NULL
);
+CREATE INDEX /*$wgDBprefix*/archive_name_title_timestamp ON /*$wgDBprefix*/archive (ar_namespace,ar_title,ar_timestamp);
+CREATE INDEX /*$wgDBprefix*/archive_user_text ON /*$wgDBprefix*/archive (ar_user_text);
CREATE TABLE /*$wgDBprefix*/pagelinks (
- pl_from int NOT NULL default '0',
- pl_namespace int NOT NULL default '0',
- pl_title varchar(255) NOT NULL default ''
+ pl_from int NOT NULL REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE,
+ pl_namespace int NOT NULL,
+ pl_title varchar(255) NOT NULL
);
+CREATE UNIQUE INDEX /*$wgDBprefix*/pagelink_unique ON /*$wgDBprefix*/pagelinks (pl_from,pl_namespace,pl_title);
+
CREATE TABLE /*$wgDBprefix*/templatelinks (
- tl_from int NOT NULL default '0',
- tl_namespace int NOT NULL default '0',
- tl_title varchar(255) NOT NULL default ''
+ tl_from int NOT NULL REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE,
+ tl_namespace int NOT NULL,
+ tl_title varchar(255) NOT NULL
);
+CREATE UNIQUE INDEX /*$wgDBprefix*/templatelinks_unique ON /*$wgDBprefix*/templatelinks (tl_namespace,tl_title,tl_from);
+
CREATE TABLE /*$wgDBprefix*/imagelinks (
- il_from int NOT NULL default '0',
- il_to varchar(255) NOT NULL default ''
+ il_from int NOT NULL REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE,
+ il_to varchar(255) NOT NULL
);
+CREATE UNIQUE INDEX /*$wgDBprefix*/il_from ON /*$wgDBprefix*/imagelinks (il_to,il_from);
CREATE TABLE /*$wgDBprefix*/categorylinks (
- cl_from int NOT NULL default '0',
- cl_to varchar(255) NOT NULL default '',
- cl_sortkey varchar(70) NOT NULL default '',
- cl_timestamp timestamp NOT NULL
+ cl_from int NOT NULL REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE,
+ cl_to varchar(255) NOT NULL,
+ cl_sortkey varchar(70) NULL,
+ cl_timestamp datetime NOT NULL
);
+CREATE UNIQUE INDEX /*$wgDBprefix*/cl_from ON /*$wgDBprefix*/categorylinks (cl_from, cl_to);
+CREATE INDEX /*$wgDBprefix*/cl_sortkey ON /*$wgDBprefix*/categorylinks (cl_to, cl_sortkey, cl_from);
CREATE TABLE /*$wgDBprefix*/category (
- cat_id int NOT NULL IDENTITY(1,1),
- cat_title varchar(255) NOT NULL,
- cat_pages int NOT NULL default 0,
- cat_subcats int NOT NULL default 0,
- cat_files int NOT NULL default 0,
- cat_hidden tinyint NOT NULL default 0,
- PRIMARY KEY (cat_id)
+ cat_id int NOT NULL PRIMARY KEY IDENTITY(1,1),
+ cat_title varchar(255) NOT NULL,
+ cat_pages int NOT NULL default 0,
+ cat_subcats int NOT NULL default 0,
+ cat_files int NOT NULL default 0,
+ cat_hidden tinyint NOT NULL default 0
);
+CREATE UNIQUE INDEX /*$wgDBprefix*/category_title ON /*$wgDBprefix*/category(cat_title);
+CREATE INDEX /*$wgDBprefix*/category_pages ON /*$wgDBprefix*/category(cat_pages);
CREATE TABLE /*$wgDBprefix*/externallinks (
- el_from int NOT NULL default '0',
- el_to text NOT NULL,
- el_index text NOT NULL
+ el_from int NOT NULL REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE,
+ el_to varchar(max) NOT NULL,
+ el_index varchar(max) NOT NULL
);
+-- CREATE INDEX /*$wgDBprefix*/externallinks_from_to ON /*$wgDBprefix*/externallinks (el_from,el_to);
+-- CREATE INDEX /*$wgDBprefix*/externallinks_index ON /*$wgDBprefix*/externallinks (el_index);
CREATE TABLE /*$wgDBprefix*/langlinks (
- ll_from int NOT NULL default '0',
- ll_lang varchar(7) NOT NULL default '',
- ll_title varchar(255) NOT NULL default ''
+ ll_from int NOT NULL REFERENCES /*$wgDBprefix*/page (page_id) ON DELETE CASCADE,
+ ll_lang varchar(20) NULL,
+ ll_title varchar(255) NULL,
);
+CREATE UNIQUE INDEX /*$wgDBprefix*/langlinks_unique ON /*$wgDBprefix*/langlinks (ll_from,ll_lang);
+CREATE INDEX /*$wgDBprefix*/langlinks_lang_title ON /*$wgDBprefix*/langlinks (ll_lang,ll_title);
CREATE TABLE /*$wgDBprefix*/site_stats (
- ss_row_id int NOT NULL,
- ss_total_views bigint default '0',
- ss_total_edits bigint default '0',
- ss_good_articles bigint default '0',
- ss_total_pages bigint default '-1',
- ss_users bigint default '-1',
- ss_admins int default '-1',
- ss_images int default '0'
+ ss_row_id int NOT NULL,
+ ss_total_views bigint NOT NULL default 0,
+ ss_total_edits bigint NOT NULL default 0,
+ ss_good_articles bigint NOT NULL default 0,
+ ss_active_users bigint NOT NULL default -1,
+ ss_total_pages bigint NOT NULL default -1,
+ ss_users bigint NOT NULL default -1,
+ ss_admins int NOT NULL default -1,
+ ss_images int NOT NULL default 0
);
CREATE TABLE /*$wgDBprefix*/hitcounter (
- hc_id int NOT NULL
+ hc_id int NOT NULL
);
CREATE TABLE /*$wgDBprefix*/ipblocks (
- ipb_id int NOT NULL IDENTITY(1,1),
- ipb_address text NOT NULL,
- ipb_user int NOT NULL default '0',
- ipb_by int NOT NULL default '0',
- ipb_by_text varchar(255) NOT NULL default '',
- ipb_reason text NOT NULL,
- ipb_timestamp varchar(5) NOT NULL default '',
- ipb_auto bit NOT NULL default 0,
- ipb_anon_only bit NOT NULL default 0,
- ipb_create_account bit NOT NULL default 1,
- ipb_enable_autoblock bit NOT NULL default '1',
- ipb_expiry varchar(5) NOT NULL default '',
- ipb_range_start text NOT NULL,
- ipb_range_end text NOT NULL,
- ipb_deleted bit NOT NULL default 0,
- ipb_block_email bit NOT NULL default 0,
- PRIMARY KEY (ipb_id)
+ ipb_id int NOT NULL PRIMARY KEY IDENTITY(1,1),
+ ipb_address varchar(255) NULL,
+ ipb_user int NULL, -- REFERENCES /*$wgDBprefix*/user(user_id) ON DELETE CASCADE,
+ ipb_by int NULL REFERENCES /*$wgDBprefix*/user(user_id) ON DELETE SET NULL,
+ ipb_by_text varchar(255) NOT NULL,
+ ipb_reason varchar(max) NOT NULL,
+ ipb_timestamp datetime NOT NULL,
+ ipb_auto bit NOT NULL default 0,
+ ipb_anon_only bit NOT NULL default 0,
+ ipb_create_account bit NOT NULL default 1,
+ ipb_enable_autoblock bit NOT NULL default '1',
+ ipb_expiry datetime NOT NULL,
+ ipb_range_start varchar(255) NULL,
+ ipb_range_end varchar(255) NULL,
+ ipb_deleted bit NOT NULL default 0,
+ ipb_block_email bit NOT NULL default 0,
+ ipb_allow_usertalk bit NOT NULL default 0
);
+CREATE UNIQUE INDEX /*$wgDBprefix*/ipb_address_unique ON /*$wgDBprefix*/ipblocks (ipb_address,ipb_user,ipb_auto,ipb_anon_only);
+CREATE INDEX /*$wgDBprefix*/ipb_user ON /*$wgDBprefix*/ipblocks (ipb_user);
+CREATE INDEX /*$wgDBprefix*/ipb_range ON /*$wgDBprefix*/ipblocks (ipb_range_start,ipb_range_end);
CREATE TABLE /*$wgDBprefix*/image (
- img_name varchar(255) NOT NULL default '',
- img_size int NOT NULL default '0',
- img_width int NOT NULL default '0',
- img_height int NOT NULL default '0',
- img_metadata text NOT NULL,
- img_bits int NOT NULL default '0',
- img_media_type TEXT default NULL,
- img_major_mime TEXT NOT NULL default "unknown",
- img_minor_mime varchar(10) NOT NULL default "unknown",
- img_description text NOT NULL,
- img_user int NOT NULL default '0',
- img_user_text varchar(255) NOT NULL,
- img_timestamp varchar(5) NOT NULL default '',
- img_sha1 varchar(10) NOT NULL default '',
- PRIMARY KEY (img_name)
+ img_name varchar(255) NOT NULL PRIMARY KEY,
+ img_size int NOT NULL,
+ img_width int NOT NULL,
+ img_height int NOT NULL,
+ img_metadata varchar(max) NULL,
+ img_bits int NOT NULL,
+ img_media_type varchar(max) NULL,
+ img_major_mime varchar(max) NOT NULL default 'unknown',
+ img_minor_mime varchar(32) NOT NULL default 'unknown',
+ img_description varchar(255) NULL,
+ img_user int NULL REFERENCES /*$wgDBprefix*/user(user_id) ON DELETE SET NULL,
+ img_user_text varchar(255) NOT NULL,
+ img_timestamp datetime NOT NULL,
+ img_sha1 varchar(32) NOT NULL default ''
);
+CREATE INDEX /*$wgDBprefix*/img_size_idx ON /*$wgDBprefix*/image (img_size);
+CREATE INDEX /*$wgDBprefix*/img_timestamp_idx ON /*$wgDBprefix*/image (img_timestamp);
+CREATE INDEX /*$wgDBprefix*/img_sha1 ON /*$wgDBprefix*/image (img_sha1);
CREATE TABLE /*$wgDBprefix*/oldimage (
- oi_name varchar(255) NOT NULL default '',
- oi_archive_name varchar(255) NOT NULL default '',
- oi_size int NOT NULL default 0,
- oi_width int NOT NULL default 0,
- oi_height int NOT NULL default 0,
- oi_bits int NOT NULL default 0,
- oi_description text NOT NULL,
- oi_user int NOT NULL default '0',
- oi_user_text varchar(255) NOT NULL,
- oi_timestamp varchar(5) NOT NULL default '',
- oi_metadata text NOT NULL,
- oi_media_type TEXT default NULL,
- oi_major_mime TEXT NOT NULL default "unknown",
- oi_minor_mime varchar(10) NOT NULL default "unknown",
- oi_deleted tinyint NOT NULL default '0',
- oi_sha1 varchar(10) NOT NULL default ''
+ oi_name varchar(255) NOT NULL,
+ oi_archive_name varchar(255) NOT NULL,
+ oi_size int NOT NULL,
+ oi_width int NOT NULL,
+ oi_height int NOT NULL,
+ oi_bits int NOT NULL,
+ oi_description varchar(max) NULL,
+ oi_user int NULL REFERENCES /*$wgDBprefix*/user(user_id) ON DELETE SET NULL,
+ oi_user_text varchar(255) NOT NULL,
+ oi_timestamp datetime NOT NULL,
+ oi_metadata varchar(max) NULL,
+ oi_media_type varchar(max) NULL,
+ oi_major_mime varchar(max) NOT NULL default 'unknown',
+ oi_minor_mime varchar(32) NOT NULL default 'unknown',
+ oi_deleted tinyint NOT NULL default 0,
+ oi_sha1 varchar(32) NOT NULL default ''
);
+ALTER TABLE /*$wgDBprefix*/oldimage ADD CONSTRAINT /*$wgDBprefix*/oldimage_oi_name_fkey_cascade FOREIGN KEY (oi_name) REFERENCES /*$wgDBprefix*/image(img_name) ON DELETE CASCADE;
+CREATE INDEX /*$wgDBprefix*/oi_name_timestamp ON /*$wgDBprefix*/oldimage (oi_name,oi_timestamp);
+CREATE INDEX /*$wgDBprefix*/oi_name_archive_name ON /*$wgDBprefix*/oldimage (oi_name,oi_archive_name);
+CREATE INDEX /*$wgDBprefix*/oi_sha1 ON /*$wgDBprefix*/oldimage (oi_sha1);
CREATE TABLE /*$wgDBprefix*/filearchive (
- fa_id int NOT NULL IDENTITY(1,1),
- fa_name varchar(255) NOT NULL default '',
- fa_archive_name varchar(255) NULL default '',
- fa_storage_group varchar(5) NULL,
- fa_storage_key varchar(17) NULL default '',
- fa_deleted_user int,
- fa_deleted_timestamp varchar(5) NULL default '',
- fa_deleted_reason text,
- fa_size int default '0',
- fa_width int default '0',
- fa_height int default '0',
- fa_metadata text,
- fa_bits int default '0',
- fa_media_type TEXT default NULL,
- fa_major_mime TEXT default "unknown",
- fa_minor_mime varchar(10) NULL default "unknown",
- fa_description text,
- fa_user int default '0',
- fa_user_text varchar(255) NULL,
- fa_timestamp varchar(5) NULL default '',
- fa_deleted tinyint NOT NULL default '0',
- PRIMARY KEY (fa_id)
+ fa_id int NOT NULL PRIMARY KEY IDENTITY(1,1),
+ fa_name varchar(255) NOT NULL,
+ fa_archive_name varchar(255) NULL,
+ fa_storage_group varchar(16) NULL,
+ fa_storage_key varchar(64) NULL,
+ fa_deleted_user int NULL, --REFERENCES /*$wgDBprefix*/user(user_id) ON DELETE SET NULL,
+ fa_deleted_timestamp datetime NULL,
+ fa_deleted_reason varchar(max) NULL,
+ fa_size int NOT NULL,
+ fa_width int NOT NULL,
+ fa_height int NOT NULL,
+ fa_metadata varchar(max) NULL,
+ fa_bits int NULL,
+ fa_media_type varchar(max) NULL,
+ fa_major_mime varchar(max) NOT NULL default 'unknown',
+ fa_minor_mime varchar(32) NOT NULL default 'unknown',
+ fa_description varchar(max) NULL,
+ fa_user int NULL REFERENCES /*$wgDBprefix*/user(user_id) ON DELETE SET NULL,
+ fa_user_text varchar(255) NULL,
+ fa_timestamp datetime NULL,
+ fa_deleted tinyint NOT NULL default 0
);
+CREATE INDEX /*$wgDBprefix*/fa_name_time ON /*$wgDBprefix*/filearchive (fa_name, fa_timestamp);
+CREATE INDEX /*$wgDBprefix*/fa_dupe ON /*$wgDBprefix*/filearchive (fa_storage_group, fa_storage_key);
+CREATE INDEX /*$wgDBprefix*/fa_notime ON /*$wgDBprefix*/filearchive (fa_deleted_timestamp);
+CREATE INDEX /*$wgDBprefix*/fa_nouser ON /*$wgDBprefix*/filearchive (fa_deleted_user);
CREATE TABLE /*$wgDBprefix*/recentchanges (
- rc_id int NOT NULL IDENTITY(1,1),
- rc_timestamp varchar(5) NOT NULL default '',
- rc_cur_time varchar(5) NOT NULL default '',
- rc_user int NOT NULL default '0',
- rc_user_text varchar(255) NOT NULL,
- rc_namespace int NOT NULL default '0',
- rc_title varchar(255) NOT NULL default '',
- rc_comment varchar(255) NOT NULL default '',
- rc_minor tinyint NOT NULL default '0',
- rc_bot tinyint NOT NULL default '0',
- rc_new tinyint NOT NULL default '0',
- rc_cur_id int NOT NULL default '0',
- rc_this_oldid int NOT NULL default '0',
- rc_last_oldid int NOT NULL default '0',
- rc_type tinyint NOT NULL default '0',
- rc_moved_to_ns tinyint NOT NULL default '0',
- rc_moved_to_title varchar(255) NOT NULL default '',
- rc_patrolled tinyint NOT NULL default '0',
- rc_ip varchar(13) NOT NULL default '',
- rc_old_len int,
- rc_new_len int,
- rc_deleted tinyint NOT NULL default '0',
- rc_logid int NOT NULL default '0',
- rc_log_type varchar(17) NULL default NULL,
- rc_log_action varchar(17) NULL default NULL,
- rc_params text NULL,
- PRIMARY KEY (rc_id)
+ rc_id int NOT NULL PRIMARY KEY IDENTITY(1,1),
+ rc_timestamp datetime NOT NULL,
+ rc_cur_time datetime NOT NULL,
+ rc_user int NULL REFERENCES /*$wgDBprefix*/user(user_id) ON DELETE SET NULL,
+ rc_user_text varchar(255) NOT NULL,
+ rc_namespace int NOT NULL,
+ rc_title varchar(255) NOT NULL,
+ rc_comment varchar(255) NULL,
+ rc_minor tinyint NOT NULL default 0,
+ rc_bot tinyint NOT NULL default 0,
+ rc_new tinyint NOT NULL default 0,
+ rc_cur_id int NULL REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE SET NULL,
+ rc_this_oldid int NOT NULL,
+ rc_last_oldid int NOT NULL,
+ rc_type tinyint NOT NULL default 0,
+ rc_moved_to_ns tinyint NULL,
+ rc_moved_to_title varchar(255) NULL,
+ rc_patrolled tinyint NOT NULL default 0,
+ rc_ip varchar(40) NULL,
+ rc_old_len int NULL,
+ rc_new_len int NULL,
+ rc_deleted tinyint NOT NULL default 0,
+ rc_logid int NOT NULL default 0,
+ rc_log_type varchar(255) NULL,
+ rc_log_action varchar(255) NULL,
+ rc_params varchar(max) NULL
);
+CREATE INDEX /*$wgDBprefix*/rc_timestamp ON /*$wgDBprefix*/recentchanges (rc_timestamp);
+CREATE INDEX /*$wgDBprefix*/rc_namespace_title ON /*$wgDBprefix*/recentchanges (rc_namespace, rc_title);
+CREATE INDEX /*$wgDBprefix*/rc_cur_id ON /*$wgDBprefix*/recentchanges (rc_cur_id);
+CREATE INDEX /*$wgDBprefix*/new_name_timestamp ON /*$wgDBprefix*/recentchanges (rc_new, rc_namespace, rc_timestamp);
+CREATE INDEX /*$wgDBprefix*/rc_ip ON /*$wgDBprefix*/recentchanges (rc_ip);
CREATE TABLE /*$wgDBprefix*/watchlist (
- wl_user int NOT NULL,
- wl_namespace int NOT NULL default '0',
- wl_title varchar(255) NOT NULL default '',
- wl_notificationtimestamp varchar(5) NULL
+ wl_user int NOT NULL REFERENCES /*$wgDBprefix*/user(user_id) ON DELETE CASCADE,
+ wl_namespace int NOT NULL,
+ wl_title varchar(255) NOT NULL,
+ wl_notificationtimestamp datetime NULL
);
+CREATE INDEX /*$wgDBprefix*/wl_user ON /*$wgDBprefix*/watchlist (wl_user);
CREATE TABLE /*$wgDBprefix*/math (
- math_inputhash varchar(5) NOT NULL,
- math_outputhash varchar(5) NOT NULL,
- math_html_conservativeness tinyint NOT NULL,
- math_html text,
- math_mathml text
+ math_inputhash varchar(16) NOT NULL UNIQUE,
+ math_outputhash varchar(16) NOT NULL,
+ math_html_conservativeness tinyint NOT NULL,
+ math_html varchar(max) NULL,
+ math_mathml varchar(max) NULL
);
CREATE TABLE /*$wgDBprefix*/searchindex (
- si_page int NOT NULL,
- si_title varchar(255) NOT NULL default '',
- si_text text NOT NULL
+ si_page int NOT NULL,
+ si_title varchar(255) NOT NULL,
+ si_text varchar(max) NULL
);
CREATE TABLE /*$wgDBprefix*/interwiki (
- iw_prefix varchar(32) NOT NULL,
- iw_url text NOT NULL,
- iw_local bit NOT NULL,
- iw_trans tinyint NOT NULL default 0
+ iw_prefix varchar(32) NOT NULL UNIQUE,
+ iw_url varchar(max) NOT NULL,
+ iw_local bit NOT NULL,
+ iw_trans tinyint NOT NULL default 0
);
CREATE TABLE /*$wgDBprefix*/querycache (
- qc_type varchar(10) NOT NULL,
- qc_value int NOT NULL default '0',
- qc_namespace int NOT NULL default '0',
- qc_title varchar(255) NOT NULL default ''
+ qc_type varchar(32) NOT NULL,
+ qc_value int NOT NULL,
+ qc_namespace int NOT NULL,
+ qc_title varchar(255) NOT NULL
);
+CREATE INDEX /*$wgDBprefix*/querycache_type_value ON /*$wgDBprefix*/querycache (qc_type, qc_value);
CREATE TABLE /*$wgDBprefix*/objectcache (
- keyname varchar(17) NOT NULL default '',
- value text,
- exptime datetime
+ keyname varchar(255) NOT NULL UNIQUE,
+ value varchar(max) NULL,
+ exptime datetime NOT NULL
);
+CREATE INDEX /*$wgDBprefix*/objectcacache_exptime ON /*$wgDBprefix*/objectcache (exptime);
CREATE TABLE /*$wgDBprefix*/transcache (
- tc_url varchar(17) NOT NULL,
- tc_contents text,
- tc_time int NOT NULL
+ tc_url varchar(255) NOT NULL UNIQUE,
+ tc_contents varchar(max) NULL,
+ tc_time int NOT NULL
);
CREATE TABLE /*$wgDBprefix*/logging (
- log_id int NOT NULL IDENTITY(1,1),
- log_type varchar(4) NOT NULL default '',
- log_action varchar(4) NOT NULL default '',
- log_timestamp varchar(5) NOT NULL default '19700101000000',
- log_user int NOT NULL default 0,
- log_namespace int NOT NULL default 0,
- log_title varchar(255) NOT NULL default '',
- log_comment varchar(255) NOT NULL default '',
- log_params text NOT NULL,
- log_deleted tinyint NOT NULL default '0',
- PRIMARY KEY (log_id)
+ log_id int NOT NULL PRIMARY KEY IDENTITY(1,1),
+ log_type varchar(10) NOT NULL,
+ log_action varchar(10) NOT NULL,
+ log_timestamp datetime NOT NULL,
+ log_user int NULL REFERENCES /*$wgDBprefix*/user(user_id) ON DELETE SET NULL,
+ log_namespace int NOT NULL,
+ log_title varchar(255) NOT NULL,
+ log_comment varchar(255) NOT NULL,
+ log_params varchar(max) NULL,
+ log_deleted tinyint NOT NULL default 0
);
+CREATE INDEX /*$wgDBprefix*/logging_type_name ON /*$wgDBprefix*/logging (log_type, log_timestamp);
+CREATE INDEX /*$wgDBprefix*/logging_user_time ON /*$wgDBprefix*/logging (log_timestamp, log_user);
+CREATE INDEX /*$wgDBprefix*/logging_page_time ON /*$wgDBprefix*/logging (log_namespace, log_title, log_timestamp);
CREATE TABLE /*$wgDBprefix*/trackbacks (
- tb_id int IDENTITY(1,1),
- tb_page int REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE,
- tb_title varchar(255) NOT NULL,
- tb_url text NOT NULL,
- tb_ex text,
- tb_name varchar(255) NULL,
- PRIMARY KEY (tb_id)
+ tb_id int NOT NULL PRIMARY KEY IDENTITY(1,1),
+ tb_page int REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE,
+ tb_title varchar(255) NOT NULL,
+ tb_url varchar(max) NOT NULL,
+ tb_ex varchar(max) NULL,
+ tb_name varchar(255) NULL
);
+CREATE INDEX /*$wgDBprefix*/trackback_page ON /*$wgDBprefix*/trackbacks (tb_page);
CREATE TABLE /*$wgDBprefix*/job (
- job_id int NOT NULL IDENTITY(1,1),
- job_cmd varchar(17) NOT NULL default '',
- job_namespace int NOT NULL,
- job_title varchar(255) NOT NULL,
- job_params text NOT NULL,
- PRIMARY KEY (job_id)
+ job_id int NOT NULL PRIMARY KEY IDENTITY(1,1),
+ job_cmd varchar(60) NOT NULL,
+ job_namespace int NOT NULL,
+ job_title varchar(255) NOT NULL,
+ job_params varchar(max) NULL
);
+CREATE INDEX /*$wgDBprefix*/job_cmd_namespace_title ON /*$wgDBprefix*/job (job_cmd, job_namespace, job_title);
CREATE TABLE /*$wgDBprefix*/querycache_info (
- qci_type varchar(10) NOT NULL default '',
- qci_timestamp varchar(5) NOT NULL default '19700101000000'
+ qci_type varchar(32) NULL,
+ qci_timestamp datetime NULL
);
CREATE TABLE /*$wgDBprefix*/redirect (
- rd_from int NOT NULL default '0',
- rd_namespace int NOT NULL default '0',
- rd_title varchar(255) NOT NULL default '',
- PRIMARY KEY (rd_from)
+ rd_from int NOT NULL REFERENCES /*$wgDBprefix*/page(page_id) ON DELETE CASCADE,
+ rd_namespace int NOT NULL,
+ rd_title varchar(255) NOT NULL
);
+CREATE INDEX /*$wgDBprefix*/redirect_ns_title ON /*$wgDBprefix*/redirect (rd_namespace,rd_title,rd_from);
CREATE TABLE /*$wgDBprefix*/querycachetwo (
- qcc_type varchar(10) NOT NULL,
- qcc_value int NOT NULL default '0',
- qcc_namespace int NOT NULL default '0',
- qcc_title varchar(255) NOT NULL default '',
- qcc_namespacetwo int NOT NULL default '0',
- qcc_titletwo varchar(255) NOT NULL default ''
+ qcc_type varchar(32) NOT NULL,
+ qcc_value int NOT NULL default 0,
+ qcc_namespace int NOT NULL default 0,
+ qcc_title varchar(255) NOT NULL default '',
+ qcc_namespacetwo int NOT NULL default 0,
+ qcc_titletwo varchar(255) NOT NULL default ''
);
+CREATE INDEX /*$wgDBprefix*/querycachetwo_type_value ON /*$wgDBprefix*/querycachetwo (qcc_type, qcc_value);
+CREATE INDEX /*$wgDBprefix*/querycachetwo_title ON /*$wgDBprefix*/querycachetwo (qcc_type,qcc_namespace,qcc_title);
+CREATE INDEX /*$wgDBprefix*/querycachetwo_titletwo ON /*$wgDBprefix*/querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo);
CREATE TABLE /*$wgDBprefix*/page_restrictions (
- pr_page int NOT NULL,
- pr_type varchar(17) NOT NULL,
- pr_level varchar(17) NOT NULL,
- pr_cascade tinyint NOT NULL,
- pr_user int NULL,
- pr_expiry varchar(5) NULL,
- pr_id int NOT NULL IDENTITY(1,1),
- PRIMARY KEY (pr_page,pr_type)
+ pr_id int NOT NULL UNIQUE IDENTITY(1,1),
+ pr_page int NOT NULL REFERENCES /*$wgDBprefix*/page (page_id) ON DELETE CASCADE,
+ pr_type varchar(60) NOT NULL,
+ pr_level varchar(60) NOT NULL,
+ pr_cascade tinyint NOT NULL,
+ pr_user int NULL,
+ pr_expiry datetime NULL
);
+ALTER TABLE /*$wgDBprefix*/page_restrictions ADD CONSTRAINT /*$wgDBprefix*/page_restrictions_pk PRIMARY KEY (pr_page,pr_type);
CREATE TABLE /*$wgDBprefix*/protected_titles (
- pt_namespace int NOT NULL,
- pt_title varchar(255) NOT NULL,
- pt_user int NOT NULL,
- pt_reason text,
- pt_timestamp varchar(5) NOT NULL,
- pt_expiry varchar(5) NOT NULL default '',
- pt_create_perm varchar(17) NOT NULL,
- PRIMARY KEY (pt_namespace,pt_title)
+ pt_namespace int NOT NULL,
+ pt_title varchar(255) NOT NULL,
+ pt_user int NULL REFERENCES /*$wgDBprefix*/user(user_id) ON DELETE SET NULL,
+ pt_reason varchar(max) NULL,
+ pt_timestamp datetime NOT NULL,
+ pt_expiry datetime NULL,
+ pt_create_perm varchar(60) NOT NULL,
);
+CREATE UNIQUE INDEX /*$wgDBprefix*/protected_titles_unique ON /*$wgDBprefix*/protected_titles(pt_namespace, pt_title);
CREATE TABLE /*$wgDBprefix*/page_props (
- pp_page int NOT NULL,
- pp_propname varchar(17) NOT NULL,
- pp_value text NOT NULL,
- PRIMARY KEY (pp_page,pp_propname)
+ pp_page int NOT NULL REFERENCES /*$wgDBprefix*/page (page_id) ON DELETE CASCADE,
+ pp_propname varchar(60) NOT NULL,
+ pp_value varchar(max) NULL
);
+ALTER TABLE /*$wgDBprefix*/page_props ADD CONSTRAINT /*$wgDBprefix*/page_props_pk PRIMARY KEY (pp_page,pp_propname);
+CREATE INDEX /*$wgDBprefix*/page_props_propname ON /*$wgDBprefix*/page_props (pp_propname);
CREATE TABLE /*$wgDBprefix*/updatelog (
- ul_key varchar(255) NOT NULL,
- PRIMARY KEY (ul_key)
+ ul_key varchar(255) NOT NULL PRIMARY KEY
);
-
+COMMIT TRANSACTION;
Index: includes/db/Database.php
===================================================================
--- includes/db/Database.php (revision 45949)
+++ includes/db/Database.php (working copy)
@@ -1059,6 +1059,45 @@
}
/**
+ * UNION SELECT wrapper
+ *
+ * @param $subsql Array: Array of sub-queries
+ * @param $vars Mixed: Array or string, field name(s) to be retrieved
+ * @param $conds Mixed: Array or string, condition(s) for WHERE
+ * @param $fname String: Calling function name (use __METHOD__) for logs/profiling
+ * @param $options Array: Associative array of options (e.g. array('GROUP BY' => 'page_title')),
+ * see Database::makeSelectOptions code for list of supported stuff
+ * @return string, the SQL text
+ */
+ function unionSQLText( $subsql, $vars, $conds, $fname = 'Database::unionSQLText', $options = array() ) {
+ if( is_array( $vars ) ) {
+ $vars = implode( ',', $vars );
+ }
+ if( !is_array( $options ) ) {
+ $options = array( $options );
+ }
+
+ $sql = "SELECT $vars FROM ((" . implode(') UNION (', $subsql) . ')) AS t';
+
+ if( !empty( $conds ) ) {
+ if ( is_array( $conds ) ) {
+ $conds = $this->makeList( $conds, LIST_AND );
+ }
+ $sql .= " WHERE $conds";
+ }
+
+ if ( isset( $options['ORDER BY'] ) )
+ $sql .= " ORDER BY {$options['ORDER BY']}";
+
+ if (isset($options['LIMIT'])) {
+ $sql = $this->limitResult($sql, $options['LIMIT'],
+ isset($options['OFFSET']) ? $options['OFFSET'] : false);
+ }
+
+ return $sql;
+ }
+
+ /**
* Single row SELECT wrapper
* Aborts or returns FALSE on error
*
@@ -1293,6 +1332,10 @@
$keys = array_keys( $a );
}
+ $explicit_id = array_search('EXPLICIT_ID', $options);
+ if ($explicit_id !== false)
+ unset($options[$explicit_id]);
+
$sql = 'INSERT ' . implode( ' ', $options ) .
" INTO $table (" . implode( ',', $keys ) . ') VALUES ';
Index: includes/db/DatabaseMssql.php
===================================================================
--- includes/db/DatabaseMssql.php (revision 45949)
+++ includes/db/DatabaseMssql.php (working copy)
@@ -17,6 +17,7 @@
var $mLastError;
var $mLastErrorNo;
var $mDatabaseFile;
+ var $mLimitOffset;
/**
* Constructor
@@ -50,6 +51,7 @@
*/
function implicitGroupby() { return false; }
function implicitOrderby() { return false; }
+ function cascadingDeletes() { return true; }
static function newFromParams($server, $user, $password, $dbName, $failFunction = false, $flags = 0) {
return new DatabaseMssql($server, $user, $password, $dbName, $failFunction, $flags);
@@ -131,26 +133,51 @@
function close() {
$this->mOpened = false;
if ($this->mConn) {
- if ($this->trxLevel()) $this->immediateCommit();
+ if ($this->trxLevel()) $this->commit();
return mssql_close($this->mConn);
} else return true;
}
/**
+ * Begin a transaction, committing any previously open transaction
+ */
+ function begin( $fname = 'Database::begin' ) {
+ $this->commit();
+ $this->mTrxLevel = 1; # Avoid recursion in Database::query
+ $this->query( 'BEGIN TRANSACTION', $fname );
+ }
+
+ /**
+ * End a transaction
+ */
+ function commit( $fname = 'Database::commit' ) {
+ if ($this->mTrxLevel)
+ $this->query( 'COMMIT TRANSACTION', $fname );
+ $this->mTrxLevel = 0;
+ }
+
+ /**
+ * Rollback a transaction.
+ * No-op on non-transactional databases.
+ */
+ function rollback( $fname = 'Database::rollback' ) {
+ $this->query( 'ROLLBACK TRANSACTION', $fname, true );
+ $this->mTrxLevel = 0;
+ }
+
+ /**
* - MSSQL doesn't seem to do buffered results
- * - the trasnaction syntax is modified here to avoid having to replicate
+ * - the transaction syntax is modified here to avoid having to replicate
* Database::query which uses BEGIN, COMMIT, ROLLBACK
*/
function doQuery($sql) {
- if ($sql == 'BEGIN' || $sql == 'COMMIT' || $sql == 'ROLLBACK') return true; # $sql .= ' TRANSACTION';
- $sql = preg_replace('|[^\x07-\x7e]|','?',$sql); # TODO: need to fix unicode - just removing it here while testing
$ret = mssql_query($sql, $this->mConn);
if ($ret === false) {
$err = mssql_get_last_message();
- if ($err) $this->mlastError = $err;
- $row = mssql_fetch_row(mssql_query('select @@ERROR'));
- if ($row[0]) $this->mlastErrorNo = $row[0];
- } else $this->mlastErrorNo = false;
+ if ($err) $this->mLastError = $err;
+ $row = mssql_fetch_row(mssql_query('select @@ERROR', $this->mConn));
+ if ($row[0]) $this->mLastErrorNo = $row[0];
+ } else $this->mLastErrorNo = false;
return $ret;
}
@@ -179,6 +206,10 @@
if ( $res instanceof ResultWrapper ) {
$res = $res->result;
}
+ if ($this->mLimitOffset) {
+ @/**/mssql_data_seek($res, $this->mLimitOffset);
+ $this->mLimitOffset = 0;
+ }
@/**/$row = mssql_fetch_object( $res );
if ( $this->lastErrno() ) {
throw new DBUnexpectedError( $this, 'Error in fetchObject(): ' . htmlspecialchars( $this->lastError() ) );
@@ -198,6 +229,10 @@
if ( $res instanceof ResultWrapper ) {
$res = $res->result;
}
+ if ($this->mLimitOffset) {
+ @/**/mssql_data_seek($res, $this->mLimitOffset);
+ $this->mLimitOffset = 0;
+ }
@/**/$row = mssql_fetch_array( $res );
if ( $this->lastErrno() ) {
throw new DBUnexpectedError( $this, 'Error in fetchRow(): ' . htmlspecialchars( $this->lastError() ) );
@@ -256,7 +291,7 @@
* $id = $dbw->insertId();
*/
function insertId() {
- $row = mssql_fetch_row(mssql_query('select @@IDENTITY'));
+ $row = mssql_fetch_row(mssql_query('select SCOPE_IDENTITY()', $this->mConn));
return $row[0];
}
@@ -277,14 +312,14 @@
* Get the last error number
*/
function lastErrno() {
- return $this->mlastErrorNo;
+ return $this->mLastErrorNo;
}
/**
* Get a description of the last error
*/
function lastError() {
- return $this->mlastError;
+ return $this->mLastError;
}
/**
@@ -295,47 +330,6 @@
}
/**
- * Simple UPDATE wrapper
- * Usually aborts on failure
- * If errors are explicitly ignored, returns success
- *
- * This function exists for historical reasons, Database::update() has a more standard
- * calling convention and feature set
- */
- function set( $table, $var, $value, $cond, $fname = 'Database::set' )
- {
- if ($value == "NULL") $value = "''"; # see comments in makeListWithoutNulls()
- $table = $this->tableName( $table );
- $sql = "UPDATE $table SET $var = '" .
- $this->strencode( $value ) . "' WHERE ($cond)";
- return (bool)$this->query( $sql, $fname );
- }
-
- /**
- * Simple SELECT wrapper, returns a single field, input must be encoded
- * Usually aborts on failure
- * If errors are explicitly ignored, returns FALSE on failure
- */
- function selectField( $table, $var, $cond='', $fname = 'Database::selectField', $options = array() ) {
- if ( !is_array( $options ) ) {
- $options = array( $options );
- }
- $options['LIMIT'] = 1;
-
- $res = $this->select( $table, $var, $cond, $fname, $options );
- if ( $res === false || !$this->numRows( $res ) ) {
- return false;
- }
- $row = $this->fetchRow( $res );
- if ( $row !== false ) {
- $this->freeResult( $res );
- return $row[0];
- } else {
- return false;
- }
- }
-
- /**
* Returns an optional USE INDEX clause to go after the table, and a
* string to go at the end of the query
*
@@ -347,7 +341,7 @@
*/
function makeSelectOptions( $options ) {
$preLimitTail = $postLimitTail = '';
- $startOpts = '';
+ $startOpts = $useIndex = '';
$noKeyOptions = array();
foreach ( $options as $key => $option ) {
@@ -360,92 +354,24 @@
if ( isset( $options['HAVING'] ) ) $preLimitTail .= " HAVING {$options['HAVING']}";
if ( isset( $options['ORDER BY'] ) ) $preLimitTail .= " ORDER BY {$options['ORDER BY']}";
- //if (isset($options['LIMIT'])) {
- // $tailOpts .= $this->limitResult('', $options['LIMIT'],
- // isset($options['OFFSET']) ? $options['OFFSET']
- // : false);
- //}
-
- if ( isset( $noKeyOptions['FOR UPDATE'] ) ) $postLimitTail .= ' FOR UPDATE';
- if ( isset( $noKeyOptions['LOCK IN SHARE MODE'] ) ) $postLimitTail .= ' LOCK IN SHARE MODE';
+ if ( isset( $noKeyOptions['FOR UPDATE'] ) ) $useIndex .= ' WITH (UPDLOCK) ';
+ if ( isset( $noKeyOptions['LOCK IN SHARE MODE'] ) ) $useIndex .= ' WITH (HOLDLOCK) ';
if ( isset( $noKeyOptions['DISTINCT'] ) || isset( $noKeyOptions['DISTINCTROW'] ) ) $startOpts .= 'DISTINCT';
# Various MySQL extensions
- if ( isset( $noKeyOptions['STRAIGHT_JOIN'] ) ) $startOpts .= ' /*! STRAIGHT_JOIN */';
- if ( isset( $noKeyOptions['HIGH_PRIORITY'] ) ) $startOpts .= ' HIGH_PRIORITY';
- if ( isset( $noKeyOptions['SQL_BIG_RESULT'] ) ) $startOpts .= ' SQL_BIG_RESULT';
- if ( isset( $noKeyOptions['SQL_BUFFER_RESULT'] ) ) $startOpts .= ' SQL_BUFFER_RESULT';
- if ( isset( $noKeyOptions['SQL_SMALL_RESULT'] ) ) $startOpts .= ' SQL_SMALL_RESULT';
- if ( isset( $noKeyOptions['SQL_CALC_FOUND_ROWS'] ) ) $startOpts .= ' SQL_CALC_FOUND_ROWS';
- if ( isset( $noKeyOptions['SQL_CACHE'] ) ) $startOpts .= ' SQL_CACHE';
- if ( isset( $noKeyOptions['SQL_NO_CACHE'] ) ) $startOpts .= ' SQL_NO_CACHE';
-
- if ( isset( $options['USE INDEX'] ) && ! is_array( $options['USE INDEX'] ) ) {
- $useIndex = $this->useIndexClause( $options['USE INDEX'] );
- } else {
- $useIndex = '';
- }
+ #if ( isset( $noKeyOptions['STRAIGHT_JOIN'] ) ) $startOpts .= ' /*! STRAIGHT_JOIN */';
+ #if ( isset( $noKeyOptions['HIGH_PRIORITY'] ) ) $startOpts .= ' HIGH_PRIORITY';
+ #if ( isset( $noKeyOptions['SQL_BIG_RESULT'] ) ) $startOpts .= ' SQL_BIG_RESULT';
+ #if ( isset( $noKeyOptions['SQL_BUFFER_RESULT'] ) ) $startOpts .= ' SQL_BUFFER_RESULT';
+ #if ( isset( $noKeyOptions['SQL_SMALL_RESULT'] ) ) $startOpts .= ' SQL_SMALL_RESULT';
+ #if ( isset( $noKeyOptions['SQL_CALC_FOUND_ROWS'] ) ) $startOpts .= ' SQL_CALC_FOUND_ROWS';
+ #if ( isset( $noKeyOptions['SQL_CACHE'] ) ) $startOpts .= ' SQL_CACHE';
+ #if ( isset( $noKeyOptions['SQL_NO_CACHE'] ) ) $startOpts .= ' SQL_NO_CACHE';
return array( $startOpts, $useIndex, $preLimitTail, $postLimitTail );
}
/**
- * SELECT wrapper
- *
- * @param $table Mixed: Array or string, table name(s) (prefix auto-added)
- * @param $vars Mixed: Array or string, field name(s) to be retrieved
- * @param $conds Mixed: Array or string, condition(s) for WHERE
- * @param $fname String: Calling function name (use __METHOD__) for logs/profiling
- * @param $options Array: Associative array of options (e.g. array('GROUP BY' => 'page_title')),
- * see Database::makeSelectOptions code for list of supported stuff
- * @return mixed Database result resource (feed to Database::fetchObject or whatever), or false on failure
- */
- function select( $table, $vars, $conds='', $fname = 'Database::select', $options = array() )
- {
- if( is_array( $vars ) ) {
- $vars = implode( ',', $vars );
- }
- if( !is_array( $options ) ) {
- $options = array( $options );
- }
- if( is_array( $table ) ) {
- if ( isset( $options['USE INDEX'] ) && is_array( $options['USE INDEX'] ) )
- $from = ' FROM ' . $this->tableNamesWithUseIndex( $table, $options['USE INDEX'] );
- else
- $from = ' FROM ' . implode( ',', array_map( array( &$this, 'tableName' ), $table ) );
- } elseif ($table!='') {
- if ($table{0}==' ') {
- $from = ' FROM ' . $table;
- } else {
- $from = ' FROM ' . $this->tableName( $table );
- }
- } else {
- $from = '';
- }
-
- list( $startOpts, $useIndex, $preLimitTail, $postLimitTail ) = $this->makeSelectOptions( $options );
-
- if( !empty( $conds ) ) {
- if ( is_array( $conds ) ) {
- $conds = $this->makeList( $conds, LIST_AND );
- }
- $sql = "SELECT $startOpts $vars $from $useIndex WHERE $conds $preLimitTail";
- } else {
- $sql = "SELECT $startOpts $vars $from $useIndex $preLimitTail";
- }
-
- if (isset($options['LIMIT']))
- $sql = $this->limitResult($sql, $options['LIMIT'],
- isset($options['OFFSET']) ? $options['OFFSET'] : false);
- $sql = "$sql $postLimitTail";
-
- if (isset($options['EXPLAIN'])) {
- $sql = 'EXPLAIN ' . $sql;
- }
- return $this->query( $sql, $fname );
- }
-
- /**
* Estimate rows in dataset
* Returns estimated count, based on EXPLAIN output
* Takes same arguments as Database::select()
@@ -454,7 +380,7 @@
$rows = 0;
$res = $this->select ($table, 'COUNT(*)', $conds, $fname, $options );
if ($res) {
- $row = $this->fetchObject($res);
+ $row = $this->fetchRow($res);
$rows = $row[0];
}
$this->freeResult($res);
@@ -491,23 +417,6 @@
throw new DBUnexpectedError( $this, 'Database::indexInfo called which is not supported yet' );
return NULL;
-
- $table = $this->tableName( $table );
- $sql = 'SHOW INDEX FROM '.$table;
- $res = $this->query( $sql, $fname );
- if ( !$res ) {
- return NULL;
- }
-
- $result = array();
- while ( $row = $this->fetchObject( $res ) ) {
- if ( $row->Key_name == $index ) {
- $result[] = $row;
- }
- }
- $this->freeResult($res);
-
- return empty($result) ? false : $result;
}
/**
@@ -573,117 +482,60 @@
$options = array( $options );
}
- # todo: need to record primary keys at table create time, and remove NULL assignments to them
- if ( isset( $a[0] ) && is_array( $a[0] ) ) {
- $multi = true;
- $keys = array_keys( $a[0] );
-# if (ereg('_id$',$keys[0])) {
- foreach ($a as $i) {
- if (is_null($i[$keys[0]])) unset($i[$keys[0]]); # remove primary-key column from multiple insert lists if empty value
- }
-# }
- $keys = array_keys( $a[0] );
- } else {
- $multi = false;
- $keys = array_keys( $a );
-# if (ereg('_id$',$keys[0]) && empty($a[$keys[0]])) unset($a[$keys[0]]); # remove primary-key column from insert list if empty value
- if (is_null($a[$keys[0]])) unset($a[$keys[0]]); # remove primary-key column from insert list if empty value
- $keys = array_keys( $a );
+ if ( !isset($a[0]) || !is_array( $a[0] ) )
+ $a = array($a);
+
+ # Remove null _id fields - null doesn't imply 'default' for MSSQL.
+ foreach ($a as $i => $row) {
+ foreach ($row as $key => $val) {
+ if (substr($key, -3) == '_id' && is_null($val))
+ unset($a[$i][$key]);
+ }
}
- # handle IGNORE option
- # example:
- # MySQL: INSERT IGNORE INTO user_groups (ug_user,ug_group) VALUES ('1','sysop')
- # MSSQL: IF NOT EXISTS (SELECT * FROM user_groups WHERE ug_user = '1') INSERT INTO user_groups (ug_user,ug_group) VALUES ('1','sysop')
- $ignore = in_array('IGNORE',$options);
+ $keys = array_keys($a[0]);
- # remove IGNORE from options list
- if ($ignore) {
- $oldoptions = $options;
- $options = array();
- foreach ($oldoptions as $o) if ($o != 'IGNORE') $options[] = $o;
+ $ignore = array_search('IGNORE',$options);
+ if ($ignore !== false) {
+ unset($options[$ignore]);
+ $ignore = true;
}
- $keylist = implode(',', $keys);
+ $prefix = $suffix = '';
+ $explicit_id = array_search('EXPLICIT_ID', $options);
+ if ($explicit_id !== false) {
+ unset($options[$explicit_id]);
+ $prefix = "SET IDENTITY_INSERT $table ON;";
+ $suffix = "SET IDENTITY_INSERT $table OFF;";
+ }
+
$sql = 'INSERT '.implode(' ', $options)." INTO $table (".implode(',', $keys).') VALUES ';
- if ($multi) {
+ $firstrow = true;
+ foreach ($a as $i => $row) {
+ # If ignore, then do each row separately
if ($ignore) {
- # If multiple and ignore, then do each row as a separate conditional insert
- foreach ($a as $row) {
- $prival = $row[$keys[0]];
- $sql = "IF NOT EXISTS (SELECT * FROM $table WHERE $keys[0] = '$prival') $sql";
- if (!$this->query("$sql (".$this->makeListWithoutNulls($row).')', $fname)) return false;
+ $ifnot = 'IF NOT EXISTS (SELECT * FROM '.$table.' WHERE ';
+ $firstkey = true;
+ foreach ($row as $k => $v) {
+ if ($firstkey) $firstkey = false; else $ifnot .= ' AND ';
+ $ifnot .= $k.' = '.$this->addQuotes($v);
}
- return true;
+ $ifnot .= ')';
+ $this->query("$prefix $ifnot $sql (".$this->makeList($row)."); $suffix", $fname);
} else {
- $first = true;
- foreach ($a as $row) {
- if ($first) $first = false; else $sql .= ',';
- $sql .= '('.$this->makeListWithoutNulls($row).')';
- }
+ if ($firstrow) $firstrow = false; else $sql .= ',';
+ $sql .= '('.$this->makeList($row).')';
}
- } else {
- if ($ignore) {
- $prival = $a[$keys[0]];
- $sql = "IF NOT EXISTS (SELECT * FROM $table WHERE $keys[0] = '$prival') $sql";
- }
- $sql .= '('.$this->makeListWithoutNulls($a).')';
}
- return (bool)$this->query( $sql, $fname );
- }
- /**
- * MSSQL doesn't allow implicit casting of NULL's into non-null values for NOT NULL columns
- * for now I've just converted the NULL's in the lists for updates and inserts into empty strings
- * which get implicitly casted to 0 for numeric columns
- * NOTE: the set() method above converts NULL to empty string as well but not via this method
- */
- function makeListWithoutNulls($a, $mode = LIST_COMMA) {
- return str_replace("NULL","''",$this->makeList($a,$mode));
- }
+ if ($ignore)
+ return true;
- /**
- * UPDATE wrapper, takes a condition array and a SET array
- *
- * @param $table String: The table to UPDATE
- * @param $values Array: An array of values to SET
- * @param $conds Array: An array of conditions (WHERE). Use '*' to update all rows.
- * @param $fname String: The Class::Function calling this function
- * (for the log)
- * @param $options Array: An array of UPDATE options, can be one or
- * more of IGNORE, LOW_PRIORITY
- * @return bool
- */
- function update( $table, $values, $conds, $fname = 'Database::update', $options = array() ) {
- $table = $this->tableName( $table );
- $opts = $this->makeUpdateOptions( $options );
- $sql = "UPDATE $opts $table SET " . $this->makeListWithoutNulls( $values, LIST_SET );
- if ( $conds != '*' ) {
- $sql .= " WHERE " . $this->makeList( $conds, LIST_AND );
- }
- return $this->query( $sql, $fname );
+ $result = (bool)$this->query( "$prefix $sql $suffix", $fname );
+ return $result;
}
/**
- * Make UPDATE options for the Database::update function
- *
- * @private
- * @param $options Array: The options passed to Database::update
- * @return string
- */
- function makeUpdateOptions( $options ) {
- if( !is_array( $options ) ) {
- $options = array( $options );
- }
- $opts = array();
- if ( in_array( 'LOW_PRIORITY', $options ) )
- $opts[] = $this->lowPriorityOption();
- if ( in_array( 'IGNORE', $options ) )
- $opts[] = 'IGNORE';
- return implode(' ', $opts);
- }
-
- /**
* Change the current database
*/
function selectDB( $db ) {
@@ -695,7 +547,10 @@
* MSSQL has a problem with the backtick quoting, so all this does is ensure the prefix is added exactly once
*/
function tableName($name) {
- return strpos($name, $this->mTablePrefix) === 0 ? $name : "{$this->mTablePrefix}$name";
+ if ($this->mTablePrefix)
+ return strpos($name, $this->mTablePrefix) === 0 ? $name : "{$this->mTablePrefix}$name";
+ else
+ return $name;
}
/**
@@ -704,7 +559,9 @@
* @return string slashed string.
*/
function strencode($s) {
- return str_replace("'","''",$s);
+ $s = str_replace('\'', '\'\'', $s);
+ $s = str_replace(chr(0), "'+CHAR(0)+'", $s);
+ return $s;
}
/**
@@ -716,7 +573,7 @@
/**
* REPLACE query wrapper
- * PostgreSQL simulates this with a DELETE followed by INSERT
+ * MSSQL simulates this with a DELETE followed by INSERT
* $row is the row to insert, an associative array
* $uniqueIndexes is an array of indexes. Each element may be either a
* field name or an array of field names
@@ -730,53 +587,50 @@
function replace( $table, $uniqueIndexes, $rows, $fname = 'Database::replace' ) {
$table = $this->tableName( $table );
+ if (count($rows)==0) {
+ return;
+ }
+
# Single row case
if ( !is_array( reset( $rows ) ) ) {
$rows = array( $rows );
}
- $sql = "REPLACE INTO $table (" . implode( ',', array_keys( $rows[0] ) ) .') VALUES ';
- $first = true;
- foreach ( $rows as $row ) {
- if ( $first ) {
- $first = false;
- } else {
- $sql .= ',';
+ foreach( $rows as $row ) {
+ # Delete rows which collide
+ if ( $uniqueIndexes ) {
+ $sql = "DELETE FROM $table WHERE ";
+ $first = true;
+ foreach ( $uniqueIndexes as $index ) {
+ if ( $first ) {
+ $first = false;
+ $sql .= "(";
+ } else {
+ $sql .= ') OR (';
+ }
+ if ( is_array( $index ) ) {
+ $first2 = true;
+ foreach ( $index as $col ) {
+ if ( $first2 ) {
+ $first2 = false;
+ } else {
+ $sql .= ' AND ';
+ }
+ $sql .= $col.'=' . $this->addQuotes( $row[$col] );
+ }
+ } else {
+ $sql .= $index.'=' . $this->addQuotes( $row[$index] );
+ }
+ }
+ $sql .= ')';
+ $this->query( $sql, $fname );
}
- $sql .= '(' . $this->makeList( $row ) . ')';
- }
- return $this->query( $sql, $fname );
- }
- /**
- * DELETE where the condition is a join
- * MySQL does this with a multi-table DELETE syntax, PostgreSQL does it with sub-selects
- *
- * For safety, an empty $conds will not delete everything. If you want to delete all rows where the
- * join condition matches, set $conds='*'
- *
- * DO NOT put the join condition in $conds
- *
- * @param $delTable String: The table to delete from.
- * @param $joinTable String: The other table.
- * @param $delVar String: The variable to join on, in the first table.
- * @param $joinVar String: The variable to join on, in the second table.
- * @param $conds Array: Condition array of field names mapped to variables, ANDed together in the WHERE clause
- * @param $fname String: Calling function name
- */
- function deleteJoin( $delTable, $joinTable, $delVar, $joinVar, $conds, $fname = 'Database::deleteJoin' ) {
- if ( !$conds ) {
- throw new DBUnexpectedError( $this, 'Database::deleteJoin() called with empty $conds' );
+ # Now insert the row
+ $sql = "INSERT INTO $table (" . $this->makeList( array_keys( $row ), LIST_NAMES ) .') VALUES (' .
+ $this->makeList( $row, LIST_COMMA ) . ')';
+ $this->query( $sql, $fname );
}
-
- $delTable = $this->tableName( $delTable );
- $joinTable = $this->tableName( $joinTable );
- $sql = "DELETE $delTable FROM $delTable, $joinTable WHERE $delVar=$joinVar ";
- if ( $conds != '*' ) {
- $sql .= ' AND ' . $this->makeList( $conds, LIST_AND );
- }
-
- return $this->query( $sql, $fname );
}
/**
@@ -799,46 +653,6 @@
}
/**
- * @return string Returns the text of the low priority option if it is supported, or a blank string otherwise
- */
- function lowPriorityOption() {
- return 'LOW_PRIORITY';
- }
-
- /**
- * INSERT SELECT wrapper
- * $varMap must be an associative array of the form array( 'dest1' => 'source1', ...)
- * Source items may be literals rather than field names, but strings should be quoted with Database::addQuotes()
- * $conds may be "*" to copy the whole table
- * srcTable may be an array of tables.
- */
- function insertSelect( $destTable, $srcTable, $varMap, $conds, $fname = 'Database::insertSelect',
- $insertOptions = array(), $selectOptions = array() )
- {
- $destTable = $this->tableName( $destTable );
- if ( is_array( $insertOptions ) ) {
- $insertOptions = implode( ' ', $insertOptions );
- }
- if( !is_array( $selectOptions ) ) {
- $selectOptions = array( $selectOptions );
- }
- list( $startOpts, $useIndex, $tailOpts ) = $this->makeSelectOptions( $selectOptions );
- if( is_array( $srcTable ) ) {
- $srcTable = implode( ',', array_map( array( &$this, 'tableName' ), $srcTable ) );
- } else {
- $srcTable = $this->tableName( $srcTable );
- }
- $sql = "INSERT $insertOptions INTO $destTable (" . implode( ',', array_keys( $varMap ) ) . ')' .
- " SELECT $startOpts " . implode( ',', $varMap ) .
- " FROM $srcTable $useIndex ";
- if ( $conds != '*' ) {
- $sql .= ' WHERE ' . $this->makeList( $conds, LIST_AND );
- }
- $sql .= " $tailOpts";
- return $this->query( $sql, $fname );
- }
-
- /**
* Construct a LIMIT query with optional offset
* This is used for query pages
* $sql string SQL query we will append the limit to
@@ -850,10 +664,11 @@
throw new DBUnexpectedError( $this, "Invalid non-numeric limit passed to limitResult()\n" );
}
if ($offset) {
- throw new DBUnexpectedError( $this, 'Database::limitResult called with non-zero offset which is not supported yet' );
- } else {
- $sql = ereg_replace("^SELECT", "SELECT TOP $limit", $sql);
+ $this->mLimitOffset = $offset;
+ $limit += $offset;
}
+
+ $sql = ereg_replace("^SELECT", "SELECT TOP $limit", $sql);
return $sql;
}
@@ -878,40 +693,13 @@
}
/**
- * Begin a transaction, committing any previously open transaction
- * @deprecated use begin()
+ * Return DB-style timestamp used for MSSQL schema
*/
- function immediateBegin( $fname = 'Database::immediateBegin' ) {
- $this->begin();
- }
-
- /**
- * Commit transaction, if one is open
- * @deprecated use commit()
- */
- function immediateCommit( $fname = 'Database::immediateCommit' ) {
- $this->commit();
- }
-
- /**
- * Return MW-style timestamp used for MySQL schema
- */
function timestamp( $ts=0 ) {
- return wfTimestamp(TS_MW,$ts);
+ return wfTimestamp(TS_DB,$ts);
}
/**
- * Local database timestamp format or null
- */
- function timestampOrNull( $ts = null ) {
- if( is_null( $ts ) ) {
- return null;
- } else {
- return $this->timestamp( $ts );
- }
- }
-
- /**
* @return string wikitext of a link to the server software's web site
*/
function getSoftwareLink() {
@@ -922,7 +710,7 @@
* @return string Version information from the database
*/
function getServerVersion() {
- $row = mssql_fetch_row(mssql_query('select @@VERSION'));
+ $row = mssql_fetch_row(mssql_query('select @@VERSION', $this->mConn));
return ereg("^(.+[0-9]+\\.[0-9]+\\.[0-9]+) ",$row[0],$m) ? $m[1] : $row[0];
}
@@ -954,44 +742,16 @@
public function setup_database() {
global $IP,$wgDBTableOptions;
$wgDBTableOptions = '';
- $mysql_tmpl = "$IP/maintenance/tables.sql";
$mysql_iw = "$IP/maintenance/interwiki.sql";
$mssql_tmpl = "$IP/maintenance/mssql/tables.sql";
- # Make an MSSQL template file if it doesn't exist (based on the same one MySQL uses to create a new wiki db)
- if (!file_exists($mssql_tmpl)) { # todo: make this conditional again
- $sql = file_get_contents($mysql_tmpl);
- $sql = preg_replace('/^\s*--.*?$/m','',$sql); # strip comments
- $sql = preg_replace('/^\s*(UNIQUE )?(INDEX|KEY|FULLTEXT).+?$/m', '', $sql); # These indexes should be created with a CREATE INDEX query
- $sql = preg_replace('/(\sKEY) [^\(]+\(/is', '$1 (', $sql); # "KEY foo (foo)" should just be "KEY (foo)"
- $sql = preg_replace('/(varchar\([0-9]+\))\s+binary/i', '$1', $sql); # "varchar(n) binary" cannot be followed by "binary"
- $sql = preg_replace('/(var)?binary\(([0-9]+)\)/ie', '"varchar(".strlen(pow(2,$2)).")"', $sql); # use varchar(chars) not binary(bits)
- $sql = preg_replace('/ (var)?binary/i', ' varchar', $sql); # use varchar not binary
- $sql = preg_replace('/(varchar\([0-9]+\)(?! N))/', '$1 NULL', $sql); # MSSQL complains if NULL is put into a varchar
- #$sql = preg_replace('/ binary/i',' varchar',$sql); # MSSQL binary's can't be assigned with strings, so use varchar's instead
- #$sql = preg_replace('/(binary\([0-9]+\) (NOT NULL )?default) [\'"].*?[\'"]/i','$1 0',$sql); # binary default cannot be string
- $sql = preg_replace('/[a-z]*(blob|text)([ ,])/i', 'text$2', $sql); # no BLOB types in MSSQL
- $sql = preg_replace('/\).+?;/',');', $sql); # remove all table options
- $sql = preg_replace('/ (un)?signed/i', '', $sql);
- $sql = preg_replace('/ENUM\(.+?\)/','TEXT',$sql); # Make ENUM's into TEXT's
- $sql = str_replace(' bool ', ' bit ', $sql);
- $sql = str_replace('auto_increment', 'IDENTITY(1,1)', $sql);
- #$sql = preg_replace('/NOT NULL(?! IDENTITY)/', 'NULL', $sql); # Allow NULL's for non IDENTITY columns
-
- # Tidy up and write file
- $sql = preg_replace('/,\s*\)/s', "\n)", $sql); # Remove spurious commas left after INDEX removals
- $sql = preg_replace('/^\s*^/m', '', $sql); # Remove empty lines
- $sql = preg_replace('/;$/m', ";\n", $sql); # Separate each statement with an empty line
- file_put_contents($mssql_tmpl, $sql);
- }
-
# Parse the MSSQL template replacing inline variables such as /*$wgDBprefix*/
$err = $this->sourceFile($mssql_tmpl);
- if ($err !== true) $this->reportQueryError($err,0,$sql,__FUNCTION__);
+ if ($err !== true) dieout("<li>Failed to prepare database</li>");
# Use DatabasePostgres's code to populate interwiki from MySQL template
$f = fopen($mysql_iw,'r');
- if ($f == false) dieout("<li>Could not find the interwiki.sql file");
+ if ($f == false) dieout("<li>Could not find the interwiki.sql file</li>");
$sql = "INSERT INTO {$this->mTablePrefix}interwiki(iw_prefix,iw_url,iw_local) VALUES ";
while (!feof($f)) {
$line = fgets($f,1024);
@@ -1033,4 +793,3 @@
}
} // end DatabaseMssql class
-
Index: includes/Revision.php
===================================================================
--- includes/Revision.php (revision 45949)
+++ includes/Revision.php (working copy)
@@ -843,7 +843,8 @@
'rev_len' => $this->mSize,
'rev_parent_id' => is_null($this->mParentId) ?
$this->getPreviousRevisionId( $dbw ) : $this->mParentId
- ), __METHOD__
+ ), __METHOD__,
+ !is_null($rev_id) ? array('EXPLICIT_ID') : null
);
$this->mId = !is_null($rev_id) ? $rev_id : $dbw->insertId();
Index: includes/specials/SpecialRecentchangeslinked.php
===================================================================
--- includes/specials/SpecialRecentchangeslinked.php (revision 45949)
+++ includes/specials/SpecialRecentchangeslinked.php (working copy)
@@ -150,7 +150,11 @@
$sql = $subsql[0];
else {
// need to resort and relimit after union
- $sql = "(" . implode( ") UNION (", $subsql ) . ") ORDER BY rc_timestamp DESC LIMIT {$limit}";
+ $sql = $dbr->unionSQLText($subsql,
+ '*',
+ array(),
+ __METHOD__,
+ array( 'LIMIT' => $limit, 'ORDER BY' => 'rc_timestamp DESC' ));
}
$res = $dbr->query( $sql, __METHOD__ );
Index: includes/specials/SpecialListfiles.php
===================================================================
--- includes/specials/SpecialListfiles.php (revision 45949)
+++ includes/specials/SpecialListfiles.php (working copy)
@@ -77,7 +77,8 @@
# Depends on $wgMiserMode
if( isset($this->mFieldNames['COUNT(oi_archive_name)']) ) {
$tables[] = 'oldimage';
- $options = array('GROUP BY' => 'img_name');
+ $options = array('GROUP BY' =>
+ 'img_name, img_timestamp, img_size, img_user, img_user_text, img_description');
$join_conds = array('oldimage' => array('LEFT JOIN','oi_name = img_name') );
}
return array(
Index: includes/specials/SpecialRecentchanges.php
===================================================================
--- includes/specials/SpecialRecentchanges.php (revision 45949)
+++ includes/specials/SpecialRecentchanges.php (working copy)
@@ -308,7 +308,12 @@
'USE INDEX' => array('recentchanges' => 'new_name_timestamp') ),
$join_conds );
# Join the two fast queries, and sort the result set
- $sql = "($sqlNew) UNION ($sqlOld) ORDER BY rc_timestamp DESC LIMIT $limit";
+ $sql = $dbr->unionSQLText( array($sqlNew, $sqlOld),
+ '*',
+ array(),
+ __METHOD__,
+ array( 'LIMIT' => $limit,
+ 'ORDER BY' => 'rc_timestamp DESC'));
$res = $dbr->query( $sql, __METHOD__ );
}
Index: includes/specials/SpecialAncientpages.php
===================================================================
--- includes/specials/SpecialAncientpages.php (revision 45949)
+++ includes/specials/SpecialAncientpages.php (working copy)
@@ -25,8 +25,19 @@
$db = wfGetDB( DB_SLAVE );
$page = $db->tableName( 'page' );
$revision = $db->tableName( 'revision' );
- $epoch = $wgDBtype == 'mysql' ? 'UNIX_TIMESTAMP(rev_timestamp)' :
- 'EXTRACT(epoch FROM rev_timestamp)';
+ switch($wgDBtype) {
+ case 'mysql':
+ $epoch = 'UNIX_TIMESTAMP(rev_timestamp)';
+ break;
+ case 'mssql':
+ # There is a trade-off between accuracy and complexity
+ #$epoch = 'datediff(ss,25567,dateadd(ms,round(datepart(ms,rev_timestamp),-3)-datepart(ms,rev_timestamp),rev_timestamp))';
+ $epoch = 'datediff(ss,25567,rev_timestamp)';
+ break;
+ default:
+ $epoch = 'EXTRACT(epoch FROM rev_timestamp)';
+ }
+
return
"SELECT 'Ancientpages' as type,
page_namespace as namespace,
Index: includes/specials/SpecialNewimages.php
===================================================================
--- includes/specials/SpecialNewimages.php (revision 45949)
+++ includes/specials/SpecialNewimages.php (working copy)
@@ -14,7 +14,10 @@
$shownav = !$specialPage->including();
$hidebots = $wgRequest->getBool( 'hidebots' , 1 );
- $hidebotsql = '';
+ $joins = array();
+ $where = array();
+ $tables = array();
+
if ( $hidebots ) {
# Make a list of group names which have the 'bot' flag set.
$botconds = array();
@@ -22,26 +25,21 @@
$botconds[] = 'ug_group = ' . $dbr->addQuotes( $groupname );
}
- # If not bot groups, do not set $hidebotsql
if ( $botconds ) {
$isbotmember = $dbr->makeList( $botconds, LIST_OR );
# This join, in conjunction with WHERE ug_group IS NULL, returns
# only those rows from IMAGE where the uploading user is not a mem-
# ber of a group which has the 'bot' permission set.
- $ug = $dbr->tableName( 'user_groups' );
- $hidebotsql = " LEFT JOIN $ug ON img_user=ug_user AND ($isbotmember)";
+ $tables[] = 'user_groups';
+ $joins['user_groups'] = array('LEFT JOIN', "img_user=ug_user AND ($isbotmember)");
+ $where[] = 'ug_group IS NULL';
}
}
- $image = $dbr->tableName( 'image' );
-
- $sql = "SELECT img_timestamp from $image";
- if ($hidebotsql) {
- $sql .= "$hidebotsql WHERE ug_group IS NULL";
- }
- $sql .= ' ORDER BY img_timestamp DESC LIMIT 1';
- $res = $dbr->query( $sql, __FUNCTION__ );
+ $tables[] = 'image';
+ $options = array('LIMIT' => 1, 'ORDER BY' => 'img_timestamp DESC');
+ $res = $dbr->select($tables, 'img_timestamp', $where, __FUNCTION__, $options, $joins);
$row = $dbr->fetchRow( $res );
if( $row !== false ) {
$ts = $row[0];
@@ -49,7 +47,6 @@
$ts = false;
}
$dbr->freeResult( $res );
- $sql = '';
# If we were clever, we'd use this to cache.
$latestTimestamp = wfTimestamp( TS_MW, $ts );
@@ -63,7 +60,6 @@
}
}
- $where = array();
$searchpar = '';
if ( $wpIlMatch != '' && !$wgMiserMode) {
$nt = Title::newFromUrl( $wpIlMatch );
@@ -82,20 +78,12 @@
$where[] = "img_timestamp >= '" . $dbr->timestamp( $from ) . "'";
$invertSort = true;
}
- $sql='SELECT img_size, img_name, img_user, img_user_text,'.
- "img_description,img_timestamp FROM $image";
+ $options['LIMIT'] = $limit + 1;
+ $options['ORDER BY'] = 'img_timestamp ' . ( $invertSort ? '' : ' DESC' );
+ $fields = array('img_size', 'img_name', 'img_user', 'img_user_text', 'img_description', 'img_timestamp');
+ $res = $dbr->select ($tables, $fields, $where, __FUNCTION__, $options, $joins);
+ $row = $dbr->fetchRow($res);
- if( $hidebotsql ) {
- $sql .= $hidebotsql;
- $where[] = 'ug_group IS NULL';
- }
- if( count( $where ) ) {
- $sql .= ' WHERE ' . $dbr->makeList( $where, LIST_AND );
- }
- $sql.=' ORDER BY img_timestamp '. ( $invertSort ? '' : ' DESC' );
- $sql.=' LIMIT ' . ( $limit + 1 );
- $res = $dbr->query( $sql, __FUNCTION__ );
-
/**
* We have to flip things around to get the last N after a certain date
*/

File Metadata

Mime Type
text/x-diff
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
4765
Default Alt Text
mssql.diff (64 KB)

Event Timeline

Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL