Sheesh - Am I still comparing ORM and Stored Procs?

I have worked with both traditional DB/SP setup and ORM solutions a wee bit myself and believe have a thin degree of understanding to make a few comments.
The most common arguments (as claimed by a strong proponent) pro stored procs. are the following

Unlike ORM generated [Ad-hoc] SQL queries, SP represents well-planned, well-tuned, controlled SQL queries that are very adaptable to business change and in contrast generated SQL is very brittle. Tiny changes to the database have severe impact on the application.

Really? Yes Really. So true. But SP is the way to fix that problem? Let’s examine it.

Bank account needs a new field called “National_Unique_ID_Number” to be stored. Ok change the SP and there is no impact on the rest of the application – OOPS that’s not true, Biz. Layer, UI, error handling all calling code, everything needs to be updated. And guess what; because SP is written in a different language in a different IDE there is no “compiler-driven” way to even assess the impact. Bugs proliferate costing $$$. In a ORM backed approach you add a new property and mark it as mandatory, you change method signatures and boom the IDE starts painting your code red. You fix the compilation problems by adjusting all those Biz. Layer, UI, error handling (hopefully driven down thorough the abstraction provided by interfaces) and mostly the pieces of your app will start talking to each other again.
Another example: Field “DOB” that was optional for an account holder is to be made mandatory for new accounts per updates in regulatory requirements. All of the arguments above hold equally true for this case as well.

Security – aka SQL Injection attacks. Stored procedures allow for better data protection by controlling how the data is accessed. By granting a database login EXECUTE permissions on stored procedures you can specify limited actions to the application. Additionally, stored procedures are a counter-measure to dangerous SQL Script injection attacks, a susceptibility that applications using embedded SQL are more vulnerable to.

That view is so orthogonal to a holistic approach to application security in terms of securing methods/functions using declarative security controls with the help of paradigms like ACEGI that arguing against feels like a bottomless task. In the case that one indeed wants to control who-sees-what at the DB layer my view is to use views. Pun not intended. 

Self-wound-inflicting-serious-security-pundits (which most DBAs are by the way) have also heard of another technique called “role based security”. Such roles are defined at the DB layer. You have 2 roles: the average user, and the administrator user which configures the application. Define 2 roles in the DB, place the users in the right role, define the rights per role on the tables and views and off you go: fine grained security which works, without a single stored procedure in sight. A new user is added? You just add it to a user role and it has the rights it should have.

Performance – The holy grail of application programming: Stored procedures are pre-compiled and therefore can bypass the cost of creating an execution plan whereas Ad-Hoc SQL statements created and issued by ORMs have to go through this cost every time. SPs can take advantage of DB layer code dealing with looping through and filtering data much better than data-access-layers sitting far out in the application server can. SPs can batch common work [select-update-select-insert-update] together smartly and give benefits in terms of reducing transport layer loopbacks in the order of thousands of cycle times.

No – Not all of the above arguments are not false. Yep at least one of them is true. Let’s examine.

Pre-compiled SP – Truth or Myth? SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans. The database engine uses an efficient algorithm for comparing new Transact-SQL statements with the Transact-SQL statements of existing execution plans. If the database engine determines that a new Transact-SQL statement matches the Transact-SQL statement of an existing execution plan, it reuses the plan. This reduces the relative performance benefit of precompiling stored procedures by extending execution plan reuse to all SQL statements. – I did not make that up

Caching of execution plan happens for any query; not just the ones fired from SPs. It’s a democracy out there.

