Update android game with new levels without replacing database

Question

So I've published a game for android users containing 400 levels and 2k users have completed or are playing it since. Now I'm going to update the game with 600 levels, but I don't want current users to lose their info in database. I just want to add more levels to the database if it exists in their phone.

I'm using this code not to let database get replaced if it exists.

public DataService()
{

        const string databaseName = Constants.DbName;

#if UNITY_EDITOR
        var dbPath = string.Format(@"Assets/StreamingAssets/{0}", databaseName);
#else
        // check if file exists in Application.persistentDataPath
        var filepath = string.Format("{0}/{1}", Application.persistentDataPath, databaseName);

        if (!File.Exists(filepath))
        {
            Debug.Log("Database not in Persistent path");
            // if it doesn't ->
            // open StreamingAssets directory and load the db ->

#if UNITY_ANDROID 
            var loadDb = new WWW("jar:file://" + Application.dataPath + "!/assets/" + databaseName);  // this is the path to your StreamingAssets in android
            while (!loadDb.isDone) { }  // CAREFUL here, for safety reasons you shouldn't let this while loop unattended, place a timer and error check
            // then save to Application.persistentDataPath
            File.WriteAllBytes(filepath, loadDb.bytes);
#elif UNITY_IOS
                 var loadDb = Application.dataPath + "/Raw/" + databaseName;  // this is the path to your StreamingAssets in iOS
                // then save to Application.persistentDataPath
                File.Copy(loadDb, filepath);
#elif UNITY_WP8
                var loadDb = Application.dataPath + "/StreamingAssets/" + databaseName;  // this is the path to your StreamingAssets in iOS
                // then save to Application.persistentDataPath
                File.Copy(loadDb, filepath);

#elif UNITY_WINRT
        var loadDb = Application.dataPath + "/StreamingAssets/" + databaseName;  // this is the path to your StreamingAssets in iOS
        // then save to Application.persistentDataPath
        File.Copy(loadDb, filepath);
#else
    var loadDb = Application.dataPath + "/StreamingAssets/" + databaseName;  // this is the path to your StreamingAssets in iOS
    // then save to Application.persistentDataPath
    File.Copy(loadDb, filepath);

#endif

            Debug.Log("Database written");
        }

        var dbPath = filepath;
#endif
        _connection = new SQLiteConnection(dbPath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create);
       // Debug.Log("Final PATH: " + dbPath);     

}

Also I've created a filed in database keeping database version to check what version the user has (like the version with 400 levels, the one with 600 levels or maybe more).

What I don't know is what is the best solution to add more records to an existing database. Has anyone got a solution?

Answer 1

The nice thing about SQL databases is that you can write data to them.

So instead of replacing the whole database, create an SQL script file with all the INSERT, UPDATE and ALTER TABLE statements you need to bring an existing database to the new version, add that script file to your package, and execute when the game is run for the first time after updating.

Answer 2

1) to insert Add in dataservice.cs

public Person Add_pwd()// gestion record tableau
    {
        var p = new Person
        {
            Util = control_text2.data1[0],
            Login = control_text2.data2[0],
            Pwd = control_text2.data3[0],
            Jour = date + " " + heure
         };
            _connection.Insert(p); //Update...
        return p;
    }

2)create table YourTableName in DB browser for sqlite 3)create YourTableName.cs

sing SQLite4Unity3d;
using System.Collections;
using UnityEngine;

public class Person  {

    [PrimaryKey, AutoIncrement]
    public int Id       { get; set; }
    public string Util  { get; set; }
    public string Login { get; set; }
    public string Pwd   { get; set; }
    public string Jour  { get; set; }

    public override string ToString ()
    { 
        return string.Format ( "[ {0}\t{1}\t{2}\t{3} ]", Util, Login, Pwd, Jour);
    }
}