Populating SQLite in memory for unit testing

Question

I am thinking of using SQLite as an in memory stub for my Oracle database. I can easily direct all my DAL commands to the SQLite, but I am now wondering how I should easily populate the data for each test method.
Should each method begin by creating the needed table(s) and inserting the rows for the specific test? Should I populate in the Fixture SetUp phase the data in the memory? Is there some other way (Like reading it from some file, but discarding the changes so that the next read will be the same)?
Maybe I should just stub the db with a normal stub, and return locally created obejcts when needed (DataSets and DataTables)? I thought about trying this, but this way I will not be testing the actual queries passing along, and I am trying to unit test methods that perform SQL selects. I want to test the syntax and validity of the queries as well.

Any best practices regarding this? Or just good ideas?

Answer 1

How about just backing up the SQLite-Db-File?

The good thing about SQLite is, that you just can copy the whole db-file as often as you want. You can also have SQL-files to populate some db file with data. I don't understand your problem totally, but with a mixture of db files (as templates) and (optionaly) some SQL-Files to fill tables as needed should suffice for also very difficult testing issues.

The SQL-Files can also easily created by dumping simple files and (optionaly) deleting unwanted entries or adding additional ones.