FIX: Truncate topic_links.url to 500 chars during remap

FIX: Truncate topic_links.url to 500 chars during remap

This column often breaks remaps because of some weird, long URLs. The data isn’t that important, so truncating a couple of URLs doesn’t hurt that much.

diff --git a/lib/db_helper.rb b/lib/db_helper.rb
index 33fd4c6..ecc1975 100644
--- a/lib/db_helper.rb
+++ b/lib/db_helper.rb
@@ -5,7 +5,7 @@ require_dependency "migration/base_dropper"
 class DbHelper
 
   REMAP_SQL ||= <<~SQL
-    SELECT table_name, column_name
+    SELECT table_name, column_name, character_maximum_length
       FROM information_schema.columns
      WHERE table_schema = 'public'
        AND is_updatable = 'YES'
@@ -19,17 +19,23 @@ class DbHelper
      WHERE trigger_name LIKE '%_readonly'
   SQL
 
+  TRUNCATABLE_COLUMNS ||= [
+    'topic_links.url'
+  ]
+
   def self.remap(from, to, anchor_left: false, anchor_right: false, excluded_tables: [], verbose: false)
     like = "#{anchor_left ? '' : "%"}#{from}#{anchor_right ? '' : "%"}"
     text_columns = find_text_columns(excluded_tables)
 
     text_columns.each do |table, columns|
       set = columns.map do |column|
-        "#{column} = REPLACE(#{column}, :from, :to)"
+        replace = "REPLACE(#{column[:name]}, :from, :to)"
+        replace = truncate(replace, table, column)
+        "#{column[:name]} = #{replace}"
       end.join(", ")
 
       where = columns.map do |column|
-        "#{column} IS NOT NULL AND #{column} LIKE :like"
+        "#{column[:name]} IS NOT NULL AND #{column[:name]} LIKE :like"
       end.join(" OR ")
 
       rows = DB.exec(<<~SQL, from: from, to: to, like: like)
@@ -49,11 +55,13 @@ class DbHelper
 
     text_columns.each do |table, columns|
       set = columns.map do |column|
-        "#{column} = REGEXP_REPLACE(#{column}, :pattern, :replacement, :flags)"
+        replace = "REGEXP_REPLACE(#{column[:name]}, :pattern, :replacement, :flags)"
+        replace = truncate(replace, table, column)
+        "#{column[:name]} = #{replace}"
       end.join(", ")
 
       where = columns.map do |column|
-        "#{column} IS NOT NULL AND #{column} #{match} :pattern"
+        "#{column[:name]} IS NOT NULL AND #{column[:name]} #{match} :pattern"
       end.join(" OR ")
 
       rows = DB.exec(<<~SQL, pattern: pattern, replacement: replacement, flags: flags, match: match)
@@ -107,9 +115,20 @@ class DbHelper
       next if excluded_tables.include?(r.table_name) ||
         triggers.include?(Migration::BaseDropper.readonly_trigger_name(r.table_name, r.column_name))
 
-      text_columns[r.table_name] << r.column_name
+      text_columns[r.table_name] << {
+        name: r.column_name,
+        max_length: r.character_maximum_length
+      }
     end
 
     text_columns
   end
+
+  def self.truncate(sql, table, column)
+    if column[:max_length] && TRUNCATABLE_COLUMNS.include?("#{table}.#{column[:name]}")
+      "LEFT(#{sql}, #{column[:max_length]})"
+    else
+      sql
+    end
+  end
 end

GitHub sha: c05739c3

1 Like