SimpleSQL - SQLite integration with Unity3D

Alright, I think I've ironed this bug out. Check out version 1.6.3 available in the Asset Store now. I also tested on the Android device itself and it worked for me. Let me know how it goes for you!

 

Working on the editor, but not yet on the device. I need to investigate this.

 

Try building the demo that comes with the plugin onto an Android device. I was able to run the simple query scene successfully. If that works, you can rule out SimpleSQL and gradually narrow down what is failing in your particular setup.

There were a lot of changes in Unity 5 which could easily account for problems with other plugins or custom scripts.

 

I tried to build the 07_SimpleTable, but it's only saying "Note that the database manager...". When in editor it shows the database table.

But now I think I'm onto something. Remember when I said I deleted the SimpleSQL and downloaded it again? Well I did that, and I also deleted the plugins folder. This sounds like why it's working on editor and not on the device. So when I copy the folders Plugins and Editor to the root it says:

Code (csharp):

1. The GUID for Assets/Plugins/SimpleSQL_Runtime.dll is already in use by Assets/SimpleSQL/Plugins/SimpleSQL_Runtime.dll. Assigning a new guid.
2.  
3. The GUID for Assets/Editor/SimpleSQL_Editor.dll is already in use by Assets/SimpleSQL/Editor/SimpleSQL_Editor.dll. Assigning a new guid.

And the I try to build, an error:

Code (csharp):

1. Found plugins with same names and architectures, Assets/SimpleSQL/Plugins/SimpleSQL_Runtime.dll () and Assets/Plugins/SimpleSQL_Runtime.dll (). Assign different architectures or delete the duplicate.
2. UnityEditor.AndroidPluginImporterExtension:CheckFileCollisions(String)
3. UnityEditorInternal.PluginsHelper:CheckFileCollisions(BuildTarget) (at C:/buildslave/unity/build/Editor/Mono/Plugins/PluginsHelper.cs:25)
4. UnityEditor.HostView:OnGUI()
5.  
6. Plugins colliding with each other.

Now what do I do? Do I delete the Plugins and Editor folders under the SimpleSQL folder?

 

It seems that after I delete the Plugins and Editor folders to prevent from "Plugins colliding with each other" it brakes.

 

I would wipe out the SimpleSQL folder entirely. Also, probably better to kill the "Plugins/Android" folder as well since it has the Android sqlite library located there. You may have to delete the folders in your OS's file system if Unity won't let you. Then redownload SimpleSQL and see if that works. Unity has some issues when updating assets, so we developers often recommend wiping out the existing plugins before downloading new ones. (And of course, always back up your work before upgrading!)

Try creating a new project with just SimpleSQL and build that. If it works, then it is definitely a Unity Asset Database glitch that is causing the issue in your project.

 

Ok, I tried that. I started a clean project and it says: Plugins colliding with each other.

Can you have folders /Plugins and /Editor at two place? In the root and in the SimpleSQL folder and it should work that way?




I also tried _not_ to copy the /Plugins and /Editor folders to the Asset root like it says in the SimpleSQL instructions (pdf)

"Your final game project layout should have the two dll's in their respective directories:
SimpleSQL_Editor.dll in the Editor directory and SimpleSQL_Runtime.dll in the Plugins directory"

This will build the game without "Plugins colliding with each other." error, but it's not working on the device. Only reading the "Note that database..." when it should show the "Weapons, name: sword" etc. that are on the example project.

 

You shouldn't try to move the SimpleSQL folders around as this will definitely cause problems since the plugin has to extract the appropriate libraries. It is looking for the folder structure it comes shipped with in order to find the right folder to extract to. If you move the files around, it will extract to a different location leaving you with two copies of the runtime library.

Yes, you can have multiple Editor and Plugins folders in your project. Unity will recognize them all and process the scripts contained in the correct order. See these links for more details:
http://docs.unity3d.com/Manual/ScriptCompileOrderFolders.html
http://docs.unity3d.com/Manual/SpecialFolders.html

Sorry, but I can't replicate this issue. I am able to successfully build, deploy, and run a demo scene on my Asus tablet. Perhaps it has something to do with your Android settings, or the version of JDK or the Android SDK, or your platform, or any other number of factors particular to your setup.

