[noosfero/noosfero][master] 2 commits: tags: migration to fix tags case differences

[Larissa Reis]

Larissa Reis pushed to branch master at Noosfero / noosfero


Commits:
eebc6b8b by Rodrigo Souto at 2015-06-11T10:36:02Z
tags: migration to fix tags case differences

Whenever you have 2 or more tags with the same name but different cases,
ActsAsTaggableOn returns an empty list of objects tagged with either of
the tags. To solve this problem, we must not have tags with different
cases stored.

Performance was my primal concern on this migration since we have
instances that have over 130k tags registered. So I decided to convert
every tag to lower case.  This is the fastest way I could conceive this
migration and still it might take a lot of time. Here is basic resume of
what it basically does:

  x: number of new downcased tags created.
  y: number of oddcased tags.
  z: number of tags

  1. Find all tags do not have a downcased form already created - [1 fast select query].
  2. Create a downcased version of the above queries - [x slow update queries but
  n is usually low because a minority of tags have odd case and all
  different cases of a single word generate only 1 query].
  3. Update taggings relations based on new ids - [1 slow update and 2 join queries].
  4. Updates the taggings_count of every tag - [1 slow update with z fast selects]
  5. Delete all unused tags tags - [1 slow delete query].

Signed-off-by: Larissa Reis <larissa at colivre.coop.br>

- - - - -
088735b3 by Larissa Reis at 2015-06-11T10:42:01Z
Merge branch 'tags-case' into master

See merge request !563

- - - - -


1 changed file:

- + db/migrate/20150423203352_fix_tags_case_differences.rb


Changes:

=====================================
db/migrate/20150423203352_fix_tags_case_differences.rb
=====================================
--- /dev/null
+++ b/db/migrate/20150423203352_fix_tags_case_differences.rb
@@ -0,0 +1,19 @@
+class FixTagsCaseDifferences < ActiveRecord::Migration
+  def up
+    tags = ActsAsTaggableOn::Tag.joins('LEFT JOIN tags as b on LOWER(tags.name) = b.name').where('b.id is null')
+    tags.find_each do |tag|
+      unless ActsAsTaggableOn::Tag.exists?(:name => tag.name.mb_chars.downcase)
+        ActsAsTaggableOn::Tag.create(:name => tag.name.mb_chars.downcase)
+      end
+    end
+
+    execute("UPDATE taggings SET tag_id = new.id FROM taggings AS t INNER JOIN tags AS old ON t.tag_id = old.id INNER JOIN tags AS new ON LOWER(old.name) = new.name WHERE old.id != new.id AND taggings.id = t.id")
+
+    execute("UPDATE tags SET taggings_count = (SELECT COUNT(*) FROM taggings WHERE taggings.tag_id = tags.id)")
+    execute("DELETE FROM tags WHERE taggings_count = 0")
+  end
+
+  def down
+    say 'This migration is irreversible.'
+  end
+end



View it on GitLab: https://gitlab.com/noosfero/noosfero/compare/944722f4313f435988e34f9e049f373551a7a997...088735b39ae92117aa914c2116f5410892cf89dd
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://listas.softwarelivre.org/pipermail/noosfero-dev/attachments/20150611/5281a8df/attachment.html>