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

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize ⇒ DatabaseManager

Initialize the database manager



14
15
16
17
18
19
20
21
 
# File 'lib/dbviewer/database_manager.rb', line 14

def initialize
  ensure_connection
  @cache_manager = CacheManager.new(self.class.configuration)
  @table_metadata_manager = TableMetadataManager.new(@connection, @cache_manager)
  @dynamic_model_factory = DynamicModelFactory.new(@connection, @cache_manager)
  @query_executor = QueryExecutor.new(@connection, self.class.configuration)
  reset_cache_if_needed
end

Instance Attribute Details

#adapter_name ⇒ Object (readonly)

Returns the value of attribute adapter_name.



11
12
13
 
# File 'lib/dbviewer/database_manager.rb', line 11

def adapter_name
  @adapter_name
end

#connection ⇒ Object (readonly)

Returns the value of attribute connection.



11
12
13
 
# File 'lib/dbviewer/database_manager.rb', line 11

def connection
  @connection
end

Class Method Details

.cache_expiry ⇒ Object

Get cache expiry from configuration



39
40
41
 
# File 'lib/dbviewer/database_manager.rb', line 39

def self.cache_expiry
  configuration.cache_expiry
end

.configuration ⇒ Object

Get configuration from class method or Dbviewer



24
25
26
 
# File 'lib/dbviewer/database_manager.rb', line 24

def self.configuration
  Dbviewer.configuration
end

.default_per_page ⇒ Object

Get default per page from configuration



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

def self.default_per_page
  configuration.default_per_page
end

.max_records ⇒ Object

Get max records from configuration



34
35
36
 
# File 'lib/dbviewer/database_manager.rb', line 34

def self.max_records
  configuration.max_records
end

Instance Method Details

#clear_all_caches ⇒ Object

Clear all caches - useful when schema changes are detected



263
264
265
 
# File 'lib/dbviewer/database_manager.rb', line 263

def clear_all_caches
  @cache_manager.clear_all
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



180
181
182
 
# File 'lib/dbviewer/database_manager.rb', line 180

def column_count(table_name)
  table_columns(table_name).size
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



195
196
197
 
# File 'lib/dbviewer/database_manager.rb', line 195

def column_exists?(table_name, column_name)
  @table_metadata_manager.column_exists?(table_name, column_name)
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



203
204
205
 
# File 'lib/dbviewer/database_manager.rb', line 203

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



211
212
213
 
# File 'lib/dbviewer/database_manager.rb', line 211

def execute_sqlite_pragma(pragma)
  @query_executor.execute_sqlite_pragma(pragma)
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



258
259
260
 
# File 'lib/dbviewer/database_manager.rb', line 258

def fetch_foreign_keys(table_name)
  @table_metadata_manager.fetch_foreign_keys(table_name)
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



251
252
253
 
# File 'lib/dbviewer/database_manager.rb', line 251

def fetch_indexes(table_name)
  @table_metadata_manager.fetch_indexes(table_name)
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



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

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

  # Apply column filters if provided
  if column_filters.present?
    column_filters.each do |column, value|
      next if value.blank?
      next unless column_exists?(table_name, column)

      # Use LIKE for string-based searches, = for exact matches on other types
      column_info = table_columns(table_name).find { |c| c[:name] == column }
      if column_info
        column_type = column_info[:type].to_s

        if column_type =~ /char|text|string|uuid|enum/i
          query = query.where("#{connection.quote_column_name(column)} LIKE ?", "%#{value}%")
        else
          # For numeric types, try exact match if value looks like a number
          if value =~ /\A[+-]?\d+(\.\d+)?\z/
            query = query.where(column => value)
          else
            # Otherwise, try string comparison for non-string fields
            query = query.where("CAST(#{connection.quote_column_name(column)} AS CHAR) LIKE ?", "%#{value}%")
          end
        end
      end
    end
  end

  query.count
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



187
188
189
 
# File 'lib/dbviewer/database_manager.rb', line 187

def primary_key(table_name)
  @table_metadata_manager.primary_key(table_name)
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



223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
 
# File 'lib/dbviewer/database_manager.rb', line 223

def query_table(table_name, select: nil, order: nil, limit: nil, offset: nil, where: nil)
  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
  max_records = self.class.max_records
  query = query.limit([ limit || max_records, max_records ].min) # Apply safety limit
  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

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



136
137
138
 
# File 'lib/dbviewer/database_manager.rb', line 136

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



52
53
54
 
# File 'lib/dbviewer/database_manager.rb', line 52

def table_columns(table_name)
  @table_metadata_manager.table_columns(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



66
67
68
69
 
# File 'lib/dbviewer/database_manager.rb', line 66

def table_count(table_name)
  model = get_model_for(table_name)
  model.count
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



59
60
61
 
# File 'lib/dbviewer/database_manager.rb', line 59

def table_metadata(table_name)
  @table_metadata_manager.table_metadata(table_name)
end

#table_records(table_name, page = 1, order_by = nil, direction = "ASC", per_page = nil, column_filters = 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



78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
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
123
124
125
126
127
128
129
130
131
 
# File 'lib/dbviewer/database_manager.rb', line 78

def table_records(table_name, page = 1, order_by = nil, direction = "ASC", per_page = nil, column_filters = nil)
  page = [ 1, page.to_i ].max
  default_per_page = self.class.default_per_page
  column_filters ||= {}
  max_records = self.class.max_records
  per_page = (per_page || default_per_page).to_i

  # 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 column filters if provided
  if column_filters.present?
    column_filters.each do |column, value|
      next if value.blank?
      next unless column_exists?(table_name, column)

      # Use LIKE for string-based searches, = for exact matches on other types
      column_info = table_columns(table_name).find { |c| c[:name] == column }
      if column_info
        column_type = column_info[:type].to_s

        if column_type =~ /char|text|string|uuid|enum/i
          query = query.where("#{connection.quote_column_name(column)} LIKE ?", "%#{value}%")
        else
          # For numeric types, try exact match if value looks like a number
          if value =~ /\A[+-]?\d+(\.\d+)?\z/
            query = query.where(column => value)
          else
            # Otherwise, try string comparison for non-string fields
            query = query.where("CAST(#{connection.quote_column_name(column)} AS CHAR) LIKE ?", "%#{value}%")
          end
        end
      end
    end
  end

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

  # 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

#tables ⇒ Array<String>

Returns a sorted list of all tables in the database

Returns:

  • (Array<String>) —

    List of table names



45
46
47
 
# File 'lib/dbviewer/database_manager.rb', line 45

def tables
  @table_metadata_manager.tables
end