ManyToMany issues

[naasking]

I'm somewhat new to Scala and squeryl, but we were all getting along
fine until I tried to add a ManyToMany relation. I'm working with
Scala 2.9.1, Squeryl 0.9.5-rc1 and Microsoft SQL Server 2008 R2 with
jtds 1.2.5. Being something of a novice here, I'll no doubt make all
sorts of obvious bone-headed mistakes, but sometimes that feedback is
valuable, so here are my trials and tribulations with ManyToMany.

First, here's the common typed enumeration, and the table
initializations didn't change:

object PowerRating extends Enumeration {
type PowerRating = Value
val KW25 = Value(25000)
val KW50 = Value(50000)
val KW100 = Value(100000)
val KW160 = Value(160000)
}
...
val partType = table[PartType]
on(partType)(x => declare(
x.id is(primaryKey, unique, indexed)
))

val powerRating = table[PowerRating]
on(powerRating)(x => declare(
x.id is(primaryKey, unique, indexed)
))

// the ManyToMany associating the above two
val billOfMaterials =
manyToManyRelation(powerRating, partType)
.via[BillOfMaterial]((r, p, bom) => (r.id === bom.powerRatingId,
p.id === bom.partTypeId))


I started with the obvious class implementations based off of the
documentation:

class PartType(val item : String,
val description : String,
val unitPrice : Int,
val partNumber : String) extends KeyedEntity[Int] {
def this() = this("", "", 0, "")

val id : Int = 0
}
class PowerRating(val power : PowerRating.PowerRating,
val description : String) extends
KeyedEntity[PowerRating.PowerRating] {
def this() = this(PowerRating.KW160, "")
def id = power
}
class BillOfMaterial(val powerRatingId : PowerRating.PowerRating,
val partTypeId : Int,
val quantity : Int) extends
KeyedEntity[CompositeKey2[PowerRating.PowerRating, Int]] {
val id = compositeKey(powerRatingId, partTypeId)
}

I get some sort of "assertion failed" error at runtime:

java.lang.AssertionError: assertion failed
at scala.Predef$.assert(Predef.scala:89) ~[na:na]
at org.squeryl.dsl.QueryDsl$class.org$squeryl$dsl$QueryDsl$
$_splitEquality(QueryDsl.scala:567) ~[squeryl_2.9.1-0.9.5-RC1.jar:
0.9.5-RC1]
at org.squeryl.dsl.QueryDsl
$ManyToManyRelationImpl.<init>(QueryDsl.scala:301)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at org.squeryl.dsl.QueryDsl
$ManyToManyRelationBuilder.via(QueryDsl.scala:258)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
...

Even if I eliminate all the references to the PowerRating enumeration,
the problem persists.

Turns out, the problem has to do with "def id = power" in the
PowerRating class. If I eliminate the PowerRating enumeration in all
classes and keys, and change the aforementioned def into "val id =
power", then everything works as expected. If try to add back the
enumerations in the keys, then I receive the following error:

java.lang.RuntimeException: error executing alter table
[BillOfMaterial] add constraint [BillOfMaterialCPK] unique()
java.sql.SQLException: Incorrect syntax near ')'.
at org.squeryl.Schema$class.org$squeryl$Schema$
$_executeDdl(Schema.scala:243) ~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-
RC1]
at org.squeryl.Schema$$anonfun$_createConstraintsOfCompositePKs
$1.apply(Schema.scala:277) ~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at org.squeryl.Schema$$anonfun$_createConstraintsOfCompositePKs
$1.apply(Schema.scala:275) ~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at scala.collection.mutable.ResizableArray
$class.foreach(ResizableArray.scala:60) ~[na:na]
at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:44)
~[na:na]
at org.squeryl.Schema
$class._createConstraintsOfCompositePKs(Schema.scala:275)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at org.squeryl.Schema$class.create(Schema.scala:182)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
...

