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

Annotation of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.22