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.