SimpleSQL - SQLite integration with Unity3D

Yes

Not that I know of, but I mostly just use queries and modification commands.

No, it is a compiled dll, but can still be run on the Indie (free) versions of Unity for PC, Mac, and iOS. Android will require the pro version of Unity to run.

It is a wrapper around the sqlite.dll. So there will be some slight overhead when calling functions, but mostly that is just passing parameters on to sqlite. The majority of SimpleSQL's code is in simplification of connecting to, querying, and updating databases. It also frees you up from having to do a lot of post build work to get your databases to work in xcode.

Thanks, hope you like it!

 

I've added a FAQ with information on how to set up your queries with Javascript here:

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

You're getting this error because Unity Javascript files don't see C# files unless those files are placed in the "Standard Assets" folder. See these links for more information:

http://forum.unity3d.com/threads/29703-Javascript-error-does-not-denote-valid-type
http://docs.unity3d.com/Documentation/ScriptReference/index.Script_compilation_28Advanced29.html

 

Right. I was having problems where i'd get blank/default data records back from the database. Turning on the rename feature fixed this (renamed to *.sqlite). A bit odd!

 

That is strange. I've always set my rename to *.sqlite, so I guess I never came across those oddities. Thanks for the info!

 

I'm running Unity3d Pro 4.x
New Project
Imported Simple SQL
Moved Editor and Plugins folder to assets as per manual.
Did Build All.
Received missing reference error to UnityEngine every time "SimpleSQLManager" is referenced.


Full Exception:

P.S. Yes UnityEngine is referenced, I presume the error is a red herring for some sort of configuration error.

 

Hey there,

I replied to your post and email, but I'll add my thoughts here as well in case anyone else is getting the issue. You might try to change your configuration settings from the SimpleSQL menu. Set it to some other platform (like Mac if you have it set to PC) and then set it back again. Wait a few seconds between switching as it takes a little bit for Unity to copy the appropriate dlls into your folders. You'll see the spinning wheel at the bottom of Unity while it copies.

 

I found the problem causing my issue. I was running Unity 4.1.1x , Apparently they caused a bug by doing some sort of strong signing of the UnityEngine.dll and UnityEditor.dll which causes the error I was getting when Compiled assemblies reference them.

Solution: Update Unity to at least 4.1.2f1


Patch notes from that update:

 

Hi, I'll need this plugin in the future but I would like to see it working.
Website and also the instructions are offline.
A video would be nice too.

 

Looks like the website is working now. My hoster may have been temporarily down for maintenance or something. I'll add the video idea to my list of items to look into. Thanks!

 

Ah, yes. I had forgotten about the Unity bug. This happened in one of my other plugins and I just had the person roll back to Unity's last stable release. Glad they sorted this out and got a patch. Thanks for the feedback!

 

I just wanted to say, this has been one of the best purchases I have made. Great job on this!

 

Just went to your website... again down.
May I suggest you to change your provider?

 

Thanks, glad you are enjoying it!

 

Not sure why it goes down, seems to be back up again. Honestly, I am on the site almost constantly (the forum) and have not seen it down once.

 

Hello echo17!
I've been looking for a solution to a new game i 'm designing and i found two choices, your SimpleSQL and the SQLiteKit.
To be honest, i want to buy your plugin. I know your stuff for a very long time now and i know that you are probably one of the most respected developers. And of course, the best support available.

The thing is, SQLiteKit has two advantages over your plugin SimpleSQL which are: Playmaker support and android basic support as well.
Do you plan to add this features in a near future as well?

 

Thanks for the kind words!

Playmaker support has been on my list for a while, and probably due for an implementation. One tricky part of doing this is that SQL is a very dynamic language. Providing a limited interface to interact with your database may actually hinder you, as opposed to being able to fully code a SQL query. I'm a bit biased here, though since I'm a DB programmer by profession in my "real" job I'll take a look and see what all might be possible and if it would be beneficial.

Android basic will be a bit tougher. The way the project is packaged on this platform doesn't allow me to use the same tricks to get around Unity's unmanaged dll for basic license problem. I'll have to look into this, though I've spent literally months already to no avail. Haven't given up yet, though

Sorry I can't give you a definite yes or a timeline. Both of these issues require some R&D, which is always hard to estimate feasibility and duration.

 

Those features would be so great! I will keep an eye in this thread and future updates
BTW, i will send an email with a simple question if you could help me with a "thing".

Thanks for your attention.

 

