1 files changed, 326 insertions, 0 deletions

diff --git a/src/load_itunes.py b/src/load_itunes.py
new file mode 100644
index 0000000..f4131b1
--- /dev/null
+++ b/src/load_itunes.py
@@ -0,0 +1,326 @@
+import sqlite3
+import plistlib
+
+library = plistlib.readPlist('iTunes-Music-Library.xml')
+
+
+class BasicLibraryItem(object):
+
+    def get_insert_query(self):
+        return 'INSERT INTO {} VALUES ({})'.format(
+                self.TABLE_NAME, self.get_bind_string(self.COLUMN_COUNT))
+
+    @staticmethod
+    def get_bind_string(count):
+        return ('?,' * count)[:-1]
+
+    @staticmethod
+    def as_data_string(value):
+        if not value:
+            return None
+        else:
+            return value.asBase64()
+
+    @staticmethod
+    def as_boolean(value):
+        return 1 if value else 0
+
+    @staticmethod
+    def format_name(name):
+        return name.lower().replace(' ', '_')
+
+    @classmethod
+    def from_plist_entry(cls, entry):
+        self = cls()
+
+        for key, value in entry.items():
+            name = cls.format_name(key)
+
+            if not hasattr(self, name):
+                raise Exception("No attribute named %r" % name)
+
+            setattr(self, name, value)
+
+        return self
+
+
+class Playlist(BasicLibraryItem):
+
+    COLUMN_COUNT = 21
+    TABLE_NAME = 'playlist'
+
+    def __init__(self):
+        self.playlist_id = None
+        self.playlist_persistent_id = None
+        self.parent_persistent_id = None
+        self.distinguished_kind = None
+        self.genius_track_id = None
+        self.name = None
+
+        self.master = False
+        self.visible = False
+        self.all_items = False
+        self.folder = False
+
+        self.music = False
+        self.movies = False
+        self.tv_shows = False
+        self.podcasts = False
+        self.itunesu = False
+        self.audiobooks = False
+        self.books = False
+        self.purchased_music = False
+
+        self.smart_info = None
+        self.smart_criteria = None
+
+        self.items = []
+
+    def as_dbrow_tuple(self):
+        return (
+            self.playlist_id, #int
+            1, # user_id
+            self.playlist_persistent_id,
+            self.parent_persistent_id,
+            self.distinguished_kind, #int
+            self.genius_track_id, #int
+            self.name,
+
+            self.as_boolean(self.master),
+            self.as_boolean(self.visible),
+            self.as_boolean(self.all_items),
+            self.as_boolean(self.folder),
+            self.as_boolean(self.music),
+            self.as_boolean(self.movies),
+            self.as_boolean(self.tv_shows),
+            self.as_boolean(self.podcasts),
+            self.as_boolean(self.itunesu),
+            self.as_boolean(self.audiobooks),
+            self.as_boolean(self.books),
+            self.as_boolean(self.purchased_music),
+
+            self.as_data_string(self.smart_info),
+            self.as_data_string(self.smart_criteria)
+        )
+
+    @classmethod
+    def from_plist_entry(cls, entry):
+        self = cls()
+
+        for key, value in entry.items():
+            if key == 'Playlist Items':
+                for item in value:
+                    self.items.append(item['Track ID'])
+
+                continue
+
+            name = cls.format_name(key)
+
+            if not hasattr(self, name):
+                raise Exception("No attribute named %r" % name)
+
+            setattr(self, name, value)
+
+        return self
+
+
+class Track(BasicLibraryItem):
+
+    COLUMN_COUNT = 53
+    TABLE_NAME = 'track'
+
+    def __init__(self):
+        self.album_rating = None
+        self.album_rating_computed = False
+        self.comments = None
+        self.volume_adjustment = None
+        self.unplayed = False
+        self.date_added = None
+        self.date_modified = None
+        self.disabled = False
+        self.play_count = None
+        self.play_date = None
+        self.play_date_utc = None
+        self.rating = None
+        self.skip_count = None
+        self.skip_date = None
+        self.track_id = None
+        self.persistent_id = None
+        self.library_folder_count = None
+        self.file_folder_count = None
+        self.location = None
+        self.track_type = None
+        self.file_type = None
+        self.artwork_count = None
+        self.hd = False
+        self.has_video = False
+        self.itunesu = False
+        self.tv_show = False
+        self.podcast = False
+        self.protected = False
+        self.purchased = False
+        self.movie = False
+        self.music_video = False
+        self.bpm = None
+        self.bit_rate = None
+        self.sample_rate = None
+        self.size = None
+        self.total_time = None
+        self.kind = None
+        self.video_height = None
+        self.video_width = None
+        self.sort_album = None
+        self.sort_album_artist = None
+        self.sort_artist = None
+        self.sort_composer = None
+        self.sort_name = None
+        self.sort_series = None
+        self.album = None
+        self.album_artist = None
+        self.artist = None
+        self.clean = False
+        self.compilation = False
+        self.composer = None
+        self.content_rating = None
+        self.disc_count = None
+        self.disc_number = None
+        self.episode = None
+        self.episode_order = None
+        self.explicit = False
+        self.genre = None
+        self.grouping = None
+        self.name = None
+        self.part_of_gapless_album = False
+        self.release_date = None
+        self.season = None
+        self.series = None
+        self.track_count = None
+        self.track_number = None
+        self.year = None
+
+    def as_dbrow_tuple(self):
+        return (
+            self.track_id,
+            self.persistent_id,
+            self.library_folder_count,
+            self.file_folder_count,
+            self.location,
+            self.track_type,
+            self.file_type,
+            self.artwork_count,
+            self.as_boolean(self.hd),
+            self.as_boolean(self.has_video),
+            self.as_boolean(self.itunesu),
+            self.as_boolean(self.tv_show),
+            self.as_boolean(self.podcast),
+            self.as_boolean(self.protected),
+            self.as_boolean(self.purchased),
+            self.as_boolean(self.movie),
+            self.as_boolean(self.music_video),
+            self.bpm,
+            self.bit_rate,
+            self.sample_rate,
+            self.size,
+            self.total_time,
+            self.kind,
+            self.video_height,
+            self.video_width,
+            self.sort_album,
+            self.sort_album_artist,
+            self.sort_artist,
+            self.sort_composer,
+            self.sort_name,
+            self.sort_series,
+            self.album,
+            self.album_artist,
+            self.artist,
+            self.as_boolean(self.clean),
+            self.as_boolean(self.compilation),
+            self.composer,
+            self.content_rating,
+            self.disc_count,
+            self.disc_number,
+            self.episode,
+            self.episode_order,
+            self.as_boolean(self.explicit),
+            self.genre,
+            self.grouping,
+            self.name,
+            self.as_boolean(self.part_of_gapless_album),
+            self.release_date,
+            self.season,
+            self.series,
+            self.track_count,
+            self.track_number,
+            self.year
+        )
+
+    def usermeta_as_dbrow_tuple(self):
+        return (
+            1,
+            self.track_id,
+            self.album_rating,
+            self.as_boolean(self.album_rating_computed),
+            self.comments,
+            self.volume_adjustment,
+            self.as_boolean(self.unplayed),
+            self.date_added,
+            self.date_modified,
+            self.as_boolean(self.disabled),
+            self.play_count,
+            self.play_date,
+            self.play_date_utc,
+            self.rating,
+            self.skip_count,
+            self.skip_date
+        )
+
+    USERMETA_COLUMN_COUNT = 16
+    USERMETA_TABLE_NAME = 'user_track_metadata'
+
+    def get_usermeta_insert_query(self):
+        return 'INSERT INTO {} VALUES ({})'.format(
+                self.USERMETA_TABLE_NAME,
+                self.get_bind_string(self.USERMETA_COLUMN_COUNT))
+
+
+assert library['Major Version'] == 1, "Invalid major version"
+assert library['Minor Version'] == 1, "Invalid minor version"
+
+def insert_playlists(db, library):
+    playlists = [Playlist.from_plist_entry(entry)
+            for entry in library['Playlists']]
+
+    curs = db.cursor()
+    track_insert_query = playlists[0].get_insert_query()
+
+    for playlist in playlists:
+        curs.execute(track_insert_query, playlist.as_dbrow_tuple())
+
+        for item in playlist.items:
+            curs.execute('INSERT INTO playlist_track VALUES (?, ?)',
+                    (playlist.playlist_id, item))
+
+    db.commit()
+
+
+def insert_tracks(db, library):
+    music_folder = library['Music Folder']
+    tracks = [Track.from_plist_entry(entry)
+            for entry in library['Tracks'].values()]
+
+    curs = db.cursor()
+    track_insert_query = tracks[0].get_insert_query()
+    usermeta_insert_query = tracks[0].get_usermeta_insert_query()
+
+    for track in tracks:
+        curs.execute(track_insert_query, track.as_dbrow_tuple())
+        curs.execute(usermeta_insert_query, track.usermeta_as_dbrow_tuple())
+
+    db.commit()
+
+
+db = sqlite3.connect('iTunesLibrary.db')
+#insert_playlists(db, library)
+insert_tracks(db, library)
+db.close()