Forum Cross Platform with Xamarin

Cross platform SQLite ORM

GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭
edited October 2018 in Cross Platform with Xamarin

Greetings,

I'm creating a cross-platform (iOS, Android and WP8) Application and I want to include database persistence. I've used MvvmCross SQLite.Net plugin for other simple projects and I'm quite happy with it. I tried to use it for this new project but got stuck with a SQLite.Net limitation: it only supports 1:1 relationships, so no 1:N or N:M relationships are supported out-of-the-box.

I've seen a pull request that includes 1:N relationships, but it's not even in the master branch and only solves part of the problem.

I've checked the MWC example to see how they handle relationships, that create intermediate classes for N:M relationships and fetch the relationships when you get the object. For example:

        public static Speaker GetSpeaker(int id)
        {
            lock (locker) {
                Speaker speaker = (from s in me.Table<Speaker> ()
                        where s.ID == id
                        select s).FirstOrDefault ();

                var keys = (from ss in me.Table<SessionSpeaker> ()
                                    where ss.SpeakerKey == speaker.Key
                                    select ss).ToList();
                // HACK: gets around "Default constructor not found for type System.String" error
                speaker.SessionKeys = keys.Select (x => x.SpeakerKey).ToList ();

                var sessions = GetItems<Session>();

                var sessionsForSpeaker = (from se in sessions
                                where speaker.SessionKeys.Contains (se.Key)
                                select se).ToList ();

                speaker.Sessions = sessionsForSpeaker;

                return speaker;
            }
        }

Writing the N:M wiring classes and all the boilerplate code for manually handling the relationships sends chills down my spine, so I started evaluating other ORM alternatives for SQLite.

Initially Vici Coolstorage seemed like a good alternative: support for MonoTouch, MonoDroid and WP; 1:1, 1:N and N:M relationships, lazy fetch and automatic pre-fetching, etc.

But when the integration started (I wanted to create a MvvmCross plugin for easier reuse) I saw that it has a nasty dependency on Vici Core. It seems like an overkill dependency for a simple SQLite ORM.

I read someone recommending Catnap as a good alternative for SQLite.Net, it seems to be decently documented and quite simple integration. But 7 months of inactivity and some dead branches makes me wonder if the projects has been abandoned.

I don't think that I'm the first one having this issue, so could you please share your experiences and recommendations about this concern?

Thank you for your time,
Guillermo

