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

Annotation of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 179 - (hide annotations) (download)
Sat Mar 16 17:13:23 2013 UTC (7 years, 4 months ago) by william
File size: 36752 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 163 public interface ISQLLITE
15 william 156 {
16 william 163 List<SQLLITE.IPROGRAMME> Programs { get; }
17     List<SQLLITE.IRECORDING_SCHEDULE> Recordings { get; }
18     List<SQLLITE.ICHANNEL> Channels { get; }
19     IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs);
20 william 174 List<SQLLITE.IPROGRAMME> FixGBPVRProgramsDatabase(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> removed_programs);
21 william 163
22     }
23     public class SQLLITE : ISQLLITE
24     {
25 william 176 private struct OldNewProgram
26     {
27     public IPROGRAMME OldProgram;
28     public IPROGRAMME NewProgram;
29     }
30 william 163 public static ISQLLITE Create(string database, EventHandler<EventArgs> OnInstanceCreated)
31     {
32     return new SQLLITE(database, OnInstanceCreated);
33     }
34    
35 william 156 #region DATABASE DEFINITIONS
36 william 159
37     public interface ICHANNEL
38     {
39     Int64 oid { get; }
40     String name { get; }
41     String channelID { get; }
42     Int64 channel_number { get; }
43     String favourite_channel { get; }
44     String display_name { get; }
45     }
46     private class CHANNEL : ICHANNEL
47     {
48     public CHANNEL()
49     {
50     BaseDatabaseDefinition<CHANNEL>.CreateDefault(this);
51     }
52     //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }
53    
54     public static void Create(ref CHANNEL instance, SQLiteDataReader r, int index)
55     {
56     BaseDatabaseDefinition<CHANNEL>.Create(ref instance, r, index);
57     }
58     #region ICHANNEL members
59     public Int64 oid { get; set; }
60     public String name { get; set; }
61     public String channelID { get; set; }
62     public Int64 channel_number { get; set; }
63     public String favourite_channel { get; set; }
64     public String display_name { get; set; }
65     #endregion
66     }
67 william 156 public interface IRECORDING_SCHEDULE
68     {
69     Int64 oid { get; }
70     Int64 programme_oid { get; }
71     Int64 capture_source_oid { get; }
72     Int16 status { get; }
73     String filename { get; }
74     Int64 recording_type { get; }
75     Int64 recording_group { get; }
76     DateTime manual_start_time { get; }
77     DateTime manual_end_time { get; }
78     Int64 manual_channel_oid { get; }
79     Int64 quality_level { get; }
80     Int64 pre_pad_minutes { get; }
81     Int64 post_pad_minutes { get;}
82     Int32 priority { get; }
83     String conversion_profile { get; }
84     }
85    
86     private static class BaseDatabaseDefinition<T>
87     {
88     public static void CreateDefault(T instance)
89     {
90     try
91     {
92     Type t = typeof(T);
93     var props = t.GetProperties();
94     foreach (var prop in props)
95     {
96     Type prop_type = prop.PropertyType;
97     object field_value = null;
98     try
99     {
100     if (prop_type == typeof(string))
101     {
102     field_value = string.Empty;
103     }
104     else
105     {
106     field_value = Activator.CreateInstance(prop_type);
107     }
108     }
109     catch (Exception ex)
110     {
111     throw ex;
112     }
113     prop.SetValue(instance, field_value, null);
114     }
115     }
116     catch (Exception ex)
117     {
118     throw ex;
119     }
120     }
121     public static void Create(ref T instance, SQLiteDataReader r, int index)
122     {
123     string field_name = r.GetName(index);
124     Type field_type = r.GetFieldType(index);
125     object field_value = r.GetValue(index);
126     //gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
127    
128     Type t = typeof(T);
129     var props = t.GetProperties();
130     foreach (var prop in props)
131     {
132     if (prop.Name.ToLower() == field_name.ToLower())
133     {
134     if (prop.PropertyType == field_type)
135     {
136     Type db_type = field_value.GetType();
137     try
138     {
139     if (db_type == typeof(System.DBNull))
140     {
141     prop.SetValue(instance, null, null);
142     }
143     else
144     {
145     prop.SetValue(instance, field_value, null);
146     }
147    
148     }
149     catch (Exception ex)
150     {
151     throw ex;
152     }
153     }
154     else
155     {
156     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);
157     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));
158     }
159     }
160     }
161    
162     }
163     }
164    
165     private class RECORDING_SCHEDULE : IRECORDING_SCHEDULE
166     {
167     public RECORDING_SCHEDULE()
168     {
169     BaseDatabaseDefinition<RECORDING_SCHEDULE>.CreateDefault(this);
170     }
171     //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }
172    
173     public static void Create(ref RECORDING_SCHEDULE instance, SQLiteDataReader r, int index)
174     {
175     BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(ref instance, r, index);
176     }
177    
178     #region IRECORDING_SCHEDULE members
179     public Int64 oid { get; set;}
180     public Int64 programme_oid { get; set;}
181     public Int64 capture_source_oid { get; set;}
182     public Int16 status { get; set; }
183     public String filename { get; set; }
184     public Int64 recording_type { get;set; }
185     public Int64 recording_group { get; set;}
186     public DateTime manual_start_time { get; set;}
187     public DateTime manual_end_time { get; set; }
188     public Int64 manual_channel_oid { get; set; }
189     public Int64 quality_level { get; set; }
190     public Int64 pre_pad_minutes { get; set; }
191     public Int64 post_pad_minutes { get; set; }
192     public Int32 priority { get; set; }
193     public String conversion_profile { get; set; }
194     #endregion
195     }
196    
197     public interface IPROGRAMME
198     {
199 william 168 Int64 oid { get; set; }
200     String name { get; set; }
201     String sub_title { get; set; }
202     String description { get; set; }
203     DateTime start_time { get; set; }
204     DateTime end_time { get; set; }
205     Int64 channel_oid { get; set; }
206     String unique_identifier { get; set; }
207     String rating { get; set; }
208 william 165
209     IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite);
210 william 156 }
211     private class PROGRAMME : IPROGRAMME
212     {
213     public PROGRAMME()
214     {
215     BaseDatabaseDefinition<PROGRAMME>.CreateDefault(this);
216     }
217     //public PROGRAMME(SQLiteDataReader r, int index) : base(r, index) { }
218     public static void Create(ref PROGRAMME instance, SQLiteDataReader r, int index)
219     {
220     BaseDatabaseDefinition<PROGRAMME>.Create(ref instance, r, index);
221     }
222     #region IPROGRAMME members
223     public Int64 oid { get; set; }
224     public String name { get; set; }
225     public String sub_title { get; set; }
226     public String description { get; set; }
227     public DateTime start_time { get; set; }
228     public DateTime end_time { get; set; }
229     public Int64 channel_oid { get; set; }
230     public String unique_identifier { get; set; }
231     public String rating { get; set; }
232 william 165 public IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite)
233     {
234     ProgramList.ProgramDefintion definition = new ProgramList.ProgramDefintion();
235     var channel = sqllite.Channels.Find(s => s.oid == this.channel_oid);
236     definition.ChannelName = channel.display_name;
237     definition.ChannelNumber = (int)channel.channel_number;
238     definition.Description = this.description;
239 william 168 definition.Start = this.start_time.ToDateTimeString();
240     definition.Stop = this.end_time.ToDateTimeString();
241 william 165 definition.SubTitle = this.sub_title;
242     definition.Title = this.name;
243     return definition;
244     }
245 william 156 #endregion
246     }
247     #endregion
248     private static class TABLES
249     {
250     public const string RECORDING_SCHEDULE = "RECORDING_SCHEDULE";
251     public const string PROGRAMME = "PROGRAMME";
252 william 159 public const string CHANNEL = "CHANNEL";
253 william 156 }
254    
255     //public SQLLite() { }
256 william 163 protected SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated)
257 william 156 {
258     this.OnInstanceCreated = OnInstanceCreated;
259     //CreateConnection(database);
260     this.Database = database;
261 william 157 if (!CreateDatabaseBackup())
262     {
263     gLog.Error.WriteLine("Failed to backup database.");
264     return;
265     }
266 william 156 ConnectionTest();
267 william 159 ReadChannelData();
268 william 156 ReadRecodringScheduleData();
269     ReadProgrammeData();
270     OnCreatedInstance(this, new EventArgs());
271     }
272    
273     [NonSerialized]
274     private EventHandler<EventArgs> _OnInstanceCreated;
275     private EventHandler<EventArgs> OnInstanceCreated { get { return _OnInstanceCreated; } set { _OnInstanceCreated = value; } }
276    
277     private void OnCreatedInstance(object sender, EventArgs e)
278     {
279     if (OnInstanceCreated != null) { OnInstanceCreated.Invoke(sender, e); }
280     }
281    
282     private string Database;
283 william 163 #region ISQLLITE members
284 william 156 public List<IPROGRAMME> Programs { get; private set; }
285     public List<IRECORDING_SCHEDULE> Recordings { get; private set; }
286 william 159 public List<ICHANNEL> Channels { get; private set; }
287 william 163
288     public IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs)
289     {
290     var list = new List<IPROGRAMME>(programs.ToArray());
291     DateTime first = new DateTime();
292     DateTime last = new DateTime();
293     first = list.OrderBy(s => s.start_time).ToList().First().start_time;
294     last = list.OrderBy(s => s.start_time).ToList().Last().start_time;
295 william 167 gLog.Verbose.Debug.WriteLine("\tFirst: {0} = ({1})", first.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), first.ToDateTimeString());
296     gLog.Verbose.Debug.WriteLine("\tLast: {0} = ({1})", last.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), last.ToDateTimeString());
297 william 163 var range = DateTimeRange.Create(first, last);
298     return range;
299     }
300    
301 william 174 public List<SQLLITE.IPROGRAMME> FixGBPVRProgramsDatabase(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> source_invalid)
302 william 163 {
303 william 169 source_invalid = new List<IPROGRAMME>();
304 william 168 gbpvr_programs = gbpvr_programs.OrderBy(s => s.start_time).ToList();
305 william 166 List<SQLLITE.IPROGRAMME> source_valid = new List<IPROGRAMME>();
306 william 176
307     List<OldNewProgram> source_update = new List<OldNewProgram>();
308 william 166 //if (range == null)
309     //{
310     // gLog.Warn.WriteLine("The DateTimeRange passed in is null...returning the original program list");
311     // return list;
312     //}
313 william 163 //gLog.Warn.WriteLine("FilterProgramsByDateRange has not been implemented");
314 william 170
315 william 168 double total = gbpvr_programs.Count;
316 william 166 double index = 0;
317     double progress = 0;
318 william 168 foreach (var program in gbpvr_programs)
319 william 169 {
320     progress = 100.0 * (index / total);
321     gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Filtering GBPVR Programs: {0:00}%", (int)progress)));
322 william 168 var channel_oid = program.channel_oid;
323     var channel = this.Channels.Find(s => s.oid == channel_oid);
324     var start_date = program.start_time;
325 william 172 //var xmltv_entry_list = xmltv_programs.FindAll(s => s.ChannelNumber == channel.channel_number && s.Start == start_date.ToDateTimeString());
326     var query = from c in xmltv_programs
327     where
328     c.ChannelNumber == channel.channel_number &&
329     c.Start == start_date.ToDateTimeString()
330     select c;
331    
332 william 170 IProgramDefinition xmltv_entry = null;
333 william 172 if (query.Count()-1 > 0)
334 william 170 {
335 william 178 gLog.Verbose.Error.WriteLine("Found more than one entry: Matching channel='{0}' and start='{1}'", channel.channel_number, start_date.ToDateTimeString());
336     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);
337 william 170 if (Debugger.IsAttached)
338 william 172 {
339     gLog.Error.WriteLine(" Found: {0} matching entries", query.Count());
340     int k_index = 0;
341     foreach (var k in query)
342     {
343 william 178 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);
344 william 172 k_index++;
345     }
346 william 170 Debugger.Break();
347     }
348     }
349     else
350     {
351 william 172 xmltv_entry = query.FirstOrDefault();
352 william 170 }
353    
354 william 168 if (xmltv_entry == null)
355 william 163 {
356 william 179 //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());
357 william 176 source_invalid.Add(program);
358 william 163 }
359 william 164 else
360     {
361 william 179 //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());
362 william 168 var updated_program = program;
363 william 174 bool needsupdate = false;
364 william 173 if (xmltv_entry.Title != program.name)
365 william 172 {
366 william 179 //gLog.Verbose.Warn.WriteLine(" Updating:");
367     //gLog.Verbose.Warn.WriteLine(" Old Title: {0}", program.name);
368     //gLog.Verbose.Warn.WriteLine(" New Title: {0}", xmltv_entry.Title);
369 william 173 updated_program.name = xmltv_entry.Title;
370 william 174 needsupdate = true;
371 william 172 }
372 william 173 if (xmltv_entry.SubTitle != program.sub_title)
373 william 172 {
374 william 179 //gLog.Verbose.Warn.WriteLine(" Updating:");
375     //gLog.Verbose.Warn.WriteLine(" Old SubTile: {0}", program.sub_title);
376     //gLog.Verbose.Warn.WriteLine(" New SubTile: {0}", xmltv_entry.SubTitle);
377 william 173 updated_program.sub_title = xmltv_entry.SubTitle;
378 william 174 needsupdate = true;
379 william 172 }
380 william 173 if (xmltv_entry.Description != program.description)
381 william 172 {
382 william 179 //gLog.Verbose.Warn.WriteLine(" Updating:");
383     //gLog.Verbose.Warn.WriteLine(" Old Descption: {0}", program.description);
384     //gLog.Verbose.Warn.WriteLine(" New Descption: {0}", xmltv_entry.Description);
385 william 174 updated_program.description = xmltv_entry.Description;
386     needsupdate = true;
387 william 172 }
388     if (DateTime.Parse(xmltv_entry.Start) != program.start_time)
389     {
390 william 179 //gLog.Verbose.Warn.WriteLine(" Updating:");
391     //gLog.Verbose.Warn.WriteLine(" Old StartTime: {0}", program.start_time.ToDateTimeString());
392     //gLog.Verbose.Warn.WriteLine(" New StartTime: {0}", DateTime.Parse(xmltv_entry.Start).ToDateTimeString());
393 william 173 updated_program.start_time = DateTime.Parse(xmltv_entry.Start);
394 william 174 needsupdate = true;
395 william 172 }
396 william 173 if (DateTime.Parse(xmltv_entry.Stop) != program.end_time)
397 william 172 {
398 william 179 //gLog.Verbose.Warn.WriteLine(" Updating:");
399     //gLog.Verbose.Warn.WriteLine(" Old EndTime: {0}", program.end_time.ToDateTimeString());
400     //gLog.Verbose.Warn.WriteLine(" New EndTime: {0}", DateTime.Parse(xmltv_entry.Stop).ToDateTimeString());
401 william 173 updated_program.end_time = DateTime.Parse(xmltv_entry.Stop);
402 william 174 needsupdate = true;
403 william 172 }
404 william 177
405 william 174 if (needsupdate)
406     {
407 william 176 OldNewProgram p = new OldNewProgram();
408     p.OldProgram = program;
409     p.NewProgram = updated_program;
410     source_update.Add(p);
411 william 174 }
412 william 177 source_valid.Add(updated_program);
413 william 164 }
414 william 169 index++;
415 william 163 }
416 william 166
417    
418     source_valid = source_valid.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
419 william 169 source_invalid = source_invalid.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
420 william 167
421 william 176 if (source_invalid.Count > 0)
422     {
423 william 177 double source_invalid_count = source_valid.Count;
424     double source_invalid_index = 0;
425     double source_invalid_progress = 0;
426 william 176 foreach (var old_program in source_invalid)
427     {
428 william 177 source_invalid_progress = 100.0 * (source_invalid_index / source_invalid_count);
429 william 178 gLog.ReportProgress(this, new ReportProgressEventArgs((int)source_invalid_progress, string.Format("Removing old GBPVR Programs: {0:00}%", (int)source_invalid_progress)));
430 william 176 // remove database entry
431     if (!RemoveProgramDatabaseEntry(old_program))
432     {
433     gLog.Error.WriteLine("Failed to remove program with oid: '{0}'", old_program.oid);
434     }
435     else
436     {
437 william 177 gLog.Verbose.Info.WriteLine("Removed program with oid: '{0}'", old_program.oid);
438 william 176 }
439 william 177 source_invalid_index++;
440 william 176 }
441     }
442     else
443     {
444     gLog.Info.WriteLine("No old GB-PVR Programs needed to be removed.");
445     }
446    
447     if (source_update.Count > 0)
448     {
449 william 177 double source_update_count = source_valid.Count;
450     double source_update_index = 0;
451     double source_update_progress = 0;
452 william 176 foreach (var p in source_update)
453     {
454 william 177 source_update_progress = 100.0 * (source_update_index / source_update_count);
455 william 178 gLog.ReportProgress(this, new ReportProgressEventArgs((int)source_update_progress, string.Format("Updating GBPVR Programs: {0:00}%", (int)source_update_progress)));
456 william 176 // remove database entry
457     if (!UpdateProgramDatabaseEntry(p.OldProgram,p.NewProgram))
458     {
459     gLog.Error.WriteLine("Failed to update program with oid: '{0}'", p.OldProgram.oid);
460     }
461     else
462     {
463 william 177 gLog.Verbose.Info.WriteLine("Upated program with oid: '{0}'", p.OldProgram.oid);
464 william 176 }
465 william 177 source_update_index++;
466 william 176 }
467     }
468     else
469     {
470     gLog.Info.WriteLine("No GB-PVR Programs needed to be updated.");
471     }
472    
473 william 167 gLog.Info.WriteLine("Total XMLTV Programs: 0x{0:x8}", xmltv_programs.Count);
474 william 168 gLog.Info.WriteLine("Updated: 0x{0:x8} GB-PVR Programs", source_valid.Count);
475 william 169 gLog.Info.WriteLine("Removed: 0x{0:x8} GB-PVR Programs", source_invalid.Count);
476     gLog.Info.WriteLine("Total GB-PVR Programs (Updated & Removed): 0x{0:x8}", source_valid.Count + source_invalid.Count);
477 william 167
478 william 166 return source_valid;
479 william 163 }
480 william 156 #endregion
481    
482    
483     private string CreateConnectionString()
484     {
485     string connection_string = string.Format("Data Source={0}", this.Database);
486     return connection_string;
487     }
488    
489     private SQLiteConnection CreateConnection() { SQLiteConnection connection = new SQLiteConnection(CreateConnectionString()); return connection; }
490    
491     [Conditional("SQLLITE_CONNECTION_TEST")]
492     private void ConnectionTest()
493     {
494     try
495     {
496     using (SQLiteConnection con = CreateConnection())
497     {
498     con.Open();
499 william 159 string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
500 william 156 gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
501     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
502     {
503     using (SQLiteDataReader r = cmd.ExecuteReader())
504     {
505     if (!r.HasRows)
506     {
507     gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
508     }
509     else
510     {
511     while (r.Read())
512     {
513     for (int i = 0; i < r.FieldCount; i++)
514     {
515     string field_name = r.GetName(i);
516     Type field_type = r.GetFieldType(i);
517     object field_value = r.GetValue(i);
518     gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
519     }
520     break;
521     }
522     }
523     }
524     }
525 william 157 con.Clone();
526 william 156 }
527     OnCreatedInstance(this, new EventArgs());
528     }
529     catch (Exception ex)
530     {
531     gLog.Error.WriteLine(ex.ToString());
532     }
533     }
534    
535 william 157
536     private bool CreateDatabaseBackup()
537     {
538     try
539     {
540     string backup_file = string.Format("{0}.{1}", this.Database, DateTime.Now.ToString("yyyyMMddHHmmss"));
541     gLog.Info.WriteLine("Creating Database backup...");
542     gLog.Info.WriteLine("\tSource: {0}", this.Database);
543     gLog.Info.WriteLine("\tDestination: {0}", backup_file);
544    
545     System.IO.File.Copy(this.Database, backup_file);
546     return true;
547     }
548     catch (Exception ex)
549     {
550     gLog.Error.WriteLine(ex.ToString());
551     return false;
552     }
553     }
554 william 159 private void ReadChannelData()
555     {
556     try
557     {
558     List<ICHANNEL> channels = new List<ICHANNEL>();
559     using (SQLiteConnection con = CreateConnection())
560     {
561     con.Open();
562     string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
563     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
564     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
565     {
566     using (SQLiteDataReader r = cmd.ExecuteReader())
567     {
568     if (!r.HasRows)
569     {
570     gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
571     }
572     else
573     {
574     while (r.Read())
575     {
576     CHANNEL channel = new CHANNEL();
577     for (int i = 0; i < r.FieldCount; i++)
578     {
579     CHANNEL.Create(ref channel, r, i);
580     }
581     channels.Add(channel);
582     }
583     }
584     }
585     }
586     con.Clone();
587     }
588     this.Channels = channels;
589     }
590     catch (Exception ex)
591     {
592     gLog.Error.WriteLine(ex.ToString());
593     }
594     }
595 william 156 private void ReadProgrammeData()
596     {
597     try
598     {
599     List<IPROGRAMME> programs = new List<IPROGRAMME>();
600     using (SQLiteConnection con = CreateConnection())
601     {
602     con.Open();
603     string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
604     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
605     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
606     {
607     using (SQLiteDataReader r = cmd.ExecuteReader())
608     {
609     if (!r.HasRows)
610     {
611     gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
612     }
613     else
614     {
615     while (r.Read())
616     {
617     PROGRAMME program = new PROGRAMME();
618     for (int i = 0; i < r.FieldCount; i++)
619     {
620     PROGRAMME.Create(ref program, r, i);
621     }
622     programs.Add(program);
623     }
624     }
625     }
626     }
627     con.Clone();
628     }
629     this.Programs = programs;
630     }
631     catch (Exception ex)
632     {
633     gLog.Error.WriteLine(ex.ToString());
634     }
635     }
636     private void ReadRecodringScheduleData()
637     {
638     try
639     {
640     List<IRECORDING_SCHEDULE> recordings = new List<IRECORDING_SCHEDULE>();
641     using (SQLiteConnection con = CreateConnection())
642     {
643     con.Open();
644     string command_text = string.Format("select * from {0};", TABLES.RECORDING_SCHEDULE);
645     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
646     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
647     {
648     using (SQLiteDataReader r = cmd.ExecuteReader())
649     {
650     if (!r.HasRows)
651     {
652     gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
653     }
654     else
655     {
656     while (r.Read())
657     {
658     RECORDING_SCHEDULE recording = new RECORDING_SCHEDULE();
659     for (int i = 0; i < r.FieldCount; i++)
660     {
661     RECORDING_SCHEDULE.Create(ref recording, r, i);
662     }
663     recordings.Add(recording);
664     }
665     }
666     }
667     }
668     con.Clone();
669     }
670     this.Recordings = recordings;
671     }
672     catch (Exception ex)
673     {
674     gLog.Error.WriteLine(ex.ToString());
675     }
676     }
677 william 159
678 william 174
679    
680     private bool RemoveProgramDatabaseEntry(IPROGRAMME old_program)
681     {
682     bool result = false;
683     try
684     {
685     using (SQLiteConnection con = CreateConnection())
686     {
687     try
688     {
689 william 176 gLog.Verbose.Info.WriteLine("Removing old program with oid: '{0}'", old_program.oid);
690 william 174 con.Open();
691     string command_text = string.Format(@"DELETE FROM [{0}] WHERE [oid] = @oid;", TABLES.PROGRAMME);
692     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
693     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
694     {
695     cmd.Parameters.Add(new SQLiteParameter("oid", old_program.oid));
696     int rowsupdated = cmd.ExecuteNonQuery();
697 william 176 gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
698 william 174 }
699     con.Clone();
700     result = true;
701     }
702     catch (SQLiteException ex)
703     {
704     gLog.Error.WriteLine(ex.ToString());
705     result = false;
706     }
707     }
708     }
709     catch (Exception ex)
710     {
711     gLog.Error.WriteLine(ex.ToString());
712     result = false;
713     }
714     return result;
715     }
716     private bool UpdateProgramDatabaseEntry(IPROGRAMME old_program, IPROGRAMME new_program)
717     {
718     bool result = false;
719     try
720     {
721     using (SQLiteConnection con = CreateConnection())
722     {
723     try
724     {
725 william 176 gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", old_program.oid);
726 william 174 con.Open();
727     //string command_text = string.Format(@"UPDATE [{0}] SET [name]=@name,[sub_title]=@subtitle, WHERE [OID] = @oid", TABLES.PROGRAMME);
728     string command_text = string.Empty;
729     StringBuilder builder = new StringBuilder();
730    
731     builder.AppendLine(string.Format("UPDATE [{0}]", TABLES.PROGRAMME));
732     builder.Append("SET ");
733     builder.AppendFormat("[name]=@name,");
734     builder.AppendFormat("[sub_title]=@sub_title,");
735     builder.AppendFormat("[description]=@description,");
736     builder.AppendFormat("[start_time]=@start_time,");
737     builder.AppendFormat("[end_time]=@end_time,");
738     builder.AppendFormat("[channel_oid]=@channel_oid,");
739     builder.AppendFormat("[unique_identifier]=@unique_identifier,");
740     builder.AppendFormat("[rating]=@rating");
741     builder.AppendLine(" WHERE [oid] = @oid");
742    
743     command_text = builder.ToString();
744     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
745     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
746     {
747 william 175 cmd.Parameters.Add(new SQLiteParameter("oid", old_program.oid));
748 william 174 cmd.Parameters.Add(new SQLiteParameter("name", new_program.name));
749     cmd.Parameters.Add(new SQLiteParameter("sub_title", new_program.sub_title));
750     cmd.Parameters.Add(new SQLiteParameter("description", new_program.description));
751     cmd.Parameters.Add(new SQLiteParameter("start_time", new_program.start_time));
752     cmd.Parameters.Add(new SQLiteParameter("end_time", new_program.end_time));
753     cmd.Parameters.Add(new SQLiteParameter("channel_oid", new_program.channel_oid));
754     cmd.Parameters.Add(new SQLiteParameter("unique_identifier", new_program.unique_identifier));
755     cmd.Parameters.Add(new SQLiteParameter("rating", new_program.rating));
756     int rowsupdated = cmd.ExecuteNonQuery();
757 william 176 gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
758 william 174 }
759     con.Clone();
760     result = true;
761     }
762     catch (SQLiteException ex)
763     {
764     gLog.Error.WriteLine(ex.ToString());
765     result = false;
766     }
767     }
768     }
769     catch (Exception ex)
770     {
771     gLog.Error.WriteLine(ex.ToString());
772     result = false;
773     }
774     return result;
775     }
776 william 156 }
777     }

  ViewVC Help
Powered by ViewVC 1.1.22