Enterprise Rails

Exercises

1. Write a stored procedure that verifies a one-to-one correspondence between rows in the materialized “reconciler” view and the unmaterialized view.

2. Using the stored procedure from Exercise 12-1, write a series of unit tests that modify records—one at a time as well as simultaneously—and then assert the validity of the reconciler view.

3. It was noted that our refresh function does extra work to calculate and return the expiry value. Write a new movie_showtime_expiry stored procedure that takes start_time as a parameter and returns an expiry value without selecting from the unmaterialized view.

4. Rewrite the snapshot creation query and the refresh function to use the new procedure you wrote in Exercise 12-3. Time the snapshot creation and refresh operations with both methods. Which set is faster and by how much?

5. Following the procedure outlined in this chapter, create a materialized view for ptc, the named subquery from Example 12-2. Then rewrite the triggers for the main materialized view to use this new physical table. How do you propagate invalidations?