Custom data persister, JodaTime and sqlite : "Generated-id field 'id' can't be type LONG"

[Carl]

Hello guys,

I am trying to persist a joda DateTime using ormlite 4.31 for jdbc with sqlite (driver sqlite-jdbc-3.7.2).

So I wrote a custom persister to store my DateTime as a unix epoch LONG.

I hacked my persister from the code of DateLongType, do adaptation and register it.

But being the cautious guy I am, I try to relaunch my project without even using the 

persister on my POJO.

Just to see if everything is fine.

The issue is that I am getting a weird error when I try to create the first dao.

Exception in thread "main" java.lang.IllegalArgumentException: Generated-id field 'id' in Place can't be type LONG.  Must be one of: INTEGER INTEGER_OBJ LONG LONG_OBJ UUID 

at com.j256.ormlite.field.FieldType.assignDataType(FieldType.java:924)

at com.j256.ormlite.field.FieldType.<init>(FieldType.java:244)

at com.j256.ormlite.field.FieldType.createFieldType(FieldType.java:878)

at com.j256.ormlite.table.DatabaseTableConfig.extractFieldTypes(DatabaseTableConfig.java:225)

at com.j256.ormlite.table.DatabaseTableConfig.fromClass(DatabaseTableConfig.java:146)

at com.j256.ormlite.table.TableInfo.<init>(TableInfo.java:43)

at com.j256.ormlite.dao.BaseDaoImpl.initialize(BaseDaoImpl.java:143)

at com.j256.ormlite.dao.BaseDaoImpl.<init>(BaseDaoImpl.java:120)

at com.j256.ormlite.dao.BaseDaoImpl.<init>(BaseDaoImpl.java:99)

at com.j256.ormlite.dao.BaseDaoImpl$3.<init>(BaseDaoImpl.java:801)

at com.j256.ormlite.dao.BaseDaoImpl.createDao(BaseDaoImpl.java:801)

at com.j256.ormlite.dao.DaoManager.createDao(DaoManager.java:57)

at rainstudios.meleo.util.DbUtil.init(DbUtil.java:102)

at rainstudios.meleo.util.DbUtil.init(DbUtil.java:86)

at rainstudios.meleo.util.DbUtil.db(DbUtil.java:127)

And here's the code for the registration : 

String databaseUrl = "jdbc:sqlite:" + path;

JodaTimePersister jodaPersister = JodaTimePersister.getSingleton();

DataPersisterManager.registerDataPersisters(jodaPersister);

// create a connection source to our database

connectionSource = new JdbcConnectionSource(databaseUrl);

// It fails at the initialization of placeDao !!

placeDao = DaoManager.createDao(connectionSource, Place.class);

eventDao = DaoManager.createDao(connectionSource, Event.class);

reportDao = DaoManager.createDao(connectionSource, Report.class);

fileCacheDao = DaoManager.createDao(connectionSource, FileCache.class);

jobDao = DaoManager.createDao(connectionSource, Job.class);

Of course, when I remove the call to registerDataPersisters it works fine !

I am a bit puzzled : the message is obviously wrong or there is a tricky side effect I fail

to understand :) 

Just for reference, here's the code of the JodaTimePersister : 

package rainstudios.meleo.util;

import java.lang.reflect.Field;

import java.sql.SQLException;

import org.joda.time.DateTime;

import com.j256.ormlite.field.FieldType;

import com.j256.ormlite.field.SqlType;

import com.j256.ormlite.field.types.BaseDateType;

import com.j256.ormlite.misc.SqlExceptionUtil;

import com.j256.ormlite.support.DatabaseResults;

