public static string stdGetSQL(SQLCondition conditions, bool selectFull, bool includeStdCond) { string field; if (selectFull) { SQLWhat what = new SQLWhat(new DBOnlineSeries()); what.AddWhat(new DBSeries()); field = what; } else { field = DBOnlineSeries.Q(DBOnlineSeries.cID) + " from " + DBOnlineSeries.cTableName; } if (includeStdCond) { conditions.AddCustom(stdConditions.ConditionsSQLString); } string conds = conditions; string orderBy = string.Empty; if (selectFull) { bool bUseSortName = DBOption.GetOptions(DBOption.cUseSortName); orderBy = conditions.customOrderStringIsSet ? conditions.orderString : " order by " + (bUseSortName?"upper(" + DBOnlineSeries.Q(DBOnlineSeries.cSortName) + "),":"") + "upper(" + DBOnlineSeries.Q(DBOnlineSeries.cPrettyName) + ")"; } return("select " + field + " left join " + cTableName + " on " + DBSeries.Q(cID) + "==" + DBOnlineSeries.Q(cID) + conds + orderBy + conditions.limitString); }
public static logicalViewStep parseFromDB(string viewStep, bool hasSeriesBeforeIt) { logicalViewStep thisView = new logicalViewStep(); thisView.hasSeriesBeforeIt = hasSeriesBeforeIt; string[] viewSteps = System.Text.RegularExpressions.Regex.Split(viewStep, s_intSeperator); thisView.setType(viewSteps[0]); thisView.addConditionsFromString(viewSteps[1]); if (viewSteps[2].Length > 0) { string[] orderFields = System.Text.RegularExpressions.Regex.Split(viewSteps[2], ";"); thisView.inLineSpecials = orderFields[0] == "<Episode.EpisodeIndex>"; thisView.inLineSpecialsAsc = orderFields[0] != "desc"; for (int i = 0; i < orderFields.Length; i += 2) { if (thisView.Type != type.group) { DBTable table = null; string tableField = string.Empty; getTableFieldname(orderFields[i], out table, out tableField); tableField = getQTableNameFromUnknownType(table, tableField); // example of how the user can order by a different table // needs to be enabled once definable views are ready /* * if (thisView.Type == type.season && table.GetType() != typeof(DBSeason)) * { * Type lType = table.GetType(); * if (lType == typeof(DBOnlineSeries)) * tableField = "( select " + tableField + " from " + DBOnlineSeries.cTableName + " where " + DBOnlineSeries.Q(DBOnlineSeries.cID) + " = " + DBSeason.Q(DBSeason.cSeriesID) + ")"; + }*/ //if (thisView.Type == type.episode && ( table.GetType() == typeof(DBEpisode) || table.GetType() == typeof(DBOnlineEpisode))) //{ // // for perf reason a subquery is build, otherwise custom orders and the nessesary join really slow down sqllite! // SQLCondition subQueryConditions = thisView.conds.Copy(); // have to have all conds too // subQueryConditions.AddOrderItem(tableField, (orderFields[i + 1] == "asc" ? SQLCondition.orderType.Ascending : SQLCondition.orderType.Descending)); // if (viewSteps[3].Length > 0) // set the limit too // { // try // { // subQueryConditions.SetLimit(System.Convert.ToInt32(viewSteps[3])); // } // catch (Exception) // { // } // } // thisView.conds.AddSubQuery("compositeid", table, subQueryConditions, table.m_tableName + "." + DBEpisode.cCompositeID, SQLConditionType.In); //} thisView.conds.AddOrderItem(tableField, (orderFields[i + 1] == "asc" ? SQLCondition.orderType.Ascending : SQLCondition.orderType.Descending)); } } } if (thisView.Type == type.group && thisView.groupedBy != null) // for groups always by their values (ignore user setting!) { if (thisView.groupedBy.table.GetType() == typeof(DBSeries)) { if (!DBOption.GetOptions(DBOption.cShowHiddenItems)) { thisView.conds.Add(new DBSeries(), DBSeries.cHidden, 1, SQLConditionType.NotEqual); } } else if (thisView.groupedBy.table.GetType() == typeof(DBOnlineSeries)) { if (!DBOption.GetOptions(DBOption.cShowHiddenItems)) { thisView.conds.AddCustom(" not exists ( select * from " + DBSeries.cTableName + " where id = " + DBOnlineSeries.Q(DBOnlineSeries.cID) + " and " + DBSeries.Q(DBSeries.cHidden) + " = 1)"); } } else if (thisView.groupedBy.table.GetType() == typeof(DBSeason)) { if (!DBOption.GetOptions(DBOption.cShowHiddenItems)) { thisView.conds.Add(new DBSeason(), DBSeason.cHidden, 1, SQLConditionType.NotEqual); } } thisView.conds.AddOrderItem(thisView.groupedBy.tableField, SQLCondition.orderType.Descending); // tablefield includes tablename itself! } try { if (viewSteps[3].Length > 0) { thisView.limitItems = System.Convert.ToInt32(viewSteps[3]); thisView.conds.SetLimit(thisView.limitItems); } } catch (Exception) { MPTVSeriesLog.Write("Cannot interpret limit in logicalview, limit was: " + viewSteps[3]); } return(thisView); }
public void addSQLCondition(string what, string type, string condition) { if ((!what.Contains("<") || !what.Contains(".")) && !what.Contains("custom:")) { return; } SQLConditionType condtype; switch (type) { case "=": condtype = SQLConditionType.Equal; break; case ">": condtype = SQLConditionType.GreaterThan; break; case ">=": condtype = SQLConditionType.GreaterEqualThan; break; case "<": condtype = SQLConditionType.LessThan; break; case "<=": condtype = SQLConditionType.LessEqualThan; break; case "!=": condtype = SQLConditionType.NotEqual; break; case "like": condtype = SQLConditionType.Like; break; default: condtype = SQLConditionType.Equal; break; } DBTable table = null; string tableField = string.Empty; getTableFieldname(what, out table, out tableField); Type lType = table.GetType(); SQLCondition fullSubCond = new SQLCondition(); if (logicalViewStep.type.series == Type && (lType != typeof(DBSeries) && lType != typeof(DBOnlineSeries))) { if (lType == typeof(DBSeason)) { fullSubCond.AddCustom(DBSeason.Q(DBSeason.cSeriesID), DBOnlineSeries.Q(DBOnlineSeries.cID), SQLConditionType.Equal); fullSubCond.AddCustom(DBSeason.Q(tableField), condition, condtype, true); conds.AddCustom(" exists( " + DBSeason.stdGetSQL(fullSubCond, false) + " )"); } else if (lType == typeof(DBOnlineEpisode)) { fullSubCond.AddCustom(DBOnlineEpisode.Q(tableField), condition, condtype, true); conds.AddCustom(" online_series.id in ( " + DBEpisode.stdGetSQL(fullSubCond, false, true, DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID)) + " )"); } else if (lType == typeof(DBEpisode)) { fullSubCond.AddCustom(DBEpisode.Q(tableField), condition, condtype, true); conds.AddCustom(" online_series.id in ( " + DBEpisode.stdGetSQL(fullSubCond, false, true, DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID)) + " )"); } } else if (logicalViewStep.type.season == Type && lType != typeof(DBSeason)) { if (lType == typeof(DBOnlineSeries) || lType == typeof(DBSeries)) { fullSubCond.AddCustom(DBOnlineSeries.Q(DBOnlineSeries.cID), DBSeason.Q(DBSeason.cSeriesID), SQLConditionType.Equal); fullSubCond.AddCustom(DBOnlineSeries.Q(tableField), condition, condtype, true); conds.AddCustom(" exists( " + DBSeries.stdGetSQL(fullSubCond, false) + " )"); } else if (lType == typeof(DBOnlineEpisode)) { // we rely on the join in dbseason for this (much, much faster) conds.AddCustom(DBOnlineEpisode.Q(tableField), condition, condtype, true); } else if (lType == typeof(DBEpisode)) { fullSubCond.AddCustom(DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID), DBSeason.Q(DBSeason.cSeriesID), SQLConditionType.Equal); fullSubCond.AddCustom(DBOnlineEpisode.Q(DBOnlineEpisode.cSeasonIndex), DBSeason.Q(DBSeason.cIndex), SQLConditionType.Equal); fullSubCond.AddCustom(DBEpisode.Q(tableField), condition, condtype); conds.AddCustom(" exists( " + DBEpisode.stdGetSQL(fullSubCond, false) + " )"); } } else if (logicalViewStep.type.episode == Type && (lType != typeof(DBEpisode) && lType != typeof(DBOnlineEpisode))) { if (lType == typeof(DBOnlineSeries) || lType == typeof(DBSeries)) { fullSubCond.AddCustom(DBOnlineSeries.Q(DBOnlineSeries.cID), DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID), SQLConditionType.Equal); fullSubCond.AddCustom(DBOnlineSeries.Q(tableField), condition, condtype, true); conds.AddCustom(" exists( " + DBSeries.stdGetSQL(fullSubCond, false) + " )"); } if (lType == typeof(DBSeason)) { fullSubCond.AddCustom(DBSeason.Q(DBSeason.cSeriesID), DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID), SQLConditionType.Equal); fullSubCond.AddCustom(DBSeason.Q(DBSeason.cIndex), DBOnlineEpisode.Q(DBOnlineEpisode.cSeasonIndex), SQLConditionType.Equal); fullSubCond.AddCustom(DBSeason.Q(tableField), condition, condtype, true); conds.AddCustom(" exists( " + DBSeason.stdGetSQL(fullSubCond, false) + " )"); } } else { // condition is on current table itself conds.Add(table, tableField, condition.Trim(), condtype); } }
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); }