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
-
.included(base) ⇒ Object
:nodoc:.
Instance Method Summary collapse
-
#add_index(table_name, column_name, options = {}) ⇒ Object
SchemaPlus provides the following extra options for PostgreSQL indexes: *
:conditions
- SQL conditions for the WHERE clause of the index *:expression
- SQL expression to index. - #alter_enum(name, value, options = {}) ⇒ Object
- #create_enum(name, *values) ⇒ Object
- #drop_enum(name, options = {}) ⇒ Object
-
#drop_table(name, options = {}) ⇒ Object
pg gem defines a drop_table with fewer options than our Abstract one, so use the abstract one instead.
-
#enums ⇒ Object
:nodoc:.
-
#exec_cache_with_schema_plus(sql, *args) ⇒ Object
Prepass to replace each ActiveRecord::DB_DEFAULT with a literal DEFAULT in the sql string.
-
#foreign_keys(table_name, name = nil) ⇒ Object
:nodoc:.
-
#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.
- #query(*args) ⇒ Object
-
#rename_table_with_schema_plus(oldname, newname) ⇒ Object
:nodoc:.
-
#reverse_foreign_keys(table_name, name = nil) ⇒ Object
:nodoc:.
-
#supports_partial_indexes? ⇒ Boolean
:nodoc:.
-
#view_definition(view_name, name = nil) ⇒ Object
:nodoc:.
-
#views(name = nil) ⇒ Object
:nodoc:.
Class Method Details
.included(base) ⇒ Object
:nodoc:
76 77 78 79 80 81 82 83 84 85 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 76 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 PostgreSQL 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 -
:operator_class
- an operator class name or a hash mapping column name to operator class name -
+: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.
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 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 102 def add_index(table_name, column_name, = {}) = {} if .nil? # some callers explicitly pass options=nil column_name, = [], column_name if column_name.is_a?(Hash) column_names = Array(column_name).compact column_names += Array([:with] || []) if column_names.empty? raise ArgumentError, "No columns and :expression missing from options - cannot create index" unless [:expression] raise ArgumentError, "Index name not given. Pass :name option" unless [:name] end index_type = [:unique] ? "UNIQUE" : "" index_name = [:name] || index_name(table_name, column_names) conditions = [:conditions] kind = [:kind] operator_classes = [:operator_class] if operator_classes and not operator_classes.is_a? Hash operator_classes = Hash[column_names.map {|name| [name, operator_classes]}] end if expression = [:expression] then raise ArgumentError, "Cannot specify :case_sensitive => false with an expression. Use LOWER(column_name)" if [: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, '']}] (operator_classes||{}).each do |column, opclass| option_strings[column] += " #{opclass}" if opclass end option_strings = add_index_sort_order(option_strings, column_names, ) if [: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, , e) end |
#alter_enum(name, value, options = {}) ⇒ Object
358 359 360 361 362 363 364 365 366 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 358 def alter_enum(name, value, = {}) opts = case when [:before] then "BEFORE #{escape_enum_value([:before])}" when [:after] then "AFTER #{escape_enum_value([:after])}" else '' end execute "ALTER TYPE #{enum_name(name, [:schema])} ADD VALUE #{escape_enum_value(value)} #{opts}" end |
#create_enum(name, *values) ⇒ Object
352 353 354 355 356 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 352 def create_enum(name, *values) = values. list = values.map { |value| escape_enum_value(value) } execute "CREATE TYPE #{enum_name(name, [:schema])} AS ENUM (#{list.join(',')})" end |
#drop_enum(name, options = {}) ⇒ Object
368 369 370 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 368 def drop_enum(name, = {}) execute "DROP TYPE #{enum_name(name, [:schema])}" end |
#drop_table(name, options = {}) ⇒ Object
pg gem defines a drop_table with fewer options than our Abstract one, so use the abstract one instead
374 375 376 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 374 def drop_table(name, ={}) SchemaPlus::ActiveRecord::ConnectionAdapters::AbstractAdapter.instance_method(:drop_table).bind(self).call(name, ) end |
#enums ⇒ Object
:nodoc:
327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 327 def enums #:nodoc: result = query(<<-SQL) SELECT N.nspname AS schema_name, T.typname AS enum_name, E.enumlabel AS enum_label, E.enumsortorder AS enum_sort_order --array_agg(E.enumlabel ORDER BY enumsortorder) AS labels FROM pg_type T JOIN pg_enum E ON E.enumtypid = T.oid JOIN pg_namespace N ON N.oid = T.typnamespace ORDER BY 1, 2, 4 SQL result.reduce([]) do |res, row| last = res.last if last && last[0] == row[0] && last[1] == row[1] last[2] << row[2] else res << (row[0..1] << [row[2]]) end res end end |
#exec_cache_with_schema_plus(sql, *args) ⇒ 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)
261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 261 def exec_cache_with_schema_plus(sql, *args) name_passed = (2 == args.size) binds, name = args.reverse 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 args = name_passed ? [name, binds] : [binds] exec_cache_without_schema_plus(sql, *args) end |
#foreign_keys(table_name, name = nil) ⇒ Object
:nodoc:
282 283 284 285 286 287 288 289 290 291 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 282 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_without_namespace(table_name)}' AND t.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = #{namespace_sql(table_name)} ) SQL end |
#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.)
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 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 165 def indexes(table_name, name = nil) #:nodoc: result = query(<<-SQL, name) SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid, m.amname, pg_get_expr(d.indpred, t.oid) as conditions, pg_get_expr(d.indexprs, t.oid) as expression, d.indclass FROM pg_class t INNER JOIN pg_index d ON t.oid = d.indrelid INNER JOIN pg_class i ON d.indexrelid = i.oid INNER JOIN pg_am m ON i.relam = m.oid WHERE i.relkind = 'i' AND d.indisprimary = 'f' AND t.relname = '#{table_name_without_namespace(table_name)}' AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = #{namespace_sql(table_name)} ) ORDER BY i.relname SQL result.map do |(index_name, is_unique, indkey, inddef, oid, kind, conditions, expression, indclass)| unique = (is_unique == 't' || is_unique == true) # The test against true is for JDBC which is returning a boolean and not a String. index_keys = indkey.split(" ") opclasses = indclass.split(" ") rows = query(<<-SQL, "Columns for index #{index_name} on #{table_name}") SELECT CAST(a.attnum as VARCHAR), a.attname, t.typname FROM pg_attribute a INNER JOIN pg_type t ON a.atttypid = t.oid WHERE a.attrelid = #{oid} SQL columns = {} types = {} rows.each do |num, name, type| columns[num] = name types[name] = type end column_names = columns.values_at(*index_keys).compact case_sensitive = true # extract column names from the expression, for a # case-insensitive index. # only applies to character, character varying, and text if expression rexp_lower = %r{\blower\(\(?([^)]+)(\)::text)?\)} if expression.match /\A#{rexp_lower}(?:, #{rexp_lower})*\z/ case_insensitive_columns = expression.scan(rexp_lower).map(&:first).select{|column| %W[char varchar text].include? types[column]} if case_insensitive_columns.any? case_sensitive = false column_names = index_keys.map { |index_key| index_key == '0' ? case_insensitive_columns.shift : columns[index_key] }.compact end end end opclass_name = {} rows = query(<<-SQL, "Op classes for index #{index_name} on #{table_name}") SELECT oid, opcname FROM pg_opclass WHERE (NOT opcdefault) AND oid IN (#{opclasses.join(',')}) SQL rows.each do |oid, opcname| opclass_name[oid.to_s] = opcname end operator_classes = {} index_keys.zip(opclasses).each do |index_key, opclass| operator_classes[columns[index_key]] = opclass_name[opclass] end operator_classes.delete_if{|k,v| v.nil?} # add info on sort order for columns (only desc order is explicitly specified, asc is the default) desc_order_columns = inddef.scan(/(\w+) DESC/).flatten orders = desc_order_columns.any? ? Hash[column_names.map {|column| [column, desc_order_columns.include?(column) ? :desc : :asc]}] : {} ::ActiveRecord::ConnectionAdapters::IndexDefinition.new(table_name, column_names, :name => index_name, :unique => unique, :orders => orders, :conditions => conditions, :case_sensitive => case_sensitive, :kind => kind.downcase == "btree" ? nil : kind, :operator_classes => operator_classes, :expression => expression) end end |
#query(*args) ⇒ Object
249 250 251 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 249 def query(*args) select(*args).map(&:values) end |
#rename_table_with_schema_plus(oldname, newname) ⇒ Object
:nodoc:
253 254 255 256 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 253 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:
293 294 295 296 297 298 299 300 301 302 303 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 293 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_without_namespace(table_name)}' AND t.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = #{namespace_sql(table_name)} ) SQL end |
#supports_partial_indexes? ⇒ Boolean
:nodoc:
156 157 158 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 156 def supports_partial_indexes? #:nodoc: true end |
#view_definition(view_name, name = nil) ⇒ Object
:nodoc:
316 317 318 319 320 321 322 323 324 325 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 316 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:
305 306 307 308 309 310 311 312 313 314 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 305 def views(name = nil) #:nodoc: sql = <<-SQL SELECT viewname FROM pg_views WHERE schemaname = ANY (current_schemas(false)) AND viewname NOT LIKE 'pg\_%' SQL sql += " AND schemaname != 'postgis'" if adapter_name == 'PostGIS' query(sql, name).map { |row| row[0] } end |