Should simultaneously having multiple different SQLite Database Versions be considered harmful?

Question

I am breaking out each SQLite table's code (including initial construction of the table as well as operations on that table) into separate classes extending SQLiteOpenHelper.

In order to get these tables to be created (that is to say, to get the onCreate() method to be called), I have to increment the DATABASE_VERSION (see my own answer here for the details).

But with this methodology, I end up with different version numbers for each class/table - I had to set the DATABASE_VERSION value for the second table I created to 2, I will have to set the third one to 3, etc.

So I will end up with multiple different version values for the same DATABASE (*.db file). If they are all the same val, the onUpgrade() method does not get called, and thus, the onCreate() method is not called, and thus the new table is not created.

Is this "okay" - having a database with several different simultaneous version numbers?

The only other (reasonable) option I know of is to put all the DB code (covering multiple tables) into a single class that extends SQLiteOpenHelper - is doing so actually the preferred method? IOW, is my separation of the db code into several classes, one for each table, a help or a hindrance?

UPDATE

So this is my non-destructive way (by inserting "IF NOT EXISTS" into the DDL) to gradually add new tables, using, per CommonsWare's advice, just one class that extends SQLiteOpenHelper:

@Override
public void onCreate(SQLiteDatabase db) {
    String CONDITIONALLY_CREATE_VENDORS_TABLE = "CREATE TABLE IF NOT EXISTS " +
            TABLE_VENDORS + "("
            + COLUMN_ID + " INTEGER PRIMARY KEY," + COLUMN_VENDORID
            + " TEXT," + COLUMN_COMPANYNAME + " TEXT" + ")";
    db.execSQL(CONDITIONALLY_CREATE_VENDORS_TABLE);
    // add more tables as needed following the pattern above
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    //db.execSQL("DROP TABLE IF EXISTS " + TABLE_VENDORS); <= Only need to DROP if the table's structure changes; so comment such a line out for the particular table in that case
    onCreate(db);
}

NOTE: Whenever I do add a new table, I have to "up" (increment) the DATABASE_VERSION value, so that onUpgrade()/onCreate() are called.

If I understand you correctly, you intend to split up an existing table into two or more, newly constructed tables. This can be done without modifying the database version. If you change the DB version, then onUpgrade method will be called. This will wipe out all the existing data that you possess in any of the tables, and this is not atall needed. Moreover, onUpgrade and DB version can be helpful if you want to change the Table structure of some EXISTING table, but in your case you just wish to fragment one table into two or more new tables. — Parth Kapoor, Apr 15 '14 at 17:22
No, I'm not talking about refactoring a table into N tables; I'm just talking about adding new tables into an existing database (adding one table at a time to the code). — B. Clay Shannon-B. Crow Raven, Apr 15 '14 at 17:37

score 3 · Accepted Answer · answered Apr 15 '14 at 17:25

3

I am breaking out each SQLite table's code (including initial construction of the table as well as operations on that table) into separate classes extending SQLiteOpenHelper

It is important, for thread safety among other reasons, to have a single instance of SQLiteDatabase that you use consistently. That in turn, will require you to have a single SQLiteOpenHelper class. The exception would be for totally independent database files (one SQLiteHelper per database), but that's not usually needed.

Is this "okay" - having a database with several different simultaneous version numbers?

That is not "okay" at all. The highest number wins.

The only other (reasonable) option I know of is to put all the DB code (covering multiple tables) into a single class that extends SQLiteOpenHelper - is doing so actually the preferred method? IOW, is my separation of the db code into several classes, one for each table, a help or a hindrance?

Breaking out "the db code into several classes, one for each table" is not necessarily a problem. What is a problem is having them be independent subclasses of SQLiteOpenHelper.

Let's say that you want these classes not merely to handle table creation and upgrades, but also other CRUD operations related to the table. Having a dedicated class for that is fine. However, the table creation and upgrade logic needs to be driven by a single SQLiteOpenHelper class. Simply have onCreate() and onUpgrade() on your SQLiteOpenHelper delegate the actual work to the per-table classes. This way, you get your code organization, without having multiple SQLiteOpenHelper classes.

answered Apr 15 '14 at 17:25

CommonsWare

986,068
189
2,389
2,491

I don't see a problem in "the highest number winning." It worked for the two tables I've added so far; the first one has a version of 1, the second a version of 2. I was able to add records to the second table (after "upping" the version number in that class) without dropping the first table. If I were to have just one version number (in a single class that extends SQLiteOpenHelper), wouldn't I have to increment the version number of the DB each time I added a new table, which in turn would DROP (and then recreate) all the previous tables, thus wiping out the data stored in them? – B. Clay Shannon-B. Crow Raven Apr 15 '14 at 17:54
As far as thread-safetiness goes, only one table at a time, and thus only one class that extends SQLiteOpenHelper at a time, would be active. – B. Clay Shannon-B. Crow Raven Apr 15 '14 at 17:55
@B.ClayShannon: "wouldn't I have to increment the version number of the DB each time I added a new table" -- yes. "which in turn would DROP (and then recreate) all the previous tables, thus wiping out the data stored in them?" -- only if that is what you are doing in `onUpgrade()`. Most developers would not do that. "only one table at a time... would be active" -- I have no idea what "active" means here. But, since you are using background threads for your database I/O, there are 2+ threads in use regardless of "active" or not. – CommonsWare Apr 15 '14 at 17:57
Okay, I'll change it per your suggestion (thanks!) because not many things scare me more than threading problems. And I agree with you: when programming, it *is* common to swear - or at least to feel the urge to. – B. Clay Shannon-B. Crow Raven Apr 15 '14 at 18:02
And it seems as if the "CREATE TABLE IF NOT EXISTS" is what I need to avoid blowing away existing tables/data. – B. Clay Shannon-B. Crow Raven Apr 15 '14 at 18:18
1

@B.ClayShannon: You really should be looking at the versions passed into `onUpgrade()` and making decisions based upon that, akin to how Rails migrations are processed. Your solution to chain to `onCreate()` will not work in the real world very long, once you want to add columns to a table and other changes that are not simply adding new tables. – CommonsWare Apr 15 '14 at 18:29
Good thought; I was thinking I would just add a "DROP TABLE" command to the onUpgrade() whenever I altered the structure of a table. – B. Clay Shannon-B. Crow Raven Apr 15 '14 at 19:06
1

@B.ClayShannon: Except that will lose your data. Use `ALTER TABLE` to add columns. In a pinch, you can copy the data from the existing table to a temp table, drop and rebuild the real table, and use `INSERT INTO ... SELECT FROM ...` to get the data back out of the temp table into the rebuilt real table. – CommonsWare Apr 15 '14 at 19:18
Good point (ALTER TABLE for DROP); I should have thought of that. – B. Clay Shannon-B. Crow Raven Apr 15 '14 at 19:22

Should simultaneously having multiple different SQLite Database Versions be considered harmful?

UPDATE

1 Answers1