SimpleSQL - SQLite integration with Unity3D

well the answer to my android dll question is that the platform switch is not detected by your plugin and you have to configure it in options, this is a bit hidden in your documentation I would reckon...

 

Is it possible to generate the SimpleDBManager without a gameobject in another script and without a dummy db if I want to switch it then anyway. It feels a bit workaround like. It seems you always need a file because you save the name in that TextAsset object...

 

That's a good point. I've updated the SimpleSQLManager to allow an empty database file. You can then set the file dynamically following the procedure outlined here:

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

Version 1.9 is now available on the Asset Store with this change.

 

Please test package on Unity 2017, it has a lot of little buggs.

 

We're getting native Android exceptions (probably Sony Xperia only) and we try to figure out where these come from.
Is it possible that your asset could cause this exception?

Code (CSharp):

1. java.lang.Error: FATAL EXCEPTION [AsyncQueryWorker]
2. Unity version : 5.6.0f3
3. Device model : Sony D5803
4. Caused by
5. at android.os.Parcel.readException(Parcel.java:1627)
6. at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:183)
7. at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:135)
8. at android.content.ContentProviderProxy.insert(ContentProviderNative.java:482)
9. at android.content.ContentResolver.insert(ContentResolver.java:1240)
10. at android.content.AsyncQueryHandler$WorkerHandler.handleMessage(AsyncQueryHandler.java:96)
11. at android.os.Handler.dispatchMessage(Handler.java:102)
12. at android.os.Looper.loop(Looper.java:234)
13. at android.os.HandlerThread.run(HandlerThread.java:61)

 

Thanks for the heads up. I'll try to test on the beta Unity when I get a chance.

 

I'm not familiar with these errors and it doesn't look like anything specific to SimpleSQL or sqlite in general. The DatabaseUtils are not something used by SimpleSQL / sqlite. That is just something internal to Android for how it stores content / messages.

 

I'm not able to get any bugs when testing SimpleSQL 1.9.0 on Unity 2017.1.0b3. Can you provide specific examples of the issues you are seeing? Please also provide the type of machine you are developing on (Mac / PC) and what platforms you are building to. Try to be elaborate on the steps to reproduce as that will help me pinpoint the problem easier.

 

Two problems:

1. I am unable to access SimpleSQL -> Options,

I am getting the following errors

Code (csharp):

1.  
2. MissingMethodException: Method not found: 'UnityEngine.Texture2D.LoadImage'.
3. SimpleSQL.Resource.LoadTextures ()
4. SimpleSQL.OptionsEditorWindow.OnGUI ()
5. System.Reflection.MonoMethod.Invoke (System.Object obj, BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) (at /Users/builduser/buildslave/mono/build/mcs/class/corlib/System.Reflection/MonoMethod.cs:222)
6. Rethrow as TargetInvocationException: Exception has been thrown by the target of an invocation.
7. System.Reflection.MonoMethod.Invoke (System.Object obj, BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) (at /Users/builduser/buildslave/mono/build/mcs/class/corlib/System.Reflection/MonoMethod.cs:232)
8. System.Reflection.MethodBase.Invoke (System.Object obj, System.Object[] parameters) (at /Users/builduser/buildslave/mono/build/mcs/class/corlib/System.Reflection/MethodBase.cs:115)
9. UnityEditor.HostView.Invoke (System.String methodName, System.Object obj) (at /Users/builduser/buildslave/unity/build/Editor/Mono/HostView.cs:272)
10. UnityEditor.HostView.Invoke (System.String methodName) (at /Users/builduser/buildslave/unity/build/Editor/Mono/HostView.cs:265)
11. UnityEditor.HostView.OnGUI () (at /Users/builduser/buildslave/unity/build/Editor/Mono/HostView.cs:94)
12.  

2. After deployment to iOS, I got the following error:

Code (csharp):

