public SqliteStorage(TableInfo table)
{
_file = Path.GetTempFileName();
_table = table;
_conn = new SQLiteConnection("Synchronous=Full;Data Source=" + _file);
_conn.Open();
string sql = String.Format("create table {0} ({1})", TABLE_NAME, ColumnsText);
_conn.ExecuteNonQuery(sql);
lock (_storageDirectory)
{
_storageDirectory[_file] = this;
}
}
void downgrade_1_to_0(SQLiteConnection connection)
{
connection.ExecuteNonQuery(@"CREATE TABLE NewPeptideSpectrumMatch (Id INTEGER PRIMARY KEY, Spectrum INT, Analysis INT, Peptide INT, QValue NUMERIC, MonoisotopicMass NUMERIC, MolecularWeight NUMERIC, MonoisotopicMassError NUMERIC, MolecularWeightError NUMERIC, Rank INT, Charge INT);
INSERT INTO NewPeptideSpectrumMatch SELECT Id, Spectrum, Analysis, Peptide, QValue, ObservedNeutralMass, ObservedNeutralMass-MolecularWeightError, MonoisotopicMassError, MolecularWeightError, Rank, Charge FROM PeptideSpectrumMatch;
DROP TABLE PeptideSpectrumMatch;
ALTER TABLE NewPeptideSpectrumMatch RENAME TO PeptideSpectrumMatch;
DROP TABLE About;
");
}
void downgrade_2_to_1(SQLiteConnection connection)
{
// add an empty ScanTimeInSeconds column
connection.ExecuteNonQuery(@"CREATE TABLE NewSpectrum (Id INTEGER PRIMARY KEY, Source INT, Index_ INT, NativeID TEXT, PrecursorMZ NUMERIC, ScanTimeInSeconds NUMERIC);
INSERT INTO NewSpectrum SELECT Id, Source, Index_, NativeID, PrecursorMZ, 0 FROM Spectrum;
DROP TABLE Spectrum;
ALTER TABLE NewSpectrum RENAME TO Spectrum;
UPDATE About SET SchemaRevision = 1;
");
}
void downgrade_3_to_2(SQLiteConnection connection)
{
// delete quantitation tables and quantitative columns from SpectrumSource
connection.ExecuteNonQuery(@"DROP TABLE SpectrumQuantitation;
DROP TABLE DistinctMatchQuantitation;
DROP TABLE PeptideQuantitation;
DROP TABLE ProteinQuantitation;
CREATE TABLE TempSpectrumSource (Id INTEGER PRIMARY KEY, Name TEXT, URL TEXT, Group_ INT, MsDataBytes BLOB);
INSERT INTO TempSpectrumSource SELECT Id, Name, URL, Group_, MsDataBytes FROM SpectrumSource;
DROP TABLE SpectrumSource;
ALTER TABLE TempSpectrumSource RENAME TO SpectrumSource;
UPDATE About SET SchemaRevision = 2;
");
}
void downgrade_4_to_3(SQLiteConnection connection)
{
// move MsDataBytes to SpectrumSource table and return to bugged INT key for DistinctMatchQuantitation
connection.ExecuteNonQuery(@"CREATE TABLE TempSpectrumSource (Id INTEGER PRIMARY KEY, Name TEXT, URL TEXT, Group_ INT, MsDataBytes BLOB, TotalSpectraMS1 INT, TotalIonCurrentMS1 NUMERIC, TotalSpectraMS2 INT, TotalIonCurrentMS2 NUMERIC, QuantitationMethod INT);
INSERT INTO TempSpectrumSource SELECT ss.Id, Name, URL, Group_, MsDataBytes, TotalSpectraMS1, TotalIonCurrentMS1, TotalSpectraMS2, TotalIonCurrentMS2, QuantitationMethod FROM SpectrumSource ss JOIN SpectrumSourceMetadata ssmd ON ss.Id=ssmd.Id;
DROP TABLE SpectrumSource;
ALTER TABLE TempSpectrumSource RENAME TO SpectrumSource;
DROP TABLE SpectrumSourceMetadata;
DROP TABLE DistinctMatchQuantitation;
CREATE TABLE DistinctMatchQuantitation (Id INTEGER PRIMARY KEY, iTRAQ_ReporterIonIntensities BLOB, TMT_ReporterIonIntensities BLOB, PrecursorIonIntensity NUMERIC);
UPDATE About SET SchemaRevision = 3;
");
}
void downgrade_7_to_4(SQLiteConnection connection)
{
// just rename table; the extra columns will be ignored
connection.ExecuteNonQuery("ALTER TABLE FilterHistory RENAME TO FilteringCriteria");
}
void downgrade_12_to_11(SQLiteConnection connection)
{
connection.ExecuteNonQuery(@"DROP TABLE XICMetrics;
CREATE TABLE XICMetrics (PsmId INTEGER PRIMARY KEY, PeakIntensity NUMERIC, PeakArea NUMERIC, PeakSNR NUMERIC, PeakTimeInSeconds NUMERIC);
INSERT INTO XICMetrics VALUES (1,0,0,0,0)");
}
public static bool IsValidFile (string path)
{
try
{
string uncCompatiblePath = Util.GetSQLiteUncCompatiblePath(path);
using (var conn = new SQLiteConnection(String.Format("Data Source={0};Version=3", uncCompatiblePath)))
{
conn.Open();
// in a valid file, this will throw "already exists"
conn.ExecuteNonQuery("CREATE TABLE IntegerSet (Value INTEGER PRIMARY KEY)");
}
}
catch (SQLiteException e)
{
if (e.Message.Contains("already exists"))
return true;
}
// creating the table or any other exception indicates an invalid file
return false;
}
public static System.Data.IDbConnection CreateFile (string path)
{
lock (mutex)
if (newSession == null)
{
Configuration configuration = new Configuration()
.SetProperty("dialect", typeof(CustomSQLiteDialect).AssemblyQualifiedName)
.SetProperty("connection.connection_string", "Data Source=:memory:;Version=3;")
.SetProperty("connection.driver_class", typeof(NHibernate.Driver.SQLite20Driver).AssemblyQualifiedName)
.SetProperty("connection.provider", typeof(NHibernate.Connection.DriverConnectionProvider).AssemblyQualifiedName)
.SetProperty("connection.release_mode", "on_close")
;
ConfigureMappings(configuration);
var sessionFactory = configuration.BuildSessionFactory();
newSession = sessionFactory.OpenStatelessSession();
createSql = configuration.GenerateSchemaCreationScript(Dialect.GetDialect(configuration.Properties));
}
string uncCompatiblePath = Util.GetSQLiteUncCompatiblePath(path);
bool pooling = false;// path == ":memory:";
var conn = new SQLiteConnection(String.Format("Data Source={0};Version=3;{1}", uncCompatiblePath, (pooling ? "Pooling=True;Max Pool Size=1;" : "")));
conn.Open();
var journal_mode = conn.ExecuteQuery("PRAGMA journal_mode").Single()[0];
var synchronous = conn.ExecuteQuery("PRAGMA synchronous").Single()[0];
conn.ExecuteNonQuery(@"PRAGMA journal_mode=OFF;
PRAGMA synchronous=OFF;
PRAGMA automatic_indexing=OFF;
PRAGMA cache_size=30000;
PRAGMA temp_store=MEMORY;
PRAGMA page_size=32768;
PRAGMA mmap_size=70368744177664; -- 2^46");
var transaction = conn.BeginTransaction();
var cmd = conn.CreateCommand();
foreach (string sql in createSql)
cmd.ExecuteNonQuery(sql);
cmd.ExecuteNonQuery(String.Format("INSERT INTO About VALUES (1, 'IDPicker', '{0}', datetime('now'), {1})",
Util.Version, SchemaUpdater.CurrentSchemaRevision));
cmd.ExecuteNonQuery(@"CREATE TABLE PeptideSpectrumMatchScoreName (Id INTEGER PRIMARY KEY, Name TEXT UNIQUE NOT NULL);
CREATE TABLE DistinctMatchQuantitation (Id TEXT PRIMARY KEY, iTRAQ_ReporterIonIntensities BLOB, TMT_ReporterIonIntensities BLOB, PrecursorIonIntensity NUMERIC);
CREATE TABLE IntegerSet (Value INTEGER PRIMARY KEY);");
CreateIndexes(conn);
transaction.Commit();
conn.ExecuteNonQuery("PRAGMA journal_mode=" + journal_mode + ";" +
"PRAGMA synchronous=" + synchronous);
return conn;
}
public static ISessionFactory CreateSessionFactory (string path, SessionFactoryConfig config)
{
string uncCompatiblePath = Util.GetSQLiteUncCompatiblePath(path);
// update the existing database's schema if necessary, and if updated, recreate the indexes
if (path != ":memory:" &&
File.Exists(path) &&
IsValidFile(path) &&
SchemaUpdater.Update(path, null))
{
using (var conn = new SQLiteConnection(String.Format("Data Source={0};Version=3", uncCompatiblePath)))
{
conn.Open();
conn.ExecuteNonQuery(@"PRAGMA journal_mode=DELETE;
PRAGMA synchronous=OFF;
PRAGMA automatic_indexing=OFF;
PRAGMA cache_size=30000;
PRAGMA temp_store=MEMORY;
PRAGMA page_size=32768;
PRAGMA mmap_size=70368744177664; -- 2^46");
DropIndexes(conn);
CreateIndexes(conn);
}
}
bool pooling = path == ":memory:";
var configuration = new Configuration()
.SetProperty("show_sql", config.WriteSqlToConsoleOut ? "true" : "false")
.SetProperty("dialect", typeof(CustomSQLiteDialect).AssemblyQualifiedName)
.SetProperty("hibernate.cache.use_query_cache", "true")
//.SetProperty("adonet.batch_size", batchSize.ToString())
.SetProperty("connection.connection_string", String.Format("Data Source={0};Version=3;{1}", uncCompatiblePath, (pooling ? "Pooling=True;Max Pool Size=1;" : "")))
.SetProperty("connection.driver_class", typeof(NHibernate.Driver.SQLite20Driver).AssemblyQualifiedName)
.SetProperty("connection.provider", typeof(NHibernate.Connection.DriverConnectionProvider).AssemblyQualifiedName)
.SetProperty("connection.release_mode", "on_close")
;
ConfigureMappings(configuration);
if (config.UseUnfilteredTables)
{
configuration.ClassMappings.Single(o => o.Table.Name == "Protein").Table.Name = "UnfilteredProtein";
configuration.ClassMappings.Single(o => o.Table.Name == "Peptide").Table.Name = "UnfilteredPeptide";
configuration.ClassMappings.Single(o => o.Table.Name == "PeptideInstance").Table.Name = "UnfilteredPeptideInstance";
configuration.ClassMappings.Single(o => o.Table.Name == "PeptideSpectrumMatch").Table.Name = "UnfilteredPeptideSpectrumMatch";
configuration.ClassMappings.Single(o => o.Table.Name == "Spectrum").Table.Name = "UnfilteredSpectrum";
}
ISessionFactory sessionFactory = null;
lock(mutex)
sessionFactory = configuration.BuildSessionFactory();
sessionFactory.OpenStatelessSession().CreateSQLQuery(@"PRAGMA cache_size=10000;
PRAGMA temp_store=MEMORY;
PRAGMA page_size=32768;
PRAGMA mmap_size=70368744177664; -- 2^46").ExecuteUpdate();
if (config.CreateSchema)
CreateFile(path);
return sessionFactory;
}
private SQLiteConnection GetConnection()
{
SQLiteConnection conn = new SQLiteConnection(ConnectionString);
conn.Open();
conn.ExecuteNonQuery("PRAGMA synchronous = OFF;");
conn.ExecuteNonQuery("PRAGMA count_changes = OFF");
return conn;
}
/// <summary>
/// Execute NonQuery
/// </summary>
/// <param name="conn"></param>
/// <param name="cmdText"></param>
/// <param name="parameters"></param>
public static void ExecuteNonQuery(this SQLiteConnection conn, string cmdText, SQLiteParameter[] parameters)
{
conn.CheckNull(nameof(conn));
conn.ExecuteNonQuery(cmdText, parameters, CommandType.Text, null);
}
/// <summary>
/// Execute NonQuery
/// </summary>
/// <param name="conn"></param>
/// <param name="cmdText"></param>
/// <param name="commandType"></param>
/// <param name="transaction"></param>
public static void ExecuteNonQuery(this SQLiteConnection conn, string cmdText, CommandType commandType, SQLiteTransaction transaction)
{
conn.CheckNull(nameof(conn));
conn.ExecuteNonQuery(cmdText, null, commandType, transaction);
}