SimpleSQL - SQLite integration with Unity3D

@echo17 , thanks for such a comprehensive response! Will read in and digest...

 

@echo17,

Thanks v much for all of that. Think you you've also shown how I might save out a runtime database to some other external folder.


With the /" thing, in other SQL statements in applications things like names of tables and indices didn't need to be in quotes and only string values need to be in ' but not in"... Only the total string of the SQL statement needed to be in "...? Assume that's not allowed here?

 

Not sure. Best bet would be to try it and see what happens. I've always used double quotes with escape sequences so I haven't given it much thought. Seems like single quotes would work too if sqlite allows it.

 

I'll give it a whirl and let you know...

 

I have a class with a sprite in it, im trying to save/load to/from a blob column. Any way to do this with ORM or Am I forced to use DataTables? Thanks in advance.

 

Ok, I managed to solve it, if someone is interested.

Code (CSharp):

1.  
2. public byte[] BytesFoto{ get; set; }
3. private Sprite photo;
4. public Sprite Photo {
5.   get {
6.     if(photo==null){
7.       if(BytesPhoto!=null){
8.         Texture2D texture = new Texture2D(4, 4, TextureFormat.RGB24, false);
9.          texture.LoadImage(BytesPhoto);
10.          photo= Sprite.Create(texture,new Rect(0,0,texture.width,texture.height), new Vector2(.5f,.5f));
11.        }
12.      }
13.      return photo;
14.   }
15.   set{
16.     photo= value;
17.     BytesPhoto= photo.texture.EncodeToPNG();
18.   }
19. }

Basically I store the photo as a blob and then i load it into a byte[]. What I store in the db is the byte[] and the Photo just converts the array into a Sprite.

 

@phortos ,

That's brilliant! I don't suppose you could do similar for Texture2D and other gameObjects?

@echo17 , nb the above code - useful in general re usability of SimpleSQL...

 

Hello,

I'm getting the below error:

Failed to open database at the working path: C:/Users/John/AppData/LocalLow/DefaultCompany/SimpleSQLBase\Static.sqlite
UnityEngine.Debug:LogError(Object)
SimpleSQL.SimpleSQLManager:Initialize(Boolean)
SimpleSQL.SimpleSQLManager:Awake()

I saw this was reported elsewhere on the thread but wasn't replicable at the time. If it helps, it seems to occur after after recompile after I change code during execution in the editor. Only workaround I see at the moment is to restart Unity.

Thanks,
John

 

@echo17 ,

I've posted some code to the SimpleSQL forum as well. Basically, I've gone looking (with a third party application) to interrogate a database (renamed) that I create on run / runtime. Sure enough the renamed sqlite database is in the Application persistent data path (which I found with Debug) but neither that nor the original Asset folder database have changed to reflect the creation of a table and a row in that table in either run or runtime.

I've reviewed the FAQs and the other forum and still don't understand why and what's wrong...?

Here is some of the salient code:-

Code (CSharp):

1. using UnityEngine;
2. using System.Collections;
3. using System.Collections.Generic;
4. using SimpleSQL;
5.  
6. public class CreateItemsTable : MonoBehaviour
7. {
8.  
9.     public SimpleSQL.SimpleSQLManager dbManager;
10.  
11.     // Use this for initialization
12.     void Start ()
13.     {
14.  
15.         dbManager.CreateTable<ItemClass> ();
16.  
17.         ItemClass itemClass = new ItemClass { Item_Name = "A Basic Item"};
18.  
19.         dbManager.Insert (itemClass);
20.  
21.         Debug.Log (Application.persistentDataPath);
22.  
23.     }
24.  
25.     // Update is called once per frame
26.     void Update ()
27.     {
28.  
29.     }
30. }
31.  

I've clearly overdone the namespaces - but you get the idea.

I should say that the first column/class variable is a primary key, autoincrement int called Name_UID - do I reall need to say instead "ItemClass itemClass = new ItemClass { Item_UID = 1, Item_Name = "A Basic Item"};" to add a row to the table? [edit] tried adding that, didn't help...

