android - Can't copy pre-created db from assets

Question

android cant copy my pre create db from assets , the old db still exist in data/data/packagename/dabases, i want to change old db with new one

ive tried to change this.getReadableDatabase(); into SQLiteDatabse db = this.getReadabledatabase()
if (db.isOpen()){
    db.close();
}


 public void createDatabase(){
   boolean dbExist = checkDatabase();
   if(!dbExist){
       this.getReadableDatabase();
       SQl
       try {
           copyDatabase();
       }catch (IOException e){
           Log.e(this.getClass().toString(),"eror kopi");
           throw new Error("error kopi");
       }
   }else{
       Log.i(this.getClass().toString(),"databse udah ada");
   }
}                                                                   
    private void copyDatabase() throws IOException {
    InputStream externalDBStream = 
     context.getAssets().open(DATABASE_NAME);
    String outFileName = DB_PATH + DATABASE_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.flush();
    localDBStream.close();
    externalDBStream.close();
}

i expected to use my pre-create db

Answer 1

You have a number of issues, assuming that you want to replace an existing pre-existing database with another copy.

The issue you are facing is that as a database exists then the copy will not proceed i.e. the checkDatabase() will return true.

If you were to simply invoke the copyDatabase() then the database would be copied every time the App is run, which would be inefficient and destructive if the database can be modified by the user.

What you need to do is have an indicator, that can be tested, to see if the pre-existing database has been changed. There are various ways but the most likely/common way would be to utilise the SQLite user_version. This is an integer value and is frequently used to update the current database via the onUpgrade method.

As part of opening the database, the SQLiteOpenHelper (and therefore a subclass thereof) it compares the user_version stored in the database against the supplied version number (4th parameter to the SQLiteOpenHelper super call) and if the latter is greater than the value stored in the database then the onUpgrade method is called. (if the reverse then the onDowngrade method will be called and without it being coded an exception occurs).

The user_version can be set in the SQLite management tool user the SQL PRAGMA user_version = n.

Another issue is that from Android 9, the database is opened in WAL (Write-Ahead Logging) mode by default. The above code by using this.getReadableDatabase(); results in the -shm and -wal files being created. Their existence results in a trapped error (as they then do not match the copied database) that then results in the SQLiteOpenHelper creating an empty (theoretically usable database) basically wiping the copied database (I believe this is what happens).

The reason why this.getReadableDatabase(); has been used is that it get's around the issue that when there is no App data, the databases folder/directory doesn't exist and using the above creates it. The correct way is to create the databases directory/folder if it doesn't exist. As such the -wal and -shm files are not created.

The following is an example DatabseHelper that overcomes the issues and additionally allows modified versions of the pre-existing database to be copied based upon changing the user_version.

public class DBHelperV001 extends SQLiteOpenHelper {

    public static final String DBNAME = "test.db"; //<<<<<<<<<< obviously change accordingly

    //
    private static int db_user_version, asset_user_version, user_version_offset = 60, user_version_length = 4;
    private static String stck_trc_msg = " (see stack-trace above)";
    private static String sqlite_ext_journal = "-journal";
    private static String sqlite_ext_shm = "-shm";
    private static String sqlite_ext_wal = "-wal";
    private static int copy_buffer_size = 1024 * 8; //Copy data in 8k chucks, change if wanted.

    SQLiteDatabase mDB;

