/[xmltv_parser]/trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs
ViewVC logotype

Diff of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

--- trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs	2013/03/14 19:36:01	157
+++ trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs	2013/03/16 23:37:05	210
@@ -6,12 +6,75 @@
 using System.Data.SQLite;
 using System.Diagnostics;
 using Enterprise.Logging;
-
+using libxmltv;
+using libxmltv.Interfaces;
+using libxmltv.Core;
 namespace GBPVRProgramDatabaseFixer
 {
-    internal class SQLLITE
+    public interface IOldNewProgram
+    {
+        SQLLITE.IPROGRAMME OldProgram { get; }
+        SQLLITE.IPROGRAMME NewProgram { get; }
+    }
+    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> FixGBPVRProgramsDatabase(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> removed_programs);
+
+        void RemoveOldGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs);
+        void UpdateGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs);
+
+    }
+    public class SQLLITE : ISQLLITE
+    {
+        private class OldNewProgram : IOldNewProgram
+        {
+            public OldNewProgram() : this(new PROGRAMME(), new PROGRAMME()) { }
+            public OldNewProgram(IPROGRAMME _old, IPROGRAMME _new) { OldProgram = _old; NewProgram = _new; }
+
+            public IPROGRAMME OldProgram { get; private set; }
+            public IPROGRAMME NewProgram { get; private set; }
+        }
+        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; }
@@ -26,7 +89,7 @@
             Int64 manual_channel_oid { get; }
             Int64 quality_level { get; }
             Int64 pre_pad_minutes { get; }
-            Int64 post_pad_minutes { get;}
+            Int64 post_pad_minutes { get; }
             Int32 priority { get; }
             String conversion_profile { get; }
         }
@@ -67,7 +130,7 @@
                 }
             }
             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);
@@ -92,9 +155,9 @@
                                 {
                                     prop.SetValue(instance, field_value, null);
                                 }
-                                
+
                             }
-                            catch (Exception ex) 
+                            catch (Exception ex)
                             {
                                 throw ex;
                             }
@@ -112,26 +175,26 @@
 
         private class RECORDING_SCHEDULE : IRECORDING_SCHEDULE
         {
-            public RECORDING_SCHEDULE() 
+            public RECORDING_SCHEDULE()
             {
-                BaseDatabaseDefinition<RECORDING_SCHEDULE>.CreateDefault(this); 
+                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); 
+                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 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 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; }
@@ -142,23 +205,26 @@
             #endregion
         }
 
-        public interface IPROGRAMME
+        public interface IPROGRAMME : IEquatable<IPROGRAMME>
         {
-            Int64 oid { get; }
-            String name { get; }
-            String sub_title { get; }
-            String description { get; }
-            DateTime start_time { get; }
-            DateTime end_time { get; }
-            Int64 channel_oid { get; }
-            String unique_identifier { get; }
-            String rating { get; }
+            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; }
+
+            string ToString();
+            IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite);
         }
         private class PROGRAMME : IPROGRAMME
         {
-            public PROGRAMME() 
+            public PROGRAMME()
             {
-                BaseDatabaseDefinition<PROGRAMME>.CreateDefault(this); 
+                BaseDatabaseDefinition<PROGRAMME>.CreateDefault(this);
             }
             //public PROGRAMME(SQLiteDataReader r, int index) : base(r, index) { }
             public static void Create(ref PROGRAMME instance, SQLiteDataReader r, int index)
@@ -175,17 +241,68 @@
             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);
