Datamapper DataMapper

Comments and changes to this ticket

• 

  Dan Kubb (dkubb) April 29th, 2009 @ 12:39 AM

  I would certainly love to see an SQL Server DO driver.

  I'm not a C coder, so my ability to contribute to the DO driver itself is limited, but I would be willing to write the DataMapper adapter after the DO driver is passing the shared specs.

  If you're interested in writing this driver, please hop onto #dm-hacking IRC on freenode.net. See dbussink or myself (dkubb) and we'll help you get up to speed.

• 

  Dirkjan Bussink April 29th, 2009 @ 01:41 AM

  I can help you out with the Ruby C parts for a driver, but I don't really have any experience with MS SQL Server. Will you be targeting linux or unix first? I've just been working on getting our current drivers working on Windows, so I have some experience there too.

  Please come by in #dm-hacking on freenode.net to talk a bit about it. I'm usually around during CET / CEST times (Western Europe).

• You flagged this item as spam.
  

  ghouston (at yahoo) April 29th, 2009 @ 08:18 AM

  My goal is to use Data Mapper with SQL Server 2005 and 2008 on the Windows platforms (XP, 2003, 2008, Vista, Win7). Initially I plan to use DataMapper with FactoryGirl to test legacy databases.

  It has been 11 years since I've done anything significant in C. So I wont be much help writting C. I can do anything needed in Ruby.

  Is it possible to write an adapter completely in Ruby? Maybe we could use the DBI driver with an ODBC connection. That might let us get an adapter working quickly. Then the C extension could be added later.

  I'll get on #dm-hacking during my day (Central US time GMT-6) which is your afternoon.

• 

  Dan Kubb (dkubb) April 29th, 2009 @ 11:18 PM

  @ghouston: Dirkjan is the authority on DO, but I would definately support a pure ruby version of any driver be included in DO, provided it met with the public/semipublic API that the other DO drivers follow.

  At the moment there are no pure Ruby drivers in DO, so we haven't really established any conventions on how they should be packaged, but that really is a minor detail to work out.. I would suggest focusing on writing something that passes the shared specs dbussink has put into DataObjects in the do/next branch on github.

  I don't have any specific implementation advice to give, not having used the DBI ODBC drivers before, but I would suggest to keep the dependencies to a minimum, and if you can stay as close to pure Ruby as possible. I would also prefer it if the driver was not tied to any specific platform .. one of DO's primary goals is cross platform drivers.

  We have a bit of a chicken and egg problem right now. No serious users of SQL Server are using DM because there's no drivers, but there's no drivers because they can't use DM. If we had something to provide, even as a starting point, then we would be in a better position to attract someone to the project who would be interested in writing the C based driver.

• You flagged this item as spam.
  

  Clifford Heath May 8th, 2009 @ 10:08 PM

  I've forked DO, added base API documentation, and am making a small start on an SQL server adapter. Will see how I go, pulling code from the new AR adapter.

• You flagged this item as spam.
  

  Clifford Heath May 12th, 2009 @ 04:39 AM

  http://github.com/cjheath/do/tre... contains a working do_sqlserver adapter that passes all the inherited DataObjects tests - not yet tested wih DataMapper

• You flagged this item as spam.
  

  Clifford Heath May 12th, 2009 @ 07:48 PM

  The matching DataMapper adapter for Sqlserver is now available in http://github.com/cjheath/dm-cor...

• 

  Dan Kubb (dkubb) May 13th, 2009 @ 12:41 AM

  • State changed from “unconfirmed” to “confirmed”

  @Clifford: How does the DM SQL server do with the dm-core specs? If you get any errors I'd love to see a stack trace.

  Also, I noticed you use backticks for quoting identifiers, like table and column names. I thought I saw somewhere that MS SQL server was using square brackets for quoting. I was just curious if there was a standard, or if it differed for different versions of MS SQL?

  Can MS SQL support double quotes for quoting, like the ANSI spec? Is there any downside to doing this. When given the choice, assuming there is no penalty, I prefer for the SQL DM generates to be as close to the ANSI spec as possible.

• You flagged this item as spam.
  

  Clifford Heath May 13th, 2009 @ 09:37 PM

  @dkubb The DataObjects adapter uses square brackets. I overlooked to change the quote_name method in copying the DataMapper adapter from Mysql. Double-quote is also supported, though I wasn't certain whether it is version-specific or configuration-specific, so I left it with the historical quoting. I've fixed it now, and it seem to work fine.

  The dm-core sqlserver_adapter_spec.rb passes cleanly (40 examples, zero failures), despite the backtick problem - quote_name obviously isn't actually being tested there.... With a few new updates, I have many fewer failures, mainly LIMIT and COUNT which are in shared code (not adapters).

