Different behaviors of SQLiteOpenHelper on different Android API levels

Question

I have two android phones, which are Sumsung Galaxy s3 (Android 4.4.2, Kitkat, API19), and Nexus 6p(Android 6.0.1, Marshmallow, API23).
Here is my DatabaseHelper class:

public class DatabaseHelper extends SQLiteOpenHelper {

private static final String LOG_TAG = DatabaseHelper.class.getSimpleName();

public DatabaseHelper(Context context) {
    super(context, DatabaseContract.DATABASE_NAME, null, DatabaseContract.DATABASE_VERSION);
    Log.v(LOG_TAG, "DatabaseHelper() executed.");
}

@Override
public void onCreate(SQLiteDatabase db) {
    Log.v(LOG_TAG, "onCreate() executed.");
    Log.v(LOG_TAG, DatabaseContract.PerformanceTable.PERFORMANCE_TABLE_CREATION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.PlayerTable.PLAYER_TABLE_CREATION);
    Log.v(LOG_TAG, DatabaseContract.TeamTable.TEAM_TABLE_CREATION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.Pt3Table.Pt3_TABLE_CREATION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.Pt2Table.Pt2_TABLE_CREATION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.Pt1Table.Pt1_TABLE_CREATION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.StealTable.STEAL_TABLE_CREATION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.BlockTable.BLOCK_TABLE_CREATION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.OffRebTable.OFFREB_TABLE_CREATION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.DefRebTable.DEFREB_TABLE_CREATION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.AssistTable.ASSIST_TABLE_CREATION_QUERY);

    db.execSQL(DatabaseContract.PerformanceTable.PERFORMANCE_TABLE_CREATION_QUERY);
    db.execSQL(DatabaseContract.PlayerTable.PLAYER_TABLE_CREATION);
    db.execSQL(DatabaseContract.TeamTable.TEAM_TABLE_CREATION_QUERY);
    db.execSQL(DatabaseContract.Pt3Table.Pt3_TABLE_CREATION_QUERY);
    db.execSQL(DatabaseContract.Pt2Table.Pt2_TABLE_CREATION_QUERY);
    db.execSQL(DatabaseContract.Pt1Table.Pt1_TABLE_CREATION_QUERY);
    db.execSQL(DatabaseContract.StealTable.STEAL_TABLE_CREATION_QUERY);
    db.execSQL(DatabaseContract.BlockTable.BLOCK_TABLE_CREATION_QUERY);
    db.execSQL(DatabaseContract.OffRebTable.OFFREB_TABLE_CREATION_QUERY);
    db.execSQL(DatabaseContract.DefRebTable.DEFREB_TABLE_CREATION_QUERY);
    db.execSQL(DatabaseContract.AssistTable.ASSIST_TABLE_CREATION_QUERY);
    db.execSQL(DatabaseContract.TurnoverTable.TURNOVER_TABLE_CREATION_QUERY);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.v(LOG_TAG, "onUpgrade() executed.");

    Log.v(LOG_TAG, DatabaseContract.PerformanceTable.PERFORMANCE_TABLE_DELETION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.PlayerTable.PLAYER_TABLE_DELETION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.TeamTable.TEAM_TABLE_DELETION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.Pt3Table.PT3_TABLE_DELETION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.Pt2Table.PT2_TABLE_DELETION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.Pt1Table.PT1_TABLE_DELETION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.StealTable.STEAL_TABLE_DELETION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.BlockTable.BLOCK_TABLE_DELETION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.OffRebTable.OFFREB_TABLE_DELETION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.DefRebTable.DEFREB_TABLE_DELETION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.AssistTable.ASSIST_TABLE_DELETION_QUERY);
    Log.v(LOG_TAG, DatabaseContract.TurnoverTable.TURNOVER_TABLE_DELETION_QUERY);

    db.execSQL(DatabaseContract.TeamTable.TEAM_TABLE_DELETION_QUERY);
    db.execSQL(DatabaseContract.PlayerTable.PLAYER_TABLE_DELETION_QUERY);
    db.execSQL(DatabaseContract.Pt3Table.PT3_TABLE_DELETION_QUERY);
    db.execSQL(DatabaseContract.Pt2Table.PT2_TABLE_DELETION_QUERY);
    db.execSQL(DatabaseContract.Pt1Table.PT1_TABLE_DELETION_QUERY);
    db.execSQL(DatabaseContract.StealTable.STEAL_TABLE_DELETION_QUERY);
    db.execSQL(DatabaseContract.BlockTable.BLOCK_TABLE_DELETION_QUERY);
    db.execSQL(DatabaseContract.OffRebTable.OFFREB_TABLE_DELETION_QUERY);
    db.execSQL(DatabaseContract.DefRebTable.DEFREB_TABLE_DELETION_QUERY);
    db.execSQL(DatabaseContract.AssistTable.ASSIST_TABLE_DELETION_QUERY);
    db.execSQL(DatabaseContract.TurnoverTable.TURNOVER_TABLE_DELETION_QUERY);
    onCreate(db);
}
}

