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

Annotation of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


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

  ViewVC Help
Powered by ViewVC 1.1.22