Auditing feature

[rreckel]

Hi,

It would be interesting to implement an auditing feature into squeryl.
I want to explain a bit how this could work and want your suggestions
before starting to implement it.

There a a few auditing strategies out there with their pros and cons,
but a very easy strategy works as follows:

First we need a table (entity) that holds the different revisions (or
transactions if you want ;-))
This table consist basically of an id and a timestamp, but has to be
user configurable.
So perhaps the user want to store the username who initiated the
transaction, or an transaction name,etc....

Second for every table audited, we need to duplicate the table with
the same schema, plus a revisionId and revisionType columns.
This way we can make the relation between the entity and the revision
in time. Multiple entities can be changed, created or deleted in a
transaction, so the revisionId will be the same for every entity in
the audit tables. It should also be customizable if we want every
column set to 'null' when we delete the entity or copy the last
entity's values.

Let's start with a simple example to make the system clear:

case class Revision(id: Long, timestamp: Date)
case class Person(id: Long, name: String, lastname: String)
case class Person_aud(id: Long, revId: Long, revType: Long, name:
String, lastName: String)

Now first we insert the Person(1, "Mickey", "Mouse)
then the Person table will only hold this row.

Assuming that the RevionType code are:
0 => Insert
1 => Update
2 => Delete

The revision created whould be: Revision(1, new Date())
and the audit table would be: Person_aud(1, 1, 1, "Mickey", "Mouse")

Second we update the Person to Person(1, "Donald", "Duck")

The revision created will be: Revision(2, new Date())
and Person_aud(1, 2, 2, "Donald", "Duck")

Deleting the Person is obvious:

A new Revision with id=3 is created and a Person_aud(1, 3, 3, null,
null) or Person_aud(1, 3, 3, "Donald", "Duck")

This strategy is very basic but meets the needs of 99% of the use
cases (obviously that's only my opinion ;-))

What do you think of it?

In the next post I will try to explain how this could be done in
squeryl.

Thanks
Roll

[Maxime Lévesque]

Here's a question :

- Is dealing with mass update outside the scope of this feature (update(t)(t0=> set(t0:= 123) where(true) ))

  I'm 70% convinced that it should be, but there is maybe a way then to prevent compilation

  if mass update or delete statements when a trait Auditable is present... Now that I thought about it

  fo 10 minutes I like the idea of supporting it more and more, ... I'm now at 50% ;-)

- Could your concept of Auditability be split into Audited, and Versioned,

by this I meand that there could be 2 traits :

 Audited, that just has a transactionId of who did the last modif, but not version history kept,

while Versioned would be exactly what you are describing.

The reasoning here is that sometime you want to know things like who did the last modif

on a row, without needing full blown versioning, so it would be nice to have 

incremental functionality here.

(note that with this approach Transaction might be a better word than Revision....).

- This feature could either be enabled in a persistent class via :

  i) annotation

  ii) trait 

  iii) DSL expressions in the Schema Definition, ex :

      val persons = table[Person].versioned[PersionAud]

     // then person is a VersionedTable[Person], exactly same as Table[Person] but with

     // an added "versions" property :

      from(persons.versions)(p=> where() select(p))

     // and perhaps also those methods :

      persons.versionsOf(aPerson)

      persons.versionsOf(idOfAPerson)

- Another thing that crosses my mind is that probably this feature should require that 

auditable entities be KeyedEntities, but not 100% sure here....

That was my $0.02 ...

[rreckel]

Hi,

Using the example, how could we define the auditing on the table or
columns?

In the Schema one could write the follwing:

val revisions = table[Revision]

val persons = table[Person]

val persons_aud = auditTable[Person] with revisions

if we wanted every column of that table audited, and/or

on(persons)(p => declare(
p.name is(audited)
))

if we wanted only some of the columns audited.

I think that would be fairly simple an explicit, but I don't know
squeryl enough to be sure that this would work!
Perhaps something like:
val persons = table[Person] auditedWith revisions
whould be enough/better?

To read the revisions we would need some basic queries like:
* Get the revision x of entity y
* Get all the revisions of entity y
and some utility queries like:
* Get timestamp of revision x
* Get Revision of timestamp d
etc....