Posts

  • CheesebaronCheesebaron DKInsider, University mod

    ServiceStack.OrmLite might do what you need. There is a Mono version, you might need to grab the source and compile it yourself against Xamarin.Android and Xamarin.iOS if you need them.

  • RinkeRinke NLMember ✭✭

    You might take a look at Ninja Database Pro.
    kellermansoftware.com/p-43-ninja-net-database-pro.aspx

    I haven't tried it out yet, but others seem to have good results. See also forums.xamarin.com/discussion/comment/16832

  • Check out the OpenNETCF.ORM. It supports Mono for Droid and says an implementation for MonoTouch should be trivial to do it yourself
    http://orm.codeplex.com/

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    I finally discarded Vici Coolstorage. The main reason is having to manually maintain the SQLite database schema. I'd rather write the SQLite-Net wiring classes than manually handle the database schema.

    I'm going to evaluate Ninja Database Pro, that seems like a good candidate even when the lack of documentation it's disturbing and give OpenNETCF.ORM a try.

    Will post back results.

    Thanks.

  • RinkeRinke NLMember ✭✭

    @GuillermoGutierrez if you download the trial version for Ninja Database Pro, you will find a lot more technical details in the enclosed chm docs. Anyway, it would be better if they allowed people to look at the docs before downloading anything.

    I will soon start with the development of a database driven app myself, so i'm very interested in your experiences.

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    @Rinke There are many weird things around Ninja Database Pro, starting from their web page, continuing when they force you to download an EXE file to get the assemblies (luckily I have a Windows VM around), documentation in chm files and ending with a set of unnecessarily cumbersome examples.

    Overall it seems like a good product wrapped by a thick layer of weirdness.

    I'm going to code some samples to see how it goes.

  • SridharanSrinivasanSridharanSrinivasan SGMember ✭✭

    I am using PetaPoco http://www.toptensoftware.com/petapoco/ for both Android and IOS and am quite happy with it.

    The main reasons I choose this was because of requirement of Paging at Database level using SQL statements which this Framework gave out of the box. I can also execute complex SQL statements (if need be) and it will return strongly typed objects and collections. It also was just one single file (current version is multiple) to include in my project

    The disadvantage is you have to use a t4 template to generate your model definitions and also it will not work in Windows Phone. Another disadvantage? is that it uses mono.data.sqlite which may increase the app size.

  • ChrisHonselaarChrisHonselaar NLBeta ✭✭✭
    edited June 2013

    PetaPoco does seem like a great little ORM. But it's been a long time since the project has seen any activity. Lots of open pull requests and issues. I'm thinking @BradRobinson must be pretty busy with XibFree and other things - maybe he would be open to the idea of someone helping out?
    It seems like adding Windows Phone support would doable with some community help, and perhaps we can create an alternative to t4 as well.

  • SridharanSrinivasanSridharanSrinivasan SGMember ✭✭
    edited June 2013

    The current version of PetaPock published in the Topten Site will not work for MonoTouch as it uses Reflection.Emit. Long back when i contacted @BradRobinson, he was kind enough to point out his earlier implementation using Reflection which I merged with the latest version to make it work in MonoTouch and during usage fixed some bugs.

    I also did some changes to get the ProviderFactory of PetaPoco to dynamically look at the loaded assemblies in the app domain to get the provider factory.

    I enclose the updated petapoco.v5 which works in android/ios, but please note as these changes have been done by me for my use case. Use it with through testing for your use case.

    I believe if you are looking for only usage in mobile, then SQLite.NET is the appropriate choice, because it is endorsed by Xamarin and has a community of developers supporting it.

    For me, I had to use MySql in GoDaddy Hosting, SQLite(later Postgresql) in EC2 Server and MSSQL and Oracle in my Development Environment, and SQLite in IOS and Android devices from a single code base, since i am building a common app runtime based on HTML and OPENGL, hence i had no choice other than use an ORM which works across many databases even if currently it does not work in Windows Phone (Requires Porting Mono.Data.SQLite to Windows Phone - Doable but requires lot of work - Can borrow some code from CSharp-Sqlite) which may not be required for everyone.

  • RaymondPerkinsRaymondPerkins NZMember

    I use ServiceStack.OrmLite with SQLite. It took a wee bit to get it to work with MonoDroid, but nothing complex or tricky. ServiceStack is actively developed but I do not think it automatically loads related entities.

  • RinkeRinke NLMember ✭✭
    edited June 2013

    Thanx for sharing @GuillermoGutierrez ! Guess its not that good then after all..

    @RaymondPerkins Any chance of sharing your port of ServiceStack somewhere public? ;-)

    Edit: never mind, found the ports for Android and iOS: https://github.com/ServiceStack/ServiceStack/tree/master/release/latest

  • RaymondPerkinsRaymondPerkins NZMember

    Hi Rinkie. The Android iOS assemblies does not include ormlite. But the process to get it going is just a matter of building a assembly for ormlite from source, which is readily available from your link (https://github.com/ServiceStack/ServiceStack).
    I had issues with a reference to IDBConnectionFactory and System.ComponentModel.DataAnnotations which has solved by building the necessary files from Mono. The two files attached have the project files I used for VS2010.
    The DataAnnotations zip has a project with all the files needed, the key to build is to put 'NET_4_0, MOBILE' as a compilation symbol.
    To build ServiceStack.OrmLite and ServiceStack.OrmLite.Sqlite:
    Git the ServiceStack repo (https://github.com/ServiceStack/ServiceStack.OrmLite)
    Extract the two folders in the servicestack zip to the local repo, make sure both projects link the relevant files, reference the DataAnnotations project and reference the assemblies from here: https://github.com/ServiceStack/ServiceStack/tree/master/release/latest

    That should now all build.

  • @GuillermoGutierrez, it would be good if you could share your experience on evaluating OpenNETCF.ORM
    Thanks

  • @GuillermoGutierrez Many thanks for the info.

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    I'm finally creating a simple ORM on top on SQLite.Net for adding OneToOne, OneToMany, ManyToOne and ManyToMany with inverse relationships. The good thing is that it's 100% compatible with SQLite.Net, it only adds some convenience methods for obtaining, creating and updating the children relationships.

    I'm using reflection for discovering the ForeignKey, PrimaryKey and Inverse properties and it's working fine by now.

  • RinkeRinke NLMember ✭✭

    @GuillermoGutierrez Sounds good! Did you modify the sqllite sources, for example to add the ForeignKeyAttribute?

    Would love to look at it for some inspiration. Is it on github by any chance?

  • PierceBogganPierceBoggan USForum Administrator, Xamarin Team, Developer Group Leader Xamurai

    Awesome work @GuillermoGutierrez! Can't wait to see it!

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    Just finished the a little of documentation for my ORM extension on top of sqlite-net:
    https://bitbucket.org/twincoders/sqlite-net-extensions

    Read and write operations are currently working for all kind of relationships.

    I'm quite proud of the result and will be using it in my current projects, so expect it to be alive for at least the following months :D

    Probably I'll change its name... :/

  • BrianBirdBrianBird USUniversity ✭✭

    I need exactly what you've created. Thanks! But, I'm curious, why didn't ServiceStack OrmLite work for you?

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    @BrianBird OrmLite is very powerful, and I love the text-blob feature, but the lack of Many-To-Many and One-To-One relationships and the fact that MonoTouch and MonoDroid (my main development platforms) are not officially supported convinced me to not use it.

    I needed something much simpler, and preferably PCL compliant, that's why I created this extension library.

    I have some improvements in mind for my SQLite-net extension, maybe text-blob simple relationships, cascade operations and some other candy. But right now it is fully functional and only took one week to code: less than the time I spent evaluating other ORM alternatives :)

  • kwlkwl USInsider, Developer Group Leader ✭✭

    @GuillermoGutierrez Wow, looks awesome. I'll give it a try!

  • RinkeRinke NLMember ✭✭
    edited March 2014

    @GuillermoGutierrez

    I noticed that SQLite-Net Extensions is by default configured to build for MvvmCross, including only NuGet package references to MvvmCross-specific libraries. I had some trouble deciding which non-MvvmCross PCL version of SQLite.Net to choose (there are several options on NuGet).

    Which SQLite.Net PCL would you recommend ?

  • SKallSKall USMember ✭✭✭✭

    SQLite.Net.Async is worth a look. I pushed generic BLOB support to it so pretty soon you should be able to push any serializable objects into the tables (with f.e. JSON serializer).

  • JeremyKolbJeremyKolb USMember ✭✭✭

    Does SQLite-Net Extensions support async? I can't find a way to make the two work together.

  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    @JeremyKolb‌ I haven't used SQLite-Net async yet. It shouldn't be difficult to adapt the library to use the async version, but I didn't have that need yet. You can wait until it's migrated (no promises here) or migrate it yourself (should be easy). Contributions are always welcome!

    Cheers.

  • CharlesHoranCharlesHoran USMember ✭✭

    @GuillermoGutierrez‌

    So far I do like the net extensions however I do have a quick question....(sorry about that)
    Why does this object store none of the data associated with the textblob attributes?

    public class MessageModel
        {
            [TextBlob("ToBlob")]
            public List<MessageAddress> To { get; set; }
            [TextBlob("SenderBlob")]
            public MessageAddress Sender { get; set; }
            [PrimaryKey]
            public string Id { get; set; }
            public DateTime CreatedAt { get; set; }
            public string Subject { get; set; }
            public string Message { get; set; }
            public bool Read { get; set; }
    
            [ForeignKey(typeof(Folder))]
            public string FolderName { get; set; }
    
            [ManyToOne]
            public Folder Folder { get; set; }
            //Blobbed fields
            public string ToBlob { get; set; }
            public string SenderBlob { get; set; }
        }
    
  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭

    @CharlesHoran How are you inserting and reading the objects from the database? You have to use the WithChildren variant of the SQLite-Net methods or call GetChildren or GetChild to fetch the relationships from the database.

  • CharlesHoranCharlesHoran USMember ✭✭
    edited October 2014

    Good Morning @GuillermoGutierrez‌, sorry for the delay in responding.

    The objects are inserted with:
    conn.InsertWithChildren(inbox); //inbox is a Folder with 3 messages

    and read with

    var val= conn.GetAllWithChildren<Folder>().OrderBy(x => x.System).ThenBy(x => x.Name).ToList();

    The odd thing is that another object that uses the MessageAddress class as a textblob works just fine.

    In case this is being caused by the hierarchy here it the full class hierarchy.

    public class MessageAddress
        {
            [Indexed]
            public string Name { get; set; }
            [PrimaryKey]
            public string Address { get; set; }
            public string ImageUri { get; set; }
        }
    
    public class MessageModel
        {
            [TextBlob("ToBlob")]
            public List<MessageAddress> To { get; set; }
            [TextBlob("SenderBlob")]
            public MessageAddress Sender { get; set; }
            [PrimaryKey]
            public string Id { get; set; }
            public DateTime CreatedAt { get; set; }
            public string Subject { get; set; }
            public string Message { get; set; }
            public bool Read { get; set; }
    
            [ForeignKey(typeof(Folder))]
            public string FolderName { get; set; }
    
            [ManyToOne]
            public Folder Folder { get; set; }
            //Blobbed fields
            public string ToBlob { get; set; }
            public string SenderBlob { get; set; }
        }
    
     public class Folder
        {
            [PrimaryKey]
            public string Name { get; set; }
            public bool System { get; set; }
            public string Icon { get; set; }
    
            [OneToMany(CascadeOperations=CascadeOperation.All)]
            public List<MessageModel> Messages { get; set; }
        }
    
    //For completeless, this class works just fine....
    public class AdminMessageModel
        {
            [TextBlob("AddressBlobbed")]
            public MessageAddress Sender { get; set; }        
            [PrimaryKey]
            public string Id { get; set; }
            public DateTime CreatedAt { get; set; }
            public DateTime ModifiedAt { get; set; }
            public string Message { get; set; }
            public string AddressBlobbed { get; set; }
        }
    
  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭
    edited October 2014

    Hey @CharlesHoran‌ ,

    You are fetching Folder objects and their direct children (Messages property), but you're not loading (and probably not writing either) children of Message objects, so TextBlob properties are not getting processed.

    Try setting the recursive parameter of the methods to true:

    // Write recursively
    conn.InsertWithChildren(inbox, recursive: true);
    
    // Read recursively
    var val= conn.GetAllWithChildren<Folder>(recursive: true).OrderBy(x => x.System).ThenBy(x => x.Name).ToList();
    

    Hope it helps.

  • CharlesHoranCharlesHoran USMember ✭✭

    @GuillermoGutierrez‌
    I tried the recursive flag, to no effect.
    However I believe I have found the root issue. When GetChildrenRecursive is called with onlyCascadeChildren=true it excludes TextBlobs.

    From ReadOperations Line 166:

    private static void GetChildrenRecursive(this SQLiteConnection conn, object element, bool onlyCascadeChildren, bool recursive, ObjectCache objectCache =  null) {
                objectCache = objectCache ?? new ObjectCache();
    
                foreach (var relationshipProperty in element.GetType().GetRelationshipProperties())
                {
                    var relationshipAttribute = relationshipProperty.GetAttribute<RelationshipAttribute>();
    //This if statement excludes textblobs from a recurisive call (it is called with onlyCascadeChildren=true
                    if (!onlyCascadeChildren || relationshipAttribute.IsCascadeRead)
                        conn.GetChildRecursive(element, relationshipProperty, recursive, objectCache);
                }
            }
    

    I modified it to:
    private static void GetChildrenRecursive(this SQLiteConnection conn, object element, bool onlyCascadeChildren, bool recursive, ObjectCache objectCache = null) {
    objectCache = objectCache ?? new ObjectCache();

                foreach (var relationshipProperty in element.GetType().GetRelationshipProperties())
                {
                    var relationshipAttribute = relationshipProperty.GetAttribute<RelationshipAttribute>();
                    if (!onlyCascadeChildren || relationshipAttribute.IsCascadeRead || relationshipAttribute is TextBlobAttribute)
                        conn.GetChildRecursive(element, relationshipProperty, recursive, objectCache);
                }
            }
    
  • GuillermoGutierrezGuillermoGutierrez ESMember ✭✭✭
    edited October 2014

    @CharlesHoran‌ It makes sense. I will add some tests and see how it goes. Thanks for the report.

    As a workaround you can set CascadeOperations = CascadeOperation.All in the TextBlob attribute.

Sign In or Register to comment.