public class JodaTimePersister extends BaseDateType {

private static final JodaTimePersister singleTon = new JodaTimePersister();

public static JodaTimePersister getSingleton() {

return singleTon;

}

private JodaTimePersister() {

super(SqlType.LONG, new Class[] { DateTime.class });

}

@Override

public Object parseDefaultString(FieldType fieldType, String defaultStr)

throws SQLException {

try {

return Long.parseLong(defaultStr);

} catch (NumberFormatException e) {

throw SqlExceptionUtil.create("Problems with field " + fieldType

+ " parsing default date-long value: " + defaultStr, e);

}

}

@Override

public Object resultToJava(FieldType fieldType, DatabaseResults results,

int columnPos) throws SQLException {

Long value = results.getLong(columnPos);

if (value == null) {

return null;

} else {

return sqlArgToJava(fieldType, value, columnPos);

}

}

@Override

public Object sqlArgToJava(FieldType fieldType, Object sqlArg, int columnPos) {

return new DateTime(sqlArg);

}

@Override

public Object javaToSqlArg(FieldType fieldType, Object obj) {

DateTime date = (DateTime) obj;

return date.getMillis();

}

@Override

public boolean isValidForField(Field field) {

// by default this is a noop

return true;

}

@Override

public boolean isEscapedValue() {

return false;

}

}

Do you have any idea what  I am doing wrong ?

Cheers,

Carl

[Carl]

I did more tests.

I tried using a String as the SqlType, and I got the same issue (Long being replaced by String in the error message).

Just so you know.

[Carl]

By the way, are the custom persisters available for Android as well ?

Performance wise, would you recommend using them on the constrained Android environment ?

Cheers,

Carl.

[Gray Watson]

On Dec 17, 2011, at 4:56 PM, Carl wrote:

> The issue is that I am getting a weird error when I try to create the first dao.
>
> Exception in thread "main" java.lang.IllegalArgumentException: Generated-id field 'id' in Place can't be type LONG. Must be one of: INTEGER INTEGER_OBJ LONG LONG_OBJ UUID

Ok. SQLite requires generated-id fields to be integers, not longs.

> Of course, when I remove the call to registerDataPersisters it works fine !

Wow. I certainly don't understand that. Is your JodaTime your id field? Can you show us your data type with the id field?

> I am a bit puzzled : the message is obviously wrong or there is a tricky side effect I fail to understand :)

Agreed. I'm puzzled as well.

> Just for reference, here's the code of the JodaTimePersister :

The persister really should have nothing to do with it.

> private JodaTimePersister() {
> super(SqlType.LONG, new Class[] { DateTime.class });
> }

Aha. So I think the first argument to the super() should be null. What you are saying with the LONG is that all Long types should be persisted by this persister. That may be confusing matters. All you need is:

super(null, new Class[] { DateTime.class });

Long value = results.getLong(columnPos);

getLong returns a lowercase l long so it will never be null. You should use the wasNull() boolean after the fact. That (unfortunately) is how JDBC works.

gray

[Gray Watson]

On Dec 17, 2011, at 5:12 PM, Carl wrote:

> By the way, are the custom persisters available for Android as well ?
> Performance wise, would you recommend using them on the constrained Android environment ?

They are available the same way as under JDBC. I see no performance ramifications of using custom persisters unless the persister code itself is an issue.

gray

[Gray Watson]

On Dec 19, 2011, at 10:58 AM, Gray Watson wrote:

>> private JodaTimePersister() {
>> super(SqlType.LONG, new Class[] { DateTime.class });
>> }
>
> Aha. So I think the first argument to the super() should be null. What you are saying with the LONG is that all Long types should be persisted by this persister. That may be confusing matters. All you need is:

No, this is completely wrong. Cancel that. Your LONG is correct. That's the how it is represented in SQL land.

I don't have any idea why you are having the problems you are having. If you respond with your data class maybe that will shed some light on the subject.
gray

[khaavren]

Is there a tutorial or anything on how to go about writing a custom
persister? I don't see anything via googling. I could look at the
source for existing persisters, but there are such nice docs on just
about everything else to do with this library, that I figured it was
worth asking.

Thanks.

[Gray Watson]

On Jan 19, 2012, at 4:03 PM, khaavren wrote:

> Is there a tutorial or anything on how to go about writing a custom
> persister? I don't see anything via googling. I could look at the
> source for existing persisters, but there are such nice docs on just
> about everything else to do with this library, that I figured it was worth asking.

