public SQLiteTransaction(SQLiteDatabase database, IsolationLevel level, SQLiteSettings settings) { _database = database; _settings = settings; _connection = _database.ConnectionPool.GetConnection(); _transaction = _connection.BeginTransaction(level); }
public VersionUpgrade() { _serializer = new XmlSerializer(typeof(VersionInfo)); _settings = ServiceRegistration.Get <ISettingsManager>().Load <SQLiteSettings>(); var pathManager = ServiceRegistration.Get <IPathManager>(); _dataDirectory = pathManager.GetPath("<DATABASE>"); _databaseFile = Path.Combine(_dataDirectory, _settings.DatabaseFileName); _versionInfoFile = Path.ChangeExtension(_databaseFile, ".version.xml"); }
public SQLiteTransaction(SQLiteDatabase database, IsolationLevel level, SQLiteSettings settings) { _database = database; _settings = settings; #if NO_POOL _connection = _database.CreateOpenAndInitializeConnection(); #else _connection = _database.ConnectionPool.GetConnection(); #endif _transaction = _connection.BeginTransaction(level); }
public SQLiteDatabase() { try { _maintenanceScheduler = new ActionBlock<bool>(async _ => await PerformDatabaseMaintenanceAsync(), new ExecutionDataflowBlockOptions { BoundedCapacity = 2 }); _messageQueue = new AsynchronousMessageQueue(this, new[] { ContentDirectoryMessaging.CHANNEL }); _messageQueue.MessageReceived += OnMessageReceived; _messageQueue.Start(); _settings = ServiceRegistration.Get<ISettingsManager>().Load<SQLiteSettings>(); _settings.LogSettings(); LogVersionInformation(); if (_settings.EnableTraceLogging) { _sqliteDebugLogger = FileLogger.CreateFileLogger(ServiceRegistration.Get<IPathManager>().GetPath(@"<LOG>\SQLiteDebug.log"), LogLevel.Debug, false, true); SQLiteLog.Initialize(); SQLiteLog.RemoveDefaultHandler(); SQLiteLog.Log += MPSQLiteLogEventHandler; } // We use our own collation sequence which is registered here to be able // to sort items taking into account culture specifics SQLiteFunction.RegisterFunction(typeof(SQLiteCultureSensitiveCollation)); var pathManager = ServiceRegistration.Get<IPathManager>(); string dataDirectory = pathManager.GetPath("<DATABASE>"); string databaseFile = Path.Combine(dataDirectory, _settings.DatabaseFileName); // We use an URI instead of a simple database path and filename. The reason is that // only this way we can switch on the shared cache mode of SQLite in System.Data.SQLite // However, when using an URI, SQLite ignores the page size value specified in the connection string. // Therefore we have to use a workaround below to create a database file with the specified page size. string databaseFileForUri = databaseFile.Replace('\\', '/'); string databaseUri = System.Web.HttpUtility.UrlPathEncode("file:///" + databaseFileForUri + "?cache=shared"); _connectionPool = new ConnectionPool<SQLiteConnection>(CreateOpenAndInitializeConnection); // We are using the ConnectionStringBuilder to generate the connection string // This ensures code compatibility in case of changes to the SQLite connection string parameters // More information on the parameters can be found here: http://www.sqlite.org/pragma.html var connBuilder = new SQLiteConnectionStringBuilder { // Name of the database file including path as URI FullUri = databaseUri, // Use SQLite database version 3.x Version = 3, // Store GUIDs as binaries, not as string // Saves some space in the database and is said to make search queries on GUIDs faster BinaryGUID = true, DefaultTimeout = _settings.LockTimeout, CacheSize = _settings.CacheSizeInPages, // Use the Write Ahead Log mode // In this journal mode write locks do not block reads // Needed to prevent sluggish behaviour of MP2 client when trying to read from the database (through MP2 server) // while MP2 server writes to the database (such as when importing shares) // More information can be found here: http://www.sqlite.org/wal.html JournalMode = SQLiteJournalModeEnum.Wal, // Do not use the inbuilt connection pooling of System.Data.SQLite // We use our own connection pool which is faster. Pooling = false, // Sychronization Mode "Normal" enables parallel database access while at the same time preventing database // corruption and is therefore a good compromise between "Off" (more performance) and "On" // More information can be found here: http://www.sqlite.org/pragma.html#pragma_synchronous SyncMode = SynchronizationModes.Normal, // MP2's database backend uses foreign key constraints to ensure referential integrity. // SQLite supports this, but it has to be enabled for each database connection by a PRAGMA command // For details see http://www.sqlite.org/foreignkeys.html ForeignKeys = true }; if (_settings.EnableTraceLogging) connBuilder.Flags = SQLiteConnectionFlags.LogAll; _connectionString = connBuilder.ToString(); ServiceRegistration.Get<ILogger>().Info("SQLiteDatabase: Connection String used: '{0}'", _connectionString); if (!File.Exists(databaseFile)) { ServiceRegistration.Get<ILogger>().Info("SQLiteDatabase: Database file does not exists. Creating database file"); if (!Directory.Exists(dataDirectory)) Directory.CreateDirectory(dataDirectory); // Since we use an URI in the standard connection string and system.data.sqlite therefore // ignores the page size value in the connection string, this is a workaroung to make sure // the page size value as specified in the settings is used. When the database file does // not yet exists, we create a special connection string where we additionally set the // datasource (which overrides the URI) and the page size. We then create a connection with // that special connection string, open it and close it. That way the database file is created // with the desired page size. The page size is permanently stored in the database file so that // it is used when as of now we use connections with URI. connBuilder.DataSource = databaseFile; connBuilder.PageSize = _settings.PageSize; using (var connection = new SQLiteConnection(connBuilder.ToString())) { connection.Open(); connection.Close(); } } // The following is necessary to avoid the creation of of a shared memory index file // ("-shm"-file) when using exclusive locking mode. When WAL-mode is used, it is possible // to switch between normal and exclusive locking mode at any time. However, the creation // of a "-shm"-file can only be avoided, when exclusive locking mode is set BEFORE entering // WAL-mode. If this is the case, it is not possible to leave exclusive locking mode // without leaving WAL-mode before, because the "-shm"-file was not created. // The regular connections in our connection pool use WAL-mode. Therefore we have // to open one connection without WAL-Mode (here with JournalMode=OFF) and set locking_mode= // EXCLUSIVE before we create the first regular connection that goes into the pool. // To use exclusive locking mode, it is additionally necessary to set locking_mode=EXCLUSIVE // for every connection in the pool via the InitializationCommand. If "PRAGMA locking_mode= // EXCLUSIVE" is not in the InitializationCommand, normal locking mode is used // although we issue "PRAGMA locking_mode=EXCLUSIVE" at this point. // For details see here: http://sqlite.org/wal.html#noshm // Avoiding the creation of an "-shm"-file materially improves the database performance. if (_settings.UseExclusiveMode) { connBuilder.JournalMode = SQLiteJournalModeEnum.Off; using (var connection = new SQLiteConnection(connBuilder.ToString())) { connection.Open(); using (var command = new SQLiteCommand(SQLiteSettings.EXCLUSIVE_MODE_COMMAND, connection)) command.ExecuteNonQuery(); connection.Close(); } } // Just test one "regular" connection, which is the first connection in the pool using (var transaction = BeginTransaction()) transaction.Rollback(); } catch (Exception e) { ServiceRegistration.Get<ILogger>().Critical("SQLiteDatabase: Error establishing database connection", e); throw; } }
/// <summary> /// Creates a new SQLiteDatabase instance. /// </summary> public SQLiteDatabase() { try { _importingShareIds = new HashSet <Guid>(); _maintenanceScheduler = new ActionBlock <bool>(async _ => await PerformDatabaseMaintenanceAsync(), new ExecutionDataflowBlockOptions { BoundedCapacity = 2 }); _messageQueue = new AsynchronousMessageQueue(this, new[] { ContentDirectoryMessaging.CHANNEL }); _messageQueue.MessageReceived += OnMessageReceived; _messageQueue.Start(); _settings = ServiceRegistration.Get <ISettingsManager>().Load <SQLiteSettings>(); _settings.LogSettings(); LogVersionInformation(); if (_settings.EnableTraceLogging) { _sqliteDebugLogger = FileLogger.CreateFileLogger(ServiceRegistration.Get <IPathManager>().GetPath(@"<LOG>\SQLiteDebug.log"), LogLevel.Debug, false, true); SQLiteLog.Initialize(); SQLiteLog.RemoveDefaultHandler(); SQLiteLog.Log += MPSQLiteLogEventHandler; } var pathManager = ServiceRegistration.Get <IPathManager>(); string dataDirectory = pathManager.GetPath("<DATABASE>"); string databaseFile = Path.Combine(dataDirectory, _settings.DatabaseFileName); // We use an URI instead of a simple database path and filename. The reason is that // only this way we can switch on the shared cache mode of SQLite in System.Data.SQLite // However, when using an URI, SQLite ignores the page size value specified in the connection string. // Therefore we have to use a workaround below to create a database file with the specified page size. string databaseFileForUri = databaseFile.Replace('\\', '/'); string databaseUri = System.Web.HttpUtility.UrlPathEncode("file:///" + databaseFileForUri + "?cache=shared"); #if !NO_POOL _connectionPool = new ConnectionPool <SQLiteConnection>(CreateOpenAndInitializeConnection); #endif // We are using the ConnectionStringBuilder to generate the connection string // This ensures code compatibility in case of changes to the SQLite connection string parameters // More information on the parameters can be found here: http://www.sqlite.org/pragma.html var connBuilder = new SQLiteConnectionStringBuilder { // Name of the database file including path as URI FullUri = databaseUri, // Use SQLite database version 3.x Version = 3, // Store GUIDs as binaries, not as string // Saves some space in the database and is said to make search queries on GUIDs faster BinaryGUID = true, DefaultTimeout = _settings.LockTimeout, CacheSize = _settings.CacheSizeInPages, // Use the Write Ahead Log mode // In this journal mode write locks do not block reads // Needed to prevent sluggish behaviour of MP2 client when trying to read from the database (through MP2 server) // while MP2 server writes to the database (such as when importing shares) // More information can be found here: http://www.sqlite.org/wal.html JournalMode = SQLiteJournalModeEnum.Wal, // Do not use the inbuilt connection pooling of System.Data.SQLite // We use our own connection pool which is faster. #if NO_POOL Pooling = true, #else Pooling = false, #endif // Sychronization Mode "Normal" enables parallel database access while at the same time preventing database // corruption and is therefore a good compromise between "Off" (more performance) and "On" // More information can be found here: http://www.sqlite.org/pragma.html#pragma_synchronous SyncMode = SynchronizationModes.Normal, // MP2's database backend uses foreign key constraints to ensure referential integrity. // SQLite supports this, but it has to be enabled for each database connection by a PRAGMA command // For details see http://www.sqlite.org/foreignkeys.html ForeignKeys = true }; if (_settings.EnableTraceLogging) { connBuilder.Flags = SQLiteConnectionFlags.LogAll; } _connectionString = connBuilder.ToString(); ServiceRegistration.Get <ILogger>().Info("SQLiteDatabase: Connection String used: '{0}'", _connectionString); if (!File.Exists(databaseFile)) { ServiceRegistration.Get <ILogger>().Info("SQLiteDatabase: Database file does not exists. Creating database file"); if (!Directory.Exists(dataDirectory)) { Directory.CreateDirectory(dataDirectory); } // Since we use an URI in the standard connection string and system.data.sqlite therefore // ignores the page size value in the connection string, this is a workaroung to make sure // the page size value as specified in the settings is used. When the database file does // not yet exists, we create a special connection string where we additionally set the // datasource (which overrides the URI) and the page size. We then create a connection with // that special connection string, open it and close it. That way the database file is created // with the desired page size. The page size is permanently stored in the database file so that // it is used when as of now we use connections with URI. connBuilder.DataSource = databaseFile; connBuilder.PageSize = _settings.PageSize; using (var connection = new SQLiteConnection(connBuilder.ToString())) { connection.Open(); connection.Close(); } } // The following is necessary to avoid the creation of of a shared memory index file // ("-shm"-file) when using exclusive locking mode. When WAL-mode is used, it is possible // to switch between normal and exclusive locking mode at any time. However, the creation // of a "-shm"-file can only be avoided, when exclusive locking mode is set BEFORE entering // WAL-mode. If this is the case, it is not possible to leave exclusive locking mode // without leaving WAL-mode before, because the "-shm"-file was not created. // The regular connections in our connection pool use WAL-mode. Therefore we have // to open one connection without WAL-Mode (here with JournalMode=OFF) and set locking_mode= // EXCLUSIVE before we create the first regular connection that goes into the pool. // To use exclusive locking mode, it is additionally necessary to set locking_mode=EXCLUSIVE // for every connection in the pool via the InitializationCommand. If "PRAGMA locking_mode= // EXCLUSIVE" is not in the InitializationCommand, normal locking mode is used // although we issue "PRAGMA locking_mode=EXCLUSIVE" at this point. // For details see here: http://sqlite.org/wal.html#noshm // Avoiding the creation of an "-shm"-file materially improves the database performance. if (_settings.UseExclusiveMode) { connBuilder.JournalMode = SQLiteJournalModeEnum.Off; using (var connection = new SQLiteConnection(connBuilder.ToString())) { connection.Open(); using (var command = new SQLiteCommand(SQLiteSettings.EXCLUSIVE_MODE_COMMAND, connection)) command.ExecuteNonQuery(); connection.Close(); } } // Just test one "regular" connection, which is the first connection in the pool using (var transaction = BeginTransaction()) transaction.Rollback(); } catch (Exception e) { ServiceRegistration.Get <ILogger>().Critical("SQLiteDatabase: Error establishing database connection", e); throw; } }
public SQLiteTransaction(SQLiteDatabase database, SQLiteSettings settings) { _database = database; _settings = settings; _connection = _database.ConnectionPool.GetConnection(); }