module DirectoryDiff::Transformer::SQL

Public Class Methods

cleanup_sql(table_name) click to toggle source

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() click to toggle source
# 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) click to toggle source
# 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) click to toggle source
# 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) click to toggle source

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