The table itself does not exist in any fathomable or locatable version of the database but I thought this might be because I failed to insert a row to the table and all tables need rows?

Help...

[edit]

I've also posted to the SimpleSQL Forum. As far as I can determine, with debugger, the code falls down after the database is created in the persistent datapath but in some way the system does not recognise the table when I go to insert a row to it...

 

Im creating a texture and then creating a sprite from that texture, if you want it to return a texture just return the texture after texture.LoadImage

Code (CSharp):

1.  public byte[] BytesPhoto{ get; set; }
2. private Texture2D photo;
3. public Texture2D Photo {
4.   get {
5.     if(photo==null){
6.       if(BytesPhoto!=null){
7.         Texture2D texture = new Texture2D(4, 4, TextureFormat.RGB24, false);
8.          texture.LoadImage(BytesPhoto);
9.          photo= texture;
10.        }
11.      }
12.      return photo;
13.   }
14.   set{
15.     photo= value;
16.     BytesPhoto= photo.texture.EncodeToPNG();
17.   }
18. }

 

I was able to create the table and insert the row using your code, so I'm not sure what might be different about our projects. Have a look here for more information:

http://www.echo17.com/forum/index.php?topic=1809.msg3059#msg3059

 

Very nice, thanks for sharing. I'll add this to my FAQ

 

This seems to happen after using the Insert or InsertAll commands. This is probably because they use transactions which place a lock on the database. Try using SQL commands directly (like "INSERT") to avoid the locks that Unity is placing on the file.

 

Does this support LInux games? I see Windows and Mac, but no Linux.

 

@echo17 ,

Thanks I will review and come back to you on the SimpleSQL forum if that's ok? Thanks for getting back to me so quickly.

 

I don't have a Linux system, so I couldn't tell you for sure. I only advertise the platforms that I have personally tested on to avoid misleading information. Someone here on this forum thread may be able to answer your question. You can also post here:

http://www.echo17.com/forum/index.php?board=5.0

 

SimpleSQL Sale 50% off for just a few more days
Hurry now before the month is over to get this great deal
for Unity Level 11

Asset Store Link:
https://www.assetstore.unity3d.com/en/#!/content/3845​

 

@echo17 ,

It's still not working for me. I'll post the relevant scripts etc to a post on the SimpleSQL forum and maybe you can figure out what's wrong. Apologies for the sheer length of the ItemClass..

[edit]

actually - can you email me and I'll then email you the scripts which aren't working...? In fact I could email you a zip of the full project?

[edit]

I've sent the entire project to your support email.

 

@echo17
Have now posted the relevant bits of project etc in a rar zip file with attachment < 3.2MB. Hopefully you will receive. I've also sent the various bits by email, also my purchase no. for SimpleSQL and finally a link to the whole project zipped up in a rar in Dropbox...

 

@ echo17,

Thank you for checking all my (laborious) code and finding the error so quickly. Top drawer support and a superb Asset. Can't wait to make this fly with Playmaker...

 

Hello, I've just recently purchased this kit and think it's great so far!

However, I had a few questions.

1) Rather than using the parameter binding (?, ?, ?), I believe the same effect could be achieved using string.Format. Which method do you think would be better in terms of memory usage and performance?

2) Would it be possible to introduce some new methods that reuse certain objects, such as the SQLiteCommands? That may be a stupid question; I am quite new to SQL, so it may be that a new command has to be created for each query, and the same for other objects/actions.

3) I will have a lot of instances where I'll be retrieving a single record via it's primary key, so I know only one record will be returned via the query. Looking at the source code, there doesn't appear to be a method for avoiding the List generation. I think I can modify the source to suit my needs, but just wanted to run the code by you to see if I may be missing something. Here it is:

Code (CSharp):

