Class: SqlGenius::Core::Analysis::TableSizes

Inherits:
Object
  • Object
show all
Defined in:
lib/sql_genius/core/analysis/table_sizes.rb

Overview

Returns size/fragmentation metrics for each user table in the current database, plus an exact SELECT COUNT(*) for each table. Delegates SQL generation to the dialect-appropriate QueryBuilder so the same class works against MySQL/MariaDB (information_schema.tables) and PostgreSQL (pg_class + pg_total_relation_size).

Takes a Core::Connection. Returns an array of hashes suitable for JSON rendering.

Instance Method Summary collapse

Constructor Details

#initialize(connection) ⇒ TableSizes

Returns a new instance of TableSizes.



15
16
17
18
 
# File 'lib/sql_genius/core/analysis/table_sizes.rb', line 15

def initialize(connection)
  @connection = connection
  @builder = QueryBuilders.for(connection)
end

Instance Method Details

#call ⇒ Object 



20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
 
# File 'lib/sql_genius/core/analysis/table_sizes.rb', line 20

def call
  result = @connection.exec_query(@builder.table_sizes(@connection))

  result.to_hashes.map do |row|
    table_name = row["table_name"] || row["TABLE_NAME"]
    row_count = begin
      @connection.select_value("SELECT COUNT(*) FROM #{@connection.quote_table_name(table_name)}")
    rescue StandardError
      nil
    end

    total_mb = (row["total_mb"] || 0).to_f
    fragmented_mb = (row["fragmented_mb"] || 0).to_f

    {
      table: table_name,
      rows: row_count,
      engine: row["engine"] || row["ENGINE"],
      collation: row["table_collation"] || row["TABLE_COLLATION"],
      auto_increment: row["auto_increment"] || row["AUTO_INCREMENT"],
      updated_at: row["update_time"] || row["UPDATE_TIME"],
      data_mb: (row["data_mb"] || 0).to_f,
      index_mb: (row["index_mb"] || 0).to_f,
      total_mb: total_mb,
      fragmented_mb: fragmented_mb,
      needs_optimize: total_mb.positive? && fragmented_mb > (total_mb * 0.1),
    }
  end
end