It will be practical guidance on usage of SQLite in Unity3d. I will tell that this such, for what could be demand how to implement and I will recommend tools for convenient operation. Article are orient on initial and average levels. In the end of you zhdyot a small bonus. All interest welcome under kat.


What is it?

SQLite are superficial 


What for?

I will mark that in our small department almost all have different OS and as for us interchangeability are important, we tr to select krossplatformenny decisions.

For us SQLite were a good variant for storage of the exterior information — resources, configuration adjustments, localization. We refus exterior xml-like files at once, because of problems with transfer on different devices, razdutost and slownesses. For your project it are possible to look at expediency of usage of SQLite  


How?

For creation and editing of a DB our choice falling on t are a plug-in for Firefox, it free, convenient and krossplatformenny. The only thing that were not pleasant to me — it were necessary to add SQL request about creation of the table for adding of foreign keys, and also impossibility of editing of records in ma VIEW from several tables. And in remaining all are very evident. It are possible even to experiment with SQL requests to your DB. On an output you received the one file which extension could specify (.db.sqlite.bytes and so forth), but Unity understood only «.bytes».


Eshchyo one important point. Layout in a DB of binarnik as pictures, music, video are the poor practice. Roughly sp, all that than 100 kb weighed more should be in a local folder, and you registered in a DB only paths. Otherwise speed of reading of binarnik from basis became  loading locally are more its.


Coming to allocate now time it all in our Unity the project. For usage of SQLite we swung libraries (for Win, iOS and MacOS) and  (for Android). We allocated libraries here — ​​Assets/Plugins/sqlite.dll​​ and ​​Assets/Plugins/Android/sqlite.so​​. If daddies ​​Plugins​​ no sozdayom are more its. The creat DB (Nazovyom are more its for example ​​db.bytes​​) Kladyom in the daddy ​​Assets/StreamingAssets​​ (Sozdayom if missed). As a result after deploya, our basis will appear here:


Win and Mac OS:

Application.dataPath + "/StreamingAssets/db.bytes"

iOS:

Application.dataPath + "/Raw/db.bytes"

and here on Android it will be pack into apk a file of application:

"jar:file://" + Application.dataPath + "!/assets/db.bytes".

You will need to use WWW a class to load basis, and then to copy in a folder ​​Application.persistentDataPath + "/"​​.


But these directories possessed only the rights for reading (except Android'a) if you need to write down something in basis, are more its it are necessary to move here:


Win and Mac OS:

Application.dataPath + "/db.bytes"

iOS:

string path = Application.dataPath.Substring(0, Application.dataPath.LastIndexOf("/"));
path = path.Substring(0, path.LastIndexOf("/")) + "/Documents/db.bytes";

or so

string path = Application.dataPath.Substring(0, Application.dataPath.length - 5);
path = path.Substring(0, path.LastIndexOf("/")) + "/Documents/db.bytes";

or so

string path = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal)  + "/db.bytes";

About Android it are wr above.


Then we connected a packet of System.IO and we written

File.Copy(openPath, savePath);

for Android'a accordingly

WWW www = new WWW(openPath);
while(!www .isDone) {} // тут очень внимательно, используйте корутины
File.WriteAllBytes(savePath, www.bytes);

Well and binarnika of resources: models, video, music and t. d. Kladyom in the daddy ​​Assets/Resources/​​. After bild they was pack in binarnik ​​resources.assets​​. And in application we loaded them — ​ ​. Well for example, we suppos a sound in the daddy ​​Assets/Resources/Sounds/sound1.mp3​​, in basis at us such record​​Sounds/sound1.mp3​​, and in application it will look so

AudioClip a = Resources.Load("Sounds/sound1.mp3") as AudioClip;

Now time coming to consider the data from our database. I does not like to write SQL requests and parsit the crude data to objects, therefore specially for SQLite we f opensource the project ​  it are library objective - relational display. It are technology who connected a database with concepts of object-oriented programming. There are a support of Linq, for example, it are possible to make sampling:


public class Favorite 
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
public int UserId { get; set; }
public string Url { get; set; }
}

public Favorite[] GetFavorites(SQLiteConnection c, int id)
{
var q = from f in c.Table<Favorite>()
where f.UserId == id
select f;
return q.ToArray();
}

or to edit:


public void AddFavorite(SQLiteConnection c, string url, int id)
{
var fav = new Favorite()
{
UserId = id,
Url = url
};
c.Insert(fav);
}

But Linq are support not in full JOIN, for example, are not support, therefore for advanced queries it is necessary to write all the same SQL the code.


Where my bonus?

Well basically I writing the most important thing. Now promised bonus. I suggests you to test SQLite in svoyom the project, for the decision of the task of localization.

We had a database: