Adding a second table in a database

This is my first post! I hope you can help me out! :-)

I used the code provided by the NotePadExample from the developers doc to create a database. Now I want to add a second table to store different data. I can add notes without a problem, and the notes table is created in a similar way as my routes table is. I simply "copied" and edited the given code, but when I try to insert into the new table I get an error saying:

"0ERROR/Database(370): android.database.sqlite.SQLiteException: no such table: routes: , while compiling: INSERT INTO routes(line, arrival, duration, start) VALUES(?, ?, ?, ?);"

Can someone please take quick look at my DbAdapter class, especially the onCreate() method. and give me a hint or a solution? I really don't see any problem. My code compiles without any errors..

CODE:

public class DbAdapter {

public static final String KEY_FROM  = "title";
public static final String KEY_TO    = "body";
public static final String KEY_ROWID = "_id";

public static final String KEY_START    = "start";
public static final String KEY_ARRIVAL  = "arrival";
public static final String KEY_LINE     = "line";
publ开发者_Go百科ic static final String KEY_DURATION = "duration";

private static final String DATABASE_NAME           = "data";
private static final String DATABASE_NOTESTABLE     = "notes";
private static final String DATABASE_ROUTESTABLE    = "routes";

private static final String TAG = "DbAdapter";
private DatabaseHelper mDbHelper;
private SQLiteDatabase mDb;

/**
 * Database creation sql statement
 */
private static final String DATABASE_CREATE_NOTES =
        "create table notes (_id integer primary key autoincrement, "
                + "title text not null, body text not null)";

private static final String DATABASE_CREATE_ROUTES =
    "create table routes (_id integer primary key autoincrement, "
            + "start , arrival , " 
            + "line , duration );";

private static final int DATABASE_VERSION = 3;

private final Context mCtx;

private static class DatabaseHelper extends SQLiteOpenHelper {
    DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE_NOTES);
        Log.d(TAG, "created notes table");

        db.execSQL(DATABASE_CREATE_ROUTES);
        Log.d(TAG, "created routes table");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS notes");
        db.execSQL("DROP TABLE IF EXISTS routes");
        onCreate(db);
    }
}

/**
 * Constructor - takes the context to allow the database to be
 * opened/created
 * 
 * @param ctx the Context within which to work
 */
public DbAdapter(Context ctx) {
    this.mCtx = ctx;
}

/**
 * Open the notes database. If it cannot be opened, try to create a new
 * instance of the database. If it cannot be created, throw an exception to
 * signal the failure
 * 
 * @return this (self reference, allowing this to be chained in an
 *         initialization call)
 * @throws SQLException if the database could be neither opened or created
 */
public DbAdapter open() throws SQLException {
    mDbHelper = new DatabaseHelper(mCtx);
    mDb = mDbHelper.getWritableDatabase();
    return this;
}

public void close() {
    mDbHelper.close();
}


/**
 * Create a new note using the title and body provided. If the note is
 * successfully created return the new rowId for that note, otherwise return
 * a -1 to indicate failure.
 * 
 * @param title the title of the note
 * @param body the body of the note
 * @return rowId or -1 if failed
 */
public long createNote(String title, String body) {

    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_FROM, title);
    initialValues.put(KEY_TO, body);

    return mDb.insert(DATABASE_NOTESTABLE, null, initialValues);
}

/**
 * Create a new route using the title and body provided. If the route is
 * successfully created return the new rowId for that route, otherwise return
 * a -1 to indicate failure.
 * 
 * @param start the start time of the route
 * @param arrival the arrival time of the route
 * @param line the line number of the route
 * @param duration the routes duration 
 * @return rowId or -1 if failed
 */

public long createRoute(String start, String arrival, String line, String duration){

    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_START, start);
    initialValues.put(KEY_ARRIVAL, arrival);
    initialValues.put(KEY_LINE, line);
    initialValues.put(KEY_DURATION, duration);

    return mDb.insert(DATABASE_ROUTESTABLE, null, initialValues);
}

