Prevent data duplication in the SQLite Database

Question

I'm developing an android app that uses SQLite as the local database. The app syncs data obtained from a web api and stores it in the local database. All the model classes have their ID property set as Primary key and Auto incremented so I can manually enter data without having to specify the ID. The issue is when I insert the data from the API into the SQlite, the ID of the object is ignored and Sqlite gives the object a new ID. I want the data stored with the same ID as the object being stored.

The web api returns the object lists that have their ID type long however the SQLite objects have their primary keys as int. Is this the reason why the ID values is not getting stored because their data types don't match? I can't change the datatype in my SQL database where the data comes from as there are hundreds of tables in it. Is there a way around it?

This is the Code to inserts or updates data in my local DB:

    }
    public async Task<string> insertUpdateVideoData(Video_Struct data)
    {
        try
        {
            var db = new SQLiteAsyncConnection(dbPath);
            var m = GetVideos();
            if (await db.FindAsync<Video_Struct>(f => f.VideoID == data.VideoID) != null)
            {

                await db.UpdateAsync(data);
            }
            else
            {
                if (await db.InsertAsync(data) != 0)
                {

                    await db.UpdateAsync(data);
                }

            }
            return "Single data file inserted or updated";
        }
        catch (SQLiteException ex)
        {
            return ex.Message;
        }
    }

This is the code to get data objects from the API:

    public async Task<List<Video_Struct>> GetVideoData()
    {
        List<Video_Struct> vids = new List<Video_Struct>();
        WebClient mClient = new WebClient();
        var output = await mClient.DownloadDataTaskAsync(new Uri(GlobalVariables.host + "/api/media/getmedia"));
        var json = Encoding.UTF8.GetString(output);
        vids = JsonConvert.DeserializeObject<List<Video_Struct>>(json);
        return vids;
    }

Based on [this answer](http://stackoverflow.com/questions/3388098/sqlite-data-types), SQLite data types are per value, not per column, so whatever type you write should be what you get back. — Tim Biegeleisen, Apr 18 '16 at 16:46
If the ID values match then why does the inserted data get a value that is different from the one I'm getting from the API object? — Ahmed Mujtaba, Apr 18 '16 at 16:51

score 0 · Answer 1 · edited May 23 '17 at 12:24

0

If your local DB is a cache for web data and external DB gives you unique IDs, don't use auto increment in scheme, just re-use external IDs.

Actually, you can have a complex (compound) primary key, it depends on data unique properties.

If you do not work with your data as structured set you can try gson+SharedPreferences. Just don't forget to override equals and hashcode for your data models.

Datatype in not an issue, because sqlite uses INTEGER type.

edited May 23 '17 at 12:24

Community

1
1

answered Apr 18 '16 at 16:52

Maxim G

1,479
1
15
23

I could do that but it prevents me from entering data in my local DB manually without specifying their ID. – Ahmed Mujtaba Apr 18 '16 at 16:55
What for do you manually enter data? If it's for tests you can query the highest id, then increment it before inserting. – Maxim G Apr 18 '16 at 16:59
Like when I have to insert data objects from within the app, I can specify the values of all other properties except the ID. Yeah this could be one solution – Ahmed Mujtaba Apr 18 '16 at 17:02

Prevent data duplication in the SQLite Database

1 Answers1