+                var channelname = channel.display_name;
+                var split = channelname.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
+                definition.ChannelNumber = Convert.ToInt32(split.First());
+                definition.ChannelName = string.IsNullOrEmpty(split.Last()) ? string.Empty : split.Last().ToString();
+                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
+
+            public bool Equals(IPROGRAMME other)
+            {
+                return this.ToString() == other.ToString();
+            }
+            public override string ToString()
+            {
+                StringBuilder builder = new StringBuilder();
+
+                builder.AppendFormat("oid: '{0}' ", oid);
+                builder.AppendFormat("name: '{0}' ", name);
+                builder.AppendFormat("sub_title: '{0}' ", sub_title);
+                builder.AppendFormat("description: '{0}' ", description);
+                builder.AppendFormat("start_time: '{0}' ", start_time.ToDateTimeString());
+                builder.AppendFormat("end_time: '{0}' ", end_time.ToDateTimeString());
+                builder.AppendFormat("channel_oid: '{0}' ", channel_oid);
+                builder.AppendFormat("unique_identifier: '{0}' ", unique_identifier);
+                builder.AppendFormat("rating: '{0}'", rating);
+
+                return builder.ToString();
+            }
+            public override bool Equals(object obj)
+            {
+                if (obj == null) { throw new ArgumentNullException("obj", "Object to compare cannot be null"); }
+                if (obj.GetType().IsAssignableFrom(typeof(IPROGRAMME)))
+                {
+                    return this.Equals((IPROGRAMME)obj);
+                }
+                return base.Equals(obj);
+            }
+            public override int GetHashCode()
+            {
+                return this.ToString().GetHashCode();
+            }
         }
         #endregion
         private static class TABLES
         {
             public const string RECORDING_SCHEDULE = "RECORDING_SCHEDULE";
             public const string PROGRAMME = "PROGRAMME";
+            public const string CHANNEL = "CHANNEL";
         }
-
+        private string DATABASE_BACKUP_FILE = string.Empty;
         //public SQLLite() { }
-        public SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated) 
+        protected SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated)
         {
             this.OnInstanceCreated = OnInstanceCreated;
             //CreateConnection(database);
@@ -196,6 +313,7 @@
                 return;
             }
             ConnectionTest();
+            ReadChannelData();
             ReadRecodringScheduleData();
             ReadProgrammeData();
             OnCreatedInstance(this, new EventArgs());
@@ -204,16 +322,276 @@
         [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); }
         }
 
-        #region SQLLite 
         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> FixGBPVRProgramsDatabase(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> source_invalid)
