Class: ActiveRecord::ConnectionAdapters::PostgreSQLTableDefinition

Inherits:
TableDefinition
  • Object
show all
Defined in:
lib/active_record/postgresql_extensions/tables.rb,
lib/active_record/postgresql_extensions/geometry.rb

Overview

Creates a PostgreSQL table definition. This class isn’t really meant to be used directly. Instead, see PostgreSQLAdapter#create_table for usage.

Beyond our various PostgreSQL-specific extensions, we’ve also added the post_processing member, which allows you to tack on some SQL statements to run after creating the table. This member should be an Array of SQL statements to run once the table has been created. See the source code for PostgreSQLAdapter#create_table and PostgreSQLTableDefinition#geometry for an example of its use.

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(base, table_name, options = {}) ⇒ PostgreSQLTableDefinition

:nodoc:



183
184
185
186
 
# File 'lib/active_record/postgresql_extensions/tables.rb', line 183

def initialize(base, table_name, options = {}) #:nodoc:
  @table_name, @options = table_name, options
  super(base)
end

Instance Attribute Details

#base ⇒ Object

Returns the value of attribute base.



181
182
183
 
# File 'lib/active_record/postgresql_extensions/tables.rb', line 181

def base
  @base
end

#options ⇒ Object

Returns the value of attribute options.



181
182
183
 
# File 'lib/active_record/postgresql_extensions/tables.rb', line 181

def options
  @options
end

#table_name ⇒ Object

Returns the value of attribute table_name.



181
182
183
 
# File 'lib/active_record/postgresql_extensions/tables.rb', line 181

def table_name
  @table_name
end

Instance Method Details

#check_constraint(expression, options = {}) ⇒ Object

Add a CHECK constraint to the table. See PostgreSQLCheckConstraint for more details.



279
280
281
 
# File 'lib/active_record/postgresql_extensions/tables.rb', line 279

def check_constraint(expression, options = {})
  table_constraints << PostgreSQLCheckConstraint.new(@base, expression, options)
end

#column_with_constraints(name, type, *args) ⇒ Object

:nodoc:



305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
 
# File 'lib/active_record/postgresql_extensions/tables.rb', line 305

def column_with_constraints(name, type, *args) #:nodoc:
  options = args.extract_options!
  check = options.delete(:check)
  references = options.delete(:references)
  unique = options.delete(:unique)
  primary_key = options.delete(:primary_key)
  column_without_constraints(name, type, options)

  if check
    check = if !check.is_a?(Array)
      [ check ]
    else
      check
    end

    table_constraints << check.collect do |c|
      if c.is_a?(Hash)
        PostgreSQLCheckConstraint.new(@base, c.delete(:expression), c)
      else
        PostgreSQLCheckConstraint.new(@base, c)
      end
    end
  end

  if references
    ref_table, ref_options = if references.is_a?(Hash)
      [ references.delete(:table), references ]
    else
      [ references, {} ]
    end

    table_constraints << PostgreSQLForeignKeyConstraint.new(
      @base,
      name,
      ref_table,
      ref_options
    )
  end

  if unique
    unless unique.is_a?(Hash)
      unique = {}
    end
    table_constraints << PostgreSQLUniqueConstraint.new(@base, name, unique)
  end

  if primary_key
    unless primary_key.is_a?(Hash)
      primary_key = {}
    end
    table_constraints << PostgreSQLPrimaryKeyConstraint.new(@base, name, primary_key)
  end

  self
end

#exclude(excludes, options = {}) ⇒ Object

Add an EXCLUDE constraint to the table. See PostgreSQLExcludeConstraint for more details.



297
298
299
 
# File 'lib/active_record/postgresql_extensions/tables.rb', line 297

def exclude(excludes, options = {})
  table_constraints << PostgreSQLExcludeConstraint.new(@base, table_name, excludes, options)
end

#foreign_key(columns, ref_table, *args) ⇒ Object

Add a FOREIGN KEY constraint to the table. See PostgreSQLForeignKeyConstraint for more details.



291
292
293
 
# File 'lib/active_record/postgresql_extensions/tables.rb', line 291

def foreign_key(columns, ref_table, *args)
  table_constraints << PostgreSQLForeignKeyConstraint.new(@base, columns, ref_table, *args)
