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

Annotation of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 163 - (hide annotations) (download)
Fri Mar 15 16:53:40 2013 UTC (6 years, 10 months ago) by william
File size: 21107 byte(s)
+ filter GBPVR Programs to match the date-range of the XMLTV Programs list

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 156 namespace GBPVRProgramDatabaseFixer
11     {
12 william 163 public interface ISQLLITE
13 william 156 {
14 william 163 List<SQLLITE.IPROGRAMME> Programs { get; }
15     List<SQLLITE.IRECORDING_SCHEDULE> Recordings { get; }
16     List<SQLLITE.ICHANNEL> Channels { get; }
17     IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs);
18     List<SQLLITE.IPROGRAMME> FilterProgramsByDateRange(List<SQLLITE.IPROGRAMME> programs, IDateTimeRange range);
19    
20     }
21     public class SQLLITE : ISQLLITE
22     {
23     public static ISQLLITE Create(string database, EventHandler<EventArgs> OnInstanceCreated)
24     {
25     return new SQLLITE(database, OnInstanceCreated);
26     }
27    
28 william 156 #region DATABASE DEFINITIONS
29 william 159
30     public interface ICHANNEL
31     {
32     Int64 oid { get; }
33     String name { get; }
34     String channelID { get; }
35     Int64 channel_number { get; }
36     String favourite_channel { get; }
37     String display_name { get; }
38     }
39     private class CHANNEL : ICHANNEL
40     {
41     public CHANNEL()
42     {
43     BaseDatabaseDefinition<CHANNEL>.CreateDefault(this);
44     }
45     //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }
46    
47     public static void Create(ref CHANNEL instance, SQLiteDataReader r, int index)
48     {
49     BaseDatabaseDefinition<CHANNEL>.Create(ref instance, r, index);
50     }
51     #region ICHANNEL members
52     public Int64 oid { get; set; }
53     public String name { get; set; }
54     public String channelID { get; set; }
55     public Int64 channel_number { get; set; }
56     public String favourite_channel { get; set; }
57     public String display_name { get; set; }
58     #endregion
59     }
60 william 156 public interface IRECORDING_SCHEDULE
61     {
62     Int64 oid { get; }
63     Int64 programme_oid { get; }
64     Int64 capture_source_oid { get; }
65     Int16 status { get; }
66     String filename { get; }
67     Int64 recording_type { get; }
68     Int64 recording_group { get; }
69     DateTime manual_start_time { get; }
70     DateTime manual_end_time { get; }
71     Int64 manual_channel_oid { get; }
72     Int64 quality_level { get; }
73     Int64 pre_pad_minutes { get; }
74     Int64 post_pad_minutes { get;}
75     Int32 priority { get; }
76     String conversion_profile { get; }
77     }
78    
79     private static class BaseDatabaseDefinition<T>
80     {
81     public static void CreateDefault(T instance)
82     {
83     try
84     {
85     Type t = typeof(T);
86     var props = t.GetProperties();
87     foreach (var prop in props)
88     {
89     Type prop_type = prop.PropertyType;
90     object field_value = null;
91     try
92     {
93     if (prop_type == typeof(string))
94     {
95     field_value = string.Empty;
96     }
97     else
98     {
99     field_value = Activator.CreateInstance(prop_type);
100     }
101     }
102     catch (Exception ex)
103     {
104     throw ex;
105     }
106     prop.SetValue(instance, field_value, null);
107     }
108     }
109     catch (Exception ex)
110     {
111     throw ex;
112     }
113     }
114     public static void Create(ref T instance, SQLiteDataReader r, int index)
115     {
116     string field_name = r.GetName(index);
117     Type field_type = r.GetFieldType(index);
118     object field_value = r.GetValue(index);
119     //gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
120    
121     Type t = typeof(T);
122     var props = t.GetProperties();
123     foreach (var prop in props)
124     {
125     if (prop.Name.ToLower() == field_name.ToLower())
126     {
127     if (prop.PropertyType == field_type)
128     {
129     Type db_type = field_value.GetType();
130     try
131     {
132     if (db_type == typeof(System.DBNull))
133     {
134     prop.SetValue(instance, null, null);
135     }
136     else
137     {
138     prop.SetValue(instance, field_value, null);
139     }
140    
141     }
142     catch (Exception ex)
143     {
144     throw ex;
145     }
146     }
147     else
148     {
149     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);
150     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));
151     }
152     }
153     }
154    
155     }
156     }
157    
158     private class RECORDING_SCHEDULE : IRECORDING_SCHEDULE
159     {
160     public RECORDING_SCHEDULE()
161     {
162     BaseDatabaseDefinition<RECORDING_SCHEDULE>.CreateDefault(this);
163     }
164     //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }
165    
166     public static void Create(ref RECORDING_SCHEDULE instance, SQLiteDataReader r, int index)
167     {
168     BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(ref instance, r, index);
169     }
170    
171     #region IRECORDING_SCHEDULE members
172     public Int64 oid { get; set;}
173     public Int64 programme_oid { get; set;}
174     public Int64 capture_source_oid { get; set;}
175     public Int16 status { get; set; }
176     public String filename { get; set; }
177     public Int64 recording_type { get;set; }
178     public Int64 recording_group { get; set;}
179     public DateTime manual_start_time { get; set;}
180     public DateTime manual_end_time { get; set; }
181     public Int64 manual_channel_oid { get; set; }
182     public Int64 quality_level { get; set; }
183     public Int64 pre_pad_minutes { get; set; }
184     public Int64 post_pad_minutes { get; set; }
185     public Int32 priority { get; set; }
186     public String conversion_profile { get; set; }
187     #endregion
188     }
189    
190     public interface IPROGRAMME
191     {
192     Int64 oid { get; }
193     String name { get; }
194     String sub_title { get; }
195     String description { get; }
196     DateTime start_time { get; }
197     DateTime end_time { get; }
198     Int64 channel_oid { get; }
199     String unique_identifier { get; }
200     String rating { get; }
201     }
202     private class PROGRAMME : IPROGRAMME
203     {
204     public PROGRAMME()
205     {
206     BaseDatabaseDefinition<PROGRAMME>.CreateDefault(this);
207     }
208     //public PROGRAMME(SQLiteDataReader r, int index) : base(r, index) { }
209     public static void Create(ref PROGRAMME instance, SQLiteDataReader r, int index)
210     {
211     BaseDatabaseDefinition<PROGRAMME>.Create(ref instance, r, index);
212     }
213     #region IPROGRAMME members
214     public Int64 oid { get; set; }
215     public String name { get; set; }
216     public String sub_title { get; set; }
217     public String description { get; set; }
218     public DateTime start_time { get; set; }
219     public DateTime end_time { get; set; }
220     public Int64 channel_oid { get; set; }
221     public String unique_identifier { get; set; }
222     public String rating { get; set; }
223     #endregion
224     }
225     #endregion
226     private static class TABLES
227     {
228     public const string RECORDING_SCHEDULE = "RECORDING_SCHEDULE";
229     public const string PROGRAMME = "PROGRAMME";
230 william 159 public const string CHANNEL = "CHANNEL";
231 william 156 }
232    
233     //public SQLLite() { }
234 william 163 protected SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated)
235 william 156 {
236     this.OnInstanceCreated = OnInstanceCreated;
237     //CreateConnection(database);
238     this.Database = database;
239 william 157 if (!CreateDatabaseBackup())
240     {
241     gLog.Error.WriteLine("Failed to backup database.");
242     return;
243     }
244 william 156 ConnectionTest();
245 william 159 ReadChannelData();
246 william 156 ReadRecodringScheduleData();
247     ReadProgrammeData();
248     OnCreatedInstance(this, new EventArgs());
249     }
250    
251     [NonSerialized]
252     private EventHandler<EventArgs> _OnInstanceCreated;
253     private EventHandler<EventArgs> OnInstanceCreated { get { return _OnInstanceCreated; } set { _OnInstanceCreated = value; } }
254    
255     private void OnCreatedInstance(object sender, EventArgs e)
256     {
257     if (OnInstanceCreated != null) { OnInstanceCreated.Invoke(sender, e); }
258     }
259    
260     private string Database;
261 william 163 #region ISQLLITE members
262 william 156 public List<IPROGRAMME> Programs { get; private set; }
263     public List<IRECORDING_SCHEDULE> Recordings { get; private set; }
264 william 159 public List<ICHANNEL> Channels { get; private set; }
265 william 163
266     public IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs)
267     {
268     var list = new List<IPROGRAMME>(programs.ToArray());
269     DateTime first = new DateTime();
270     DateTime last = new DateTime();
271     first = list.OrderBy(s => s.start_time).ToList().First().start_time;
272     last = list.OrderBy(s => s.start_time).ToList().Last().start_time;
273     gLog.Info.WriteLine("\tFirst: {0} = ({1})", first.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), first.ToDateTimeString());
274     gLog.Info.WriteLine("\tLast: {0} = ({1})", last.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), last.ToDateTimeString());
275     var range = DateTimeRange.Create(first, last);
276     return range;
277     }
278    
279     public List<SQLLITE.IPROGRAMME> FilterProgramsByDateRange(List<SQLLITE.IPROGRAMME> programs, IDateTimeRange range)
280     {
281     List<SQLLITE.IPROGRAMME> list = new List<IPROGRAMME>(programs.ToArray());
282     list = list.OrderBy(s => s.start_time).ToList();
283     List<SQLLITE.IPROGRAMME> list2 = new List<IPROGRAMME>();
284     if (range == null)
285     {
286     gLog.Warn.WriteLine("The DateTimeRange passed in is null...returning the original program list");
287     return list;
288     }
289     //gLog.Warn.WriteLine("FilterProgramsByDateRange has not been implemented");
290    
291     foreach (var program in list)
292     {
293     if (program.start_time >= range.Start &&
294     program.start_time <= range.End)
295     {
296     list2.Add(program);
297     }
298     }
299     list2 = list2.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
300     return list2;
301     }
302 william 156 #endregion
303    
304    
305     private string CreateConnectionString()
306     {
307     string connection_string = string.Format("Data Source={0}", this.Database);
308     return connection_string;
309     }
310    
311     private SQLiteConnection CreateConnection() { SQLiteConnection connection = new SQLiteConnection(CreateConnectionString()); return connection; }
312    
313     [Conditional("SQLLITE_CONNECTION_TEST")]
314     private void ConnectionTest()
315     {
316     try
317     {
318     using (SQLiteConnection con = CreateConnection())
319     {
320     con.Open();
321 william 159 string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
322 william 156 gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
323     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
324     {
325     using (SQLiteDataReader r = cmd.ExecuteReader())
326     {
327     if (!r.HasRows)
328     {
329     gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
330     }
331     else
332     {
333     while (r.Read())
334     {
335     for (int i = 0; i < r.FieldCount; i++)
336     {
337     string field_name = r.GetName(i);
338     Type field_type = r.GetFieldType(i);
339     object field_value = r.GetValue(i);
340     gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
341     }
342     break;
343     }
344     }
345     }
346     }
347 william 157 con.Clone();
348 william 156 }
349     OnCreatedInstance(this, new EventArgs());
350     }
351     catch (Exception ex)
352     {
353     gLog.Error.WriteLine(ex.ToString());
354     }
355     }
356    
357 william 157
358     private bool CreateDatabaseBackup()
359     {
360     try
361     {
362     string backup_file = string.Format("{0}.{1}", this.Database, DateTime.Now.ToString("yyyyMMddHHmmss"));
363     gLog.Info.WriteLine("Creating Database backup...");
364     gLog.Info.WriteLine("\tSource: {0}", this.Database);
365     gLog.Info.WriteLine("\tDestination: {0}", backup_file);
366    
367     System.IO.File.Copy(this.Database, backup_file);
368     return true;
369     }
370     catch (Exception ex)
371     {
372     gLog.Error.WriteLine(ex.ToString());
373     return false;
374     }
375     }
376 william 159 private void ReadChannelData()
377     {
378     try
379     {
380     List<ICHANNEL> channels = new List<ICHANNEL>();
381     using (SQLiteConnection con = CreateConnection())
382     {
383     con.Open();
384     string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
385     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
386     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
387     {
388     using (SQLiteDataReader r = cmd.ExecuteReader())
389     {
390     if (!r.HasRows)
391     {
392     gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
393     }
394     else
395     {
396     while (r.Read())
397     {
398     CHANNEL channel = new CHANNEL();
399     for (int i = 0; i < r.FieldCount; i++)
400     {
401     CHANNEL.Create(ref channel, r, i);
402     }
403     channels.Add(channel);
404     }
405     }
406     }
407     }
408     con.Clone();
409     }
410     this.Channels = channels;
411     }
412     catch (Exception ex)
413     {
414     gLog.Error.WriteLine(ex.ToString());
415     }
416     }
417 william 156 private void ReadProgrammeData()
418     {
419     try
420     {
421     List<IPROGRAMME> programs = new List<IPROGRAMME>();
422     using (SQLiteConnection con = CreateConnection())
423     {
424     con.Open();
425     string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
426     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
427     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
428     {
429     using (SQLiteDataReader r = cmd.ExecuteReader())
430     {
431     if (!r.HasRows)
432     {
433     gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
434     }
435     else
436     {
437     while (r.Read())
438     {
439     PROGRAMME program = new PROGRAMME();
440     for (int i = 0; i < r.FieldCount; i++)
441     {
442     PROGRAMME.Create(ref program, r, i);
443     }
444     programs.Add(program);
445     }
446     }
447     }
448     }
449     con.Clone();
450     }
451     this.Programs = programs;
452     }
453     catch (Exception ex)
454     {
455     gLog.Error.WriteLine(ex.ToString());
456     }
457     }
458     private void ReadRecodringScheduleData()
459     {
460     try
461     {
462     List<IRECORDING_SCHEDULE> recordings = new List<IRECORDING_SCHEDULE>();
463     using (SQLiteConnection con = CreateConnection())
464     {
465     con.Open();
466     string command_text = string.Format("select * from {0};", TABLES.RECORDING_SCHEDULE);
467     gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
468     using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
469     {
470     using (SQLiteDataReader r = cmd.ExecuteReader())
471     {
472     if (!r.HasRows)
473     {
474     gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
475     }
476     else
477     {
478     while (r.Read())
479     {
480     RECORDING_SCHEDULE recording = new RECORDING_SCHEDULE();
481     for (int i = 0; i < r.FieldCount; i++)
482     {
483     RECORDING_SCHEDULE.Create(ref recording, r, i);
484     }
485     recordings.Add(recording);
486     }
487     }
488     }
489     }
490     con.Clone();
491     }
492     this.Recordings = recordings;
493     }
494     catch (Exception ex)
495     {
496     gLog.Error.WriteLine(ex.ToString());
497     }
498     }
499 william 159
500 william 156 }
501     }

  ViewVC Help
Powered by ViewVC 1.1.22