Found the problem:

http://www.echo17.com/SimpleSQL.html

http://www.echo17.com/simplesql.html

You could have tried your link in the first post if somebody tells you the website is down

 

Oh, those pesky casing issues Thanks for pointing this out!

 

So I was trying to find a way to check if a value is already present on a table without iterating trough all the table values.

After a long time I was able to do it with Java. I am a sql newbie. It might be usefull to someone, so i will post it here.

var valorV : List.<QueryAnimalUser> = dbManagerUser.Query.<QueryAnimalUser> ("SELECT COUNT(*) AS answer FROM animalStatsUser WHERE animalCode= 70001");

var valorI : QueryAnimalUser;
valorI = valorV[0];
var result : int = valorI.answer;

if(result > 0){
// register already exists
}

else{
//do something (insert register)
}


Even if there is not a column named "answer" in your database, the get/set must be declared as an "int" on your C# file so Simple SQL can return the value trough it.

Hope it helps someone

 

Sounds like a good way to do it, thanks for the info. FYI, since you are just wanting to return a single record instead of a list, you can use the function "QueryFirstRecord" instead. It will return a single instance of your generic table definition filled with the first record. It also passes back if the record exists in an "out" parameter so you can quickly see if you have any data.

Have a look at the API docs for this here: QueryFirstRecord Documentation

 

I didn't knew you had API documentation.... I feel really dumb. Is this new?

Yep your right, I did not knew the QueryFirstRecord. In my case what I did was enough beacuse there can be only a single record since the AnimalCode acts as the Primary key

Your Plugin is great Echo, as of now with a little efford since i had no SQL background, I am able to do with it everything my project needs.

 

Yep, it is fairly new, no need to feel dumb

That's great, glad it is working out for you!

 

Hi there,

I just bought SimpleSQL. Looks great. I am wondering tho, is it possible to store a GameObject variable inside the database?
I appologise if this was already answered. I couldn't find the answer.

Regards

 

Thanks for the kind words!

You can store integers, floats, and strings in a SQLite database (see this link for more information on SQLite's specs: http://www.sqlite.org/datatype3.html).

You can store the properties of a gameobject, like the transform position / rotation / and scale. You can store things like the gameobject's name, etc. Is there something specific you are trying to keep track of?

 

After 3 failed attempts of writing and implementing my own sqlite (was so close all three times!) I gave up and ordered this, so far I love it! My time was worth more than this cost!

Well, I spoke too soon... I am getting the same error I received the last two times I tried to implement my own SQLite so I must be doing something wrong:

I am using the standard weapons class and when I compile and run I get this error. The sqlite3.dll is there as well as the runtime dll both from the original asset. ok I have copied the database file (.bytes file) in both the root of the game and in the assets and in plugins as well as making sure the locations contain sqlite3.dll and other runtime.dll files that came with the asset.... still no go.

I am going to dig and see if I can find an answer but wanted to post here since I have already spent 2 days on this.

 

Looks like recompiling with x86 architecture instead of x86_64 did the trick. That dllnotfound error is usually so difficult to nail down, glad I got it quick this time! Of course now I wonder if that was all that was wrong with my version of sqlite!!!! oh well... I am moving forward! Thanks!

 

Awesome, glad you found the solution. Thanks for posting here and for the kind words!

Happy querying

 

Should this work?

Code (csharp):

1. int lastObjectID = dbManager.Execute("SELECT * FROM objects WHERE objectid = (SELECT MAX(objectid) FROM objects)");

I will keep digging but it is not giving me a very good error, simply stating:

 

The "Execute" function is used for non-query processes like UPDATE, DELETE, or INSERT. For SELECT, you'll need to use "Query" instead. To store the results of a query, you'll need to use one of two methods:

1) ORM (Object Relational Mapping): create a class structure to hold the query results. Store the results, then read through them to get your information

