public static DBView[] getTaggedViews()
{
try {
// Make sure the table is created - create a dummy object
DBView dummy = new DBView();
// Only get Tagged Views
String sqlQuery = "select * from " + cTableName + " where " + cTaggedView + " = 1";
SQLiteResultSet results = DBTVSeries.Execute(sqlQuery);
if (results.Rows.Count > 0)
{
DBView[] views = new DBView[results.Rows.Count];
for (int index = 0; index < results.Rows.Count; index++)
{
views[index] = new DBView();
views[index].Read(ref results, index);
}
return(views);
}
}
catch (Exception ex) {
MPTVSeriesLog.Write("Error in retrieving Tagged Views (" + ex.Message + ").");
}
return(new DBView[0]);
}
public static DBImportPath[] GetAll()
{
try
{
// make sure the table is created - create a dummy object
DBImportPath dummy = new DBImportPath();
// retrieve all fields in the table
String sqlQuery = "select * from " + cTableName + " order by " + cIndex;
SQLiteResultSet results = DBTVSeries.Execute(sqlQuery);
if (results.Rows.Count > 0)
{
DBImportPath[] importPathes = new DBImportPath[results.Rows.Count];
for (int index = 0; index < results.Rows.Count; index++)
{
importPathes[index] = new DBImportPath();
importPathes[index].Read(ref results, index);
}
return(importPathes);
}
}
catch (Exception ex)
{
MPTVSeriesLog.Write("Error in DBImportPath.Get (" + ex.Message + ").");
}
return(null);
}
/// <summary>
/// Check if column exists in Database table
/// </summary>
/// <param name="table"></param>
/// <param name="field"></param>
/// <returns></returns>
public static bool ColumnExists(string table, string field)
{
string sql = "select * from sqlite_master where type='table' and tbl_name='" + table + "' and sql like '%" + field + "%'";
SQLiteResultSet results = DBTVSeries.Execute(sql);
return(results.Rows.Count > 0);
}
public static DBView[] getAll(bool includeDisabled)
{
try
{
// make sure the table is created - create a dummy object
DBView dummy = new DBView();
// retrieve all fields in the table
String sqlQuery = "select * from " + cTableName +
(includeDisabled ? string.Empty : " where " + cEnabled + " = 1")
+ " order by " + cSort;
SQLiteResultSet results = DBTVSeries.Execute(sqlQuery);
if (results.Rows.Count > 0)
{
DBView[] views = new DBView[results.Rows.Count];
for (int index = 0; index < results.Rows.Count; index++)
{
views[index] = new DBView();
views[index].Read(ref results, index);
}
return(views);
}
}
catch (Exception ex)
{
MPTVSeriesLog.Write("Error in DBView.Get (" + ex.Message + ").");
}
return(new DBView[0]);
}
public static void ClearAll()
{
String sqlQuery = "delete from " + cTableName;
cache = null;
DBTVSeries.Execute(sqlQuery);
}
public static bool SetOptions(string property, DBValue value)
{
try
{
lock (thisLock)
{
if (!optionsCache.ContainsKey(property) || optionsCache[property] != value)
{
// ensure our sql query will be using a valid string
string sqlQuery;
string convertedProperty = property;
string convertedValue = value.ToString().Replace("'", "''");
if (GetOptions(property) == null)
{
sqlQuery = "INSERT INTO options (option_id, property, value) VALUES(NULL, '" + convertedProperty + "', '" + convertedValue + "')";
}
else
{
sqlQuery = "UPDATE options SET value = '" + convertedValue + "' WHERE property = '" + convertedProperty + "'";
}
optionsCache[property] = value;
DBTVSeries.Execute(sqlQuery);
}
}
return(true);
}
catch (Exception ex)
{
MPTVSeriesLog.Write("An error occurred (" + ex.Message + ").");
return(false);
}
}
public static void ClearAll()
{
String sqlQuery = "delete from " + cTableName;
DBTVSeries.Execute(sqlQuery);
_includesNetworkShares = false;
}
public static List <DBFanart> GetAll(int SeriesID, bool availableOnly)
{
lock (cache)
{
if (SeriesID < 0)
{
return(new List <DBFanart>());
}
if (cache == null || !cache.ContainsKey(SeriesID))
{
try
{
// make sure the table is created - create a dummy object
DBFanart dummy = new DBFanart();
// retrieve all fields in the table
String sqlQuery = "select * from " + cTableName;
sqlQuery += " where " + cSeriesID + " = " + SeriesID.ToString();
if (availableOnly)
{
sqlQuery += " and " + cLocalPath + " != ''";
}
sqlQuery += " order by " + cIndex;
SQLiteResultSet results = DBTVSeries.Execute(sqlQuery);
if (results.Rows.Count > 0)
{
List <DBFanart> ourFanart = new List <DBFanart>(results.Rows.Count);
for (int index = 0; index < results.Rows.Count; index++)
{
ourFanart.Add(new DBFanart());
ourFanart[index].Read(ref results, index);
}
if (cache == null)
{
cache = new Dictionary <int, List <DBFanart> >();
}
cache.Add(SeriesID, ourFanart);
}
MPTVSeriesLog.Write("Found " + results.Rows.Count + " fanart from database", MPTVSeriesLog.LogLevel.Debug);
}
catch (Exception ex)
{
MPTVSeriesLog.Write("Error in DBFanart.Get (" + ex.Message + ").");
}
}
List <DBFanart> faForSeries = null;
if (cache != null && cache.TryGetValue(SeriesID, out faForSeries))
{
return(faForSeries);
}
return(new List <DBFanart>());
}
}
public static void Clear(DBTable obj, SQLCondition conditions)
{
String sqlQuery = "delete from " + obj.m_tableName + conditions;
SQLiteResultSet results = DBTVSeries.Execute(sqlQuery);
if (dbUpdateOccured != null)
{
dbUpdateOccured(obj.m_tableName);
}
}
public static void GlobalSet(DBTable obj, String sKey1, String sKey2, SQLCondition conditions)
{
if (obj.m_fields.ContainsKey(sKey1) && obj.m_fields.ContainsKey(sKey2))
{
String sqlQuery = "update " + obj.m_tableName + " SET " + sKey1 + " = " + sKey2 + conditions;
SQLiteResultSet results = DBTVSeries.Execute(sqlQuery);
if (dbUpdateOccured != null)
{
dbUpdateOccured(obj.m_tableName);
}
}
}
public virtual bool AddColumn(String sName, DBField field)
{
// verify if we already have that field avail
if (!m_fields.ContainsKey(sName))
{
if (m_fields.Count == 0 && !field.Primary)
{
throw new Exception("First field added needs to be the index");
}
try
{
// ok, we don't, add it
SQLiteResultSet results;
results = DBTVSeries.Execute("SELECT name FROM sqlite_master WHERE name='" + m_tableName + "'");
if (results != null && results.Rows.Count > 0)
{
// table already exists, alter it
String sQuery = "ALTER TABLE " + m_tableName + " ADD " + sName + " " + field.Type;
DBTVSeries.Execute(sQuery);
}
else
{
// new table, create it
// no tables, assume it's going to be created later (using AddColumn)
string type = field.Type.ToString();
if (field.Primary && field.Type == DBField.cType.Int && field.AutoIncrement)
{
//for the automatic creation of an auto incremental integer primary key you must use the full "Integer" not just "int"
type = "Integer";
}
String sQuery = "CREATE TABLE " + m_tableName + " (" + sName + " " + type + (field.Primary ? " primary key)" : ")");
DBTVSeries.Execute(sQuery);
}
// delete the s_fields cache so newed up objects get the right fields
lock (fields)
fields.Remove(m_tableName);
m_fields.Add(sName, field);
return(true);
}
catch (Exception ex)
{
MPTVSeriesLog.Write(m_tableName + " table.AddColumn failed (" + ex.Message + ").");
return(false);
}
}
return(false);
}
private static void CreateTable()
{
try
{
SQLiteResultSet results = DBTVSeries.Execute("SELECT name FROM sqlite_master WHERE name='options' and type='table' UNION ALL SELECT name FROM sqlite_temp_master WHERE type='table' ORDER BY name");
if (results == null || results.Rows.Count == 0)
{
// no table, create it
DBTVSeries.Execute("CREATE TABLE options (option_id integer primary key, property text, value text)");
}
}
catch (Exception ex)
{
MPTVSeriesLog.Write("DBOption.CreateTable failed (" + ex.Message + ").");
}
}
public static IEnumerable <DBFormatting> GetAll(bool includeDisabled)
{
if (cache == null || Settings.isConfig)
{
try
{
// make sure the table is created - create a dummy object
DBFormatting dummy = new DBFormatting();
// retrieve all fields in the table
String sqlQuery = "select * from " + cTableName;
if (!includeDisabled)
{
sqlQuery += " where " + cEnabled + " = 1";
}
sqlQuery += " order by " + cIndex;
SQLiteResultSet results = DBTVSeries.Execute(sqlQuery);
if (results.Rows.Count > 0)
{
cache = new DBFormatting[results.Rows.Count];
for (int index = 0; index < results.Rows.Count; index++)
{
cache[index] = new DBFormatting();
cache[index].Read(ref results, index);
}
}
MPTVSeriesLog.Write("Found and loaded " + results.Rows.Count + " User Formatting Rules", MPTVSeriesLog.LogLevel.Debug);
if (results.Rows.Count == 0)
{
cache = new DBFormatting[0];
}
}
catch (Exception ex)
{
MPTVSeriesLog.Write("Error in DBFormatting.Get (" + ex.Message + ").");
}
}
if (cache != null)
{
for (int i = 0; i < cache.Length; i++)
{
yield return(cache[i]);
}
}
}
public bool ReadPrimary(DBValue Value)
{
try
{
m_fields[PrimaryKey()].Value = Value;
SQLCondition condition = new SQLCondition();
condition.Add(this, PrimaryKey(), m_fields[PrimaryKey()].Value, SQLConditionType.Equal);
String sqlQuery = "select * from " + m_tableName + condition;
SQLiteResultSet records = DBTVSeries.Execute(sqlQuery);
return(Read(ref records, 0));
}
catch (Exception ex)
{
MPTVSeriesLog.Write("An Error Occurred (" + ex.Message + ").");
}
return(false);
}
public static List <DBValue> GetSingleField(string field, SQLCondition conds, DBTable obj)
{
string sql = "select " + field + " from " + obj.m_tableName + conds + conds.orderString + conds.limitString;
List <DBValue> results = new List <DBValue>();
try
{
foreach (SQLiteResultSet.Row result in DBTVSeries.Execute(sql).Rows)
{
results.Add(result.fields[0]);
}
}
catch (Exception ex)
{
MPTVSeriesLog.Write("GetSingleField SQL method generated an error: " + ex.Message);
}
return(results);
}
public static List <DBSeason> Get(SQLCondition condition, bool includeStdCond)
{
string sqlQuery = stdGetSQL(condition, true, includeStdCond);
//MPTVSeriesLog.Write(sqlQuery);
SQLiteResultSet results = DBTVSeries.Execute(sqlQuery);
List <DBSeason> outList = new List <DBSeason>();
if (results.Rows.Count > 0)
{
for (int index = 0; index < results.Rows.Count; index++)
{
DBSeason season = new DBSeason();
season.Read(ref results, index);
outList.Add(season);
}
}
return(outList);
}
public static List <int> GetSeriesWithFanart()
{
List <int> seriesids = new List <int>();
string sqlQuery = "SELECT DISTINCT seriesID FROM Fanart";
SQLiteResultSet results = DBTVSeries.Execute(sqlQuery);
if (results.Rows.Count > 0)
{
for (int index = 0; index < results.Rows.Count; index++)
{
int result = 0;
if (int.TryParse(results.Rows[index].fields[0], out result))
{
seriesids.Add(result);
}
}
}
return(seriesids);
}
public static DBValue GetOptions(string property)
{
try
{
lock (thisLock)
{
DBValue retValue;
if (optionsCache.TryGetValue(property, out retValue))
{
return(retValue);
}
// ensure our sql query will be using a valid string
string convertedProperty = property;
DatabaseUtility.RemoveInvalidChars(ref convertedProperty);
string sqlQuery = "SELECT value FROM options WHERE property = '" + convertedProperty + "'";
SQLiteResultSet sqlResults = DBTVSeries.Execute(sqlQuery);
if (sqlResults.Rows.Count > 0)
{
string dbValue = DatabaseUtility.Get(sqlResults, 0, "value");
if (!optionsCache.ContainsKey(property))
{
optionsCache.Add(property, dbValue);
}
return(dbValue);
}
}
}
catch (Exception ex)
{
MPTVSeriesLog.Write("An error occurred (" + ex.Message + ").");
}
return(null);
}
private static List <DBSeries> Get(String sqlQuery)
{
SQLiteResultSet results = DBTVSeries.Execute(sqlQuery);
List <DBSeries> outList = new List <DBSeries>();
if (results.Rows.Count > 0)
{
for (int index = 0; index < results.Rows.Count; index++)
{
DBSeries series = new DBSeries();
series.Read(ref results, index);
series.m_onlineSeries = new DBOnlineSeries();
series.m_onlineSeries.Read(ref results, index);
outList.Add(series);
if (series[cID] < 0 && series.m_onlineSeries[DBOnlineSeries.cPrettyName].ToString().Length == 0)
{
series.m_onlineSeries[DBOnlineSeries.cPrettyName] = series[cParsedName];
series.m_onlineSeries.Commit();
}
}
}
return(outList);
}
public static void GlobalSet(DBTable obj, String sKey, DBValue Value, SQLCondition conditions)
{
if (obj.m_fields.ContainsKey(sKey))
{
String sqlQuery = "update " + obj.m_tableName + " SET " + sKey + "=";
switch (obj.m_fields[sKey].Type)
{
case DBField.cTypeInt:
sqlQuery += Value;
break;
case DBField.cTypeString:
sqlQuery += "'" + Value + "'";
break;
}
sqlQuery += conditions;
SQLiteResultSet results = DBTVSeries.Execute(sqlQuery);
if (dbUpdateOccured != null)
{
dbUpdateOccured(obj.m_tableName);
}
}
}
public static DBExpression[] GetAll()
{
try
{
// retrieve all fields in the table
String sqlQuery = "select * from " + cTableName + " order by " + cIndex;
SQLiteResultSet results = DBTVSeries.Execute(sqlQuery);
if (results.Rows.Count > 0)
{
DBExpression[] expressions = new DBExpression[results.Rows.Count];
for (int index = 0; index < results.Rows.Count; index++)
{
expressions[index] = new DBExpression();
expressions[index].Read(ref results, index);
}
return(expressions);
}
}
catch (Exception ex)
{
MPTVSeriesLog.Write("Error in DBExpression.Get (" + ex.Message + ").");
}
return(null);
}
public List <string> getGroupItems(int stepIndex, string[] currentStepSelection) // in nested groups, eg. Networks-Genres-.. we also need selections
{
SQLCondition conditions = null;
MPTVSeriesLog.Write("View: GetGroupItems: Begin", MPTVSeriesLog.LogLevel.Debug);
if (stepIndex >= m_steps.Count)
{
return(null); // wrong index specified!!
}
addHierarchyConditions(ref stepIndex, ref currentStepSelection, ref conditions);
logicalViewStep step = m_steps[stepIndex];
List <string> items = new List <string>();
// to ensure we respect on the fly filter settings
if (DBOption.GetOptions(DBOption.cOnlyShowLocalFiles) && (typeof(DBOnlineEpisode) != step.groupedBy.table.GetType() && typeof(DBEpisode) != step.groupedBy.table.GetType()))
{
// not generic
SQLCondition fullSubCond = new SQLCondition();
fullSubCond.AddCustom(DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID), DBOnlineSeries.Q(DBOnlineSeries.cID), SQLConditionType.Equal);
conditions.AddCustom(" exists( " + DBEpisode.stdGetSQL(fullSubCond, false) + " )");
}
else if (DBOption.GetOptions(DBOption.cOnlyShowLocalFiles))
{
// has to be grouped by something episode
conditions.Add(new DBEpisode(), DBEpisode.cFilename, "", SQLConditionType.NotEqual);
}
string fieldName = step.groupedBy.rawFieldname;
string tableName = step.groupedBy.table.m_tableName;
string tableField = step.groupedBy.tableField;
string userEditField = tableField + DBTable.cUserEditPostFix;
string sql = string.Empty;
// check if the useredit column exists
if (DBTable.ColumnExists(tableName, fieldName + DBTable.cUserEditPostFix))
{
sql = "select distinct(" +
"case when (" + userEditField + " is null or " + userEditField + " = " + "'" + "'" + ") " +
"then " + tableField + " else " + userEditField + " " +
"end) as gnr, " +
"count(*) from " + tableName + conditions + " group by gnr" + step.conds.orderString;
}
else
{
sql = "select distinct " + tableField +
" , count(*) " +
" from " + tableName + conditions +
" group by " + tableField +
step.conds.orderString;
}
SQLite.NET.SQLiteResultSet results = DBTVSeries.Execute(sql);
MPTVSeriesLog.Write("View: GetGroupItems: SQL complete", MPTVSeriesLog.LogLevel.Debug);
if (results.Rows.Count > 0)
{
for (int index = 0; index < results.Rows.Count; index++)
{
string tmpItem = results.Rows[index].fields[0];
// assume we now have a list of all distinct ones
if (step.groupedBy.attempSplit)
{
// we want to try to split by "|" eg. for actors/genres
string[] split = DBOnlineEpisode.splitField(tmpItem);
foreach (string item in split)
{
if (item.Trim().Length == 0)
{
// display "Unknown" if field is empty"
items.Add(Translation.Unknown);
}
else
{
items.Add(item.Trim());
}
}
}
else
{
if (tmpItem.Trim().Length == 0)
{
items.Add(Translation.Unknown);
}
else
{
items.Add(tmpItem.Trim());
}
}
}
if (step.groupedBy.attempSplit)
{
// have to check for dups (because we split eg. Drama|Action so "Action" might be in twice
items = Helper.RemoveDuplicates(items);
}
// now we have to sort them again (Unknown/splitting above)
items.Sort();
if (step.groupedBy.attempSplit)
{
// and limit in memory here (again because those splits are hard to deal with)
if (step.limitItems > 0)
{
Helper.LimitList(ref items, step.limitItems);
}
}
}
MPTVSeriesLog.Write("View: GetGroupItems: Complete", MPTVSeriesLog.LogLevel.Debug);
return(items);
}
static DBSeason()
{
DBSeason dummy = new DBSeason();
////////////////////////////////////////////////////////////////////////////////
#region Pretty Names displayed in Configuration Details Tab
s_FieldToDisplayNameMap.Add(cID, "Composite Season ID");
s_FieldToDisplayNameMap.Add(cSeriesID, "Series ID");
s_FieldToDisplayNameMap.Add(cIndex, "Season Index");
s_FieldToDisplayNameMap.Add(cEpisodeCount, "Episodes");
s_FieldToDisplayNameMap.Add(cEpisodesUnWatched, "Episodes UnWatched");
s_FieldToDisplayNameMap.Add(cMyRating, "My Rating");
#endregion
////////////////////////////////////////////////////////////////////////////////
int nCurrentDBVersion = cDBVersion;
int nUpgradeDBVersion = DBOption.GetOptions(DBOption.cDBSeasonVersion);
while (nUpgradeDBVersion != nCurrentDBVersion)
{
SQLCondition condEmpty = new SQLCondition();
List <DBSeason> AllSeasons = Get(condEmpty, true);
// take care of the upgrade in the table
switch (nUpgradeDBVersion)
{
case 1:
// upgrade to version 2; clear the season table (series table format changed)
try
{
String sqlQuery = "DROP TABLE season";
DBTVSeries.Execute(sqlQuery);
nUpgradeDBVersion++;
}
catch { }
break;
case 2:
DBSeason.GlobalSet(DBSeason.cHidden, 0, new SQLCondition());
DBSeries.GlobalSet(DBOnlineSeries.cGetEpisodesTimeStamp, 0, new SQLCondition());
nUpgradeDBVersion++;
break;
case 3:
// create the unwatcheditem value by parsin the episodes
foreach (DBSeason season in AllSeasons)
{
DBEpisode episode = DBEpisode.GetFirstUnwatched(season[DBSeason.cSeriesID], season[DBSeason.cIndex]);
if (episode != null)
{
season[DBSeason.cUnwatchedItems] = true;
}
else
{
season[DBSeason.cUnwatchedItems] = false;
}
season.Commit();
}
nUpgradeDBVersion++;
break;
case 4:
// Set number of watched/unwatched episodes
foreach (DBSeason season in AllSeasons)
{
int epsTotal = 0;
int epsUnWatched = 0;
DBEpisode.GetSeasonEpisodeCounts(season, out epsTotal, out epsUnWatched);
season[DBSeason.cEpisodeCount] = epsTotal;
season[DBSeason.cEpisodesUnWatched] = epsUnWatched;
season.Commit();
}
nUpgradeDBVersion++;
break;
default:
nUpgradeDBVersion = nCurrentDBVersion;
break;
}
}
DBOption.SetOptions(DBOption.cDBSeasonVersion, nCurrentDBVersion);
}
public List <DBFanart> FanartsToDownload(int SeriesID)
{
// Only get a list of fanart that is available for download
String sqlQuery = "select * from " + cTableName;
sqlQuery += " where " + cSeriesID + " = " + SeriesID.ToString();
// Get Preferred Resolution
int res = DBOption.GetOptions(DBOption.cAutoDownloadFanartResolution);
bool getSeriesNameFanart = DBOption.GetOptions(DBOption.cAutoDownloadFanartSeriesNames);
if (res == (int)FanartResolution.HD)
{
sqlQuery += " and " + cResolution + " = " + "\"1280x720\"";
}
if (res == (int)FanartResolution.FULLHD)
{
sqlQuery += " and " + cResolution + " = " + "\"1920x1080\"";
}
if (!getSeriesNameFanart)
{
sqlQuery += " and " + cSeriesName + " != " + "\"true\"";
}
SQLiteResultSet results = DBTVSeries.Execute(sqlQuery);
if (results.Rows.Count > 0)
{
int iFanartCount = 0;
List <DBFanart> AvailableFanarts = new List <DBFanart>(results.Rows.Count);
for (int index = 0; index < results.Rows.Count; index++)
{
if (results.GetField(index, (int)results.ColumnIndices[cLocalPath]).Length > 0)
{
iFanartCount++;
}
else
{
// Add 'Available to Download' fanart to list
AvailableFanarts.Add(new DBFanart());
AvailableFanarts[AvailableFanarts.Count - 1].Read(ref results, index);
}
}
// sort by highest rated
AvailableFanarts.Sort();
// Only return the fanarts that we want to download
int AutoDownloadCount = DBOption.GetOptions(DBOption.cAutoDownloadFanartCount);
for (int i = 0; i < AvailableFanarts.Count; i++)
{
// Dont get more than the user wants
if (iFanartCount >= AutoDownloadCount)
{
break;
}
_FanartsToDownload.Add(AvailableFanarts[i]);
iFanartCount++;
}
}
return(_FanartsToDownload);
}
public DBTable(string tableName)
{
// this base constructor was very expensive
// we now cache the result for all objects : dbtable and only redo it if an alter table occured (this drastically cut down the number of sql statements)
// this piece of code alone took over 30% of the time on my machine when entering config and newing up all the episodes
Dictionary <string, DBFieldType> cachedForTable;
m_tableName = tableName;
//m_fields = new Dictionary<string, DBField>();
if (fields.TryGetValue(tableName, out cachedForTable)) // good, cached, this happens 99% of the time
{
foreach (KeyValuePair <string, DBFieldType> entry in cachedForTable)
{
if (!m_fields.ContainsKey(entry.Key))
{
m_fields.Add(entry.Key, new DBField(entry.Value));
}
}
}
else // we have to get it, happens when the first object is created or after an alter table
{
cachedForTable = new Dictionary <string, DBFieldType>();
// load up fields from the table
SQLiteResultSet results = DBTVSeries.Execute("SELECT sql FROM sqlite_master WHERE name='" + m_tableName + "'");
if (results != null && results.Rows.Count > 0)
{
// we have the table definition, parse it for names/types
String sCreateTable = results.Rows[0].fields[0];
String RegExp = @"CREATE TABLE .*?\((.*?)\)";
Regex Engine = new Regex(RegExp, RegexOptions.IgnoreCase);
Match tablematch = Engine.Match(sCreateTable);
if (tablematch.Success)
{
String sParameters = tablematch.Groups[1].Value + ','; //trailng comma make the regex below find the last column
// we have the list of parameters, parse them
// group 1: fieldname, group2: type, group3: test for primary key, group4: any thing else until a comma (not null, Default value etc.)
RegExp = @"([^\s]+)\s+([^\s,]+)(\s+primary key)?([^,]+)?,";
Engine = new Regex(RegExp, RegexOptions.IgnoreCase);
MatchCollection matches = Engine.Matches(sParameters);
foreach (Match parammatch in matches)
{
String sName = parammatch.Groups[1].Value;
// could be either "int" or "integer"
bool bIntType = parammatch.Groups[2].Value.StartsWith("int", StringComparison.InvariantCultureIgnoreCase);
bool bPrimary = parammatch.Groups[3].Success;
// In Sqlite an "integer" (but not "int") Primary Key is an alias for the sqlite rowid, and therefore auto increments
bool bAutoIncrement = (bPrimary && parammatch.Groups[2].Value.Equals("integer", StringComparison.InvariantCultureIgnoreCase)) ||
// or a column can be set as autoincrement
parammatch.Groups[4].Value.ToLowerInvariant().Contains("autoincrement");
DBFieldType cachedInfo = new DBFieldType
{
Primary = bPrimary,
Type = (bIntType ? DBField.cTypeInt : DBField.cType.String),
AutoIncrement = bAutoIncrement
};
if (!m_fields.ContainsKey(sName))
{
m_fields.Add(sName, new DBField(cachedInfo));
}
cachedForTable.Add(sName, cachedInfo);
}
lock (fields)
fields.Add(tableName, cachedForTable);
}
else
{
MPTVSeriesLog.Write("parsing of CREATE TABLE failed!!!");
}
}
else
{
// no tables, assume it's going to be created later (using AddColumn)
}
}
}
public static Dictionary <string, List <EpisodeCounter> > GetEpisodesForCount()
{
var episodesForCount = new Dictionary <string, List <EpisodeCounter> >();
string selectFields = "online_episodes.SeriesID, online_episodes.EpisodeIndex, online_episodes.SeasonIndex, online_episodes.Combined_season, online_episodes.Watched";
string query = string.Empty;
string whereClause = string.Empty;
var wheres = new List <string>();
if (!DBOption.GetOptions(DBOption.cShowHiddenItems))
{
wheres.Add("online_episodes.Hidden = 0");
}
if (DBOption.GetOptions(DBOption.cOnlyShowLocalFiles))
{
wheres.Add("local_episodes.EpisodeFilename != ''");
}
if (!DBOption.GetOptions(DBOption.cCountEmptyAndFutureAiredEps))
{
wheres.Add(string.Format("online_episodes.FirstAired <= '{0}' AND online_episodes.FirstAired != ''", DateTime.Now.ToString("yyyy-MM-dd")));
}
if (wheres.Count > 0)
{
whereClause = string.Format("WHERE {0}", string.Join(" AND ", wheres.ToArray()));
}
if (DBOption.GetOptions(DBOption.cOnlyShowLocalFiles))
{
// if we are only counting episodes that have a file ie. local reference
// then we need to join the local and online episode tables
// further more we also need to union two select statements with
// one returning only the first of a single/double episode and the other
// returning the second of any double episodes
query = string.Format(@"
SELECT {0}
FROM online_episodes
LEFT JOIN local_episodes
ON local_episodes.CompositeID = online_episodes.CompositeID
{1}
UNION
SELECT {0}
FROM online_episodes
LEFT JOIN local_episodes
ON local_episodes.CompositeID2 = online_episodes.CompositeID
{1}
", selectFields, whereClause);
}
else
{
query = string.Format(@"
SELECT {0}
FROM online_episodes
{1}
", selectFields, whereClause);
}
SQLiteResultSet results = DBTVSeries.Execute(query);
foreach (var row in results.Rows)
{
var seriesId = row.fields[0];
var episodeAirIdx = row.fields[1];
var seasonAirIdx = row.fields[2];
var seasonDvdIdx = row.fields[3];
var watched = row.fields[4];
if (episodesForCount.ContainsKey(seriesId))
{
episodesForCount[seriesId].Add(new EpisodeCounter {
EpisodeIdx = episodeAirIdx, SeasonAirIdx = seasonAirIdx, SeasonDvdIdx = seasonDvdIdx, EpisodeWatched = watched
});
}
else
{
var episodeList = new List <EpisodeCounter>();
episodeList.Add(new EpisodeCounter {
EpisodeIdx = episodeAirIdx, SeasonAirIdx = seasonAirIdx, SeasonDvdIdx = seasonDvdIdx, EpisodeWatched = watched
});
episodesForCount.Add(seriesId, episodeList);
}
}
return(episodesForCount);
}
public virtual bool Commit()
{
try
{
if (!m_CommitNeeded)
{
return(false);
}
KeyValuePair <string, DBField> PrimaryField = new KeyValuePair <string, DBField>();
foreach (KeyValuePair <string, DBField> field in m_fields)
{
if (field.Value.Primary == true)
{
PrimaryField = field;
break;
}
}
bool update = false;
if (String.IsNullOrEmpty(PrimaryField.Value.Value) && !PrimaryField.Value.AutoIncrement)
{
return(false);
}
String sqlQuery;
StringBuilder builder = new StringBuilder();
String sWhere = " where ";
if (!String.IsNullOrEmpty(PrimaryField.Value.Value))
{
switch (PrimaryField.Value.Type)
{
case DBField.cTypeInt:
sWhere += PrimaryField.Key + " = " + PrimaryField.Value.Value;
break;
case DBField.cTypeString:
sWhere += PrimaryField.Key + " = '" + ((String)PrimaryField.Value.Value).Replace("'", "''") +
"'";
break;
}
// use the primary key field
sqlQuery = "select " + PrimaryField.Key + " from " + m_tableName + sWhere;
SQLiteResultSet records = DBTVSeries.Execute(sqlQuery);
if (records.Rows.Count > 0)
{
update = true;
}
}
if (update)
{
// already exists, update
builder.Append("update ").Append(m_tableName).Append(" set ");
int fieldsNeedingUpdating = 0;
foreach (KeyValuePair <string, DBField> fieldPair in m_fields)
{
if (!fieldPair.Value.Primary && fieldPair.Value.WasChanged)
{
builder.Append(fieldPair.Key).Append(" = ");
switch (fieldPair.Value.Type)
{
case DBField.cTypeInt:
if (String.IsNullOrEmpty(fieldPair.Value.Value))
{
builder.Append("'',");
}
else
{
builder.Append((string)fieldPair.Value.Value).Append(',');
}
break;
case DBField.cTypeString:
builder.Append(" '").Append(((String)(fieldPair.Value.Value)).Replace("'", "''")).Append("',");
break;
}
fieldsNeedingUpdating++;
fieldPair.Value.WasChanged = false;
}
}
if (fieldsNeedingUpdating > 0)
{
sqlQuery = builder.ToString().Substring(0, builder.Length - 1) + sWhere;
DBTVSeries.Execute(sqlQuery);
}
}
else
{
// add new record
StringBuilder paramNames = new StringBuilder();
bool first = true;
foreach (KeyValuePair <string, DBField> fieldPair in m_fields)
{
if (!first)
{
paramNames.Append(',');
builder.Append(',');
}
else
{
if (fieldPair.Value.AutoIncrement)
{
//skip the autoincrementing field as we want this to be generated
continue;
}
first = false;
}
paramNames.Append(fieldPair.Key);
switch (fieldPair.Value.Type)
{
case DBField.cTypeInt:
if (String.IsNullOrEmpty(fieldPair.Value.Value))
{
builder.Append("''");
}
else
{
builder.Append((string)fieldPair.Value.Value);
}
break;
case DBField.cTypeString:
builder.Append(" '").Append(((String)(fieldPair.Value.Value)).Replace("'", "''")).Append("'");
break;
}
}
String sParamValues = builder.ToString();
builder.Remove(0, builder.Length);
builder.Append("insert into ").Append(m_tableName).Append(" (").Append(paramNames).Append(") values(").Append(sParamValues).Append(")");
sqlQuery = builder.ToString();
DBTVSeries.Execute(sqlQuery);
if (PrimaryField.Value.AutoIncrement)
{
//we've just done an insert to an auto crementing field, so fetch the value
SQLiteResultSet results = DBTVSeries.Execute("SELECT last_insert_rowid() AS ID");
this[PrimaryField.Key] = int.Parse(results.Rows[0].fields[0]);
}
}
m_CommitNeeded = false;
return(true);
}
catch (Exception ex)
{
MPTVSeriesLog.Write("An Error Occurred (" + ex.Message + ").");
return(false);
}
}
static DBSeries()
{
// make sure the table is created on first run (and columns are added before we call SET)
DBSeries dummy = new DBSeries();
s_nLastLocalID = DBOption.GetOptions(DBOption.cDBSeriesLastLocalID);
s_FieldToDisplayNameMap.Add(cParsedName, "Parsed Name");
int nCurrentDBVersion = cDBVersion;
int nUpgradeDBVersion = DBOption.GetOptions(DBOption.cDBSeriesVersion);
while (nUpgradeDBVersion != nCurrentDBVersion)
{
SQLCondition condEmpty = new SQLCondition();
List <DBSeries> AllSeries = Get(condEmpty);
// take care of the upgrade in the table
switch (nUpgradeDBVersion)
{
case 1:
case 2:
// upgrade to version 3; clear the series table (we use 2 other tables now)
try
{
String sqlQuery = "DROP TABLE series";
DBTVSeries.Execute(sqlQuery);
nUpgradeDBVersion++;
}
catch { }
break;
case 3:
// set all new perseries timestamps to 0
DBOnlineSeries.GlobalSet(new DBOnlineSeries(), DBOnlineSeries.cGetEpisodesTimeStamp, 0, new SQLCondition());
DBOnlineSeries.GlobalSet(new DBOnlineSeries(), DBOnlineSeries.cUpdateBannersTimeStamp, 0, new SQLCondition());
nUpgradeDBVersion++;
break;
case 4:
DBSeries.GlobalSet(new DBSeries(), DBSeries.cHidden, 0, new SQLCondition());
nUpgradeDBVersion++;
break;
case 5:
// copy all local parsed name into the online series if seriesID = 0
SQLCondition conditions = new SQLCondition();
conditions.Add(new DBOnlineSeries(), DBOnlineSeries.cID, 0, SQLConditionType.LessThan);
// just getting the series should be enough
List <DBSeries> seriesList = DBSeries.Get(conditions);
nUpgradeDBVersion++;
break;
case 6:
// set all watched flag timestamp to 0 (will be created)
DBOnlineSeries.GlobalSet(new DBOnlineSeries(), DBOnlineSeries.cWatchedFileTimeStamp, 0, new SQLCondition());
nUpgradeDBVersion++;
break;
case 7:
// all series no tagged for auto download at first
DBOnlineSeries.GlobalSet(new DBOnlineSeries(), DBOnlineSeries.cTaggedToDownload, 0, new SQLCondition());
nUpgradeDBVersion++;
break;
case 8:
// create the unwatcheditem value by parsin the episodes
foreach (DBSeries series in AllSeries)
{
DBEpisode episode = DBEpisode.GetFirstUnwatched(series[DBSeries.cID]);
if (episode != null)
{
series[DBOnlineSeries.cUnwatchedItems] = true;
}
else
{
series[DBOnlineSeries.cUnwatchedItems] = false;
}
series.Commit();
}
nUpgradeDBVersion++;
break;
case 9:
// Set number of watched/unwatched episodes
foreach (DBSeries series in AllSeries)
{
int epsTotal = 0;
int epsUnWatched = 0;
DBEpisode.GetSeriesEpisodeCounts(series[DBSeries.cID], out epsTotal, out epsUnWatched);
series[DBOnlineSeries.cEpisodeCount] = epsTotal;
series[DBOnlineSeries.cEpisodesUnWatched] = epsUnWatched;
series.Commit();
}
nUpgradeDBVersion++;
break;
case 10:
// Update Sort Name Column
foreach (DBSeries series in AllSeries)
{
series[DBOnlineSeries.cSortName] = Helper.GetSortByName(series[DBOnlineSeries.cPrettyName]);
series.Commit();
}
nUpgradeDBVersion++;
break;
case 11:
// Migrate isFavourite to new Tagged View
conditions = new SQLCondition();
conditions.Add(new DBOnlineSeries(), DBOnlineSeries.cIsFavourite, "1", SQLConditionType.Equal);
seriesList = DBSeries.Get(conditions);
MPTVSeriesLog.Write("Migrating Favourite Series");
foreach (DBSeries series in seriesList)
{
// Tagged view are seperated with the pipe "|" character
string tagName = "|" + DBView.cTranslateTokenFavourite + "|";
series[DBOnlineSeries.cViewTags] = Helper.GetSeriesViewTags(series, true, tagName);
series.Commit();
}
// Migrate isOnlineFavourite to new TaggedView
conditions = new SQLCondition();
conditions.Add(new DBOnlineSeries(), DBOnlineSeries.cIsOnlineFavourite, "1", SQLConditionType.Equal);
seriesList = DBSeries.Get(conditions);
MPTVSeriesLog.Write("Migrating Online Favourite Series");
foreach (DBSeries series in seriesList)
{
// Tagged view are seperated with the pipe "|" character
string tagName = "|" + DBView.cTranslateTokenOnlineFavourite + "|";
series[DBOnlineSeries.cViewTags] = Helper.GetSeriesViewTags(series, true, tagName);
series.Commit();
}
nUpgradeDBVersion++;
break;
case 12:
// we now have parsed_series names as titlecased
// to avoid users having to re-identify series for new episodes, and to avoid duplicate entries, we upgrade existing series names
foreach (var series in AllSeries)
{
string oldName = series[DBSeries.cParsedName];
string newName = oldName.ToTitleCase();
MPTVSeriesLog.Write(string.Format("Upgrading Parsed Series Name: {0} to {1}", oldName, newName));
series[DBSeries.cParsedName] = newName;
series.Commit();
}
nUpgradeDBVersion++;
break;
case 13:
// original name not working in previous release
DBOnlineSeries.GlobalSet(new DBOnlineSeries(), DBOnlineSeries.cOriginalName, (DBValue)string.Empty, new SQLCondition());
nUpgradeDBVersion++;
break;
case 14:
// original name not working in previous release
DBOnlineSeries.GlobalSet(new DBOnlineSeries(), DBOnlineSeries.cTraktIgnore, 0, new SQLCondition());
nUpgradeDBVersion++;
break;
default:
// new DB, nothing special to do
nUpgradeDBVersion = nCurrentDBVersion;
break;
}
}
DBOption.SetOptions(DBOption.cDBSeriesVersion, nCurrentDBVersion);
}
public static void Init()
{
if (!DBOption.GetOptions(DBOption.cNewAPIUpgradeDone)) // upgrade
{
String sqlDel = "drop table if exists " + cTableName;
DBTVSeries.Execute(sqlDel);
}
if (String.IsNullOrEmpty(cApiKey))
{
MPTVSeriesLog.Write("No APIKey...if you compile yourself you need to register an APIKey at theTVDB.com and add it to the resourceFile, nothing will be downloaded!");
return;
}
// no mirrors yet - refresh using "seed"
IsMirrorsAvailable = true;
string sMirror = DBOption.GetOptions(DBOption.cMainMirror).ToString().Replace("http://", "https://");
if (!LoadMirrorList(sMirror))
{
IsMirrorsAvailable = false;
// Try again using the Hardcoded mirror
if (!sMirror.Equals(DBOption.cMainMirrorHardCoded))
{
MPTVSeriesLog.Write("Attempting to retrieve Mirrors from default location");
if (LoadMirrorList(DBOption.cMainMirrorHardCoded))
{
DBOption.SetOptions(DBOption.cMainMirror, DBOption.cMainMirrorHardCoded);
IsMirrorsAvailable = true;
}
}
if (!IsMirrorsAvailable)
{
MPTVSeriesLog.Write("Warning: No mirrors received, nothing will be downloaded!");
return;
}
}
//This is now handled server-side using mod_rewrite and round-robin DNS,
//so the mirrors file is somewhat deprecated.
var xmlMirrors = new List <DBOnlineMirror>(mMemoryMirrors);
var zipMirrors = new List <DBOnlineMirror>(mMemoryMirrors);
var bannerMirrors = new List <DBOnlineMirror>(mMemoryMirrors);
// seperate them by which one can do what
CheckMirrorCapable(xmlMirrors, TypeMask.XML);
CheckMirrorCapable(zipMirrors, TypeMask.Zip);
CheckMirrorCapable(bannerMirrors, TypeMask.Banners);
// select a random one for each of them
var r = new Random();
if (xmlMirrors.Count > 0)
{
mCurrentInterface = xmlMirrors[r.Next(xmlMirrors.Count)][cMirrorpath].ToString().Replace("http://", "https://");
}
if (zipMirrors.Count > 0)
{
mCurrentZip = zipMirrors[r.Next(zipMirrors.Count)][cMirrorpath].ToString().Replace("http://", "https://") + "/";
}
if (bannerMirrors.Count > 0)
{
mCurrentBanner = bannerMirrors[r.Next(bannerMirrors.Count)][cMirrorpath].ToString().Replace("http://", "https://") + "/banners/";
}
}