private static void GetItemsFromDatabase()
{
List <int> numberOfPeakMatchesList = new List <int>();
List <string> toDatabaseList = new List <string>();
var Binner = new Binner();
using (
System.Data.SQLite.SQLiteConnection conn =
new System.Data.SQLite.SQLiteConnection("data source=" + LibraryDBPath + ".db3"))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "ATTACH '" + QueryDBPath + ".db3' AS db2;";
cmd.ExecuteNonQuery();
cmd.CommandText = "ATTACH '" + QueryLibraryPairsDB + ".db3' AS dbPair;";
cmd.ExecuteNonQuery();
//This select statement is equivalent to if I had used inner joins between the tables. This version is just in an older style.
cmd.CommandText = "SELECT main.Spectrum.mzList, main.Spectrum.charge, main.Spectrum.precursor, main.Spectrum.peptide, main.Files.path, " +
"db2.QuerySpectrum.mzList, db2.QuerySpectrum.charge, db2.QuerySpectrum.precursor, db2.Files.path " +
"FROM main.Spectrum, main.Files, db2.Files, db2.QuerySpectrum, dbPair.QueryLibraryPair " +
"WHERE main.Spectrum.ID = dbPair.QueryLibraryPair.id_Library AND db2.QuerySpectrum.ID = dbPair.QueryLibraryPair.id_Query " +
"AND db2.QuerySpectrum.codex = db2.Files.codex AND main.Spectrum.codex = main.Files.codex " +
"AND dbPair.QueryLibraryPair.PeakMatchNumber<11;";
using (var reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleResult))
{
while (reader.Read())
{
Library oneLibrary = new Library(Convert.ToInt32(reader.GetString(1)), reader.GetString(3), reader.GetString(0).Split(',').Select(n => Convert.ToDouble(n)).ToArray(), reader.GetString(4), Convert.ToDouble(reader.GetString(2)));
Query oneQuery = new Query(Convert.ToInt32(reader.GetString(6)), reader.GetString(5).Split(',').Select(n => Convert.ToDouble(n)).ToArray(), reader.GetString(8), Convert.ToDouble(reader.GetString(7)));
listOfAllLibraries.Add(oneLibrary);
listOfAllQueries.Add(oneQuery);
if (listOfAllLibraries.Count % 50 == 0)
{
Console.WriteLine("processed: " + listOfAllLibraries.Count);
}
if (listOfAllLibraries.Count >= 500)
//if(listOfAllLibraries.Count %1000 ==0)
{
Binner.processor(listOfAllQueries, listOfAllLibraries);
listOfAllLibraries = new List <Library>();
listOfAllQueries = new List <Query>();
// break;
}
}
}
Binner.sendToFile(outputFilename);
}
conn.Close();
}
}
