Module: RR::ConnectionExtenders::PostgreSQLExtender

Defined in:
lib/rubyrep/connection_extenders/postgresql_extender.rb

Overview

Provides various PostgreSQL specific functionality required by Rubyrep.

Instance Method Summary collapse

Instance Method Details

#column_aware_quote(value, column) ⇒ String

Quotes the value so it can be used in SQL insert / update statements.

Parameters:

  • value (Object) —

    the target value

  • column (ActiveRecord::ConnectionAdapters::PostgreSQLColumn) —

    the target column

Returns:

  • (String) —

    the quoted string



111
112
113
 
# File 'lib/rubyrep/connection_extenders/postgresql_extender.rb', line 111

def column_aware_quote(value, column)
  quote column.type_cast_for_database value
end

#fixed_type_cast(value, column) ⇒ Object

Casts a value returned from the database back into the according ruby type.

Parameters:

  • value (Object) —

    the received value

  • column (ActiveRecord::ConnectionAdapters::PostgreSQLColumn) —

    the originating column

Returns:

  • (Object) —

    the casted value



120
121
122
123
124
125
126
127
128
 
# File 'lib/rubyrep/connection_extenders/postgresql_extender.rb', line 120

def fixed_type_cast(value, column)
  if column.sql_type == 'bytea' and RUBY_PLATFORM == 'java'
    # Apparently in Java / JRuby binary data are automatically unescaped.
    # So #type_cast_from_database must be prevented from double-unescaping the binary data.
      value
  else
    column.type_cast_from_database value
  end
end

#primary_key_names(table) ⇒ Object

Returns an ordered list of primary key column names of the given table



33
34
35
36
37
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
66
67
68
69
70
71
72
73
74
75
 
# File 'lib/rubyrep/connection_extenders/postgresql_extender.rb', line 33

def primary_key_names(table)
  row = self.select_one(<<-end_sql)
    SELECT relname
    FROM pg_class
    WHERE relname = '#{table}' and relnamespace IN
      (SELECT oid FROM pg_namespace WHERE nspname in (#{schemas}))
  end_sql
  raise "table '#{table}' does not exist" if row.nil?
  
  row = self.select_one(<<-end_sql)
    SELECT cons.conkey 
    FROM pg_class           rel
    JOIN pg_constraint      cons ON (rel.oid = cons.conrelid)
    WHERE cons.contype = 'p' AND rel.relname = '#{table}' AND rel.relnamespace IN
      (SELECT oid FROM pg_namespace WHERE nspname in (#{schemas}))
  end_sql
  return [] if row.nil?
  column_parray = row['conkey']
  
  # Change a Postgres Array of attribute numbers
  # (returned in String form, e. g.: "{1,2}") into an array of Integers
  if column_parray.kind_of?(Array)
    column_ids = column_parray # in JRuby the attribute numbers are already returned as array
  else
    column_ids = column_parray.sub(/^\{(.*)\}$/,'\1').split(',').map {|a| a.to_i}
  end

  columns = {}
  rows = self.select_all(<<-end_sql)
    SELECT attnum, attname
    FROM pg_class           rel
    JOIN pg_constraint      cons ON (rel.oid = cons.conrelid)
    JOIN pg_attribute       attr ON (rel.oid = attr.attrelid and attr.attnum = any (cons.conkey))
    WHERE cons.contype = 'p' AND rel.relname = '#{table}' AND rel.relnamespace IN
      (SELECT oid FROM pg_namespace WHERE nspname in (#{schemas}))
  end_sql
  sorted_columns = []
  if not rows.nil?
    rows.each() {|r| columns[r['attnum'].to_i] = r['attname']}
    sorted_columns = column_ids.map {|column_id| columns[column_id]}
  end
  sorted_columns
end

#referenced_tables(tables) ⇒ Object

Returns for each given table, which other tables it references via foreign key constraints.

  • tables: an array of table names

Returns: a hash with

  • key: name of the referencing table

  • value: an array of names of referenced tables



83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
 
# File 'lib/rubyrep/connection_extenders/postgresql_extender.rb', line 83

def referenced_tables(tables)
  rows = self.select_all(<<-end_sql)
    select distinct referencing.relname as referencing_table, referenced.relname as referenced_table
    from pg_class referencing
    left join pg_constraint on referencing.oid = pg_constraint.conrelid
    left join pg_class referenced on pg_constraint.confrelid = referenced.oid
    where referencing.relkind='r'
    and referencing.relname in ('#{tables.join("', '")}')
    and referencing.relnamespace IN
      (SELECT oid FROM pg_namespace WHERE nspname in (#{schemas}))
  end_sql
  result = {}
  rows.each do |row|
    unless result.include? row['referencing_table']
      result[row['referencing_table']] = []
    end
    if row['referenced_table'] != nil
      result[row['referencing_table']] << row['referenced_table']
    end
  end
  result
end

#schemas ⇒ Object

Returns an array of schemas in the current search path.



11
12
13
14
15
16
17
 
# File 'lib/rubyrep/connection_extenders/postgresql_extender.rb', line 11

def schemas
  unless @schemas
    search_path = select_one("show search_path")['search_path']
    @schemas = search_path.split(/,/).map { |p| quote(p.strip) }.join(',')
  end
  @schemas
end

#tables(name = nil) ⇒ Object

*** Monkey patch*** Returns the list of all tables in the schema search path or a specified schema. This overwrites the according ActiveRecord::PostgreSQLAdapter method to make sure that also search paths with spaces work (E. g. ‘public, rr’ instead of only ‘public,rr’)



24
25
26
27
28
29
30
 
# File 'lib/rubyrep/connection_extenders/postgresql_extender.rb', line 24

def tables(name = nil)
  select_all(<<-SQL, name).map { |row| row['tablename'] }
    SELECT tablename
      FROM pg_tables
     WHERE schemaname IN (#{schemas})
  SQL
end