Loops over resultsets vis-à-vis SP (loops in DB). SP are sure to win? Not so fast. SPs do their looping through data sets in T-SQL. That requires a cursor. A cursor always creates a temp table in tempdb, which can hurt if tempdb is full and has to be re-allocated. In a large application, looping a lot will reach that condition sooner or later. Also T-SQL is a set-based language (not procedural) and that means it does have an advantage over procedural languages (multiple but separate SQL statements feeding the result of one into another) when it works on very large sets of data. That said, if you have looped a lot over resultsets means only one of the following two things; Either you did not understand what set-based means or whatever you are doing can’t be done in a set-based batch of statements. Need I mention that all matured ORMs let you write lambda based batch statements specifically designed to do this and reduce or eliminate looping through resultsets. For the second case of things that just cannot be done in a set-based batch, SPs may have a real advantage as choosing another procedural language may not be a realistic option within the scope of a project.

Now some performance detail – Let’s say we have an application with 100 tables with an average to 10 fields in each of them. This requires 100 ‘update’ SPs. These 100 SPs will have 10 fields to update on an average (because you cannot have optional update statements in SPs). One cannot create SPs for all combination of fields either; that would require 100^10 SPs and lead to madness. You can have optional parameters in the SP itself but that’s a performance killer anyway. SP open up a whole new problem front in terms of maintenance. Because they become a sort of API set which can’t be extended. They violate the single most important design talisman - “open for extension, closed for modification”. Every time a business need changes the SP cannot be modified; a new one is to be written. This is not a minor problem specially when the system is large and has been maintained for years. Microsoft also believes in this and advises this.

Optimistic Locking - Play! Way

I would not go into the details of describing what locking or optimistic locking is. There is a great article on wikipedia for reference. I would stop at saying that here I am trying to describe my method of implementing the same in the context of a database within the boundaries of Play! Framework.

A common strategy to build optimistic locking is to add a version field to the model and update the version field on every update. In fact it is so common that JPA has a special annotation for it.

Here is the code for my model class followed by explanation.

@Entity
@Table(name = "REF_BRANCH")
public class Branch extends Model{
  @Version
  @CheckWith(value=OptimisticLockingCheck.class, message="optimisticLocking.modelHasChanged")
  public int version;
  ...
}

@Version is the JPA annotation that will add an auto-incremented column named version to my table (REF_BRANCH) in this case. As a result when I create a new Branch object and persist it, the version field will take a value of 0. Every subsequent update to the above created object will automatically change the value of the version field to 1, 2, 3 ... and so on. The version field above is also annotated with the @CheckWith annotation. More on that in a minute.

I decided to design my application in such a way that failure to acquire a (pseudo) lock before record is updated will be treated as a validation failure. In Play! Framework, custom validation is done by extending the class play.data.validation.Check. Thus I also mark my version field in the model class with the @CheckWith annotation.

public class OptimisticLockingCheck extends Check {
  public boolean isSatisfied(Object model, Object inputVersionObject) {
    try {
      Model modelObject = (Model) model;
      modelObject.refresh();
      Field versionField = modelObject.getClass().getField("version");
      int dbVersion = versionField.getInt(model);
      int inputVersion = ((Integer)inputVersionObject).intValue();
      return dbVersion == inputVersion;
    } catch (Exception e) {
      return false;
    }
  }
}

@CheckWith(value=OptimisticLockingCheck.class, message="some.message") ensures that the above class is used to validate the model instance. A quick reference to the controller's update method will be helpful.

public static void update(@Valid Branch entity) {
  if (validation.hasErrors()) {
    flash.error(Messages.get("scaffold.validation"));
    entity.refresh();
    render("@edit", entity);
  }               
  entity.auditLogs.add(AuditHelper.onUpdate(Security.connected()));
  entity = entity.merge();
  entity.save();
  flash.success(Messages.get("scaffold.updated", "Branch"));
  index();
}

Here update(@Valid Branch entity) ensures that all validations on the Branch object fires before the update method is executed.
One has to ensure that the version field is included in the edit form albeit hidden.

Whack a bit of EXT JS

Sometimes just using an Ext JS class inside of a simple javascript is all that is required as opposed to formally extending one. In this case I want to wrap a Ext.data.store object inside a simple javascript object so I can configure it to perform all my CRUD operations.