1. public T ExecuteQuery<T>(TableMapping map)
2. {
3.     if (_conn.Trace)
4.     {
5.         Debug.Log("Executing Query: " + this);
6.     }
7.  
8.     var stmt = Prepare();
9.  
10.     var cols = new TableMapping.Column[SQLite3.ColumnCount(stmt)];
11.  
12.     for (int i = 0; i < cols.Length; i++)
13.     {
14.         var name = Marshal.PtrToStringUni(SQLite3.ColumnName16(stmt, i));
15.         cols[i] = map.FindColumn(name);
16.     }
17.  
18.     SQLite3.Step(stmt);
19.  
20.     var obj = Activator.CreateInstance(map.MappedType);
21.     for (int i = 0; i < cols.Length; i++)
22.     {
23.         if (cols[i] == null)
24.             continue;
25.         var colType = SQLite3.ColumnType(stmt, i);
26.         var val = ReadCol(stmt, i, colType, cols[i].ColumnType);
27.         cols[i].SetValue(obj, val);
28.     }
29.     T r = (T)obj;
30.            
31.  
32.     Finalize(stmt);
33.     return r;
34. }

Obviously a couple additional methods will need to be written in some other classes, but this is the main one where the work occurs (from what I can tell).

Thanks!

 

You could use string.Format. The parameters actually are a specialized function in SQLite. When you construct a query with parameters SQLite gets some performance benefits when you reuse the statement. For instance, if you construct an insert query with parameters, then insert multiple times using the one statement with different parameters, SQLite will run some optimizations to make the insert more efficient. See this link for more info:

https://visualstudiomagazine.com/articles/2014/03/01/sqlite-performance-and-prepared-statements.aspx

Also, if you are doing lots of inserts, using a transaction will greatly speed up performance.

I'm not entirely sure what you mean by this, but the above explanation of prepared statements might be what you are after. Using parameters, SQLite will compile the statement once, then you can reuse it over and over to get improved performance.

Have a look at this link to the SimpleSQL API: http://echo17.com/support/simplesql...anager.html#a13e3805999c4a53391ce3b2937486504

 

Good to know, thanks! I just want to make sure I understand corectly, does the optimization occur when that same statement is used throughout the entire session (i.e. the entire time the game is running), or only within a single transaction?

Looking at the source code, it appears a new SQLiteCommand is created each time a query/insert/delete/etc. is run. I was specifically asking if it would be possible to just create one command per operation and then reuse them throughout the lifetime of the program.

So let's say I have this code:

Code (CSharp):

1. string query = "SELECT ID, Manager_Type FROM Cards WHERE ID = ?";
2.  
3. Card SelectCard(int ID)
4. {
5.     bool cardExists;
6.     return QueryFirstRecord<Card>(out cardExists, query, ID);
7. }
8.  

Could I do this instead:

Code (CSharp):

1. SQLiteCommand command;
2.  
3. void Start()
4. {
5.     string sql = "SELECT ID, Manager_Type FROM Cards WHERE ID = ?";
6.     command = simpleSQLManager.CreateCommand(sql);
7. }
8.  
9. Card SelectCard(int ID)
10. {
11.     bool cardExists;
12.     return QueryFirstRecord<Card>(out cardExists, command, ID);
13. }
14.  

Do you see anything wrong with doing it this way? Obviously the source code will need to be modified with new methods that utilize a command object rather than a string statement, where the list of bindings is recreated each time the command is run (I think).

I did see that method, but looking at the source code it doesn't appear that it is optimized. A full list of all records that match the query are created even though we only care about the first one.

I have already modified the source (posted below the post you quoted) with what I believe to be a more efficient solution, but there is a decent chance I may have missed something (even though it appears to be working). If you wouldn't mind looking over the code, I'd be very grateful!

Thanks for the quick response! Great work on this package!

 

I'm not positive, but I believe it is only when used in transactions. You might try googling SQLite, prepared statements, transactions, parameters, that sort of thing. There's probably a good explanation of how the underlying engine handles these things.