The docs use "def" to specify the "id" of a KeyedEntity all the time,
so I have no idea why it caused a problem in this case (a bug?).
Enumerations are also just ints according to the docs, so I don't see
any intrinsic problems with using them in keys. Any insight into why
my limited intellect isn't grasping this would be much appreciated!

Sandro

[Maxime Lévesque]

Here's a variation that will work, the id of PowerRating is the val and 'power' refers to it with a def,

a CompositeKey should always be a def (not a val), I added a note in the doc to this effect. 

Try this variation :  http://pastebin.com/NmjPGVAk

2012/3/26 naasking <naas...@gmail.com>

--
Seuls les poissons morts nagent avec le courant

[naasking]

Yup, that works. Of course, I don't really understand *why* it works,
and why the other variations don't work.

Is there a simple explanation why "def" must be used for the composite
key id, and why using "val" worked as long as I didn't use an
enumeration type in the composite key? Why is id as a def instead of a
val a problem? I'm trying to understand what's going on so I have a
better foundation to diagnose future problems. I presume these
patterns can't be ruled out statically either.

Also, as an additional note you might want to add to the
documentation, squeryl won't declare a field a primary key, even if
you implement KeyedEntity. Obvious in retrospect, due to a simple
misinterpretation of the purpose of of KeyedEntity.

Sandro

On Mar 26, 9:15 am, Maxime Lévesque <maxime.leves...@gmail.com> wrote:
> Here's a variation that will work, the id of PowerRating is the val and
> 'power' refers to it with a def,
> a CompositeKey should always be a def (not a val), I added a note in the
> doc to this effect.
>
> Try this variation :  http://pastebin.com/NmjPGVAk
>

> 2012/3/26 naasking <naask...@gmail.com>

> *Seuls les poissons morts nagent avec le courant*

[naasking]

Another gotcha: you can't use the composite key id to map a oneToMany:

val specSheet =
oneToManyRelation(powerRating, billOfMaterial)
.via((r, b) => r.id === b.id.a1)

This fails with a mysterious runtime error:

java.lang.ExceptionInInitializerError: null
at bootstrap.liftweb.Data$$anonfun$init$2.apply(Data.scala:27)
~[classes/:na]
at bootstrap.liftweb.Data$$anonfun$init$2.apply(Data.scala:25)
~[classes/:na]
at org.squeryl.dsl.QueryDsl$class._using(QueryDsl.scala:46)

~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at org.squeryl.dsl.QueryDsl

$class._executeTransactionWithin(QueryDsl.scala:105)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at org.squeryl.dsl.QueryDsl$class.transaction(QueryDsl.scala:69)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at org.squeryl.PrimitiveTypeMode$.transaction(PrimitiveTypeMode.scala:
40) ~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at bootstrap.liftweb.Data$.init(Data.scala:25) ~[classes/:na]
at bootstrap.liftweb.Boot.boot(Boot.scala:58) ~[classes/:na]
...

You must instead use the underlying val:

val specSheet =
oneToManyRelation(powerRating, billOfMaterial)
.via((r, b) => r.id === b.powerRatingId)

Although this also triggers a runtime error during schema drop, the
application actually runs fine:

java.lang.RuntimeException: Exception while executing statement,
SQLState:23000, ErrorCode:3726
:drop table [User]
at org.squeryl.internals.DatabaseAdapter
$class.execFailSafeExecute(DatabaseAdapter.scala:330)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at
org.squeryl.adapters.MSSQLServer.execFailSafeExecute(MSSQLServer.scala:
23) ~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at org.squeryl.internals.DatabaseAdapter
$class.dropTable(DatabaseAdapter.scala:666) ~[squeryl_2.9.1-0.9.5-
RC1.jar:0.9.5-RC1]
at org.squeryl.adapters.MSSQLServer.dropTable(MSSQLServer.scala:23)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at org.squeryl.Schema$$anonfun$drop$1.apply(Schema.scala:172)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at org.squeryl.Schema$$anonfun$drop$1.apply(Schema.scala:171)

