Polyglot queries

[the8472]

Is there a way to support multiple SQL derivates that have different syntax for some functions? Arel takes care of mapping the standard SQL syntax to RDBMs-specific stuff, but their functions (e.g. string and date processing) also vary.

E.g. i want to do a Foo.where(:bar => "baz").group{startofmonth(created_at)}. Of course start of month needs to be mapped to different function calls in e.g. sqlite and mysql.

So a way to add custom functions as wrappers for native ones either in squeel or arel would be useful.

[ernie]

Interesting idea, but I'm not entirely sure how useful it would be in practice. If there isn't a 1:1 correspondence between both the functions and their parameter order, then the abstraction quickly breaks down and the user incurs a lot of overhead in configuring the mapping.

Given that, it seems like it'd be much simpler to just change the code if you change your DB.

Thoughts?

[the8472]

Given that, it seems like it'd be much simpler to just change the code if you change your DB.

We're trying to keep queries as database-agnostic as possible, squeel really helps with that since we don't have to write the predicates or joins as strings. So if something really is database specific we would like to keep it in a central location that can be easily updated or extended instead of going through the whole application and re-writing queries.

The mapping for my example would be

sqlite: startofmonth(column) -> date(column,'start of month')
mysql: startofmonth(column) -> extract(year_month from column)
postgres: startofmonth(column) -> date_trunc(month, column)

[the8472]

Note that Hibernate (a java SQL abstraction/ORM) has similar functionality, you can define custom SQL dialects and map HQL functions -> SQL strings. http://docs.jboss.org/hibernate/core/4.1/javadocs/org/hibernate/dialect/function/SQLFunction.html

[pdf]

Given that, it seems like it'd be much simpler to just change the code if you change your DB.

There's the case to consider of applications where the choice of backing database isn't mandated, like open projects where the user may choose their preferred database.