1
require File.join(File.dirname(File.expand_path(__FILE__)), "spec_helper")
3
describe "constraint_validations extension" do
5
m = /\AINSERT INTO sequel_constraint_validations \((.*)\) VALUES \((.*)\)\z/.match(s)
6
Hash[*m[1].split(', ').map{|v| v.to_sym}.zip(m[2].split(', ').map{|v| parse_insert_value(v)}).reject{|k, v| v.nil?}.flatten]
9
def parse_insert_value(s)
16
raise Sequel::Error, "unhandled insert value: #{s.inspect}"
22
@db.extend(Module.new{attr_writer :schema; def schema(table, *) execute("parse schema for #{table}"); @schema; end})
23
@db.extension(:constraint_validations)
26
it "should allow creating the sequel_constraint_validations table" do
27
@db.create_constraint_validations_table
28
@db.sqls.should == ["CREATE TABLE sequel_constraint_validations (table varchar(255) NOT NULL, constraint_name varchar(255), validation_type varchar(255) NOT NULL, column varchar(255) NOT NULL, argument varchar(255), message varchar(255), allow_nil boolean)"]
31
it "should allow creating the sequel_constraint_validations table with a non-default table name" do
32
@db.constraint_validations_table = :foo
33
@db.create_constraint_validations_table
34
@db.sqls.should == ["CREATE TABLE foo (table varchar(255) NOT NULL, constraint_name varchar(255), validation_type varchar(255) NOT NULL, column varchar(255) NOT NULL, argument varchar(255), message varchar(255), allow_nil boolean)"]
37
it "should allow dropping the sequel_constraint_validations table" do
38
@db.drop_constraint_validations_table
39
@db.sqls.should == ["DROP TABLE sequel_constraint_validations"]
42
it "should allow dropping the sequel_constraint_validations table with a non-default table name" do
43
@db.constraint_validations_table = :foo
44
@db.drop_constraint_validations_table
45
@db.sqls.should == ["DROP TABLE foo"]
48
it "should allow dropping validations for a given table" do
49
@db.drop_constraint_validations_for(:table=>:foo)
50
@db.sqls.should == ["DELETE FROM sequel_constraint_validations WHERE (table = 'foo')"]
53
it "should allow dropping validations for a given table and column" do
54
@db.drop_constraint_validations_for(:table=>:foo, :column=>:bar)
55
@db.sqls.should == ["DELETE FROM sequel_constraint_validations WHERE ((table = 'foo') AND (column = 'bar'))"]
58
it "should allow dropping validations for a given table and constraint" do
59
@db.drop_constraint_validations_for(:table=>:foo, :constraint=>:bar)
60
@db.sqls.should == ["DELETE FROM sequel_constraint_validations WHERE ((table = 'foo') AND (constraint_name = 'bar'))"]
63
it "should allow dropping validations for a non-default constraint_validations table" do
64
@db.constraint_validations_table = :cv
65
@db.drop_constraint_validations_for(:table=>:foo)
66
@db.sqls.should == ["DELETE FROM cv WHERE (table = 'foo')"]
69
it "should raise an error without deleting if attempting to drop validations without table, column, or constraint" do
70
proc{@db.drop_constraint_validations_for({})}.should raise_error(Sequel::Error)
74
it "should allow adding constraint validations via create_table validate" do
75
@db.create_table(:foo){String :name; validate{presence :name}}
77
parse_insert(sqls.slice!(1)).should == {:validation_type=>"presence", :column=>"name", :table=>"foo"}
78
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name varchar(255), CHECK ((name IS NOT NULL) AND (trim(name) != '')))"]
81
it "should allow adding constraint validations via alter_table validate" do
82
@db.schema = [[:name, {:type=>:string}]]
83
@db.alter_table(:foo){validate{presence :name}}
85
parse_insert(sqls.slice!(2)).should == {:validation_type=>"presence", :column=>"name", :table=>"foo"}
86
sqls.should == ["parse schema for foo", "BEGIN", "COMMIT", "ALTER TABLE foo ADD CHECK ((name IS NOT NULL) AND (trim(name) != ''))"]
89
it "should handle :message option when adding validations" do
90
@db.create_table(:foo){String :name; validate{presence :name, :message=>'not there'}}
92
parse_insert(sqls.slice!(1)).should == {:validation_type=>"presence", :column=>"name", :table=>"foo", :message=>'not there'}
93
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name varchar(255), CHECK ((name IS NOT NULL) AND (trim(name) != '')))"]
96
it "should handle :allow_nil option when adding validations" do
97
@db.create_table(:foo){String :name; validate{presence :name, :allow_nil=>true}}
99
parse_insert(sqls.slice!(1)).should == {:validation_type=>"presence", :column=>"name", :table=>"foo", :allow_nil=>'t'}
100
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name varchar(255), CHECK ((name IS NULL) OR (trim(name) != '')))"]
103
it "should handle :name option when adding validations" do
104
@db.create_table(:foo){String :name; validate{presence :name, :name=>'cons'}}
106
parse_insert(sqls.slice!(1)).should == {:validation_type=>"presence", :column=>"name", :table=>"foo", :constraint_name=>'cons'}
107
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name varchar(255), CONSTRAINT cons CHECK ((name IS NOT NULL) AND (trim(name) != '')))"]
110
it "should handle multiple columns when adding validations" do
111
@db.create_table(:foo){String :name; String :bar; validate{presence [:name, :bar]}}
113
parse_insert(sqls.slice!(1)).should == {:validation_type=>"presence", :column=>"name", :table=>"foo"}
114
parse_insert(sqls.slice!(1)).should == {:validation_type=>"presence", :column=>"bar", :table=>"foo"}
115
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name varchar(255), bar varchar(255), CHECK ((name IS NOT NULL) AND (bar IS NOT NULL) AND (trim(name) != '') AND (trim(bar) != '')))"]
118
it "should handle presence validation on non-String columns" do
119
@db.create_table(:foo){Integer :name; validate{presence :name}}
121
parse_insert(sqls.slice!(1)).should == {:validation_type=>"presence", :column=>"name", :table=>"foo"}
122
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name integer, CHECK (name IS NOT NULL))"]
124
@db.schema = [[:name, {:type=>:integer}]]
125
@db.alter_table(:foo){validate{presence :name}}
127
parse_insert(sqls.slice!(2)).should == {:validation_type=>"presence", :column=>"name", :table=>"foo"}
128
sqls.should == ["parse schema for foo", "BEGIN", "COMMIT", "ALTER TABLE foo ADD CHECK (name IS NOT NULL)"]
131
it "should handle presence validation on Oracle with IS NOT NULL instead of != ''" do
132
@db = Sequel.mock(:host=>'oracle')
133
@db.extension(:constraint_validations)
134
@db.create_table(:foo){String :name; validate{presence :name}}
136
parse_insert(sqls.slice!(1)).should == {:validation_type=>"presence", :column=>"name", :table=>"foo"}
137
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name varchar(255), CHECK ((name IS NOT NULL) AND (trim(name) IS NOT NULL)))"]
140
it "should assume column is not a String if it can't determine the type" do
141
@db.create_table(:foo){Integer :name; validate{presence :bar}}
143
parse_insert(sqls.slice!(1)).should == {:validation_type=>"presence", :column=>"bar", :table=>"foo"}
144
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name integer, CHECK (bar IS NOT NULL))"]
146
@db.schema = [[:name, {:type=>:integer}]]
147
@db.alter_table(:foo){validate{presence :bar}}
149
parse_insert(sqls.slice!(2)).should == {:validation_type=>"presence", :column=>"bar", :table=>"foo"}
150
sqls.should == ["parse schema for foo", "BEGIN", "COMMIT", "ALTER TABLE foo ADD CHECK (bar IS NOT NULL)"]
153
it "should handle presence validation on non-String columns with :allow_nil option" do
154
@db.create_table(:foo){Integer :name; validate{presence :name, :allow_nil=>true}}
156
parse_insert(sqls.slice!(1)).should == {:validation_type=>"presence", :column=>"name", :table=>"foo", :allow_nil=>'t'}
157
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name integer)"]
160
it "should support :exact_length constraint validation" do
161
@db.create_table(:foo){String :name; validate{exact_length 5, :name}}
163
parse_insert(sqls.slice!(1)).should == {:validation_type=>"exact_length", :column=>"name", :table=>"foo", :argument=>'5'}
164
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name varchar(255), CHECK ((name IS NOT NULL) AND (char_length(name) = 5)))"]
167
it "should support :min_length constraint validation" do
168
@db.create_table(:foo){String :name; validate{min_length 5, :name}}
170
parse_insert(sqls.slice!(1)).should == {:validation_type=>"min_length", :column=>"name", :table=>"foo", :argument=>'5'}
171
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name varchar(255), CHECK ((name IS NOT NULL) AND (char_length(name) >= 5)))"]
174
it "should support :max_length constraint validation" do
175
@db.create_table(:foo){String :name; validate{max_length 5, :name}}
177
parse_insert(sqls.slice!(1)).should == {:validation_type=>"max_length", :column=>"name", :table=>"foo", :argument=>'5'}
178
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name varchar(255), CHECK ((name IS NOT NULL) AND (char_length(name) <= 5)))"]
181
it "should support :length_range constraint validation" do
182
@db.create_table(:foo){String :name; validate{length_range 3..5, :name}}
184
parse_insert(sqls.slice!(1)).should == {:validation_type=>"length_range", :column=>"name", :table=>"foo", :argument=>'3..5'}
185
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name varchar(255), CHECK ((name IS NOT NULL) AND (char_length(name) >= 3) AND (char_length(name) <= 5)))"]
187
@db.create_table(:foo){String :name; validate{length_range 3...5, :name}}
189
parse_insert(sqls.slice!(1)).should == {:validation_type=>"length_range", :column=>"name", :table=>"foo", :argument=>'3...5'}
190
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name varchar(255), CHECK ((name IS NOT NULL) AND (char_length(name) >= 3) AND (char_length(name) < 5)))"]
193
it "should support :format constraint validation" do
194
@db = Sequel.mock(:host=>'postgres')
195
@db.extension(:constraint_validations)
196
@db.create_table(:foo){String :name; validate{format(/^foo.*/, :name)}}
198
parse_insert(sqls.slice!(1)).should == {:validation_type=>"format", :column=>"name", :table=>"foo", :argument=>'^foo.*'}
199
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name text, CHECK ((name IS NOT NULL) AND (name ~ '^foo.*')))"]
202
it "should support :format constraint validation with case insensitive format" do
203
@db = Sequel.mock(:host=>'postgres')
204
@db.extension(:constraint_validations)
205
@db.create_table(:foo){String :name; validate{format(/^foo.*/i, :name)}}
207
parse_insert(sqls.slice!(1)).should == {:validation_type=>"iformat", :column=>"name", :table=>"foo", :argument=>'^foo.*'}
208
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name text, CHECK ((name IS NOT NULL) AND (name ~* '^foo.*')))"]
211
it "should support :includes constraint validation with an array of strings" do
212
@db.create_table(:foo){String :name; validate{includes %w'a b c', :name}}
214
parse_insert(sqls.slice!(1)).should == {:validation_type=>"includes_str_array", :column=>"name", :table=>"foo", :argument=>'a,b,c'}
215
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name varchar(255), CHECK ((name IS NOT NULL) AND (name IN ('a', 'b', 'c'))))"]
218
it "should support :includes constraint validation with an array of integers" do
219
@db.create_table(:foo){String :name; validate{includes [1, 2, 3], :name}}
221
parse_insert(sqls.slice!(1)).should == {:validation_type=>"includes_int_array", :column=>"name", :table=>"foo", :argument=>'1,2,3'}
222
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name varchar(255), CHECK ((name IS NOT NULL) AND (name IN (1, 2, 3))))"]
225
it "should support :includes constraint validation with a inclusive range of integers" do
226
@db.create_table(:foo){String :name; validate{includes 3..5, :name}}
228
parse_insert(sqls.slice!(1)).should == {:validation_type=>"includes_int_range", :column=>"name", :table=>"foo", :argument=>'3..5'}
229
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name varchar(255), CHECK ((name IS NOT NULL) AND (name >= 3) AND (name <= 5)))"]
232
it "should support :includes constraint validation with a exclusive range of integers" do
233
@db.create_table(:foo){String :name; validate{includes 3...5, :name}}
235
parse_insert(sqls.slice!(1)).should == {:validation_type=>"includes_int_range", :column=>"name", :table=>"foo", :argument=>'3...5'}
236
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name varchar(255), CHECK ((name IS NOT NULL) AND (name >= 3) AND (name < 5)))"]
239
it "should support :like constraint validation" do
240
@db.create_table(:foo){String :name; validate{like 'foo%', :name}}
242
parse_insert(sqls.slice!(1)).should == {:validation_type=>"like", :column=>"name", :table=>"foo", :argument=>'foo%'}
243
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name varchar(255), CHECK ((name IS NOT NULL) AND (name LIKE 'foo%' ESCAPE '\\')))"]
246
it "should support :ilike constraint validation" do
247
@db.create_table(:foo){String :name; validate{ilike 'foo%', :name}}
249
parse_insert(sqls.slice!(1)).should == {:validation_type=>"ilike", :column=>"name", :table=>"foo", :argument=>'foo%'}
250
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name varchar(255), CHECK ((name IS NOT NULL) AND (UPPER(name) LIKE UPPER('foo%') ESCAPE '\\')))"]
253
it "should support :unique constraint validation" do
254
@db.create_table(:foo){String :name; validate{unique :name}}
256
parse_insert(sqls.slice!(1)).should == {:validation_type=>"unique", :column=>"name", :table=>"foo"}
257
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name varchar(255), UNIQUE (name))"]
260
it "should support :unique constraint validation with multiple columns" do
261
@db.create_table(:foo){String :name; Integer :id; validate{unique [:name, :id]}}
263
parse_insert(sqls.slice!(1)).should == {:validation_type=>"unique", :column=>"name,id", :table=>"foo"}
264
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE foo (name varchar(255), id integer, UNIQUE (name, id))"]
267
it "should support :unique constraint validation in alter_table" do
268
@db.alter_table(:foo){validate{unique :name}}
270
parse_insert(sqls.slice!(1)).should == {:validation_type=>"unique", :column=>"name", :table=>"foo"}
271
sqls.should == ["BEGIN", "COMMIT", "ALTER TABLE foo ADD UNIQUE (name)"]
274
it "should drop constraints and validations when dropping a constraint validation" do
275
@db.alter_table(:foo){String :name; validate{drop :bar}}
276
@db.sqls.should == ["DELETE FROM sequel_constraint_validations WHERE ((table, constraint_name) IN (('foo', 'bar')))", "ALTER TABLE foo DROP CONSTRAINT bar"]
279
it "should raise an error if attempting to validate inclusion with a range of non-integers" do
280
proc{@db.create_table(:foo){String :name; validate{includes 'a'..'z', :name}}}.should raise_error(Sequel::Error)
283
it "should raise an error if attempting to validate inclusion with a range of non-integers or strings" do
284
proc{@db.create_table(:foo){String :name; validate{includes [1.0, 2.0], :name}}}.should raise_error(Sequel::Error)
287
it "should raise an error if attempting to validate inclusion with a unsupported object" do
288
proc{@db.create_table(:foo){String :name; validate{includes 'a', :name}}}.should raise_error(Sequel::Error)
291
it "should raise an error if attempting to drop a constraint validation in a create_table generator" do
292
proc{@db.create_table(:foo){String :name; validate{drop :foo}}}.should raise_error(Sequel::Error)
295
it "should raise an error if attempting to drop a constraint validation without a name" do
296
proc{@db.alter_table(:foo){String :name; validate{drop nil}}}.should raise_error(Sequel::Error)
299
it "should raise an error if attempting attempting to process a constraint validation with an unsupported type" do
300
proc{@db.alter_table(:foo){String :name; validations << {:type=>:foo}}}.should raise_error(Sequel::Error)
303
it "should allow adding constraint validations for tables specified as a SQL::Identifier" do
304
@db.create_table(Sequel.identifier(:sch__foo)){String :name; validate{presence :name}}
306
parse_insert(sqls.slice!(1)).should == {:validation_type=>"presence", :column=>"name", :table=>"sch__foo"}
307
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE sch__foo (name varchar(255), CHECK ((name IS NOT NULL) AND (trim(name) != '')))"]
310
it "should allow adding constraint validations for tables specified as a SQL::QualifiedIdentifier" do
311
@db.create_table(Sequel.qualify(:sch, :foo)){String :name; validate{presence :name}}
313
parse_insert(sqls.slice!(1)).should == {:validation_type=>"presence", :column=>"name", :table=>"sch.foo"}
314
sqls.should == ["BEGIN", "COMMIT", "CREATE TABLE sch.foo (name varchar(255), CHECK ((name IS NOT NULL) AND (trim(name) != '')))"]