+        {
+            source_invalid = new List<IPROGRAMME>();
+            List<IPROGRAMME> source_valid = new List<IPROGRAMME>();
+            gbpvr_programs = gbpvr_programs.OrderBy(s => s.start_time).ToList();
+            xmltv_programs = xmltv_programs.OrderBy(s => DateTime.Parse(s.Start)).ToList();
+            gbpvr_programs.TrimExcess();
+            xmltv_programs.TrimExcess();
+
+            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)));
+                // find an entry in xmltv_programs by Channel Number & StartDate                
+                var program_xmltv_entry = program.AsXMLTVProgramDefinition(this);
+                var xmltv_entry = xmltv_programs.Find(s => s.ChannelNumber == program_xmltv_entry.ChannelNumber && s.Start == program_xmltv_entry.Start);
+                if (xmltv_entry == null)
+                {
+                    // xmltv entry was not found
+                    source_invalid.Add(program);
+                }
+                else
+                {
+                    // check if the xmltv entry has different data from the current program
+                    if (!xmltv_entry.Equals(program_xmltv_entry))
+                    {
+                        // data is different
+                        var updated_program = program;
+
+                        if (program_xmltv_entry.Title != xmltv_entry.Title)
+                        {
+                            gLog.Verbose.Warn.WriteLine("    Program oid: '{0}' - replacing title", updated_program.oid);
+                            updated_program.name = xmltv_entry.Title;
+                        }
+                        if (program_xmltv_entry.SubTitle != xmltv_entry.SubTitle)
+                        {
+                            gLog.Verbose.Warn.WriteLine("    Program oid: '{0}' - replacing sub_title", updated_program.oid);
+                            updated_program.sub_title = xmltv_entry.SubTitle;
+                        }
+                        if (program_xmltv_entry.Stop != xmltv_entry.Stop)
+                        {
+                            gLog.Verbose.Warn.WriteLine("    Program oid: '{0}' - replacing end_time", updated_program.oid);
+                            updated_program.end_time = DateTime.Parse(xmltv_entry.Stop);
+                        }
+                        if (program_xmltv_entry.Start != xmltv_entry.Start)
+                        {
+                            gLog.Verbose.Warn.WriteLine("    Program oid: '{0}' - replacing start_time", updated_program.oid);
+                            updated_program.start_time = DateTime.Parse(xmltv_entry.Start);
+                        }
+                        if (program_xmltv_entry.Description != xmltv_entry.Description)
+                        {
+                            gLog.Verbose.Warn.WriteLine("    Program oid: '{0}' - replacing description", updated_program.oid);
+                            updated_program.description = xmltv_entry.Description;
+                        }
+
+                        var updated_xmltv_entry = updated_program.AsXMLTVProgramDefinition(this);
+                        if (!xmltv_entry.Equals(updated_xmltv_entry))
+                        {
+                            throw new Exception(string.Format("Program oid '{0}' was not properly updated.", updated_program.oid));
+                        }
+                        //source_valid.Add(new OldNewProgram(program, updated_program));
+                        source_valid.Add(updated_program);
+                    }
+                    else
+                    {
+                        // data is the same
+                        //source_valid.Add(new OldNewProgram(program, program));
+                        source_valid.Add(program);
+                    }
+                }
+                index++;
+            }
+            gLog.Info.WriteLine("Total XMLTV Programs: 0x{0:x8}", xmltv_programs.Count);
+            gLog.Info.WriteLine("Total Valid GB-PVR Programs: 0x{0:x8}", source_valid.Count);
+            gLog.Info.WriteLine("Total Invalid GB-PVR Programs: 0x{0:x8}", source_invalid.Count);
+            gLog.Info.WriteLine("Total GB-PVR Programs (Valid+Invalid): 0x{0:x8} == 0x{1:x8}", source_valid.Count + source_invalid.Count, gbpvr_programs.Count);
+            #region old-code
+            //List<OldNewProgram> source_update = new List<OldNewProgram>();
+            ////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.Verbose.Error.WriteLine("Found more than one entry: Matching channel='{0}' and start='{1}'", channel.channel_number, start_date.ToDateTimeString());
+            //        gLog.Verbose.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.Verbose.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.Verbose.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.Verbose.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;
+            //        bool needsupdate = false;
+            //        if (xmltv_entry.Title != program.name)
+            //        {
+            //            //gLog.Verbose.Warn.WriteLine("    Updating:");
+            //            //gLog.Verbose.Warn.WriteLine("        Old Title: {0}", program.name);
+            //            //gLog.Verbose.Warn.WriteLine("        New Title: {0}", xmltv_entry.Title);
+            //            updated_program.name = xmltv_entry.Title;
+            //            needsupdate = true;
+            //        }
+            //        if (xmltv_entry.SubTitle != program.sub_title)
+            //        {
+            //            //gLog.Verbose.Warn.WriteLine("    Updating:");
+            //            //gLog.Verbose.Warn.WriteLine("        Old SubTile: {0}", program.sub_title);
+            //            //gLog.Verbose.Warn.WriteLine("        New SubTile: {0}", xmltv_entry.SubTitle);
+            //            updated_program.sub_title = xmltv_entry.SubTitle;
+            //            needsupdate = true;
+            //        }
+            //        if (xmltv_entry.Description != program.description)
+            //        {
+            //            //gLog.Verbose.Warn.WriteLine("    Updating:");
+            //            //gLog.Verbose.Warn.WriteLine("        Old Descption: {0}", program.description);
+            //            //gLog.Verbose.Warn.WriteLine("        New Descption: {0}", xmltv_entry.Description);
+            //            updated_program.description = xmltv_entry.Description;
+            //            needsupdate = true;
+            //        }
+            //        if (DateTime.Parse(xmltv_entry.Start) != program.start_time)
+            //        {
+            //            //gLog.Verbose.Warn.WriteLine("    Updating:");
+            //            //gLog.Verbose.Warn.WriteLine("        Old StartTime: {0}", program.start_time.ToDateTimeString());
+            //            //gLog.Verbose.Warn.WriteLine("        New StartTime: {0}", DateTime.Parse(xmltv_entry.Start).ToDateTimeString());
+            //            updated_program.start_time = DateTime.Parse(xmltv_entry.Start);
+            //            needsupdate = true;
+            //        }
+            //        if (DateTime.Parse(xmltv_entry.Stop) != program.end_time)
+            //        {
+            //            //gLog.Verbose.Warn.WriteLine("    Updating:");
+            //            //gLog.Verbose.Warn.WriteLine("        Old EndTime: {0}", program.end_time.ToDateTimeString());
+            //            //gLog.Verbose.Warn.WriteLine("        New EndTime: {0}", DateTime.Parse(xmltv_entry.Stop).ToDateTimeString());
+            //            updated_program.end_time = DateTime.Parse(xmltv_entry.Stop);
+            //            needsupdate = true;
+            //        }                    
+            //        if (needsupdate)
+            //        {
+            //            OldNewProgram p = new OldNewProgram();
+            //            p.OldProgram = program;
+            //            p.NewProgram = updated_program;
+            //            source_update.Add(p);
+            //        }
+            //        source_valid.Add(updated_program);
+            //    }
+            //    index++;
+            //}
+            //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();
+            //if (source_invalid.Count > 0)
+            //{
+            //    double source_invalid_count = source_valid.Count;
+            //    double source_invalid_index = 0;
+            //    double source_invalid_progress = 0;
+            //    foreach (var old_program in source_invalid)
+            //    {
+            //        source_invalid_progress = 100.0 * (source_invalid_index / source_invalid_count);
+            //        gLog.ReportProgress(this, new ReportProgressEventArgs((int)source_invalid_progress, string.Format("Removing old GBPVR Programs: {0:00}%", (int)source_invalid_progress)));    
+            //        // remove database entry
+            //        if (!RemoveProgramDatabaseEntry(old_program))
+            //        {
+            //            gLog.Error.WriteLine("Failed to remove program with oid: '{0}'", old_program.oid);
+            //        }
+            //        else
+            //        {
+            //            gLog.Verbose.Info.WriteLine("Removed program with oid: '{0}'", old_program.oid);
+            //        }
+            //        source_invalid_index++;
+            //    }
+            //}
+            //else
+            //{
+            //    gLog.Info.WriteLine("No old GB-PVR Programs needed to be removed.");
+            //}
+            //if (source_update.Count > 0)
+            //{
+            //    double source_update_count = source_valid.Count;
+            //    double source_update_index = 0;
+            //    double source_update_progress = 0;
+            //    foreach (var p in source_update)
+            //    {
+            //        source_update_progress = 100.0 * (source_update_index / source_update_count);
+            //        gLog.ReportProgress(this, new ReportProgressEventArgs((int)source_update_progress, string.Format("Updating GBPVR Programs: {0:00}%", (int)source_update_progress)));    
+            //        // remove database entry
+            //        if (!UpdateProgramDatabaseEntry(p.OldProgram,p.NewProgram))
+            //        {
+            //            gLog.Error.WriteLine("Failed to update program with oid: '{0}'", p.OldProgram.oid);
+            //        }
+            //        else
+            //        {
+            //            gLog.Verbose.Info.WriteLine("Upated program with oid: '{0}'", p.OldProgram.oid);
+            //        }
+            //        source_update_index++;
+            //    }
+            //}
+            //else
+            //{
+            //    gLog.Info.WriteLine("No GB-PVR Programs needed to be updated.");
+            //}
+            //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);
+            #endregion
+
+            return source_valid;
+        }
         #endregion
 
 
