android - Update DB. Sqlite-asset-helper library

Question

I use the library android-sqlite-asset-helper

It is necessary to update, but so that the user has saved data. The names of the tables, columns remained the same. Only the number of entries in the tables has increased.

I increased DATABASE_VERSION. Put the database in the archive. As stated in the instructions created file - brodsky.db_upgrade_1-2.sql

ALTER TABLE "poems_table" RENAME TO 'poems_table_TMP'; CREATE TABLE
        "poems_table" ( "id" long NOT NULL, "title" text, "poem" text,
        "subject" text, "years" text, "favorite" text, PRIMARY KEY ("id") );
        INSERT INTO "poems_table"  ("id", "title", "poem", "subject",
        "years", "favorite") SELECT "id", "title", "poem", "subject",
        "years", "favorite" FROM "poems_table_TMP"; DROP TABLE
        "poems_table_TMP";

DbHelper

public class PoemsDbHelper extends SQLiteAssetHelper {

    private static String DB_NAME = "brodsky.db";
    private static final int DB_VERSION = 2;

    public PoemsDbHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }
}

nothing changed. old data is displayed

Answer 1

From your code and assuming that the code is in the onUpgrade method and that the version was changed from 1 to 2 then the code does nothing of use. That is it :-

1. renames the poems_table of the older database
2. creates a new poems_table
3. copies the contents of the renamed table to the newly created table
4. deletes the renamed poems_table.

Nowhere does it access the newer version of the brodsky.db

What you need (I believe) to do is open that newer brodsky.db and copy the data from that database.

Working Example

The following is code for such a working example.

This will, when the Database version is increased (for simplicity any upgrade):-

