lib/tasks/backfill_changeset_stats.rake

   1 # frozen_string_literal: true
   2
   3 # rubocop:disable Metrics/BlockLength
   4 namespace :db do
   5   desc "Backfill enhanced changeset stats"
   6   task :changeset_stats => :environment do
   7     chunk_size = ENV["CHUNK_SIZE"]&.to_i || 10_000
   8
   9     min_id = ENV["MIN_CHANGESET"]&.to_i || Changeset.minimum(:id)
  10     max_id = ENV["MAX_CHANGESET"]&.to_i || Changeset.maximum(:id)
  11
  12     puts "Backfilling enhanced changeset stats from id #{min_id} to #{max_id} in chunks of #{chunk_size}"
  13
  14     Changeset.where(:id => min_id..max_id)
  15              .where('(num_created_nodes + num_modified_nodes + num_deleted_nodes +
  16                       num_created_ways + num_modified_ways + num_deleted_ways +
  17                       num_created_relations + num_modified_relations + num_deleted_relations)
  18                      != num_changes')
  19              .in_batches(:of => chunk_size) do |batch|
  20       ids = batch.ids
  21       print "Processing changesets #{ids.first} to #{ids.last} ..."
  22
  23       id_list = "{#{ids.join(',')}}"
  24
  25       # Backfill enhanced changeset stats
  26       sql = <<~SQL.squish
  27         WITH changes AS (
  28             SELECT
  29               nodes.changeset_id,
  30               CASE WHEN nodes.version = 1 THEN 1 ELSE 0 END AS num_created_nodes,
  31               CASE WHEN nodes.version > 1 AND nodes.visible THEN 1 ELSE 0 END AS num_modified_nodes,
  32               CASE WHEN nodes.version > 1 AND NOT nodes.visible THEN 1 ELSE 0 END AS num_deleted_nodes,
  33               0 AS num_created_ways,
  34               0 AS num_modified_ways,
  35               0 AS num_deleted_ways,
  36               0 AS num_created_relations,
  37               0 AS num_modified_relations,
  38               0 AS num_deleted_relations
  39             FROM nodes
  40             WHERE nodes.changeset_id = ANY($1::bigint[])
  41           UNION ALL
  42             SELECT
  43               ways.changeset_id,
  44               0 AS num_created_nodes,
  45               0 AS num_modified_nodes,
  46               0 AS num_deleted_nodes,
  47               CASE WHEN ways.version = 1 THEN 1 ELSE 0 END AS num_created_ways,
  48               CASE WHEN ways.version > 1 AND ways.visible THEN 1 ELSE 0 END AS num_modified_ways,
  49               CASE WHEN ways.version > 1 AND NOT ways.visible THEN 1 ELSE 0 END AS num_deleted_ways,
  50               0 AS num_created_relations,
  51               0 AS num_modified_relations,
  52               0 AS num_deleted_relations
  53             FROM ways
  54             WHERE ways.changeset_id = ANY($1::bigint[])
  55           UNION ALL
  56             SELECT
  57               relations.changeset_id,
  58               0 AS num_created_nodes,
  59               0 AS num_modified_nodes,
  60               0 AS num_deleted_nodes,
  61               0 AS num_created_ways,
  62               0 AS num_modified_ways,
  63               0 AS num_deleted_ways,
  64               CASE WHEN relations.version = 1 THEN 1 ELSE 0 END AS num_created_relations,
  65               CASE WHEN relations.version > 1 AND relations.visible THEN 1 ELSE 0 END AS num_modified_relations,
  66               CASE WHEN relations.version > 1 AND NOT relations.visible THEN 1 ELSE 0 END AS num_deleted_relations
  67             FROM relations
  68             WHERE relations.changeset_id = ANY($1::bigint[])
  69         ),
  70         total AS (
  71           SELECT
  72             changes.changeset_id,
  73             SUM(changes.num_created_nodes) AS num_created_nodes,
  74             SUM(changes.num_modified_nodes) AS num_modified_nodes,
  75             SUM(changes.num_deleted_nodes) AS num_deleted_nodes,
  76             SUM(changes.num_created_ways) AS num_created_ways,
  77             SUM(changes.num_modified_ways) AS num_modified_ways,
  78             SUM(changes.num_deleted_ways) AS num_deleted_ways,
  79             SUM(changes.num_created_relations) AS num_created_relations,
  80             SUM(changes.num_modified_relations) AS num_modified_relations,
  81             SUM(changes.num_deleted_relations) AS num_deleted_relations
  82           FROM changes
  83           GROUP BY changes.changeset_id
  84         )
  85         UPDATE changesets
  86         SET num_created_nodes      = total.num_created_nodes,
  87             num_modified_nodes     = total.num_modified_nodes,
  88             num_deleted_nodes      = total.num_deleted_nodes,
  89             num_created_ways       = total.num_created_ways,
  90             num_modified_ways      = total.num_modified_ways,
  91             num_deleted_ways       = total.num_deleted_ways,
  92             num_created_relations  = total.num_created_relations,
  93             num_modified_relations = total.num_modified_relations,
  94             num_deleted_relations  = total.num_deleted_relations
  95         FROM total
  96         WHERE changesets.id = total.changeset_id
  97       SQL
  98
  99       binds = [
 100         ActiveRecord::Relation::QueryAttribute.new(
 101           "ids",
 102           id_list,
 103           ActiveRecord::Type::String.new
 104         )
 105       ]
 106       rows_affected = ActiveRecord::Base.connection.exec_update(sql, "UpdateStats", binds)
 107       puts " #{rows_affected} changesets updated."
 108     end
 109
 110     puts "\nDone."
 111   end
 112 end
 113 # rubocop:enable Metrics/BlockLength