-1

I have changed my table structure and i wrote it in onCreate(). I delete my application, and all of sqlite data, then install application and use the new table structure. However, somehow sqlite data are not deleted. I delete application. Then install it, onCreate() is not called, the below error occurs :

android.database.sqlite.SQLiteException: Can't downgrade database from version 3 to 1

I open the settings -> then clear the cache and data of application. Then delete the app. However i'm still taking the same error and still onCreate() is never called.

I read the similar questions and applied the following solution but that does not work.

MyDBHandler dbHandler = new MyDBHandler(this,null);
dbHandler.getWritableDatabase();

public class MyDBHandler extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "customers.db";

    public MyDBHandler(Context context, SQLiteDatabase.CursorFactory factory) {
        super(context, DATABASE_NAME, factory, DATABASE_VERSION);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }

    @Override
    public void onCreate(SQLiteDatabase db)
    {
        // here is never called
        String query1 = "CREATE TABLE IF NOT EXISTS " + TABLE_DRAWINGS + "(" +
                COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT ," +
                COLUMN_IMAGENAME + " TEXT , " +
                COLUMN_DRAWVERSION + " INTEGER ); ";

        String query2 = "CREATE TABLE  IF NOT EXISTS " + TABLE_ACTIONS + "(" +
                COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT ," +
                COLUMN_DRAWINGSID + " INTEGER , " +
                COLUMN_ACTION + " BLOB ," +
                " FOREIGN KEY( " +  COLUMN_DRAWINGSID   + " ) REFERENCES " + TABLE_DRAWINGS + "( "+ COLUMN_ID + " ));";

        String query3 = "CREATE TABLE  IF NOT EXISTS " + TABLE_MYWORK + "(" +
                COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT ," +
                COLUMN_DRAWINGSID + " INTEGER , " +
                COLUMN_ORDER + " INTEGER , " +
                " FOREIGN KEY( " + COLUMN_DRAWINGSID  + " ) REFERENCES " + TABLE_DRAWINGS + "( "+ COLUMN_ID + " ));";

        db.execSQL(query1);
        db.execSQL(query2);
        db.execSQL(query3);
    }
}

In a similar question in the link ( Can't downgrade database from version 2 to 1 even after fresh install and re-run ), the first answer matches with my case :

The device that you're running the code on has a database file of version 2.

However, my question is that why that database file cannot be removed. There is no answer for that. The answers forces me to use onDowngrade() or onUpgrade() method although i should not use that for my case because i have not released for my app yet. If i use onDowngrade() or onUpgrade() for each ddl change for my test app which is not published yet, this will be a bad solution i think.

I tested my app in a different device and an emulator and there is no error in that devices. Then what is the problem with the first device. Why sqlite data cannot be deleted? Why onCreate is never called?

oiyio
  • 5,219
  • 4
  • 42
  • 54
  • 1
    Possible duplicate of [Can't downgrade database from version 2 to 1 even after fresh install and re-run](https://stackoverflow.com/questions/24053786/cant-downgrade-database-from-version-2-to-1-even-after-fresh-install-and-re-run) – AskNilesh Apr 24 '18 at 07:22
  • https://stackoverflow.com/questions/43200408/cant-downgrade-database-from-version-n-to-n-1-on-samsung – AskNilesh Apr 24 '18 at 07:23
  • @NileshRathod , i read that answer and my case is different since my app works on different devices which installed freshly. But in my test device, it does not work because sqlite data are not deleted somehow. I don't need onDowngrade() method in my case. – oiyio Apr 24 '18 at 07:29
  • https://stackoverflow.com/a/24053893/7666442 – AskNilesh Apr 24 '18 at 07:30
  • @NileshRathod , in the first case of that answer matches with my case : The device that you're running the code on has a database file of version 3. My question is why i cannot delete this database file although i clear the app data and cache and delete the app? – oiyio Apr 24 '18 at 07:32
  • Can you edit your question by adding the DatabaseHelper class (MyDBHandler). – MikeT Apr 24 '18 at 09:13
  • Are you sure you've delete the apps data or uninstalled the App from settings and then rerun? And can you please confirm that you don't need any of the existing data. – MikeT Apr 24 '18 at 09:26
  • Also adding to MikeT's confirmatory questions above: Do you have any other code that would create `customers.db` in your app? – laalto Apr 24 '18 at 09:30
  • yes i have deleted app and cleared cache from settings. I only create customers.db in one place. – oiyio Apr 24 '18 at 10:51

3 Answers3

3

The following should work (did when I tested it). It will reset the version back to 1, then drop all the tables and then call the onCreate method.

That's assuming that you have retried by deleting the App's data or uninstalling the App and then rerunning the App and that failed.

There are 3 changes to be made to the MyDBHandler Class and a change to the activity that creates an instance of the MyDBhandler.

  1. Add a new class member variable as per

:-

   private boolean resetversion = false;
  1. Change the constructor to include an extra parameter so the above can be passed and set as per

:-

public MyDBHandler(
            Context context, 
            SQLiteDatabase.CursorFactory factory, 
            boolean resetversion) { //<<<< CHANGED (added boolean resetversion)

    super(context, DATABASE_NAME, factory, DATABASE_VERSION);
    this.resetversion = resetversion; //<<<< ADDED
}
  1. Add an override for the onConfigure method (i.e. add the following as a method)

:-

@Override
public void onConfigure(SQLiteDatabase db) {
    super.onConfigure(db);
    if (resetversion) {
        db.execSQL("pragma user_version = 1");
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_MYWORK);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ACTIONS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_DRAWINGS);
        onCreate(db);
    }
}
  • Dropped in reverse order so as to not result in conflicts due to FOREIGN KEYS.

