SimpleSQL - SQLite integration with Unity3D

Hi, I purchased this asset today and I played Demo. it looks very simple and i like it. I have a question.
If I want to store GameObjects(like sounds, textures, prefabs, 3D Modes, etc) , is it possible?
Of course, I already know that SQLite data type is INTEGER,TEXT,NULL,BLOB etc. However, another SQLite asset (ex. UniSQLite) store GameObject possible.
Is the way to store GameObject in SimpleSQL only use Path? Please teach me how can I do that

 

You can store the components of the gameobject, but not the gameobject itself. I imagine UniSQLite is doing this behind the scene because a gameobject has no relevance outside of a scene.

You could create a table with fields like:

Name: TEXT
LocalPositionX: INTEGER
LocalPositionY: INTEGER
LocalPositionZ: INTEGER
LocalRotationX: INTEGER
LocalRotationY: INTEGER
.
.
.
etc.

Note I am storing the float values as integer. Sqlite is a bit finicky with floating points and math associated with them, so it's always better to store your data as INTEGER if you will be doing any math operations in your queries. You can always divide by the decimal place count when you get your final results.

If you are just wanting to reference a prefab that will be used at runtime, then I would just store a path to a resources folder as a TEXT field. Resources can be loaded at runtime with just a string to reference them. Have a look at this Unity link for more info:

http://docs.unity3d.com/Documentation/ScriptReference/Resources.Load.html

Instantiating objects from prefabs based on a sqlite database is a good question, so I made a FAQ for it here:

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

 

WOW~ Fantastic Support~!!!!!
I really thank you

 

I know it's already been asked, but is there any update on web player support? I think you can use the StreamingAssets folder to sort-of-load an arbitrary file in the web player. See: http://docs.unity3d.com/Documentation/ScriptReference/Application-streamingAssetsPath.html

I'd love to use a SQL-based solution in our current project, but unfortunately web player support is important to us for a few reasons...

 

Sorry for the late reply but what you did here is great. I now understand how to start doing my Database and working on it now. I'm thinking of using a UserID as a primary integer so i can reference to that one in other tables (everything with UserID 1 in wishlist is tied to user 1 etc) thx again for the support!

 

I'll have to look into this, I wasn't aware of this feature. No guarantees, but thanks for the tip!

 

Sounds like a good plan, hope it works for you!

 

Echo -

I'd be happy to help you test on a windows phone 8 device if you want. I have an HTC 8X and a developer account so it's registered as a development device and I can deploy to it

 

Sounds good. Shoot me an email to the address in my signature and we can discuss details. Thanks!

 

I have a big problem. I want to using SimpleSQL with iOS and Android. I tried a lot of ORM solution to SQLite database handling. Every solution was failed because of the limitation of iOS Mono support. When I found SimpleSQL I hoped that I found the solution. But after I spend a lot of many and I tried the library, I realized that SimpleSQL doesn't work on iOS properly. If I try to call SimpleSQL.SQLiteConnection.Insert method with my POCO object, it throw the same F***in ExecutionEngineException error when it try to use the System.Reflection.MonoProperty.GetValue method. My main question is this: Can I use the ORM feature of the library on iOS or not? Because of this error I think not. But if I can't use it I think you should write this to your home page, because on the internet there are a lot of free solution to core SQLite handling. And now I can using just the core functions.
I found a post in your forum too and you doesn't answer this question: http://www.echo17.com/forum/index.php?topic=354.0

 

I've used ORM in my iOS apps successfully, so I'm not sure what specific difference in your project is giving you issues. I'd probably need to see your project to give you advice. You can follow the directions in this FAQ if you are interested:

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

 

