What method do you use to version-control your database? I've committed all our database tables as separate .sql scripts to our respository (mercurial). In that way, if any member of the team makes a change to the employee table, say, I will immediately know which particular table has been modified when I updated my repository.
Such a method was described in: What are the best practices for database scripts under code control.
Presently, I'm writing a python script to execute all the .sql files within the database folder, however, the issue of dependencies due to foreign-key constraints ensures we can't just run the .sql files in just any order.
The python script is to generate a file with the order in which to execute the .sql files. It will execute the .sql files in the order in which they appear in the tableorder.txt file. A table cannot be executed until its foreign key table has been executed, for example:
tableorder.txt
table3.sql
table1.sql
table7.sql and so on
Already, I have generated the dependency list for each table, from code, by parsing the result of the "show create table" mysql command. The dependency list may look thus:
tblstate: tblcountry //tblcountry.sql must be executed before tblstate.sql etc
tblemployee: tbldepartment, tblcountry
To generate the content of the tableorder.txt, I will need an algorithm that will look thus:
function print_table(table):
foreach table in database:
if table.dependencies.count == 0
print to tableorder.txt
if table.dependencies.count > 0
print_table(dependency) //print dependency first
end function
As you will imagine, this involves lots of recursion. I'm beginning to wonder if it's worth the effort? If there's some tool out there? What tool (or algorithm) is there to generate a list of the order to execute separate .sql tables and views taking into consideration dependencies? Is it better to version control separate .sql file for each table/view or better to version control the entire database to a single .sql file? I will appreciate any response as this has taken so many days. Thanks.
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…