The ORM class to hold the data might look something like this (note I just made up some fields since I don't know what is actually in your table):

Code (csharp):

1.  
2.  
3. using SimpleSQL;
4.  
5. public class Objects
6. {
7.     [PrimaryKey]
8.     public int ObjectID { get; set; }
9.  
10.     public string SomeStringData { get; set; }
11.     public int SomeIntData { get; set; }
12. }
13.  
14.  

You could then query and store the information like this:

Code (csharp):

1.  
2.  
3. using UnityEngine;
4. using System.Collections.Generic;
5. using SimpleSQL;
6.  
7. public class QueryORM : MonoBehaviour
8. {
9.     public SimpleSQLManager manager;
10.  
11.     void Start ()
12.     {
13.         List<Objects> objects = manager.Query<Objects>("SELECT * FROM Objects WHERE ObjectID = (SELECT MAX(ObjectID) FROM Objects)");
14.  
15.         foreach (Objects obj in objects)
16.         {
17.             Debug.Log(obj.ObjectID + " " + obj.SomeStringData + " " + obj.SomeIntData);
18.         }
19.     }
20.    
21. }
22.  
23.  

2) DataTable: store the results of your query directly in a System.Data.DataTable and parse the information. For this method, you'll need to be sure you have selected System.Data from the SimpleSQL options window (go to the Unity menu SimpleSQL > Options). Your database manager will need to have the "SimpleSQLManager_WithSystemData" script attached instead of the "SimpleSQLManager" script. Note that this method does not require you to set up a class to map your data, you just have to parse it yourself:

Code (csharp):

1.  
2.  
3. using UnityEngine;
4. using SimpleSQL;
5. using System.Data;
6.  
7. public class QueryDataTable : MonoBehaviour
8. {
9.     public SimpleSQLManager_WithSystemData manager;
10.  
11.     void Start ()
12.     {
13.         int rowIndex = 0;
14.  
15.         DataTable dt = manager.Query("SELECT * FROM Objects WHERE ObjectID = (SELECT MAX(ObjectID) FROM Objects)");
16.         foreach (DataRow dr in dt.Rows)
17.         {
18.             Debug.Log("Row: " + rowIndex);
19.  
20.             foreach (DataColumn dc in dt.Columns)
21.             {
22.                 Debug.Log(dc.ColumnName + " : " + dr[dc.ColumnName]);
23.             }
24.  
25.             rowIndex++;
26.         }
27.     }
28. }
29.  
30.  

For more information, please check out the user manual and API links on the website here: http://echo17.com/simplesql.html

 

Thanks! Those options work perfectly!

 

I didn't read every post but what are you doing in your code when you say it requires Android Pro but works with iOS basic? Plugins have always been supported on mobile platforms no matter if its basic or pro but with desktop it has been required to have pro for native code plugins.

 

I've purchased SimpleSQL yesterday and I have to say: This is awesome. Thank you for creating it and publishing via the Asset Store!!! The documentation is very complete (including "basics" I wouldn't expect), it works quite robust so far (haven't deployed to devices, yet, though ;-) ) ... and is just really fun to work with.

One thing I'm missing (but it's not a biggie): Support for the Guid data type, also the nullable version of it. I noticed that it works for DateTime (also DateTime?, which is awesome ;-) ). I'm working around this using a string field with MaxLength(36) and having some custom properties to be able to work with type-safe GUIDs. Having "native GUIDs" would be really awesome (of course, SQLite doesn't support that, but "native" in my classes and doing the conversion stuff "under the hood" would still be better than having to write that boilerplate code myself ;-) ).

Another thing that I'd like to have: An attribute with which I can tell the OR-mapper to stay away from certain properties. EDIT: Well, actually, this is just missing in the PDF documentation ;-) ... and I just found out that the attributes are under "Extra Docs" in the API documentation. Ok, that problem solved ;-)

In any case, that's all really just "nice-to-have"-stuff ... I really like the way this whole package is set up and look forward to incremental improvements ;-)

 

There is actually some unmanaged code in the sqlite3.dll file that is required for SimpleSQL. iOS doesn't require this file since all apple devices already come with sqlite3.dll. Android does not, unfortunately.

 

Thanks for the kind words! I'm glad you are enjoying SimpleSQL.

As you've discovered, sqlite actually only supports 5 data types: NULL, INTEGER, REAL, TEXT, BLOB. This means that any other type that you come up with will just be a derivation of one of these. In the case of GUID, it would just be a TEXT field as you've done.

http://www.sqlite.org/datatype3.html

When you talk about having custom properties, what exactly are you looking for? There's not much that can be done with a guid since it is just a string, so I'm not sure what customization you would need for that.

Yeah, I felt the attributes were more of an API thing, so I put them there. I'll probably update the docs to include some of the API stuff.

 

Hi I just purchased the SimpleSQL to use with Playmaker. Learning C# and now SQL is very much on my list of things to do but at the moment this is kind of going over my head. I've looked through the basic examples, it seems straight forward to pull data from the database and manipulate it using scripts, but how do I get the information from the database and put them into variables that Playmaker can access?

