public List <IntranetLibraryItem> GetIntranetLibraryItemsSummary()
{
List <IntranetLibraryItem> results = new List <IntranetLibraryItem>();
using (var connection = GetConnection())
{
connection.Open();
string command_string = "SELECT filename, md5 FROM LibraryItem WHERE 1=1 ";
using (var command = new SQLiteCommand(command_string, connection))
{
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
IntranetLibraryItem result = new IntranetLibraryItem();
results.Add(result);
result.filename = reader.GetString(0);
result.md5 = reader.GetString(1);
}
}
}
return(results);
}
public List <IntranetLibraryItem> GetIntranetLibraryItemsSummary()
{
List <IntranetLibraryItem> results = new List <IntranetLibraryItem>();
lock (DBAccessLock.db_access_lock)
{
using (var connection = GetConnection())
{
connection.Open();
string command_string = "SELECT filename, md5 FROM LibraryItem WHERE 1=1 ";
using (var command = new SQLiteCommand(command_string, connection))
{
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
IntranetLibraryItem result = new IntranetLibraryItem();
results.Add(result);
result.filename = reader.GetString(0);
result.md5 = reader.GetString(1);
}
reader.Close();
}
}
connection.Close();
}
//
// see SO link above at the `DBAccessLock.db_access_lock` declaration.
//
// We keep this *inside* the critical section so that we know we'll be the only active SQLite
// action which just transpired.
// *This* is also the reason why I went with a *global* lock (singeton) for *all* databases,
// even while *theoretically* this is *wrong* or rather: *unneccessary* as the databases
// i.e. Qiqqa Libraries shouldn't bite one another. I, however, need to ensure that the
// added `System.Data.SQLite.SQLiteConnection.ClearAllPools();` statements don't foul up
// matters in another library while lib A I/O is getting cleaned up.
//
// In short: Yuck. + Cave canem.
//
SQLiteConnection.ClearAllPools();
}
return(results);
}
public List <IntranetLibraryItem> GetIntranetLibraryItems(string filename)
{
List <IntranetLibraryItem> results = new List <IntranetLibraryItem>();
using (var connection = GetConnection())
{
connection.Open();
string command_string = "SELECT filename, last_updated_by, md5, data FROM LibraryItem WHERE 1=1 ";
if (null != filename)
{
command_string = command_string + " AND filename=@filename";
}
using (var command = new SQLiteCommand(command_string, connection))
{
if (null != filename)
{
command.Parameters.AddWithValue("@filename", filename);
}
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
IntranetLibraryItem result = new IntranetLibraryItem();
results.Add(result);
result.filename = reader.GetString(0);
result.last_updated_by = reader.GetString(1);
result.md5 = reader.GetString(2);
long total_bytes = reader.GetBytes(3, 0, null, 0, 0);
result.data = new byte[total_bytes];
long total_bytes2 = reader.GetBytes(3, 0, result.data, 0, (int)total_bytes);
if (total_bytes != total_bytes2)
{
throw new Exception("Error reading blob - blob size different on each occasion.");
}
}
}
}
return(results);
}
public List <IntranetLibraryItem> GetIntranetLibraryItemsSummary()
{
List <IntranetLibraryItem> results = new List <IntranetLibraryItem>();
List <Exception> database_corruption = new List <Exception>();
try
{
lock (DBAccessLock.db_access_lock)
{
using (var connection = GetConnection())
{
connection.Open();
string command_string = "SELECT filename, md5 FROM LibraryItem WHERE 1=1 ";
using (var command = new SQLiteCommand(command_string, connection))
{
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
IntranetLibraryItem result = new IntranetLibraryItem();
results.Add(result);
try
{
result.filename = reader.GetString(0);
result.md5 = reader.GetString(1);
}
catch (Exception ex)
{
string msg = String.Format("IntranetLibraryDB::GetIntranetLibraryItemsSummary: Database record #{3} decode failure for DB '{0}': filename_id={1}, md5={2}.",
library_path,
String.IsNullOrEmpty(result.filename) ? "???" : result.filename,
String.IsNullOrEmpty(result.md5) ? "???" : result.md5,
reader.StepCount // ~= results.Count + database_corruption.Count
);
Logging.Error(ex, "{0}", msg);
Exception ex2 = new Exception(msg, ex);
database_corruption.Add(ex2);
}
}
reader.Close();
}
}
connection.Close();
}
//
// see SO link above at the `DBAccessLock.db_access_lock` declaration.
//
// We keep this *inside* the critical section so that we know we'll be the only active SQLite
// action which just transpired.
// *This* is also the reason why I went with a *global* lock (singleton) for *all* databases,
// even while *theoretically* this is *wrong* or rather: *unnecessary* as the databases
// i.e. Qiqqa Libraries shouldn't bite one another. I, however, need to ensure that the
// added `System.Data.SQLite.SQLiteConnection.ClearAllPools();` statements don't foul up
// matters in library B while lib A I/O is getting cleaned up.
//
// In short: Yuck. + Cave canem.
//
SQLiteConnection.ClearAllPools();
}
}
catch (Exception ex)
{
Logging.Error(ex, "IntranetLibraryDB::GetLibraryItemsSummary: Database I/O failure for DB '{0}'.", library_path);
LibraryDB.FurtherDiagnoseDBProblem(ex, database_corruption, library_path);
throw;
}
if (database_corruption.Count > 0)
{
// report database corruption: the user may want to recover from this ASAP!
if (MessageBoxes.AskErrorQuestion(true, "INTRANET Library (Sync Point) '{0}' has some data corruption. Do you want to abort the application to attempt recovery using external tools, e.g. a data restore from backup?\n\nWhen you answer NO, we will continue with what we could recover so far instead.\n\n\nConsult the Qiqqa logfiles to see the individual corruptions reported.",
library_path))
{
Logging.Warn("User chose to abort the application on database corruption report");
Environment.Exit(3);
}
}
return(results);
}
public List <IntranetLibraryItem> GetIntranetLibraryItems(string filename, int MaxRecordCount = 0)
{
List <IntranetLibraryItem> results = new List <IntranetLibraryItem>();
lock (DBAccessLock.db_access_lock)
{
using (var connection = GetConnection())
{
connection.Open();
string command_string = "SELECT filename, last_updated_by, md5, data FROM LibraryItem WHERE 1=1 ";
command_string += turnArgumentIntoQueryPart("filename", filename);
if (MaxRecordCount > 0)
{
// http://www.sqlitetutorial.net/sqlite-limit/
command_string += " LIMIT @maxnum";
}
using (var command = new SQLiteCommand(command_string, connection))
{
turnArgumentIntoQueryParameter(command, "filename", filename);
if (MaxRecordCount > 0)
{
command.Parameters.AddWithValue("@maxnum", MaxRecordCount);
}
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
IntranetLibraryItem result = new IntranetLibraryItem();
results.Add(result);
result.filename = reader.GetString(0);
result.last_updated_by = reader.GetString(1);
result.md5 = reader.GetString(2);
long total_bytes = reader.GetBytes(3, 0, null, 0, 0);
result.data = new byte[total_bytes];
long total_bytes2 = reader.GetBytes(3, 0, result.data, 0, (int)total_bytes);
if (total_bytes != total_bytes2)
{
throw new Exception("Error reading blob - blob size different on each occasion.");
}
}
reader.Close();
}
}
connection.Close();
}
//
// see SO link above at the `DBAccessLock.db_access_lock` declaration.
//
// We keep this *inside* the critical section so that we know we'll be the only active SQLite
// action which just transpired.
// *This* is also the reason why I went with a *global* lock (singeton) for *all* databases,
// even while *theoretically* this is *wrong* or rather: *unneccessary* as the databases
// i.e. Qiqqa Libraries shouldn't bite one another. I, however, need to ensure that the
// added `System.Data.SQLite.SQLiteConnection.ClearAllPools();` statements don't foul up
// matters in another library while lib A I/O is getting cleaned up.
//
// In short: Yuck. + Cave canem.
//
SQLiteConnection.ClearAllPools();
}
return(results);
}
