Need common framework for upgrading/downgrading sqlite DB's

NEW
Unassigned

Status

()

Toolkit
Storage
P5
normal
11 years ago
2 years ago

People

(Reporter: sdwilsh, Unassigned)

Tracking

Trunk
Future
Points:
---
Dependency tree / graph

Firefox Tracking Flags

(Not tracked)

Details

(Reporter)

Description

11 years ago
(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

11 years ago
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
(Reporter)

Comment 2

11 years ago
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.
(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);

};
(Reporter)

Updated

11 years ago
Depends on: 386366
(Reporter)

Updated

11 years ago
Blocks: 386369
(Reporter)

Updated

11 years ago
Blocks: 386371
(Reporter)

Updated

11 years ago
No longer blocks: 386371
(Reporter)

Updated

11 years ago
No longer blocks: 386369
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?).
(Reporter)

Updated

11 years ago
Depends on: 386394
(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.
(Reporter)

Comment 6

11 years ago
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.
Priority: -- → P5
You need to log in before you can comment on or make changes to this bug.