1.  
2. DllNotFoundException: Unable to load DLL 'sqlite3': The specified module could not be found.
3.  
4.   at SimpleSQL.SQLite3.Open (System.String filename, IntPtr& db) [0x00000] in <filename unknown>:0
5.  
6.   at SimpleSQL.SQLiteConnection..ctor (System.String databasePath) [0x00000] in <filename unknown>:0
7.  
8.   at SimpleSQL.SimpleSQLManager.CreateConnection (System.String documentsPath) [0x00000] in <filename unknown>:0
9.  
10.   at SimpleSQL.SimpleSQLManager.Initialize (Boolean forceReinitialization) [0x00000] in <filename unknown>:0
11.  

 

I can provide a fix for this if you will send me a private email with your invoice number you received when you purchased SimpleSQL. My email can be found in the signature of my post.

After getting the fix from me, Try setting the platform to iOS.

 

I am using Windows 10 PC.
Problems which I remember was:
1) Importing problem. It doesn't work, if you do not allow Unity to fix deprecated Texture on package import. Atleast you couldnt change the options.
2) I had problem to put bytes asset into DB object with Inspector. I was able to put it, but after restarting the app it was reset. So I had to save DB object as prefab only to make it hold my bytes asset.
3) Editor doesn't disconnect DB after stopping the application. Not sure is it bugg or not, but it created some troubles for me so anytime when I needed to change DB file manually it was blocked, so I had to close Unity.

 

I have a fix for this, but I can't submit to the Asset Store while this version of Unity is in beta. You can send me a private email to the address in my signature with you invoice number and I'll send you an update directly.

That is an odd one. I'll have to investigate that.

This is a known issue with all versions of Unity. Unity puts a lock on the file sometimes that causes transactions to fail. I don't have an idea of how to fix this one.

 

I'm getting a

Code (CSharp):

1. SQLiteException: Row

at

Code (CSharp):

1. SimpleSQL.SQLiteCommand.ExecuteNonQuery ()

when using this piece of code:

Code (CSharp):

1. string selectSql = "SELECT * FROM " + Settings.DbName + " WHERE 1";
2. dbManager.Execute(selectSql);

Does someone know why and/or a better way to check if a table already exists?

 

You will probably need to supply a valid where clause. Something like "WHERE Id IS NOT NULL" if you have an Id field.

Try this FAQ: http://www.echo17.com/forum/index.php?topic=257.msg387#msg387

 

Hi, first of all great plugin! Where does the plugin store the data of the tables you create at runtime? In the [name].bytes file or is it saved at another location? I'm just wondering, because if it is stored in the .bytes file, won't it increase the build size?

 

Thanks for the kind words! Have a look at these FAQs that may help answer your question:

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

 

Hi, I think I've discovered a bug? I've got to build a version of my game in 32 as well as 62 bit. x86_64 works fine, but when I go to build x86 version I get:
"DllNotFoundException: {BUILD_DIRECTORY}/build_Data/Plugins/sqlite3.dll"

In the root of the build directory, there is a sqlite3.dll file, which is 586KB. and in the Plugins folder, there is another file called sqlite3.dll, which is 1148KB. If I copy the smaller file onto the larger file, in the plugins folder, then everything works fine. We're using automatic cloud builds, which make it a bit of hassle if I need to copy around dll files to make sure the app runs fine.

 

Hmmm... I have not seen this before. I am able to build an x86 version fine. Be sure you are using the Universal build option in the Tools > SimpleSQL > Options window. You do not need to set the 64 bit version, that was added more to be complete than necessary. The Universal option will work on x86 and 64 bit.

 