The object will be created using a simple method intuitively named init() which will be invoked like this...

dataStore = MyStoreObject.JsonStore.init({

enterpriseId: 'acompany', // send some parameters (config options in javascript speak through the init method

screenId: 'somescreenid', // send some more config options

userId: 'arindam', // and still some more ...

id: 'someId',

root: 'root_of_json',

fields: ['field1', 'field2', 'field3'],

autoLoad: true

});

Now the object itself

MyStoreObject.JsonStore = {

init: function(config){ // all those config opts are contained in config

// constants

... some constants go here for next processing

// url pattern

... some calculations based on the constants and config (see config.enterpriseId)

var urlPattern = P18N_CONTROLLER_NAME + PATH_SEPARATOR + config.enterpriseId 

+ PATH_SEPARATOR + config.screenId + PATH_SEPARATOR + config.userId;

// setup proxy

... some more value setting etc...

var proxy = new Ext.data.HttpProxy({

api: {

read    : urlPattern + PATH_SEPARATOR + READ_FUNCTION,

create  : urlPattern + PATH_SEPARATOR + CREATE_FUNCTION,

update  : urlPattern + PATH_SEPARATOR + UPDATE_FUNCTION,

destroy : urlPattern + PATH_SEPARATOR + DELETE_FUNCTION

}

});

// setup reader

... and some more 

var reader = new Ext.data.JsonReader({

root: config.root,

fields: config.fields

});

// setup writer

... still more 

var writer = new Ext.data.JsonWriter({

encode: false

});

// setup the store

... now we create the Ext object store

var store = new Ext.data.Store({

id: 'MyStoreObject.InternalStore_' + new Date().getTime(),

restful: true,

proxy: proxy,

reader: reader,

writer: writer,

autoLoad: config.autoLoad ? config.autoLoad : false

});

... now we insert a new attribute [viz. store] into our current object (this) and assign the Ext's store to it

this.store = store;

... and return the new object. this way the callers of the init method actually get a fully configured Ext.data.Store object which they are free to use in whatever way they see fit

return store;

}

}

Lens Comparision

Never thought that I would be writing a lens comparison article ever. But this one had to be written. Since my lenses and the D300 body were stolen on the way back from Benares I had been thinking a lot about getting a lens. I seriously considered a Tokina 17-50mm f/2.8, my old (and now stolen) trusty 18-105mm Nikon and the 16-85mm much praised Nikon. The long-zoom section bothered me (and still does) as there is almost nothing without a constructional flaw except probably 70-300 Nikon but that be saved for another day.
After a lot of flip-flopping I decided to buy the 16-85mm Nikon. Now I still had two lenses from before, the 18-55mm Nikon and the 50mm f/1.8 fixed again Nikon. Immediately I wanted to make a comparison between the three lenses. I just don't want to make any comments. The comparison is right here for you to see.

18-55mm f/8 1/10s

16-85mm f/8 1/10s

50mm f/8 1/10s

And all above were shot in exact same light within minutes of each other. Camera mounted on tripod. The zooms were set to 50mm manually. All were stopped down enough to achive optimum or near optimum performance. The crop was taken slightly off center. The image on the left is obviously the weakest. But the one on the right (50mm prime) is clearly far less flattering than the center image. 16-85mm f/3.5-5.6 AF-S ED shines through the 2 ED and 1 Nano coats on the glass.

Concurrency for Persistent Storage

I have been thinking about concurrent updates to relational databases and a strategy to deal with the problem. Of course it is not the hottest thing on earth but until I figured out what to do - I just did not know the solution. I will have this documented here in hope of referring to in the future. If this also helps anyone else; I will be glad.
A few words about concurrency itself in the beginning would put the following discussion in the right perspective. Any object that is visible to multiple threads is subject to be read or written by multiple threads. The problem of concurrency arises from the scenario described above. A particular thread may 'think' that it has updated the value of a String variable from "a" to "b" but in reality it may have only updated the value from "c" to "b". Consider the events below.


