Override table name per DAO instance

[Joe]

I have a table with a large number of immutable objects (lets call the
immutable objects Foo). I need to have a second table (Bar) with one
Bar object for every Foo per user. It looks something like this:

@DatabaseTable
class Foo {
@DatabaseField(id = true)
int fooId;

// ... additional Foo fields
}

@DatabaseTable
class User {
@DatabaseField(id = true)
int userId;
}

@DatabaseTable
class Bar {
@DatabaseField
int userId;

@DatabaseField
int fooId;

// ... additional Bar fields
}

The Foo table contains potentially thousands of objects, and Bar of
course contains that number times the number of users (which
admittedly is very small). If I were using a normal relational
database, one optimization I would make would be to make one table per
user. The creation and deletion of users is very rare, and there are
only a few of them. In this case, I could get rid of the userId field
in the Bar table, index the fooId field, and make lookups a lot
faster.

Using ORMLite, there are even bigger benefits in that I would be able
to make fooId into a proper "id" field in the Bar object, allowing me
to do things like use an object cache, etc.

What I was hoping to find was a way to override the tableName
parameter for a particular instance of the Bar DAO. If such a feature
exists, I was unable to find it. It would look something like:

String overrideTableName = "bar_username";
Dao<Bar, Integer> barDao = DaoManager.createDao(source, Bar.class,
overrideTableName);

There would probably also have to be an overrideTableName parameter
allowed in the TableUtils.createTable() function.

Anyway, I know one design constraint is to keep the "lite" in ORMLite,
but this feature would be very useful to me.

[jc]

I don't think you will be able to do that using the ormlite
annotations, but you *might* be able to do what you want if you
manually create your table configurations. Take a look at the docs
for the simple example: http://ormlite.com/javadoc/ormlite-core/doc-files/ormlite_5.html#SEC47

The example shows creating a Dao from a custom DatabaseTableConfig
which has another constructor allowing you to specify the table name.
Following the example using OOTB ormlite classes, you would need to
create a DatabaseTableConfig and Dao instance per table to do what you
want: http://ormlite.com/javadoc/ormlite-core/com/j256/ormlite/table/DatabaseTableConfig.html#DatabaseTableConfig(java.lang.Class,
java.lang.String, java.util.List)

You will need to be careful using the DaoManager and stick to the
create/register/lookup methods that take the DatabaseTableConfig
arguments. Trying to use the create/lookup methods based on the data
Class (in your example Bar.class) will cause the DaoManager to use
default logic to create a default BaseDaoImpl instance that most
likely cause issues.

If you want to know why you need separate Dao instances per table, the
BaseDaoImpl internally stores the DatabaseTableConfig, TableInfo, and
a StatementExecutor, etc which would each be specific to the table.
If you tried to extend BaseDaoImpl in an attempt to create a single
Dao instance that swapped these internal references based on a
specific table, the result would most likely not be thread safe. I'm
not saying it would not be possible, but the result would probably be
messy and require lots of synchronization that would probably impact
performance.

As a side note, it would be possible to do this using Annotations but
it would result in an ugly object model that required a data object
per user. For instance, Bar.class is the data object super class that
defines your fields but shouldn't need to define the DatabaseTable
annotation and then BarUser1.class, BarUser2.class, etc would each
extend Bar.class and define the DatabaseTable annotation resulting in
different tables and data objects. Internally, ormlite would create
separate DatabaseTableConfig objects for you at the expense of having
to define a complex object model in your code based on users which
would be ugly. Dynamically creating your DatabaseTableConfig objects
instead of using annotations as described above avoids this.

As far as adding this feature/function to ormlite, we'll have to wait
and see what Gray thinks. I would suspect that this would add a lot
of complexity to the current library and wouldn't see a lot of usage
for this specific use case.

[Joe]

Thanks for the response jc.

It looks like I would still be able to use the field annotations if I
did something like:

// Create the table config, with a custom table name
DatabaseTableConfig<Bar, Void> tableConfig =
DatabaseTableConfig.fromClass(Bar.class);
config.setTableName("bar_" + userName);

// Create table in the database
TableUtils.createTable(tableConfig);

// Create a DAO around the user specific table.
Dao<Bar, Void> barDao = DaoManager.createDao(connectionSource,
tableConfig);

I think this is exactly what I was looking for, thanks for the help!

-joe

