Exemplo n.º 1
0
        public static DateTime GetAsDateTime(SQLiteResultSet results, int iRecord, string aTimestampColum)
        {
            DateTime finalResult = DateTime.MinValue;

            if (results == null || string.IsNullOrEmpty(aTimestampColum) || results.Rows.Count < 1 ||
                results.Rows.Count < iRecord)
            {
                return(finalResult);
            }

            try
            {
                SQLiteResultSet.Row arr = results.Rows[iRecord];
                int iCol = 0;
                if (results.ColumnIndices.ContainsKey(aTimestampColum))
                {
                    iCol = (int)results.ColumnIndices[aTimestampColum];
                    if (arr.fields[iCol] != null)
                    {
                        finalResult = Convert.ToDateTime((arr.fields[iCol]));
                    }
                }
            }
            catch (Exception) {}

            return(finalResult);
        }
Exemplo n.º 2
0
        public static bool IntegrityCheck(SQLiteClient m_db)
        {
            SQLiteResultSet results;

            if (m_db == null)
            {
                return(false);
            }

            results = m_db.Execute("PRAGMA integrity_check;");
            if (results != null)
            {
                if (results.Rows.Count == 1)
                {
                    SQLiteResultSet.Row arr = results.Rows[0];
                    if (arr.fields.Count == 1)
                    {
                        if (arr.fields[0] == "ok")
                        {
                            Log.Debug("IntegrityCheck: the {0} is OK", m_db.DatabaseName);
                            return(true);
                        }
                    }
                }
            }
            Log.Error("IntegrityCheck: the {0} is corrupt.", m_db.DatabaseName);
            return(false);
        }
Exemplo n.º 3
0
        public static string Get(SQLiteResultSet results, int iRecord, int column)
        {
            if (null == results)
            {
                return(string.Empty);
            }
            if (results.Rows.Count < iRecord)
            {
                return(string.Empty);
            }
            if (column < 0 || column >= results.ColumnNames.Count)
            {
                return(string.Empty);
            }
            SQLiteResultSet.Row arr = results.Rows[iRecord];
            if (arr.fields[column] == null)
            {
                return(string.Empty);
            }
            string strLine = (arr.fields[column]).Trim();

            //strLine = strLine.Replace("''","'");
            return(strLine);

            ;
        }
        private string Get(SQLiteResultSet results, int iRecord, string strColum)
        {
            if (null == results)
            {
                return(string.Empty);
            }
            if (results.Rows.Count < iRecord)
            {
                return(string.Empty);
            }

            SQLiteResultSet.Row arr = results.Rows[iRecord];
            int iCol = 0;

            foreach (string columnName in results.ColumnNames)
            {
                if (strColum == columnName)
                {
                    string strLine = arr.fields[iCol].Trim();
                    strLine = strLine.Replace("''", "'");
                    return(strLine);
                }
                iCol++;
            }
            return(string.Empty);
        }
Exemplo n.º 5
0
        /// <summary>
        /// Check if a view exists
        /// </summary>
        /// <param name="table">name of view</param>
        /// <returns>true: view exists
        /// false: view does not exist</returns>
        public static bool ViewExists(SQLiteClient m_db, string view)
        {
            SQLiteResultSet results;

            if (m_db == null)
            {
                return(false);
            }
            if (view == null)
            {
                return(false);
            }
            if (view.Length == 0)
            {
                return(false);
            }
            results = m_db.Execute("SELECT name FROM sqlite_master WHERE name like '" + view + "' and type like 'view'");
            // UNION ALL SELECT name FROM sqlite_temp_master WHERE type='view' ORDER BY name");
            if (results != null)
            {
                if (results.Rows.Count == 1)
                {
                    SQLiteResultSet.Row arr = results.Rows[0];
                    if (arr.fields.Count == 1)
                    {
                        if (arr.fields[0] == view)
                        {
                            return(true);
                        }
                    }
                }
            }
            return(false);
        }
