Android SQLite change rowids after deleting

Question

I am having trouble with the sqlite database with an android program. I'm making a widget which has a list I want to be able to scroll through, so when I click the "next" button it will show the next row in the database.

This works fine until I delete something from the database and I have missing numbers for my rowids. I have the rowids set up to AUTOINCREMENT, but after some data is deleted I have missing numbers, and when I try to scroll through those numbers an error is returned.

I just wanted to know if there is another way change the rowids to sequential order after a row is deleted. Or is there a better way to scroll through each row without referring to the rowid?

edit: this is the code I have for my "Next" activity

    @Override
protected void onCreate(Bundle savedInstanceState){
    super.onCreate(savedInstanceState);
    c = this;

    //get the shared data
    myShared = getSharedPreferences(FILENAME, 0);
    //gets NUM_LOC data from myShared or if that fails loads in number 1
    myNum = myShared.getInt(NUM_LOC, 0); 

    //check to see if myNum is equal to or bigger than the total number
    //and if it is, then set it back to one
    FlashDataBase testDB = new FlashDataBase(this);
    testDB.open();
    int testNum = testDB.getNumber();
    testDB.close();
    if(myNum >= testNum ){
        myNum = 1;
    }
    else{
        //set to next value
        myNum ++;
    }
    //set the appwidgetmanager to this context
    awm = AppWidgetManager.getInstance(c);

    //make a remoteviews for the widget
    RemoteViews v = new RemoteViews(c.getPackageName(), R.layout.widget);
    ComponentName thisWidget = new ComponentName(c, VanillaProvider.class);

    //get word from database
    FlashDataBase fdb = new FlashDataBase(this);
    fdb.open();
    String someWord = fdb.getWord(myNum);
    fdb.close();

    //store current myNum into sharedpref
    SharedPreferences.Editor editor = myShared.edit();
    editor.putInt(NUM_LOC, myNum);
    editor.commit();

    //change the widget's remoteviews
    //and update the appwidgemanager
    v.setTextViewText(R.id.middle_button, someWord);
    awm.updateAppWidget(thisWidget, v);

    finish();
}

Which seems crude, but I am thinking is there a way to use cursor.moveToNext(); in my Database class to get the next number?

this is my Database class btw private static class DbHelper extends SQLiteOpenHelper {

    public DbHelper(Context context) {
        super(context, DATABASE_NAME, null, SCHEMA_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // execute some SQL code
        db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" + KEY_ROWID
                + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
                //+ KEY_NUM + " INTEGER NOT NULL, "
                + KEY_WORD + " TEXT NOT NULL, "
                + KEY_MEANING + " TEXT NOT NULL);");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
        onCreate(db);
    }

}

public FlashDataBase(Context c) {
    myContext = c;
}

// open our db
public FlashDataBase open() throws SQLException {
    myHelper = new DbHelper(myContext);
    myDatabase = myHelper.getWritableDatabase();
    return this;
}

// and this closes it
public void close() {
    myHelper.close();
}

public long createMethod(String word, String meaning) {
    ContentValues cv = new ContentValues();
    //cv.put(KEY_NUM, num);
    cv.put(KEY_WORD, word);
    cv.put(KEY_MEANING, meaning);

    // and put these strings into our database
    // inserts the cv into the table
    return myDatabase.insert(DATABASE_TABLE, null, cv);
}

public String getData() {
    String[] columns = new String[] { KEY_ROWID, KEY_WORD, KEY_MEANING };

    // set a cursor to read the info
    Cursor c = myDatabase.query(DATABASE_TABLE, columns, null, null, null,
            null, null);
    String result = "";

    // these ints are the same as getWord's method
    // in other words, 0 1 and 2 respectively

    int iWord = c.getColumnIndex(KEY_WORD);
    int iMeaning = c.getColumnIndex(KEY_MEANING);

    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
        result += c.getInt(0) + " " + c.getString(iWord) + " " + c.getString(iMeaning) + "\n";

    }
    return result;
}
    public String getWord(long l) {

    String[] columns = new String[] { KEY_ROWID, KEY_WORD, KEY_MEANING };
    Cursor c = myDatabase.query(DATABASE_TABLE, columns, KEY_ROWID + "="
            + l, null, null, null, null);
    if (c != null) {
        // since we set KEY_ROWID to itself plus Long (l), then the
        // moveToFirst starts at L (l) ^as seen above
        c.moveToFirst();
        // thats a number 1 not letter l below
        // which is the second column, aka int iWord from the getData class
        String word = c.getString(1);
        return word;
    }
    return null;
}

