Class: Dbtools::Database::MysqlConnection

Inherits:
DbConnection show all
Defined in:
lib/dbtools/database/mysql_connection.rb

Instance Attribute Summary

Attributes inherited from DbConnection

#connection

Instance Method Summary collapse

Methods inherited from DbConnection

#check_reserved_keywords, #check_spelling, #close, #create_database, #database_name, #execute_files, #get_all_columns, #get_completeness, #get_current_database, #get_inverse_functional_property, #get_syntax_compression, #get_uppercase_columns, #initialize

Constructor Details

This class inherits a constructor from Dbtools::Database::DbConnection

Instance Method Details

#check_indexes ⇒ Object

Queries all the primary keys in the database and outputs a query to create an index for that key.



21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
 
# File 'lib/dbtools/database/mysql_connection.rb', line 21

def check_indexes
  sql = %{
select cols.table_schema as "table_schema", cols.table_name as "table_name", cols.column_name as "column_name"
from information_schema.columns as cols
left join information_schema.statistics as stats
	on cols.table_schema = stats.table_schema
and cols.table_name = stats.table_name
and cols.column_name = stats.column_name
where cols.table_schema not in ('information_schema', 'pg_catalog', 'performance_schema', 'mysql', 'sys')
	and cols.column_name like '%\_id'
and stats.column_name IS NULL}

  violations = []
  execute_query(sql).each do |h|
    table_schema = h['table_schema']
    table_name = h['table_name']
    column_name = h['column_name']
    violations << Violation.new(database: database_name,
                                metric: "Missing indexes",
                                offender: "#{table_schema}.#{table_name}.#{column_name}",
                                solution: "CREATE INDEX idx_#{table_schema}_#{table_name}_#{column_name} ON #{table_schema}.#{table_name} (#{column_name}); ")
  end
  return violations
end

#execute_query(query) ⇒ Object

Executes a SQL statement on the connected database.



7
8
9
10
11
12
13
14
15
16
17
 
# File 'lib/dbtools/database/mysql_connection.rb', line 7

def execute_query(query)
  # Replaces quotes with ticks, so queries are compatible with MySQL.
  query.gsub!("\"", "`")
  # The MySQL ActiveRecord adapter throws an error when the query is empty.
  result = begin
             @connection.exec_query(query).to_hash
           rescue
             {}
           end
  return result
end

#get_all_databases ⇒ Object

Returns all databases on the system.



53
54
55
56
 
# File 'lib/dbtools/database/mysql_connection.rb', line 53

def get_all_databases
  sql = %q{show databases}
  execute_query(sql).map {|v| v.values}.flatten.to_set
end

#get_tables_without_comments ⇒ Object

Queries for all columns that don’t have comment metadata.



60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
 
# File 'lib/dbtools/database/mysql_connection.rb', line 60

def get_tables_without_comments
  query = %{select t.table_schema as "table_schema", t.table_name as "table_name"
from information_schema.tables as t
where t.TABLE_SCHEMA not in ('information_schema', 'performance_schema', 'mysql', 'sys', 'pg_catalog')
  and t.table_comment = ''}

  violations = []
  execute_query(query).each do |h|
    table_schema = h['table_schema']
    table_name = h['table_name']
    violations << Violation.new(database: database_name,
                                metric: "Table without comments",
                                offender: "#{table_schema}.#{table_name}")
  end
  return violations
end

#set_description_table(comment, object_name) ⇒ Object

Adds a description to a table by adding a comment.



47
48
49
50
 
# File 'lib/dbtools/database/mysql_connection.rb', line 47

def set_description_table(comment, object_name)
  query = %{ALTER TABLE `#{object_name}` COMMENT is '#{comment}'}
  execute_query(query)
end