State of Scala database access?

On Sunday April 17 2011, Doug Pardee wrote:
> After a few years away, I'm looking at Scala again. I like most of
> the changes I see in 2.8, but what's going on with database access?
>
> From what I can tell, scala.dbc has been deprecated, scala.dbc2 was
> stillborn, and scala.dbc3 never really got going. There are a number
> of user-created database access packages, most of which (still
> speaking from what I can tell) are nowhere close to industrial-
> strength. The only one I could get to work at all with an Oracle DB
> was orbroker, and while it's not bad it's not IMO fully Scala-ized
> (yet).
>
> It's obviously possible to use raw JDBC, but JDBC's API is extremely
> non-Scala-ish.
>
> Am I overlooking The Answer?

LMGTFY?

- ScalaQuery [1]
- SQueryl [2]
- Querulous [3]
- CircumflexORM [4]
- O/R Broker [5]

[1] http://github.com/szeiger/scala-query
[2] http://squeryl.org/
[3] https://github.com/twitter/querulous
[4] http://circumflex.ru/
[5] http://www.orbroker.org/

My group is working with Squeryl. We're neophytes, so I can't offer much
in-depth insight, yet.

Randall Schulz

On 17 April 2011 16:26, Randall R Schulz <rschulz [at] sonic [dot] net> wrote:

On Sunday April 17 2011, Doug Pardee wrote:
> After a few years away, I'm looking at Scala again. I like most of
> the changes I see in 2.8, but what's going on with database access?
>
> From what I can tell, scala.dbc has been deprecated, scala.dbc2 was
> stillborn, and scala.dbc3 never really got going. There are a number
> of user-created database access packages, most of which (still
> speaking from what I can tell) are nowhere close to industrial-
> strength. The only one I could get to work at all with an Oracle DB
> was orbroker, and while it's not bad it's not IMO fully Scala-ized
> (yet).
>
> It's obviously possible to use raw JDBC, but JDBC's API is extremely
> non-Scala-ish.
>
> Am I overlooking The Answer?

LMGTFY?

- ScalaQuery [1]
- SQueryl [2]
- Querulous [3]
- CircumflexORM [4]
- O/R Broker [5]

[1] http://github.com/szeiger/scala-query
[2] http://squeryl.org/
[3] https://github.com/twitter/querulous
[4] http://circumflex.ru/
[5] http://www.orbroker.org/

Not to mention the Mapper and Record frameworks in Lift. 

My group is working with Squeryl. We're neophytes, so I can't offer much
in-depth insight, yet.


Randall Schulz

--
Kevin Wright

gtalk / msn : kev [dot] lee [dot] wright [at] gmail [dot] comkev [dot] lee [dot] wright [at] gmail [dot] commail: kevin [dot] wright [at] scalatechnology [dot] com
vibe / skype: kev.lee.wrightquora: http://www.quora.com/Kevin-Wright
twitter: @thecoda

"My point today is that, if we wish to count lines of code, we should not regard them as "lines produced" but as "lines spent": the current conventional wisdom is so foolish as to book that count on the wrong side of the ledger" ~ Dijkstra

Not to mention the Mapper and Record frameworks in Lift.

And Anorm in Play! Framework: http://scala.playframework.org/documentation/scala-0.9/anorm

Cheers,
Mario Fusco
http://twitter.com/mariofusco

On Sunday April 17 2011, Kevin Wright wrote:
> On 17 April 2011 16:26, Randall R Schulz wrote:
> > On Sunday April 17 2011, Doug Pardee wrote:
> > > ...
> > >
> > > Am I overlooking The Answer?
> >
> > LMGTFY?
> >
> > - ScalaQuery [1]
> > - SQueryl [2]
> > - Querulous [3]
> > - CircumflexORM [4]
> > - O/R Broker [5]
> >
> > [1] http://github.com/szeiger/scala-query
> > [2] http://squeryl.org/
> > [3] https://github.com/twitter/querulous
> > [4] http://circumflex.ru/
> > [5] http://www.orbroker.org/
>
> Not to mention the Mapper and Record frameworks in Lift.

Which, as I understand it, are not separable from Lift itself.

Randall Schulz

Oooh, tasty! I didn't notice that one...

I'm in the process of learning scala right now with the hope of re-implementing
my employers dead-slow python-based CRM-like app using Play + Scala
and anything new on the Play front makes me happy :-)

On 17 April 2011 18:01, Mario Fusco wrote:
>
>> Not to mention the Mapper and Record frameworks in Lift.
>
> And Anorm in Play! Framework:
> http://scala.playframework.org/documentation/scala-0.9/anorm
>
> Cheers,
> Mario Fusco
> http://twitter.com/mariofusco
>

On Sun, Apr 17, 2011 at 9:57 AM, Doug Pardee <dougpardee [at] yahoo [dot] com> wrote:

