db.rake

namespace :db do
  namespace :structure do
    # A hack to post-process structure.sql so that it's normalized to a
    # canonical format that is unlikely to have spurious diffs.
    task :dump do
      structure_fname = File.join(Rails.root, "db/structure.sql")
      raw_contents = File.read(structure_fname)

      # 1. ditch AUTO_INCREMENT= clauses.
      raw_contents = raw_contents.
        gsub(/\s*AUTO_INCREMENT=\d+(.*?;)$/, '\\1')

      # 2. Normalize the order of various declarations, to avoid issues when
      #    people have run migrations in different orders.
      #     1. First, break things up into separate statements by looking for
      #        semicolons at the end of the line or end of the file.  Discard
      #        the semicolons and any subquent newlines.
      statements = raw_contents.split(/;(?:\n+|$)/)
      #     2. Now, separate out `CREATE TABLE` statements vs. everything else
      #        (definitely includes `INSERT`s, may include `CREATE VIEW`, etc).
      declarations = (statements.select { |stmt| stmt =~ /^CREATE TABLE/ })
      everything_else = (statements - declarations)
      #     3. Break up the `CREATE TABLE` statement into its constituent
      #        declarations and reorganize each discrete group.
      #        TODO: Put PK columns at the top, and `(created|updated)_(at|on)`
      #        TODO: at the bottom.
      declarations = declarations.
        map do |stmt|
          header = nil
          footer = nil
          columns = []
          pk = nil
          unique_keys = []
          keys = []
          constraints = []

          stmt.
            split(/,?\n+/).
            map do |line|
              line = line.rstrip
              case
              when line =~ /^CREATE TABLE/
                header = line
              when line =~ /^\)/
                footer = line
              when line =~ /^\s*`/
                columns << line
              when line =~ /^\s*PRIMARY KEY/
                pk = line
              when line =~ /^\s*UNIQUE/
                unique_keys << line
              when line =~ /^\s*KEY/
                keys << line
              when line =~ /\s*CONSTRAINT/
                constraints << line
              else
                raise "Uh oh, couldn't parse the following statement:\n\n#{stmt}"
              end
            end

          # Figure out which columns are part of our PK, and what order they
          # appear in...
          if(pk)
            pk_column_set = Set.new(pk.sub(/^.*?\(/, '').sub(/\)$/, '').gsub(/`/, '').split(/,\s*/))
          else
            # No primary key.  UGH!
            # TODO: Check to see if we have an inline definition on one of the columns?
            pk_column_set = Set.new
          end

          # Separate the columns into relevant groupins...
          pk_columns = columns.select { |col| colname = col.gsub(/(^.*?`)|(`.*?$)/, ''); pk_column_set.include?(colname) }
          ts_columns = columns.select { |col| col =~ /^\s*`(created|updated)_(at|on)`/ }
          other_columns = (columns - pk_columns) - ts_columns

          # Bring the groupings back together with each group sorted how we'd
          # like.
          columns = [
            # PK columns always come first, in the order they're specified in.
            pk_columns.sort { |a,b| pk_column_set.find_index(a) <=> pk_column_set.find_index(b) },
            other_columns.sort,
            # Rails timestamp columns always come last.
            ts_columns.sort,
          ].flatten

          body = [
            columns,
            pk,
            unique_keys.sort,
            keys.sort,
            constraints.sort
          ].flatten.reject { |line| line.try(:strip).blank? }.join(",\n")

          [
            header,
            body,
            footer
          ].flatten.join("\n")
        end

      #     4. Sort and recombine everything, with `CREATE TABLE` statements
      #        first, then everything else after.
      cleansed_contents = (declarations.sort + everything_else.sort).
        flatten.
        map { |statement| statement + ";" }.
        join("\n\n")

      # 3. Rewrite the file.
      File.atomic_write(structure_fname) do |fh|
        fh.write(cleansed_contents)
        fh.write("\n") # Add a trailing newline so interactive staging of the
                       # last block of the file is easier.
      end
    end
  end
end