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

Contents of /trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 156 - (show annotations) (download)
Thu Mar 14 19:25:17 2013 UTC (7 years, 5 months ago) by william
File size: 14678 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 public interface IRECORDING_SCHEDULE
16 {
17 Int64 oid { get; }
18 Int64 programme_oid { get; }
19 Int64 capture_source_oid { get; }
20 Int16 status { get; }
21 String filename { get; }
22 Int64 recording_type { get; }
23 Int64 recording_group { get; }
24 DateTime manual_start_time { get; }
25 DateTime manual_end_time { get; }
26 Int64 manual_channel_oid { get; }
27 Int64 quality_level { get; }
28 Int64 pre_pad_minutes { get; }
29 Int64 post_pad_minutes { get;}
30 Int32 priority { get; }
31 String conversion_profile { get; }
32 }
33
34 private static class BaseDatabaseDefinition<T>
35 {
36 public static void CreateDefault(T instance)
37 {
38 try
39 {
40 Type t = typeof(T);
41 var props = t.GetProperties();
42 foreach (var prop in props)
43 {
44 Type prop_type = prop.PropertyType;
45 object field_value = null;
46 try
47 {
48 if (prop_type == typeof(string))
49 {
50 field_value = string.Empty;
51 }
52 else
53 {
54 field_value = Activator.CreateInstance(prop_type);
55 }
56 }
57 catch (Exception ex)
58 {
59 throw ex;
60 }
61 prop.SetValue(instance, field_value, null);
62 }
63 }
64 catch (Exception ex)
65 {
66 throw ex;
67 }
68 }
69 public static void Create(ref T instance, SQLiteDataReader r, int index)
70 {
71 string field_name = r.GetName(index);
72 Type field_type = r.GetFieldType(index);
73 object field_value = r.GetValue(index);
74 //gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
75
76 Type t = typeof(T);
77 var props = t.GetProperties();
78 foreach (var prop in props)
79 {
80 if (prop.Name.ToLower() == field_name.ToLower())
81 {
82 if (prop.PropertyType == field_type)
83 {
84 Type db_type = field_value.GetType();
85 try
86 {
87 if (db_type == typeof(System.DBNull))
88 {
89 prop.SetValue(instance, null, null);
90 }
91 else
92 {
93 prop.SetValue(instance, field_value, null);
94 }
95
96 }
97 catch (Exception ex)
98 {
99 throw ex;
100 }
101 }
102 else
103 {
104 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);
105 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));
106 }
107 }
108 }
109
110 }
111 }
112
113 private class RECORDING_SCHEDULE : IRECORDING_SCHEDULE
114 {
115 public RECORDING_SCHEDULE()
116 {
117 BaseDatabaseDefinition<RECORDING_SCHEDULE>.CreateDefault(this);
118 }
119 //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }
120
121 public static void Create(ref RECORDING_SCHEDULE instance, SQLiteDataReader r, int index)
122 {
123 BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(ref instance, r, index);
124 }
125
126 #region IRECORDING_SCHEDULE members
127 public Int64 oid { get; set;}
128 public Int64 programme_oid { get; set;}
129 public Int64 capture_source_oid { get; set;}
130 public Int16 status { get; set; }
131 public String filename { get; set; }
132 public Int64 recording_type { get;set; }
133 public Int64 recording_group { get; set;}
134 public DateTime manual_start_time { get; set;}
135 public DateTime manual_end_time { get; set; }
136 public Int64 manual_channel_oid { get; set; }
137 public Int64 quality_level { get; set; }
138 public Int64 pre_pad_minutes { get; set; }
139 public Int64 post_pad_minutes { get; set; }
140 public Int32 priority { get; set; }
141 public String conversion_profile { get; set; }
142 #endregion
143 }
144
145 public interface IPROGRAMME
146 {
147 Int64 oid { get; }
148 String name { get; }
149 String sub_title { get; }
150 String description { get; }
151 DateTime start_time { get; }
152 DateTime end_time { get; }
153 Int64 channel_oid { get; }
154 String unique_identifier { get; }
155 String rating { get; }
156 }
157 private class PROGRAMME : IPROGRAMME
158 {
159 public PROGRAMME()
160 {
161 BaseDatabaseDefinition<PROGRAMME>.CreateDefault(this);
162 }
163 //public PROGRAMME(SQLiteDataReader r, int index) : base(r, index) { }
164 public static void Create(ref PROGRAMME instance, SQLiteDataReader r, int index)
165 {
166 BaseDatabaseDefinition<PROGRAMME>.Create(ref instance, r, index);
167 }
168 #region IPROGRAMME members
169 public Int64 oid { get; set; }
170 public String name { get; set; }
171 public String sub_title { get; set; }
172 public String description { get; set; }
173 public DateTime start_time { get; set; }
174 public DateTime end_time { get; set; }
175 public Int64 channel_oid { get; set; }
176 public String unique_identifier { get; set; }
177 public String rating { get; set; }
178 #endregion
179 }
180 #endregion
181 private static class TABLES
182 {
183 public const string RECORDING_SCHEDULE = "RECORDING_SCHEDULE";
184 public const string PROGRAMME = "PROGRAMME";
185 }
186
187 //public SQLLite() { }
188 public SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated)
189 {
190 this.OnInstanceCreated = OnInstanceCreated;
191 //CreateConnection(database);
192 this.Database = database;
193 ConnectionTest();
194 ReadRecodringScheduleData();
195 ReadProgrammeData();
196 OnCreatedInstance(this, new EventArgs());
197 }
198
199 [NonSerialized]
200 private EventHandler<EventArgs> _OnInstanceCreated;
201 private EventHandler<EventArgs> OnInstanceCreated { get { return _OnInstanceCreated; } set { _OnInstanceCreated = value; } }
202
203 private void OnCreatedInstance(object sender, EventArgs e)
204 {
205 if (OnInstanceCreated != null) { OnInstanceCreated.Invoke(sender, e); }
206 }
207
208 #region SQLLite
209 private string Database;
210 public List<IPROGRAMME> Programs { get; private set; }
211 public List<IRECORDING_SCHEDULE> Recordings { get; private set; }
212 #endregion
213
214
215 private string CreateConnectionString()
216 {
217 string connection_string = string.Format("Data Source={0}", this.Database);
218 return connection_string;
219 }
220
221 private SQLiteConnection CreateConnection() { SQLiteConnection connection = new SQLiteConnection(CreateConnectionString()); return connection; }
222
223 [Conditional("SQLLITE_CONNECTION_TEST")]
224 private void ConnectionTest()
225 {
226 try
227 {
228 using (SQLiteConnection con = CreateConnection())
229 {
230 con.Open();
231 string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
232 gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
233 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
234 {
235 using (SQLiteDataReader r = cmd.ExecuteReader())
236 {
237 if (!r.HasRows)
238 {
239 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
240 }
241 else
242 {
243 while (r.Read())
244 {
245 for (int i = 0; i < r.FieldCount; i++)
246 {
247 string field_name = r.GetName(i);
248 Type field_type = r.GetFieldType(i);
249 object field_value = r.GetValue(i);
250 gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
251 }
252 break;
253 }
254 }
255 }
256 }
257 con.Clone();
258 }
259 OnCreatedInstance(this, new EventArgs());
260 }
261 catch (Exception ex)
262 {
263 gLog.Error.WriteLine(ex.ToString());
264 }
265 }
266
267 private void ReadProgrammeData()
268 {
269 try
270 {
271 List<IPROGRAMME> programs = new List<IPROGRAMME>();
272 using (SQLiteConnection con = CreateConnection())
273 {
274 con.Open();
275 string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
276 gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
277 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
278 {
279 using (SQLiteDataReader r = cmd.ExecuteReader())
280 {
281 if (!r.HasRows)
282 {
283 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
284 }
285 else
286 {
287 while (r.Read())
288 {
289 PROGRAMME program = new PROGRAMME();
290 for (int i = 0; i < r.FieldCount; i++)
291 {
292 PROGRAMME.Create(ref program, r, i);
293 }
294 programs.Add(program);
295 }
296 }
297 }
298 }
299 con.Clone();
300 }
301 this.Programs = programs;
302 }
303 catch (Exception ex)
304 {
305 gLog.Error.WriteLine(ex.ToString());
306 }
307 }
308 private void ReadRecodringScheduleData()
309 {
310 try
311 {
312 List<IRECORDING_SCHEDULE> recordings = new List<IRECORDING_SCHEDULE>();
313 using (SQLiteConnection con = CreateConnection())
314 {
315 con.Open();
316 string command_text = string.Format("select * from {0};", TABLES.RECORDING_SCHEDULE);
317 gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
318 using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
319 {
320 using (SQLiteDataReader r = cmd.ExecuteReader())
321 {
322 if (!r.HasRows)
323 {
324 gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
325 }
326 else
327 {
328 while (r.Read())
329 {
330 RECORDING_SCHEDULE recording = new RECORDING_SCHEDULE();
331 for (int i = 0; i < r.FieldCount; i++)
332 {
333 RECORDING_SCHEDULE.Create(ref recording, r, i);
334 }
335
336 recordings.Add(recording);
337 }
338 }
339 }
340 }
341 con.Clone();
342 }
343 this.Recordings = recordings;
344 }
345 catch (Exception ex)
346 {
347 gLog.Error.WriteLine(ex.ToString());
348 }
349 }
350 }
351 }

  ViewVC Help
Powered by ViewVC 1.1.22