sqlTunes - Query Your iTunes Library

/*******************************************************************************
Copyright � 2005-2008 Alexander Kojevnikov <alexander@kojevnikov.com>

LICENSE

sqlTunes is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.

sqlTunes is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with sqlTunes; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
*******************************************************************************/

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Reflection;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using System.Xml;

namespace sqlTunes
{

    #region class WorkItems

    // Operations to run in a separate thread.
    public class WorkItems
    {
        public static void Export(object status)
        {
            var view = (IMainFormView)status;

            try
            {
                view.ProgressStart();

                // Check if the file exists.
                string fileName = view.iTunesLibraryFile;
                if(!File.Exists(fileName))
                {
                    throw new FileNotFoundException("Cannot find the iTunes library.", fileName);
                }

                // Will need this later.
                Dictionary<string, object> dict;

                // Open the file.
                view.ProgressSetMessage("Parsing iTunes xml...");
                using(var sr = new StreamReader(fileName, true))
                {
                    // Create the xml reader.
                    using(var reader = new XmlTextReader(sr))
                    {
                        // Skip to <plist>.
                        while(reader.Read() && (reader.NodeType != XmlNodeType.Element || reader.Name != "plist"))
                        {
                        }

                        // Skip to <dict>.
                        while(reader.Read() && (reader.NodeType != XmlNodeType.Element || reader.Name != "dict"))
                        {
                        }

                        // Create and populate the dictinary.
                        dict = readDictionary(reader);
                    }
                }

                // Open the connection to the SQL Server database.
                view.ProgressSetMessage("Connecting to the SQL Server...");
                string connectionString = string.Format(
                    @"Data Source={0};Initial Catalog={1};" +
                        @"Application Name=sqlTunes;Integrated Security=SSPI;",
                    view.SQLServer, view.Database);
                var cn = new SqlConnection(connectionString);
                cn.Open();
                using(cn)
                {
                    // Re-create the table.
                    view.ProgressSetMessage("Deleting old data...");
                    SqlHelper.ExecuteScript("sqlTunes.Definition.sql", cn);

                    // Get the track dictionary.
                    var tracks = (Dictionary<string, object>)dict["Tracks"];

                    // Valid keys and their lengths.
                    var keys = new Dictionary<string, int>();
                    keys["Track ID"] = 0;
                    keys["Name"] = 200;
                    keys["Artist"] = 100;
                    keys["Album"] = 100;
                    keys["Grouping"] = 100;
                    keys["Genre"] = 100;
                    keys["Kind"] = 100;
                    keys["Size"] = 0;
                    keys["Total Time"] = 0;
                    keys["Track Number"] = 0;
                    keys["Track Count"] = 0;
                    keys["Year"] = 0;
                    keys["Date Modified"] = 0;
                    keys["Date Added"] = 0;
                    keys["Bit Rate"] = 0;
                    keys["Sample Rate"] = 0;
                    keys["Comments"] = 200;
                    keys["Play Count"] = 0;
                    keys["Play Date"] = 0;
                    keys["Play Date UTC"] = 0;
                    keys["Rating"] = 0;
                    keys["Track Type"] = 100;
                    keys["Location"] = 500;
                    keys["File Folder Count"] = 0;
                    keys["Library Folder Count"] = 0;

                    view.ProgressSetMessage("Exporting the data...");
                    long total = tracks.Values.Count;
                    long current = 0;
                    foreach(object obj in tracks.Values)
                    {
                        view.ProgressSetValue((int)(100L*current++/total));
                        if(view.ProgressCancelled)
                        {
                            Thread.CurrentThread.Abort();
                        }

                        var track = (Dictionary<string, object>)obj;

                        // Columns and values for the INSERT statement.
                        var cols = new List<string>();
                        var vals = new List<string>();

                        // Add all values.
                        foreach(string key in track.Keys)
                        {
                            if(keys.ContainsKey(key))
                            {
                                cols.Add('[' + key.Replace("]", "]]") + ']');
                                vals.Add(toDatabaseString(track[key], keys[key]));
                            }
                        }

                        // Make the SQL statement.
                        string sql = String.Format(@"
INSERT INTO [Track]
({0})
VALUES 
({1})", String.Join(",", cols.ToArray()), String.Join(",", vals.ToArray()));
                        var cmd = new SqlCommand(sql, cn);
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch(ThreadAbortException)
            {
            }
            catch(ThreadInterruptedException)
            {
            }
            catch(Exception ex)
            {
                view.ReportException(ex);
            }
            finally
            {
                if(view != null)
                {
                    view.ProgressEnd();
                }
            }
        }

        private static Dictionary<string, object> readDictionary(XmlTextReader reader)
        {
            // Check if we are currently at <dict>.
            if(reader.NodeType != XmlNodeType.Element || reader.Name != "dict")
            {
                throw new ArgumentException("Invalid node type or name");
            }

            // Create a new dictionary object.
            var dict = new Dictionary<string, object>();

            // Read into it.
            while(true)
            {
                bool read;
                while((read = reader.Read()) && reader.NodeType != XmlNodeType.Element && 
                    (reader.NodeType != XmlNodeType.EndElement || reader.Name != "dict"))
                {
                }

                if(!read) break;

                // Check if we are done.
                if(reader.NodeType == XmlNodeType.EndElement)
                {
                    if(reader.Name != "dict")
                    {
                        throw new FormatException("Expected the </dict> node.");
                    }
                    return dict;
                }

                // Now we are at the <key> element.
                if(reader.Name != "key")
                {
                    throw new FormatException("Expected the <key> element.");
                }

                // Skip to the value.
                reader.Read();
                string key = reader.Value;

                // Skip to the next element;
                while(reader.Read() && reader.NodeType != XmlNodeType.Element)
                {
                }

                // Read the value.
                object val = readValue(reader);

                // At last add it to the dictionary.
                dict[key] = val;
            }
            return dict;
        }

        private static object readValue(XmlTextReader reader)
        {
            // Check if we are currently at an element.
            if(reader.NodeType != XmlNodeType.Element)
            {
                throw new ArgumentException("Expected an element.");
            }

            // Update the progress bar.

            object val;
            string type = reader.Name;
            switch(type)
            {
                case "string":
                {
                    val = readString(reader);
                    break;
                }
                case "integer":
                {
                    string s = readString(reader);
                    int n;
                    val = Int32.TryParse(s, out n) ? n : Int64.Parse(s);
                    break;
                }
                case "real":
                {
                    string s = readString(reader);
                    val = double.Parse(s);
                    break;
                }
                case "date":
                {
                    string s = readString(reader);
                    // Remove the trailing 'Z'.
                    s = s.Substring(0, s.Length - 1);
                    // Parse.
                    val = DateTime.ParseExact(s, "s", null);
                    break;
                }
                case "true":
                {
                    reader.Read();
                    val = true;
                    break;
                }
                case "false":
                {
                    reader.Read();
                    val = false;
                    break;
                }
                case "data":
                {
                    string s = readString(reader);
                    val = Convert.FromBase64String(s);
                    break;
                }
                case "dict":
                {
                    val = readDictionary(reader);
                    break;
                }
                case "array":
                {
                    val = readArray(reader);
                    break;
                }
                default:
                {
                    throw new FormatException("Unknown element name: " + type);
                }
            }
            return val;
        }

        private static string readString(XmlTextReader reader)
        {
            // Check if we are currently at an element.
            if(reader.NodeType != XmlNodeType.Element)
            {
                throw new ArgumentException("Expected an element.");
            }

            // Skip to the value.
            reader.Read();
            return reader.Value;
        }

        private static List<object> readArray(XmlTextReader reader)
        {
            // Check if we are currently at <dict>.
            if(reader.NodeType != XmlNodeType.Element || reader.Name != "array")
            {
                throw new ArgumentException("Invalid node type or name");
            }

            // Create a new array object.
            var array = new List<object>();

            // Read into it.
            while(true)
            {
                bool read;
                while((read = reader.Read()) && reader.NodeType != XmlNodeType.Element && 
                    reader.NodeType != XmlNodeType.EndElement)
                {
                }
                if(!read) break;

                // Check if we are done.
                if(reader.NodeType == XmlNodeType.EndElement)
                {
                    if(reader.Name != "array")
                    {
                        throw new FormatException("Expected the </array> node.");
                    }
                    return array;
                }

                // Read the value.
                object val = readValue(reader);

                // Add it to the array.
                array.Add(val);
            }
            return array;
        }

        private static string toDatabaseString(object obj, int length)
        {
            if((!(obj is string) && length != 0) || length < 0)
            {
                throw new ArgumentException();
            }
            if(obj is string)
            {
                var s = (string)obj;
                if(s.Length > length)
                {
                    // Truncate the string.
                    s = s.Substring(0, length);
                }
                // Replace single quotes and quote the entire string.
                return "N'" + s.Replace("'", "''") + "'";
            }
            if(obj is long || obj is int)
            {
                return obj.ToString();
            }
            if(obj is double)
            {
                return ((double)obj).ToString("F", CultureInfo.InvariantCulture);
            }
            if(obj is DateTime)
            {
                return "'" + ((DateTime)obj).ToString("yyyyMMdd HH:mm:ss.ff") + "'";
            }
            throw new ArgumentException("Invalid argument type.", "obj");
        }
    }

    #endregion class WorkItems

    #region class SQLHelper

    public class SqlHelper
    {
        private SqlHelper()
        {
        }

        public static void ExecuteScript(string resource, SqlConnection cn)
        {
            Assembly curAssembly = Assembly.GetExecutingAssembly();
            using(Stream stream = curAssembly.GetManifestResourceStream(resource))
            {
                Debug.Assert(stream != null);
                var sr = new StreamReader(stream);
                string script = sr.ReadToEnd();
                script = script.Replace("\r\n", "\n");
                script += "\n";
                string[] commands = script.Split(new[] {"\nGO\n"}, StringSplitOptions.RemoveEmptyEntries);
                var cmd = new SqlCommand {Connection = cn, CommandType = CommandType.Text};
                foreach(string command in commands)
                {
                    cmd.CommandText = command;
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }

    #endregion class SQLHelper

    #region class ErrorHelper

    public static class ErrorHelper
    {
        private const string _logFileName = "error.log";
        private static readonly string _logPath;

        static ErrorHelper()
        {
            string appDataPath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
            appDataPath = Path.Combine(appDataPath, Application.ProductName);
            _logPath = Path.Combine(appDataPath, _logFileName);
        }

        public static void Publish(IWin32Window parent, Exception ex)
        {
            using(var writer = new StreamWriter(_logPath, true, Encoding.UTF8))
            {
                writer.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " - an exception occured:");
                writer.WriteLine(ex.ToString());
            }

            MessageBox.Show(parent, ex.Message + "\n\nSee the error log for more details.",
                Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

    #endregion class ErrorHelper
}