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

Defined Under Namespace

Modules: AddColumnOptions

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.included(base) ⇒ Object

:nodoc:



60
61
62
63
64
65
66
67
68
69
 
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 60

def self.included(base) #:nodoc:
  base.class_eval do
    if ::ActiveRecord::VERSION::MAJOR.to_i < 4 && !defined?(JRUBY_VERSION)
      remove_method :indexes
    end
    alias_method_chain :rename_table, :schema_plus
    alias_method_chain :exec_cache, :schema_plus unless defined?(JRUBY_VERSION)
  end
  ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn.send(:include, PostgreSQLColumn) unless ::ActiveRecord::ConnectionAdapters::PostgreSQLColumn.include?(PostgreSQLColumn)
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 - setting to false is a shorthand for :expression => ‘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

Since since :case_sensitive => false is implemented by using :expression, this raises an ArgumentError if both are specified simultaneously.



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
 
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 85

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" unless options[:expression]
    raise ArgumentError, "Index name not given. Pass :name option" unless options[:name]
  end

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

  if expression = options[:expression] then
    raise ArgumentError, "Cannot specify :case_sensitive => false with an expression.  Use LOWER(column_name)" if options[:case_sensitive] == false
    # Wrap expression in parentheses if necessary
    expression = "(#{expression})" if expression !~ /(using|with|tablespace|where)/i
    expression = "USING #{kind} #{expression}" if kind
    expression = "#{expression} WHERE #{conditions}" if conditions

    sql = "CREATE #{index_type} INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} #{expression}"
  else
    option_strings = Hash[column_names.map {|name| [name, '']}]
    option_strings = add_index_sort_order(option_strings, column_names, options)

    if options[:case_sensitive] == false
      caseable_columns = columns(table_name).select { |col| [:string, :text].include?(col.type) }.map(&:name)
      quoted_column_names = column_names.map do |col_name|
        (caseable_columns.include?(col_name.to_s) ? "LOWER(#{quote_column_name(col_name)})" : quote_column_name(col_name)) + option_strings[col_name]
      end
    else
      quoted_column_names = column_names.map { |col_name| quote_column_name(col_name) + option_strings[col_name] }
    end

    expression = "(#{quoted_column_names.join(', ')})"
    expression = "USING #{kind} #{expression}" if kind

    sql = "CREATE #{index_type} INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} #{expression}"
    sql += " WHERE (#{ ::ActiveRecord::Base.send(:sanitize_sql, conditions, quote_table_name(table_name)) })" if conditions
  end
  execute sql
rescue => e
  SchemaStatements.add_index_exception_handler(self, table_name, column_names, options, e)
end

#exec_cache_with_schema_plus(sql, binds) ⇒ Object

Prepass to replace each ActiveRecord::DB_DEFAULT with a literal DEFAULT in the sql string. (The underlying pg gem provides no way to bind a value that will replace $n with DEFAULT)



218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
 
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 218

