SQL Database Compatibility

Question

I am deploying a Ruby on Rails application that I developed with Sqlite3 on a server with MySQL or PostgreSQL. I quickly found that the "group by" and "strftime" functions that I use to report monthly collapse work differently or are not compatible between different databases.

I can reorganize my code for grouping, summing and averaging, but the database does such a good job and reduces the processing required by the server! Advanced applications go beyond simple selection and integration. ActiveRecord gives us: group, but the DATABASES DO NOT AGREE.

So my question is the architecture question - does anyone expect to create truly “portable” application databases in Ruby on Rails? Should I modify my code base only for working with MySQL and forget about other databases? Do I have to change my code base to do advanced grouping, summing and averaging?

Greetings - Don

Answer 1

A few comments:

• Design and test with the same brand and version of RDBMS that you intend to deploy.

• SQL , . , strftime() ANSI SQL. - RTM , . , . - .

• GROUP BY, SQLite MySQL , ANSI SQL ( , ). , GROUP BY , .

  :

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

  , B , :

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

• - SQL. Rails ActiveRecord . , ActiveRecord , , , GROUP BY.

Answer 2

, GROUP BY MySQL . MySQL, "-", , .

() ONLY_FULL_GROUP_BY, MySQL , GROUP BY .

, MySQL,

:

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