Class: RailsLens::Schema::Adapters::Postgresql

Inherits:
Base
  • Object
show all
Defined in:
lib/rails_lens/schema/adapters/postgresql.rb

Instance Attribute Summary

Attributes inherited from Base

#connection, #table_name

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from Base

#initialize, #unqualified_table_name

Constructor Details

This class inherits a constructor from RailsLens::Schema::Adapters::Base

Class Method Details

.fetch_functions(connection) ⇒ Object

Fetch all user-defined functions, excluding extension-owned functions This is a database-level operation, returns all functions in user schemas



466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
 
# File 'lib/rails_lens/schema/adapters/postgresql.rb', line 466

def self.fetch_functions(connection)
  result = connection.exec_query(<<~SQL.squish, 'PostgreSQL Functions')
    SELECT
      p.proname AS name,
      n.nspname AS schema,
      l.lanname AS language,
      CASE
        WHEN p.prorettype = 'trigger'::regtype THEN 'trigger'
        ELSE pg_catalog.format_type(p.prorettype, NULL)
      END AS return_type,
      pg_get_functiondef(p.oid) AS definition,
      obj_description(p.oid, 'pg_proc') AS description
    FROM pg_proc p
    JOIN pg_namespace n ON p.pronamespace = n.oid
    JOIN pg_language l ON p.prolang = l.oid
    WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
      AND NOT EXISTS (
        SELECT 1 FROM pg_depend d
        JOIN pg_extension e ON d.refobjid = e.oid
        WHERE d.objid = p.oid
          AND d.deptype = 'e'
      )
    ORDER BY n.nspname, p.proname
  SQL

  result.rows.map do |row|
    {
      name: row[0],
      schema: row[1],
      language: row[2],
      return_type: row[3],
      definition: row[4],
      description: row[5]
    }
  end
rescue ActiveRecord::StatementInvalid, PG::Error => e
  RailsLens.logger.debug { "Failed to fetch functions: #{e.message}" }
  []
end

Instance Method Details

#adapter_name ⇒ Object 



7
8
9
 
# File 'lib/rails_lens/schema/adapters/postgresql.rb', line 7

def adapter_name
  'PostgreSQL'
end

#extract_trigger_condition(definition) ⇒ Object

Extract WHEN condition from trigger definition if present



457
458
459
460
461
462
 
# File 'lib/rails_lens/schema/adapters/postgresql.rb', line 457

def extract_trigger_condition(definition)
  return nil unless definition

  match = definition.match(/WHEN\s*\((.+?)\)\s*EXECUTE/i)
  match ? match[1] : nil
end

#fetch_triggers ⇒ Object

Fetch triggers for the table, excluding extension-owned triggers



398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
 
# File 'lib/rails_lens/schema/adapters/postgresql.rb', line 398

def fetch_triggers
  result = connection.exec_query(<<~SQL.squish, 'PostgreSQL Table Triggers')
    SELECT
      t.tgname AS name,
      CASE t.tgtype::integer & 66
        WHEN 2 THEN 'BEFORE'
        WHEN 64 THEN 'INSTEAD OF'
        ELSE 'AFTER'
      END AS timing,
      CASE t.tgtype::integer & 60
        WHEN 4 THEN 'INSERT'
        WHEN 8 THEN 'DELETE'
        WHEN 16 THEN 'UPDATE'
        WHEN 20 THEN 'INSERT OR UPDATE'
        WHEN 24 THEN 'UPDATE OR DELETE'
        WHEN 12 THEN 'INSERT OR DELETE'
        WHEN 28 THEN 'INSERT OR UPDATE OR DELETE'
        WHEN 32 THEN 'TRUNCATE'
        ELSE 'UNKNOWN'
      END AS event,
      CASE WHEN t.tgtype::integer & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END AS for_each,
      p.proname AS function_name,
      n.nspname AS function_schema,
      pg_get_triggerdef(t.oid) AS definition
    FROM pg_trigger t
    JOIN pg_class c ON t.tgrelid = c.oid
    JOIN pg_namespace cn ON c.relnamespace = cn.oid
    JOIN pg_proc p ON t.tgfoid = p.oid
    JOIN pg_namespace n ON p.pronamespace = n.oid
    WHERE c.relname = '#{connection.quote_string(unqualified_table_name)}'
      AND cn.nspname = '#{connection.quote_string(schema_name || 'public')}'
      AND NOT t.tgisinternal
      -- Exclude triggers owned by extensions
      AND NOT EXISTS (
        SELECT 1 FROM pg_depend d
        JOIN pg_extension e ON d.refobjid = e.oid
        WHERE d.objid = t.oid
          AND d.deptype = 'e'
      )
    ORDER BY t.tgname
  SQL

  result.rows.map do |row|
    {
      name: row[0],
      timing: row[1],
      event: row[2],
      for_each: row[3],
      function: row[5] == 'public' ? row[4] : "#{row[5]}.#{row[4]}",
      definition: row[6],
      condition: extract_trigger_condition(row[6])
    }
  end
