代码之家 › 专栏 › 技术社区 › oiyio

SQLiteException无法将数据库从版本3降级为1虽然我删除了我的应用程序,但仍发生错误

android-sqlite android

-1

oiyio · 技术社区 · 7 年前

我已经更改了表结构,并在onCreate()中编写了它。我删除我的应用程序和所有sqlite数据,然后安装应用程序并使用新的表结构。然而,不知何故,sqlite数据并没有被删除。我删除应用程序。然后安装,未调用onCreate(),出现以下错误:

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

我打开设置->然后清除应用程序的缓存和数据。然后删除应用程序。但是,我仍然会遇到相同的错误,并且仍然不会调用onCreate()。

我阅读了类似的问题,并应用了以下解决方案,但这不起作用。

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);
    }
}

在链接中的类似问题中( Can't downgrade database from version 2 to 1 even after fresh install and re-run ),第一个答案与我的情况相符:

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

然而,我的问题是,为什么不能删除该数据库文件。对此没有答案。答案迫使我使用onDowngrade()或onUpgrade()方法,尽管我不应该在我的案例中使用,因为我还没有发布我的应用程序。如果我对尚未发布的测试应用程序的每个ddl更改使用onDowngrade()或onUpgrade(),我认为这将是一个糟糕的解决方案。

我在不同的设备和模拟器上测试了我的应用程序,这些设备中没有错误。那么第一台设备有什么问题。为什么不能删除sqlite数据?为什么从不调用onCreate?

3 回复 | 直到 7 年前

MikeT 7 年前

以下应该可以工作(我测试它时做的)。它会将版本重置回1,然后删除所有表,然后调用 onCreate 方法

这是假设您已通过删除应用程序的数据或卸载应用程序,然后重新运行应用程序进行重试,但失败。

要对MyDBHandler类进行3个更改,并对创建MyDBHandler实例的活动进行一个更改。

根据添加新的类成员变量

   private boolean resetversion = false;

更改构造函数以包含一个额外参数,以便可以根据

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

    super(context, DATABASE_NAME, factory, DATABASE_VERSION);
    this.resetversion = resetversion; //<<<< ADDED
}

为添加覆盖 onConfigure 方法(即添加以下内容作为方法)

@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);
    }
}

按相反顺序删除,以避免外键导致冲突。

。

改变

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

到

    MyDBHandler dbHandler = new MyDBHandler(this,null,false); //<<<< Added ,false
    dbHandler.getWritableDatabase();

运行应用程序, 它应该以失败的方式失败 (只是测试)。
现在将其更改为使用 符合事实的 然后跑。应将数据库版本更改为1,删除3个表,然后通过调用 创建时的回调函数 方法
再次运行应用程序之前,请将true改回false (您可能应该撤消上述所有更改)。

这是用于测试的完整myDBHanlder I类:-

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);
    }
}

我在活动中使用以下代码进行了测试:-

    MyDBHandler mydbhlpr = new MyDBHandler(this,null, true);
    mydbhlpr.getWritableDatabase();
    CommonSQLiteUtilities.logDatabaseInfo(mydbhlpr.getWritableDatabase());

我首先使用它将版本设置为3以复制 Can't downgrade database from version 3 to 1 (已工作。)
注意CommonSQLiteUtilities是我用来检查数据库和游标的一些东西,它们在这里 Are there any methods that assist with resolving common SQLite issues?
然后,我再次运行version=1,以确认它会将数据库版本设置回1。

结果如下:-

首次运行将DB版本设置为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)
    ........

第二次运行,版本号设置为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

附加的

要使用外键,您需要使用PRAGMA来启用它们,建议在 onConfigure配置 因此,您应该在 MyDBHandler :-

@Override
public void onConfigure(SQLiteDatabase db) {
    super.onConfigure(db);
    db.execSQL("pragma foreign_keys = ON"); //<<< turn FK handling on
}

Tomin B Azhakathu 7 年前

在SQLITEOpenHelper类中,给出大于3的版本号。您不能降级到旧版本的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.
    }
}

请参考此问题,这可能会对您有所帮助 Can't downgrade database from version 2 to 1 even after fresh install and re-run

-1

oiyio 7 年前

米凯特的回答有一些有益的建议,但它并没有回答我的问题。我解决了这个问题,让我解释一下。

如前所述,无论我做了什么,都不能删除sqlite数据库。下面是我的构造函数的旧版本:

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;
}

日志:

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

换句话说,sqlite数据位于该目录中,无法删除。

在构造函数中,我更改了super()的第二个参数。以下是我的构造函数的新版本:

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;
}

日志:

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

通过使用此目录,解决了无法删除sqlite数据的问题。