预先填充的数据库在API 28上不起作用，并且在列表28中抛出“没有这样的表".例外 [英] A pre-populated database does not work at API 28 throws "no such table" exception
问题描述
我在项目中使用了预先填充的数据库.我有一个创建的.sql基础，并在第一次启动时将其复制.该基础大33mb.
I use a pre-populated database in my project. I have a created .sql base and copy it at first start.The base is big 33mb.
private void copyDataBase() throws IOException {
InputStream externalDbStream = context.getAssets().open(DB_NAME);
String outFileName = DB_PATH + DB_NAME;
OutputStream localDbStream = new FileOutputStream(outFileName);
byte[] buffer = new byte[1024];
int bytesRead;
while ((bytesRead = externalDbStream.read(buffer)) > 0) {
localDbStream.write(buffer, 0, bytesRead);
}
localDbStream.close();
externalDbStream.close();
}
它在API 28以外的其他Android版本上都可以正常工作.API28引发原因:android.database.sqlite.SQLiteException:无此类表:短语"例外:
It works fine at different android versions except API 28. API 28 throws "Caused by: android.database.sqlite.SQLiteException: no such table: phrases" exception:
Caused by: android.database.sqlite.SQLiteException: no such table: phrases (code 1 SQLITE_ERROR): , while compiling: select * from phrases where complexity > 1 and known is null or known == 1 order by complexity limit 10
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:903)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:514)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46)
谢谢.
推荐答案
使用SQLite并复制先前不支持API 28的现有数据库的App的典型原因是，解决该问题的方法是: database 文件夹不存在(如果目录不存在，则复制将失败)是用于创建一个空数据库，然后覆盖该数据库.
The typical cause of an App, that uses SQLite and that copies a pre-existing database suddenly not working for API 28 is that to get around the issue of the database folder not existing (the copy would fail if the directory didn't exist) is to create an empty database and then overwrite the database.
但是，默认情况下，默认情况下，来自API 28，SDK使用WAL(预写日志记录)，并且创建要覆盖的空数据库会导致-shm和-wal文件创建.这些文件的存在导致复制后数据库为空.
However, as by default, from API 28, the SDK uses WAL (Write-ahead logging) and that creating the empty database to be overwritten, results in the -shm and -wal files being created. It is the existence of these files that result in the database being empty after the copy.
- 我相信这是因为一旦打开复制的数据库，就会检测到错误信息，并且SDK的方法会创建一个空的可用数据库(这是推测，实际上并未显示出来).
一种快速但不建议使用的修复方法是，重写将SQLiteOpenHelper子类化的类中的 onConfigure 方法，以使用
The quick, but not recommended fix, is to override the onConfigure method in the class that subclasses SQLiteOpenHelper to use the disableWriteAheadLogging method so that the database is opened in journal mode.
- 下面的完整代码(第二段代码)包括此内容，但该行已被注释掉.
为了从WAL的好处中获益，推荐的方法是检查数据库目录的存在，如果目录不存在则创建目录，而不是创建要覆盖的数据库(，因此复制数据库时-shm和-wal文件不存在)
The recommended method, so as to gain from the benefits of WAL, is to check for the existence of the database directory and create the directory if it doesn't exist rather than create a database to be overwritten (and therefore the -shm and -wal file don't exist when the database is copied)
以下是示例方法，其中在检查数据库是否存在时检查/创建目录( 显然，这需要相应地调整 ):-
The following is an example method where the directory is checked/created when checking to see if the database exists (obviously this would need to be tailored accordingly) :-
private boolean checkDataBase() {
/**
* Does not open the database instead checks to see if the file exists
* also creates the databases directory if it does not exists
* (the real reason why the database is opened, which appears to result in issues)
*/
File db = new File(myContext.getDatabasePath(DB_NAME).getPath()); //Get the file name of the database
if (db.exists()) return true; // If it exists then return doing nothing
// Get the parent (directory in which the database file would be)
File dbdir = db.getParentFile();
// If the directory does not exits then make the directory (and higher level directories)
if (!dbdir.exists()) {
db.getParentFile().mkdirs();
dbdir.mkdirs();
}
return false;
}
- 请注意，这取决于变量DB_NAME是数据库名称(数据库文件的文件名)，并且数据库的最终位置是标准位置(data/data/the_package/databases/).
以上内容摘自SQLiteOpenHelper的以下子类:-
The above has been extracted from the following subclass of SQLiteOpenHelper :-
public class DBHelper extends SQLiteOpenHelper {
private static String DB_NAME = "db";
private SQLiteDatabase myDataBase;
private final Context myContext;
private int bytes_copied = 0;
private static int buffer_size = 1024;
private int blocks_copied = 0;
public DBHelper(Context context) {
super(context, DB_NAME, null, 1);
this.myContext = context;
// Check for and create (copy DB from assets) when constructing the DBHelper
if (!checkDataBase()) {
bytes_copied = 0;
blocks_copied = 0;
createDataBase();
}
}
/**
* Creates an empty database on the system and rewrites it with your own database.
* */
public void createDataBase() {
boolean dbExist = checkDataBase(); // Double check
if(dbExist){
//do nothing - database already exists
} else {
//By calling this method an empty database will be created into the default system path
//of your application so we are gonna be able to overwrite that database with our database.
//this.getReadableDatabase();
//<<<<<<<<<< Dimsiss the above comment
//By calling this method an empty database IS NOT created nor are the related -shm and -wal files
//The method that creates the database is flawed and was only used to resolve the issue
//of the copy failing in the absence of the databases directory.
//The dbExist method, now utilised, checks for and creates the database directory, so there
//is then no need to create the database just to create the databases library. As a result
//the -shm and -wal files will not exist and thus result in the error associated with
//Android 9+ failing with due to tables not existining after an apparently successful
//copy.
try {
copyDataBase();
} catch (IOException e) {
File db = new File(myContext.getDatabasePath(DB_NAME).getPath());
if (db.exists()) {
db.delete();
}
e.printStackTrace();
throw new RuntimeException("Error copying database (see stack-trace above)");
}
}
}
/**
* Check if the database already exist to avoid re-copying the file each time you open the application.
* @return true if it exists, false if it doesn't
*/
private boolean checkDataBase() {
/**
* Does not open the database instead checks to see if the file exists
* also creates the databases directory if it does not exists
* (the real reason why the database is opened, which appears to result in issues)
*/
File db = new File(myContext.getDatabasePath(DB_NAME).getPath()); //Get the file name of the database
Log.d("DBPATH","DB Path is " + db.getPath()); //TODO remove for Live App
if (db.exists()) return true; // If it exists then return doing nothing
// Get the parent (directory in which the database file would be)
File dbdir = db.getParentFile();
// If the directory does not exits then make the directory (and higher level directories)
if (!dbdir.exists()) {
db.getParentFile().mkdirs();
dbdir.mkdirs();
}
return false;
}
/**
* Copies your database from your local assets-folder to the just created empty database in the
* system folder, from where it can be accessed and handled.
* This is done by transfering bytestream.
* */
private void copyDataBase() throws IOException {
final String TAG = "COPYDATABASE";
//Open your local db as the input stream
Log.d(TAG,"Initiated Copy of the database file " + DB_NAME + " from the assets folder."); //TODO remove for Live App
InputStream myInput = myContext.getAssets().open(DB_NAME); // Open the Asset file
String dbpath = myContext.getDatabasePath(DB_NAME).getPath();
Log.d(TAG,"Asset file " + DB_NAME + " found so attmepting to copy to " + dbpath); //TODO remove for Live App
// Path to the just created empty db
//String outFileName = DB_PATH + DB_NAME;
//Open the empty db as the output stream
File outfile = new File(myContext.getDatabasePath(DB_NAME).toString());
Log.d("DBPATH","path is " + outfile.getPath()); //TODO remove for Live App
//outfile.setWritable(true); // NOT NEEDED as permission already applies
//OutputStream myoutputx2 = new FileOutputStream(outfile);
/* Note done in checkDatabase method
if (!outfile.getParentFile().exists()) {
outfile.getParentFile().mkdirs();
}
*/
OutputStream myOutput = new FileOutputStream(outfile);
//transfer bytes from the inputfile to the outputfile
byte[] buffer = new byte[buffer_size];
int length;
while ((length = myInput.read(buffer))>0) {
blocks_copied++;
Log.d(TAG,"Ateempting copy of block " + String.valueOf(blocks_copied) + " which has " + String.valueOf(length) + " bytes."); //TODO remove for Live App
myOutput.write(buffer, 0, length);
bytes_copied += length;
}
Log.d(TAG,
"Finished copying Database " + DB_NAME +
" from the assets folder, to " + dbpath +
String.valueOf(bytes_copied) + "were copied, in " +
String.valueOf(blocks_copied) + " blocks of size " +
String.valueOf(buffer_size) + "."
); //TODO remove for Live App
//Close the streams
myOutput.flush();
myOutput.close();
myInput.close();
Log.d(TAG,"All Streams have been flushed and closed."); //TODO remove for Live App
}
@Override
public synchronized void close() {
if(myDataBase != null)
myDataBase.close();
super.close();
}
@Override
public void onCreate(SQLiteDatabase db) {
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
@Override
public void onConfigure(SQLiteDatabase db) {
super.onConfigure(db);
Log.d("DBCONFIGURE","Database has been configured "); //TODO remove for Live App
//db.disableWriteAheadLogging(); //<<<<<<<<<< un-comment to force journal mode
}
@Override
public void onOpen(SQLiteDatabase db) {
super.onOpen(db);
Log.d("DBOPENED","Database has been opened."); //TODO remove for live App
}
}
- 请注意，上面的代码用于/打算用于开发/实验，因此包括可以删除的代码.
这篇关于预先填充的数据库在API 28上不起作用，并且在列表28中抛出“没有这样的表".例外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
