Ledona Tech

I have to do this every 6 months or so and always have to look it up.  So this is as much a note for me as it is for anyone else reading this.

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘monty’@'localhost’ IDENTIFIED BY ’some_pass’ WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘monty’@'%’ IDENTIFIED BY ’some_pass’ WITH GRANT OPTION;

(note: I’m sure I originally copied this from somewhere)

I have a couple USB drives that I want to automatically mount on my Ubuntu system.  Problem is that since they are USB their device names change, so using the name in fstab doesn’t always work.  So… UUID is the way to go.  So… here’s the line I added to my fstab…

UUID=95edbea6-3793-438d-a09a-a609f88e257c      /mnt/extra1   ext3    rw,auto,user    0       0

To get the UUID use this on the command line…

>sudo vol_id -u  device_name

Got this info from here

Here’s something that I’ve needed to do on occasion. Mostly to give additional IPs for ssh tunneling. I’ve used this mostly on my MacBook, but I’m sure it works in any linux based environment.

sudo ifconfig lo0 add 127.0.10.10

I’ve pulled this down many times from many places on the web. This time around I grabbed it from Extra Pepperoni.

You’re supposed to be able to do this already. But unless I’m losing my mind, it looks like the implementation for random desktop background images does not work the same as random screensaver images. Specifically the random screensaver image selection recursively looks through the folder you select as the image source, while the desktop background image selection does not.

I don’t really care why they did this, but here is a simple work around (inspired by this article at devchix).  Open up a terminal and do the following.

1. Create a folder that will be the desktop background image location:

   > mkdir ~/desktop_image_folder

2. Then run the following command, which will create symbolic links in the new folder, to all the images in the folder that stores your images you want to use:

   > find ~/Pictures/IMAGES -exec ln -s ‘{}’ ~/desktop_image_folder \;

3. In the system preferences select the new folder as the location for your background images.

That’s pretty much it.  There are a couple catches.  If you have a large enough image collection, you will likely have filename collisions,  in which case the latter files will not be linked into the folder.  Second, if you add new files to the original image folder(s) you will need to rerun the command.

I’ve been using Microsoft Office since the late 90s and have a ton of information in it that needs to be migrated. Email, tasks, calendar events, contacts and notes all need to be moved and preserved. There are a number of options for moving each content type to a Mac app.  In the end here are the things that I did.

1. Outlook Contacts to Mac Address Book

Export to text file (.csv) then import using the Mac Address Book import function. Unfortunately this will require some manual tweaking. You’ll have to make sure that the columns in the csv file match up with the address book contact properties. Once this is done let the import proceed. Once complete you may want to check your contacts to make sure that you got what you want.

2. Outlook Email to Thunderbird

This turned out to be the most daunting task for me because I have a very large email archive and wanted to preserve everything. I also wanted to use Thunderbird (I’m not a fan of Mac Mail, or Entourage). Unfortunately, at this time the Mac address book is not used by Thunderbird. However use of the address book is promised for Thunderbird 3.0 and there is a prebuild of Thunderbird 2.0 that has this feature somewhat enabled. Read more at http://forums.mozillazine.org/viewtopic.php?p=3003235 .

So all I needed to do was figure out how to get my mail from Outlook on PC, to Thunderbird on Mac. A little Googling turned up the follow http://guides.macrumors.com/Moving_Email_from_PC_Outlook_to_Apple_Mail . So all that is needed is to copy the entire profile folder from Thunderbird in Windows to Thunderbird in Mac. Additionally, make sure that the transfered profile folder is renamed so that it has the same name as the Mac Thunderbird profile folder that it is replacing, or modify the profiles.ini file in the Mac Thunderbird setup (located at ~/Libraries/Thunderbird/profiles.ini ) so that it includes the new profile. If you do the later you should end up with a profiles.ini file that looks something like the following.

[General]
StartWithLastProfile=0

[Profile0]
Name=default
IsRelative=1
Path=Profiles/1i0y50nd.default

[Profile1]
Name=outlook
IsRelative=1
Path=Profiles/5yd4og12.default

3. Outlook Events to iCal

Transferring events was pretty simple. The following tool when run will generate an iCal export file which can be directly imported into iCal.

http://www.mhsoftware.com/bin/iCalConvert.msi

4. Outlook Notes and Tasks to Eaglefiler

For my Outlook notes and tasks I tried a bunch of different options. In the end I wanted something simple that would allow me to easily view, modify and backup information. Additionally I wanted tagging functionality. Eaglefiler does all of that. It stores data in simple text files, so I can sync and modify the information on other platforms. It has integrated support for encrypted sparseimage files so I can have a higher level of security for some information. And it supports the tagging of notes for categorization and filtering. The only shortcoming is that from what I can tell it does not integrate with iCal (for due date, etc…).

