jenkins-bot has submitted this change and it was merged.
Change subject: Use ref_src_workflow_id to find workflow ID, add index
......................................................................
Use ref_src_workflow_id to find workflow ID, add index
Add a temporary index to make lookups by ref_src_workflow_id and
ref_src_wiki efficient.
The phases have been shifted.
Phase 2 - Just temporary indices
Phase 3 - Actual backfill
Phase 4 - Final schema changes
Bug: T111084
Change-Id: I695d48e56f54303f8694b090569cd2b62322fc3b
---
M db_patches/patch-reference_wiki-phase2.sql
M db_patches/patch-reference_wiki-phase3.sql
A db_patches/patch-reference_wiki-phase4.sql
M db_patches/patch-reference_wiki.sql
4 files changed, 55 insertions(+), 100 deletions(-)
Approvals:
Catrope: Looks good to me, approved
jenkins-bot: Verified
diff --git a/db_patches/patch-reference_wiki-phase2.sql
b/db_patches/patch-reference_wiki-phase2.sql
index 38dede8..32fd7e6 100644
--- a/db_patches/patch-reference_wiki-phase2.sql
+++ b/db_patches/patch-reference_wiki-phase2.sql
@@ -1,65 +1,5 @@
--- Phase 2 for adding ref_src_wiki field
--- Back-fills the field from other data available in the database.
---
--- These updates are idempotent, but must be re-run until 0 rows are
--- affected for all.
+CREATE INDEX /*i*/flow_wiki_ref_workflow_id_idx_tmp ON /*_*/flow_wiki_ref
+ (ref_src_workflow_id, ref_src_wiki);
--- Populate header and summary wiki references with the appropriate wiki
-UPDATE
- /*_*/flow_wiki_ref, (
- SELECT ref_src_object_id, workflow_wiki
- FROM /*_*/flow_wiki_ref, /*_*/flow_workflow
- WHERE
- flow_wiki_ref.ref_src_object_id =
flow_workflow.workflow_id AND
- flow_wiki_ref.ref_src_object_type IN ('header',
'post-summary') AND
- ( flow_wiki_ref.ref_src_wiki = '' OR
flow_wiki_ref.ref_src_wiki IS NULL )
- LIMIT 1000
- ) tmp
- SET flow_wiki_ref.ref_src_wiki = tmp.workflow_wiki
- WHERE flow_wiki_ref.ref_src_object_id = tmp.ref_src_object_id;
-
-
--- Populate post wiki references with the appropriate wiki.
-UPDATE
- /*_*/flow_wiki_ref, (
- SELECT ref_src_object_id, workflow_wiki
- FROM /*_*/flow_wiki_ref, /*_*/flow_tree_node, /*_*/flow_workflow
- WHERE
- flow_wiki_ref.ref_src_object_id =
flow_tree_node.tree_descendant_id AND
- flow_tree_node.tree_ancestor_id =
flow_workflow.workflow_id AND
- flow_wiki_ref.ref_src_object_type IN ('post') AND
- ( flow_wiki_ref.ref_src_wiki = '' OR
flow_wiki_ref.ref_src_wiki IS NULL )
- LIMIT 1000
- ) tmp
- SET flow_wiki_ref.ref_src_wiki = tmp.workflow_wiki
- WHERE flow_wiki_ref.ref_src_object_id = tmp.ref_src_object_id;
-
-
-UPDATE
- /*_*/flow_ext_ref, (
- SELECT ref_src_object_id, workflow_wiki
- FROM /*_*/flow_ext_ref, /*_*/flow_workflow
- WHERE
- flow_ext_ref.ref_src_object_id =
flow_workflow.workflow_id AND
- flow_ext_ref.ref_src_object_type IN ('header',
'post-summary') AND
- ( flow_ext_ref.ref_src_wiki = '' OR
flow_ext_ref.ref_src_wiki IS NULL )
- LIMIT 1000
- ) tmp
- SET flow_ext_ref.ref_src_wiki = tmp.workflow_wiki
- WHERE flow_ext_ref.ref_src_object_id = tmp.ref_src_object_id;
-
-
--- Populate post wiki references with the appropriate wiki.
-UPDATE
- /*_*/flow_ext_ref, (
- SELECT ref_src_object_id, workflow_wiki
- FROM /*_*/flow_ext_ref, /*_*/flow_tree_node, /*_*/flow_workflow
- WHERE
- flow_ext_ref.ref_src_object_id =
flow_tree_node.tree_descendant_id AND
- flow_tree_node.tree_ancestor_id =
flow_workflow.workflow_id AND
- flow_ext_ref.ref_src_object_type IN ('post') AND
- ( flow_ext_ref.ref_src_wiki = '' OR
flow_ext_ref.ref_src_wiki IS NULL )
- LIMIT 1000
- ) tmp
- SET flow_ext_ref.ref_src_wiki = tmp.workflow_wiki
- WHERE flow_ext_ref.ref_src_object_id = tmp.ref_src_object_id;
+CREATE INDEX /*i*/flow_ext_ref_workflow_id_idx_tmp ON /*_*/flow_ext_ref
+ (ref_src_workflow_id, ref_src_wiki);
diff --git a/db_patches/patch-reference_wiki-phase3.sql
b/db_patches/patch-reference_wiki-phase3.sql
index b255ef7..b511721 100644
--- a/db_patches/patch-reference_wiki-phase3.sql
+++ b/db_patches/patch-reference_wiki-phase3.sql
@@ -1,15 +1,32 @@
--- This is the final schema change required for
--- 'Segregate Reference objects by source wiki.'
+-- Phase 3 for adding ref_src_wiki field
+-- Back-fills the field from other data available in the database.
--
--- After phase 2 is complete (re-running does not affect any more rows),
--- this should be run.
+-- These updates are idempotent, but must be re-run until 0 rows are
+-- affected for all.
--- Mark field as not null
-ALTER TABLE /*_*/flow_wiki_ref MODIFY ref_src_wiki varchar(16) binary not null;
-ALTER TABLE /*_*/flow_ext_ref MODIFY ref_src_wiki varchar(16) binary not null;
+-- Populate wiki references with the appropriate wiki
+UPDATE
+ /*_*/flow_wiki_ref, (
+ SELECT ref_src_workflow_id, workflow_wiki
+ FROM /*_*/flow_wiki_ref, /*_*/flow_workflow
+ WHERE
+ flow_wiki_ref.ref_src_workflow_id =
flow_workflow.workflow_id AND
+ ( flow_wiki_ref.ref_src_wiki = '' OR
flow_wiki_ref.ref_src_wiki IS NULL )
+ LIMIT 1000
+ ) tmp
+ SET flow_wiki_ref.ref_src_wiki = tmp.workflow_wiki
+ WHERE flow_wiki_ref.ref_src_workflow_id = tmp.ref_src_workflow_id;
--- Drop old indexes
-DROP INDEX /*i*/flow_wiki_ref_idx ON /*_*/flow_wiki_ref;
-DROP INDEX /*i*/flow_wiki_ref_revision ON /*_*/flow_wiki_ref;
-DROP INDEX /*i*/flow_ext_ref_idx ON /*_*/flow_ext_ref;
-DROP INDEX /*i*/flow_ext_ref_revision ON /*_*/flow_ext_ref;
+
+-- Populate external references with the appropriate wiki.
+UPDATE
+ /*_*/flow_ext_ref, (
+ SELECT ref_src_workflow_id, workflow_wiki
+ FROM /*_*/flow_ext_ref, /*_*/flow_workflow
+ WHERE
+ flow_ext_ref.ref_src_workflow_id =
flow_workflow.workflow_id AND
+ ( flow_ext_ref.ref_src_wiki = '' OR
flow_ext_ref.ref_src_wiki IS NULL )
+ LIMIT 1000
+ ) tmp
+ SET flow_ext_ref.ref_src_wiki = tmp.workflow_wiki
+ WHERE flow_ext_ref.ref_src_workflow_id = tmp.ref_src_workflow_id;
diff --git a/db_patches/patch-reference_wiki-phase4.sql
b/db_patches/patch-reference_wiki-phase4.sql
new file mode 100644
index 0000000..898dc10
--- /dev/null
+++ b/db_patches/patch-reference_wiki-phase4.sql
@@ -0,0 +1,18 @@
+-- This is the final schema change required for
+-- 'Segregate Reference objects by source wiki.'
+--
+-- After phase 3 is complete (re-running does not affect any more rows),
+-- this should be run.
+
+-- Mark field as not null
+ALTER TABLE /*_*/flow_wiki_ref MODIFY ref_src_wiki varchar(16) binary not null;
+ALTER TABLE /*_*/flow_ext_ref MODIFY ref_src_wiki varchar(16) binary not null;
+
+-- Drop old indexes
+DROP INDEX /*i*/flow_wiki_ref_idx ON /*_*/flow_wiki_ref;
+DROP INDEX /*i*/flow_wiki_ref_revision ON /*_*/flow_wiki_ref;
+DROP INDEX /*i*/flow_wiki_ref_workflow_id_idx_tmp ON /*_*/flow_wiki_ref;
+
+DROP INDEX /*i*/flow_ext_ref_idx ON /*_*/flow_ext_ref;
+DROP INDEX /*i*/flow_ext_ref_revision ON /*_*/flow_ext_ref;
+DROP INDEX /*i*/flow_ext_ref_workflow_id_idx_tmp ON /*_*/flow_ext_ref;
diff --git a/db_patches/patch-reference_wiki.sql
b/db_patches/patch-reference_wiki.sql
index cefa33e..63b7778 100644
--- a/db_patches/patch-reference_wiki.sql
+++ b/db_patches/patch-reference_wiki.sql
@@ -8,21 +8,11 @@
DROP INDEX /*i*/flow_wiki_ref_idx ON /*_*/flow_wiki_ref;
DROP INDEX /*i*/flow_wiki_ref_revision ON /*_*/flow_wiki_ref;
--- Populate header and summary references with the appropriate wiki
+-- Populate wiki references with the appropriate wiki
UPDATE /*_*/flow_wiki_ref, /*_*/flow_workflow
SET flow_wiki_ref.ref_src_wiki = flow_workflow.workflow_wiki
WHERE
- flow_wiki_ref.ref_src_object_id = flow_workflow.workflow_id AND
- flow_wiki_ref.ref_src_object_type IN ('header', 'post-summary');
-
-
--- Populate post references with the appropriate wiki.
-UPDATE /*_*/flow_wiki_ref, /*_*/flow_tree_node, /*_*/flow_workflow
- SET flow_wiki_ref.ref_src_wiki = flow_workflow.workflow_wiki
- WHERE
- flow_wiki_ref.ref_src_object_id =
flow_tree_node.tree_descendant_id AND
- flow_tree_node.tree_ancestor_id = flow_workflow.workflow_id AND
- flow_wiki_ref.ref_src_object_type IN ('post');
+ flow_wiki_ref.ref_src_workflow_id = flow_workflow.workflow_id;
-- Recreate new indexes
CREATE INDEX /*i*/flow_wiki_ref_idx_v2 ON /*_*/flow_wiki_ref
@@ -38,21 +28,11 @@
DROP INDEX /*i*/flow_ext_ref_idx ON /*_*/flow_ext_ref;
DROP INDEX /*i*/flow_ext_ref_revision ON /*_*/flow_ext_ref;
--- Populate header and summary references
+-- Populate external references with the appropriate wiki
UPDATE /*_*/flow_ext_ref, /*_*/flow_workflow
SET flow_ext_ref.ref_src_wiki = flow_workflow.workflow_wiki
WHERE
- flow_ext_ref.ref_src_object_id = flow_workflow.workflow_id AND
- flow_ext_ref.ref_src_object_type IN ('header', 'post-summary');
-
-
--- Populate post references
-UPDATE /*_*/flow_ext_ref, /*_*/flow_tree_node, /*_*/flow_workflow
- SET flow_ext_ref.ref_src_wiki = flow_workflow.workflow_wiki
- WHERE
- flow_ext_ref.ref_src_object_id =
flow_tree_node.tree_descendant_id AND
- flow_tree_node.tree_ancestor_id = flow_workflow.workflow_id AND
- flow_ext_ref.ref_src_object_type IN ('post');
+ flow_ext_ref.ref_src_workflow_id = flow_workflow.workflow_id;
-- Recreate new indexes
CREATE INDEX /*i*/flow_ext_ref_idx_v2 ON /*_*/flow_ext_ref
--
To view, visit https://gerrit.wikimedia.org/r/237453
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: merged
Gerrit-Change-Id: I695d48e56f54303f8694b090569cd2b62322fc3b
Gerrit-PatchSet: 4
Gerrit-Project: mediawiki/extensions/Flow
Gerrit-Branch: master
Gerrit-Owner: Mattflaschen <mflasc...@wikimedia.org>
Gerrit-Reviewer: Catrope <roan.katt...@gmail.com>
Gerrit-Reviewer: Jcrespo <jcre...@wikimedia.org>
Gerrit-Reviewer: Mattflaschen <mflasc...@wikimedia.org>
Gerrit-Reviewer: Matthias Mullie <mmul...@wikimedia.org>
Gerrit-Reviewer: Springle <sprin...@wikimedia.org>
Gerrit-Reviewer: jenkins-bot <>
_______________________________________________
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
