Relations for 0.9.4

[Maxime Lévesque]

 Hi everyone, I wanted to get some feedback on relations I have implemened in Squeryl,

I`ve put some preliminary docs here :

   http://max-l.github.com/Squeryl/relations-ng.html

(Note that you cannot navigate to this page from the site... it's a sneak preview)

More examples can be seen in the unit tests  :

  http://github.com/max-l/Squeryl/blob/relations/src/test/scala/org/squeryl/tests/schooldb2/SchoolDb2.scala

This should be the main feature for 0.9.4

What remains to be done :

1) Constraints : now that the concept of relation is introduced, there should be a
   way to define the standard foreing key attributes (cascade delete, foreing key constraints, etc...),
   and have them created by the Schema trait.
2) Documentation
3) Beef up the test suite.

Like always, ideas, collaboration, feedback and constructive critique is welcome
ideally before 0.9.4 release ! ;-)

 Regards

[Max]

I forgot to add, for those on the bleeding edge, the code is
available
in the "relations" branch :

git://github.com/max-l/Squeryl.git relations

Max

On 26 avr, 22:50, Maxime Lévesque <maxime.leves...@gmail.com> wrote:
>  Hi everyone, I wanted to get some feedback on relations I have implemened
> in Squeryl,
>
> I`ve put some preliminary docs here :
>
>    http://max-l.github.com/Squeryl/relations-ng.html
>
> (Note that you cannot navigate to this page from the site... it's a sneak
> preview)
>
> More examples can be seen in the unit tests  :
>

> http://github.com/max-l/Squeryl/blob/relations/src/test/scala/org/squ...

>
> This should be the main feature for 0.9.4
>
> What remains to be done :
>
> 1) Constraints : now that the concept of relation is introduced, there
> should be a
>    way to define the standard foreing key attributes (cascade delete,
> foreing key constraints, etc...),
>    and have them created by the Schema trait.
> 2) Documentation
> 3) Beef up the test suite.
>
> Like always, ideas, collaboration, feedback and constructive critique is
> welcome
> ideally before 0.9.4 release ! ;-)
>
>  Regards
>

> --
> Subscription settings:http://groups.google.com/group/squeryl/subscribe?hl=en

[Dmitry Grigoriev]

Hello Max,

Feature looks promising, though I'm too sleepy yet to dig into. :)
Questions raised by reading docs page:


You mentioned but not used ManyToOne trait. May Subject.courses must be
defined as ManyToOne instead of OneToMany?

How can I specify table name for intermediate many-to-many relation?

How can I select N+1 rows (master row + one-to-many detail rows) in a
single query? (Most important.)

Probably you should demonstrate how to work with
CourseSubscription.grade value: make that class a case class and write:
SchoolDb.courseSubscription.insert(cs.copy(grade=123)).

--
Cheers,
dimgel

http://dimgel.ru/lib.web
Thin, stateless, strictly typed Scala web framework.

[Dmitry Grigoriev]

> How can I select N+1 rows (master row + one-to-many detail rows) in a
> single query? (Most important.)

I suspect this feature won't be much useful for anemic programming
model. And for rich model one will have complex queries optimization
headache anyway, even if you provide solution for simple N+1 case.

[Maxime Lévesque]

  The whole N+1 select problematic is resolved in Squeryl by *not*
using relations, but by using a query instead :

  from(one, many)((o,m) =>
    where(o.pKey === m.fKey)
    select((o, m))
  )

 This reminds me that there remains an optimisation to be made in the  above query :

   Should the "one" side instantiated "many" times ?

  The way I see relations in Squery is that they are totally lazy,
the N+1 issue, is a case where eagerness is preffered : we want to load many parts
of a relation in a single call, the case can be generalized for
"n" level relations, if we want to load :

   R1xR2,.....Rn

in a single call, we do it with a query instead of having

 1* |R2| * |R3| * .... * |Rn| 

 database trips  (not :  |Rk|  means the number of rows in the Rk part of the relation ) 

 Thanks for bringing up the topic, it needs to be documented...

 Cheers

[Dmitry Grigoriev]

> The whole N+1 select problematic is resolved in Squeryl by *not*
> using relations, but by using a query instead :
>
> from(one, many)((o,m) =>
> where(o.pKey === m.fKey)
> select((o, m))
> )
>
> This reminds me that there remains an optimisation to be made in the
> above query :
>
> Should the "one" side instantiated "many" times ?

I'm not sure... From relational viewpoint, we have many separate rows
and thus many "one" instances. As for me, it would be easier to
understand and remember (no... I should say "one special case less to
remember"). If we have immutable entities, there's no difference in
fact, so it's totally up to you. So we are to listen guys preferring
mutable entities.

Another question here is: do you have reliable way to determine when
this optimization is applicable? I think only if you got one-to-many
relation declared in schema.

> The way I see relations in Squery is that they are totally lazy, [...]
> in a single call, we do it with a query instead of having [...]
I see. Good. At least, simple. :) There's a thing called Hibernate I
hate for all those lots of tweaks impossible to remember and to use
together without headache.

A bit of philosophy (somewhat offtopic). I don't participate in that
discussion about advantages of immutability on scala mailing list
(because I have to read it before answering and I'm too lazy for that),
so I'll write here. I found immutability and functional approach work
great with anemic programming model and transaction script. The idea of
anemic is that data is passive, all actions are done by controller
objects which pass data to each other ("data floating by pipes"
metaphor). That is, data and logic are separated (yes, violation of OOP,
but rich programming model almost always violates SRP). In my case,
ideal HTTP request processing is this: when request comes, I get all
data in one place (with maximally optimized queries), pass that data to
BL controllers (which act as immutable functions) and write results back
to database in one place (that's great because this "one place" which
reads and writes data is DAL which implementation can contain
transparent caching). So, as I said before, this Squeryl feature we
discuss is not of much use for me: I'd always prefer writing queries by
hand to have maximum control over generated SQL. Thanks to strict
typization, that's now simple and reliable. (In fact, C# programmers got
LINQ and leveraged advantages of anemic model long ago.)

On the other hand, I'm just talking with a guy who loves laziness and
direct access to child collections, hates to remember join points and to
write queries by hand, and does not care about query optimizations. So
he will love this new feature for sure. :)

--
Cheers,
dimgel

http://dimgel.ru/lib.web
Thin, stateless, strictly typed Scala web framework.

[Adam Rabung]

Hi,
So far I only admire from afar, so my question is probably naive.
What is the definition of the SchoolDb2Object class used in the
relations documentation?

I think Squeryl is amazing - keep it up!

Thanks,
Adam

[Maxime Lévesque]

 Oops, will have to add it to the doc...

the examples have been mostly pasted from here :

    http://github.com/max-l/Squeryl/blob/relations/src/test/scala/org/squeryl/tests/schooldb2/SchoolDb2.scala

SchoolDb2Object is just this :

trait SchoolDb2Object extends KeyedEntity[Long] {

  val id: Long = 0

}

Cheers

[Maxime Lévesque]

> How can I specify table name for intermediate many-to-many relation?

I'll add a name override as an extra signature of the "via" method that
takes an alternate name :

  // courseSubscriptions is a ManyToManyRelation, it extends Table[CourseSubscriptions]
  val courseSubscriptions =
    manyToManyRelation(courses, students).
    via[CourseSubscription]("alternateTableName", (c,s,cs) => (cs.studentId === s.id, c.id === cs.courseId)) 


 Cheers

[Philippe Derome]

Too bad it didn't get implemented, it'd be quite more friendly to use. My Postgres JDBC environment seems to insist on the name of the table having double quotes around it.