To do the migration I simply exported my Outlook notes and tasks using the CSV export function built into Outlook. Then on the Mac side I opened up the CSV file and saved a separate text file for each entry. Lastly I imported the new text files into Eaglefiler.

Backstory (if your not interested then skip to the meat):

A very bad tech karma week ended in the death of my Windows XP desktop. Everything was backed up, but I was not looking forward to hours installing xp, office, iTunes, etc…. Like many I have recently purchased a shiny new MacBook, and like many I now use it for almost all of my desktop duties. So instead of rebuilding XP, I’ve decided to migrate my desktop life to the Mac.

A number of things need to be migrated, some easy, some much more difficult (I’ll probably have future posts chronicling some of migration tasks). My priorities being what they are, the first and most critical migration is iTunes. The problem is that after a couple hours of googling I could not find a solution that made me happy. Wht would make me happy?

Requirements For iTunes Migration:

Apart from migrating the music itself there is a lot of metadata that I wanted to preserve, specifically I wanted the last played date, play count, skip count, last skipped date, date added and rating. In addition, I like the folder structure of my music files, so if possible I would like to maintain it.

What I Found:

A little googling turned up some helpful links, but nothing that completely satisfied my needs. I found applescript snippets that showed how to programmatically update much of the metadata that I wanted to preserve (with one very significant exception). I also found details on importing a music library using iTunes (unfortunately it does not import all of the metadata).

What I ended up doing (i.e. the meat):

Ultimately I decided to cobble together my own solution. So here’s what I did.

1. Get the old library XML file
2. Place your music files where they need to be
3. Update the old XML library file to point to the new XML files
4. Import the music
5. Import the metadata

Please,… before you go any further keep the following in mind. The following steps assume basic programming skills (specifically applescript and PHP). Review the code and backup your music and system before you do anything.

1. Get the old XML library file

There are two data files that iTunes uses to manage its data, a binary library file and an XML library file. The XML file is what you want. Under XP this file is typically located in c:\Documents and Settings\username\My Documents\My Music\iTunes\iTunes Music Library.xml .

2. Place your music files where they need to be

Place your music files where you want them. Figure out if you want your music on an internal drive or an external drive. Decide if you want iTunes to manage your file locations or if you want to manage your music folder manually. I decided to place my music on an external drive. Also, I am not yet a complete fan of letting iTunes move my music files around, so I turn that option off and make iTunes leave my music files where I put them.

3. Update the file paths in the XML file

The next step was to modify the paths in the XML library file so that they point to the new music path. This should be pretty straight forward. Open up the XML file in whatever text editor you like, do a simple search/replace which replaces the old path with the new location.

4. Import the music

This is one thing that iTunes will do for you (assuming that step 3 worked out). Open iTunes on your Mac. Go to File >> Import. Then in the file selection dialog select the updated version of the old XML library file. If the paths are correct iTunes should import your old music info. If you told iTunes to handle where your files are placed, it should also move your music files to the music location specified in the iTunes preferences.

5. Import the MetaData

If you don’t need to import metadata not included in the import performed in step 4 then you are done. However, as stated previously there are some pieces of data that I wanted preserved namely skipped date, skip count, last played, play count and date added. I wrote two scripts to do the job. The first is a PHP script that extracts the metadata from the XML Library file. The PHP script then generates an intermediate shell script that calls an applescript which tells iTunes to update its library data.

There are a couple important caveats/limitations. First, the applescript must search for the tracks that will be updated. But since there is no known unique identifier guaranteed to be consistent between the old XML library file and the new iTunes library data an alternative is needed. So, these scripts use the following fields to uniquely identify a track; Track Name, Track Artist, Track Album Name, Track Number, Track Size.

These fields in concert should be unique. However, in the off chance that there are still duplicates, the applescript wil output a message saying that a unique match could not be found. A second limitation is that at the time of this blog entry Apple has not provided a way to update the Date Added property for tracks in iTunes. So for now the original add date information will be appended to the track description property. This is obviously not what I really want, but at least the metadata is there, and if/when Apple allows this property to be update or when there is a reasonable work around the data will be there.

So, with no more adieu, here are the two scripts.

iTunesMetaExtract.php