@@ -233,7 +611,7 @@
                 using (SQLiteConnection con = CreateConnection())
                 {
                     con.Open();
-                    string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
+                    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))
                     {
@@ -280,6 +658,7 @@
                 gLog.Info.WriteLine("\tDestination: {0}", backup_file);
 
                 System.IO.File.Copy(this.Database, backup_file);
+                DATABASE_BACKUP_FILE = backup_file;
                 return true;
             }
             catch (Exception ex)
@@ -288,40 +667,100 @@
                 return false;
             }
         }
-
-        private void ReadProgrammeData()
+        private void ReadChannelData()
         {
             try
             {
-                List<IPROGRAMME> programs = new List<IPROGRAMME>();
+                List<ICHANNEL> channels = new List<ICHANNEL>();
                 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))
+                    try
                     {
-                        using (SQLiteDataReader r = cmd.ExecuteReader())
+                        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))
                         {
-                            if (!r.HasRows)
+                            using (SQLiteDataReader r = cmd.ExecuteReader())
                             {
-                                gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
+                                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);
+                                    }
+                                }
                             }
-                            else
+                        }
+                    }
+                    catch (SQLiteException ex)
+                    {
+                        gLog.Error.WriteLine(ex.ToString());
+                    }
+                    finally
+                    {
+                        con.Close();
+                    }
+                }
+                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())
+                {
+                    try
+                    {
+                        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())
                             {
-                                while (r.Read())
+                                if (!r.HasRows)
                                 {
-                                    PROGRAMME program = new PROGRAMME();
-                                    for (int i = 0; i < r.FieldCount; i++)
+                                    gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
+                                }
+                                else
+                                {
+                                    while (r.Read())
                                     {
-                                        PROGRAMME.Create(ref program, r, i);
+                                        PROGRAMME program = new PROGRAMME();
+                                        for (int i = 0; i < r.FieldCount; i++)
+                                        {
+                                            PROGRAMME.Create(ref program, r, i);
+                                        }
+                                        programs.Add(program);
                                     }
-                                    programs.Add(program);
                                 }
                             }
                         }
                     }
