4

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

1 Answers1

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 :-

enter image description here

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/INSERTCOREPOEM: Skipping insert of row
12-18 06:24:46.985 3689-3689/? D/INSERTCOREPOEM: Inserting new column with id 4
12-18 06:24:46.985 3689-3689/? D/INSERTCOREPOEM: Inserting new column with id 5
12-18 06:24:46.985 3689-3689/? D/INSERTCOREPOEM: Inserting new column with id 6
12-18 06:24:46.993 3689-3689/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@53462060
12-18 06:24:46.993 3689-3689/? I/System.out: 0 {
12-18 06:24:46.993 3689-3689/? I/System.out:    id=1
12-18 06:24:46.993 3689-3689/? I/System.out:    title=A Poem
12-18 06:24:46.993 3689-3689/? I/System.out:    poem=This is a poem
12-18 06:24:46.993 3689-3689/? I/System.out:    subject=poem
12-18 06:24:46.993 3689-3689/? I/System.out:    years=2018
12-18 06:24:46.993 3689-3689/? I/System.out:    favorite=NO
12-18 06:24:46.993 3689-3689/? I/System.out: }
12-18 06:24:46.993 3689-3689/? I/System.out: 1 {
12-18 06:24:46.993 3689-3689/? I/System.out:    id=2
12-18 06:24:46.993 3689-3689/? I/System.out:    title=Another Poem
12-18 06:24:46.993 3689-3689/? I/System.out:    poem=This is another poem
12-18 06:24:46.993 3689-3689/? I/System.out:    subject=another poem
12-18 06:24:46.993 3689-3689/? I/System.out:    years=2017
12-18 06:24:46.993 3689-3689/? I/System.out:    favorite=NO
12-18 06:24:46.993 3689-3689/? I/System.out: }
12-18 06:24:46.993 3689-3689/? I/System.out: 2 {
12-18 06:24:46.993 3689-3689/? I/System.out:    id=3
12-18 06:24:46.993 3689-3689/? I/System.out:    title=the Third Poem
12-18 06:24:46.993 3689-3689/? I/System.out:    poem=This is the third poem
12-18 06:24:46.993 3689-3689/? I/System.out:    subject=third poem
12-18 06:24:46.993 3689-3689/? I/System.out:    years=2018
12-18 06:24:46.993 3689-3689/? I/System.out:    favorite=NO
12-18 06:24:46.993 3689-3689/? I/System.out: }
12-18 06:24:46.993 3689-3689/? I/System.out: 3 {
12-18 06:24:46.993 3689-3689/? I/System.out:    id=4
12-18 06:24:46.993 3689-3689/? I/System.out:    title=The Update Poem
12-18 06:24:46.993 3689-3689/? I/System.out:    poem=This is a new poem
12-18 06:24:46.993 3689-3689/? I/System.out:    subject=4th Core Poem
12-18 06:24:46.993 3689-3689/? I/System.out:    years=2019
12-18 06:24:46.993 3689-3689/? I/System.out:    favorite=NO
12-18 06:24:46.993 3689-3689/? I/System.out: }
12-18 06:24:46.993 3689-3689/? I/System.out: 4 {
12-18 06:24:46.993 3689-3689/? I/System.out:    id=5
12-18 06:24:46.993 3689-3689/? I/System.out:    title=Another Updated Poem
12-18 06:24:46.993 3689-3689/? I/System.out:    poem=This is another updated poem
12-18 06:24:46.997 3689-3689/? I/System.out:    subject=5th Core Poem
12-18 06:24:46.997 3689-3689/? I/System.out:    years=2019
12-18 06:24:46.997 3689-3689/? I/System.out:    favorite=NO
12-18 06:24:46.997 3689-3689/? I/System.out: }
12-18 06:24:46.997 3689-3689/? I/System.out: 5 {
12-18 06:24:46.997 3689-3689/? I/System.out:    id=6
12-18 06:24:46.997 3689-3689/? I/System.out:    title=The 3rd Updated Poem
12-18 06:24:46.997 3689-3689/? I/System.out:    poem=This is the 3rd updated poem
12-18 06:24:46.997 3689-3689/? I/System.out:    subject=6th Core Poem
12-18 06:24:46.997 3689-3689/? I/System.out:    years=2019
12-18 06:24:46.997 3689-3689/? I/System.out:    favorite=NO
12-18 06:24:46.997 3689-3689/? I/System.out: }
12-18 06:24:46.997 3689-3689/? I/System.out: <<<<<

Running the App again and nothing is changed.

Increasing the version to 3 results in the same 6 rows. However, onUpgrade runs and an attempt is made to add the rows but they are all skipped, as per :-

12-18 06:27:08.933 3789-3789/so53801149.so53801149poemupdatefromassets D/DBVERSION: Database version = 3
12-18 06:27:08.937 3789-3789/so53801149.so53801149poemupdatefromassets I/SQLiteAssetHelper: successfully opened database brodsky.db
12-18 06:27:08.937 3789-3789/so53801149.so53801149poemupdatefromassets D/GETNEWPOEMS: Initiating getting new poems due to Database version increased.
12-18 06:27:08.937 3789-3789/so53801149.so53801149poemupdatefromassets D/INSERTCOREPOEM: Skipping insert of row
12-18 06:27:08.945 3789-3789/so53801149.so53801149poemupdatefromassets D/INSERTCOREPOEM: Skipping insert of row
12-18 06:27:08.945 3789-3789/so53801149.so53801149poemupdatefromassets D/INSERTCOREPOEM: Skipping insert of row
12-18 06:27:08.945 3789-3789/so53801149.so53801149poemupdatefromassets D/INSERTCOREPOEM: Skipping insert of row
12-18 06:27:08.945 3789-3789/so53801149.so53801149poemupdatefromassets D/INSERTCOREPOEM: Skipping insert of row
12-18 06:27:08.945 3789-3789/so53801149.so53801149poemupdatefromassets D/INSERTCOREPOEM: Skipping insert of row
12-18 06:27:08.949 3789-3789/so53801149.so53801149poemupdatefromassets I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@5347529c
..... results same as above (6 rows)
  • Note you may need to tailor the query in the insertCorePoem method to suit your needs.
MikeT
  • 51,415
  • 16
  • 49
  • 68
  • could you help me? Your code does not work. Application falls. Logs - java.lang.RuntimeException: Unable to start activity ComponentInfo{rodionova.lyubov.test/rodionova.lyubov.test.MainActivity}: com.readystatesoftware.sqliteasset.SQLiteAssetHelper$SQLiteAssetException: Missing databases/brodsky.db file (or .zip, .gz archive) in assets, or target folder not writable Error in the line - at rodionova.lyubov.test.PoemsDbHelper.getAllPoems(PoemsDbHelper.java:156) I myself have been looking for a mistake for two days. Although I understand that your answer is logically correct –  Dec 21 '18 at 13:43
  • That message is normally because the file **brodsky.db** is not in the assets/databases folder. The above expects the original database in the assets/databases folder to be replaced with a modified version of the original database. As such this would then apply all the core data to new installs as well as upgrades from a prior package. – MikeT Dec 21 '18 at 19:26
  • You were right. Many thanks for such a detailed explanation! –  Dec 21 '18 at 21:25