Class: Dbviewer::Datatable::QueryOperations

Inherits:
Object
  • Object
show all
Defined in:
lib/dbviewer/datatable/query_operations.rb

Overview

QueryOperations handles CRUD operations and data querying for database tables It provides methods to fetch, filter and manipulate data in tables

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(connection, dynamic_model_factory, query_executor, table_metadata_manager) ⇒ QueryOperations

Initialize with dependencies

Parameters:



13
14
15
16
17
18
19
20
 
# File 'lib/dbviewer/datatable/query_operations.rb', line 13

def initialize(connection, dynamic_model_factory, query_executor, table_metadata_manager)
  @connection = connection
  @adapter_name = connection.adapter_name.downcase
  @dynamic_model_factory = dynamic_model_factory
  @query_executor = query_executor
  @table_metadata_manager = table_metadata_manager
  @query_analyzer = ::Dbviewer::Query::Analyzer.new(connection)
end

Instance Attribute Details

#adapter_name ⇒ Object (readonly)

Returns the value of attribute adapter_name.



6
7
8
 
# File 'lib/dbviewer/datatable/query_operations.rb', line 6

def adapter_name
  @adapter_name
end

#connection ⇒ Object (readonly)

Returns the value of attribute connection.



6
7
8
 
# File 'lib/dbviewer/datatable/query_operations.rb', line 6

def connection
  @connection
end

Instance Method Details

#analyze_query(table_name, query_params) ⇒ Hash

Analyze query patterns and return performance recommendations

Parameters:

Returns:

  • (Hash) —

    Analysis results



212
213
214
 
# File 'lib/dbviewer/datatable/query_operations.rb', line 212

def analyze_query(table_name, query_params)
  @query_analyzer.analyze_query(table_name, query_params)
end

#column_count(table_name) ⇒ Integer

Get the number of columns in a table

Parameters:

  • table_name (String) —

    Name of the table

Returns:

  • (Integer) —

    Number of columns



25
26
27
 
# File 'lib/dbviewer/datatable/query_operations.rb', line 25

def column_count(table_name)
  table_columns(table_name).size
end

#execute_query(sql) ⇒ ActiveRecord::Result

Execute a raw SQL query after validating for safety

Parameters:

  • sql (String) —

    SQL query to execute

Returns:

  • (ActiveRecord::Result) —

    Result set with columns and rows

Raises:

  • (StandardError) —

    If the query is invalid or unsafe



196
197
198
 
# File 'lib/dbviewer/datatable/query_operations.rb', line 196

def execute_query(sql)
  @query_executor.execute_query(sql)
end

#execute_sqlite_pragma(pragma) ⇒ ActiveRecord::Result

Execute a SQLite PRAGMA command without adding a LIMIT clause

Parameters:

  • pragma (String) —

    PRAGMA command to execute (without the “PRAGMA” keyword)

Returns:

  • (ActiveRecord::Result) —

    Result set with the PRAGMA value

Raises:

  • (StandardError) —

    If the query is invalid or cannot be executed



204
205
206
 
# File 'lib/dbviewer/datatable/query_operations.rb', line 204

def execute_sqlite_pragma(pragma)
  @query_executor.execute_sqlite_pragma(pragma)
end

#fetch_column_distribution(table_name, column_name, limit = 20) ⇒ Array<Hash>

Get column histogram/value distribution data for a specific column

Parameters:

  • table_name (String) —

    Name of the table

  • column_name (String) —

    Name of the column

  • limit (Integer) (defaults to: 20) —

    Maximum number of distinct values to return

Returns:

  • (Array<Hash>) —

    Array of value distribution data with labels and counts



98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
 
# File 'lib/dbviewer/datatable/query_operations.rb', line 98

def fetch_column_distribution(table_name, column_name, limit = 20)
  return [] unless column_exists?(table_name, column_name)

  query = "SELECT #{column_name} as label, COUNT(*) as count FROM #{table_name}
           WHERE #{column_name} IS NOT NULL
           GROUP BY #{column_name}
           ORDER BY count DESC LIMIT #{limit}"

  begin
    result = @connection.execute(query)
    adapter = @connection.adapter_name.downcase

    # Format depends on adapter
    if adapter =~ /mysql/
      result.to_a.map { |row| { label: row[0], value: row[1] } }
    elsif adapter =~ /sqlite/
      result.map { |row| { label: row["label"], value: row["count"] } }
    else # postgresql
      result.map { |row| { label: row["label"], value: row["count"] } }
    end
  rescue => e
    Rails.logger.error("Error fetching column distribution: #{e.message}")
    []
  end
end

#fetch_timestamp_data(table_name, grouping = "daily", column = "created_at") ⇒ Array<Hash>

Get timestamp aggregation data for charts

Parameters:

  • table_name (String) —

    Name of the table

  • grouping (String) (defaults to: "daily") —

    Grouping type (hourly, daily, weekly, monthly)

  • column (String) (defaults to: "created_at") —

    Timestamp column name (defaults to created_at)

