386184 - Need common framework for upgrading/downgrading sqlite DB's

Status: RESOLVED INACTIVE

(Toolkit :: Storage, defect, P5)

Description

[Shawn Wilsher :sdwilsh]

(from conversation with Dietrich)
We keep reproducing the same code over and over again for each new consumer.  We should try to abstract this as best we can and make some common code for all consumers to use.

Comment 1

[[e-mail]]

any ideas how? if you're thinking column detection, we'd need to store that info somewhere (maybe as a query string), i.e. what schema versions use what columns. after that, backing up & blowing away table & resetting schema is pretty simple.

ftr, example of code in question is
http://bonsai.mozilla.org/cvsblame.cgi?file=/mozilla/netwerk/cookie/src/nsCookieService.cpp&rev=1.64#447

Comment 2

[Shawn Wilsher :sdwilsh]

I guess the question I should be asking is what do people expect out of something like this first?

As for dwitte's remark, we could easily add a method to mozIStorageConnection to get and set the Database's schema and wrap that bit of code.

Comment 3

[Dietrich Ayala (:dietrich)]

(In reply to comment #2)
> I guess the question I should be asking is what do people expect out of
> something like this first?
> 
> As for dwitte's remark, we could easily add a method to mozIStorageConnection
> to get and set the Database's schema and wrap that bit of code.
> 

that'd be a good first step.

thunder suggested modeling it on the Rails migration scheme:

http://api.rubyonrails.com/classes/ActiveRecord/Migration.html

maybe something like:

mozIStorageConnection {

  /* provides upgrade and downgrade migrations for a schema version */
  void registerSchemaMigration(PRInt32 version, mozIStorageFunction upgrade, mozIStorageFunction downgrade);

  /* performs migration to this version, using the registered migrations */
  void setSchemaVersion(PRInt32 version);

};

Comment 4

[Asaf Romano (gone)]

Adding a bug-free version of BackupDBFile (from nsNavHistory) to mozIStorageConnection would be nice too, probably taking an optional nsIFile folder argument (otherwise create the backups in the same folder as the db file itself?).

Comment 5

[Myk Melez [:myk] [@mykmelez]]

(In reply to comment #2)
> I guess the question I should be asking is what do people expect out of
> something like this first?

At a high level, I'd like the app to automatically upgrade or downgrade a schema when the user starts a build that uses a different version of the schema.  For an example of one way we've done this already, see the schema migration code in the content pref service:

http://lxr.mozilla.org/mozilla/source/toolkit/components/contentprefs/src/nsContentPrefService.js#732

That code only works for upgrades.  For downgrades, presumably we'd have to update the older version with knowledge of the schemas used by the newer version.

For example, let's say Firefox M is the current stable version of that application, and Firefox N is the next version, currently under development.  And we want users to be able to try out Firefox N beta and then go back to Firefox M without horking their databases.

Once Firefox N's schemas are stable, but before we release the beta, we would update Firefox M in a minor point release with the ability to downgrade from Firefox N's schemas back to Firefox M's schemas.  Then users would be able to switch between the two releases, and each time they switch from one to the other, their schemas are automatically migrated.

Of course there may be cases where a downgrade will result in information loss because the old schema didn't store as much information as the new schema.  And there are probably a bunch of other details to work out.  But code to do basic upgrades and downgrades would be a great start.

Comment 6

[Shawn Wilsher :sdwilsh]

How the download manager and cookies (and maybe places - I can't recall on that) checks for downgrade is that it tries to compile a statement with all the columns that we expect in it.  If it fails, it means the db is not useable to us, but if it succeeds, we know we have all the columns we need, and just update the schema version.  The only thing this requires is that if you change how a column is used, you have to change it's name.

Comment 7

[Dietrich Ayala (:dietrich)]

see also: http://bugzilla.songbirdnest.com/show_bug.cgi?id=7355

Comment 8

[BMO Automation]

Per policy at https://wiki.mozilla.org/Bug_Triage/Projects/Bug_Handling/Bug_Husbandry#Inactive_Bugs. If this bug is not an enhancement request or a bug not present in a supported release of Firefox, then it may be reopened.