Are you in full framework or subset? I'm betting MonoProperty.GetValue is the same as calling GetValue on a PropertyInfo instance. In this case, it will break in iOS when you're running in Subset. I ran into the same issue with my JSON .NET asset and was able to solve it. GetValue essentially invokes the getter on a property. I'm not sure if MonoProperty has all of the same methods that PropertyInfo has, but for some reason the exception is because GetValue can't invoke the getter. What you can do instead is call GetGetMethod to get an instance of the getter function and then call Invoke on it yourself.

For instance, if the platform is iOS I do the following:

Code (csharp):

1.  
2. var getMethod = ((PropertyInfo) member).GetGetMethod(true);
3. return getMethod.Invoke(target, null);
4.  

In my case, member is a "MemberInfo" instance which has already been checked and found to be a property and not a field. Passing "true" to GetGetMethod will allow you to get and execute the getter of the property even if it's marked "private" as long as it has the [Serializable] attribute applied.

 

@Dustin Horne: Thanks for the code and information!

 

Hi Echo17,

I'm having a problem with my Database. It's not inserting all the items in the Database. Here's my script:

And a screenshot of my Database after calling AddToWishList


In the Screenshot the WishListID is the Primary Integer, UserNumber is a nummeric value and the rest are strings.

 

In your ORM class definition, you have UserNumber set as your primary key with auto-increment and don't have WishListID listed at all. You should change this to where WishListID is your primary key with auto-increment instead, so your class definition would look like:

Code (csharp):

1.  
2.  
3. public class WishList
4. {
5.  
6. [PrimaryKey, AutoIncrement]
7. public int WishListID { get; set; }
8.  
9. public int UserNumber {get; set;}
10.  
11. public string PicturePath {get; set;}
12.  
13. public string Description {get; set;}
14.  
15. public string Cost {get; set;}
16. }
17.  
18.  

 

SimpleSQL is on sale for 75% off for a limited time

​

 

hi, I have a question.
Can I use array or List<T> in DB Class?

for example...I have a Map DB and it has obstacles column.

| obstacle1 | obstacle2 | obstacle3 | obstacle4 | <- like this.



and Map Class..


using System.Collections;
using System.Collections.Generic;
using SimpleSQL;

public class Map {

public string[] Obstacles { get; set; } // like this or
public List<T> Obstacles { get; set; } // this

}

is it possible like that? if it impossible, how can I use data set?
(I want to set several obstacles for random )

 

No, unfortunately you would not be able to do this in any database system. You are asking the compiler to just "know" your database structure without defining it explicitly. There isn't a way for it to know that your List applies only to certain fields.

Also, I think you are confusing the concepts of database and table. It sounds like you would have a Game database, with a Map table, with columns obstacle1, obstacle2, obstacle3, and obstacle4.

To set up a dataset, you would do something like this:

Code (csharp):

1.  
2.  
3. using UnityEngine;
4. using System.Collections.Generic;
5. using SimpleSQL;
6.  
7. public class Map
8. {
9.  
10.     public string obstacle1 { get; set; }
11.     public string obstacle2 { get; set; }
12.     public string obstacle3 { get; set; }
13.     public string obstacle4 { get; set; }
14.  
15. }
16.  
17. public class ListMap : MonoBehaviour {
18.  
19.     public SimpleSQLManager dbManager;
20.  
21.     void Start () {
22.  
23.         List<Map> mapRecords = dbManager.Query<Map>("SELECT * FROM Map");
24.  
25.     }
26.    
27. }
28.  
29.  

the Map class is your table structure. The mapRecords variable would be your dataset.

 

That's not exactly true. There are NoSQL / document database systems that support loosely structured data such as MongoDB. A more accurate statement would be that you can't do it with any relational database system. You could make it work however but storing the items in your List in a separate table and relate them back to the other table with a Key. So:

TableA = Map
TableB = Obstacles (with a MapId to point back to Map).


Your other option would be to ignore the Obstacles list when storing in the database but instead, create a new property called something like DbObstacles. That property would combine those obstacles into a single string separated by a delimiter and that's what would be stored in the database. The setter for that property would split the string back out and repopulate the list so you'd still have the list of items accessible.

 

