Migrate Libraries and Playlists from Rhapsody to Spotify

by Joe Havelick 24. August 2011 18:58
UPDATE:  I have created a tool to automate most of this process. Please see Rhapsody2Spotify

If you're a Rhapsody user looking to try out Spotify, one of the frustrating things will be the lack of your historical playlists. Rhapsody appears to have done everything it can to prevent you from easily sharing out a library or playlist in a portable format, but there is a fairly simple approach to migrate a playlist or library.

Step 1: Export your library

Option 1:

You're going to want to export the contents of the database table which stores your track information. It's a SQL Lite database, and basic instructions on accessing it can be gathered here. I would suggest using the following SQL command, which will exclude tracks you have deleted:

SELECT ARTIST_NAME, TRACK_NAME
FROM TRACK
WHERE In_Library = 1

Alternately, you could just generate a list for a single playlist using the following:

SELECT ARTIST_NAME, TRACK_NAME
FROM Track
INNER JOIN Playlist_Track ON  Playlist_Track .Track_ID = Track.Track_ID
INNER JOIN Playlist ON  Playlist_Track .Playlist_ID = Playlist.Playlist_ID
WHERE In_Library = 1
AND Playlist_Name = 'Blues'

Save the results to a CSV file.

Option 2:

Right click on a playlist, and select share. Email the playlist to yourself. Copy and paste just the artists and tracks to Excel

Step 2: Remove the quotes

It appears that there is no way to export to CSV from SQLit without double quote qualifiers. The tool that we will use doesn't like that. Simply open the CSV in Excel and save it as a CSV. Alternately, you could do a Replace in notepad.

Step 3: Migrate

The tool we will use is located at http://www.ivyishere.org/. Insomuch as it might be tempting to upload our file, it did not work for me. However, if you open the CSV as a text file, then use the copy/paste mode, I had very good success.

Step 4: Paste into Spotify

Copy the results as instructed. Load the Spotify desktop all, create a new playlist, select it, then click on Paste from the edit menu.

Note: Ivy only imports by Artist and Track name. Although we could export a lot more, it would only consume extra space :-/

Happy listening.

Tags:

Tech Tips

Restore All SQL Databases from a Backup File

by Joe Havelick 5. July 2011 09:22

As part of a  disaster recovery plan, I recently needed to have a script to restore all the databases, from all the backups (Full and Differential) from a single backup file. Here's how I did it:

 

SET NOCOUNT ON;

DECLARE @DBName nvarchar(256)
DECLARE @BackupFile nvarchar(256) = N'\\SERVER\PATH\FILE.bak' 
DECLARE @FileID int

DECLARE @BackupHeaders TABLE
      (
      BackupName nvarchar(128) NULL,
      BackupDescription  nvarchar(255) NULL,
      BackupType smallint NULL,
      ExpirationDate datetime NULL,
      Compressed tinyint NULL,
      Position smallint NULL,
      DeviceType tinyint NULL,
      UserName nvarchar(128) NULL,
      ServerName nvarchar(128) NULL,
      DatabaseName nvarchar(128) NULL,
      DatabaseVersion int NULL,
      DatabaseCreationDate  datetime NULL,
      BackupSize numeric(20,0) NULL,
      FirstLSN numeric(25,0) NULL,
      LastLSN numeric(25,0) NULL,
      CheckpointLSN  numeric(25,0) NULL,
      DatabaseBackupLSN  numeric(25,0) NULL,
      BackupStartDate  datetime NULL,
      BackupFinishDate  datetime NULL,
      SortOrder smallint NULL,
      CodePage smallint NULL,
      UnicodeLocaleId int NULL,
      UnicodeComparisonStyle int NULL,
      CompatibilityLevel  tinyint NULL,
      SoftwareVendorId int NULL,
      SoftwareVersionMajor int NULL,
      SoftwareVersionMinor int NULL,
      SoftwareVersionBuild int NULL,
      MachineName nvarchar(128) NULL,
      Flags int NULL,
      BindingID uniqueidentifier NULL,
      RecoveryForkID uniqueidentifier NULL,
      Collation nvarchar(128) NULL,
      FamilyGUID uniqueidentifier NULL,
      HasBulkLoggedData bit NULL,
      IsSnapshot bit NULL,
      IsReadOnly bit NULL,
      IsSingleUser bit NULL,
      HasBackupChecksums bit NULL,
      IsDamaged bit NULL,
      BeginsLogChain bit NULL,
      HasIncompleteMetaData bit NULL,
      IsForceOffline bit NULL,
      IsCopyOnly bit NULL,
      FirstRecoveryForkID uniqueidentifier NULL,
      ForkPointLSN decimal(25, 0) NULL,
      RecoveryModel nvarchar(60) NULL,
      DifferentialBaseLSN decimal(25, 0) NULL,
      DifferentialBaseGUID uniqueidentifier NULL,
      BackupTypeDescription  nvarchar(60) NULL,
      BackupSetGUID uniqueidentifier NULL,
      CompressedBackupSize binary(8) NULL
);

