Class: Dbviewer::DatabaseManager

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

Overview

DatabaseManager handles all database interactions for the DBViewer engine It provides methods to access database structure and data

Constant Summary collapse

DEFAULT_PER_PAGE =

Default number of records per page if not specified

20
MAX_RECORDS =

Max number of records to return in any query for safety

10000
CACHE_EXPIRY =

Cache expiration time in seconds (5 minutes)

300
@@dynamic_models =

Cache for dynamically created AR models

{}
@@table_columns_cache =

Cache for table column info

{}
@@table_metadata_cache =

Cache for table metadata

{}
@@cache_last_reset =

Last cache reset time

Time.now

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize ⇒ DatabaseManager

Returns a new instance of DatabaseManager.



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

def initialize
  ensure_connection
  reset_cache_if_needed
end

Instance Attribute Details

#adapter_name ⇒ Object (readonly)

Returns the value of attribute adapter_name.



5
6
7
 
# File 'lib/dbviewer/database_manager.rb', line 5

def adapter_name
  @adapter_name
end

#connection ⇒ Object (readonly)

Returns the value of attribute connection.



5
6
7
 
# File 'lib/dbviewer/database_manager.rb', line 5

def connection
  @connection
end

Instance Method Details

#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



160
161
162
163
164
165
166
167
 
# File 'lib/dbviewer/database_manager.rb', line 160

def column_count(table_name)
  begin
    table_columns(table_name).size
  rescue => e
    Rails.logger.error("[DBViewer] Error counting columns in table #{table_name}: #{e.message}")
    0
  end
end

#column_exists?(table_name, column_name) ⇒ Boolean

Check if a column exists in a table

Parameters:

  • table_name (String) —

    Name of the table

  • column_name (String) —

    Name of the column

Returns:

  • (Boolean) —

    true if column exists, false otherwise



185
186
187
188
189
190
191
192
193
194
195
 
# File 'lib/dbviewer/database_manager.rb', line 185

def column_exists?(table_name, column_name)
  return false if table_name.blank? || column_name.blank?

  begin
    columns = table_columns(table_name)
    columns.any? { |col| col[:name].to_s == column_name.to_s }
  rescue => e
    Rails.logger.error("[DBViewer] Error checking column existence for #{column_name} in #{table_name}: #{e.message}")
    false
  end
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



201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
 
# File 'lib/dbviewer/database_manager.rb', line 201

def execute_query(sql)
  # Use the SqlValidator class to validate and normalize the SQL query
  begin
    # Validate and normalize the SQL
    normalized_sql = ::Dbviewer::SqlValidator.validate!(sql.to_s)

    # Get max records from configuration if available
    max_records = self.class.respond_to?(:max_records) ? self.class.max_records : MAX_RECORDS

    # Add a safety limit if not already present
    unless normalized_sql =~ /\bLIMIT\s+\d+\s*$/i
      normalized_sql = "#{normalized_sql} LIMIT #{max_records}"
    end

    # Log and execute the query
    Rails.logger.debug("[DBViewer] Executing SQL query: #{normalized_sql}")
    start_time = Time.now
    result = connection.exec_query(normalized_sql)
    duration = Time.now - start_time

    Rails.logger.debug("[DBViewer] Query completed in #{duration.round(2)}s, returned #{result.rows.size} rows")
    result
  rescue => e
    Rails.logger.error("[DBViewer] SQL query error: #{e.message} for query: #{sql}")
    raise e
  end
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



233
234
235
236
237
238
239
240
241
242
243
244
 
# File 'lib/dbviewer/database_manager.rb', line 233

def execute_sqlite_pragma(pragma)
  begin
    sql = "PRAGMA #{pragma}"
    Rails.logger.debug("[DBViewer] Executing SQLite pragma: #{sql}")
    result = connection.exec_query(sql)
    Rails.logger.debug("[DBViewer] Pragma completed, returned #{result.rows.size} rows")
    result
  rescue => error
    Rails.logger.error("[DBViewer] SQLite pragma error: #{error.message} for pragma: #{pragma}")
    raise error
  end
end

#fetch_foreign_keys(table_name) ⇒ Array<Hash>

Get foreign keys

Parameters:

  • table_name (String) —

    Name of the table

Returns:

  • (Array<Hash>) —

    List of foreign keys with details



303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
 
# File 'lib/dbviewer/database_manager.rb', line 303

def fetch_foreign_keys(table_name)
  return [] if table_name.blank?

  begin
    # Only some adapters support foreign key retrieval
    if connection.respond_to?(:foreign_keys)
      connection.foreign_keys(table_name).map do |fk|
        {
          name: fk.name,
          from_table: fk.from_table,
          to_table: fk.to_table,
          column: fk.column,
          primary_key: fk.primary_key
        }
      end
    else
      []
    end
  rescue => e
    Rails.logger.error("[DBViewer] Error retrieving foreign keys for table #{table_name}: #{e.message}")
    []
  end
end

#fetch_indexes(table_name) ⇒ Array<Hash>

Get table indexes

Parameters:

  • table_name (String) —

    Name of the table

Returns:

  • (Array<Hash>) —

    List of indexes with details



278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
 
# File 'lib/dbviewer/database_manager.rb', line 278

def fetch_indexes(table_name)
  return [] if table_name.blank?

  begin
    # Only some adapters support index retrieval
    if connection.respond_to?(:indexes)
      connection.indexes(table_name).map do |index|
        {
          name: index.name,
          columns: index.columns,
          unique: index.unique
        }
      end
    else
      []
    end
  rescue => e
    Rails.logger.error("[DBViewer] Error retrieving indexes for table #{table_name}: #{e.message}")
    []
  end
