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 



25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
# File 'lib/eve_app/sde/normalizer.rb', line 25

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[:target_column_name] == 'id' && table_info(row[:table_name]).add_primary
      next
    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 



41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
# File 'lib/eve_app/sde/normalizer.rb', line 41

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)
      sql %Q(ALTER TABLE #{row[:table_name]} ADD PRIMARY KEY (id))
    end
  end

  # Custom indexes
  # sql %Q(CREATE INDEX IF NOT EXISTS idx_type_attributes_type_id_attribute_id ON type_attributes (type_id, attribute_id);)
end

#normalize ⇒ Object 



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

def normalize
  normalize_table_names
  primary_keys
  column_names
  normalize_inv_tables if table_names.key?('invTypes')
  indexes
end

#normalize_inv_tables ⇒ Object 



56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
 
# File 'lib/eve_app/sde/normalizer.rb', line 56

def normalize_inv_tables
  sql %Q(ALTER TABLE #{table_names['invTypes']} ADD IF NOT EXISTS category_id integer)
  sql %Q(ALTER TABLE #{table_names['invTypes']} ADD IF NOT EXISTS category_name character varying)
  sql %Q(ALTER TABLE #{table_names['invTypes']} ADD IF NOT EXISTS group_name character varying)
  sql %Q(ALTER TABLE #{table_names['invTypes']} ADD IF NOT EXISTS market_group_name character varying)
  sql %Q(ALTER TABLE #{table_names['invTypes']} ADD IF NOT EXISTS market_group_root_id integer)
  sql %Q(ALTER TABLE #{table_names['invTypes']} ADD IF NOT EXISTS blueprint_type_id integer)

  sql %Q(UPDATE #{table_names['invTypes']} SET group_name = (SELECT name FROM #{table_names['invGroups']} WHERE id = #{table_names['invTypes']}.group_id))
  sql %Q(UPDATE #{table_names['invTypes']} SET category_id = (SELECT category_id FROM #{table_names['invGroups']} WHERE id = #{table_names['invTypes']}.group_id))
  sql %Q(UPDATE #{table_names['invTypes']} SET category_name = (SELECT name FROM #{table_names['invCategories']} WHERE id = #{table_names['invTypes']}.category_id))
  sql %Q(UPDATE #{table_names['invTypes']} SET market_group_name = (SELECT name FROM #{table_names['invMarketGroups']} WHERE id = #{table_names['invTypes']}.market_group_id))
  # sql %Q(UPDATE #{table_names['invTypes']} SET blueprint_type_id = (SELECT type_id FROM #{table_names['industryActivityProducts']} WHERE activity_id = 1 AND product_type_id = #{table_names['invTypes']}.id LIMIT 1))

  sql %Q(ALTER TABLE #{table_names['invMarketGroups']} ADD IF NOT EXISTS root_group_id INTEGER DEFAULT NULL)

  sql %Q(
    WITH RECURSIVE mg_roots(id, root_id) AS (
      SELECT mg.id, mg.id AS root_id FROM #{table_names['invMarketGroups']} AS mg WHERE mg.parent_group_id IS NULL
      UNION ALL
        SELECT c.id, p.root_id FROM mg_roots AS p, #{table_names['invMarketGroups']} AS c WHERE c.parent_group_id = p.id
    )
    UPDATE #{table_names['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_names['invMarketGroups']}.id = mg_roots.id;
  )
  sql %Q(UPDATE #{table_names['invTypes']} SET market_group_root_id = (SELECT root_group_id FROM #{table_names['invMarketGroups']} WHERE id = #{table_names['invTypes']}.market_group_id))
end

#normalize_table_names ⇒ Object 



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

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

#primary_keys ⇒ Object 



18
19
20
21
22
23
 
# File 'lib/eve_app/sde/normalizer.rb', line 18

def primary_keys
  tables = table_list.select { |_, info| !!info[:add_primary] }
  tables.values.each do |info|
    sql %Q(ALTER TABLE #{info[:name]} ADD id SERIAL PRIMARY KEY)
  end
end