1. copy the new updated database as a separate/additional database and
2. attempt to copy the rows from the updated database into the existing database
   1. only if they are not existing rows (based upon all columns except the id column as the end user may have added poems and thus used id's).

the core code is within PoemsDbHelper.java and is :-

public class PoemsDbHelper extends SQLiteAssetHelper {

    public static final String DBNAME = "brodsky.db";
    public static final int DBVERSION = 1;

    public static final String TBLNAME = "poems_table";

    public static final String COL_ID = "id";
    public static final String COL_TITLE = "title";
    public static final String COl_POEM = "poem";
    public static final String COL_SUBJECT = "subject";
    public static final String COL_YEARS = "years";
    public static final String COL_FAVOURITE = "favorite";

    Context mContext;

    public PoemsDbHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        mContext = context;
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        getNewPoems(mContext, db); //<<<<<<<<<< get the new poems when upgraded
    }


    private void getNewPoems(Context context, SQLiteDatabase db) {

        Log.d("GETNEWPOEMS","Initiating getting new poems due to Database version increased.");

        // Prepare to copy the updated database from the assets folder
        InputStream is;
        OutputStream os;
        final String tempnewdbname = "tempbrodsky.db";
        int buffersize = 4096;
        byte[] buffer = new byte[buffersize];
        String newDBPath = mContext.getDatabasePath(tempnewdbname).getPath();

        // If a copied version of the updated database exists then delete it
        // This should not be required but better safe than sorry
        File newDBFile = new File(newDBPath);
        if (newDBFile.exists()) {
            newDBFile.delete();
        }

        // Just in case create the databases directory (it should exist)
        File newDBFileDirectory = newDBFile.getParentFile();
        if (!newDBFileDirectory.exists()) {
            newDBFileDirectory.mkdirs();
        }

        // Preapre to copy update database from the assets folder
        try {
            is = context.getAssets().open("databases/" + DBNAME);
            os = new FileOutputStream(newDBFile);
            int bytes_read;
            while ((bytes_read = is.read(buffer,0,buffersize)) > 0) {
                os.write(buffer);
            }
            os.flush();
            os.close();
            is.close();

        }catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("Ouch updated database not copied - processing stopped - see stack-trace above.");
        }

        long id = maxid(db) + 1; // Get the next available id
        SQLiteDatabase newdb = SQLiteDatabase.openDatabase(newDBFile.getPath(),null,SQLiteDatabase.OPEN_READONLY);
        Cursor csr = newdb.query(TBLNAME,null,null,null,null,null,null);
        long insert_result;
        db.beginTransaction();
        while (csr.moveToNext()) {
            insert_result = insertCorePoem(
                    db,
                    id,
                    csr.getString(csr.getColumnIndex(COL_TITLE)),
                    csr.getString(csr.getColumnIndex(COl_POEM)),
                    csr.getString(csr.getColumnIndex(COL_SUBJECT)),
                    csr.getString(csr.getColumnIndex(COL_YEARS)),
                    csr.getString(csr.getColumnIndex(COL_FAVOURITE))
            );
            // If the row was inserted then increment the if ready for the next insert
            // If not inserted (result = -2) then leave id as it is as it was unused
            if (insert_result > 0) {
                id++;
            }

        }
        db.setTransactionSuccessful();
        db.endTransaction();
        csr.close();
        newDBFile.delete(); // Delete the copied database as no longer required
    }

    public long insertCorePoem(SQLiteDatabase db, long id, String title, String poem, String subject, String years, String favourite) {

        String whereclause = COL_TITLE + "=? AND " + COl_POEM + "=? AND " + COL_SUBJECT + "=? AND " + COL_YEARS + "=?";
        String[] whereargs = new String[]{
                title,
                poem,
                subject,
                years
        };

        Cursor csr = db.query(TBLNAME,null,whereclause,whereargs,null,null,null);
        boolean rowexists = (csr.getCount() > 0);
        csr.close();
        if (rowexists) {
            Log.d("INSERTCOREPOEM","Skipping insert of row");
            return -2; // Don't insert if the poem already exists
        }

        ContentValues cv = new ContentValues();
        cv.put(COL_ID,id);
        cv.put(COL_TITLE,title);
        cv.put(COl_POEM,poem);
        cv.put(COL_SUBJECT,subject);
        cv.put(COL_YEARS,years);
        cv.put(COL_FAVOURITE,favourite);
        Log.d("INSERTCOREPOEM","Inserting new column with id " + String.valueOf(id));
        return db.insert(TBLNAME, null, cv);
    }

    private long maxid(SQLiteDatabase db) {
        long rv = 0;
        String extractcolumn = "maxid";
        String[] col = new String[]{"max(" + COL_ID + ") AS " + extractcolumn};
        Cursor csr = db.query(TBLNAME,col,null,null,null,null,null);
        if (csr.moveToFirst()) {
            rv = csr.getLong(csr.getColumnIndex(extractcolumn));
        }
        csr.close();
        return rv;
    }

    public Cursor getAllPoems() {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.query(TBLNAME,null,null,null,null,null,null);
    }
}

• getNewPoems is the main method that performs the above (note how it is called from within the onUpgrade method). This copies the updated database from the assets folder, and then extracts all of the poems (the core poems supplied with the app ()).
• Prepares to insert columns gets the current highest id from the existing database using the maxid method and adds 1 so that the new first id is unique.
• Attempts to insert every row BUT if a row to be inserted already exists then it will be skipped. This being determined in the insertCorePoem method.
• the method getAllPoems returns a cursor (used by the invoking activity).
• Note you may have other existing methods that should be included.

Testing

A database named brodsky.db was created using an external tool with 3 poems. This was copied into the database folder of the assets folder.

The following was used in an invoking activity :-

public class MainActivity extends AppCompatActivity {
    PoemsDbHelper mDBHlpr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mDBHlpr = new PoemsDbHelper(this);
        Log.d("DBVERSION", "Database version = " + String.valueOf(PoemsDbHelper.DBVERSION));
        Cursor csr = mDBHlpr.getAllPoems();
        DatabaseUtils.dumpCursor(csr);
        csr.close();
    }
}

