It is often desirable to preload the database with test data before running tests. In this recipe, I will show you how to quickly load the in-memory database with data from a SQLite file database.
Complete the previous recipe, Fast testing with SQLite in-memory database.
Create a SQLite file database with identical schema, containing test data. This can be accomplished in a number of ways. Perhaps the easiest is to export an in-memory database using SQLiteLoader.ExportData
from this recipe.
SQLiteLoader
using the following code:private static ILog log = LogManager.GetLogger(typeof(SQLiteLoader)); private const string ATTACHED_DB = "asdfgaqwernb"; public void ImportData( SQLiteConnection conn, string sourceDataFile) { var tables = GetTableNames(conn); AttachDatabase(conn, sourceDataFile); foreach (var table in tables) { var sourceTable = string.Format("{0}.{1}", ATTACHED_DB, table); CopyTableData(conn, sourceTable, table); } DetachDatabase(conn); } public void ExportData( SQLiteConnection conn, string destinationDataFile) { var tables = GetTableNames(conn); AttachDatabase(conn, destinationDataFile); foreach (var table in tables) { var destTable = string.Format("{0}.{1}", ATTACHED_DB, table); CopyTableData(conn, table, destTable); } DetachDatabase(conn); } private IEnumerable<string> GetTableNames( SQLiteConnection conn) { string tables = SQLiteMetaDataCollectionNames.Tables; DataTable dt = conn.GetSchema(tables); return from DataRow R in dt.Rows select (string)R["TABLE_NAME"]; } private void AttachDatabase( SQLiteConnection conn, string sourceDataFile) { SQLiteCommand cmd = new SQLiteCommand(conn); cmd.CommandText = String.Format("ATTACH '{0}' AS {1}", sourceDataFile, ATTACHED_DB); log.Debug(cmd.CommandText); cmd.ExecuteNonQuery(); } private void CopyTableData( SQLiteConnection conn, string source, string destination) { SQLiteCommand cmd = new SQLiteCommand(conn); cmd.CommandText = string.Format( "INSERT INTO {0} SELECT * FROM {1}", destination, source); log.Debug(cmd.CommandText); cmd.ExecuteNonQuery(); }
private void DetachDatabase(SQLiteConnection conn) { SQLiteCommand cmd = new SQLiteCommand(conn); cmd.CommandText = string.Format(«DETACH {0}», ATTACHED_DB); log.Debug(cmd.CommandText); cmd.ExecuteNonQuery(); }
DataDependentFixture
, inherited from NHibernateFixture
, using the following code:protected abstract string GetSQLiteFilename(); protected override void OnSetup() { base.OnSetup(); var conn = (SQLiteConnection) Session.Connection; new SQLiteLoader().ImportData(conn, GetSQLiteFilename()); }
QueryTests
, inherited from DataDependentFixture
.QueryTests
, override GetSQLiteFilename()
to return the path to your SQLite file.QueryTests
:[Test] public void Director_query_should_return_one_movie() { var query = Session.QueryOver<Movie>() .Where(m => m.Director == "Tim Burton"); using (var tx = Session.BeginTransaction()) { var movies = query.List<Movie>(); Assert.That(movies.Count == 1); tx.Commit(); } }
QueryTests
with NUnit's TestFixture
attribute.In the QueryTests
fixture, GetSQLiteFilename()
returns the path of the SQLite file containing our test data. DataDependentFixture
passes this file path and our connection to the SQLite in-memory database over to SQLiteLoader.ImportData()
.
We call SQLiteConnection.GetSchema()
to create a list of table names in the database.
Next, we attach the file database to the in-memory database using the command ATTACH 'filePath' AS schemaName
where filePath
is the path to the file database and schemaName
is a string constant. This allows us to reference the tables in the file database from the memory database. For example, if our file database has a table named tblTestData
, and we use the string asdf
for schemaName
, we can execute SELECT * FROM asdf.tblTestData
.
We loop through each table, executing the statement INSERT INTO tableName SELECT * FROM schemaName.tableName
. This command quickly copies all the data from a table in the file database to an identical table in the memory database. Because SQLite doesn't enforce foreign key constraints, we do not need to be concerned with the order we use to copy this data.
Finally, we detach the file database using the command DETACH schemaName
.