<?php
	// parse the command line and get the input filename
	if ($argc != 2) {
	   die("Please specify the input file and nothing else!");
	}

	$filename = $argv[1];

	// get the XML object
	$xmlData = simplexml_load_file($filename);

	// iterate through the children of dict until we find the key with the value tracks
	$i = -1;
	$indexOfTracksKey = -1;
	$topDictElements = $xmlData->dict->children();
	foreach ($topDictElements as $ele) {
		$i++;
		if ($ele->getName() != "key") {
			continue;
		} else if ((string) $ele == "Tracks") {
			// get the next element should be a dict
			$indexOfTracksKey = $i;
			break;
		}
	}

	// the element that contains all the tracks elements
	$xmlTracksDictEle = $topDictElements[$indexOfTracksKey + 1];
	echo "# The number of tracks in the library is : " . count($xmlTracksDictEle->children()) / 2 . "\n";
	// iterate over every track
	$i = 0;
	foreach ($xmlTracksDictEle->xpath("dict") as $track) {
		// get the track info
		$trackinfo = GetTrackInfo($track);

		// if there is something to update then add the call to the applescript to the output
		if ($trackinfo['Play Count'] != 0 || $trackinfo['Skip Count'] != 0) {
			printf('osascript iTunesUpdateMeta.applescript %s %s %s "%s" "%s" "%s" "%s" "%s" "%s" "%s"' .
				"\n", CleanParam($trackinfo['Name']), CleanParam($trackinfo['Artist']),
				CleanParam($trackinfo['Album']), $trackinfo['Track Number'], $trackinfo['Size'],
				$trackinfo['Play Count'], $trackinfo['Play Date UTC'], $trackinfo['Date Added'],
				$trackinfo['Skip Count'], $trackinfo['Skip Date']);
		} else {
			// nothing to update,... then report that we are skipping this track
			printf('# Skipping (%s)"%s"' . "\n", $trackinfo['Track ID'], $trackinfo['Name']);
		}
	}

	// retrieve all track info form a track element
	function GetTrackInfo($trackEle) {
		// get all child elements
		$childrenEles = $trackEle->children();
		$i = 0;
		$ret = array();
		// iterate through all track element children
		while  ($i < count($childrenEles)) {
			// get the property key and value
			$key = (string) $childrenEles[$i++];
			$value = (string) $childrenEles[$i];

			// if there is no value part to the next element then use the element name,...
			// it is usually the case for true/false values
			if (strlen($value) == 0) {
				$value = $childrenEles[$i] ->getName();
			} else if (($key == "Play Date UTC") || ($key == "Date Added") || ($key == "Skip Date")) {
				// if the value is a key then reformat the data to something that is applescript friendly
				$value = date_create($value)->format("m-d-Y H:i:s");
			}

			// add to hashtable
			$ret[$key] = $value;
			$i++;
		}

		// Fill out the information by adding any missing values that we need
		if (! array_key_exists("Skip Count", $ret)) {
			$ret['Skip Count'] = 0;
		}

		if (! array_key_exists("Skip Date", $ret)) {
			$ret['Skip Date'] = "";
		}

		if (! array_key_exists("Play Count", $ret)) {
			$ret['Play Count'] = 0;
		}

		if (! array_key_exists("Play Date UTC", $ret)) {
			$ret['Play Date UTC'] = "";
		}

		if (!array_key_exists("Album", $ret)) {
			$ret['Album'] = "";
		}

		if (!array_key_exists("Artist", $ret)) {
			$ret['Artist'] = "";
		}

		if (!array_key_exists("Track Number", $ret)) {
			$ret['Track Number'] = "";
		}

		return $ret;
	}

	// make sure that special characters are escaped
	function CleanParam($param) {
		$ret = $param;
		// if the parameter does not contain a single quote,... then just wrap it in single quotes
		if (stripos($ret, "'") === false) {
			$ret = "'" . $param . "'";
		} else {
			// escape special chars
			$ret = str_replace('\\', '\\\\', $param);
			$ret = str_replace('$', '\\$', $param);
			$ret = str_replace("`", "\`", $param);
			$ret = str_replace('"', '\\"', $param);
			$ret = str_replace('`', '\\`', $param);
			$ret =  '"' . $ret . '"';
		}

		return $ret;
	}
?>

iTunesUpdateMeta.applescript

(*
	This applescript is meant to be run on the commandline using osascript.
	It is used to update the metadata for a single track in an iTunes
	library.  The parametes include search values used to identify the
	track and metadata which will be updated.

	The input parameters are in order

	name - The name of the iTunes track (search)
	artist - The track artist (search)
	album - The album on which the track appears (search)
	track number - the track number on the track's album (search)
	track size - The size of the audio file for the track, in bytes (search)
	played count - The number of times that the track has been
	       played (for update)
	last_played_date - The last date that the track was played (for update)
	date_added - The date on which the track was added to the
		library (for update)
	skipped_count - The number of times the track has been
		skipped (for update)
	last_skipped_date - The date of the last time the track
		was skipped (for update)

	All parameters are required.  An empty parameter is acceptable.

	Notes:

	For now,... date added is not updatable via the iTunes object model.
	So the desired date added value is appended to the end of the
	description property of the track.  The information will be of the
	form...

<true_add_date>Saturday, December 23, 2006 7:17:17 AM</true_add_date>

	If no track matching the search criteria is found, or if multiple
	tracks are found, then no update will be done and a
	message displaying the search problem will be output to stdout.

	The skipped count and play count currently stored in the library
	will be incremented by the amount specified in the command line.

	The assumption is that the data being used to update the iTunes
	Library comes from a previously used and no longer in use library.
	Therefore if the skipped count or played count is greater than 0,
	it is assumed that the last played or skipped date for the track
	will be more recent than it was in the previous library and no
	update to these fields will be performed.
*)

