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

Contents of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 233 - (show annotations) (download)
Sun Mar 17 06:53:55 2013 UTC (7 years, 4 months ago) by william
File size: 61176 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 gLog.Info.WriteLine("Creating Database backup...");
740 gLog.Info.WriteLine("\tSource: {0}", this.Database);
741 gLog.Info.WriteLine("\tDestination: {0}", backup_file);
742
743 System.IO.File.Copy(this.Database, backup_file);
744 DATABASE_BACKUP_FILE = backup_file;
745 return true;
746 }
747 catch (Exception ex)
748 {
749 gLog.Error.WriteLine(ex.ToString());
750 return false;
751 }
752 }
753 private void ReadChannelData()
754 {
755 try
756 {
757 List<ICHANNEL> channels = new List<ICHANNEL>();
758 using (SQLiteConnection con = CreateConnection())
759 {
760 try
761 {
762 con.Open();
763 string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
764 gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
765 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
766 {
767 using (SQLiteDataReader r = cmd.ExecuteReader())
768 {
769 if (!r.HasRows)
770 {
771 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
772 }
773 else
774 {
775 while (r.Read())
776 {
777 CHANNEL channel = new CHANNEL();
778 for (int i = 0; i < r.FieldCount; i++)
779 {
780 CHANNEL.Create(ref channel, r, i);
781 }
782 channels.Add(channel);
783 }
784 }
785 }
786 }
787 }
788 catch (SQLiteException ex)
789 {
790 gLog.Error.WriteLine(ex.ToString());
791 }
792 finally
793 {
794 con.Close();
795 }
796 }
797 this.Channels = channels;
798 }
799 catch (Exception ex)
800 {
801 gLog.Error.WriteLine(ex.ToString());
802 }
803 }
804 private void ReadProgrammeData()
805 {
806 try
807 {
808 List<IPROGRAMME> programs = new List<IPROGRAMME>();
809 using (SQLiteConnection con = CreateConnection())
810 {
811 try
812 {
813 con.Open();
814 string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
815 gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
816 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
817 {
818 using (SQLiteDataReader r = cmd.ExecuteReader())
819 {
820 if (!r.HasRows)
821 {
822 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
823 }
824 else
825 {
826 while (r.Read())
827 {
828 PROGRAMME program = new PROGRAMME();
829 for (int i = 0; i < r.FieldCount; i++)
830 {
831 PROGRAMME.Create(ref program, r, i);
832 }
833 programs.Add(program);
834 }
835 }
836 }
837 }
838 }
839 catch (SQLiteException ex)
840 {
841 gLog.Error.WriteLine(ex.ToString());
842 }
843 finally
844 {
845 con.Close();
846 }
847 }
848 this.Programs = programs;
849 }
850 catch (Exception ex)
851 {
852 gLog.Error.WriteLine(ex.ToString());
853 }
854 }
855 private void ReadRecodringScheduleData()
856 {
857 try
858 {
859 List<IRECORDING_SCHEDULE> recordings = new List<IRECORDING_SCHEDULE>();
860 using (SQLiteConnection con = CreateConnection())
861 {
862 try
863 {
864 con.Open();
865 string command_text = string.Format("select * from {0};", TABLES.RECORDING_SCHEDULE);
866 gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
867 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
868 {
869 using (SQLiteDataReader r = cmd.ExecuteReader())
870 {
871 if (!r.HasRows)
872 {
873 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
874 }
875 else
876 {
877 while (r.Read())
878 {
879 RECORDING_SCHEDULE recording = new RECORDING_SCHEDULE();
880 for (int i = 0; i < r.FieldCount; i++)
881 {
882 RECORDING_SCHEDULE.Create(ref recording, r, i);
883 }
884 recordings.Add(recording);
885 }
886 }
887 }
888 }
889 }
890 catch (SQLiteException ex)
891 {
892 gLog.Error.WriteLine(ex.ToString());
893 }
894 finally
895 {
896 con.Close();
897 }
898 }
899 this.Recordings = recordings;
900 }
901 catch (Exception ex)
902 {
903 gLog.Error.WriteLine(ex.ToString());
904 }
905 }
906
907 public void DeleteGBPVRScheduledRecordings(List<SQLLITE.IRECORDING_SCHEDULE> recordings)
908 {
909 gLog.Info.WriteLine("Removing {0} gbpvr scheduled recordings.", recordings.Count);
910
911 double total = recordings.Count;
912 double index = 0;
913 double progress = 0;
914 Stopwatch st1 = new Stopwatch();
915 foreach (var recording in recordings)
916 {
917 progress = 100.0 * (index / total);
918 if (!RemoveScheduledRecordingEntry(recording))
919 {
920 gLog.Error.WriteLine("Failed to remove one or more recordings(s)");
921 }
922 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Removing scheduled recordings ({0:00}%) oid='{1}'", (int)progress, recording.oid)));
923 index++;
924 }
925 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Removing scheduled recordings ({0:00}%)", (int)progress)));
926 st1.Stop();
927 gLog.Warn.WriteLine(" operation took: {0:0.00000} seconds overall", st1.Elapsed.TotalSeconds);
928
929 }
930 public void RemoveOldGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs)
931 {
932 gLog.Info.WriteLine("Removing {0} gbpvr programs.", programs.Count);
933 int ChunkSize = 1024;
934 var ChunkList = programs.Chunk<IPROGRAMME>(ChunkSize);
935 double total = programs.Count;
936 double index = 0;
937 double progress = 0;
938 Stopwatch st1 = new Stopwatch();
939 st1.Start();
940 foreach (var p in ChunkList)
941 {
942 progress = 100.0 * (index / total);
943 if (!RemoveProgramDatabaseEntry(p))
944 {
945 gLog.Error.WriteLine("Failed to remove one or more program(s)");
946 }
947 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Removing old gbpvr programs ({0} of {1}) {2:00}%", index, total, (int)progress)));
948 index += (double)p.Count();
949 }
950 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Removing old gbpvr programs ({0} of {1}) {2:00}%", index, total, (int)progress)));
951 st1.Stop();
952 gLog.Warn.WriteLine(" operation took: {0:0.00000} seconds overall", st1.Elapsed.TotalSeconds);
953 }
954 public void UpdateGBPVRPrograms(List<IOldNewProgram> programs)
955 {
956 gLog.Info.WriteLine("Updating {0} gbpvr programs.", programs.Count);
957 int ChunkSize = 4096;
958 var ChunkList = programs.Chunk<IOldNewProgram>(ChunkSize);
959
960 double total = programs.Count;
961 double index = 0;
962 double progress = 0;
963 Stopwatch st1 = new Stopwatch();
964 st1.Start();
965 foreach (var p in ChunkList)
966 {
967 progress = 100.0 * (index / total);
968 if (!UpdateProgramEntryDatabase(p))
969 {
970 gLog.Error.WriteLine("Failed to update one or more program(s)");
971 }
972 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Updating gbpvr programs ({0} of {1}) {2:00}%", index, total, (int)progress)));
973 index += (double)p.Count();
974 }
975 gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Updating gbpvr programs ({0} of {1}) {2:00}%", index, total, (int)progress)));
976 st1.Stop();
977 gLog.Warn.WriteLine(" operation took: {0:0.00000} seconds overall", st1.Elapsed.TotalSeconds);
978 }
979
980 private bool RemoveAllProgramDatabaseEntries()
981 {
982 bool result = false;
983 try
984 {
985 using (SQLiteConnection con = CreateConnection())
986 {
987 try
988 {
989 //gLog.Verbose.Info.WriteLine("Removing old program with oid: '{0}'", old_program.oid);
990 con.Open();
991 string command_text = string.Format(@"DELETE FROM [{0}];", TABLES.PROGRAMME);
992 //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
993 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
994 {
995 //cmd.Parameters.Add(new SQLiteParameter("oid", old_program.oid));
996 int rowsupdated = cmd.ExecuteNonQuery();
997 //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
998 }
999 result = true;
1000 }
1001 catch (SQLiteException ex)
1002 {
1003 gLog.Error.WriteLine(ex.ToString());
1004 result = false;
1005 }
1006 finally
1007 {
1008 con.Close();
1009 }
1010 }
1011 }
1012 catch (Exception ex)
1013 {
1014 gLog.Error.WriteLine(ex.ToString());
1015 result = false;
1016 }
1017 return result;
1018 }
1019 private bool RemoveScheduledRecordingEntry(IRECORDING_SCHEDULE recording)
1020 {
1021 bool result = false;
1022 try
1023 {
1024 using (SQLiteConnection con = CreateConnection())
1025 {
1026 try
1027 {
1028 //gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", new_program.oid);
1029 con.Open();
1030 //string command_text = string.Format(@"UPDATE [{0}] SET [name]=@name,[sub_title]=@subtitle, WHERE [OID] = @oid", TABLES.PROGRAMME);
1031 StringBuilder builder = new StringBuilder();
1032 builder.AppendLine("begin transaction;");
1033 builder.AppendFormat("delete from {0} where oid={1};", TABLES.RECORDING_SCHEDULE, recording.oid);
1034 builder.AppendLine("end transaction;");
1035 //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
1036 using (SQLiteCommand cmd = new SQLiteCommand(builder.ToString(), con))
1037 {
1038 int rowsupdated = cmd.ExecuteNonQuery();
1039 //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
1040 }
1041
1042 result = true;
1043 }
1044 catch (SQLiteException ex)
1045 {
1046 gLog.Error.WriteLine(ex.ToString());
1047 result = false;
1048 }
1049 finally
1050 {
1051 con.Close();
1052 }
1053 }
1054 }
1055 catch (Exception ex)
1056 {
1057 gLog.Error.WriteLine(ex.ToString());
1058 result = false;
1059 }
1060 return result;
1061 }
1062 private bool RemoveProgramDatabaseEntry(IEnumerable<IPROGRAMME> list)
1063 {
1064 bool result = false;
1065 try
1066 {
1067 using (SQLiteConnection con = CreateConnection())
1068 {
1069 try
1070 {
1071 //gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", new_program.oid);
1072 con.Open();
1073 //string command_text = string.Format(@"UPDATE [{0}] SET [name]=@name,[sub_title]=@subtitle, WHERE [OID] = @oid", TABLES.PROGRAMME);
1074 string command_text = string.Empty;
1075 command_text = BuildGBPVRMultiDeleteCommand(list);
1076 //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
1077 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
1078 {
1079 int rowsupdated = cmd.ExecuteNonQuery();
1080 //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
1081 }
1082
1083 result = true;
1084 }
1085 catch (SQLiteException ex)
1086 {
1087 gLog.Error.WriteLine(ex.ToString());
1088 result = false;
1089 }
1090 finally
1091 {
1092 con.Close();
1093 }
1094 }
1095 }
1096 catch (Exception ex)
1097 {
1098 gLog.Error.WriteLine(ex.ToString());
1099 result = false;
1100 }
1101 return result;
1102 }
1103 private bool UpdateProgramEntryDatabase(IEnumerable<IOldNewProgram> list)
1104 {
1105 bool result = false;
1106 try
1107 {
1108 using (SQLiteConnection con = CreateConnection())
1109 {
1110 try
1111 {
1112 //gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", new_program.oid);
1113 con.Open();
1114 //string command_text = string.Format(@"UPDATE [{0}] SET [name]=@name,[sub_title]=@subtitle, WHERE [OID] = @oid", TABLES.PROGRAMME);
1115 string command_text = string.Empty;
1116 command_text = BuildGBPVRMultiUpdateCommand(list);
1117 //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
1118 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
1119 {
1120 int rowsupdated = cmd.ExecuteNonQuery();
1121 //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
1122 }
1123
1124 result = true;
1125 }
1126 catch (SQLiteException ex)
1127 {
1128 gLog.Error.WriteLine(ex.ToString());
1129 result = false;
1130 }
1131 finally
1132 {
1133 con.Close();
1134 }
1135 }
1136 }
1137 catch (Exception ex)
1138 {
1139 gLog.Error.WriteLine(ex.ToString());
1140 result = false;
1141 }
1142 return result;
1143 }
1144 private bool InsertProgramEntryDatabase(IEnumerable<IPROGRAMME> list)
1145 {
1146 bool result = false;
1147 try
1148 {
1149 using (SQLiteConnection con = CreateConnection())
1150 {
1151 try
1152 {
1153 //gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", new_program.oid);
1154 con.Open();
1155 //string command_text = string.Format(@"UPDATE [{0}] SET [name]=@name,[sub_title]=@subtitle, WHERE [OID] = @oid", TABLES.PROGRAMME);
1156 string command_text = string.Empty;
1157 command_text = BuildGBPVRMultiInsertCommand(list);
1158 //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
1159 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
1160 {
1161 int rowsupdated = cmd.ExecuteNonQuery();
1162 //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
1163 }
1164
1165 result = true;
1166 }
1167 catch (SQLiteException ex)
1168 {
1169 gLog.Error.WriteLine(ex.ToString());
1170 result = false;
1171 }
1172 finally
1173 {
1174 con.Close();
1175 }
1176 }
1177 }
1178 catch (Exception ex)
1179 {
1180 gLog.Error.WriteLine(ex.ToString());
1181 result = false;
1182 }
1183 return result;
1184 }
1185
1186
1187 #region Multi-Delete Command Support
1188 private string BuildGBPVRMultiDeleteCommand(IEnumerable<IPROGRAMME> list)
1189 {
1190 StringBuilder builder = new StringBuilder();
1191 /*
1192 insert into table1 (field1,field2) values (value1,value2);
1193 insert into table1 (field1,field2) values (value1,value2);
1194 insert into table1 (field1,field2) values (value1,value2);
1195 insert into table1 (field1,field2) values (value1,value2)
1196 */
1197 builder.AppendLine("begin transaction;");
1198 foreach (var t in list)
1199 {
1200 builder.AppendLine(BuildGBPVRSingleDeleteCommand(t));
1201 }
1202 builder.AppendLine("end transaction;");
1203 return builder.ToString();
1204 }
1205 private string BuildGBPVRSingleDeleteCommand(IPROGRAMME program)
1206 {
1207 StringBuilder builder = new StringBuilder();
1208 builder.AppendFormat("delete from {0} where oid={1};", TABLES.PROGRAMME, program.oid);
1209 return builder.ToString();
1210 }
1211 #endregion
1212 #region Multi-Update Command Support
1213 private string BuildGBPVRMultiUpdateCommand(IEnumerable<IOldNewProgram> list)
1214 {
1215 StringBuilder builder = new StringBuilder();
1216 /*
1217 insert into table1 (field1,field2) values (value1,value2);
1218 insert into table1 (field1,field2) values (value1,value2);
1219 insert into table1 (field1,field2) values (value1,value2);
1220 insert into table1 (field1,field2) values (value1,value2)
1221 */
1222 builder.AppendLine("begin transaction;");
1223 foreach (var t in list)
1224 {
1225 if (!t.OldProgram.Equals(t.NewProgram))
1226 {
1227 // only update the entry if it is different
1228 builder.AppendLine(BuildGBPVRSingleUpdateCommand(t.NewProgram));
1229 }
1230 }
1231 builder.AppendLine("end transaction;");
1232 return builder.ToString();
1233 }
1234 private string BuildGBPVRSingleUpdateCommand(IPROGRAMME program)
1235 {
1236 StringBuilder builder = new StringBuilder();
1237 builder.AppendFormat("update {0} SET ", TABLES.PROGRAMME);
1238 builder.AppendFormat("name=\"{0}\", ", program.name);
1239 builder.AppendFormat("sub_title=\"{0}\", ", program.sub_title);
1240 builder.AppendFormat("description=\"{0}\", ", program.description);
1241 builder.AppendFormat("start_time='{0}', ", program.start_time.ToString("yyyy-MM-dd HH:mm:ss.fffffff"));
1242 builder.AppendFormat("end_time='{0}', ", program.end_time.ToString("yyyy-MM-dd HH:mm:ss.fffffff"));
1243 builder.AppendFormat("channel_oid={0}, ", program.channel_oid);
1244 builder.AppendFormat("unique_identifier=\"{0}\", ", program.unique_identifier);
1245 builder.AppendFormat("rating=\"{0}\" ", program.rating);
1246 builder.AppendFormat("where oid={0};", program.oid);
1247 return builder.ToString();
1248 }
1249 #endregion
1250 #region Multi-Insert Command Support
1251 private string BuildGBPVRMultiInsertCommand(IEnumerable<IPROGRAMME> list)
1252 {
1253 StringBuilder builder = new StringBuilder();
1254 /*
1255 insert into table1 (field1,field2) values (value1,value2);
1256 insert into table1 (field1,field2) values (value1,value2);
1257 insert into table1 (field1,field2) values (value1,value2);
1258 insert into table1 (field1,field2) values (value1,value2)
1259 */
1260 builder.AppendLine("begin transaction;");
1261 foreach (var t in list)
1262 {
1263 builder.AppendLine(BuildGBPVRSingleInsertCommand(t));
1264 }
1265 builder.AppendLine("end transaction;");
1266 return builder.ToString();
1267 }
1268
1269 private string BuildGBPVRSingleInsertCommand(IPROGRAMME program)
1270 {
1271 StringBuilder builder = new StringBuilder();
1272 builder.AppendFormat("insert into {0} (oid,name,sub_title,description,start_time,end_time,channel_oid,unique_identifier,rating) values (", TABLES.PROGRAMME);
1273 builder.AppendFormat("{0},",program.oid);
1274 builder.AppendFormat("\"{0}\",", program.name);
1275 builder.AppendFormat("\"{0}\",", program.sub_title);
1276 builder.AppendFormat("\"{0}\",", program.description);
1277 builder.AppendFormat("'{0}',", program.start_time.ToString("yyyy-MM-dd HH:mm:ss.fffffff"));
1278 builder.AppendFormat("'{0}',", program.end_time.ToString("yyyy-MM-dd HH:mm:ss.fffffff"));
1279 builder.AppendFormat("{0},", program.channel_oid);
1280 builder.AppendFormat("\"{0}\",", program.unique_identifier);
1281 builder.AppendFormat("\"{0}\");", program.rating);
1282 return builder.ToString();
1283 }
1284 #endregion
1285
1286 }
1287 }

  ViewVC Help
Powered by ViewVC 1.1.22