Good point!

 

//echo17
Thanks a lot. That is what I wanted!!
I really thank you

 

SimpleSQL is now on sale for 50% off for a limited time!​

If you missed the 75% off sale this last week, now's your chance to get it at a hefty discount before it goes back to full price.


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

 

<Shameless Cross Promotion>

If you like SimpleSQL and have thought about trying out 2D animation, have a look at one of my other plugins, SmoothMoves. It's temporarily on sale during Unity's Birthday Bonanza for 60% off through tomorrow!

​

</Shameless Cross Promotion>

 

Did anyone manage to use SimpleSQL in a Windows Store App? If so - how to make it work?

 

Hi @echo17, do you support SimpleSQL? I'm interested buying SimpleSQL but I need support because I want to use it in an importante development. Do you confirm me this point?

Thanks in advance.

 

Yep, I support all my products. You can either contact me via my email address, post on this thread, or post to my dedicated forums here:

http://www.echo17.com/forum/

 

BibleWorld by San Paolo Digital
a new app created with SimpleSQL for database integration.

Available on the iTunes store now! https://itunes.apple.com/app/bibleworld/id750627897?mt=8&ls=1

[video=youtube_share;n4xJcM391C8]http://youtu.be/n4xJcM391C8

iTunes | Website | Facebook | Support

​

 

That looks really well done. What UI package did you use?

 

Oops, guess I forgot to put the author in my post (corrected now - thanks!). This was done by San Paolo Digital, and I agree it looks very impressive. I am not sure what UI package was used.

Here's some links to their sites in case you want to contact them:

iTunes | Website | Facebook | Support

 

I'm looking to use SimpleSQL in a server, there's a chance I may have multiple servers, can all my servers connect to the same SQL database?

 

SimpleSQL is designed for local database access only, no network server access. What I do in my apps is access / update the database locally and then sync the file with a server account, like Dropbox.

 

Thanks for the reply, actually the servers will all be on the same machine. So they would all connect to the same local database. Would that work?

 

Possibly, I have never tried a configuration like that. SimpleSQL stores the working database at Unity's persistent data path. As long as the application that is running can access this path, you should be okay. Have a look at these FAQs for more information:

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

 

Hi,

Just one question : How to save and load a texture with your tool?

 

Have a look at these FAQs:

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

 

No way.
This implies for my project hundreds of small picture files (OpenStreet tiles) on an Android device. As you know, each of these files occupies a significant place in this kind of device. A database of all these images is the best solution.

Have you seen this? :

 

Whether you are embedding these in a database or embedding them in your project's resources, they will take up the same amount of space.

If you are meaning that you want to stream a database into your app at runtime, and load the resources from it then, you might consider AssetBundles. You can still keep references to your assetbundles in the database, and then stream them at runtime. Have a look here:

http://docs.unity3d.com/Documentation/ScriptReference/AssetBundle.html

 

Most of these tiles have between 1 and 6 kb. I have 498000 files with a real size of 943 Mo. 2.47Go on an hard drive or an sdcard. Drive sector pb.

 

Sounds like a lot to embed in a database. If the user would only need a subset of these tiles at any point in their gaming experience, then I would recommend storing them offline and downloading as needed using the asset bundle feature of Unity.

 

Does SimpleSQL work on Windows Phone?

Thanks

 

Any updates on this? I'd rather share a webplayer WIP version of my project with my friends rather than having them download a new build every time I make some updates, so this is the only thing that truly holds me back from purchasing your package.

 

I'm not sure. I don't own a Windows Phone, so I can't test this out. Perhaps others subscribed to this thread can help out?

 

I'm not sure what the original quote was referring to, but I'm assuming it has to do with a webplayer implementation? If so, then that is unfortunately not likely to happen. The problem with the webplayer is that it has to stream the database into memory since access to the file system is not allowed. This means that you can't write back to the database (read only) unless it is located on a remote server, which is not what SimpleSQL is designed for. The plugin is structured for local storage, so this makes a web implementation unfeasible.

 

