Add column to SQLite for WP8 C# table in existing db?

A place to discuss the implementation and style of computer programs.

Moderators: phlip, Moderators General, Prelates

Cervisiae Amatorem
Posts: 57
Joined: Mon Aug 29, 2011 5:47 pm UTC

Add column to SQLite for WP8 C# table in existing db?

Postby Cervisiae Amatorem » Tue Apr 22, 2014 6:41 pm UTC

So I guess I'm a faking-it-till-I'm-making-it Windows Phone app dev. I have to add a column to a table that has already been created and deployed with live user data in it. What and where is the preferred place in code to do this? All my searching has turned up is instructions for ALTER TABLE which I know how to use. What I mean is, what is best practices to actually implement this?

The BFI method is to read from the new column with a try/catch in the Application_Launching handler, if it throws a "column not found" exception then do the alter table. This seems very inelegant.

KnightExemplar
Posts: 5494
Joined: Sun Dec 26, 2010 1:58 pm UTC

Re: Add column to SQLite for WP8 C# table in existing db?

Postby KnightExemplar » Tue Apr 22, 2014 8:04 pm UTC

Cervisiae Amatorem wrote:So I guess I'm a faking-it-till-I'm-making-it Windows Phone app dev. I have to add a column to a table that has already been created and deployed with live user data in it. What and where is the preferred place in code to do this? All my searching has turned up is instructions for ALTER TABLE which I know how to use. What I mean is, what is best practices to actually implement this?


The best practice is to almost never do this.

If this is anything outside of an "upgrade application" situation, you're doing it wrong. I'm betting that your schema design isn't flexible enough. Can you tell us more about your problem?

IE: Tell me how are your tables created right now, and how they work.
First Strike +1/+1 and Indestructible.

Cervisiae Amatorem
Posts: 57
Joined: Mon Aug 29, 2011 5:47 pm UTC

Re: Add column to SQLite for WP8 C# table in existing db?

Postby Cervisiae Amatorem » Tue Apr 22, 2014 9:59 pm UTC

Yes, this is an upgrade, we are adding functionality to an already live app. The tables are implicitly created by magic I don't fully understand. There is a .cs files with declarations in it that looks like this:

Code: Select all

namespace MyProject.Models
{
    using SQLite;
    using System;

    [Table("foos")]
    public class foo : Entity, ICalendarModel
    {
        [Ignore]
        public IfooItemActions Actions { get; set; }

        [AutoIncrement, PrimaryKey]
        public int Id { get; set; }

        [Ignore]
        public bool IsActive
        {
            get
            {
                return !this.IsCompleted && !this.IsDeleted;
            }
        }

        public string Title { get; set; }
        public string Note { get; set; }
        public DateTime CreateDate { get; set; }
        public bool IsCompleted { get; set; }
        public bool IsSkipped { get; set; }
        public bool IsDeleted { get; set; }
    }
}


So this table is in a released app, and is filled on each of our users phones with data. I want to add another field in our next update:

Code: Select all

        public bool IsUrgent { get; set; }


I certainly cannot drop the table and re-add it, I would lose customer data and with it our customer. I added the field into this .cs file and it all works fine on a fresh install; it's the migration path for an update that has me confused.

User avatar
Zabaron
Posts: 113
Joined: Wed Jun 04, 2008 3:33 am UTC
Location: Georgia

Re: Add column to SQLite for WP8 C# table in existing db?

Postby Zabaron » Wed Apr 23, 2014 12:48 am UTC

Is that Entity Framework? Entity Framework has a method for doing migrations. If it's a different ORM, then that probably handles migrations as well, but you'll need to check the docs for whatever framework you're using.

--Zabaron
I love deadlines. I love that wooshing sound they make as they fly by. -Douglas Adams


Return to “Coding”

Who is online

Users browsing this forum: No registered users and 5 guests