The first 2 could be implemented in a trait:
trait AuditedTable[A] {

def revisions: List[Revision] = {...}
def revision(id: Long):A = {...}
}

The helper functions could be implemented in an Revision companion
class....

The functions insert, update, delete of the Table class have to be
overridden to insert the entities into the audit tables.
The revision could be a problem as it has to be linked to the current
session.

What do you think?
Am I missing something?
Is there a better DSL possible?

Thanks for your comments!
Roll

[rreckel]

Hi,

Sorry I didn't read your post before mine ;-)

I think to split between Audited and Versioned is a very nice idea!
Specially thinking of that AuditedEntity could be a trait just like
KeyedEntity with
def updateTimestamp: Date
def updateUsername: String

As for the batchUpdates:
Audited should be fairly simple as one has only to add: set
updateTimestamp = ?, set updateUsername= ? to the update query
Versioned could be more difficult as one has to now exactly which
entities will (or have been) updated.
I don't know if the jdbc drivers could return the ids of the updated
rows....
Unless one would need to do a select first to get the entities to be
updated and insert them into the audit tables after the actual update.

I also think that your third proposal (DSL expressions) is the way to
go.
In my opinion, annotations are well overused ;-)
A good mixture of traits and dsl is far better....

I dont think that the entities have to be KeyedEntities as we select
them with there revisionId.
But if we want something like:
Get the revisions of Person with id x, then of course KeyedEntity is
mandatory...

Anyway, thanks for your interest in the subject ;-)
and keep me updated!
Roll

[Maxime Lévesque]

 

I think to split between Audited and Versioned is a very nice idea!
Specially thinking of that AuditedEntity could be a trait just like
KeyedEntity with
def updateTimestamp: Date
def updateUsername: String

 The way I was envisioning Audited is :

trait Audited {

  def transactionId: Long

}

 

then timestamp, username, etc would go in the Transaction (revision) table, 

then we can have :

trait Versioned extends Audited

About the relevance of requiring KeyedEntity[K], it allows to define this :

class VersionedTable[B,A](classOfA: Class[_], auditTable: Table[B]) extends Table[A] {

    def history: OneToManyRelation

}

class Table[A] {

    // the implicit param 'ev' requires that B extends A, in addition to the constraint that it implements Versioned.

    // the manifest is there to obtain the class of B :

    def versioned[B <: Versioned]()(implicit ev: B <:< A, m: Manifest[B]) = { 

         val auditTable = new  VersionedTable(this.classOfA, m.erasure) {

            val history = oneToManyRelation(this, auditTable).via((a,b) => a.id === b.id)

       }

       auditTable

    }

}

Now since VersionTable has a oneToMany, we get this magic :

class Person(...) {

   lazy val versions = personTable.history.left(this)

}

This is almost pseudo code (uncompiled and untested...), but the point I'm trying to make is that 

making KeyedEntity a prerequisit, buys you an easy way to use a one to many relation.

There is a demand for loosening the requirement of being a KeyedEnty in relations, 

and to require only the existence of an 'id' field. When that get's done, this feature 

will benefit from it, in that the requirement will be only to have an 'id' field...

[Maxime Lévesque]

 Here I've put the 'versioned' method in table, but it would be better as available via an implicit

conv only avalable in Schema :

implicit def table2VersionedTablePrecursor[A](t: Table[A])

class VersionedTablePrecursor[A] {

    // the implicit param 'ev' requires that B extends A, in addition to the constraint that it implements Versioned.

    // the manifest is there to obtain the class of B :

