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

Annotation of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.22