Exemple #1
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);
        }
Exemple #3
0
        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);
            }
        }
Exemple #4
0
        /// <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);
        }
Exemple #5
0
        public static void ClearAll()
        {
            String sqlQuery = "delete from " + cTableName;

            DBTVSeries.Execute(sqlQuery);
            _includesNetworkShares = false;
        }
Exemple #6
0
        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]);
        }
Exemple #7
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);
        }
Exemple #8
0
        public DBSeries()
            : base(cTableName)
        {
            InitColumns();
            InitValues();

            DBTVSeries.CreateDBIndices("create index if not exists seriesIDLocal on local_series(ID ASC)", "local_series", true);
        }
        public DBOnlineEpisode()
            : base(cTableName)
        {
            InitColumns();
            InitValues();

            DBTVSeries.CreateDBIndices("create index if not exists seriesIDOnlineEp on online_episodes(SeriesID ASC)", "online_episodes", true);
        }
Exemple #10
0
        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>());
            }
        }
Exemple #11
0
        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);
            }
        }
Exemple #12
0
 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);
         }
     }
 }
Exemple #13
0
        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);
        }
Exemple #14
0
 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 + ").");
     }
 }
Exemple #15
0
 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);
 }
Exemple #16
0
        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]);
                }
            }
        }
Exemple #17
0
        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);
        }
Exemple #18
0
        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);
        }
Exemple #19
0
        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);
        }
Exemple #20
0
        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);
        }
Exemple #21
0
        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);
        }
Exemple #22
0
        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);
 }
Exemple #24
0
        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);
            }
        }
Exemple #25
0
        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);
        }
Exemple #26
0
        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)
                }
            }
        }
Exemple #27
0
        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);
        }
Exemple #28
0
        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);
        }
Exemple #29
0
        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);
        }
Exemple #30
0
        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);
        }