0

I'm working on the structure for a database, in Android Studio. I am not at the stages of testing, yet, but understanding smaller details in how it works would allow me to write a better structure sooner rather than later. Unfortunately, there is one small element that am unsure of that has proved rather difficult to look for, online.

I create an SQLiteDatabase, and use it to store values that I represent in a class called Task. When I enter each Task into the database, I also retrieve the Id to store into my local container newTask, with newTask.SetId(mTaskDataSource.Create(newTask)). I ultimately use the SQLiteDatabase to store my tasks across sessions, and use a RecyclerView to display the list in an activity. This means that I have to keep track of the elements position in both the SQLiteDatabase and the RecyclerView.

If I delete an element in the database, will the consecutive element Ids "cascade down", or will they stay at their original values? If an example is needed, consider that my database stores elements, and displays the Ids. If I start with {0, 1, 2, 3}, and remove element 2, will my database balance its Ids back to {0, 1, 2}, or will I have {0, 1, 3}?

As I am working off the assumption that it balances back ({0, 1, 2}), I have realised that I potentially set myself up for a lot of extra/redundant work, if my assumptions are wrong. If it does not balance in this way, is there a reasonably cheap way to do so?

I am asking if ID numbers will rebalance themselves. This is not the same as asking what the ID number is actually for, nor is the duplicate question of any valid use to my question. I am asking specifically in regards to an SQLiteDatabase, not a generic Adapter.

Gnemlock
  • 325
  • 6
  • 22
  • @CL., I have added a clarification to my question. Very clearly different to "what is an Id for". – Gnemlock Oct 05 '16 at 07:08
  • I will not give an answer ("no") that is unhelpful because of the [XY problem](http://xyproblem.info/). Your *actual* problem is "to keep track of the elements position in both the `SQLiteDatabase` and the `RecyclerView`", and the linked answer shows the correct way to do this. – CL. Oct 05 '16 at 10:29
  • @CL., I think we have some difficulty understanding each other. Answering the question instead of focusing on the context helps significantly. In regards to the question you link, most answers simply tell me *what* an ID is, and *why* we use it. I already know this, as it is core database mechanics. This does not help me at all in understanding how I should offset the IDs for the dropped rows. – Gnemlock Oct 05 '16 at 11:21
  • One answer does stand out as *partially* helpful; However, since it only talks about the `Adapter` class, of which `SQLiteDatabase` is only a relative at the `object` level, I fail to see how this would work without me completely removing the `SQLiteDatabase` in favor of another `Adapter`. In essence, your trying to say "this is a duplicate because the other answer *should* help you". I was under the impression you could not mark a question as a duplicate based entirely on the answers, because ultimately, it is not helpful. – Gnemlock Oct 05 '16 at 11:26
  • a/ no, sqlite does not do that, for obvious reasons, such as allowing you to rely of the values of the id. b/ what are you actually trying to do? if you are just trying to sort the elements by order of insertion, you can use an autoincrement index to explicitly prevent reusing deleted IDs. – njzk2 Oct 05 '16 at 12:55
  • @njzk2, I am trying to keep track of the same ID in the `RecyclerView` and the `SQLiteDatabase`, as I explicitly state in my question. – Gnemlock Oct 05 '16 at 13:47
  • i.e. if I delete {2}, the task of ID 3 becomes the task of ID 2 in the `RecyclerView`, so if it does not do so in the `SQLiteDatabase`, I can no longer refer to the database via adapter position. – Gnemlock Oct 05 '16 at 13:49
  • @Timelord64 and that's exactly why you should be happy that the id in the db does not change. You can rely on it. The id you have in your recycler view, coming from the db, will stay valid. – njzk2 Oct 05 '16 at 14:01
  • @Timelord64 "adapter position" oooooh. so that's what is happening. You are confusing ID and position. Don't. use the id from the db as an id in your recycler view. Read the doc for `getItemId`. It says "Return the stable ID for the item at position.". The keyword here is `stable`. You need to be able to identify an element using that id, even if you remove elements before that one. The id provided by the db, because it does not change, is *stable* (by definition) – njzk2 Oct 05 '16 at 14:02
  • @njzk2, I use ID in relation to RecyclerView in comments more out of ease to type + character limit. Nevertheless, you have given me some idea for solution. The tutorials I have previously followed regarding `RecyclerView` instruct you to build it with `getItemId` returning `null`, so I implemented a simple alternative that only returns a *non*stable ID. Thank-you for actually taking the time to read my problem and not pigeon holeing me, SO needs more users like you. – Gnemlock Oct 05 '16 at 14:23
  • If this question does get reopened, I would accept the current answer, and probably upvote it after some grammar cleanup. I still find it incredibly *un*useful to be pointed towards the other question, and as a result, have voted on the other question/answers accordingly. – Gnemlock Oct 05 '16 at 14:25

2 Answers2

3

The database does not change its ID, or any other data in other rows, when deleting one row. The ID is just for easier join queries and other things, so it must not be changed.

If you want to have a specific order, you do not need to use row ID. Data will be returned in the order you added them, so you can use their order index (row index in database) as the recycler view index.

Gnemlock
  • 325
  • 6
  • 22
Amir Ziarati
  • 14,248
  • 11
  • 47
  • 52
  • 1
    look, DB wont change anything for you, are you using an ORM ? are you using any kind of data binding ? if so, it may change your data (though most form of ORM doesnt change anything until you call .save()). put your code to show where you delete the row and how you add or update data. – Amir Ziarati Oct 05 '16 at 06:26
  • 1
    if you want to have a specific order you dont need to use row ID data will be returned in the order you added them so you can use their order index (row index in DB) as recyclerView index. – Amir Ziarati Oct 05 '16 at 06:28
  • To clarify my comment, "surely *the* database does not change *its* IDs" does not say "yes it does" or "no it does not"; it says "it *should*". I am looking for a confidant answer, not another asumption. I do provide my code for adding rows, and as previously stated, most of my database class structure will differ depending on the answer, *including* how I delete and update rows. – Gnemlock Oct 05 '16 at 07:12
  • 1
    tnx for your edit @Timelord64. wish i cud be of any help ;) – Amir Ziarati Oct 09 '16 at 05:08
-1

If your ID is the primary key with autoincrement modifed , the id will only increment by one based on the current max index . The Db will not reuse the ID you have used before .

Lion
  • 81
  • 5
  • thats great, but that has nothing to do with what I asked. Regardless, my question has already been answered. – Gnemlock Oct 11 '16 at 00:17