~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at scala.collection.mutable.ResizableArray
$class.foreach(ResizableArray.scala:60) ~[na:na]
at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:44)
~[na:na]

at org.squeryl.Schema$class.drop(Schema.scala:171)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at com.rogers.sitetool.Data$.drop(Data.scala:154) ~[classes/:na]
at bootstrap.liftweb.Data$$anonfun$init$2.apply(Data.scala:27)
~[classes/:na]
at bootstrap.liftweb.Data$$anonfun$init$2.apply(Data.scala:25)
~[classes/:na]
at org.squeryl.dsl.QueryDsl$class._using(QueryDsl.scala:46)

~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at org.squeryl.dsl.QueryDsl

$class._executeTransactionWithin(QueryDsl.scala:105)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at org.squeryl.dsl.QueryDsl$class.transaction(QueryDsl.scala:69)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at org.squeryl.PrimitiveTypeMode$.transaction(PrimitiveTypeMode.scala:
40) ~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at bootstrap.liftweb.Data$.init(Data.scala:25) ~[classes/:na]
at bootstrap.liftweb.Boot.boot(Boot.scala:58) ~[classes/:na]
...

On Mar 26, 9:15 am, Maxime Lévesque <maxime.leves...@gmail.com> wrote:

> Here's a variation that will work, the id of PowerRating is the val and
> 'power' refers to it with a def,
> a CompositeKey should always be a def (not a val), I added a note in the
> doc to this effect.
>
> Try this variation :  http://pastebin.com/NmjPGVAk
>

> 2012/3/26 naasking <naask...@gmail.com>

> *Seuls les poissons morts nagent avec le courant*

[Maxime Lévesque]

