3 require 'bigdecimal/util'
6 module ConnectionAdapters #:nodoc:
7 # An abstract definition of a column in a table.
10 ISO_DATE = /\A(\d{4})-(\d\d)-(\d\d)\z/
11 ISO_DATETIME = /\A(\d{4})-(\d\d)-(\d\d) (\d\d):(\d\d):(\d\d)(\.\d+)?\z/
14 attr_reader :name, :default, :type, :limit, :null, :sql_type, :precision, :scale
15 attr_accessor :primary
17 # Instantiates a new column in the table.
19 # +name+ is the column's name, as in <tt><b>supplier_id</b> int(11)</tt>.
20 # +default+ is the type-casted default value, such as <tt>sales_stage varchar(20) default <b>'new'</b></tt>.
21 # +sql_type+ is only used to extract the column's length, if necessary. For example, <tt>company_name varchar(<b>60</b>)</tt>.
22 # +null+ determines if this column allows +NULL+ values.
23 def initialize(name, default, sql_type = nil, null = true)
24 @name, @sql_type, @null = name, sql_type, null
25 @limit, @precision, @scale = extract_limit(sql_type), extract_precision(sql_type), extract_scale(sql_type)
26 @type = simplified_type(sql_type)
27 @default = extract_default(default)
33 [:string, :text].include? type
37 [:float, :integer, :decimal].include? type
40 # Returns the Ruby class that corresponds to the abstract data type.
43 when :integer then Fixnum
44 when :float then Float
45 when :decimal then BigDecimal
46 when :datetime then Time
48 when :timestamp then Time
50 when :text, :string then String
51 when :binary then String
52 when :boolean then Object
56 # Casts value (which is a String) to an appropriate instance.
58 return nil if value.nil?
60 when :string then value
62 when :integer then value.to_i rescue value ? 1 : 0
63 when :float then value.to_f
64 when :decimal then self.class.value_to_decimal(value)
65 when :datetime then self.class.string_to_time(value)
66 when :timestamp then self.class.string_to_time(value)
67 when :time then self.class.string_to_dummy_time(value)
68 when :date then self.class.string_to_date(value)
69 when :binary then self.class.binary_to_string(value)
70 when :boolean then self.class.value_to_boolean(value)
75 def type_cast_code(var_name)
79 when :integer then "(#{var_name}.to_i rescue #{var_name} ? 1 : 0)"
80 when :float then "#{var_name}.to_f"
81 when :decimal then "#{self.class.name}.value_to_decimal(#{var_name})"
82 when :datetime then "#{self.class.name}.string_to_time(#{var_name})"
83 when :timestamp then "#{self.class.name}.string_to_time(#{var_name})"
84 when :time then "#{self.class.name}.string_to_dummy_time(#{var_name})"
85 when :date then "#{self.class.name}.string_to_date(#{var_name})"
86 when :binary then "#{self.class.name}.binary_to_string(#{var_name})"
87 when :boolean then "#{self.class.name}.value_to_boolean(#{var_name})"
92 # Returns the human name of the column name.
95 # Column.new('sales_stage', ...).human_name #=> 'Sales stage'
97 Base.human_attribute_name(@name)
100 def extract_default(default)
105 # Used to convert from Strings to BLOBs
106 def string_to_binary(value)
110 # Used to convert from BLOBs to Strings
111 def binary_to_string(value)
115 def string_to_date(string)
116 return string unless string.is_a?(String)
117 return nil if string.empty?
119 fast_string_to_date(string) || fallback_string_to_date(string)
122 def string_to_time(string)
123 return string unless string.is_a?(String)
124 return nil if string.empty?
126 fast_string_to_time(string) || fallback_string_to_time(string)
129 def string_to_dummy_time(string)
130 return string unless string.is_a?(String)
131 return nil if string.empty?
133 string_to_time "2000-01-01 #{string}"
136 # convert something to a boolean
137 def value_to_boolean(value)
138 if value == true || value == false
141 %w(true t 1).include?(value.to_s.downcase)
145 # convert something to a BigDecimal
146 def value_to_decimal(value)
147 if value.is_a?(BigDecimal)
149 elsif value.respond_to?(:to_d)
157 # '0.123456' -> 123456
158 # '1.123456' -> 123456
159 def microseconds(time)
160 ((time[:sec_fraction].to_f % 1) * 1_000_000).to_i
163 def new_date(year, mon, mday)
165 Date.new(year, mon, mday) rescue nil
169 def new_time(year, mon, mday, hour, min, sec, microsec)
170 # Treat 0000-00-00 00:00:00 as nil.
171 return nil if year.nil? || year == 0
173 Time.send(Base.default_timezone, year, mon, mday, hour, min, sec, microsec)
174 # Over/underflow to DateTime
175 rescue ArgumentError, TypeError
176 zone_offset = Base.default_timezone == :local ? DateTime.local_offset : 0
177 # Append zero calendar reform start to account for dates skipped by calendar reform
178 DateTime.new(year, mon, mday, hour, min, sec, zone_offset, 0) rescue nil
181 def fast_string_to_date(string)
182 if string =~ Format::ISO_DATE
183 new_date $1.to_i, $2.to_i, $3.to_i
187 # Doesn't handle time zones.
188 def fast_string_to_time(string)
189 if string =~ Format::ISO_DATETIME
190 microsec = ($7.to_f * 1_000_000).to_i
191 new_time $1.to_i, $2.to_i, $3.to_i, $4.to_i, $5.to_i, $6.to_i, microsec
195 def fallback_string_to_date(string)
196 new_date *ParseDate.parsedate(string)[0..2]
199 def fallback_string_to_time(string)
200 time_hash = Date._parse(string)
201 time_hash[:sec_fraction] = microseconds(time_hash)
203 new_time *time_hash.values_at(:year, :mon, :mday, :hour, :min, :sec, :sec_fraction)
208 def extract_limit(sql_type)
209 $1.to_i if sql_type =~ /\((.*)\)/
212 def extract_precision(sql_type)
213 $2.to_i if sql_type =~ /^(numeric|decimal|number)\((\d+)(,\d+)?\)/i
216 def extract_scale(sql_type)
218 when /^(numeric|decimal|number)\((\d+)\)/i then 0
219 when /^(numeric|decimal|number)\((\d+)(,(\d+))\)/i then $4.to_i
223 def simplified_type(field_type)
229 when /decimal|numeric|number/i
230 extract_scale(field_type) == 0 ? :integer : :decimal
239 when /clob/i, /text/i
241 when /blob/i, /binary/i
243 when /char/i, /string/i
251 class IndexDefinition < Struct.new(:table, :name, :unique, :columns) #:nodoc:
254 class ColumnDefinition < Struct.new(:base, :name, :type, :limit, :precision, :scale, :default, :null) #:nodoc:
257 base.type_to_sql(type.to_sym, limit, precision, scale) rescue type
261 column_sql = "#{base.quote_column_name(name)} #{sql_type}"
262 add_column_options!(column_sql, :null => null, :default => default) unless type.to_sym == :primary_key
269 def add_column_options!(sql, options)
270 base.add_column_options!(sql, options.merge(:column => self))
274 # Represents a SQL table in an abstract way.
275 # Columns are stored as a ColumnDefinition in the #columns attribute.
276 class TableDefinition
277 attr_accessor :columns
284 # Appends a primary key definition to the table definition.
285 # Can be called multiple times, but this is probably not a good idea.
286 def primary_key(name)
287 column(name, :primary_key)
290 # Returns a ColumnDefinition for the column with name +name+.
292 @columns.find {|column| column.name.to_s == name.to_s}
295 # Instantiates a new column for the table.
296 # The +type+ parameter must be one of the following values:
297 # <tt>:primary_key</tt>, <tt>:string</tt>, <tt>:text</tt>,
298 # <tt>:integer</tt>, <tt>:float</tt>, <tt>:decimal</tt>,
299 # <tt>:datetime</tt>, <tt>:timestamp</tt>, <tt>:time</tt>,
300 # <tt>:date</tt>, <tt>:binary</tt>, <tt>:boolean</tt>.
302 # Available options are (none of these exists by default):
303 # * <tt>:limit</tt> -
304 # Requests a maximum column length (<tt>:string</tt>, <tt>:text</tt>,
305 # <tt>:binary</tt> or <tt>:integer</tt> columns only)
306 # * <tt>:default</tt> -
307 # The column's default value. Use nil for NULL.
309 # Allows or disallows +NULL+ values in the column. This option could
310 # have been named <tt>:null_allowed</tt>.
311 # * <tt>:precision</tt> -
312 # Specifies the precision for a <tt>:decimal</tt> column.
313 # * <tt>:scale</tt> -
314 # Specifies the scale for a <tt>:decimal</tt> column.
316 # Please be aware of different RDBMS implementations behavior with
317 # <tt>:decimal</tt> columns:
318 # * The SQL standard says the default scale should be 0, <tt>:scale</tt> <=
319 # <tt>:precision</tt>, and makes no comments about the requirements of
320 # <tt>:precision</tt>.
321 # * MySQL: <tt>:precision</tt> [1..63], <tt>:scale</tt> [0..30].
323 # * PostgreSQL: <tt>:precision</tt> [1..infinity],
324 # <tt>:scale</tt> [0..infinity]. No default.
325 # * SQLite2: Any <tt>:precision</tt> and <tt>:scale</tt> may be used.
326 # Internal storage as strings. No default.
327 # * SQLite3: No restrictions on <tt>:precision</tt> and <tt>:scale</tt>,
328 # but the maximum supported <tt>:precision</tt> is 16. No default.
329 # * Oracle: <tt>:precision</tt> [1..38], <tt>:scale</tt> [-84..127].
331 # * DB2: <tt>:precision</tt> [1..63], <tt>:scale</tt> [0..62].
333 # * Firebird: <tt>:precision</tt> [1..18], <tt>:scale</tt> [0..18].
334 # Default (9,0). Internal types NUMERIC and DECIMAL have different
335 # storage rules, decimal being better.
336 # * FrontBase?: <tt>:precision</tt> [1..38], <tt>:scale</tt> [0..38].
337 # Default (38,0). WARNING Max <tt>:precision</tt>/<tt>:scale</tt> for
338 # NUMERIC is 19, and DECIMAL is 38.
339 # * SqlServer?: <tt>:precision</tt> [1..38], <tt>:scale</tt> [0..38].
341 # * Sybase: <tt>:precision</tt> [1..38], <tt>:scale</tt> [0..38].
343 # * OpenBase?: Documentation unclear. Claims storage in <tt>double</tt>.
345 # This method returns <tt>self</tt>.
348 # # Assuming td is an instance of TableDefinition
349 # td.column(:granted, :boolean)
350 # #=> granted BOOLEAN
352 # td.column(:picture, :binary, :limit => 2.megabytes)
353 # #=> picture BLOB(2097152)
355 # td.column(:sales_stage, :string, :limit => 20, :default => 'new', :null => false)
356 # #=> sales_stage VARCHAR(20) DEFAULT 'new' NOT NULL
358 # def.column(:bill_gates_money, :decimal, :precision => 15, :scale => 2)
359 # #=> bill_gates_money DECIMAL(15,2)
361 # def.column(:sensor_reading, :decimal, :precision => 30, :scale => 20)
362 # #=> sensor_reading DECIMAL(30,20)
364 # # While <tt>:scale</tt> defaults to zero on most databases, it
365 # # probably wouldn't hurt to include it.
366 # def.column(:huge_integer, :decimal, :precision => 30)
367 # #=> huge_integer DECIMAL(30)
369 # == Short-hand examples
371 # Instead of calling column directly, you can also work with the short-hand definitions for the default types.
372 # They use the type as the method name instead of as a parameter and allow for multiple columns to be defined
373 # in a single statement.
375 # What can be written like this with the regular calls to column:
377 # create_table "products", :force => true do |t|
378 # t.column "shop_id", :integer
379 # t.column "creator_id", :integer
380 # t.column "name", :string, :default => "Untitled"
381 # t.column "value", :string, :default => "Untitled"
382 # t.column "created_at", :datetime
383 # t.column "updated_at", :datetime
386 # Can also be written as follows using the short-hand:
388 # create_table :products do |t|
389 # t.integer :shop_id, :creator_id
390 # t.string :name, :value, :default => "Untitled"
394 # There's a short-hand method for each of the type values declared at the top. And then there's
395 # TableDefinition#timestamps that'll add created_at and updated_at as datetimes.
397 # TableDefinition#references will add an appropriately-named _id column, plus a corresponding _type
398 # column if the :polymorphic option is supplied. If :polymorphic is a hash of options, these will be
399 # used when creating the _type column. So what can be written like this:
401 # create_table :taggings do |t|
402 # t.integer :tag_id, :tagger_id, :taggable_id
403 # t.string :tagger_type
404 # t.string :taggable_type, :default => 'Photo'
407 # Can also be written as follows using references:
409 # create_table :taggings do |t|
411 # t.references :tagger, :polymorphic => true
412 # t.references :taggable, :polymorphic => { :default => 'Photo' }
414 def column(name, type, options = {})
415 column = self[name] || ColumnDefinition.new(@base, name, type)
416 column.limit = options[:limit] || native[type.to_sym][:limit] if options[:limit] or native[type.to_sym]
417 column.precision = options[:precision]
418 column.scale = options[:scale]
419 column.default = options[:default]
420 column.null = options[:null]
421 @columns << column unless @columns.include? column
425 %w( string text integer float decimal datetime timestamp time date binary boolean ).each do |column_type|
427 def #{column_type}(*args)
428 options = args.extract_options!
431 column_names.each { |name| column(name, '#{column_type}', options) }
437 column(:created_at, :datetime)
438 column(:updated_at, :datetime)
441 def references(*args)
442 options = args.extract_options!
443 polymorphic = options.delete(:polymorphic)
445 column("#{col}_id", :integer, options)
446 unless polymorphic.nil?
447 column("#{col}_type", :string, polymorphic.is_a?(Hash) ? polymorphic : {})
451 alias :belongs_to :references
453 # Returns a String whose contents are the column definitions
454 # concatenated together. This string can then be prepended and appended to
455 # to generate the final SQL to create the table.
462 @base.native_database_types