Returns:

  • (Array<Hash>) —

    Array of timestamp data with labels and counts



129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
 
# File 'lib/dbviewer/datatable/query_operations.rb', line 129

def fetch_timestamp_data(table_name, grouping = "daily", column = "created_at")
  return [] unless column_exists?(table_name, column)

  adapter = @connection.adapter_name.downcase

  date_format = case grouping
  when "hourly"
    if adapter =~ /mysql/
      "DATE_FORMAT(#{column}, '%Y-%m-%d %H:00')"
    elsif adapter =~ /sqlite/
      "strftime('%Y-%m-%d %H:00', #{column})"
    else # postgresql
      "TO_CHAR(#{column}, 'YYYY-MM-DD HH24:00')"
    end
  when "weekly"
    if adapter =~ /mysql/
      "DATE_FORMAT(#{column}, '%Y-%v')"
    elsif adapter =~ /sqlite/
      "strftime('%Y-%W', #{column})"
    else # postgresql
      "TO_CHAR(#{column}, 'YYYY-IW')"
    end
  when "monthly"
    if adapter =~ /mysql/
      "DATE_FORMAT(#{column}, '%Y-%m')"
    elsif adapter =~ /sqlite/
      "strftime('%Y-%m', #{column})"
    else # postgresql
      "TO_CHAR(#{column}, 'YYYY-MM')"
    end
  else # daily is default
    if adapter =~ /mysql/
      "DATE(#{column})"
    elsif adapter =~ /sqlite/
      "date(#{column})"
    else # postgresql
      "DATE(#{column})"
    end
  end

  # Query works the same for all database adapters
  query = "SELECT #{date_format} as label, COUNT(*) as count FROM #{table_name}
           WHERE #{column} IS NOT NULL
           GROUP BY label
           ORDER BY MIN(#{column}) DESC LIMIT 30"

  begin
    result = @connection.execute(query)

    # Format depends on adapter
    if adapter =~ /mysql/
      result.to_a.map { |row| { label: row[0], value: row[1] } }
    elsif adapter =~ /sqlite/
      result.map { |row| { label: row["label"], value: row["count"] } }
    else # postgresql
      result.map { |row| { label: row["label"], value: row["count"] } }
    end
  rescue => e
    Rails.logger.error("Error fetching timestamp data: #{e.message}")
    []
  end
end

#filtered_record_count(table_name, column_filters = {}) ⇒ Integer

Get the number of records in a table with filters applied

Parameters:

  • table_name (String) —

    Name of the table

  • column_filters (Hash) (defaults to: {}) —

    Hash of column_name => filter_value for filtering

Returns:

  • (Integer) —

    Number of filtered records



79
80
81
82
83
84
85
86
87
88
89
90
91
 
# File 'lib/dbviewer/datatable/query_operations.rb', line 79

def filtered_record_count(table_name, column_filters = {})
  return table_count(table_name) unless column_filters.present?

  model = get_model_for(table_name)
  query = model.all

  # Apply filters in the same way as table_records
  query = apply_column_filters(query, table_name, column_filters)
  query.count
rescue => e
  Rails.logger.error("[DBViewer] Error counting filtered records in table #{table_name}: #{e.message}")
  0
end

#record_count(table_name) ⇒ Integer

Get the number of records in a table (alias for table_count)

Parameters:

  • table_name (String) —

    Name of the table

Returns:

  • (Integer) —

    Number of records



71
72
73
 
# File 'lib/dbviewer/datatable/query_operations.rb', line 71

def record_count(table_name)
  table_count(table_name)
end

#table_count(table_name) ⇒ Integer

Get the total count of records in a table

Parameters:

  • table_name (String) —

    Name of the table

Returns:

  • (Integer) —

    Number of records



61
62
63
64
65
66
 
# File 'lib/dbviewer/datatable/query_operations.rb', line 61

def table_count(table_name)
  get_model_for(table_name).count
rescue => e
  Rails.logger.error("[DBViewer] Error counting records in table #{table_name}: #{e.message}")
  0
end

#table_records(table_name, params) ⇒ ActiveRecord::Result

Get records from a table with pagination and sorting

Parameters:

Returns:

  • (ActiveRecord::Result) —

    Result set with columns and rows



33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
 
# File 'lib/dbviewer/datatable/query_operations.rb', line 33

def table_records(table_name, params)
  model = get_model_for(table_name)
  query = model.all

  # Apply column filters if provided
  query = apply_column_filters(query, table_name, params.column_filters)

  # Apply sorting if provided
  if params.order_by.present? && column_exists?(table_name, params.order_by)
    query = query.order("#{connection.quote_column_name(params.order_by)} #{params.direction}")
  end

  # Apply pagination
  records = query.limit(params.per_page).offset((params.page - 1) * params.per_page)

  # Get column names for consistent ordering
  column_names = table_columns(table_name).map { |c| c[:name] }

  # Format results
  @query_executor.to_result_set(records, column_names)
rescue => e
  Rails.logger.error("[DBViewer] Error executing table query: #{e.message}")
  raise e
end