Ah, that's a pity. Still probably gonna pick it up since it looks like the most polished solution of them all.

 

One last question - is it correct to assume that a database that is not set to be overwritten will remain persistent between gaming sessions?

 

Yes, the database remains persistent if you have the overwrite turned off. Overwrite is good for static databases like for unit metrics (health, damage, range, etc). Turning overwrite off will allow you to store data like player progress or world changes.

Have a look at this FAQ that might explain this better:

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

Oh, and you can have multiple databases in a single project. So you could have one database for your static data and another for stuff that gets updated as you play.

 

Hi,

I have an error when I try to use this function :

Code (csharp):

1. public int GetCurrentTimeInDB ()
2.     {
3.         tmpSavedTimeInDB = dbManager.Execute("SELECT savedtime FROM utils WHERE id_user = ?", tmpUserId);
4.         return tmpSavedTimeInDB;
5.     }

error message :

SQLiteException: Row
SimpleSQL.SQLiteCommand.ExecuteNonQuery ()
SimpleSQL.SQLiteConnection.Execute (System.String query, System.Object[] args)
SimpleSQL.SimpleSQLManager.Execute (System.String query, System.Object[] args)
LogicTimer.GetCurrentTimeInDB () (at Assets/_QUEST/_SCRIPTS/LogicTimer.cs:121)
LogicTimer.StartTimer () (at Assets/_QUEST/_SCRIPTS/LogicTimer.cs:63)
...

Any idea ?

thanks

 

Execute is only used for UPDATE, DELETE, or INSERT commands. To retrieve data, you need to use the Query, QueryGeneric, or QueryFirstRecord functions. Please see the following links:

API: http://echo17.com/support/simplesql_api_documentation/namespaces.html
User Manual: http://echo17.com/support/simplesql_user_manual.pdf
FAQ: http://www.echo17.com/forum/index.php?topic=257.0

 

Hi Echo17

Ive recently bought your addon and its working nicely, thanks for making this and i definately recommend looking into it if your considering a DB, however i have a small request as i cant do one basic thing, would it be possible to get some help on a playmaker action that returns a row count directly from the database. i know theres a work around using "get results row count" but i just want the rowcount of the db without having to duplicate all the data to a gameobject.

any help much appreciated

 

You can use the existing Playmaker action called "SimpleSQL_Query_SingleRow" to get the info you need. For the query string, put in something like this (note this is using the Fantasy database included with the SimpleSQL demo, with the Weapon table):

Code (csharp):

1.  
2.  
3. SELECT COUNT(WeaponID) FROM Weapon
4.  
5.  

or you could use the * syntax to get the record count as well:

Code (csharp):

1.  
2.  
3. SELECT COUNT(*) FROM Weapon
4.  
5.  

If you are going to count on a field (and not *), then the best field to count would be your key field since it will be unique. This will get you the number of records in the table. You can even put filters on it with a where clause in your SQL string to get a count of records with a given criteria. You can then store the result into a variable.

To see this quickly, you can open up the SimpleSQL playmaker example scene called "SimpleSQL - Query Single Row". In the "Get Row" state, change the "Query" field of the "SimpleSQL_Query_SingleRow" action to the string shown above ("SELECT COUNT(*) FROM Weapon"). Create a new variable to store the count and assign it to the results. You'll want to remove the other results that are there since they aren't relevant to this query. Your state should look like:



Be sure to change the last state to see the new variable instead of the others.

FYI, you can do a lot of stuff with SQL commands. I'd recommend doing Google searches to find examples of queries to perform to suit your needs. For example, here is the first hit I got on Google searching "sql get row count":

http://www.w3schools.com/sql/sql_func_count.asp

Hope this helps!