The only one I could get to work at all with an Oracle DB
was orbroker, and while it's not bad it's not IMO fully Scala-ized
(yet).

As the author, I have to ask what you feel is missing? :-)

On Apr 17, 8:26 am, Randall R Schulz wrote:
> - ScalaQuery [1]
> - SQueryl [2]
> - Querulous [3]
> - CircumflexORM [4]
> - O/R Broker [5]

As I said, "There are a number of user-created database access
packages, most of which (still speaking from what I can tell) are
nowhere close to industrial-strength. The only one I could get to work
at all with an Oracle DB was orbroker."

If you have a different opinion on any of those, or if there's simply
some quirk that incorrectly made me write off the package, that's what
I'd like to hear about. Particularly nasty example: Querulous doesn't
build with 2.8, and even if you build it with 2.7 it only works with
MySQL. That simply isn't my idea of "industrial strength". If you know
of a way to get Querulous to build with 2.8 and work with Oracle,
Sybase, and DB2 (for example), that's the information that I seek.

I don't need a list of all of the junk out there. What I'm looking for
is One Good Package that will provide unrestricted database access in
a Scala-ish way.

On Sunday April 17 2011, Doug Pardee wrote:
> ... That simply isn't my idea of "industrial strength". ...

Please characterize completely and precisely what you do mean
by "industrial strength" (an otherwise highly subject term) and then
people can answer your question about which tools are and are not and
why.

> I don't need a list of all of the junk out there. What I'm looking
> for is One Good Package that will provide unrestricted database
> access in a Scala-ish way.

I suspect you're going to have to do your own homework and evaluate the
options in the light of your own requirements.

Randall Schulz

Dough,

it is possible to use JPA from within Scala
for creators, primary key retrievers, updaters and deleters JPA is "Scala-ish" enough
for other retrievers you can, once you have a result set, make use monadic for loops
(very "Scala-ish" (and you get pattern matching "for free"))
and hope that the persistence implementation (EclipseLink if you use Oracle products) is clever enough (e.g. about its primary and secondary cache) to give
you the "industrial strength" you need

it is very easy to wrap a generic domain store around JPA
(for example: https://github.com/LucDupAtGitHub/SimpleDomainStore)

last year a friend of me was payed by a bank for implementing pattern
matching for database querying in Java: again, using Scala the bank
would have gotten it "for free"
 
Luc

On Sun, Apr 17, 2011 at 9:40 PM, Doug Pardee <dougpardee [at] yahoo [dot] com> wrote:

On Apr 17, 8:26 am, Randall R Schulz <rsch [dot] [dot] [dot] [at] sonic [dot] net> wrote:
> - ScalaQuery [1]
> - SQueryl [2]
> - Querulous [3]
> - CircumflexORM [4]
> - O/R Broker [5]

As I said, "There are a number of user-created database access
packages, most of which (still speaking from what I can tell) are
nowhere close to industrial-strength. The only one I could get to work
at all with an Oracle DB was orbroker."

If you have a different opinion on any of those, or if there's simply
some quirk that incorrectly made me write off the package, that's what
I'd like to hear about. Particularly nasty example: Querulous doesn't
build with 2.8, and even if you build it with 2.7 it only works with
MySQL. That simply isn't my idea of "industrial strength". If you know
of a way to get Querulous to build with 2.8 and work with Oracle,
Sybase, and DB2 (for example), that's the information that I seek.

I don't need a list of all of the junk out there. What I'm looking for
is One Good Package that will provide unrestricted database access in
a Scala-ish way.

--
   __~O
  -\ <,
(*)/ (*)

reality goes far beyond imagination

On Apr 17, 11:29 am, Nils Kilden-Pedersen wrote:
> On Sun, Apr 17, 2011 at 9:57 AM, Doug Pardee wrote:
> > The only one I could get to work at all with an Oracle DB was orbroker,
> > and while it's not bad it's not IMO fully Scala-ized (yet).
>
> As the author, I have to ask what you feel is missing? :-)

The big issue for me is QuerySession.select. It's procedural in
nature. But from what I can tell, the functional alternative of
selectAll will cause the entire result set to be read into memory.

I suspect this might simply be an artifact from pre-2.8. With 2.8,
we've got the TraversableOnce trait that would seem to me to be ideal
for handling large result sets. Of course, if you used
TraversableOnce, then orbroker would only work with 2.8 or later. A
more inclusive alternative might be to create your own return trait
that includes at least the functions used by for comprehensions.

On Sun, Apr 17, 2011 at 3:53 PM, Doug Pardee <dougpardee [at] yahoo [dot] com> wrote:

The big issue for me is QuerySession.select. It's procedural in
nature.

QuerySession.select takes a callback function that is called for every row processed, with an option to shortcut the processing. It's procedural (or imperative if you like) because a JDBC result set must be processed in that manner.
 

