protected internal static Bitmap RetrieveImage(int imgid)
{
using (SQLiteCommand command = new SQLiteCommand("select image from images where imgid=@imgid", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@imgid", imgid));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
if (!reader.Read())
{
return(null);
}
// Get the size of the image data by passing nothing to getbytes
int dataLength = (int)reader.GetBytes(reader.GetOrdinal("image"), 0, null, 0, 0);
byte[] content = new byte[dataLength];
reader.GetBytes(reader.GetOrdinal("image"), 0, content, 0, dataLength);
using (MemoryStream contentStream = new MemoryStream(content))
{
using (Bitmap streamBitmap = new Bitmap(contentStream))
{
return(new Bitmap(streamBitmap));
}
}
}
}
}
protected internal static Bitmap RetrieveImage(int imgid)
{
using (SQLiteCommand command = new SQLiteCommand("select image from images where imgid=@imgid", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@imgid", imgid));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
if (!reader.Read())
{
return null;
}
// Get the size of the image data by passing nothing to getbytes
int dataLength = (int)reader.GetBytes(reader.GetOrdinal("image"), 0, null, 0, 0);
byte[] content = new byte[dataLength];
reader.GetBytes(reader.GetOrdinal("image"), 0, content, 0, dataLength);
using (MemoryStream contentStream = new MemoryStream(content))
{
using (Bitmap streamBitmap = new Bitmap(contentStream))
{
return new Bitmap(streamBitmap);
}
}
}
}
}
private static void ResumeDownloadsAsync()
{
List <int> epids = new List <int>();
lock (downloads)
{
using (SQLiteCommand command = new SQLiteCommand("select episodes.epid from downloads, episodes where downloads.epid=episodes.epid and status=@statuswait order by date", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@statuswait", Model.Download.DownloadStatus.Waiting));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
int epidOrdinal = reader.GetOrdinal("epid");
while (reader.Read())
{
epids.Add(reader.GetInt32(epidOrdinal));
}
}
}
if (epids.Count > 0)
{
AddDownloads(epids.ToArray());
}
}
RetryErrored();
}
private static void RetryErrored()
{
List <int> epids = new List <int>();
using (SQLiteCommand command = new SQLiteCommand("select epid from downloads where status=@statuserr and errortime < datetime('now', '-' || (1 << errorcount) || ' hours')", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@statuserr", Model.Download.DownloadStatus.Errored));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
int epidOrdinal = reader.GetOrdinal("epid");
while (reader.Read())
{
epids.Add(reader.GetInt32(epidOrdinal));
}
}
}
foreach (int epid in epids)
{
Model.Download.ResetAsync(epid, true);
}
ThreadPool.QueueUserWorkItem(delegate
{
// Wait for 30 minutes, and check again
Thread.Sleep(1800000);
RetryErrored();
});
}
/// <summary>
/// Initializes static members of the <see cref="TempFile"/> class.
/// Any file names still listed in the database are attempted to be deleted.
/// </summary>
static TempFile()
{
lock (notInUse)
{
using (SQLiteCommand command = new SQLiteCommand("select filepath from tempfiles", FetchDbConn()))
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
int filePathOrdinal = reader.GetOrdinal("filepath");
while (reader.Read())
{
notInUse.Add(reader.GetString(filePathOrdinal));
}
}
}
DeleteFiles();
}
protected internal static int?StoreImage(Image image)
{
if (image == null)
{
return(null);
}
// Convert the image into a byte array
byte[] imageAsBytes = null;
using (MemoryStream memstream = new MemoryStream())
{
image.Save(memstream, System.Drawing.Imaging.ImageFormat.Png);
imageAsBytes = memstream.ToArray();
}
lock (DbUpdateLock)
{
using (SQLiteCommand command = new SQLiteCommand("select imgid from images where image=@image", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@image", imageAsBytes));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
if (reader.Read())
{
return(reader.GetInt32(reader.GetOrdinal("imgid")));
}
}
}
using (SQLiteCommand command = new SQLiteCommand("insert into images (image) values (@image)", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@image", imageAsBytes));
command.ExecuteNonQuery();
}
using (SQLiteCommand command = new SQLiteCommand("select last_insert_rowid()", FetchDbConn()))
{
return((int)(long)command.ExecuteScalar());
}
}
}
/// <summary>
/// Initializes static members of the <see cref="TempFile"/> class and cleans up
/// any left-over files from previous sessions. Any file names still listed in
/// the database are added to the notInUse list and are attempted to be deleted.
/// </summary>
static TempFile()
{
lock (notInUse)
{
using (SQLiteCommand command = new SQLiteCommand("select filepath from tempfiles", FetchDbConn()))
{
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
int filePathOrdinal = reader.GetOrdinal("filepath");
while (reader.Read())
{
notInUse.Add(reader.GetString(filePathOrdinal));
}
}
}
}
DeleteFiles();
}
protected static string GetValue(string propertyName)
{
if (string.IsNullOrEmpty(propertyName))
{
throw new ArgumentException("Property name for value must be specified", "propertyName");
}
using (SQLiteCommand command = new SQLiteCommand("select value from settings where property=@property", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@property", propertyName));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
if (!reader.Read())
{
return(null);
}
return(reader.GetString(reader.GetOrdinal("value")));
}
}
}
protected internal static int?StoreImage(CompressedImage image)
{
if (image == null)
{
return(null);
}
byte[] imageAsBytes = image.GetBytes();
lock (DbUpdateLock)
{
using (SQLiteCommand command = new SQLiteCommand("select imgid from images where image=@image", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@image", imageAsBytes));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
if (reader.Read())
{
return(reader.GetInt32(reader.GetOrdinal("imgid")));
}
}
}
using (SQLiteCommand command = new SQLiteCommand("insert into images (image) values (@image)", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@image", imageAsBytes));
command.ExecuteNonQuery();
}
using (SQLiteCommand command = new SQLiteCommand("select last_insert_rowid()", FetchDbConn()))
{
return((int)(long)command.ExecuteScalar());
}
}
}
private static void UpdateStructure(SQLiteConnection specConn, SQLiteConnection updateConn)
{
using (SQLiteCommand specCommand = new SQLiteCommand("select name, sql from sqlite_master where type='table'", specConn))
{
using (SQLiteCommand checkUpdateCmd = new SQLiteCommand("select sql from sqlite_master where type='table' and name=@name", updateConn))
{
SQLiteParameter nameParam = new SQLiteParameter("@name");
checkUpdateCmd.Parameters.Add(nameParam);
using (SQLiteMonDataReader specReader = new SQLiteMonDataReader(specCommand.ExecuteReader()))
{
int nameOrd = specReader.GetOrdinal("name");
int sqlOrd = specReader.GetOrdinal("sql");
while (specReader.Read())
{
string specName = specReader.GetString(nameOrd);
string specSql = specReader.GetString(sqlOrd);
nameParam.Value = specName;
UpdateType updateReqd;
using (SQLiteMonDataReader checkUpdateRdr = new SQLiteMonDataReader(checkUpdateCmd.ExecuteReader()))
{
if (!checkUpdateRdr.Read())
{
// The table doesn't exist
updateReqd = UpdateType.Create;
}
else
{
if (specSql == checkUpdateRdr.GetString(checkUpdateRdr.GetOrdinal("sql")))
{
// The table does not require an update
updateReqd = UpdateType.None;
}
else
{
// The structure of the table doesn't match, so update it
updateReqd = UpdateType.Update;
}
}
}
if (updateReqd == UpdateType.Create)
{
// Create the table
using (SQLiteCommand updateCommand = new SQLiteCommand(specSql, updateConn))
{
updateCommand.ExecuteNonQuery();
}
}
else if (updateReqd == UpdateType.Update)
{
// Fetch a list of common column names for transferring the data
string columnNames = ColNames(specConn, updateConn, specName);
// Rename the existing table to table_name_old
using (SQLiteCommand updateCommand = new SQLiteCommand("alter table [" + specName + "] rename to [" + specName + "_old]", updateConn))
{
updateCommand.ExecuteNonQuery();
}
// Create the new table with the correct structure
using (SQLiteCommand updateCommand = new SQLiteCommand(specSql, updateConn))
{
updateCommand.ExecuteNonQuery();
}
// Copy across the data (discarding rows which violate any new constraints)
if (!string.IsNullOrEmpty(columnNames))
{
using (SQLiteCommand updateCommand = new SQLiteCommand("insert or ignore into [" + specName + "] (" + columnNames + ") select " + columnNames + " from [" + specName + "_old]", updateConn))
{
updateCommand.ExecuteNonQuery();
}
}
// Delete the old table
using (SQLiteCommand updateCommand = new SQLiteCommand("drop table [" + specName + "_old]", updateConn))
{
updateCommand.ExecuteNonQuery();
}
}
}
}
}
}
}
private static void RetryErrored()
{
List<int> epids = new List<int>();
using (SQLiteCommand command = new SQLiteCommand("select epid from downloads where status=@statuserr and errortime < datetime('now', '-' || (1 << errorcount) || ' hours')", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@statuserr", Model.Download.DownloadStatus.Errored));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
int epidOrdinal = reader.GetOrdinal("epid");
while (reader.Read())
{
epids.Add(reader.GetInt32(epidOrdinal));
}
}
}
foreach (int epid in epids)
{
Model.Download.ResetAsync(epid, true);
}
ThreadPool.QueueUserWorkItem(delegate
{
// Wait for 30 minutes, and check again
Thread.Sleep(1800000);
RetryErrored();
});
}
private static void ResumeDownloadsAsync()
{
List<int> epids = new List<int>();
lock (downloads)
{
using (SQLiteCommand command = new SQLiteCommand("select episodes.epid from downloads, episodes where downloads.epid=episodes.epid and status=@statuswait order by date", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@statuswait", Model.Download.DownloadStatus.Waiting));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
int epidOrdinal = reader.GetOrdinal("epid");
while (reader.Read())
{
epids.Add(reader.GetInt32(epidOrdinal));
}
}
}
if (epids.Count > 0)
{
AddDownloads(epids.ToArray());
}
}
RetryErrored();
}
public DownloadHandler(int epid)
{
using (SQLiteCommand command = new SQLiteCommand("select pr.progid, pluginid, pr.image as progimg, ep.duration, ep.image as epimg, pr.extid as progextid, ep.extid as epextid from episodes as ep, programmes as pr where ep.epid=@epid and ep.progid=pr.progid", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@epid", epid));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
if (!reader.Read())
{
throw new DataNotFoundException(epid, "Episode does not exist");
}
this.pluginId = new Guid(reader.GetString(reader.GetOrdinal("pluginid")));
this.progExtId = reader.GetString(reader.GetOrdinal("progextid"));
this.episodeExtId = reader.GetString(reader.GetOrdinal("epextid"));
this.progInfo = new Model.Programme(reader.GetInt32(reader.GetOrdinal("progid")));
this.episodeInfo = new Model.Episode(epid);
this.providerProgInfo = new ProgrammeInfo(this.progInfo);
if (reader.IsDBNull(reader.GetOrdinal("progimg")))
{
this.providerProgInfo.Image = null;
}
else
{
this.providerProgInfo.Image = Database.RetrieveImage(reader.GetInt32(reader.GetOrdinal("progimg")));
}
this.providerEpisodeInfo = new EpisodeInfo(this.episodeInfo);
if (reader.IsDBNull(reader.GetOrdinal("duration")))
{
this.providerEpisodeInfo.Duration = null;
}
else
{
this.providerEpisodeInfo.Duration = reader.GetInt32(reader.GetOrdinal("duration"));
}
if (reader.IsDBNull(reader.GetOrdinal("epimg")))
{
this.providerEpisodeInfo.Image = null;
}
else
{
this.providerEpisodeInfo.Image = Database.RetrieveImage(reader.GetInt32(reader.GetOrdinal("epimg")));
}
using (SQLiteCommand extCommand = new SQLiteCommand("select name, value from episodeext where epid=@epid", FetchDbConn()))
{
extCommand.Parameters.Add(new SQLiteParameter("@epid", epid));
using (SQLiteMonDataReader extReader = new SQLiteMonDataReader(extCommand.ExecuteReader()))
{
while (extReader.Read())
{
this.providerEpisodeInfo.ExtInfo.Add(extReader.GetString(extReader.GetOrdinal("name")), extReader.GetString(extReader.GetOrdinal("value")));
}
}
}
}
}
}
private static void UpdateStructure(SQLiteConnection specConn, SQLiteConnection updateConn)
{
using (SQLiteCommand specCommand = new SQLiteCommand("select name, sql from sqlite_master where type='table'", specConn))
{
using (SQLiteCommand checkUpdateCmd = new SQLiteCommand("select sql from sqlite_master where type='table' and name=@name", updateConn))
{
SQLiteParameter nameParam = new SQLiteParameter("@name");
checkUpdateCmd.Parameters.Add(nameParam);
using (SQLiteMonDataReader specReader = new SQLiteMonDataReader(specCommand.ExecuteReader()))
{
int nameOrd = specReader.GetOrdinal("name");
int sqlOrd = specReader.GetOrdinal("sql");
while (specReader.Read())
{
string specName = specReader.GetString(nameOrd);
string specSql = specReader.GetString(sqlOrd);
nameParam.Value = specName;
UpdateType updateReqd;
using (SQLiteMonDataReader checkUpdateRdr = new SQLiteMonDataReader(checkUpdateCmd.ExecuteReader()))
{
if (!checkUpdateRdr.Read())
{
// The table doesn't exist
updateReqd = UpdateType.Create;
}
else
{
if (specSql == checkUpdateRdr.GetString(checkUpdateRdr.GetOrdinal("sql")))
{
// The table does not require an update
updateReqd = UpdateType.None;
}
else
{
// The structure of the table doesn't match, so update it
updateReqd = UpdateType.Update;
}
}
}
if (updateReqd == UpdateType.Create)
{
// Create the table
using (SQLiteCommand updateCommand = new SQLiteCommand(specSql, updateConn))
{
updateCommand.ExecuteNonQuery();
}
}
else if (updateReqd == UpdateType.Update)
{
// Fetch a list of common column names for transferring the data
string columnNames = ColNames(specConn, updateConn, specName);
// Rename the existing table to table_name_old
using (SQLiteCommand updateCommand = new SQLiteCommand("alter table [" + specName + "] rename to [" + specName + "_old]", updateConn))
{
updateCommand.ExecuteNonQuery();
}
// Create the new table with the correct structure
using (SQLiteCommand updateCommand = new SQLiteCommand(specSql, updateConn))
{
updateCommand.ExecuteNonQuery();
}
// Copy across the data (discarding rows which violate any new constraints)
if (!string.IsNullOrEmpty(columnNames))
{
using (SQLiteCommand updateCommand = new SQLiteCommand("insert or ignore into [" + specName + "] (" + columnNames + ") select " + columnNames + " from [" + specName + "_old]", updateConn))
{
updateCommand.ExecuteNonQuery();
}
}
// Delete the old table
using (SQLiteCommand updateCommand = new SQLiteCommand("drop table [" + specName + "_old]", updateConn))
{
updateCommand.ExecuteNonQuery();
}
}
}
}
}
}
}
protected internal static int? StoreImage(Image image)
{
if (image == null)
{
return null;
}
// Convert the image into a byte array
byte[] imageAsBytes = null;
using (MemoryStream memstream = new MemoryStream())
{
image.Save(memstream, System.Drawing.Imaging.ImageFormat.Png);
imageAsBytes = memstream.ToArray();
}
lock (DbUpdateLock)
{
using (SQLiteCommand command = new SQLiteCommand("select imgid from images where image=@image", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@image", imageAsBytes));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
if (reader.Read())
{
return reader.GetInt32(reader.GetOrdinal("imgid"));
}
}
}
using (SQLiteCommand command = new SQLiteCommand("insert into images (image) values (@image)", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@image", imageAsBytes));
command.ExecuteNonQuery();
}
using (SQLiteCommand command = new SQLiteCommand("select last_insert_rowid()", FetchDbConn()))
{
return (int)(long)command.ExecuteScalar();
}
}
}
public DownloadHandler(int epid)
{
using (SQLiteCommand command = new SQLiteCommand("select pr.progid, pluginid, pr.image as progimg, ep.duration, ep.image as epimg, pr.extid as progextid, ep.extid as epextid from episodes as ep, programmes as pr where ep.epid=@epid and ep.progid=pr.progid", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@epid", epid));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
if (!reader.Read())
{
throw new DataNotFoundException(epid, "Episode does not exist");
}
this.pluginId = new Guid(reader.GetString(reader.GetOrdinal("pluginid")));
this.progExtId = reader.GetString(reader.GetOrdinal("progextid"));
this.episodeExtId = reader.GetString(reader.GetOrdinal("epextid"));
this.progInfo = new Model.Programme(reader.GetInt32(reader.GetOrdinal("progid")));
this.episodeInfo = new Model.Episode(epid);
this.providerProgInfo = new Provider.ProgrammeInfo(this.progInfo);
if (reader.IsDBNull(reader.GetOrdinal("progimg")))
{
this.providerProgInfo.Image = null;
}
else
{
this.providerProgInfo.Image = RetrieveImage(reader.GetInt32(reader.GetOrdinal("progimg")));
}
this.providerEpisodeInfo = new Provider.EpisodeInfo(this.episodeInfo);
if (reader.IsDBNull(reader.GetOrdinal("duration")))
{
this.providerEpisodeInfo.Duration = null;
}
else
{
this.providerEpisodeInfo.Duration = reader.GetInt32(reader.GetOrdinal("duration"));
}
if (reader.IsDBNull(reader.GetOrdinal("epimg")))
{
this.providerEpisodeInfo.Image = null;
}
else
{
this.providerEpisodeInfo.Image = RetrieveImage(reader.GetInt32(reader.GetOrdinal("epimg")));
}
using (SQLiteCommand extCommand = new SQLiteCommand("select name, value from episodeext where epid=@epid", FetchDbConn()))
{
extCommand.Parameters.Add(new SQLiteParameter("@epid", epid));
using (SQLiteMonDataReader extReader = new SQLiteMonDataReader(extCommand.ExecuteReader()))
{
while (extReader.Read())
{
this.providerEpisodeInfo.ExtInfo.Add(extReader.GetString(extReader.GetOrdinal("name")), extReader.GetString(extReader.GetOrdinal("value")));
}
}
}
}
}
}
protected static string GetValue(string propertyName)
{
if (string.IsNullOrEmpty(propertyName))
{
throw new ArgumentException("Property name for value must be specified", "propertyName");
}
using (SQLiteCommand command = new SQLiteCommand("select value from settings where property=@property", FetchDbConn()))
{
command.Parameters.Add(new SQLiteParameter("@property", propertyName));
using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
{
if (!reader.Read())
{
return null;
}
return reader.GetString(reader.GetOrdinal("value"));
}
}
}