I started creating a card game / turn based strategy where you put together deck from large list of cards, draw cards from the deck to the hand, champ card goes on main gameboard, land / infastructure cards go to village board, and dead / used cards go into graveyard. Realized very quickly that I'd need a database to keep track of all the stats and information.

The solution is probably something very obvious. I'll keep working through the documentation and hopefully figure it out soon, but would be great if there was a Playmaker custom action for the SimpleSQL functions.

 

Playmaker scripts are highly requested and on my todo list. I have no idea of the implementation date for this, however. You can subscribe to my social media links below (Facebook, Twitter, Google+) to get updates.

 

Good news everyone!​

I created some Playmaker scripts for SimpleSQL that will allow you to query or update your databases! You can find the scripts (and examples) on the website at:

http://echo17.com/simplesql.html

Be sure to read the "readme.txt" file in the action script folder since it has important information about how to setup your project before using the scripts.

​

 

More Good News!

​

SimpleSQL has been verified to work on the Android Free license! I've submitted an update to the Asset Store to indicate this (with graphics and text), but your current version will work just fine as well.

I didn't realize that Unity had relaxed their stance on non-managed plugins for the free licenses. Sorry if anyone passed this plugin up due to thinking it wouldn't work on Android Free. Happy querying!

 

SimpleSQL 1.3.1 Approved and in the Asset Store

Download here: http://u3d.as/content/echo17/simple-sql/387

Be sure to back up your work and download the update into a new project. You can copy the code over into your current projects using your OS's file system to ensure no import errors occur.

Check out this link for a complete list of changes: Changelog

FYI, you can follow these updates on my Facebook, Google+, and Twitter feeds as well (links in my signature)

 

Just curious, is that changelog missing stuff? The version I have is 1.2.2 but don't even see that in the list. Was it a full 1.1 to 1.3 just for a cosmetic change? =)

 

When you submit a package to Unity you have to provide a unique version number. Sometimes I realize after I've submitted that I've left something out, so I'll resubmit (and give a new version number). Since there really isn't any change in these little bumps, I usually leave them off the change log.

I bumped it from 1.2.2 to 1.3.1 (was supposed to be 1.3.0, but again forgot something ) for the cosmetic change. I thought it warranted a new minor release version due to the fact that SimpleSQL has been tested to run on both versions of Android.

[EDIT]

Realized that I had indeed left off version 1.2.2 from the change log. It's there now, thanks!

[/EDIT]

 

Good News!

SimpleSQL version 1.4.0 is now available in the asset store. This version adds some generic table, row, and column classes that let you store data without using an ORM or System.Data classes. These new classes are extremely lightweight and can be useful for quickly grabbing a value or values without having to set up an ORM class.

Also, this eliminates the need to use the System.Data classes with Playmaker! Now you can use Playmaker with mobile devices without a pro license. I've updated the Playmaker scripts and examples here:

http://echo17.com/simplesql.html



The updated API is available here:

http://echo17.com/support/simplesql_api_documentation/namespaces.html

 

Hello echo. I am one of your happy customers
Are you planning adding support for WP8?
It's all I i currently need to make a working version of my game on that platform.

All the best!

 

Hey Echo,
Invoice 301276157
Date 08/21/2013

Looks like this can not works with MS SQL, i mean how to use remote MS SQL ?

 

Just FYI - you should definitely not post your invoice number on a public forum ... and you may consider editing the post and remove that information and send a private message instead ;-)

 

I don't currently have WP8, so I'm not sure what all would be required to make it work there. Have you tried the Mac/PC settings in the Control panel? That usually covers most platforms.

 

SimpleSQL uses sqlite, not MS SQL. I have no plans to integrate MS SQL support since this is not the engine it runs on. Also, SimpleSQL is designed for local db access (storing app settings, etc) and won't work with remote databases.

 

Hi echo17,

