Changing Android database over time

[Chris Merrill]

I've tried the latest ORMLite release with Android and so far, all is
well. I was working through various iterations of my app design by
changing the DB version in my copy of the DatabaseHelper example.
However, dropping and re-creating tables obviously is not going to
work very well if I make changes to the database structure once I've
distributed the app, since users would loose all their data.

Is there a recommended strategy for dealing with this?

One option, though it seems cumbersome, would be to read out all the
objects, drop/add the tables and then put everything back.

Chris

[Kevin Galligan]

In general, its sort of a manual process. Right? Something like the following:

List<String> allSql = new ArrayList<String>();

switch(oldVersion)
{
case 4:
allSql.add("alter table mytable add column cheese(text)");
allSql.add("update mytable set cheese = 'Swiss'");
case 5:
allSql.add("alter table mytable add column wine(text)");

}

//Loop through all and execute...

Fully creating tables from the objects is really useful in
development, and when you have completely new tables, but doing that
as an upgrade strategy is a bad idea: phone, desktop or server app.
Sql updates are generally much easier than writing custom code to copy
the data out, then copy it back in. Keep in mind, you might have apps
that are more than one version behind, so you'll need to make sure all
updates are applied incrementally (hence the switch statement. NOTICE
NO BREAK STATEMENTS!!!! The code will start at the old version
number, and run through all of the rest of the statements).

ormlite is not meant to really "replace" everything. Just sort of
help out when boilerplate code would otherwise be written. Updates
are generally a manual process any way you shake it.

I am toying with something a little more removed. Like a liquibase
lite, where we track what updates already exist in your db and only
apply new ones, but in general, the phone db's are all that
complicated.

-Kevin

[Chris Merrill]

Thanks, Kevin. My only other ORM experience is with OpenJPA, which for
minor table changes (like adding columns) magically makes it happen.
At 1/30th of the size, I didn't really expect ORMLite to do that
(wouldn't be "lite" anymore, I'm guessing), so I figured I was in for
some manual work. Guess I'll have to dust off my meager SQL skills for
my first update (ugh).

Chris

[Kevin Galligan]

Well, the problem with OpenJPA (Hibernate, ibatis, whatever) is that
it knows what's different, but not *why*. Say you had a column called
"catCount", which was an integer of how many cats a person has. Then
you realize that you don't really care how many cats a person has,
just that they have some cats, so you modify the field to be a
boolean.

OpenJPA, or any other ORM, will probably delete that int column and
add the boolean column, but what it SHOULD do, is add the boolean,
fill it with 'hasCats = catCount > 0' and THEN delete catCount.

