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
-
#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:
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 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.
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 129 130 131 132 133 134 135 136 137 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 86 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 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
341 342 343 344 345 346 347 348 349 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 341 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
335 336 337 338 339 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 335 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
351 352 353 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 351 def drop_enum(name, = {}) execute "DROP TYPE #{enum_name(name, [:schema])}" end |
#enums ⇒ Object
:nodoc:
310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 310 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)
244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 244 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:
265 266 267 268 269 270 271 272 273 274 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 265 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.)
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 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 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 148 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
232 233 234 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 232 def query(*args) select(*args).map(&:values) end |
#rename_table_with_schema_plus(oldname, newname) ⇒ Object
:nodoc:
236 237 238 239 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 236 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:
276 277 278 279 280 281 282 283 284 285 286 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 276 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:
139 140 141 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 139 def supports_partial_indexes? #:nodoc: true end |
#view_definition(view_name, name = nil) ⇒ Object
:nodoc:
299 300 301 302 303 304 305 306 307 308 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 299 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:
288 289 290 291 292 293 294 295 296 297 |
# File 'lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb', line 288 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 |