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);
}
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);
}
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);
}
/// <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);
}
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);
}
// 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;
}
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);
}
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);
}
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);
;
}
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);
}
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);
}
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);
}
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);
}
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);
}