    def versioned[B <: Versioned]()(implicit ev: B <:< A, m: Manifest[B]) = { 

         val vTable = new  VersionedTable(this.classOfA, m.erasure) {

            val history = oneToManyRelation(this, auditTable).via((a,b) => a.id === b.id)

       }

       vTable

[rreckel]

All this seems very right to me ;-)
I'm pretty convinced that it could work out!

Now something gets to my mind, and I don't know if I'm wrong or not.
So here a simple example using the Versioned trait:

case class Address(id: Long, street: String) extends KeyedEntity[Long]

abstract class PersonBase(id: Long, name: String, addressId: Long)
extends KeyedEntity[Long] {
lazy val adress = addressToPersons.right(this).headOption
}

case class Person(id:Long, name: String, addressId: Long) extends
PersonBase(id, name, addressId)
case class PersonVersion(id: Long, transactionId: Long, name: String,
addressId: Long) extends PersonBase(id, name, addressId) with
Versioned

This seems simple, but:
the relation addressToPersons in the base class is not possible as we
cannot define it in the Schema with PersonBase:

val persons = table[Person].versioned[PersonVersion]
val adresses = table[Address]

val addressToPersons = oneToManyRelation(addresses, persons).via((a,
p) => ea.id === p.addressId)

so calling the addressToPersons.right(this) will throw a compilation
error telling that "this" has to be Person and not PersonBase

Am I thinking in the wrong direction?
Is there an easy way to solve this problem?

Thanks for your thoughts!
Roll

BTW: The PersonBase class is needed because case class inheritance is
prohibited!!!


On Mar 10, 10:34 am, Maxime Lévesque <maxime.leves...@gmail.com>

[rreckel]

Sorry but I still got something wrong in my previous post:

PersonBase cannot extend KeyedEntity because PersonVersion's primary
key is (id, transactionId) (Composite key!)
Unless we could only insert one version of Person in it's audit
table ;-))

this means Person extends KeyedEntity
but PersonVersion not!

But this won't break the magic of the history relation, will it?

[Maxime Lévesque]

I see....

How about if we replace inheritance by composition ?

case class Person(...)

class Version[A](val transactionId: Long, val revisionType: RevisionType, val versionElement: A)

 val persons = table[Person].isVersioned

 persons.historyOf(aPerson): Version[Person]

now to create a Table[Person] that has the extra fields (transactionId, and revisionType)

that woule be a mater of 1) creating a Table[Person], and then adding 2 FieldMetaData

to it's posoMetaData ...

How's that ?

[rreckel]

This will work as it does not need any changes to the Person Poso!
What's nice too: No need to create a new "Versioned" class per class
to the model!

Now, I do not have enough knowledge of the inner workings of squeryl,
to implement the "isVersioned" method :-(

Could you please provide some info, or a starting point? (Like: how to
add FieldMetaData to a PosoMetaData or Table etc....
Help is much appreciated ;-))


On Mar 10, 12:21 pm, Maxime Lévesque <maxime.leves...@gmail.com>
wrote:

[Maxime Lévesque]

 I started to write the thing, and got me reflecting on a disadvantage of

this composition approach, that you can not query the history table

as easily as a normal table, for example if you want to do :

from(persons)(p=> compute(max(p.versionNumber)))

there is a work around though : to create a org.squeryl.View with an "augmented" class.

I wonder if it would be a good idea to have this low level feature : 

the ability to define tables that map to two classes :

  Table[(A1,A2)]

i.e. the history table would be a :

 table[(Person,Version)]

... I'm not sure it's worth the trouble though, not to mention the added complexity,

I'll create a branch shortly with a start of solution #1 (above...)

[Maxime Lévesque]

 Ok, I'm thinking that if we want to go the "composition" route, we first have to 

support this :  table[(Person,Version)] as a lower level feature, and then

build the feature on top of it... Frankly, I don't think it's worth introducing

this complexity (I'm talking of generic support for Table[(A1,A2)]),

so I'm back to your initial idea, so :

  val persons = versionedTable[Persion,PersonHistory]

then : 

  persons.history.where(_.id === idOfAPerson)

... no oneToMany, no composition, etc, so my conclusion is that

with this we have 90% of the functionality, with 10% of the complexity ...

I pushed a branch 'versioning-and-auditing' 

It looks like a couple of overrides in VersionedTable could do it...

of course, nothing is set in cement, everything is open for debate...

2011/3/11 Maxime Lévesque <maxime....@gmail.com>

[Maxime Lévesque]

I forgot to add that in versionedTable[A,B]

B only needs to extend : Versioned, so it can have a subset of the fields in A,

or could extend A, or A and B could extend a common class...

2011/3/11 Maxime Lévesque <maxime....@gmail.com>

[rreckel]

I got the branch and tested a bit the versionedTable:

The Schema is ok and even DDL is correctly generated ;-))
But thinking of it, I want to share some of my ideas:

