Module: EveApp::SDE::Normalizer

Included in:
DataImporter
Defined in:
lib/eve_app/sde/normalizer.rb

Instance Method Summary collapse

Instance Method Details

#column_names ⇒ Object 


17
18
19
20
21
22
23
24
25
26
27
28
29
 
# File 'lib/eve_app/sde/normalizer.rb', line 17

def column_names
  columns.each do |row|
    resource_name = row[:table_name].singularize.gsub('eve_', '')
    row[:target_column_name] = row[:column_name].underscore
    if row[:target_column_name].starts_with?(resource_name)
      row[:target_column_name] = row[:target_column_name].gsub("#{resource_name}_", "")
    end

    if row[:column_name] != row[:target_column_name]
      sql %Q(ALTER TABLE "#{row[:table_name]}" RENAME COLUMN "#{row[:column_name]}" TO "#{row[:target_column_name]}")
    end
  end
end

#indexes ⇒ Object 


31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
 
# File 'lib/eve_app/sde/normalizer.rb', line 31

def indexes
  columns.each do |row|
    if (row[:column_name].ends_with?('id') || row[:column_name] == 'name') && !has_index?(db, row[:table_name], row[:column_name])
      sql %Q(CREATE INDEX IF NOT EXISTS idx_#{row[:table_name]}_#{row[:column_name]} ON #{row[:table_name]} (#{row[:column_name]});)
    end
    if row[:column_name] == 'id'
      sql %Q(ALTER TABLE #{row[:table_name]} DROP CONSTRAINT IF EXISTS #{row[:table_name]}_pkey)

      if complex_id_index?(row[:table_name])
        sql %Q(ALTER TABLE #{row[:table_name]} ADD PRIMARY KEY (id, type_id))
      else
        sql %Q(ALTER TABLE #{row[:table_name]} ADD PRIMARY KEY (id))
      end
    end
  end
end

#missing_relations ⇒ Object 


48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
# File 'lib/eve_app/sde/normalizer.rb', line 48

def missing_relations
  sql %Q(ALTER TABLE #{table_list['invTypes']} ADD IF NOT EXISTS category_id integer)
  sql %Q(ALTER TABLE #{table_list['invMarketGroups']} ADD root_group_id INTEGER DEFAULT NULL)
  sql %Q(ALTER TABLE #{table_list['invTypes']} ADD market_group_root_id integer)
  sql %Q(UPDATE #{table_list['invTypes']} SET category_id = (SELECT category_id FROM #{table_list['invGroups']} WHERE id = #{table_list['invTypes']}.group_id))
  sql %Q(
    WITH RECURSIVE mg_roots(id, root_id) AS (
      SELECT mg.id, mg.id AS root_id FROM #{table_list['invMarketGroups']} AS mg WHERE mg.parent_group_id IS NULL
      UNION ALL
        SELECT c.id, p.root_id FROM mg_roots AS p, #{table_list['invMarketGroups']} AS c WHERE c.parent_group_id = p.id
    )
    UPDATE #{table_list['invMarketGroups']} SET root_group_id = mg_roots.root_id FROM (
      SELECT id, root_id FROM mg_roots WHERE root_id != id
    ) AS mg_roots WHERE #{table_list['invMarketGroups']}.id = mg_roots.id;
  )
  sql %Q(UPDATE #{table_list['invTypes']} SET market_group_root_id = (SELECT root_group_id FROM #{table_list['invMarketGroups']} WHERE id = #{table_list['invTypes']}.market_group_id))
end

#normalize ⇒ Object 


4
5
6
7
8
9
 
# File 'lib/eve_app/sde/normalizer.rb', line 4

def normalize
  table_names
  column_names
  missing_relations
  indexes
end

#table_names ⇒ Object 


11
12
13
14
15
 
# File 'lib/eve_app/sde/normalizer.rb', line 11

def table_names
  table_list.each do |table_name, normalized_name|
    sql %Q(ALTER TABLE IF EXISTS "#{table_name}" RENAME TO "#{normalized_name}")
  end
end