end

#geography(column_name, opts = {}) ⇒ Object 



233
234
235
236
237
238
239
240
241
 
# File 'lib/active_record/postgresql_extensions/geometry.rb', line 233

def geography(column_name, opts = {})
  opts = {
    :srid => ActiveRecord::PostgreSQLExtensions::PostGIS.UNKNOWN_SRIDS[:geography]
  }.merge(opts)

  self.spatial(column_name, opts.merge(
    :spatial_column_type => :geography
  ))
end

#geometry(column_name, opts = {}) ⇒ Object 



229
230
231
 
# File 'lib/active_record/postgresql_extensions/geometry.rb', line 229

def geometry(column_name, opts = {})
  self.spatial(column_name, opts)
end

#index(name, columns, options = {}) ⇒ Object

Add an INDEX to the table. This INDEX will be added during post processing after the table has been created. See PostgreSQLIndexDefinition for more details.



365
366
367
 
# File 'lib/active_record/postgresql_extensions/tables.rb', line 365

def index(name, columns, options = {})
  post_processing << PostgreSQLIndexDefinition.new(@base, name, self.table_name, columns, options)
end

#like(parent_table, options = {}) ⇒ Object

Creates a LIKE statement for use in a table definition.

Options

  • :including and :excluding - set options for the INCLUDING and EXCLUDING clauses in a LIKE statement. Valid values are :constraints, :defaults and :indexes. You can set one or more by using an Array.

See the PostgreSQL documentation for details on how to use LIKE. Be sure to take note as to how it differs from INHERITS.

Also, be sure to note that, like, this LIKE isn’t, like, the LIKE you use in a WHERE condition. This is, PostgreSQL’s own special LIKE clause for table definitions. Like.



259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
 
# File 'lib/active_record/postgresql_extensions/tables.rb', line 259

def like(parent_table, options = {})
  assert_valid_like_types(options[:includes])
  assert_valid_like_types(options[:excludes])

  # Huh? Whyfor I dun this?
  # @like = base.with_schema(@schema) { "LIKE #{base.quote_table_name(parent_table)}" }
  @like = "LIKE #{@base.quote_table_name(parent_table)}"

  if options[:including]
    @like << Array.wrap(options[:including]).collect { |l| " INCLUDING #{l.to_s.upcase}" }.join
  end

  if options[:excluding]
    @like << Array.wrap(options[:excluding]).collect { |l| " EXCLUDING #{l.to_s.upcase}" }.join
  end
  @like
end

#post_processing ⇒ Object

Add statements to execute to after a table has been created.



370
371
372
 
# File 'lib/active_record/postgresql_extensions/tables.rb', line 370

def post_processing
  @post_processing ||= []
end

#primary_key_constraint(columns, options = {}) ⇒ Object 



301
302
303
 
# File 'lib/active_record/postgresql_extensions/tables.rb', line 301

def primary_key_constraint(columns, options = {})
  table_constraints << PostgreSQLPrimaryKeyConstraint.new(@base, columns, options)
end

#spatial(column_name, opts = {}) ⇒ Object

This is a special spatial type for the PostGIS extension’s data types. It is used in a table definition to define a spatial column.

Depending on the version of PostGIS being used, we’ll try to create geometry columns in a post-2.0-ish, typmod-based way or a pre-2.0-ish AddGeometryColumn-based way. We can also add CHECK constraints and create a GiST index on the column all in one go.

In versions of PostGIS prior to 2.0, geometry columns are created using the AddGeometryColumn and will created with CHECK constraints where appropriate and entries to the geometry_columns will be updated accordingly.

In versions of PostGIS after 2.0, geometry columns are creating using typmod specifiers. CHECK constraints can still be created, but their creation must be forced using the :force_constraints option.

The geometry and geography methods are shortcuts to calling the spatial method with the :spatial_column_type option set accordingly.