on run argv
	-- make sure there are exactly 10 args
	if (count of argv) is not 10 then
		return (count of argv) & " arguments given!" & Usage()
	end if

	-- PARSE THE COMMANDLINE PARAMETERS
	set _trackname to item 1 of argv
	set _artist to item 2 of argv
	set _album to item 3 of argv
	set _tracknum to item 4 of argv
	set _tracksize to item 5 of argv as number
	set _playcount to item 6 of argv as number
	if _playcount is greater than 0 then
		set _lastPlayed to date (item 7 of argv)
	else
		set _lastPlayed to ""
	end if
	set _dateadded to date (item 8 of argv)
	set _skipcount to item 9 of argv as number
	if _skipcount is greater than 0 then
		set _skipdate to date (item 10 of argv)
	else
		set _skipdate to ""
	end if

	-- generate a string for reporting the parameters
	set parameters to _trackname & ":" & _artist & ":" & _album & ":" & ¬
		_tracknum & ":" & _tracksize & " -> " & _playcount & ":" & ¬
		_lastPlayed & ":" & _dateadded & ":" & _skipcount & ":" & _skipdate

	tell application "iTunes"
		-- get the iTunes Library Playlist
		set iTunesLibrary to library playlist 1

		-- use the search method to quickly find tracks with the search name
		set tracks_matched_by_name to (search iTunesLibrary for _trackname only songs)

		-- iterate through the returned tracks to find the track that matches the rest of the criteria
		set successfullyFound to false
		repeat with maybet in tracks_matched_by_name

			-- match the track number,... since some of my tracks don't have
			-- track numbers, if no track number exists in the search or
			-- matched track, then handle that as well
			set tracknummatch to (length of _tracknum > 0 and ¬
				_tracknum as number is equal to track number of maybet) or ¬
				(length of _tracknum is equal to 0 and track number of maybet is missing value)

			-- now compare criteria
			if (artist of maybet is _artist) and (album of maybet is _album) and tracknummatch then
				-- if this is the second successful match then we may have
				-- a problem,... lets try and resolve with track size
				if successfullyFound is true then
					-- use the track that matches the size,... 

					-- if the current track matches the tracksize and the previously found
					-- track does not then use the current track if neither track matches the
					-- tracksize, or if both do then we have a problem,... otherwise we are
					-- good because the only other possibility is that the previously found track
					-- matches and the new one does not,... so use the previously found track
					if (size of maybet = _tracksize) and not (_tracksize = size of tracktofix) then
						set tracktofix to maybet
					else if (not (size of maybet = _tracksize) and not (_tracksize = size of tracktofix)) or ¬
						((size of maybet = _tracksize) and (_tracksize = size of tracktofix)) then
						return "Multiple possible files found for ::" & parameters
					end if
				else
					-- looks like ths is the first successful math, so set some variables
					set successfullyFound to true
					set tracktofix to maybet
				end if
			end if
		end repeat

		-- if we found a unique match then perform the updates
		if successfullyFound is true then

			-- Handle played count updates
			if _playcount is greater than 0 then
				if (played count of tracktofix is greater than 0) then
					-- increment the played count if there is already a value
					set played count of tracktofix to (_playcount + (played count of tracktofix))
				else
					-- set the played count and last played date
					set played date of tracktofix to _lastPlayed
					set played count of tracktofix to _playcount
				end if
			end if

			-- Handle skipped count,.. everything here is similar to how play count updates are handled
			if _skipcount is greater than 0 then
				if (skipped count of tracktofix is greater than 0) then
					set skipped count of tracktofix to (_skipcount + (skipped count of tracktofix))
				else
					set skipped count of tracktofix to _skipcount
					set skipped date of tracktofix to _skipdate
				end if
			end if

			-- lastly take care of the add date by appending to the description property
			set description of tracktofix to (description of tracktofix) & "
" & ¬
				_dateadded & ""

			return "UPDATED (" & database ID of tracktofix & ")::" & parameters
		else
			return "NOT FOUND ::" & parameters
		end if
	end tell
end run

-- Command line Usage
on Usage()
	return "
Usage is: <trackname> <artist> <album> <track_number> <track_size> " & ¬
		"<played_count> <last_played_date> <date_added> " & ¬
		"<skipped_count> <last_skipped_date>"
end Usage

I can’t tell you how many times I’ve had to look this up. Here’s a simple example of a bash script that will do something if a file exists.

if [[ -a /path/to/file/filename ]]
then
    echo The File Exists
fi