1. Thread 1 reads the value of a String variable name to be "Joe".
2. Thread 2 also reads the value of a String variable name to be "Joe".
3. Thread 1 & 2 decide to update the value to "Jack" and "Jill" respectively.
4. Thread 2 gets his chance and changes name to "Jill".
5. Thread 1 (having no knowledge of Thread 2's existence changes name to "Jack".


In my real life applications I would like Thread 1 to be informed at step 5 something like this "sorry mate that name has changed. please go and fetch the new value and then come and update it". This problem begins to fade slightly if the threads are required to acquire the intrinsic lock on the name object described above. Note: if we just make the threads acquire the lock, the problem does not go away completely. In order to fix the problem one must acquire the lock, read the current value, ensure that is the same as before acquiring the lock, update the value to the new desired one and release the lock. In code that looks like so.


public void setName(String oldName, String newName){
    synchronized(this){
        String currentName = getName(); // Database equivalent of "Select... for Update"
        if(currentName.equals(oldName)){ // verify name has not been changed since our last read
                name = newName; // only now we are ready to make an exclusive update
        } else{
                throw NameAlreadyChangedException();
        }
    }
}


Ok problem solved. But wait, that name field is stored in a persistent storage and locking a Java object does not necessarily mean that no one else can update that row in the store. That's why most good databases let the user lock a record using "select for update" and it is generally referred as the 'last select'. But that works for some (may be most if not all) relational databases. But I am not ready to make any assumption about the type of store (relational or otherwise). I am looking for a strategy; not a specific solution.


In my test environment I am using Spring, Spring's JpaTemplate and MySQL. And I particularly like the approach of optimistic locking using version attributes. So here goes my Entity class.

@Entity
public class Person {
    @Version
    @Column(name = "VERSION", 
     nullable = false
    )
  private long version;
  @Column(name = "NAME")
  private String name;
  public long getVersion() {
return version;
  }
  private void setVersion(long version) {
     this.version = version;
  }

  public String getName() {

return name;

  }

  public void setName(String name) {

     this.name = name;

  }

}


So JPA provides this @Version annotation and the value of the field is automatically managed (i.e. auto-incremented on every update). Very nice. So my original if(currentName.equals(oldName)) translates to something like if(pojo.getVersion() == persistedPojo.getVersion())

And I can finish off my Repository (aka DAO) code in style like so.

@Repository

public class SupplierRepository extends JPATemplate // not exactly so in real code

...

// pojo.setName() has been called elsewhere

public Person update(Person pojo) {

synchronized (pojo) {

  Supplier persistedPojo = super.find(Supplier.class, pojo.getId());

  if(pojo.getVersion() == persistedPojo.getVersion()) {

    return super.update(pojo);

  }

  else {

    throw new OptimisticLockingFailureException();

  }

 }

}

Beginning with the commented line I can let hundreds of threads call the setName(...) method totally 'un-thread-safely'. Only one is going to be successful in committing the data to the persistence layer anyway. The intrinsic lock acquiring line synchronized(pojo) ensures none but one thread executes the remaining lines. Then the findbyId reads the current row (hence the latest version number) from the store. On verification (if condition) the update happens. The point I want to make here is multiple threads can still get inside the synchronized block (after the first one to get access relinquishes) but they will all fail the version test hence will face the OptimisticLockingFailureException which is an unchecked exception defined by my application.

Validation in Grails

I had a long time inertia against writing exception handling and displaying user friendly message in the UI. And it appears I am not alone! But is time to shake it off. Welcome to Validation and Error messaging - Grails Style!
Before I get started (and promise I will be done before I notice) some facts.

1. You define constraints in your Domain class. For example..
package foo.bar;
class Item {
String itemName
..
static constraints = {
itemName(blank:false,unique:true, nullable:false)
..
}

2. Now if you say..
def someCoreItemInstance = new Item()
Domain classes have implicit object "error" in them. In fact the above line of code will already populate the error object since someCoreItemInstance violates the constraint (nullable:false). However in an explicit call someCoreItemInstance.validate() will create "error" object and put it in the someCoreItemInstance object; but

3. someCoreItemInstance.save() will automatically call someCoreItemInstance.validate()


In the controller even if you do not do anything extra (there are rare exceptions like while doing a transaction) you must be calling the save method. That automatically puts the error object inside someCoreItemInstance object; but "how do I create nice user friendly error message"?

This is where convention over configuration (over coding!) comes into play. Your error object by default looks for a message in /i18/messages.properties file in the following format.
[Class class] [Attribute attribute] [Constraint constraint]

So in the above example all you need to create nice error message is a message like this...
foo.bar.Item.itemName.nullable = Hey please enter item name

All that remains is to display the message in the gsp page; for that...
<g:hasErrors bean="${someCoreItemInstance}">
<div class="errors">
<g:renderErrors bean="${someCoreItemInstance}" as="list" />
</div>
</g:hasErrors>
...

And thats it!
So all you really need is the message in the correct format and the gsp code to display.

But I want to validate non-domain (where domain class is a class representing a persisted object, think hibernate) classes too. How do I inject the 'error' object into them? Well there seems to be a bit of a confusion but here is what always works.

Lets say you have non-domain class ItemBean and you want to be able to call validate() and want the rest of the magic to happen. So you need...
package foo.bar;
@Validatable
class ItemBean {
...
}

and also enter the following line in Config.groovy
grails.validateable.classes = [foo.bar.ItemBean] // more comma separated allowed


Happy Validating!

DBDesigner - Don't Use It

It took me significant googling to finally install DBDesigner. For friends I will document the steps here.
1. Download DBDesigner (rpm version) here
2. After downloading this file you will need alien to convert it to .deb, if you don't have it installed, just type sudo apt-get install alien

3. cd to where you downloaded the rpm package and install it using sudo alien -i DBDesigner4-0.5.4-0.i586.rpm
4. At this point DBDesigner is installed in /opt/DBDesigner4 but if you try to run it (./DBDesigner) it says libborqt-6.9-qt2.3.so: cannot open shared object file: No such file or directory
5. You need the library package. Download it here
6. Convert the package to .deb sudo alien kylixlibs3-borqt-3.0-rh.1.i386.rpm
7. Verify you have the file /usr/lib/libborqt-6.9.0-qt2.3.so
8. Copy and rename the file sudo cp libborqt-6.9.0-qt2.3.so /lib/libborqt-6.9-qt2.3.so
9. Go to /opt/DBDesigner4 and run ./DBDesigner
10. Please change the font ;)


Edit: I do not use DB Designer anymore since the last... hmm 3 years ;) My SQL Workbench is so cool 

Obituary: OpenSolaris

The corporation killed the community spirit again. James Gosling has compared Larry Ellison to Chengiz Khan ("we must not only ensure that we win but also everyone else loose"). They killed OpenSolaris and will only release a Developer MTU. Erstwhile Sun built, contributed and nourished a community that thrives and continues to grow on the principles of open source. The world (including Oracle) has savored the fruits of sun's philosophy and today they are taking away what belongs really to the solaris developers (both inside and outside of sun). Based on patents files by Gosling and his colleagues they have already sued Google for alleged violating of rights (re Android and Dalvik vm). These attacks go beyond the issues between corporations. The open source java community and everything else that was born of that works everyday to make life a better experience. Oracle cannot just take this away. I don't care if Google looses a lawsuit or two (they did try to screw up Java by building this Dalvik anyway) but if Oracle wins and continues to do what it has started, many years of community work done by smart people will just be wasted (I fear). At the moment I am drawing hope from the fact that java is so deep seated in almost all of oracle products that they will not dare to screw with java.

man ubuntu

Everything i wanted at fingertips