trunk/GBPVRProgramDatabaseFixer/SQLLITE.cs

//#define SQLLITE_CONNECTION_TEST
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Diagnostics;
using Enterprise.Logging;

namespace GBPVRProgramDatabaseFixer
{
    internal class SQLLITE
    {
        #region DATABASE DEFINITIONS
        public interface IRECORDING_SCHEDULE
        {
            Int64 oid { get; }
            Int64 programme_oid { get; }
            Int64 capture_source_oid { get; }
            Int16 status { get; }
            String filename { get; }
            Int64 recording_type { get; }
            Int64 recording_group { get; }
            DateTime manual_start_time { get; }
            DateTime manual_end_time { get; }
            Int64 manual_channel_oid { get; }
            Int64 quality_level { get; }
            Int64 pre_pad_minutes { get; }
            Int64 post_pad_minutes { get;}
            Int32 priority { get; }
            String conversion_profile { get; }
        }

        private static class BaseDatabaseDefinition<T>
        {
            public static void CreateDefault(T instance)
            {
                try
                {
                    Type t = typeof(T);
                    var props = t.GetProperties();
                    foreach (var prop in props)
                    {
                        Type prop_type = prop.PropertyType;
                        object field_value = null;
                        try
                        {
                            if (prop_type == typeof(string))
                            {
                                field_value = string.Empty;
                            }
                            else
                            {
                                field_value = Activator.CreateInstance(prop_type);
                            }
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                        prop.SetValue(instance, field_value, null);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            public static void Create(ref T instance, SQLiteDataReader r, int index)
            {                
                string field_name = r.GetName(index);
                Type field_type = r.GetFieldType(index);
                object field_value = r.GetValue(index);
                //gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());

                Type t = typeof(T);
                var props = t.GetProperties();
                foreach (var prop in props)
                {
                    if (prop.Name.ToLower() == field_name.ToLower())
                    {
                        if (prop.PropertyType == field_type)
                        {
                            Type db_type = field_value.GetType();
                            try
                            {
                                if (db_type == typeof(System.DBNull))
                                {
                                    prop.SetValue(instance, null, null);
                                }
                                else
                                {
                                    prop.SetValue(instance, field_value, null);
                                }
                                
                            }
                            catch (Exception ex) 
                            {
                                throw ex;
                            }
                        }
                        else
                        {
                            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);
                            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));
                        }
                    }
                }

            }
        }

        private class RECORDING_SCHEDULE : IRECORDING_SCHEDULE
        {
            public RECORDING_SCHEDULE() 
            {
                BaseDatabaseDefinition<RECORDING_SCHEDULE>.CreateDefault(this); 
            }
            //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }

            public static void Create(ref RECORDING_SCHEDULE instance, SQLiteDataReader r, int index)
            {
                BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(ref instance, r, index); 
            }

            #region IRECORDING_SCHEDULE members
            public Int64 oid { get; set;}
            public Int64 programme_oid { get; set;}
            public Int64 capture_source_oid { get; set;}
            public Int16 status { get; set; }
            public String filename { get; set; }
            public Int64 recording_type { get;set; }
            public Int64 recording_group { get; set;}
            public DateTime manual_start_time { get; set;}
            public DateTime manual_end_time { get; set; }
            public Int64 manual_channel_oid { get; set; }
            public Int64 quality_level { get; set; }
            public Int64 pre_pad_minutes { get; set; }
            public Int64 post_pad_minutes { get; set; }
            public Int32 priority { get; set; }
            public String conversion_profile { get; set; }
            #endregion
        }

        public interface IPROGRAMME
        {
            Int64 oid { get; }
            String name { get; }
            String sub_title { get; }
            String description { get; }
            DateTime start_time { get; }
            DateTime end_time { get; }
            Int64 channel_oid { get; }
            String unique_identifier { get; }
            String rating { get; }
        }
        private class PROGRAMME : IPROGRAMME
        {
            public PROGRAMME() 
            {
                BaseDatabaseDefinition<PROGRAMME>.CreateDefault(this); 
            }
            //public PROGRAMME(SQLiteDataReader r, int index) : base(r, index) { }
            public static void Create(ref PROGRAMME instance, SQLiteDataReader r, int index)
            {
                BaseDatabaseDefinition<PROGRAMME>.Create(ref instance, r, index);
            }
            #region IPROGRAMME members
            public Int64 oid { get; set; }
            public String name { get; set; }
            public String sub_title { get; set; }
            public String description { get; set; }
            public DateTime start_time { get; set; }
            public DateTime end_time { get; set; }
            public Int64 channel_oid { get; set; }
            public String unique_identifier { get; set; }
            public String rating { get; set; }
            #endregion
        }
        #endregion
        private static class TABLES
        {
            public const string RECORDING_SCHEDULE = "RECORDING_SCHEDULE";
            public const string PROGRAMME = "PROGRAMME";
        }