I'm making an app about money on iOS and Android, and I need some advice on how to use SimpleSQL plugin to achieve the following.
I have a user managemen, each user has:
Name
Gender
Picture
Pincode (password)
Cash amount
Cash the user saved
Pictures for the Wishlist (these can be multiple items (i think of saving links to the pictures on the device itself)
What he bought
what he saved

It's a lot of data. How would you do this in SimpleSQL?

 

Whooo-boy, that's a huge question! I'm glad to give some guidance, but database design is a pretty big topic that can easily encompass a full college curriculum.

I'll give you a simple high-level view of how I would go about this:

Create these tables and their fields with types in parenthesis:

table: User
fields: UserID (INTEGER), Name (TEXT), Gender (TEXT), PicturePath (TEXT), Pincode (TEXT), CashAmount (INTEGER), CashSaved (INTEGER)

table: WishList
fields: UniqueID (INTEGER), UserID (INTEGER), PicturePath (TEXT)

table: Purchased
fields: UniqueID (INTEGER), UserID (INTEGER), Description (TEXT)

This will create a relational database structure where the User table is your primary table and the WishList and Purchased tables are linked to the User table by the UserID field. This allows you to have a single entry for your user, with multiple entries for the wish list and purchased items.

I wasn't sure what you meant by "what he saved" since you already have a CashSaved field, so I don't know if this should be stored in a different table, or in the primary User table. If these will be a list of items, then you would probably want to create another table like the WishList and Purchased tables to store these since the relationship is many-to-one.

When querying your user information, you can provide a sql statement something like:

Code (csharp):

1.  
2.  
3. using UnityEngine;
4. using System.Collections.Generic;
5. using SimpleSQL;
6.  
7. public class ShowUser : MonoBehaviour
8. {
9.     public SimpleSQLManager manager;
10.     public int userID;
11.  
12.     void Start ()
13.     {
14.         string sql = "SELECT " +
15.                 "u.UserID, " +
16.                 "u.Name, " +
17.                 "u.Gender, " +
18.                 "u.PicturePath, " +
19.                 "u.PinCode, " +
20.                 "u.CashAmount, " +
21.                 "u.CashSaved, " +
22.                 "w.PicturePath, " +
23.                 "p.Description " +
24.             "FROM " +
25.                 "User u " +
26.                 "INNER JOIN WishList w " +
27.                     "ON u.UserID = w.UserID " +
28.                 "INNER JOIN Purchased p " +
29.                     "ON u.UserID = p.UserID " +
30.             "WHERE " +
31.                 "u.UserID = ? ";
32.  
33.         bool recordExists;
34.         User_Complete user = manager.QueryFirstRecord<User_Complete>(out recordExists, sql, userID);
35.  
36.         if (recordExists)
37.         {
38.             Debug.Log("User Found!");
39.             Debug.Log("UserID: " + user.UserID);
40.             Debug.Log("Name: " + user.Name);
41.             Debug.Log("CashAmount: " + ((float)(user.CashAmount) / 100.0f).ToString("$#,###,###,##0.00"));
42.         }
43.     }
44. }
45.  
46.  

With User_Complete table structure like:

Code (csharp):

1.  
2.  
3. using System.Collections.Generic;
4. using SimpleSQL;
5.  
6. public class User_Complete
7. {
8.     [PrimaryKey]
9.     public int UserID { get; set; }
10.  
11.     public string Name { get; set; }
12.  
13.     public string Gender { get; set; }
14.  
15.     public string PicturePath { get; set; }
16.  
17.     public string PinCode { get; set; }
18.  
19.     // Note that this is stored as an integer. We will need to divide by 100 to get the actual amount
20.     public int CashAmount { get; set; }
21.  
22.     // Note that this is stored as an integer. We will need to divide by 100 to get the actual amount
23.     public int CashSaved { get; set; }
24.  
25.     public string PicturePath { get; set; }
26.  
27.     public string Description { get; set; }
28. }
29.  
30.  

Note that I store the cash values as integers without any decimal points. So $622.43 would be stored 62243. At runtime, when I am displaying the amount, I divide by 100, so the final display comes back to 622.43. I've learned the hard way that sqlite (the underlying database structure) does not play nice with decimals or floats and will introduce rounding errors. That is why I store all values as integers and then just format them as needed when displaying. You would need to have some logic in place for localization. I believe some Asian currencies use three decimal places instead of the western two, so that gets a bit tricky.

Also note that the ORM class structure used to store the data actually encompasses multiple tables. That is because we are doing joins in our query.

I store the picture paths as text. That way I can load picture resources at runtime by using Unity's LoadResource. Have a look at this FAQ for more information:

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

Again, this is just the tip of a very massive iceberg. I don't have any specific examples or books to recommend to you, but doing a Google search on sql database design should point you in the right direction, specifically sqlite database design. If you want to see a working example of a financial application that uses sqlite, you can take a look at my program "iVelopes" which is a personal budgeting app:

http://echo17.com/ivelopes.html