I'm implementing a Ruby on Rails application which i developed with Sqlite3 to some server with either MySQL or PostgreSQL. I rapidly learned that the "group by" and "strftime" functions that i'm using heavily to create by-month rollup reviews will work in a different way or otherwise compatible between your various databases.

I'm able to refactor my code to complete the grouping, summing and calculating - however the database does this type of nice job from it and cuts down on the processing needed through the server! Advanced programs exceed simple choose and join. ActiveRecord provides for us :group, however the DATABASES aren't CONSISTENT.

So my real question is dependent on architecture - does anybody be prepared to create truly "database portable" programs in Ruby on Rails? Must I modify my codebase to utilize MySQL only and overlook the other databases? Must I modify my codebase to complete the advanced grouping, summing, and calculating?

cheers - Don

Several comments:

  • Develop and test with similar RDBMS brand and version that you are likely to deploy to.

  • Writing portable SQL code is difficult because suppliers have these non-standard extra features and functions. For instance, strftime() isn't area of the ANSI SQL standard. The only method to resolve this really is to RTFM for every database you utilize, and learn what functions they share. Sometimes there is a purpose of another title which you can use similarly. There is no short-cut for this -- you need to study the manuals.

  • All of the databases support GROUP BY, but SQLite and MySQL are type of more permissive about certain usage than standard ANSI SQL (and all sorts of other brands of database that stick to the standard). Particularly, inside your GROUP BY clause you have to title every column inside your choose-list that is not a part of a grouping function.

    The next two good examples are right:

    SELECT A, B, COUNT(C) FROM MyTable GROUP BY A, B;
    SELECT A, COUNT(C) FROM MyTable GROUP BY A;
    

    But the next is wrong, because B has multiple values per group, and it is ambiguous which value it will return inside a given row:

    SELECT A, B, COUNT(C) FROM MyTable GROUP BY A;
    
  • No framework creates truly portable SQL. Rails' ActiveRecord solves this only in very trivial cases. Actually, ActiveRecord helps solve neither from the good examples you allow, of functions which are brand-specific, and non-standard GROUP BY clauses.

However , particularly with GROUP BY MySQL will it wrong. Should you omit posts in the group by MySQL simply returns "something" accepting the results might be indeterminate.

You are able to (should) make use of the ONLY_FULL_GROUP_BY parameter to create MySQL throw a mistake if caused by your GROUP BY wouldn't be clearly defined.

Really you will find much more configurations that needs to be transformed in MySQL to really make it behave more sanely

You may be thinking about reading through this:

http://www.slideshare.net/ronaldbradford/mysql-idiosyncrasies-that-bite-201007