Every installation involved is a clean installation: uninstalled the app and installed it as it was new
On API19(Sumsung Galaxy 3), onCreate() method in DatabaseHelper was executed when the app started up, please refer to related log:

09-19 14:31:17.255 5727-5727/com.leontheprofessional.bballscoreboard V/DatabaseHelper: DatabaseHelper() executed.
09-19 14:31:17.355 5727-5727/com.leontheprofessional.bballscoreboard V/DatabaseHelper: onCreate() executed.
09-19 14:31:17.355 5727-5727/com.leontheprofessional.bballscoreboard V/DatabaseHelper: CREATE TABLE performance_table (_id INTEGER PRIMARY KEY AUTOINCREMENT, jersey_number TEXT, pt_3 INTEGER, pt_2 INTEGER, pt_1 INTEGER, steal INTEGER, offensive_reb INTEGER, defensive_reb INTEGER, turnover INTEGER, block INTEGER, faul INTEGER, technical_faul INTEGER, flagrant_faul INTEGER, performance_table_created_timestamp INTEGER)
09-19 14:31:17.355 5727-5727/com.leontheprofessional.bballscoreboard V/DatabaseHelper: CREATE TABLE player_table (_id INTEGER PRIMARY KEY AUTOINCREMENT, player_uuid BLOB, player_first_name TEXT, player_last_name TEXT, player_position INTEGER, player_height INTEGER, player_weight INTEGER, player_profile_created_time_stamp INTEGER, player_birthday INTEGER, player_nickname TEXT, player_icon_url TEXT, player_career_3pt_made INTEGER DEFAULT 0, player_career_3pt_missed INTEGER DEFAULT 0, player_career_3pt_rate REAL DEFAULT 0, player_career_2pt_made INTEGER DEFAULT 0, player_career_2pt_missed INTEGER DEFAULT 0, player_career_2pt_percentage REAL DEFAULT 0, player_career_1pt_made INTEGER DEFAULT 0, player_career_1pt_missed INTEGER DEFAULT 0, player_career_1pt_percentage REAL DEFAULT 0, player_career_blocks INTEGER DEFAULT 0, player_career_steals INTEGER DEFAULT 0, player_career_assists INTEGER DEFAULT 0, player_career_turnovers INTEGER DEFAULT 0, player_career_total_games_played INTEGER DEFAULT 0, player_career_total_teams_joined INTEGER DEFAULT 0, player_career_total_personal_fauls INTEGER DEFAULT 0, player_career_total_flagrant_fauls INTEGER DEFAULT 0, player_career_total_technical_fauls INTEGER DEFAULT 0)
09-19 14:31:17.355 5727-5727/com.leontheprofessional.bballscoreboard V/DatabaseHelper: CREATE TABLE team_table (_id INTEGER PRIMARY KEY AUTOINCREMENT, team_uuid BLOB, team_name TEXT, player_jersey_number_team INTEGER, team_created_time_stamp INTEGER, player_uuid_team BLOB,  UNIQUE(player_jersey_number_team, player_uuid_team))
09-19 14:31:17.355 5727-5727/com.leontheprofessional.bballscoreboard V/DatabaseHelper: CREATE TABLE pt_3_table (_id INTEGER PRIMARY KEY AUTOINCREMENT, player_uuid BLOB, game_uuid BLOB, team_uuid BLOB, pt3_shot INTEGER, pt3_timestamp INTEGER)
09-19 14:31:17.355 5727-5727/com.leontheprofessional.bballscoreboard V/DatabaseHelper: CREATE TABLE pt_2_table (_id INTEGER PRIMARY KEY AUTOINCREMENT, player_uuid BLOB, game_uuid BLOB, team_uuid BLOB, pt2_shot INTEGER, pt2_timestamp INTEGER)
09-19 14:31:17.355 5727-5727/com.leontheprofessional.bballscoreboard V/DatabaseHelper: CREATE TABLE pt_1_table (_id INTEGER PRIMARY KEY AUTOINCREMENT, player_uuid BLOB, game_uuid BLOB, team_uuid BLOB, pt1_shot INTEGER, pt1_timestamp INTEGER)
09-19 14:31:17.355 5727-5727/com.leontheprofessional.bballscoreboard V/DatabaseHelper: CREATE TABLE steal_table (_id INTEGER PRIMARY KEY AUTOINCREMENT, player_uuid BLOB, game_uuid BLOB, team_uuid BLOB, steal_timestamp INTEGER)
09-19 14:31:17.355 5727-5727/com.leontheprofessional.bballscoreboard V/DatabaseHelper: CREATE TABLE block_table (_id INTEGER PRIMARY KEY AUTOINCREMENT, player_uuid BLOB, game_uuid BLOB, team_uuid BLOB, block_timestamp INTEGER)
09-19 14:31:17.355 5727-5727/com.leontheprofessional.bballscoreboard V/DatabaseHelper: CREATE TABLE off_reb_table (_id INTEGER PRIMARY KEY AUTOINCREMENT, player_uuid BLOB, game_uuid BLOB, team_uuid BLOB, off_reb_timestamp INTEGER)
09-19 14:31:17.355 5727-5727/com.leontheprofessional.bballscoreboard V/DatabaseHelper: CREATE TABLE def_reb_table (_id INTEGER PRIMARY KEY AUTOINCREMENT, player_uuid BLOB, game_uuid BLOB, team_uuid BLOB, def_reb_timestamp INTEGER)
09-19 14:31:17.355 5727-5727/com.leontheprofessional.bballscoreboard V/DatabaseHelper: CREATE TABLE assist_table (_id INTEGER PRIMARY KEY AUTOINCREMENT, player_uuid BLOB, game_uuid BLOB, team_uuid BLOB, assist_timestamp INTEGER)

