public void UpdateWorkspaceVersion(LongOperationBroker longOperationBroker, ISession session, WorkspaceChangeArgs savedWorkspaceChange)
{
if (savedWorkspaceChange.HasChromatogramMassChange)
{
longOperationBroker.UpdateStatusMessage("Deleting chromatograms");
session.CreateSQLQuery("DELETE FROM DbChromatogram")
.ExecuteUpdate();
session.CreateSQLQuery("UPDATE DbPeptideFileAnalysis SET ChromatogramSet = NULL, PeakCount = 0")
.ExecuteUpdate();
session.CreateSQLQuery("DELETE FROM DbChromatogramSet").ExecuteUpdate();
session.CreateSQLQuery("DELETE FROM DbLock").ExecuteUpdate();
}
if (savedWorkspaceChange.HasTurnoverChange)
{
session.CreateSQLQuery("UPDATE DbPeptideFileAnalysis SET TracerPercent = NULL")
.ExecuteUpdate();
if (savedWorkspaceChange.HasPeakPickingChange)
{
if (!savedWorkspaceChange.HasChromatogramMassChange)
{
session.CreateSQLQuery("UPDATE DbPeptideFileAnalysis SET PeakCount = 0")
.ExecuteUpdate();
}
longOperationBroker.UpdateStatusMessage("Deleting peaks");
session.CreateSQLQuery("DELETE FROM DbPeak").ExecuteUpdate();
}
if (!savedWorkspaceChange.HasChromatogramMassChange)
{
session.CreateSQLQuery("DELETE FROM DbLock WHERE LockType = " + (int)LockType.results).ExecuteUpdate();
}
}
}
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 static void DeletePeptideAnalyses(LongOperationBroker broker, ISession session, IList <PeptideAnalysis> peptideAnalyses)
{
var changeLogs = peptideAnalyses.Select(peptideAnalysis => new DbChangeLog(peptideAnalysis)).ToArray();
var analysisIds = peptideAnalyses.Select(peptideAnalysis => peptideAnalysis.Id).ToArray();
var strAnalysisIds = string.Join(",", analysisIds.Select(id => id.ToString(CultureInfo.InvariantCulture)).ToArray());
session.BeginTransaction();
broker.UpdateStatusMessage("Deleting chromatograms");
session.CreateSQLQuery(
string.Format(
"UPDATE DbPeptideFileAnalysis SET ChromatogramSet = NULL WHERE PeptideAnalysis IN ({0})",
strAnalysisIds))
.ExecuteUpdate();
session.CreateSQLQuery(
string.Format("DELETE C FROM DbChromatogram C"
+ "\nJOIN DbChromatogramSet S ON C.ChromatogramSet + S.Id"
+ "\nJOIN DbPeptideFileAnalysis F ON S.PeptideFileAnalysis = F.Id"
+ "\nWHERE F.PeptideAnalysis IN ({0})",
strAnalysisIds))
.ExecuteUpdate();
session.CreateSQLQuery(
string.Format("DELETE S FROM DbChromatogramSet S"
+ "\nJOIN DbPeptideFileAnalysis F ON S.PeptideFileAnalysis = F.Id"
+ "\nWHERE F.PeptideAnalysis IN ({0})",
strAnalysisIds))
.ExecuteUpdate();
broker.UpdateStatusMessage("Deleting results");
session.CreateSQLQuery(
string.Format("DELETE P FROM DbPeak P"
+ "\nJOIN DbPeptideFileAnalysis F ON P.PeptideFileAnalysis = F.Id"
+ "\nWHERE F.PeptideAnalysis IN ({0})",
strAnalysisIds))
.ExecuteUpdate();
broker.UpdateStatusMessage("Deleting analyses");
session.CreateSQLQuery(
string.Format("DELETE FROM DbPeptideFileAnalysis WHERE PeptideAnalysis IN ({0})",
strAnalysisIds))
.ExecuteUpdate();
session.CreateSQLQuery(
string.Format("DELETE FROM DbPeptideAnalysis WHERE Id IN ({0})",
strAnalysisIds))
.ExecuteUpdate();
foreach (var changeLog in changeLogs)
{
session.Save(changeLog);
}
session.Transaction.Commit();
}
public void Save(LongOperationBroker longOperationBroker)
{
longOperationBroker.UpdateStatusMessage("Synchronizing with database changes");
int reconcileAttempt = 1;
while (true)
{
var dirtyPeptideAnalyses = PeptideAnalyses.ListDirty()
.ToDictionary(pa => pa.Id, pa => pa.ChromatogramsWereLoaded);
if (DatabasePoller.TryLoadAndMergeChanges(dirtyPeptideAnalyses))
{
break;
}
reconcileAttempt++;
longOperationBroker.UpdateStatusMessage("Synchronizing with database changes attempt #" + reconcileAttempt);
}
using (var session = OpenWriteSession())
{
session.BeginTransaction();
UpdateWorkspaceVersion(longOperationBroker, session, SavedWorkspaceChange);
bool workspaceChanged = false;
var dbWorkspace = LoadDbWorkspace(session);
longOperationBroker.UpdateStatusMessage("Saving tracer definitions");
workspaceChanged = TracerDefs.Save(session, dbWorkspace) || workspaceChanged;
longOperationBroker.UpdateStatusMessage("Saving modifications");
workspaceChanged = Modifications.Save(session, dbWorkspace) || workspaceChanged;
longOperationBroker.UpdateStatusMessage("Saving settings");
workspaceChanged = Settings.Save(session, dbWorkspace) || workspaceChanged;
longOperationBroker.UpdateStatusMessage("Saving data files");
foreach (var msDataFile in MsDataFiles.ListDirty())
{
msDataFile.Save(session);
}
longOperationBroker.UpdateStatusMessage("Saving peptides");
foreach (var peptide in Peptides.ListDirty())
{
peptide.Save(session);
}
longOperationBroker.UpdateStatusMessage("Saving peptide analyses");
foreach (var peptideAnalysis in PeptideAnalyses.ListDirty())
{
if (longOperationBroker.WasCancelled)
{
return;
}
peptideAnalysis.Save(session);
}
if (workspaceChanged)
{
session.Save(new DbChangeLog(this));
}
longOperationBroker.SetIsCancelleable(false);
session.Transaction.Commit();
}
}
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);
}
}
}
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;
}
private void ExportTable <T>()
{
_longOperationBroker.UpdateStatusMessage("Exporting " + typeof(T).Name);
var table = typeof(T);
var classMapping = _targetConfiguration.GetClassMapping(table);
var columnNames = new List <String>();
foreach (Column column in classMapping.IdentifierProperty.ColumnIterator)
{
columnNames.Add(column.Name);
}
foreach (Property property in classMapping.PropertyIterator)
{
foreach (Column column in property.ColumnIterator)
{
columnNames.Add(column.Name);
}
}
var sqlSelect = "SELECT " + Lists.Join(columnNames, ",") + " FROM " + classMapping.Table.Name;
using (var cmd = _sourceConnection.CreateCommand())
{
cmd.CommandText = sqlSelect;
using (var reader = cmd.ExecuteReader())
{
bool lockTable = DatabaseTypeEnum == DatabaseTypeEnum.mysql;
if (lockTable)
{
_writer.WriteLine("LOCK TABLES " + classMapping.Table.Name + " WRITE;");
}
else
{
_writer.WriteLine("BEGIN TRANSACTION;");
}
int recordCount = 0;
while (reader.Read())
{
_longOperationBroker.UpdateStatusMessage("Exporting " + typeof(T).Name + " #" + (++recordCount));
_writer.Write("INSERT INTO " + classMapping.Table.Name + " VALUES(");
for (int i = 0; i < columnNames.Count; i++)
{
var value = reader.GetValue(i);
if (i != 0)
{
_writer.Write(",");
}
WriteValue(_writer, value);
}
_writer.WriteLine(");");
}
if (lockTable)
{
_writer.WriteLine("UNLOCK TABLES;");
}
else
{
_writer.WriteLine("COMMIT TRANSACTION;");
}
}
}
}
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();
}
}