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

Contents of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 244 - (show annotations) (download)
Sun Mar 17 10:48:34 2013 UTC (7 years, 3 months ago) by william
File size: 61317 byte(s)

1 //#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 using libxmltv;
10 using libxmltv.Interfaces;
11 using libxmltv.Core;
12 namespace GBPVRProgramDatabaseFixer
13 {
14 public interface ICloneable<T>
15 {
16 T Clone();
17 }
18 public interface IOldNewProgram
19 {
20 SQLLITE.IPROGRAMME OldProgram { get; }
21 SQLLITE.IPROGRAMME NewProgram { get; }
22 }
23 public interface ISQLLITE
24 {
25 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 List<IOldNewProgram> FixGBPVRProgramsDatabase(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> removed_programs);
30
31 void RemoveOldGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs);
32 void UpdateGBPVRPrograms(List<IOldNewProgram> programs);
33 void DeleteGBPVRScheduledRecordings(List<SQLLITE.IRECORDING_SCHEDULE> recordings);
34
35 }
36 public class SQLLITE : ISQLLITE
37 {
38 private class OldNewProgram : IOldNewProgram
39 {
40 public OldNewProgram() : this(new PROGRAMME()) { }
41 public OldNewProgram(IPROGRAMME _old) : this(_old,_old) { }
42 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 }
47 public static ISQLLITE Create(string database, EventHandler<EventArgs> OnInstanceCreated)
48 {
49 return new SQLLITE(database, OnInstanceCreated);
50 }
51
52 #region DATABASE DEFINITIONS
53 #region BaseDatabaseDefinition
54 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 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 public static void Create(ref T instance, SQLiteDataReader r, int index)
107 {
108 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
133 }
134 catch (Exception ex)
135 {
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 #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
167 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 {
193 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 }
209 private class RECORDING_SCHEDULE : IRECORDING_SCHEDULE, ICloneable<RECORDING_SCHEDULE>
210 {
211 public RECORDING_SCHEDULE()
212 {
213 BaseDatabaseDefinition<RECORDING_SCHEDULE>.CreateDefault(this);
214 }
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 BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(ref instance, r, index);
220 }
221 IRECORDING_SCHEDULE ICloneable<IRECORDING_SCHEDULE>.Clone()
222 {
223 return Clone();
224 }
225 public RECORDING_SCHEDULE Clone()
226 {
227 RECORDING_SCHEDULE p = new RECORDING_SCHEDULE();
228 BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, ref p);
229 return p;
230 }
231 #region IRECORDING_SCHEDULE members
232 public Int64 oid { get; set; }
233 public Int64 programme_oid { get; set; }
234 public Int64 capture_source_oid { get; set; }
235 public Int16 status { get; set; }
236 public String filename { get; set; }
237 public Int64 recording_type { get; set; }
238 public Int64 recording_group { get; set; }
239 public DateTime manual_start_time { get; set; }
240 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
249
250 }
251 public interface IPROGRAMME : IEquatable<IPROGRAMME>, ICloneable<IPROGRAMME>
252 {
253 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
263 string ToString();
264 IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite);
265 }
266 private class PROGRAMME : IPROGRAMME, ICloneable<PROGRAMME>
267 {
268
269 IPROGRAMME ICloneable<IPROGRAMME>.Clone()
270 {
271 return Clone();
272 }
273 public PROGRAMME Clone()
274 {
275 PROGRAMME p = new PROGRAMME();
276 BaseDatabaseDefinition<PROGRAMME>.Create(this, ref p);
277 return p;
278 }
279 public PROGRAMME()
280 {
281 BaseDatabaseDefinition<PROGRAMME>.CreateDefault(this);
282 }
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 public IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite)
299 {
300 ProgramList.ProgramDefintion definition = new ProgramList.ProgramDefintion();
301
302 var channel = sqllite.Channels.Find(s => s.oid == this.channel_oid);
303 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 definition.Description = this.description;
308 definition.Start = this.start_time.ToDateTimeString();
309 definition.Stop = this.end_time.ToDateTimeString();
310 definition.SubTitle = this.sub_title;
311 definition.Title = this.name;
312 definition.Rating = this.rating;
313 return definition;
314 }
315 #endregion
316
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
351
352
353
354
355
356
357
358 }
359 #endregion
360 private static class TABLES
361 {
362 public const string RECORDING_SCHEDULE = "RECORDING_SCHEDULE";
363 public const string PROGRAMME = "PROGRAMME";
364 public const string CHANNEL = "CHANNEL";
365 }
366 //private string DATABASE_BACKUP_FILE = string.Empty;
367 //public SQLLite() { }
368 protected SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated)
369 {
370 this.OnInstanceCreated = OnInstanceCreated;
371 //CreateConnection(database);
372 this.Database = database;
373 if (!CreateDatabaseBackup())
374 {
375 gLog.Error.WriteLine("Failed to backup database.");
376 return;
377 }
378 ConnectionTest();
379 ReadChannelData();
380 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
389 private void OnCreatedInstance(object sender, EventArgs e)
390 {
391 if (OnInstanceCreated != null) { OnInstanceCreated.Invoke(sender, e); }
392 }
393
394 private string Database;
395 #region ISQLLITE members
396 public List<IPROGRAMME> Programs { get; private set; }
397 public List<IRECORDING_SCHEDULE> Recordings { get; private set; }
398 public List<ICHANNEL> Channels { get; private set; }
399
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 first = list.OrderBy(s => s.start_time).ToList().First().start_time;
406 last = list.OrderBy(s => s.start_time).ToList().Last().start_time;
407 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 var range = DateTimeRange.Create(first, last);
410 return range;
411 }
412
413 public List<IOldNewProgram> FixGBPVRProgramsDatabase(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> source_invalid)
414 {
415 source_invalid = new List<IPROGRAMME>();
416 List<IOldNewProgram> source_valid = new List<IOldNewProgram>();
417 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
422 int programs_needing_update_count = 0;
423 double total = gbpvr_programs.Count;
424 double index = 0;
425 double progress = 0;
426 foreach (var program in gbpvr_programs)
427 {
428 progress = 100.0 * (index / total);
429 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Filtering GBPVR Programs: {0:00}%", (int)progress)));
430 // 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 if (xmltv_entry == null)
434 {
435 // xmltv entry was not found
436 source_invalid.Add(program);
437 }
438 else
439 {
440 // check if the xmltv entry has different data from the current program
441 if (!xmltv_entry.Equals(program_xmltv_entry))
442 {
443 bool update_needed = false;
444 // data is different
445 var updated_program = program.Clone();
446
447 if (program_xmltv_entry.Title != xmltv_entry.Title)
448 {
449 gLog.Verbose.Warn.WriteLine(" Program oid: '{0}' - replacing title", updated_program.oid);
450 updated_program.name = xmltv_entry.Title;
451 update_needed = true;
452 }
453 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 update_needed = true;
458 }
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 update_needed = true;
464 }
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 update_needed = true;
470 }
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 update_needed = true;
476 }
477
478 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 update_needed = true;
483 }
484
485 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 //source_valid.Add(new OldNewProgram(program, updated_program));
491 source_valid.Add(new OldNewProgram(program, updated_program));
492 if (update_needed)
493 {
494 programs_needing_update_count++;
495 }
496 }
497 else
498 {
499 // data is the same
500 //source_valid.Add(new OldNewProgram(program, program));
501 source_valid.Add(new OldNewProgram(program));
502 }
503 }
504 index++;
505 }
506 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 gLog.Info.WriteLine("GB-PVR Programs needing an update: 0x{0:x8}", programs_needing_update_count);
511 #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
675 return source_valid;
676 }
677 #endregion
678
679
680
681 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 string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
698 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 con.Clone();
724 }
725 OnCreatedInstance(this, new EventArgs());
726 }
727 catch (Exception ex)
728 {
729 gLog.Error.WriteLine(ex.ToString());
730 }
731 }
732
733
734 private bool CreateDatabaseBackup()
735 {
736 try
737 {
738 //string backup_file = string.Format("{0}.{1}", this.Database, DateTime.Now.ToString("yyyyMMddHHmmss"));
739 string backup_file = string.Format("{0}.bak", this.Database);
740 gLog.Info.WriteLine("Creating Database backup...");
741 gLog.Info.WriteLine("\tSource: {0}", this.Database);
742 gLog.Info.WriteLine("\tDestination: {0}", backup_file);
743
744 System.IO.File.Copy(this.Database, backup_file, true);
745 //DATABASE_BACKUP_FILE = backup_file;
746 return true;
747 }
748 catch (Exception ex)
749 {
750 gLog.Error.WriteLine(ex.ToString());
751 return false;
752 }
753 }
754 private void ReadChannelData()
755 {
756 try
757 {
758 List<ICHANNEL> channels = new List<ICHANNEL>();
759 using (SQLiteConnection con = CreateConnection())
760 {
761 try
762 {
763 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 {
768 using (SQLiteDataReader r = cmd.ExecuteReader())
769 {
770 if (!r.HasRows)
771 {
772 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
773 }
774 else
775 {
776 while (r.Read())
777 {
778 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 }
785 }
786 }
787 }
788 }
789 catch (SQLiteException ex)
790 {
791 gLog.Error.WriteLine(ex.ToString());
792 }
793 finally
794 {
795 con.Close();
796 }
797 }
798 this.Channels = channels;
799 }
800 catch (Exception ex)
801 {
802 gLog.Error.WriteLine(ex.ToString());
803 }
804 }
805 private void ReadProgrammeData()
806 {
807 try
808 {
809 List<IPROGRAMME> programs = new List<IPROGRAMME>();
810 using (SQLiteConnection con = CreateConnection())
811 {
812 try
813 {
814 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 {
819 using (SQLiteDataReader r = cmd.ExecuteReader())
820 {
821 if (!r.HasRows)
822 {
823 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
824 }
825 else
826 {
827 while (r.Read())
828 {
829 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 }
836 }
837 }
838 }
839 }
840 catch (SQLiteException ex)
841 {
842 gLog.Error.WriteLine(ex.ToString());
843 }
844 finally
845 {
846 con.Close();
847 }
848 }
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 try
864 {
865 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 {
870 using (SQLiteDataReader r = cmd.ExecuteReader())
871 {
872 if (!r.HasRows)
873 {
874 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
875 }
876 else
877 {
878 while (r.Read())
879 {
880 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 }
887 }
888 }
889 }
890 }
891 catch (SQLiteException ex)
892 {
893 gLog.Error.WriteLine(ex.ToString());
894 }
895 finally
896 {
897 con.Close();
898 }
899 }
900 this.Recordings = recordings;
901 }
902 catch (Exception ex)
903 {
904 gLog.Error.WriteLine(ex.ToString());
905 }
906 }
907
908 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 st1.Start();
917 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 st1.Stop();
928 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 public void RemoveOldGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs)
933 {
934 gLog.Info.WriteLine("Removing {0} gbpvr programs.", programs.Count);
935 int ChunkSize = 1024;
936 var ChunkList = programs.Chunk<IPROGRAMME>(ChunkSize);
937 double total = programs.Count;
938 double index = 0;
939 double progress = 0;
940 Stopwatch st1 = new Stopwatch();
941 st1.Start();
942 foreach (var p in ChunkList)
943 {
944 progress = 100.0 * (index / total);
945 if (!RemoveProgramDatabaseEntry(p))
946 {
947 gLog.Error.WriteLine("Failed to remove one or more program(s)");
948 }
949 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Removing old gbpvr programs ({0} of {1}) {2:00}%", index, total, (int)progress)));
950 index += (double)p.Count();
951 }
952 st1.Stop();
953 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Removing old gbpvr programs ({0} of {1}) {2:00}%", index, total, (int)progress)));
954 gLog.Warn.WriteLine(" operation took: {0:0.00000} seconds overall", st1.Elapsed.TotalSeconds);
955 }
956 public void UpdateGBPVRPrograms(List<IOldNewProgram> programs)
957 {
958 gLog.Info.WriteLine("Updating {0} gbpvr programs.", programs.Count);
959 int ChunkSize = 4096;
960 var ChunkList = programs.Chunk<IOldNewProgram>(ChunkSize);
961
962 double total = programs.Count;
963 double index = 0;
964 double progress = 0;
965 Stopwatch st1 = new Stopwatch();
966 st1.Start();
967 foreach (var p in ChunkList)
968 {
969 progress = 100.0 * (index / total);
970 if (!UpdateProgramEntryDatabase(p))
971 {
972 gLog.Error.WriteLine("Failed to update one or more program(s)");
973 }
974 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Updating gbpvr programs ({0} of {1}) {2:00}%", index, total, (int)progress)));
975 index += (double)p.Count();
976 }
977 st1.Stop();
978 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Updating gbpvr programs ({0} of {1}) {2:00}%", index, total, (int)progress)));
979 gLog.Warn.WriteLine(" operation took: {0:0.00000} seconds overall", st1.Elapsed.TotalSeconds);
980 }
981
982 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 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 private bool RemoveProgramDatabaseEntry(IEnumerable<IPROGRAMME> list)
1065 {
1066 bool result = false;
1067 try
1068 {
1069 using (SQLiteConnection con = CreateConnection())
1070 {
1071 try
1072 {
1073 //gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", new_program.oid);
1074 con.Open();
1075 //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 //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
1079 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
1080 {
1081 int rowsupdated = cmd.ExecuteNonQuery();
1082 //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
1083 }
1084
1085 result = true;
1086 }
1087 catch (SQLiteException ex)
1088 {
1089 gLog.Error.WriteLine(ex.ToString());
1090 result = false;
1091 }
1092 finally
1093 {
1094 con.Close();
1095 }
1096 }
1097 }
1098 catch (Exception ex)
1099 {
1100 gLog.Error.WriteLine(ex.ToString());
1101 result = false;
1102 }
1103 return result;
1104 }
1105 private bool UpdateProgramEntryDatabase(IEnumerable<IOldNewProgram> list)
1106 {
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 private bool InsertProgramEntryDatabase(IEnumerable<IPROGRAMME> list)
1147 {
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 command_text = BuildGBPVRMultiInsertCommand(list);
1160 //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
1161 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
1162 {
1163 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
1188
1189 #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 #region Multi-Update Command Support
1215 private string BuildGBPVRMultiUpdateCommand(IEnumerable<IOldNewProgram> list)
1216 {
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 if (!t.OldProgram.Equals(t.NewProgram))
1228 {
1229 // only update the entry if it is different
1230 builder.AppendLine(BuildGBPVRSingleUpdateCommand(t.NewProgram));
1231 }
1232 }
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 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 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 builder.AppendFormat("where oid={0};", program.oid);
1249 return builder.ToString();
1250 }
1251 #endregion
1252 #region Multi-Insert Command Support
1253 private string BuildGBPVRMultiInsertCommand(IEnumerable<IPROGRAMME> list)
1254 {
1255 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 builder.AppendLine("begin transaction;");
1263 foreach (var t in list)
1264 {
1265 builder.AppendLine(BuildGBPVRSingleInsertCommand(t));
1266 }
1267 builder.AppendLine("end transaction;");
1268 return builder.ToString();
1269 }
1270
1271 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 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 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 #endregion
1287
1288 }
1289 }

  ViewVC Help
Powered by ViewVC 1.1.22