MigrateDB0To2

From SongbirdWiki

To rescue your databases, first, download this SQL script: http://bugzilla.songbirdnest.com/attachment.cgi?id=5114

Then, you need to run this script on each of your databases:

 sqlite3 $SONGBIRDPROFILE/db/main@library.songbirdnest.com.db < migrate.sql
 sqlite3 $SONGBIRDPROFILE/db/web@library.songbirdnest.com.db < migrate.sql
 sqlite3 $SONGBIRDPROFILE/db/hypem.com%252F.db < migrate.sql

Where $SONGBIRDPROFILE is the location of your personal profile, and is something like:

For Linux:

 ~/.Songbird1/Profiles/yourprofilehere/ 

For Mac:

 ~/Library/Application Data/Songbird1/Profiles/yourprofilehere/ 

and I have no idea what it is on Windows but it should be pretty easy to find.

Note: you may have other library files that need converting too, if you visit other sites that do full songbird integration and create their own library.

An Easier (Maybe) Solution

This solution runs entirely inside your Songbird. Please follow the steps carefully. Your precious data is on the line.

1. Back up your profile directory. If you can't do that, you'll just have to hope for the best.
2. Save the block of code below into a file. Remember where it went.
3. Open Songbird, bypassing the "Do you want to delete your data?" dialog with "Continue Anyway".
4. Click on the Add-ons bookmark or any other non-media-containing URL.
5. Open the debugger by putting "x-jsd:debugger" into the location bar and pressing enter.
6. In the debugger's bottom text-bar type: /loadd file://<the filename>. In my case this was: /loadd file:///Users/pvh/migrate.js. On OS X and Linux your path will begin with a 3rd "/". On Windows, it will begin with a drive letter.
7. Say "OK" when I ask if you really did back up your database.
8. Click OK for any files that look like databases. In my case this was:

• • hypem<somethingsomethingsomething>.db
  • main@library.songbirdnest.com
  • web@library.songbirdnest.com
  • HIT CANCEL FOR EVERYTHING ELSE.

1. Restart Songbird, you're done.
   1. If everything went according to plan, you should now be happy and everything should work.
   2. IF NOT: send an email to pvh@songbirdnest.com about what went wrong and hopefully we can fix the process.

Good luck everyone.

Paste this code below into a file.

(function(){
var queryStrings = 
 [
  "drop trigger tgr_media_items_simple_media_lists_delete;",
  "create table resource_properties_new (media_item_id integer not null, property_id integer not null, obj text not null, obj_sortable text, primary key (media_item_id, property_id));",
  "insert into resource_properties_new \
 select \
  0, \
  rp.property_id, \
  rp.obj, \
  rp.obj_sortable \
 from \
  resource_properties rp \
 where \
  rp.guid = (select value from library_metadata where name = 'resource-guid');",
"insert into resource_properties_new \
 select \
  mi.media_item_id, \
  rp.property_id, \
  rp.obj, \
  rp.obj_sortable \
 from \
  resource_properties rp \
  join media_items mi on rp.guid = mi.guid;",
  "drop table resource_properties;",
  "alter table resource_properties_new rename to resource_properties;",
  "create index idx_resource_properties_property_id_obj on resource_properties (property_id, obj);",
  "create index idx_resource_properties_obj_sortable on resource_properties (obj_sortable);",
  "create index idx_resource_properties_media_item_id_property_id_obj_sortable on resource_properties (media_item_id, property_id, obj_sortable);",
  "create index idx_resource_properties_property_id_obj_sortable_media_item_id on resource_properties (property_id, obj_sortable, media_item_id);",
  "create trigger tgr_media_items_simple_media_lists_delete before delete on media_items \
     begin \
      delete from simple_media_lists where member_media_item_id = OLD.media_item_id or media_item_id = OLD.media_item_id; /**/ \
      delete from resource_properties where media_item_id = OLD.media_item_id; /**/ \
    end;",
  "update library_metadata set value = '2' where name = 'version';"
];

// now update your database files
if(!confirm("Beginning the update. Did you back up your profile directory?")) {
  return;
}

var Cc = Components.classes;
var Ci = Components.interfaces;

var dbDirectory = Components.classes["@mozilla.org/file/directory_service;1"]
                     .getService(Components.interfaces.nsIProperties)
                     .get("ProfD", Components.interfaces.nsIFile);
var ios = Cc["@mozilla.org/network/io-service;1"]
                .createInstance(Ci.nsIIOService);

dbDirectory.append("db");

var dbFiles = dbDirectory.directoryEntries;

while (dbFiles.hasMoreElements()) { 
  
  var libfile = dbFiles.getNext().QueryInterface(Ci.nsIFile);
  if (!libfile.isFile()) { continue; } // skip .. and . 
  if (!confirm("Convert this file? \n" + libfile.path )) { continue; }
  // todo: only try to convert actual DB files

  var guid = libfile.leafName;
  for(var i = 0; i < queryStrings.length; i++) {
    var dbq = Cc["@songbirdnest.com/Songbird/DatabaseQuery;1"]
                 .createInstance(Ci.sbIDatabaseQuery);
    dbq.databaseLocation = ios.newFileURI(dbDirectory);
    dbq.setDatabaseGUID(guid.substring(0, guid.indexOf(".db")));
    dbq.addQuery(queryStrings[i]);
    dbq.execute();
    dbq.waitForCompletion();
    if (dbq.getLastError()) {
      alert("FAILED! Ask for pvh on irc at irc.mozilla.org #songbird.\n" + guid + "\n" + queryStrings[i]);
      break;
    }
  }
}

})();