/[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 19:13:18	187
+++ trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs	2013/03/16 20:07:53	191
@@ -816,7 +816,7 @@
         }
 
         public void RemoveOldGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs)
-        {
+        {            
             gLog.Info.WriteLine("Removing all gbpvr programs.");
             //double total = programs.Count;
             //double index = 0;
@@ -831,35 +831,37 @@
             //    }
             //    index++;
             //}
-              if (!RemoveAllProgramDatabaseEntries())
-              {
-                  gLog.Error.WriteLine("Failed to remove one or more program entries");
-              }
+            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} minutes", st.Elapsed.TotalMinutes);
         }
         public void UpdateGBPVRPrograms(List<IOldNewProgram> programs)
         {
-            gLog.Info.WriteLine("Inserting {0} gbpvr programs.", programs.Count);
-            double total = programs.Count;
-            double index = 0;
-            double progress = 0;
+            
+            //double total = programs.Count;
+            //double index = 0;
+            //double progress = 0;
+            List<IPROGRAMME> new_program_list = new List<IPROGRAMME>();
             foreach (var program in programs)
             {
-                progress = 100.0 * (index / total);
-                gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Updating/Inserting GBPVR Programs: {0:00}%", (int)progress)));
-                if (program.NewProgram.Equals(program.OldProgram))
-                {
-                    // program does not need to be updated
-                    continue;
-                }
-                else
-                {
-                    if (!InsertProgramDatabaseEntry(program.NewProgram))
-                    {
-                        gLog.Error.WriteLine("Failed to insert program with oid: {0}", program.NewProgram.oid);
-                    }
-                }
-                index++;
+                new_program_list.Add(program.NewProgram);
             }
+            new_program_list.TrimExcess();
+            gLog.Info.WriteLine("Inserting {0} gbpvr programs.", new_program_list.ToArray().Count());
+            Stopwatch st = new Stopwatch();
+            st.Start();
+            if (!InsertProgramEntryDatabase(new_program_list))
+            {
+                gLog.Error.WriteLine("Failed to insert one or more program(s)");
+            }
+            //string command = BuildGBPVRMultiInsertCommand(new_program_list);
+            st.Stop();
+            gLog.Warn.WriteLine("    operation took: {0:0.00000} minutes", st.Elapsed.TotalMinutes);
         }
 
         private bool RemoveAllProgramDatabaseEntries()
@@ -940,7 +942,7 @@
             }
             return result;
         }
-        private bool InsertProgramDatabaseEntry(IPROGRAMME new_program)
+        private bool InsertProgramEntryDatabase(List<IPROGRAMME> list)
         {
             bool result = false;
             try
@@ -953,33 +955,10 @@
                         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("INSERT INTO [{0}]", TABLES.PROGRAMME));
-                        builder.Append("VALUES (");
-                        builder.AppendFormat("[oid]=@oid,");
-                        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");
-                        builder.AppendFormat(");");
-                        command_text = builder.ToString();
+                        command_text = BuildGBPVRMultiInsertCommand(list);
                         //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
                         using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
-                        {
-                            cmd.Parameters.Add(new SQLiteParameter("oid", new_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);
                         }
@@ -1004,67 +983,38 @@
             }
             return result;
         }
-        private bool UpdateProgramDatabaseEntry(IPROGRAMME new_program)
-        {
-            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;
-                        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", new_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);
-                        }
 
-                        result = true;
-                    }
-                    catch (SQLiteException ex)
-                    {
-                        gLog.Error.WriteLine(ex.ToString());
-                        result = false;
-                    }
-                    finally
-                    {
-                        con.Close();
-                    }
-                }
-            }
-            catch (Exception ex)
+        private string BuildGBPVRMultiInsertCommand(List<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)
+             */
+            foreach (var t in list)
             {
-                gLog.Error.WriteLine(ex.ToString());
-                result = false;
+                builder.AppendLine(BuildGBPVRSingleInsertCommand(t));
             }
-            return result;
+            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);
+            builder.AppendFormat("'{0}',", program.end_time);
+            builder.AppendFormat("{0},", program.channel_oid);
+            builder.AppendFormat("\"{0}\",", program.unique_identifier);
+            builder.AppendFormat("\"{0}\");", program.rating);
+            return builder.ToString();
         }
+      
     }
 }

 

  ViewVC Help
Powered by ViewVC 1.1.22