1) How could a special declaration an the historytable be made?
val persons = versionedTable[Person,PersonHistory]
Now we can write:
on(persons) (p => declare(
p.name is(dbType("VARCHAR2(4000)"))
But not on personHistory, so won't it be possible that
versionedTable() returns a Pair of the tables so we could write:
val (persons, personVersions) = versionTable[Person, PersonHistory]

2) The Versioned trait defines vals....
Is this intended, or could it be defs.
What are the pros/cons between vals or defs?

3) What do you think of filling the history table?
I mean, do you want to fill in the properties which are common (by
name and/or type) between the class A and B?
Is there a way that the application could fill in some special
properties (by calling some sort of listener or something)?
And how do you think the TransactionTable should look like and be
filled?

These are just some ideas to think about and to keep things moving ;-)

Thanks for this very quick Kick Start!!!
It is really a pleasure to work and test on something while the ideas
are still fresh! ;-)

On Mar 11, 1:22 pm, Maxime Lévesque <maxime.leves...@gmail.com> wrote:
> I forgot to add that in versionedTable[A,B]
>
> B only needs to extend : Versioned, so it can have a subset of the fields in
> A,
> or could extend A, or A and B could extend a common class...
>

> 2011/3/11 Maxime Lévesque <maxime.leves...@gmail.com>

>
>
>
>
>
> >  Ok, I'm thinking that if we want to go the "composition" route, we first
> > have to
> > support this :  table[(Person,Version)] as a lower level feature, and then
> > build the feature on top of it... Frankly, I don't think it's worth
> > introducing
> > this complexity (I'm talking of generic support for Table[(A1,A2)]),
> > so I'm back to your initial idea, so :
>
> >   val persons = versionedTable[Persion,PersonHistory]
>
> > then :
>
> >   persons.history.where(_.id === idOfAPerson)
>
> > ... no oneToMany, no composition, etc, so my conclusion is that
> > with this we have 90% of the functionality, with 10% of the complexity ...
>
> > I pushed a branch 'versioning-and-auditing'
>
> > It looks like a couple of overrides in VersionedTable could do it...
>
> > of course, nothing is set in cement, everything is open for debate...
>

> > 2011/3/11 Maxime Lévesque <maxime.leves...@gmail.com>

>
> >>  I started to write the thing, and got me reflecting on a disadvantage of
> >> this composition approach, that you can not query the history table
> >> as easily as a normal table, for example if you want to do :
>
> >> from(persons)(p=> compute(max(p.versionNumber)))
>
> >> there is a work around though : to create a org.squeryl.View with an
> >> "augmented" class.
> >> I wonder if it would be a good idea to have this low level feature :
>
> >> the ability to define tables that map to two classes :
>
> >>   Table[(A1,A2)]
>
> >> i.e. the history table would be a :
>
> >>  table[(Person,Version)]
>
> >> ... I'm not sure it's worth the trouble though, not to mention the added
> >> complexity,
> >> I'll create a branch shortly with a start of solution #1 (above...)
>

> ...
>
> read more »

[Maxime Lévesque]

1) How could a special declaration an the historytable be made?
val persons = versionedTable[Person,PersonHistory]

