SimpleSQL - SQLite integration with Unity3D

Yes.

Yes. After downloading the database to your local application's path, you can set the local database. Please see this FAQ: https://echo17.proboards.com/thread/2/faq?page=1&scrollTo=14

 

(RESOLVED)

Hi! Sorry if this is an obvious question but I have my database in my assets folder as GameWorld.bytes

I can read and write to my db with no problems with SimpleSQL. However, I noticed that when I open up the db using "DB Browser for SQLite" the data is different to the data unity sees during runtime.

I tried dropping the db again on the unity editor to make sure it was the same file, and it is, but there seems to be a different copy of data for runtime compared to when I open the file in DB Browser.

Was the db copied by unity to some other folder? I would like to edit that version of the db outside runtime.

Thank you so much!

UPDATE:
I found the answer on the other simplesql forums:
https://echo17.proboards.com/thread/2/faq?page=1&scrollTo=9

The paths for Unity's working directory per platform are:

• Windows Editor and App (XP): C:\Documents and Settings\<user name>\Local Settings\Application Data\<company name>\<application name>
  
• Windows Editor and App (7): C:\Users\<user name>\AppData\LocalLow\<company name>\<application name>
  
• Mac Editor and App: /Users/<user name>/library/caches/<company name>.<application name>
  
• iOS: the documents directory for the device (i don't believe the simulator is used)
  

These paths can also be found at runtime using Unity's member:

Application.persistentDatapath

 

Thanks for letting us know. I was going to link that FAQ

 

Hiii

When I am creating table in db on android it gives me error

SQLiteException: IOError

but it working fine in unity editor

what i am doing wrong here ???

 

I'm not sure. I would need to see your project to give you a diagnosis. You can create a simplified version of the project, just enough to demonstrate the issue and send it to the email address in my signature. Please be sure to include your invoice number and a list of steps to recreate. Please see this FAQ for more information: https://echo17.proboards.com/thread/2/faq?page=1&scrollTo=3

 

Hi team,

I haven't purchased your asset yet, but I would want to know if your Unity asset can help me with my project.

I have used SQLite for the functioning of a game made with Unity 2020.3.8f1 and it worked fine in both: Windows and Android OS (IL2CPP and Mono build). However, I am getting the below issue when trying to run the same app on iOS platform. I believe, I have set everything correctly for the app to work.

MissingMethodException: System.Runtime.InteropServices.Marshal::SetLastWin32Error(System.Int32)
​

at System.Runtime.InteropServices.CriticalHandle.Cleanup () [0x00000] in <00000000000000000000000000000000>:0​

​

at System.Runtime.InteropServices.CriticalHandle.Dispose (System.Boolean disposing) [0x00000] in <00000000000000000000000000000000>:0​

​

at System.Runtime.InteropServices.CriticalHandle.Dispose () [0x00000] in <00000000000000000000000000000000>:0​

​

at Mono.Data.Sqlite.SQLite3.Close () [0x00000] in <00000000000000000000000000000000>:0​

​

at Mono.Data.Sqlite.SqliteConnection.Close () [0x00000] in <00000000000000000000000000000000>:0​

​

at Mono.Data.Sqlite.SqliteConnection.Dispose (System.Boolean disposing) [0x00000] in <00000000000000000000000000000000>:0​

​

at System.ComponentModel.Component.Dispose () [0x00000] in <00000000000000000000000000000000>:0​

Can you let me know if your asset be used to make apps for iOS platform as well without getting any issues on all the architectures (arm64 , arm7 , x86)?

 

Hi there. Yes, SimpleSQL works on Android, Windows, and iOS. I haven't had any issues compiling across these platforms. If you do come across something, you take a look at these FAQs: https://echo17.proboards.com/thread/2/faq or email me at my address in the signature.

 

I would like to initialize a single variable with one query line. Is this possible?
EDIT: I'm trying to avoid

foreach

loops to keep the code manageable.

int defense = dbManager.Query<Buildings>("SELECT defense FROM buildings WHERE name = '" + type + "'");

 

You can technically do this with something like:

Code (csharp):

1.  
2. int defense = dbManager.Query<Buildings>("SELECT defense FROM buildings WHERE name = '" + type + "'")[0].defense;
3.  

since the Query result is just a list, so returning the first element is as simple as referencing the 0th position.

This will crash if there is no value returned, however, so that is why I would not recommend a single line. Instead I would use the QueryFirstRecord method and check if there is a value.

Code (csharp):

1.  
2.             bool recordExists;
3.             var record = dbManager.QueryFirstRecord<Weapon>(out recordExists, "SELECT Damage FROM Weapon WHERE WeaponID = 1");
4.             if (recordExists)
5.             {
6.                 var damage = record.Damage;
7.             }
8.  

 

Is this statement valid? It returns the total row count including nulls.

names = dbManager.Query<Names>("SELECT English FROM names WHERE English IS NOT NULL");

Debug.Log(names.Count);

EDIT: This is working now. My database software wasn't writing NULL fields properly.

 

I have a string field that contains Mi'kmaq and I encountered this error today:
"SQLiteException: unrecognized token: "'Miꞌkma"

I have confirmed there is no typo in the table or the code and no data field called Mi'kma. Is it possible the apostrophe is causing the issue?

Update: I have had another field with an apostrophe throw an exception.

 

If would recommend using parameters to insert data as it will handle the string building for you. Something like:

Code (csharp):

1.  
2. var sql = "INSERT INTO Weapon (WeaponID, WeaponName) VALUES (?, ?)";
3. dbManager.Execute(sql, 1000, "Gandalf's Staff");
4.  

If you want to build the string yourself, you'll need to escape the single quote with a double instance of the single quote. This is a requirement built into the sqlite language itself. You can find out more here: https://stackoverflow.com/questions/603572/escape-single-quote-character-for-use-in-an-sqlite-query

Here is an example:

Code (csharp):

1.  
2. var sql = "INSERT INTO Weapon (WeaponID, WeaponName) VALUES (999, 'Gandalf''s staff')";
3. dbManager.Execute(sql);
4.  

Note: if you are using an external program to view data, like DB Browser for SQLite, then it will display the value without the single quote, so you might think it didn't work. This is just a display issue in that particular program. If you copy and paste that value into a text editor you will see it still has the single quote.

 

I'm creating the tables in Google Sheets and exporting csv to DBeaver. No issues with that and the fields show up properly.

Maybe a better question: Do these statements follow standard sqlite convention? If so, then I can look for guides and docs online.

 

Yes, they follow the standard conventions. The link I posted was to StackOverflow, which is a generic help source. The examples I provided were based on the information in that thread using the standard sqlite method of escaping quotation marks and parameter usage.

 

Is there a way to SELECT query a single row and not have to pass it into a class list and instead just a single class instance?

 

Check out this FAQ: https://echo17.proboards.com/thread/2/faq?page=2&scrollTo=25

 

Hi,

I am trying to create a table with a combined primary key

Code (CSharp):

1. public class Test
2. {
3.     [PrimaryKey] // would like to add [ForeignKey], but does not exist
4.     public int SaveGameID { get; set; } // corresponds to SaveGame.ID for handling multiple save games
5.  
6.     [PrimaryKey]
7.     public int ID { get; set; } // ID of asset
8.  
9.     public int Data { get; set; } = 0;
10. }

If I try with

DatabaseManager.CreateTable<Test>();

I get an error "SQLiteException: no such table".

I can use a command as a work around

Code (CSharp):

1. DatabaseManager.Execute("CREATE TABLE Test(" +
2.     "SaveGameID INTEGER NOT NULL, " +
3.     "ID INTEGER NOT NULL, " +
4.     "Data INTEGER NOT NULL, " +
5.     "PRIMARY KEY (SaveGameID, ID))");

But I would like to do it with an ORM class. Is this possible?

On a general node, I am unsure about my design using a combined primary key of save-game ID and asset ID. How do you recommend handling multiple save games?
SaveGameID in this design would be a typical example for a foreign key. Is there any news on that feature?

Thank you for your support!

 

I pulled the ORM to SQL generation code from sqlite.net a few years back. I'm not sure if that code has been updated to include multiple primary keys, I'll have to investigate.

I generally try to keep a single primary key for a table, usually naming it UniqueID or something like that. It simplifies the coding, especially since I don't have to remember what I've named my primary key when creating queries. So, something like this for your ORM would be how I'd structure it:

Code (CSharp):

1. public class Test
2. {
3.     [PrimaryKey, AutoIncrement]
4.     public int UniqueID { get; set; }
5.  
6.     public int SaveGameID { get; set; }
7.     public int ID { get; set; }
8.     public int Data { get; set; } = 0;
9. }

That would be another feature I'd have to investigate in sqlite.net.

 

Hi. Why I can't read a long type value from CREATED_AT and UPDATED_AT fields? SimpleDataRow returns value type of Int32 witch equals -1.

I am using the QueryGeneric method. Data in table present and has valid values.

 

Does this work on Linux and Web player builds??

 

SimpleDataRow pulls data as raw objects. If you want to have the full effect of type casting, you'll need to use an ORM. You can define your data fields in the ORM with the appropriate object type, something like:

Code (CSharp):

1. public class User
2. {
3.     public string ID { get; set; }
4.     public string NAME { get; set; }
5.     public string AVATAR { get; set; }
6.     public string FLAG { get; set; }
7.     public long CREATED_AT { get; set; }
8.     public long UPDATED_At { get; set; }
9. }

Then you can pull the data like:

Code (CSharp):

1. var results = dbManager.Query<User>("SELECT * FROM USERS");

 

It will work on Linux, but not web player. The reason is that the sqlite database file is a physical file that has to reside on a file system to be accessed. Web players don't allow file access for security reasons.

 

You sure its not possible now..

?

 

Looks like that is using a 3rd party library to somehow get the webplayer to persist data. Unfortunately, I cannot include 3rd party libraries due to the licensing restrictions. The source code is included with SimpleSQL if you want to make modifications, however.

 

So is there no way around it.. like being able to use simplesql during runtime and save load handled outside of it?

 

None that I am aware of.

 

Damn that is a bugger.. would have prefer to stay using sql lite been considering this https://forum.unity.com/threads/bg-...ets-syncing-codegen-save-load-support.529740/ has some webgl limitations aswel..

(╯°□°）╯︵ ┻━┻ webgl

 

Hey @echo17,
I changed your source in the following ways,
Instead of using a

MonoBehaviour

for the

SimpleSQLManager

I have used

ScriptableObject

. This helps in having to configure just one Asset that everyone can reference and doesn't stay dependent on Scenes and GameObjects. Refactored the Editor Scripts for the same.
Had to create a separate class to handle the

OnApplicationQuit

to handle Closing and Disposing of the

SimpleSQLManagers

.
Ping me if you want to take a look at it.

 

My plugin works https://u3d.as/3ka

 

last updated in 2016?

 

exactly, stable . It's pure C# (no dll) and it works even on WebGL

 

actually - you right, I'll update it, there is non critical issue which I promise to update, but mean while there is nothing to change to do empty update.

 

You drag and drop the Fantasy database file. What is the file format of the Fantasy file?
Can I use .CSV file?

 

SimpleSQL is a wrapper for SQLite, so the database files are sqlite formatted databases. CSV files will not work. You will need to rename your files with a .bytes extension so that Unity can use it in the inspector for the component. Please see the user manual for more information: https://support9569.wixsite.com/echo17/simplesql

 

Hi there! Where does this asset store the DB file on mobile platforms?

 

Please see this FAQ:https://echo17.proboards.com/thread/2/faq?page=1&scrollTo=9

You can also override the path that is being used, but this won't work on all platforms, notably iOS. Android can use the override path to store to a remote drive, but you have to make sure that drive is accessible to all users.

 

Hi @echo17,

How do i use sqlite db which i downloads on runtime? SQLManager only seems to take TextAsset and not a File or String.

Please advise
Thanks

 

Please see this FAQ: https://echo17.proboards.com/post/14/thread

 

Awesome, thank you for your prompt reply

 

Hi @echo17
Could you please provide an overload for Execute that takes List<object> instead of params object[].
Reason is im using pooling a lot and passing arguments in generic way and reusing the same List<object> would b really good for performance.
Thanks

 

That is actually a design limitation of C#. See this link: https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/params

You could always cast your generic list as an object array using Linq, like:

Code (CSharp):

1.  
2. var values = new List<object>();
3.  
4. values.Add(102);
5. values.Add("MyNewWeapon");
6. values.Add(11);
7. values.Add(22);
8. values.Add(33);
9. values.Add(1);
10.  
11. dbManager.Execute("INSERT INTO Weapon (WeaponID, WeaponName, Damage, Cost, Weight, WeaponTypeID) VALUES (?, ?, ?, ?, ?, ?)", values.ToArray<object>());

 

Sorry for not being clear earlier,
This is your current function signature

Code (CSharp):

1.     public int Execute(string query, params object[] args)
2.  

If you could add an entirely new method and sub methods that use List<object>

Code (CSharp):

1.     public int Execute(string query, List<object> args)
2. public virtual SQLiteCommand CreateCommand(string cmdText, List<object> ps)
3.  

This will actually allow me to pass a List<object> directly and avoid toArray which will cause unnessary garbage.
and once command is complete i can keep using the same List for other purposes (pooling)

Thanks a lot

 

Thanks for the clarification. I've added the new Execute and CreateCommand overloads that allow you to pass a list of objects. You'll find it in SimpleSQL v2.16.0+

 

Thank you very much , really appreciate

 

Hello, purchased your plugin some time ago but haven't used it yet. Now Im wondering if I can use it on my next project that will probably need to be ported to Nintendo Switch, is there a plan to support Nintendo Switch in the future, as I can see its currently not supported?

 

I don’t have access to the Nintendo Switch development environment, unfortunately. You’d probably have to reach out to the community to see if anyone has tried to use SimpleSQL on that platform.

 

Thanks, but found this looks like its not possible to discuss that here because of NDA?

https://forum.unity.com/threads/nintendo-switch-and-sqlite-or-other-database.1244203/

 

Possibly. Not really sure, haven't worked with Nintendo before.

 

Hello, I am having some trouble running SimpleSQL with System.Data.

When I install the plugin and enable System.Data via the "Optimize" dialog, I get the following error. It seems that System.Data is defined both in your plugin as well as somewhere in Unity.




I am running Unity 2021.3 LTS (specifically, Unity 2021.3.29f1).

My player settings are set as follows:


The only other option instead of ".NET Standard 2.1" is ".Net Framework".

To see if the SimpleSQL plugin was causing the problem, I switched back to "No System Data" in the Optimize dialog.

And sure enough, the System.Data error went away AND System.Data is still a valid class (presumably via the other DLL that seemingly ships with Unity).

Now, however the problem is that all the SimpleSQL System.Data related classes (such as
SimpleSQLManager_WithSystemData etc.) have "disappeared".

So, while System.Data itself does not now have conflicts. There seems to be no way in this configuration to actually access SQLite with the System.Data approach in SimpleSQL. Is there a way around this seeming catch-22?

ps) On a separate note, is there a way to get access to the "raw" SQLite connection?

thanks,

 

This is an error in the VS editor, but it won't affect the actual Unity runtime. For example, I just made this code:

Code (CSharp):

1.     using UnityEngine;
2.     using UnityEngine.UI;
3.     using System.Collections.Generic;
4.     using SimpleSQL;
5.     using System.Data;
6.  
7.     public class SimpleQuery : MonoBehaviour
8.     {
9.         public SimpleSQLManager_WithSystemData dbManager;
10.         public Text outputText;
11.  
12.         void Start()
13.         {
14.             DataTable dt = dbManager.Query("SELECT * FROM Weapon");
15.  
16.             foreach (DataRow dr in dt.Rows)
17.             {
18.                 Debug.Log(dr["WeaponName"]);
19.             }
20.         }
21.     }

And it runs fine, with the output:

Code (CSharp):

1. Sword
2. UnityEngine.Debug:Log (object)
3. SimpleSQL.Demos.SimpleQuery:Start () (at Assets/SimpleSQL/Demos/Scripts/01_SimpleQuery/SimpleQuery.cs:20)
4.  
5. Mace
6. UnityEngine.Debug:Log (object)
7. SimpleSQL.Demos.SimpleQuery:Start () (at Assets/SimpleSQL/Demos/Scripts/01_SimpleQuery/SimpleQuery.cs:20)
8.  
9. Axe
10. UnityEngine.Debug:Log (object)
11. SimpleSQL.Demos.SimpleQuery:Start () (at Assets/SimpleSQL/Demos/Scripts/01_SimpleQuery/SimpleQuery.cs:20)
12.  
13. Bow
14. UnityEngine.Debug:Log (object)
15. SimpleSQL.Demos.SimpleQuery:Start () (at Assets/SimpleSQL/Demos/Scripts/01_SimpleQuery/SimpleQuery.cs:20)
16.  
17. Sling
18. UnityEngine.Debug:Log (object)
19. SimpleSQL.Demos.SimpleQuery:Start () (at Assets/SimpleSQL/Demos/Scripts/01_SimpleQuery/SimpleQuery.cs:20)
20.  
21. Staff Of Herill
22. UnityEngine.Debug:Log (object)
23. SimpleSQL.Demos.SimpleQuery:Start () (at Assets/SimpleSQL/Demos/Scripts/01_SimpleQuery/SimpleQuery.cs:20)
24.  
25. Fire Ring
26. UnityEngine.Debug:Log (object)
27. SimpleSQL.Demos.SimpleQuery:Start () (at Assets/SimpleSQL/Demos/Scripts/01_SimpleQuery/SimpleQuery.cs:20)

You could always try removing the System.Data reference from the source and recompile. I haven't tried that, so I'm not sure what the side effects would be. Maybe if you upgrade that to 2.1 in the source that would get rid of the harmless errors. I had to put the reference in the source for older versions of Unity that were not shipping with the System.Data dll file.

You'd need to modify the source to get access to the underlying sqlite library code. Please see this FAQ for how to do this: https://echo17.proboards.com/thread/2/faq?page=2&scrollTo=23