Redmine

Yes I am aware. It has been working good up until 2.0 but I think the adapter has a bug in it so I will open a case with them.

Jason Trahan wrote:

Yes I am aware. It has been working good up until 2.0 but I think the adapter has a bug in it so I will open a case with them.

I'm curious about the amount of work you had to go before having Redmine fully working with SQL server, if you don't mind?

Virtually none. I had to manually change 1 thing in the database due to a bug in the adapter not converting an int to an nvarchar, but for redmine itself i didn't have to make any changes.

However something with the adtapter is generating this sql statement which is invalid because the order by has the same column twice sorting by both directions which is invalid.

EXEC sp_executesql N'SELECT TOP (1) [enumerations].* FROM [enumerations] WHERE (type = ''DocumentCategory'') ORDER BY enumerations.position ASC, position DESC'

Should be avoidable by tweaking source:/tags/2.0.0/app/models/enumeration.rb#L21 and source:/tags/2.0.0/app/models/enumeration.rb#L27.

Commenting out those 2 lines does seem to help with the data load. I will put them back in and see how normal operation is.

Actually I'm not even sure that the act_as_tree is useful??

Right now I don't get the purpose of r2947.

Actually that act_as_tree seems to be the culprit. If I comment out that line instead of the default_scope it works.

correction. once I truncated the tables and tried again it still errored out. Weird thing is that the default_scope has existed since 2009 and has worked so I am thinking some other change was made that is causing the adtapter to generate both order bys.

if I remove default_scope then the sql statement comes out to this below which is correct.

EXEC sp_executesql N'SELECT TOP (1) [enumerations].* FROM [enumerations] WHERE (type = ''DocumentCategory'') ORDER BY position DESC'

Opened a ticket here too to see if they say if this is an adapter issue or not.

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/203

Based on this comment. https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/203#issuecomment-5761927

I modified subversion.rb and changed

  def latest_changesets(path, rev, limit=10)
    revisions = scm.revisions(path, rev, nil, :limit => limit)
    revisions ? changesets.find_all_by_revision(revisions.collect(&:identifier), :order => "committed_on DESC", :include => :user) : []
  end

to

  def latest_changesets(path, rev, limit=10)
    revisions = scm.revisions(path, rev, nil, :limit => limit)
    revisions ? changesets.find_all_by_revision(revisions.collect(&:identifier), :order => "#{Changeset.table_name}.committed_on DESC", :include => :user) : []
  end

I imagine this will need to be done in various places to make it more compliant with mssql. I'm still not sure why this became a problem when I upgraded to 2.0.0 and the latest sql adapter because in 1.4.x it was working without any changes.

Etienne Massip wrote:

I'm curious about the amount of work you had to go before having Redmine fully
working with SQL server, if you don't mind?

I inherited a Redmine 1.0.4 database from another developer. He said he'd had issues getting dates to work, though that was with ODBC and the description of TinyTDS sounds like it may have solved that. He never got the Gantt view working, but it does now which backs that up.

Aside from this sorting issue, the only other one I've encountered is that any attempt to sort or group by a custom field will fail. Filtering is fine. The problem is that the SQL includes a LIMIT clause which MSSQL doesn't recognise. Maybe that's the driver too? Our workaround is to point Excel at the database to get those queries.

A simple fix to that is to open a ticket with the guy who writes the adapter and ask him to convert the limit statements to top statements. He does other conversions now with the adapter.

OK, I'll get some evidence together tomorrow and open a ticket. Thanks Jason.