Class: QueryGuard::Suggest::PatternExtractors

Inherits:
Object
  • Object
show all
Defined in:
lib/query_guard/suggest/pattern_extractors.rb

Overview

Extracts SQL patterns from queries for index suggestion. Conservative patterns only - only suggests when reasonably confident.

Example:

extractor = PatternExtractors.new
where_cols = extractor.extract_where_columns("SELECT * FROM users WHERE email = '[email protected]'")
# => ["email"]

order_cols = extractor.extract_order_by_columns("SELECT * FROM events ORDER BY created_at DESC")
# => ["created_at"]

Instance Method Summary collapse

Instance Method Details

#extract_all_columns(sql) ⇒ Hash

Extract both WHERE and ORDER BY columns Returns them in a structured format for index suggestion

Parameters:

  • sql (String) —

    SQL query

Returns:

  • (Hash) —

    { where_columns: [...], order_by_columns: [...] }



55
56
57
58
59
60
 
# File 'lib/query_guard/suggest/pattern_extractors.rb', line 55

def extract_all_columns(sql)
  {
    where_columns: extract_where_columns(sql),
    order_by_columns: extract_order_by_columns(sql)
  }
end

#extract_order_by_columns(sql) ⇒ Array<String>

Extract column names from ORDER BY clause Handles: simple column ordering Avoids: expressions, COLLATE, NULLS FIRST/LAST

Parameters:

  • sql (String) —

    SQL query

Returns:

  • (Array<String>) —

    Column names in ORDER BY clause



39
40
41
42
43
44
45
46
47
48
 
# File 'lib/query_guard/suggest/pattern_extractors.rb', line 39

def extract_order_by_columns(sql)
  return [] if sql.nil? || sql.empty?

  normalized = normalize_sql(sql)
  order_match = normalized.match(/\bORDER\s+BY\s+(.+?)(?:\s+(?:LIMIT)(?:\s|$)|$)/i)
  return [] unless order_match

  order_clause = order_match[1]
  extract_columns_from_order_by(order_clause)
end

#extract_table_name(sql) ⇒ String?

Extract table name from query Simple extraction of first table mentioned after FROM

Parameters:

  • sql (String) —

    SQL query

Returns:

  • (String, nil) —

    Table name



67
68
69
70
71
72
73
74
75
 
# File 'lib/query_guard/suggest/pattern_extractors.rb', line 67

def extract_table_name(sql)
  return nil if sql.nil? || sql.empty?

  normalized = normalize_sql(sql)
  # Match FROM or JOIN followed by table name
  # Handles: "FROM users", "FROM public.users", "JOIN orders ON"
  match = normalized.match(/\b(?:FROM|JOIN)\s+(?:(?:\w+\.)?(\w+))\b/i)
  match&.[](1)&.downcase
end

#extract_where_columns(sql) ⇒ Array<String>

Extract column names from WHERE clause Handles: simple equality, comparison operators Avoids: functions, expressions, BETWEEN

Parameters:

  • sql (String) —

    SQL query

Returns:

  • (Array<String>) —

    Column names in WHERE clause



22
23
24
25
26
27
28
29
30
31
 
# File 'lib/query_guard/suggest/pattern_extractors.rb', line 22

def extract_where_columns(sql)
  return [] if sql.nil? || sql.empty?

  normalized = normalize_sql(sql)
  where_match = normalized.match(/\bWHERE\s+(.+?)(?:\s+(?:GROUP|ORDER|LIMIT|HAVING)(?:\s|$)|$)/i)
  return [] unless where_match

  where_clause = where_match[1]
  extract_columns_from_where(where_clause)
end