Options

  • :spatial_column_type - the column type. This value can be one of :geometry or :geography. This value doesn’t refer to the spatial type used by the column, but rather by the actual column type itself.

  • :geometry_type - set the geometry type. The actual data type is either “geometry” or “geography”; this option refers to the spatial type being used.

  • :add_constraints - automatically creates the CHECK constraints used to enforce ndims, srid and geometry type. The default is true.

  • :force_constraints - forces the creation of CHECK constraints in versions of PostGIS post-2.0.

  • :add_geometry_columns_entry - automatically adds an entry to the geometry_columns table. We will try to delete any existing match in geometry_columns before inserting. The default is true. This value is ignored in versions of PostGIS post-2.0.

  • :create_gist_index - automatically creates a GiST index for the new geometry column. This option accepts either a true/false expression or a String. If the value is a String, we’ll use it as the index name. The default is true.

  • :ndims - the number of dimensions to allow in the geometry. This value is either 2 or 3 by default depending on the value of the :geometry_type option. If the :geometry_type ends in an “m” (for “measured geometries” the default is 3); for everything else, it is 2.

  • :srid - the SRID, a.k.a. the Spatial Reference Identifier. The default depends on the version of PostGIS being used and the spatial column type being used. Refer to the PostGIS docs for the specifics, but generally this means either a value of -1 for versions of PostGIS prior to 2.0 for geometry columns and a value of 0 for versions post-2.0 and for all geography columns.



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
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
 
# File 'lib/active_record/postgresql_extensions/geometry.rb', line 107

def spatial(column_name, opts = {})
  opts = {
    :spatial_column_type => :geometry,
    :geometry_type => :geometry,
    :add_constraints => true,
    :force_constraints => false,
    :add_geometry_columns_entry => true,
    :create_gist_index => true,
    :srid => ActiveRecord::PostgreSQLExtensions::PostGIS.UNKNOWN_SRID
  }.merge(opts)

  if opts[:ndims].blank?
    opts[:ndims] = if opts[:geometry_type].to_s.upcase =~ /M$/
      3
    else
      2
    end
  end

  assert_valid_spatial_column_type(opts[:spatial_column_type])
  assert_valid_geometry_type(opts[:geometry_type])
  assert_valid_ndims(opts[:ndims], opts[:geometry_type])

  column_type = if ActiveRecord::PostgreSQLExtensions::PostGIS.VERSION[:lib] < '2.0'
    opts[:spatial_column_type]
  else
    column_args = [ opts[:geometry_type].to_s.upcase ]

    if ![ 0, -1 ].include?(opts[:srid])
      column_args << opts[:srid]
    end

    "#{opts[:spatial_column_type]}(#{column_args.join(', ')})"
  end

  column = self[column_name] || ColumnDefinition.new(base, column_name, column_type)
  column.default = opts[:default]
  column.null = opts[:null]

  unless @columns.include?(column)
    @columns << column
    if opts[:add_constraints] && (
      ActiveRecord::PostgreSQLExtensions::PostGIS.VERSION[:lib] < '2.0' ||
      opts[:force_constraints]
    )
      table_constraints << PostgreSQLCheckConstraint.new(
        base,
        "ST_srid(#{base.quote_column_name(column_name)}) = (#{opts[:srid].to_i})",
        :name => "enforce_srid_#{column_name}"
      )

      table_constraints << PostgreSQLCheckConstraint.new(
        base,
        "ST_ndims(#{base.quote_column_name(column_name)}) = #{opts[:ndims].to_i}",
        :name => "enforce_dims_#{column_name}"
      )

      if opts[:geometry_type].to_s.upcase != 'GEOMETRY'
        table_constraints << PostgreSQLCheckConstraint.new(
          base,
          "geometrytype(#{base.quote_column_name(column_name)}) = '#{opts[:geometry_type].to_s.upcase}'::text OR #{base.quote_column_name(column_name)} IS NULL",
          :name => "enforce_geotype_#{column_name}"
        )
      end
    end
  end

  # We want to split up the schema and the table name for the
  # upcoming geometry_columns rows and GiST index.
  current_scoped_schema, current_table_name = if self.table_name.is_a?(Hash)
    [ self.table_name.keys.first, self.table_name.values.first ]
  elsif base.current_scoped_schema
    [ base.current_scoped_schema, self.table_name ]
  else
    schema, table_name = base.extract_schema_and_table_names(self.table_name)
    [ schema || 'public', table_name ]
  end

  if opts[:add_geometry_columns_entry] &&
    opts[:spatial_column_type].to_s != 'geography' &&
    ActiveRecord::PostgreSQLExtensions::PostGIS.VERSION[:lib] < '2.0'

    self.post_processing << sprintf(
      "DELETE FROM \"geometry_columns\" WHERE f_table_catalog = '' AND " +
      "f_table_schema = %s AND " +
      "f_table_name = %s AND " +
      "f_geometry_column = %s;",
      base.quote(current_scoped_schema.to_s),
      base.quote(current_table_name.to_s),
      base.quote(column_name.to_s)
    )

    self.post_processing << sprintf(
      "INSERT INTO \"geometry_columns\" VALUES ('', %s, %s, %s, %d, %d, %s);",
      base.quote(current_scoped_schema.to_s),
      base.quote(current_table_name.to_s),
      base.quote(column_name.to_s),
      opts[:ndims].to_i,
      opts[:srid].to_i,
      base.quote(opts[:geometry_type].to_s.upcase)
    )
  end

  if opts[:create_gist_index]
    index_name = if opts[:create_gist_index].is_a?(String)
      opts[:create_gist_index]
    else
      "#{current_table_name}_#{column_name}_gist_index"
    end

    self.post_processing << PostgreSQLIndexDefinition.new(
      base,
      index_name,
      { current_scoped_schema => current_table_name },
      column_name,
      :using => :gist
    ).to_s
  end

  self
