Class: Dbviewer::TableQueryOperations

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

Overview

TableQueryOperations 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) ⇒ TableQueryOperations

Initialize with dependencies

Parameters:

  • connection (ActiveRecord::ConnectionAdapters::AbstractAdapter) —

    The database connection

  • dynamic_model_factory (DynamicModelFactory) —

    Factory for creating dynamic AR models

  • query_executor (QueryExecutor) —

    Executor for raw SQL queries

  • table_metadata_manager (TableMetadataManager) —

    Manager for table metadata



16
17
18
19
20
21
22
23
 
# File 'lib/dbviewer/table_query_operations.rb', line 16

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::QueryAnalyzer.new(connection)
end

Instance Attribute Details

#adapter_name ⇒ Object (readonly)

Returns the value of attribute adapter_name.



9
10
11
 
# File 'lib/dbviewer/table_query_operations.rb', line 9

def adapter_name
  @adapter_name
end

#connection ⇒ Object (readonly)

Returns the value of attribute connection.



9
10
11
 
# File 'lib/dbviewer/table_query_operations.rb', line 9

def connection
  @connection
end

Instance Method Details

#analyze_query_performance(table_name, params) ⇒ Hash

Analyze query performance for a table with given filters

Parameters:

  • table_name (String) —

    Name of the table

  • params (TableQueryParams) —

    Query parameters object

Returns:

  • (Hash) —

    Performance analysis and recommendations



207
208
209
 
# File 'lib/dbviewer/table_query_operations.rb', line 207

def analyze_query_performance(table_name, params)
  @query_analyzer.analyze_query(table_name, 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



28
29
30
 
# File 'lib/dbviewer/table_query_operations.rb', line 28

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



91
92
93
 
# File 'lib/dbviewer/table_query_operations.rb', line 91

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



99
100
101
 
# File 'lib/dbviewer/table_query_operations.rb', line 99

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

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

Fetch timestamp data for visualization

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



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
191
192
193
194
195
196
197
198
199
200
201
 
# File 'lib/dbviewer/table_query_operations.rb', line 140

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



77
78
79
80
81
82
83
84
85
 
# File 'lib/dbviewer/table_query_operations.rb', line 77

def filtered_record_count(table_name, column_filters = {})
  model = get_model_for(table_name)
  query = model.all

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

  query.count
end

#query_table(table_name, select: nil, order: nil, limit: nil, offset: nil, where: nil, max_records: 1000) ⇒ ActiveRecord::Result

Query a table with more granular control using ActiveRecord

Parameters:

  • table_name (String) —

    Name of the table

  • select (String, Array) (defaults to: nil) —

    Columns to select

  • order (String, Hash) (defaults to: nil) —

    Order by clause

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

    Maximum number of records to return

  • offset (Integer) (defaults to: nil) —

    Offset from which to start returning records

  • where (String, Hash) (defaults to: nil) —

    Where conditions

Returns:

  • (ActiveRecord::Result) —

    Result set with columns and rows



111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
 
# File 'lib/dbviewer/table_query_operations.rb', line 111

def query_table(table_name, select: nil, order: nil, limit: nil, offset: nil, where: nil, max_records: 1000)
  model = get_model_for(table_name)
  query = model.all

  query = query.select(select) if select.present?
  query = query.where(where) if where.present?
  query = query.order(order) if order.present?

  # Apply safety limit
  query = query.limit([ limit || max_records, max_records ].min)
  query = query.offset(offset) if offset.present?

  # Get column names for the result set
  column_names = if select.is_a?(Array)
    select
  elsif select.is_a?(String) && !select.include?("*")
    select.split(",").map(&:strip)
  else
    table_columns(table_name).map { |c| c[:name] }
  end

  @query_executor.to_result_set(query, column_names)
end

#record_count(table_name) ⇒ Integer

Alias for table_count

Parameters:

  • table_name (String) —

    Name of the table

Returns:

  • (Integer) —

    Number of records



69
70
71
 
# File 'lib/dbviewer/table_query_operations.rb', line 69

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
 
# File 'lib/dbviewer/table_query_operations.rb', line 61

def table_count(table_name)
  model = get_model_for(table_name)
  model.count
end

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

Get records from a table with pagination and sorting

Parameters:

  • table_name (String) —

    Name of the table

  • params (TableQueryParams) —

    Query parameters object

Returns:

  • (ActiveRecord::Result) —

    Result set with columns and rows



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

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)
end