SQLite4Unity3d: free sqlite plugin with sqlite-net support

Hi, I've just created a new open source project called SQLite4Unity3d. It allows you to use sqlite-net in your project and it works in iOS, Android and Windows Phone.

You can read more about it here, too.

Best regards,

@Newton_W

 

Hey Newton,

Thanks for doing this, I've been trying it out on one of my projects but am running into an issue on iOS. I can create tables and read from them fine, but this happens when I try to do an .Insert:

Code (csharp):

1. ExecutionEngineException: Attempting to JIT compile method '(wrapper delegate-invoke) System.Reflection.MonoProperty/Getter`2<MyApp.Model.UserProfile, string>:invoke_string__this___UserProfile (MyApp.Model.UserProfile)' while running with --aot-only.
2.  
3.   at System.Reflection.MonoProperty.GetterAdapterFrame[UserProfile,String] (System.Reflection.Getter`2 getter, System.Object obj) [0x00000] in <filename unknown>:0
4.   at System.Reflection.MonoProperty.GetValue (System.Object obj, System.Object[] index) [0x00000] in <filename unknown>:0
5.   at SQLite4Unity3d.TableMapping+Column.GetValue (System.Object obj) [0x00000] in <filename unknown>:0
6.   at SQLite4Unity3d.SQLiteConnection.Insert (System.Object obj, System.String extra, System.Type objType) [0x00000] in <filename unknown>:0
7.   at SQLite4Unity3d.SQLiteConnection.Insert (System.Object obj, System.Type objType) [0x00000] in <filename unknown>:0
8.   at MyApp.Data.Local.CreateUserProfile (MyApp.Model.UserProfile profile) [0x00000] in <filename unknown>:0

In the editor I don't have any issues or errors. The UserProfile class I'm trying to insert looks like this:

Code (CSharp):

1. public class UserProfile
2. {
3.     [PrimaryKey]
4.     public string FacebookID { get; set; }
5.     public string FirstName { get; set; }
6.     public string LastName { get; set; }
7.    
8.     public float AverageScoreWeekly { get; set; }
9.     public float AverageScoreAllTime { get; set; }
10.     public float AverageAnswerTimeWeekly { get; set; }
11.     public float AverageAnswerTimeAllTime { get; set; }
12.    
13.     public int PlayingStreakCurrent { get; set; }
14.     public int PlayingStreakRecord  { get; set; }
15.    
16.     public string ScoreTotals { get; set; }
17. }

Looks like it's running into the runtime JIT/AOT limitations with reflection on iOS, which is a known issue. I've disabled stripping and selected Net 2.0 instead of Net 2.0 subset, but that doesn't seem to help - the only difference being that with stripping on, the app throws a EXC_BAD_ACCESS in Xcode instead of outputting this strack trace.

On your GitHub page for the library you say it should work on iOS. Have you tested Inserts, and if so how do you avoid this error?

Thanks!

 

Hi Kennyd,

unfortunately I've not fully tested in iOS so maybe you will find some more issues. For what I've seen iOS almost forces us to us SQL syntax instead of linq or direct methods. For instance, instead of using the Insert method, just use the Query<T>(yourqueryhereasstring). It will work flawlessly but obviously you will have to build the query for yourself.

I have good news for you. I've modified the code and know you will be able to use Insert in iOS without causing a JIT compilation error.

Please donwload again the zip file or the code from the Github project and give it a try. I've tested it in an iPad and it worked.

Let me know if you find troubles.

Best regards.
@Newton_W

 

That does fix it for me on the iPhone, thanks!

This is turning out to be really useful - before I was using straight SQLite with IDataReader to run my own queries and parse them, which was a hassle.

Your GitHub repo is a bit of a mess unfortunately; I wanted to take a look at what exactly you changed to get this working but it seems like git swapped out your whole SQLiteNet.cs file instead of marking lines as added or deleted (did you change line endings or something?). I'd love to see this as a clean, well-maintained open source project - with unit tests to verify that every method is supported on every common deployment platform, etc. Would be happy to contribute to something like that!

Anyway, thanks for the quick fix I'll let you know if anything else pops up.

 

I had a problem with github and I had to unlink my local code from the repository one and then link it again. I guess that it went crazy and didn't keep the changes right.

The change was simple. Just changed PropertyInfo.GetValue method and used PropertyInfo.GetGetMethod.Invoke instead. It seems that the first one uses some of the Activator magic causing JIT compiler errors.

This repository is something I did for a current project I'm working in. It's my first Unity3d work so I'm creating a few utilities in the process and I'm publishing some of them so anyone can benefit of them (I've also created a simple Binding utility to use MVVM and PropertyChanged).

About the test, I'm standing on the shoulders of giants. The core of the code is from MVVMCross and SQLite-net projects, both fully tested, that's why I decided to publish the code right away without implementing a battery of tests. I must confess I don't have much time so I thought it was better to share it with the community than waiting to implement unit tests to publish it.

Anyway, if you want to contribute to the project you're very welcome. Maybe we can isolate the places in code that make use of Activator stuff and replace them with a iOS friendly methods. Or implement the asynchronous part of SQLite-net.

For instance, if you try to make a complex query (with orderby and then linq extension methods) you will get the JIT compiler error, too. At the moment I'm using SQL to overpass this but it will be cool to solve this if possible.

As I said, I think the code it's pretty useful at this moment and as I don't have much time to dedicate to it I'm just putting out fires until I finish my main project.

Best regards and thank you for your inputs. If you want to collaborate just let me know

 

@Newton_W ,
Thanks for sharing this.Its great tool.I am accessing data values and i am trying to update its not updating db value.Even if i am trying to insert the values its replacing values only.its not adding more.Can you please help me.
its always replacing only
publicvoidCreateDB()
{
_connection.DropTable<SaveObjects> ();
_connection.CreateTable<SaveObjects> ();

update:

publicSaveObjectsCreatePerson()
{
varp = newSaveObjects
{
Name = "Tom",
Power = 4,
HP = 12,
};
_connection.Update (p);
Debug.Log("2222");
returnp;
}

its not updating

 

Hi, I think I'm going to need more information here. Do you drop the table and create it again before updating? You have to take into account that update will only take place if the primary key is in filled. In your code snippet I don't know if you have set the Name property as Primary.

I've just tested here with the example project that it's provided in the repo and it works just fine. Which platform are you testing?

Maybe it would be helpful if you could provide me more of your code to see what's going on. You can send it to me to roberto.huertas@outllok.com.

Best regards,
Roberto.

 

Hello Newton,

Thank you for supplying this fine front end to sqlite3. I'm wondering if it's possible to serialize collections with CreateTable? Particularly List<> would be most useful.

Thanks,
-Brett

 

Hi @brettcook99
could you elaborate a bit more? I don't really get what you want :S

Best regards,
Roberto.

 

Hi Roberto, Thank you for responding.

Well, for example, I have two classes setup like this:

Code (CSharp):

1.     public class ProjectEvent
2.     {
3.         [PrimaryKey]
4.         public int ID { get; set; }
5.  
6.         public string date { get; set; }
7.         public int eventId { get; set; }
8.         public string name { get; set; }
9.     }
10.  
11.     public class ScannerProject
12.     {
13.         [PrimaryKey]
14.         public int ID { get; set; }
15.  
16.         public string projectName { get; set; }
17.         public string projectLogo { get; set; }
18.  
19.         public List<ProjectEvent> events { get; set; }
20.     }
21.  

I would like to be able to create a table of ScannerProjects that have a list of ProjectEvents inside of it. I think this can be accomplished using the [OneToMany] attribute of the SQLite extensions. So I guess my question is, can the SQLite extensions be added to this project?

Here is a link to the SQLite-net Extensions:
https://bitbucket.org/twincoders/sqlite-net-extensions

Thank you,
-Brett

 

Hi Brett,

the only thing that worries me is iOS support. I only searched a bit amongst the code and I've seen extensive use of reflection. Some of the issues can be resolved with a little bit of refactoring, the same way I did with SQLite-net but I should have to study it.

In case you want to try it yourself, if you want to collaborate with the project you're very welcome!

As I said before, iOS can be a total mess and maybe it won't never work. I'll give it a try when I have more time.

Could you open an issue in the GitHub project with this so we can keep track of it?

Kind regards,
Roberto.

 

Hi Roberto,

Thank you for checking the extension library out. I agree that the iOS side of things would be a problem. I'll also look into it when I can, and I think it would be a fine addition to your already great package.

I've opened the issue on GitHub as you requested.

Thanks,
-Brett

 

Thanks for sharing this project, Roberto.

A few comments/questions:

1. In DataService you build the path by hand for each platform. Use this instead and you can nuke the compile flags. It's not thread safe (!) but it's easier on the eye.
   
   Code (CSharp):

   1. var dbPath = Application.streamingAssetsPath+"/"+databaseName;
   2.  
2. I query in a separate thread and it works well, any recommendation/caveats I should be looking for?
3. Is it a port of SQL-Net c# compiled to work on Unity's version of mono or a wrapper around sql.net compiled on VS (I think it fails on mono, correct me..)? I'm asking because I see multiple dll, one per platform and a pure c# port wouldn't need that.
4. Which version of sqlite is it?
5. The .db file is 100% compatible with sqlite? I'd like to use Azure's sqlite sync.
6. How do I write a SQL query and bind variables?

Cheers.

 

Hi Laurent,

I'll try to answer all your questions:

1. I'll give it a try as soon as I'll have more time! Thanks for the tip!
2. None, that I'm aware of.
3. The dlls that you see are the sqlite.dll compiled specifically for each kind of CPU architecture. I've not ported this code.
4. It's a little bit old version but still works. I have an upgrade pending in my agenda but unfortunately I'm not having to much time these days.
5. Do you mean the extension? Or the generated file? Sorry, but I don't know about Azure's sqlite sync. Could you share a link to this resource so I can take a look at it? I would guess you shouldn't have a problem with that.
6. Take a look at sqlite-net repo if you want to get more insights on how to create your queries:
Basically you have to options:
a) use linq
b) use sql flavor:

public static IEnumerable<Val> QueryVals (SQLiteConnection db, Stock stock)
{

return db.Query<Val> ("select 'Price' as 'Money', 'Time' as 'Date' from Valuation where StockId = ?", stock.Id);​

}
​

Best regards,
Roberto.

 

Can u add support for float[] as blob ?

 

Foreing Keys doesn't work...