/[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 20:26:05	159
+++ trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs	2013/03/16 17:13:23	179
@@ -6,11 +6,32 @@
 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 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);
+
+    }
+    public class SQLLITE : ISQLLITE
     {
+        private struct OldNewProgram
+        {
+            public IPROGRAMME OldProgram;
+            public IPROGRAMME NewProgram;
+        }
+        public static ISQLLITE Create(string database, EventHandler<EventArgs> OnInstanceCreated)
+        {
+            return new SQLLITE(database, OnInstanceCreated);
+        }
+
         #region DATABASE DEFINITIONS
 
         public interface ICHANNEL
@@ -175,15 +196,17 @@
 
         public interface 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; }
+
+            IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite);
         }
         private class PROGRAMME : IPROGRAMME
         {
@@ -206,6 +229,19 @@
             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
@@ -217,7 +253,7 @@
         }
 
         //public SQLLite() { }
-        public SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated) 
+        protected SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated) 
         {
             this.OnInstanceCreated = OnInstanceCreated;
             //CreateConnection(database);
@@ -243,11 +279,204 @@
             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>();
+            gbpvr_programs = gbpvr_programs.OrderBy(s => s.start_time).ToList();            
+            List<SQLLITE.IPROGRAMME> source_valid = new List<IPROGRAMME>();
+
+            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);
+
+            return source_valid;
+        }
         #endregion
 
 
@@ -446,5 +675,103 @@
             }
         }
 
+
+
+        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);
+                        }
+                        con.Clone();
+                        result = true;
+                    }
+                    catch (SQLiteException ex)
+                    {
+                        gLog.Error.WriteLine(ex.ToString());
+                        result = false;
+                    }
+                }
+            }
+            catch (Exception ex)
+            {
+                gLog.Error.WriteLine(ex.ToString());
+                result = false;
+            }
+            return result;
+        }
+        private bool UpdateProgramDatabaseEntry(IPROGRAMME old_program, IPROGRAMME new_program)
+        {
+            bool result = false;
+            try
+            {
+                using (SQLiteConnection con = CreateConnection())
+                {
+                    try
+                    {
+                        gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", old_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;
+                        StringBuilder builder = new StringBuilder();
+
+                        builder.AppendLine(string.Format("UPDATE [{0}]", TABLES.PROGRAMME));
+                        builder.Append("SET ");
+                        builder.AppendFormat("[name]=@name,");
+                        builder.AppendFormat("[sub_title]=@sub_title,");
+                        builder.AppendFormat("[description]=@description,");
+                        builder.AppendFormat("[start_time]=@start_time,");
+                        builder.AppendFormat("[end_time]=@end_time,");
+                        builder.AppendFormat("[channel_oid]=@channel_oid,");
+                        builder.AppendFormat("[unique_identifier]=@unique_identifier,");
+                        builder.AppendFormat("[rating]=@rating");
+                        builder.AppendLine(" WHERE [oid] = @oid");
+
+                        command_text = builder.ToString();
+                        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));
+                            cmd.Parameters.Add(new SQLiteParameter("name", new_program.name));
+                            cmd.Parameters.Add(new SQLiteParameter("sub_title", new_program.sub_title));
+                            cmd.Parameters.Add(new SQLiteParameter("description", new_program.description));
+                            cmd.Parameters.Add(new SQLiteParameter("start_time", new_program.start_time));
+                            cmd.Parameters.Add(new SQLiteParameter("end_time", new_program.end_time));
+                            cmd.Parameters.Add(new SQLiteParameter("channel_oid", new_program.channel_oid));
+                            cmd.Parameters.Add(new SQLiteParameter("unique_identifier", new_program.unique_identifier));
+                            cmd.Parameters.Add(new SQLiteParameter("rating", new_program.rating));
+                            int rowsupdated = cmd.ExecuteNonQuery();
+                            gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
+                        }
+                        con.Clone();
+                        result = true;
+                    }
+                    catch (SQLiteException ex)
+                    {
+                        gLog.Error.WriteLine(ex.ToString());
+                        result = false;
+                    }
+                }
+            }
+            catch (Exception ex)
+            {
+                gLog.Error.WriteLine(ex.ToString());
+                result = false;
+            }
+            return result;
+        }
     }
 }

 

  ViewVC Help
Powered by ViewVC 1.1.22