But from what I can tell, the functional alternative of
selectAll will cause the entire result set to be read into memory.

Correct, selectAll is a convenience method that uses select to process the entire result set and return an IndexedSeq.
 

I suspect this might simply be an artifact from pre-2.8. With 2.8,
we've got the TraversableOnce trait that would seem to me to be ideal
for handling large result sets.

If I understand you correct, you seem to would like a data structure that is loaded on demand? If so, I think you are forgetting things like transaction isolation levels and data consistency. You cannot do correct and deterministic database processing with a lazy data structure.

On Sun, Apr 17, 2011 at 8:23 PM, Nils Kilden-Pedersen <nilskp [at] gmail [dot] com> wrote:

On Sun, Apr 17, 2011 at 3:53 PM, Doug Pardee <dougpardee [at] yahoo [dot] com> wrote:

The big issue for me is QuerySession.select. It's procedural in
nature.

QuerySession.select takes a callback function that is called for every row processed, with an option to shortcut the processing. It's procedural (or imperative if you like) because a JDBC result set must be processed in that manner.

In case Tony doesn't show up, just one point: an Iteratee-based approach to result set processing could be exposed enabling the client to drive exactly how greedy or lazy it wants to be processing each row (plus the other benefits of iteratees such as composability and the ability of clients to not consume all the results).  

 

But from what I can tell, the functional alternative of
selectAll will cause the entire result set to be read into memory.

Correct, selectAll is a convenience method that uses select to process the entire result set and return an IndexedSeq.
 

I suspect this might simply be an artifact from pre-2.8. With 2.8,
we've got the TraversableOnce trait that would seem to me to be ideal
for handling large result sets.

If I understand you correct, you seem to would like a data structure that is loaded on demand? If so, I think you are forgetting things like transaction isolation levels and data consistency. You cannot do correct and deterministic database processing with a lazy data structure.

Again an Enumeratee/Iteratee approach could address this.  Iteratees drive the consumption of values in a controlled manner where sensitive resources (connections, statements, transactions, result sets, etc) are guaranteed to be cleaned up properly.  A good article on this can be found here: http://apocalisp.wordpress.com/2010/10/17/scalaz-tutorial-enumeration-based-io-with-iteratees/  Alas, this approach has not made it into any of the Scala/DB libraries that I'm aware of.
--
Jim Powers

Dear Doug,
i have also been experimenting with Datalog meets NoSQL and have built a NoSQL-like DB on top of BaseX. This has some interesting features.

• prolog terms (approximately iso to Scala case classes/patterns) are queries
• the queries are translated to XQuery
• "records" are organized as "key"-value pairs where
• a match between a query for a term and a key is effectively unification
• this can be expanded to full datalog capabilities, but even with unification this provides incredibly unique capabilities
• the value structure is not queried, only the key structure; however,
• keys can present views of values, eventually we plan to present a view transform API
• the implementation is distributed in the following sense
• a query first checks in-memory cache, if that fails to find a match
• it checks the local XML store, if that fails to find a match
• it forwards the requests along RabbitMQ queues to connected nodes in the data network
• the API is entirely monadic; that is, to effect a query one writes something like
• for( data <- mySpace.get( <pattern> ) ) { /* do something with the data */ ... }

This aim is that the expression of applications scales linearly in complexity as the complexity of the application itself grows at a much faster rate. Thus, one still writes simple manageable and performant code to handle very complex distributed applications.
Best wishes,
--greg

On Sun, Apr 17, 2011 at 8:26 AM, Randall R Schulz <rschulz [at] sonic [dot] net> wrote:

On Sunday April 17 2011, Doug Pardee wrote:
> After a few years away, I'm looking at Scala again. I like most of
> the changes I see in 2.8, but what's going on with database access?
>
> From what I can tell, scala.dbc has been deprecated, scala.dbc2 was
> stillborn, and scala.dbc3 never really got going. There are a number
> of user-created database access packages, most of which (still
> speaking from what I can tell) are nowhere close to industrial-
> strength. The only one I could get to work at all with an Oracle DB
> was orbroker, and while it's not bad it's not IMO fully Scala-ized
> (yet).
>
> It's obviously possible to use raw JDBC, but JDBC's API is extremely
> non-Scala-ish.
>
> Am I overlooking The Answer?

LMGTFY?

- ScalaQuery [1]
- SQueryl [2]
- Querulous [3]
- CircumflexORM [4]
- O/R Broker [5]

[1] http://github.com/szeiger/scala-query
[2] http://squeryl.org/
[3] https://github.com/twitter/querulous
[4] http://circumflex.ru/
[5] http://www.orbroker.org/


My group is working with Squeryl. We're neophytes, so I can't offer much
in-depth insight, yet.


Randall Schulz

