Core Data or Sqlite - iPhone Local Persistence

This post is after a while. So, what have I been up to. Nothing new in Java world, but with my third iPhone / iPad application underway, I think I have made some serious progress in iPhone development.

So, what is today's topic of discussion? Recently I was faced with an option to choose between SQLite or Core Data for data storage on the device. I was all for SQLite until I found out that all the examples provided by Apple using SQLite were gone.. Not really gone, but switched to Core Data. So, that's how quest to choose began.

So, what do you look into making choices. My first reaction was to see what people out there are doing. What are they using and what choices are they making. Of course, not to follow them, but just to gain insight into their thoughts.

And I found arguments going both ways. Instead of pointing them out, as a quick google search will lead you there, I want to mention my decision. Am I glad I went with Core Data. Let me explain.

Before I made the call to use Core Data I decided that I needed to clearly identify my 'need' and find a solution for it, and which-ever solution fit, I'd go for it. I could quantify my 'need' in the following:

1. invoke a web service and get data and store it on device.
2. query device for listing of saved data with basic querying.
3. update of individual records of saved data.
4. access related entities when reading one entity.

Could I do all of above with SQLite? Yes. What about Core Data? Yes. So, still not clear which one to choose. Next I though of what other 'needs' may arise?

1. update multiple records based on criteria
2. generate detailed, grouped reports

These are possible 'needs' an application may have, but did I foresee this in my app? No. Would SQLite be suitable? Yes. What about Core Data? Its do-able, but not elegant. (such as, looping and updating each record instead of issuing 'update xyz where abc')

This settled one question. If I had these needs, then I would have used SQLite, for performance, ease of use. But, since I do not have this need, which one should I go with? That question still was not answered. Next thought: What does Core Data give me that SQLite does not?

1. ORM 'style' working. (Note: Core Data is not ORM. It does not map a Database Schema, actually it is DB agnostic in a way.. But it works just like ORM models, such as Hibernate models, in many respects).
2. Lazy loading of related entities, reverse relations
3. API for querying, ordering & filtering
4. Session based updates.

Basically, all the goodies that I know and love about Hibernate. (not really all, such as, I cannot bypass the Core Data request and query objects by SQL as in Hibernate). What else would Core Data provide?

1. A new skill.
2. A better understanding of Core Data, so that if and when I have to decide again, I would make a more informed decision.

So, you can see.. I am leaning towards Core Data. So, what is there to convince me? Apple. There is a reason (with Apple knows better) why they switched their examples from SQLite to Core Data. Though it is unlikely that they will drop support for SQLite (Core data itself uses SQLite as one of data store options), but there is going to be lesser and lesser support in Apple documentation for SQLite.

So, there you have it. My reasoning to switch to Core Data. Having said that, Am I glad that I chose it. (Did I already say that?). Accessing and writing data is a breeze. In summary, I would say that if you do not have a 'need' that overwhelmingly 'requires' a SQLite solution, go for Core Data.

ORM and searching / filtering

OK, so you have decided to use an ORM. And there is a need to get all users or a user from database.

This is not a discussion about how to implement the DAO method. Is it by a query, calling a stored procedure or using the ORM's object based querying.

Having said that, to get users from database, you go ahead and write your DAO class with methods: getAll() and get(id)

How about, get all users who belong to a specific company. Easy. You go and write :getUsersByCompany(Company company).

So far so good. There is a new use case that requires all users who belong to a specific company and are employed full time and were hired in last 4 months and speak English.

Is the method: getUsersByComapnyAndByHireTypeAndByDateOfHireAndBy.... in works for your DAO class?

Or you just use the DAO method : getUsersByCompany(..)and then further filter in a service method by parsing and inspecting user attributes and excluding the ones that do not match the criteria?

This makes me think, what is a criteria for a method to qualify for a home in a DAO class? get(), getAll(), delete(id), update(object), getByThis(..), getByThisAndThat(..)These methods seem logical and most of them are necessary. But why would the above mentioned method not qualify? (or should it?)