After you create a new project and import SimpleSQL (without moving any files), go to "Tools > SimpleSQL > Options" switch to any other platform, wait a bit for the spinning wheel to stop, then switch to Android and wait again for the spinning wheel to stop. Immediately set up your build from that without changing anything. If that fails, it is likely something to do with your device, build settings, or development machine because I am not able to get any errors with this setup.

 

I think I found a solution for the problem on my end.

This morning I read the release notes of Unity 5 and noticed this:

https://unity3d.com/unity/whats-new/unity-5.0

Unity editor is now 64-bit!

• Note that this affects native plugins used in the editor, which now also need to be 64-bit.

Then I installed the 32-bit version of Unity 5 instead of 64-bit version that I had before, imported SimpleSQL from Asset Store and it worked right of the bat.

Maybe you can replicate the issue if you download the 64-bit version of Unity 5?

 

I'm using the 64 bit version on both pc and mac. I built the android test using 64 bit mac.

 

I build iOS version with 64bit Mac and the Android version with 64bit PC/Windows 7. It seems that the 32bit PC/Windows 7 build doesn't always work either. I couldn't build working version twice, restarting Unity helped but it was very random. I also tried to change the options as you recommended. Frustrating because I didn't get any errors, then tested with device and sometimes it worked and sometimes not.

But I managed to ship updates for my game as 32bit android versions. As I said before there was no problem with Mac and iOS. Hopefully coming Unity 5 patches will fix this in the future. I didn't do any changes to the database/SimpleSQL. So it could be that there are some issues with Unity 5 and Windows version.

 

Very possible. Thanks for the continued feedback. I'm sure there will be some bumps along the road with the new version of Unity. Hopefully they'll smooth out soon.

 

Dear echo,

I'm planning to buy your plug in, but afraid that it will not work with IL2CPP, could you please confirm that it is working flawlessly?

Regards

 

Yep, it works with IL2CPP so long as you are using Unity 4.6.3+. There was a bug in Unity in earlier versions that wouldn't allow external libraries which SimpleSQL uses. 4.6.3 fixed the issue.

 

Hello echo15,
I would like to know how to access database in all scene. I tried to write database in a scene, but i cannot retrieve data in another scene. Thanks

 

The data is persistent on the device's drive, so you can access it from all scenes (and even outside of Unity). Be sure you create a SimpleSQL database manager in all scenes where you want to access the data and link the database template to all the managers. Also, please read these FAQs since they might be of use in your situation:

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

 

@echo17 if I want to export for iOS and Android. Should I keep the optimization on Android as it the larger one or shall change it based on what I'm building for?

 

The platform settings are not just optimizations, but sometimes necessary libraries. For instance, when you switch to the Android platform, it extracts the android sqlite.so package from the SimpleSQL editor dll into the project. I don't know if this package will cause errors on iOS or not. If you aren't getting errors, then you should be okay to leave it, otherwise you'll need to switch the platform based on what you are building.

 

Thanks, I Really Really Like your plugin... It is Very "Simple"

 

Thanks for the kind words, glad you are enjoying it!

 

Hi, great product!

Just wanted to drop a comment that might help others:
I was using the sqliteadmin program suggested in the documentation, but it saved the file in some wrong version which gave me the error that the file was either encrypted or not a database. After i switched to the firefox plugin, everything worked fine.

So if you get that error try another admin tool.

Cheers.

 

Thanks for the tip! I've always used the Firefox plugin so I didn't realize other administrators gave that error. Appreciate the heads up...

 

Hi Echo17,

Great product and a very promising ORM so far...

Also having the same issue with SQLite Administrator as above, are there any plans to look into this. I have used SQLite Administrator when using SQLite databases extensively in other .NET projects without any problems. Not sure why SImpleSQL sees the database as corrupted....

I would really rather not have to install Firefox and the plugin specially just to edit my SQLite databases for use in SimpleSQL (seeing as I have learned and successfully used SQLite Administrator for so long)

Thanks,

Chris
London

 

It is definitely odd, but SimpleSQL is just a wrapper around the sqlite libraries, so the compatibility is probably something with the version of the sqlite dll. I'm not sure what SQLite Administrator is creating, but SimpleSQL attempts to be compatible with older versions of the library so there the administrator is probably creating a newer version.

SimpleSQL extracts a pre-made empty database file from the editor dll when you select the create new database. The database file was made in Firefox sqlite admin.