--
L.G. Meredith
Managing Partner
Biosimilarity LLC
7329 39th Ave SWSeattle, WA 98136

+1 206.650.3740

http://biosimilarity.blogspot.com

On Sun, Apr 17, 2011 at 7:51 PM, Jim Powers <jim [at] casapowers [dot] com> wrote:

On Sun, Apr 17, 2011 at 8:23 PM, Nils Kilden-Pedersen <nilskp [at] gmail [dot] com> wrote:

On Sun, Apr 17, 2011 at 3:53 PM, Doug Pardee <dougpardee [at] yahoo [dot] com> wrote:

The big issue for me is QuerySession.select. It's procedural in
nature.

QuerySession.select takes a callback function that is called for every row processed, with an option to shortcut the processing. It's procedural (or imperative if you like) because a JDBC result set must be processed in that manner.

In case Tony doesn't show up, just one point: an Iteratee-based approach to result set processing could be exposed enabling the client to drive exactly how greedy or lazy it wants to be processing each row (plus the other benefits of iteratees such as composability and the ability of clients to not consume all the results).  

 

But from what I can tell, the functional alternative of
selectAll will cause the entire result set to be read into memory.

Correct, selectAll is a convenience method that uses select to process the entire result set and return an IndexedSeq.
 

I suspect this might simply be an artifact from pre-2.8. With 2.8,
we've got the TraversableOnce trait that would seem to me to be ideal
for handling large result sets.

If I understand you correct, you seem to would like a data structure that is loaded on demand? If so, I think you are forgetting things like transaction isolation levels and data consistency. You cannot do correct and deterministic database processing with a lazy data structure.

Again an Enumeratee/Iteratee approach could address this.  Iteratees drive the consumption of values in a controlled manner where sensitive resources (connections, statements, transactions, result sets, etc) are guaranteed to be cleaned up properly.  A good article on this can be found here: http://apocalisp.wordpress.com/2010/10/17/scalaz-tutorial-enumeration-based-io-with-iteratees/  Alas, this approach has not made it into any of the Scala/DB libraries that I'm aware of.

Based on my reading of that article, that is almost exactly how O/R Broker works.

"Instead of implementing an interface from which we request Strings by pulling, we’re going to give an implementation of an interface that can receive Strings by pushing"

The results are pushed to the callback function, which returns a Boolean indicating whether to continue or not.

On Mon, Apr 18, 2011 at 8:04 AM, Nils Kilden-Pedersen <nilskp [at] gmail [dot] com> wrote:

Based on my reading of that article, that is almost exactly how O/R Broker works.
"Instead of implementing an interface from which we request Strings by pulling, we’re going to give an implementation of an interface that can receive Strings by pushing"

The results are pushed to the callback function, which returns a Boolean indicating whether to continue or not.

That's cool, but not quite the same:
** Note: pseudo-code -- merely an outline of what could be.
Given something like this: (Enumeratee)def enumResultSet[A](rs:ResultSet, i: IterV[ResultSet, A]): StatementIteratee[IterV[ResultSet, A]] = ...
And two iteratees: (contrived for example)def process1stPart: IterV[ResultSet,Option[Int]] = ... def process2ndPart: IterV[ResultSet,Option[Int]] = ...def process3rdPart: IterV[ResultSet,Option[Int]] = ... // ignore the rest
Now you can do[1]:
def processQuery(query:Query): ConnectionIteratee[Result] = (rs => for {
  p1 <- enumResultSet(rs, process1stPart)
  p2 <- enumResultSet(rs, process2ndPart)
  p3 <- enumResultSet(rs, process3rdPart)
} yield Result(p1,p2,p3)).run(query)
The various explicit "run" methods take care of resource handling (calling EOF and closing resources as necessary when the total computation ends).  When an iteratee says they are done it is with regard to its part of the computation, not that the overall computation is complete, that happens when run exits (which is the only time that the ResultSet is actually closed and cleaned up).  By not making the iteratees responsible for when to say that the overall computation is over, rather only gracefully exiting with their part when ready enables this kind of composition.  Mind you, these contrived examples quasi-utilize Scalaz notions of iteratees (and the IO Monad), neither are required to implement iteratees.
Just a thought.
--
Jim Powers
[1] run[A]:Query => ConnectionIteratee[A] -> Internally this run converts the Query to a JDBC Statement and passes it to the run of the StatementIteratee which returns a ConnectionIteratee[A] that has a run[A]:ConnectionFactory => IO[A] and executes the embedded computation when an instance of ConnectionFactory is supplied.

On Mon, Apr 18, 2011 at 10:00 AM, Jim Powers <jim [at] casapowers [dot] com> wrote:

On Mon, Apr 18, 2011 at 8:04 AM, Nils Kilden-Pedersen <nilskp [at] gmail [dot] com> wrote:

