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

Annotation of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.22