public SQLiteTransaction(SQLiteDatabase database, IsolationLevel level, SQLiteSettings settings)
{
_database = database;
_settings = settings;
_connection = _database.ConnectionPool.GetConnection();
_transaction = _connection.BeginTransaction(level);
}
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();
}