I suppose that would work, but I don't know that it would gain you anything other than reusability. SQLite will still have to open a connection and run the command, so there won't be any performance boosts. It would be just as simple to keep track of SQL strings that you create and reuse those instead. You also wouldn't have to modify the source for that.

I see. Well, you could just run a SQL query that returns the first record and pass that to the QueryFirstRecord function. Something like:

Code (CSharp):

1. SELECT TOP 1 FirstName, LastName FROM Employees ORDER BY LastName ASC

Sure, you can send it to me at my support email address in my signature if you'd like. I'll be traveling in the near future, so it may be some time before I can peruse.

No prob, and thanks!

 

@echo17 ,

Have posted another question to the SimpleSQL forum - just wondering again (GO references aside) what happens if I use certain variable types (GameObject, Texture2D, Sound, Vector3) in a class I then seek to add as a new table, through ORM, to the database...

 

Please see answer here: http://www.echo17.com/forum/index.php?topic=1811.msg3075#msg3075

 

thanks

 

I always get the error:

Did I creat my database wrong?
I used SQLite Manager for Firefox, created a Databse, hit export and renamed the file so it has the .bytes ending.
But then I get the error message.

Regards

 

@_Beyond_ I was having a similar issue with the program I use (not Firefox SQLite Manager). What is the file extension of the file when you first export it (before renaming)? If it's an sqlite file, it will not work. I can't offer much help when it comes to Firefox, but perhaps try looking for the base file that Firefox is using (not the exported one) and using that (re saving it as a .bytes file).

Personally I use SQLiteStudio, which you can find via a google search. I haven't seen any issues (viruses, etc.) with it in the few weeks I've been using it, so it should be safe to download, though note I have no affiliation with the product and can't guarantee anything.

If you decide to switch to SQLiteStudio, you'll want to use the Database file created when you add the database, not the export feature. This file will be of type "File". After making changes to my database, I simply open this file in notepad and re save it with the .bytes extension, then import it into Unity.

Good luck

 

Thanks for your help man!

Switched to another program to create the database and now everything works as it should.

Regards

 

@gilley003 - Thanks for helping out!

@_Beyond_ Glad you got it working. You can also create a blank database file directly from Unity using the Tools > SimpleSQL > Create Empty Database. This file can be opened in Firefox's SQLite Manager (which is what I personally use)

 

Hello Echo17,

Just out of curiosity, when building for Android, you said that you extract libsqlite3.so from the SimpleSQL_Editor.dll.
What that means ? Is there a DLL to SO engine ? Is there a big "libso" const string in that DLL ?

 

It just means that I embed the libsqlite3.so file in the dll. You can actually see it in the source code. Open up the solution that comes with the plugin and look at the SimpleSQL_Editor project. You'll see a folder called Resources. In that folder are a number of items that I embed. Some of them, like the Android sqlite library (libsqlite3.so) are extracted from the dll when the Android platform is selected in the SimpleSQL options.

If you look at the file OptionsEditorWindow.cs you'll see where I extract various files depending on the platform selected in the OnGUI function. Just search for "EditorHelper.CreateFileFromEmbeddedResource".

The reason I embed files and extract is to simplify the process for the user. I could have created different Unity packages for each platform and require the user to import the correct package, but I wanted a more friendly approach that let the user select a platform from an options window, letting the plugin handle what needs to be extracted.

 

Thank you for the answer.
My final question is, where doest the ARMV7 Sqlite3 library comes from ? Did you compiled it wit gcc ?
Or did you download it from somewhere ?

 

I honestly cannot remember where I found the Android libsqlite3.so file. I probably downloaded it from a post on the Unity forums somewhere, but I'm not sure. The regular sqlite3.dll files I found here: http://sqlite.org/download.html

 

@echo17 ,

I've posted you a doozy of a challenge/request for some example script on the SimpleSQL forum:- http://www.echo17.com/forum/index.php?topic=1850.0

