为 Unity 设置数据库 (SQLite) [英] Setup Database (SQLite) for Unity
问题描述
我看了太多教程无法列出，它们都推荐相同的东西.但是，他们并没有帮助解决我的问题.
I have looked at too many tutorials to list and they all recommend the same thing. However, they have not helped to solve my problem.
我正在尝试在我的项目中包含一个 SQLite 数据库，并且在为 PC、MAC & 构建时Linux Standalone(在 Windows 机器上测试)，数据库按预期工作.在 Android 设备上进行测试时，出现以下错误.
I am trying to include in my project an SQLite DB, and when building for PC, MAC & Linux Standalone (testing on a Windows machine), the database works as expected. When testing on an Android device, I get the following errors.
E/Unity: ArgumentException: Invalid ConnectionString format for parameter "/storage/emulated/0/Android/data/com.tbltools.tbl_project/files/TBLDatabase.db"
at Mono.Data.Sqlite.SqliteConnection.ParseConnectionString (System.String connectionString) [0x00000] in <filename unknown>:0
at Mono.Data.Sqlite.SqliteConnection.Open () [0x00000] in <filename unknown>:0
at UIHandler+<RequestAllStudentNames>c__Iterator2.MoveNext () [0x00000] in <filename unknown>:0
at UnityEngine.SetupCoroutine.InvokeMoveNext (IEnumerator enumerator, IntPtr returnValueAddress) [0x00000] in <filename unknown>:0
我认为修改 connectionString 应该很简单，但这并没有解决我的问题.这是我目前所拥有的:
I thought that making an amendment to the connectionString should be simple enough, but that hasn't solved my problem. This is what I have so far:
if (Application.platform != RuntimePlatform.Android)
{
// The name of the db.
tblDatabase = "URI=file:" + Application.dataPath + "/TBLDatabase.db"; //returns the complete path to database file exist.
}
else
{
tblDatabase = Application.persistentDataPath + "/TBLDatabase.db";
if (!File.Exists(tblDatabase))
{
// if it doesn't ->
Debug.LogWarning("File "" + tblDatabase + "" does not exist. Attempting to create from "" + Application.dataPath + "!/assets/" + "TBLDatabase.db");
// open StreamingAssets directory and load the db ->
// #if UNITY_ANDROID
var loadDb = new WWW("jar:file://" + Application.dataPath + "!/assets/" + "TBLDatabase.db"); // 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(tblDatabase, loadDb.bytes);
}
}
//open db connection
var connection = new SqliteConnection(tblDatabase);
connection.Open();
var command = connection.CreateCommand();
我使用了 adb shell 并从我的 Android 设备中提取了数据库，一切都按预期进行(数据库确实存在并且它不是空的).
I have used adb shell and pulled the DB from my Android device and everything is as expected (the DB does exist and it isn't empty).
我相信我拥有所有相关的 dll 文件，但如果有人能给我一些指导，我将不胜感激.
I believe I have all the relevant dll files, but if anyone could give me some guidance I would appreciate it.
********************************************************编辑**********************************************
***************************************************EDIT**********************************************
此后，我根据给出的建议进行了以下更改.
I have since made the following alterations based on advice given.
我现在正在调用以下方法来启动我的连接并处理数据库请求StartCoroutine(RunDbCode(dbFileName, jsonStudentID, jsonIndiNames, jsonIndiStudentNumbers));
I am now calling the following method to start my connection and handle DB requestsStartCoroutine(RunDbCode(dbFileName, jsonStudentID, jsonIndiNames, jsonIndiStudentNumbers));
然后我有以下方法:
IEnumerator RunDbCode(string fileName, List jsonStudentID, List jsonIndiNames, List jsonIndiStudentNumbers)
{
//Where to copy the db to
string dbDestination = Path.Combine(Application.persistentDataPath, "data");
dbDestination = Path.Combine(dbDestination, fileName);
//Check if the File do not exist then copy it
if (!File.Exists(dbDestination))
{
//Where the db file is at
string dbStreamingAsset = Path.Combine(Application.streamingAssetsPath, fileName);
byte[] result;
//Read the File from streamingAssets. Use WWW for Android
if (dbStreamingAsset.Contains("://") || dbStreamingAsset.Contains(":///"))
{
WWW www = new WWW(dbStreamingAsset);
yield return www;
result = www.bytes;
}
else
{
result = File.ReadAllBytes(dbStreamingAsset);
}
Debug.Log("Loaded db file");
//Create Directory if it does not exist
if (!Directory.Exists(Path.GetDirectoryName(dbDestination)))
{
Directory.CreateDirectory(Path.GetDirectoryName(dbDestination));
}
//Copy the data to the persistentDataPath where the database API can freely access the file
File.WriteAllBytes(dbDestination, result);
Debug.Log("Copied db file");
}
//Now you can do the database operation
//open db connection
var connection = new SqliteConnection(dbDestination);
connection.Open();
var command = connection.CreateCommand();
// Drop the table if it already exists.
command.CommandText = "DROP TABLE IF EXISTS existing_individual;";
command.ExecuteNonQuery();
var sql = "CREATE TABLE existing_individual (studentID VARCHAR(23), fullName VARCHAR(50), studentNumber VARCHAR(20))";
command.CommandText = sql;
command.ExecuteNonQuery();
//Inserting the exisiting student names returned, into the SQLite DB
int count = 0;
foreach (var individuals in jsonStudentID)
{
//looping through the existing students registered for the individual quiz - below has been written to avoid SQL injection
sql = "INSERT INTO existing_individual (studentID, fullName, studentNumber) VALUES (@jsonStudentID, @jsonIndiNames, @jsonIndiStudentNumbers)";
command.Parameters.AddWithValue("@jsonStudentID", jsonStudentID[count]);
command.Parameters.AddWithValue("@jsonIndiNames", jsonIndiNames[count]);
command.Parameters.AddWithValue("@jsonIndiStudentNumbers", jsonIndiStudentNumbers[count]);
command.CommandText = sql;
command.ExecuteNonQuery();
count++;
}
//close the connection
command.Dispose();
command = null;
connection.Close();
connection = null;
}
但是，我仍然收到以下错误:
However, I am still getting the following errors:
06-08 15:26:56.498 16300-16315/? E/Unity: ArgumentException: Invalid ConnectionString format for parameter "/storage/emulated/0/Android/data/com.tbltools.tbl_project/files/data/TBLDatabase.db"
at Mono.Data.Sqlite.SqliteConnection.ParseConnectionString (System.String connectionString) [0x00000] in <filename unknown>:0
at Mono.Data.Sqlite.SqliteConnection.Open () [0x00000] in <filename unknown>:0
at UIHandler+<RunDbCode>c__Iterator3.MoveNext () [0x00000] in <filename unknown>:0
at UnityEngine.SetupCoroutine.InvokeMoveNext (IEnumerator enumerator, IntPtr returnValueAddress) [0x00000] in <filename unknown>:0
UnityEngine.MonoBehaviour:StartCoroutineManaged2(IEnumerator)
UnityEngine.MonoBehaviour:StartCoroutine(IEnumerator)
<RequestAllStudentNames>c__Iterator2:MoveNext()
UnityEngine.SetupCoroutine:InvokeMoveNext(IEnumerator, IntPtr)
(Filename: Line: -1)
06-08 15:26:56.502 16300-16315/? E/Unity: ArgumentException: Invalid ConnectionString format for parameter "URI"
at Mono.Data.Sqlite.SqliteConnection.ParseConnectionString (System.String connectionString) [0x00000] in <filename unknown>:0
at Mono.Data.Sqlite.SqliteConnection.Open () [0x00000] in <filename unknown>:0
at UIHandler.CreateIndiButton () [0x00000] in <filename unknown>:0
at UIHandler+<RequestAllStudentNames>c__Iterator2.MoveNext () [0x00000] in <filename unknown>:0
at UnityEngine.SetupCoroutine.InvokeMoveNext (IEnumerator enumerator, IntPtr returnValueAddress) [0x00000] in <filename unknown>:0
我还将我的数据库添加到StreamingAssets"文件夹中，如下图所示:
I have also added my database to the 'StreamingAssets' folder as shown in the image below:
下面还显示了我的插件文件夹的图像，其中包含我的 dll 文件.
Below also shows an image of my plugins folder that holds my dll files.
推荐答案
大多数关于这个主题的教程已经过时.
Most tutorials on this topic are outdated.
查看代码并发现一些问题，但我不知道这些是否是您收到此错误的原因.WWW 应该在协程函数中使用，以便您可以通过在 yield return null 中添加 yield return null 来让步或等待 loadDb.isDone 完成code>while 循环.您还可以生成 WWW 请求本身，这就是我将在回答中使用的方法.
Looked at the code and found few problems but I can't tell if those are the reason you are getting this error. WWW should be used in a coroutine function so that you can yield or wait for loadDb.isDone is finish by adding yield return null inside the while loop. You can also yield the WWW request itself and that's the method I will use in my answer.
此外，jar:file://" + Application.dataPath 是旧代码.为此使用 Application.streamingAssetsPath.此外，您不需要 "URI=file:" + Application.dataPath.只需使用 Application.persistentDataPath 即可.
Also, jar:file://" + Application.dataPath is an old code. Use Application.streamingAssetsPath for that. Furthermore, you don't need "URI=file:" + Application.dataPath. Just use Application.persistentDataPath for that.
我只会说明如何进行设置.
I will just put an instruction on how to do the setup.
设置管理代码部分:
1.转到您的 Unity 安装路径
1.Go to your Unity installation path
<UnityInstallationDirecory>EditorDataMonolibmono2.0
复制以下文件:
I18N.MidEast.dllI18N.Other.dllI18N.Rare.dllI18N.West.dllMono.Data.Sqlite.dllMono.Data.SqliteClient.dllSystem.Data.dll
到项目的
这将允许您从 Mono.Data.Sqlite 命名空间编译 API，而不会出现任何错误.
This will allow you to compile API from the Mono.Data.Sqlite namespace without any error.
设置 UNMANAGED 代码部分:
在这一步中，您将需要获取本机 Sqlite 库.您可以获取源代码，构建并使用它或使用已经预编译的 binray.
In this step, you will need to get the native Sqlite library. You can get the source code, build it and use it or use already pre-compiled binray.
1.获取 Windows
从 此处，并将其放在
Download the pre-compiled sqlite3.dll for Windows 64 bit from here, and put it in the <ProjectName>AssetsPluginsx86_64 path.
如果使用 Windows 32 位，则从 here 并将其放在
If using Windows 32 bit then get the sqlite3.dll version from here and put it in the <ProjectName>AssetsPluginsx86 path.
2.获取 Android
从Android ARM 处理器下载预编译的libsqlite3.so这里 并将其放入
Download the pre-compiled libsqlite3.so for Android ARM processor from
here and put it in the <ProjectName>AssetsPluginsAndroidlibsarmeabi-v7a path.
从 Android Intel x86 处理器 下载预编译的 libsqlite3.so此处并将其放入<代码><项目名称>AssetsPluginsAndroidlibsx86 路径.
Download the pre-compiled libsqlite3.so for Android Intel x86 processor from
here and put it in the <ProjectName>AssetsPluginsAndroidlibsx86 path.
这涵盖了 Android 设备上使用的大多数处理器.
This covers most processors used on Android devices.
3.获取 UWP
A.下载 WSA 文件夹，然后将 WSA 文件夹放在
A.Download the WSA folder then put the WSA folder in the <ProjectName>AssetsPlugins path. That folder contains the native part.
B.在
B.Create 2 files named "mcs.rsp" and "csc.rsp" in the <ProjectName>Assets path.
C.在 "mcs.rsp" 和 "csc.rsp" 文件中添加以下内容:
C.Add the following inside the "mcs.rsp" and "csc.rsp" files:
-r:I18N.MidEast.dll
-r:I18N.Other.dll
-r:I18N.Rare.dll
-r:I18N.West.dll
-r:Mono.Data.Sqlite.dll
-r:Mono.Data.SqliteClient.dll
-r:System.Data.dll
D.在为 UWP 构建时，您必须将托管的 dll 移动到项目的根文件夹.所以，移动I18N.MidEast.dll、I18N.Other.dll、I18N.Rare.dll、I18N.West.dll、Mono.Data.Sqlite.dll、Mono.Data.SqliteClient.dll、System.Data.dll 到
D.You will have to move the managed dlls to the root folder of the project when building for UWP. So, move I18N.MidEast.dll, I18N.Other.dll, I18N.Rare.dll, I18N.West.dll, Mono.Data.Sqlite.dll, Mono.Data.SqliteClient.dll, System.Data.dll to the <ProjectName> path not <ProjectName>AssetsPlugins path.
4.对于 iOS、Linux 和 Mac，您似乎无需为它们下载任何其他内容或执行此步骤.它们通常内置了本地预编译的 Sqlite 库.
4.For iOS, Linux and Mac, it looks like you don't have to download anything else for them or do this step. They usually have the native pre-compiled Sqlite libraries built-in.
在构建中包含数据库文件:
1.在您的
1.Create a folder in your <ProjectName>Assets folder and name it StreamingAssets. Spelling counts and it's case sensitive.
2.将数据库文件 (TBLDatabase.db) 放在此 StreamingAssets 文件夹中.
2.Put the database file (TBLDatabase.db) in this StreamingAssets folder.
构建项目后访问数据库文件
Sqlite 无法处理构建中 StreamingAssets 文件夹中的文件，因为这是一个只读路径.此外，Android 要求您使用 WWW API 而不是标准的 System.IO API 来读取 StreamingAssets 文件夹.您必须将 db 文件从 Application.streamingAssetsPath/filename.db 复制到 Application.persistentDataPath/filename.db.
Sqlite cannot work on files in the StreamingAssets folder in a build since that's a read-only path. Also, Android requires that you use the WWW API instead of the standard System.IO API to read from the StreamingAssets folder. You have to copy the db file from Application.streamingAssetsPath/filename.db to Application.persistentDataPath/filename.db.
在某些平台上，需要您在 Application.persistentDataPath 中创建一个文件夹并将数据保存到该文件夹中.总是这样做.下面示例代码中的文件夹是data"，因此将成为Application.persistentDataPath/data/filename.db.
On some platforms, it is required that you create a folder inside Application.persistentDataPath and save data to that folder instead. Always do that. The folder in the example code below is "data" so that will become Application.persistentDataPath/data/filename.db.
3.由于上面的语句，检查数据库文件是否存在于Application.persistentDataPath/data/filename.db.如果是，请使用 Application.persistentDataPath/data/filename.db 作为数据库操作的路径.如果没有，请从 #4 继续.
3.Because of the statement above, check if the database file exist in the
Application.persistentDataPath/data/filename.db. If it does, use Application.persistentDataPath/data/filename.db as a path for your database operation. If it doesn't, continue from #4.
4.从StreamingAssets文件夹中读取数据库文件并将其复制到Application.persistentDataPath
4.Read and copy the database file from the StreamingAssets folder to Application.persistentDataPath
在某些平台上，需要您在 Application.persistentDataPath 中创建一个文件夹并将数据保存到该文件夹中.总是这样做.下面例子中的文件夹是data".
On some platforms, it is required that you create a folder inside Application.persistentDataPath and save data to that folder instead. Always do that. The folder in the example below is "data".
检测这是否是 Android 并使用 WWW 从 Application.streamingAssetsPath/filename.db 读取文件.使用 File.ReadAllBytes 在 Android 以外的任何其他设备上读取它.在您的示例中，您为此使用了 Application.platform.在我的示例中，我将简单地检查路径是否包含 "://" 或 :/// 来执行此操作.
Detect if this is Android and use WWW read to the file from Application.streamingAssetsPath/filename.db. Use File.ReadAllBytes to read it on anything else other than Android. In your example, you used Application.platform for that. In my example, I will simply check if the path contains "://" or :/// to do that.
5.读取文件后，使用File.WriteAllBytes将刚刚读取的数据写入Application.persistentDataPath/data/filename.db代码>.现在，您可以使用此路径进行数据库操作.
5.Once you read the file, write the data you just read to Application.persistentDataPath/data/filename.db with File.WriteAllBytes. Now, you can use this path for your database operation.
6.Prefix "URI=file:" 到 Application.persistentDataPath/data/filename.db 路径，这是应该在使用 Sqlite API 的数据库操作中使用.
6.Prefix "URI=file:" to the Application.persistentDataPath/data/filename.db path and that's the path for that should be used in your database operation with the Sqlite API.
了解所有这些非常重要，以便在发生变化时进行修复，但我已经完成了下面的 #3 到 #6 步骤.
It's very important that you understand all these in order to fix it when something changes but I've already done step #3 to #6 below.
IEnumerator RunDbCode(string fileName)
{
//Where to copy the db to
string dbDestination = Path.Combine(Application.persistentDataPath, "data");
dbDestination = Path.Combine(dbDestination, fileName);
//Check if the File do not exist then copy it
if (!File.Exists(dbDestination))
{
//Where the db file is at
string dbStreamingAsset = Path.Combine(Application.streamingAssetsPath, fileName);
byte[] result;
//Read the File from streamingAssets. Use WWW for Android
if (dbStreamingAsset.Contains("://") || dbStreamingAsset.Contains(":///"))
{
WWW www = new WWW(dbStreamingAsset);
yield return www;
result = www.bytes;
}
else
{
result = File.ReadAllBytes(dbStreamingAsset);
}
Debug.Log("Loaded db file");
//Create Directory if it does not exist
if (!Directory.Exists(Path.GetDirectoryName(dbDestination)))
{
Directory.CreateDirectory(Path.GetDirectoryName(dbDestination));
}
//Copy the data to the persistentDataPath where the database API can freely access the file
File.WriteAllBytes(dbDestination, result);
Debug.Log("Copied db file");
}
try
{
//Tell the db final location for debugging
Debug.Log("DB Path: " + dbDestination.Replace("/", "\"));
//Add "URI=file:" to the front of the url beore using it with the Sqlite API
dbDestination = "URI=file:" + dbDestination;
//Now you can do the database operation below
//open db connection
var connection = new SqliteConnection(dbDestination);
connection.Open();
var command = connection.CreateCommand();
Debug.Log("Success!");
}
catch (Exception e)
{
Debug.Log("Failed: " + e.Message);
}
}
用法:
string dbFileName = "TBLDatabase.db";
void Start()
{
StartCoroutine(RunDbCode(dbFileName));
}
这篇关于为 Unity 设置数据库 (SQLite)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
