Code Review for Unique Key Constraints

[Calen Pennington]

As mentioned is this issue
(http://github.com/dpp/liftweb/issues#issue/19), and as came up on the
list recently, Lift currently has no way to specify that a field or an
index be unique. I've coded up a patch that addresses this, and could
also be used for other index types on a project specific basis (for
instance, FULLTEXT or SPATIAL indexes in mysql).

-Cale

[DFectuoso]

I for one would like to say: Cool! Thanks! If i need unique indexes in
the next couple of weeks i'll get this baby to the war( .war thats
it ) =)

On Jul 9, 11:30 am, Calen Pennington <calen.penning...@gmail.com>
wrote:

>  0001-Adding-the-ability-to-create-UNIQUE-indexes-over-sin.patch
> 5KViewDownload

[Derek Chen-Becker]

I've pushed a smaller commit into the wip-dcb-unique-indices branch on GitHub that adds a UniqueIndex case class. Conceivably we could add other types of indices if there's a need. If no one has any objections to what I've added I can merge with trunk on Monday.

Derek

http://github.com/dpp/liftweb/tree/wip-dcb-unique-indices

[Calen Pennington]

Hey, I'm glad the code could make it in. One comment on your changes:
It seems to me that using the pattern matching in ensureIndexes puts
Lift in the position of maintaining support for various DBs, rather
than letting the client code do it in a project by project basis (for
instance, the FULLTEXT and SPACIAL index types that I mentioned, that
are mysql specific.) Is there a reason that you preferred that over
allowing client code to specify the index type?

-Cale

[Derek Chen-Becker]

I'll add a UserIndex type that will let you specify the creation of the index directly. Unique indices are generally supported across all DBs, AFAIK, so it makes it more clear to have a specific type.

Derek

[Derek Chen-Becker]

OK, on wip-dcb-unique-indices there's now a GenericIndex that should give you full flexibility to do whatever you want for an index. I'll hold until tomorrow to merge with trunk.

Derek

[Derek Chen-Becker]

OK, it's merged. An example usage of the new GenericIndex is:

package com.test.model

import _root_.net.liftweb.mapper._

class DateItem extends Mapper[DateItem] with IdPK {
  def getSingleton = DateItem
  object timestamp extends MappedDateTime(this)
  object time extends MappedTime(this)
  object date extends MappedDate(this)
}

object DateItem extends DateItem with MetaMapper[DateItem] {
  override def fieldOrder = date :: time :: timestamp :: Nil
  override def dbIndexes = GenericIndex({ (table,columns) => String.format("CREATE UNIQUE INDEX %s ON %s %s", "myindex_" + table + "_" + columns.mkString("_"), table, columns.mkString("(", ",", ")"))}, IHaveValidatedThisSQL("Derek", "2009-07-13"), date) :: Nil
}

It's basically up to you to generate the DDL statement based on the table and column names.

Derek

[Calen Pennington]

So, I'm trying to make use of this code, now that it's in the git repo.

However, I'm running into the follow issue:

In my model, I have

object RecipeIngredient extends RecipeIngredient with LongKeyedMetaMapper[RecipeIngredient] {
  override def dbIndexes = UniqueIndex(new IndexField(recipe), new IndexField(order)) :: Nil
}

However, when the schemifier runs, I get a match error:

scala.MatchError: UniqueIndex(Array(IndexField(NULL), IndexField(0)))
    at net.liftweb.mapper.Schemifier$$anonfun$net$liftweb$mapper$Schemifier$$ensureIndexes$2.apply(Schemifier.scala:266)
    at net.liftweb.mapper.Schemifier$$anonfun$net$liftweb$mapper$Schemifier$$ensureIndexes$2.apply(Schemifier.scala:261)
...

It seems that the only way that the UniqueIndex wouldn't match would be if there was a type inconsistency. However, I can't figure out how that could be happening. (For reference, I get the same error if I use an Index, rather than a UniqueIndex).

Any thoughts?

Thanks
-Cale

[Derek Chen-Becker]

Try just

object RecipeIngredient extends RecipeIngredient with LongKeyedMetaMapper[

RecipeIngredient] {
  override def dbIndexes = UniqueIndex(recipe, order) :: Nil
}

Also, there was a bug in index creation. I just checked in a fix.

Derek