Support for hand-written SQL?

[Graham Tackley]

Does Squeryl support execution of custom hand written SQL?

This, of course, kind of defeats the point of Squeryl, but we find that in performance sensitive applications the vast majority (98%+) of queries can be generated, but there's always a performance bottleneck on a few queries. These queries we end up writing the SQL by hand (using, for example, database specific SQL features, or hints, or clever row-limited sub selects to trick the query optimiser into doing the right thing).

I'd still like all the benefits of e.g. transforming the ResultSet into scala objects when I do this.

I've looked through the code & docs and can't see a way to do this. Have I missed something?

Cheers

g

[Maxime Lévesque]

The featue is not there, it would indeed reasonable, or at least 
pragmatic to have a way to write for handwritten queries, without
having to go all the way down to JDBC,

The question is what form it would take,
I can imagine something like this :

  rawSql[Long,Int,String,Option[String], Option[Int]]("select aLong, anInt, aString, aNullableString, aNullableInt from ..."):
   Tuple5[Long,Int,String,Option[String], Option[Int]]

and / or something like this :

from(table1,table2, ...tableN)((t1,t2,...,tN) =>
  rawSql(""select t1.*, t2.*, ..., tN.*")
  select(t1,t2,...,tN)
)

When I run into something not feasable by Squeryl I usually extend the DSL or API, but I can see the use for it,
it just needs to be implemented ;-)

ML

2011/9/28 Graham Tackley <gra...@tackley.net>

[Graham Tackley]

I've used hibernate extensively in the past (for my sins), and it has quite a flexible way of doing raw sql queries while still being able to hook into its object mapping stuff[1]. As an example:

   List cats = session.createSQLQuery("SELECT {cat.*} FROM CAT {cat} WHERE ROWNUM<10")

    .addEntity("cat", Cat.class)

    .list();

which returns a list of Cat objects. The stuff in curlies in the query gets replaced with all the table and column aliasing that hibernate does (just a Squeryl does).

There's lots of other complex stuff that hibernate supports in the curlies, but all I've used is {entity.*} and {entity.columnName}. If you don't do "addEntity" hibernate just returns an object array of raw values (which in scala would be a TupleN of course).

Your first example 

 rawSql[Long,Int,String,Option[String], Option[Int]]("select aLong, anInt, aString, aNullableString, aNullableInt from ..."):

   Tuple5[Long,Int,String,Option[String], Option[Int]]

would be fine for primative values.

With my 1 day of Squeryl experience, perhaps entity mappings in the query might work something like:

from(person)((p) =>

  rawSql("select {p.*} from {p} where {p.age} > 18 /*hint*/", Map("p" -> p))

  select(p)

)

where actual sql executed replaces 

 {p} with "<personTableName> <personTableAlias>" 

 {p.*} with the aliased list of column names referencing <personTableAlias>

 {p.age} with the alias for the age column

Or maybe hibernate's over-compilication has infected me too much, and I should just make sure the query returns the field values for the entity I want to return in the right order.

Thanks,

g

[1] http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/querysql.html

[Han van Venrooij]

Hey,

I'm currently working on an application in which the structure of the data might change when newer versions are released. 

To cope with that, I plan on manually writing SQL script. 

Since there is no need for the any return values, I think this shouldn't be that difficult. 

However the way the application is set up, I can use different database adapters. For instance, for testing purposes an in memory H2 database is used while users can select to either use an in memory database which will be persisted into a file, or use a JDBC connection to a remote database. Given these restrictions, I cannot simply use JDBC/H2 to execute the query. 

Given the fact that it is a while ago that this discussion was had, have there been updates to squeryl implementing this feature? And if not is there a way I can contribute to this?

I briefly looked into implementing the Query trait but I wouldn't have a clue where to start. 

I hope you can help. 

Regards,

Han

[Maxime Lévesque]

Here's an example of what I use in a few projects :  

   https://gist.github.com/max-l/9250053

the pattern can be extended for Tuple3, Tuple4, etc,

I was hesitant to add it to the source, but I think it has proven itself over time,

If you can expand this to some TupleN, write some test cases, and 

send a pull request, I would accept it.

I would also put the q method in the QueryDsl trait.

--
You received this message because you are subscribed to the Google Groups "Squeryl" group.
To unsubscribe from this group and stop receiving emails from it, send an email to squeryl+u...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Alex D]

Hi, Max!

Thanks for this great example! 

Though, some time pasted since you posted it, I'm wondering if the there is a way the hand-written query, e.g. q() function can return not tuple of primitives, but a a Scala object, which was earlier defined as Schema table?

val users = table[User]("users"))
In this example a collection of User objects?

I would really appreciate your answer!