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); }