/[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/16 15:33:25	173
+++ trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs	2013/03/16 16:03:19	174
@@ -17,7 +17,7 @@
         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);
+        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
@@ -293,7 +293,7 @@
             return range;
         }
 
-        public List<SQLLITE.IPROGRAMME> CreateUpdatedProgramsList(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> source_invalid)
+        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();            
@@ -348,17 +348,24 @@
                 {
                     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);
+                    // remove database entry
+                    if (!RemoveProgramDatabaseEntry(program))
+                    {
+                        gLog.Error.WriteLine("Failed to remove entry with oid: '{0}'", program.oid);
+                    }
                 }
                 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;
+                    bool needsupdate = false;
                     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;
+                        needsupdate = true;
                     }
                     if (xmltv_entry.SubTitle != program.sub_title)
                     {
@@ -366,13 +373,15 @@
                         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;
+                        needsupdate = true;
                     }
                     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;                        
+                        updated_program.description = xmltv_entry.Description;
+                        needsupdate = true;
                     }
                     if (DateTime.Parse(xmltv_entry.Start) != program.start_time)
                     {
@@ -380,6 +389,7 @@
                         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);
+                        needsupdate = true;
                     }
                     if (DateTime.Parse(xmltv_entry.Stop) != program.end_time)
                     {
@@ -387,8 +397,17 @@
                         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);
+                        needsupdate = true;
                     }
                     source_valid.Add(updated_program);
+                    if (needsupdate)
+                    {
+                        // update database entry
+                        if (!UpdateProgramDatabaseEntry(program, updated_program))
+                        {
+                            gLog.Error.WriteLine("Failed to update entry with oid: '{0}'", program.oid);
+                        }
+                    }
                 }
                 index++;
             }
@@ -611,5 +630,102 @@
             }
         }
 
+
+
+        private bool RemoveProgramDatabaseEntry(IPROGRAMME old_program)
+        {
+            bool result = false;
+            try
+            {                
+                using (SQLiteConnection con = CreateConnection())
+                {
+                    try
+                    {
+                        gLog.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.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.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("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.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