Easiest way to update database table?

Nov 23, 2010 at 9:10 AM

Hi,

On a dynamic scenario applications will evolve together with their tables and their table structure... creating a new table is no big issue but if we have a big table on the client, and we add a new field/property to the table/model, how do you propose to manage this with Sterling?

I mean if there is another approach than copying the actual table to a temporary table, generate the new structure, copy the old data and then, download the new data on the new structure & delete the old temporary table..

Thanks,

Jose

Nov 29, 2010 at 12:37 AM
Edited Nov 29, 2010 at 12:55 AM

I'm not sure how you could copy the table to a temp table, create the new one and fill the new table with the data from the old and then delete the temp table.

The table name is derived from the name of the class that defines the table structure. For example, on my WP7 device, the "Contacts" class "table" is stored in /Sterling/0/0/ directory. When I create a Contacts table and load 10 contacts into it, then call Database.Truncate(typeof(Contact)), the following are deleted from Isolated Storage:

/Sterling/0/0/0
/Sterling/0/0/1
/Sterling/0/0/2
/Sterling/0/0/3
/Sterling/0/0/4
/Sterling/0/0/5
/Sterling/0/0/6
/Sterling/0/0/7
/Sterling/0/0/8
/Sterling/0/0/9
/Sterling/0/0/10
/Sterling/0/0/11
/Sterling/0/0/keys.dat

Then the directory itself (/Sterling/0/0) is deleted.

If you have two databases registered at the same time, the objects for the second database are stored in /Sterling/1.

Nov 29, 2010 at 11:53 AM
Edited Nov 29, 2010 at 11:54 AM

Hi MDMiller,

I think I made a wrong question, the correct one would be: "If the data model - the class - changes, how do I update the issolated storage table to reflect these changes?".  For example, I add three or more properties.. the new data thas comes from the service might have these data but I'd like to keep the old records with the new format and not having to download & saving them again to issolated storage...

On this scenario what would be the best approach?

Thanks!

Coordinator
Nov 29, 2010 at 12:31 PM

Currently Sterling does not specifically address that - and that's on purpose, to keep it lightweight. I'm thinking about a possible satellite module that can be included optionally to help some of this, but for now what most people are doing is versioning the tables. The tables are referenced by their full type name, so even having a different name space is fine, and the step is to migrate the data from the old class to the new one. In Silverlight this could be done with dynamic modules - i.e. if the database is old version, load a "bridge" module that does the work of migrating the data, then after that it is never loaded again.

Dec 1, 2010 at 8:31 AM

This is definitely going to be a major issue for developers using sterling.  I'm not sure what the right answer is, but even on my dev phone, I have to uninstall and reinstall the app to wipe out the data if I make a schema change.  That won't be an option for my customer's data.  I'm scared that once I go into production, I can NEVER change my schema.

It sounds like sterling doesn't store any schema information with the tables.  Would it be possible to store schema information so that you know when the class has an extra property and add a new "column" to the db?  Would it be easier if we attributed the properties some way?

From what I've used so far I really like Sterling, but I'm holding back from publishing in the Marketplace yet, until I figure out how I'm going to version.