1

I am trying to figure out how I execute my database operations efficiently and effectively. I have it so that when a user logs in, I use the Facebook API and grab details regarding the user's friends and place them into my database. This is done in a method called createFriend. However, if I login a second time, I get an error because every friend has a unique user id. Therefore, if I log in the second time, my code calls the createFriend method yet again and tries to insert the same friend that was already inserted the first time I logged in, and I get a unique column violation.

As a result, I don't know when I should call the createFriend method. I thought of 2 solutions to fixing my problem:

  1. I looked at this link to see if I could check each row and if that row exists, only then I do the insert. I found: SQLiteDatabase: Insert only if the value does not exist (not via raw SQL command). It just doesn't seem very efficient to check every single time I want to insert a friend, to see if the row exists, and if not, I don't insert, otherwise I do. I have to do this select statement for every friend the user has.

  2. The other method I was thinking of was this: I can call the Facebook API and check if the number of friends returned from the Facebook API is equal to the number of rows in my local database. If so, I skip calling createFriend all together. If not, I can just remove the entire database and reinsert all the friends again. However, this doesn't seem to work well because there is the chance that the Facebook API can keep returning the same number of friends as are in my database, BUT those friends returned could be different people than what the database has. Therefore, the database won't update because the number of friends is still the same, but I would want it to update because I would like to insert those potential new friends into the database.

Can anyone please point me in the right direction on where I can solve this problem? Thank you!

Community
  • 1
  • 1
user1871869
  • 3,317
  • 13
  • 56
  • 106
  • You do update also if friend found in local database or just insert? – Dhaval Patel Nov 22 '15 at 08:51
  • @DhavalPatel if friend is found in local DB, right now, I perform an insert but that gives me unique constraint violation. I would want it so that I don't even perform the insert because the row already exists in my database. I don't need to update anything in the database. I apologize, I will change the title. – user1871869 Nov 22 '15 at 08:54
  • Or May be you can try update the database first.. if update fails then do insert. – Dhaval Patel Nov 22 '15 at 08:56

2 Answers2

1

You can update on duplicate key.

Have a look at this for reference, SQLite UPSERT - ON DUPLICATE KEY UPDATE

Community
  • 1
  • 1
BrendanDodd
  • 237
  • 2
  • 10
0

It just doesn't seem very efficient to check every single time I want to insert a friend, to see if the row exists

You are jumping to conclusions.

Guesses are likely to be wrong; you do not know how fast a query is until you have actually measured it.

And when you have a UNIQUE constraint, the database searches for a matching row anyway, so there will not be any additional I/O. In any case, make your algorithm correct before thinking about optimizing it.

And if you want to ignore friends that already exist in the database, just use INSERT OR IGNORE.

CL.
  • 173,858
  • 17
  • 217
  • 259