-                    con.Clone();
+                    catch (SQLiteException ex)
+                    {
+                        gLog.Error.WriteLine(ex.ToString());
+                    }
+                    finally
+                    {
+                        con.Close();
+                    }
                 }
                 this.Programs = programs;
             }
@@ -337,33 +776,42 @@
                 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))
+                    try
                     {
-                        using (SQLiteDataReader r = cmd.ExecuteReader())
+                        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))
                         {
-                            if (!r.HasRows)
+                            using (SQLiteDataReader r = cmd.ExecuteReader())
                             {
-                                gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
-                            }
-                            else
-                            {
-                                while (r.Read())
+                                if (!r.HasRows)
                                 {
-                                    RECORDING_SCHEDULE recording = new RECORDING_SCHEDULE();
-                                    for (int i = 0; i < r.FieldCount; i++)
+                                    gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
+                                }
+                                else
+                                {
+                                    while (r.Read())
                                     {
-                                        RECORDING_SCHEDULE.Create(ref recording, r, i);
+                                        RECORDING_SCHEDULE recording = new RECORDING_SCHEDULE();
+                                        for (int i = 0; i < r.FieldCount; i++)
+                                        {
+                                            RECORDING_SCHEDULE.Create(ref recording, r, i);
+                                        }
+                                        recordings.Add(recording);
                                     }
-
-                                    recordings.Add(recording);
                                 }
                             }
                         }
                     }
-                    con.Clone();
+                    catch (SQLiteException ex)
+                    {
+                        gLog.Error.WriteLine(ex.ToString());
+                    }
+                    finally
+                    {
+                        con.Close();
+                    }
                 }
                 this.Recordings = recordings;
             }
@@ -372,5 +820,293 @@
                 gLog.Error.WriteLine(ex.ToString());
             }
         }