Exemplo n.º 6
0
        public static bool CheckIntegrity()
        {
            string query = "PRAGMA integrity_check;";

            MPTVSeriesLog.Write("Executing SQL integrity check");

            SQLiteResultSet results = Execute(query);

            if (results != null)
            {
                if (results.Rows.Count == 1)
                {
                    SQLiteResultSet.Row arr = results.Rows[0];
                    if (arr.fields.Count == 1)
                    {
                        if (arr.fields[0] == "ok")
                        {
                            MPTVSeriesLog.Write("Database integrity check succeeded");
                            return(true);
                        }
                    }
                }
            }
            MPTVSeriesLog.Write("Integrity check failed, database is corrupt. Filename = '{0}'", m_db.DatabaseName);
            return(false);
        }
Exemplo n.º 7
0
        // Loads data into this object based on the given database record.
        public void LoadByRow(SQLiteResultSet.Row row)
        {
            RetrievalInProcess = true;
            ReadOnlyCollection <DBField> fieldList = DBField.GetFieldList(this.GetType());

            // load each field one at a time. they should have been retrieved in the
            // ordering in FieldList
            int i;

            for (i = 0; i < fieldList.Count; i++)
            {
                if (row.fields[i] == "")
                {
                    fieldList[i].SetValue(this, null);
                }
                else
                {
                    fieldList[i].SetValue(this, row.fields[i]);
                }
            }

            // id is always at the end, assign that too
            id = int.Parse(row.fields[i]);

            // all values are in sync with DB so no commit needed.
            commitNeeded       = false;
            RetrievalInProcess = false;
        }
Exemplo n.º 8
0
        public bool Read(SQLiteResultSet.Row row, System.Collections.Hashtable ColumnIndices)
        {
            if (row == null || row.fields.Count == 0)
            {
                return(false);
            }
            string res  = null;
            int    iCol = 0;

            foreach (KeyValuePair <string, DBField> field in m_fields)
            {
                object o = null;
                if (((o = ColumnIndices[field.Key]) != null) ||
                    ((o = ColumnIndices[m_tableName + "." + field.Key]) != null) ||
                    ((o = ColumnIndices[m_tableName + field.Key]) != null))    // because of order bug in sqlite
                {
                    iCol = (int)o;
                    res  = row.fields[iCol];
                    field.Value.Value = res == null ? string.Empty : res;
                }
                else
                {
                    // we have a column in mfields that is not in the database result (or null), as such it is to be empty
                    field.Value.Value = string.Empty;
                }
            }
            m_CommitNeeded = false;
            return(true);
        }
Exemplo n.º 9
0
        private bool verifyIntegrity(SQLiteClient client)
        {
            string query = "PRAGMA integrity_check;";

            logger.Info("Executing SQL integrity check");

            try {
                SQLiteResultSet results = client.Execute(query);
                if (results != null)
                {
                    if (results.Rows.Count == 1)
                    {
                        SQLiteResultSet.Row arr = results.Rows[0];
                        if (arr.fields.Count == 1)
                        {
                            if (arr.fields[0] == "ok")
                            {
                                logger.Info("Database integrity check succeeded");
                                return(true);
                            }
                        }
                    }
                }
            }
            catch (Exception e) {
                logger.Info("Integrity check failed, database is corrupt. Reason = '{0}', Filename = '{1}'", e.Message, client.DatabaseName);
                return(false);
            }
            logger.Info("Integrity check failed, database is corrupt. Filename = '{0}'", client.DatabaseName);
            return(false);
        }
Exemplo n.º 10
0
        public static string Get(SQLiteResultSet results, int iRecord, int column)
        {
            if (null == results)
            {
                Log.Error("DatabaseUtility.Get: Results=null");
                return(string.Empty);
            }
            if (results.Rows.Count < iRecord)
            {
                Log.Error("DatabaseUtility.Get: Recordnr {0} not found", iRecord);
                return(string.Empty);
            }
            if (column < 0 || column >= results.ColumnNames.Count)
            {
                Log.Error("DatabaseUtility.Get: Columnr {0} out of range", column);
                return(string.Empty);
            }
            SQLiteResultSet.Row arr = results.Rows[iRecord];
            if (arr.fields[column] == null)
            {
                Log.Error("DatabaseUtility.Get: Field {0} = null", column);
                return(string.Empty);
            }
            string strLine = (arr.fields[column]).Trim();

            //strLine = strLine.Replace("''","'");
            return(strLine);

            ;
        }