        //public SQLLite() { }
        public SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated) 
        {
            this.OnInstanceCreated = OnInstanceCreated;
            //CreateConnection(database);
            this.Database = database;
            if (!CreateDatabaseBackup())
            {
                gLog.Error.WriteLine("Failed to backup database.");
                return;
            }
            ConnectionTest();
            ReadRecodringScheduleData();
            ReadProgrammeData();
            OnCreatedInstance(this, new EventArgs());
        }

        [NonSerialized]
        private EventHandler<EventArgs> _OnInstanceCreated;
        private EventHandler<EventArgs> OnInstanceCreated { get { return _OnInstanceCreated; } set { _OnInstanceCreated = value; } }
        
        private void OnCreatedInstance(object sender, EventArgs e)
        {
            if (OnInstanceCreated != null) { OnInstanceCreated.Invoke(sender, e); }
        }

        #region SQLLite 
        private string Database;
        public List<IPROGRAMME> Programs { get; private set; }
        public List<IRECORDING_SCHEDULE> Recordings { get; private set; }
        #endregion


        private string CreateConnectionString()
        {
            string connection_string = string.Format("Data Source={0}", this.Database);
            return connection_string;
        }

        private SQLiteConnection CreateConnection() { SQLiteConnection connection = new SQLiteConnection(CreateConnectionString()); return connection; }

        [Conditional("SQLLITE_CONNECTION_TEST")]
        private void ConnectionTest()
        {
            try
            {
                using (SQLiteConnection con = CreateConnection())
                {
                    con.Open();
                    string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
                    gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
                    using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
                    {
                        using (SQLiteDataReader r = cmd.ExecuteReader())
                        {
                            if (!r.HasRows)
                            {
                                gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
                            }
                            else
                            {
                                while (r.Read())
                                {
                                    for (int i = 0; i < r.FieldCount; i++)
                                    {
                                        string field_name = r.GetName(i);
                                        Type field_type = r.GetFieldType(i);
                                        object field_value = r.GetValue(i);
                                        gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
                                    }
                                    break;
                                }
                            }
                        }
                    }
                    con.Clone();
                }
                OnCreatedInstance(this, new EventArgs());
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
            }
        }


        private bool CreateDatabaseBackup()
        {
            try
            {
                string backup_file = string.Format("{0}.{1}", this.Database, DateTime.Now.ToString("yyyyMMddHHmmss"));
                gLog.Info.WriteLine("Creating Database backup...");
                gLog.Info.WriteLine("\tSource: {0}", this.Database);
                gLog.Info.WriteLine("\tDestination: {0}", backup_file);

                System.IO.File.Copy(this.Database, backup_file);
                return true;
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
                return false;
            }
        }