/**
 * Delete the note with the given rowId
 * 
 * @param rowId id of note to delete
 * @return true if deleted, false otherwise
 */
public boolean deleteNote(long rowId) {
    return mDb.delete(DATABASE_NOTESTABLE, KEY_ROWID + "=" + rowId, null) > 0;
}

/**
 * Return a Cursor over the list of all notes in the database
 * 
 * @return Cursor over all notes
 */
public Cursor fetchAllNotes() {
    return mDb.query(DATABASE_NOTESTABLE, new String[] {KEY_ROWID, KEY_FROM,
            KEY_TO}, null, null, null, null, null);
}

/**
 * Return a Cursor over the list of all routes in the database
 * 
 * @return Cursor over all routes
 */
public Cursor fetchAllRoutes() {
    return mDb.query(DATABASE_ROUTESTABLE, new String[] {KEY_ROWID, KEY_START,
            KEY_ARRIVAL, KEY_LINE, KEY_DURATION}, null, null, null, null, null);
}

/**
 * Return a Cursor positioned at the note that matches the given rowId
 * 
 * @param rowId id of note to retrieve
 * @return Cursor positioned to matching note, if found
 * @throws SQLException if note could not be found/retrieved
 */
public Cursor fetchNote(long rowId) throws SQLException {
    Cursor mCursor =
            mDb.query(true, DATABASE_NOTESTABLE, new String[] {KEY_ROWID,
                    KEY_FROM, KEY_TO}, KEY_ROWID + "=" + rowId, null,
                    null, null, null, null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;

}

/**
 * Return a Cursor positioned at the route that matches the given rowId
 * 
 * @param rowId id of route to retrieve
 * @return Cursor positioned to matching route
 * @throws SQLException if note could not be found/retrieved
 */
public Cursor fetchRoute(long rowId) throws SQLException {
    Cursor mCursor =
            mDb.query(true, DATABASE_ROUTESTABLE, new String[] {KEY_ROWID,
                    KEY_START, KEY_ARRIVAL, KEY_LINE, KEY_DURATION}, KEY_ROWID + "=" + rowId, null,
                    null, null, null, null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;

}

/**
 * Update the note using the details provided. The note to be updated is
 * specified using the rowId, and it is altered to use the title and body
 * values passed in
 * 
 * @param rowId id of note to update
 * @param title value to set note title to
 * @param body value to set note body to
 * @return true if the note was successfully updated, false otherwise
 */
public boolean updateNote(long rowId, String title, String body) {
    ContentValues args = new ContentValues();
    args.put(KEY_FROM, title);
    args.put(KEY_TO, body);
    return mDb.update(DATABASE_NOTESTABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
}

public void deleteRoutesTable(SQLiteDatabase db){
    db.delete("routes", null, null);
}
}

ADD

I want to implement a method like

public void deleteTableRoutes(SQLiteDataBase db){
 db.delete("routes", null, null);
}

My problem: I want to call this function from a point where I have no reference to my db (as far as I know) to pass as an argument for my deleteTableRoutes(SQLiteDataBase) ...

---

I think you need to choose a new (higher) version number, and modify your onUpgrade method to delete routes if it exists as well. Of course you may not actually need to delete your existing tables (e.g. creating and altering may be enough), but doing so is consistent with your current implementation.

You will call the SQLiteOpenHelper constructor with the new version, and it will internally call onUpgrade, passing the old and new versions.

---

Instead of:

@Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS notes");
        onCreate(db);
    }

I had to modify the method like the following:

@Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS notes");
        db.execSQL("DROP TABLE IF EXISTS routes"); // line added
        onCreate(db);
    }

And I instead of:

private static final int DATABASE_VERSION = 2;

I changed it to:

private static final int DATABASE_VERSION = 3;

However, I'm not completely aware of the REAL affect the change to the database version had.