Module: SchemaPlus::ActiveRecord::ConnectionAdapters::PostgresqlAdapter

Defined in:
lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb

Overview

The Postgresql adapter implements the SchemaPlus extensions and enhancements

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.included(base) ⇒ Object

:nodoc:



8
9
10
11
12
 
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 8

def self.included(base) #:nodoc:
  base.class_eval do
    remove_method :indexes
  end
end

Instance Method Details

#add_index(table_name, column_name, options = {}) ⇒ Object

SchemaPlus provides the following extra options for Postgres indexes:

  • :conditions - SQL conditions for the WHERE clause of the index

  • :expression - SQL expression to index. column_name can be nil or ommitted, in which case :name must be provided

  • :kind - index method for Postgresql to use

  • +:case_sensitive - if false then the index will be created on LOWER(column_name)

The :case_sensitive => false option ties in with Rails built-in support for case-insensitive searching:

validates_uniqueness_of :name, :case_sensitive => false



24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 24

def add_index(table_name, column_name, options = {})
  column_name, options = [], column_name if column_name.is_a?(Hash)
  column_names = Array(column_name).compact
  if column_names.empty?
    raise ArgumentError, "No columns and :expression missing from options - cannot create index" if options[:expression].blank?
    raise ArgumentError, "Index name not given. Pass :name option" if options[:name].blank?
  end

  index_type = options[:unique] ? "UNIQUE" : ""
  index_name = options[:name] || index_name(table_name, column_names)
  conditions = options[:conditions]

  if options[:expression] then
    sql = "CREATE #{index_type} INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} #{options[:expression]}"
  else
    quoted_column_names = column_names.map { |e| options[:case_sensitive] == false && e.to_s !~ /_id$/ ? "LOWER(#{quote_column_name(e)})" : quote_column_name(e) }

    sql = "CREATE #{index_type} INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} (#{quoted_column_names.join(", ")})"
    sql += " WHERE (#{ ::ActiveRecord::Base.send(:sanitize_sql, conditions, quote_table_name(table_name)) })" if conditions
  end
  execute sql
end

#foreign_keys(table_name, name = nil) ⇒ Object

:nodoc:



92
93
94
95
96
97
98
99
100
 
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 92

def foreign_keys(table_name, name = nil) #:nodoc:
  load_foreign_keys(<<-SQL, name)
SELECT f.conname, pg_get_constraintdef(f.oid), t.relname
  FROM pg_class t, pg_constraint f
 WHERE f.conrelid = t.oid
   AND f.contype = 'f'
   AND t.relname = '#{table_name}'
  SQL
end

#indexes(table_name, name = nil) ⇒ Object

:nodoc:



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
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
 
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 51

def indexes(table_name, name = nil) #:nodoc:
  schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',')
  result = query(<<-SQL, name)
   SELECT distinct i.relname, d.indisunique, d.indkey, m.amname, t.oid, 
            pg_get_expr(d.indpred, t.oid), pg_get_expr(d.indexprs, t.oid)
     FROM pg_class t, pg_class i, pg_index d, pg_am m
   WHERE i.relkind = 'i'
     AND i.relam = m.oid
     AND d.indexrelid = i.oid
     AND d.indisprimary = 'f'
     AND t.oid = d.indrelid
     AND t.relname = '#{table_name}'
     AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN (#{schemas}) )
  ORDER BY i.relname
  SQL

  result.map do |(index_name, is_unique, indkey, kind, oid, conditions, expression)|
    unique = (is_unique == 't')
    index_keys = indkey.split(" ")

    columns = Hash[query(<<-SQL, "Columns for index #{index_name} on #{table_name}")]
    SELECT a.attnum, a.attname
    FROM pg_attribute a
    WHERE a.attrelid = #{oid}
    AND a.attnum IN (#{index_keys.join(",")})
    SQL

    column_names = columns.values_at(*index_keys).compact
    if md = expression.try(:match, /^lower\(\(?([^)]+)\)?(::text)?\)$/i)
      column_names << md[1]
    end
    ::ActiveRecord::ConnectionAdapters::IndexDefinition.new(table_name, column_names,
                                                            :name => index_name,
                                                            :unique => unique,
                                                            :conditions => conditions,
                                                            :case_sensitive => !(expression =~ /lower/i),
                                                            :kind => kind.downcase == "btree" ? nil : kind,
                                                            :expression => expression)
  end
end

#reverse_foreign_keys(table_name, name = nil) ⇒ Object

:nodoc:



102
103
104
105
106
107
108
109
110
111
 
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 102

def reverse_foreign_keys(table_name, name = nil) #:nodoc:
  load_foreign_keys(<<-SQL, name)
SELECT f.conname, pg_get_constraintdef(f.oid), t2.relname
  FROM pg_class t, pg_class t2, pg_constraint f
 WHERE f.confrelid = t.oid
   AND f.conrelid = t2.oid
   AND f.contype = 'f'
   AND t.relname = '#{table_name}'
  SQL
end

#supports_partial_indexes? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


47
48
49
 
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 47

def supports_partial_indexes? #:nodoc:
  true
end

#view_definition(view_name, name = nil) ⇒ Object

:nodoc:



122
123
124
125
126
127
128
129
130
131
 
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 122

def view_definition(view_name, name = nil) #:nodoc:
  result = query(<<-SQL, name)
SELECT pg_get_viewdef(oid)
  FROM pg_class
 WHERE relkind = 'v'
   AND relname = '#{view_name}'
  SQL
  row = result.first
  row.first.chomp(';') unless row.nil?
end

#views(name = nil) ⇒ Object

:nodoc:



113
114
115
116
117
118
119
120
 
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 113

def views(name = nil) #:nodoc:
  schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',')
  query(<<-SQL, name).map { |row| row[0] }
SELECT viewname
  FROM pg_views
 WHERE schemaname IN (#{schemas})
  SQL
end