On Dec 7, 2:15 pm, jc <jroma...@gmail.com> wrote:
> I don't think you will be able to do that using the ormlite
> annotations, but you *might* be able to do what you want if you
> manually create your table configurations.  Take a look at the docs
> for the simple example:  http://ormlite.com/javadoc/ormlite-core/doc-files/ormlite_5.html#SEC47
>
> The example shows creating a Dao from a custom DatabaseTableConfig
> which has another constructor allowing you to specify the table name.
> Following the example using OOTB ormlite classes, you would need to
> create a DatabaseTableConfig and Dao instance per table to do what you

> want:  http://ormlite.com/javadoc/ormlite-core/com/j256/ormlite/table/Databa...,

[Gray Watson]

On Dec 8, 2011, at 11:27 AM, Joe wrote:

> It looks like I would still be able to use the field annotations if I did something like:
>

> ...
> config.setTableName("bar_" + userName);

Yeah, that's a good solution that should work. As JC said, you need to be careful about the DaoManager cache which is going to cache the first BarDao regardless of its table name. So some of the autoCreate/autoRefresh features of ORMLite might get confused.

I'd be curious to know how it works when you get it rolling. Let us know.
gray

[jc]

On Dec 8, 10:27 am, Joe <alv...@gmail.com> wrote:
>
> It looks like I would still be able to use the field annotations if I
> did something like:
>

I didn't realize Gray had that method to create the
DatabaseTableConfig from annotations manually. So yes, your code is
correct and you could still use the annotation and then just override
what you need.

Also, if you use object caching make sure to be careful about sizing
and verify it does what you need. I haven't used this feature yet, so
Gray would have to comment if the default caching is based on the
object classname or not and if you can simply set your own caching
object per Dao to get the cache isolation that you need.

[Gray Watson]

On Dec 8, 2011, at 2:01 PM, jc wrote:

> Also, if you use object caching make sure to be careful about sizing
> and verify it does what you need. I haven't used this feature yet, so
> Gray would have to comment if the default caching is based on the
> object classname or not and if you can simply set your own caching
> object per Dao to get the cache isolation that you need.

Yes, you can simply set your own cacheing object per Dao. That's the only way it works actually. As long as you use a different cache per DAO you won't get conflicts.

gray

[Joe]

I think this is going to work out for me, but I think I may have run
into a bug.

TableUtils.createTable() takes a DatabaseTableConfig as a parameter,
to which I pass my tableConfig that has had the name overridden.
However, that tableConfig is not passed down into the doCreateTable
method below. Is there any reason doCreateTable() can't use the
tableConfig that is passed in to get the table name, rather than the
TableInfo that it pulls out of the class? It seems like they should
generally be the same value, unless you're doing what I'm doing, in
which case you would prefer the value in the tableConfig rather than
the one embedded in the class.

Any reason doCreateTable couldn't take a tableConfig as an additional
parameter and get the table name from that?

Thanks,
- joe

[Joe]

Ok, I found the problem. Somewhere in my code I was still creating a
Dao from the class directly, without an overriding tableConfig, and
then when I went to create the version WITH the tableConfig, it was
finding the old one in the cache (see createDao() on line 152 of
DaoManager.java).

My problem is fixed by not creating the class based Dao in the first
place, but it seems like there could also be some code here that
checks to see that the the tableConfig is the same before returning
the cached class Dao.

Anyway, I'll leave that to you're judgement Gray, since it is working
fine for my purposes now.

Thanks!
- joe

[joe]

Ok, I take it back, I still have a problem.

The problem seems to be with DaoManager.createDao(ConnectionSource, DatabaseTableConfig<T>).

On line 141 of DaoManager.java there is a check to see if there is an existing Dao that matches the given table config, and this correctly fails since the table config is different.

However, on line 150 there is a second check, this time only using the class and not comparing table configs at all.  In this case, a dao is already found for the class using the first table and it is returned, even though we have specified a different table name in the table config.

It seems like a simple enough fix would be putting a simple check to make sure the table names are the same inside the second if block.  Something like this:

// now look it up in the class map

ClassConnectionSource key2 = new ClassConnectionSource(connectionSource, tableConfig.getDataClass());

dao = lookupDao(key2);

if (dao != null && dao.getTableConfig().getTableName().equals(tableConfig.getTableName()) {

tableMap.put(key, dao);

@SuppressWarnings("unchecked")

D castDao = (D) dao;

return castDao;

}

Or perhaps something should be added to the ClassConnectionSource key?  I'm not really sure of the correct answer, but it seems like it should be fixable pretty easily.  It currently is preventing me from creating the second Dao for the second table name.  

Thanks,

-Joe