Yes, compositeKeys can`t be used as binding expressions in relations,

there's a note on this here :  http://squeryl.org/composite-keys.html 

As to why a composite key cannot be a def, the reason for not supporting them is that it would involve duplicating values, 

in case of 'val' usage this is not a problem, but for a var ex.: 

case C(x: Int, var y: Int) { 

  val ck = compositeKey(x,y)

}

modifying y would render ck invalid.

ideally this would be checked statically, there's a current TODO in my list 

to at least verify at shcema creation time that val composite keys don't exist.

2012/3/26 naasking <naas...@gmail.com>

--

[naasking]

On Mar 26, 2:50 pm, Maxime Lévesque <maxime.leves...@gmail.com> wrote:
> As to why a composite key cannot be a def, the reason for not supporting
> them is that it would involve duplicating values,
> in case of 'val' usage this is not a problem, but for a var ex.:
>
> case C(x: Int, var y: Int) {
>   val ck = compositeKey(x,y)
> }
>
> modifying y would render ck invalid.

Right, because 'var' is a mutable slot in C, not a heap-allocated ref
cell as it would be in ML. Thanks!

I'm running into another assertion violation now [1]. I did away with
the manyToMany, and I'm just using two oneToMany relations:

class PartType(val item : String,
val description : String,
val unitPrice : Int,
val partNumber : String) extends KeyedEntity[Int] {
def this() = this("", "", 0, "")

val id : Int = 0
}

class PowerRating(val id : PowerRating.PowerRating,

val description : String) extends
KeyedEntity[PowerRating.PowerRating] {
def this() = this(PowerRating.KW160, "")

def power = id
lazy val billOfMaterials = Data.specSheet.left(this)

}
class BillOfMaterial(val powerRatingId : PowerRating.PowerRating,
val partTypeId : Int,
val quantity : Int) extends
KeyedEntity[CompositeKey2[PowerRating.PowerRating, Int]] {

def id = compositeKey(powerRatingId, partTypeId)
lazy val partType = Data.bomChart.left(this)
}
object Data extends Schema {

val powerRating = table[PowerRating]
on(powerRating)(x => declare(
x.id is(primaryKey, unique, indexed)
))

val billOfMaterial = table[BillOfMaterial]
on(billOfMaterial)(x => declare(
x.id is(primaryKey, unique, indexed),
x.powerRatingId is(indexed)
))

val specSheet =
oneToManyRelation(powerRating, billOfMaterial)
.via((r, b) => r.id === b.powerRatingId)

val bomChart =
oneToManyRelation(billOfMaterial, partType)
.via((b, p) => b.partTypeId === p.id)
}

If I comment out the bomChart and the corresponding lazy value, I just
get the usual drop error [2]. I still don't know what that error is
about either. Any thoughts on these two problems? This is exactly the
same structure I use for other classes with oneToMany, but only these
ones are triggering an error.

Sandro

[1] java.lang.AssertionError: assertion failed

at scala.Predef$.assert(Predef.scala:89) ~[na:na]
at org.squeryl.dsl.QueryDsl$class.org$squeryl$dsl$QueryDsl$
$_splitEquality(QueryDsl.scala:567) ~[squeryl_2.9.1-0.9.5-RC1.jar:
0.9.5-RC1]
at org.squeryl.dsl.QueryDsl

$OneToManyRelationImpl.<init>(QueryDsl.scala:500)

~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at org.squeryl.dsl.QueryDsl

$OneToManyRelationBuilder.via(QueryDsl.scala:472)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at com.rogers.sitetool.Data$.<init>(Data.scala:192) ~[classes/:na]
at com.rogers.sitetool.Data$.<clinit>(Data.scala) ~[classes/:na]
at bootstrap.liftweb.Data$$anonfun$init$1.apply$mcV$sp(Data.scala:27)
~[classes/:na]
at bootstrap.liftweb.Data$$anonfun$init$1.apply(Data.scala:25)
~[classes/:na]
at bootstrap.liftweb.Data$$anonfun$init$1.apply(Data.scala:25)

~[classes/:na]
at org.squeryl.dsl.QueryDsl$class._using(QueryDsl.scala:46)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at org.squeryl.dsl.QueryDsl
$class._executeTransactionWithin(QueryDsl.scala:105)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at org.squeryl.dsl.QueryDsl$class.transaction(QueryDsl.scala:69)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at org.squeryl.PrimitiveTypeMode$.transaction(PrimitiveTypeMode.scala:
40) ~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at bootstrap.liftweb.Data$.init(Data.scala:25) ~[classes/:na]
at bootstrap.liftweb.Boot.boot(Boot.scala:58) ~[classes/:na]

[2] java.lang.RuntimeException: Exception while executing statement,

SQLState:23000, ErrorCode:3726
:drop table [User]
at org.squeryl.internals.DatabaseAdapter
$class.execFailSafeExecute(DatabaseAdapter.scala:330)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at
org.squeryl.adapters.MSSQLServer.execFailSafeExecute(MSSQLServer.scala:
23) ~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at org.squeryl.internals.DatabaseAdapter
$class.dropTable(DatabaseAdapter.scala:666) ~[squeryl_2.9.1-0.9.5-
RC1.jar:0.9.5-RC1]
at org.squeryl.adapters.MSSQLServer.dropTable(MSSQLServer.scala:23)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at org.squeryl.Schema$$anonfun$drop$1.apply(Schema.scala:172)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at org.squeryl.Schema$$anonfun$drop$1.apply(Schema.scala:171)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at scala.collection.mutable.ResizableArray
$class.foreach(ResizableArray.scala:60) ~[na:na]
at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:44)
~[na:na]
at org.squeryl.Schema$class.drop(Schema.scala:171)
~[squeryl_2.9.1-0.9.5-RC1.jar:0.9.5-RC1]
at com.rogers.sitetool.Data$.drop(Data.scala:154) ~[classes/:na]

at bootstrap.liftweb.Data$$anonfun$init$1.apply$mcV$sp(Data.scala:27)
~[classes/:na]
at bootstrap.liftweb.Data$$anonfun$init$1.apply(Data.scala:25)
~[classes/:na]
at bootstrap.liftweb.Data$$anonfun$init$1.apply(Data.scala:25)