end

#to_sql ⇒ Object Also known as: to_s

:nodoc:



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
 
# File 'lib/active_record/postgresql_extensions/tables.rb', line 188

def to_sql #:nodoc:
  if self.options[:of_type]
    if !@columns.empty?
      raise ArgumentError.new("Cannot specify columns while using the :of_type option")
    elsif options[:like]
      raise ArgumentError.new("Cannot specify both the :like and :of_type options")
    elsif options[:inherits]
      raise ArgumentError.new("Cannot specify both the :inherits and :of_type options")
    else
      options[:id] = false
    end
  end

  if options.key?(:if_not_exists)
    ActiveRecord::PostgreSQLExtensions::Features.check_feature(:create_table_if_not_exists)
  elsif options.key?(:unlogged)
    ActiveRecord::PostgreSQLExtensions::Features.check_feature(:create_table_unlogged)
  end

  unless options[:id] == false
    self.primary_key(options[:primary_key] || Base.get_primary_key(table_name))

    # ensures that the primary key column is first.
    @columns.unshift(@columns.pop)
  end

  sql = 'CREATE '
  sql << 'TEMPORARY ' if options[:temporary]
  sql << 'UNLOGGED ' if options[:unlogged]
  sql << 'TABLE '
  sql << 'IF NOT EXISTS ' if options[:if_not_exists]
  sql << "#{base.quote_table_name(table_name)}"
  sql << " OF #{base.quote_table_name(options[:of_type])}" if options[:of_type]

  ary = []
  if !options[:of_type]
    ary << @columns.collect(&:to_sql)
    ary << @like if defined?(@like) && @like
  end
  ary << table_constraints unless table_constraints.empty?

  unless ary.empty?
    sql << " (\n  "
    sql << ary * ",\n  "
    sql << "\n)"
  end

  sql << "\nINHERITS (" << Array.wrap(options[:inherits]).collect { |i| base.quote_table_name(i) }.join(', ') << ')' if options[:inherits]
  sql << "\nWITH (#{ActiveRecord::PostgreSQLExtensions::Utils.options_from_hash_or_string(options[:storage_parameters], base)})" if options[:storage_parameters].present?
  sql << "\nON COMMIT #{options[:on_commit].to_s.upcase.gsub(/_/, ' ')}" if options[:on_commit]
  sql << "\n#{options[:options]}" if options[:options]
  sql << "\nTABLESPACE #{base.quote_tablespace(options[:tablespace])}" if options[:tablespace]
  "#{sql};"
end

#unique_constraint(columns, options = {}) ⇒ Object

Add a UNIQUE constraint to the table. See PostgreSQLUniqueConstraint for more details.



285
286
287
 
# File 'lib/active_record/postgresql_extensions/tables.rb', line 285

def unique_constraint(columns, options = {})
  table_constraints << PostgreSQLUniqueConstraint.new(@base, columns, options)
end