Module: ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaStatements
- Defined in:
- lib/core_ext/active_record/connection_adapters/postgresql/schema_statements.rb
Overview
:nodoc:
Constant Summary collapse
- FUNCTIONAL_INDEX_REGEXP =
Regexp used to find the function name and function argument of a function call:
/(\w+)\(((?:'.+'(?:::\w+)?, *)*)(\w+)\)/
- OPERATOR_REGEXP =
Regexp used to find the operator name (or operator string, e.g. “DESC NULLS LAST”):
/(.+?)\s([\w\s]+)$/
- INDEX_COLUMN_EXPRESSION =
Regex to find columns used in index statements
/ON [\w\.]+(?: USING \w+ )?\((.+)\)/
- INDEX_WHERE_EXPRESSION =
Regex to find where clause in index statements
/WHERE (.+)$/
Instance Method Summary collapse
-
#add_index(table_name, column_name, options = {}) ⇒ Object
Redefine original add_index method to handle :concurrently option.
-
#index_exists?(table_name, column_name, options = {}) ⇒ Boolean
Check to see if an index exists on a table for a given index definition.
-
#index_name(table_name, options) ⇒ Object
Derive the name of the index from the given table name and options hash.
-
#indexes(table_name) ⇒ Object
Returns an array of indexes for the given table.
-
#quoted_columns_for_index(column_names, options = {}) ⇒ Object
Override super method to provide support for expression column names.
-
#tables(name = nil) ⇒ Object
Returns the list of all tables in the schema search path or a specified schema.
Instance Method Details
#add_index(table_name, column_name, options = {}) ⇒ Object
Redefine original add_index method to handle :concurrently option.
Adds a new index to the table. column_name
can be a single Symbol, or an Array of Symbols.
Creating a partial index
add_index(:accounts, [:branch_id, :party_id], :using => 'BTree'
:unique => true, :concurrently => true, :where => 'active')
generates
CREATE UNIQUE INDEX CONCURRENTLY
index_accounts_on_branch_id_and_party_id
ON
accounts(branch_id, party_id)
WHERE
active
131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 |
# File 'lib/core_ext/active_record/connection_adapters/postgresql/schema_statements.rb', line 131 def add_index(table_name, column_name, = {}) creation_method = .delete(:concurrently) ? 'CONCURRENTLY' : nil # Whether to skip the quoting of columns. Used only for expressions like JSON indexes in which # the column is difficult to target for quoting. skip_column_quoting = .delete(:skip_column_quoting) or false index, algorithm, if_not_exists = (table_name, column_name, **) algorithm = creation_method || algorithm create_index = CreateIndexDefinition.new(index, algorithm, if_not_exists) # GOTCHA: # It ensures that there is no existing index only for the case when the index # is created concurrently to avoid changing the error behavior for default # index creation. # -- zekefast 2012-09-25 # GOTCHA: # This check prevents invalid index creation, so after migration failed # here there is no need to go to database and clean it from invalid # indexes. But note that this handles only one of the cases when index # creation can fail!!! All other case should be procesed manually. # -- zekefast 2012-09-25 if creation_method.present? && index_exists?(table_name, column_name, ) raise ::PgSaurus::IndexExistsError, "Index #{index.name} for `#{table_name}.#{column_name}` " \ "column can not be created concurrently, because such index already exists." end execute schema_creation.accept(create_index) end |
#index_exists?(table_name, column_name, options = {}) ⇒ Boolean
Check to see if an index exists on a table for a given index definition.
Examples
# Check that a partial index exists
index_exists?(:suppliers, :company_id, :where => 'active')
# GIVEN: 'index_suppliers_on_company_id' UNIQUE, btree (company_id) WHERE active
index_exists?(:suppliers, :company_id, :unique => true, :where => 'active') => true
index_exists?(:suppliers, :company_id, :unique => true) => false
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 |
# File 'lib/core_ext/active_record/connection_adapters/postgresql/schema_statements.rb', line 172 def index_exists?(table_name, column_name, = {}) column_names = Array.wrap(column_name) index_name = .key?(:name) ? [:name].to_s : index_name(table_name, column: column_names) # Always compare the index name default_comparator = lambda { |index| index.name == index_name } comparators = [default_comparator] # Add a comparator for each index option that is part of the query = [:unique, :where] .each do |index_option| comparators << if .key?(index_option) lambda do |index| pg_where_clause = index.send(index_option) # pg does nothing to boolean clauses, e.g. 'where active' => 'where active' if pg_where_clause.is_a?(TrueClass) or pg_where_clause.is_a?(FalseClass) pg_where_clause == [index_option] else # pg adds parentheses around non-boolean clauses, e.g. 'where color IS NULL' => 'where (color is NULL)' pg_where_clause.gsub!(/[()]/,'') # pg casts string comparison ::text. e.g. "where color = 'black'" => "where ((color)::text = 'black'::text)" pg_where_clause.gsub!(/::text/,'') # prevent case from impacting the comparison pg_where_clause.downcase == [index_option].downcase end end else # If the given index_option is not an argument to the index_exists? query, # select only those pg indexes that do not have the component lambda { |index| index.send(index_option).blank? } end end # Search all indexes for any that match all comparators indexes(table_name).any? do |index| comparators.inject(true) { |ret, comparator| ret && comparator.call(index) } end end |
#index_name(table_name, options) ⇒ Object
Derive the name of the index from the given table name and options hash.
212 213 214 215 216 217 218 219 220 221 222 223 224 225 |
# File 'lib/core_ext/active_record/connection_adapters/postgresql/schema_statements.rb', line 212 def index_name(table_name, ) #:nodoc: if Hash === # legacy support if [:column] column_names = Array.wrap([:column]).map {|c| expression_index_name(c)} "index_#{table_name}_on_#{column_names * '_and_'}" elsif [:name] [:name] else raise ArgumentError, "You must specify the index name" end else index_name(table_name, column: ) end end |
#indexes(table_name) ⇒ Object
Returns an array of indexes for the given table.
Patch 1:
Remove type specification from stored Postgres index definitions.
Patch 2:
Split compound functional indexes to array.
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 76 77 78 79 80 81 82 83 84 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 |
# File 'lib/core_ext/active_record/connection_adapters/postgresql/schema_statements.rb', line 41 def indexes(table_name) scope = quoted_scope(table_name) result = query(<<-SQL, "SCHEMA") SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid, pg_catalog.obj_description(i.oid, 'pg_class') AS comment FROM pg_class t INNER JOIN pg_index d ON t.oid = d.indrelid INNER JOIN pg_class i ON d.indexrelid = i.oid LEFT JOIN pg_namespace n ON n.oid = i.relnamespace WHERE i.relkind = 'i' AND d.indisprimary = 'f' AND t.relname = #{scope[:name]} AND n.nspname = #{scope[:schema]} ORDER BY i.relname SQL result.map do |row| index_name = row[0] unique = row[1] indkey = row[2].split(" ").map(&:to_i) inddef = row[3] oid = row[4] comment = row[5] using, expressions, where = inddef.scan(/ USING (\w+?) \((.+?)\)(?: WHERE (.+))?\z/m).flatten orders = {} opclasses = {} if indkey.include?(0) definition = inddef.sub(INDEX_WHERE_EXPRESSION, '') if column_expression = definition.match(INDEX_COLUMN_EXPRESSION)[1] columns = split_expression(expressions).map do |functional_name| remove_type(functional_name) end columns = columns.size > 1 ? columns : columns[0] end else columns = Hash[query(<<-SQL.strip_heredoc, "SCHEMA")].values_at(*indkey).compact SELECT a.attnum, a.attname FROM pg_attribute a WHERE a.attrelid = #{oid} AND a.attnum IN (#{indkey.join(",")}) SQL # add info on sort order (only desc order is explicitly specified, asc is the default) # and non-default opclasses expressions.scan(/(?<column>\w+)"?\s?(?<opclass>\w+_ops)?\s?(?<desc>DESC)?\s?(?<nulls>NULLS (?:FIRST|LAST))?/).each do |column, opclass, desc, nulls| opclasses[column] = opclass.to_sym if opclass if nulls orders[column] = [desc, nulls].compact.join(" ") else orders[column] = :desc if desc end end end IndexDefinition.new( table_name, index_name, unique, columns, orders: orders, opclasses: opclasses, where: where, using: using.to_sym, comment: comment.presence ) end end |
#quoted_columns_for_index(column_names, options = {}) ⇒ Object
Override super method to provide support for expression column names.
228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 |
# File 'lib/core_ext/active_record/connection_adapters/postgresql/schema_statements.rb', line 228 def quoted_columns_for_index(column_names, = {}) return [column_names] if column_names.is_a?(String) quoted_columns = Hash[ column_names.map do |name| column_name, operator_name = split_column_name(name) result_name = if column_name =~ FUNCTIONAL_INDEX_REGEXP _name = column_name.gsub(/\b#{$3}\b/, quote_column_name($3)) _name += " #{operator_name}" _name else quote_column_name(column_name).dup end [column_name.to_sym, result_name] end ] (quoted_columns, **).values.join(", ") end |
#tables(name = nil) ⇒ Object
Returns the list of all tables in the schema search path or a specified schema.
Patch:
If current user is not ‘postgres` original method return all tables from all schemas without schema prefix. This disables such behavior by querying only default schema. Tables with schemas will be queried later.
25 26 27 28 29 30 31 |
# File 'lib/core_ext/active_record/connection_adapters/postgresql/schema_statements.rb', line 25 def tables(name = nil) query(<<-SQL, 'SCHEMA').map { |row| row[0] } SELECT tablename FROM pg_tables WHERE schemaname = ANY (ARRAY['public']) SQL end |