I have a database full of tweets. When the user clicks refresh, I want to grab tweets newer than an ID (which I know how to do). After I grab the tweets, I want to insert them before ID 0, so tweet with ID 0 is no longer the first tweet, and instead replaced with the newer tweets.
I have searched and I have no idea how to do this.
Here is the code I am using to add the user's timeline on first launch:
public void addTimemline(Timeline timeline){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(COL_TEXT, timeline.getText());
values.put(COL_TWEET_ID, timeline.getTweetId());
values.put(COL_SCREEN_NAME, timeline.getTwitterScreenName());
values.put(COL_REAL_NAME, timeline.getRealName());
values.put(COL_FAVORITE_COUNT, timeline.getFavoriteCount());
values.put(COL_RETWEET_COUNT, timeline.getRetweetedCount());
values.put(COL_HASHTAG_ENTITIES, timeline.getHashtagEntities());
values.put(COL_USER_MENTION_ENTITIES, timeline.getUserMentionEntities());
values.put(COL_URL_ENTITIES, timeline.getUrlEntities());
values.put(COL_MEDIA_ENTITIES, timeline.getMediaEntities());
db.insertOrThrow(TABLE_TWEETS, null, values);
db.close();
}
Create statement:
private static final String TABLE_TWEETS_CREATE = "create table "+TABLE_TWEETS+" ("
+ Tweets.COL_ROW_ID + " integer primary key autoincrement not null, "
+ Tweets.COL_TEXT + " text not null, "
+ Tweets.COL_TWEET_ID + " text,"
+ Tweets.COL_SCREEN_NAME + " text,"
+ Tweets.COL_REAL_NAME + " text,"
+ Tweets.COL_FAVORITE_COUNT + " text,"
+ Tweets.COL_RETWEET_COUNT + " text,"
+ Tweets.COL_HASHTAG_ENTITIES + " text,"
+ Tweets.COL_USER_MENTION_ENTITIES + " text,"
+ Tweets.COL_URL_ENTITIES + " text,"
+ Tweets.COL_MEDIA_ENTITIES + " text);";
As you have it set up, you cannot do that. You are telling the DB to autoincrement the key, so it will give you the next available slot that has not been used yet. Meaning if you have four records and delete the last one, you will still get the fifth slot the next time you insert a record. It will not allow you tot set the id yourself or insert a record before the last one inserted.
You could change to manually managing the IDs but that is much more work and prone to errors, and I would strenuously recommend you avoid doing so.
You should really investigate the ORDER BY keyword for SQLite. That will sort the cursor into the order you define in your query rather than relying on the id order of the records. If you put a time stamp of some kind in your data structure you could then sort in reverse order on that when you pull the cursor.
You can find full documentation of the SQLite ORDER BY keyword here.
You have not said if you are using raw SQL or any of the android SQLite functions, so you may also want to look at the android sqlite insert function here.