Exemplo n.º 11
0
        public static DateTime GetAsDateTime(SQLiteResultSet results, int iRecord, string aTimestampColum)
        {
            DateTime finalResult = DateTime.MinValue;

            if (results == null || string.IsNullOrEmpty(aTimestampColum) || results.Rows.Count < 1 ||
                results.Rows.Count < iRecord)
            {
                return(finalResult);
            }

            try
            {
                SQLiteResultSet.Row arr = results.Rows[iRecord];
                int?iCol = (int?)results.ColumnIndices[aTimestampColum];
                if (iCol.HasValue)
                {
                    if (arr.fields[iCol.Value] != null)
                    {
                        finalResult = Convert.ToDateTime((arr.fields[iCol.Value]));
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Error("DatabaseUtility:GetAsDateTime: {0}", ex.Message);
            }

            return(finalResult);
        }
Exemplo n.º 12
0
 public bool Read(ref SQLiteResultSet records, int index)
 {
     if (records.Rows.Count > 0 || records.Rows.Count < index)
     {
         SQLiteResultSet.Row row = records.Rows[index];
         return(Read(row, records.ColumnIndices));
     }
     return(false);
 }
Exemplo n.º 13
0
        public static string Get(SQLiteResultSet results, int iRecord, string strColum)
        {
            if (null == results)
            {
                Log.Error("DatabaseUtility.Get: Results=null");
                return(string.Empty);
            }
            if (results.Rows.Count == 0)
            {
                Log.Error("DatabaseUtility.Get: Rowcount=0");
                return(string.Empty);
            }
            if (results.Rows.Count < iRecord)
            {
                Log.Error("DatabaseUtility.Get: Recordnr {0} not found", iRecord);
                return(string.Empty);
            }
            SQLiteResultSet.Row arr = results.Rows[iRecord];
            int?iCol = (int?)results.ColumnIndices[strColum];

            if (iCol.HasValue)
            {
                if (arr.fields[iCol.Value] == null)
                {
                    Log.Error("DatabaseUtility.Get: Field {0} not found", strColum);
                    return(string.Empty);
                }
                string strLine = (arr.fields[iCol.Value]).Trim();
                //strLine = strLine.Replace("''","'");
                return(strLine);
            }
            int pos = strColum.IndexOf(".", StringComparison.Ordinal);

            if (pos < 0)
            {
                Log.Error("DatabaseUtility.Get: Field {0} not found", strColum);
                return(string.Empty);
            }
            strColum = strColum.Substring(pos + 1);
            iCol     = (int?)results.ColumnIndices[strColum];
            if (iCol.HasValue)
            {
                ;
                if (arr.fields[iCol.Value] == null)
                {
                    Log.Error("DatabaseUtility.Get: Field {0} not found", strColum);
                    return(string.Empty);
                }
                string strLine = (arr.fields[iCol.Value]).Trim();
                //strLine = strLine.Replace("''","'");
                return(strLine);
            }

            return(string.Empty);
        }
Exemplo n.º 14
0
        public static string Get(SQLiteResultSet results, int iRecord, string strColum)
        {
            if (null == results)
            {
                return(string.Empty);
            }
            if (results.Rows.Count == 0)
            {
                return(string.Empty);
            }
            if (results.Rows.Count < iRecord)
            {
                return(string.Empty);
            }
            SQLiteResultSet.Row arr = results.Rows[iRecord];
            int iCol = 0;

            if (results.ColumnIndices.ContainsKey(strColum))
            {
                iCol = (int)results.ColumnIndices[strColum];
                if (arr.fields[iCol] == null)
                {
                    return(string.Empty);
                }
                string strLine = (arr.fields[iCol]).Trim();
                //strLine = strLine.Replace("''","'");
                return(strLine);
            }
            int pos = strColum.IndexOf(".", StringComparison.Ordinal);

            if (pos < 0)
            {
                return(string.Empty);
            }
            strColum = strColum.Substring(pos + 1);
            if (results.ColumnIndices.ContainsKey(strColum))
            {
                iCol = (int)results.ColumnIndices[strColum];
                if (arr.fields[iCol] == null)
                {
                    return(string.Empty);
                }
                string strLine = (arr.fields[iCol]).Trim();
                //strLine = strLine.Replace("''","'");
                return(strLine);
            }

            return(string.Empty);
        }
Exemplo n.º 15
0
        private DBMovieInfo getMovieInfo(string idMovie)
        {
            DBMovieInfo movieRes = new DBMovieInfo();

            try
            {
                SQLiteClient    mp_db      = new SQLiteClient(Config.GetFile(Config.Dir.Database, @"VideoDatabaseV5.db3"));
                SQLiteResultSet sqlResults = mp_db.Execute("SELECT * FROM movieinfo WHERE idMovie LIKE '" + idMovie + "'");

                SQLiteResultSet.Row          sqlRow  = sqlResults.GetRow(0);
                System.Collections.Hashtable columns = sqlResults.ColumnIndices;

                movieRes.Popularity = int.Parse(sqlResults.GetField(0, int.Parse(columns["strVotes"].ToString())));
                movieRes.Runtime    = int.Parse(sqlResults.GetField(0, int.Parse(columns["runtime"].ToString())));
                movieRes.Score      = float.Parse(sqlResults.GetField(0, int.Parse(columns["fRating"].ToString())));
                movieRes.Year       = int.Parse(sqlResults.GetField(0, int.Parse(columns["iYear"].ToString())));

                string Title = sqlResults.GetField(0, int.Parse(columns["strTitle"].ToString()));
                if (!Title.Contains("unknown"))
                {
                    movieRes.Title = Title;
                }

                string Certification = sqlResults.GetField(0, int.Parse(columns["mpaa"].ToString()));
                if (!Certification.Contains("unknown"))
                {
                    movieRes.Certification = Certification;
                }

                string Tagline = sqlResults.GetField(0, int.Parse(columns["strTagLine"].ToString()));
                if (!Tagline.Contains("unknown"))
                {
                    movieRes.Tagline = Tagline;
                }

                string Summary = sqlResults.GetField(0, int.Parse(columns["strPlotOutline"].ToString()));
                if (!Summary.Contains("unknown"))
                {
                    movieRes.Summary = Summary;
                }

                string imdb_id = sqlResults.GetField(0, int.Parse(columns["IMDBID"].ToString()));
                if (!imdb_id.Contains("unknown"))
                {
                    movieRes.ImdbID = imdb_id;
                }

                string genreMain = sqlResults.GetField(0, int.Parse(columns["strGenre"].ToString()));
                if (!genreMain.Contains("unknown"))
                {
                    string[] genreSplit = genreMain.Split('/');
                    foreach (string genre in genreSplit)
                    {
                        movieRes.Genres.Add(genre.Trim());
                    }
                }

                string castMain = sqlResults.GetField(0, int.Parse(columns["strCast"].ToString()));
                if (!castMain.Contains("unknown"))
                {
                    string[] castSplit = castMain.Split('\n');
                    foreach (string cast in castSplit)
                    {
                        string castFinal = cast;
                        if (cast.Contains(" as "))
                        {
                            castFinal = cast.Remove(cast.IndexOf(" as "));
                        }
                        movieRes.Actors.Add(castFinal.Trim());
                    }
                }

                string idDirector = sqlResults.GetField(0, int.Parse(columns["idDirector"].ToString()));
                if (!castMain.Contains("unknown"))
                {
                    SQLiteResultSet sqlDirector = mp_db.Execute("SELECT strActor FROM actors WHERE idActor LIKE '" + idDirector + "'");
                    movieRes.Directors.Add(sqlDirector.GetField(0, 0));
                }

                string writers = sqlResults.GetField(0, int.Parse(columns["strCredits"].ToString()));
                if (!writers.Contains("unknown"))
                {
                    string[] writerArray = writers.Split(new string[] { "\n", "   " }, StringSplitOptions.None);
                    foreach (string writer in writerArray)
                    {
                        if (!movieRes.Writers.Contains(writer.Trim()))
                        {
                            movieRes.Writers.Add(writer.Trim());
                        }
                    }
                }

                movieRes.GetSourceMovieInfo(SourceInfo).Identifier = sqlResults.GetField(0, int.Parse(columns["idMovie"].ToString())).ToString();

                mp_db.Close();
            }
            catch
            {
                return(null);
            }

            return(movieRes);
        }