project_position_balancer.rb

# frozen_string_literal: true

# See the comments in the ProjectPositionSequencer class for info about how the gaps are set
# for the "position" of the Project records.

# Goals of this class:
#   * Reset the positions so that they have proper gaps between records.
#   * Try to perform the updates in fewer SQL queries, so that it's not 1 update per record.

# Assumption: It's okay if the update takes a long time.  For now, the rebalancer is rarely
# needed, and it is run manually.  It won't be run at a time-sensitive moment, like for
# example, during an HTTP request/response.

# Assumption: We don't need to lock the records during the rebalance.  During prototype
# phase, we don't yet have any situation of records being editing during rebalance.  In the
# future, project lists will be scoped by user, and rebalancing will be more selectively
# targeted.

# Assumption: There will never be enough Project records (scoped per user) that we need to
# worry about hitting the max value for the position column.  So for now, don't worry about
# that edge case.  Just let Postgres throw an error.
# (See: ProjectPositionSequencer.max_position)

class ProjectPositionBalancer

  def self.rebalance
    Project.connection.exec_update(rebalancing_query)
  end

  def self.gap
    ProjectPositionSequencer.gap
  end

  # A self-join on the "projects" table (P1, P2, P3).
  #
  # The "P2" sub-query does this:
  #   1. Gets a list of all Project records in the correct
  #      order by position.
  #   2. Uses a window function called row_number() to
  #      label each record with its row number in the
  #      ordered list.
  #
  # Then I can rebalance the gaps just by multiplying the
  # record's row number by the desired gap.
  #
  def self.rebalancing_query
    <<-SQL
      UPDATE projects P1
      SET position = P3.row_num * #{gap}
      FROM (
        SELECT P2.position, row_number() over (ORDER BY P2.position) AS row_num
        FROM projects P2
      ) P3
      WHERE P3.position = P1.position;
    SQL
  end

end