I have a greenfield project involving a SQL database that is under development. There is no production data involved yet. There is a rather long list of specifications which I expect can be implemented with a collection of SQL tables, views, functions, and procedures. The overview of the inner loop of the development process is basically the following:
- drop and recreate the database
- create the tables, views, etc
- import test data
- run tests
- make changes to code
- repeat beginning at (1)
The development has already involved a number of refactorings. The results of these refactorings are good. However, they usually precipitate a period of dependency hell in step (2) during which I have to manually re-resolve the order of the files defining the objects are executed. That file order is currently hard-coded as a list of file names in a script. Each file has exactly one object defined in it.
The process of manually resolving dependencies works, but is labor-intensive. For C and .Net projects in the past I have used make and MSBuild to manage dependencies, respectively. I can almost imagine how to manage SQL dependencies with those tools, but I haven't seen it done and I'd rather not re-invent the wheel if there is a method that is already proven. A search of stackoverflow for makefile and MSBuild with SQL didn't produce any results that were obviously on-point.
Is there a best practice for resolving dependencies of SQL objects for build during development?