dimanche 2 août 2015

Most efficient "ignore if exists" in SQLite?

Once per day, I am hitting an API to get the 50 most recent posts from a forum. I'm storing that information in a database, one row per post, storing Author, Title, and PostID.

If there are less than 50 posts in a day, then there'll be duplicate data, the same posts appearing twice or more. I want to avoid this. I want to say "if PostID already exists in the database, just skip inserting that record."

I can easily do this by first fetching a list of all PostIDs and saving it as an array in my program, then making sure the PostID isn't in that array before inserting; but that seems dumb and sloppy. Surely there must be a way to do it in the database itself.

I've read up a little, and INSERT OR REPLACE works, but also seems like it's not the 'correct' solution. It would also pose a problem if I wanted to alter a field in the row -- hitting the API again would reset all fields to initial values.

What's the smart way to do this?

Aucun commentaire:

Enregistrer un commentaire