Unity - Database on Android not loaded on first run

Question

I am trying to get data from a local Database using UnityWebRequest. On Windows it's working without any problem. On Android the first time it runs can't find the database, but if you go back to previous scene and load again this scene then the database is created and works as it should.

I placed Database in StreamingAssets folder (I tried also directly in Assets/ but the result is the same).

Here is what I have so far:

private void Start()
{
    //Check platform.
    if (Application.platform == RuntimePlatform.Android)
    {
       platformPath = Application.persistentDataPath;
       databasePath = Path.Combine("URI=file:" + platformPath + dbName);
    }
    else
    {                
       platformPath = Application.dataPath;
       databasePath = Path.Combine("URI=file:" + platformPath + "/StreamingAssets" + dbName);
    }   

  if (!File.Exists(Path.Combine(platformPath + dbName)))
  {
     Debug.Log("File doesn't exists!");           
     StartCoroutine(GetText());
  }

  try
  {
      //Communicate with database.
        IDbConnection dbConnection = new SqliteConnection(databasePath);
        dbConnection.Open();

      //Read and print data in DB table.
         IDbCommand cmnd_read = dbConnection.CreateCommand();
         IDataReader reader;

         string query = "SELECT * FROM BonusWords";
         cmnd_read.CommandText = query;
         reader = cmnd_read.ExecuteReader();

         while (reader.Read())         
            BonusWordsList.Add(reader[0].ToString().ToUpper());


         //Close DB connection.
         dbConnection.Close();
  }
  catch(Exception ex)
  {
      Debug.Log("EXCEPTION: " + ex);
  }
}


private IEnumerator GetText()
{
  string path = Path.Combine("jar:file://" + Application.dataPath + "!/assets" + dbName);

  UnityWebRequest www = UnityWebRequest.Get(path);
  yield return www.SendWebRequest();

  while (!www.isDone) { }
  try
  {
    // Retrieve results as binary data.
    byte[] data = www.downloadHandler.data;

    // Writes the DB in the persistent memory.
    File.WriteAllBytes(Path.Combine(Application.persistentDataPath + dbName), data);
  }
  catch (Exception ex)
  {
    Debug.Log(ex.Message);
  }
}

So the problem is that on Android, on first run the database cannot be found so it creates it after the level is loaded. How should I change it to always locate and load database before loading the rest of the scene stuff?

Answer 1

Solved the issue by assigning this script to a GameObject in the scene so it will only check the first time (at Start()) and get the appropriate platform path and if is Android then create the Database if not found.

public class DBConnection : MonoBehaviour
{
    private IDbConnection dbConnection;
    private IDbCommand dbCommand;
    private IDataReader reader;

    private string platformPath;
    private readonly string databaseFolder = "/StreamingAssets";
    private readonly string dbName = "/BonusWords.db";
    private string connectionString = "";


    private void Start()
    {
        connectionString = GetDatabasePath();
    }

    public string CheckIfWordExistsInDB(string word)
    {    
        try
        {
            OpenDatabaseConnection(connectionString);
        }
        catch(Exception ex)
        {
            Debug.Log("Connection Exception: " + ex);
            return null;
        }

        string sqlQuery = "SELECT * FROM BonusWords WHERE Word='" + word + "' LIMIT 1";

        try
        {
            if (ExecuteQuery(sqlQuery, word) != null)
            {
                /// Close DB connection.
                CloseDatabaseConnection(reader, dbCommand, dbConnection);
                return word;
            }
            else
            {
                /// Close DB connection.
                CloseDatabaseConnection(reader, dbCommand, dbConnection);
                return null;
            }
        }
        catch(Exception ex)
        {
            Debug.Log("Exception: " + ex);
            return null;
        }             
    }

    private void OpenDatabaseConnection(string connectionString)
    {
        dbConnection = new SqliteConnection(connectionString);
        dbConnection.Open();
        Debug.Log("Connected to database: #" + dbConnection.ConnectionString);
    }

    private IDataReader ExecuteQuery(string sqlQuery, string word)
    {
        dbCommand = dbConnection.CreateCommand();
        dbCommand.CommandText = sqlQuery;

        reader = dbCommand.ExecuteReader();

        if (reader[0].ToString() == word)           
            return reader;

        return null;
    }

    /// Close all opened connections.
    private void CloseDatabaseConnection(IDataReader reader, IDbCommand dbCommand, IDbConnection dbConnection)
    {
        reader.Close(); 
        reader = null;
        dbCommand.Dispose();
        dbCommand = null;
        dbConnection.Close();
        dbConnection = null;
        Debug.Log("Closed all opened connections.");
    }

    private string GetDatabasePath()
    {
        string databasePath;

        /// Set path according to platform.
        if (Application.platform == RuntimePlatform.Android)
        {
            platformPath = Application.persistentDataPath;

            /// Looks for the DB in the persistent memory
            /// on Android.
            if (!File.Exists(Path.Combine(platformPath + dbName)))
            {
                Debug.Log("File doesn't exists!");
                StartCoroutine(GetDatabaseForAndroid());
            }

            return databasePath = Path.Combine("URI=file:" + platformPath + dbName);
        }
        else
        {
            platformPath = Application.dataPath;
            return databasePath = Path.Combine("URI=file:" + platformPath + databaseFolder + dbName);
        }
    }

    private IEnumerator GetDatabaseForAndroid()
    {
        string path = Path.Combine("jar:file://" + Application.dataPath + "!/assets" + dbName);

        UnityWebRequest unityWebRequest = UnityWebRequest.Get(path);

        yield return unityWebRequest.SendWebRequest();

        while (!unityWebRequest.isDone) { }

        try
        {
            /// Retrieve results as binary data.
            byte[] data = unityWebRequest.downloadHandler.data;

            /// Writes the DB in the persistent memory.
            File.WriteAllBytes(Path.Combine(Application.persistentDataPath + dbName), data);
        }
        catch (Exception ex)
        {
            Debug.Log(ex.Message);
        }
    }      
}