def exec_cache_with_schema_plus(sql, binds)
  if binds.any?{ |col, val| val.equal? ::ActiveRecord::DB_DEFAULT}
    j = 0
    binds.each_with_index do |(col, val), i|
    if val.equal? ::ActiveRecord::DB_DEFAULT
      sql = sql.sub(/\$#{i+1}/, 'DEFAULT')
    else
      sql = sql.sub(/\$#{i+1}/, "$#{j+1}") if i != j
      j += 1
    end
    end
    binds = binds.reject{|col, val| val.equal? ::ActiveRecord::DB_DEFAULT}
  end
  exec_cache_without_schema_plus(sql, binds)
end

#foreign_keys(table_name, name = nil) ⇒ Object

:nodoc:



234
235
236
237
238
239
240
241
242
243
 
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 234

def foreign_keys(table_name, name = nil) #:nodoc:
  load_foreign_keys("SELECT f.conname, pg_get_constraintdef(f.oid), t.relname\n  FROM pg_class t, pg_constraint f\n WHERE f.conrelid = t.oid\n   AND f.contype = 'f'\n   AND t.relname = '\#{table_name_without_namespace(table_name)}'\n   AND t.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = \#{namespace_sql(table_name)} )\n  SQL\nend\n", name)

#indexes(table_name, name = nil) ⇒ Object

This method entirely duplicated from AR’s postgresql_adapter.c, but includes the extra bit to determine the column name for a case-insensitive index. (Haven’t come up with any clever way to only code up the case-insensitive column name bit here and otherwise use the existing method.)



139
140
141
142
143
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
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
 
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 139

def indexes(table_name, name = nil) #:nodoc:
  result = query("\n   SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid,\n          m.amname, pg_get_expr(d.indpred, t.oid) as conditions, pg_get_expr(d.indexprs, t.oid) as expression\n   FROM pg_class t\n   INNER JOIN pg_index d ON t.oid = d.indrelid\n   INNER JOIN pg_class i ON d.indexrelid = i.oid\n   INNER JOIN pg_am m ON i.relam = m.oid\n   WHERE i.relkind = 'i'\n     AND d.indisprimary = 'f'\n     AND t.relname = '\#{table_name_without_namespace(table_name)}'\n     AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = \#{namespace_sql(table_name)} )\n  ORDER BY i.relname\n  SQL\n\n  result.map do |(index_name, is_unique, indkey, inddef, oid, kind, conditions, expression)|\n    unique = (is_unique == 't')\n    index_keys = indkey.split(\" \")\n\n    rows = query(<<-SQL, \"Columns for index \#{index_name} on \#{table_name}\")\n      SELECT CAST(a.attnum as VARCHAR), a.attname, t.typname\n      FROM pg_attribute a\n      INNER JOIN pg_type t ON a.atttypid = t.oid\n      WHERE a.attrelid = \#{oid}\n    SQL\n    columns = {}\n    types = {}\n    rows.each do |num, name, type|\n      columns[num] = name\n      types[name] = type\n    end\n\n    column_names = columns.values_at(*index_keys).compact\n    case_sensitive = true\n\n    # extract column names from the expression, for a\n    # case-insensitive index.\n    # only applies to character, character varying, and text\n    if expression\n      rexp_lower = %r{\\blower\\(\\(?([^)]+)(\\)::text)?\\)}\n      if expression.match /\\A\#{rexp_lower}(?:, \#{rexp_lower})*\\z/\n        case_insensitive_columns = expression.scan(rexp_lower).map(&:first).select{|column| %W[char varchar text].include? types[column]}\n        if case_insensitive_columns.any?\n          case_sensitive = false\n          column_names = index_keys.map { |index_key|\n            index_key == '0' ? case_insensitive_columns.shift : columns[index_key]\n          }.compact\n        end\n      end\n    end\n\n    # add info on sort order for columns (only desc order is explicitly specified, asc is the default)\n    desc_order_columns = inddef.scan(/(\\w+) DESC/).flatten\n    orders = desc_order_columns.any? ? Hash[column_names.map {|column| [column, desc_order_columns.include?(column) ? :desc : :asc]}] : {}\n\n    ::ActiveRecord::ConnectionAdapters::IndexDefinition.new(table_name, column_names,\n                                                            :name => index_name,\n                                                            :unique => unique,\n                                                            :orders => orders,\n                                                            :conditions => conditions,\n                                                            :case_sensitive => case_sensitive,\n                                                            :kind => kind.downcase == \"btree\" ? nil : kind,\n                                                            :expression => expression)\n  end\nend\n", name)

#query(*args) ⇒ Object 



206
207
208
 
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 206

def query(*args)
  select(*args).map(&:values)
end

#rename_table_with_schema_plus(oldname, newname) ⇒ Object

:nodoc:



210
211
212
213
 
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 210

def rename_table_with_schema_plus(oldname, newname) #:nodoc:
  rename_table_without_schema_plus(oldname, newname)
  rename_indexes_and_foreign_keys(oldname, newname)
end

#reverse_foreign_keys(table_name, name = nil) ⇒ Object

:nodoc:



245
246
247
248
249
250
251
252
253
254
255
 
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 245

def reverse_foreign_keys(table_name, name = nil) #:nodoc:
  load_foreign_keys("SELECT f.conname, pg_get_constraintdef(f.oid), t2.relname\n  FROM pg_class t, pg_class t2, pg_constraint f\n WHERE f.confrelid = t.oid\n   AND f.conrelid = t2.oid\n   AND f.contype = 'f'\n   AND t.relname = '\#{table_name_without_namespace(table_name)}'\n   AND t.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = \#{namespace_sql(table_name)} )\n  SQL\nend\n", name)

#supports_partial_indexes? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


130
131
132
 
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 130

def supports_partial_indexes? #:nodoc:
  true
end

#view_definition(view_name, name = nil) ⇒ Object

:nodoc:



267
268
269
270
271
272
273
274
275
276
 
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 267

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

#views(name = nil) ⇒ Object

:nodoc:



257
258
259
260
261
262
263
264
265
 
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 257

def views(name = nil) #:nodoc:
  sql = "    SELECT viewname\n      FROM pg_views\n    WHERE schemaname = ANY (current_schemas(false))\n  SQL\n  sql += \" AND schemaname != 'postgis'\" if adapter_name == 'PostGIS'\n  query(sql, name).map { |row| row[0] }\nend\n"