Jeremy- Down to 3 things to fix and this should be ready to implement into Sequel:
1) native disconnect - should I free the sqlany resources during a disconnect or is there a way to remove the connection from the pool? 2) jdbc and native - setting up a composite foreign key without an exception and then dropping a foreign key causing a duplicate alter drop of the same key. 3) jdbc - smallint not converting to a boolean. responses below... On Sep 10, 2013, at 3:55 PM, Jeremy Evans <[email protected]> wrote: > On Tuesday, September 10, 2013 12:09:53 PM UTC-7, GregD wrote: >> >> >> Failures: >> >> 1) RcteTree Plugin should not populate :children associations for final >> level when loading descendants to a given level >> Failure/Error: nodes[0].associations[:children].map{|c1| >> c1.associations[:children]}.should == [nil, nil] >> expected: [nil, nil] >> got: [[], []] (using ==) >> Diff: >> @@ -1,2 +1,2 @@ >> -[nil, nil] >> +[[], []] >> # ./spec/integration/plugin_test.rb:858:in `block (2 levels) in <top >> (required)>' >> >> I don't even know where to begin to figure out what is going on with this. >> Very complicated recursion sql is generated and looks valid (works in >> dbisql). Something to do with the eager method, maybe? Although all the >> other tests with eager are working. >> >> You might want to add some debugging code to the `if level` block near line >> 297 of the plugin. It should be setting no_cache to true for the >> grandchildren, but if for some reason the dataset is returning a level value >> that is not equal to 1, it will be false, so the associations hash in the >> grandchildren will be initialized to [] in line 307. You could try changing >> `obj.values.delete(la)` to `obj.values.delete(la).to_i` on line 297 and see >> if that fixes it. > > I put in debug statements, the value being compared to is false and not 0. > This works with the jdbc adapter version because the value is 0. This is the > whole boolean thing. Sybase does not have a boolean type. I'm having it use > smallint. I've looked through others that have this problem too. Tried to > follow them and not sure where the to fix it throughout both native and jdbc. > > Maybe we can try adding an explicit cast to integer in the SQL, so that it > doesn't use smallint implicitly, and would therefore have the values returned > as integers and not converted to booleans. Around line 285 in the plugin, > doing this: > > base_case = base_case.select_more(SQL::AliasedExpression.new(Sequel.cast(0, > Integer), la)) That obviously fixed it. Surprised another adapter that uses smallint as boolean did not have this problem. Probably sqlanywhere will default a FIxnum type to the lowest native integer type that can hold the value if a type has not been declared or cast. > > > >> >> The pending ones are with reasons: >> Pending: >> Sequel::Database should provide ability to check connections for validity >> # Not yet working on sqlanywhere >> # ./spec/integration/spec_helper.rb:75 >> >> It looks like the sqlanywhere native gem acts just like the sybase client >> software dbisql and sybase central where if you disconnect and then issue an >> SQL statement it auto reconnects you to your last connection sending back >> the results. >> >> Yuck. Is there no way to turn that off? That's a pretty horrible idea. >> Think starting a transaction and having a disconnect happen midway through. >> With transparent auto reconnection, Sequel thinks you are inside a >> transaction, but SQLAnwhere is outside a transaction. That's not a good >> situation to be in. > > I'm not sure the test is really testing a true lost connection. I know if > your in the client (dbisql) and start any long transaction and a lost > connection happens like the database goes down, the transaction fails and is > rolled back. However, if you issue another sql statement in dbisql it tries > a new connection automatically using the last connection's parameters. If it > it does connect, it does issue the new sql. In the test, it is issuing a > disconnect, which I issue the native C ext drivers disconnect on that > connection and then it issues a select 1 or select null. It looks like the > native sqlanywhere C ext driver is doing what the dbisql client software is > doing trying a reconnect before issuing the new sql select. I certainly can > do more than the sqlany disconnect on the Sequel disconnect, like free and > release the resources api that probably would result in the failure. But, > I'd probably have to do something to re-initialize all of that on a > re-connect. > > The idea here is that if you can detect a disconnect, you have the adapter > raise a Sequel::DatabaseDisconnectError. Then Sequel will remove that > connection from the pool, and a new connection will be created on the next > query. I'll take a closer look at others for this. > > >> >> Bound Argument Types should handle blob type with embedded zeros >> # Not yet working on sqlanywhere >> # ./spec/integration/spec_helper.rb:75 >> >> Is there a way to hex-encode the blobs to allow this? > > Not that I can tell. The problem lies in the C ext gem sqlanywhere I am > using and the StringValueCStr it is using. It does not allow ascii NUL char > in the string. This works in JDBC. > > I'd just mark the test pending for the native sqlanywhere driver then. > >> >> All of these have to do with escaping characters and the native sqlanywhere >> gem use of StringValueCStr and there is no other method to call that does >> not use that on the sql string that is passed in. I split the escape >> backslash strings into 2 tests. On that worked for sqlanywhere and the ones >> that did not putting them into a cspecify. I called it escape multiple >> backslash strings. >> >> Ah, that's why I couldn't find it. Anyway, you might want to take another >> look and convince yourself 100% that SQL injection is not possible in that >> case. > > The multiple backslashes is not working for either native or the JDBC. I'm > not sure why. I'll have to see the generated SQL again and try it in their > client, dbissql. > > OK. I think this is probably the most pressing issue, since getting it wrong > can cause security issues. > All of the Blob and escaping is fixed. I had to do what the old mysql adapter did in the dataset with: # SQL fragment for String. Doubles \ and ' by default. def literal_string_append(sql, v) sql << APOS << v.gsub(BACKSLASH_RE, QUAD_BACKSLASH).gsub(APOS_RE, DOUBLE_APOS) << APOS end # SqlAnywhere uses a preceding X for hex escaping strings def literal_blob_append(sql, v) sql << BLOB_START << v.unpack(HSTAR).first end > >> >> I can set up a JDBC sqlanyhere adapter. That should not take too long to do >> and see if these can pass. >> >> That would be great. >> >> I would definitely like to get this merged into Sequel when you have >> finished. Ideally I would have a local SQLAnywhere installation in a VM >> that I could use for testing (to make sure future Sequel versions don't >> break SQLAnywhere support). If that's something you could walk me through, >> I would appreciate it. However, even if there are things preventing me from >> testing it locally, I still think it would be beneficial to merge it. > > I can do that and provide the DB init commands and init.d script to bring the > DB up. I used an Ubuntu VM on a Windows box. I think Sybase provides a > developers version. I would either use a linux VM or the OSX version of > SqlAnywhere. I really don't like working with Ruby on a Windows box hence > why I'm using an Ubuntu VM since the place I'm working at is a Windows/Java > shop and only provide Windows hardware. > > OK. I'll try to get an environment set up locally, but I may not have time > until October. > > > Here is the JDBC run. And you will notice the failing tests are around > booleans/smallint conversions. I don't know how to fix these, yet. Do you > suggest any existing adapter to look at? > > If the JDBC driver doesn't have a flag that you can set to handle smallint as > boolean, you'll probably need to just mark the tests as pending on [:jdbc, > :sqlanywhere] > I saw that the cubrid adapter is using smallint as boolean and its jdbc adapter is not making these pending. So this leads me to think I'm not setting something up right for my jdbc. Thanks, -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.
