Search Unity

SimpleSQL - SQLite integration with Unity3D

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

  1. Ponytech

    Ponytech

    Joined:
    Jun 13, 2016
    Posts:
    19
    Does "Webplayer" mean WebGL export or the legacy unsupported Unity web plugin?
    I am asking because with WebGL export it is possible to save/read files using C# System.IO.File methods. They are stored in the browser IndexedDB.
    I am storing lots of files in my web (and mobile) project but I am considering switching to an SQLite solution and will use your asset if you can confirm WebGL can work :)

    Thanks!
     
  2. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    When running in WebGL, there is an exception that will not link to the sqlite dll library. The exact response is:

    Code (csharp):
    1. An error occurred running the Unity content on this page. See your browser JavaScript console for more info. The error was:
    2. uncaught exception: abort("To use dlopen, you need to use Emscripten's linking support, see https://github.com/kripken/emscripten/wiki/Linking") at jsStackTrace (WebGL.asm.framework.unityweb:2:27619)
    I have not explored using the third party library suggested, so I cannot say whether it would work. It appears to be a suggestion outside of the Unity environment, generic to javascript and linked libraries.
     
  3. Ponytech

    Ponytech

    Joined:
    Jun 13, 2016
    Posts:
    19
    Thanks for the reply.
    Your asset looks great otherwise, I'll keep looking forward if you manage to circumvent this issue in the future.

    EDIT: after extensively reviewing ALL other SQLite assets I chose yours anyway! Mobile is my first target, I'll figure out how to do with WebGL later.
    Keep up the good work :)
     
    Last edited: Aug 2, 2018
  4. SpingDeveloper

    SpingDeveloper

    Joined:
    Nov 21, 2017
    Posts:
    13
    Hi,

    We've just updated to Android 9.0 (and updated SimpleSQL to v2.3.0) and database doesn't want to load anymore and it still works fine on Android 8.1.0. The error i'm getting:

    Code (CSharp):
    1. SQLiteException: database disk image is malformed
    2.       at SimpleSQL.SQLite3.Prepare3 (IntPtr db, System.String query) [0x00000] in <filename unknown>:0
    3.       at SimpleSQL.SQLiteCommand.Prepare3 () [0x00000] in <filename unknown>:0
    4.       at SimpleSQL.SQLiteCommand.ExecuteQuery[User] (SimpleSQL.TableMapping map) [0x00000] in <filename unknown>:0
    5.       at SimpleSQL.SQLiteCommand.ExecuteQuery[User] () [0x00000] in <filename unknown>:0
    6.       at SimpleSQL.TableQuery`1[User].GetEnumerator () [0x00000] in <filename unknown>:0
    7.       at System.Linq.Enumerable+<CreateSelectIterator>c__Iterator10`2[User,User].MoveNext () [0x00000] in <filename unknown>:0
    8.       at System.Linq.Enumerable.First[User] (IEnumerable`1 source) [0x00000] in <filename unknown>:0
    9.       at User.getUser (SimpleSQL.SimpleSQLManager dbManager) [0x00000] in <filename unknown>:0
    10.       at Translation.getLocalizationForKey (SimpleSQL.SimpleSQLManager dbManager, System.String key) [0x00000] in <filename unknown>:0
    11.       at PVMInstructionsSceneManager.setTranslations () [0x00000] in <f
    This fails in our released version of the app.

    Does this maybe have something to do with a stricter UTF-8 decoding in Android 9.0?
    https://developer.android.com/about/versions/pie/android-9.0-changes-all

    Java UTF decoder
    UTF-8 is the default charset in Android. A UTF-8 byte sequence can be decoded by a String constructor, such as String(byte[] bytes).

    The UTF-8 decoder in Android 9 follows the Unicode standards more strictly than in previous versions. The changes include the following:

    • The non-shortest form of UTF-8, such as <C0, AF>, is treated as ill-formed.
    • The surrogate form of UTF-8, such as U+D800..U+DFFF, is treated as ill-formed.
    • The maximal subpart is replaced by a single U+FFFD. For example, in the byte sequence "41 C0 AF 41 F4 80 80 41," the maximal subparts are "C0," "AF," and "F4 80 80." "F4 80 80" can be the initial subsequence of "F4 80 80 80", but "C0" can't be the initial subsequence of any well-formed code unit sequence. Thus, the output should be "A\ufffd\ufffdA\ufffdA."
    • To decode a modified UTF-8 / CESU-8 sequence in Android 9 or higher, use the DataInputStream.readUTF()method or the NewStringUTF() JNI method.
     
  5. deiong

    deiong

    Joined:
    May 24, 2013
    Posts:
    79
    can you easily drop and create tables through code with this
     
  6. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
  7. WatchYourVector

    WatchYourVector

    Joined:
    Jun 17, 2013
    Posts:
    24
    Hello,

    How do I hide the "Executing Query" messages? Setting debugTrace to false does not seem to do it.
     
  8. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Sorry for the late reply. My email was down since Thursday and I didn't get the notification for this post.

    The SimpleSQLManager script sets the debug trace on the connection at startup, so adjusting it at runtime has no effect currently (pre v2.3.1). I have put in an update (version 2.3.1) that will allow you to change the debug trace at runtime and have it set the value on the connection.

    Once you update to this version, if you are importing into an existing project with SimpleSQL, you will need to go to the Tools > SimpleSQL > Options menu in Unity and select a different platform, wait a few seconds, then set the platform back to what you had it previously. This will extract the update into the Project. If you are importing into a new project, or one without SimpleSQL already in it, then this step is unnecessary.
     
  9. Ponytech

    Ponytech

    Joined:
    Jun 13, 2016
    Posts:
    19
    Hello!

    How can I check if a table exists?
    I'd like to drop it if it does and obviously, do nothing if it doesn't in order to get rid of the "No such table error"


    ps: I tried to register on your new Proboards forum, it didn't work: continue button is grayed out when I have to choose a username. I've seen a couple 400 errors in Chrome debug console and came here instead.
     
  10. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Check out this FAQ: http://echo17.proboards.com/thread/2/faq?page=1&scrollTo=4

    Sorry for your troubles. I tried registering a new account using Chrome (latest version as of this post 69.0.3497.100 (Official Build) (64-bit)) and it went fine. Perhaps try upgrading your Chrome and giving it another go. If that fails, you may need to contact proboards.com with a list of errors you are getting. They'd probably be able to help you out.
     
  11. username132323232

    username132323232

    Joined:
    Dec 9, 2014
    Posts:
    439
    Let's say I'm using a class StarShip to create a table and write/read data (as in dbManager.CreateTable<StarShip>();). If I add some helper functions, such as ToString() to the StarShip class, will that change anything as far as DB functionality is concerned?
     
  12. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Nope, you can certainly add helper functions and even helper properties if you wish. The ORM allows the SimpleSQL system to use reflection to pull out the fields for a query, but added functionality like methods will not interfere.
     
    username132323232 likes this.
  13. username132323232

    username132323232

    Joined:
    Dec 9, 2014
    Posts:
    439
    Great. Thank you!
     
  14. malkere

    malkere

    Joined:
    Dec 6, 2013
    Posts:
    1,076
    Hello. Looking at getting my hands dirty with db'ing via this tool and wanted to ask a few beginner questions from the developer or any experienced users.

    I run an open-world game, meaning there are millions of square kilometers in multiple dimensions per world. Each terrain is 1 km squared, with buildings, resources, monsters, encounters, etc. all of which are persistent. Currently I use a per terrain file system that I read/write off/on the hard disk.

    Assuming I were to shift the entire game state to a database system.

    1. When a terrain is regenerated (after loading a saved game for example), say there are 5,000 objects that need to be populated with data from the database. Currently I read off the disk into memory and go from there. I assume I could do the same with SimpleSQL. If though say I were to query the database per object for it's data is that a poor approach? Should I leave my game structure as is and only move from save/load to update/query? Or can a database be setup efficiently enough for 5,000 queries in near runtime? (Assuming 5,000 rows for example). Or even 1 complex query with 5,000 returns?

    2. Say I've got a storage chest with a dynamic List of Items, but each Item has a dynamic List of Enchants. How does one go about managing Lists like that? I don't really want a table just for WorldStorageChests do I? And then a table for each Item stored in the chest? But that's the only way about it isn't there?

    Thank you for any insight before I purchase <3
     
  15. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    I would definitely not try to do 5,000 queries in a row at runtime. That would grind the game to a halt since there will be a lot of IO with the db file. What you could do is something similar to what you are already doing. I am not sure how your data files are structured, but if you could serialize them, they could all be stored in a data table keyed by their location. So if you had your data file as a JSON structure, you could then store that data in a field in your table. Then when the location is needed, do a query based on the location keys and pull in that data, deserialize it just like you have been doing. I am not sure that will gain you much, however, since it is practically what you are doing now, other than fast lookups of the location data and simpler storage.

    Alternatively, if you had all your items keyed by location, you could easily query every item in that location in a single query, returning 5,000 rows in a single query like you suggested.

    What you are looking for here is called a relational table system. So you might have the following tables:

    Code (CSharp):
    1. ChestTable
    2. --------------
    3. ChestID INTEGER
    4. ChestName TEXT
    Code (CSharp):
    1. ItemTable
    2. ---------------------
    3. ItemID INTEGER
    4. ItemName TEXT
    Code (CSharp):
    1. EnchantmentTable
    2. -----------------------------
    3. EnchantmentID INTEGER
    4. EnchantmentName TEXT
    Where ItemTable stores the generic version of an item, not a specific one. Meaning it has the item's properties, but many unique items can use these generic properties.

    Then you can create mapping tables that link these elements together:

    Code (CSharp):
    1. UniqueItemsTable
    2. ---------------------
    3. UniqueItemID INTEGER
    4. ChestID INTEGER
    5. ItemID INTEGER
    Code (CSharp):
    1.  
    2. ItemEnchantmentTable
    3. -----------------------------
    4. UniqueItemID INTEGER
    5. EnchantmentID INTEGER
    So let's say you had the following generic data:

    Code (CSharp):
    1. ChestTable (ChestID, ChestName):
    2. 0 Dungeon Chest
    3. 1 3rd Floor Corridor Chest
    4. 2 Tower Chest
    Code (CSharp):
    1. ItemTable (ItemID, ItemName):
    2. 0 Health Potion
    3. 1 Sword
    4. 2 Ring
    Code (CSharp):
    1. EnchantmentTable (EnchantmentID, EnchantmentName):
    2. 0 Invisibility
    3. 1 Strength
    4. 2 Endurance
    and the following mapping data:

    Code (CSharp):
    1. UniqueItemsTable (UniqueItemID, ChestID, ItemID):
    2. 0 0 2
    3. 1 2 0
    4. 2 0 0
    Code (CSharp):
    1. ItemEnchantmentTable (UniqueItemID, EnchantmentID):
    2. 1 0
    You would have three items in your world. The Dungeon Chest would have two of them: a Ring and a Health Potion. The Tower Chest would have the other: a Health Potion. So there are two unique health potions, but they both share the data in the ItemTable for the Health Potion. The Ring in the Dungeon Chest has an Invisibility Enchantment on it.

    You can use joins to get all this data together. So if you wanted every unique item, where it was located, and what enchantments it had on it:

    Code (CSharp):
    1. SELECT
    2.     u.UniqueItemID,
    3.     i.ItemID,
    4.     i.ItemName,
    5.     c.ChestName,
    6.     e.EnchantmentName
    7. FROM
    8.     UniqueItemsTable u
    9.     JOIN ItemTable i
    10.         ON u.ItemID = i.ItemID
    11.     JOIN ChestTable c
    12.         ON u.ChestID = c.ChestID
    13.     LEFT JOIN EnchantmentTable e
    14.         ON u.UniqueItemID = e.UniqueItemID
    I use a left join on the EnchantmentTable in case there is no enchantment on the item. In those cases e.EnchantmentName in the query will be null.
     
    Last edited: Oct 24, 2018
    malkere likes this.
  16. malkere

    malkere

    Joined:
    Dec 6, 2013
    Posts:
    1,076
    That all makes sense to me, thank you for the in-depth reply.

    One of the main reason I want to switch to SimpleSQL is for parts of my map system which make use of data stored on terrains. If I want to update a 20km radius on the map that's like 15,000 terrain files I'd have to open to gather the data I want.

    In that scenario I was thinking that I could have a database per World, and a schema per Terrain, with my tables then being items, structures, resources, etc per terrain/schema. That way I could keep the row counts down instead of possibly millions of rows per table. In the above example though would querying 15,000 schema end up being 15,000 queries or can I do a WHERE Schema.TerrainData.WorldX < ## AND Schema.TerrainData.WorldX > ## sort of multi schema query?
     
  17. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    As long as your data is in a table, then you could certainly use the WHERE clause to filter down what you are looking for. It is rare to pull in all data, like I did in the above contrived example. Normally, you are only interested in a subset, so WHERE clauses are very commonly used.
     
    malkere likes this.
  18. spootymilk

    spootymilk

    Joined:
    Jun 9, 2017
    Posts:
    15
    Hi !
    I’m using simple sql to store string data until there everything is working as excepted.
    I need now, to store image data from a webcam texture. How can i do that ?
    I see I can read the pixels value with GetPixels(), but after that ?

    Thank you for your help !
     
  19. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Instead of storing the image directly in the database, I would recommend storing the image on your device's drive, then you can store the path to that image in your database.

    This FAQ doesn't speak about images captured at runtime, rather the resources available from the application itself, but it might also be useful to you: http://echo17.proboards.com/thread/2/faq?page=1&scrollTo=11
     
  20. lferreira_S

    lferreira_S

    Joined:
    Jan 25, 2019
    Posts:
    3
    Every time that I try to query the database, I receive this error: (the database file is ok, I can open with my sqlite manager)

    SQLiteException: file is encrypted or is not a database
    SimpleSQL.SQLite3.Prepare3 (System.IntPtr db, System.String query) (at <b1ac2b4badf247b086a9cfe190753850>:0)
    SimpleSQL.SQLiteCommand.Prepare3 () (at <b1ac2b4badf247b086a9cfe190753850>:0)
    SimpleSQL.SQLiteCommand.ExecuteQuery[T] (SimpleSQL.TableMapping map) (at <b1ac2b4badf247b086a9cfe190753850>:0)
    SimpleSQL.SQLiteCommand.ExecuteQuery[T] () (at <b1ac2b4badf247b086a9cfe190753850>:0)
    SimpleSQL.SQLiteConnection.Query[T] (System.String query, System.Object[] args) (at <b1ac2b4badf247b086a9cfe190753850>:0)
    SimpleSQL.SimpleSQLManager.Query[T] (System.String query, System.Object[] args) (at <b1ac2b4badf247b086a9cfe190753850>:0)
     
  21. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Everyone that has contacted me about this error claims it goes away when using a different database administrator. From one of my clients:

    "Just wanted to drop a comment that might help others:
    I was using the sqliteadmin program suggested in the documentation, but it saved the file in some wrong version which gave me the error that the file was either encrypted or not a database. After i switched to the firefox plugin, everything worked fine.

    So if you get that error try another admin tool."

    I have also had great success with DB Browser for sqlite. Please see this FAQ for more information: http://echo17.proboards.com/post/181/thread
     
  22. JimmyDeemo

    JimmyDeemo

    Joined:
    Mar 15, 2013
    Posts:
    6
    Sorry if this information is about somewhere, I have tried searching for it here and on the echo17 forums but I've not been able to find it.

    What version of sqlite is SimpleSQL (2.4.0) built against? I'm looking for a version that supports multiple inserts.
    Is it based off another plugin? e.g. https://github.com/praeclarum/sqlite-net
    In testing other solutions I have found once of the slowest operations to be that of marshaling the data into the C# objects; do you have any documentation for how this works in SimpleSQL? I'm looking for a solution that is quite fast on mobile platforms and reflection (for this purpose) can be cause problems.

    Many thanks.
     
  23. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    I believe it is version 3.8.7.1 which was released on Oct 29, 2014. I have tried to keep this version steady since it has to work on a variety of devices.

    You can do multiple inserts using transactions to speed up processing. Please see this link:

    https://support9569.wixsite.com/echo17/simplesql

    Go to the Demo Scripts link on that page to download all the demo scripts that come with the plugin. Check out Demos/Scripts/03_Insert/InsertCommand.cs, specifically method SavePlayerStats_QueryThreeTimes, for an example of using transactions.

    The user manual on the home page also describes how to use transactions:

    https://www.dropbox.com/s/jm6fpx8djz21fj8/SimpleSQL_UserManual.pdf?dl=1

    No, it is written from scratch to interface with sqlite directly. A portion of the code utilizes methods created by an open-source solution from Krueger Systems. That portion has been tailored to fit the SimpleSQL code base.

    SimpleSQL uses reflection to match up the properties of your ORM classes to the resulting datasets from the sqlite queries. That is one method of using SimpleSQL. Another is to just get the raw data and reference fields by index, which is faster, but contains no type casting or object-oriented naming of the results. There are examples of each method included with the plugin (and available in the demo link above).

    I wrote SimpleSQL specifically for mobile (iOS in my case) so that I could create a budgeting app. I have not had any negative experiences with database speed in this app, the bottleneck being the Dropbox network connection. You can check it out here:

    https://itunes.apple.com/us/app/ivelopes/id350035867?mt=8
     
  24. JimmyDeemo

    JimmyDeemo

    Joined:
    Mar 15, 2013
    Posts:
    6
    Thank you for the reply, the information you provided is very useful.

    To be more specific about the multiple inserts I wanted to check if the db would accept a statement in a similar format to the following;

    Code (JavaScript):
    1. INSERT INTO mytable ("col1", "col2", "col3") VALUES
    2.    (1, 2, "abc"),
    3.    (2, 4, "xyz"),
    4.    (3, 5, "aaa"),
    5.    (4, 7, "bbb");
     
  25. JimmyDeemo

    JimmyDeemo

    Joined:
    Mar 15, 2013
    Posts:
    6
    Oh, I also would like to confirm that SimpleSQL does *not* require the .NET 4.6 runtime in order to function?
     
  26. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Yes, you can use this format for inserting multiple records at one time.

    Alternatively, you could prepare a generic sql statement with parameters and fill them in using a transaction like:

    Code (CSharp):
    1.  
    2.         string sql = "INSERT INTO PlayerStats (col1, col2, col3) VALUES (?, ?, ?)";
    3.    
    4.         dbManager.BeginTransaction();
    5.    
    6.         dbManager.Execute(sql, 1, 2, "abc");
    7.         dbManager.Execute(sql, 2, 4, "xyz");
    8.         dbManager.Execute(sql, 3, 5, "aaa");
    9.         dbManager.Execute(sql, 4, 7, "bbb");  
    10.  
    11.         dbManager.Commit();
    Whichever you prefer
     
  27. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    No, it does not require .NET 4.6 runtime. The flavor of .NET that comes with Unity works just fine.
     
  28. lferreira_S

    lferreira_S

    Joined:
    Jan 25, 2019
    Posts:
    3
    Guys
    I use the dbmanager.CreateTable<T> to create my tables based in my classes, I already use [PrimaryKey, Indexed, AutoIncrement] as attributes for the id field for example, but I can't find a way to create a FK between two tables.
     
  29. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    I don't currently have a foreign key attribute available, unfortunately, but it is definitely something I can put on my list to consider. I personally don't use foreign keys as they become cumbersome to manage as the database evolves. It is easier for me just to enforce the constraints in my code or in user input.
     
  30. lferreira_S

    lferreira_S

    Joined:
    Jan 25, 2019
    Posts:
    3
    My game is a management/strategy game with a lot of financial data so will be great to have the ability to create the fk with the CreateTable... So if you can, please add this to your list, and I will change my code to create the tables manually for now...
    Thanks
     
    jcmontanez likes this.
  31. TerrenceRao

    TerrenceRao

    Joined:
    Jun 19, 2015
    Posts:
    5
    It does not work in arm64 architectures in android. My SimpleSQL version is ver2.4 (latest version so far).

    Everything works fine in armv7, but when I turn to IL2cpp and checked arm64 in player setting. it does not work, the errorlog contains:

    05-13 09:58:19.828 27533 27602 E Unity : Unable to find sqlite3

    05-13 09:58:19.842 27533 27602 E Unity : DllNotFoundException: Unable to load DLL 'sqlite3': The specified module could not be found.

    05-13 09:58:19.842 27533 27602 E Unity : at SimpleSQL.SQLite3.Open (System.String filename, System.IntPtr& db) [0x00000] in <00000000000000000000000000000000>:0

    05-13 09:58:19.842 27533 27602 E Unity : at SimpleSQL.SQLiteConnection..ctor (System.String databasePath) [0x00000] in <00000000000000000000000000000000>:0

    05-13 09:58:19.842 27533 27602 E Unity : at SimpleSQL.SimpleSQLManager.CreateConnection (System.String documentsPath) [0x00000] in <00000000000000000000000000000000>:0

    05-13 09:58:19.842 27533 27602 E Unity : at SimpleSQL.SimpleSQLManager.Initialize (System.Boolean forceReinitialization) [0x00000] in <00000000000000000000000000000000>:0

    05-13 09:58:19.842 27533 27602 E Unity : at UnityEngine.Object.Instantiate[T] (T original) [0x00000] in <00000000000000000000000000000000>:0

    05-13 09:58:19.842 27533 27602 E Unity : at DontDestroySimpleSQLDB.get_Instance () [0x00000] in <00000000000000000000000000000000>:0

    05-13 09:58:19.842 27533 27602 E Unity : at IapItemMgr.Init () [0x00000] in <00000000000000000000000000000000>:0

    05-13 09:58:19.842 27533 27602 E Unity : at GameApp.InitServices () [0x00000] in <00000000000000000000000000000000>:0

    05-13 09:58:19.842 27533 27602 E Unity : at LaunchScreen.DoWork (System.Int32 stage) [0x00000] in <0000000000000

    05-13 09:58:19.851 27533 27602 E Unity : DllNotFoundException: Unable to load DLL 'sqlite3': The specified module could not be found.

    05-13 09:58:19.851 27533 27602 E Unity : at SimpleSQL.SQLite3.Open (System.String filename, System.IntPtr& db) [0x00000] in <00000000000000000000000000000000>:0

    05-13 09:58:19.851 27533 27602 E Unity : at SimpleSQL.SQLiteConnection..ctor (System.String databasePath) [0x00000] in <00000000000000000000000000000000>:0

    05-13 09:58:19.851 27533 27602 E Unity : at SimpleSQL.SimpleSQLManager.CreateConnection (System.String documentsPath) [0x00000] in <00000000000000000000000000000000>:0

    05-13 09:58:19.851 27533 27602 E Unity : at SimpleSQL.SimpleSQLManager.Initialize (System.Boolean forceReinitialization) [0x00000] in <00000000000000000000000000000000>:0

    05-13 09:58:19.851 27533 27602 E Unity : at SimpleSQL.SimpleSQLManager.CreateTable[T] () [0x00000] in <00000000000000000000000000000000>:0

    05-13 09:58:19.851 27533 27602 E Unity : at HiStudioGames.Word.Log.HiWordUserLogController.get_Instance () [0x00000] in <00000000000000000000000000000000>:0




    Supporting arm64 is required by GooglePlay after August 1 2019. Thanks.
     
    Last edited: May 14, 2019
  32. TerrenceRao

    TerrenceRao

    Joined:
    Jun 19, 2015
    Posts:
    5
    Problem Resolved.
    The file "libsqlite3.so" that SimpleSql ver2.4 contains is only for armeabi-v7a. It needs another libsqlite3.so file for arm64-v8a.

    My Solution:
    1. Download the latest Precompiled Binaries for Android from its offical website https://sqlite.org/download.html, It's a *.aar file.
    2. Pay attention to the file name "libsqliteX.so" in it, so I changed the file name in both armeabi-v7a and arm64-v8a folder and repack te aar file.
    3. Move the final *.aar file to unity project, and remove the original libsqlite.so

    Another Solution :
    1. Change the SimpleSql Source , search the string of "sqlite3" and change it to "sqliteX"
    2. Rebuild the SimpleSql project and got the new runtime.dll
    3. Use the latest *.aar file directly in unity.

    Hope it can be useful.
     
    echo17 and mentorgame1 like this.
  33. millar5001

    millar5001

    Joined:
    Feb 13, 2017
    Posts:
    8
    Thanks TerrenceRao, great work. Only just purchased this tool a few days ago and then I found your link about it not working in 64bit;
     
  34. mentorgame1

    mentorgame1

    Joined:
    Oct 31, 2016
    Posts:
    19
    thank's man i will change the sqlite3 reference from sqliteX on sqlite-net project too and see if work using .aar plugin!
     
  35. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Thank you for this detailed explanation. I will see if I can get an update in to handle this automatically without too much pain.
     
    username132323232 likes this.
  36. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    So I tested the default configuration of SimpleSQL straight from the Asset Store on a Galaxy Tab A 10.1 running Android Pie and everything worked fine for me. I built ARMv7 and ARM64 architectures for IL2CPP .NET 4.x using Unity 2019.1.3f1. SimpleSQL setting is on Android 9+.

    I had another user get these errors using Unity 2018.3.7f1, so I am thinking it might be a bug in Unity 2018 that is causing incorrect 64 bit builds for Android. Here are the steps that they took to circumvent the issue in 2018:

    1. Change the sql options to use system.data and remove the duplicate files that causes the duplicate errors.
    2. Change the optimize platform option to Mac/iOS/Windows to include the sqlite3.dll.
    3 Add the following structure Plugins/Android/libs/arm64-v8a & armeabi-v7a, then add the files libsqlite3.so for each platform in the respective folders. (the libsqlite3.so files are attached)

    I would highly recommend upgrading to Unity 2019 since the bug seems to lie in the build system of Unity 2018. I realize that is not always an option that is available, so hopefully some of the steps outlined above may help someone.
     

    Attached Files:

  37. username132323232

    username132323232

    Joined:
    Dec 9, 2014
    Posts:
    439
    I have Unity 2019.1.4f1 and SimpleSQL 2.4.0. When I go to Tools>SimpleSQL>Options, nothing happens. No errors, no Options window. Restarting Unity didn't help.

    I'm also seeing a problem with ARM64 builds (Logcat error: "Unable to find sqlite3"). I understand that there is a "manual" solution for that. Any plans to update SimpleSQL before August to handle this automatically?
     
  38. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    I am not sure what would cause this. I can run Unity 2019.1.4f1 and open the options on my Mac running OSX 10.14.5. (see attached screenshot). One guess might be that you opened it on a computer that sometimes connects to two screens and it is trying to display on a different monitor. I've had that happen with other programs on my laptop that I work on in both laptop and external monitor modes.

    A universal solution is my goal. Unfortunately, each solution I have come up with for individuals does not seem to work for other people. I believe this is due to some combination of Player settings, Build settings, Unity version, and Android device. For example, I can run SimpleSQL demos on my Samsung Galaxy Tab A 10.1" running Android 9 Pie using IL2CPP, .NET 4.x, ARM64 without any modifications to SimpleSQL.

    In order for SimpleSQL to work on Android, you have to switch to the Android platform in the SimpleSQL options. Since you are not able to see the window and switch platforms, that is likely why you are getting the error.

    If you can reach out to my email address (support@echo17.com) maybe we can help establish the pattern and figure out a universal solution without workarounds.
     

    Attached Files:

    Last edited: May 30, 2019
  39. username132323232

    username132323232

    Joined:
    Dec 9, 2014
    Posts:
    439
    1) The Options window re-appeared today! I remember this happening about a month ago with another project. It could be like you said, a second monitor issue, but it reappeared on the main monitor. In any case, if it happens again, I'll email you and maybe we can try to diagnose it.

    2) Now that the Options window works, do I choose Android <9 or 9+ for ARM64 support?

    Also, just wanted to thank you again for making this awesome plugin and for continuously supporting it.
     
  40. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    That's fantastic!

    I would use Android 9+. It is the latest version of the library so it should work the best. The other version of libsqlite.so I left in there for legacy purposes.

    You are most welcome! I use it in my own apps, so I have a very personal stake in it.
     
    username132323232 likes this.
  41. millar5001

    millar5001

    Joined:
    Feb 13, 2017
    Posts:
    8
    I also have that problem with the option menu not appearing or it appears miles off screen and I can't get to it. I have to change my screen resolution for it to appear. For example on my laptop (1080p) I have to change to 720p resolution to see the options. If I then close the options and open it again whilst still at 720p it opens off screen again. This time I have to raise the resolution to see it. Very strange.
    It also does it on my PC (1 monitor 3440x1440 and another 1920x1080 portrait).
     
  42. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    SimpleSQL 2.5.0 is now available on the Asset Store. This solution has a universal Android sqlite library that will work on platforms back to Jelly Bean and all three architectures: x86, ARMv7, and ARM64.
     
    username132323232 likes this.