Now we can write:
on(persons) (p => declare(

  p.name is(dbType("VARCHAR2(4000)"))
But not on personHistory, so won't it be possible that
versionedTable() returns a Pair of the tables so we could write:
val (persons, personVersions) = versionTable[Person, PersonHistory]

You can do :

on(persons.hstory) (p => declare(

  p.name is(dbType("VARCHAR2(4000)"))

 

since persons.hstory is a table as any other, no ?

2) The Versioned trait defines vals....
Is this intended, or could it be defs.
What are the pros/cons between vals or defs?

 That was just a quick hack on my part, I think it should be a trait

with only defs (the same way KeyedEntity has a def 'id').

 

3) What do you think of filling the history table?
I mean, do you want to fill in the properties which are common (by
name and/or type) between the class A and B?

Exactly, the intersection of A and B, of fields with same name and type.

 

Is there a way that the application could fill in some special
properties (by calling some sort of listener or something)? 

And how do you think the TransactionTable should look like and be
filled?

there could be this new method in schema : 

trait Schema {

   def transactionTable[A <: KeyedEntity[Long]]: Option[Table[A] = None

}

one can do : 

val = myTransactionTable = ...

def transactionTable = Some(myTransactionTable)

the fact that it is an option makes it non mandatory, one could use versioning without such a table.

About introducing "beforeInsertIntoHistory" listener, see this thread :

https://groups.google.com/d/topic/squeryl/s0f8JHg4vbY/discussion

the callback mechanism would work with history tables as well :

  def callbacks = Seq(
    beforeInsertInto(professors) call (p => println(p + " will be inserted in " + professors)),

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

These are just some ideas to think about and to keep things moving ;-)

Thanks for this very quick Kick Start!!!
It is really a pleasure to work and test on something while the ideas
are still fresh! ;-)

Well, thanks for taking the initiative on this topic ! 

[rreckel]

Hi again,

I've been working a little bit on this feature, and I wanted to share
the first draft, which is, beware, not working correctly in some
cases:

First I changed, as you said, the Versioned trait to:

trait Versioned {
def transactionId: Long
def historyEventType: HistoryEventType.Value
def versionNumber: Int
}

Second I added a little helper method to create a TransactionTable.
This table is not mandatory to get the different versions of the
entities, but helps keep the changes grouped:

def findTransactionTable =
_tables.find(_.isInstanceOf[TransactionTable[_]])

def transactionTable[A <: KeyedEntity[Long]](builder: () => A)
(implicit mA: Manifest[A]) = {
val t = new TransactionTable[A](tableNameFromClass(mA.erasure),
mA.erasure.asInstanceOf[Class[A]], this, None, builder)
_addTable(t)
t
}

and now the bigger part in Table:

class TransactionTable[A <: KeyedEntity[Long]](n: String, c: Class[A],
schema: Schema, _prefix: Option[String], val builder: () => A)
extends Table[A](n,c,schema, _prefix) {
}

class VersionedTable[A,B <: Versioned](n: String, c: Class[A], schema:
Schema, _prefix: Option[String], val history: Table[B])
extends Table[A](n,c,schema, _prefix) {

override def insert(t: A): A = {
super.insert(t)
history.insert(createVersion(t, HistoryEventType.Created))
t
}

override def insert(e: Iterable[A]):Unit = {
super.insert(e)
val versions = e.map(a => createVersion(a,
HistoryEventType.Created))
history.insert(versions)
}

override def update(o: A)(implicit ev: <:<[A, KeyedEntity[_]]) {
super.update(o)
history.insert(createVersion(o, HistoryEventType.Updated))
}

private def createVersion(a: A, historyEventType:
HistoryEventType.Value):B = {
val copy = history._createInstanceOfRowObject.asInstanceOf[B]
val fmds = history.posoMetaData.fieldsMetaData.map(fmd =>
Pair(fmd, posoMetaData.fieldsMetaData.find(_.nameOfProperty ==
fmd.nameOfProperty)))
fmds.foreach({case(hfmd, fmd) => (fmd.foreach(f => hfmd.set(copy,
f.get(a.asInstanceOf[AnyRef]))))})

val tid =
history.posoMetaData.fieldsMetaData.find(_.nameOfProperty ==
"transactionId")
tid.foreach(_.set(copy, new java.lang.Long(transactionId)))

val het =
history.posoMetaData.fieldsMetaData.find(_.nameOfProperty ==
"historyEventType")
het.foreach(_.set(copy, new Integer(historyEventType.id)))
copy
}

private def transactionId = {
schema.findTransactionTable match {
case Some(t:TransactionTable[_]) =>
Session.currentSession.transactionId match {
case Some(id) => id
case _ => {
val tid = t.insert(t.builder())
Session.currentSession.transactionId = Some(tid.id)
tid.id
}
}
case _ => 0l
}
}
}

Here I implemented the logic to insert the different versions of the
entities in their VersionTable.
This works pretty well, but for insert(e: Iterable[A]):Unit !!

The problem here is that the id of the entities inserted is not
updated, so that the versions always get 0 in their id field !!
Perhaps you know what to do, to get this working?

To use transactions you only need to add a TransactionTable into your
schema:

val transactions = transactionTable[Transaction] (() =>
new Transaction(0l, new Date, "testuser")
)

Transaction only has to be a KeyedEntity[Long] and you can add
whatever other fields you please.
The builder is used to create the new transaction rows......


What do you think?
Am I going in the right direction??

Thanks for your thoughts!

Roll

[Maxime Lévesque]

Here are some thoughts :

1) Could the "transaction table" not be any table ?
  the user would just declare def findTransactionTable = theTableThatRepresentsTransactions
  and return whatever table he decides to be the transaction table, provided that it is a KeyedEntity[Long], (or KeyedEntity[UUID]) ?

2) I like the idea that the transaction table be optional, but could we still generate a unique id
  to correlate rows in the DB ? In databases that have sequences, we could simply use a sequence,
  Of even simpler, the transaction Id could be a UUID (see the new UUID support in master)


