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

Annotation of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.22