However, on Google Nexus 6P, onCreate() method was NOT executed, even though DatabaseHelper was executed or created. Please refer to related log:

09-19 14:59:14.178 15504-15504/com.leontheprofessional.bballscoreboard V/DatabaseHelper: DatabaseHelper() executed.

Only one line was here.

What's more weird:
after getting the db file from Nexus6P, I found the tables was previously designed: only 3 three were there, and columns were much fewer than current ones. When trying to insert a player object into the database via my ContentProvider, I got SQLiteException error saying "No such column..."
Every installation is a clean one, after uninstall of previous app.
After an uninstall, I tried to backup the data of this app (with package.name) to see whether database residues were still kept by any chance, but not one item in the back_up_data.ab file generated.
I tried to run the command adb uninstall <yourpackagename> on Nexus6P, but it was the same: once the app was installed, previous versions of database were kept, onCreate() method never got executed.
What went wrong?

If I had to guess, you incompletely uninstalled the previous app, and you failed to update your `DATABASE_VERSION` between the previous schema and your current one. Those would fit your symptoms: the database would have already existed (skipping `onCreate()`) and the schema version would have been the same (skipping `onUpgrade()`). — CommonsWare, Sep 19 '16 at 22:20
Your explanation sounds reasonable. But before every installation there was uninstall. Anything special I need to pay attention to to make sure of full uninstalls? — Leon, Sep 19 '16 at 22:24
"But before every installation there was uninstall" -- how exactly did you do this? Does your device have multiple user accounts)? I seem to recall some people having problems fully uninstalling an app on multi-user devices. Try using `adb uninstall your.application.id.goes.here`. — CommonsWare, Sep 19 '16 at 22:26
I did not even know what is a multiuser...After an uninstall, I tried to backup the data of this app (through its package.name) to see whether database residues were still kept, but not one item in the `back_up_data.ab` file. Does this mean a complete uninstall? — Leon, Sep 19 '16 at 22:31
I do not know, as I have not attempted to do a backup of an uninstalled app. I would expect an error message. Note, though, that if you are using the system backup and restore, the *system* will restore your backup when you run your app again. So, that may be where your old database is coming from. — CommonsWare, Sep 19 '16 at 22:32
http://stackoverflow.com/a/9602828/6061609 According to this post, Android SQLite db files will be removed after uninstalling the app. — Leon, Sep 19 '16 at 22:42

Different behaviors of SQLiteOpenHelper on different Android API levels

0 Answers0