trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

//#define SQLLITE_CONNECTION_TEST
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Diagnostics;
using Enterprise.Logging;
using libxmltv;
using libxmltv.Interfaces;
using libxmltv.Core;
namespace GBPVRProgramDatabaseFixer
{
    public interface ISQLLITE
    {
        List<SQLLITE.IPROGRAMME> Programs { get; }
        List<SQLLITE.IRECORDING_SCHEDULE> Recordings { get; }
        List<SQLLITE.ICHANNEL> Channels { get; }
        IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs);
        List<SQLLITE.IPROGRAMME> CreateUpdatedProgramsList(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> removed_programs);

    }
    public class SQLLITE : ISQLLITE
    {
        public static ISQLLITE Create(string database, EventHandler<EventArgs> OnInstanceCreated)
        {
            return new SQLLITE(database, OnInstanceCreated);
        }

        #region DATABASE DEFINITIONS

        public interface ICHANNEL
        {
            Int64 oid { get; }
            String name { get; }
            String channelID { get; }
            Int64 channel_number { get; }
            String favourite_channel { get; }
            String display_name { get; }
        }
        private class CHANNEL : ICHANNEL
        {
            public CHANNEL() 
            {
                BaseDatabaseDefinition<CHANNEL>.CreateDefault(this); 
            }
            //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }

            public static void Create(ref CHANNEL instance, SQLiteDataReader r, int index)
            {
                BaseDatabaseDefinition<CHANNEL>.Create(ref instance, r, index);
            }
            #region ICHANNEL members
            public Int64 oid { get; set; }
            public String name { get; set; }
            public String channelID { get; set; }
            public Int64 channel_number { get; set; }
            public String favourite_channel { get; set; }
            public String display_name { get; set; }
            #endregion
        }
        public interface IRECORDING_SCHEDULE
        {
            Int64 oid { get; }
            Int64 programme_oid { get; }
            Int64 capture_source_oid { get; }
            Int16 status { get; }
            String filename { get; }
            Int64 recording_type { get; }
            Int64 recording_group { get; }
            DateTime manual_start_time { get; }
            DateTime manual_end_time { get; }
            Int64 manual_channel_oid { get; }
            Int64 quality_level { get; }
            Int64 pre_pad_minutes { get; }
            Int64 post_pad_minutes { get;}
            Int32 priority { get; }
            String conversion_profile { get; }
        }

        private static class BaseDatabaseDefinition<T>
        {
            public static void CreateDefault(T instance)
            {
                try
                {
                    Type t = typeof(T);
                    var props = t.GetProperties();
                    foreach (var prop in props)
                    {
                        Type prop_type = prop.PropertyType;
                        object field_value = null;
                        try
                        {
                            if (prop_type == typeof(string))
                            {
                                field_value = string.Empty;
                            }
                            else
                            {
                                field_value = Activator.CreateInstance(prop_type);
                            }
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                        prop.SetValue(instance, field_value, null);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            public static void Create(ref T instance, SQLiteDataReader r, int index)
            {                
                string field_name = r.GetName(index);
                Type field_type = r.GetFieldType(index);
                object field_value = r.GetValue(index);
                //gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());

                Type t = typeof(T);
                var props = t.GetProperties();
                foreach (var prop in props)
                {
                    if (prop.Name.ToLower() == field_name.ToLower())
                    {
                        if (prop.PropertyType == field_type)
                        {
                            Type db_type = field_value.GetType();
                            try
                            {
                                if (db_type == typeof(System.DBNull))
                                {
                                    prop.SetValue(instance, null, null);
                                }
                                else
                                {
                                    prop.SetValue(instance, field_value, null);
                                }
                                
                            }
                            catch (Exception ex) 
                            {
                                throw ex;
                            }
                        }
                        else
                        {
                            gLog.Verbose.Debug.WriteLine("Found Property: {0} but there was a type mismatch. Property Type: '{1}' Expected: '{2}'", prop.Name, prop.PropertyType.Name, field_type.Name);
                            throw new InvalidOperationException(string.Format("Found Property: {0} but there was a type mismatch. Property Type: '{1}' Expected: '{2}'", prop.Name, prop.PropertyType.Name, field_type.Name));
                        }
                    }
                }

            }
        }

        private class RECORDING_SCHEDULE : IRECORDING_SCHEDULE
        {
            public RECORDING_SCHEDULE() 
            {
                BaseDatabaseDefinition<RECORDING_SCHEDULE>.CreateDefault(this); 
            }
            //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }

            public static void Create(ref RECORDING_SCHEDULE instance, SQLiteDataReader r, int index)
            {
                BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(ref instance, r, index); 
            }

            #region IRECORDING_SCHEDULE members
            public Int64 oid { get; set;}
            public Int64 programme_oid { get; set;}
            public Int64 capture_source_oid { get; set;}
            public Int16 status { get; set; }
            public String filename { get; set; }
            public Int64 recording_type { get;set; }
            public Int64 recording_group { get; set;}
            public DateTime manual_start_time { get; set;}
            public DateTime manual_end_time { get; set; }
            public Int64 manual_channel_oid { get; set; }
            public Int64 quality_level { get; set; }
            public Int64 pre_pad_minutes { get; set; }
            public Int64 post_pad_minutes { get; set; }
            public Int32 priority { get; set; }
            public String conversion_profile { get; set; }
            #endregion
        }

        public interface IPROGRAMME
        {
            Int64 oid { get; set; }
            String name { get; set; }
            String sub_title { get; set; }
            String description { get; set; }
            DateTime start_time { get; set; }
            DateTime end_time { get; set; }
            Int64 channel_oid { get; set; }
            String unique_identifier { get; set; }
            String rating { get; set; }

            IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite);
        }
        private class PROGRAMME : IPROGRAMME
        {
            public PROGRAMME() 
            {
                BaseDatabaseDefinition<PROGRAMME>.CreateDefault(this); 
            }
            //public PROGRAMME(SQLiteDataReader r, int index) : base(r, index) { }
            public static void Create(ref PROGRAMME instance, SQLiteDataReader r, int index)
            {
                BaseDatabaseDefinition<PROGRAMME>.Create(ref instance, r, index);
            }
            #region IPROGRAMME members
            public Int64 oid { get; set; }
            public String name { get; set; }
            public String sub_title { get; set; }
            public String description { get; set; }
            public DateTime start_time { get; set; }
            public DateTime end_time { get; set; }
            public Int64 channel_oid { get; set; }
            public String unique_identifier { get; set; }
            public String rating { get; set; }
            public IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite)
            {
                ProgramList.ProgramDefintion definition = new ProgramList.ProgramDefintion();
                var channel = sqllite.Channels.Find(s => s.oid == this.channel_oid);
                definition.ChannelName = channel.display_name;
                definition.ChannelNumber = (int)channel.channel_number;
                definition.Description = this.description;
                definition.Start = this.start_time.ToDateTimeString();
                definition.Stop = this.end_time.ToDateTimeString();
                definition.SubTitle = this.sub_title;
                definition.Title = this.name;
                return definition;
            }
            #endregion
        }
        #endregion
        private static class TABLES
        {
            public const string RECORDING_SCHEDULE = "RECORDING_SCHEDULE";
            public const string PROGRAMME = "PROGRAMME";
            public const string CHANNEL = "CHANNEL";
        }

        //public SQLLite() { }
        protected SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated) 
        {
            this.OnInstanceCreated = OnInstanceCreated;
            //CreateConnection(database);
            this.Database = database;
            if (!CreateDatabaseBackup())
            {
                gLog.Error.WriteLine("Failed to backup database.");
                return;
            }
            ConnectionTest();
            ReadChannelData();
            ReadRecodringScheduleData();
            ReadProgrammeData();
            OnCreatedInstance(this, new EventArgs());
        }

        [NonSerialized]
        private EventHandler<EventArgs> _OnInstanceCreated;
        private EventHandler<EventArgs> OnInstanceCreated { get { return _OnInstanceCreated; } set { _OnInstanceCreated = value; } }
        
        private void OnCreatedInstance(object sender, EventArgs e)
        {
            if (OnInstanceCreated != null) { OnInstanceCreated.Invoke(sender, e); }
        }

        private string Database;
        #region ISQLLITE members        
        public List<IPROGRAMME> Programs { get; private set; }
        public List<IRECORDING_SCHEDULE> Recordings { get; private set; }
        public List<ICHANNEL> Channels { get; private set; }

        public IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs)
        {
            var list = new List<IPROGRAMME>(programs.ToArray());
            DateTime first = new DateTime();
            DateTime last = new DateTime();
            first = list.OrderBy(s => s.start_time).ToList().First().start_time;            
            last = list.OrderBy(s => s.start_time).ToList().Last().start_time;
            gLog.Verbose.Debug.WriteLine("\tFirst: {0} = ({1})", first.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), first.ToDateTimeString());
            gLog.Verbose.Debug.WriteLine("\tLast: {0} = ({1})", last.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), last.ToDateTimeString());
            var range = DateTimeRange.Create(first, last);
            return range;
        }

        public List<SQLLITE.IPROGRAMME> CreateUpdatedProgramsList(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> source_invalid)
        {
            source_invalid = new List<IPROGRAMME>();
            gbpvr_programs = gbpvr_programs.OrderBy(s => s.start_time).ToList();            
            List<SQLLITE.IPROGRAMME> source_valid = new List<IPROGRAMME>();
            //if (range == null)
            //{
            //    gLog.Warn.WriteLine("The DateTimeRange passed in is null...returning the original program list");
            //    return list;
            //}
            //gLog.Warn.WriteLine("FilterProgramsByDateRange has not been implemented");
            
            double total = gbpvr_programs.Count;
            double index = 0;
            double progress = 0;
            foreach (var program in gbpvr_programs)
            {  
                progress = 100.0 * (index / total);
                gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Filtering GBPVR Programs: {0:00}%", (int)progress)));                
                var channel_oid = program.channel_oid;
                var channel = this.Channels.Find(s => s.oid == channel_oid);
                var start_date = program.start_time;
                //var xmltv_entry_list = xmltv_programs.FindAll(s => s.ChannelNumber == channel.channel_number && s.Start == start_date.ToDateTimeString());
                var query = from c in xmltv_programs 
                            where 
                            c.ChannelNumber == channel.channel_number &&
                            c.Start == start_date.ToDateTimeString()
                            select c;

                IProgramDefinition xmltv_entry = null;
                if (query.Count()-1 > 0)
                {
                    gLog.Error.WriteLine("Found more than one entry: Matching channel='{0}' and start='{1}'", channel.channel_number, start_date.ToDateTimeString());
                    gLog.Error.WriteLine("    GB-PVR Program Data: oid='{0}' channel_oid='{1}' name='{2}' sub_title='{3}' description='{4}'", program.oid, program.channel_oid, program.name, program.sub_title, program.description);
                    if (Debugger.IsAttached)
                    {                      
                        gLog.Error.WriteLine("    Found: {0} matching entries", query.Count());
                        int k_index = 0;
                        foreach (var k in query)
                        {
                            gLog.Error.WriteLine("    query[{0}]: channel='{1}' start='{2}' ('{3}') title='{4}' subtitle='{5}' description='{6}'", k_index, k.ChannelNumber, k.Start, DateTime.Parse(k.Start).ToString("yyyy/MM/dd HH:mm:ss.fffffff"), k.Title, k.SubTitle, k.Description);
                            k_index++;
                        }
                        Debugger.Break();
                    }
                }
                else
                {
                    xmltv_entry = query.FirstOrDefault();
                }

                if (xmltv_entry == null)
                {
                    gLog.Warn.WriteLine("Invalidating GB-PVR Program: oid='{0}' channel_oid='{1}' title='{2}' start='{3}'", program.oid, program.channel_oid, program.name, program.start_time.ToDateTimeString());
                    source_invalid.Add(program);
                }
                else
                {
                    gLog.Warn.WriteLine("Updating GB-PVR Program (if needed): oid='{0}' channel_oid='{1}' title='{2}' subtitle='{3}' start='{4}'", program.oid, program.channel_oid, program.name, program.sub_title, program.start_time.ToDateTimeString());
                    var updated_program = program;
                    if (xmltv_entry.Title != program.name)
                    {
                        gLog.Warn.WriteLine("    Updating:");
                        gLog.Warn.WriteLine("        Old Title: {0}", program.name);
                        gLog.Warn.WriteLine("        New Title: {0}", xmltv_entry.Title);
                        updated_program.name = xmltv_entry.Title;
                    }
                    if (xmltv_entry.SubTitle != program.sub_title)
                    {
                        gLog.Warn.WriteLine("    Updating:");
                        gLog.Warn.WriteLine("        Old SubTile: {0}", program.sub_title);
                        gLog.Warn.WriteLine("        New SubTile: {0}", xmltv_entry.SubTitle);
                        updated_program.sub_title = xmltv_entry.SubTitle;
                    }
                    if (xmltv_entry.Description != program.description)
                    {
                        gLog.Warn.WriteLine("    Updating:");
                        gLog.Warn.WriteLine("        Old Descption: {0}", program.description);
                        gLog.Warn.WriteLine("        New Descption: {0}", xmltv_entry.Description);
                        updated_program.description = xmltv_entry.Description;                        
                    }
                    if (DateTime.Parse(xmltv_entry.Start) != program.start_time)
                    {
                        gLog.Warn.WriteLine("    Updating:");
                        gLog.Warn.WriteLine("        Old StartTime: {0}", program.start_time.ToDateTimeString());
                        gLog.Warn.WriteLine("        New StartTime: {0}", DateTime.Parse(xmltv_entry.Start).ToDateTimeString());
                        updated_program.start_time = DateTime.Parse(xmltv_entry.Start);
                    }
                    if (DateTime.Parse(xmltv_entry.Stop) != program.end_time)
                    {
                        gLog.Warn.WriteLine("    Updating:");
                        gLog.Warn.WriteLine("        Old EndTime: {0}", program.end_time.ToDateTimeString());
                        gLog.Warn.WriteLine("        New EndTime: {0}", DateTime.Parse(xmltv_entry.Stop).ToDateTimeString());
                        updated_program.end_time = DateTime.Parse(xmltv_entry.Stop);
                    }
                    source_valid.Add(updated_program);
                }
                index++;
            }

            //for (int i = 0; i < programs.Count(); i++)
            //{
            //    var gbpvr_entry = gbpvr[i];
            //    var xmltv_entry = gbpvr_entry.AsXMLTVProgramDefinition(sqlite);
            //    if (!xmltv[i].Equals(xmltv_entry))
            //    {
            //        gLog.Warn.WriteLine("Warning GBPVR Program oid: {0} might be invalid", gbpvr_entry.oid);
            //    }
            //}

            source_valid = source_valid.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
            source_invalid = source_invalid.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();

            gLog.Info.WriteLine("Total XMLTV Programs: 0x{0:x8}", xmltv_programs.Count);
            gLog.Info.WriteLine("Updated: 0x{0:x8} GB-PVR Programs", source_valid.Count);
            gLog.Info.WriteLine("Removed: 0x{0:x8} GB-PVR Programs", source_invalid.Count);
            gLog.Info.WriteLine("Total GB-PVR Programs (Updated & Removed): 0x{0:x8}", source_valid.Count + source_invalid.Count);

            return source_valid;
        }
        #endregion


        private string CreateConnectionString()
        {
            string connection_string = string.Format("Data Source={0}", this.Database);
            return connection_string;
        }

        private SQLiteConnection CreateConnection() { SQLiteConnection connection = new SQLiteConnection(CreateConnectionString()); return connection; }

        [Conditional("SQLLITE_CONNECTION_TEST")]
        private void ConnectionTest()
        {
            try
            {
                using (SQLiteConnection con = CreateConnection())
                {
                    con.Open();
                    string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
                    gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
                    using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
                    {
                        using (SQLiteDataReader r = cmd.ExecuteReader())
                        {
                            if (!r.HasRows)
                            {
                                gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
                            }
                            else
                            {
                                while (r.Read())
                                {
                                    for (int i = 0; i < r.FieldCount; i++)
                                    {
                                        string field_name = r.GetName(i);
                                        Type field_type = r.GetFieldType(i);
                                        object field_value = r.GetValue(i);
                                        gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
                                    }
                                    break;
                                }
                            }
                        }
                    }
                    con.Clone();
                }
                OnCreatedInstance(this, new EventArgs());
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
            }
        }


        private bool CreateDatabaseBackup()
        {
            try
            {
                string backup_file = string.Format("{0}.{1}", this.Database, DateTime.Now.ToString("yyyyMMddHHmmss"));
                gLog.Info.WriteLine("Creating Database backup...");
                gLog.Info.WriteLine("\tSource: {0}", this.Database);
                gLog.Info.WriteLine("\tDestination: {0}", backup_file);

                System.IO.File.Copy(this.Database, backup_file);
                return true;
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
                return false;
            }
        }
        private void ReadChannelData()
        {
            try
            {
                List<ICHANNEL> channels = new List<ICHANNEL>();
                using (SQLiteConnection con = CreateConnection())
                {
                    con.Open();
                    string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
                    gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
                    using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
                    {
                        using (SQLiteDataReader r = cmd.ExecuteReader())
                        {
                            if (!r.HasRows)
                            {
                                gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
                            }
                            else
                            {
                                while (r.Read())
                                {
                                    CHANNEL channel = new CHANNEL();
                                    for (int i = 0; i < r.FieldCount; i++)
                                    {
                                        CHANNEL.Create(ref channel, r, i);
                                    }
                                    channels.Add(channel);
                                }
                            }
                        }
                    }
                    con.Clone();
                }
                this.Channels = channels;
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
            }
        }
        private void ReadProgrammeData()
        {
            try
            {
                List<IPROGRAMME> programs = new List<IPROGRAMME>();
                using (SQLiteConnection con = CreateConnection())
                {
                    con.Open();
                    string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
                    gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
                    using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
                    {
                        using (SQLiteDataReader r = cmd.ExecuteReader())
                        {
                            if (!r.HasRows)
                            {
                                gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
                            }
                            else
                            {
                                while (r.Read())
                                {
                                    PROGRAMME program = new PROGRAMME();
                                    for (int i = 0; i < r.FieldCount; i++)
                                    {
                                        PROGRAMME.Create(ref program, r, i);
                                    }
                                    programs.Add(program);
                                }
                            }
                        }
                    }
                    con.Clone();
                }
                this.Programs = programs;
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
            }
        }
        private void ReadRecodringScheduleData()
        {
            try
            {
                List<IRECORDING_SCHEDULE> recordings = new List<IRECORDING_SCHEDULE>();
                using (SQLiteConnection con = CreateConnection())
                {
                    con.Open();
                    string command_text = string.Format("select * from {0};", TABLES.RECORDING_SCHEDULE);
                    gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
                    using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
                    {
                        using (SQLiteDataReader r = cmd.ExecuteReader())
                        {
                            if (!r.HasRows)
                            {
                                gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
                            }
                            else
                            {
                                while (r.Read())
                                {
                                    RECORDING_SCHEDULE recording = new RECORDING_SCHEDULE();
                                    for (int i = 0; i < r.FieldCount; i++)
                                    {
                                        RECORDING_SCHEDULE.Create(ref recording, r, i);
                                    }
                                    recordings.Add(recording);
                                }
                            }
                        }
                    }
                    con.Clone();
                }
                this.Recordings = recordings;
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
            }
        }

    }
}
1	william	156	//#define SQLLITE_CONNECTION_TEST
2			using System;
3			using System.Collections.Generic;
4			using System.Linq;
5			using System.Text;
6			using System.Data.SQLite;
7			using System.Diagnostics;
8			using Enterprise.Logging;
9	william	163	using libxmltv;
10	william	165	using libxmltv.Interfaces;
11			using libxmltv.Core;
12	william	156	namespace GBPVRProgramDatabaseFixer
13			{
14	william	163	public interface ISQLLITE
15	william	156	{
16	william	163	List<SQLLITE.IPROGRAMME> Programs { get; }
17			List<SQLLITE.IRECORDING_SCHEDULE> Recordings { get; }
18			List<SQLLITE.ICHANNEL> Channels { get; }
19			IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs);
20	william	168	List<SQLLITE.IPROGRAMME> CreateUpdatedProgramsList(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> removed_programs);
21	william	163
22			}
23			public class SQLLITE : ISQLLITE
24			{
25			public static ISQLLITE Create(string database, EventHandler<EventArgs> OnInstanceCreated)
26			{
27			return new SQLLITE(database, OnInstanceCreated);
28			}
29
30	william	156	#region DATABASE DEFINITIONS
31	william	159
32			public interface ICHANNEL
33			{
34			Int64 oid { get; }
35			String name { get; }
36			String channelID { get; }
37			Int64 channel_number { get; }
38			String favourite_channel { get; }
39			String display_name { get; }
40			}
41			private class CHANNEL : ICHANNEL
42			{
43			public CHANNEL()
44			{
45			BaseDatabaseDefinition<CHANNEL>.CreateDefault(this);
46			}
47			//public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }
48
49			public static void Create(ref CHANNEL instance, SQLiteDataReader r, int index)
50			{
51			BaseDatabaseDefinition<CHANNEL>.Create(ref instance, r, index);
52			}
53			#region ICHANNEL members
54			public Int64 oid { get; set; }
55			public String name { get; set; }
56			public String channelID { get; set; }
57			public Int64 channel_number { get; set; }
58			public String favourite_channel { get; set; }
59			public String display_name { get; set; }
60			#endregion
61			}
62	william	156	public interface IRECORDING_SCHEDULE
63			{
64			Int64 oid { get; }
65			Int64 programme_oid { get; }
66			Int64 capture_source_oid { get; }
67			Int16 status { get; }
68			String filename { get; }
69			Int64 recording_type { get; }
70			Int64 recording_group { get; }
71			DateTime manual_start_time { get; }
72			DateTime manual_end_time { get; }
73			Int64 manual_channel_oid { get; }
74			Int64 quality_level { get; }
75			Int64 pre_pad_minutes { get; }
76			Int64 post_pad_minutes { get;}
77			Int32 priority { get; }
78			String conversion_profile { get; }
79			}
80
81			private static class BaseDatabaseDefinition<T>
82			{
83			public static void CreateDefault(T instance)
84			{
85			try
86			{
87			Type t = typeof(T);
88			var props = t.GetProperties();
89			foreach (var prop in props)
90			{
91			Type prop_type = prop.PropertyType;
92			object field_value = null;
93			try
94			{
95			if (prop_type == typeof(string))
96			{
97			field_value = string.Empty;
98			}
99			else
100			{
101			field_value = Activator.CreateInstance(prop_type);
102			}
103			}
104			catch (Exception ex)
105			{
106			throw ex;
107			}
108			prop.SetValue(instance, field_value, null);
109			}
110			}
111			catch (Exception ex)
112			{
113			throw ex;
114			}
115			}
116			public static void Create(ref T instance, SQLiteDataReader r, int index)
117			{
118			string field_name = r.GetName(index);
119			Type field_type = r.GetFieldType(index);
120			object field_value = r.GetValue(index);
121			//gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
122
123			Type t = typeof(T);
124			var props = t.GetProperties();
125			foreach (var prop in props)
126			{
127			if (prop.Name.ToLower() == field_name.ToLower())
128			{
129			if (prop.PropertyType == field_type)
130			{
131			Type db_type = field_value.GetType();
132			try
133			{
134			if (db_type == typeof(System.DBNull))
135			{
136			prop.SetValue(instance, null, null);
137			}
138			else
139			{
140			prop.SetValue(instance, field_value, null);
141			}
142
143			}
144			catch (Exception ex)
145			{
146			throw ex;
147			}
148			}
149			else
150			{
151			gLog.Verbose.Debug.WriteLine("Found Property: {0} but there was a type mismatch. Property Type: '{1}' Expected: '{2}'", prop.Name, prop.PropertyType.Name, field_type.Name);
152			throw new InvalidOperationException(string.Format("Found Property: {0} but there was a type mismatch. Property Type: '{1}' Expected: '{2}'", prop.Name, prop.PropertyType.Name, field_type.Name));
153			}
154			}
155			}
156
157			}
158			}
159
160			private class RECORDING_SCHEDULE : IRECORDING_SCHEDULE
161			{
162			public RECORDING_SCHEDULE()
163			{
164			BaseDatabaseDefinition<RECORDING_SCHEDULE>.CreateDefault(this);
165			}
166			//public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }
167
168			public static void Create(ref RECORDING_SCHEDULE instance, SQLiteDataReader r, int index)
169			{
170			BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(ref instance, r, index);
171			}
172
173			#region IRECORDING_SCHEDULE members
174			public Int64 oid { get; set;}
175			public Int64 programme_oid { get; set;}
176			public Int64 capture_source_oid { get; set;}
177			public Int16 status { get; set; }
178			public String filename { get; set; }
179			public Int64 recording_type { get;set; }
180			public Int64 recording_group { get; set;}
181			public DateTime manual_start_time { get; set;}
182			public DateTime manual_end_time { get; set; }
183			public Int64 manual_channel_oid { get; set; }
184			public Int64 quality_level { get; set; }
185			public Int64 pre_pad_minutes { get; set; }
186			public Int64 post_pad_minutes { get; set; }
187			public Int32 priority { get; set; }
188			public String conversion_profile { get; set; }
189			#endregion
190			}
191
192			public interface IPROGRAMME
193			{
194	william	168	Int64 oid { get; set; }
195			String name { get; set; }
196			String sub_title { get; set; }
197			String description { get; set; }
198			DateTime start_time { get; set; }
199			DateTime end_time { get; set; }
200			Int64 channel_oid { get; set; }
201			String unique_identifier { get; set; }
202			String rating { get; set; }
203	william	165
204			IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite);
205	william	156	}
206			private class PROGRAMME : IPROGRAMME
207			{
208			public PROGRAMME()
209			{
210			BaseDatabaseDefinition<PROGRAMME>.CreateDefault(this);
211			}
212			//public PROGRAMME(SQLiteDataReader r, int index) : base(r, index) { }
213			public static void Create(ref PROGRAMME instance, SQLiteDataReader r, int index)
214			{
215			BaseDatabaseDefinition<PROGRAMME>.Create(ref instance, r, index);
216			}
217			#region IPROGRAMME members
218			public Int64 oid { get; set; }
219			public String name { get; set; }
220			public String sub_title { get; set; }
221			public String description { get; set; }
222			public DateTime start_time { get; set; }
223			public DateTime end_time { get; set; }
224			public Int64 channel_oid { get; set; }
225			public String unique_identifier { get; set; }
226			public String rating { get; set; }
227	william	165	public IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite)
228			{
229			ProgramList.ProgramDefintion definition = new ProgramList.ProgramDefintion();
230			var channel = sqllite.Channels.Find(s => s.oid == this.channel_oid);
231			definition.ChannelName = channel.display_name;
232			definition.ChannelNumber = (int)channel.channel_number;
233			definition.Description = this.description;
234	william	168	definition.Start = this.start_time.ToDateTimeString();
235			definition.Stop = this.end_time.ToDateTimeString();
236	william	165	definition.SubTitle = this.sub_title;
237			definition.Title = this.name;
238			return definition;
239			}
240	william	156	#endregion
241			}
242			#endregion
243			private static class TABLES
244			{
245			public const string RECORDING_SCHEDULE = "RECORDING_SCHEDULE";
246			public const string PROGRAMME = "PROGRAMME";
247	william	159	public const string CHANNEL = "CHANNEL";
248	william	156	}
249
250			//public SQLLite() { }
251	william	163	protected SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated)
252	william	156	{
253			this.OnInstanceCreated = OnInstanceCreated;
254			//CreateConnection(database);
255			this.Database = database;
256	william	157	if (!CreateDatabaseBackup())
257			{
258			gLog.Error.WriteLine("Failed to backup database.");
259			return;
260			}
261	william	156	ConnectionTest();
262	william	159	ReadChannelData();
263	william	156	ReadRecodringScheduleData();
264			ReadProgrammeData();
265			OnCreatedInstance(this, new EventArgs());
266			}
267
268			[NonSerialized]
269			private EventHandler<EventArgs> _OnInstanceCreated;
270			private EventHandler<EventArgs> OnInstanceCreated { get { return _OnInstanceCreated; } set { _OnInstanceCreated = value; } }
271
272			private void OnCreatedInstance(object sender, EventArgs e)
273			{
274			if (OnInstanceCreated != null) { OnInstanceCreated.Invoke(sender, e); }
275			}
276
277			private string Database;
278	william	163	#region ISQLLITE members
279	william	156	public List<IPROGRAMME> Programs { get; private set; }
280			public List<IRECORDING_SCHEDULE> Recordings { get; private set; }
281	william	159	public List<ICHANNEL> Channels { get; private set; }
282	william	163
283			public IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs)
284			{
285			var list = new List<IPROGRAMME>(programs.ToArray());
286			DateTime first = new DateTime();
287			DateTime last = new DateTime();
288			first = list.OrderBy(s => s.start_time).ToList().First().start_time;
289			last = list.OrderBy(s => s.start_time).ToList().Last().start_time;
290	william	167	gLog.Verbose.Debug.WriteLine("\tFirst: {0} = ({1})", first.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), first.ToDateTimeString());
291			gLog.Verbose.Debug.WriteLine("\tLast: {0} = ({1})", last.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), last.ToDateTimeString());
292	william	163	var range = DateTimeRange.Create(first, last);
293			return range;
294			}
295
296	william	169	public List<SQLLITE.IPROGRAMME> CreateUpdatedProgramsList(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> source_invalid)
297	william	163	{
298	william	169	source_invalid = new List<IPROGRAMME>();
299	william	168	gbpvr_programs = gbpvr_programs.OrderBy(s => s.start_time).ToList();
300	william	166	List<SQLLITE.IPROGRAMME> source_valid = new List<IPROGRAMME>();
301			//if (range == null)
302			//{
303			// gLog.Warn.WriteLine("The DateTimeRange passed in is null...returning the original program list");
304			// return list;
305			//}
306	william	163	//gLog.Warn.WriteLine("FilterProgramsByDateRange has not been implemented");
307	william	170
308	william	168	double total = gbpvr_programs.Count;
309	william	166	double index = 0;
310			double progress = 0;
311	william	168	foreach (var program in gbpvr_programs)
312	william	169	{
313			progress = 100.0 * (index / total);
314			gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Filtering GBPVR Programs: {0:00}%", (int)progress)));
315	william	168	var channel_oid = program.channel_oid;
316			var channel = this.Channels.Find(s => s.oid == channel_oid);
317			var start_date = program.start_time;
318	william	172	//var xmltv_entry_list = xmltv_programs.FindAll(s => s.ChannelNumber == channel.channel_number && s.Start == start_date.ToDateTimeString());
319			var query = from c in xmltv_programs
320			where
321			c.ChannelNumber == channel.channel_number &&
322			c.Start == start_date.ToDateTimeString()
323			select c;
324
325	william	170	IProgramDefinition xmltv_entry = null;
326	william	172	if (query.Count()-1 > 0)
327	william	170	{
328			gLog.Error.WriteLine("Found more than one entry: Matching channel='{0}' and start='{1}'", channel.channel_number, start_date.ToDateTimeString());
329	william	171	gLog.Error.WriteLine(" GB-PVR Program Data: oid='{0}' channel_oid='{1}' name='{2}' sub_title='{3}' description='{4}'", program.oid, program.channel_oid, program.name, program.sub_title, program.description);
330	william	170	if (Debugger.IsAttached)
331	william	172	{
332			gLog.Error.WriteLine(" Found: {0} matching entries", query.Count());
333			int k_index = 0;
334			foreach (var k in query)
335			{
336			gLog.Error.WriteLine(" query[{0}]: channel='{1}' start='{2}' ('{3}') title='{4}' subtitle='{5}' description='{6}'", k_index, k.ChannelNumber, k.Start, DateTime.Parse(k.Start).ToString("yyyy/MM/dd HH:mm:ss.fffffff"), k.Title, k.SubTitle, k.Description);
337			k_index++;
338			}
339	william	170	Debugger.Break();
340			}
341			}
342			else
343			{
344	william	172	xmltv_entry = query.FirstOrDefault();
345	william	170	}
346
347	william	168	if (xmltv_entry == null)
348	william	163	{
349	william	170	gLog.Warn.WriteLine("Invalidating GB-PVR Program: oid='{0}' channel_oid='{1}' title='{2}' start='{3}'", program.oid, program.channel_oid, program.name, program.start_time.ToDateTimeString());
350	william	169	source_invalid.Add(program);
351	william	163	}
352	william	164	else
353			{
354	william	172	gLog.Warn.WriteLine("Updating GB-PVR Program (if needed): oid='{0}' channel_oid='{1}' title='{2}' subtitle='{3}' start='{4}'", program.oid, program.channel_oid, program.name, program.sub_title, program.start_time.ToDateTimeString());
355	william	168	var updated_program = program;
356	william	173	if (xmltv_entry.Title != program.name)
357	william	172	{
358			gLog.Warn.WriteLine(" Updating:");
359	william	173	gLog.Warn.WriteLine(" Old Title: {0}", program.name);
360			gLog.Warn.WriteLine(" New Title: {0}", xmltv_entry.Title);
361			updated_program.name = xmltv_entry.Title;
362	william	172	}
363	william	173	if (xmltv_entry.SubTitle != program.sub_title)
364	william	172	{
365			gLog.Warn.WriteLine(" Updating:");
366	william	173	gLog.Warn.WriteLine(" Old SubTile: {0}", program.sub_title);
367			gLog.Warn.WriteLine(" New SubTile: {0}", xmltv_entry.SubTitle);
368			updated_program.sub_title = xmltv_entry.SubTitle;
369	william	172	}
370	william	173	if (xmltv_entry.Description != program.description)
371	william	172	{
372			gLog.Warn.WriteLine(" Updating:");
373	william	173	gLog.Warn.WriteLine(" Old Descption: {0}", program.description);
374			gLog.Warn.WriteLine(" New Descption: {0}", xmltv_entry.Description);
375			updated_program.description = xmltv_entry.Description;
376	william	172	}
377			if (DateTime.Parse(xmltv_entry.Start) != program.start_time)
378			{
379			gLog.Warn.WriteLine(" Updating:");
380			gLog.Warn.WriteLine(" Old StartTime: {0}", program.start_time.ToDateTimeString());
381			gLog.Warn.WriteLine(" New StartTime: {0}", DateTime.Parse(xmltv_entry.Start).ToDateTimeString());
382	william	173	updated_program.start_time = DateTime.Parse(xmltv_entry.Start);
383	william	172	}
384	william	173	if (DateTime.Parse(xmltv_entry.Stop) != program.end_time)
385	william	172	{
386			gLog.Warn.WriteLine(" Updating:");
387	william	173	gLog.Warn.WriteLine(" Old EndTime: {0}", program.end_time.ToDateTimeString());
388			gLog.Warn.WriteLine(" New EndTime: {0}", DateTime.Parse(xmltv_entry.Stop).ToDateTimeString());
389			updated_program.end_time = DateTime.Parse(xmltv_entry.Stop);
390	william	172	}
391	william	168	source_valid.Add(updated_program);
392	william	164	}
393	william	169	index++;
394	william	163	}
395	william	166
396			//for (int i = 0; i < programs.Count(); i++)
397			//{
398			// var gbpvr_entry = gbpvr[i];
399			// var xmltv_entry = gbpvr_entry.AsXMLTVProgramDefinition(sqlite);
400			// if (!xmltv[i].Equals(xmltv_entry))
401			// {
402			// gLog.Warn.WriteLine("Warning GBPVR Program oid: {0} might be invalid", gbpvr_entry.oid);
403			// }
404			//}
405
406			source_valid = source_valid.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
407	william	169	source_invalid = source_invalid.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
408	william	167
409			gLog.Info.WriteLine("Total XMLTV Programs: 0x{0:x8}", xmltv_programs.Count);
410	william	168	gLog.Info.WriteLine("Updated: 0x{0:x8} GB-PVR Programs", source_valid.Count);
411	william	169	gLog.Info.WriteLine("Removed: 0x{0:x8} GB-PVR Programs", source_invalid.Count);
412			gLog.Info.WriteLine("Total GB-PVR Programs (Updated & Removed): 0x{0:x8}", source_valid.Count + source_invalid.Count);
413	william	167
414	william	166	return source_valid;
415	william	163	}
416	william	156	#endregion
417
418
419			private string CreateConnectionString()
420			{
421			string connection_string = string.Format("Data Source={0}", this.Database);
422			return connection_string;
423			}
424
425			private SQLiteConnection CreateConnection() { SQLiteConnection connection = new SQLiteConnection(CreateConnectionString()); return connection; }
426
427			[Conditional("SQLLITE_CONNECTION_TEST")]
428			private void ConnectionTest()
429			{
430			try
431			{
432			using (SQLiteConnection con = CreateConnection())
433			{
434			con.Open();
435	william	159	string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
436	william	156	gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
437			using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
438			{
439			using (SQLiteDataReader r = cmd.ExecuteReader())
440			{
441			if (!r.HasRows)
442			{
443			gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
444			}
445			else
446			{
447			while (r.Read())
448			{
449			for (int i = 0; i < r.FieldCount; i++)
450			{
451			string field_name = r.GetName(i);
452			Type field_type = r.GetFieldType(i);
453			object field_value = r.GetValue(i);
454			gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
455			}
456			break;
457			}
458			}
459			}
460			}
461	william	157	con.Clone();
462	william	156	}
463			OnCreatedInstance(this, new EventArgs());
464			}
465			catch (Exception ex)
466			{
467			gLog.Error.WriteLine(ex.ToString());
468			}
469			}
470
471	william	157
472			private bool CreateDatabaseBackup()
473			{
474			try
475			{
476			string backup_file = string.Format("{0}.{1}", this.Database, DateTime.Now.ToString("yyyyMMddHHmmss"));
477			gLog.Info.WriteLine("Creating Database backup...");
478			gLog.Info.WriteLine("\tSource: {0}", this.Database);
479			gLog.Info.WriteLine("\tDestination: {0}", backup_file);
480
481			System.IO.File.Copy(this.Database, backup_file);
482			return true;
483			}
484			catch (Exception ex)
485			{
486			gLog.Error.WriteLine(ex.ToString());
487			return false;
488			}
489			}
490	william	159	private void ReadChannelData()
491			{
492			try
493			{
494			List<ICHANNEL> channels = new List<ICHANNEL>();
495			using (SQLiteConnection con = CreateConnection())
496			{
497			con.Open();
498			string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
499			gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
500			using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
501			{
502			using (SQLiteDataReader r = cmd.ExecuteReader())
503			{
504			if (!r.HasRows)
505			{
506			gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
507			}
508			else
509			{
510			while (r.Read())
511			{
512			CHANNEL channel = new CHANNEL();
513			for (int i = 0; i < r.FieldCount; i++)
514			{
515			CHANNEL.Create(ref channel, r, i);
516			}
517			channels.Add(channel);
518			}
519			}
520			}
521			}
522			con.Clone();
523			}
524			this.Channels = channels;
525			}
526			catch (Exception ex)
527			{
528			gLog.Error.WriteLine(ex.ToString());
529			}
530			}
531	william	156	private void ReadProgrammeData()
532			{
533			try
534			{
535			List<IPROGRAMME> programs = new List<IPROGRAMME>();
536			using (SQLiteConnection con = CreateConnection())
537			{
538			con.Open();
539			string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
540			gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
541			using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
542			{
543			using (SQLiteDataReader r = cmd.ExecuteReader())
544			{
545			if (!r.HasRows)
546			{
547			gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
548			}
549			else
550			{
551			while (r.Read())
552			{
553			PROGRAMME program = new PROGRAMME();
554			for (int i = 0; i < r.FieldCount; i++)
555			{
556			PROGRAMME.Create(ref program, r, i);
557			}
558			programs.Add(program);
559			}
560			}
561			}
562			}
563			con.Clone();
564			}
565			this.Programs = programs;
566			}
567			catch (Exception ex)
568			{
569			gLog.Error.WriteLine(ex.ToString());
570			}
571			}
572			private void ReadRecodringScheduleData()
573			{
574			try
575			{
576			List<IRECORDING_SCHEDULE> recordings = new List<IRECORDING_SCHEDULE>();
577			using (SQLiteConnection con = CreateConnection())
578			{
579			con.Open();
580			string command_text = string.Format("select * from {0};", TABLES.RECORDING_SCHEDULE);
581			gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
582			using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
583			{
584			using (SQLiteDataReader r = cmd.ExecuteReader())
585			{
586			if (!r.HasRows)
587			{
588			gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
589			}
590			else
591			{
592			while (r.Read())
593			{
594			RECORDING_SCHEDULE recording = new RECORDING_SCHEDULE();
595			for (int i = 0; i < r.FieldCount; i++)
596			{
597			RECORDING_SCHEDULE.Create(ref recording, r, i);
598			}
599			recordings.Add(recording);
600			}
601			}
602			}
603			}
604			con.Clone();
605			}
606			this.Recordings = recordings;
607			}
608			catch (Exception ex)
609			{
610			gLog.Error.WriteLine(ex.ToString());
611			}
612			}
613	william	159
614	william	156	}
615			}