end

#primary_key(table_name) ⇒ String?

Get the primary key of a table

Parameters:

  • table_name (String) —

    Name of the table

Returns:

  • (String, nil) —

    Primary key column name or nil if not found



172
173
174
175
176
177
178
179
 
# File 'lib/dbviewer/database_manager.rb', line 172

def primary_key(table_name)
  begin
    connection.primary_key(table_name)
  rescue => e
    Rails.logger.error("[DBViewer] Error retrieving primary key for table #{table_name}: #{e.message}")
    nil
  end
end

#query_table(table_name, select: nil, order: nil, limit: nil, offset: nil, where: nil) ⇒ 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



254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
 
# File 'lib/dbviewer/database_manager.rb', line 254

def query_table(table_name, select: nil, order: nil, limit: nil, offset: nil, where: nil)
  begin
    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?

    # Get max records from configuration if available
    max_records = self.class.respond_to?(:max_records) ? self.class.max_records : MAX_RECORDS
    query = query.limit([ limit || max_records, max_records ].min) # Apply safety limit
    query = query.offset(offset) if offset.present?

    to_result_set(query, table_name)
  rescue => e
    Rails.logger.error("[DBViewer] Error querying table #{table_name}: #{e.message}")
    raise e
  end
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



153
154
155
 
# File 'lib/dbviewer/database_manager.rb', line 153

def record_count(table_name)
  table_count(table_name)
end

#table_columns(table_name) ⇒ Array<Hash>

Returns column information for a specific table

Parameters:

  • table_name (String) —

    Name of the table

Returns:

  • (Array<Hash>) —

    List of column details with name, type, null, default



50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
# File 'lib/dbviewer/database_manager.rb', line 50

def table_columns(table_name)
  return [] if table_name.blank?

  # Return from cache if available
  return @@table_columns_cache[table_name] if @@table_columns_cache[table_name]

  begin
    columns = connection.columns(table_name).map do |column|
      {
        name: column.name,
        type: column.type,
        null: column.null,
        default: column.default,
        primary: column.name == primary_key(table_name)
      }
    end

    # Cache the result
    @@table_columns_cache[table_name] = columns
    columns
  rescue => e
    Rails.logger.error("[DBViewer] Error retrieving columns for table #{table_name}: #{e.message}")
    []
  end
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



104
105
106
107
108
109
110
111
112
 
# File 'lib/dbviewer/database_manager.rb', line 104

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

#table_metadata(table_name) ⇒ Hash

Get detailed metadata about a table (primary keys, indexes, foreign keys)

Parameters:

  • table_name (String) —

    Name of the table

Returns:

  • (Hash) —

    Table metadata



79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
 
# File 'lib/dbviewer/database_manager.rb', line 79

def table_metadata(table_name)
  return {} if table_name.blank?

  # Return from cache if available
  return @@table_metadata_cache[table_name] if @@table_metadata_cache[table_name]

  begin
    metadata = {
      primary_key: primary_key(table_name),
      indexes: fetch_indexes(table_name),
      foreign_keys: fetch_foreign_keys(table_name)
    }

    # Cache the result
    @@table_metadata_cache[table_name] = metadata
    metadata
  rescue => e
    Rails.logger.error("[DBViewer] Error retrieving metadata for table #{table_name}: #{e.message}")
    { primary_key: nil, indexes: [], foreign_keys: [] }
  end
end

#table_records(table_name, page = 1, order_by = nil, direction = "ASC", per_page = nil) ⇒ ActiveRecord::Result

Get records from a table with pagination and sorting

Parameters:

  • table_name (String) —

    Name of the table

  • page (Integer) (defaults to: 1) —

    Page number (1-based)

  • order_by (String) (defaults to: nil) —

    Column to sort by

  • direction (String) (defaults to: "ASC") —

    Sort direction (‘ASC’ or ‘DESC’)

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

    Number of records per page

Returns:

  • (ActiveRecord::Result) —

    Result set with columns and rows



121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
 
# File 'lib/dbviewer/database_manager.rb', line 121

def table_records(table_name, page = 1, order_by = nil, direction = "ASC", per_page = nil)
  page = [ 1, page.to_i ].max

  # Use class method if defined, otherwise fall back to constant
  default_per_page = self.class.respond_to?(:default_per_page) ? self.class.default_per_page : DEFAULT_PER_PAGE
  max_records = self.class.respond_to?(:max_records) ? self.class.max_records : MAX_RECORDS

  per_page = (per_page || default_per_page).to_i
  per_page = default_per_page if per_page <= 0

  # Ensure we don't fetch too many records for performance/memory reasons
  per_page = [ per_page, max_records ].min

  model = get_model_for(table_name)
  query = model.all

  # Apply sorting if provided
  if order_by.present? && column_exists?(table_name, order_by)
    direction = %w[ASC DESC].include?(direction.to_s.upcase) ? direction.to_s.upcase : "ASC"
    query = query.order("#{connection.quote_column_name(order_by)} #{direction}")
  end

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

  # Transform the ActiveRecord::Relation to the format expected by the application
  to_result_set(records, table_name)
end

#tables ⇒ Array<String>

Returns a sorted list of all tables in the database

Returns:

  • (Array<String>) —

    List of table names



35
36
37
38
39
40
41
42
43
44
45
 
# File 'lib/dbviewer/database_manager.rb', line 35

def tables
  return [] unless connection.respond_to?(:tables)

  begin
    # Get and sort tables, cache is handled at the database adapter level
    connection.tables.sort
  rescue => e
    Rails.logger.error("[DBViewer] Error retrieving tables: #{e.message}")
    []
  end
end