for the problem with insert(e: Iterable[A]):Unit, the mechanics of retreiving Ids must get broken somewhere,
It would be easyer if you pushed some code to the repo, I could then debug it.

ML

[rreckel]

1) In fact I wanted to be as generic as possible, but I didn't find a
way to store the callback to build the transaction entities.
That's why I created the TransactionTable class to store it.... I'm
sure there is a better way to do it!
I will try to think of something else to get rid of
TransactionTable ;-)

2) That's a good idea! As I use a transaction table, I simply
defaulted to 0l, but a unique id would be great.
That should be very easy to get running. (BTW is there a possibility
to put the UUID support to versioning branch ?!?)

I did a checkin in idea, and everything should be in the repo.....
But as you said, there seems to be nothing in GitHub ?!?
It's the first time I use GitHub, so perhaps I have to do it via the
terminal ...?

BTW do I need to have a login to do commits ? I never created one, but
the commit seemed to work!

Roll

[Maxime Lévesque]

2) That's a good idea! As I use a transaction table, I simply
defaulted to 0l, but a unique id would be great.
That should be very easy to get running. (BTW is there a possibility
to put the UUID support to versioning branch ?!?)

1) Fork the repo,

2) Merge the master branch in the versioning-and-auditing branch of your repo,
merge from master as often as possible, there will be less conflicts.
 

BTW do I need to have a login to do commits ? I never created one, but
the commit seemed to work!

An authentification is only needed for the push operation (pushing from your local repo to GitHub)

ML

 

[rreckel]

Ouff....
Thanks for your help!!!

So I did a fork under rreckel/Squeryl
I pushed my changes to the versioning-and-auditing branch and merged
it with master......

All this forking and merging starts to give me headaches ;-))

But I think it should be ok now!

So be free to fetch my few changes!

Roll

[rreckel]

I changed the source in Schema:

def transactionTable[A <: KeyedEntity[Long]]: Option[Table[A]] =
None

And it works fine, but in the custom Schema you have to write:

override def transactionTable[A <: KeyedEntity[Long]]:
Option[Table[A]] = Some(transactions.asInstanceOf[Table[A]])

I really don't know why I have to specify the asInstanceOf, but if I
don't the compiler complains that nothing is overridden!!!
Perhaps you know why that is needed, or tell my what I have to do, so
that it is not needed?

Thanks for your help!

Roll

[Maxime Lévesque]

Roll, you can probably do this :

 override def transactionTable[A <: KeyedEntity[Long]]: Option[Table[A]] = Some(transactions : KeyedEntity[Long])

or maybe instead of a def, introduce a method like :

 transactionTableIs(aTable)

to call within the Schema's construction.

def transactionTableIs[A](aTable)(implicit ev: A <:< KeyedEntity[Long])


I suggest we save this detail for last, I think I have cleaner solutions,
the goal is to allow a user to specify a transaction table and to restrict
the type param K in KeyedEntity[K] (i.e. we probably should't allow KeyedEntity[Byte],
....or boolean KeyedEntity[Boolean] !)

So let's use the ugly instanceOf for now, and leave the polishing for later.

ML