Based on my reading of that article, that is almost exactly how O/R Broker works.
"Instead of implementing an interface from which we request Strings by pulling, we’re going to give an implementation of an interface that can receive Strings by pushing"

The results are pushed to the callback function, which returns a Boolean indicating whether to continue or not.

That's cool, but not quite the same:
** Note: pseudo-code -- merely an outline of what could be.

Jim, I appreciate your response, but I still am not quite sure what you're attempting to do that differs, other than syntax, from what can already be done? And I don't mean to say that syntax doesn't matter, it obviously does, but are we talking differences of substance rather than style? Perhaps a more concrete example would be helpful.

Or maybe this is just a matter of the design goals of O/R Broker being different that what you'd expect.

On Mon, Apr 18, 2011 at 11:19 AM, Nils Kilden-Pedersen <nilskp [at] gmail [dot] com> wrote:

Jim, I appreciate your response, but I still am not quite sure what you're attempting to do that differs, other than syntax, from what can already be done? And I don't mean to say that syntax doesn't matter, it obviously does, but are we talking differences of substance rather than style? Perhaps a more concrete example would be helpful.
Or maybe this is just a matter of the design goals of O/R Broker being different that what you'd expect.

Ha!  No, I'm all good.  No misunderstanding and I'm not even trying to talk about design goals.  Merely point out that there are different formulations as to how to drive consumption of rows in a ResultSet rather than strictly greedily.  Merely responding to the statement:
On Sun, Apr 17, 2011 at 8:23 PM, Nils Kilden-Pedersen <nilskp [at] gmail [dot] com> wrote:

If I understand you correct, you seem to would like a data structure that is loaded on demand? If so, I think you are forgetting things like transaction isolation levels and data consistency. You cannot do correct and deterministic database processing with a lazy data structure.

Lazy I/O is also a problem in Haskell that Iteratees intend to correct.  They offer an approach to incrementally consume a resource in a composable fashion without fear of resource leaks, even if lazy evaluation is performed within the confines of the driving function.
W.R.T. a more complete example, I am getting to that as well (Tony Morris alluded to this approach on the scala-user list a little while back).  My particular need is that I uses server-side DB cursors a lot to walk large amounts of data that would be effectively impossible to bring into memory, but I don't want to hand-code the consumption semantics of the data processing pipeline, so iteratees appear to be a good fit.
--
Jim Powers

On Mon, Apr 18, 2011 at 11:48 AM, Jim Powers <jim [at] casapowers [dot] com> wrote:

W.R.T. a more complete example, I am getting to that as well (Tony Morris alluded to this approach on the scala-user list a little while back).  My particular need is that I uses server-side DB cursors a lot to walk large amounts of data that would be effectively impossible to bring into memory, but I don't want to hand-code the consumption semantics of the data processing pipeline, so iteratees appear to be a good fit.

When you write "server-side", are you referring to the DB server, or some Scala mid-tier server?

On 2011-04-17 16:57, Doug Pardee wrote:
> From what I can tell, scala.dbc has been deprecated, scala.dbc2 was
> stillborn, and scala.dbc3 never really got going. There are a number
> of user-created database access packages, most of which (still
> speaking from what I can tell) are nowhere close to industrial-
> strength. The only one I could get to work at all with an Oracle DB
> was orbroker, and while it's not bad it's not IMO fully Scala-ized
> (yet).
>
> It's obviously possible to use raw JDBC, but JDBC's API is extremely
> non-Scala-ish.

I am wondering if you find ScalaQuery to be not of industrial strength
or lacking in Oracle support. While it does not yet have a working
Oracle driver for generating Oracle-specific SQL code from its query
DSL, the database/session management and the org.scalaquery.simple.*
feature (where you write the SQL queries yourself) should be perfectly
usable with Oracle and allow you to avoid all the complexity of raw JDBC.

Regarding the ScalaQuery Oracle driver, I'm always looking for
volunteers for implementing new drivers. At the moment, I am in the
process of writing a ScalaQuery driver for SQL Server. Depending on the
direction taken by the project that I am currently building with
ScalaQuery, I might end up having to write an Oracle driver soon anyway.

-sz

On Mon, Apr 18, 2011 at 1:34 PM, Nils Kilden-Pedersen <nilskp [at] gmail [dot] com> wrote:

On Mon, Apr 18, 2011 at 11:48 AM, Jim Powers <jim [at] casapowers [dot] com> wrote:

W.R.T. a more complete example, I am getting to that as well (Tony Morris alluded to this approach on the scala-user list a little while back).  My particular need is that I uses server-side DB cursors a lot to walk large amounts of data that would be effectively impossible to bring into memory, but I don't want to hand-code the consumption semantics of the data processing pipeline, so iteratees appear to be a good fit.

When you write "server-side", are you referring to the DB server, or some Scala mid-tier server?