• 

  Dan Kubb (dkubb) May 14th, 2009 @ 02:16 PM

  @Clifford: Actually, quote_name would've been used to quote every table and column name in every query, so it was probably being exercised before. From what I could read, backticks can be used for quoting in limited cases.

  It's also fine that do_sqlserver is using square brackets for now. I'm working on getting a contact person inside MicroSoft, to get some MS SQL license to run on a test server, and I'm sure I can find out if ANSI quoting is supported by default or if it requires a configuration change. Obviously we don't want to force people to reconfigure their servers just to use DO, so we'll end up using whatever is supported by default.. with a preference for ANSI compliant syntax of course.

• You flagged this item as spam.
  

  Ben Lovell May 14th, 2009 @ 02:26 PM

  You can get fully functional trial editions here:

  2005 - http://www.microsoft.com/sqlserver/2005/en/us/trial-software.aspx
  2008 - http://www.microsoft.com/sqlserver/2008/en/us/trial-software.aspx

  Or the express edition here:

  http://www.microsoft.com/express/sql/register/

• 

  Dan Kubb (dkubb) May 14th, 2009 @ 05:43 PM

  @Ben: Awesome, thanks. That'll let me try things out in the interim.

  Over the long term though we'll need real licenses so we can make sure the DO driver is tested properly as part of the (still to be done) CI system. At minimum the CI system must:

  • Run the specs for every commit to dm-core or any dm-more gem using every "spec compliant" DM adapter.

  • Handle dependencies, so that commits to dm-core will also run the specs for every dm-more gem. since all of dm-core and dm-more is dependent on the DO drivers, we'd want to do a full run each time a DO change was made.

  • Work with multiruby so we can test against ruby 1.8.6, 1.8.7, 1.9.1 and JRuby.

  • Aggregate results from multiple distributed computers. We need to ensure the DO code compiles and runs on every supported platform, not just OSX and Linux. Also in the case of the MS SQL driver, we need to be able to run the specs on a Windows machine, and then communicate the results somewhere.

  I'm still not sure if any of the open source CI systems lik CruiseControl.rb or Integrity support these basic requirements. If not, we'll probably need to write something simple to handle this.

• You flagged this item as spam.
  

  Clifford Heath May 14th, 2009 @ 06:17 PM

  @dkubb: 1) The current code in my github repos uses double-quotes.
  2) As the do_sqlserver starts up, it queries (and can set) the 'quoted_identifier' configuration variable.
  3) We need to run CI on more than one OS platform - but to test sqlserver from Linux, you need two (virtual?) machines, one running Linux with FreeTDS and one running Windows. Just an annoying feature of the required testing environment...

  @ben: 4) The "fully functional" versions time out after 180 days. Not a good option for a CI system.
  5) The express edition is the right answer if you don't need the graphical tools, as we don't.
  6) There isn't an SQL Server 2000 version still available, but that should be still supported.

• You flagged this item as spam.
  

  ghouston (at yahoo) May 15th, 2009 @ 09:05 AM

  @dkubb: For CI systems, I recommend Hudson. It is very easy to setup and run. Hudson integrates very well with Rake. Hudson can run and manage builds across multiple platforms by using agents on the different machines that report back to the Hudson server. I've found Hudson to be the easiest opensource CI tool, and very powerful. https://hudson.dev.java.net/

  @clifford: I would recommend focusing on SQL 2005 and SQL 2008. SQL 2000 is no longer in mainstream support by Microsoft. Also, there are significant differences between SQL 2000 and the later versions. Supporting SQL 2000 would help anyone on that legacy database, but I wouldn't let that slow down support for SQL 2005 and later. If you have to, I'd save SQL 2000 support for last; or separate it into a different adapter and data object.

• You flagged this item as spam.
  

  ghouston (at yahoo) May 15th, 2009 @ 10:22 AM

  @dkubb and @ben: The Express editions of SQL 2005 and SQL 2008 are good enough to cover the functionality of DataMapper. You shouldn't need any features from the other versions.

  The express edition doesn't include the GUI tool Management Studio. If you need the front-end, you can install Management Studio Express 2008 (it will work with SQL Server 2005 as well) from http://www.microsoft.com/downloads/details.aspx?displaylang=en&...

• 

  ghouston (at yahoo) June 12th, 2009 @ 01:09 PM

  • no changes were found...

  • fix_do_sqlserver_massage_limit_and_offset.diff 1.9 KB

