It is often desirable to preload the database with test data before running tests. In this recipe, we will show you how to load the in-memory database with data from a SQLite file database.
Complete the previous recipe, Fast-testing with the SQLite in-memory database.
Create a SQLite file database with the desired schema (tables and columns), 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.
- Add a new class named
SQLiteLoaderusing 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(); } - Add a new abstract class named
DataDependentFixture, inherited fromNHibernateFixture, using the following code:protected abstract string GetSQLiteFilename(); protected override void OnSetup() { base.OnSetup(); var conn = (SQLiteConnection) Session.Connection; new SQLiteLoader().ImportData(conn, GetSQLiteFilename()); } - Add a new class named
QueryTests, inherited fromDataDependentFixture. - In
QueryTests, overrideGetSQLiteFilename()to return the path to your SQLite file. - Add the following test to
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(); } } - Decorate
QueryTestswith NUnit'sTestFixtureattribute. - Build the project.
- Run the NUnit tests.
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 ATTACH 'filePath' AS schemaName command, 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 asdf string for schemaName, we can execute SELECT * FROM asdf.tblTestData.
We loop through each table executing the INSERT INTO tableName SELECT * FROM schemaName.tableName statement. 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 DETACH schemaName command.
We can use SQLiteLoader.ExportData to move data from the SQLite in-memory database to a file database. In addition, each test fixture can use test data from a different file database.