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.
- Add a new class member variable as per
:-
private boolean resetversion = false;
- 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
}
- 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.
.
- Change
:-
MyDBHandler dbHandler = new MyDBHandler(this,null);
dbHandler.getWritableDatabase();
to
:-
MyDBHandler dbHandler = new MyDBHandler(this,null,false); //<<<< Added ,false
dbHandler.getWritableDatabase();
Run the App, it should fail with what it was failing with (just testing).
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.
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
}