public void UpdateGrid()
{
using (var session = Workspace.OpenSession())
{
IEnumerable<TableData> tableDatas = null;
using (var longWaitDialog = new LongWaitDialog(TopLevelControl, "Querying Table Sizes"))
{
var broker = new LongOperationBroker(b => { tableDatas = RequeryGrid(b, session); }, longWaitDialog
, session);
if (!broker.LaunchJob() || tableDatas == null)
{
return;
}
dataGridView1.Rows.Clear();
foreach (var tableData in tableDatas)
{
var row = dataGridView1.Rows[dataGridView1.Rows.Add()];
row.Cells[colTableName.Index].Value = tableData.Name;
row.Cells[colRowCount.Index].Value = tableData.RowCount;
row.Cells[colDataFileSize.Index].Value = tableData.DataFileSize;
row.Cells[colFreeSpace.Index].Value = tableData.FreeSpace;
row.Cells[colIndexSize.Index].Value = tableData.IndexSize;
}
}
}
}
public void Run(LongOperationBroker longOperationBroker)
{
int attemptNumber = 1;
longOperationBroker.UpdateStatusMessage("Loading peptide analyses");
while (true)
{
var savedData = Workspace.SavedData;
var requestedPeptideAnalyses = new Dictionary<long, bool>();
foreach (var id in PeptideAnalysisIds)
{
PeptideAnalysisData existing;
if (null != savedData.PeptideAnalyses && savedData.PeptideAnalyses.TryGetValue(id, out existing))
{
if (existing.ChromatogramsWereLoaded)
{
continue;
}
}
requestedPeptideAnalyses[id] = true;
}
if (requestedPeptideAnalyses.Count == 0)
{
return;
}
if (Workspace.DatabasePoller.TryLoadAndMergeChanges(requestedPeptideAnalyses))
{
return;
}
attemptNumber++;
longOperationBroker.UpdateStatusMessage("Loading peptide analyses. Attempt #" + attemptNumber);
}
}
private void DeleteDataFiles(LongOperationBroker broker, ICollection<long> dataFileIds)
{
var sqlDataFileIds = new StringBuilder("(");
var strComma = "";
foreach (var id in dataFileIds)
{
sqlDataFileIds.Append(strComma);
strComma = ",";
sqlDataFileIds.Append(id);
}
sqlDataFileIds.Append(")");
var sqlFileAnalysisIds = "(SELECT Id FROM DbPeptideFileAnalysis WHERE MsDataFile IN " + sqlDataFileIds + ")";
var sqlChromatogramSetIds = "(SELECT ChromatogramSet FROM DbPeptideFileAnalysis WHERE MsDataFile IN " +
sqlFileAnalysisIds + ")";
using (var session = Workspace.OpenSession())
{
var connection = session.Connection;
try
{
Workspace.LockTables(connection);
using (var transaction = connection.BeginTransaction())
using (var cmd = connection.CreateCommand())
{
broker.UpdateStatusMessage("Deleting chromatograms");
cmd.CommandText = "DELETE FROM DbChromatogram WHERE ChromatogramSet IN " +
sqlChromatogramSetIds;
cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE FROM DbChromatogramSet WHERE PeptideFileAnalysis IN " +
sqlFileAnalysisIds;
cmd.ExecuteNonQuery();
broker.UpdateStatusMessage("Deleting peaks");
cmd.CommandText = ("DELETE FROM DbPeak WHERE PeptideFileAnalysis IN " + sqlFileAnalysisIds);
cmd.ExecuteNonQuery();
broker.UpdateStatusMessage("Deleting file analyses");
cmd.CommandText = ("DELETE FROM DbPeptideFileAnalysis WHERE MsDataFile IN " + sqlDataFileIds);
cmd.ExecuteNonQuery();
broker.UpdateStatusMessage("Deleting search results");
cmd.CommandText = ("DELETE FROM DbPeptideSpectrumMatch WHERE MsDataFile IN " +
sqlDataFileIds);
cmd.ExecuteNonQuery();
broker.UpdateStatusMessage("Deleting data files");
cmd.CommandText = ("DELETE FROM DbMsDataFile WHERE Id IN " + sqlDataFileIds);
broker.UpdateStatusMessage("Updating parent tables");
cmd.CommandText =
"UPDATE DbPeptideAnalysis SET FileAnalysisCount = (SELECT COUNT(Id) FROM DbPeptideFileAnalysis WHERE PeptideAnalysis = DbPeptideAnalysis.Id)";
cmd.ExecuteNonQuery();
// session.CreateSQLQuery("UPDATE DbPeptide SET SearchResultCount = (SELECT Count(Id) FROM DbPeptideSearchResult WHERE Peptide = DbPeptide.Id)")
// .ExecuteUpdate();
broker.SetIsCancelleable(false);
broker.UpdateStatusMessage("Committing transaction");
transaction.Commit();
}
}
finally
{
Workspace.UnlockTables(connection);
}
}
}
private void Requery()
{
var locks = new List<DbLock>();
using (var session = Workspace.OpenSession())
{
using (var longWaitDialog = new LongWaitDialog(TopLevelControl, "Querying Database"))
{
var broker = new LongOperationBroker(b => session.CreateCriteria(typeof (DbLock)).List(locks),
longWaitDialog, session);
if (broker.LaunchJob())
{
BeginInvoke(new Action<List<DbLock>>(DisplayResults), locks);
}
}
}
}
public override bool RunLongJob(Control owner, Action<IProgressMonitor> job)
{
using (var longWaitDialog = new LongWaitDialog(owner.TopLevelControl, Program.AppName))
{
var longOperationBroker =
new LongOperationBroker(
broker =>
job.Invoke(ProgressMonitorImpl.NewProgressMonitorImpl(new ProgressStatus("Working"),
iProgress =>
{
try
{
broker.
UpdateStatusMessage(
iProgress + "% complete");
return true;
}
catch (JobCancelledException)
{
return false;
}
})), longWaitDialog);
longOperationBroker.LaunchJob();
return !longOperationBroker.WasCancelled;
}
}
private void btnRequery_Click(object sender, EventArgs e)
{
var lstResultData = new List<ResultData>();
var activeCohortKeys = new HashSet<CohortKey>();
using (var session = Workspace.OpenSession())
{
var query = session.CreateQuery("SELECT d.PeptideFileAnalysis.Id,"
+ "\nd.PeptideFileAnalysis.PeptideAnalysis.Peptide.Id,"
+ "\nd.PeptideFileAnalysis.MsDataFile.TimePoint,"
+ "\nd.PeptideFileAnalysis.MsDataFile.Cohort,"
+ "\nd.PeptideFileAnalysis.MsDataFile.Sample,"
+ "\nd.TracerPercent,"
+ "\nd.TotalArea,"
+ "\nd.RatioToBase,"
+ "\nd.PeptideFileAnalysis.Turnover * 100,"
+ "\nd.PeptideFileAnalysis.PrecursorEnrichment * 100"
+ "\nFROM DbPeak d"
+ "\nWHERE d.PeptideFileAnalysis.DeconvolutionScore > :minScore "
+ "\nAND d.PeptideFileAnalysis.ValidationStatus <> " + (int) ValidationStatus.reject);
if (!string.IsNullOrEmpty(tbxMinScore.Text))
{
query.SetParameter("minScore", double.Parse(tbxMinScore.Text));
}
else
{
query.SetParameter("minScore", 0.0);
}
var rowDataArrays = new List<object[]>();
using (var longWaitDialog = new LongWaitDialog(TopLevelControl, "Executing query"))
{
var broker = new LongOperationBroker(b => query.List(rowDataArrays),
longWaitDialog, session);
if (!broker.LaunchJob())
{
return;
}
}
bool groupByCohort = cbxGroupByCohort.Checked;
bool groupByTimePoint = cbxGroupByTimePoint.Checked;
bool groupBySample = cbxGroupBySample.Checked;
var rawRows = rowDataArrays.Select(r => new RawRow()
{
PeptideFileAnalysisId = (long) r[0],
PeptideId = (long) r[1],
CohortKey = new CohortKey(
groupByCohort ? (string) r[3] : null,
groupByTimePoint ? (double?) r[2] : null,
groupBySample ? (string) r[4] : null),
TracerPercent = (double) r[5],
TotalArea = (double) r[6],
RatioToBase = (double) r[7],
Turnover = (double?) r[8],
PrecursorEnrichment = (double?) r[9],
});
var rowDatas = rawRows.GroupBy(r => r.PeptideFileAnalysisId, (id,peaks) => AggregatePeaks(id, peaks));
var byProtein = cbxByProtein.Checked;
var groupedRowDatas = new Dictionary<string, IList<RowData>>();
foreach (var rowData in rowDatas)
{
var peptide = Workspace.Peptides.GetChild(rowData.PeptideId);
if (peptide == null)
{
continue;
}
var key = byProtein ? peptide.ProteinName : peptide.Sequence;
IList<RowData> list;
if (!groupedRowDatas.TryGetValue(key, out list))
{
list = new List<RowData>();
groupedRowDatas.Add(key, list);
}
list.Add(rowData);
}
var resultRows = new List<ResultRow>();
foreach (var entry in groupedRowDatas)
{
resultRows.Add(GetResultRow(entry.Value, byProtein));
}
dataGridView1.Rows.Clear();
if (resultRows.Count > 0)
{
dataGridView1.Rows.Add(resultRows.Count());
for (int iRow = 0; iRow < resultRows.Count; iRow ++)
{
var resultRow = resultRows[iRow];
var row = dataGridView1.Rows[iRow];
row.Cells[colPeptide.Index].Value = resultRow.PeptideSequence;
row.Cells[colProteinName.Index].Value = resultRow.ProteinName;
row.Cells[colProteinKey.Index].Value = resultRow.ProteinKey;
row.Cells[colProteinDescription.Index].Value
= row.Cells[colProteinDescription.Index].ToolTipText = resultRow.ProteinDescription;
foreach (var entry in resultRow.ResultDatas)
{
Columns columns;
if (!_columnsDict.TryGetValue(entry.Key, out columns))
{
string cohortName = entry.Key.Cohort ?? "";
if (entry.Key.TimePoint != null)
{
cohortName += " " + entry.Key.TimePoint;
}
columns = AddColumns(string.IsNullOrEmpty(cohortName) ? "" : cohortName + " ");
_columnsDict.Add(entry.Key, columns);
}
row.Cells[columns.ReplicateCountColumn.Index].Value = entry.Value.TracerPercentByArea.Length;
SetColumnValues(columns.TracerAmountsByAreaColumns, row, entry.Value.TracerPercentByArea);
SetColumnValues(columns.TracerAmountsBySlopeColumns, row, entry.Value.TracerPercentBySlope);
SetColumnValues(columns.PrecursorEnrichmentColumns, row, entry.Value.PrecursorEnrichment);
SetColumnValues(columns.TurnoverColumns, row, entry.Value.Turnover);
SetColumnValues(columns.AreaUnderCurveColumns, row, entry.Value.AreaUnderCurve);
lstResultData.Add(entry.Value);
activeCohortKeys.Add(entry.Key);
}
}
}
}
dataGridViewSummary.Rows.Clear();
SetSummary("Tracer % (area)", lstResultData.Select(r=>r.TracerPercentByArea));
SetSummary("Tracer % (slope)", lstResultData.Select(r => r.TracerPercentBySlope));
SetSummary("Precursor Enrichment", lstResultData.Select(r => r.PrecursorEnrichment));
SetSummary("Turnover", lstResultData.Select(r => r.Turnover));
SetSummary("Area Under Curve", lstResultData.Select(r => r.AreaUnderCurve));
foreach (var entry in _columnsDict.ToArray())
{
if (!activeCohortKeys.Contains(entry.Key))
{
dataGridView1.Columns.Remove(entry.Value.ReplicateCountColumn);
RemoveDataColumns(entry.Value.TracerAmountsByAreaColumns);
RemoveDataColumns(entry.Value.TracerAmountsBySlopeColumns);
RemoveDataColumns(entry.Value.PrecursorEnrichmentColumns);
RemoveDataColumns(entry.Value.TurnoverColumns);
RemoveDataColumns(entry.Value.AreaUnderCurveColumns);
_columnsDict.Remove(entry.Key);
}
}
UpdateColumnVisibility();
}
private void BtnRequeryOnClick(object sender, EventArgs e)
{
var halfLifeSettings = HalfLifeSettings;
Settings.Default.Reload();
Settings.Default.HalfLifeSettings = halfLifeSettings;
Settings.Default.Save();
var calculator = new HalfLifeCalculator(Workspace, halfLifeSettings)
{
ExcludedTimePoints = UpdateTimePoints(),
};
using (var longWaitDialog = new LongWaitDialog(TopLevelControl, "Calculating Half Lives"))
{
var longOperationBroker = new LongOperationBroker(calculator.Run, longWaitDialog);
if (!longOperationBroker.LaunchJob())
{
return;
}
}
var viewInfo = bindingSource1.ViewInfo;
var rows = calculator.ResultRows.Select(row => new ResultRow(this, row)).ToArray();
if (viewInfo == null || "default" == viewInfo.Name)
{
viewInfo = new ViewInfo(ColumnDescriptor.RootColumn(bindingSource1.ViewInfo.DataSchema, typeof(ResultRow)),
GetDefaultViewSpec(calculator.ByProtein));
bindingSource1.SetViewContext(GetViewContext(rows), viewInfo);
}
bindingSource1.RowSource = rows;
}
public void Run(LongOperationBroker longOperationBroker)
{
longOperationBroker.CancellationToken.Register(Session.CancelQuery);
Query.List(Results);
}
private void BtnRequeryOnClick(object sender, EventArgs e)
{
var calculator = new HalfLifeCalculator(Workspace, HalfLifeSettings.Default)
{
MaxResults = MaxResults,
};
using (var longWaitDialog = new LongWaitDialog(TopLevelControl, "Calculating Half Lives"))
{
var longOperationBroker = new LongOperationBroker(calculator.Run, longWaitDialog);
if (!longOperationBroker.LaunchJob())
{
return;
}
}
UpdateRows(calculator);
}
private void btnRequery_Click(object sender, EventArgs e)
{
var halfLifeCalculator = new HalfLifeCalculator(Workspace, HalfLifeSettings)
{
ByFile = cbxGroupByFile.Checked,
};
using (var longWaitDialog = new LongWaitDialog(TopLevelControl, "Calculating Half Lives"))
{
var longOperationBroker = new LongOperationBroker(halfLifeCalculator.Run, longWaitDialog);
if (!longOperationBroker.LaunchJob())
{
return;
}
}
bool byCohort = cbxGroupByCohort.Checked;
bool byTimePoint = cbxGroupByTimePoint.Checked;
bool bySample = cbxGroupBySample.Checked;
bool byFile = cbxGroupByFile.Checked;
var displayRows = new List<DisplayRow>();
foreach (var resultRow in halfLifeCalculator.ResultRows)
{
var displayRow = new DisplayRow(halfLifeCalculator, resultRow);
var rowDatasByCohort = new Dictionary<GroupKey, List<HalfLifeCalculator.ProcessedRowData>>();
foreach (var halfLife in resultRow.HalfLives)
{
if (resultRow.HalfLives.Count > 1 && string.IsNullOrEmpty(halfLife.Key) != byCohort)
{
continue;
}
foreach (var rowData in halfLife.Value.FilteredRowDatas)
{
GroupKey cohortKey = new GroupKey(
byCohort ? rowData.RawRowData.MsDataFile.Cohort : null,
byTimePoint ? rowData.RawRowData.MsDataFile.TimePoint : null,
bySample ? rowData.RawRowData.MsDataFile.Sample : null,
byFile ? rowData.RawRowData.MsDataFile.Name : null);
List<HalfLifeCalculator.ProcessedRowData> list;
if (!rowDatasByCohort.TryGetValue(cohortKey, out list))
{
list = new List<HalfLifeCalculator.ProcessedRowData>();
rowDatasByCohort.Add(cohortKey, list);
}
list.Add(rowData);
}
}
foreach (var cohortRowDatas in rowDatasByCohort)
{
displayRow.Results.Add(cohortRowDatas.Key, new GroupResult(this, displayRow, cohortRowDatas.Key, new ResultData(cohortRowDatas.Value)));
}
displayRows.Add(displayRow);
}
var viewInfo = bindingSource1.ViewInfo;
var dataSchema = new TopographDataSchema(Workspace);
if (viewInfo == null || "default" == viewInfo.Name)
{
viewInfo= new ViewInfo(ColumnDescriptor.RootColumn(dataSchema, typeof(DisplayRow)),
GetDefaultViewSpec(halfLifeCalculator.ByProtein));
}
var viewContext = new TopographViewContext(Workspace, typeof (DisplayRow), displayRows,
GetDefaultViewSpec(halfLifeCalculator.ByProtein));
bindingSource1.SetViewContext(viewContext, viewInfo);
bindingSource1.RowSource = displayRows;
dataGridViewSummary.Rows.Clear();
SetSummary("Tracer %", displayRows.Select(dr=>dr.Results).SelectMany(r=>r.Values
.Select(cohortResult=>cohortResult.GetResultData().TracerPercentByArea)));
SetSummary("Precursor Enrichment", displayRows.Select(dr=>dr.Results).SelectMany(r=>r.Values
.Select(cohortResult=>cohortResult.GetResultData().IndPrecursorEnrichment)));
SetSummary("Turnover", displayRows.Select(dr=>dr.Results).SelectMany(r=>r.Values
.Select(cohortResult=>cohortResult.GetResultData().IndTurnover)));
SetSummary("Area Under Curve", displayRows.Select(dr=>dr.Results).SelectMany(r=>r.Values
.Select(cohortResult=>cohortResult.GetResultData().AreaUnderCurve)));
}
public void Run(LongOperationBroker longOperationBroker)
{
using (var session = PeptideAnalysis.Workspace.OpenSession())
{
var peptideAnalysis = session.Get<DbPeptideAnalysis>(PeptideAnalysis.Id);
var peptide = peptideAnalysis.Peptide;
var psmTimesByDataFileId = peptide.PsmTimesByDataFileId(session);
session.BeginTransaction();
foreach (var msDataFile in MsDataFiles)
{
var dbPeptideFileAnalysis = PeptideFileAnalysis.CreatePeptideFileAnalysis(
session, msDataFile, peptideAnalysis, psmTimesByDataFileId);
session.Save(dbPeptideFileAnalysis);
peptideAnalysis.FileAnalysisCount++;
}
session.Update(peptideAnalysis);
session.Save(new DbChangeLog(PeptideAnalysis));
session.Transaction.Commit();
}
PeptideAnalysis.Workspace.DatabasePoller.MergeChangesNow();
}
public override void Delete()
{
IList<MsDataFile> dataFiles = GetSelectedRows<DataFileRow>(_form.gridView).Select(row=>row.MsDataFile).ToArray();
if (dataFiles.Count == 0)
{
return;
}
string message;
if (dataFiles.Count == 1)
{
message = "Are you sure you want to remove this data file from the workspace?";
}
else
{
message = string.Format(
"Are you sure you want to remove these {0} data files from the workspace?", dataFiles.Count);
}
message += "\nAll search results and analyses of these files will also be deleted.";
if (MessageBox.Show(_form, message, Program.AppName, MessageBoxButtons.OKCancel) != DialogResult.OK)
{
return;
}
using (var longWaitDialog = new LongWaitDialog(_form, "Deleting data files"))
{
var dataFileIds = dataFiles.Select(dataFile => dataFile.Id).ToArray();
var longWaitBroker = new LongOperationBroker((b => _form.DeleteDataFiles(b, dataFileIds)), longWaitDialog);
longWaitBroker.LaunchJob();
}
_form.Workspace.DatabasePoller.LoadAndMergeChanges(null);
}
public void Run(LongOperationBroker longOperationBroker)
{
longOperationBroker.CancellationToken.Register(Cancel);
_job.Invoke(longOperationBroker);
}
public void Run(LongOperationBroker longOperationBroker)
{
if (RequiresPrecursorPool)
{
longOperationBroker.UpdateStatusMessage("Querying precursor pools");
_precursorPools = GetPrecursorPools();
}
RowDatas = new List<RawRowData>();
foreach (var peptideAnalysis in Workspace.PeptideAnalyses.ToArray())
{
foreach (var peptideFileAnalysis in peptideAnalysis.FileAnalyses)
{
if (longOperationBroker.WasCancelled)
{
return;
}
var rawRowData = ToRawRowData(peptideFileAnalysis);
if (null != rawRowData)
{
RowDatas.Add(rawRowData);
}
}
}
var groupedRowDatas = new Dictionary<String, List<RawRowData>>();
var cohorts = new HashSet<String> {""};
longOperationBroker.UpdateStatusMessage("Grouping results");
foreach (var rowData in RowDatas)
{
if (longOperationBroker.WasCancelled)
{
return;
}
if (rowData.ValidationStatus == ValidationStatus.reject)
{
continue;
}
cohorts.Add(GetCohort(rowData));
var key = ByProtein ? rowData.ProteinName : rowData.Peptide.Sequence;
key = key ?? "";
List<RawRowData> list;
if (!groupedRowDatas.TryGetValue(key, out list))
{
list = new List<RawRowData>();
groupedRowDatas.Add(key, list);
}
list.Add(rowData);
}
Cohorts = cohorts;
var groupRowDatasList = groupedRowDatas.ToArray();
var resultRows = new List<ResultRow>();
for (int iGroupRow = 0; iGroupRow < groupRowDatasList.Count(); iGroupRow++)
{
longOperationBroker.UpdateStatusMessage(string.Format("Calculating final results {0}/{1}", iGroupRow, groupRowDatasList.Length));
var entry = groupRowDatasList[iGroupRow];
if (longOperationBroker.WasCancelled)
{
return;
}
var resultRow = CalculateResultRow(entry.Value);
resultRows.Add(resultRow);
}
ResultRows = resultRows;
}
public void DisplayLongOperationUi(LongOperationBroker broker)
{
LongOperationBroker = broker;
Timer1OnTick(timer1, new EventArgs());
ShowDialog(ParentWindow);
}
public void Run(LongOperationBroker broker)
{
_longOperationBroker = broker;
_longOperationBroker.CancellationToken.Register(Cancel);
broker.UpdateStatusMessage("Opening file");
using (var connection = OpenConnection())
{
int dbVersion = ReadSchemaVersion(connection);
if (dbVersion == CurrentVersion)
{
return;
}
var transaction = connection.BeginTransaction();
if (dbVersion < 2)
{
broker.UpdateStatusMessage("Upgrading from version 1 to 2");
CreateCommand(connection, "ALTER TABLE DbPeptideAnalysis ADD COLUMN ExcludedMasses BLOB").
ExecuteNonQuery();
CreateCommand(connection, "UPDATE DbPeptideAnalysis SET ExcludedMasses = ExcludedMzs").ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbPeptideFileAnalysis ADD COLUMN ExcludedMasses BLOB").
ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbPeptideFileAnalysis ADD COLUMN OverrideExcludedMasses INTEGER")
.ExecuteNonQuery();
CreateCommand(connection,
"UPDATE DbPeptideFileAnalysis SET ExcludedMasses = ExcludedMzs, OverrideExcludedMasses = OverrideExcludedMzs")
.ExecuteNonQuery();
}
if (dbVersion < 3)
{
broker.UpdateStatusMessage("Upgrading from version 2 to 3");
CreateCommand(connection,
"CREATE TABLE DbChangeLog (Id integer, InstanceIdBytes BLOB, PeptideAnalysisId INTEGER, "
+"PeptideId INTEGER, MsDataFileId INTEGER, WorkspaceId INTEGER, primary key (Id))")
.ExecuteNonQuery();
CreateCommand(connection,
"CREATE TABLE DbLock (Id integer, Version INTEGER not null, InstanceIdBytes BLOB,"
+ "LockType INTEGER, WorkspaceId INTEGER, PeptideAnalysisId INTEGER, MsDataFileId INTEGER,"
+ " primary key (Id))")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbWorkspace ADD COLUMN DataFilePath TEXT").ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbChromatogram ADD COLUMN UncompressedSize INTEGER")
.ExecuteNonQuery();
}
if (dbVersion < 4)
{
broker.UpdateStatusMessage("Upgrading from version 3 to 4");
CreateCommand(connection, "ALTER TABLE DbPeptideAnalysis ADD COLUMN MassAccuracy DOUBLE")
.ExecuteNonQuery();
}
if (dbVersion < 5)
{
broker.UpdateStatusMessage("Upgrading from version 4 to 5");
CreateCommand(connection, "ALTER TABLE DbPeptideDistribution ADD COLUMN PrecursorEnrichment DOUBLE")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbPeptideDistribution ADD COLUMN Turnover DOUBLE")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbPeptideDistribution ADD COLUMN PrecursorEnrichmentFormula TEXT")
.ExecuteNonQuery();
}
if (dbVersion < 6)
{
broker.UpdateStatusMessage("Upgrading from version 5 to 6");
if (IsSqlite)
{
CreateCommand(connection, "DROP TABLE DbPeak")
.ExecuteNonQuery();
CreateCommand(connection, "CREATE TABLE DbPeak (Id integer, Version INTEGER not null, "
+ "\nPeptideFileAnalysis INTEGER not null, Name TEXT not null, StartTime NUMERIC, EndTime NUMERIC, TotalArea NUMERIC,"
+ "\nBackground NUMERIC, RatioToBase NUMERIC, RatioToBaseError NUMERIC,"
+ "\nCorrelation NUMERIC, Intercept NUMERIC, TracerPercent NUMERIC, RelativeAmount NUMERIC,"
+ "\nprimary key (Id),unique (PeptideFileAnalysis, Name))")
.ExecuteNonQuery();
}
else
{
CreateCommand(connection, "DELETE FROM DbPeak")
.ExecuteNonQuery();
try
{
CreateCommand(connection, "DROP INDEX PeptideFileAnalysis ON DbPeak")
.ExecuteNonQuery();
}
// ReSharper disable EmptyGeneralCatchClause
catch
// ReSharper restore EmptyGeneralCatchClause
{
// ignore
}
CreateCommand(connection, "ALTER TABLE DbPeak ADD COLUMN Name VARCHAR(255)")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbPeak ADD COLUMN StartTime DOUBLE")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbPeak ADD COLUMN EndTime DOUBLE")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbPeak ADD COLUMN RatioToBase DOUBLE")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbPeak ADD COLUMN RatioToBaseError DOUBLE")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbPeak ADD COLUMN Correlation DOUBLE")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbPeak ADD COLUMN Intercept DOUBLE")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbPeak ADD COLUMN TracerPercent DOUBLE")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbPeak ADD COLUMN RelativeAmount DOUBLE")
.ExecuteNonQuery();
CreateCommand(connection,
"CREATE UNIQUE INDEX PeptideFileAnalysis ON DbPeak (PeptideFileAnalysis, Name)")
.ExecuteNonQuery();
}
CreateCommand(connection, "ALTER TABLE DbPeptideFileAnalysis ADD COLUMN BasePeakName TEXT")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbPeptideFileAnalysis ADD COLUMN TracerPercent DOUBLE")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbPeptideFileAnalysis ADD COLUMN DeconvolutionScore DOUBLE")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbPeptideFileAnalysis ADD COLUMN PrecursorEnrichment DOUBLE")
.ExecuteNonQuery();
CreateCommand(connection, "UPDATE DbPeptideFileAnalysis SET PeakCount = 0")
.ExecuteNonQuery();
CreateCommand(connection,
"ALTER TABLE DbPeptideFileAnalysis ADD COLUMN PrecursorEnrichmentFormula TEXT")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbPeptideFileAnalysis ADD COLUMN Turnover DOUBLE")
.ExecuteNonQuery();
}
if (dbVersion < 7)
{
broker.UpdateStatusMessage("Upgrading from version 6 to 7");
CreateCommand(connection, "ALTER TABLE DbMsDataFile ADD Column Sample TEXT")
.ExecuteNonQuery();
}
if (dbVersion < 8)
{
broker.UpdateStatusMessage("Upgrading from version 7 to 8");
CreateCommand(connection, "ALTER TABLE DbPeptideFileAnalysis ADD Column TurnoverScore DOUBLE")
.ExecuteNonQuery();
}
if (dbVersion < 9)
{
broker.UpdateStatusMessage("Upgrading from version 8 to 9");
CreateCommand(connection, "ALTER TABLE DbMsDataFile ADD COLUMN TotalIonCurrentBytes MEDIUMBLOB")
.ExecuteNonQuery();
}
if (dbVersion < 10)
{
broker.UpdateStatusMessage("Upgrading from version 9 to 10");
if (IsSqlite)
{
CreateCommand(connection,
"CREATE TABLE DbChromatogramSet (Id integer, Version INTEGER not null, TimesBytes BLOB, ScanIndexesBytes BLOB, PeptideFileAnalysis INTEGER not null, ChromatogramCount INTEGER, primary key (Id),unique (PeptideFileAnalysis))")
.ExecuteNonQuery();
CreateCommand(connection, "DROP TABLE DbChromatogram").ExecuteNonQuery();
CreateCommand(connection,
"CREATE TABLE DbChromatogram (Id integer, Version INTEGER not null, ChromatogramSet INTEGER not null, Charge INTEGER not null, MassIndex INTEGER not null, MzMin NUMERIC, MzMax NUMERIC, PointsBytes BLOB, UncompressedSize INTEGER, primary key (Id),unique (ChromatogramSet, Charge, MassIndex))")
.ExecuteNonQuery();
}
else
{
CreateCommand(connection, "CREATE TABLE DbChromatogramSet ("
+ "\nId BIGINT NOT NULL AUTO_INCREMENT"
+ "\n,Version INT NOT NULL"
+ "\n,TimesBytes MEDIUMBLOB"
+ "\n,ScanIndexesBytes MEDIUMBLOB"
+ "\n,PeptideFileAnalysis BIGINT NOT NULL"
+ "\n,ChromatogramCount INT"
+ "\n,primary key (Id)"
+ "\n,unique KEY PeptideFileAnalysis (PeptideFileAnalysis)"
+")")
.ExecuteNonQuery();
CreateCommand(connection, "DROP INDEX PeptideFileAnalysis ON DbChromatogram")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbChromatogram ADD COLUMN ChromatogramSet BIGINT")
.ExecuteNonQuery();
}
CreateCommand(connection, "ALTER TABLE DbPeptideFileAnalysis ADD COLUMN ChromatogramSet BIGINT")
.ExecuteNonQuery();
// CreateCommand(connection,
// "SELECT Id AS PeptideFileAnalysis, TimesBytes, ScanIndexesBytes, ChromatogramCount INTO DbChromatogramSet FROM DbPeptideFileAnalysis WHERE ChromatogramCount > 0")
// .ExecuteNonQuery();
if (!IsSqlite)
{
CreateCommand(connection,
"INSERT INTO DbChromatogramSet (PeptideFileAnalysis, TimesBytes, ScanIndexesBytes, ChromatogramCount)"
+ "\nSELECT Id, TimesBytes, ScanIndexesBytes, ChromatogramCount"
+ "\nFROM DbPeptideFileAnalysis"
+ "\nWHERE ChromatogramCount > 0")
.ExecuteNonQuery();
}
CreateCommand(connection,
"UPDATE DbPeptideFileAnalysis SET TimesBytes = NULL, ScanIndexesBytes = NULL, ChromatogramSet = (SELECT Id FROM DbChromatogramSet WHERE DbChromatogramSet.PeptideFileAnalysis = DbPeptideFileAnalysis.Id)")
.ExecuteNonQuery();
if (!IsSqlite)
{
CreateCommand(connection,
"UPDATE DbChromatogram C INNER JOIN DbChromatogramSet S ON C.PeptideFileAnalysis = S.PeptideFileAnalysis SET C.ChromatogramSet = S.Id")
.ExecuteNonQuery();
CreateCommand(connection,
"CREATE UNIQUE INDEX ChromatogramSetMz ON DbChromatogram (ChromatogramSet, Charge, MassIndex)")
.ExecuteNonQuery();
}
}
if (dbVersion < 11)
{
broker.UpdateStatusMessage("Upgrading from version 10 to 11");
CreateCommand(connection, "ALTER TABLE DbPeptideSearchResult ADD COLUMN PsmCount INT")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbPeptideFileAnalysis ADD COLUMN PsmCount INT")
.ExecuteNonQuery();
CreateCommand(connection,
"ALTER TABLE DbPeptideFileAnalysis ADD COLUMN IntegrationNote VARCHAR(255)")
.ExecuteNonQuery();
CreateCommand(connection,
"UPDATE DbPeptideSearchResult SET PsmCount = 1 WHERE FirstDetectedScan = LastDetectedScan")
.ExecuteNonQuery();
CreateCommand(connection,
"UPDATE DbPeptideSearchResult SET PsmCount = 2 WHERE FirstDetectedScan <> LastDetectedScan")
.ExecuteNonQuery();
if (!IsSqlite)
{
CreateCommand(connection, "UPDATE DbPeptideFileAnalysis F"
+ "\nINNER JOIN DbPeptideAnalysis A ON F.PeptideAnalysis = A.Id"
+
"\nLEFT JOIN DbPeptideSearchResult R ON R.Peptide = A.Peptide AND R.MsDataFile = F.MsDataFile"
+ "\nSET F.PsmCount = Coalesce(R.PsmCount, 0)")
.ExecuteNonQuery();
}
}
if (dbVersion < 12)
{
broker.UpdateStatusMessage("Upgrading from version 11 to 12");
CreateCommand(connection, "CREATE TABLE DbPeptideSpectrumMatch ("
+ "\nId bigint NOT NULL AUTO_INCREMENT,"
+ "\nVersion int NOT NULL,"
+ "\nMsDataFile bigint NOT NULL,"
+ "\nPeptide bigint NOT NULL,"
+ "\nPrecursorMz double ,"
+ "\nPrecursorCharge int,"
+ "\nModifiedSequence varchar(255),"
+ "\nRetentionTime double,"
+ "\nSpectrumId varchar(255),"
+ "\nPRIMARY KEY (Id),"
+ "\nKEY MsDataFile (MsDataFile),"
+ "\nKEY Peptide (Peptide)"
+ "\n)").ExecuteNonQuery();
if (!IsSqlite)
{
foreach (string table in new[] { "DbChangeLog", "DbChromatogram", "DbChromatogramSet", "DbLock", "DbModification", "DbMsDataFile", "DbPeak", "DbPeptide", "DbPeptideAnalysis", "DbPeptideFileAnalysis", "DbPeptideSearchResult", "DbPeptideSpectrumMatch", "DbSetting", "DbTracerDef", "DbWorkspace" })
{
try
{
CreateCommand(connection, string.Format("ALTER TABLE {0} ENGINE=INNODB", table)).ExecuteNonQuery();
}
catch(Exception exception)
{
Trace.TraceWarning("Exception changing storage engine on table {0}:{1}", table, exception);
}
}
}
CreateCommand(connection, "INSERT INTO DbPeptideSpectrumMatch(MsDataFile, Peptide, SpectrumId, PrecursorCharge, ModifiedSequence)"
+"\nSELECT S.MsDataFile,"
+ "\nS.Peptide,"
+ "\nS.FirstDetectedScan,"
+ "\nS.MinCharge,"
+ "\nP.Sequence"
+ "\nFROM DbPeptideSearchResult S INNER JOIN DbPeptide P ON S.Peptide = P.Id")
.ExecuteNonQuery();
CreateCommand(connection, "INSERT INTO DbPeptideSpectrumMatch(MsDataFile, Peptide, SpectrumId, PrecursorCharge, ModifiedSequence)"
+ "\nSELECT S.MsDataFile,"
+ "\nS.Peptide,"
+ "\nS.FirstDetectedScan,"
+ "\nS.MinCharge,"
+ "\nP.Sequence"
+ "\nFROM DbPeptideSearchResult S INNER JOIN DbPeptide P ON S.Peptide = P.Id"
+ "\nWHERE S.FirstDetectedScan <> S.LastDetectedScan")
.ExecuteNonQuery();
}
if (dbVersion < 13)
{
broker.UpdateStatusMessage("Upgrading from version 12 to 13");
CreateCommand(connection, "ALTER TABLE DbPeptide MODIFY COLUMN Workspace BIGINT")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbMsDataFile MODIFY COLUMN Workspace BIGINT")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbPeptideAnalysis MODIFY COLUMN Workspace BIGINT")
.ExecuteNonQuery();
}
if (dbVersion < 14)
{
broker.UpdateStatusMessage("Upgrading from version 13 to 14");
string strCreateDbPeak2 = @"CREATE TABLE DbPeak2
(Id bigint(20) NOT NULL AUTO_INCREMENT,
PeptideFileAnalysis bigint(20) NOT NULL,
PeakIndex int NOT NULL,
StartTime double DEFAULT NULL,
EndTime double DEFAULT NULL,
Area double DEFAULT NULL,
PRIMARY KEY (Id),
UNIQUE KEY PeptideFileAnalysis (PeptideFileAnalysis,PeakIndex))";
if (!IsSqlite)
{
strCreateDbPeak2 += " ENGINE=InnoDb";
}
CreateCommand(connection, strCreateDbPeak2)
.ExecuteNonQuery();
CreateCommand(connection,
@"INSERT INTO DbPeak2 (Id, PeptideFileAnalysis, PeakIndex, StartTime, EndTime, Area)
SELECT Id, PeptideFileAnalysis,
(SELECT COUNT(Id) FROM DbPeak P2 WHERE P2.PeptideFileAnalysis = P1.PeptideFileAnalysis AND P2.Name < P1.Name),
StartTime,EndTime,TotalArea
FROM DbPeak P1")
.ExecuteNonQuery();
CreateCommand(connection, "DROP TABLE DbPeak")
.ExecuteNonQuery();
CreateCommand(connection, "ALTER TABLE DbPeak2 RENAME TO DbPeak")
.ExecuteNonQuery();
}
if (dbVersion < 15)
{
broker.UpdateStatusMessage("Upgrading from version 14 to 15");
CreateCommand(connection, "ALTER TABLE DbMsDataFile ADD COLUMN PrecursorPool VARCHAR(255)")
.ExecuteNonQuery();
}
if (dbVersion < CurrentVersion)
{
broker.UpdateStatusMessage("Upgrading");
CreateCommand(connection, "UPDATE DbWorkspace SET SchemaVersion = " + CurrentVersion)
.ExecuteNonQuery();
}
broker.UpdateStatusMessage("Committing transaction");
broker.SetIsCancelleable(false);
transaction.Commit();
}
}
private IEnumerable<TableData> RequeryGrid(LongOperationBroker longOperationBroker, ISession session)
{
var connection = session.Connection;
var cmd = connection.CreateCommand();
cmd.CommandText = "SHOW TABLE STATUS";
var reader = cmd.ExecuteReader();
Debug.Assert(null != reader);
var result = new List<TableData>();
int iColName = reader.GetOrdinal("Name");
int iColRows = reader.GetOrdinal("Rows");
int iColDataFileSize = reader.GetOrdinal("Data_length");
int iColFreeSpace = reader.GetOrdinal("Data_free");
int iColIndexSize = reader.GetOrdinal("Index_length");
int iColEngine = reader.GetOrdinal("Engine");
while (reader.Read())
{
result.Add(new TableData
{
Name = reader.GetString(iColName),
RowCount = reader.GetInt64(iColRows),
DataFileSize = reader.GetInt64(iColDataFileSize),
FreeSpace = reader.GetInt64(iColFreeSpace),
IndexSize = reader.GetInt64(iColIndexSize),
Engine = reader.GetString(iColEngine),
});
}
return result;
}
// ReSharper disable AccessToDisposedClosure
private void UpdateWorkspaceVersion(WorkspaceChangeArgs v)
{
using (var session = Workspace.OpenSession())
{
using (var longWaitDialog = new LongWaitDialog(TopLevelControl, "Updating Workspace"))
{
var broker = new LongOperationBroker(b =>
{
session.BeginTransaction();
Workspace.UpdateWorkspaceVersion(b, session, v);
session.Transaction.Commit();
}, longWaitDialog, session);
broker.LaunchJob();
}
}
}
public void Run(LongOperationBroker longOperationBroker)
{
longOperationBroker.CancellationToken.Register(Cancel);
_job.Invoke(longOperationBroker);
}