Question:
There is an application that works with a local SQLite database. This database stores links to files and keywords that can be used to find them.
When the application is first launched, a database is created by parsing xml. Everything worked wonderfully until the question arose of updating ALL records in 2 database tables.
I thought to execute DROP TABLE ... , and then create them and fill them in the onUpgrade () method, but when I call AsyncTask from the database, I catch an error writing to the database – the database is busy! Nothing to break!
What should be done in such a situation?
UPD 0
new LinksWordColorLoader(MyAppClass.getContext()).loadStepByStep();
содержит в себе
public LinksWordColorLoader(Context context) {
this.mContext = context;
DataBase dataBase = MyAppClass.getDataBaseInstance();
mDB = dataBase.getSQLiteDataBase();
}
public void loadStepByStep() {
new LoadDataAsync().execute();
}
// прописан в классе MyAppClass, который унаследован от Application
public static DataBase getDataBaseInstance() {
DataBase dataBase = new DataBase();
dataBase.openDB();
return dataBase;
}
UPD1
01-11 14:28:47.410 E: FATAL EXCEPTION: main
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.ui.activity.ActivityMain}: android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5): , while compiling: PRAGMA journal_mode
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2332)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2368)
at android.app.ActivityThread.access$600(ActivityThread.java:151)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1330)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:155)
at android.app.ActivityThread.main(ActivityThread.java:5536)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1074)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:841)
at dalvik.system.NativeStart.main(Native Method)
Caused by: android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5): , while compiling: PRAGMA journal_mode
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:909)
at android.database.sqlite.SQLiteConnection.executeForString(SQLiteConnection.java:654)
at android.database.sqlite.SQLiteConnection.setJournalMode(SQLiteConnection.java:340)
at android.database.sqlite.SQLiteConnection.setWalModeFromConfiguration(SQLiteConnection.java:314)
at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:239)
at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:214)
at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:463)
at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:185)
at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:177)
at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:849)
at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:829)
at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:734)
at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:1086)
at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:221)
at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:224)
at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)
at com.database.DataBase.openDB(DataBase.java:284)
at com.util.app.MyAppClass.getDataBaseInstance(MyAppClass.java:22)
at com.database.LinksWordColorLoader.(LinksWordColorLoader.java:32)
at com.database.DataBase$DBManager.onUpgrade(DataBase.java:328)
at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:257)
at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)
at com.database.DataBase.openDB(DataBase.java:284)
at com.app.MyAppClass.getDataBaseInstance(MyAppClass.java:22)
at com.ui.fragment.data.HomeFrag.onCreateView(HomeFrag.java:62)
at android.support.v4.app.Fragment.performCreateView(Fragment.java:2192)
at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:1299)
at android.support.v4.app.FragmentManagerImpl.moveFragmentToExpectedState(FragmentManager.java:1528)
at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:1595)
at android.support.v4.app.BackStackRecord.executeOps(BackStackRecord.java:758)
at android.support.v4.app.FragmentManagerImpl.executeOps(FragmentManager.java:2363)
at android.support.v4.app.FragmentManagerImpl.executeOpsTogether(FragmentManager.java:
UPD2
// в классе DataBase
private DBManager mDBManager;
private SQLiteDatabase mDB;
public void openDB() {
mDBManager = new DBManager(
mContext, // context
DATABASE_NAME, // DataBase Name
null, // SQLiteDataBase Factory
DATABASE_VERSION); // DataBase Version
mDB = mDBManager.getWritableDatabase();
}
public void closeDB() {
if (mDBManager != null) { mDBManager.close(); }
}
private class DBManager extends SQLiteOpenHelper {
DBManager(Context context, String dbName,
SQLiteDatabase.CursorFactory factory, int dbVersion) {
super(context, dbName, factory, dbVersion);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(DB_LINKS_TABLE_CREATE);
db.execSQL(DB_WORD_COLOR_TABLE_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + DB_LINKS_TABLE); // delete Links Table
db.execSQL("DROP TABLE IF EXISTS " + DB_WORD_COLORS_TABLE); // delete Word-Color Table
onCreate(db);
new LinksWordColorLoader(MyAppClass.getContext()).loadStepByStep();
}
}
Answer:
To find a solution for my question, I had to rewrite the entire code. Now everything is like this:
public class DataBaseHelper extends SQLiteOpenHelper {
public static final int DATABASE_VERSION = 3;
public static final String DATABASE_NAME = "my_database";
private static DataBaseHelper mInstance;
private static SQLiteDatabase myWritableDb;
private DataBaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
public static DataBaseHelper getInstance(Context context) {
if (mInstance == null) {
mInstance = new DataBaseHelper(context);
}
return mInstance;
}
public SQLiteDatabase getMyWritableDatabase() {
if ((myWritableDb == null) || (!myWritableDb.isOpen())) {
myWritableDb = this.getWritableDatabase();
}
return myWritableDb;
}
@Override
public void onCreate(SQLiteDatabase db) {
MyAppLogs.show("onCreate DataBase Tables");
db.execSQL(DB_LINKS_TABLE_CREATE); // CREATE LINK TABLES
db.execSQL(DB_WORD_COLOR_TABLE_CREATE); // CREATE WORD-COLOR TABLE
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// удаляем таблицы
db.execSQL("DROP TABLE IF EXISTS " + DB_LINKS_TABLE);
db.execSQL("DROP TABLE IF EXISTS " + DB_WORD_COLORS_TABLE);
onCreate(db);
new UpgradeDataAsync(db).execute();
}
// далее идут определения: название таблиц, колонок, скрипты создания этих таблиц
class UpgradeDataAsync() – responsible for filling empty tables with data in AsyncTask
public class UpgradeDataAsync extends AsyncTask {
// здесь различные поля
private SQLiteDatabase mSQLiteDB;
public UpgradeDataAsync(SQLiteDatabase db) {
this.mSQLiteDB = db;
}
// далее идут переопределенные методы AsyncTask: onPreExecute(), doInBackground(), и onProgressUpdate() и т.д. В которых мне и нужен был экземпляр SQLiteDatabase.
}