android - 通过 Unity 在 android 中使用填充的 SQLite 数据库

Question

我将在使用 Unity 创建的 Android 游戏中使用预填充的 SQLite 数据库。

自 the simple way不适用于 Android(它在 Windows 上完美运行)，我关注了 this tutorial在 Android 应用程序中使用我的数据库。

public void OpenDB(string p) //p is the database name

{
    // check if file exists in Application.persistentDataPath

    string filepath = Application.persistentDataPath + "/" + p;

    if(!File.Exists(filepath))

    {

        // if it doesn't ->

        // open StreamingAssets directory and load the db -> 

        WWW loadDB = new WWW("jar:file://" + Application.dataPath + "!/assets/" + p);  // 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);

    }

    //open db connection

    connection = "URI=file:" + filepath;

    dbcon = new SqliteConnection(connection);

    dbcon.Open();

}

当我运行这段代码时，出现以下错误:

SqliteSyntaxException: file is encrypted or is not a database

这是完整的错误:

SqliteSyntaxException: file is encrypted or is not a database Mono.Data.SqliteClient.SqliteCommand.GetNextStatement (IntPtr pzStart, System.IntPtr& pzTail, System.IntPtr& pStmt) Mono.Data.SqliteClient.SqliteCommand.ExecuteReader (CommandBehavior behavior, Boolean want_results, System.Int32& rows_affected) Mono.Data.SqliteClient.SqliteCommand.ExecuteReader (CommandBehavior behavior) Mono.Data.SqliteClient.SqliteCommand.ExecuteDbDataReader (CommandBehavior behavior) System.Data.Common.DbCommand.ExecuteReader () System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader () dbAccess.SingleSelectWhere (System.String tableName, System.String itemToSelect, System.String wCol, System.String wPar, System.String wValue) (at Assets/dbAccess.cs:152)

我已经从该帖子下载了示例，但遇到了同样的错误。

我怀疑这一行:

File.WriteAllBytes(filepath, loadDB.bytes); 

我的想法是出于某种原因，它不能将数据库数据写入文件。

有人知道怎么解决吗？

Answer 1

感谢 this blog，我解决了这个问题.

由于 if 语句会根据应用程序运行的平台执行不同的行为，此代码在每个平台上都能完美运行。

这是执行重要部分的 DataService.cs(或者我最好说整个部分)

using SQLite4Unity3d;
using UnityEngine;
#if !UNITY_EDITOR
using System.Collections;
using System.IO;
#endif
using System.Collections.Generic;

public class DataService  {

    private SQLiteConnection _connection;

    public DataService(string DatabaseName){

#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);     

    }

    public void CreateDB(){
        _connection.DropTable<Person> ();
        _connection.CreateTable<Person> ();

        _connection.InsertAll (new[]{
            new Person{
                Id = 1,
                Name = "Tom",
                Surname = "Perez",
                Age = 56
            },
            new Person{
                Id = 2,
                Name = "Fred",
                Surname = "Arthurson",
                Age = 16
            },
            new Person{
                Id = 3,
                Name = "John",
                Surname = "Doe",
                Age = 25
            },
            new Person{
                Id = 4,
                Name = "Roberto",
                Surname = "Huertas",
                Age = 37
            }
        });
    }

    public IEnumerable<Person> GetPersons(){
        return _connection.Table<Person>();
    }

    public IEnumerable<Person> GetPersonsNamedRoberto(){
        return _connection.Table<Person>().Where(x => x.Name == "Roberto");
    }

    public Person GetJohnny(){
        return _connection.Table<Person>().Where(x => x.Name == "Johnny").FirstOrDefault();
    }

    public Person CreatePerson(){
        var p = new Person{
                Name = "Johnny",
                Surname = "Mnemonic",
                Age = 21
        };
        _connection.Insert (p);
        return p;
    }
}

它后面跟着另外两个脚本来创建或使用现有数据库。

ExistingDBScript.cs

using UnityEngine;
using System.Collections.Generic;
using UnityEngine.UI;

public class ExistingDBScript : MonoBehaviour {

    public Text DebugText;

    // Use this for initialization
    void Start () {
        var ds = new DataService ("existing.db");
        //ds.CreateDB ();
        var people = ds.GetPersons ();
        ToConsole (people);

        people = ds.GetPersonsNamedRoberto ();
        ToConsole("Searching for Roberto ...");
        ToConsole (people);

        ds.CreatePerson ();
        ToConsole("New person has been created");
        var p = ds.GetJohnny ();
        ToConsole(p.ToString());

    }

    private void ToConsole(IEnumerable<Person> people){
        foreach (var person in people) {
            ToConsole(person.ToString());
        }
    }

    private void ToConsole(string msg){
        DebugText.text += System.Environment.NewLine + msg;
        Debug.Log (msg);
    }

}

CreateDBScript.cs

using UnityEngine;
using System.Collections.Generic;
using UnityEngine.UI;

public class CreateDBScript : MonoBehaviour {

    public Text DebugText;

    // Use this for initialization
    void Start () {
        StartSync();
    }

    private void StartSync()
    {
        var ds = new DataService("tempDatabase.db");
        ds.CreateDB();

        var people = ds.GetPersons ();
        ToConsole (people);
        people = ds.GetPersonsNamedRoberto ();
        ToConsole("Searching for Roberto ...");
        ToConsole (people); 
    }

    private void ToConsole(IEnumerable<Person> people){
        foreach (var person in people) {
            ToConsole(person.ToString());
        }
    }

    private void ToConsole(string msg){
        DebugText.text += System.Environment.NewLine + msg;
        Debug.Log (msg);
    }
}

和person脚本，演示数据库中的person表

using SQLite4Unity3d;

public class Person  {

    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }
    public int Age { get; set; }

    public override string ToString ()
    {
        return string.Format ("[Person: Id={0}, Name={1},  Surname={2}, Age={3}]", Id, Name, Surname, Age);
    }
}

您还需要将插件和Sqlite.cs 添加到您的项目中，您可以在git repository 中找到它。

它帮助我克服了这个问题，希望它也能帮助其他人。