Class: Dbtools::Database::Table

Inherits:
Object
  • Object
show all
Defined in:
lib/dbtools/database/database_data.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(name, schema) ⇒ Table

Returns a new instance of Table.



29
30
31
32
33
 
# File 'lib/dbtools/database/database_data.rb', line 29

def initialize(name, schema)
  @name = name
  @schema = schema
  @columns = Hash.new
end

Instance Attribute Details

#columns ⇒ Object (readonly)

Returns the value of attribute columns.



28
29
30
 
# File 'lib/dbtools/database/database_data.rb', line 28

def columns
  @columns
end

#name ⇒ Object (readonly)

Put quotes around name to avoid casing problems.



36
37
38
 
# File 'lib/dbtools/database/database_data.rb', line 36

def name
  @name
end

#schema ⇒ Object (readonly)

Put quotes around name to avoid casing problems.



41
42
43
 
# File 'lib/dbtools/database/database_data.rb', line 41

def schema
  @schema
end

Instance Method Details

#add_column(column_name, data_type) ⇒ Object

Add column if it doesn’t exist yet.



46
47
48
49
 
# File 'lib/dbtools/database/database_data.rb', line 46

def add_column(column_name, data_type)
  @columns[column_name] = Column.new(column_name, name, schema, data_type) unless @columns.include?(column_name)
  return @columns[column_name]
end

#query_distinct_entries ⇒ Object

Create a query to count all distinct values per column.



87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
 
# File 'lib/dbtools/database/database_data.rb', line 87

def query_distinct_entries
  # Skip unless column is a string column
  query_columns = columns.values.map do |col|
    %{count(distinct(#{col.full_name})) AS #{col.name}} if Dbtools::Constants::STRING_COLUMNS.include?(col.data_type)
  end
  # Remove nulls caused by skipping
  query_columns.compact!
  query_columns = query_columns.join(", \n")

  query = unless query_columns.empty?
            %{SELECT #{query_columns} FROM #{schema}.#{name}}
          else
            ''
          end
  return query
end

#query_distinct_lowercased_entries ⇒ Object

Create a query to count all distinct lowercased values per column.



70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
 
# File 'lib/dbtools/database/database_data.rb', line 70

def query_distinct_lowercased_entries
  # Skip unless column is a string column
  query_columns = columns.values.map do |col|
    %{count(distinct(lower(#{col.full_name}))) AS #{col.name}} if Dbtools::Constants::STRING_COLUMNS.include?(col.data_type)
  end
  # Remove nulls caused by skipping
  query_columns.compact!
  query_columns = query_columns.join(", \n")
  query = unless query_columns.empty?
            %{SELECT #{query_columns} FROM #{schema}.#{name}}
          else
            ''
          end
  return query
end

#query_empty_records ⇒ Object

Create a query to count all records that are empty.



52
53
54
55
56
57
58
59
60
61
62
 
# File 'lib/dbtools/database/database_data.rb', line 52

def query_empty_records
  query_columns = columns.values.map do |col|
    %{SUM(CASE WHEN #{col.not_empty} THEN 1 ELSE 0 END) AS #{col.name}}
  end.join(", \n")
  query = unless query_columns.empty?
            %{SELECT #{query_columns} FROM #{schema}.#{name}}
          else
            ''
          end
  return query
end

#query_total_records ⇒ Object

Create a query to count all the records.



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

def query_total_records
  return %{SELECT COUNT(*) FROM #{schema}.#{name}}
end

#to_s ⇒ Object 



104
105
106
107
108
109
110
 
# File 'lib/dbtools/database/database_data.rb', line 104

def to_s
  output = "+ #{@name}: \n"
  @columns.each do |k, v|
    output << v.to_s << "\n"
  end
  return output
end