Class: BeetleETL::TableDiff

Inherits:
Step
  • Object
show all
Defined in:
lib/beetle_etl/steps/table_diff.rb

Constant Summary collapse

IMPORTER_COLUMNS = 
i[
  external_id
  transition
]

Instance Attribute Summary

Attributes inherited from Step

#table_name

Instance Method Summary collapse

Methods inherited from Step

#database, #external_source, #initialize, #name, #stage_table_name, step_name, #target_schema

Constructor Details

This class inherits a constructor from BeetleETL::Step

Instance Method Details

#dependencies ⇒ Object 



9
10
11
 
# File 'lib/beetle_etl/steps/table_diff.rb', line 9

def dependencies
  [MapRelations.step_name(table_name)].to_set
end

#run ⇒ Object 



13
14
15
16
17
 
# File 'lib/beetle_etl/steps/table_diff.rb', line 13

def run
  %w(create update delete reinstate keep).each do |transition|
    public_send(:"transition_#{transition}")
  end
end

#transition_create ⇒ Object 



19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
# File 'lib/beetle_etl/steps/table_diff.rb', line 19

def transition_create
  database.execute "    UPDATE \"\#{target_schema}\".\"\#{stage_table_name}\" stage_update\n    SET\n      transition = 'CREATE',\n      id = NEXTVAL('\#{target_schema}.\#{table_name}_id_seq')\n    FROM \"\#{target_schema}\".\"\#{stage_table_name}\" stage\n    LEFT JOIN \"\#{target_schema}\".\"\#{table_name}\" target ON (\n      target.external_id = stage.external_id\n      AND target.external_source = '\#{external_source}'\n    )\n    WHERE stage_update.external_id = stage.external_id\n      AND target.external_id IS NULL\n  SQL\nend\n"

#transition_delete ⇒ Object 



55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
 
# File 'lib/beetle_etl/steps/table_diff.rb', line 55

def transition_delete
  database.execute "    INSERT INTO \"\#{target_schema}\".\"\#{stage_table_name}\"\n      (transition, id)\n    SELECT\n      'DELETE',\n      target.id\n    FROM \"\#{target_schema}\".\"\#{table_name}\" target\n    LEFT OUTER JOIN \"\#{target_schema}\".\"\#{stage_table_name}\" stage\n      ON (stage.external_id = target.external_id)\n    WHERE stage.external_id IS NULL\n    AND target.external_source = '\#{external_source}'\n    AND target.deleted_at IS NULL\n  SQL\nend\n"

#transition_keep ⇒ Object 



87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
 
# File 'lib/beetle_etl/steps/table_diff.rb', line 87

def transition_keep
  database.execute "    UPDATE \"\#{target_schema}\".\"\#{stage_table_name}\" stage_update\n    SET\n      transition = 'KEEP',\n      id = target.id\n    FROM \"\#{target_schema}\".\"\#{stage_table_name}\" stage\n    JOIN \"\#{target_schema}\".\"\#{table_name}\" target ON (\n      target.external_id = stage.external_id\n      AND target.external_source = '\#{external_source}'\n      AND target.deleted_at IS NULL\n      AND\n        (\#{target_record_columns.join(', ')})\n        IS NOT DISTINCT FROM\n        (\#{stage_record_columns.join(', ')})\n    )\n    WHERE stage_update.external_id = stage.external_id\n  SQL\nend\n"

#transition_reinstate ⇒ Object 



71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
 
# File 'lib/beetle_etl/steps/table_diff.rb', line 71

def transition_reinstate
  database.execute "    UPDATE \"\#{target_schema}\".\"\#{stage_table_name}\" stage_update\n    SET\n      transition = 'REINSTATE',\n      id = target.id\n    FROM \"\#{target_schema}\".\"\#{stage_table_name}\" stage\n    JOIN \"\#{target_schema}\".\"\#{table_name}\" target ON (\n      target.external_id = stage.external_id\n      AND target.external_source = '\#{external_source}'\n      AND target.deleted_at IS NOT NULL\n    )\n    WHERE stage_update.external_id = stage.external_id\n  SQL\nend\n"

#transition_update ⇒ Object 



35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
 
# File 'lib/beetle_etl/steps/table_diff.rb', line 35

def transition_update
  database.execute "    UPDATE \"\#{target_schema}\".\"\#{stage_table_name}\" stage_update\n    SET\n      transition = 'UPDATE',\n      id = target.id\n    FROM \"\#{target_schema}\".\"\#{stage_table_name}\" stage\n    JOIN \"\#{target_schema}\".\"\#{table_name}\" target ON (\n      target.external_id = stage.external_id\n      AND target.external_source = '\#{external_source}'\n      AND target.deleted_at IS NULL\n      AND\n        (\#{target_record_columns.join(', ')})\n        IS DISTINCT FROM\n        (\#{stage_record_columns.join(', ')})\n    )\n    WHERE stage_update.external_id = stage.external_id\n  SQL\nend\n"