/[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 21:04:37	193
+++ trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs	2013/03/17 01:01:10	213
@@ -22,10 +22,10 @@
         List<SQLLITE.IRECORDING_SCHEDULE> Recordings { get; }
         List<SQLLITE.ICHANNEL> Channels { get; }
         IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs);
-        List<IOldNewProgram> FixGBPVRProgramsDatabase(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);
 
         void RemoveOldGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs);
-        void UpdateGBPVRPrograms(List<IOldNewProgram> programs);
+        void UpdateGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs);
 
     }
     public class SQLLITE : ISQLLITE
@@ -300,7 +300,7 @@
             public const string PROGRAMME = "PROGRAMME";
             public const string CHANNEL = "CHANNEL";
         }
-
+        private string DATABASE_BACKUP_FILE = string.Empty;
         //public SQLLite() { }
         protected SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated)
         {
@@ -347,10 +347,10 @@
             return range;
         }
 
-        public List<IOldNewProgram> FixGBPVRProgramsDatabase(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>();
-            List<IOldNewProgram> source_valid = new List<IOldNewProgram>();
+            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();
@@ -410,12 +410,14 @@
                         {
                             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(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(new OldNewProgram(program, program));
+                        source_valid.Add(program);
                     }
                 }
                 index++;
@@ -656,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)
@@ -820,63 +823,80 @@
 
         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 Programs: {0:00}%", (int)progress)));
+            //    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} minutes", st.Elapsed.TotalMinutes);
-        }
-        public void UpdateGBPVRPrograms(List<IOldNewProgram> programs)
-        {
-            int STEP = 100;
-            List<List<IPROGRAMME>> array_list = new List<List<IPROGRAMME>>(); // holds a list of ~STEP programs per index
-            for (int i = 0; i < programs.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);
+
+            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)
             {
-                List<IPROGRAMME> tmp = new List<IPROGRAMME>(STEP);
-                for (int j = 0; j < STEP; j++)
+                progress = 100.0 * (index / total);
+                if (!RemoveProgramDatabaseEntry(p))
                 {
-                    try
-                    {
-                        tmp.Add(programs[i + j].NewProgram);
-                    }
-                    catch { }
+                    gLog.Error.WriteLine("Failed to remove one or more program(s)");
                 }
-                tmp.TrimExcess();
-                array_list.Add(tmp);
-                i += STEP;
+                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(); 
             }
-            array_list.TrimExcess();
+            st1.Stop();
+            gLog.Warn.WriteLine("    operation took: {0:0.00000} seconds overall", st1.Elapsed.TotalSeconds);
+        }
+        public void UpdateGBPVRPrograms(List<IPROGRAMME> programs)
+        {
+            int ChunkSize = 1024;
+            var ChunkList = programs.Chunk<IPROGRAMME>(ChunkSize);
 
-            foreach (var p in array_list)
+            double total = programs.Count;
+            double index = 0;
+            double progress = 0;
+            Stopwatch st1 = new Stopwatch();
+            st1.Start();
+            foreach (var p in ChunkList)
             {
-                gLog.Info.WriteLine("Inserting {0} gbpvr programs.", p.Count);
-                Stopwatch st = new Stopwatch();
-                st.Start();
-                if (!InsertProgramEntryDatabase(p))
+                //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 insert one or more program(s)");
+                    gLog.Error.WriteLine("Failed to update one or more program(s)");
                 }
-                st.Stop();
-                gLog.Warn.WriteLine("    operation took: {0:0.00000} minutes", st.Elapsed.TotalMinutes);
-            }          
-
+                //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}) {3: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()
@@ -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,59 @@
                 {
                     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(@"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))
+                        {
+                            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(@"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 = BuildGBPVRMultiUpdateCommand(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)
@@ -957,7 +1020,7 @@
             }
             return result;
         }
-        private bool InsertProgramEntryDatabase(List<IPROGRAMME> list)
+        private bool InsertProgramEntryDatabase(IEnumerable<IPROGRAMME> list)
         {
             bool result = false;
             try
@@ -999,7 +1062,8 @@
             return result;
         }
 
-        private string BuildGBPVRMultiInsertCommand(List<IPROGRAMME> list)
+        #region Multi-Delete Command Support
+        private string BuildGBPVRMultiDeleteCommand(IEnumerable<IPROGRAMME> list)
         {
             StringBuilder builder = new StringBuilder();
             /*
@@ -1008,13 +1072,74 @@
                 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)
+        {
+            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();
@@ -1023,13 +1148,14 @@
             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.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