Jeremy, Still having issues with these….and need some suggestions.
On Aug 20, 2013, at 1:30 PM, Jeremy Evans <[email protected]> wrote: > On Monday, August 19, 2013 12:42:59 PM UTC-7, GregD wrote: > Jeremy- > > That worked. Thanks! Updated the gist: > > I have 6 remaining dataset tests that are failing. These are the ones I have > no clue about: > > rspec ./spec/integration/dataset_test.rb:1332 # Sequel::Dataset DSL support > should work empty arrays with nulls > > Generated SQL does not work and not sure what is should look like: > > "SELECT TOP 1 (\"A\" != \"A\") AS \"V\" FROM \"A\" ORDER BY \"A\"" > > 1) Sequel::Dataset DSL support should work empty arrays with nulls > Failure/Error: pr[@ds.get(Sequel.expr(:a=>[]))].should == nil > Sequel::DatabaseError: > Sequel::SqlAnywhere::SQLAnywhereException: Syntax error near '!=' on > line 1 > > > Sybase uses <> instead of != for not equal. You probably need to handle this > inside Dataset#complex_expression_sql_append. We should probably add an > integration test that specifically tests for not equal. This is not true. != is supported by SQLAnywhere, but not in the selection columns so it is the whole expression (A != A) should either be in the where or needs to be in an if statement that eval to 1 or 0 since SQLAnywhere does not have TRUE or FALSE election criteria. At least this is what I'm basing it on with my experiences and reading the Sybase docs. > > ------ > rspec ./spec/integration/dataset_test.rb:1349 # Sequel::Dataset DSL support > should work empty arrays with nulls and the empty_array_ignore_nulls extension > > Generated SQL is using (1=0) for the FALSE. I thought I took care of that > with the BOOL_FALSE = 0 > > "SELECT TOP 1 (1 = 0) AS \"V\" FROM \"A\" ORDER BY \"A\"" > > 1) Sequel::Dataset DSL support should work empty arrays with nulls and the > empty_array_ignore_nulls extension > Failure/Error: pr[ds.get(Sequel.expr(:a=>[]))].should == false > Sequel::DatabaseError: > Sequel::SqlAnywhere::SQLAnywhereException: Syntax error near '=' on > line 1 > > Microsoft SQL Server can't do this either. You probably need to update the > Sequel.guarded? call in that spec. I went ahead and added it to the guarded, but this is the same as above. > > ---- > rspec ./spec/integration/dataset_test.rb:1459 # Dataset string methods #like > should be case sensitive > > SQL looks correct. But, Sybase SQLAnywhere is case insensitive by default, I > believe. Well, we don't set the case sensitivity when we build a DB. How do > I make this work vs the ilike in the SQL? > > "SELECT * FROM \"A\" WHERE (\"A\" LIKE 'Foo' ESCAPE '\\') ORDER BY \"A\"" > > 1) Dataset string methods #like should be case sensitive > Failure/Error: @ds.filter(Sequel.expr(:a).like('Foo')).all.should == [] > expected: [] > got: [{:a=>"foo", :b=>"bar"}] (using ==) > Diff: > @@ -1,2 +1,2 @@ > -[] > +[{:a=>"foo", :b=>"bar"}] > > You'll have to figure out a way to do a case insensitive LIKE. Microsoft SQL > Server does this by using a case insensitive collation. If it is really not > possible, then you'll have to add a guard to the spec. Looking through the Sybase docs and searching the net, it looks like this could be nasty to figure out. When doing an '=', a cast the char to binary will work to make those case sensitive. But the like is really gong to be a challenge. Collation is done when creating the database not when creating a table and there is nothing like what MSSQL server has in a select statement. I tried REGEXP in their client (dbisql) and it seems to work. The REGEXP behavior matches Perl 5 according to the docs. I could translate the wild cards of '_' to '.' and '%' to '.*'. Do you see any problem doing that and using REGEXP. SqlAnywhere does have SIMILAR TO but that behave like LIKE. Also, there is a COMPARE, that can collate the data and then return 1, 0, -1 but I think that would not work as well as the REGEXP. > > > --- > rspec ./spec/integration/dataset_test.rb:1479 # Dataset string methods > #escape_like should escape any metacharacters > > Multiple backslashes (escapes) in the SQL is not working real well with > SQLAnywhere. I actually had to break apart 1 test that had multiple strings > it tested to ones that worked and ones the did not putting those in cspecify. > Do I need to do the same for this one. > > "SELECT \"B\" FROM \"A\" WHERE (\"B\" LIKE 'bar\\\\\\%' ESCAPE '\\') ORDER BY > \"B\"" > > 1) Dataset string methods #escape_like should escape any metacharacters > Failure/Error: > @ds.filter(Sequel.expr(:b).like(@ds.escape_like('bar\\%'))).select_order_map(:b).should > == ['bar\\%'] > expected: ["bar\\%"] > got: ["bar\\%", "bar\\.."] (using ==) > > You probably need to override Dataset#escape_like so that it uses the correct > escaping. I am assuming it is possible for this case to be handled correctly. > I have not messed with this one yet. > --- > rspec ./spec/integration/spec_helper.rb:77 # Common Table Expressions should > give correct results for recursive WITH > > After adding the RECURSIVE statement to the WITH. Got these to start to work > other than this one. > > "WITH RECURSIVE \"T\"(\"I\", \"PI\") AS (SELECT * FROM \"I1\" WHERE > (\"PARENT_ID\" IS NULL) UNION ALL SELECT \"I1\".\"ID\", \"I1\".\"PARENT_ID\" > FROM \"I1\" INNER JOIN \"T\" ON (\"T\".\"I\" = \"I1\".\"PARENT_ID\")) SELECT > \"I\" AS \"ID\", \"PI\" AS \"PARENT_ID\" FROM \"T\"" > > 1) Common Table Expressions should give correct results for recursive WITH > Failure/Error: Unable to find matching line from backtrace > expected: [{:parent_id=>nil, :id=>1}, {:parent_id=>nil, :id=>2}, > {:parent_id=>1, :id=>3}, {:parent_id=>1, :id=>4}, {:parent_id=>3, :id=>5}, > {:parent_id=>5, :id=>6}] > got: [{:id=>1, :parent_id=>nil}, {:id=>2, :parent_id=>nil}, > {:id=>4, :parent_id=>1}, {:id=>3, :parent_id=>1}, {:id=>5, :parent_id=>3}, > {:id=>6, :parent_id=>5}] (using ==) > Diff: > @@ -1,7 +1,7 @@ > -[{:parent_id=>nil, :id=>1}, > - {:parent_id=>nil, :id=>2}, > - {:parent_id=>1, :id=>3}, > - {:parent_id=>1, :id=>4}, > - {:parent_id=>3, :id=>5}, > - {:parent_id=>5, :id=>6}] > +[{:id=>1, :parent_id=>nil}, > + {:id=>2, :parent_id=>nil}, > + {:id=>4, :parent_id=>1}, > + {:id=>3, :parent_id=>1}, > + {:id=>5, :parent_id=>3}, > + {:id=>6, :parent_id=>5}] > > Looks like the hash is in the wrong order. I didn't think an array of hashes > comparison would result in this failure. I don't think I'm sorting them as a > fetch them. > > The spec should be modified to use order(:id, :parent_id) to ensure a > consistent order Fixed the spec. Kinda surprised that this was working for all the other adapter types without that order statement. BTW: When I started this I had almost all the integration tests failing. I went ahead and ran them all and I'm below 70 failing now. I'm working through the schema ones now and also getting the ones that were skipped because I did not have some of the database methods implemented to allow those to run. A few of failures have to do with ALTER TABLE statements syntax errors and FOREIGN KEY exceptions in Sybase. I'll probably have to send any of those to this group as a tackle them and if I can't figure out an approach. Again thanks for the help. -GregD -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/groups/opt_out.
