Search Unity

  1. Welcome to the Unity Forums! Please take the time to read our Code of Conduct to familiarize yourself with the forum rules and how to post constructively.

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,233
    Last edited: Dec 13, 2019
  2. newgaze

    newgaze

    Joined:
    Oct 27, 2012
    Posts:
    13
  3. Player7

    Player7

    Joined:
    Oct 21, 2015
    Posts:
    1,533
    Can you sort your loose scripts out, and put them in a proper namespace.. shouldn't need to be said to every asset store developer but eventually one comes across two addons who want to use the same name for a script and then the end user gets the conflict...

    MainMenuButton.cs ...being the case in this instance.

    just noticed another called MainMenu.cs ..I mean common
     
  4. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    Good idea. I'll try to get this in the next service release.
     
  5. unity_60gLK6FiHk2Krg

    unity_60gLK6FiHk2Krg

    Joined:
    Sep 16, 2019
    Posts:
    1
    Hello. I found a behavior that might need to be fixed.

    I found
    T SimpleSQL.SimpleSQLManager.Get< T >(object pk)
    throws InvalidOperationException when specified record is not found, but references saids
    The object with the given primary key. Throws a not found exception if the object is not found
    .

    error code is below. IEnumerable threw error.

    Code (CSharp):
    1. InvalidOperationException: Sequence contains no elements
    2. System.Linq.Enumerable.First[TSource] (System.Collections.Generic.IEnumerable`1[T] source) (at <351e49e2a5bf4fd6beabb458ce2255f3>:0)
    3. SimpleSQL.SQLiteConnection.Get[T] (System.Object pk) (at <9d351781d88446ec91fb4a6de237d175>:0)
    4. SimpleSQL.SimpleSQLManager.Get[T] (System.Object pk) (at <9d351781d88446ec91fb4a6de237d175>:0)
    5.  
    6. //omit the rest (below is my code)
    I'm grad to you check this if you have time.
     
  6. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    I don't officially support Linq with SimpleSQL, so I don't include reference to that library in my documentation or code examples. Instead, try using the built-in functions of the SimpleSQLManager like Query and QueryFirstRecord. These should be more robust and get you the data that you need.

    As to the error that you are getting, that would seem to be the expected behavior if you do not have a record. I cannot speak for how the .NET developers designed Linq, but it would seem you would want an exception thrown for missing data. You can probably handle this with a try catch or just use the QueryFirstRecord method in SimpleSQLManager (which does provide an out parameter that tells you if the record exists).

    Code (CSharp):
    1. var record = dbManager.QueryFirstRecord<MyORM>(out recordExists, sql);
     
  7. DavidLe360

    DavidLe360

    Joined:
    Dec 24, 2018
    Posts:
    123
    Hi, i have the follow error (it's a conflic with Runtime Editor Asset):
    Assets\Battlehub\UIControls\Menu\Scripts\UIStyle.cs(13,32): error CS1061: 'MainMenuButton' does not contain a definition for 'NormalColor' and no accessible extension method 'NormalColor' accepting a first argument of type 'MainMenuButton' could be found (are you missing a using directive or an assembly reference?)



    'MainMenuButton' does not contain a definition for 'FocusedColor' and no accessible extension method 'FocusedColor' accepting a first argument of type 'MainMenuButton' could be found (are you missing a using directive or an assembly reference?)


    Thanks in advance.
     
  8. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    I plan on adding namespaces to my demo code in the next service release to avoid these conflicts. Until then, you can either add a namespace to the SimpleSQL MainMenuButton code or delete the code entirely. The only purpose it has is to allow you to navigate the demos without having to load each scene individually. You can still play each demo without it.
     
  9. Nadan

    Nadan

    Joined:
    Jan 20, 2013
    Posts:
    341
    Hi,

    My game works on Unity Editor, but when I build the game on Android. It's not working. I have installed the latest version from the Asset Store and I don't know how to fix this.

    Is there any way I can debug SimpleSQL when I run the game on my android device?
     
  10. jacente

    jacente

    Joined:
    Dec 5, 2019
    Posts:
    1
    I had the same problem. Try this:
    Tools > SimpleSQL > Options -> Android
    and then run build.
     
    Last edited: Jan 22, 2020
    Nadan likes this.
  11. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    In addition to the above suggestion by jacente, I would also have a look at these links for advice on debugging on Android:

    https://support.unity3d.com/hc/en-us/articles/205485376-How-do-you-debug-on-Android-
    https://answers.unity.com/questions/1368808/how-to-see-debuglog-when-using-android.html
     
    Nadan likes this.
  12. Nadan

    Nadan

    Joined:
    Jan 20, 2013
    Posts:
    341
    Thank you so much, jacente and echo17! I got it working with a single click of a button. I wish I could always fix my games like this. :)
     
  13. TSI25

    TSI25

    Joined:
    Sep 22, 2013
    Posts:
    11
    After we go through the steps to notarize our build, and only on OSX versions older than Catalina we encounter the following error.

    Code (CSharp):
    1. Uploading Crash Report
    2. DllNotFoundException: Unable to load DLL 'sqlite3': The specified module could not be found.
    3.   at SimpleSQL.SQLiteConnection..ctor (System.String databasePath) [0x00000] in <00000000000000000000000000000000>:0
    We are using Unity 2019.2.21f1, and are using version 2.9.0 of SimpleSQL from the Asset Store. We have IL2CPP selected as our scripting backend and our Api Compatibility Level set to .NET 4.x.

    We aren't seeing anything like this on windows, and if we test prior to notarization everything works just fine. Its worth noting that everything else in the app seems to work just fine after notarization, but it will fail as soon as that call is made to the sqlite3.dll from SimpleSQL.SQLiteConnection. Its also worth noting that after notarization the app works perfectly on Catalina, for whatever reason this only seems to affect older version of OSX for us.

    Does anyone have any thoughts on this?
     
  14. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    The first thing I would try is a different build setting in the Tools > SimpleSQL > Options menu. Try setting it to universal (Mac / PC) and then build again. The universal setting will extract the sqlite3.dll at runtime. The Mac setting does not extract a sqlite3.dll file at runtime since Mac OS has included this by default for a very long time. I'm not sure why you would be getting this error now, however.

    If you already had it on Universal, try the Mac setting instead, then rebuild. Hopefully one of those two settings will help out.
     
  15. Kingtem

    Kingtem

    Joined:
    Jun 19, 2011
    Posts:
    18
    I've got the similar problem on Mac App. I have tried 2019.2.17 and 2019.3.3 and using a runtime debug reporter. Following is the error:
     

    Attached Files:

  16. Kingtem

    Kingtem

    Joined:
    Jun 19, 2011
    Posts:
    18
    Have tried, but this solution doesn't work for me.
    My Mac verison is 10.13.6
     
  17. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    It's possible that newer versions of the sqlite library are no longer compatible with older versions of Mac. You might try downloading an older version of the sqlite library from the sqlite website directly and putting it into your plugins folder of your project. sqlite website can be found here: https://sqlite.org/index.html
     
  18. nemeth-regime

    nemeth-regime

    Joined:
    Feb 13, 2017
    Posts:
    38
    @echo17 Hello, been using this asset for over a year now and its been great. Any tips for getting this working with uwp?
     
  19. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    Not sure. I haven't worked with UWP myself. Are you getting any particular error?
     
  20. IfItsPeeLetItBe

    IfItsPeeLetItBe

    Joined:
    Jul 31, 2014
    Posts:
    4
    I am not seeing the database file change, but it does seem like information is being stored somewhere.
    I am using Tools>SimpleSQL>Create Empty Database to make a database file named "New Database.Bytes"
    Then I use this command to make a table
    Code (CSharp):
    1. string sql = @"CREATE TABLE Table01(id INTEGER PRIMARY KEY, name_txt TEXT)";
    2. dbManager.Execute(sql);
    Then I use this code to enter information into the database
    Code (CSharp):
    1. string sql = "INSERT INTO Table01 (name_txt) VALUES (?)";
    2. dbManager.Execute(sql, "My Name");
    The information is recorded and I can later read back the information with a separate command in Unity, but the file size does not change. I open New Database.Bytes in an external SQLite editor and the editor does not see the information entered into the tables. But when I run a query on the New Database.Bytes file in Unity I can see the information that I entered into the table.

    What is going on?
     
  21. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    IfItsPeeLetItBe likes this.
  22. IfItsPeeLetItBe

    IfItsPeeLetItBe

    Joined:
    Jul 31, 2014
    Posts:
    4
  23. cdr9042

    cdr9042

    Joined:
    Apr 22, 2018
    Posts:
    129
    Does this asset work on Android phone using ARMv7l (32 bit processor)? Specifically Sony Xperia Z3 (D6603)
     
  24. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    Sorry, I don't have an ARMv7 32 bit Android device to test on. It should work, but if it doesn't shoot me an email (located in my signature) and we'll try to see if we can get it to.
     
  25. cdr9042

    cdr9042

    Joined:
    Apr 22, 2018
    Posts:
    129
    It can be tested on 64 bit Android device too. You just have to build from Unity like usual but only target architecture ARMv7. Can you please try this? Other solutions I've tried always failed to load ARMv7's SQL DLL
    https://imgur.com/WRCiZlY
     
    Last edited: Nov 3, 2020
  26. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    Yep, looks like it works for me. Here are my specs:

    SimpleSQL 2.12.0
    Unity 2020.1.6f1
    Mac OSX 10.15.5

    Device:
    Galaxy Tab A
    SM-T510
    Android 9 (Kernel 4.4.111-17420370)

    Android Player Settings:
    Minimum API Level: Android 9.0
    Scripting Backend: IL2CPP
    API Compatibility Level: .NET 4.x
    Target Architectures: ARMv7
     

    Attached Files:

    cdr9042 likes this.
  27. cdr9042

    cdr9042

    Joined:
    Apr 22, 2018
    Posts:
    129
    Thank you!
     
  28. tacman1123

    tacman1123

    Joined:
    Sep 14, 2020
    Posts:
    77
    Can you add Linux support please? Or, if it's already there, can you add it to the description? Thanks.
     
  29. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    Unfortunately, I do not have a Linux machine to test SimpleSQL on. If anyone here can confirm it works, I'll certainly add to the description.
     
  30. tacman1123

    tacman1123

    Joined:
    Sep 14, 2020
    Posts:
    77
    It appears to work on Ubuntu 20.04.
     
  31. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    Thanks!
     
  32. ZolnierGames

    ZolnierGames

    Joined:
    Feb 19, 2018
    Posts:
    84
    I prefer to use a password-protected sqlite database on mobile. Does this support using password-protected databases?
     
  33. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
  34. nemeth-regime

    nemeth-regime

    Joined:
    Feb 13, 2017
    Posts:
    38
    @echo17 Hi, I have just noticed that the issue with the options window appearing off screen was fixed in version 2.8.0 in Oct 19 after I mentioned it in May 19. Is it possible to have access to the last version that worked on 2019.2? (version 2.11.1) Using the latest version of this asset means I need to use Unity 2019.4 or newer but for now I am stuck on 2019.2.

    Thanks
     
  35. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    Sure, I'll try to get you hooked up. Shoot me an email with your invoice # at the address in my signature.
     
  36. andreim44

    andreim44

    Joined:
    Apr 17, 2014
    Posts:
    13
    Hello, I have a question related to encryption. I read the documentation and went through this thread, and my understanding is there is no tool for it out of the box but supposedly we can use our own encryption at string level.

    How exactly would that work?

    One of the reasons I'm using this plugin is for its ORM potential; I just create an object from my class and hit Insert(obj) and the plugin handles everything - awesome.
    But then, where does my potential encryption fit in? I don't want to encrypt the fields and values in my object, I still want to insert a neat and readable object so I can debug what I'm doing, but I do want it encrypted by the time it gets to the database. So I don't understand where I could fit that encryption code. Can someone please elaborate?
     
  37. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    Check out Demo #10 included with SimpleSQL v2.13.0+ (added today). This question is asked so often, I figured it would be a good time to add it as a stand alone demo. I hope this helps!
     
  38. ZolnierGames

    ZolnierGames

    Joined:
    Feb 19, 2018
    Posts:
    84
    So field by field encryption is nice, but at scale (say thousands of rows) I worry about the performance impact and complexities. I really just want to be able to password-protect my sqlite database. Will that be possible any time soon?
     
  39. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    The public version of SQLite (The underlying library that manages the database connection, which is separate from SimpleSQL, a wrapper for the SQLite library) does not support encryption. In order to use encryption, you have to own a third-party license to the SQLite Encryption Extension (SEE). Because of this, I cannot include the extension in my library. See this link for more information: https://www.sqlite.org/see/doc/release/www/readme.wiki

    SimpleSQL comes with the source code, so if you are interested in using the third-party libraries you can purchase a license, incorporate them, and recompile the SimpleSQL library. See this link for information on how to compile SimpleSQL: https://echo17.proboards.com/thread/2/faq?page=2&scrollTo=23

    One thing to keep in mind, mobile platform development (Apple at least, most likely Google too since this is a federal law requirement) requires you to complete extra steps if you are using encryption at this level. Some of these include supplying encryption compliance documentation and requiring the user to enable pin access on their device. Other steps may be required as well.

    Also, if you use encryption on a database, you won't be able to open and edit the database in any third party tool. This makes it difficult to make modifications as your game develops and evolves as you will have to do every update in code.

    If performance becomes an issue, you could remove the encryption / decryption from the property level and just do a bulk load / save encryption when reading and writing to the database. My example was just to show one way of handling encryption behind the scenes with little developer interaction. You could certainly improve performance if you are not concerned with having to provide a little more code at database interaction times.
     
    Last edited: Feb 22, 2021
  40. ZolnierGames

    ZolnierGames

    Joined:
    Feb 19, 2018
    Posts:
    84
    I'm really liking this plugin for my current app but I was curious what is the recommended workflow/process for multiple databases? Do I need to add multiple database managers to my scene(s) from the start or is there a better way? For example, my core database has tables for unlocks, items, etc. and now I want to instantiate a second database that would hold save-specific data, team info, rosters, etc. What is the best way to use this plugin to do that!
     
  41. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    This is the perfect way to use SimpleSQL (in my opinion :) ). I would set up a SimpleSQLManager component for each database that you will be needing in the scene. Generally, that would be one manager for the static data and one for the dynamic data, but you can set up as many managers and databases as needed. For the static data, you can also set "overwrite if exists" to true in the inspector so that any changes you make in your project static database will automatically get copied to the working file in the user's directory. You do not want to set this true for the dynamic data, however, as that would wipe out the saved records.

    One added bonus: you can attach multiple databases together if you need to use joins across databases. This lets you break out your data, but still use relational queries. See demo #9 for an example of how to do this.
     
  42. Eatton

    Eatton

    Joined:
    Jan 16, 2019
    Posts:
    8
    Hi

    I have a game on iOS & Android which is a single-player, offline game. The database is saved and stored locally on the device only as it is quite large. However, I would like my users to be able to continue their saved game on another device, or if they purchase a new device to be able to continue with their saved game.

    So what is the best practise for this? Can I upload the entire SimpleSQL database to a server and then have my users download it again (if changes are detected) when opening the app? If so, how would I go about this?
     
  43. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    This is basically what I do in my app iVelopes (an envelope budgeting app). When I make updates to the database, I first check to see if there is a newer version of the database on Dropbox by comparing the time stamps. If there is a newer server version, I close the local database, download from Dropbox, then re-open the local database. After that, I make the change in the local database, close it, upload to Dropbox, then re-open the local database. It sounds complicated, but it's pretty standard practice for keeping data in sync. Also, when viewing a new page (like accounts in my app), I do the same process as above for checking for newer versions, without the second part of changing the database locally and uploading to Dropbox.

    This has a drawback of having to check for new data constantly, which can slow the experience down. As little as I use the app throughout the day, this is not a big deal. For games, you'll probably want to figure out a way to sync with little delay to the user. I've experimented with this a little, but it wasn't worth the time for me to figure out in the end.

    To close the database, you'll need to call Close() and Dispose() on SimpleSQLManager. To re-open, you'll need to call Initialize(true).
     
  44. Eatton

    Eatton

    Joined:
    Jan 16, 2019
    Posts:
    8
    Thanks for the information, I'm glad it's do-able.

    I have zero experience with networks/servers etc, so I'm trying to learn as much as I can before deciding on the way forward. Please allow me to explain what I think might work and you can correct me if I get anything wrong...

    When my user finishes his session, he can upload the database to a server. I will need to write some code to find the database and upload it to a server. Then when he next opens my game, I check whether the database on the server is newer than his local database file. If so, I download it and replace his old local database with the newer downloaded one.

    If that is all correct, I have two questions:
    1) Is the database stored as a single file? If so, what format is it?
    2) For the 'server' you mentioned Dropbox. Would I be able to use something like Firebase/Playfab so I can also take advantage of the other services they offer?
     
  45. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    Sounds correct.

    It is a single file and it is in sqlite format. I'm attaching one of the sample databases from the plugin's demos to show you what the file is like. You'll need to extract it before viewing (Unity does not allow uploading sqlite files for some reason). To view and edit the file, you can use a third party tool, like DB Browser for Sqlite: https://sqlitebrowser.org/

    I have no experience with Firebase or Playfab so I can't comment on their usability. If they have an API to check files, upload and download, then yes, that should work. Bonus points if they have an SDK you can use so you don't have to write low-level web request calls.
     

    Attached Files:

  46. Eatton

    Eatton

    Joined:
    Jan 16, 2019
    Posts:
    8
    That's great, thanks for your help. :)
     
  47. knas01

    knas01

    Joined:
    Feb 24, 2018
    Posts:
    230
    Hi, I've been looking for something to help me setup a grid system that works with Playmaker. Would this asset be a good choice to setup a grid with several thousands cells and then easily check for and/or place objects in specific cells in specific patterns and control this through Playmaker?
     
  48. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    Unfortunately, I don't think SimpleSQL is what you are looking for. SimpleSQL is an interface to using sqlite databases and does not deal with UI.
     
  49. knas01

    knas01

    Joined:
    Feb 24, 2018
    Posts:
    230
    @echo17 Ah, too bad. The array system that comes with Playmaker isn't exactly usable if you want to create huge 2d arrays. Cause that's what I'm really looking for.
     
  50. vasanthbalaji

    vasanthbalaji

    Joined:
    Nov 15, 2014
    Posts:
    34
    @echo17

    Does it support .NET 4.x and can we download server side database to local and access it.