Hello ! Following the latest thread on this [0] (and the older [1]) about implementation details for my GsoC project, aiming at adding category redirects / moves, I would need more input on the DB schema change that was being discussed, knowing that : * We expect category moves to be reversible * The only "feature" we are in fact adding is category redirects * I am considering this schema change as a good opportunity to fix bug #13579 [Categorylinks table should use category ID rather than category name in cl_to field] (please comment overthere if you see specific issues for that change) While thinking about that schema change, I considered the following use cases, considering categories A B and C : 1)Move existing A to empty B. 2)A and B contain pages : Redirect A to B. 3)A redirects to B : make A redirect to C 4)A redirects to B : undo the redirect, make A and B plain categories 5)A redirects to B : invert the redirect, make B redirect to A 6)On page edits, alter the category_links table 7)Listing pages that belong to a specific category Use case #1 is fairly easy to implement if cl_to points to a cat_id : you only have to rename the cat_title of the corresponding category table row, leaving the cat_id unchanged, and that's in fact the reason for switching cl_to' type == Original idea : add a cl_final field == The original idea, from the previous threads, was to change the category_links table : instead of only having the single cl_to pointing to the cat_id of the included category, add a cl_final integer field, also pointing to a cat_id, but this time the cat_id of the final category (see [2] ) : in other words, when category A redirects to category B, if a page includes category A, its category_links row' cl_to will point to category A' cat_id, and its cl_final will point to category B' cat_id. * Use cases #2, #3, and #4 : Expensive when A is a big category ! For each category_links row where cl_to = catA_cat_id, update cl_final, to respectively, B' cat_id, C' cat_id, and A' cat_id. * Use case #5 : Very expensive, for large A and B: you have to update every A and B category_links. * Use case #6 : When adding a category to a page, you have to fetch its corresponding cat_id for cl_to, (fairly easy), but you also have to fetch the right cl_final. You have to know first, if the category is a redirect. And if I'm right, with that schema, the only ways to tell this actually, are to retrieve the corresponding page_is_redirect in the page table, or to check for an entry in the redirect table. I believe that this forces us to compute a page-redirect join on page_id + a redirect-category join on page_title for each category title (see [3] ). If there's no results for that query, (can be caused if {1} the category page does not exist, {2} the category page exist but is not a redirect), the category is not a redirect, and else it returns us the cat_id for cl_final. * Use case #7 : Easy. SELECT ... FROM category_links WHERE cl_final = ## == But what about adding a cat_final field instead ? == When A redirects to B, all A' category_links entries will share the same cl_final field. Being quite unexperienced, and very naive, I may miss something important here... but I think that it makes much more sense to add that shared value to the category table, instead of duplicating it times the number of pages included in the category. I'm saying that instead of adding a cl_final field to the category_links table, we should perhaps add a cat_final field to the category table pointing to the final category it belongs to (see [4] ). * Use cases #2 #3 and #4 : Trivial. Change cat_final in one category table row. * Use case #5 : Damn. Tricky. Alter TWO category table rows :p * Use case #6 : Easy, fetch the corresponding cat_id to fill cl_to. * Use case #7 : The most expensive operation for this proposal. You have to join category_links and category (hopefully on cat_id) to retrieve what was cl_final in the first proposition, and select ... where cat_final = ### Evaluating the cost of a query is clearly one of the hardest things to do for that young me, and I fail to estimate the cost of that last query. How expensive is it compared to its use frequency ? Being apparently the most expensive part for this proposed change, the answer of that question will probably state how valid was my second approach ... I need your help to finalize the schema change needed to implement category redirects. I may also miss a third solution, even better, or have forgiven some important details while considering what has to be done : let me know :) [0] http://lists.wikimedia.org/pipermail/wikitech-l/2008-June/038495.html [1] http://lists.wikimedia.org/pipermail/wikitech-l/2008-April/thread.html#37218 [2] http://commons.wikimedia.org/wiki/Image:Mediawiki_schema_change_for_categor… [3] http://commons.wikimedia.org/wiki/Image:Mediawiki_use_case_category_redirec… [4] http://commons.wikimedia.org/wiki/Image:Mediawiki_schema_change_for_categor… -- Nicolas Dumazet — NicDumZ [ nIk.d̪ymz ]