/[xmltv_parser]/trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs
ViewVC logotype

Annotation of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 194 - (hide annotations) (download)
Sat Mar 16 21:37:29 2013 UTC (7 years, 3 months ago) by william
File size: 49481 byte(s)

1 william 156 //#define SQLLITE_CONNECTION_TEST
2     using System;
3     using System.Collections.Generic;
4     using System.Linq;
5     using System.Text;
6     using System.Data.SQLite;
7     using System.Diagnostics;
8     using Enterprise.Logging;
9 william 163 using libxmltv;
10 william 165 using libxmltv.Interfaces;
11     using libxmltv.Core;
12 william 156 namespace GBPVRProgramDatabaseFixer
13     {
14 william 182 public interface IOldNewProgram
15     {
16     SQLLITE.IPROGRAMME OldProgram { get; }
17     SQLLITE.IPROGRAMME NewProgram { get; }
18     }
19 william 163 public interface ISQLLITE
20 william 156 {
21 william 163 List<SQLLITE.IPROGRAMME> Programs { get; }
22     List<SQLLITE.IRECORDING_SCHEDULE> Recordings { get; }
23     List<SQLLITE.ICHANNEL> Channels { get; }
24     IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs);
25 william 182 List<IOldNewProgram> FixGBPVRProgramsDatabase(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> removed_programs);
26 william 163
27 william 183 void RemoveOldGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs);
28     void UpdateGBPVRPrograms(List<IOldNewProgram> programs);
29    
30 william 163 }
31     public class SQLLITE : ISQLLITE
32     {
33 william 182 private class OldNewProgram : IOldNewProgram
34 william 176 {
35 william 180 public OldNewProgram() : this(new PROGRAMME(), new PROGRAMME()) { }
36     public OldNewProgram(IPROGRAMME _old, IPROGRAMME _new) { OldProgram = _old; NewProgram = _new; }
37    
38     public IPROGRAMME OldProgram { get; private set; }
39     public IPROGRAMME NewProgram { get; private set; }
40 william 176 }
41 william 163 public static ISQLLITE Create(string database, EventHandler<EventArgs> OnInstanceCreated)
42     {
43     return new SQLLITE(database, OnInstanceCreated);
44     }
45    
46 william 156 #region DATABASE DEFINITIONS
47 william 159
48     public interface ICHANNEL
49     {
50     Int64 oid { get; }
51     String name { get; }
52     String channelID { get; }
53     Int64 channel_number { get; }
54     String favourite_channel { get; }
55     String display_name { get; }
56     }
57     private class CHANNEL : ICHANNEL
58     {
59 william 184 public CHANNEL()
60 william 159 {
61 william 184 BaseDatabaseDefinition<CHANNEL>.CreateDefault(this);
62 william 159 }
63     //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }
64    
65     public static void Create(ref CHANNEL instance, SQLiteDataReader r, int index)
66     {
67     BaseDatabaseDefinition<CHANNEL>.Create(ref instance, r, index);
68     }
69     #region ICHANNEL members
70     public Int64 oid { get; set; }
71     public String name { get; set; }
72     public String channelID { get; set; }
73     public Int64 channel_number { get; set; }
74     public String favourite_channel { get; set; }
75     public String display_name { get; set; }
76     #endregion
77     }
78 william 156 public interface IRECORDING_SCHEDULE
79     {
80     Int64 oid { get; }
81     Int64 programme_oid { get; }
82     Int64 capture_source_oid { get; }
83     Int16 status { get; }
84     String filename { get; }
85     Int64 recording_type { get; }
86     Int64 recording_group { get; }
87     DateTime manual_start_time { get; }
88     DateTime manual_end_time { get; }
89     Int64 manual_channel_oid { get; }
90     Int64 quality_level { get; }
91     Int64 pre_pad_minutes { get; }
92 william 184 Int64 post_pad_minutes { get; }
93 william 156 Int32 priority { get; }
94     String conversion_profile { get; }
95     }
96    
97     private static class BaseDatabaseDefinition<T>
98     {
99     public static void CreateDefault(T instance)
100     {
101     try
102     {
103     Type t = typeof(T);
104     var props = t.GetProperties();
105     foreach (var prop in props)
106     {
107     Type prop_type = prop.PropertyType;
108     object field_value = null;
109     try
110     {
111     if (prop_type == typeof(string))
112     {
113     field_value = string.Empty;
114     }
115     else
116     {
117     field_value = Activator.CreateInstance(prop_type);
118     }
119     }
120     catch (Exception ex)
121     {
122     throw ex;
123     }
124     prop.SetValue(instance, field_value, null);
125     }
126     }
127     catch (Exception ex)
128     {
129     throw ex;
130     }
131     }
132     public static void Create(ref T instance, SQLiteDataReader r, int index)
133 william 184 {
134 william 156 string field_name = r.GetName(index);
135     Type field_type = r.GetFieldType(index);
136     object field_value = r.GetValue(index);
137     //gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
138    
139     Type t = typeof(T);
140     var props = t.GetProperties();
141     foreach (var prop in props)
142     {
143     if (prop.Name.ToLower() == field_name.ToLower())
144     {
145     if (prop.PropertyType == field_type)
146     {
147     Type db_type = field_value.GetType();
148     try
149     {
150     if (db_type == typeof(System.DBNull))
151     {
152     prop.SetValue(instance, null, null);
153     }
154     else
155     {
156     prop.SetValue(instance, field_value, null);
157     }
158 william 184
159 william 156 }
160 william 184 catch (Exception ex)
161 william 156 {
162     throw ex;
163     }
164     }
165     else
166     {
167     gLog.Verbose.Debug.WriteLine("Found Property: {0} but there was a type mismatch. Property Type: '{1}' Expected: '{2}'", prop.Name, prop.PropertyType.Name, field_type.Name);
168     throw new InvalidOperationException(string.Format("Found Property: {0} but there was a type mismatch. Property Type: '{1}' Expected: '{2}'", prop.Name, prop.PropertyType.Name, field_type.Name));
169     }
170     }
171     }
172    
173     }
174     }
175    
176     private class RECORDING_SCHEDULE : IRECORDING_SCHEDULE
177     {
178 william 184 public RECORDING_SCHEDULE()
179 william 156 {
180 william 184 BaseDatabaseDefinition<RECORDING_SCHEDULE>.CreateDefault(this);
181 william 156 }
182     //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }
183    
184     public static void Create(ref RECORDING_SCHEDULE instance, SQLiteDataReader r, int index)
185     {
186 william 184 BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(ref instance, r, index);
187 william 156 }
188    
189     #region IRECORDING_SCHEDULE members
190 william 184 public Int64 oid { get; set; }
191     public Int64 programme_oid { get; set; }
192     public Int64 capture_source_oid { get; set; }
193 william 156 public Int16 status { get; set; }
194     public String filename { get; set; }
195 william 184 public Int64 recording_type { get; set; }
196     public Int64 recording_group { get; set; }
197     public DateTime manual_start_time { get; set; }
198 william 156 public DateTime manual_end_time { get; set; }
199     public Int64 manual_channel_oid { get; set; }
200     public Int64 quality_level { get; set; }
201     public Int64 pre_pad_minutes { get; set; }
202     public Int64 post_pad_minutes { get; set; }
203     public Int32 priority { get; set; }
204     public String conversion_profile { get; set; }
205     #endregion
206     }
207    
208 william 182 public interface IPROGRAMME : IEquatable<IPROGRAMME>
209 william 184 {
210 william 168 Int64 oid { get; set; }
211     String name { get; set; }
212     String sub_title { get; set; }
213     String description { get; set; }
214     DateTime start_time { get; set; }
215     DateTime end_time { get; set; }
216     Int64 channel_oid { get; set; }
217     String unique_identifier { get; set; }
218     String rating { get; set; }
219 william 165
220 william 182 string ToString();
221 william 165 IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite);
222 william 156 }
223     private class PROGRAMME : IPROGRAMME
224     {
225 william 184 public PROGRAMME()
226 william 156 {
227 william 184 BaseDatabaseDefinition<PROGRAMME>.CreateDefault(this);
228 william 156 }
229     //public PROGRAMME(SQLiteDataReader r, int index) : base(r, index) { }
230     public static void Create(ref PROGRAMME instance, SQLiteDataReader r, int index)
231     {
232     BaseDatabaseDefinition<PROGRAMME>.Create(ref instance, r, index);
233     }
234     #region IPROGRAMME members
235     public Int64 oid { get; set; }
236     public String name { get; set; }
237     public String sub_title { get; set; }
238     public String description { get; set; }
239     public DateTime start_time { get; set; }
240     public DateTime end_time { get; set; }
241     public Int64 channel_oid { get; set; }
242     public String unique_identifier { get; set; }
243     public String rating { get; set; }
244 william 165 public IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite)
245     {
246     ProgramList.ProgramDefintion definition = new ProgramList.ProgramDefintion();
247 william 181
248 william 165 var channel = sqllite.Channels.Find(s => s.oid == this.channel_oid);
249 william 181 var channelname = channel.display_name;
250     var split = channelname.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
251     definition.ChannelNumber = Convert.ToInt32(split.First());
252     definition.ChannelName = string.IsNullOrEmpty(split.Last()) ? string.Empty : split.Last().ToString();
253 william 165 definition.Description = this.description;
254 william 168 definition.Start = this.start_time.ToDateTimeString();
255     definition.Stop = this.end_time.ToDateTimeString();
256 william 165 definition.SubTitle = this.sub_title;
257     definition.Title = this.name;
258     return definition;
259     }
260 william 156 #endregion
261 william 182
262     public bool Equals(IPROGRAMME other)
263     {
264     return this.ToString() == other.ToString();
265     }
266     public override string ToString()
267     {
268     StringBuilder builder = new StringBuilder();
269    
270     builder.AppendFormat("oid: '{0}' ", oid);
271     builder.AppendFormat("name: '{0}' ", name);
272     builder.AppendFormat("sub_title: '{0}' ", sub_title);
273     builder.AppendFormat("description: '{0}' ", description);
274     builder.AppendFormat("start_time: '{0}' ", start_time.ToDateTimeString());
275     builder.AppendFormat("end_time: '{0}' ", end_time.ToDateTimeString());
276     builder.AppendFormat("channel_oid: '{0}' ", channel_oid);
277     builder.AppendFormat("unique_identifier: '{0}' ", unique_identifier);
278     builder.AppendFormat("rating: '{0}'", rating);
279    
280     return builder.ToString();
281     }
282     public override bool Equals(object obj)
283     {
284     if (obj == null) { throw new ArgumentNullException("obj", "Object to compare cannot be null"); }
285     if (obj.GetType().IsAssignableFrom(typeof(IPROGRAMME)))
286     {
287     return this.Equals((IPROGRAMME)obj);
288     }
289     return base.Equals(obj);
290     }
291     public override int GetHashCode()
292     {
293     return this.ToString().GetHashCode();
294     }
295 william 156 }
296     #endregion
297     private static class TABLES
298     {
299     public const string RECORDING_SCHEDULE = "RECORDING_SCHEDULE";
300     public const string PROGRAMME = "PROGRAMME";
301 william 159 public const string CHANNEL = "CHANNEL";
302 william 156 }
303    
304     //public SQLLite() { }
305 william 184 protected SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated)
306 william 156 {
307     this.OnInstanceCreated = OnInstanceCreated;
308     //CreateConnection(database);
309     this.Database = database;
310 william 157 if (!CreateDatabaseBackup())
311     {
312     gLog.Error.WriteLine("Failed to backup database.");
313     return;
314     }
315 william 156 ConnectionTest();
316 william 159 ReadChannelData();
317 william 156 ReadRecodringScheduleData();
318     ReadProgrammeData();
319     OnCreatedInstance(this, new EventArgs());
320     }
321    
322     [NonSerialized]
323     private EventHandler<EventArgs> _OnInstanceCreated;
324     private EventHandler<EventArgs> OnInstanceCreated { get { return _OnInstanceCreated; } set { _OnInstanceCreated = value; } }
325 william 184
326 william 156 private void OnCreatedInstance(object sender, EventArgs e)
327     {
328     if (OnInstanceCreated != null) { OnInstanceCreated.Invoke(sender, e); }
329     }
330    
331     private string Database;
332 william 184 #region ISQLLITE members
333 william 156 public List<IPROGRAMME> Programs { get; private set; }
334     public List<IRECORDING_SCHEDULE> Recordings { get; private set; }
335 william 159 public List<ICHANNEL> Channels { get; private set; }
336 william 163
337     public IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs)
338     {
339     var list = new List<IPROGRAMME>(programs.ToArray());
340     DateTime first = new DateTime();
341     DateTime last = new DateTime();
342 william 184 first = list.OrderBy(s => s.start_time).ToList().First().start_time;
343 william 163 last = list.OrderBy(s => s.start_time).ToList().Last().start_time;
344 william 167 gLog.Verbose.Debug.WriteLine("\tFirst: {0} = ({1})", first.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), first.ToDateTimeString());
345     gLog.Verbose.Debug.WriteLine("\tLast: {0} = ({1})", last.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), last.ToDateTimeString());
346 william 163 var range = DateTimeRange.Create(first, last);
347     return range;
348     }
349    
350 william 182 public List<IOldNewProgram> FixGBPVRProgramsDatabase(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> source_invalid)
351 william 163 {
352 william 169 source_invalid = new List<IPROGRAMME>();
353 william 182 List<IOldNewProgram> source_valid = new List<IOldNewProgram>();
354 william 180 gbpvr_programs = gbpvr_programs.OrderBy(s => s.start_time).ToList();
355     xmltv_programs = xmltv_programs.OrderBy(s => DateTime.Parse(s.Start)).ToList();
356     gbpvr_programs.TrimExcess();
357     xmltv_programs.TrimExcess();
358 william 176
359 william 168 double total = gbpvr_programs.Count;
360 william 166 double index = 0;
361     double progress = 0;
362 william 168 foreach (var program in gbpvr_programs)
363 william 180 {
364 william 169 progress = 100.0 * (index / total);
365 william 184 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Filtering GBPVR Programs: {0:00}%", (int)progress)));
366 william 180 // find an entry in xmltv_programs by Channel Number & StartDate
367     var program_xmltv_entry = program.AsXMLTVProgramDefinition(this);
368     var xmltv_entry = xmltv_programs.Find(s => s.ChannelNumber == program_xmltv_entry.ChannelNumber && s.Start == program_xmltv_entry.Start);
369 william 168 if (xmltv_entry == null)
370 william 163 {
371 william 180 // xmltv entry was not found
372     source_invalid.Add(program);
373 william 163 }
374 william 164 else
375     {
376 william 180 // check if the xmltv entry has different data from the current program
377 william 184 if (!xmltv_entry.Equals(program_xmltv_entry))
378 william 172 {
379 william 180 // data is different
380 william 182 var updated_program = program;
381    
382     if (program_xmltv_entry.Title != xmltv_entry.Title)
383 william 180 {
384 william 182 gLog.Verbose.Warn.WriteLine(" Program oid: '{0}' - replacing title", updated_program.oid);
385     updated_program.name = xmltv_entry.Title;
386 william 180 }
387 william 182 if (program_xmltv_entry.SubTitle != xmltv_entry.SubTitle)
388     {
389     gLog.Verbose.Warn.WriteLine(" Program oid: '{0}' - replacing sub_title", updated_program.oid);
390     updated_program.sub_title = xmltv_entry.SubTitle;
391     }
392     if (program_xmltv_entry.Stop != xmltv_entry.Stop)
393     {
394     gLog.Verbose.Warn.WriteLine(" Program oid: '{0}' - replacing end_time", updated_program.oid);
395     updated_program.end_time = DateTime.Parse(xmltv_entry.Stop);
396     }
397     if (program_xmltv_entry.Start != xmltv_entry.Start)
398     {
399     gLog.Verbose.Warn.WriteLine(" Program oid: '{0}' - replacing start_time", updated_program.oid);
400     updated_program.start_time = DateTime.Parse(xmltv_entry.Start);
401     }
402     if (program_xmltv_entry.Description != xmltv_entry.Description)
403     {
404     gLog.Verbose.Warn.WriteLine(" Program oid: '{0}' - replacing description", updated_program.oid);
405     updated_program.description = xmltv_entry.Description;
406     }
407    
408     var updated_xmltv_entry = updated_program.AsXMLTVProgramDefinition(this);
409     if (!xmltv_entry.Equals(updated_xmltv_entry))
410     {
411     throw new Exception(string.Format("Program oid '{0}' was not properly updated.", updated_program.oid));
412     }
413     source_valid.Add(new OldNewProgram(program, updated_program));
414 william 172 }
415 william 176 else
416     {
417 william 180 // data is the same
418 william 182 source_valid.Add(new OldNewProgram(program, program));
419 william 176 }
420     }
421 william 181 index++;
422 william 176 }
423 william 192 gLog.Info.WriteLine("Total XMLTV Programs: 0x{0:x8}", xmltv_programs.Count);
424     gLog.Info.WriteLine("Total Valid GB-PVR Programs: 0x{0:x8}", source_valid.Count);
425     gLog.Info.WriteLine("Total Invalid GB-PVR Programs: 0x{0:x8}", source_invalid.Count);
426     gLog.Info.WriteLine("Total GB-PVR Programs (Valid+Invalid): 0x{0:x8} == 0x{1:x8}", source_valid.Count + source_invalid.Count, gbpvr_programs.Count);
427 william 180 #region old-code
428     //List<OldNewProgram> source_update = new List<OldNewProgram>();
429     ////if (range == null)
430     ////{
431     //// gLog.Warn.WriteLine("The DateTimeRange passed in is null...returning the original program list");
432     //// return list;
433     ////}
434     ////gLog.Warn.WriteLine("FilterProgramsByDateRange has not been implemented");
435     //double total = gbpvr_programs.Count;
436     //double index = 0;
437     //double progress = 0;
438     //foreach (var program in gbpvr_programs)
439     //{
440     // progress = 100.0 * (index / total);
441     // gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Filtering GBPVR Programs: {0:00}%", (int)progress)));
442     // var channel_oid = program.channel_oid;
443     // var channel = this.Channels.Find(s => s.oid == channel_oid);
444     // var start_date = program.start_time;
445     // //var xmltv_entry_list = xmltv_programs.FindAll(s => s.ChannelNumber == channel.channel_number && s.Start == start_date.ToDateTimeString());
446     // var query = from c in xmltv_programs
447     // where
448     // c.ChannelNumber == channel.channel_number &&
449     // c.Start == start_date.ToDateTimeString()
450     // select c;
451     // IProgramDefinition xmltv_entry = null;
452     // if (query.Count()-1 > 0)
453     // {
454     // gLog.Verbose.Error.WriteLine("Found more than one entry: Matching channel='{0}' and start='{1}'", channel.channel_number, start_date.ToDateTimeString());
455     // 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);
456     // if (Debugger.IsAttached)
457     // {
458     // gLog.Error.WriteLine(" Found: {0} matching entries", query.Count());
459     // int k_index = 0;
460     // foreach (var k in query)
461     // {
462     // 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);
463     // k_index++;
464     // }
465     // Debugger.Break();
466     // }
467     // }
468     // else
469     // {
470     // xmltv_entry = query.FirstOrDefault();
471     // }
472     // if (xmltv_entry == null)
473     // {
474     // //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());
475     // source_invalid.Add(program);
476     // }
477     // else
478     // {
479     // //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());
480     // var updated_program = program;
481     // bool needsupdate = false;
482     // if (xmltv_entry.Title != program.name)
483     // {
484     // //gLog.Verbose.Warn.WriteLine(" Updating:");
485     // //gLog.Verbose.Warn.WriteLine(" Old Title: {0}", program.name);
486     // //gLog.Verbose.Warn.WriteLine(" New Title: {0}", xmltv_entry.Title);
487     // updated_program.name = xmltv_entry.Title;
488     // needsupdate = true;
489     // }
490     // if (xmltv_entry.SubTitle != program.sub_title)
491     // {
492     // //gLog.Verbose.Warn.WriteLine(" Updating:");
493     // //gLog.Verbose.Warn.WriteLine(" Old SubTile: {0}", program.sub_title);
494     // //gLog.Verbose.Warn.WriteLine(" New SubTile: {0}", xmltv_entry.SubTitle);
495     // updated_program.sub_title = xmltv_entry.SubTitle;
496     // needsupdate = true;
497     // }
498     // if (xmltv_entry.Description != program.description)
499     // {
500     // //gLog.Verbose.Warn.WriteLine(" Updating:");
501     // //gLog.Verbose.Warn.WriteLine(" Old Descption: {0}", program.description);
502     // //gLog.Verbose.Warn.WriteLine(" New Descption: {0}", xmltv_entry.Description);
503     // updated_program.description = xmltv_entry.Description;
504     // needsupdate = true;
505     // }
506     // if (DateTime.Parse(xmltv_entry.Start) != program.start_time)
507     // {
508     // //gLog.Verbose.Warn.WriteLine(" Updating:");
509     // //gLog.Verbose.Warn.WriteLine(" Old StartTime: {0}", program.start_time.ToDateTimeString());
510     // //gLog.Verbose.Warn.WriteLine(" New StartTime: {0}", DateTime.Parse(xmltv_entry.Start).ToDateTimeString());
511     // updated_program.start_time = DateTime.Parse(xmltv_entry.Start);
512     // needsupdate = true;
513     // }
514     // if (DateTime.Parse(xmltv_entry.Stop) != program.end_time)
515     // {
516     // //gLog.Verbose.Warn.WriteLine(" Updating:");
517     // //gLog.Verbose.Warn.WriteLine(" Old EndTime: {0}", program.end_time.ToDateTimeString());
518     // //gLog.Verbose.Warn.WriteLine(" New EndTime: {0}", DateTime.Parse(xmltv_entry.Stop).ToDateTimeString());
519     // updated_program.end_time = DateTime.Parse(xmltv_entry.Stop);
520     // needsupdate = true;
521     // }
522     // if (needsupdate)
523     // {
524     // OldNewProgram p = new OldNewProgram();
525     // p.OldProgram = program;
526     // p.NewProgram = updated_program;
527     // source_update.Add(p);
528     // }
529     // source_valid.Add(updated_program);
530     // }
531     // index++;
532     //}
533     //source_valid = source_valid.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
534     //source_invalid = source_invalid.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
535     //if (source_invalid.Count > 0)
536     //{
537     // double source_invalid_count = source_valid.Count;
538     // double source_invalid_index = 0;
539     // double source_invalid_progress = 0;
540     // foreach (var old_program in source_invalid)
541     // {
542     // source_invalid_progress = 100.0 * (source_invalid_index / source_invalid_count);
543     // gLog.ReportProgress(this, new ReportProgressEventArgs((int)source_invalid_progress, string.Format("Removing old GBPVR Programs: {0:00}%", (int)source_invalid_progress)));
544     // // remove database entry
545     // if (!RemoveProgramDatabaseEntry(old_program))
546     // {
547     // gLog.Error.WriteLine("Failed to remove program with oid: '{0}'", old_program.oid);
548     // }
549     // else
550     // {
551     // gLog.Verbose.Info.WriteLine("Removed program with oid: '{0}'", old_program.oid);
552     // }
553     // source_invalid_index++;
554     // }
555     //}
556     //else
557     //{
558     // gLog.Info.WriteLine("No old GB-PVR Programs needed to be removed.");
559     //}
560     //if (source_update.Count > 0)
561     //{
562     // double source_update_count = source_valid.Count;
563     // double source_update_index = 0;
564     // double source_update_progress = 0;
565     // foreach (var p in source_update)
566     // {
567     // source_update_progress = 100.0 * (source_update_index / source_update_count);
568     // gLog.ReportProgress(this, new ReportProgressEventArgs((int)source_update_progress, string.Format("Updating GBPVR Programs: {0:00}%", (int)source_update_progress)));
569     // // remove database entry
570     // if (!UpdateProgramDatabaseEntry(p.OldProgram,p.NewProgram))
571     // {
572     // gLog.Error.WriteLine("Failed to update program with oid: '{0}'", p.OldProgram.oid);
573     // }
574     // else
575     // {
576     // gLog.Verbose.Info.WriteLine("Upated program with oid: '{0}'", p.OldProgram.oid);
577     // }
578     // source_update_index++;
579     // }
580     //}
581     //else
582     //{
583     // gLog.Info.WriteLine("No GB-PVR Programs needed to be updated.");
584     //}
585     //gLog.Info.WriteLine("Total XMLTV Programs: 0x{0:x8}", xmltv_programs.Count);
586     //gLog.Info.WriteLine("Updated: 0x{0:x8} GB-PVR Programs", source_valid.Count);
587     //gLog.Info.WriteLine("Removed: 0x{0:x8} GB-PVR Programs", source_invalid.Count);
588     //gLog.Info.WriteLine("Total GB-PVR Programs (Updated & Removed): 0x{0:x8}", source_valid.Count + source_invalid.Count);
589     #endregion
590 william 176
591 william 166 return source_valid;
592 william 163 }
593 william 156 #endregion
594    
595    
596     private string CreateConnectionString()
597     {
598     string connection_string = string.Format("Data Source={0}", this.Database);
599     return connection_string;
600     }
601    
602     private SQLiteConnection CreateConnection() { SQLiteConnection connection = new SQLiteConnection(CreateConnectionString()); return connection; }
603    
604     [Conditional("SQLLITE_CONNECTION_TEST")]
605     private void ConnectionTest()
606     {
607     try
608     {
609     using (SQLiteConnection con = CreateConnection())
610     {
611     con.Open();
612 william 159 string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
613 william 156 gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
614     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
615     {
616     using (SQLiteDataReader r = cmd.ExecuteReader())
617     {
618     if (!r.HasRows)
619     {
620     gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
621     }
622     else
623     {
624     while (r.Read())
625     {
626     for (int i = 0; i < r.FieldCount; i++)
627     {
628     string field_name = r.GetName(i);
629     Type field_type = r.GetFieldType(i);
630     object field_value = r.GetValue(i);
631     gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
632     }
633     break;
634     }
635     }
636     }
637     }
638 william 157 con.Clone();
639 william 156 }
640     OnCreatedInstance(this, new EventArgs());
641     }
642     catch (Exception ex)
643     {
644     gLog.Error.WriteLine(ex.ToString());
645     }
646     }
647    
648 william 157
649     private bool CreateDatabaseBackup()
650     {
651     try
652     {
653     string backup_file = string.Format("{0}.{1}", this.Database, DateTime.Now.ToString("yyyyMMddHHmmss"));
654     gLog.Info.WriteLine("Creating Database backup...");
655     gLog.Info.WriteLine("\tSource: {0}", this.Database);
656     gLog.Info.WriteLine("\tDestination: {0}", backup_file);
657    
658     System.IO.File.Copy(this.Database, backup_file);
659     return true;
660     }
661     catch (Exception ex)
662     {
663     gLog.Error.WriteLine(ex.ToString());
664     return false;
665     }
666     }
667 william 159 private void ReadChannelData()
668     {
669     try
670     {
671     List<ICHANNEL> channels = new List<ICHANNEL>();
672     using (SQLiteConnection con = CreateConnection())
673     {
674 william 184 try
675 william 159 {
676 william 184 con.Open();
677     string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
678     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
679     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
680 william 159 {
681 william 184 using (SQLiteDataReader r = cmd.ExecuteReader())
682 william 159 {
683 william 184 if (!r.HasRows)
684 william 159 {
685 william 184 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
686     }
687     else
688     {
689     while (r.Read())
690 william 159 {
691 william 184 CHANNEL channel = new CHANNEL();
692     for (int i = 0; i < r.FieldCount; i++)
693     {
694     CHANNEL.Create(ref channel, r, i);
695     }
696     channels.Add(channel);
697 william 159 }
698     }
699     }
700     }
701     }
702 william 184 catch (SQLiteException ex)
703     {
704     gLog.Error.WriteLine(ex.ToString());
705     }
706     finally
707     {
708     con.Close();
709     }
710 william 159 }
711     this.Channels = channels;
712     }
713     catch (Exception ex)
714     {
715     gLog.Error.WriteLine(ex.ToString());
716     }
717     }
718 william 156 private void ReadProgrammeData()
719     {
720     try
721     {
722     List<IPROGRAMME> programs = new List<IPROGRAMME>();
723     using (SQLiteConnection con = CreateConnection())
724     {
725 william 184 try
726 william 156 {
727 william 184 con.Open();
728     string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
729     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
730     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
731 william 156 {
732 william 184 using (SQLiteDataReader r = cmd.ExecuteReader())
733 william 156 {
734 william 184 if (!r.HasRows)
735 william 156 {
736 william 184 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
737     }
738     else
739     {
740     while (r.Read())
741 william 156 {
742 william 184 PROGRAMME program = new PROGRAMME();
743     for (int i = 0; i < r.FieldCount; i++)
744     {
745     PROGRAMME.Create(ref program, r, i);
746     }
747     programs.Add(program);
748 william 156 }
749     }
750     }
751     }
752     }
753 william 184 catch (SQLiteException ex)
754     {
755     gLog.Error.WriteLine(ex.ToString());
756     }
757     finally
758     {
759     con.Close();
760     }
761 william 156 }
762     this.Programs = programs;
763     }
764     catch (Exception ex)
765     {
766     gLog.Error.WriteLine(ex.ToString());
767     }
768     }
769     private void ReadRecodringScheduleData()
770     {
771     try
772     {
773     List<IRECORDING_SCHEDULE> recordings = new List<IRECORDING_SCHEDULE>();
774     using (SQLiteConnection con = CreateConnection())
775     {
776 william 184 try
777 william 156 {
778 william 184 con.Open();
779     string command_text = string.Format("select * from {0};", TABLES.RECORDING_SCHEDULE);
780     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
781     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
782 william 156 {
783 william 184 using (SQLiteDataReader r = cmd.ExecuteReader())
784 william 156 {
785 william 184 if (!r.HasRows)
786 william 156 {
787 william 184 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
788     }
789     else
790     {
791     while (r.Read())
792 william 156 {
793 william 184 RECORDING_SCHEDULE recording = new RECORDING_SCHEDULE();
794     for (int i = 0; i < r.FieldCount; i++)
795     {
796     RECORDING_SCHEDULE.Create(ref recording, r, i);
797     }
798     recordings.Add(recording);
799 william 156 }
800     }
801     }
802     }
803     }
804 william 184 catch (SQLiteException ex)
805     {
806     gLog.Error.WriteLine(ex.ToString());
807     }
808     finally
809     {
810     con.Close();
811     }
812 william 156 }
813     this.Recordings = recordings;
814     }
815     catch (Exception ex)
816     {
817     gLog.Error.WriteLine(ex.ToString());
818     }
819     }
820 william 159
821 william 183 public void RemoveOldGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs)
822 william 190 {
823 william 187 gLog.Info.WriteLine("Removing all gbpvr programs.");
824 william 186 //double total = programs.Count;
825     //double index = 0;
826     //double progress = 0;
827     //foreach (var program in programs)
828     //{
829     // progress = 100.0 * (index / total);
830     // gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Removing GBPVR Programs: {0:00}%", (int)progress)));
831     // if (!RemoveProgramDatabaseEntry(program))
832     // {
833     // gLog.Error.WriteLine("Failed to remove program with oid: {0}", program.oid);
834     // }
835     // index++;
836     //}
837 william 190 Stopwatch st = new Stopwatch();
838     st.Start();
839     if (!RemoveAllProgramDatabaseEntries())
840     {
841     gLog.Error.WriteLine("Failed to remove one or more program(s)");
842     }
843     st.Stop();
844 william 191 gLog.Warn.WriteLine(" operation took: {0:0.00000} minutes", st.Elapsed.TotalMinutes);
845 william 183 }
846     public void UpdateGBPVRPrograms(List<IOldNewProgram> programs)
847     {
848 william 194 int STEP = 500;
849 william 193 List<List<IPROGRAMME>> array_list = new List<List<IPROGRAMME>>(); // holds a list of ~STEP programs per index
850     for (int i = 0; i < programs.Count;)
851 william 183 {
852 william 193 List<IPROGRAMME> tmp = new List<IPROGRAMME>(STEP);
853     for (int j = 0; j < STEP; j++)
854     {
855     try
856     {
857     tmp.Add(programs[i + j].NewProgram);
858     }
859     catch { }
860     }
861     tmp.TrimExcess();
862     array_list.Add(tmp);
863     i += STEP;
864 william 183 }
865 william 193 array_list.TrimExcess();
866    
867 william 194 int total = programs.Count;
868     int index = 0;
869     double progress = 0;
870 william 193 foreach (var p in array_list)
871 william 189 {
872 william 194 progress = 100.0 * (index / total);
873    
874     gLog.Info.WriteLine("Inserting {0} gbpvr programs.", p.Count);
875 william 193 Stopwatch st = new Stopwatch();
876     st.Start();
877     if (!InsertProgramEntryDatabase(p))
878     {
879     gLog.Error.WriteLine("Failed to insert one or more program(s)");
880     }
881     st.Stop();
882     gLog.Warn.WriteLine(" operation took: {0:0.00000} minutes", st.Elapsed.TotalMinutes);
883 william 194 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Inserting GBPVR Programs: {0:00}%", (int)progress)));
884     index += p.Count;
885 william 193 }
886    
887 william 183 }
888 william 174
889 william 186 private bool RemoveAllProgramDatabaseEntries()
890     {
891     bool result = false;
892     try
893     {
894     using (SQLiteConnection con = CreateConnection())
895     {
896     try
897     {
898     //gLog.Verbose.Info.WriteLine("Removing old program with oid: '{0}'", old_program.oid);
899     con.Open();
900     string command_text = string.Format(@"DELETE FROM [{0}];", TABLES.PROGRAMME);
901     //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
902     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
903     {
904     //cmd.Parameters.Add(new SQLiteParameter("oid", old_program.oid));
905     int rowsupdated = cmd.ExecuteNonQuery();
906     //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
907     }
908     result = true;
909     }
910     catch (SQLiteException ex)
911     {
912     gLog.Error.WriteLine(ex.ToString());
913     result = false;
914     }
915     finally
916     {
917     con.Close();
918     }
919     }
920     }
921     catch (Exception ex)
922     {
923     gLog.Error.WriteLine(ex.ToString());
924     result = false;
925     }
926     return result;
927     }
928 william 174 private bool RemoveProgramDatabaseEntry(IPROGRAMME old_program)
929     {
930     bool result = false;
931 william 183 try
932     {
933     using (SQLiteConnection con = CreateConnection())
934     {
935     try
936     {
937 william 185 //gLog.Verbose.Info.WriteLine("Removing old program with oid: '{0}'", old_program.oid);
938 william 183 con.Open();
939     string command_text = string.Format(@"DELETE FROM [{0}] WHERE [oid] = @oid;", TABLES.PROGRAMME);
940 william 185 //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
941 william 183 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
942     {
943     cmd.Parameters.Add(new SQLiteParameter("oid", old_program.oid));
944     int rowsupdated = cmd.ExecuteNonQuery();
945 william 185 //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
946 william 183 }
947     result = true;
948     }
949     catch (SQLiteException ex)
950     {
951     gLog.Error.WriteLine(ex.ToString());
952     result = false;
953     }
954 william 184 finally
955     {
956     con.Close();
957     }
958 william 183 }
959     }
960     catch (Exception ex)
961     {
962     gLog.Error.WriteLine(ex.ToString());
963     result = false;
964     }
965 william 174 return result;
966     }
967 william 189 private bool InsertProgramEntryDatabase(List<IPROGRAMME> list)
968 william 186 {
969     bool result = false;
970     try
971     {
972     using (SQLiteConnection con = CreateConnection())
973     {
974     try
975     {
976     //gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", new_program.oid);
977     con.Open();
978     //string command_text = string.Format(@"UPDATE [{0}] SET [name]=@name,[sub_title]=@subtitle, WHERE [OID] = @oid", TABLES.PROGRAMME);
979     string command_text = string.Empty;
980 william 189 command_text = BuildGBPVRMultiInsertCommand(list);
981 william 186 //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
982     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
983 william 189 {
984 william 186 int rowsupdated = cmd.ExecuteNonQuery();
985     //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
986     }
987    
988     result = true;
989     }
990     catch (SQLiteException ex)
991     {
992     gLog.Error.WriteLine(ex.ToString());
993     result = false;
994     }
995     finally
996     {
997     con.Close();
998     }
999     }
1000     }
1001     catch (Exception ex)
1002     {
1003     gLog.Error.WriteLine(ex.ToString());
1004     result = false;
1005     }
1006     return result;
1007     }
1008 william 189
1009     private string BuildGBPVRMultiInsertCommand(List<IPROGRAMME> list)
1010 william 174 {
1011 william 189 StringBuilder builder = new StringBuilder();
1012     /*
1013     insert into table1 (field1,field2) values (value1,value2);
1014     insert into table1 (field1,field2) values (value1,value2);
1015     insert into table1 (field1,field2) values (value1,value2);
1016     insert into table1 (field1,field2) values (value1,value2)
1017     */
1018 william 194 builder.AppendLine("begin transaction;");
1019 william 189 foreach (var t in list)
1020 william 183 {
1021 william 189 builder.AppendLine(BuildGBPVRSingleInsertCommand(t));
1022 william 183 }
1023 william 194 builder.AppendLine("end transaction;");
1024 william 189 return builder.ToString();
1025 william 174 }
1026 william 189
1027     private string BuildGBPVRSingleInsertCommand(IPROGRAMME program)
1028     {
1029     StringBuilder builder = new StringBuilder();
1030     builder.AppendFormat("insert into {0} (oid,name,sub_title,description,start_time,end_time,channel_oid,unique_identifier,rating) values (", TABLES.PROGRAMME);
1031     builder.AppendFormat("{0},",program.oid);
1032     builder.AppendFormat("\"{0}\",", program.name);
1033     builder.AppendFormat("\"{0}\",", program.sub_title);
1034     builder.AppendFormat("\"{0}\",", program.description);
1035     builder.AppendFormat("'{0}',", program.start_time);
1036     builder.AppendFormat("'{0}',", program.end_time);
1037     builder.AppendFormat("{0},", program.channel_oid);
1038     builder.AppendFormat("\"{0}\",", program.unique_identifier);
1039     builder.AppendFormat("\"{0}\");", program.rating);
1040     return builder.ToString();
1041     }
1042    
1043 william 156 }
1044     }

  ViewVC Help
Powered by ViewVC 1.1.22