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;
using libxmltv;
namespace GBPVRProgramDatabaseFixer
{
    public interface ISQLLITE
    {
        List<SQLLITE.IPROGRAMME> Programs { get; }
        List<SQLLITE.IRECORDING_SCHEDULE> Recordings { get; }
        List<SQLLITE.ICHANNEL> Channels { get; }
        IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs);
        List<SQLLITE.IPROGRAMME> FilterProgramsByDateRange(List<SQLLITE.IPROGRAMME> programs, IDateTimeRange range);

    }
    public class SQLLITE : ISQLLITE
    {
        public static ISQLLITE Create(string database, EventHandler<EventArgs> OnInstanceCreated)
        {
            return new SQLLITE(database, OnInstanceCreated);
        }

        #region DATABASE DEFINITIONS

        public interface ICHANNEL
        {
            Int64 oid { get; }
            String name { get; }
            String channelID { get; }
            Int64 channel_number { get; }
            String favourite_channel { get; }
            String display_name { get; }
        }
        private class CHANNEL : ICHANNEL
        {
            public CHANNEL() 
            {
                BaseDatabaseDefinition<CHANNEL>.CreateDefault(this); 
            }
            //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }

            public static void Create(ref CHANNEL instance, SQLiteDataReader r, int index)
            {
                BaseDatabaseDefinition<CHANNEL>.Create(ref instance, r, index);
            }
            #region ICHANNEL members
            public Int64 oid { get; set; }
            public String name { get; set; }
            public String channelID { get; set; }
            public Int64 channel_number { get; set; }
            public String favourite_channel { get; set; }
            public String display_name { get; set; }
            #endregion
        }
        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 const string CHANNEL = "CHANNEL";
        }

        //public SQLLite() { }
        protected 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();
            ReadChannelData();
            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); }
        }

        private string Database;
        #region ISQLLITE members        
        public List<IPROGRAMME> Programs { get; private set; }
        public List<IRECORDING_SCHEDULE> Recordings { get; private set; }
        public List<ICHANNEL> Channels { get; private set; }

        public IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs)
        {
            var list = new List<IPROGRAMME>(programs.ToArray());
            DateTime first = new DateTime();
            DateTime last = new DateTime();
            first = list.OrderBy(s => s.start_time).ToList().First().start_time;            
            last = list.OrderBy(s => s.start_time).ToList().Last().start_time;
            gLog.Info.WriteLine("\tFirst: {0} = ({1})", first.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), first.ToDateTimeString());
            gLog.Info.WriteLine("\tLast: {0} = ({1})", last.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), last.ToDateTimeString());
            var range = DateTimeRange.Create(first, last);
            return range;
        }

        public List<SQLLITE.IPROGRAMME> FilterProgramsByDateRange(List<SQLLITE.IPROGRAMME> programs, IDateTimeRange range)
        {
            List<SQLLITE.IPROGRAMME> list = new List<IPROGRAMME>(programs.ToArray());
            list = list.OrderBy(s => s.start_time).ToList();
            List<SQLLITE.IPROGRAMME> list2 = new List<IPROGRAMME>();
            if (range == null)
            {
                gLog.Warn.WriteLine("The DateTimeRange passed in is null...returning the original program list");
                return list;
            }
            //gLog.Warn.WriteLine("FilterProgramsByDateRange has not been implemented");
           
            foreach (var program in list)
            {
                if (program.start_time >= range.Start && 
                    program.start_time <= range.End)
                {
                    list2.Add(program);
                }
            }
            list2 = list2.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
            return list2;
        }
        #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.CHANNEL);
                    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 ReadChannelData()
        {
            try
            {
                List<ICHANNEL> channels = new List<ICHANNEL>();
                using (SQLiteConnection con = CreateConnection())
                {
                    con.Open();
                    string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
                    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())
                                {
                                    CHANNEL channel = new CHANNEL();
                                    for (int i = 0; i < r.FieldCount; i++)
                                    {
                                        CHANNEL.Create(ref channel, r, i);
                                    }
                                    channels.Add(channel);
                                }
                            }
                        }
                    }
                    con.Clone();
                }
                this.Channels = channels;
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
            }
        }
        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	using libxmltv;
10	namespace GBPVRProgramDatabaseFixer
11	{
12	public interface ISQLLITE
13	{
14	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	#region DATABASE DEFINITIONS
29
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	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	public const string CHANNEL = "CHANNEL";
231	}
232
233	//public SQLLite() { }
234	protected SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated)
235	{
236	this.OnInstanceCreated = OnInstanceCreated;
237	//CreateConnection(database);
238	this.Database = database;
239	if (!CreateDatabaseBackup())
240	{
241	gLog.Error.WriteLine("Failed to backup database.");
242	return;
243	}
244	ConnectionTest();
245	ReadChannelData();
246	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	#region ISQLLITE members
262	public List<IPROGRAMME> Programs { get; private set; }
263	public List<IRECORDING_SCHEDULE> Recordings { get; private set; }
264	public List<ICHANNEL> Channels { get; private set; }
265
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	#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	string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
322	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	con.Clone();
348	}
349	OnCreatedInstance(this, new EventArgs());
350	}
351	catch (Exception ex)
352	{
353	gLog.Error.WriteLine(ex.ToString());
354	}
355	}
356
357
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	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	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
500	}
501	}