rescue ActiveRecord::StatementInvalid, PG::Error => e
  RailsLens.logger.debug { "Failed to fetch triggers for #{table_name}: #{e.message}" }
  []
end

#fetch_view_metadata ⇒ Object

Fetch all view metadata in a single consolidated query



278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
 
# File 'lib/rails_lens/schema/adapters/postgresql.rb', line 278

def fetch_view_metadata
  target_schema = schema_name || 'public'
  result = connection.exec_query(<<~SQL.squish, 'PostgreSQL View Metadata')
    WITH view_info AS (
      SELECT
        'materialized' as view_type,
        false as is_updatable,
        mv.matviewname as view_name
      FROM pg_matviews mv
      WHERE mv.matviewname = '#{connection.quote_string(unqualified_table_name)}'
        AND mv.schemaname = '#{connection.quote_string(target_schema)}'
      UNION ALL
      SELECT
        'regular' as view_type,
        CASE WHEN v.is_updatable = 'YES' THEN true ELSE false END as is_updatable,
        v.table_name as view_name
      FROM information_schema.views v
      WHERE v.table_name = '#{connection.quote_string(unqualified_table_name)}'
        AND v.table_schema = '#{connection.quote_string(target_schema)}'
    ),
    dependencies AS (
      SELECT DISTINCT c2.relname as dependency_name
      FROM pg_class c1
      JOIN pg_depend d ON c1.oid = d.objid
      JOIN pg_class c2 ON d.refobjid = c2.oid
      WHERE c1.relname = '#{connection.quote_string(unqualified_table_name)}'
      AND c1.relkind IN ('v', 'm')
      AND c2.relkind IN ('r', 'v', 'm')
      AND d.deptype = 'n'
    )
    SELECT
      vi.view_type,
      vi.is_updatable,
      COALESCE(
        (SELECT array_agg(dependency_name ORDER BY dependency_name) FROM dependencies),
        ARRAY[]::text[]
      ) as dependencies
    FROM view_info vi
    LIMIT 1
  SQL

  return nil if result.rows.empty?

  row = result.rows.first
  # Parse PostgreSQL array string (e.g., "{table1,table2}" or "{}")
  deps_raw = row[2]
  deps = if deps_raw.is_a?(Array)
           deps_raw
         elsif deps_raw.is_a?(String) && deps_raw.start_with?('{')
           deps_raw.gsub(/[{}]/, '').split(',').map(&:strip).reject(&:empty?)
         else
           []
         end
  {
    type: row[0],
    updatable: ['t', true].include?(row[1]),
    dependencies: deps
  }
rescue ActiveRecord::StatementInvalid, PG::Error => e
  RailsLens.logger.debug { "Failed to fetch view metadata for #{table_name}: #{e.message}" }
  nil
end

#generate_annotation(model_class) ⇒ Object 



11
12
13
14
15
16
17
 
# File 'lib/rails_lens/schema/adapters/postgresql.rb', line 11

def generate_annotation(model_class)
  if model_class && ModelDetector.view_exists?(model_class)
    generate_view_annotation(model_class)
  else
    generate_table_annotation(model_class)
  end
end

#generate_table_annotation(_model_class) ⇒ Object 



19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
# File 'lib/rails_lens/schema/adapters/postgresql.rb', line 19