.

  1. Change

:-

    MyDBHandler dbHandler = new MyDBHandler(this,null);
    dbHandler.getWritableDatabase();

to

:-

    MyDBHandler dbHandler = new MyDBHandler(this,null,false); //<<<< Added ,false
    dbHandler.getWritableDatabase();
  1. Run the App, it should fail with what it was failing with (just testing).

  2. Now change it to use true and run. The database version should be changed to 1, the 3 tables dropped and then recreated via the call to the onCreate method.

  3. Change true back to false before rerunning the App again (you should probably undo all the above changes).

This is the complete myDBHanlder class I used for testing :-

public class MyDBHandler extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "customers.db";

    // Added the following to mimic your definitions
    private static final String COLUMN_ID = BaseColumns._ID;
    private static final String TABLE_DRAWINGS = "drwaings";
    private static final String COLUMN_IMAGENAME = "imagename";
    private static final String COLUMN_DRAWVERSION = "drawversion";
    private static final String TABLE_ACTIONS = "actions";
    private static final String COLUMN_DRAWINGSID = "drawingsid";
    private static final String COLUMN_ACTION = "actioncolumn";
    private static final String TABLE_MYWORK = "mywork";
    private static final String COLUMN_ORDER = "ordercolumn";

    private boolean resetversion = false; //<<<< ADDED 

    public MyDBHandler(Context context, SQLiteDatabase.CursorFactory factory, boolean resetversion) {
        super(context, DATABASE_NAME, factory, DATABASE_VERSION);
        this.resetversion = resetversion;
    }

    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
        if (resetversion) {
            db.execSQL("pragma user_version = 1");
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_MYWORK);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_ACTIONS);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_DRAWINGS);
            onCreate(db);
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }

    @Override
    public void onCreate(SQLiteDatabase db)
    {
        // here is never called
        String query1 = "CREATE TABLE IF NOT EXISTS " + TABLE_DRAWINGS + "(" +
                COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT ," +
                COLUMN_IMAGENAME + " TEXT , " +
                COLUMN_DRAWVERSION + " INTEGER ); ";

        String query2 = "CREATE TABLE  IF NOT EXISTS " + TABLE_ACTIONS + "(" +
                COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT ," +
                COLUMN_DRAWINGSID + " INTEGER , " +
                COLUMN_ACTION + " BLOB ," +
                " FOREIGN KEY( " +  COLUMN_DRAWINGSID   + " ) REFERENCES " + TABLE_DRAWINGS + "( "+ COLUMN_ID + " ));";

        String query3 = "CREATE TABLE  IF NOT EXISTS " + TABLE_MYWORK + "(" +
                COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT ," +
                COLUMN_DRAWINGSID + " INTEGER , " +
                COLUMN_ORDER + " INTEGER , " +
                " FOREIGN KEY( " + COLUMN_DRAWINGSID  + " ) REFERENCES " + TABLE_DRAWINGS + "( "+ COLUMN_ID + " ));";

        db.execSQL(query1);
        db.execSQL(query2);
        db.execSQL(query3);
    }
}