Unfortunately not. I've spent a TON of time on the docs but still some of the more advanced features do not get mentioned. Custom persisters is one such area. It's already in the TODO list to add the docs. Feel free to write up your experience and I'll put it into the manual.

In the meantime, the quick recipe is:

1) You will probably want to extend BaseDataType and then override the specific methods that you want. The parseDefaultString, resultToJava, and isValidForField methods are the required methods you need to override. isValidForField won't be required in 4.34.

2) Take a look at StoredClassPersister in DataPersisterManagerTest. That gives a good starting place: http://goo.gl/upCSc

Let me know if you have any questions directly khaavren and it will help to flesh out the docs.
gray

[khaavren]

Thanks for the pointers.  Got it working!
Here's the gist:
https://gist.github.com/1b708ba702bdf9268d48

[Gray Watson]

On Jan 20, 2012, at 12:45 PM, khaavren wrote:

> Thanks for the pointers. Got it working!
> Here's the gist:
> https://gist.github.com/1b708ba702bdf9268d48

Looks good. Couple of comments:

1) Instead of overriding getSqlType, just pass ing SqlType.LONG as the first argument of the persister. I found and fixed that pattern in the code/tests.

2) You could obviously support a default string with on of the JodaTime parsers.

3) I've refactored some of the BaseDataType stuff a bit so watch for some tweaks in 4.34. Sorry about that but it's an internal improvement. It changes resultToJava to be resultToSqlArg and then it has a impl of resultToJava which calls resultToSqlArg and sqlArgToJava itself. That was the pattern used by 80+% of the data types. You also would not need your isValidForField() method because that's been improved in BaseDataType. You resultToSqlArg would then be:

public Object resultToSqlArg(FieldType fieldType, DatabaseResults results, int columnPos) throws SQLException {
return results.getLong(columnPos);
}

4) You don't need the singleton pattern. That's only for types that are built in.

gray

[Chris Nevill]

As Joda is used by a considerable number of people it would be great if this solution could be hosted somewhere and perfected!

Thanks for the work so far!

[Gray Watson]

On Feb 28, 2012, at 10:50 AM, Chris Nevill wrote:

> As Joda is used by a considerable number of people it would be great if this solution could be hosted somewhere and perfected!

As I just posted, I've added a default handler for DateTime that uses reflection. Since it is using reflection then it can't detect the field directly and you have to turn it on with:

@DatabaseField(dataType = DataType.DATE_TIME)
private DateTime dateTime;

You can also do:

DataPersisterManager.register

gray

[Craig Andrews]

I'm curious if using reflection is really best approach. Writing (and
testing) reflection code is challenging for one thing, so maintenance is a
problem. Secondly, this solution makes things less "automatic" than they
could be - for example, because reflection is used, it can't detect the
field directly.

Would it better to make an ormlite-jodatime package that has a real
dependency on joda time? And continue that pattern as other optional
persisters are added?

~Craig

[Gray Watson]

On Feb 29, 2012, at 3:19 PM, Craig Andrews wrote:

> I'm curious if using reflection is really best approach. Writing (and
> testing) reflection code is challenging for one thing, so maintenance is a problem.

I think it is a good approach or I wouldn't have added it. :-)

I would doubt that DateTime is changing very much of a class. Right now I'm using 1 method (toMillis()) and 1 constructor from it so I doubt this is an issue. Testing is pretty easy as long as I have the DateTime class in my test class path.

> Secondly, this solution makes things less "automatic" than they
> could be - for example, because reflection is used, it can't detect the
> field directly.

This would be the case no matter what. Come to think of it, I guess I could do auto detection of DateTime if I see a DateTime field. Duh. I'll add this to the TODO file.

> Would it better to make an ormlite-jodatime package that has a real
> dependency on joda time? And continue that pattern as other optional
> persisters are added?

If you want to set one up then go ahead. Unfortunately, it takes more than an insignificant amount of time to maintain the 3 packages I have. Another one just for jodatime would not be a good idea but some sort of ormlite-utility package with some other classes would be good.

gray