Since this is really outside the scope of SimpleSQL, you might try contacting the developers of SQLite Administrator to see why you can't open a (possibly) older version of the sqlite file. Be sure to send them an example database file for testing as that will probably help them out.

 

Just to be sure, does your extension supports Arabic language?

 

I haven't tried specifically with the Arabic language, but I have tested with Chinese and Russian. You just have to be sure to use parameters in your sql strings instead of string concatenation to make it work properly. Please see this FAQ for more information:

http://www.echo17.com/forum/index.p...15df0bd7777ce8911dc9c&topic=257.msg399#msg399

 

Following up on this, I made a test on english letters and I still get empty string, please check my code and advice.

First the table definition class:

Code (CSharp):

1. using System;
2. using System.Collections.Generic;
3. using System.Linq;
4. using System.Text;
5. using SimpleSQL;
6.  
7. class wordlist
8. {
9.     public string full_word { get; set; }
10.  
11.     public string letter_1 { get; set; }
12.  
13.     public string letter_2 { get; set; }
14.  
15.     public string letter_3 { get; set; }
16.  
17.     public string letter_4 { get; set; }
18. }
19.  

second, List definition and query:

Code (CSharp):

1. public SimpleSQL.SimpleSQLManager dbManager;
2.  
3. List<wordlist> Words_List = new List<wordlist>();
4.  
5. Words_List = dbManager.Query<wordlist>("Select * From wordlist2 limit 5");
6.         Debug.Log(Words_List.Count);
7.         Debug.Log(Words_List[1].full_word);

and by executing above code, I get this in the Console:

1- 5
2- Null

clearly the list is receiving the values, thats why it has 5 items.

but when printing one of the values I get Null.

 

I'm not sure what would be causing this without seeing your data. I typically avoid using "SELECT *" because it hides the columns from you as a programmer which can be tricky to debug. I usually explicitly list each column to be sure that I am matching the ORM I have set up. It may be that the collumn "full_word" does not exist, but I can't be sure without seeing a project example.

If you would like me to take a look, please see this FAQ: http://www.echo17.com/forum/index.php?topic=257.msg950#msg950

 

Is there any continuation on supporting encryption?

 

No, there are no plans to incorporate encryption into SimpleSQL. However, the plugin comes with the complete source code, so feel free to modify it to meet your needs.

 

Thinking about buying that tool...

What happens to the working database (parameter "overwrite if exist" is unchecked) if I UPDATE my application via Appstore? Is that the same behaviour like unistalling/reinstalling and I loose all the data stored so far?

If I provide some data in the project database as well, will that data be copied over to the working database as well in the very first run ((parameter "overwrite if exist" is still unchecked)?

 

When you update an application via AppStore, only the executable is overwritten. The user's data is preserved in the sandbox environment of the device. As long as you keep the "overwrite if exists" unchecked, the user's entered data will always be preserved.

Yes, that is correct. The first time the application is run, the project database will always be copied over to the working directory (sandbox on mobiles). Every time the application is run after that, the only time the database is copied over is if the "overwrite if exists" is set to true.

Generally speaking, the only time you'd want to overwrite the database is if the data is static, like application settings. You would never want to overwrite the data if the user has modified at runtime. For dynamic data like this, you would need to create an upgrade process to keep the runtime database in sync with the project database structure. This can be done in a series of steps, modifying the database as necessary for each version until the database in the working directory matches what you have in the project, thus preserving the data itself. Note that you can have more than one database in a project. I usually keep two in my applications: one for static data (which is always overwritten) and one for dynamically entered user data (which is never overwritten).

Have a look at this FAQ for more information: http://www.echo17.com/forum/index.php?topic=257.msg453#msg453

 

Hi,

What is the best practice for performing an UPDATE if a PK exists, INSERT if not (using text keys)?

My scenario:

I'm using two databases, StaticDB and SaveDB. I have a 'Character' table in both databases; entries in StaticDB.Character define all persistent characters, while entries in SaveDB.Character store updated versions of character data based on player progress.

On game load I merge the data into a single list. If an entry with a matching CharacterID exists in SaveDB.Character, I add this to the merged list; otherwise I add the entry from Static.Character. I then use the merged list to spawn my character objects. This is working fine for loading.

When saving, I need to UPDATE the existing record if it exists, or INSERT a new one. If possible I'd like to stick with SimpleSQLManager's UpdateTable and Insert methods rather than executing SQL strings so I don't have to update my queries every time my data model changes.

Thanks!

 

I would call QueryFirstRecord, looking for the existing record. If the output parameter "recordExists" is true, then call your UpdateTable function, else call the Insert function. Have a look here for details concerning the "QueryFirstRecord" function: http://echo17.com/support/simplesql...anager.html#a13e3805999c4a53391ce3b2937486504

If you decide to use queries, have a look at this link. I have not tried it myself, so I'm not sure if it will work, but it seems to be pretty much what you are looking for: http://stackoverflow.com/questions/3634984/insert-if-not-exists-else-update

 

Hi!

How would you approach a situation where I would like to allow users to share their databases between each other? ie. I'd give users an UI to modify content of the database to an extent, and then allow users to load content others have shared.

Two methods I thought of:

a) Simple way would be to replace the working copy of the database with DB that user selects from available databases. I do not know if this could cause some issues.

