Database sqlite. Update without data loss

Question

There is a table with columns: id, title, poem, years, favorite. So, the favorite is written 1 or 0, the default is 0, it changes if the user adds a line to the Favorites. The problem is that if I update the database (add more rows), all Favorites will disappear from the user. In the open spaces of SO, I found a solution - to attach the old and updated database. I increase the version, I add a database to the project. In the application, everything will remain as it was. The favorites are preserved, but new ones have not been added.

 public class PoemsDbHelper extends SQLiteOpenHelper {
        private static String DB_NAME = "brodsky.db";
        private static String DB_PATH = "";
        private static final int DB_VERSION = 8;

        private SQLiteDatabase db;
        private final Context context;
        private boolean needUpdate = false;

        public PoemsDbHelper(Context context) {
            super(context, DB_NAME, null, DB_VERSION);
            if (android.os.Build.VERSION.SDK_INT >= 17)
                DB_PATH = context.getApplicationInfo().dataDir + "/databases/";
            else
                DB_PATH = "/data/data/" + context.getPackageName() + "/databases/";
            this.context = context;

            copyDataBase();

            this.getReadableDatabase();
        }

        public void updateDataBase() throws IOException {
            if (needUpdate) {
                File dbFile = new File(DB_PATH + DB_NAME);
                if (dbFile.exists()) {
                    //   dbFile.delete();
                    //   copyDataBase();
                    openDataBase();
                    db.execSQL("ATTACH DATABASE '" + DB_PATH + File.separator + DB_NAME + "' AS Old_DB");
                    db.execSQL("INSERT OR IGNORE INTO poems_table (favorite) SELECT id, title, poem, subject, years FROM Old_DB.poems_table;");

                    }
            }
            needUpdate = false;
        }

        private boolean checkDataBase() {
            File dbFile = new File(DB_PATH + DB_NAME);
            return dbFile.exists();
        }

        private void copyDataBase() {
            if (!checkDataBase()) {
                this.getReadableDatabase();
                this.close();
                try {
                    copyDBFile();
                } catch (IOException mIOException) {
                    throw new Error("ErrorCopyingDataBase");
                }
            }
        }

        private void copyDBFile() throws IOException {
            InputStream input = context.getAssets().open(DB_NAME);
            OutputStream output = new FileOutputStream(DB_PATH + DB_NAME);
            byte[] buffer = new byte[1024];
            int length;
            while ((length = input.read(buffer)) > 0)
                output.write(buffer, 0, length);
            output.flush();
            output.close();
            input.close();
        }

        public boolean openDataBase() throws SQLException {
            db = SQLiteDatabase.openDatabase(DB_PATH + DB_NAME, null, SQLiteDatabase.CREATE_IF_NECESSARY);
            return db != null;
        }

        @Override
        public synchronized void close() {
            if (db != null)
                db.close();
            super.close();
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            if (newVersion > oldVersion) {
                needUpdate = true;

          }
       }
    }



 public class PoemsProvider extends ContentProvider {
 @Override
    public boolean onCreate() {
        poemsDbHelper = new PoemsDbHelper(getContext());
        try {
            poemsDbHelper.updateDataBase();
        } catch (IOException mIOException) {
            throw new Error("UnableToUpdateDatabase");
        }

        return true;
    }
}

A solution to basically exactly the same question is at [Update DB. Sqlite-asset-helper library](https://stackoverflow.com/questions/53801149/update-db-sqlite-asset-helper-library/53827525#53827525) — MikeT, Dec 18 '18 at 19:26

score 0 · Accepted Answer · answered Dec 18 '18 at 19:49

Your issue is that you are overwriting the database rather than having two distinct copies of the old and the updated database. Basically you are attaching the database to itself.

A solution has already been provided at Update DB. Sqlite-asset-helper library

The solution uses a secondary database (copies the updated database from the assets folder) and attempts to copy the rows from the updated database to the original database not copying (skipping) rows if they already exist. Rows added by the app (as opposed to those loaded with the external database) remain in the original database.

Database sqlite. Update without data loss

1 Answers1