proof of concept for poso lifecycle event callbacks (was : Insert and database default values)

[Maxime Lévesque]

Hi, I just checked in this proof of concept (branch "lifecycle-events"),

   https://github.com/max-l/Squeryl/commit/0ef39f2ef41620ac9e34cabe954ff90e9db3f505

this is how you use it :

object ASchema extends Schema {

//....

  val books = table[Book]

    def afterSelect(p: AnyRef) =
      println("this is a callback that get's called whenever an AnyRef is selected from the "+
              "database (after being created and mapped from the result set)")

    def afterSelect(p: Book) =
      println("this callback overrides the previous because it is more specific (Book is a subtype of AnyRef)")
     
    def beforeInsert(p: Book) =
      println("The book " + p.title + " is about to be inserted")
      //available callbacks are :
      //beforeInsert, afterInsert, beforeDelete, afterDelete, beforeUpdate, afterUpdate, afterSelect
}


The following callbacks are available :

   beforeInsert, afterInsert, beforeUpdate, afterUpdate, afterSelect

I consider this a prototype, because after implementing it, I think I would preffer this way of declaring the
callbacks, note that the methods are substituted for closures, I preffer it because there is better static validation
and a bit less magic, i.e. the  above mechanism can suffer silently from misspellings :

object ASchema extends Schema {

//....
  val books = table[Book]

  override def callbacks = Seq(
    afterSelect((p: AnyRef) => println("this is a callback that get's called whenever an AnyRef is selected from the database (after being created and mapped from the result set)")),
    afterSelect((p: Book) => println("this callback overrides the previous because it is more specific (Book is a subtype of AnyRef)")),
    beforeInsert((p: Book) =>  println("The book " + p.title + " is about to be inserted"))
  )
}


The first is the less verbose, while the second is a bit more type safe, I have a slight preference for the second,...

Note that factory methods for Posos could be introduced this way :

  override def callbacks = Seq(
    afterSelect((p: AnyRef) => println("this is a callback that get's called whenever an AnyRef is selected from the database (after being created and mapped from the result set)")),
    afterSelect((p: Book) => println("this callback overrides the previous because it is more specific (Book is a subtype of AnyRef)")),
    beforeInsert((p: Book) =>  println("The book " + p.title + " is about to be inserted"))
    create({
       //...
       new Book(...) // the callbyname would become the factory method for Book
    }),
    create(new Author(...))
  )


The create method would be defined as :

   def create[A](f: =>A)(implicit manifestT: Manifest[A]) = ....

Cheers

2010/11/23 Maxime Lévesque <maxime....@gmail.com>

Hi, I just opened this issue :

  https://github.com/max-l/Squeryl/issues#issue/70

implementing callbacks for lifecycle events, i.e. :

(pre|post)insert,

(pre|post)update

(post)select

(pre|post)delete

is what you need, I'm thinking that callbacks could be methods in the Schema like this one :

def afterSelect(o: ATraitOrClass) = {...}

in this case all Query[]s returning objects from a Table[T] where T <: ATraitOrClass

will cause the callback to be called.

In your case, you would declare :

def beforeInsert(t: YourCommonAncestorTrait) = {

   t.sessionStartTime = Session.currentSession.startTime // note that Session doesn't have a startTime, but it could....

}

The callback could also be on a per table basis :

yourTable.beforeInsert(t=>

   t.sessionStartTime = Session.currentSession.startTime 

)

In the meantime, you can do this hack, by overriding DatabasAdapter.writeInsert : 

class YourDatabaseAdapter(...) extends MySqlAdapter (...) {

def writeInsert[T](o: T, t: Table[T], sw: StatementWriter):Unit = {

super.writeInsert(o,t,sw)

   t.asInstanceOf[YourTrait].sessionStartTime = Session.currentSession.asInstanceOf[YourCustomSession].startTime 

    }

}

and : 

class YourCustomSession extends Session {

val startTime = new Date

}

but of course this is a hack... The real solution is to have a callback mechanism,

they should be pretty easy to write, stay tuned for issue #70, if you want to try

implementing it you can fork the repo.

Cheers

On Tue, Nov 23, 2010 at 2:02 AM, Kristian Domagala <kristian...@gmail.com> wrote:

Thanks for the reply.

Yes, current_timestamp is the same as now(). I tried your suggestion, and regardless of whether createdDate is set to new Date or null, it does not cause the date to be set from the database using the now() function (instead, it sets it to the current client date and null respectively).

In my case, I could possibly work around the issue of the generated date not being returned by the insert statement (although that would be nice), but it is a show-stopper if I can't somehow get the date to be generated from the database itself, set to the start time of the current transaction.

Are there any plans for this sort of thing in future releases of Squeryl? Is there an existing part of the library that would be amenable to adding this functionality in the form of a patch?

