Module: DirectoryDiff::Transformer::SQL

Defined in:

lib/directory_diff/transformer/temp_table.rb

Class Method Summary

• .cleanup_sql(table_name) ⇒ Object

  Cleanup some bad records 1.

• .current_directory_projection ⇒ Object
• .insert_into_operations(table_name, sql) ⇒ Object
• .insert_into_temp_csv_table(table_name, values) ⇒ Object
• .latest_unique_sql(table_name) ⇒ Object

  Remove dupe email rows, keeping the last one.

Class Method Details

.cleanup_sql(table_name) ⇒ Object

Cleanup some bad records

1. Assistant email is set on an employee, but no assistant record in csv. Remove the assistant email.

2. Assistant email is employee’s own email. Remove the assistant email.

TODO move this into the temp table creation above www.db-fiddle.com/f/gxg6qABP1LygYvvgRvyH2N/1

# File 'lib/directory_diff/transformer/temp_table.rb', line 269

def self.cleanup_sql(table_name)
  <<-SQL
    with
      unnested_assistants as
      (
        select
          email,
          name,
          unnest(assistants) assistant
        from #{table_name}
      ),
      own_email_removed as
      (
        select
          a.*
        from unnested_assistants a
        where a.email != a.assistant
      ),
      missing_assistants_removed as
      (
        select
          a.*
        from own_email_removed a
        left outer join #{table_name} b on a.assistant = b.email
        where
          (a.assistant is null and b.email is null)
          or (a.assistant is not null and b.email is not null)
      ),
      only_valid_assistants as
      (
        select
          a.email,
          a.name,
          array_remove(
            array_agg(b.assistant),
            null
          ) assistants
        from #{table_name} a
        left outer join missing_assistants_removed b
        using (email)
        group by
          a.email, a.name
      )
    update #{table_name}
    set assistants = only_valid_assistants.assistants
    from only_valid_assistants
    where #{table_name}.email = only_valid_assistants.email
  SQL
end

.current_directory_projection ⇒ Object

# File 'lib/directory_diff/transformer/temp_table.rb', line 343

def self.current_directory_projection
  <<-SQL
    name,
    lower(email) email,
    coalesce(phone_number, '') phone_number,
    array_remove(
      regexp_split_to_array(
        coalesce(assistants, ''),
        '\s*,\s*'
      )::varchar[],
      ''
    ) assistants
  SQL
end

.insert_into_operations(table_name, sql) ⇒ Object

# File 'lib/directory_diff/transformer/temp_table.rb', line 358

def self.insert_into_operations(table_name, sql)
  <<-SQL
    insert into #{table_name}(
      operation,
      row_number,
      name,
      email,
      phone_number,
      assistants,
      extra
    ) #{sql}
  SQL
end

.insert_into_temp_csv_table(table_name, values) ⇒ Object

# File 'lib/directory_diff/transformer/temp_table.rb', line 372

def self.insert_into_temp_csv_table(table_name, values)
  <<-SQL
    insert into #{table_name}(
      name,
      email,
      phone_number,
      assistants,
      extra
    ) values #{values.join(", ")}
  SQL
end

.latest_unique_sql(table_name) ⇒ Object

Remove dupe email rows, keeping the last one

# File 'lib/directory_diff/transformer/temp_table.rb', line 320

def self.latest_unique_sql(table_name)
  <<-SQL
    SELECT
      DISTINCT ON (lower(email)) name,
      lower(email) email,
      coalesce(phone_number, '') phone_number,
      array_remove(
        regexp_split_to_array(
          coalesce(assistants, ''),
          '\s*,\s*'
        )::varchar[],
        ''
      ) assistants,
      extra,
      ROW_NUMBER () OVER ()
    FROM
      #{table_name}
    ORDER BY
      lower(email),
      row_number desc
  SQL
end