Yeah, I'm sure I was using the Universal option. So before the first run, there's a sqlite3.dll in the build_Data\Plugins directory. After first run (which can't load that file) there's another sqlite3.dll file, which is a different size, in the root folder of the build.

Would you mind if I sent you my build? If you've time to look at it? Maybe there's something you can glean from it?

 

Certainly, send it my way. You can email it to the address in my signature. Please provide an invoice number in the email as well.

 

Hi! Can you help me with this problems: I have no access to SimpleSQL -> Options and I get this error:

Code (CSharp):

1. TypeLoadException: Could not load type 'UnityEngine.ImageConversion' from assembly 'SimpleSQL_Editor'.
2. SimpleSQL.Resource.LoadTextures ()
3. SimpleSQL.OptionsEditorWindow.OnGUI ()
4. System.Reflection.MonoMethod.Invoke (System.Object obj, BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) (at /Users/builduser/buildslave/mono/build/mcs/class/corlib/System.Reflection/MonoMethod.cs:222)
5. Rethrow as TargetInvocationException: Exception has been thrown by the target of an invocation.
6. System.Reflection.MonoMethod.Invoke (System.Object obj, BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) (at /Users/builduser/buildslave/mono/build/mcs/class/corlib/System.Reflection/MonoMethod.cs:232)
7. System.Reflection.MethodBase.Invoke (System.Object obj, System.Object[] parameters) (at /Users/builduser/buildslave/mono/build/mcs/class/corlib/System.Reflection/MethodBase.cs:115)
8. UnityEditor.HostView.Invoke (System.String methodName, System.Object obj) (at C:/buildslave/unity/build/Editor/Mono/HostView.cs:262)
9. UnityEditor.HostView.Invoke (System.String methodName) (at C:/buildslave/unity/build/Editor/Mono/HostView.cs:255)
10. UnityEditor.HostView.OnGUI () (at C:/buildslave/unity/build/Editor/Mono/HostView.cs:84)
11.  

Also I get error

Code (CSharp):

1. DllNotFoundException: Unable to load DLL 'sqlite3': The specified module could not be found.

on Android devise. Thanks.

 

I am unable to reproduce these errors. Be sure you have updated to the latest version of SimpleSQL. What version of Unity and SimpleSQL are you using?

 

Unity 5.6.1f1
SimpleSQL 2.0.1

 

Can you help me at least with

Code (CSharp):

1. DllNotFoundException: Unable to load DLL 'sqlite3': The specified module could not be found

on my Android devices?

 

Take a look at this thread: http://www.echo17.com/forum/index.php?topic=1848.msg3200#msg3200

 

I am unable to reproduce the issue with this combination of versions. If you would like, I could take a look at your project on my machine and see if there is anything unusual about the setup. Please see this FAQ for more information: http://www.echo17.com/forum/index.php?topic=257.msg950#msg950

 

hello, I've just bought the plugin, i hope there would be some detailed video tutorials for newbies ; on how to use it with the playmaker actions
it would be really great..
thanks in advance

 

Sorry, I do not have any video tutorials at this time, but please see this FAQ for a quick guide on how to start using SimpleSQL in Playmaker: http://www.echo17.com/forum/index.php?topic=257.msg3957#msg3957

If you have specific questions on usage, you can send me an email to the address in my signature.

 

it took me a couple of days to learn the basics of simple SQL it turned out to be the best asset next to playmaker, helped me enormously in creating a grading system for my students
thanks a lot..

 

That is awesome! I am so glad it could be used in an educational context. Thanks for the note!

 

Hello,
I just bought this asset, and am wondering how to go about customizing the way a certain field gets set, when importing the result of a query into a class. For example, lets say I am pulling from an Item table, into a class called Item. Item has a member variable of type ItemCategory, which itself is a class.

Let's say I store ItemCategory info in its own table, keyed by an integer, which also represents an enum on the code side named ItemCategoryType. So each Item has a reference to the ItemCategoryType it is linked to, by having an integer in its row.

Item Table
ItemCategoryType Name
0 Sword
0 Axe
1 Bow

ItemCategory Table
ItemCategoryType Name
0 Melee
1 Ranged

public class Item {
[PrimaryKey]
ItemCategory ItemCategoryType {get; set;}
}

If I try to load an Item row through this setup, it won't be able to convert the integer value for ItemCategoryType, into the relevant class for ItemCategory, that I defined earlier in the code. What is the best way to handle such a situation? Would I need to add some logic within the constructor, or within the {get;} next to the ItemCategory field?

 

It sounds like you are describing a relational table system. What I would suggest is to use a join on your query and pull in the data that way. The first demo that comes with SimpleSQL demonstrates this with a join between the Weapon table (which contains a weapon type id) and the WeaponType table (which contains the detail information about the weapon type). Here is the code copied from demo #1 SimpleQuery.cs:

Code (CSharp):

1.         // Gather a list of weapons and their type names pulled from the weapontype table      
2.         List<Weapon> weapons = dbManager.Query<Weapon>(
3.                                                         "SELECT " +
4.                                                             "W.WeaponID, " +
5.                                                             "W.WeaponName, " +
6.                                                             "W.Damage, " +
7.                                                             "W.Cost, " +
8.                                                             "W.Weight, " +
9.                                                             "W.WeaponTypeID, " +
10.                                                             "T.Description AS WeaponTypeDescription " +
11.                                                         "FROM " +
12.                                                             "Weapon W " +
13.                                                             "JOIN WeaponType T " +
14.                                                                 "ON W.WeaponTypeID = T.WeaponTypeID " +
15.                                                         "ORDER BY " +
16.                                                             "W.WeaponID "
17.                                                         );

And here is the Weapon ORM class that stores the data from the query. You will note that it contains fields that are from both the Weapon table and the WeaponType table:

Code (CSharp):

1. using SimpleSQL;
2.  
3. public class Weapon
4. {
5.     // The WeaponID field is set as the primary key in the SQLite database,
6.     // so we reflect that here with the PrimaryKey attribute
7.     [PrimaryKey]
8.     public int WeaponID { get; set; }
9.    
10.     public string WeaponName { get; set; }
11.    
12.     public float Damage { get; set; }
13.    
14.     public float Cost { get; set; }
15.    
16.     public float Weight { get; set; }  
17.    
18.     public int WeaponTypeID { get; set; }
19.    
20.     public string WeaponTypeDescription { get; set; }
21. }

 

Thank you for the suggestion, and sorry about the late reply on my end. I did look at the example, but unfortunately it does not cover my scenario, since all the member variables of class Weapon, are all primitive types.

My question is, what do I do if a member value is of a class type, instead of a primitive type. Where do I write the logic to get a piece of data from the db, and use it to retrieve the relevant object to assign to that value?

 

All database systems, whether it be sqlite, Oracle, Microsoft SQL Server, MySQL, etc, store data in their tables as primitive data. Instead of storing a class worth of information in a field, you would move that information into its own table along with a unique ID and reference it from the parent table using that unique ID.

In the example of the weapons class, instead of storing the weapon type information directly in the Weapon table, you instead move it into a WeaponType table and reference the appropriate element using the ID of the weapon type. This is known as a relational database system. It is very powerful because you can use joins between tables to filter, sort, and gather information. I would strongly suggest going over some tutorials on database design, as it will give you a firm grasp of how these relations can be used. The examples provided in the demos will also show you some basic ways to use relational links to store complex data.

There is one way you can circumvent this, but I would recommend against it. You could store your class worth of information as a JSON formatted string, making the field type a simple TEXT. You would have to serialize to store the information, and deserialize after reading it, but it allows a lot of flexibility, especially if your relational data structure is not fixed. In my 25 years as a database applications developer, I have only used this scenario once, and it has haunted me every day since. The downside to using this approach is that you cannot join, filter, or directly gather information from the serialized data. You have to read it, deserialize, then process it which is not simple or direct. The cost of the flexibility is the loss simplicity and ease.

Here are some links to FAQs that deal with Unity object storage and retrieval if that happens to be your need:

http://www.echo17.com/forum/index.php?topic=257.msg763#msg763
http://www.echo17.com/forum/index.php?topic=257.msg939#msg939
http://www.echo17.com/forum/index.php?topic=257.msg3805#msg3805

 

Thank you very much for the detailed explanation, and for the links! I think I understand now what you are saying by loading them through a join. I do have a bunch of experience with running SQL queries, joins, subqueries, etc, but I have just never written code to pull a query result directly into an instance of its own class. In the past I usually just got the result array and used that data somewhere, such as arguments to the constructor of a class. This is much cleaner, but I need to understand it better.

So I think what you are saying is that each table would then be loaded into their respective classes, similar with how pulling from just one table, only loads one class. Therefore, I think I might just be getting caught up on the syntax, as the example above where you joined the Weapon class to WeaponType, only mentions a weapon class as the receiving end:

Code (CSharp):

1. List<Weapon> weapons = dbManager.Query<Weapon>(...)

So in my scenario where there would be a member variable that is class, and if we assume for a second that WeaponType is its own class, would I do something like:

Code (CSharp):

1. List<Weapon, WeaponType> weapons = dbManager.Query<Weapon, WeaponType>(...)

Would it also assign the instance of the WeaponType class object it created, to the Weapon's WeaponType class variable?

Or are you simply saying that I should not have member variables of a class be instances of other classes, but rather, an id that can be used to pull an instance of the other class? For example, if I have a class called BusTicket that needs to link to BusCompany, should I just have a variable in the former that is the company's id, rather than a link to an instance of the BusCompany object for the specific company? The reason I am trying this approach is because I was hoping to not always have to do an extra lookup to get the details of the company by using an id, and instead have a reference to an instance of the BusCompany itself where I could get the results I want.

 

Sorry, I do not think I explained it very well. I will elaborate more. Basically, you will store your primitive data into individual tables that you can later join together using sql queries. The result of those queries do not need to reflect the structure of the individual tables, just the combined results. So if you had these tables in your database:

Code (CSharp):

1. Weapon
2. -----------
3. WeaponID INT
4. WeaponName TEXT
5. WeaponTypeID INT
6.  
7. WeaponType
8. -----------------
9. WeaponTypeID INT
10. WeaponTypeName TEXT

You could write a sql query that would bring the two tables' data together, like:

Code (CSharp):

1.  
2. SELECT
3.     w.WeaponID,
4.     w.WeaponName,
5.     t.WeaponTypeName
6. FROM
7.     Weapon w
8.     JOIN WeaponType t
9.         ON w.WeaponTypeID = t.WeaponTypeID

Then your class structure would like like:

Code (CSharp):

1.  
2. class WeaponAndWeaponType
3. {
4.     public int WeaponID { get; set; }
5.     public string WeaponName { get; set; }
6.     public string WeaponTypeName { get; set; }
7. }

And you would pull the information from the query into your combined class like:

Code (CSharp):

1.  
2. var result = dbManager.Query<WeaponAndWeaponType>("SELECT w.WeaponID, w.WeaponName, t.WeaponTypeName FROM Weapon w JOIN WeaponType t ON w.WeaponTypeID = t.WeaponTypeID

I think the confusion was in my naming the resulting class the same as one of the database tables. So you would have separate Weapon and WeaponType tables in the database, but your ORM structure would reflect the combination of the two tables.

Note that the WeaponTypeID was omitted from the resulting query, so do not even need to include the all the fields from both classes if you do not need that data for your particular query. For example, the Weapon table might have damage information, but if you are only interested in showing the names and types of weapons, you can leave that out of the query and not include it in the ORM class. In this example, I was not interested in the weapon type id, just the name of the weapon type, so I left the type id out of the result (even though it is still used to join the tables in the database).

Some example data:

Code (CSharp):

1.  
2. Weapon:
3.  
4. WeaponID    WeaponName WeaponTypeID
5. 1           Gun             1
6. 2           Knife           2
7. 3           Crossbow        1
8.  
9. WeaponType:
10.  
11. WeaponTypeID   WeaponTypeName
12. 1              Ranged
13. 2              Melee

Result from the query above:

Code (CSharp):

1.  
2. WeaponID  WeaponName WeaponTypeName
3. 1         Gun        Ranged
4. 2         Knife      Melee
5. 3         Crossbow   Ranged

 

Hello,

Thanks again for the detailed reply on my last question. I do have another issue happening when I change scenes, that is giving me the following error:

Code (CSharp):

1. Failed to open database at the working path: (...)

The scene loads the db fine by itself, but when I want to go back to the main menu, and start a new game, I get the error above when returning to the original scene that loaded the db. I have tried using DontDestroyOnLoad, in order to try and avoid loading it twice, but that hasn't helped. My theory is that the db file is locked from the first use.

I was also thinking about editing the Simple SQL Manager class to be a singleton, but it does not seem to be editable. Do I need to manually close the db connection before leaving the scene? Or is there another solution?

 

You can call Close and Dispose on the SimpleSQL manager when the scene changes like:

Code (CSharp):

1. dbManager.Close();
2. dbManager.Dispose();

SimpleSQL is precompiled into a library DLL to speed up Unity builds and keep the project clean. The plugin does come with the source, which you can modify and recompile to suit your needs. Please see this FAQ for more information:

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

 

Hi, Simple SQL is not running on Android

I did a roll-your-own sql class but could not get it to run on the devices so I purchased yours. It is running on iOS but not android.

Is there something I have done wrong? I am using the same project and just switching platforms for one and then the other.

 

I replied to your email so we can figure this out. Once we have a solution, I will post it here.

 

I have installed and imported SimpleSQL and I'm using a third party SQLite DB browser to add some records. However when I make changes to the DB they do not seem to refresh in my Unity application. I have had to make a copy of the database every time I add records or modify tables in order for Unity to see the changes. How can I refresh the existing DB without needing to make a copy each time? I hope I do not need to do this when I start adding records from my application.

Other than this it is working great.

Any help is much appreciated.

 

Please see these FAQs which will help:

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

 

Thanks very much for that! Great help!

 

If you have Unity Plus/Pro, you can save on all my assets for a limited time! https://assetstore.unity.com/publishers/599

 

In the asset store you imply that Simple SQL does NOT work with Unity 2017+ -- is that true?

"Works with Unity 3.5.6 to 5.x"

Jay

 

Sorry for the confusion, I had forgotten to update the description. I have tested on 3.5.6 to 2018 and SimpleSQL works great! The description has now been updated. Thanks for pointing this out!

 

Hello,

Ever since upgrading to Unity 2018, I seem to keep getting random occurrences of:

"Assertion failed: Cancelling DisplayDialog because it was run from a thread that is not the main thread: Opening file failed...StaticDataDB.bytes"

followed by

"File couldn't be read"

after I make any change to my database values in DBeaver. I know the database file is not corrupt, as I can still access it fine and modify it.

The only thing that fixes it for me, is to shut down DBeaver, and then reimport the static db file inside Unity. This wasn't the case before, and DBeaver version wasn't updated before this started happening.

 

I have not encountered this error before. It may be that Unity is handling file locks differently in v2018 than they have in the past and having the file open in another application could be causing the conflict. I try to keep external DB editors closed while running my apps in Unity just in case.

You may want to submit a report to Unity about this since they could better debug and explain the error that you are seeing.

 

Hi, its impossible to use the WHERE with IL2CPP (so on iOS) with .net 4.6
Its annoying because only the where seems to use reflection that breaks it so we have to change all our code in order to make it work on iOS.
Is there a fix for it?

Example : var metadata = (
from w in staticDatabase.Table<T>()
where w.MetadataId == primaryKey

will break because WHERE is like such :
public TableQuery<T> Where(Expression<Func<T, bool>> predExpr)

thank you

 

The code you show is actually .NET's built-in query language called Linq and is not related to or affiliated with SimpleSQL. While Linq is pretty cool and can be handy sometimes, I would not recommend using it for cross-platform development for the reasons you have discovered.

Instead, I would suggest using the SimpleSQL built-in query language which uses sqlite syntax. Something like:

Code (CSharp):

1. var results = dbManager.Query<T>("SELECT * FROM MyTable WHERE MetadataId = ?", primaryKey);

Where T is the type of your ORM class used to store the data retrieved from MyTable. For more examples of this, please see the demos included with the plugin.