Please feel free to skip to the question as this background understanding may not be necessary for you.
I am new to android and sqlite and I am designing an app which has a content provider to access an sqlite database with multiple tables. There are several activities which use different adapters to display info from the database to the UI (i.e. cursor adapters, fragment state page adapter and array adapters). I have been having issues with the delete function in all of my activities which don't use the cursor adapter. When I try to update or delete a row from a table it deletes the wrong row or it doesn't delete anything at all. I believe it is a problem with the adapter where I am trying to figure out which row it is to send the correct info to the content provider.
The identical java code works perfectly with the cursor adapter and the rows delete normally and the other CRUD operations work. The insert and query functions work normally for all tables.The provider code uses a switch statement for each table but it is basically identical for each Uri case. All of the tables have _id as the integer primary key which is NOT set to auto increment. Since I don't fully understand how the row id works my java code does not reflect it and I keep having these issues. Although I have read many documents about content providers, adapters, sqlite databases, etc. certain key details are not clear to me.
My question is how does the row id get assigned numbers in the database when it is set to _id column as a primary key and what happens to those numbers when the database is changed?
For example, say I have an empty database. Initially after inserting the first row, the Uri will return a path segment for the 0 row and the adapter position would be 0... what would the row id for the database be (0 or 1) ?
Then for each row I insert, I know that row number would increase by one integer. Say I insert 4 rows - 0,1,2,3. Now when I am ready to delete - should the last path segment on the Uri be one integer less than the row number (i.e do I send a Uri with a last path segment of 2 to delete row 3)? Finally, after deleting, will the row ids then automatically get re-assigned so that row 4 now becomes row 3 ? Is there some code that I need to write to make that happen in the database? The primary keys are not set to auto increment.
I have different adapters and activities to where I can not access the actual database row ID once the data is displayed in the UI, so I am using the adapter position as a surrogate. This is why I am having trouble with update and delete. Thank you very much if you read this entire question and take the time to answer it, it would help me tremendously.
I have an activity that is tabbed and uses FragmentStatePagerAdapter that is populated by a database. Here is the Adapter that I adjusted to keep track of the rows:
**EDITED:**
public class TankSectionsPagerAdapter extends FragmentStatePagerAdapter {
private ArrayList<Fragment> tankFragments = new ArrayList<>();
private ArrayList<String> tankTitles = new ArrayList<>();
//I added this ArrayList below to store the tankIDs to match the Fragments//
**public ArrayList<Integer> tankIDs = new ArrayList<>();**
public TankSectionsPagerAdapter(FragmentManager fm) {
super(fm);
}
@Override
public Fragment getItem(int position) {
return tankFragments.get(position);
}
@Override
public int getCount() {
return tankFragments.size();
}
@Override
public String getPageTitle(int position) {
return tankTitles.get(position);
}
public void addPage(Fragment fragment, String tankName) {
tankFragments.add(fragment);
tankTitles.add(tankName);
// I added this below so the ID position would match each fragment position //
**tankIDs.add(tankId);**
notifyDataSetChanged();
}
// Finally I added this method below to the adapter//
** public ArrayList<Integer> getPageId(){
return tankIDs;
}**
Here is the activity where the method is called and where it pulls the data from the cursor to pass to the Adapter. There is a loop where each row creates a page(tab) in the ViewPager:
public class MyClass extends Tank implements TabLayout.OnTabSelectedListener, LoaderManager.LoaderCallbacks<Cursor> {
public TankSectionsPagerAdapter tankSectionsPagerAdapter;
TabLayout tabLayout;
private ViewPager mViewPager;
...
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_my_class);
mViewPager = (ViewPager) findViewById(R.id.container);
addPages(mViewPager);
tabLayout = (TabLayout) findViewById(R.id.tabLayout);
tabLayout.setupWithViewPager(mViewPager);
tabLayout.addOnTabSelectedListener(this);
}
public void addPages(ViewPager mViewPager) {
tankSectionsPagerAdapter = new TankSectionsPagerAdapter(getSupportFragmentManager());
mViewPager.setAdapter(tankSectionsPagerAdapter)
try {
...
Cursor cursor = getContentResolver().query(MyProvider.CONTENT_URI_TABLE_TANK_SETUP, MyDatabaseHelper.ALL_TABLE_TANK_SETUP_COLUMNS, tankDataFilter, null, null);
if (cursor.moveToFirst()) {
do {
tName = cursor.getString(cursor.getColumnIndex(MyDatabaseHelper.TANK_NAME)); ...
// all the variables are stored in the bundle passed to the fragment/
...
**tankSectionsPagerAdapter.addPage(MainTankFragment.newInstance(tankBundle),tName, int tankID);**
tankDataFilter = tankDataFilter + (-1);
}
while (cursor.moveToNext());
cursor.close();
} else {
Toast...
}
} catch (Exception e) {
Toast..
}
}
...
// Get Row ID from cursor(tankID), parameter in addPage() above//
//Get ID's from Adapter //
** ArrayList <Integer> pageID= tankSectionsPagerAdapter.getPageId();**
This is the Activity with Spinner to choose the rows/fragments to edit or delete.
public class EditTank extends Tank implements LoaderManager.LoaderCallbacks<Cursor>
...
// Get the ArrayList//
ArrayList<Integer> IDtags =getIDIntent.getIntegerArrayListExtra("tank_edit_key");
loadEditTankSpinnerData();
////***Here is the Spinner. Use row ID from the ArrayList******
Note: Don't use the id of the spinner
editTankSpinner.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
@Override
public void onItemSelected(AdapterView<?> parent, View view int position, long id) {
*** tankID = IDtags.get(position); ***
}
private void loadEditTankSpinnerData() {
List<String> tankNames = new ArrayList<String>();
Cursor cursor = getContentResolver().query(MyProvider.CONTENT_URI_TABLE_TANK_SETUP, MyDatabaseHelper.TANK_NAMES, null, null,null);
try{
if (cursor.moveToFirst()) {
do {
tankNames.add(cursor.getString(1));
} while (cursor.moveToNext());
cursor.close();
} else {
deleteTankBtn.setEnabled(false);
editTankBtn.setEnabled(false);
Toast...
}
} catch (Exception e) {
Toast...
}
...
}
The above code worked well with CursorAdapter but not with the fragmentStatePagerAdapter (***Prior to the edits it did not work, now it works well and deletes correctly).
I spent days(weeks) on this because I didn't understand why the rows weren't deleting. I hope this helps someone.
Thanks to @pskink, @CL, and @albeee - this is what I learned from you guys and my own research.
In order to delete rows from database which is populating FragmentStatePagerAdapter or ArrayAdapter you have to be able to link the correct row with what is being displayed in the adapter. Otherwise the rows won't delete or will be inconsistent or incorrect. The CursorAdapter will automatically handle the watching for changes and selecting the ID for you. If you can use CursorAdapter or a direct onItemClickListener and get the id directly from the AdapterView with getSelectedId() or just long ID, then that is a good way to get the row ID. However, if you are getting the id indirectly by other means then you have to handle the associations...
1.You should not use the adapter position, spinner position or even spinner id to select the row. These are useful only to determine which item/fragment you want. Only the ID of the OnClickListener on the item itself will consistently give the correct row.
2.The database rows behave as such - the integer primary key will auto increment even if AUTOINCREMENT is not chosen, but the row ID's are stable once assigned. This means that each new row will have a higher ID than the last but if you delete rows in between, it will not change the IDs of the remaining rows. So the rows will skip and not be consecutive when there are edits and deletions. For this reason you must have a method to link the item to the ID permanently. There may be a better way to do this, however, I will edit the code above to show one way that I was able to do it for the FragmentStatePagerAdapter so the user can add and delete fragments dynamically.