def generate_table_annotation(_model_class)
  lines = []
  lines << "table = \"#{table_name}\""
  lines << "database_dialect = \"#{database_dialect}\""

  # Add schema information for PostgreSQL
  lines << "schema = \"#{schema_name}\"" if schema_name && schema_name != 'public'
  lines << ''

  add_columns_toml(lines)
  add_indexes_toml(lines) if show_indexes?
  add_foreign_keys_toml(lines) if show_foreign_keys?
  add_check_constraints_toml(lines) if show_check_constraints?
  add_triggers_toml(lines) if show_triggers?
  add_table_comment_toml(lines) if show_comments?

  lines.join("\n")
end

#generate_view_annotation(model_class) ⇒ Object 



38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
 
# File 'lib/rails_lens/schema/adapters/postgresql.rb', line 38

def generate_view_annotation(model_class)
  lines = []
  lines << "view = \"#{table_name}\""
  lines << "database_dialect = \"#{database_dialect}\""

  # Add schema information for PostgreSQL
  lines << "schema = \"#{schema_name}\"" if schema_name && schema_name != 'public'

  # Fetch all view metadata in a single query
  view_info = fetch_view_metadata

  if view_info
    lines << "view_type = \"#{view_info[:type]}\"" if view_info[:type]
    lines << "updatable = #{view_info[:updatable]}"

    if view_info[:type] == 'materialized'
      lines << 'materialized = true'
      lines << 'refresh_strategy = "manual"'
    end
  end

  lines << ''

  add_columns_toml(lines)
  add_view_dependencies_toml(lines, view_info)

  lines.join("\n")
end

#view_definition ⇒ Object 



357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
 
# File 'lib/rails_lens/schema/adapters/postgresql.rb', line 357

def view_definition
  result = if view_type == 'materialized'
             connection.exec_query(<<~SQL.squish, 'PostgreSQL Materialized View Definition')
               SELECT definition FROM pg_matviews
               WHERE matviewname = '#{connection.quote_string(unqualified_table_name)}'
               LIMIT 1
             SQL
           else
             connection.exec_query(<<~SQL.squish, 'PostgreSQL View Definition')
               SELECT view_definition FROM information_schema.views
               WHERE table_name = '#{connection.quote_string(unqualified_table_name)}'
               LIMIT 1
             SQL
           end

  result.rows.first&.first&.strip
rescue ActiveRecord::StatementInvalid, PG::Error
  nil
end

#view_dependencies ⇒ Object 



352
353
354
355
 
# File 'lib/rails_lens/schema/adapters/postgresql.rb', line 352

def view_dependencies
  @view_metadata ||= fetch_view_metadata
  @view_metadata&.dig(:dependencies) || []
end

#view_last_refreshed ⇒ Object 



381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
 
# File 'lib/rails_lens/schema/adapters/postgresql.rb', line 381

def view_last_refreshed
  return nil unless view_type == 'materialized'

  # Get the last refresh time from pg_stat_user_tables
  result = connection.exec_query(<<~SQL.squish, 'PostgreSQL Materialized View Last Refresh')
    SELECT COALESCE(last_vacuum, last_autovacuum) as last_refreshed
    FROM pg_stat_user_tables
    WHERE relname = '#{connection.quote_string(unqualified_table_name)}'
    LIMIT 1
  SQL

  result.rows.first&.first
rescue ActiveRecord::StatementInvalid, PG::Error
  nil
end

#view_refresh_strategy ⇒ Object 



377
378
379
 
# File 'lib/rails_lens/schema/adapters/postgresql.rb', line 377

def view_refresh_strategy
  view_type == 'materialized' ? 'manual' : nil
end

#view_type ⇒ Object

Legacy methods - kept for backward compatibility but now use consolidated query



342
343
344
345
 
# File 'lib/rails_lens/schema/adapters/postgresql.rb', line 342

def view_type
  @view_metadata ||= fetch_view_metadata
  @view_metadata&.dig(:type)
end

#view_updatable? ⇒ Boolean

Returns:

  • (Boolean) 


347
348
349
350
 
# File 'lib/rails_lens/schema/adapters/postgresql.rb', line 347

def view_updatable?
  @view_metadata ||= fetch_view_metadata
  @view_metadata&.dig(:updatable) || false
end