+
+        public void RemoveOldGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs)
+        {            
+            gLog.Info.WriteLine("Removing all gbpvr programs.");
+            double total = programs.Count;
+            double index = 0;
+            double progress = 0;
+            Stopwatch st = new Stopwatch();
+            st.Start();
+            foreach (var program in programs)
+            {
+                progress = 100.0 * (index / total);
+                gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Removing GBPVR Program oid='{0}' ({1:00}%)", program.oid, (int)progress)));
+                if (!RemoveProgramDatabaseEntry(program))
+                {
+                    gLog.Error.WriteLine("Failed to remove program with oid: {0}", program.oid);
+                }
+                index++;
+            }
+            //Stopwatch st = new Stopwatch();
+            //st.Start();
+            //if (!RemoveAllProgramDatabaseEntries())
+            //{
+            //    gLog.Error.WriteLine("Failed to remove one or more program(s)");
+            //}
+            st.Stop();
+            gLog.Warn.WriteLine("    operation took: {0:0.00000} seconds", st.Elapsed.TotalSeconds);
+        }
+        public void UpdateGBPVRPrograms(List<IPROGRAMME> programs)
+        {
+            int ChunkSize = 1024;
+            var ChunkList = programs.Chunk<IPROGRAMME>(ChunkSize);
+
+            double total = programs.Count;
+            double index = 0;
+            double progress = 0;
+            Stopwatch st1 = new Stopwatch();
+            st1.Start();
+            foreach (var p in ChunkList)
+            {
+                //Stopwatch st2 = new Stopwatch();
+                //st2.Start();
+                progress = 100.0 * (index / total);
+                //gLog.Warn.WriteLine("Inserting {0} gbpvr programs ({1} of {2})", p.Count(), index, total);   
+                if (!UpdateProgramEntryDatabase(p))
+                {
+                    gLog.Error.WriteLine("Failed to update one or more program(s)");
+                }
+                //st2.Stop();
+                //gLog.Warn.WriteLine("    operation took: {0:0.00000} seconds", st2.Elapsed.TotalSeconds);
+                //gLog.Warn.WriteLine(System.Environment.NewLine);
+                gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Updating {0} gbpvr programs ({1} of {2}) {0:00}%", p.Count(), index, total, (int)progress)));
+                index += (double)p.Count();                
+            }
+            st1.Stop();
+            gLog.Warn.WriteLine("    operation took: {0:0.00000} seconds overall", st1.Elapsed.TotalSeconds);
+        }
+
+        private bool RemoveAllProgramDatabaseEntries()
+        {
+            bool result = false;
+            try
+            {
+                using (SQLiteConnection con = CreateConnection())
+                {
+                    try
+                    {
+                        //gLog.Verbose.Info.WriteLine("Removing old program with oid: '{0}'", old_program.oid);
+                        con.Open();
+                        string command_text = string.Format(@"DELETE FROM [{0}];", TABLES.PROGRAMME);
+                        //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
+                        using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
+                        {
+                            //cmd.Parameters.Add(new SQLiteParameter("oid", old_program.oid));
+                            int rowsupdated = cmd.ExecuteNonQuery();
+                            //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
+                        }
+                        result = true;
+                    }
+                    catch (SQLiteException ex)
+                    {
+                        gLog.Error.WriteLine(ex.ToString());
+                        result = false;
+                    }
+                    finally
+                    {
+                        con.Close();
+                    }
+                }
+            }
+            catch (Exception ex)
+            {
+                gLog.Error.WriteLine(ex.ToString());
+                result = false;
+            }
+            return result;
+        }
+        private bool RemoveProgramDatabaseEntry(IPROGRAMME old_program)
+        {
+            bool result = false;
+            try
+            {
+                using (SQLiteConnection con = CreateConnection())
+                {
+                    try
+                    {
+                        //gLog.Verbose.Info.WriteLine("Removing old program with oid: '{0}'", old_program.oid);
+                        con.Open();
+                        string command_text = string.Format(@"DELETE FROM [{0}] WHERE [oid] = @oid;", TABLES.PROGRAMME);
+                        //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
+                        using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
+                        {
+                            cmd.Parameters.Add(new SQLiteParameter("oid", old_program.oid));
+                            int rowsupdated = cmd.ExecuteNonQuery();
+                            //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
+                        }
+                        result = true;
+                    }
+                    catch (SQLiteException ex)
+                    {
+                        gLog.Error.WriteLine(ex.ToString());
+                        result = false;
+                    }
+                    finally
+                    {
+                        con.Close();
+                    }
+                }
+            }
+            catch (Exception ex)
+            {
+                gLog.Error.WriteLine(ex.ToString());
+                result = false;
+            }
+            return result;
+        }
+        private bool UpdateProgramEntryDatabase(IEnumerable<IPROGRAMME> list)
+        {
+            bool result = false;
+            try
+            {
+                using (SQLiteConnection con = CreateConnection())
+                {
+                    try
+                    {
+                        //gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", new_program.oid);
+                        con.Open();
+                        //string command_text = string.Format(@"UPDATE [{0}] SET [name]=@name,[sub_title]=@subtitle, WHERE [OID] = @oid", TABLES.PROGRAMME);
+                        string command_text = string.Empty;
+                        command_text = BuildGBPVRMultiUpdateCommand(list);
+                        //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
+                        using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
+                        {
+                            int rowsupdated = cmd.ExecuteNonQuery();
+                            //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
+                        }
+
+                        result = true;
+                    }
+                    catch (SQLiteException ex)
+                    {
+                        gLog.Error.WriteLine(ex.ToString());
+                        result = false;
+                    }
+                    finally
+                    {
+                        con.Close();
+                    }
+                }
+            }
+            catch (Exception ex)
+            {
+                gLog.Error.WriteLine(ex.ToString());
+                result = false;
+            }
+            return result;
+        }
+        private bool InsertProgramEntryDatabase(IEnumerable<IPROGRAMME> list)
+        {
+            bool result = false;
+            try
+            {
+                using (SQLiteConnection con = CreateConnection())
+                {
+                    try
+                    {
+                        //gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", new_program.oid);
+                        con.Open();
+                        //string command_text = string.Format(@"UPDATE [{0}] SET [name]=@name,[sub_title]=@subtitle, WHERE [OID] = @oid", TABLES.PROGRAMME);
+                        string command_text = string.Empty;
+                        command_text = BuildGBPVRMultiInsertCommand(list);
+                        //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
+                        using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
+                        {                          
+                            int rowsupdated = cmd.ExecuteNonQuery();
+                            //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
+                        }
+
+                        result = true;
+                    }
+                    catch (SQLiteException ex)
+                    {
+                        gLog.Error.WriteLine(ex.ToString());
+                        result = false;
+                    }
+                    finally
+                    {
+                        con.Close();
+                    }
+                }
+            }
+            catch (Exception ex)
+            {
+                gLog.Error.WriteLine(ex.ToString());
+                result = false;
+            }
+            return result;
+        }
+        #region Multi-Update Command Support
+        private string BuildGBPVRMultiUpdateCommand(IEnumerable<IPROGRAMME> list)
+        {
+            StringBuilder builder = new StringBuilder();
+            /*
+                insert into table1 (field1,field2) values (value1,value2);
+                insert into table1 (field1,field2) values (value1,value2);
+                insert into table1 (field1,field2) values (value1,value2);
+                insert into table1 (field1,field2) values (value1,value2)
+             */
+            builder.AppendLine("begin transaction;");
+            foreach (var t in list)
+            {
+                builder.AppendLine(BuildGBPVRSingleInsertCommand(t));
+            }
+            builder.AppendLine("end transaction;");
+            return builder.ToString();
+        }
+        private string BuildGBPVRSingleUpdateCommand(IPROGRAMME program)
+        {
+            StringBuilder builder = new StringBuilder();
+            builder.AppendFormat("update {0} SET ", TABLES.PROGRAMME);
+            builder.AppendFormat("name=\"{0}\", ", program.name);
+            builder.AppendFormat("sub_title=\"{0}\", ", program.sub_title);
+            builder.AppendFormat("description=\"{0}\", ", program.description);
+            builder.AppendFormat("start_time='{0}', ", program.start_time.ToString("yyyy/MM/dd HH:mm:ss.fffffff"));
+            builder.AppendFormat("end_time='{0}', ", program.end_time.ToString("yyyy/MM/dd HH:mm:ss.fffffff"));
+            builder.AppendFormat("channel_oid={0}, ", program.channel_oid);
+            builder.AppendFormat("unique_identifier=\"{0}\", ", program.unique_identifier);
+            builder.AppendFormat("rating=\"{0}\" ", program.rating);
+            builder.AppendFormat("where oid={0};", program.oid);
+            return builder.ToString();
+        }
+        #endregion
+        #region Multi-Insert Command Support
+        private string BuildGBPVRMultiInsertCommand(IEnumerable<IPROGRAMME> list)
+        {
+            StringBuilder builder = new StringBuilder();
+            /*
+                insert into table1 (field1,field2) values (value1,value2);
+                insert into table1 (field1,field2) values (value1,value2);
+                insert into table1 (field1,field2) values (value1,value2);
+                insert into table1 (field1,field2) values (value1,value2)
+             */
+            builder.AppendLine("begin transaction;");
+            foreach (var t in list)
+            {
+                builder.AppendLine(BuildGBPVRSingleInsertCommand(t));
+            }
+            builder.AppendLine("end transaction;");
+            return builder.ToString();
+        }
+        
+        private string BuildGBPVRSingleInsertCommand(IPROGRAMME program)
+        {
+            StringBuilder builder = new StringBuilder();
+            builder.AppendFormat("insert into {0} (oid,name,sub_title,description,start_time,end_time,channel_oid,unique_identifier,rating) values (", TABLES.PROGRAMME);
+            builder.AppendFormat("{0},",program.oid);
+            builder.AppendFormat("\"{0}\",", program.name);
+            builder.AppendFormat("\"{0}\",", program.sub_title);
+            builder.AppendFormat("\"{0}\",", program.description);
+            builder.AppendFormat("'{0}',", program.start_time.ToString("yyyy/MM/dd HH:mm:ss.fffffff"));
+            builder.AppendFormat("'{0}',", program.end_time.ToString("yyyy/MM/dd HH:mm:ss.fffffff"));
+            builder.AppendFormat("{0},", program.channel_oid);
+            builder.AppendFormat("\"{0}\",", program.unique_identifier);
+            builder.AppendFormat("\"{0}\");", program.rating);
+            return builder.ToString();
+        }
+        #endregion
+
     }
 }

 

  ViewVC Help
Powered by ViewVC 1.1.22