I tested using the following code in an activity :-

    MyDBHandler mydbhlpr = new MyDBHandler(this,null, true);
    mydbhlpr.getWritableDatabase();
    CommonSQLiteUtilities.logDatabaseInfo(mydbhlpr.getWritableDatabase());
  • I first used this to set the version to 3 to reproduce the Can't downgrade database from version 3 to 1 (worked.)
  • Note CommonSQLiteUtilities is some stuff I put together to inspect Databases and Cursors, they are here Are there any methods that assist with resolving common SQLite issues?
  • I then ran again with version = 1 to confirm that it would set the database version back to 1.

Results were :-

First run to set DB version to 3 :-

04-24 10:30:42.417 1503-1503/? E/AndroidRuntime: FATAL EXCEPTION: main
    java.lang.RuntimeException: Unable to start activity ComponentInfo{fkdemo.foreignkeydemonstration/fkdemo.foreignkeydemonstration.MainActivity}: android.database.sqlite.SQLiteException: Can't downgrade database from version 3 to 1
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2059)
    ........

Second run with the version number set to 1 :-

04-24 10:33:54.690 1564-1564/fkdemo.foreignkeydemonstration D/SQLITE_CSU: PRAGMA -  sqlite_version = 3.7.11
    PRAGMA -  user_version = 1
    PRAGMA -  encoding = UTF-8
    PRAGMA -  auto_vacuum = 1
04-24 10:33:54.694 1564-1564/fkdemo.foreignkeydemonstration D/SQLITE_CSU: PRAGMA -  cache_size = 2000
    PRAGMA -  foreign_keys = 0
    PRAGMA -  freelist_count = 0
    PRAGMA -  ignore_check_constraints = 0
04-24 10:33:54.698 1564-1564/fkdemo.foreignkeydemonstration D/SQLITE_CSU: PRAGMA -  journal_mode = persist
    PRAGMA -  journal_size_limit = 524288
    PRAGMA -  locking_mode = normal
    PRAGMA -  max_page_count = 1073741823
04-24 10:33:54.702 1564-1564/fkdemo.foreignkeydemonstration D/SQLITE_CSU: PRAGMA -  page_count = 7
    PRAGMA -  page_size = 4096
    PRAGMA -  recursive_triggers = 0
    PRAGMA -  reverse_unordered_selects = 0
    PRAGMA -  secure_delete = 0
    PRAGMA -  synchronous = 2
    PRAGMA -  temp_store = 0
    PRAGMA -  wal_autocheckpoint = 100
    Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT)
04-24 10:33:54.706 1564-1564/fkdemo.foreignkeydemonstration D/SQLITE_CSU: Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Number of Indexes = 0
    Number of Foreign Keys = 0
    Number of Triggers = 0
    Table Name = sqlite_sequence Created Using = CREATE TABLE sqlite_sequence(name,seq)
    Table = sqlite_sequence ColumnName = name ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = sqlite_sequence ColumnName = seq ColumnType =  Default Value = null PRIMARY KEY SEQUENCE = 0
04-24 10:33:54.710 1564-1564/fkdemo.foreignkeydemonstration D/SQLITE_CSU: Number of Indexes = 0
    Number of Foreign Keys = 0
    Number of Triggers = 0
    Table Name = drwaings Created Using = CREATE TABLE drwaings(_id INTEGER PRIMARY KEY AUTOINCREMENT ,imagename TEXT , drawversion INTEGER )
    Table = drwaings ColumnName = _id ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 1
    Table = drwaings ColumnName = imagename ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = drwaings ColumnName = drawversion ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 0
