API tools faq
paste
Guest User

Untitled

a guest
Dec 9th, 2017
68
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 57.25 KB | None | 0 0
raw download clone embed print report
  1. diff --git a/CHANGELOG b/CHANGELOG
  2. index b735e0f..c44547d 100644
  3. --- a/CHANGELOG
  4. +++ b/CHANGELOG
  5. @@ -1,5 +1,13 @@
  6. === HEAD
  7.  
  8. +* Fix literalization of boolean values in filters on SQLite (jeremyevans)
  9. +
  10. +* Add support for filtering and excluding by multiple associations (jeremyevans)
  11. +
  12. +* Add support for inverting some SQL::Constant instances such as TRUE, FALSE, NULL, and NOTNULL (jeremyevans)
  13. +
  14. +* Add support for excluding by associations to model datasets (jeremyevans)
  15. +
  16. * The Sequel::Postgres.use_iso_date_format setting now only affects future Database objects (jeremyevans)
  17.  
  18. * Add Sequel::Postgres::PG_NAMED_TYPES hash for extensions to register type conversions for non-standard types (jeremyevans, pvh)
  19. diff --git a/lib/sequel/adapters/shared/sqlite.rb b/lib/sequel/adapters/shared/sqlite.rb
  20. index 8b1df3f..8e521e3 100644
  21. --- a/lib/sequel/adapters/shared/sqlite.rb
  22. +++ b/lib/sequel/adapters/shared/sqlite.rb
  23. @@ -339,7 +339,25 @@ module Sequel
  24. SELECT_CLAUSE_METHODS = Dataset.clause_methods(:select, %w'distinct columns from join where group having compounds order limit')
  25. COMMA_SEPARATOR = ', '.freeze
  26. CONSTANT_MAP = {:CURRENT_DATE=>"date(CURRENT_TIMESTAMP, 'localtime')".freeze, :CURRENT_TIMESTAMP=>"datetime(CURRENT_TIMESTAMP, 'localtime')".freeze, :CURRENT_TIME=>"time(CURRENT_TIMESTAMP, 'localtime')".freeze}
  27. -
  28. +
  29. + # Ugly hack. Really, SQLite uses 0 for false and 1 for true
  30. + # but then you can't differentiate between integers and booleans.
  31. + # In filters, SQL::BooleanConstants are used more, while in other places
  32. + # the ruby true/false values are used more, so use 1/0 for SQL::BooleanConstants.
  33. + # The correct fix for this would require separate literalization paths for
  34. + # filters compared to other values, but doing that just to work around shortcomings
  35. + # in SQLite doesn't appeal to me.
  36. + def boolean_constant_sql(constant)
  37. + case constant
  38. + when true
  39. + '1'
  40. + when false
  41. + '0'
  42. + else
  43. + super
  44. + end
  45. + end
  46. +
  47. # SQLite does not support pattern matching via regular expressions.
  48. # SQLite is case insensitive (depending on pragma), so use LIKE for
  49. # ILIKE.
  50. @@ -414,6 +432,22 @@ module Sequel
  51. "#{expression} AS #{literal(aliaz.to_s)}"
  52. end
  53.  
  54. + # Special case when true or false is provided directly to filter.
  55. + def filter_expr(expr)
  56. + if block_given?
  57. + super
  58. + else
  59. + case expr
  60. + when true
  61. + 1
  62. + when false
  63. + 0
  64. + else
  65. + super
  66. + end
  67. + end
  68. + end
  69. +
  70. # SQL fragment specifying a list of identifiers
  71. def identifier_list(columns)
  72. columns.map{|i| quote_identifier(i)}.join(COMMA_SEPARATOR)
  73. @@ -425,7 +459,7 @@ module Sequel
  74. v.each_byte{|x| blob << sprintf('%02x', x)}
  75. "X'#{blob}'"
  76. end
  77. -
  78. +
  79. # SQLite does not support the SQL WITH clause
  80. def select_clause_methods
  81. SELECT_CLAUSE_METHODS
  82. diff --git a/lib/sequel/model/associations.rb b/lib/sequel/model/associations.rb
  83. index 84117b6..a486189 100644
  84. --- a/lib/sequel/model/associations.rb
  85. +++ b/lib/sequel/model/associations.rb
  86. @@ -1415,18 +1415,27 @@ module Sequel
  87. # types, this is a simple transformation, but for +many_to_many+ associations this
  88. # creates a subquery to the join table.
  89. def complex_expression_sql(op, args)
  90. - if op == :'=' and args.at(1).is_a?(Sequel::Model)
  91. - l, r = args
  92. + r = args.at(1)
  93. + if (((op == :'=' || op == :'!=') and r.is_a?(Sequel::Model)) ||
  94. + (multiple = ((op == :IN || op == :'NOT IN') and !r.is_a?(Sequel::Dataset) and r.all?{|x| x.is_a?(Sequel::Model)})))
  95. + l = args.at(0)
  96. if ar = model.association_reflections[l]
  97. - unless r.is_a?(ar.associated_class)
  98. + if multiple
  99. + klass = ar.associated_class
  100. + unless r.all?{|x| x.is_a?(klass)}
  101. + raise Sequel::Error, "invalid association class for one object for association #{l.inspect} used in dataset filter for model #{model.inspect}, expected class #{klass.inspect}"
  102. + end
  103. + elsif !r.is_a?(ar.associated_class)
  104. raise Sequel::Error, "invalid association class #{r.class.inspect} for association #{l.inspect} used in dataset filter for model #{model.inspect}, expected class #{ar.associated_class.inspect}"
  105. end
  106.  
  107. - if exp = association_filter_expression(ar, r)
  108. + if exp = association_filter_expression(op, ar, r)
  109. literal(exp)
  110. else
  111. raise Sequel::Error, "invalid association type #{ar[:type].inspect} for association #{l.inspect} used in dataset filter for model #{model.inspect}"
  112. end
  113. + elsif multiple && r.empty?
  114. + super
  115. else
  116. raise Sequel::Error, "invalid association #{l.inspect} used in dataset filter for model #{model.inspect}"
  117. end
  118. @@ -1577,9 +1586,42 @@ module Sequel
  119. private
  120.  
  121. # Return an expression for filtering by the given association reflection and associated object.
  122. - def association_filter_expression(ref, obj)
  123. + def association_filter_expression(op, ref, obj)
  124. meth = :"#{ref[:type]}_association_filter_expression"
  125. - send(meth, ref, obj) if respond_to?(meth, true)
  126. + send(meth, op, ref, obj) if respond_to?(meth, true)
  127. + end
  128. +
  129. + # Handle inversion for association filters by returning an inverted expression,
  130. + # plus also handling cases where the referenced columns are NULL.
  131. + def association_filter_handle_inversion(op, exp, cols)
  132. + if op == :'!=' || op == :'NOT IN'
  133. + if exp == SQL::Constants::FALSE
  134. + ~exp
  135. + else
  136. + ~exp | Sequel::SQL::BooleanExpression.from_value_pairs(cols.zip([]), :OR)
  137. + end
  138. + else
  139. + exp
  140. + end
  141. + end
  142. +
  143. + # Return an expression for making sure that the given keys match the value of
  144. + # the given methods for either the single object given or for any of the objects
  145. + # given if +obj+ is an array.
  146. + def association_filter_key_expression(keys, meths, obj)
  147. + vals = Array(obj).reject{|o| !meths.all?{|m| o.send(m)}}
  148. + return SQL::Constants::FALSE if vals.empty?
  149. + vals = if obj.is_a?(Array)
  150. + if keys.length == 1
  151. + meth = meths.first
  152. + {keys.first=>vals.map{|o| o.send(meth)}}
  153. + else
  154. + {keys=>vals.map{|o| meths.map{|m| o.send(m)}}}
  155. + end
  156. + else
  157. + keys.zip(meths.map{|k| obj.send(k)})
  158. + end
  159. + SQL::BooleanExpression.from_value_pairs(vals)
  160. end
  161.  
  162. # Make sure the association is valid for this model, and return the related AssociationReflection.
  163. @@ -1685,20 +1727,27 @@ module Sequel
  164. end
  165.  
  166. # Return a subquery expression for filering by a many_to_many association
  167. - def many_to_many_association_filter_expression(ref, obj)
  168. - lpks, lks, rks = ref.values_at(:left_primary_keys, :left_keys, :right_keys)
  169. - lpks = lpks.first if lpks.length == 1
  170. - SQL::BooleanExpression.from_value_pairs(lpks=>model.db[ref[:join_table]].select(*lks).where(rks.zip(ref.right_primary_keys.map{|k| obj.send(k)})))
  171. + def many_to_many_association_filter_expression(op, ref, obj)
  172. + lpks, lks, rks = ref.values_at(:left_primary_keys, :left_keys, :right_keys)
  173. + lpks = lpks.first if lpks.length == 1
  174. + exp = association_filter_key_expression(rks, ref.right_primary_keys, obj)
  175. + if exp == SQL::Constants::FALSE
  176. + association_filter_handle_inversion(op, exp, Array(lpks))
  177. + else
  178. + association_filter_handle_inversion(op, SQL::BooleanExpression.from_value_pairs(lpks=>model.db[ref[:join_table]].select(*lks).where(exp).exclude(SQL::BooleanExpression.from_value_pairs(lks.zip([]), :OR))), Array(lpks))
  179. + end
  180. end
  181.  
  182. # Return a simple equality expression for filering by a many_to_one association
  183. - def many_to_one_association_filter_expression(ref, obj)
  184. - SQL::BooleanExpression.from_value_pairs(ref[:keys].zip(ref.primary_keys.map{|k| obj.send(k)}))
  185. + def many_to_one_association_filter_expression(op, ref, obj)
  186. + keys = ref[:keys]
  187. + association_filter_handle_inversion(op, association_filter_key_expression(keys, ref.primary_keys, obj), keys)
  188. end
  189.  
  190. # Return a simple equality expression for filering by a one_to_* association
  191. - def one_to_many_association_filter_expression(ref, obj)
  192. - SQL::BooleanExpression.from_value_pairs(ref[:primary_keys].zip(ref[:keys].map{|k| obj.send(k)}))
  193. + def one_to_many_association_filter_expression(op, ref, obj)
  194. + keys = ref[:primary_keys]
  195. + association_filter_handle_inversion(op, association_filter_key_expression(keys, ref[:keys], obj), keys)
  196. end
  197. alias one_to_one_association_filter_expression one_to_many_association_filter_expression
  198.  
  199. diff --git a/lib/sequel/plugins/many_through_many.rb b/lib/sequel/plugins/many_through_many.rb
  200. index b495051..2578d4a 100644
  201. --- a/lib/sequel/plugins/many_through_many.rb
  202. +++ b/lib/sequel/plugins/many_through_many.rb
  203. @@ -224,7 +224,7 @@ module Sequel
  204. private
  205.  
  206. # Use a subquery to filter rows to those related to the given associated object
  207. - def many_through_many_association_filter_expression(ref, obj)
  208. + def many_through_many_association_filter_expression(op, ref, obj)
  209. lpks = ref[:left_primary_keys]
  210. lpks = lpks.first if lpks.length == 1
  211. edges = ref.edges
  212. @@ -238,8 +238,13 @@ module Sequel
  213. last_join = ds.opts[:join].last
  214. last_join.table_alias || last_join.table
  215. end
  216. - ds = ds.where(Array(ref[:final_edge][:left]).map{|x| ::Sequel::SQL::QualifiedIdentifier.new(last_alias, x)}.zip(ref.right_primary_keys.map{|k| obj.send(k)}))
  217. - SQL::BooleanExpression.from_value_pairs(lpks=>ds)
  218. + exp = association_filter_key_expression(Array(ref[:final_edge][:left]).map{|x| ::Sequel::SQL::QualifiedIdentifier.new(last_alias, x)}, ref.right_primary_keys, obj)
  219. + if exp == SQL::Constants::FALSE
  220. + association_filter_handle_inversion(op, exp, Array(lpks))
  221. + else
  222. + ds = ds.where(exp).exclude(SQL::BooleanExpression.from_value_pairs(ds.opts[:select].zip([]), :OR))
  223. + association_filter_handle_inversion(op, SQL::BooleanExpression.from_value_pairs(lpks=>ds), Array(lpks))
  224. + end
  225. end
  226. end
  227. end
  228. diff --git a/lib/sequel/sql.rb b/lib/sequel/sql.rb
  229. index 80a7aaa..36f56e5 100644
  230. --- a/lib/sequel/sql.rb
  231. +++ b/lib/sequel/sql.rb
  232. @@ -595,6 +595,8 @@ module Sequel
  233. end
  234. when StringExpression, NumericExpression
  235. raise(Sequel::Error, "cannot invert #{ce.inspect}")
  236. + when Constant
  237. + CONSTANT_INVERSIONS[ce] || raise(Sequel::Error, "cannot invert #{ce.inspect}")
  238. else
  239. BooleanExpression.new(:NOT, ce)
  240. end
  241. @@ -706,6 +708,12 @@ module Sequel
  242. NOTNULL = NegativeBooleanConstant.new(nil)
  243. end
  244.  
  245. + class ComplexExpression
  246. + # A hash of the opposite for each constant, used for inverting constants.
  247. + CONSTANT_INVERSIONS = {Constants::TRUE=>Constants::FALSE, Constants::FALSE=>Constants::TRUE,
  248. + Constants::NULL=>Constants::NOTNULL, Constants::NOTNULL=>Constants::NULL}
  249. + end
  250. +
  251. # Represents an SQL function call.
  252. class Function < GenericExpression
  253. # The array of arguments to pass to the function (may be blank)
  254. diff --git a/spec/core/connection_pool_spec.rb b/spec/core/connection_pool_spec.rb
  255. index 746cd22..a69fac3 100644
  256. --- a/spec/core/connection_pool_spec.rb
  257. +++ b/spec/core/connection_pool_spec.rb
  258. @@ -234,6 +234,17 @@ describe "A connection pool with a max size of 1" do
  259. end
  260.  
  261. shared_examples_for "A threaded connection pool" do
  262. + specify "should raise a PoolTimeout error if a connection couldn't be acquired before timeout" do
  263. + x = nil
  264. + q, q1 = Queue.new, Queue.new
  265. + pool = Sequel::ConnectionPool.get_pool(@cp_opts.merge(:max_connections=>1, :pool_timeout=>0)) {@invoked_count += 1}
  266. + t = Thread.new{pool.hold{|c| q1.push nil; q.pop}}
  267. + q1.pop
  268. + proc{pool.hold{|c|}}.should raise_error(Sequel::PoolTimeout)
  269. + q.push nil
  270. + t.join
  271. + end
  272. +
  273. specify "should let five threads simultaneously access separate connections" do
  274. cc = {}
  275. threads = []
  276. @@ -295,7 +306,8 @@ end
  277. describe "Threaded Unsharded Connection Pool" do
  278. before do
  279. @invoked_count = 0
  280. - @pool = Sequel::ConnectionPool.get_pool(CONNECTION_POOL_DEFAULTS.merge(:max_connections=>5)) {@invoked_count += 1}
  281. + @cp_opts = CONNECTION_POOL_DEFAULTS.merge(:max_connections=>5)
  282. + @pool = Sequel::ConnectionPool.get_pool(@cp_opts) {@invoked_count += 1}
  283. end
  284.  
  285. it_should_behave_like "A threaded connection pool"
  286. @@ -304,7 +316,8 @@ end
  287. describe "Threaded Sharded Connection Pool" do
  288. before do
  289. @invoked_count = 0
  290. - @pool = Sequel::ConnectionPool.get_pool(CONNECTION_POOL_DEFAULTS.merge(:max_connections=>5, :servers=>{})) {@invoked_count += 1}
  291. + @cp_opts = CONNECTION_POOL_DEFAULTS.merge(:max_connections=>5, :servers=>{})
  292. + @pool = Sequel::ConnectionPool.get_pool(@cp_opts) {@invoked_count += 1}
  293. end
  294.  
  295. it_should_behave_like "A threaded connection pool"
  296. diff --git a/spec/core/database_spec.rb b/spec/core/database_spec.rb
  297. index 6b2dac4..8203fff 100644
  298. --- a/spec/core/database_spec.rb
  299. +++ b/spec/core/database_spec.rb
  300. @@ -809,6 +809,25 @@ describe "Database#transaction" do
  301. t.join
  302. @db.transactions.should be_empty
  303. end
  304. +
  305. + if (!defined?(RUBY_ENGINE) or RUBY_ENGINE == 'ruby' or RUBY_ENGINE == 'rbx') and RUBY_VERSION < '1.9'
  306. + specify "should handle Thread#kill for transactions inside threads" do
  307. + q = Queue.new
  308. + q1 = Queue.new
  309. + t = Thread.new do
  310. + @db.transaction do
  311. + @db.execute 'DROP TABLE test'
  312. + q1.push nil
  313. + q.pop
  314. + @db.execute 'DROP TABLE test2'
  315. + end
  316. + end
  317. + q1.pop
  318. + t.kill
  319. + t.join
  320. + @db.sql.should == ['BEGIN', 'DROP TABLE test', 'ROLLBACK']
  321. + end
  322. + end
  323. end
  324.  
  325. describe "Database#transaction with savepoints" do
  326. diff --git a/spec/core/expression_filters_spec.rb b/spec/core/expression_filters_spec.rb
  327. index 492f8f1..3c6de5d 100644
  328. --- a/spec/core/expression_filters_spec.rb
  329. +++ b/spec/core/expression_filters_spec.rb
  330. @@ -424,6 +424,19 @@ describe "Blockless Ruby Filters" do
  331. @d.l(~{:x => Sequel::SQLFALSE}).should == '(x IS NOT FALSE)'
  332. end
  333.  
  334. + it "should support direct negation of SQL::Constants" do
  335. + @d.l({:x => ~Sequel::NULL}).should == '(x IS NOT NULL)'
  336. + @d.l({:x => ~Sequel::NOTNULL}).should == '(x IS NULL)'
  337. + @d.l({:x => ~Sequel::TRUE}).should == '(x IS FALSE)'
  338. + @d.l({:x => ~Sequel::FALSE}).should == '(x IS TRUE)'
  339. + @d.l({:x => ~Sequel::SQLTRUE}).should == '(x IS FALSE)'
  340. + @d.l({:x => ~Sequel::SQLFALSE}).should == '(x IS TRUE)'
  341. + end
  342. +
  343. + it "should raise an error if trying to invert an invalid SQL::Constant" do
  344. + proc{~Sequel::CURRENT_DATE}.should raise_error(Sequel::Error)
  345. + end
  346. +
  347. it "should raise an error if trying to create an invalid complex expression" do
  348. proc{Sequel::SQL::ComplexExpression.new(:BANG, 1, 2)}.should raise_error(Sequel::Error)
  349. end
  350. diff --git a/spec/extensions/many_through_many_spec.rb b/spec/extensions/many_through_many_spec.rb
  351. index e68a8b3..30f11cd 100644
  352. --- a/spec/extensions/many_through_many_spec.rb
  353. +++ b/spec/extensions/many_through_many_spec.rb
  354. @@ -114,22 +114,58 @@ describe Sequel::Model, "many_through_many" do
  355.  
  356. it "should allowing filtering by many_through_many associations" do
  357. @c1.many_through_many :tags, [[:albums_artists, :artist_id, :album_id], [:albums, :id, :id], [:albums_tags, :album_id, :tag_id]]
  358. - @c1.filter(:tags=>@c2.load(:id=>1234)).sql.should == 'SELECT * FROM artists WHERE (id IN (SELECT albums_artists.artist_id FROM albums_artists INNER JOIN albums ON (albums.id = albums_artists.album_id) INNER JOIN albums_tags ON (albums_tags.album_id = albums.id) WHERE (albums_tags.tag_id = 1234)))'
  359. + @c1.filter(:tags=>@c2.load(:id=>1234)).sql.should == 'SELECT * FROM artists WHERE (id IN (SELECT albums_artists.artist_id FROM albums_artists INNER JOIN albums ON (albums.id = albums_artists.album_id) INNER JOIN albums_tags ON (albums_tags.album_id = albums.id) WHERE ((albums_tags.tag_id = 1234) AND (albums_artists.artist_id IS NOT NULL))))'
  360. end
  361.  
  362. it "should allowing filtering by many_through_many associations with a single through table" do
  363. @c1.many_through_many :tags, [[:albums_artists, :artist_id, :album_id]]
  364. - @c1.filter(:tags=>@c2.load(:id=>1234)).sql.should == 'SELECT * FROM artists WHERE (id IN (SELECT albums_artists.artist_id FROM albums_artists WHERE (albums_artists.album_id = 1234)))'
  365. + @c1.filter(:tags=>@c2.load(:id=>1234)).sql.should == 'SELECT * FROM artists WHERE (id IN (SELECT albums_artists.artist_id FROM albums_artists WHERE ((albums_artists.album_id = 1234) AND (albums_artists.artist_id IS NOT NULL))))'
  366. end
  367.  
  368. it "should allowing filtering by many_through_many associations with aliased tables" do
  369. @c1.many_through_many :tags, [[:albums_artists, :artist_id, :album_id], [:albums_artists, :id, :id], [:albums_artists, :album_id, :tag_id]]
  370. - @c1.filter(:tags=>@c2.load(:id=>1234)).sql.should == 'SELECT * FROM artists WHERE (id IN (SELECT albums_artists.artist_id FROM albums_artists INNER JOIN albums_artists AS albums_artists_0 ON (albums_artists_0.id = albums_artists.album_id) INNER JOIN albums_artists AS albums_artists_1 ON (albums_artists_1.album_id = albums_artists_0.id) WHERE (albums_artists_1.tag_id = 1234)))'
  371. + @c1.filter(:tags=>@c2.load(:id=>1234)).sql.should == 'SELECT * FROM artists WHERE (id IN (SELECT albums_artists.artist_id FROM albums_artists INNER JOIN albums_artists AS albums_artists_0 ON (albums_artists_0.id = albums_artists.album_id) INNER JOIN albums_artists AS albums_artists_1 ON (albums_artists_1.album_id = albums_artists_0.id) WHERE ((albums_artists_1.tag_id = 1234) AND (albums_artists.artist_id IS NOT NULL))))'
  372. end
  373.  
  374. it "should allowing filtering by many_through_many associations with composite keys" do
  375. @c1.many_through_many :tags, [[:albums_artists, [:b1, :b2], [:c1, :c2]], [:albums, [:d1, :d2], [:e1, :e2]], [:albums_tags, [:f1, :f2], [:g1, :g2]]], :right_primary_key=>[:h1, :h2], :left_primary_key=>[:id, :yyy]
  376. - @c1.filter(:tags=>@c2.load(:h1=>1234, :h2=>85)).sql.should == 'SELECT * FROM artists WHERE ((id, yyy) IN (SELECT albums_artists.b1, albums_artists.b2 FROM albums_artists INNER JOIN albums ON ((albums.d1 = albums_artists.c1) AND (albums.d2 = albums_artists.c2)) INNER JOIN albums_tags ON ((albums_tags.f1 = albums.e1) AND (albums_tags.f2 = albums.e2)) WHERE ((albums_tags.g1 = 1234) AND (albums_tags.g2 = 85))))'
  377. + @c1.filter(:tags=>@c2.load(:h1=>1234, :h2=>85)).sql.should == 'SELECT * FROM artists WHERE ((id, yyy) IN (SELECT albums_artists.b1, albums_artists.b2 FROM albums_artists INNER JOIN albums ON ((albums.d1 = albums_artists.c1) AND (albums.d2 = albums_artists.c2)) INNER JOIN albums_tags ON ((albums_tags.f1 = albums.e1) AND (albums_tags.f2 = albums.e2)) WHERE ((albums_tags.g1 = 1234) AND (albums_tags.g2 = 85) AND (albums_artists.b1 IS NOT NULL) AND (albums_artists.b2 IS NOT NULL))))'
  378. + end
  379. +
  380. + it "should allowing excluding by many_through_many associations" do
  381. + @c1.many_through_many :tags, [[:albums_artists, :artist_id, :album_id], [:albums, :id, :id], [:albums_tags, :album_id, :tag_id]]
  382. + @c1.exclude(:tags=>@c2.load(:id=>1234)).sql.should == 'SELECT * FROM artists WHERE ((id NOT IN (SELECT albums_artists.artist_id FROM albums_artists INNER JOIN albums ON (albums.id = albums_artists.album_id) INNER JOIN albums_tags ON (albums_tags.album_id = albums.id) WHERE ((albums_tags.tag_id = 1234) AND (albums_artists.artist_id IS NOT NULL)))) OR (id IS NULL))'
  383. + end
  384. +
  385. + it "should allowing excluding by many_through_many associations with composite keys" do
  386. + @c1.many_through_many :tags, [[:albums_artists, [:b1, :b2], [:c1, :c2]], [:albums, [:d1, :d2], [:e1, :e2]], [:albums_tags, [:f1, :f2], [:g1, :g2]]], :right_primary_key=>[:h1, :h2], :left_primary_key=>[:id, :yyy]
  387. + @c1.exclude(:tags=>@c2.load(:h1=>1234, :h2=>85)).sql.should == 'SELECT * FROM artists WHERE (((id, yyy) NOT IN (SELECT albums_artists.b1, albums_artists.b2 FROM albums_artists INNER JOIN albums ON ((albums.d1 = albums_artists.c1) AND (albums.d2 = albums_artists.c2)) INNER JOIN albums_tags ON ((albums_tags.f1 = albums.e1) AND (albums_tags.f2 = albums.e2)) WHERE ((albums_tags.g1 = 1234) AND (albums_tags.g2 = 85) AND (albums_artists.b1 IS NOT NULL) AND (albums_artists.b2 IS NOT NULL)))) OR (id IS NULL) OR (yyy IS NULL))'
  388. + end
  389. +
  390. + it "should allowing filtering by multiple many_through_many associations" do
  391. + @c1.many_through_many :tags, [[:albums_artists, :artist_id, :album_id], [:albums, :id, :id], [:albums_tags, :album_id, :tag_id]]
  392. + @c1.filter(:tags=>[@c2.load(:id=>1234), @c2.load(:id=>2345)]).sql.should == 'SELECT * FROM artists WHERE (id IN (SELECT albums_artists.artist_id FROM albums_artists INNER JOIN albums ON (albums.id = albums_artists.album_id) INNER JOIN albums_tags ON (albums_tags.album_id = albums.id) WHERE ((albums_tags.tag_id IN (1234, 2345)) AND (albums_artists.artist_id IS NOT NULL))))'
  393. + end
  394. +
  395. + it "should allowing filtering by multiple many_through_many associations with composite keys" do
  396. + @c1.many_through_many :tags, [[:albums_artists, [:b1, :b2], [:c1, :c2]], [:albums, [:d1, :d2], [:e1, :e2]], [:albums_tags, [:f1, :f2], [:g1, :g2]]], :right_primary_key=>[:h1, :h2], :left_primary_key=>[:id, :yyy]
  397. + @c1.filter(:tags=>[@c2.load(:h1=>1234, :h2=>85), @c2.load(:h1=>2345, :h2=>95)]).sql.should == 'SELECT * FROM artists WHERE ((id, yyy) IN (SELECT albums_artists.b1, albums_artists.b2 FROM albums_artists INNER JOIN albums ON ((albums.d1 = albums_artists.c1) AND (albums.d2 = albums_artists.c2)) INNER JOIN albums_tags ON ((albums_tags.f1 = albums.e1) AND (albums_tags.f2 = albums.e2)) WHERE (((albums_tags.g1, albums_tags.g2) IN ((1234, 85), (2345, 95))) AND (albums_artists.b1 IS NOT NULL) AND (albums_artists.b2 IS NOT NULL))))'
  398. + end
  399. +
  400. + it "should allowing excluding by multiple many_through_many associations" do
  401. + @c1.many_through_many :tags, [[:albums_artists, :artist_id, :album_id], [:albums, :id, :id], [:albums_tags, :album_id, :tag_id]]
  402. + @c1.exclude(:tags=>[@c2.load(:id=>1234), @c2.load(:id=>2345)]).sql.should == 'SELECT * FROM artists WHERE ((id NOT IN (SELECT albums_artists.artist_id FROM albums_artists INNER JOIN albums ON (albums.id = albums_artists.album_id) INNER JOIN albums_tags ON (albums_tags.album_id = albums.id) WHERE ((albums_tags.tag_id IN (1234, 2345)) AND (albums_artists.artist_id IS NOT NULL)))) OR (id IS NULL))'
  403. + end
  404. +
  405. + it "should allowing excluding by multiple many_through_many associations with composite keys" do
  406. + @c1.many_through_many :tags, [[:albums_artists, [:b1, :b2], [:c1, :c2]], [:albums, [:d1, :d2], [:e1, :e2]], [:albums_tags, [:f1, :f2], [:g1, :g2]]], :right_primary_key=>[:h1, :h2], :left_primary_key=>[:id, :yyy]
  407. + @c1.exclude(:tags=>[@c2.load(:h1=>1234, :h2=>85), @c2.load(:h1=>2345, :h2=>95)]).sql.should == 'SELECT * FROM artists WHERE (((id, yyy) NOT IN (SELECT albums_artists.b1, albums_artists.b2 FROM albums_artists INNER JOIN albums ON ((albums.d1 = albums_artists.c1) AND (albums.d2 = albums_artists.c2)) INNER JOIN albums_tags ON ((albums_tags.f1 = albums.e1) AND (albums_tags.f2 = albums.e2)) WHERE (((albums_tags.g1, albums_tags.g2) IN ((1234, 85), (2345, 95))) AND (albums_artists.b1 IS NOT NULL) AND (albums_artists.b2 IS NOT NULL)))) OR (id IS NULL) OR (yyy IS NULL))'
  408. + end
  409. +
  410. + it "should allowing filtering/excluding many_through_many associations with NULL values" do
  411. + @c1.many_through_many :tags, [[:albums_artists, :artist_id, :album_id], [:albums, :id, :id], [:albums_tags, :album_id, :tag_id]]
  412. + @c1.filter(:tags=>@c2.new).sql.should == 'SELECT * FROM artists WHERE \'f\''
  413. + @c1.exclude(:tags=>@c2.new).sql.should == 'SELECT * FROM artists WHERE \'t\''
  414. end
  415.  
  416. it "should support a :conditions option" do
  417. diff --git a/spec/integration/associations_test.rb b/spec/integration/associations_test.rb
  418. index b2fe47a..a519d47 100644
  419. --- a/spec/integration/associations_test.rb
  420. +++ b/spec/integration/associations_test.rb
  421. @@ -26,10 +26,6 @@ shared_examples_for "regular and composite key associations" do
  422. @album.update(:artist => @artist)
  423. @album.add_tag(@tag)
  424.  
  425. - @album.reload
  426. - @artist.reload
  427. - @tag.reload
  428. -
  429. Artist.filter(:albums=>@album).all.should == [@artist]
  430. Album.filter(:artist=>@artist).all.should == [@album]
  431. Album.filter(:tags=>@tag).all.should == [@album]
  432. @@ -37,7 +33,104 @@ shared_examples_for "regular and composite key associations" do
  433. Album.filter(:artist=>@artist, :tags=>@tag).all.should == [@album]
  434. @artist.albums_dataset.filter(:tags=>@tag).all.should == [@album]
  435. end
  436. +
  437. + specify "should work correctly when excluding by associations" do
  438. + @album.update(:artist => @artist)
  439. + @album.add_tag(@tag)
  440. + album, artist, tag = @pr.call
  441. +
  442. + Artist.exclude(:albums=>@album).all.should == [artist]
  443. + Album.exclude(:artist=>@artist).all.should == [album]
  444. + Album.exclude(:tags=>@tag).all.should == [album]
  445. + Tag.exclude(:albums=>@album).all.should == [tag]
  446. + Album.exclude(:artist=>@artist, :tags=>@tag).all.should == [album]
  447. + end
  448.  
  449. + specify "should work correctly when filtering by multiple associations" do
  450. + album, artist, tag = @pr.call
  451. + @album.update(:artist => @artist)
  452. + @album.add_tag(@tag)
  453. +
  454. + Artist.filter(:albums=>[@album, album]).all.should == [@artist]
  455. + Album.filter(:artist=>[@artist, artist]).all.should == [@album]
  456. + Album.filter(:tags=>[@tag, tag]).all.should == [@album]
  457. + Tag.filter(:albums=>[@album, album]).all.should == [@tag]
  458. + Album.filter(:artist=>[@artist, artist], :tags=>[@tag, tag]).all.should == [@album]
  459. + @artist.albums_dataset.filter(:tags=>[@tag, tag]).all.should == [@album]
  460. +
  461. + album.add_tag(tag)
  462. +
  463. + Artist.filter(:albums=>[@album, album]).all.should == [@artist]
  464. + Album.filter(:artist=>[@artist, artist]).all.should == [@album]
  465. + Album.filter(:tags=>[@tag, tag]).all.sort_by{|x| x.pk}.should == [@album, album]
  466. + Tag.filter(:albums=>[@album, album]).all.sort_by{|x| x.pk}.should == [@tag, tag]
  467. + Album.filter(:artist=>[@artist, artist], :tags=>[@tag, tag]).all.should == [@album]
  468. +
  469. + album.update(:artist => artist)
  470. +
  471. + Artist.filter(:albums=>[@album, album]).all.sort_by{|x| x.pk}.should == [@artist, artist]
  472. + Album.filter(:artist=>[@artist, artist]).all.sort_by{|x| x.pk}.should == [@album, album]
  473. + Album.filter(:tags=>[@tag, tag]).all.sort_by{|x| x.pk}.should == [@album, album]
  474. + Tag.filter(:albums=>[@album, album]).all.sort_by{|x| x.pk}.should == [@tag, tag]
  475. + Album.filter(:artist=>[@artist, artist], :tags=>[@tag, tag]).all.sort_by{|x| x.pk}.should == [@album, album]
  476. + end
  477. +
  478. + specify "should work correctly when excluding by multiple associations" do
  479. + album, artist, tag = @pr.call
  480. +
  481. + Artist.exclude(:albums=>[@album, album]).all.sort_by{|x| x.pk}.should == [@artist, artist]
  482. + Album.exclude(:artist=>[@artist, artist]).all.sort_by{|x| x.pk}.should == [@album, album]
  483. + Album.exclude(:tags=>[@tag, tag]).all.sort_by{|x| x.pk}.should == [@album, album]
  484. + Tag.exclude(:albums=>[@album, album]).all.sort_by{|x| x.pk}.should == [@tag, tag]
  485. + Album.exclude(:artist=>[@artist, artist], :tags=>[@tag, tag]).all.sort_by{|x| x.pk}.should == [@album, album]
  486. +
  487. + @album.update(:artist => @artist)
  488. + @album.add_tag(@tag)
  489. +
  490. + Artist.exclude(:albums=>[@album, album]).all.sort_by{|x| x.pk}.should == [artist]
  491. + Album.exclude(:artist=>[@artist, artist]).all.sort_by{|x| x.pk}.should == [album]
  492. + Album.exclude(:tags=>[@tag, tag]).all.sort_by{|x| x.pk}.should == [album]
  493. + Tag.exclude(:albums=>[@album, album]).all.sort_by{|x| x.pk}.should == [tag]
  494. + Album.exclude(:artist=>[@artist, artist], :tags=>[@tag, tag]).all.sort_by{|x| x.pk}.should == [album]
  495. +
  496. + album.add_tag(tag)
  497. +
  498. + Artist.exclude(:albums=>[@album, album]).all.should == [artist]
  499. + Album.exclude(:artist=>[@artist, artist]).all.should == [album]
  500. + Album.exclude(:tags=>[@tag, tag]).all.should == []
  501. + Tag.exclude(:albums=>[@album, album]).all.should == []
  502. + Album.exclude(:artist=>[@artist, artist], :tags=>[@tag, tag]).all.should == [album]
  503. +
  504. + album.update(:artist => artist)
  505. +
  506. + Artist.exclude(:albums=>[@album, album]).all.should == []
  507. + Album.exclude(:artist=>[@artist, artist]).all.should == []
  508. + Album.exclude(:tags=>[@tag, tag]).all.should == []
  509. + Tag.exclude(:albums=>[@album, album]).all.should == []
  510. + Album.exclude(:artist=>[@artist, artist], :tags=>[@tag, tag]).all.should == []
  511. + end
  512. +
  513. + specify "should work correctly when excluding by associations in regards to NULL values" do
  514. + Artist.exclude(:albums=>@album).all.should == [@artist]
  515. + Album.exclude(:artist=>@artist).all.should == [@album]
  516. + Album.exclude(:tags=>@tag).all.should == [@album]
  517. + Tag.exclude(:albums=>@album).all.should == [@tag]
  518. + Album.exclude(:artist=>@artist, :tags=>@tag).all.should == [@album]
  519. +
  520. + @album.update(:artist => @artist)
  521. + @artist.albums_dataset.exclude(:tags=>@tag).all.should == [@album]
  522. + end
  523. +
  524. + specify "should handle NULL values in join table correctly when filtering/excluding many_to_many associations" do
  525. + @ins.call
  526. + Album.exclude(:tags=>@tag).all.should == [@album]
  527. + @album.add_tag(@tag)
  528. + Album.filter(:tags=>@tag).all.should == [@album]
  529. + album, artist, tag = @pr.call
  530. + Album.exclude(:tags=>@tag).all.should == [album]
  531. + Album.exclude(:tags=>tag).all.sort_by{|x| x.pk}.should == [@album, album]
  532. + end
  533. +
  534. specify "should have remove methods work" do
  535. @album.update(:artist => @artist)
  536. @album.add_tag(@tag)
  537. @@ -167,6 +260,8 @@ describe "Sequel::Model Simple Associations" do
  538. @album = Album.create(:name=>'Al')
  539. @artist = Artist.create(:name=>'Ar')
  540. @tag = Tag.create(:name=>'T')
  541. + @pr = lambda{[Album.create(:name=>'Al2'),Artist.create(:name=>'Ar2'),Tag.create(:name=>'T2')]}
  542. + @ins = lambda{@db[:albums_tags].insert(:tag_id=>@tag.id)}
  543. end
  544. after do
  545. @db.drop_table(:albums_tags, :tags, :albums, :artists)
  546. @@ -333,6 +428,8 @@ describe "Sequel::Model Composite Key Associations" do
  547. @album = Album.create(:name=>'Al', :id1=>1, :id2=>2)
  548. @artist = Artist.create(:name=>'Ar', :id1=>3, :id2=>4)
  549. @tag = Tag.create(:name=>'T', :id1=>5, :id2=>6)
  550. + @pr = lambda{[Album.create(:name=>'Al2', :id1=>11, :id2=>12),Artist.create(:name=>'Ar2', :id1=>13, :id2=>14),Tag.create(:name=>'T2', :id1=>15, :id2=>16)]}
  551. + @ins = lambda{@db[:albums_tags].insert(:tag_id1=>@tag.id1, :tag_id2=>@tag.id2)}
  552. end
  553. after do
  554. @db.drop_table(:albums_tags, :tags, :albums, :artists)
  555. diff --git a/spec/integration/dataset_test.rb b/spec/integration/dataset_test.rb
  556. index b5d2179..6569f36 100644
  557. --- a/spec/integration/dataset_test.rb
  558. +++ b/spec/integration/dataset_test.rb
  559. @@ -86,6 +86,13 @@ describe "Simple Dataset operations" do
  560. specify "should alias columns correctly" do
  561. @ds.select(:id___x, :number___n).first.should == {:x=>1, :n=>10}
  562. end
  563. +
  564. + specify "should handle true/false properly" do
  565. + @ds.filter(Sequel::TRUE).select_map(:number).should == [10]
  566. + @ds.filter(Sequel::FALSE).select_map(:number).should == []
  567. + @ds.filter(true).select_map(:number).should == [10]
  568. + @ds.filter(false).select_map(:number).should == []
  569. + end
  570. end
  571.  
  572. describe Sequel::Dataset do
  573. diff --git a/spec/integration/plugin_test.rb b/spec/integration/plugin_test.rb
  574. index b454484..cd9d9af 100644
  575. --- a/spec/integration/plugin_test.rb
  576. +++ b/spec/integration/plugin_test.rb
  577. @@ -210,6 +210,17 @@ describe "Many Through Many Plugin" do
  578. Artist.filter(:albums=>@album2).all.map{|a| a.name}.sort.should == %w'3 4'
  579. Artist.filter(:albums=>@album3).all.map{|a| a.name}.sort.should == %w'2 3'
  580. Artist.filter(:albums=>@album4).all.map{|a| a.name}.sort.should == %w'1 4'
  581. +
  582. + Artist.exclude(:albums=>@album1).all.map{|a| a.name}.sort.should == %w'3 4'
  583. + Artist.exclude(:albums=>@album2).all.map{|a| a.name}.sort.should == %w'1 2'
  584. + Artist.exclude(:albums=>@album3).all.map{|a| a.name}.sort.should == %w'1 4'
  585. + Artist.exclude(:albums=>@album4).all.map{|a| a.name}.sort.should == %w'2 3'
  586. +
  587. + Artist.filter(:albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'1 2 3'
  588. + Artist.filter(:albums=>[@album2, @album4]).all.map{|a| a.name}.sort.should == %w'1 3 4'
  589. +
  590. + Artist.exclude(:albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'4'
  591. + Artist.exclude(:albums=>[@album2, @album4]).all.map{|a| a.name}.sort.should == %w'2'
  592. end
  593.  
  594. specify "should handle typical case with 3 join tables" do
  595. @@ -233,6 +244,14 @@ describe "Many Through Many Plugin" do
  596. Artist.filter(:related_artists=>@artist2).all.map{|a| a.name}.sort.should == %w'1 2 3'
  597. Artist.filter(:related_artists=>@artist3).all.map{|a| a.name}.sort.should == %w'2 3 4'
  598. Artist.filter(:related_artists=>@artist4).all.map{|a| a.name}.sort.should == %w'1 3 4'
  599. +
  600. + Artist.exclude(:related_artists=>@artist1).all.map{|a| a.name}.sort.should == %w'3'
  601. + Artist.exclude(:related_artists=>@artist2).all.map{|a| a.name}.sort.should == %w'4'
  602. + Artist.exclude(:related_artists=>@artist3).all.map{|a| a.name}.sort.should == %w'1'
  603. + Artist.exclude(:related_artists=>@artist4).all.map{|a| a.name}.sort.should == %w'2'
  604. +
  605. + Artist.filter(:related_artists=>[@artist1, @artist4]).all.map{|a| a.name}.sort.should == %w'1 2 3 4'
  606. + Artist.exclude(:related_artists=>[@artist1, @artist4]).all.map{|a| a.name}.sort.should == %w''
  607. end
  608.  
  609. specify "should handle extreme case with 5 join tables" do
  610. @@ -265,6 +284,17 @@ describe "Many Through Many Plugin" do
  611. Artist.filter(:related_albums=>@album2).all.map{|a| a.name}.sort.should == %w'1 2 3 4'
  612. Artist.filter(:related_albums=>@album3).all.map{|a| a.name}.sort.should == %w'1 2'
  613. Artist.filter(:related_albums=>@album4).all.map{|a| a.name}.sort.should == %w'2 3 4'
  614. +
  615. + Artist.exclude(:related_albums=>@album1).all.map{|a| a.name}.sort.should == %w'4'
  616. + Artist.exclude(:related_albums=>@album2).all.map{|a| a.name}.sort.should == %w''
  617. + Artist.exclude(:related_albums=>@album3).all.map{|a| a.name}.sort.should == %w'3 4'
  618. + Artist.exclude(:related_albums=>@album4).all.map{|a| a.name}.sort.should == %w'1'
  619. +
  620. + Artist.filter(:related_albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'1 2 3'
  621. + Artist.filter(:related_albums=>[@album3, @album4]).all.map{|a| a.name}.sort.should == %w'1 2 3 4'
  622. +
  623. + Artist.exclude(:related_albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'4'
  624. + Artist.exclude(:related_albums=>[@album2, @album4]).all.map{|a| a.name}.sort.should == %w''
  625. end
  626. end
  627.  
  628. diff --git a/spec/model/associations_spec.rb b/spec/model/associations_spec.rb
  629. index 2f0a601..450d4fb 100644
  630. --- a/spec/model/associations_spec.rb
  631. +++ b/spec/model/associations_spec.rb
  632. @@ -2795,7 +2795,7 @@ describe "Filtering by associations" do
  633. end
  634.  
  635. it "should be able to filter on many_to_many associations" do
  636. - @Album.filter(:tags=>@Tag.load(:id=>3)).sql.should == 'SELECT * FROM albums WHERE (id IN (SELECT album_id FROM albums_tags WHERE (tag_id = 3)))'
  637. + @Album.filter(:tags=>@Tag.load(:id=>3)).sql.should == 'SELECT * FROM albums WHERE (id IN (SELECT album_id FROM albums_tags WHERE ((tag_id = 3) AND (album_id IS NOT NULL))))'
  638. end
  639.  
  640. it "should be able to filter on many_to_one associations with composite keys" do
  641. @@ -2811,7 +2811,7 @@ describe "Filtering by associations" do
  642. end
  643.  
  644. it "should be able to filter on many_to_many associations with composite keys" do
  645. - @Album.filter(:ctags=>@Tag.load(:tid1=>3, :tid2=>4)).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN (SELECT album_id1, album_id2 FROM albums_tags WHERE ((tag_id1 = 3) AND (tag_id2 = 4))))'
  646. + @Album.filter(:ctags=>@Tag.load(:tid1=>3, :tid2=>4)).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN (SELECT album_id1, album_id2 FROM albums_tags WHERE ((tag_id1 = 3) AND (tag_id2 = 4) AND (album_id1 IS NOT NULL) AND (album_id2 IS NOT NULL))))'
  647. end
  648.  
  649. it "should work inside a complex filter" do
  650. @@ -2828,16 +2828,286 @@ describe "Filtering by associations" do
  651. it "should raise for an invalid association type" do
  652. @Album.many_to_many :iatags, :clone=>:tags
  653. @Album.association_reflection(:iatags)[:type] = :foo
  654. - proc{@Album.filter(:mtmtags=>@Tag.load(:id=>3)).sql}.should raise_error(Sequel::Error)
  655. + proc{@Album.filter(:iatags=>@Tag.load(:id=>3)).sql}.should raise_error(Sequel::Error)
  656. end
  657.  
  658. it "should raise for an invalid associated object class " do
  659. proc{@Album.filter(:tags=>@Artist.load(:id=>3)).sql}.should raise_error(Sequel::Error)
  660. end
  661.  
  662. + it "should raise for an invalid associated object class when multiple objects are used" do
  663. + proc{@Album.filter(:tags=>[@Tag.load(:id=>3), @Artist.load(:id=>3)]).sql}.should raise_error(Sequel::Error)
  664. + end
  665. +
  666. + it "should correctly handle case when a multiple value association is used" do
  667. + proc{@Album.filter(:tags=>[@Tag.load(:id=>3), @Artist.load(:id=>3)]).sql}.should raise_error(Sequel::Error)
  668. + end
  669. +
  670. + it "should not affect non-association IN/NOT IN filtering with an empty array" do
  671. + @Album.filter(:tag_id=>[]).sql.should == 'SELECT * FROM albums WHERE (tag_id != tag_id)'
  672. + @Album.exclude(:tag_id=>[]).sql.should == 'SELECT * FROM albums WHERE (1 = 1)'
  673. + end
  674. +
  675. it "should work correctly in subclasses" do
  676. c = Class.new(@Album)
  677. c.many_to_one :sartist, :class=>@Artist
  678. c.filter(:sartist=>@Artist.load(:id=>3)).sql.should == 'SELECT * FROM albums WHERE (sartist_id = 3)'
  679. end
  680. +
  681. + it "should be able to exclude on many_to_one associations" do
  682. + @Album.exclude(:artist=>@Artist.load(:id=>3)).sql.should == 'SELECT * FROM albums WHERE ((artist_id != 3) OR (artist_id IS NULL))'
  683. + end
  684. +
  685. + it "should be able to exclude on one_to_many associations" do
  686. + @Album.exclude(:tracks=>@Track.load(:album_id=>3)).sql.should == 'SELECT * FROM albums WHERE ((id != 3) OR (id IS NULL))'
  687. + end
  688. +
  689. + it "should be able to exclude on one_to_one associations" do
  690. + @Album.exclude(:album_info=>@AlbumInfo.load(:album_id=>3)).sql.should == 'SELECT * FROM albums WHERE ((id != 3) OR (id IS NULL))'
  691. + end
  692. +
  693. + it "should be able to exclude on many_to_many associations" do
  694. + @Album.exclude(:tags=>@Tag.load(:id=>3)).sql.should == 'SELECT * FROM albums WHERE ((id NOT IN (SELECT album_id FROM albums_tags WHERE ((tag_id = 3) AND (album_id IS NOT NULL)))) OR (id IS NULL))'
  695. + end
  696. +
  697. + it "should be able to exclude on many_to_one associations with composite keys" do
  698. + @Album.exclude(:cartist=>@Artist.load(:id1=>3, :id2=>4)).sql.should == 'SELECT * FROM albums WHERE ((artist_id1 != 3) OR (artist_id2 != 4) OR (artist_id1 IS NULL) OR (artist_id2 IS NULL))'
  699. + end
  700. +
  701. + it "should be able to exclude on one_to_many associations with composite keys" do
  702. + @Album.exclude(:ctracks=>@Track.load(:album_id1=>3, :album_id2=>4)).sql.should == 'SELECT * FROM albums WHERE ((id1 != 3) OR (id2 != 4) OR (id1 IS NULL) OR (id2 IS NULL))'
  703. + end
  704. +
  705. + it "should be able to exclude on one_to_one associations with composite keys" do
  706. + @Album.exclude(:calbum_info=>@AlbumInfo.load(:album_id1=>3, :album_id2=>4)).sql.should == 'SELECT * FROM albums WHERE ((id1 != 3) OR (id2 != 4) OR (id1 IS NULL) OR (id2 IS NULL))'
  707. + end
  708. +
  709. + it "should be able to exclude on many_to_many associations with composite keys" do
  710. + @Album.exclude(:ctags=>@Tag.load(:tid1=>3, :tid2=>4)).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN (SELECT album_id1, album_id2 FROM albums_tags WHERE ((tag_id1 = 3) AND (tag_id2 = 4) AND (album_id1 IS NOT NULL) AND (album_id2 IS NOT NULL)))) OR (id1 IS NULL) OR (id2 IS NULL))'
  711. + end
  712. +
  713. + it "should be able to filter on multiple many_to_one associations" do
  714. + @Album.filter(:artist=>[@Artist.load(:id=>3), @Artist.load(:id=>4)]).sql.should == 'SELECT * FROM albums WHERE (artist_id IN (3, 4))'
  715. + end
  716. +
  717. + it "should be able to filter on multiple one_to_many associations" do
  718. + @Album.filter(:tracks=>[@Track.load(:album_id=>3), @Track.load(:album_id=>4)]).sql.should == 'SELECT * FROM albums WHERE (id IN (3, 4))'
  719. + end
  720. +
  721. + it "should be able to filter on multiple one_to_one associations" do
  722. + @Album.filter(:album_info=>[@AlbumInfo.load(:album_id=>3), @AlbumInfo.load(:album_id=>4)]).sql.should == 'SELECT * FROM albums WHERE (id IN (3, 4))'
  723. + end
  724. +
  725. + it "should be able to filter on multiple many_to_many associations" do
  726. + @Album.filter(:tags=>[@Tag.load(:id=>3), @Tag.load(:id=>4)]).sql.should == 'SELECT * FROM albums WHERE (id IN (SELECT album_id FROM albums_tags WHERE ((tag_id IN (3, 4)) AND (album_id IS NOT NULL))))'
  727. + end
  728. +
  729. + it "should be able to filter on multiple many_to_one associations with composite keys" do
  730. + @Album.filter(:cartist=>[@Artist.load(:id1=>3, :id2=>4), @Artist.load(:id1=>5, :id2=>6)]).sql.should == 'SELECT * FROM albums WHERE ((artist_id1, artist_id2) IN ((3, 4), (5, 6)))'
  731. + end
  732. +
  733. + it "should be able to filter on multiple one_to_many associations with composite keys" do
  734. + @Album.filter(:ctracks=>[@Track.load(:album_id1=>3, :album_id2=>4), @Track.load(:album_id1=>5, :album_id2=>6)]).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN ((3, 4), (5, 6)))'
  735. + end
  736. +
  737. + it "should be able to filter on multiple one_to_one associations with composite keys" do
  738. + @Album.filter(:calbum_info=>[@AlbumInfo.load(:album_id1=>3, :album_id2=>4), @AlbumInfo.load(:album_id1=>5, :album_id2=>6)]).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN ((3, 4), (5, 6)))'
  739. + end
  740. +
  741. + it "should be able to filter on multiple many_to_many associations with composite keys" do
  742. + @Album.filter(:ctags=>[@Tag.load(:tid1=>3, :tid2=>4), @Tag.load(:tid1=>5, :tid2=>6)]).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN (SELECT album_id1, album_id2 FROM albums_tags WHERE (((tag_id1, tag_id2) IN ((3, 4), (5, 6))) AND (album_id1 IS NOT NULL) AND (album_id2 IS NOT NULL))))'
  743. + end
  744. +
  745. + it "should be able to exclude on multiple many_to_one associations" do
  746. + @Album.exclude(:artist=>[@Artist.load(:id=>3), @Artist.load(:id=>4)]).sql.should == 'SELECT * FROM albums WHERE ((artist_id NOT IN (3, 4)) OR (artist_id IS NULL))'
  747. + end
  748. +
  749. + it "should be able to exclude on multiple one_to_many associations" do
  750. + @Album.exclude(:tracks=>[@Track.load(:album_id=>3), @Track.load(:album_id=>4)]).sql.should == 'SELECT * FROM albums WHERE ((id NOT IN (3, 4)) OR (id IS NULL))'
  751. + end
  752. +
  753. + it "should be able to exclude on multiple one_to_one associations" do
  754. + @Album.exclude(:album_info=>[@AlbumInfo.load(:album_id=>3), @AlbumInfo.load(:album_id=>4)]).sql.should == 'SELECT * FROM albums WHERE ((id NOT IN (3, 4)) OR (id IS NULL))'
  755. + end
  756. +
  757. + it "should be able to exclude on multiple many_to_many associations" do
  758. + @Album.exclude(:tags=>[@Tag.load(:id=>3), @Tag.load(:id=>4)]).sql.should == 'SELECT * FROM albums WHERE ((id NOT IN (SELECT album_id FROM albums_tags WHERE ((tag_id IN (3, 4)) AND (album_id IS NOT NULL)))) OR (id IS NULL))'
  759. + end
  760. +
  761. + it "should be able to exclude on multiple many_to_one associations with composite keys" do
  762. + @Album.exclude(:cartist=>[@Artist.load(:id1=>3, :id2=>4), @Artist.load(:id1=>5, :id2=>6)]).sql.should == 'SELECT * FROM albums WHERE (((artist_id1, artist_id2) NOT IN ((3, 4), (5, 6))) OR (artist_id1 IS NULL) OR (artist_id2 IS NULL))'
  763. + end
  764. +
  765. + it "should be able to exclude on multiple one_to_many associations with composite keys" do
  766. + @Album.exclude(:ctracks=>[@Track.load(:album_id1=>3, :album_id2=>4), @Track.load(:album_id1=>5, :album_id2=>6)]).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN ((3, 4), (5, 6))) OR (id1 IS NULL) OR (id2 IS NULL))'
  767. + end
  768. +
  769. + it "should be able to exclude on multiple one_to_one associations with composite keys" do
  770. + @Album.exclude(:calbum_info=>[@AlbumInfo.load(:album_id1=>3, :album_id2=>4), @AlbumInfo.load(:album_id1=>5, :album_id2=>6)]).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN ((3, 4), (5, 6))) OR (id1 IS NULL) OR (id2 IS NULL))'
  771. + end
  772. +
  773. + it "should be able to exclude on multiple many_to_many associations with composite keys" do
  774. + @Album.exclude(:ctags=>[@Tag.load(:tid1=>3, :tid2=>4), @Tag.load(:tid1=>5, :tid2=>6)]).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN (SELECT album_id1, album_id2 FROM albums_tags WHERE (((tag_id1, tag_id2) IN ((3, 4), (5, 6))) AND (album_id1 IS NOT NULL) AND (album_id2 IS NOT NULL)))) OR (id1 IS NULL) OR (id2 IS NULL))'
  775. + end
  776. +
  777. + it "should be able to handle NULL values when filtering many_to_one associations" do
  778. + @Album.filter(:artist=>@Artist.new).sql.should == 'SELECT * FROM albums WHERE \'f\''
  779. + end
  780. +
  781. + it "should be able to handle NULL values when filtering one_to_many associations" do
  782. + @Album.filter(:tracks=>@Track.new).sql.should == 'SELECT * FROM albums WHERE \'f\''
  783. + end
  784. +
  785. + it "should be able to handle NULL values when filtering one_to_one associations" do
  786. + @Album.filter(:album_info=>@AlbumInfo.new).sql.should == 'SELECT * FROM albums WHERE \'f\''
  787. + end
  788. +
  789. + it "should be able to handle NULL values when filtering many_to_many associations" do
  790. + @Album.filter(:tags=>@Tag.new).sql.should == 'SELECT * FROM albums WHERE \'f\''
  791. + end
  792. +
  793. + it "should be able to handle filteringing with NULL values for many_to_one associations with composite keys" do
  794. + @Album.filter(:cartist=>@Artist.load(:id2=>4)).sql.should == 'SELECT * FROM albums WHERE \'f\''
  795. + @Album.filter(:cartist=>@Artist.load(:id1=>3)).sql.should == 'SELECT * FROM albums WHERE \'f\''
  796. + @Album.filter(:cartist=>@Artist.new).sql.should == 'SELECT * FROM albums WHERE \'f\''
  797. + end
  798. +
  799. + it "should be able to filtering with NULL values for one_to_many associations with composite keys" do
  800. + @Album.filter(:ctracks=>@Track.load(:album_id2=>4)).sql.should == 'SELECT * FROM albums WHERE \'f\''
  801. + @Album.filter(:ctracks=>@Track.load(:album_id1=>3)).sql.should == 'SELECT * FROM albums WHERE \'f\''
  802. + @Album.filter(:ctracks=>@Track.new).sql.should == 'SELECT * FROM albums WHERE \'f\''
  803. + end
  804. +
  805. + it "should be able to filtering with NULL values for one_to_one associations with composite keys" do
  806. + @Album.filter(:calbum_info=>@AlbumInfo.load(:album_id2=>4)).sql.should == 'SELECT * FROM albums WHERE \'f\''
  807. + @Album.filter(:calbum_info=>@AlbumInfo.load(:album_id1=>3)).sql.should == 'SELECT * FROM albums WHERE \'f\''
  808. + @Album.filter(:calbum_info=>@AlbumInfo.new).sql.should == 'SELECT * FROM albums WHERE \'f\''
  809. + end
  810. +
  811. + it "should be able to filtering with NULL values for many_to_many associations with composite keys" do
  812. + @Album.filter(:ctags=>@Tag.load(:tid1=>3)).sql.should == 'SELECT * FROM albums WHERE \'f\''
  813. + @Album.filter(:ctags=>@Tag.load(:tid2=>4)).sql.should == 'SELECT * FROM albums WHERE \'f\''
  814. + @Album.filter(:ctags=>@Tag.new).sql.should == 'SELECT * FROM albums WHERE \'f\''
  815. + end
  816. +
  817. + it "should be able to handle NULL values when excluding many_to_one associations" do
  818. + @Album.exclude(:artist=>@Artist.new).sql.should == 'SELECT * FROM albums WHERE \'t\''
  819. + end
  820. +
  821. + it "should be able to handle NULL values when excluding one_to_many associations" do
  822. + @Album.exclude(:tracks=>@Track.new).sql.should == 'SELECT * FROM albums WHERE \'t\''
  823. + end
  824. +
  825. + it "should be able to handle NULL values when excluding one_to_one associations" do
  826. + @Album.exclude(:album_info=>@AlbumInfo.new).sql.should == 'SELECT * FROM albums WHERE \'t\''
  827. + end
  828. +
  829. + it "should be able to handle NULL values when excluding many_to_many associations" do
  830. + @Album.exclude(:tags=>@Tag.new).sql.should == 'SELECT * FROM albums WHERE \'t\''
  831. + end
  832. +
  833. + it "should be able to handle excluding with NULL values for many_to_one associations with composite keys" do
  834. + @Album.exclude(:cartist=>@Artist.load(:id2=>4)).sql.should == 'SELECT * FROM albums WHERE \'t\''
  835. + @Album.exclude(:cartist=>@Artist.load(:id1=>3)).sql.should == 'SELECT * FROM albums WHERE \'t\''
  836. + @Album.exclude(:cartist=>@Artist.new).sql.should == 'SELECT * FROM albums WHERE \'t\''
  837. + end
  838. +
  839. + it "should be able to excluding with NULL values for one_to_many associations with composite keys" do
  840. + @Album.exclude(:ctracks=>@Track.load(:album_id2=>4)).sql.should == 'SELECT * FROM albums WHERE \'t\''
  841. + @Album.exclude(:ctracks=>@Track.load(:album_id1=>3)).sql.should == 'SELECT * FROM albums WHERE \'t\''
  842. + @Album.exclude(:ctracks=>@Track.new).sql.should == 'SELECT * FROM albums WHERE \'t\''
  843. + end
  844. +
  845. + it "should be able to excluding with NULL values for one_to_one associations with composite keys" do
  846. + @Album.exclude(:calbum_info=>@AlbumInfo.load(:album_id2=>4)).sql.should == 'SELECT * FROM albums WHERE \'t\''
  847. + @Album.exclude(:calbum_info=>@AlbumInfo.load(:album_id1=>3)).sql.should == 'SELECT * FROM albums WHERE \'t\''
  848. + @Album.exclude(:calbum_info=>@AlbumInfo.new).sql.should == 'SELECT * FROM albums WHERE \'t\''
  849. + end
  850. +
  851. + it "should be able to excluding with NULL values for many_to_many associations with composite keys" do
  852. + @Album.exclude(:ctags=>@Tag.load(:tid1=>3)).sql.should == 'SELECT * FROM albums WHERE \'t\''
  853. + @Album.exclude(:ctags=>@Tag.load(:tid2=>4)).sql.should == 'SELECT * FROM albums WHERE \'t\''
  854. + @Album.exclude(:ctags=>@Tag.new).sql.should == 'SELECT * FROM albums WHERE \'t\''
  855. + end
  856. +
  857. + it "should be able to handle NULL values when filtering multiple many_to_one associations" do
  858. + @Album.filter(:artist=>[@Artist.load(:id=>3), @Artist.new]).sql.should == 'SELECT * FROM albums WHERE (artist_id IN (3))'
  859. + @Album.filter(:artist=>[@Artist.new, @Artist.new]).sql.should == 'SELECT * FROM albums WHERE \'f\''
  860. + end
  861. +
  862. + it "should be able to handle NULL values when filtering multiple one_to_many associations" do
  863. + @Album.filter(:tracks=>[@Track.load(:album_id=>3), @Track.new]).sql.should == 'SELECT * FROM albums WHERE (id IN (3))'
  864. + @Album.filter(:tracks=>[@Track.new, @Track.new]).sql.should == 'SELECT * FROM albums WHERE \'f\''
  865. + end
  866. +
  867. + it "should be able to handle NULL values when filtering multiple one_to_one associations" do
  868. + @Album.filter(:album_info=>[@AlbumInfo.load(:album_id=>3), @AlbumInfo.new]).sql.should == 'SELECT * FROM albums WHERE (id IN (3))'
  869. + @Album.filter(:album_info=>[@AlbumInfo.new, @AlbumInfo.new]).sql.should == 'SELECT * FROM albums WHERE \'f\''
  870. + end
  871. +
  872. + it "should be able to handle NULL values when filtering multiple many_to_many associations" do
  873. + @Album.filter(:tags=>[@Tag.load(:id=>3), @Tag.new]).sql.should == 'SELECT * FROM albums WHERE (id IN (SELECT album_id FROM albums_tags WHERE ((tag_id IN (3)) AND (album_id IS NOT NULL))))'
  874. + @Album.filter(:tags=>[@Tag.new, @Tag.new]).sql.should == 'SELECT * FROM albums WHERE \'f\''
  875. + end
  876. +
  877. + it "should be able to handle NULL values when filtering multiple many_to_one associations with composite keys" do
  878. + @Album.filter(:cartist=>[@Artist.load(:id1=>3, :id2=>4), @Artist.load(:id1=>3)]).sql.should == 'SELECT * FROM albums WHERE ((artist_id1, artist_id2) IN ((3, 4)))'
  879. + @Album.filter(:cartist=>[@Artist.load(:id1=>3, :id2=>4), @Artist.new]).sql.should == 'SELECT * FROM albums WHERE ((artist_id1, artist_id2) IN ((3, 4)))'
  880. + end
  881. +
  882. + it "should be able handle NULL values when filtering multiple one_to_many associations with composite keys" do
  883. + @Album.filter(:ctracks=>[@Track.load(:album_id1=>3, :album_id2=>4), @Track.load(:album_id1=>3)]).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN ((3, 4)))'
  884. + @Album.filter(:ctracks=>[@Track.load(:album_id1=>3, :album_id2=>4), @Track.new]).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN ((3, 4)))'
  885. + end
  886. +
  887. + it "should be able to handle NULL values when filtering multiple one_to_one associations with composite keys" do
  888. + @Album.filter(:calbum_info=>[@AlbumInfo.load(:album_id1=>3, :album_id2=>4), @AlbumInfo.load(:album_id1=>5)]).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN ((3, 4)))'
  889. + @Album.filter(:calbum_info=>[@AlbumInfo.load(:album_id1=>3, :album_id2=>4), @AlbumInfo.new]).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN ((3, 4)))'
  890. + end
  891. +
  892. + it "should be able to handle NULL values when filtering multiple many_to_many associations with composite keys" do
  893. + @Album.filter(:ctags=>[@Tag.load(:tid1=>3, :tid2=>4), @Tag.load(:tid1=>5)]).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN (SELECT album_id1, album_id2 FROM albums_tags WHERE (((tag_id1, tag_id2) IN ((3, 4))) AND (album_id1 IS NOT NULL) AND (album_id2 IS NOT NULL))))'
  894. + @Album.filter(:ctags=>[@Tag.load(:tid1=>3, :tid2=>4), @Tag.new]).sql.should == 'SELECT * FROM albums WHERE ((id1, id2) IN (SELECT album_id1, album_id2 FROM albums_tags WHERE (((tag_id1, tag_id2) IN ((3, 4))) AND (album_id1 IS NOT NULL) AND (album_id2 IS NOT NULL))))'
  895. + end
  896. +
  897. + it "should be able to handle NULL values when excluding multiple many_to_one associations" do
  898. + @Album.exclude(:artist=>[@Artist.load(:id=>3), @Artist.new]).sql.should == 'SELECT * FROM albums WHERE ((artist_id NOT IN (3)) OR (artist_id IS NULL))'
  899. + @Album.exclude(:artist=>[@Artist.new, @Artist.new]).sql.should == 'SELECT * FROM albums WHERE \'t\''
  900. + end
  901. +
  902. + it "should be able to handle NULL values when excluding multiple one_to_many associations" do
  903. + @Album.exclude(:tracks=>[@Track.load(:album_id=>3), @Track.new]).sql.should == 'SELECT * FROM albums WHERE ((id NOT IN (3)) OR (id IS NULL))'
  904. + @Album.exclude(:tracks=>[@Track.new, @Track.new]).sql.should == 'SELECT * FROM albums WHERE \'t\''
  905. + end
  906. +
  907. + it "should be able to handle NULL values when excluding multiple one_to_one associations" do
  908. + @Album.exclude(:album_info=>[@AlbumInfo.load(:album_id=>3), @AlbumInfo.new]).sql.should == 'SELECT * FROM albums WHERE ((id NOT IN (3)) OR (id IS NULL))'
  909. + @Album.exclude(:album_info=>[@AlbumInfo.new, @AlbumInfo.new]).sql.should == 'SELECT * FROM albums WHERE \'t\''
  910. + end
  911. +
  912. + it "should be able to handle NULL values when excluding multiple many_to_many associations" do
  913. + @Album.exclude(:tags=>[@Tag.load(:id=>3), @Tag.new]).sql.should == 'SELECT * FROM albums WHERE ((id NOT IN (SELECT album_id FROM albums_tags WHERE ((tag_id IN (3)) AND (album_id IS NOT NULL)))) OR (id IS NULL))'
  914. + @Album.exclude(:tags=>[@Tag.new, @Tag.new]).sql.should == 'SELECT * FROM albums WHERE \'t\''
  915. + end
  916. +
  917. + it "should be able to handle NULL values when excluding multiple many_to_one associations with composite keys" do
  918. + @Album.exclude(:cartist=>[@Artist.load(:id1=>3, :id2=>4), @Artist.load(:id1=>3)]).sql.should == 'SELECT * FROM albums WHERE (((artist_id1, artist_id2) NOT IN ((3, 4))) OR (artist_id1 IS NULL) OR (artist_id2 IS NULL))'
  919. + @Album.exclude(:cartist=>[@Artist.load(:id1=>3, :id2=>4), @Artist.new]).sql.should == 'SELECT * FROM albums WHERE (((artist_id1, artist_id2) NOT IN ((3, 4))) OR (artist_id1 IS NULL) OR (artist_id2 IS NULL))'
  920. + end
  921. +
  922. + it "should be able handle NULL values when excluding multiple one_to_many associations with composite keys" do
  923. + @Album.exclude(:ctracks=>[@Track.load(:album_id1=>3, :album_id2=>4), @Track.load(:album_id1=>3)]).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN ((3, 4))) OR (id1 IS NULL) OR (id2 IS NULL))'
  924. + @Album.exclude(:ctracks=>[@Track.load(:album_id1=>3, :album_id2=>4), @Track.new]).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN ((3, 4))) OR (id1 IS NULL) OR (id2 IS NULL))'
  925. + end
  926. +
  927. + it "should be able to handle NULL values when excluding multiple one_to_one associations with composite keys" do
  928. + @Album.exclude(:calbum_info=>[@AlbumInfo.load(:album_id1=>3, :album_id2=>4), @AlbumInfo.load(:album_id1=>5)]).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN ((3, 4))) OR (id1 IS NULL) OR (id2 IS NULL))'
  929. + @Album.exclude(:calbum_info=>[@AlbumInfo.load(:album_id1=>3, :album_id2=>4), @AlbumInfo.new]).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN ((3, 4))) OR (id1 IS NULL) OR (id2 IS NULL))'
  930. + end
  931. +
  932. + it "should be able to handle NULL values when excluding multiple many_to_many associations with composite keys" do
  933. + @Album.exclude(:ctags=>[@Tag.load(:tid1=>3, :tid2=>4), @Tag.load(:tid1=>5)]).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN (SELECT album_id1, album_id2 FROM albums_tags WHERE (((tag_id1, tag_id2) IN ((3, 4))) AND (album_id1 IS NOT NULL) AND (album_id2 IS NOT NULL)))) OR (id1 IS NULL) OR (id2 IS NULL))'
  934. + @Album.exclude(:ctags=>[@Tag.load(:tid1=>3, :tid2=>4), @Tag.new]).sql.should == 'SELECT * FROM albums WHERE (((id1, id2) NOT IN (SELECT album_id1, album_id2 FROM albums_tags WHERE (((tag_id1, tag_id2) IN ((3, 4))) AND (album_id1 IS NOT NULL) AND (album_id2 IS NOT NULL)))) OR (id1 IS NULL) OR (id2 IS NULL))'
  935. + end
  936. +
  937. end
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!