/[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 23:35:42	208
+++ trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs	2013/03/17 01:01:10	213
@@ -823,31 +823,51 @@
 
         public void RemoveOldGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs)
         {            
-            gLog.Info.WriteLine("Removing all gbpvr programs.");
+            //gLog.Info.WriteLine("Removing {0} gbpvr programs.", programs.Count);
+            //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 ({0:00}%) oid='{1}'", (int)progress, program.oid)));
+            //    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);
+
+            int ChunkSize = 1024;
+            var ChunkList = programs.Chunk<IPROGRAMME>(ChunkSize);
+
             double total = programs.Count;
             double index = 0;
             double progress = 0;
-            Stopwatch st = new Stopwatch();
-            st.Start();
-            foreach (var program in programs)
+            Stopwatch st1 = new Stopwatch();
+            st1.Start();
+            foreach (var p in ChunkList)
             {
                 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))
+                if (!RemoveProgramDatabaseEntry(p))
                 {
-                    gLog.Error.WriteLine("Failed to remove program with oid: {0}", program.oid);
+                    gLog.Error.WriteLine("Failed to remove one or more program(s)");
                 }
-                index++;
+                gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Removing {0} gbpvr programs ({1} of {2}) {3:00}%", p.Count(), index, total, (int)progress)));
+                index += (double)p.Count(); 
             }
-
-            //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);
+            st1.Stop();
+            gLog.Warn.WriteLine("    operation took: {0:0.00000} seconds overall", st1.Elapsed.TotalSeconds);
         }
         public void UpdateGBPVRPrograms(List<IPROGRAMME> programs)
         {
@@ -872,7 +892,7 @@
                 //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)));
+                gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Updating {0} gbpvr programs ({1} of {2}) {3:00}%", p.Count(), index, total, (int)progress)));
                 index += (double)p.Count();                
             }
             st1.Stop();
@@ -918,7 +938,7 @@
             }
             return result;
         }
-        private bool RemoveProgramDatabaseEntry(IPROGRAMME old_program)
+        private bool RemoveProgramDatabaseEntry(IEnumerable<IPROGRAMME> list)
         {
             bool result = false;
             try
@@ -927,16 +947,18 @@
                 {
                     try
                     {
-                        //gLog.Verbose.Info.WriteLine("Removing old program with oid: '{0}'", old_program.oid);
+                        //gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", new_program.oid);
                         con.Open();
-                        string command_text = string.Format(@"DELETE FROM [{0}] WHERE [oid] = @oid;", TABLES.PROGRAMME);
+                        //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 = BuildGBPVRMultiDeleteCommand(list);
                         //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)
@@ -1039,6 +1061,32 @@
             }
             return result;
         }
+
+        #region Multi-Delete Command Support
+        private string BuildGBPVRMultiDeleteCommand(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(BuildGBPVRSingleDeleteCommand(t));
+            }
+            builder.AppendLine("end transaction;");
+            return builder.ToString();
+        }
+        private string BuildGBPVRSingleDeleteCommand(IPROGRAMME program)
+        {
+            StringBuilder builder = new StringBuilder();
+            builder.AppendFormat("delete from {0} where oid={1};", TABLES.PROGRAMME, program.oid);
+            return builder.ToString();
+        }
+        #endregion
         #region Multi-Update Command Support
         private string BuildGBPVRMultiUpdateCommand(IEnumerable<IPROGRAMME> list)
         {
@@ -1069,7 +1117,7 @@
             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);
+            builder.AppendFormat("where oid={0};", program.oid);
             return builder.ToString();
         }
         #endregion

 

  ViewVC Help
Powered by ViewVC 1.1.22