Module: SexyPgConstraints::Constraints

Defined in:
lib/sexy_pg_constraints/constraints.rb

Class Method Summary collapse

Class Method Details

.alphanumeric(column, options) ⇒ Object

Allow only alphanumeric values.

Example:

constrain :books, :author, :alphanumeric => true



114
115
116
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 114

def alphanumeric(column, options)
  %{check ((("#{column}")::text ~* '^[a-z0-9]+$'::text))}
end

.blacklist(column, options) ⇒ Object

Prohibit listed values.

Example:

constrain :books, :isbn, :blacklist => %w(invalid_isbn1 invalid_isbn2)



21
22
23
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 21

def blacklist(column, options)
  %{check ("#{column}" not in (#{ options.collect{|v| "'#{v}'"}.join(',') }))}
end

.constrain_like(column, operator, string) ⇒ Object 



263
264
265
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 263

def constrain_like(column, operator, string)
  %{check (("#{column}")::text #{operator} '#{string.gsub(/([^'])'([^'])/, '\1\'\'\2')}')}
end

.email(column, options) ⇒ Object

Allow only valid email format.

Example:

constrain :books, :author, :email => true



104
105
106
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 104

def email(column, options)
  %{check ((("#{column}")::text ~ E'^([-a-z0-9]+)@([-a-z0-9]+[.]+[a-z]{2,4})$'::text))}
end

.even(column, options) ⇒ Object

Allow only even values.

Example:

constrain :books, :quantity, :even => true



194
195
196
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 194

def even(column, options)
  %{check (mod("#{column}", 2) = 0)}
end

.exact_length(column, options) ⇒ Object

Allow only text/strings of the exact length specified, no more, no less.

Example:

constrain :books, :hash, :exact_length => 32



229
230
231
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 229

def exact_length(column, options)
  %{check ( length(trim(both from "#{column}")) = #{options} )}
end

.format(column, options) ⇒ Object

Allow only values that match the regular expression.

Example:

constrain :orders, :visa, :format => /^([4]{1})([0-9]{12,15})$/



239
240
241
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 239

def format(column, options)
  %{check ((("#{column}")::text #{options.casefold? ? '~*' : '~'}  E'#{options.source}'::text ))}
end

.greater_than(column, options) ⇒ Object

Allow only values greater than the provided limit.

Example:

constrain :books, :quantity, :greater_than => 12



164
165
166
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 164

def greater_than(column, options)
  %{check ("#{column}" > #{options})}
end

.greater_than_or_equal_to(column, options) ⇒ Object

Allow only values greater than or equal to the provided limit.

Example:

constrain :books, :quantity, :greater_than_or_equal_to => 12



174
175
176
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 174

def greater_than_or_equal_to(column, options)
  %{check ("#{column}" >= #{options})}
end

.length_within(column, options) ⇒ Object

Check the length of strings/text to be within the range.

Example:

constrain :books, :author, :length_within => 4..50



94
95
96
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 94

def length_within(column, options)
  within(%{length("#{column}")}, options)
end

.less_than(column, options) ⇒ Object

Allow only values less than the provided limit.

Example:

constrain :books, :quantity, :greater_than => 12



144
145
146
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 144

def less_than(column, options)
  %{check ("#{column}" < #{options})}
end

.less_than_or_equal_to(column, options) ⇒ Object

Allow only values less than or equal to the provided limit.

Example:

constrain :books, :quantity, :greater_than => 12



154
155
156
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 154

def less_than_or_equal_to(column, options)
  %{check ("#{column}" <= #{options})}
end

.like(column, options) ⇒ Object

Exclude values not matching the like pattern provided

Example:

constrain :orders, :visa, :like => '%FOO%'



249
250
251
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 249

def like(column, options)
  constrain_like(column, 'LIKE', options)
end

.lowercase(column, options) ⇒ Object

Allow only lower case values.

Example:

constrain :books, :author, :lowercase => true



124
125
126
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 124

def lowercase(column, options)
  %{check ("#{column}" = lower("#{column}"))}
end

.not_like(column, options) ⇒ Object

Exclude values matching the like pattern provided

Example:

constrain :orders, :visa, :not_like => '%FOO%'



259
260
261
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 259

def not_like(column, options)
  constrain_like(column, 'NOT LIKE', options)
end

.not_only(column, options) ⇒ Object

The value must have characters other than those listed in the option string.

Example:

constrain :books, :title, :not_only => 'abcd'



41
42
43
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 41

def not_only(column, options)
  %{check ( length(btrim("#{column}", E'#{options}')) > 0 )}
end

.odd(column, options) ⇒ Object

Allow only odd values.

Example:

constrain :books, :quantity, :odd => true



184
185
186
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 184

def odd(column, options)
  %{check (mod("#{column}", 2) != 0)}
end

.positive(column, options) ⇒ Object

Allow only positive values.

Example:

constrain :books, :quantity, :positive => true



134
135
136
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 134

def positive(column, options)
  greater_than_or_equal_to(column, 0)
end

.present(column, options) ⇒ Object

The value must have at least 1 non-space character.

Example:

constrain :books, :title, :present => true



31
32
33
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 31

def present(column, options)
  %{check ( length(btrim("#{column}")) > 0 )}
end

.reference(column, options) ⇒ Object

Add foreign key constraint.

Example:

constrain :books, :author_id, :reference => {:authors => :id, :on_delete => :cascade}



274
275
276
277
278
279
280
281
282
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 274

def reference(column, options)
  on_delete = options.delete(:on_delete)
  fk_table = options.keys.first
  fk_column = options[fk_table]

  on_delete = "on delete #{on_delete}" if on_delete

  %{foreign key ("#{column}") references #{fk_table} (#{fk_column}) #{on_delete}}
end

.stripped(column, options) ⇒ Object

The value must not have leading or trailing spaces.

You can pass a string as an option to indicate what characters are stripped.

Example:

constrain :books, :title, :stripped => true
constrain :books, :title, :stripped => "abc"



54
55
56
57
58
59
60
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 54

def stripped(column, options)
  if options == true
    %{check (length("#{column}") = length(btrim("#{column}")))}
  else
    %{check (length("#{column}") = length(btrim("#{column}", E'#{options}')))}
  end
end

.unique(column, options) ⇒ Object

Make sure every entry in the column is unique.

Example:

constrain :books, :isbn, :unique => true



204
205
206
207
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 204

def unique(column, options)
  columns = Array(column).map {|c| %{"#{c}"} }.join(', ')
  "unique(#{columns})"
end

.whitelist(column, options) ⇒ Object

Only allow listed values.

Example:

constrain :books, :variation, :whitelist => %w(hardcover softcover)



11
12
13
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 11

def whitelist(column, options)
  %{check ("#{column}" in (#{ options.collect{|v| "'#{v}'"}.join(',')  }))}
end

.within(column, options) ⇒ Object

The numeric value must be within given range.

Example:

constrain :books, :year, :within => 1980..2008
constrain :books, :year, :within => 1980...2009
constrain :books, :year, :within => {:range => 1979..2008, :exclude_beginning => true}
constrain :books, :year, :within => {:range => 1979..2009, :exclude_beginning => true, :exclude_end => true}

(the four lines above do the same thing)



72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 72

def within(column, options)
  column_ref = column.to_s.include?('"') ? column : %{"#{column}"}
  if options.respond_to?(:to_hash)
    options = options.to_hash
    options.assert_valid_keys(:range, :exclude_end, :exclude_beginning)
    range = options.fetch(:range)
    exclude_end = options.has_key?(:exclude_end) ? options.fetch(:exclude_end) : range.exclude_end?
    exclude_beginning = options.has_key?(:exclude_beginning) ? options.fetch(:exclude_beginning) : false
  else
    range = options
    exclude_end = range.exclude_end?
    exclude_beginning = false
  end
  "check (#{column_ref} >#{'=' unless exclude_beginning} #{range.begin} and #{column_ref} <#{'=' unless exclude_end} #{range.end})"
end

.xor(column, options) ⇒ Object

Allow only one of the values in the given columns to be true. Only reasonable with more than one column. See Enterprise Rails, Chapter 10 for details.

Example:

constrain :books, [], :xor => true



217
218
219
220
221
 
# File 'lib/sexy_pg_constraints/constraints.rb', line 217

def xor(column, options)
  addition = Array(column).map {|c| %{("#{c}" is not null)::integer} }.join(' + ')

  "check (#{addition} = 1)"
end