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

Contents of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 159 - (show annotations) (download)
Thu Mar 14 20:26:05 2013 UTC (6 years, 5 months ago) by william
File size: 18620 byte(s)

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

  ViewVC Help
Powered by ViewVC 1.1.22