Recipe required for testing all database views in specified database

[adrianyorke]

Database views should be regularly tested to ensure that they are still functioning. Views are effectively a SELECT statement that queries data from database tables and other views. The underlying tables and join conditions that the view depends on can be modified in a way that breaks existing views if they have not been refactored in line with new db structure.

First, it is necessary to query the database catalog and loop through all database views that exist. For each view discovered, a simple "SELECT TOP 1 * FROM <database_view>;" will be sufficient to check that the view logic is still functioning. This technique should work even if there is no data loaded.

[ericbjones]

This one sounds fun and is pretty well defined already, but we could also add a suggested sample database to test against in the description (as just an optional, but useful suggestion to the developer). I propose the Sakila sample database from MySql : https://dev.mysql.com/doc/sakila/en/ . Nice thing about this sample db is its well documented at that link and it even contains install instructions. This database has seven views in it, so that will work perfectly for dev on this recipe, and it has a lot of other features so it will work nicely for pretty much any db recipies we make.