b) A bit logner road: Instead of users exporting the whole database file and sharing that, they'd export content of the databse (in XML for example) and share that. Then I'd build interfaces to import the data to the local database when user imports shared XML file.

Would you have any ideas on what would be a good practice to approach this? The DB structure does not change, only the content.

 

The two methods you described would probably work well. You could also upload data to a server, but that would require a server database, web hosting, and scripting on the server side to process requests. You would also need to create scripts in your application to interface to this server since SimpleSQL is a local solution only.

I have a very similar situation to this with one of my iOS apps called "iVelopes" (https://itunes.apple.com/us/app/ivelopes/id350035867?mt=8). It is a budgeting app that allows users to share the same financial data so that it is always in sync. For this, I use Dropbox, uploading the entire database when one user makes a change, and downloading the entire database when another device detects a change has occurred. This assumes you can share the entire database, but for this app that is what I needed. For this app, each user signs in with the same Dropbox credentials so that they access the same Dropbox account where the cloud instance of the database is stored.

Keep in mind, your project can have multiple databases, not just one. So you could store private info in some databases, and publically overwritable databases could be uploaded / downloaded without worrying about stomping on other user's data. Just note that as the database size grows, your users will have to use more bandwidth to transfer the data (which could be prohibitive on mobile data plans), unless you have some kind of grooming or archiving process on the publically shared database.

In case you are interested, I've also created a Dropbox wrapper for Unity called "Boxit", which you can find here: https://www.assetstore.unity3d.com/en/#!/content/4096

 

Thanks for the quick reply! Glad to know both approaches work. I won't be needing any online functionality initially, so I'll go with users downloading and installing the content to proper location themselves. Then I plan to use Resources.Load() to grab the list of available database files and allow the user to choose the content that is copied as active DB. This sort of old school way to distribute user created content should suffice.

 

Be aware that Resources.Load only loads internal files from the compiled project build (embedded resources, in other words). It won't allow you to load resources from a file system location. For that you will need to use System.IO .NET file functions and structures like FileInfo, DirectoryInfo, etc.

Also, SimpleSQL by default only reads and writes to the application's persistent data path. This is in order to be compatible with all platforms that Unity supports. If you need to access files outside of this path, you will need to tweak the source code in the Initialize function of the SimpleSQLManager class. This will only work on computer platforms like Mac and PC since mobile devices have very strict limitations on where you can access data. Please see this FAQ for information on modifying the source code: http://www.echo17.com/forum/index.php?topic=257.msg2183#msg2183

 

SimpleSQL Sale 50% off through June
for Unity Level 11

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

 

@ echo17,

If I use Simple SQL but not the ORM stuff (I will use it, but for the purpose of this question) and only the open. stuff, is the resultant project still compatible with Android, iOS etc? What about the dlls.?

[edit] Also, any chance of a 50% sale on SimpleSQL in the ordinary asset store (not just Level11)?

 

I'm not sure what is meant by "open. stuff", but yes, you can use SimpleSQL without the ORM. There are two other options besides ORM: System.Data or SimpleSQL's really light-weight table and row classes. I would highly recommend using ORM, however, because it makes coding much simpler. You don't have to cast every field to the appropriate type which saves on typing and errors. Also, you get the added bonus of intellisense. Once the classes are set up you will save a ton of time using them.

The dlls are managed code, so they will work on all versions of Unity.

Unfortunately, that is out of my control. We can submit our plugins to the store for sales, but it is up to the Asset Store staff to determine where, when, and if the product goes on sale.

 

@ echo17,

Many thanks. Well, I had to ask. will be buying this in a week's time when next paid. don't worry, having read the manual, i am sold on ORM...

 

@echo17
I couldnt wait and so bought it now.

I have another question - let's say I use largely ORM SimpleSQL and some nifty UI to create an application in Unity to manage, create and edit SQLITE databases (with option to save out as .byte) and/or I simply want to save out the runtime sqlite database and copy it over the Project equivalent - how would I go about doing that?

 

That's a good question. I suppose you would use the System.IO classes to copy the file to the location that you want. SimpleSQL runtime databases are stored in the device's working directory (Application.persistentDataPath). I would imagine something as simple as File.Copy(...) should do the trick. http://www.echo17.com/forum/index.php?topic=257.msg518#msg518

Just keep in mind that if you are planning on making and selling a plugin that does this within Unity you won't be able to include SimpleSQL since you can't distribute the plugin with your own plugins. If you are creating an application (compiled -- source hidden) or the plugin is just for your own use, then all is well.

 

echo17,

Panic not - the idea was basically for my own use or, in the highly unlikely event it was useful, a compiled version...

You can see how exporting out the altered database(s) would be v useful...

 

Indeed

 

@echo17

- If you could post some further c# script on how to export the runtime sqlite database(s) out that would be incredibly helpful.

- Also, with ORM, I've been wondering how I might, with a method, get the string for the path/reference of an object or gameObject to make it easier to save that reference as a string field in a SQLITE database. Is that possible? If so, could you post some C# script?

- I was familiar with SQL statement syntax and structure before coming to Unity 5.x. In SQL statements, what's with the '\'s and the additional "s??? - see for instance pp 51-52 of your manual:- sql = "CREATE
TABLE \"StarShip\" "+ "(\"StarShipID\" integer PRIMARY KEY NOT NULL ," etc.

Also - sql = "ALTER TABLE \"LocationMapping\" ADD COLUMN \"NewField\" INTEGER";

Why not just

sql = "CREATE TABLE Starship(StarshipID INTEGER PRIMARY KEY NOT NULL, StarshipName TEXT)" ? ; and ???

sql = "ALTER TABLE LocationMapping ADD COLUMN NewField Integer"; ???

- When I've used SQL statements before it wasn't with the '\'..? Why are they included?

Any guidance gratefully received...

 

Another question, following on or building on the others above - let's say I've created an RPG and I want to be able to allow for multiple save files (databases) that the runtime can save out to and then read from later. How best to do that if the system is only looking at the [temporary] sql database in runtime? What the happens if the player closes the 'game' and comes back in later and wants to load in the current position in their game? (hope you understand what I'm getting at?)

 

This would just be a System.IO file copy operation. https://msdn.microsoft.com/en-us/library/system.io.file(v=vs.110).aspx

Roughly:

Code (CSharp):

1. string fromPath = Application.persistentDataPath + "/" + workingDatabaseFileName;
2. string toPath = Application.dataPath + "/" + projectDatabasePathAndFileName;
3.  
4. File.Copy(fromPath, toPath, true);
5.  

Note: I used the true parameter in File.Copy to overwrite the existing file. You will need to be sure the project file is closed before copying over it.

Check out this FAQ: http://www.echo17.com/forum/index.php?topic=257.msg939#msg939

Since the sql string is enclosed in quotation marks, the only way to include qoutation marks within the string is to use an escape sequence. So if you want something like ALTER TABLE "LocationMapping" ADD COLUMN "NewField" INTEGER inside a string, you have to escape the quotes like the example you showed. If sqlite doesn't need these fields surrounded by quotes, then you can leave them out.

Have a look at this link for more info: http://stackoverflow.com/questions/14480724/escape-double-quotes-in-string

 

I'm not sure what you mean by "temporary" database at runtime. The runtime database located at the application's persistentDataPath (the working directory) is the permanent database. Any changes made to this database will persist. Next time you run the game or access the database anything you've done to it previously will still be there. Have a look at this FAQ:

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