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. Tinjaw

    Tinjaw

    Joined:
    Jan 9, 2014
    Posts:
    518
    I thought I would share something. I have begun a practice of including the SELECT statement and a ToString method with my table classes.

    Here is an example:

    Code (CSharp):
    1. using SimpleSQL;
    2. using System;
    3.  
    4. public class Formation  {
    5.  
    6.     [PrimaryKey]
    7.     public int ID { get; set; }
    8.    
    9.     [NotNull]
    10.     public int UnitID {get;set;}
    11.    
    12.     [NotNull]
    13.     public int FactionID {get;set;}
    14.    
    15.     [NotNull]
    16.     public int TypeID {get;set;}
    17.    
    18.     [NotNull]
    19.     public int Quantity {get;set;}
    20.    
    21.     public string UnitName {get;set;}
    22.    
    23.     public string FactionName {get;set;}
    24.    
    25.     public string Type {get;set;}
    26.    
    27.     static public string sql = "SELECT\n" +
    28.         "    Unit.Name AS UnitName,\n" +
    29.         "    Faction.Name AS FactionName,\n" +
    30.         "    UnitType.Name AS Type,\n" +
    31.         "    Formation.Quantity AS Quantity\n" +
    32.         "FROM\n" +
    33.         "    Formation\n" +
    34.         "INNER JOIN Unit ON Formation.UnitID = Unit.ID\n" +
    35.         "INNER JOIN Faction ON Formation.FactionID = Faction.ID\n" +
    36.         "INNER JOIN UnitType ON Formation.TypeID = UnitType.ID\n" +
    37.         "ORDER BY\n" +
    38.         "    UnitName ASC";
    39.    
    40.     public override string ToString()
    41.     {
    42.         return String.Format("{0} - {1} (x{2})", UnitName, Type, Quantity);
    43.     }
    44. }
    45.  
     
  2. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    Awesome, thanks for sharing!
     
  3. Kondor0

    Kondor0

    Joined:
    Feb 20, 2010
    Posts:
    601
    I noticed in the Options that there's no mention of Linux. Would this plug in work in a Linux build?
     
  4. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    That's a good question, I'm not sure. I don't have a Linux system to test on so that is why I don't include it. I imagine that it would, however.
     
  5. bakira

    bakira

    Joined:
    Jul 1, 2016
    Posts:
    19
    Hello.
    Maybe, I found one bug in this plugin. (unity v5.3.6f1, SimpleSql v1.6.4, mac book)
    I created a 'DB Manager' on my first scene. And 'Overwrite if Exists' and 'Debug Trace' were not checked.
    I saved the scene and move to the second scene. When I move to the first scene, I found that above two check box were checked.
    Also, when I change the check box state and move to other scene without scene save, 'Scene(s) Have Been Modified' popup not occurred..
    Please help me!!
     
  6. bakira

    bakira

    Joined:
    Jul 1, 2016
    Posts:
    19
    Hi. This plugin is very useful and easy to use.
    I have a question regarding Rollback() and RunInTransaction() methods.
    I usually write code like following and did not mind exception handling. (there is exception handling code in demo script)

    1: dbManger.BeginTransaction();
    2: dbManger.CreateTable<MyTable>();
    3: dbManger.Insert(obj);
    4: dbManger.Commit();

    q1) when i use this plugin, exception handling is essential ?
    q2) how can i change above code to handle exception using RunInTransaction() method?
     
  7. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    This sounds like a Unity bug since the SimpleSQL plugin does not handle the serialization of its component state. I would submit a bug report to the Unity development team.
     
  8. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    I wouldn't say it is essential, just a good idea in any project.

    The RunInTransaction method already does exception handling for you so there shouldn't be a need to add any of your own. If you look in the included source code, in the SimpleSQL_Runtime project, SQLite.cs file, you will see the method looks like this:

    Code (CSharp):
    1.         /// <summary>
    2.         /// Executes <param name="action"> within a transaction and automatically rollsback the transaction
    3.         /// if an exception occurs. The exception is rethrown.
    4.         /// </summary>
    5.         /// <param name="action">
    6.         /// The <see cref="Action"/> to perform within a transaction. <param name="action"> can contain any number
    7.         /// of operations on the connection but should never call <see cref="BeginTransaction"/>,
    8.         /// <see cref="Rollback"/>, or <see cref="Commit"/>.
    9.         /// </param>
    10.         public void RunInTransaction (Action action)
    11.         {
    12.             if (IsInTransaction) {
    13.                 throw new InvalidOperationException ("The connection must not already be in a transaction when RunInTransaction is called");
    14.             }
    15.             try {
    16.                 BeginTransaction ();
    17.                 action ();
    18.                 Commit ();
    19.             } catch (Exception) {
    20.                 Rollback ();
    21.                 throw;
    22.             }
    23.         }
     
  9. bakira

    bakira

    Joined:
    Jul 1, 2016
    Posts:
    19
    I wrote following code and it caused SQLiteException. (no such table: MyTable)
    Can't I use multiple keys with CreateTable method or SimpeSql plugin?
    Please tell me the solution for this problem. Thank you : )
    Also, Can I use multiple keys with other methods? (insert, update, delete , and so on)

    public class MyTable {
    [PrimaryKey] public int Id1 { get; set; }
    [PrimaryKey] public int Id2 { get; set; }
    }
    dbManager.CreateTable<MyTable>();
     
    Last edited: Aug 31, 2016
  10. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    Please see this FAQ: http://www.echo17.com/forum/index.php?topic=257.msg453#msg453

    SimpleSQL's helper function CreateTable can handle a single primary key. To handle multiple keys, you'll need to skip the helper function and just execute a create table sql statement directly.

    Something like:

    Code (CSharp):
    1. var sql = "CREATE TABLE something (
    2.  column1,
    3.  column2,
    4.  column3,
    5.  PRIMARY KEY (column1, column2)
    6. );";
    7.  
    8. dbManager.Execute(sql);
    9.  
    See this StackOverflow thread here: http://stackoverflow.com/questions/734689/sqlite-primary-key-on-multiple-columns
     
  11. bakira

    bakira

    Joined:
    Jul 1, 2016
    Posts:
    19
    Thank you for your answer.
    I know that i can not use createtable method with multi primary key.
    Then how about other methods?
    (Insert, delete, update, query and so on)
    I wish to know availabe/unavailabe methods with multi keys.
     
  12. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    I have not tried it, but I don't see why you could not use insert, delete, update, or query. Just so long as you don't violate the primary key constraint by inserting a record with a key combination already existing in the database.
     
  13. CGPepper

    CGPepper

    Joined:
    Jan 28, 2013
    Posts:
    151
    Does this solution work well with runtime data, or is it more suited for long term storage?
     
  14. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    You can use it for either, and in fact I do in my projects. I keep all my dynamic data like game states, user stats, etc. in one database and all my static data like unit attributes and level data in another.
     
  15. CGPepper

    CGPepper

    Joined:
    Jan 28, 2013
    Posts:
    151
    Thanks for the info. Did you ever do any benchmarks? How long does it take to query a simple select statement on an everage 2016 mobile phone?
     
  16. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    That would really depend on the database setup, how complicated the query is, how much data is being queried, what indices are placed on the database (and if they are being used in the query), etc.

    Generally speaking, all my queries in my applications take less than a second to perform. I would definitely not use them on a frame by frame basis. Retrieving and modifying data to a sqlite database should be handled only when necessary, perhaps pushing the data management off to in-memory storage structures until a good point is reached to update them to the database.
     
  17. ORR314

    ORR314

    Joined:
    Sep 29, 2016
    Posts:
    1
    Hello echo17,
    I want to edit a SQL dasetfile and then copy this file.
    The problem that the changes that I make don't save in this file.

    How can obtain the file where the changes are made it?

    Thank you!
     
  18. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
  19. kenshin

    kenshin

    Joined:
    Apr 21, 2010
    Posts:
    940
    Hi, any plan for webgl support?
     
  20. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    I'm not sure if SimpleSQL will work with WebGL or not. I tried doing a build, but the Unity editor locks up on converting managed libraries to c++. Seems the Unity team has some kinks to work out first.

    SimpleSQL uses a file system to store data, so if WebGL will handle files then I imagine it should work. I know that the webplayer would not work because of files due to some security issues, so if WebGL has the same issues then it will not work as well.
     
  21. kenshin

    kenshin

    Joined:
    Apr 21, 2010
    Posts:
    940
    Understood, thanks for the quick answer.

    Any performance issue using tables with 100K rows on mobile?
     
  22. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    I haven't tried it with this many records so I couldn't tell you what its performance would be. Here is a link to some general tips on how to improve SQLite queries that may help you out:

    https://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning
     
    kenshin likes this.
  23. Elzean

    Elzean

    Joined:
    Nov 25, 2011
    Posts:
    584
    Does this work on console (ps4, xbox, wii u) ?
     
  24. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    I haven't tried it on these platforms, but I don't know of any restrictions that would cause a problem. I know for certain it won't work on web platforms due to file security issues, but so long as the platform will allow file access it should be fine.
     
    Elzean likes this.
  25. username132323232

    username132323232

    Joined:
    Dec 9, 2014
    Posts:
    477
    Great plugin! What is the recommended way to check if table exists? I'm thinking
    Code (CSharp):
    1. SELECT name FROM sqlite_master WHERE type='table' AND name='table_name';
    , but will it work on all platforms?
     
  26. Dustin-Horne

    Dustin-Horne

    Joined:
    Apr 4, 2013
    Posts:
    4,568
    Should as long as they allow file access... I would change your query a bit though for efficiency and do a Count which will return 1 if the table exists and 0 if not:

    Code (csharp):
    1.  
    2. SELECT Count(name) FROM sqlite_master WHERE type='table' AND name='table_name';
    3.  
     
  27. username132323232

    username132323232

    Joined:
    Dec 9, 2014
    Posts:
    477
    Sounds good. Sorry, I'm new to SimpleSQL, so how I would get that count(*) result inside C# without using SimpleSQLManager_WithSystemData?

    Also, if for any reason I do have to use SimpleSQLManager_WithSystemData instead of SimpleSQLManager, what is the downside? If it's just extra app size, how much approximately would that be? Thanks again!
     
  28. Dustin-Horne

    Dustin-Horne

    Joined:
    Apr 4, 2013
    Posts:
    4,568
    not entirely sure. I haven't used it, just familiar with sqllite in general.
     
  29. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    You could set up a simple ORM class like:

    Code (CSharp):
    1. public class TableCountRecord
    2. {
    3.     public int NumberOfTables { get; set; }
    4. }
    Then you can get the count by running something like:

    Code (CSharp):
    1. var recordExists = false;
    2. var record = db.QueryFirstRecord<TableCountRecord>(out recordExists, "SELECT Count(name) NumberOfTables FROM sqlite_master WHERE type='table' AND name='table_name');
    3. if (recordExists)
    4. {
    5.    Debug.Log("The number of tables is " + record.NumberOfTables);
    6. }
    assuming you've set up:

    Code (CSharp):
    1. public SimpleSQLManager db;
    Thank you, @Dustin-Horne for your help!
     
  30. isidro02139

    isidro02139

    Joined:
    Jul 31, 2012
    Posts:
    72
    Hey folks, nice to meet here and Happy New Year!

    I have the following situation:

    I am making a multiplayer (PvP) game that needs access to SQLite databases from various scenes. Each scene that needs to access my databases has a prefab called 'DatabaseManager' with a DatabaseManager.cs singleton script attached to it.

    The problem is that when I run multiplayer mode with the Editor and a Standalone copy of the game at the same time on my MacBook, the Standalone always gets an I/O access error when trying to query a static DB. This photo shows the Editor and Standalone side by side in multiplayer mode on my Macbook Pro; the Editor is on the left side and successfully queries the DB, but the Standalone on the right gets an I/O error when making the same sql call (see: multiplayer_standalone_bad.png).

    Here is the full stacktrace of the I/O error, apologies for the poor resolution (see: SimpleSQL_IO_Error_Stacktrace.png).

    Note that if I run the Standalone in single player mode I can make the same sql query without issue (see: singleplayer_standalone_good.png).

    To try and avoid this issue, I have an EditorStartup.cs helper class in the Editor folder that copies the dbs from their location in Application.persistentDataPath to a new sub-folder (/Editor). The copied DBs are also renames the files with an "_E" extension, just to be safe, i.e. so both path and filenames are different.

    Perhaps the way I assign the DBs in the prefab from a Resources folder somehow supersedes changing the db references from code in DatabaseManager.Awake? (see: database_manager_prefab.png).

    Any thoughts are appreciated!

    Arun
     

    Attached Files:

  31. Dustin-Horne

    Dustin-Horne

    Joined:
    Apr 4, 2013
    Posts:
    4,568
    you're running both on the same PC and both instances are trying to access the same sqllite database which is a file and locked by your database manager. if you want to that to work your standalone and editor will need to use different database files or run on separate machines.
     
  32. isidro02139

    isidro02139

    Joined:
    Jul 31, 2012
    Posts:
    72
    Dustin, thanks for the reply.

    My code is already duplicating the Databases into a separate path for the Editor, but somehow I am still getting a conflict; I think the root cause is deeper (shared virtual folder?) or else I am not using the SimpleSQL1.8 API correctly to change which DB path the Editor uses during runtime.
     
    Last edited: Jan 21, 2017
  33. username132323232

    username132323232

    Joined:
    Dec 9, 2014
    Posts:
    477
    That's perfect! Thanks @echo17 and @Dustin-Horne!
     
  34. username132323232

    username132323232

    Joined:
    Dec 9, 2014
    Posts:
    477
    With an ORM class, what type should be used for a SQL Date? Sorry, couldn't find that in the documentation.
     
  35. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    I just use DateTime (from the System library). Depends on how you are storing your date in the sqlite database, however, since there is no internal date type in sqlite. I just store my date in a string (TEXT) field and let the ORM class translate that to DateTime for me.
     
  36. username132323232

    username132323232

    Joined:
    Dec 9, 2014
    Posts:
    477
    I created an ORM class GameResult with a DateTime member and created a table:

    Code (CSharp):
    1. CreateTable<GameResult>();
    DB Browser for SQLite shows a "datetime" field in the schema, so I guess that worked :) Thanks!
     
  37. isidro02139

    isidro02139

    Joined:
    Jul 31, 2012
    Posts:
    72
    PSA: Solved my problem; I made a menu pull down that makes a copy of my SQL DB in Assets/Resources/ into a subfolder (seems that the Mac Standalone and Editor Application.persistentDataPaths are the same)
     
  38. Nadan

    Nadan

    Joined:
    Jan 20, 2013
    Posts:
    341
    Hi, my game uses SimpleSQL. It has worked before but now I'm getting this error when I try to submit my game to the App Store.

    Missing Info.plist key - This app attempts to access privacy-sensitive data without a usage description. The app's Info.plist must contain an NSCameraUsageDescription key with a string value explaining to the user how the app uses this data.

    Could this have something to do with SimpleSQL?
     
  39. Dustin-Horne

    Dustin-Horne

    Joined:
    Apr 4, 2013
    Posts:
    4,568
    I don't think so. It looks like there is an intent or something missing in your configuration. You must have an API that accesses (or can potentially access) the camera and the camera permissions haven't been specified for your app.
     
  40. Nadan

    Nadan

    Joined:
    Jan 20, 2013
    Posts:
    341
    This is strange because my game doesn't use the camera. But I'll keep looking. Thanks!
     
  41. Dustin-Horne

    Dustin-Horne

    Joined:
    Apr 4, 2013
    Posts:
    4,568
    Nadan likes this.
  42. username132323232

    username132323232

    Joined:
    Dec 9, 2014
    Posts:
    477
    My app has to update a row every couple of second. There are 300 records in the table. On mobile, the SimpleSQLManager.UpdateTable() takes about 200ms (with most columns indexed) or about 50ms (with no indexed columns other than the primary key). I also tried straight SQL with SimpleSQLManager.Execute() with similar results. The game runs at 60 fps, so I'm looking for a way to do the update asynchronously.

    Does SimpleSQL offer anything to accomplished that "out of the box"? If not, what other solution would you recommend? (coroutines, threads, etc.?)
     
    Last edited: Feb 16, 2017
  43. gaiomed

    gaiomed

    Joined:
    May 5, 2013
    Posts:
    48
    Hi,

    so far I am having an easy time using your plugin. But one thing I am missing to read a DBFile from an arbetrary folder (in my case a micro sd card on mobile). I saw the sample with loading from resources and persistent data path which might do the trick when set to external storage. But just wondering if u have an answer for best practice loading DBs from microsds on iOS and Android...
    Thx,
    Robert
     
  44. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    Sorry for the late reply, I never got a notification of your post from Unity.

    I don't think you would be able to use threading for accessing your database. You could imagine that it would cause many problems if you are trying to access data that has not been written yet, or overlapping on the insert of rows, etc. Transactions would help with that some by locking the database, but then you get back to synchronous access.

    I would recommend storing your data in a memory buffer (something as simple as just raw class objects). Then you can offload the data to the database on a larger periodic interval. You may lose some data this way, however, if the game ends before the buffer can be transferred to the database. You probably could account for that, making the final cleanup store the remainder of the buffer before exiting.
     
  45. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    SimpleSQL accesses its databases from the persistent data path since the only guaranteed location for persistent data on all devices is the Application.persistentDataPath location. If you need your data outside of this, I would recommend using your device's file system to copy from your location to the persistentDataPath on load, then copy back to your location on exit.

    Alternatively, if you can use it, the latest version of SimpleSQL allows you to specify the stored path directly from the SimpleSQLManager (both in script and the inspector). This can be useful on desktop computers where the entire file system is available, but may not be so useful for mobile devices.
     
  46. gaiomed

    gaiomed

    Joined:
    May 5, 2013
    Posts:
    48

    Thx sounds great, is the new version already available if I send you my purchase number and
     
    Last edited: Apr 10, 2017
  47. gaiomed

    gaiomed

    Joined:
    May 5, 2013
    Posts:
    48
    ah it is the override path, it is working. to my mind it should really override the whole filenamepath and not use the other one of the dummy db so initialize is correct....
     
    Last edited by a moderator: Apr 7, 2017
  48. gaiomed

    gaiomed

    Joined:
    May 5, 2013
    Posts:
    48
    so on desktop I got it to work but on Android it get the following exception
    DllNotFound Exception: sqlite3
    SimpleSQL.SQLiteConncetion.ctor (system.string databasePath)

    I am wondering is this error because the plugin can't find the database on the persistent data paht or is something wrong with the dlls you need for your plugin to work. I am on Unity 5.6 and 1.8 of your plugin.
    I guess its because of DLLs because you for sure have more verbose debug logs when it can't find the db?
    best,
    Robert
     
  49. jashan

    jashan

    Joined:
    Mar 9, 2007
    Posts:
    3,307
    You should never post your invoice number on a public forum. You can edit the post to remove that information. Use a private message to the publisher instead.
     
    gaiomed likes this.
  50. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,249
    The override path should only work on desktop platforms. Mobile devices lock down access, so that is why I use the Application.persistentDataPath to be globally available.