Stage 1 - verifying/inspecting the existing database

When first run the database (with 3 poems) is copied from the assets folder and the log contains (as it does whenever the App is run at this stage) :-

12-18 06:19:58.505 3574-3574/? D/DBVERSION: Database version = 1
12-18 06:19:58.505 3574-3574/? W/SQLiteAssetHelper: copying database from assets...
12-18 06:19:58.505 3574-3574/? W/SQLiteAssetHelper: database copy complete
12-18 06:19:58.521 3574-3574/? I/SQLiteAssetHelper: successfully opened database brodsky.db
12-18 06:19:58.521 3574-3574/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@534758c8
12-18 06:19:58.521 3574-3574/? I/System.out: 0 {
12-18 06:19:58.521 3574-3574/? I/System.out:    id=1
12-18 06:19:58.521 3574-3574/? I/System.out:    title=A Poem
12-18 06:19:58.521 3574-3574/? I/System.out:    poem=This is a poem
12-18 06:19:58.521 3574-3574/? I/System.out:    subject=poem
12-18 06:19:58.521 3574-3574/? I/System.out:    years=2018
12-18 06:19:58.521 3574-3574/? I/System.out:    favorite=NO
12-18 06:19:58.521 3574-3574/? I/System.out: }
12-18 06:19:58.521 3574-3574/? I/System.out: 1 {
12-18 06:19:58.521 3574-3574/? I/System.out:    id=2
12-18 06:19:58.521 3574-3574/? I/System.out:    title=Another Poem
12-18 06:19:58.521 3574-3574/? I/System.out:    poem=This is another poem
12-18 06:19:58.521 3574-3574/? I/System.out:    subject=another poem
12-18 06:19:58.521 3574-3574/? I/System.out:    years=2017
12-18 06:19:58.521 3574-3574/? I/System.out:    favorite=NO
12-18 06:19:58.521 3574-3574/? I/System.out: }
12-18 06:19:58.521 3574-3574/? I/System.out: 2 {
12-18 06:19:58.521 3574-3574/? I/System.out:    id=3
12-18 06:19:58.521 3574-3574/? I/System.out:    title=the Third Poem
12-18 06:19:58.521 3574-3574/? I/System.out:    poem=This is the third poem
12-18 06:19:58.521 3574-3574/? I/System.out:    subject=third poem
12-18 06:19:58.521 3574-3574/? I/System.out:    years=2018
12-18 06:19:58.521 3574-3574/? I/System.out:    favorite=NO
12-18 06:19:58.521 3574-3574/? I/System.out: }
12-18 06:19:58.521 3574-3574/? I/System.out: <<<<<

Stage 2 - Using an updated database (but version number not changed).

1. 3 additional rows were added to the database using the SQlite tool
2. The existing database in the assets folder was renamed (easy to revert back for testing debugging).
3. The updated database was copied into the databases folder in the assets folder.

resulting in :-

The App was then rerun, but the version number hadn't been changed as an intermediate check. The results were above as expected i.e. as the version number wasn't changed the onUpgrade method wasn't run.

Stage 3 - Changing the version number.

The version number was increased from 1 to 2 and the App run.

resulting in the 3 rows being added as per :-

12-18 06:24:46.973 3689-3689/? D/DBVERSION: Database version = 2
12-18 06:24:46.981 3689-3689/? I/SQLiteAssetHelper: successfully opened database brodsky.db
12-18 06:24:46.981 3689-3689/? D/GETNEWPOEMS: Initiating getting new poems due to Database version increased.
12-18 06:24:46.981 3689-3689/? D/INSERTCOREPOEM: Skipping insert of row
12-18 06:24:46.985 3689-3689/? D/INSERTCOREPOEM: Skipping insert of row
12-18 06:24:46.985 3689-3689/? D/INSE