04-24 10:33:54.714 1564-1564/fkdemo.foreignkeydemonstration D/SQLITE_CSU: Number of Indexes = 0
    Number of Foreign Keys = 0
    Number of Triggers = 0
    Table Name = actions Created Using = CREATE TABLE actions(_id INTEGER PRIMARY KEY AUTOINCREMENT ,drawingsid INTEGER , actioncolumn BLOB , FOREIGN KEY( drawingsid ) REFERENCES drwaings( _id ))
    Table = actions ColumnName = _id ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 1
    Table = actions ColumnName = drawingsid ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = actions ColumnName = actioncolumn ColumnType = BLOB Default Value = null PRIMARY KEY SEQUENCE = 0
    Number of Indexes = 0
04-24 10:33:54.718 1564-1564/fkdemo.foreignkeydemonstration D/SQLITE_CSU: Number of Foreign Keys = 1
        Column drawingsid References Table drwaings Column _id ON UPDATE Action=NO ACTION ON DELETE Action = NO ACTION MATCh Clause (unsopprted) = NONEID = 0SEQ = 0
    Number of Triggers = 0
    Table Name = mywork Created Using = CREATE TABLE mywork(_id INTEGER PRIMARY KEY AUTOINCREMENT ,drawingsid INTEGER , ordercolumn INTEGER ,  FOREIGN KEY( drawingsid ) REFERENCES drwaings( _id ))
    Table = mywork ColumnName = _id ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 1
    Table = mywork ColumnName = drawingsid ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = mywork ColumnName = ordercolumn ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 0
    Number of Indexes = 0
04-24 10:33:54.722 1564-1564/fkdemo.foreignkeydemonstration D/SQLITE_CSU: Number of Foreign Keys = 1
        Column drawingsid References Table drwaings Column _id ON UPDATE Action=NO ACTION ON DELETE Action = NO ACTION MATCh Clause (unsopprted) = NONEID = 0SEQ = 0
    Number of Triggers = 0

Additional

To utilise FOREIGN KEYS you will need to use a PRAGMA to enable them, this is recommended to be done in the onConfigure. So you should include the following in your MyDBHandler :-

@Override
public void onConfigure(SQLiteDatabase db) {
    super.onConfigure(db);
    db.execSQL("pragma foreign_keys = ON"); //<<< turn FK handling on
}
MikeT
  • 51,415
  • 16
  • 49
  • 68
0

In SQLITEOpenHelper Class give version number greater than 3. you cant downgrade to an older version of your db

public class YourHelperClassName extends SQLiteOpenHelper {

    public YourHelperClassName(Context c) {
            super(c, "YOUR_DBNAME", null, version_Number);
        }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //Add the Table creation code for new version here. Since this code works for the new user or when the user cleare data from settings.
    }
}

Refer this question which may Help you Can't downgrade database from version 2 to 1 even after fresh install and re-run

Tomin B Azhakathu
  • 2,656
  • 1
  • 19
  • 28
-1

MikeT's answer has helpful suggessions but it does not give the answer for my question. I solved the problem, let me explain it.

As a said earlier, the sqlite database cannot be deleted whatever i did. Below is the old version of my constructor :

public MyDBHandler(Context context, SQLiteDatabase.CursorFactory factory) {
    super(context, DATABASE_NAME, factory, DATABASE_VERSION);  // sqlite dosyasi This PC\Galaxy A3 2016\Phone\Android\data\com.lyrebirdstudio.mandala\files\customers.db lokasyonunda tutulur.
    Log.d("testxx","path : " + context.getFilesDir());
    mcontext = context;
}

Logs :

04-25 12:09:31.681 16448-16448/com.test.myapp D/testxx: path : /data/user/0/com.test.myapp/files

In other words, sqlite data is in this directory and it cannot be deleted.

In the constructor, i changed the 2nd argument of the super() . Below is the new version of my constructor :

public MyDBHandler(Context context, SQLiteDatabase.CursorFactory factory) {
    super(context, context.getExternalFilesDir(null)+ File.separator + DATABASE_NAME, factory, DATABASE_VERSION);  
    Log.d("testxy","path2 : " + context.getExternalFilesDir(null)+ File.separator + DATABASE_NAME);
    mcontext = context;
}

Logs:

04-25 12:09:31.684 16448-16448/com.test.myapp D/testxy: path2 : /storage/emulated/0/Android/data/com.test.myapp/files/customers.db

By using this directory solved the problem of sqlite data which cannot be deleted.

oiyio
  • 5,219
  • 4
  • 42
  • 54