There exists a solution in a more dynamic language based framework, like Grails based on Groovy, which kind of does away with the DAO objects and the domain class itself is responsible for its data access and storage. An example is : customer = Customer.findByName("... This is achieved by dynamic methods. But we're talking about java, which is not dynamic.

Having raised the question,I have couple of methods that I follow, but I'm not sure if they are most optimal.

I use a mix of DAO methods and additional service methods to apply further filtering. Lets take the example above: find users "who belong to a specific company and are employed full time and were hired in last 4 months and speak English".

Based on the overall scope of the project, I would decide to what detail I should create a DAO method. Accordingly I'll create: 
getUsersByCompanyAndEmploymentType(company, employmentType) Among the criteria to create this method, is, Is the resultant list of users manageable for any further processing without putting undue stress on the server? Is this method useful to fulfill some other use cases?

This gets the users for the specific company and who work full time, then I pass this to a service method that parses the users and filters users who were hired within last 4 months and who speak English.

Another alternative for such cases could be a single catch-all method called: searchUsers(criteria)where criteria could be sent either as a Hashmap, or a class with specific fields that the users can be search by. The service method fills in the criteria based on a use case and pass it on to DAO.

These may not be the most optimal solutions, but I would like to hear of alternatives.

On a side note, there exists a mini-framework that helps with Hibernate and this 'search' issue, which formalizes the Search method. Its called 'hibernate-generic-dao'. I have not used it, but concept looks nice, but again, its tied to Hibernate only.

stuff change on you...

I am sure at some point in time we all make a judgment call to go with a piece of software based on its merits. The criteria for what constitutes a merit differs from one individual to another. We come up with a set of criteria and a set of softwares to choose from. We subject the software to the criteria and make a call. All well and good.

If for some reason the software we choose is the choice of many others in our 'sphere of interest' we have a hit. And if we are an outlier, we end up in one of two scenarios.

1. Either we really have made a good choice and the market around has to 'see' the light and catch on, or
2. We really missed the mark and we have to re-analyze our choice and 'catch-up' with the rest.

Of course, the realization of 'hitting the mark' itself is relative. I'm sure there are some die hard fans of EJB2 who will still say that Hibernate and other ORM's have not got it.

So, what would you say, a person trying to make a call between EJB2 and Hibernate?

One can't say for sure. Hear this argument.

Joe: It does not matter what features EJB2 and Hibernate have, our company policy is not to use open source products. All products have to comply with industry standards.

Mark: But what is 'industry standard'. Isn't it what the 'industry' accepts as a standard.. meaning us, developers/architects. If we say Hibernate is a better choice, so it is a standard.

Joe: Possibly, but our policy writers do not see it like that. If it does not come from the Suns, IBMs, Xs or Ys, its not a standard.

Mark: but..

So, you see, Joe and Mark agree. But still have to disagree due to the criteria used for making a choice.

Then there are re-incarnations.

Remember when you looked at JSF when the world was going with Struts and its sisters. Its too dotnet. Its too component oriented. Its not MVC enough. Its this and its not that. The 'sphere of interest' goes with Struts, Webworks, SpringMVC etc. etc. And then JSF comes with a re-incarnation. It has the blessing of the big names Joe was looking for. It has the blessing of 'us, developers/architects' in open source implementations of JSF that Mark was looking for.

So, what would you say, a person who made the choice of JSF at a time when it did not make the cut.. and stuck with it. Is it because that person 'saw' the promise and potential and had the foresight of its success? Or is it just that the person gambled with it and it paid out?

Its the similar story with EJB in its new EJB3 re-incarnation.

Then there is stuff that changes on you.

How often do you make a choice of software with due diligence. Even the 'sphere of interest' is with you. Things are going fine.. You bet your projects and your raise on it. And then it hits you. The people responsible for your choice software bail out on you. The product is no longer supported.

Recently I was looking for a tag library to use jfreecharts.. Cewolf was the number one choice. Even documented and recommended by jfreechart folks.. But its not supported anymore.

Would you use it in your project? (on a side note, I'm writing my own now.)

Another case in point is Appfuse. I have used it multiple times.. with JSF and SpringMVC. Every time I have to use Appfuse, I've to make a call, which MVC framework to use. And to be frank, I had gotten to like SpringMVC. But it seems appfuse may drop support for springMVC. (http://raibledesigns.com/rd/entry/appfuse_light_converted_to_maven). I hope I read it wrong.

So, what would you say, a person who made a choice of SpringMVC with Appfuse, and then the folks at Appfuse possibly dropping SpringMVC support?

One could say, 'Its a price for getting stuff free.'.
Or one could say, 'Get a life.. what else do you expect. It is the hazard of the landscape.'
Or as Arnold would say, 'Stop whining.'

I say, Is it even something one should feel averse to? Is it something that is not good for you? Isn't change supposed to be good?

Yes, sometimes you change your choice, sometimes the 'sphere of interest' changes its choices and sometimes someone else changes your choice. Its an opportunity.

Its an opportunity to learn something new. Its an opportunity to make yourself adapt.. and be agile.

Yes, a root canal at a dentist's office hurts.. but its good for you.

Hibernate and connection pools

I recently spent some time researching and fixing a bug in one of my applications. This is just a summary of its solution, for all those it may help.

Problem:
Mysql has a default time-out on idle connections. The default is 8 hours. If an application uses a connection that has been sitting idle in a connection pool beyond the database time-out period, the application throws an exception.

Solution:
Well, firstly, I thank all those who wrote on blogs/mailing-lists that I had to read to come to the solution, some of which I will quote from here.

To reporduce the problem, I first had to re-create it for testing. I updated mysql's my.ini (windows file, use the corresponding file in Unix/Linux environments) file. I added a wait_timeout value of 120 seconds.

[mysqld]
wait_timeout=120

This immediately produced the error after 2 minutes of reusing an idle connection.

org.hibernate.exception.GenericJDBCException: Cannot release connection
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
    ..
    ..
Caused by: java.sql.SQLException: Already closed.
    at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:84)
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:181)
    at org.hibernate.connection.DatasourceConnectionProvider.closeConnection(DatasourceConnectionProvider.java:74)
    at org.hibernate.jdbc.ConnectionManager.closeConnection(ConnectionManager.java:451)
    ... 44 more