    /**
     *  Instantiate the DBHelper, copying the databse from the asset folder if no DB exists
     *  or if the user_version is greater than the user_version of the current database.
     *  NOTE The pre-existing database copied into the assets folder MUST have the user version set
     *  to 1 or greater. If the user_version in the assets folder is increased above the
     *
     * @param context
     */
    public DBHelperV001(Context context) {

        // Note get the version according to the asset file
        // avoid having to maintain the version number passed
        super(context, DBNAME, null, setUserVersionFromAsset(context,DBNAME));
        if (!ifDbExists(context,DBNAME)) {
            copyDBFromAssets(context, DBNAME,DBNAME);
        } else {
            setUserVersionFromAsset(context,DBNAME);
            setUserVersionFromDB(context,DBNAME);
            if (asset_user_version > db_user_version) {
                copyDBFromAssets(context,DBNAME,DBNAME);
            }
        }
        // Force open (and hence copy attempt) when constructing helper
        mDB = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }

    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }

    /**
     * Check to see if the databse file exists
     * @param context   The Context
     * @param dbname    The databse name
     * @return          true id database file exists, else false
     */
    private static boolean ifDbExists(Context context, String dbname) {
        File db = context.getDatabasePath(dbname);
        if (db.exists()) return true;
        if (!db.getParentFile().exists()) {
            db.getParentFile().mkdirs();
        }
        return false;
    }

    /**
     * set the db_user_version according to the user_version obtained from the current database file
     * @param context   The Context
     * @param dbname    The database (file) name
     * @return          The user_version
     */
    private static int setUserVersionFromDB(Context context, String dbname) {
        File db = context.getDatabasePath(dbname);
        InputStream is;
        try {
            is = new FileInputStream(db);
        } catch (IOException e) {
            throw new RuntimeException("IOError Opening " + db.getPath() + " as an InputStream" + stck_trc_msg);
        }
        db_user_version = getUserVersion(is);
        Log.d("DATABASEUSERVERSION","Obtained user_version from current DB, it is " + String.valueOf(db_user_version)); //TODO remove for live App
        return db_user_version;
    }

    /**
     * set the asset_user_version according to the user_version from the asset file
     * @param context
     * @param assetname
     * @return
     */
    private static int setUserVersionFromAsset(Context context, String assetname) {
        InputStream is;
        try {
            is = context.getAssets().open(assetname);
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("IOError Getting asset " + assetname + " as an InputStream" + stck_trc_msg);
        }
        asset_user_version = getUserVersion(is);
        Log.d("ASSETUSERVERSION","Obtained user_version from asset, it is " + String.valueOf(asset_user_version)); //TODO remove for Live App
        return asset_user_version;
    }

    /**
     * Retrieve SQLite user_version from the provied InputStream
     * @param is    The InputStream
     * @return      the user_version
     */
    private static int getUserVersion(InputStream is) {
        String ioerrmsg = "Reading DB header bytes(60-63) ";
        int rv;
        byte[] buffer = new byte[user_version_length];
        byte[] header = new byte[64];
        try {
            is.skip(user_version_offset);
            is.read(buffer,0,user_version_length);
            ByteBuffer bb = ByteBuffer.wrap(buffer);
            rv = ByteBuffer.wrap(buffer).getInt();
            ioerrmsg = "Closing DB ";
            is.close();
            return rv;
        } catch (IOException e) {
            e.printStackTrace();
            throw  new RuntimeException("IOError " + ioerrmsg + stck_trc_msg);
        }
    }

    /**
     * Copy the database file from the assets 
     * Note backup of existing files may not be required
     * @param context   The Context
     * @param dbname    The database (file)name
     * @param assetname The asset name (may therefore be different but )
     */
    private static void copyDBFromAssets(Context context, String dbname, String assetname) {
        String tag = "COPYDBFROMASSETS";
        Log.d(tag,"Copying Database from assets folder");
        String backup_base = "bkp_" + String.valueOf(System.currentTimeMillis());
        String ioerrmsg = "Opening Asset " + assetname;

        // Prepare Files that could be used
        File db = context.getDatabasePath(dbname);
        File dbjrn = new File(db.getPath() + sqlite_ext_journal);
        File dbwal = new File(db.getPath() + sqlite_ext_wal);
        File dbshm = new File(db.getPath() + sqlite_ext_shm);
        File dbbkp = new File(db.getPath() + backup_base);
        File dbjrnbkp = new File(db.getPath() + backup_base);
        File dbwalbkp = new File(db.getPath() + backup_base);
        File dbshmbkp = new File(db.getPath() + backup_base);
        byte[] buffer = new byte[copy_buffer_size];
        int bytes_read = 0;
        int total_bytes_read = 0;
        int total_bytes_written = 0;

        // Backup existing sqlite files
        if (db.exists()) {
            db.renameTo(dbbkp);
            dbjrn.renameTo(dbjrnbkp);
            dbwal.renameTo(dbwalbkp);
            dbshm.renameTo(dbshmbkp);
        }
        // ALWAYS delete the additional sqlite log files
        dbjrn.delete();
        dbwal.delete();
        dbshm.delete();

        //Attempt the copy
        try {
            ioerrmsg = "Open InputStream for Asset " + assetname;
            InputStream is = context.getAssets().open(assetname);
            ioerrmsg = "Open OutputStream for Databse " + db.getPath();
            OutputStream os = new FileOutputStream(db);
            ioerrmsg = "Read/Write Data";
             while((bytes_read = is.read(buffer)) > 0) {
                 total_bytes_read = total_bytes_read + bytes_read;
                 os.write(buffer,0,bytes_read);
                 total_bytes_written = total_bytes_written + bytes_read;
             }
             ioerrmsg = "Flush Written data";
             os.flush();
             ioerrmsg = "Close DB OutputStream";
             os.close();
             ioerrmsg = "Close Asset InputStream";
             is.close();
             Log.d(tag,"Databsse copied from the assets folder. " + String.valueOf(total_bytes_written) + " bytes were copied.");
             // Delete the backups
             dbbkp.delete();
             dbjrnbkp.delete();
             dbwalbkp.delete();
             dbshmbkp.delete();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("IOError attempting to " + ioerrmsg + stck_trc_msg);
        }
    }
}

Example usage

Consider the following assets files (sqlite databases) (warning as they are the App would fail) :-

So there are two databases (identical bar the user_version as set using PRAGMA user_version = 1 and PRAGMA user_version = 2 respectively/according to the file names) For the brand new, first time run App (i.e. uninstalled) then file test.dbV1 is renamed to test.db and the following activity is used :-

public class MainActivity extends AppCompatActivity {

    DBHelperV001 mDbhlpr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mDbhlpr = new DBHelperV001(this);
        DatabaseUtils.dumpCursor(
                mDbhlpr.getWritableDatabase().query(
                        "sqlite_master",
                        null,null,null,null,null,null
                )
        );
    }
}

• This simply instantiates the Database Helper (which will copy or use the databa