I've had a bit of a look, and I wonder if it would be possible to have a special type for database-generated values that could be assigned as a default value or even set as an explicit value on the entity. At query execution time, it's expanded to 'now()', and the generated value is included in the RETURNING statement and returned with the result. Alternatively, I noticed the '&' function for "evaluating arbitrary expressions on the database side", so maybe something could be worked in to that.

I can imagine other functions (even if they're custom or db-specific) that this kind of functionality could be useful for. If you can think of anything else I can try with the current (or upcoming) library, or can suggest anywhere I can look to possibly add something, that would be appreciated.

Cheers,
Kristian.

2010/11/22 Maxime Lévesque <maxime....@gmail.com>

 You are right, defaultsTo only affects the DDL generation, 

in your example is current_timestamp  a synonym of a now() function on the DB side ?

If yes, I am assuming you want the value retrieved from the DB after the insert and assigned to your

object on created_date, is that correct ?

Could you live with this solution :

class YourClass(val z: Int,...) {

  val createdDate = new Date

}

? 

you can then choose to kep the defaultsTo declaration or not, if you do, it will only serve 

for inserts done with createdDate set to null. The only down side I can see is that you 

have the createdDate created on the client when using Squeryl, and by the DB otherwise,

so the two clocks must be reasonably in sync.

Cheers

On Mon, Nov 22, 2010 at 1:28 AM, Kristian Domagala <kristian...@gmail.com> wrote:

Hi,

Is it possible to define a schema in a way such that when records are inserted into the database, columns that have a default value expression specified in the database schema are initialised to those values, and not the values that are set on the corresponding entity?

For example, if there was an existing table with a created_date column that defaults to current_timestamp (ie, start of current transaction), would it be possible to use Squeryl and have the corresponding insert statement to be generated without the created_date column specification so that it uses the current_timestamp value instead?

I've had a look at the defaultsTo column declaration, but it only seems to affect how the DDL is generated, and from what I tell so far, is not used when constructing the insert query.

I hope I haven't missed any previous discussion about this, but I've only just started looking into Squeryl and couldn't find any relevant search results on the topic.

Cheers,
Kristian.

[Kristian Domagala]

Hi Maxime,

Again, thank you for your reply and for looking in to this.

If I understand you correctly, the solution is still all client-side - is that right? Eg, in my case, the timestamp is generated by the client, not from the now() function in the database?

I'll have a bit more of a look at the solution over the next few days and see what I can do with it.

Cheers,
Kristian.

2010/11/25 Maxime Lévesque <maxime....@gmail.com>

[Maxime Lévesque]

Yes, this solution is client side,

the callback mechanism has evolved a bit (only in my mind though... !) :

  def callbacks = Seq(
    beforeInsertInto(professors) call (p => println(p + " will be inserted in " + professors)),
    beforeInsertOf[Professor] call (p => println("!")),
    factoryFor(professors) is (new Professor),
    factoryFor[Professor] is (new Professor)
  )

you could then do something like this :

  afterInsertInto(professors) call (p =>
    val nowFromDB = from(professors)(p0 => where(p0.id === p.id) select(p0.now))
    p.now = nowFromDB
  )

now of course you don't want to write something like this for each table, so you could do :

    for(t <- allTablesThatAreKeyedEntities)
      yield afterInsertInto(t) call (o =>
              val now = from(t)(t0=>select(t0.now) where(t0.id === o.id))
            )
note that you need a common trait in order to do a generic lookup, KeyedEntity[] suits the purpose in this example.

note also that it will be costly to bring the 'now from DB' on the client : you need a select after each insert,
there's only one way you can avoid this cost : generate a stored proc for each insert that returns the 'now' and call it,
in my opinion, it's a lot of work for not much...



It seem like your need to put start time on db objects has a more generic goal : to give traceability,
if that's the case I think you can do better, with less work (and less dependency on fancy features ;-) ) :

Suppose you have this table :

  class Transaction(val id: Long, val startTime: Timestamp)

and that one Transaction get's created for every new Session,

you write yourself a custom session :

class YourCustomSession(val tx: Transaction, c: Connection, adapter: DatabaseAdapter) extends Session(c, adapter)

Session.concreteFactory = () => {
   val c = ...jdbcConnection
   val a = ...dbadapter
    val tx = using(new Session(c,a) {
       transactions.insert(new Transaction(new Timestamp))
    }
    new YourCustomSession(tx, c, a)
}

trait TraceableObject {

   val creatorTransactionId: Long =
       if(Session.hasCurrentSession)
         Session.currentSession.asInstanceOf[YourCustomSession].tx.id
       else -1
}

now that you have a table for session related info, you can pack a lot of usefull things in it :

  class Transaction(val id: Long, val startTime: Timestamp, userId: Long, val entryPointmethod: String, val webPageCalledFrom: String, endTime: Timestamp, completionStatus: Int)

of course you will eventually want a "updaterTransactionId" field also, then a "beforeUpdate" becomes necessary....


Cheers