I was using commons-dbcp connection pool as a spring managed bean. Since I used appfuse's spring mvc archtype as a quickstart, it came configured with the commons-dbcp. (http://appfuse.org/display/APF/AppFuse+QuickStart).

Gavin King from Hibernate suggests not to use the commons-dbcp as it is faulty. (http://opensource.atlassian.com/projects/hibernate/browse/HB-959) So, taking his advice, my first take was to change the datasource to use a different one.

So, the next question was, if not dbcp, then which one? After a while I stumbled across this connection pool that hibernate supports. I must say, I had not heard of it before, and with an acronym (c3p0) it is not forgetteable. (http://www.hibernate.org/214.html)

Accordingly, I first changed the bean definition from

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <property name="maxActive" value="100"/>
        <property name="maxWait" value="1000"/>
        <property name="poolPreparedStatements" value="true"/>
        <property name="defaultAutoCommit" value="true"/>
</bean>

to the bean definition that uses c3p0:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
        <property name="driverClass" value="${jdbc.driverClassName}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="user" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
</bean>

Note the difference in some of the property names between them. driverClassName/driverClass, url/jdbcUrl, username/user etc. Since these are bean properties, and not resource definition (such as for a web container), the names can be whatever the bean-writers choose them to be.

Refer to this link for more discussion about setting up c3p0 as a bean.
http://forum.springframework.org/showthread.php?t=16309

As you can see, I did not set any other property in the bean definition. The reason requires a little bit of explanation.
In another link (http://forum.springframework.org/showthread.php?t=13078), you can see how the properties can be set.

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
        <property name="driverClass" value="${jdbc.driverClassName}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <!--<property name="user" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>-->
    <property name="properties">
            <props>
                <prop key="c3p0.acquire_increment">5</prop>
                <prop key="c3p0.idle_test_period">100</prop>
                <prop key="c3p0.max_size">100</prop>
                <prop key="c3p0.max_statements">0</prop>
                <prop key="c3p0.min_size">10</prop>
                <prop key="user">${db.user}</prop>
                <prop key="password">${db.pass}</prop>
            </props>
        </property>
    </bean>

But, if you set the properties in this manner, c3p0 does not pickup the user and password as a regular 'property', you have to specify them as prop's in the properties 'property'. So, I yanked all from here, but kept the user and password. I moved all other properties to a c3p0.properties file.

c3p0.acquireIncrement=1
c3p0.idleConnectionTestPeriod=100
c3p0.initialPoolSize=5
c3p0.maxIdleTime=80
c3p0.maxPoolSize=10
c3p0.maxStatements=0
c3p0.minPoolSize=5

One thing that is easily 'missable' is the names of properties. They are named differently in hibernate for the corresponding properties in c3p0. For example, the property of most interest c3p0.idleConnectionTestPeriod is named in hibernate as c3p0.idle_test_period. That makes me wonder, if the property was correctly set in the second springforum link I quoted. (http://forum.springframework.org/showthread.php?t=13078)

So, if you set your c3p0 properties in a c3p0.properties files, you should use c3p0 property names, and in hibernate config files, you should use hibernate co-equivalents.

Another note of caution, even though it is mentioned in passing in the hibernate document (http://www.hibernate.org/214.html), is when you set any of the hibernate cp30 properties, there are 7 properties that hibernate overrides. So you should set all those properties in hibernate, if you do not want hibernate defaults to override cp30 settings or defaults. You will find a reminder in cp30 documentation as well. (http://www.mchange.com/projects/c3p0/index.html#hibernate-specific)

Here is my hibernate.cfg.xml snippet.

<session-factory>
        <property name="connection.pool_size">10</property>
        
        <property name="c3p0.acquire_increment">1</property> 
    <property name="c3p0.idle_test_period">100</property> <!-- seconds --> 
    <property name="c3p0.max_size">10</property> 
    <property name="c3p0.max_statements">0</property> 
    <property name="c3p0.min_size">5</property> 
    <property name="c3p0.timeout">80</property> <!-- seconds --> 
    ..
    ..

As you can see they are same as that in c3p0.properties file. In any case it does not matter, hibernate values will supercede any corresponding value set in c3p0.properties file.

So, what do we have here. We have idle_test_period as 100 seconds, and we had kept mySql's wait-timeout as 120 seconds. This means that the connection pool will discard any connection that has been idle for 100 seconds, and thus will not get used.

The logs show evidence.

DEBUG [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0] C3P0PooledConnectionPool.finerLoggingTestPooledConnection(315) | Testing PooledConnection [com.mchange.v2.c3p0.impl.NewPooledConnection@17fd168] on IDLE CHECK.
DEBUG [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2] C3P0PooledConnectionPool.finerLoggingTestPooledConnection(319) | Test of PooledConnection [com.mchange.v2.c3p0.impl.NewPooledConnection@f5b2da] on IDLE CHECK has
SUCCEEDED.
DEBUG [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1] C3P0PooledConnectionPool.destroyResource(468) | Preparing to destroy PooledConnection: com.mchange.v2.c3p0.impl.NewPooledConnection@70ccb
DEBUG [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1] NewPooledConnection.close(566) | com.mchange.v2.c3p0.impl.NewPooledConnection@70ccb closed by a client.
java.lang.Exception: DEBUG -- CLOSE BY CLIENT STACK TRACE
        at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:566)
        at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:234)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.destroyResource(C3P0PooledConnectionPool.java:470)
        at com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask.run(BasicResourcePool.java:964)
        at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

So, a long story in short.

Use c3p0 as your connection pool if you are using hibernate. c3p0 can be configured as a resource bean in Spring. Set hibernate properties for c3p0 to override the c3p0 defaults and hibernate's own c3p0 defaults.

Happy programming.

By the way, I like this convention over configuration thing, but care is needed. I may write about it in another post.

ORM Models : Hibernate with Annotations and Cayenne

Before I used Hibernate (*1), I played around with Cayenne (*2). I liked its Visual Modeler. It allows you to create models, and map them to database fields. It also generates Java Code and sql scripts. All packaged up. I liked it. Untill I worked with Hibernate.

I must admit, I tend to not like things that get too much hype, and sometimes the hype is much deserved. So, in such cases I kind of 'miss the boat'. Hibernate falls in that category.. But, in the end, having a better grasp at competing technologies never hurts.. Let me explain, and I will limit the discussion to few items, which I feel are a 'make or break', at least for me, and please take it for what its worth.

Lets take Cayenne first. Lets take a look at its generated model. Or should I say 'models', as it creates two files.

public class Blog extends _Blog {
...
}

pretty small.. Wait till you see the _Blog

public class _Blog extends org.apache.cayenne.CayenneDataObject {

public static final String BLOG_DESCRIPTION_PROPERTY = "blogDescription";
public static final String BLOG_TITLE_PROPERTY = "blogTitle";
public static final String DATE_CREATED_PROPERTY = "dateCreated";
public static final String DATE_MODIFIED_PROPERTY = "dateModified";

public static final String BLOG_ID_PK_COLUMN = "BLOG_ID";

public void setBlogDescription(String blogDescription) {
writeProperty("blogDescription", blogDescription);
}

public String getBlogDescription() {
return (String)readProperty("blogDescription");
}

public void setBlogTitle(String blogTitle) {
writeProperty("blogTitle", blogTitle);
}

public String getBlogTitle() {
return (String)readProperty("blogTitle");
}

public void setDateCreated(java.util.Date dateCreated) {
writeProperty("dateCreated", dateCreated);
}

public java.util.Date getDateCreated() {
return (java.util.Date)readProperty("dateCreated");
}

public void setDateModified(java.util.Date dateModified) {
writeProperty("dateModified", dateModified);
}

public java.util.Date getDateModified() {
return (java.util.Date)readProperty("dateModified");
}

}

So, what is the issue..

Firstly, Cayenne creates a model, which is for you to extend and customize, which extends another class (_Blog.java).

The _ in _Blog.java means, 'do not touch'. Cayenne reserves the full right to over-write it. This is a problem, in at least one of these ways:

1. If I have to modify the model, I have to use the 'Modeler' and then 're-generate' the code.
2. I am tied to Cayenne for the model class, and the proof? _Blog.java itself extends org.apache.cayenne.CayenneDataObject

Secondly, Blog.java is not a POJO.. It extends _Blog.java and that itself extends CayenneDataObject (Does it have to be a POJO? No, but Does it help if it is a POJO? Of Course..)

Thirdly, Can you find getBlogId() or setBlogId(...) in the _Blog.java ? Neither do I. Cayenne documentation says, "Normally it is not advisable to map primary and foreign key columns (PK and FK) as Java class properties (ObjAttributes)." (*3) But they do not explain the wisdom in it. So, you have to write an elaborate custom method to get the id from the model itself (see the referenced link). But to set an id, that is still a no-no. Of course, you may agree with the methodology of not allowing access to Id from model, but I tend to like ot have it.

So, to keep it short, I'll just limit my discussion to these three points. And no, I'm not skipping Hibernate. Here I'll just relate Hibernate with these three points.

Lets see a Hibernate Model class.

@Entity
@Table(name="CLIENT")
public class Client implements Serializable {

@Id
@Column(name="id", nullable=false)
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;

@Column(name="name", nullable=false, length=255, unique=true)
private String name;

@Column(name="account_manager",nullable=true, length=255)
private String accountManager;

@Column(name="account_owner",nullable=true, length=255)
private String accountOwner;

public Client() {

}

public Long getId() {
return id;
}

public void setId(Long id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public String getAccountManager() {
return accountManager;
}

public void setAccountManager(String accountManager) {
this.accountManager = accountManager;
}

public String getAccountOwner() {
return accountOwner;
}

public void setAccountOwner(String accountOwner) {
this.accountOwner = accountOwner;
}

}

This example of the Model class uses Annotations, though all of this can be accomplished using an XML file. But I prefer Annotations, and using Annotations has its benefits, which I won't mention here, as I'm discussing Model classes.

So, First issue : I generate the Class. Even if there exists tools that generate the Model classes, there is nothing stopping me from writing or modifying it.

Secondly, the Client.java extends Serializable, which qualifies it for a POJO. I know, I know, this class has javax.persistence.Entity, javax.persistence.Id and similar annotations, but, there is nothing that prevents me from instantiating a Client Class, and setting all the properties and have it then persist..

Thirdly, getId() and setId(...). Ahh. Dosen't that make you breathe easier.

In any case, I would use Hibernate just for these reasons, even though there are other equally compelling reasons to use it. Like, javax.persistence/EJB3 supported annnotations, the Hibernate model above could be used with JPA without changing a single line of code. (Of course, a Hibernate DAO will differ from JPA DAO), extensive Maven support for Hibernate and other 'nice to haves'.

(*1) http://www.hibernate.org/
(*2) http://cayenne.apache.org/
(*3) http://cayenne.apache.org/doc20/accessing-pk-and-fk-values.html