I really hope you can help - I have dreams of rigging an enhanced Inspector to allow you to insert data in a useful way in to SimpleSQL tables at design time, building on the scripts you have already provided...

 

How can I get the insert id when I do an insert query? I have a primarykey setup, and everytime I insert new data, it gets a an incremented ID, but I need a way to get that ID. In sqlite there is a function called Last insert row id, here https://www.sqlite.org/c3ref/last_insert_rowid.html. How to do that? Thank you.

 

There is no limit imposed by SimpleSQL to how many parameters you can use. I would imagine there is something in the value of the Level field that is causing the sql string to terminate (possibly a quotation mark?). If you would like me to take a look at an example project demonstrating the issue, please see this FAQ:

http://www.echo17.com/forum/index.php?topic=257.msg950#msg950

 

@echo17 thank you for your response, but I actually solved that problem, my new problem is the one you quoted Its about last_inserted_rowid function

 

oops. Unity sends out an email with the text of the post, so I read that and then replied without realizing you'd deleted the post and added another. My bad

To return the row id of the query, you can either do a query that gets the max of the id field you set up, or you can use the built in row id function. To get the max, you'd write something like:

Code (CSharp):

1. SELECT MAX(MyIDField) AS LastID FROM MyTable

To get the built-in row, see this FAQ: http://www.echo17.com/forum/index.php?topic=1160.0

Note that the row id and the maximum of your key field are two different things, so you can pick one based on what fits your project best.

 

*EDIT*
After some research I have found there is not any tvOS local storage allowed. If this is not true someone let me know but it appears I will have to integrate some iCloud features.

I have Simple SQL and it works great in all my iOS and Android packages. I have just started an Apple tvOS project and I get the warnings below. I am just wondering if you plan on supporting tvOS or if maybe there is something I need to do to get things working. Thanks

 

I don't have an Apple TV to test with, so I couldn't give you a solution to this.

 

Thanks for the reply but looks like I will have to implement something else as there is no ability to save data locally. Unless you could modify it to create a local SQL database without saving it to disk and then I could save it to the cloud using cloud kit or something. Just a thought.

 

Hi,

Is it possible to use SimpleSQL to store and compare dates?

Regards

G.

 

Just curious- could the SimpleSQLManager.Close call be moved from its OnApplicationQuit to its destructor? The docs suggest DB connect errors are mostly due to external conflicts, but most of my issues occur within Unity when:

1) I connect to my DB via an inspector button on a prefab; when I run my game in editor then stop, I get locked out after reserialization and need to restart Unity to reconnect.

2) If I make code changes at runtime and my scripts finish compiling before I have a chance to hit stop. The DB manager created at start never releases its connection, requiring a Unity restart.

When I add mySQLManager.Close() to my parent destructors, these problems go away, but it seems like it'd make more sense long term for the call to be made within ~SimpleSQLManager itself.

 

Hello echo17

The query on sqlBrowser (in miliseconds)

Same query in unity

result (in seconds)


1. My question is - WHY it's ~420 times slower ?
2. Why do you use exactly .bytes extension ?
3. Why runtime db change is so complicated ?

you can check the db load from this lib
https://github.com/codecoding/SQLite4Unity3d
only con of above lib is join, it's not implemented ...

 

Hi everyone,
Is there an example script on how to use Group By implementation?
I have a script that takes rows from a table by the attribute actorID and I want to be able to run simultaneously on different rows by actorID.
Thanks alot!

 

I see some old posts on here that I haven't responded to. My apologies. For some reason, the notifications for these posts were not sent to my email so I didn't see them. If you need assistance, please check out my forum at echo17.com/forum or send me an email directly.

 

I would suggest doing a search for SQLite queries since SimpleSQL is just a wrapper for SQLite. I'm not sure what it is you are trying to do, so if you could send me an email describing it in detail, I'll try to help out.

 

SimpleSQL is on sale during Unity's Asset Store Madness Sale!
http://u3d.as/387​