B8787

I used following code to initialise my sql database based on http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/:

public class DataBaseHelper extends SQLiteOpenHelper {

	// The Android's default system path of your application database.
	private static String DB_PATH = "/data/data/com.test/databases/";
	private static String DB_NAME = "**";
	private SQLiteDatabase myDataBase;
	private final Context myContext;
	private Cursor c;
	static int numberOfLevels = 10;

	/**
	 * Constructor Takes and keeps a reference of the passed context in order to
	 * access to the application assets and resources.
	 * 
	 * @param context
	 */
	public DataBaseHelper(Context context) {
		super(context, DB_NAME, null, 1);
		this.myContext = context;
	}

	/**
	 * Creates a empty database on the system and rewrites it with your own
	 * database.
	 * */
	public void createDataBase() throws IOException {

		boolean dbExist = checkDataBase();
		if (!dbExist) {
			// By calling this method and 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();

			try {
				copyDataBase();
			} catch (IOException e) {
				throw new Error("Error copying database");
			}
		}
	}

	/**
	 * 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() {
		File dbFile = new File(DB_PATH + DB_NAME);
		return dbFile.exists();
	}

	/**
	 * 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 {

		// Open your local db as the input stream
		InputStream myInput = myContext.getAssets().open(DB_NAME);

		// Path to the just created empty db
		String outFileName = DB_PATH + DB_NAME;

		// Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream(outFileName);

		// transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}

		// Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();

	}

	public void openDataBase() throws SQLException {
		// Open the database
		String myPath = DB_PATH + DB_NAME;

		myDataBase = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READONLY);
	}

	@Override
	public synchronized void close() {
		if (c != null)
			c.close();
		if (myDataBase != null)
			myDataBase.close();

		super.close();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
	}

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

So I also made a sql file, the thing is, it is very likely that in the future this db will be updated. The structure is as follows, the database has two tables: questions and answers.

The thing now is, that in the future the questions table will be expanded, for example, it now has 100 records, (id 1-100) but from time to time there will be 50 records added. The answers given to these questions are stored in the answers table.

QUESTION: how can I make sure that a new app version (with a new, expanded .db (sql) file) gets updated correctly, which means: keeping the answers given (and stored in 'old' sql database), but replace the questions table if the new database contains more records in the questions table than the previous one.

Hope it makes sense, thanks!

jonbonazza

You need to perform what is called a "database migration" when a new database version is detected.

See here:
How to do database schema migrations in Android? - Stack Overflow

__________________
My blog, Inverted Keystrokes, is looking for aspiring developers (not necessarily with Android) to post articles. If you have any development experience and are interested in participating, please PM me. =)

B8787

I replaced the onUpgrade method by:

public File getDatabasePath(String name) {

        File file = myContext.getDatabasePath(name);

        return file;
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("ATTACH DATABASE ? as AttachedDB",
                new String[] { getDatabasePath("quizDbNew").getPath() });
        db.execSQL("INSERT OR IGNORE INTO questions (_id, file, answer, level) SELECT _id, file, answer, level FROM AttachedDB.questions");
        db.execSQL("DETACH AttachedDB");

    }

But it seems that it doesn't copy the elements in the table..