SQLite Database and Data Access Logic

Question

Problem :

I have to create a number of tables for caching some amount of textual data, obtained by reading XMLs. These tables need to be created only once - on the initial run of the application. The data in the tables should be cleared after fixed time period. There should be a class exposed to other classes that would allow CRUD operations on this database.

Googling found me some links to tutorials for creating databases and Data Access logic.

I have some questions, please help:

1. How many DataBaseHelper(DBAdapter) classes should I have, I am guessing only one? Is it okay to have all the SQL DDL and DML statements, DB name, Table Names as static strings of this class?
2. How do I ensure that the tables are created only once?
3. Is it possible to clear the DataBase after a fixed time interval?
4. Are there any best practices to be followed when designing the database?
5. The data in the database is to be displayed in Lists. I have data in ArrayLists(created when parsing XML) as well as Database(after these lists are persisted). What adapter should I use to back the list up? Should I use ListAdapter or CursorAdapter?

Thanks.

Answer 1

1. One would be fine. Yes, SQL statements as static strings is ok too.
2. By executing the SQL to create the database in the helper's onCreate method. Android will ensure that happens only once. You can use version numbers to upgrade your database later.
3. Yes, just retrieve your database object from the helper and execute the SQL to clear the database any time you want. You can use a Timer to schedule this.
4. SQLite database best practices still apply here too.
5. Don't understand clearly what you mean, but will attempt anyhow. You can use CursorAdapter to bind your database entries to a ListView. If you just want to save your list, you can traverse the database with the Cursor returned from a SQLiteDatabase.query(...) and save to a file. Or if you still have your parsed data as an ArrayList in memory, you can still save that too to a file.

Answer 2

The most simple approach in this case is to stick with ContentProviders. They allow you to perfectly separate your DB related logic (setup of DB tables, managing of DB upgrades, CRUD ops) from the rest of the app.

Rather than rewriting it here I'll link you to the post I've done here on this SO question: http://stackoverflow.com/questions/3601535/android-database-access-design-approach/3602140#3602140

The data in the database is to be displayed in Lists. I have data in ArrayLists(created when parsing XML) as well as Database(after these lists are persisted). What adapter should I use to back the list up? Should I use ListAdapter or CursorAdapter?

Yep, the CursorAdapter is probably fine in this case. In your ListActivity you can then execute the query like

...
CursorAdapter adapter = managedQuery(....);
setListAdapter(adapter);
...

The refresh of the list will happen automatically if you implemented the ContentProvider correctly. Because in that case you will have a line like

...
getContext().getContentResolver().notifyChange(uri, null);
...

in your insert/update/delete methods which will notify the registered observers to update their data.