public String getMeaning(long l) {

    String[] columns = new String[] { KEY_ROWID, KEY_WORD, KEY_MEANING };
    Cursor c = myDatabase.query(DATABASE_TABLE, columns, KEY_ROWID + "="
            + l, null, null, null, null);
    if (c != null) {
        // since we set KEY_ROWID to itself plus Long (l), then the
        // moveToFirst starts at L (l) ^as seen above
        c.moveToFirst();
        // thats a number 2 below
        // which is the second column, aka int iWord from the getData class
        String meaning = c.getString(2);
        return meaning;
    }
    return null;
}

public int getNumber() {
    String[] columns = new String[] { KEY_ROWID, KEY_WORD, KEY_MEANING };

    // set a cursor to read the info
    Cursor c = myDatabase.query(DATABASE_TABLE, columns, null, null, null,
            null, null);
    int result = 0;

    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
        result++;
    }
    return result;
}

Thanks for all the help also

*final edit: I figured it out a little differently than what was told to me; I kept count in a shared integer value which would reset if more than the total amount of rows, and did a loop in the database class itself using c.moveToNext() instead of the shared integer as so:

    public String getWord(long l) {

    String[] columns = new String[] { KEY_ROWID, KEY_WORD, KEY_MEANING };
    Cursor c = myDatabase.query(DATABASE_TABLE, columns, KEY_ROWID, null, null, null, null);
    String result;

    c.moveToFirst();
    for(int i=1; i<=l; i++){
        if(c.isLast()){
            c.moveToFirst();
            result = c.getString(1);
            return result;
        }

        c.moveToNext();
    }
    result = c.getString(1);
    return result;
}

Answer 1

But you shouldn't rely upon the rowid. Use the actual PK column name you have given, not this built-in name, and don't pay attention to what the PK value is and don't worry about gaps in the numbering sequence. The PK is just a convenient handle that lets you uniquely identify the row and fetch it ....where id = ?.

Answer 2

You could use SQLite version of ROWID (SQLite have them, but you can't see them till directly request them in SELECT). They are auto incremented. After you removed some record or group of records, you can perform VACUUM;. For SQLite VACUUM is very handy, because it will completely rewrite database file and remove fragmentation. All point with remove data from SQLite tables, what it's actually still reside in database file, but marked as dirty. So this method will allow you to clean database file from garbage data (file will reduce in size) and also will rebuild ROWID sequence to be correct and will rebuild indices and so on.

Answer 3

The way SQL works an autoincrementing ID will be consistent after rows are deleted, so the only ways of making the IDs sequential again is to move entries manually around or copy the rows, drop the table, create a new one and inserting the rows again.

You could however just use the LIMIT clause in your SQL query to tell which and how many rows you want to fetch. To be specific you could use a query similar to this:

SELECT * FROM `your_table` LIMIT first_row_to_fetch, number_of_rows_to_fetch

for instance, if you want 10 rows, starting with the 17th (independant of IDs), you would use

SELECT * FROM `your_table` LIMIT 17, 10

EDIT:

Given your own edit you don't execute your own query string, so in your case you should use the query() method with an added attribute for LIMIT/OFFSET (see the docs). This overload of the query() method you 're already using takes a string that should contain a number (which is the limit for the query) or two numbers separated by a comma (which is first the offset and then the limit for the query). In effect this means that your

Cursor c = myDatabase.query(DATABASE_TABLE, columns, null, null, null, null, null);

becomes

Cursor c = myDatabase.query(DATABASE_TABLE, columns, null, null, null, null, null, "offset,limit");

where offset and limit works like my first example above.

Also, you might want to put something in the orderBy attribute (the one just before the limit attribute), so you know your result comes out the same way every time. I would recommend using the KEY_ROWID for this, resulting in:

Cursor c = myDatabase.query(DATABASE_TABLE, columns, null, null, null, null, KEY_ROWID, "offset,limit");