MonoSQLite is up and running! SQLite universal solution for Unity3D

Discussion in 'Assets and Asset Store' started by MonoSapiens, Apr 13, 2012.

  1. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Links to: OUR BLOG, OUR FORUM and OUR WIKI

    MonoSQLite is a plugin developed for Unity3D C# programmers in order to allow them to use SQLite database capabilities from within their Unity3D projects and up to almost any output device. It works seamlessly in both Editor and iOS devices using iOS Basic or Pro Licenses, in Android devices, also using iOS Basic or Pro Licenses as well as PC Mac Standalone builds. Additionally, and most significantly, it allows the users to ship their databases “as is” up to the targeted devices, thus eliminating the effort of recreating programmatically all DB’s data structures.

    [​IMG]

    The best feature of MonoSQLite plugin for Unity3D is, however, the simplicity. Borrowing a famous quote from Steve Jobs: “We must strive to reach the simplicity that lies beyond sophistication”. That’s how we chose to develop and deliver MonoSQLite. All databases (if your game or application uses more than one db) can be queried from the C# scripts using simple SQL syntax, allowing the users to get full access and control over the SQLite databases within their game using only two very simple methods: ExecuteFreeQuery() and ExecuteVoidQuery(). Only two methods to administer your database and build up your game or application only once!

    Additionally, Unity itself has been constructed through a minimalist paradigm which, in terms, reaches the most significant level of usability pursuing the least effort or shortest-path to accomplish any goal. That is also one of our intentions herein. By providing the simplest and most direct tool to achieve what is expected to be done, we sincerely believe we can touch the very core of technology which should, by concept, turn everyone’s life easier.

    ASSET STORE DIRECT LINK
    Last edited: Jun 13, 2012
  2. lazygunn

    lazygunn

    Member

    Joined:
    Jul 24, 2011
    Messages:
    2,162
    this looks really very useful and i could well be looking at it carefully in future so i hope you get enough support and interest in this endeavour to maintain development!
  3. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Thank you very much for your kind words, LazyGunn.

    It is truly a very useful asset. We're using it now to build all our games in all platforms using Basic Licenses and, since it made our lifes so easy, we have decided to share through the Asset Store [ASSET STORE LINK]

    This is our first product available at the Asset Store and, of course there is still (and always) room for improvement and so we do ask for every single client to give us any feedback possible. Therefore, our Contact Form is available or you can simply send us an email using mono@monosapiens.com.br

    Please, whenever you feel like taking the special look you said you could take in the future, contact us. We will be happy and proud to give you any support and hear all you have to say about our product.

    Video Tutorials are available in our YouTube Channel and you can also find us by Twitter or Facebook.
  4. ozRocker

    ozRocker

    New Member

    Joined:
    Feb 7, 2012
    Messages:
    11
    I'm using monosqlite. I can't run an INSERT statement on the iphone. SELECT works fine but INSERT just does nothing, yet in the Editor INSERT works just fine. I'm trying to sort this out but I can't find any forums or support sites for this product so I am asking here.
  5. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Thank you very much ozRocker for finding us here.

    We are working currently on our forum to make these requests easier and faster to be worked with. Meanwhile, we have sent you full support via email to have all your questions answered exactly the way you need. We'll also extend our support with FAQs and clients considerations to get the best out of our product and full satisfaction from our clients.
  6. ozRocker

    ozRocker

    New Member

    Joined:
    Feb 7, 2012
    Messages:
    11
    hello monosapiens, thanx for helping me sort out this problem. That was super-quick response! Great product btw, saves me a lot of time with my apps. I only have to watch 2 youtube videos and I know how to use it properly.
  7. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Thank you so much, ozRocker.

    We are very pleased to see that you really liked MonoSQLite and to know that it does what it has been built to do: make your life much easier. Please, don't hesitate to contact us whenever you feel like it. We are bringing a Playmaker extension to MonoSQLite pretty soon to turn what was easy into something even easier! Forum is also on its way as soon as we migrate our blog to our new server.

    Thanks!
    MS
  8. quitebuttery

    quitebuttery

    Member

    Joined:
    Mar 12, 2011
    Messages:
    204
    Is there no way to create a database? I'm trying to create a database from an editor script (by just NEW'ing the object with a filename that doesn't exist in the filesystem yet) but when I try to execute queries in it I always get this error:


    SqliteSyntaxException: file is encrypted or is not a database
    Mono.Data.SqliteClient.SqliteCommand.GetNextStatement (IntPtr pzStart, System.IntPtr pzTail, System.IntPtr pStmt)
    Mono.Data.SqliteClient.SqliteCommand.ExecuteReader (CommandBehavior behavior, Boolean want_results, System.Int32 rows_affected)
    Mono.Data.SqliteClient.SqliteCommand.ExecuteReader (CommandBehavior behavior)
    Mono.Data.SqliteClient.SqliteCommand.ExecuteDbDataReader (CommandBehavior behavior)
    System.Data.Common.DbCommand.ExecuteReader ()
    System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader ()
    MonoSQLite.MonoSQLiteDB.ExecuteFreeQuery (System.String query)
    ChunkExporter.Export () (at Assets/Mobile Test Stuff/Scripts/Editor/ChunkExporter.cs:23)
  9. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Hello, quitebuttery.

    Thank you very much for your post.

    Here's the deal: when you're using SQLite, the databases are the very files which are holding the tables. This is different from MySQL and SQL Server that allow you to create databases on the run (I believe you do know this). This way, if you need to create a database, you'll need to, either copy a file with a seed database you must initially provide to the file system (since you're trying to do it in the Editor, I would advise you to check for the FileUtil class. Perhaps CopyFileOrDirectory() might do your trick.) or, create a .sqlite file from scratch which would require adding some namespaces and using sqlite classes like this:


    Create a c# file inside the Editor folder. Edit it:

    Code (csharp):
    1.  
    2. using UnityEditor;
    3. using UnityEngine;
    4. using Mono.Data.Sqlite;
    5.  
    6. static class MenuSQLiteHandler {
    7.  
    8.     [MenuItem("My Menu/Create SQLite DB")]
    9.     static public void CreateSQLiteDB() {
    10.        
    11.         SqliteConnection.CreateFile( Application.streamingAssetsPath + "/db.sqlite" );
    12.        
    13.         EditorUtility.DisplayDialog("Success!", "Success!" +
    14.             "DB created successfully in the Streaming Assets folder", "OK!" );
    15.        
    16.        
    17.     }
    18.  
    19.  }
    20.  

    Then, refresh the StreamingAssets folder to see the db or wait a few seconds.

    Anyhow, if your intention is to have only another database, I advise you get Navicat or SQLite browser and create the seed db inside StreamingAssets Folder. Please, watch for our next updates of MonoSQLite because things are going to get even easier for the user in a near future.

    Also, please register to our forum if you will and tell me if this is the answer you need. Our goal is to provide the best support ever for our clients.

    Thanks,
    Eduardo Capanema
    Last edited: Jun 27, 2012
  10. quitebuttery

    quitebuttery

    Member

    Joined:
    Mar 12, 2011
    Messages:
    204
    Yeah, I can just use a blank one from Navicat. But now I'm having a problem telling your plug-in where the database is. It seems to append the user's profile path to any path I pass to New. Is there any way to get it to pick up the sqlite database from a folder I specify? I want to put it in StreamingAssets or Resources.
  11. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Great.

    So let me see if I can help you. First, put all databases in the StreamingAssets folder. It's the only folder it will work.

    Now, grab this file here: http://www.monosapiens.com.br/monosqlite/MonoSQLiteMenu.cs

    and put it inside the Assets/Editor folder or create the c# there and copy and paste inside.

    Now, on the Menu, you'll see MonoSQLite, choose Builds and click Sync DBs.

    Then, try to execute your code. Tell me if it worked.

    If this does not work, please, send me the code through mono@monosapiens.com.br or post it here or in the forum, i've created a post just for you there: POST.
  12. quitebuttery

    quitebuttery

    Member

    Joined:
    Mar 12, 2011
    Messages:
    204
    Well if I just New with the filename, it's not looking in StreamingAssets.

    When I execute this line:

    MonoSQLiteDB db = new MonoSQLiteDB("chunk.sqlite");

    I get this error:

    Stablishing connection to: URI=file:/Users/ralph/Library/Caches/xxx_yyyy_com/GameProject/chunk.sqlite
  13. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Excellent! So, just get this code I sent you, do the Sync DB and it will work. Please, tell me if it worked.
  14. quitebuttery

    quitebuttery

    Member

    Joined:
    Mar 12, 2011
    Messages:
    204
    Like I said above, this doesn't work. It's not looking in streaming assets, look at that error message.

    I'm calling this from an editor script, is that a problem with the pathing?
  15. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Correct! In the editor, it does not look in the StreamingAssets folder, it looks in that path you just sent. You must copy it to this path like the tutorial video says. But this code I just sent you with the Sync DB will do it for you. And it will work.
  16. quitebuttery

    quitebuttery

    Member

    Joined:
    Mar 12, 2011
    Messages:
    204
    I get it now. This seems unnecessary though. Why not make it work in StreamingAssets in the editor as well? The path is accessible.
  17. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Because the asset is universal. It must work in the editor, the android, the iOS and Standalone. The way it can work is by putting in the StreamingAssets folder but, also, persisting in the PersistentDataPath. Specially for Android. But, in the editor, the db must be copied to that path. With the script I sent you it is being done by simply clicking Sync DB but, at the time we launched, this functionality wasn't available.

    Please, tell me if it is working now.

    Att,
  18. quitebuttery

    quitebuttery

    Member

    Joined:
    Mar 12, 2011
    Messages:
    204
    Yeah it works now.
  19. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Excellent!

    I'm so very happy to "hear" that! I'm really truly engaged in putting up the best support ever. Anything else, just contact us.
    If you will, register in the forum as well.

    Thankfully,
    Eduardo Capanema
    Mono Sapiens
  20. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    The Asset Store is reviewing our current update that is bringing this solution I just sent you. When you update it, just simply remove this script from your Editor's so you won't have a duplicate.

    Att,
    Eduardo Capanema
  21. quitebuttery

    quitebuttery

    Member

    Joined:
    Mar 12, 2011
    Messages:
    204
    Do you have instructions on how to get this running on iOs pro? The documentation isn't written very well, and the youtube video just shows how to do it with iOS Basic.

    Seems like a real pain that I have to add code to AppController.m every time I build the project--why can't you just include .m files in the plug-in that get copied into the project like every other native Unity3D plug-in?
  22. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Hey, quitebuttery.

    I know. I already have the code. It's on this PostprocessBuildPlayer I'm linking below. It must be placed inside the Editor's folder. I'm finishing an update with all these improvements and others to make your lives much easier. There are just a few edges to be cut so, please, tell me if anything goes wrong. Here's the PostprocessBuildPlayer: www.monosapiens.com.br/monosqlite/PostprocessBuildPlayer

    One important thing in this code is: it will automatically put the code in the .mm files but, if you choose append on the build it will duplicate the code. I'm finishing this up and when I release to the Asset Store, this will be fixed. So, please, hang on with this beta.

    Another important thing. PostprocessBuildPlayer is a Perl script but, it must be placed in the Editor folder without the .pl extension. So, either simply download the file and copy it to the Editor's folder or, if you have to copy/paste the code, be sure you have it spelled correctly and leave the file without any extension.

    iOS Pro will run the same way but you won't have to keep appending manually the code. I know this is a hassle, that's why I'm almost done with this update. So, after placing PostprocessBuildPlayer, simply build to iOS and add the databases and the SQLite library. Next update will do everything that is possible to be automated. Just hang on.

    Please, tell me if everything works correctly. I'll be here.

    Att,
    Eduardo Capanema
  23. quitebuttery

    quitebuttery

    Member

    Joined:
    Mar 12, 2011
    Messages:
    204
    When is the new version going to be in the asset store? I append my builds every time, so that bug will get quite annoying.
  24. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Really soon, quitebuttery. I have a few more coding to make it work seamlessly, I'd say, max. a week, and the Asset Store takes also, max. another week to publish (usually they approve the updates in only 1 or 2 days). So, I'd say, in about a week (max, 2), you'll have this update I can guarantee will be top of usability. I thank you very much for the patience.

    Meanwhile, I'm never gonna leave you 'supportless'! So, anything at all, look for me.

    One more thing. If you register in our forum, you can opt for our newsletter. That will definitely warn you when the update is ready.

    go for: www.monosapiens.com.br/blog/monosqlite/forum and click on the Register link (top right of the page).

    Also, please tell me if the PostprocessBuildPlayer worked like I described.
  25. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Man! I tell you what! I'm going to write this code you need right NOW! Wait a few minutes and I'll send you the same PostprocessBuildPlayer that will allow you to use the Append! Hang on just a while.
  26. quitebuttery

    quitebuttery

    Member

    Joined:
    Mar 12, 2011
    Messages:
    204
    BTW why do you have to include the sqlite database in the XCode project? Can you not access the Resources folder or StreamingAssets folder from a native plug-in?
  27. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Yes, you can access. But, simply copying it to the Resources folder in the XCode project does not include it in the project. The databases and the files must be bundled together with the project. This requires some extra code I'm currently finishing to fully automate the iOS builds. This, I cannot send you right away because it isn't yet done. But in a few days it will be.

    The PostprocessBuildPlayer that will allow you to append (which will almost make the builds automatic because you won't have to add the db files over and over) is almost done.
  28. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
  29. quitebuttery

    quitebuttery

    Member

    Joined:
    Mar 12, 2011
    Messages:
    204
    What ever happened to this update?
  30. Arthur Pai

    Arthur Pai

    New Member

    Joined:
    May 29, 2012
    Messages:
    6
    I am download new version,
    but when i update to my project, it will cause the error, this error message is follow

    EntryPointNotFoundException: _ExecuteFreeQuery
    MonoSQLite.MonoSQLiteDB.ExecuteFreeQuery (System.String query)

    How to solve this problem?

    Thank you.
  31. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Hello Arthur,

    Thank you for updating MonoSQLite. Let's solve this right away!
    The way I see this error, it is possible that you chose MonoSQLite > Builds > Prepare iOS Build on the Top Menu Bar.
    If this is true, simply shift to Editor Mode by choosing MonoSQLite > Builds > Prepare non-iOS Build.
    Then, choose MonoSQLite > Sync DBs or use ALT+SHIFT+S every time you need to rebuild your Editors Database.

    Please, send me an email with a screenshot if you feel like it. mono@monosapiens.com.br

    I'll upload new tutorials to the YouTube Channel showing how much easier it is now to use MonoSQLite. Also, I'll be monitoring your reply to get your copy back on track.

    att,
    Eduardo
  32. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Hey, quitebuttery. Update is up! Please, enjoy it. If you use Playmaker, you're going to like it even better.

    att,
    Eduardo
  33. Arthur Pai

    Arthur Pai

    New Member

    Joined:
    May 29, 2012
    Messages:
    6
    It is work, but sometime will error again.
    I need choose Prepare iOS Build, then Prepare non-iOS Build, then it will work.

    the other thing is, i have other plugin in the plugin directory, when i choose Prepare iOS Build or Prepare non-iOS Build,
    it will replace the plugin director, and my other will delete,
    Can you find other way to solve this problem. may not use replace entire plugin directory.
  34. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Hey Arthur,

    Good to know that it works. Not happy to see that it is not working all the time. Let's deal with it. Please, tell me when it stops working because I have tried to reproduce this error message you told me but I wasn't successful. Does it raise the error by itself? How? During development? Or when you re-open the Project?

    About the Plugins Folder Replacement. Sure, this must be done and I'm working on it right now! Thanks for warning.

    Also, please, check out our Forum
  35. Arthur Pai

    Arthur Pai

    New Member

    Joined:
    May 29, 2012
    Messages:
    6
    ok, i will goto your Forum, continue this question.

    thank you quick reply, its very helpful.
  36. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Great!

    We're completely engaged in providing the best support ever. Please, find your answer in our Forum.

    Att,
    Eduardo Capanema
  37. achingupta87

    achingupta87

    New Member

    Joined:
    Apr 9, 2012
    Messages:
    145
    Hello Team

    I purchased your MonoSqlite plugin for my project. But I am facing some problem . can you provide sample project where this is using or any sample scene which is helpful for me to understand your plugin.

    Its urgent please help.

    Thanks
  38. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Hello achingupta,

    Thanks for replying. You may also surely use our Forums to have all the help you need.

    Please check this tutorial here: http://www.youtube.com/watch?v=euqN5dVNNf0&feature=plcp to set it up. Also, see this DOC for real examples.

    If none of these help, please, tell us exactly what you need and I'll personally provide you with it. Use mono@monosapiens.com.br to contact us in a way we can send you files if needed. Also, skype is available by looking for bioduds user.

    Hope this helps. I'm here for what you need.

    Att,
    Eduardo Capanema
    Systems Engineering
    Mono Sapiens Support
  39. instbldrjems

    instbldrjems

    New Member

    Joined:
    May 1, 2009
    Messages:
    10
    The new version in the Asset Store (v 1.2) has some serious issues. When you switch builds between IOS and standalone all of your other plugins in the Plugins folder are deleted, and have to be reinstalled every time. Also, the new PostProcessBuildPlayer causes an IOS build to hang at that point; Unity has to be shutdown with Force Quit and cannot be restarted properly, requiring a restart of the computer.

    I'm trying not to be too negative, because MonoSQLite is basically a great product. The previous version worked really well, even though we had to move files around. However this new version has completely stopped me from working.
  40. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Thank you very much instbldrjems,

    I was aware of the replacement of the Plugins. I'm finishing its solution.

    Wasn't aware, though, of the hanging caused by the new PostprocessBuildPlayer. I'm going to work non-stop until these two are completely solved. Thank you, very much. Also, please, register in our Forum so I can Newsletter you when these and other important changes are ready.

    thanks
  41. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    The Replacement problem is fixed. Now, I'm tracking the iOS Build problem. When it's ready, I'll notify you.
  42. achingupta87

    achingupta87

    New Member

    Joined:
    Apr 9, 2012
    Messages:
    145
    Thanks Team for your quick reply

    I am getting the problem with your plugin.
    I am using the sample code which is provide in the you tutorial for editor.
    when I attached this script it gives run time error. It gives -: NullReferenceException: Object reference not set to an instance of an object
    Mono.Data.SqliteClient.SqliteDataReader.GetString (Int32 i)
    MonoSQLite.MonoSQLiteDB.ExecuteFreeQuery (System.String query)
    monofirst.Start () (at Assets/monofirst.cs:11)


    and the script is-:

    using UnityEngine;
    using System.Collections;
    using MonoSQLite;

    public class monofirst : MonoBehaviour {

    public MonoSQLiteDB db2;
    // Use this for initialization
    void Start () {
    db2=new MonoSQLiteDB(“db.sqlite”);
    db2.result=db2.ExecuteFreeQuery(“select * from sqlite_sequence”);
    // Debug.Log(“”+db2.result[1,1]);
    }
    void OnGUI ()
    {
    //Debug.Log(“hi”+giantsLife);
    //GUI.Label( new Rect( 10, 10, 200, 80), “giantLife is: ” + db2.result[0,1] );
    }
    // Update is called once per frame
    void Update () {

    }
    }



    One more thing when I try to build the project for desktop its not creates the build hang the unity window.
    please help ......can you please provide me the skype add which is easy for me to solve problem with the help of you easily.....


    Thanks
  43. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Fine, thanks for going to OUR FORUM. Your full answer is there, now.

    Att,
    Eduardo Capanema
  44. keithsoulasa

    keithsoulasa

    Member

    Joined:
    Feb 15, 2012
    Messages:
    1,980
    Do you guys have any tips for getting started with SQL , I want to use SQL but i have no idea how it actually works ...
  45. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Hey keithsoulasa. Glad you contacted us.

    I can tell you I never read a book about SQL. All I've learned came from practice. However, learning about databases is quite different. You may want to read some starting topics about what is a database and how do you build and manage a well structured db. A good start should be the book Database Development for Dummies

    One of our greatest customer, Amy Newman, has never used SQL syntax before in her whole life. She has Playmaker and she is asking me absolutely everything she wants to know. Our email reply count, from since she started using MonoSQLite a week ago, is in 86 now (meaning, we have changed 86 messages, so far, between each other - and not counting our skype long conversations). She asks me all she wants to know and I can tell you, she is already building her whole inventory system by herself all inside her SQLite databases we have built together. So, I can really tell you, by this experience, that MonoSQLite with Playmaker Extension is really a very good way to start learning SQL, specially if you get a dedicated support like I can provide you with.

    Please, visit our BLOG and register in OUR FORUM. Place any questions you want there and I'll answer it ASAP.

    Hope I've helped you!


    Eduardo Capanema
    Mono Sapiens
    0-0
  46. achingupta87

    achingupta87

    New Member

    Joined:
    Apr 9, 2012
    Messages:
    145
    Hello Team

    I am using your Plugin for my project. But it gives some problem when I try to use your plugin with my project. then there is no build for any one not for plugin not for my project just blank screen is displayed when I try to run the build.
    please help
  47. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    Let's discover what's going on, then, achingupta. What is the MonoSQLite version you are using?
  48. achingupta87

    achingupta87

    New Member

    Joined:
    Apr 9, 2012
    Messages:
    145
    Can You please come on skype for sort out my problem ?
  49. MonoSapiens

    MonoSapiens

    New Member

    Joined:
    Apr 12, 2012
    Messages:
    79
    definitely, my skype is bioduds
  50. Xeir

    Xeir

    Member

    Joined:
    Oct 21, 2007
    Messages:
    130
    Does monosqlite handle field name referencing from the result?

    Db.result[0,"username"] for example.

    Just curious and thanks.