Module: ForeignKeyChecker::Utils

Defined in:
lib/foreign_key_checker/utils.rb,
lib/foreign_key_checker/utils/belongs_to.rb

Defined Under Namespace

Modules: BelongsTo Classes: Result, UnsupportedConnectionAdapter

Class Method Summary collapse

Class Method Details

.get_columns(connection) ⇒ Object 



126
127
128
129
130
 
# File 'lib/foreign_key_checker/utils.rb', line 126

def self.get_columns(connection)
  get_tables.each_with_object({}) do |table, object|
    object[table] = connection.columns(table)
  end
end

.get_foreign_keys(model = ActiveRecord::Base) ⇒ Object

Raises:



10
11
12
13
14
15
16
 
# File 'lib/foreign_key_checker/utils.rb', line 10

def self.get_foreign_keys(model = ActiveRecord::Base)
  adapter = model.connection_db_config.configuration_hash[:adapter]
  raise(UnsupportedConnectionAdapter, adapter) unless %w[postgresql mysql2 sqlserver sqlite3].include?(adapter)

  connection = model.connection
  send("get_#{adapter}_foreign_keys", connection)
end

.get_foreign_keys_hash(model = ActiveRecord::Base) ⇒ Object 



18
19
20
21
22
23
 
# File 'lib/foreign_key_checker/utils.rb', line 18

def self.get_foreign_keys_hash(model = ActiveRecord::Base)
  get_foreign_keys(model).to_a.each_with_object({}) do |datum, obj|
    obj[datum.to_table] ||= []
    obj[datum.to_table].push(datum)
  end
end

.get_mysql2_foreign_keys(connection) ⇒ Object 



41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
# File 'lib/foreign_key_checker/utils.rb', line 41

def self.get_mysql2_foreign_keys(connection)
  res = connection.select_all <<-SQL
    SELECT
      fks.TABLE_NAME AS from_table,
      fks.COLUMN_NAME AS from_column,
      fks.REFERENCED_TABLE_NAME AS to_table,
      fks.REFERENCED_COLUMN_NAME AS to_column
    FROM information_schema.KEY_COLUMN_USAGE AS fks
    INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS rules ON rules.CONSTRAINT_NAME = fks.CONSTRAINT_NAME
    WHERE
      fks.CONSTRAINT_SCHEMA = DATABASE()
      AND rules.CONSTRAINT_SCHEMA = DATABASE();
  SQL
  res.to_a.map{|i| Result.new(i) }
end

.get_mysql2_tables(connection) ⇒ Object 



110
111
112
 
# File 'lib/foreign_key_checker/utils.rb', line 110

def self.get_mysql2_tables(connection)
  connection.select_all("SELECT table_name FROM information_schema.tables WHERE TABLE_SCHEMA = '#{connection.current_database}'").to_a.pluck('table_name')
end

.get_postgresql_foreign_keys(connection) ⇒ Object 



57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
 
# File 'lib/foreign_key_checker/utils.rb', line 57

def self.get_postgresql_foreign_keys(connection)
  res = connection.select_all <<-SQL
    SELECT
      tc.table_name AS from_table,
      kcu.column_name AS from_column,
      ccu.table_name AS to_table,
      ccu.column_name AS to_column
    FROM
      information_schema.table_constraints AS tc
      JOIN information_schema.key_column_usage AS kcu
        ON tc.constraint_name = kcu.constraint_name
        AND tc.table_schema = kcu.table_schema
      JOIN information_schema.constraint_column_usage AS ccu
        ON ccu.constraint_name = tc.constraint_name
        AND ccu.table_schema = tc.table_schema
    WHERE tc.constraint_type = 'FOREIGN KEY';
  SQL
  res.to_a.map{ |i| Result.new(i) }
end

.get_postgresql_tables(connection) ⇒ Object 



114
115
116
 
# File 'lib/foreign_key_checker/utils.rb', line 114

def self.get_postgresql_tables(connection)
  connection.select_all("SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema'").to_a.pluck('tablename')
end

.get_sqlite3_foreign_keys(connection) ⇒ Object 



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

def self.get_sqlite3_foreign_keys(connection)
  res = connection.select_all <<-SQL
    SELECT
      m.name as from_table,
      p."from" as from_column,
      p."table" as to_table,
      p."to" as to_column
    FROM
      sqlite_master m
      JOIN pragma_foreign_key_list(m.name) p ON 1
    WHERE m.type = 'table'
    ORDER BY m.name ;
  SQL
  res.to_a.map{|i| Result.new(i) }
end

.get_sqlite3_tables(connection) ⇒ Object 



118
119
120
 
# File 'lib/foreign_key_checker/utils.rb', line 118

def self.get_sqlite3_tables(connection)
  connection.select_all("SELECT name FROM sqlite_master WHERE type='table'").to_a.pluck('name') - ['sqlite_sequence']
end

.get_sqlserver_foreign_keys(connection) ⇒ Object 



77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
 
# File 'lib/foreign_key_checker/utils.rb', line 77

def self.get_sqlserver_foreign_keys(connection)
  res = connection.select_all <<-SQL
    SELECT  obj.name AS FK_NAME,
        sch.name AS [schema_name],
        tab1.name AS [from_table],
        col1.name AS [from_column],
        tab2.name AS [to_table],
        col2.name AS [to_column]
    FROM sys.foreign_key_columns fkc
    INNER JOIN sys.objects obj
        ON obj.object_id = fkc.constraint_object_id
    INNER JOIN sys.tables tab1
        ON tab1.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas sch
        ON tab1.schema_id = sch.schema_id
    INNER JOIN sys.columns col1
        ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
    INNER JOIN sys.tables tab2
        ON tab2.object_id = fkc.referenced_object_id
    INNER JOIN sys.columns col2
        ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
  SQL
  res.to_a.map { |i| Result.new(i) }
end

.get_sqlserver_tables(connection) ⇒ Object 



122
123
124
 
# File 'lib/foreign_key_checker/utils.rb', line 122

def self.get_sqlserver_tables(connection)
  connection.tables
end

.get_tables(model = ActiveRecord::Base) ⇒ Object

Raises:



102
103
104
105
106
107
108
 
# File 'lib/foreign_key_checker/utils.rb', line 102

def self.get_tables(model = ActiveRecord::Base)
  adapter = model.connection_db_config.configuration_hash[:adapter]
  raise(UnsupportedConnectionAdapter, adapter) unless %w[postgresql mysql2 sqlite3 sqlserver].include?(adapter)

  connection = model.connection
  send("get_#{adapter}_tables", connection)
end