SQLite Database Rowid sorted

Question

I have a delete Row function as according:

public boolean removeData(int position) {

    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_NAME, COL_ID+"="+position, null);
    return true;
}

This function deletes a row according to its unique ID.

How can I change this so that after deleting a row, all rows below that one will be moved up to fill the empty space in the database?

score 1 · Answer 1 · answered Mar 16 '16 at 13:29

1

That's against the design principle of a relational database. The rows are not ordered in a predictable way. So after delete you can only be sure that the deleted record appears to be away, but you have no control on the physical locations of any record, including which record(s), if any, now cover the space of the deleted one.

Querying data is another topic. You can specify a sort order, available as a parameter with the query methods. When querying your table, the results will appear exactly as you want it: If previously your results were Adam, Eve, Jack, Michael, then after deleting Jack, the result will be Adam, Eve, Michael.

answered Mar 16 '16 at 13:29

TAM

1,731
13
18

Allright, thank you for the comment. But how do I apply this when selecting a list view item and deleting the row in the database with the same list view row Id? – introzen Mar 16 '16 at 13:38
Ah, it's not about the database but the list view. Does http://stackoverflow.com/questions/4198425/updating-the-list-view-when-the-adapter-data-changes help in this case? – TAM Mar 16 '16 at 13:42
I don't seem to understand... what I need to do is to delete the row in the database with the same ID as in the listview. How can the adapter possibly change this? – introzen Mar 16 '16 at 13:58
Would it be better to delete the row in the database based on the text from the TextView instead of the ID in the Listview? – introzen Mar 16 '16 at 14:46

score 0 · Answer 2 · answered Mar 16 '16 at 15:09

The interplay between the displayed list, the domain objects behind that list, and the database is a different topic. Here are a few code snippets I use for a similar task. The basic idea is, when reading the objects that will be displayed, to include the database id with the object. So, if I read a list of products, the the domain class Product will have an id field that gets set with the database id when reading it.

To get the domain object displayed at a specific list position (e.g. the one where a user hit a delete button), the code fragment is.

public void onClick(View view) {
   Product product = (Product) ProductList.this.products.get(ProductAdapter.this.listView.getPositionForView((View) view.getParent()));
   ... now do whatever is necessary to delete the product, probably
       calling a DAO class that deletes the object based on its id,
       not the list position
   ProductAdapter.this.notifyDataSetChanged();
}

Thank you. I'll look into this when I get home from work. – introzen Mar 16 '16 at 15:15 — introzen, Mar 16 '16 at 15:15

score 0 · Answer 3 · answered Mar 16 '16 at 18:22

Solved this by removing the row in the database by the text of the TextView in the ListView instead of removing by the position of the TextView.

Now looks like this:

//Erasebutton listener
    final Button eraseButton = (Button) findViewById(R.id.eraseButton);
    assert eraseButton != null;
    eraseButton.setOnClickListener(new View.OnClickListener() { //erasebutton onclick
        public void onClick(View eraseButton) {
            SparseBooleanArray checked = questionList.getCheckedItemPositions();
            for(int i = questionList.getCount() - 1; i >= 0; i--)
            {
                if(checked.get(i)) {
                    //What to do with selected listitems
                    TextView tv = (TextView) questionList.getChildAt(i).findViewById(R.id.checkedTextView1);
                    db.removeData(tv.getText().toString());
                }
            }
            checked.clear();
            Cursor newCursor = db.getData();
            adapter.swapCursor(newCursor);
        }
    });

And removeData function now looks likte this:

public boolean removeData(String question) {

    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_NAME, COL_QUESTION+"='"+question+"'", null);
    return true;
}

SQLite Database Rowid sorted

3 Answers3