Class: Dbtools::Database::PostgresqlConnection

Inherits:
DbConnection
  • Object
show all
Defined in:
lib/dbtools/database/postgresql_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

#analyze_missing_indexes ⇒ Object

Analyzes the tables and checks if there are more sequence scans than index scans. Suggests where indexes could be created



19
20
21
22
23
24
25
26
27
28
 
# File 'lib/dbtools/database/postgresql_connection.rb', line 19

def analyze_missing_indexes
  sql = %{
  SELECT relname, seq_scan-idx_scan AS too_much_seq, case when seq_scan-idx_scan>0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan
  FROM pg_stat_all_tables
  WHERE schemaname ='public' AND pg_relation_size(relname::regclass)>80000 ORDER BY too_much_seq DESC;
}
  execute_query(sql).each do |index|
    puts index
  end
end

#check_indexes ⇒ Object

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



32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 
# File 'lib/dbtools/database/postgresql_connection.rb', line 32

def check_indexes
  sql = %{
select cols.table_schema as "table_schema", cols.table_name as "table_name", cols.column_name as "column_name"
from
(select c.table_schema, c.table_name, c.column_name
from information_schema.columns as c
where c.column_name != 'ID' and c.column_name != 'id' and c.column_name like '%\\_id'
  and c.table_schema not in ('information_schema', 'pg_catalog', 'performance_schema', 'mysql', 'sys')) as cols
left join
(SELECT UNNEST(ARRAY(
   SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
   FROM generate_subscripts(idx.indkey, 1) as k
   ORDER BY k
   )) as indkey_names
FROM   pg_index as idx
  JOIN   pg_class as i ON i.oid = idx.indexrelid
  JOIN   pg_am as am ON i.relam = am.oid) as indexes
on cols.column_name = indexes.indkey_names
where indexes.indkey_names 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

#copy_from_file(file, table_name, delimiter) ⇒ Object

Runs the copy command using stdin



122
123
124
125
126
127
128
129
130
 
# File 'lib/dbtools/database/postgresql_connection.rb', line 122

def copy_from_file(file, table_name, delimiter)
  raw_connection = @connection.raw_connection

  raw_connection.copy_data(%(COPY "#{table_name}" FROM STDIN DELIMITER '#{delimiter}' CSV HEADER;)) do
    File.foreach(file) do |line|
      raw_connection.put_copy_data(line)
    end
  end
end

#execute_query(query) ⇒ Object

Executes a SQL query on the connected database.



7
8
9
10
11
12
13
14
 
# File 'lib/dbtools/database/postgresql_connection.rb', line 7

def execute_query(query)
  result = begin
             @connection.exec_query(query)
           rescue
             {}
           end
  return result
end

#get_all_databases ⇒ Object

Returns all databases from the postgres.



100
101
102
103
 
# File 'lib/dbtools/database/postgresql_connection.rb', line 100

def get_all_databases
  sql = %q{SELECT datname FROM pg_database WHERE datistemplate = false;}
  execute_query(sql).map {|v| v.values}.flatten.to_set
end

#get_databases_without_comments ⇒ Object

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



106
107
108
109
110
111
112
113
114
115
116
117
118
119
 
# File 'lib/dbtools/database/postgresql_connection.rb', line 106

def get_databases_without_comments
  query = %{select db.datname
from pg_database as db
left join pg_shdescription as sd on sd.objoid = db.oid
where sd.objoid is null}
  violations = []
  execute_query(query).each do |h|
    datname = h['datname']
    violations << Violation.new(database: database_name,
                                metric: "Database without comment",
                                offender: datname)
  end
  return violations
end

#get_tables_without_comments ⇒ Object

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



78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
 
# File 'lib/dbtools/database/postgresql_connection.rb', line 78

def get_tables_without_comments
  query = %{select t.table_catalog, t.table_schema, t.table_name, d.description
from information_schema.tables as t
join 
pg_class as c on c.relname = t.table_name
join pg_namespace as n on c.relnamespace = n.oid
left join pg_description as d on c.oid = d.objoid
where t.table_schema not in ('information_schema', 'pg_catalog', 'performance_schema', 'mysql', 'sys')
and d.objoid is null}
  violations = []
  execute_query(query).each do |h|
    table_catalog = h['table_catalog']
    table_schema = h['table_schema']
    table_name = h['table_name']
    violations << Violation.new(database: database_name,
                                metric: "Table without comments",
                                offender: "#{table_catalog}.#{table_schema}.#{table_name}")
  end
  return violations
end

#set_description_database(comment) ⇒ Object

Adds a description to the current database by adding a comment.



65
66
67
68
 
# File 'lib/dbtools/database/postgresql_connection.rb', line 65

def set_description_database(comment)
  query = %{COMMENT ON DATABASE #{@connection.current_database} IS '#{comment}'}
  execute_query(query)
end

#set_description_table(comment, object_name) ⇒ Object

Adds a description to a table by adding a comment.



71
72
73
74
 
# File 'lib/dbtools/database/postgresql_connection.rb', line 71

def set_description_table(comment, object_name)
  query = %{COMMENT ON TABLE #{object_name} IS '#{comment}'}
  execute_query(query)
end