One of our "competitors", https://www.activeandroid.com/, is going to
have that problem. One of the reasons I didn't use that, and got
involved with ormlite, is because they were going down the road of
having a "migration engine", which will suffer from the same problem
described above. (See my forum posts towards the bottom here...

http://tracker.activeandroid.com/boards/1/topics/6

No ORM tool that auto-updates can avoid it. Its inherent in the domain.

Now, you might say "OK, but I don't have any updates like that".
Maybe true. Not yet. But when you do, you're kind of screwed. Any
"migration engine" should allow for some form of manual update as I've
described, because although it might be rare in your code, its sort of
like a gun to a cop. Usually you don't need it, but if you do and
don't have it, oh well.

So, we probably could provide for some sort of object diff to update,
but the effort involved would probably dwarf any urge to have it. For
me, anyway. Its kind of a poison feature. Say you have versions 1-4,
on object "Person".

Version 1:
Person object has 'catCount'

Version 2:
Person object loses 'catCount' and adds 'hasCats'. We use
auto-update, which means we've lost all the cat related info, but we
don't care. We also add a 'dogCount' field, because dogs are cooler,
and we actually care how many you have.

Version 3:
We decide that, again, the number of dogs you haven't isn't important,
but we want to retain your dog ownership info, again because dogs are
cooler, so we drop down to manual updates for this version. We run
'alter table add column hasDogs(short)'. Then 'update Person set
hasDogs = dogCount > 0'.

Version 4:
We want a field for how many categories you have, called 'catCount'.
(ha ha). We realize doing this that sqlite doesn't actually delete
columns, so the old catCount filed is still there. Set it to 0, then
repurpose it.

So, somebody downloads your app at version 1, and updates your app
through all versions. it works.

Somebody else downloads version 1, then updates on version 3. Great,
right? Fail. The update tries to access 'dogCount', which never
existed on the entity object, since the user never saw V2, and never
updated to V2.

You can imaging several other version conflicts in that series
(install V3, update to V4, not 'catCount'. Some others probably in
there).

Moral of story. You can auto-create some tables, but updates in
pretty much every context, except temporary cache, are best handled
with sql modifications (and temp caches might as well be dropped and
recreated, right?)

-Kevin

[Chris Merrill]

Kevin,

Thanks for the very thorough explanation! I've been away from
databases for a long time and had not even considered the scenario you
presented.

I've been through a few schema changes over the past few days and it
went smoothly using the strategy you described previously. The biggest
bump was discovering that SQLite does not support dropping columns
from a table - took me a while to figure out that my SQL was fine...it
was the DB that was the problem :( I guess I'll have to be more
careful in the future about adding columns that I'm not absolutely
sure I need yet.

Chris

[Gray Watson]

On Sep 24, 2010, at 8:51 AM, Chris Merrill wrote:

> I've been through a few schema changes over the past few days and it
> went smoothly using the strategy you described previously. The biggest
> bump was discovering that SQLite does not support dropping columns
> from a table - took me a while to figure out that my SQL was fine...

Huh, didn't know that. Here's the recipe to drop columns by copying to a temp table:

http://www.sqlite.org/faq.html#q11

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

Interesting that the don't rename t1_backup to t1 there.
gray

[Gray]

So I've been meaning to respond to this earlier around how SQLite can
help with complex update statements. Kevin pushed me a month ago or
so to consider changing the idea of a query builder that handled just
SELECT statements into a _statement_ builder which could also handle
DELETE and UPDATE. So I've taken the opportunity to add this
functionality to ORMLite. It will be in version 3.6 but I'd love some
feedback on it beforehand.

Basically, if you take Kevin's example: 'update Person set hasDogs =
dogCount > 0', you would do something like the following to accomplish
it:

StatementBuilder<Person, Integer> updateBuilder =
personDao.statementBuilder(StatementType.UPDATE);
updateBuilder.updateColumnExpression("hasDogs", "dogCount >
0");
personDao.update(updateBuilder.prepareStatement());

you could also do something like the following which will leave the
hasDogs as null instead of false.

updateBuilder.updateColumnValue("hasDogs", Boolean.TRUE);
updateBuilder.where().gt("dogCount", 0);
personDao.update(updateBuilder.prepareStatement());

Also, you can do things like:

StatementBuilder<Person, Integer> deleteBuilder =
personDao.statementBuilder(StatementType.DELETE);
deleteBuilder.where().gt("dogCount", 0);
personDao.delete(deleteBuilder.prepareStatement());

which will delete all rows there dogCount > 0. If you don't specify
any where() then it will delete all rows. DANGER WILL ROBINSON!

Thoughts? Comments?
gray

[Gray]

On Sep 30, 12:06 pm, Gray <256....@gmail.com> wrote:
> Basically, if you take Kevin's example: 'update Person set hasDogs =
> dogCount > 0', you would do something like the following to accomplish
> it:

I've changed the types a bit to give better type safety:

UpdateBuilder<Person, Integer> updateBuilder =
personDao.updateBuilder();

updateBuilder.updateColumnExpression("hasDogs",
"dogCount > 0");

personDao.update(updateBuilder.prepare());

and

DeleteBuilder<Person, Integer> deleteBuilder =
personDao.deleteBuilder();
deleteBuilder.where().gt("dogCount", 0);
personDao.delete(deleteBuilder.prepare());

This is (amusingly) going to mean that StatementBuilder is going to be
deprecated and QueryBuilder put back into its place. Sigh.
gray