Search Unity

  1. Megacity Metro Demo now available. Download now.
    Dismiss Notice
  2. Unity support for visionOS is now available. Learn more in our blog post.
    Dismiss Notice

SimpleSQL - SQLite integration with Unity3D

Discussion in 'Assets and Asset Store' started by echo17, Jul 12, 2012.

  1. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249

    Introducing SimpleSQL, a quick and easy way to integrate SQLite database access in your game or application. Create level map links, store player stats, keep inventory specs, and much more!

    Support:

    echo17software@gmail.com
    support@echo17.com

    Features:
    • Attach SQLite databases to managers in your scene
    • No need to move files around for mobile devices!
    • Simple and efficient access to data using customizable classes or write your own SQL statements
    • Optionally use the .NET System.Data library
    • Works with PC, Mac, iOS, and Android
    • Works with Unity Pro and Free licenses
    • Works with Playmaker with scripts from here

    Links:
     
    Last edited: Feb 24, 2019
  2. AntFitch

    AntFitch

    Joined:
    Jan 31, 2012
    Posts:
    243
    WOOT! You could not have released this at a better time! If this is as well designed as SmoothMoves, I bet it will be a hit. :D

    As a scripting newbie, I have a couple of questions:

    -I'm making a freemium game in which the player must purchase coins for real money. If I use this to store the coins, do I have to worry about security and hackers?

    -Let's say I have a row in the database that contains default attributes for "Slime Monster". I need 100 slime monsters on screen with attributes that change (like health). I guess what I mean is... how easy is it to create instances of the slime monster and keep track of the dynamic data for each one?

    -Can this work with web?

    Sorry for the newbie questions, but er yeah... I'm learning!
     
  3. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    Thanks, amaranth! Nice to see a familiar face!

    I won't pretend to be a security expert, so I can't answer that question fully. I will tell you that the database is stored locally (not on the internet), so a hacker would be able to get access to the database if they are savvy enough to know where the application data is stored and also how to manipulate data in a sql database. I've never worked with in-app upgrades, so I don't have many suggestions for you there. You might search around the net, or even these forums, for someone who has experience with storing purchased content.

    Coming from a database application developer background, I'd say this is a piece of cake. If you are new to the SQL game, then it might be a bit of a learning curve. What I would do is keep a table in your database that stores slime monsters' stats. You would need to be sure to have a unique ID field for each monster, something that would correspond to the unique ID you assign to the gameobjects in your scene. This is a pretty involved process and would take several pages to explain fully, but that's the gist anyway. I'd recommend some intro lessons on the SQL language before tackling something like this. It'd definitely be worth the time.

    No, unfortunately the web player does not allow storage of local files for security reasons. I've only been successful with the standalone and mobile platforms.

    No problem, scripting is pretty tricky. SQL is a bit easier once you get the hang of it.
     
  4. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    One way you might be able to get around this is to use some form of encryption. You could store the data in a format that would only make sense to your application. This is a horrible example, but may get the ideas flowing:

    A1243E8sfIUAI9 = 1000 coins

    Code:

    A1 = meaningless
    24 = required code to show string is valid (other acceptable values could be 89, F2, UU)
    3E8 = hex value representing 1000 coins
    sfl = meaningless
    UA = required code to show string is valud (other acceptable values could be 9P, lk, 45)
    l9 = meaningless

    Using this code, you could store 543 coins as:

    i8UU21Fokm9Pb2

    The key here being 21F = 543 in hexidecimal and the required values at positions (3-4 = UU) and (11-12 = 9P) are valid.

    the code opJK21Fmmn45io would have no value since the required code at position (3-4 = JK) is not valid, so you would assign this to zero coins.

    This code has a limitation that only allows coin values up to 4095 (FFF in hex) since only three positions are being used to store the coin value. You'd probably need to increase this.

    Again, this is a bad example since a hacker could eventually figure out the pattern and manipulate it (or just get lucky and set a high number of coins by guessing). If there is even one invalid code assigned, you could lock down the game to where no coins could be used to discourage this, but that wouldn't help your revenue any. Perhaps an in-app purchase could reset the lock so that they could use coins again.

    There are probably much better code methods out there, so I'd hunt around.
     
  5. kenlem

    kenlem

    Joined:
    Oct 16, 2008
    Posts:
    1,630
    Check the System.Security.Cryptography namespace. It should be easy to encrypt and decrypt a string.
     
  6. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    Good idea, kenlem. You could even use the method above and then encrypt that with the cryptography library to avoid patterns.
     
  7. rockysam888

    rockysam888

    Joined:
    Jul 28, 2009
    Posts:
    650
    (bookmarked)
     
  8. Favo-Yang

    Favo-Yang

    Joined:
    Apr 4, 2011
    Posts:
    464
    http://stackoverflow.com/questions/4753433/encrypting-sqlite-database-file-on-ios

    sqlcipher has been mentioned on related posts - an open source extension to SQLite that provides transparent 256-bit AES encryption of database files. Though I haven't try it yet, it seems potential. A hacker can still change your database if they find the key somewhere from your app binary, but at least it avoid normal user to hack your database directly.

    The only way to protect your data is storing them in your own server. However for local storage, any layer of protection is helpful.
     
  9. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    Thanks for the info, Favo Yang. Good to know!
     
  10. numberkruncher

    numberkruncher

    Joined:
    Feb 18, 2012
    Posts:
    953
    Q1. Does this mean that changes cannot be made and persisted at runtime?

    Q2. Is it possible to read write to the database using editor scripts?
     
  11. numberkruncher

    numberkruncher

    Joined:
    Feb 18, 2012
    Posts:
    953
    Q3.a. If changes to databases are persisted at runtime, where do these changes get saved? (UserPrefs / a data file)

    Q3.b. How does this work with iOS/Android?
     
  12. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    You can make and persist changes at runtime. The distinction is that you have a database in your project (what you see in the Unity editor) which does not get modified at runtime. You also have a database in your application's working directory that can be modified at runtime.

    Think of the db in your project as the template from which to create working directories. SimpleSQL copies this template into your working directory where it can be modified.

    If you toggle the "overwrite if exists" on, then it will wipe out what is in your working directory with the project template. This overwrite toggle should only be used if you are not modifying data in your working directory. A static db full of app settings or level information would be a good example.

    You can read and write to the project's db (the template) using editor scripts. I had actually started working on a db editor for that very purpose, but I soon realized I was just re-inventing the wheel since there are so many SQLite managers out there for free. I personally use the sqlite manager addon for firefox.

    I don't know if the editor scripts can reach the application's working directory, however, especially if you are using an external device such as a mobile phone. If it's just static data, you can simple copy the db to the working directory with the "overwrite if exists" when the applicaiton runs.
     
  13. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    See the answer to the last post in #1. Changes are stored in the working directory's db.

    See the answer to the last post in #2. I don't believe the editor can reach the mobile device's working path, but you could make changes to the project db and copy it over upon application startup with the "overwrite if exists" option on. This will wipe out any changes you made at runtime, however, so it should only be used for static data.
     
  14. numberkruncher

    numberkruncher

    Joined:
    Feb 18, 2012
    Posts:
    953
    Awesome that sounds good. Are there any `live` examples of this working on iOS that I can purchase/download?

    Perhaps in a game that you have made?

    I realize that this extension is still warm out of the oven :)
     
  15. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    No live examples yet. I wanted to make a web player demo, but the web player has the limitation that it cannot work with local files for security reasons, so that is out. I'm working on something to include with the plugin, but obviously that requires purchasing :)

    I made SimpleSQL so that I could do a remake of my iVelopes budgetting app for iOS. Thought it might be cool to use Unity to create an application and not a game, but I needed a solid SQL system first.

    Maybe I can do a video of the demo once it is made. It wouldn't be interactive, but may inspire creativity.
     
  16. numberkruncher

    numberkruncher

    Joined:
    Feb 18, 2012
    Posts:
    953
  17. numberkruncher

    numberkruncher

    Joined:
    Feb 18, 2012
    Posts:
    953
    Ah, reading the comments a little further down suggests to use `Application.persistentDatapath` this is probably what your already doing
     
  18. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    I need to add this to the documentation, but the working directories for the platforms 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)

    You are correct that I am using Application.persistentDatapath
     
  19. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    SimpleSQL 1.1.0 Released!

    New Features:
    • Optionally add .NET's System.Data library so that you can use DataTable, DataRow, DataView, and all the other structures included in this library. This is great if you are transitioning your code from a .NET project and don't want to have to rewrite your code to fit an ORM. You can still use the ORM model alongside System.Data if you choose.
    • Optimize your runtime package size depending on your target platforms. If you are running on Mac OS or iOS you can shave the runtime dll down to 40 KB!

     
    Last edited: Sep 4, 2012
  20. NTDC-DEV

    NTDC-DEV

    Joined:
    Jul 22, 2010
    Posts:
    593
    Been wondering,

    I know you cannot write into files from the WebPlayer, but in theory, can you still manage to read the DB and once loaded into memory do the queries?

    I'm using xml files as a DB, which means I can write into them from a standalone build but can only read from the webplayer. It's not really a problem, as long as I can read them.

    Thanks
     
  21. UnityDigger

    UnityDigger

    Joined:
    Nov 20, 2011
    Posts:
    79
  22. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    You probably could. Unfortunately, I've structure SimpleSQL so that it first copies the database to the working directory. From there you can read and write (except in web players). I did this to simplify the workflow and give some consistency. This way the database is always in the same place, whether it is read-only, or read/write.

    I'll add it to my list to look into a way to make the database optionally not copy to the working directory first. This way you could use it in a read-only mode if this is possible and feasible.
     
  23. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    The SimpleSQL plugin is written completely from scratch and is tightly integrated with Unity to save you the headache of having to move files around after you create your project.
     
    Last edited: Aug 1, 2012
  24. NTDC-DEV

    NTDC-DEV

    Joined:
    Jul 22, 2010
    Posts:
    593
    Aight, thanks!
    I'm leaning towards your product more than your competitors, so I would definitely consider a purchase if you made it webplayer compatible in read-only in a seamless way. As you can imagine, there are many uses for an offline local DB compatible with all export options.
     
  25. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    Unfortunately, it looks like opening a SQLite database through the web player is a dead end at the moment. Unity has a bunch of built-in limitations (dubbed "security") that prevent all the classical ways of handling files in environments that don't allow access to the local file system -- such as the webplayer. Unity blocks the use of virtual file systems, so you can't keep your file open in a virtual space. Not sure why, as this is the safest method available.

    I will continue to monitor the development of this as Unity releases new features, but I wouldn't hold my breath on this one. Hopefully they will see the need for a virtual file system (or at least the need for allowing us to create our own) in the future.
     
  26. NTDC-DEV

    NTDC-DEV

    Joined:
    Jul 22, 2010
    Posts:
    593
    Thanks for investigating.

    I understand, I guess XML shall do for now.
     
  27. peterept2

    peterept2

    Joined:
    Aug 1, 2012
    Posts:
    41
    Can anyone confirm if this works on Android?
     
  28. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    The code should work on Android, but because I don't have an Android device, I can't test it. One thing that SimpleSQL does at runtime is make sure you have sqlite3.dll in your working directory. Each device has a different working directory (PC standalone, Mac standalone, iOS, Android), so I'm not sure what Android uses, and therefore can't copy the sqlite3.dll to the correct directory.

    So to put it shortly, it may work, but it hasn't been tested. If I get an Android device I can definitely make it work since I'll know the Android working path, but until then I would assume it does not work on this platform.
     
  29. jeffmun

    jeffmun

    Joined:
    Jun 28, 2012
    Posts:
    10
    Hi,
    I'm using Unity to create activities for individuals with autism with severe communication impairments. I use a wide variety of input devices (Kinect, Razer Hydra, SmartEye eye tracker, heart rate, Qsensor electrodermal activity sensor) and log game state info and player behavior (Kinect joint position, eye gaze coords, heart rate, etc.) to a SQLite DB.

    SimpleSQL sounds useful, but I can't get to the User Manual via the links in the Asset store. I'd appreciate learning some more details about your tool.
    Thanks. jeffmun at u d0t washington d0t edu
     
  30. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    Sorry the links aren't working, I'll have to take a look at that. Here's a link to my webpage for SimpleSQL which has the user manual:

    SimpleSQL webpage

    FYI, I just bought an Android device, so if it is possible, I should have Android support fairly soon. Can't guarantee that due to not knowing what the file structure will be like, but I'm hopeful.
     
  31. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    Oh, and if you can't browse to the links due to being blocked by your work's security or some other reason, shoot me an email to support@echo17.com and I'll email the manual to you.
     
  32. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    Just an update. I purchased a Samsung Galaxy this weekend and confirmed that SimpleSQL currently does not work on Android. I'm looking into it as I would like a universal solution.
     
  33. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    For all you Javascript users out there, just wanted to let you know that it is super simple to convert the C# examples to JS. SimpleSQL uses generic lists to store data, and ORM to define the results. To convert from C# to JS, simple place periods before the generic types.

    Check out this FAQ for more information:

    http://www.echo17.com/forum/index.php?topic=257.msg431#msg431
     
    Last edited: Apr 2, 2013
  34. kenlem

    kenlem

    Joined:
    Oct 16, 2008
    Posts:
    1,630

    Any progress on the Android version?
     
  35. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    No, sorry. I've been working on Smooth Moves upgrades at the moment. Android looks like its going to be a tough cookie to crack, so it may be a while.
     
  36. squid808

    squid808

    Joined:
    Oct 7, 2012
    Posts:
    7
    Hey, how much does this end up bloating the project when used? A few MB? Also, is there any sort of mailing list or announcement I could sign up for to be notified when it's working with Android?
     
  37. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    It depends on what platform you are using. Mac and iOS are the lightest since it already has most of the necessary SQLite foundation. It comes in at around 40 KB. For windows you'll be using about 630 KB. For Windows 64, about 1.2 MB.

    If I ever getting it running on Android, I'll post to this forum.
     
  38. rockysam888

    rockysam888

    Joined:
    Jul 28, 2009
    Posts:
    650
    (bookmarked)
     
  39. ibps13

    ibps13

    Joined:
    Oct 6, 2012
    Posts:
    113
    Hi,

    How I can reference database manager object in the scene via script (not public)

    like :

    private SimpleSQl.SimpleSqlManager = ...

    I have a GO (simple SQLManager) in scene named "DB Manager"

    thanks
     
  40. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    SimpleSQLManager is just a MonoBehaviour script, so you can get the object like any component in Unity:

    Code (csharp):
    1.  
    2.  
    3. private SimpleSQL.SimpleSQLManager _dbManager;
    4.  
    5. public GameObject dbGameObject;
    6.  
    7. void Awake()
    8. {
    9.     _dbManager = dbGameObject.GetComponent<SimpleSQL.SimpleSQLManager>();
    10. }
    11.  
    12.  
    13.  
     
  41. ibps13

    ibps13

    Joined:
    Oct 6, 2012
    Posts:
    113
    Thanks for the tips ! and useful tools !!!

    :mrgreen:
     
  42. sleepingwalrus

    sleepingwalrus

    Joined:
    Jan 6, 2013
    Posts:
    9
    SQLite has been included with Android since 1.5 (Cupcake). I've never programmed for Android nor made an Unity plugin so I don't know if that makes it any easier to implement but it would be nice to have Android support.
     
  43. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    SimpleSQL will work with Android Pro, just not the indie version. I don't advertise this since I was hoping to have a more universal solution like I have for PC, Mac, and iOS (indie and pro). If you are using Android Pro, you'll need one extra file to go into your plugins folder. If you are interested, shoot me an email and I'll send you the file with instructions.

    FYI, I plan on looking into automating the Android Pro option in the future. Just hate to alienate the indie users.
     
  44. sleepingwalrus

    sleepingwalrus

    Joined:
    Jan 6, 2013
    Posts:
    9
    I'm an Android indie user. I appreciate not being alienated. ;)
     
  45. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    I hear ya. Unity does not allow managed plugins to be used in Indie versions. That is quite frustrating to those of us who do not like to shell out thousands of dollars just for this one feature. I've been able to circumvent this limitation in the PC, Mac, and iOS versions, but Android's package is different than these other platforms. It doesn't allow the same flexibility that lets me force the plugins.
     
  46. trelobyte

    trelobyte

    Joined:
    Nov 17, 2010
    Posts:
    54
    looking forward to that universal integration so that i can purchase it.
    Since i am an NGUI user i was wondering if there is any planed further integration of SimpleSQL with NGUI
    any ETA ?
    thanks
     
    Last edited: Jan 7, 2013
  47. ibps13

    ibps13

    Joined:
    Oct 6, 2012
    Posts:
    113
    I use SimpleSQL with NGUI for my Ipad app, and work like a charm... :mrgreen:

    Close your eyes and buy it...
     
  48. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    Like ibps13, I too use NGUI and SimpleSQL together in my apps. I also integrate nData (extremely useful tool) to bridge my data gathered from SimpleSQL and the interface provided by NGUI. The data from SimpleSQL becomes the Model in the MVVM framework. Have a look here:

    http://tools.artofbytes.com/mvvm-pattern
    http://forum.unity3d.com/threads/127918-NData-MVVM-framework-for-NGUI
     
  49. ibps13

    ibps13

    Joined:
    Oct 6, 2012
    Posts:
    113
    I just can confirm, nData it's very powerful, I like binding possibility... this 3 tools are best choice for serious works !
     
  50. trelobyte

    trelobyte

    Joined:
    Nov 17, 2010
    Posts:
    54
    thanks for the reply guys i am looking to both the plugins tutorials to get my head around their usage and ease of use for a new user like myself.
    i dont understand though why would anyone need both since one can pull data from an sqlite database and display the data with ngui.
    I dont see the benefit of having both ndata and simplesql ..to me it seems that simplesql would be enough.
    am i missing something ?