module DirectoryDiff::Transformer::SQL
Public Class Methods
cleanup_sql(table_name)
click to toggle source
Cleanup some bad records
-
Assistant email is set on an employee, but no assistant record in csv. Remove the assistant email.
-
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