Change Details

This isn't ready yet, but I want to write this down while I'm still thinking about it. It's currently blocked on the schema change being done for {T187089} which is adding the `img_description_id` field. There will be two rounds of changes needed. [ ] Round 1: Start reading from the new `img_description_id` field. [ ] Round 2: Stop reading from the `image_comment_temp` table. === Round 1 === The first round should be done after the schema change in T187089 is complete. * Add an index to the `image` table, likely `ALTER TABLE image ADD KEY img_description_id (img_description_id);` * On the `comment` table, adjust the image table EXISTS clause, something like ``` exists( select 1 from image where img_description_id = comment_id ) OR exists( select 1 from image JOIN image_comment_temp on (img_name = imgcomment_name) where img_description_id = 0 AND imgcomment_description_id = comment_id ) ``` * Adjust the `image` view itself: ** The `img_description_id` field should be something like `CASE WHEN img_description_id != 0 THEN img_description_id ELSE coalesce(imgcomment_description_id,0) END` ** For the `img_description` field, the test becomes `img_description_id = 0 AND (imgcomment_description_id is null OR imgcomment_description_id = 0)` ** The `comment` join condition becomes `on comment_id = CASE WHEN img_description_id != 0 THEN img_description_id ELSE imgcomment_description_id END` === Round 2 === The second round should be done after https://gerrit.wikimedia.org/r/417041 gets merged: * On the `comment` table, delete the EXISTS clause referring to `image_comment_temp`. * On the `image` view itself: ** The `img_description_id` field is just `img_description_id`, nothing fancy. ** For the `img_description` field, the test becomes just `img_description_id = 0` ** Remove the join against `image_comment_temp` ** The `comment` join condition becomes `on comment_id = img_description_id` * Remove the line in extra-wikireplicas-only-indexes.sql about the added index on `image_comment_temp`.

There will be two rounds of changes needed. [ ] Round 1: Start reading from the new `img_description_id` field. [ ] Round 2: Stop reading from the `image_comment_temp` table. === Round 1 === The first round should be done after the schema change in T187089 is complete. * Add an index to the `image` table, likely `ALTER TABLE image ADD KEY img_description_id (img_description_id);` * On the `comment` table, adjust the image table EXISTS clause, something like ``` exists( select 1 from image where img_description_id = comment_id ) OR exists( select 1 from image JOIN image_comment_temp on (img_name = imgcomment_name) where img_description_id = 0 AND imgcomment_description_id = comment_id ) ``` * Adjust the `image` view itself: ** The `img_description_id` field should be something like `CASE WHEN img_description_id != 0 THEN img_description_id ELSE coalesce(imgcomment_description_id,0) END` ** For the `img_description` field in image_compat, the test becomes `img_description_id = 0 AND (imgcomment_description_id is null OR imgcomment_description_id = 0)` ** The `comment` join condition becomes `on comment_id = CASE WHEN img_description_id != 0 THEN img_description_id ELSE imgcomment_description_id END` === Round 2 === The second round should be done after https://gerrit.wikimedia.org/r/417041 gets merged: * On the `comment` table, delete the EXISTS clause referring to `image_comment_temp`. * On the `image` view: ** The `img_description_id` field is just `img_description_id`, nothing fancy. ** Remove the join against `image_comment_temp` * On the `image_compat` view: ** The `img_description_id` field is just `img_description_id`, nothing fancy. ** For the `img_description` field, the test becomes just `img_description_id = 0` ** Remove the join against `image_comment_temp` ** The `comment` join condition becomes `on comment_id = img_description_id` * Remove the line in extra-wikireplicas-only-indexes.sql about the added index on `image_comment_temp`.

This isn't ready yet, but I want to write this down while I'm still thinking about it. It's currently blocked on the schema change being done for {T187089} which is adding the `img_description_id` field. There will be two rounds of changes needed. [ ] Round 1: Start reading from the new `img_description_id` field. [ ] Round 2: Stop reading from the `image_comment_temp` table. === Round 1 === The first round should be done after the schema change in T187089 is complete. * Add an index to the `image` table, likely `ALTER TABLE image ADD KEY img_description_id (img_description_id);` * On the `comment` table, adjust the image table EXISTS clause, something like ``` exists( select 1 from image where img_description_id = comment_id ) OR exists( select 1 from image JOIN image_comment_temp on (img_name = imgcomment_name) where img_description_id = 0 AND imgcomment_description_id = comment_id ) ``` * Adjust the `image` view itself: ** The `img_description_id` field should be something like `CASE WHEN img_description_id != 0 THEN img_description_id ELSE coalesce(imgcomment_description_id,0) END` ** For the `img_description` field in image_compat, the test becomes `img_description_id = 0 AND (imgcomment_description_id is null OR imgcomment_description_id = 0)` ** The `comment` join condition becomes `on comment_id = CASE WHEN img_description_id != 0 THEN img_description_id ELSE imgcomment_description_id END` === Round 2 === The second round should be done after https://gerrit.wikimedia.org/r/417041 gets merged: * On the `comment` table, delete the EXISTS clause referring to `image_comment_temp`. * On the `image` view itself: ** The `img_description_id` field is just `img_description_id`, nothing fancy. ** Remove the join against `image_comment_temp` * On the `image_compat` view: ** The `img_description_id` field is just `img_description_id`, nothing fancy. ** For the `img_description` field, the test becomes just `img_description_id = 0` ** Remove the join against `image_comment_temp` ** The `comment` join condition becomes `on comment_id = img_description_id` * Remove the line in extra-wikireplicas-only-indexes.sql about the added index on `image_comment_temp`.