        private void ReadProgrammeData()
        {
            try
            {
                List<IPROGRAMME> programs = new List<IPROGRAMME>();
                using (SQLiteConnection con = CreateConnection())
                {
                    con.Open();
                    string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
                    gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
                    using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
                    {
                        using (SQLiteDataReader r = cmd.ExecuteReader())
                        {
                            if (!r.HasRows)
                            {
                                gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
                            }
                            else
                            {
                                while (r.Read())
                                {
                                    PROGRAMME program = new PROGRAMME();
                                    for (int i = 0; i < r.FieldCount; i++)
                                    {
                                        PROGRAMME.Create(ref program, r, i);
                                    }
                                    programs.Add(program);
                                }
                            }
                        }
                    }
                    con.Clone();
                }
                this.Programs = programs;
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
            }
        }
        private void ReadRecodringScheduleData()
        {
            try
            {
                List<IRECORDING_SCHEDULE> recordings = new List<IRECORDING_SCHEDULE>();
                using (SQLiteConnection con = CreateConnection())
                {
                    con.Open();
                    string command_text = string.Format("select * from {0};", TABLES.RECORDING_SCHEDULE);
                    gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
                    using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
                    {
                        using (SQLiteDataReader r = cmd.ExecuteReader())
                        {
                            if (!r.HasRows)
                            {
                                gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
                            }
                            else
                            {
                                while (r.Read())
                                {
                                    RECORDING_SCHEDULE recording = new RECORDING_SCHEDULE();
                                    for (int i = 0; i < r.FieldCount; i++)
                                    {
                                        RECORDING_SCHEDULE.Create(ref recording, r, i);
                                    }

                                    recordings.Add(recording);
                                }
                            }
                        }
                    }
                    con.Clone();
                }
                this.Recordings = recordings;
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
            }
        }
    }
}
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	if (!CreateDatabaseBackup())
194	{
195	gLog.Error.WriteLine("Failed to backup database.");
196	return;
197	}
198	ConnectionTest();
199	ReadRecodringScheduleData();
200	ReadProgrammeData();
201	OnCreatedInstance(this, new EventArgs());
202	}
203
204	[NonSerialized]
205	private EventHandler<EventArgs> _OnInstanceCreated;
206	private EventHandler<EventArgs> OnInstanceCreated { get { return _OnInstanceCreated; } set { _OnInstanceCreated = value; } }
207
208	private void OnCreatedInstance(object sender, EventArgs e)
209	{
210	if (OnInstanceCreated != null) { OnInstanceCreated.Invoke(sender, e); }
211	}
212
213	#region SQLLite
214	private string Database;
215	public List<IPROGRAMME> Programs { get; private set; }
216	public List<IRECORDING_SCHEDULE> Recordings { get; private set; }
217	#endregion
218
219
220	private string CreateConnectionString()
221	{
222	string connection_string = string.Format("Data Source={0}", this.Database);
223	return connection_string;
224	}
225
226	private SQLiteConnection CreateConnection() { SQLiteConnection connection = new SQLiteConnection(CreateConnectionString()); return connection; }
227
228	[Conditional("SQLLITE_CONNECTION_TEST")]
229	private void ConnectionTest()
230	{
231	try
232	{
233	using (SQLiteConnection con = CreateConnection())
234	{
235	con.Open();
236	string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
237	gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
238	using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
239	{
240	using (SQLiteDataReader r = cmd.ExecuteReader())
241	{
242	if (!r.HasRows)
243	{
244	gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
245	}
246	else
247	{
248	while (r.Read())
249	{
250	for (int i = 0; i < r.FieldCount; i++)
251	{
252	string field_name = r.GetName(i);
253	Type field_type = r.GetFieldType(i);
254	object field_value = r.GetValue(i);
255	gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
256	}
257	break;
258	}
259	}
260	}
261	}
262	con.Clone();
263	}
264	OnCreatedInstance(this, new EventArgs());
265	}
266	catch (Exception ex)
267	{
268	gLog.Error.WriteLine(ex.ToString());
269	}
270	}
271
272
273	private bool CreateDatabaseBackup()
274	{
275	try
276	{
277	string backup_file = string.Format("{0}.{1}", this.Database, DateTime.Now.ToString("yyyyMMddHHmmss"));
278	gLog.Info.WriteLine("Creating Database backup...");
279	gLog.Info.WriteLine("\tSource: {0}", this.Database);
280	gLog.Info.WriteLine("\tDestination: {0}", backup_file);
281
282	System.IO.File.Copy(this.Database, backup_file);
283	return true;
284	}
285	catch (Exception ex)
286	{
287	gLog.Error.WriteLine(ex.ToString());
288	return false;
289	}
290	}
291
292	private void ReadProgrammeData()
293	{
294	try
295	{
296	List<IPROGRAMME> programs = new List<IPROGRAMME>();
297	using (SQLiteConnection con = CreateConnection())
298	{
299	con.Open();
300	string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
301	gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
302	using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
303	{
304	using (SQLiteDataReader r = cmd.ExecuteReader())
305	{
306	if (!r.HasRows)
307	{
308	gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
309	}
310	else
311	{
312	while (r.Read())
313	{
314	PROGRAMME program = new PROGRAMME();
315	for (int i = 0; i < r.FieldCount; i++)
316	{
317	PROGRAMME.Create(ref program, r, i);
318	}
319	programs.Add(program);
320	}
321	}
322	}
323	}
324	con.Clone();
325	}
326	this.Programs = programs;
327	}
328	catch (Exception ex)
329	{
330	gLog.Error.WriteLine(ex.ToString());
331	}
332	}
333	private void ReadRecodringScheduleData()
334	{
335	try
336	{
337	List<IRECORDING_SCHEDULE> recordings = new List<IRECORDING_SCHEDULE>();
338	using (SQLiteConnection con = CreateConnection())
339	{
340	con.Open();
341	string command_text = string.Format("select * from {0};", TABLES.RECORDING_SCHEDULE);
342	gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
343	using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
344	{
345	using (SQLiteDataReader r = cmd.ExecuteReader())
346	{
347	if (!r.HasRows)
348	{
349	gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
350	}
351	else
352	{
353	while (r.Read())
354	{
355	RECORDING_SCHEDULE recording = new RECORDING_SCHEDULE();
356	for (int i = 0; i < r.FieldCount; i++)
357	{
358	RECORDING_SCHEDULE.Create(ref recording, r, i);
359	}
360
361	recordings.Add(recording);
362	}
363	}
364	}
365	}
366	con.Clone();
367	}
368	this.Recordings = recordings;
369	}
370	catch (Exception ex)
371	{
372	gLog.Error.WriteLine(ex.ToString());
373	}
374	}
375	}
376	}