DECLARE @FileList TABLE
      (
      LogicalName nvarchar(128) NOT NULL,
      PhysicalName nvarchar(260) NOT NULL,
      [Type] char(1) NOT NULL,
      FileGroupName nvarchar(120) NULL,
      Size numeric(20, 0) NOT NULL,
      MaxSize numeric(20, 0) NOT NULL,
      FileID bigint NULL,
      CreateLSN numeric(25,0) NULL,
      DropLSN numeric(25,0) NULL,
      UniqueID uniqueidentifier NULL,
      ReadOnlyLSN numeric(25,0) NULL ,
      ReadWriteLSN numeric(25,0) NULL,
      BackupSizeInBytes bigint NULL,
      SourceBlockSize int NULL,
      FileGroupID int NULL,
      LogGroupGUID uniqueidentifier NULL,
      DifferentialBaseLSN numeric(25,0)NULL,
      DifferentialBaseGUID uniqueidentifier NULL,
      IsReadOnly bit NULL,
      IsPresent bit NULL,
      TDEThumbprint varbinary(32) NULL
 );

DECLARE @DataFilePath nvarchar(256) = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\';
DECLARE @LogFilePath nvarchar(256)  = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\';

--Load backup headers (Backup level Information)
DECLARE @RestoreStatement nvarchar(256);
SET @RestoreStatement = N'RESTORE HEADERONLY FROM DISK=N''' + @BackupFile + '''' ;
INSERT INTO @BackupHeaders
      EXEC(@RestoreStatement);

DECLARE iDB CURSOR FOR SELECT DISTINCT DatabaseName FROM @BackupHeaders 
--For each DB
OPEN iDB
FETCH NEXT FROM iDB INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN 
	IF @DBName IN ('master','msdb','model','tempdb') GOTO NextDB;	

	PRINT '';
	PRINT '';
	PRINT '-- Restoring :' + @DBName ;
	
	DECLARE @DBNewName nvarchar(256) = N'Restored_' + @dbName ;
	PRINT '--- Destination Database Name: ' + @DBNewName;
	
	--Get the file list for this DB
	SET @RestoreStatement = N'RESTORE FILELISTONLY
		 FROM DISK=N''' + @BackupFile + ''' WITH FILE=' + CAST((SELECT TOP 1 Position from @BackupHeaders WHERE DatabaseName = @DBName) as nvarchar(10));;
	DELETE FROM @FileList;
	INSERT INTO @FileList
		EXEC(@RestoreStatement);
	--SELECT * FROM @FileList;
	
	--Get the physical file names
	DECLARE @DataFileName nvarchar(256) = REPLACE((SELECT LTRIM(RTRIM(RIGHT(PhysicalName,CHARINDEX('\',REVERSE(PhysicalName)) - 1))) AS DataFileName FROM @FileList WHERE Type = 'D' ), '.mdf', '');
	DECLARE @LogFileName nvarchar(256)  = REPLACE((SELECT LTRIM(RTRIM(RIGHT(PhysicalName,CHARINDEX('\',REVERSE(PhysicalName)) - 1))) AS DataFileName FROM @FileList WHERE Type = 'L' ), '.ldf', '');
	PRINT '--- Destination DataFileName: ' + @DBNewName;
	PRINT '--- Destination LogFileName: ' + @DBNewName;

	DECLARE @LogicalDataFileName nvarchar(256) = (SELECT LogicalName FROM @FileList WHERE Type = 'D');
	DECLARE @LogicalLogFileName nvarchar(256)  = (SELECT LogicalName FROM @FileList WHERE Type = 'L');
	PRINT '--- LogicalDestination DataFileName: ' + @DBNewName;
	PRINT '--- LogicalDestination LogFileName: ' + @DBNewName;

	
	DECLARE iBackup CURSOR FOR SELECT Position FROM @BackupHeaders WHERE DatabaseName = @DBName ORDER BY Position ASC 
	--For each backup, in sequence
	OPEN iBackup
	FETCH NEXT FROM iBackup INTO @FileID
	WHILE @@FETCH_STATUS = 0
	BEGIN 
		
		--Restore the file
		PRINT '---- Restoring FileID: ' + CONVERT(nvarchar(5),@FileID)
			
		DECLARE @RestoreSQL nvarchar(max)= N'
		RESTORE DATABASE 
			[' + @dbNewName + ']
			FROM  DISK = N''' + @BackupFile + '''
			WITH  FILE = ' + CONVERT(nvarchar(5),@FileID) + ',
			MOVE N''' + @LogicalDataFileName + ''' TO N''' + @DataFilePath + @DataFileName + ''',
			MOVE N''' + @LogicalLogFileName + ''' TO N''' + @LogFilePath + @LogFileName + ''',
			NORECOVERY,  
			NOUNLOAD,  
			REPLACE'
		--PRINT @RestoreSQL

		EXEC(@RestoreSQL)
		
		PRINT '';
	NextFile:
	FETCH NEXT FROM iBackup INTO @FileID
	END 
	CLOSE iBackup
	DEALLOCATE iBackup
	
	--Recover the database
	PRINT '--- Recovering ' + @DBNewName
	RESTORE Database @DBNewName WITH RECOVERY
	

NextDB:
FETCH NEXT FROM iDB INTO @DBName
END 
CLOSE iDB
DEALLOCATE iDB

 

Note:

  • This was not engineered to handle incrementals, but I believe it should be easily accomidated with a couple small changes (if any)
  • This excludes the system databases via the 'IF @DBName IN...' statement.

Tags:

Tech Tips

Boston City Tour

by Joe Havelick 28. May 2011 08:26

Yesterday, I decided to take a break from the norm, and do some flying over the City of Boston. We flew down Rt.90 to the head of the Charles, and took some snapshots of Fenway Park. I decided to see how well this matched up with Google Earth.

As well, the entire flight path can be viewed in Google Earth with this file: 110527GPSLog.kmz (259.89 kb)

Tags:

Aviation | General

General Aviation Stack Exchange

by Joe Havelick 28. May 2011 06:40

For those of you that are pilots, pilots in training, or an avid entusiast of general aviation, I suggest you support the General Aviation Stack Enchange proposal.

A stack exchange is a Q&A site that allows community members to ask and answer questions about a certain topic. While this doesn't seem to exciting, there are a number of existing Exchanges for Software Development, Cooking, Math, etc., all of which by virtue of how they work, quickly becomes a definative source of information for a specific community. It's not trying to be the "Facebook" of aviation (there are enough of those), but it would become an awesome resource for pilots. If you ever wonder about things like what you need to do to fly over Fenway (inside the Class Bravo), or want to be able to share your knowledge, then please use the link above, and consider committing to support the community. No money. No significant effort required. Simply a couple of clicks.

Tags:

Aviation

Aerobatic Flight Data Recorder Trial 5

by Joe Havelick 16. April 2011 10:33

(see part one of this article)

1302967162-00422-146.115.171.212.kmz (357.16 kb)

I brought the data recorder up yesterday for my second flight of the season. Although my flying was a bit rusty, the unit performed perfectly. You'll notice a significant difference in the roundness of my loops, and half cubans, and precision on the stalls.

I've found that a rechargable battery was not sufficient for an hour flight, but a regular alkaline battery works perfectly. Thus I was able to log my landings, including a go around for spacing.

On the side, I spent some time working with a Netduino Mini as a potential replacement for the Arduino. It is a much smaller form factor, and would allow for much simpler debugging.

Tags:

Aviation

Review: Jawbone JAMBOX

by Joe Havelick 16. April 2011 09:42

The Jawbone JAMBOX is a phenomenal sounding portable speaker that is a little smaller than a bottled water. It's battery powered, bluetooth compatible, and ready for use with an iPhone, laptop, or any other device. At $200, it's a bit on the pricy side, but in my opinion, it's worth every penny.

 

The thing that first caught my attention of this unit was the sound quality. My brain still has trouble accepting that the sound I hear comes from this unit. Especially surprising is the amount of bass that it musters up. At full volume, this thing has some thump. The mid and high ranges are well balanced as well. There are limits to the loudness of the unit, but I think it will suffice for most indoor scenarios, and some outdoor.

The unit itself can be used comepletely wireless. It has an internal battery that is said to last 10 hours. Although I haven't tested this, I will say that I've used it for some time, and the battery still reports full. It can also be plugged in (and recharged) via a standard USB mini plug. You can broadcast to the speaker using Bluetooth or a standard phone cable. I'm not a wizard with Bluetooth protocals, but I will say that there are ones that sound just okay. This is not one of them, it sounds great.

Out of the box, the unit comes with some nice accessories. It comes with (2) usb mini cables (short and long), a wall plug, a compact phone cable, and a neoprene case that adds some protection with very little bulk.

It is well integrated with the iPhone. After pairing, I was surprised to see that there was a little batter power meter for it in my iPhone status bar. The unit is "upgradable" through http://mytalk.jawbone.com, including some alternate languages and tools. Oh, did I not mention that it's also a kick ass speakerphone too.

I find this unit ideal for listening to music/podcasts or watching movies

  • In the shower,
  • while working out,
  • at my workbench,
  • doing laundry,
  • grilling on the back porch,
  • or in a hotel room.

In summary, you really need to see and hear this unit to get a feel for how mind bending it is. It's size and battery power make it ideal for portability. It's quality (sound and design) make it an awesome product that enriches my life.

Oh, and then there's this:

Tags:

Reviews

About Me

Joe Havelick is a reasonable facsimile of this photo.


profile for Joe on Stack Exchange, a network of free, community-driven Q&A sites

Recommendations