Bulk updates

[jwoertink]

Related: #662

We technically have a bulk update already through the query objects.

# Updates all profiles locked value to false
ProfileQuery.new.update(locked: false)

This is handy, but if you're looking to update a bunch of records and each one potentially will get different values, that gets a little more tricky.

In this case, you'll want to pass in an array of all of the updates you want to make. I do have this "working" in my app, but it's very messy and brittle, so I'd like to try and figure out a better way that can also conform to Avram's principals of type-safety.

class Test < BaseModel
  enum State
    Good
    Bad
  end
  table do
    primary_key id : UUID
    column foo : Test::State
    column bar : String
    column baz : Array(String)
  end
end

def self.update_all(params : Array(Hash(String, String)), &)
  sql = <<-SQL
  UPDATE tests SET foo = data_table.foo, bar = data_table.bar, baz = string_to_array(data_table.baz, ',')
  FROM (
    SELECT unnest($1::uuid[]) AS id, unnest($2::integer[]) AS foo, unnest($3::text[]) AS bar, unnest($4::text[]) AS baz
  ) AS data_table
  WHERE tests.id = data_table.id
  RETURNING tests.*
  SQL

  ids = params.map(&.["id"])
  foos = params.map(&.["foo"].to_i)
  bars = params.map(&.["bar"])
  # should look like an array of CSV strings
  bazs = params.map(&.["baz"])
  inputs = [ids, foos, bars, bazs] # Can we even guarantee that these will always be in the correct order here?
  # if they're not all the same size, this will fail
  inputs_are_same_size = inputs.map(&.size).uniq!.first == ids.size

  if inputs_are_same_size
    args = inputs.map { |group| PQ::Param.encode_array(group) }
    records = AppDatabase.query_all(sql, args: args, as: Test)
    yield self.new, records
  else
    yield nil, nil
  end
end

Some of the questions/issues I have here

• Enums have to be passed in as stringified integers here because params in Lucky is Hash(String, String)
• I couldn't figure out how to cast the baz to array of array of strings...
• Each arg to be an array of that column's data, so we can't do things like [id, foo, bar, baz], [id, foo, bar, baz]
• How do you handle errors? We'd have to some how map the error to the specific column that failed so you're not digging through 50k rows to figure out where waldo is.
• Notice the arrays have to be pre-encoded before being passed in. That's a whole thing....

I'm sure there's a lot more, so if anyone has some insight in to how this could work reliably, I'm open to ideas.