• You flagged this item as spam.
  

  ghouston (at yahoo) June 12th, 2009 @ 01:09 PM

  I've fixed an issue in do_sqlserver.rb. The massage_limit_and_offset method was not generating t-sql correctly. This was causing #find to fail with an error. A patch is attached.

• You flagged this item as spam.
  

  Clifford Heath June 15th, 2009 @ 11:40 PM

  @ghouston can you describe what the previous code was doing wrong? Your patched version doesn't work on SQL Server 2000, and I'd rather not drop support for it if possible... nice though the row_number() solution is.

• You flagged this item as spam.
  

  ghouston (at yahoo) June 17th, 2009 @ 01:55 PM

  On Sql2005 the previous code was throwing an error "Incorrect syntax near the keyword 'ORDER'." I looked at the SQL generated, even if the syntax was corrected, the approach would have returned the wrong rows (the reverse ordering was throwing it off).

  I don't know of a good simple solution for Sql2000. The most common solution for Sql2000 involves capturing the data into a temporary table and adding a count() column. Then selecting the final results from the temp table. But the performance and memory consumption isn't good. I've seen other solutions that used cursors to iterate through the data; again with poor performance.

  This is one example why I recommended separating Sql2000 support from Sql2005/2008. There are significant changes between 2000 and 2005. A solution that works for 2000 will be crippling in 2005. Make a do_sqlserver2000 if you really need 2000 support.

  I'm attaching an updated patch. This one still only supports Sql 2005 and later. I've include an update to do_sqlserver's command spec to demonstrate the problem and test the solution. The new patch will also use SELECT TOP x when appropriate. That will improve the performance when using #first in DataMapper.

  • patch.diff 7.3 KB
• 

  Jonathan Stott (namelessjon) October 9th, 2009 @ 09:30 AM

  • State changed from “confirmed” to “hold”

  I'm setting this to hold, because to the best of my knowledge the SQL server adapter now works. Could someone with more knowledge of the situation confirm this is the case (in which case the ticket can be closed) or reopen it?

• 

  Alex Coles October 12th, 2009 @ 03:03 PM

  • Assigned user set to “Alex Coles”
  • State changed from “hold” to “accepted”

  There is a DataObjects SQL Server Driver. As of today, all but one spec (handling ByteArrays) pass on JRuby. On MRI, the driver is built on top of DBI/DBD ODBC/RubyODBC/FreeTDS. Unfortunately, I haven't got too far as I'm hitting some segfaults on Snow Leopard (AR's SQL Server Adapter uses the same dependencies and I gather from some Googling others have experienced similar issues). I'll take another look when I can. In the meantime, to reproduce see the INSTALL file

  cjheath also did work on a corresponding DataMapper SQL Server. I've got a few patches to push to get more specs passing (a few things have changed in the DM API since the summer).

• 

  Dan Kubb (dkubb) October 23rd, 2009 @ 03:58 AM

  @Alex: Is this ticket complete now that we've merged in do_sqlserver support into dm-core?

• 

  Alex Coles October 23rd, 2009 @ 04:21 PM

  @dkubb I don't know if we should mark it as closed. Support is in DataObjects and DataMapper for SQL Server, but I've only verified it as
  working (all tests passing) on JRuby. Our support on 1.8.6/7 (and
  theoretically, 1.9) relies on dbd-odbc, dbi, ruby-odbc and the FreeTDS
  libraries. In theory, it should be more or less there, but I've yet to get a
  working setup (I'm hitting a BusError in Ruby when connecting using the
  above libraries) to test against.

• 

  Dan Kubb (dkubb) October 23rd, 2009 @ 09:30 PM

  Ah, my mistake. You are correct, we should keep this ticket open. I wrongly assumed that the DO driver was working with MRI too.

• 

  Alex Coles October 28th, 2009 @ 09:41 AM

  No problem. I've been keeping the following file up-to-date with what's
  working:
  http://github.com/datamapper/do/blob/next/README.markdown

  Finally got my environment up and running to test the MRI implementation of
  the driver (based on the patches from cjheath). Its seeing quite a few
  failures atm - so still a little more work before we can close this ticket.

• You flagged this item as spam.
  

  ronin-96121 (at lighthouseapp) April 22nd, 2010 @ 02:53 AM

  ODBC Manager and associated ODBC library for OSX Snow Leopard was crashing for me. unixODBC package (via MacPorts) worked well for me, though. Check that is not the source of your bus errors or crashing.

  $ sudo port install freetds +odbc

  Driver = /opt/local/lib/libtdsodbc.so Setup = /opt/local/lib/libtdsodbc.so

  (Setting up a test database to run specs against now.)