Yes, DB-server side as in "CURSOR FOR ..."
As far as DB access libraries I have "rolled(several)" of my own not so much for the reasons Doug Pardee was alluding to in the OP.  I would guess that most people consider things like Hibernate, JPA, Spring-ORM, etc. to be what is considered "industrial strength" these days.  I'm not a big fan of the O in ORM and code generators usually fail me within a pretty short period of time after falling in love :-(.  Personally, I like the direction that Scala-query is going (I think Lift-Mapper may be similar, but not sure) in terms of type safety on results and the ability to define ad-hoc projections.  However, I find myself writing a lot more of my own SQL (via DynamicQuery) than I expected.
W.R.T. ORBroker - The SQL writing stuff is interesting and I used a similar approach in a earlier version of one of my own "home-rolled" DB access libraries, but I'm a lazy man and I don't like my SQL being out of sight and the fact that templating engines are not SQL aware.  I've written my own SQL-aware templating system that I'll see if my employer will let me open-source, kinda looks like this:
sql("SELECT * FROM foo")=> "SELECT * FROM foo"
sql("SELECT * FROM foo WHERE x{=x}") => "SELECT * FROM foo WHERE x IS NULL"sql("SELECT * FROM foo WHERE x{=x}", 'x -> null) => "SELECT * FROM foo WHERE x IS NULL"sql("SELECT * FROM foo WHERE x{=x}", 'x -> 1) => "SELECT * FROM foo WHERE x=1"sql("SELECT * FROM foo WHERE x{=x[string]}", 'x -> 1) =>  "SELECT * FROM foo WHERE x='1'"
sql("SELECT {@columns} FROM foo WHERE x{=x[string]}", 'x -> 1,                                                       'columns -> List("A","B","C")) => "SELECT A,B,C FROM foo WHERE x='1'"sql("SELECT {@columns} FROM foo WHERE x{=x[string]}", 'x -> 1,                                                        'columns -> List("A",("B",sql("NOW()")),"C")) => "SELECT A,NOW() AS B,C FROM foo WHERE x='1'"sql("SELECT {@columns} FROM foo WHERE x{=x[string]}", 'x -> 1,                                                        'columns -> List(("A","Woo"),("B",sql("NOW()")),("C","Hoo"))) => "SELECT Woo AS A,NOW() AS B,Hoo AS C FROM foo WHERE x='1'"sql("SELECT {@columns} FROM foo WHERE x{=x[string]}", 'x -> 1,                                                        'columns -> List(("A",sql("(SELECT id FROM bar WHERE bar.id=foo.id)")),                                                                        ("B",sql("NOW()")),                                                                       ("C","Hoo"))) =>"SELECT (SELECT id FROM bar WHERE bar.id=foo.id) AS A,NOW() AS B,Hoo AS C FROM foo WHERE x='1'"
sql("INSERT INTO foo ({@<columns}) VALUES ({@>columns})", 'columns -> List(("A","Woo"),("B",sql("NOW()")),("C","Hoo"))) =>  "INSERT INTO foo (A,B,C) VALUES ('Woo',NOW(),'Hoo')"sql("INSERT INTO foo ({@<columns}) VALUES ({@>columns})", 'columns -> List(("A","The 'Woo' stops 'here'."),("B",sql("NOW()")),("C","My Hoo'"))) =>  "INSERT INTO foo (A,B,C) VALUES ('The ''Woo'' stops ''here''.',NOW(),'My Hoo''')"
sql("UPDATE foo SET {_=_columns} WHERE id{=id}", 'id -> 5, 'columns -> List(("A","Woo"),("B",sql("NOW()")),("C","Hoo"))) => "UPDATE foo SET A='Woo', B=NOW(), C='Hoo' WHERE id=5"
Yes, as you can see I use a lot of computed columns in results as well as write some fairly hefty SQL that I stitch together.  Most of this I do by hand[1] since code-generators generally don't think of my use cases when being designed[2] ;-).  Since ORBroker doesn't try to solve the code generation problem it would seem like a natural fit but the O in ORBroker means it isn't my kind of thing, different strokes for different folks I guess.
--
Jim Powers
[1] Of course a code generator that could handle my use cases would be welcome because if I could get more (correct) SQL by typing less I would be happy.[2] Not to mention that good SQL generation is genuinely hard.

On Mon, Apr 18, 2011 at 2:18 PM, Jim Powers <jim [at] casapowers [dot] com> wrote:

Yes, DB-server side as in "CURSOR FOR ..."

Ok, that's what I thought, but it also confused me, because then I'm still at a loss of the point being made. Maybe it's just a matter of O/R Broker lacking an overall encapsulation of a query, but that is by design, and could be easily implemented on top (if I understand your example right).
 

Yes, as you can see I use a lot of computed columns in results as well as write some fairly hefty SQL that I stitch together.  Most of this I do by hand[1] since code-generators generally don't think of my use cases when being designed[2] ;-).  

Amen.
 

Since ORBroker doesn't try to solve the code generation problem it would seem like a natural fit but the O in ORBroker means it isn't my kind of thing, different strokes for different folks I guess.

In O/R Broker, rows are processed using a typed RowExtractor, so the result can be anything you want, even Unit. I have a project spitting out JSON to a stream without any intermediary objects, in a strict O(n) manner.

On Mon, Apr 18, 2011 at 3:40 PM, Nils Kilden-Pedersen <nilskp [at] gmail [dot] com> wrote:

On Mon, Apr 18, 2011 at 2:18 PM, Jim Powers <jim [at] casapowers [dot] com> wrote:

Yes, DB-server side as in "CURSOR FOR ..."

Ok, that's what I thought, but it also confused me, because then I'm still at a loss of the point being made. Maybe it's just a matter of O/R Broker lacking an overall encapsulation of a query, but that is by design, and could be easily implemented on top (if I understand your example right).

Sorry, I must admit that my DB world-view these days is pretty narrow, mostly Postgres.  The Postgres JDBC driver and setFetchSize down't always play nice (the Postgres JDBC docs say it's supposed to use a server-side cursor, but sometimes it doesn't).  I have (as part of my cobbled-together DB library) a mechanism to explicitly create named cursors that need to get cleaned up after doing their job.  I'm doing this because many gillions of bytes need to get to my program without killing it.  I've begun working on a way to generalize and abstract row fetching via and Iteratee/Enumeratee so my client code doesn't know/care if a cursor is in effect.  So, some cases I can let Postgres disgorge all the rows at me (as it wants) and other times I can nibble away at the rows of a server-side cursor. The issue is that the consuming code (interatee) no longer is in charge of the resource lifetime (ResultSet), the combination of the particular Enumeratee and driving function takes care of the differences.
And yes, there are many ways to skin this cat (sorry cat).  I'm likely to use Scalaz Iteratees because the implementation is generic enough that I can reuse the same "protocol" elsewhere and simply change the types being processed as opposed to a one-off solution that only handles ResultSets, Statements, and Connections in a special way.  Of course, a "standard bottom[1]" implementation of Iteratee could make it into the standard libraries ;-).  

In O/R Broker, rows are processed using a typed RowExtractor, so the result can be anything you want, even Unit. I have a project spitting out JSON to a stream without any intermediary objects, in a strict O(n) manner.

I will give it a look-see, thanks. 
--
Jim Powers
[1] The protocol used between an Iteratee and Enumeratee is not fixed and can be adapted to suit particular needs, so Iteratee is not a single "thing", but a conceptual framework to expose the details of "the iterator pattern" so as to make iteration composable and resource management "safe", or as those smarter than me: the type system guarantees that resources are used in a safe manner otherwise your code won't compile ;-).

On Apr 17, 12:56 pm, Randall R Schulz wrote:
> Please characterize completely and precisely what you do mean
> by "industrial strength" (an otherwise highly subject term)

It's a simple definition: the package must have no functional
limitations (and little performance limitation) relative to JDBC.
Either the package must be 100% functionally complete, or it mustn't
break when the programmer uses JDBC to obtain the missing
functionality (the need for which should be limited to rare
situations). And if the package doesn't work with the big commercial
RDB software -- Oracle, Sybase, and DB2 -- it's not a contender.

SQL-free concepts like ScalaQuery are nifty, but in many large
corporate environments the SQL code is tightly controlled by DBAs for
reliability and performance reasons. Either the DBAs write the SQL
themselves and provide it to the programmers, or the programmers
submit their SQL code to the DBAs for approval. I've worked at an
insurance company where all access to the database is done through
stored procedures supplied by the DBAs.

The problem is that "rare situations" are common everyday occurrences, these tools need adaptors for each and every database to do even trivial stuff.
VARCHAR vs VARCHAR2 anyone?

On 19 April 2011 18:16, Doug Pardee <dougpardee [at] yahoo [dot] com> wrote:

On Apr 17, 12:56 pm, Randall R Schulz <rsch [dot] [dot] [dot] [at] sonic [dot] net> wrote:
> Please characterize completely and precisely what you do mean
> by "industrial strength" (an otherwise highly subject term)

It's a simple definition: the package must have no functional
limitations (and little performance limitation) relative to JDBC.
Either the package must be 100% functionally complete, or it mustn't
break when the programmer uses JDBC to obtain the missing
functionality (the need for which should be limited to rare
situations). And if the package doesn't work with the big commercial
RDB software -- Oracle, Sybase, and DB2 -- it's not a contender.

SQL-free concepts like ScalaQuery are nifty, but in many large
corporate environments the SQL code is tightly controlled by DBAs for
reliability and performance reasons. Either the DBAs write the SQL
themselves and provide it to the programmers, or the programmers
submit their SQL code to the DBAs for approval. I've worked at an
insurance company where all access to the database is done through
stored procedures supplied by the DBAs.

--
Kevin Wright

gtalk / msn : kev [dot] lee [dot] wright [at] gmail [dot] comkev [dot] lee [dot] wright [at] gmail [dot] commail: kevin [dot] wright [at] scalatechnology [dot] com
vibe / skype: kev.lee.wrightquora: http://www.quora.com/Kevin-Wright
twitter: @thecoda

"My point today is that, if we wish to count lines of code, we should not regard them as "lines produced" but as "lines spent": the current conventional wisdom is so foolish as to book that count on the wrong side of the ledger" ~ Dijkstra

Vs CLOB for texts longer than 4k letters. Or UTF-8 resulting 3-bytes per char...

On Apr 19, 2011 7:41 PM, "Kevin Wright" <kev [dot] lee [dot] wright [at] gmail [dot] com> wrote:
> The problem is that "rare situations" are common everyday occurrences, these
> tools need adaptors for each and every database to do even trivial stuff.
>
> VARCHAR vs VARCHAR2 anyone?
>
>
> On 19 April 2011 18:16, Doug Pardee <dougpardee [at] yahoo [dot] com> wrote:
>
>> On Apr 17, 12:56 pm, Randall R Schulz <rsch [dot] [dot] [dot] [at] sonic [dot] net> wrote:
>> > Please characterize completely and precisely what you do mean
>> > by "industrial strength" (an otherwise highly subject term)
>>
>> It's a simple definition: the package must have no functional
>> limitations (and little performance limitation) relative to JDBC.
>> Either the package must be 100% functionally complete, or it mustn't
>> break when the programmer uses JDBC to obtain the missing
>> functionality (the need for which should be limited to rare
>> situations). And if the package doesn't work with the big commercial
>> RDB software -- Oracle, Sybase, and DB2 -- it's not a contender.
>>
>> SQL-free concepts like ScalaQuery are nifty, but in many large
>> corporate environments the SQL code is tightly controlled by DBAs for
>> reliability and performance reasons. Either the DBAs write the SQL
>> themselves and provide it to the programmers, or the programmers
>> submit their SQL code to the DBAs for approval. I've worked at an
>> insurance company where all access to the database is done through
>> stored procedures supplied by the DBAs.
>
>
>
>
> --
> Kevin Wright
>
> gtalk / msn : kev [dot] lee [dot] wright [at] gmail [dot] com
> <kev [dot] lee [dot] wright [at] gmail [dot] com>mail: kevin [dot] wright [at] scalatechnology [dot] com
> vibe / skype: kev.lee.wright
> quora: http://www.quora.com/Kevin-Wright
> twitter: @thecoda
>
> "My point today is that, if we wish to count lines of code, we should not
> regard them as "lines produced" but as "lines spent": the current
> conventional wisdom is so foolish as to book that count on the wrong side of
> the ledger" ~ Dijkstra

On Tue, Apr 19, 2011 at 1:41 PM, Kevin Wright <kev [dot] lee [dot] wright [at] gmail [dot] com> wrote:

The problem is that "rare situations" are common everyday occurrences, these tools need adaptors for each and every database to do even trivial stuff.
VARCHAR vs VARCHAR2 anyone?

Sadly yes.  There just seem to be way too many idiosyncrasies between DBs, they types they use, support for cursors, SPs, etc. to abstract away nicely.  My latest incarnation of "DB library thingy to make things more pleasant in Scala-land" is to just pimp the hell out of good-ol' JDBC and friends (connection poolers, for instance) and leave most of JDBC out in the open.  Sad, but true.
--
Jim Powers

Which brings us nicely to the DailyWTF :-)

On 19 April 2011 19:16, Doug Pardee wrote:
> On Apr 17, 12:56 pm, Randall R Schulz wrote:
>> Please characterize completely and precisely what you do mean
>> by "industrial strength" (an otherwise highly subject term)
>
> It's a simple definition: the package must have no functional
> limitations (and little performance limitation) relative to JDBC.
> Either the package must be 100% functionally complete, or it mustn't
> break when the programmer uses JDBC to obtain the missing
> functionality (the need for which should be limited to rare
> situations). And if the package doesn't work with the big commercial
> RDB software -- Oracle, Sybase, and DB2 -- it's not a contender.
>
> SQL-free concepts like ScalaQuery are nifty, but in many large
> corporate environments the SQL code is tightly controlled by DBAs for
> reliability and performance reasons. Either the DBAs write the SQL
> themselves and provide it to the programmers, or the programmers
> submit their SQL code to the DBAs for approval. I've worked at an
> insurance company where all access to the database is done through
> stored procedures supplied by the DBAs.