你用什么方法,版本控制你的数据库? 我承诺我们所有的数据库表作为单独的.sql脚本,我们的程序存储库(水银)。 这样一来,如果团队中的任何成员进行了更改雇员表,再说,我会马上知道哪个特定的表时,我更新了我的资料库已被修改。
这样的方法中描述了: 什么是代码控制数据库脚本的最佳实践 。 目前,我正在写一个python脚本到数据库文件夹中执行所有的.sql文件,然而,依赖的是由于外键约束的问题,确保我们不能只是运行在短短任何顺序.sql文件。
该Python脚本是产生在其中执行的.sql文件的顺序文件。 这将在它们出现在tableorder.txt文件中的顺序执行.sql文件。 能不能执行,直到表的外键表已经被执行,例如:
tableorder.txt
table3.sql
table1.sql
table7.sql等
目前,我已经生成每个表的相关列表,从代码,通过解析的“秀CREATE TABLE” mysql命令的结果。 依赖关系列表可以这样看:
tblstate: tblcountry //tblcountry.sql must be executed before tblstate.sql etc
tblemployee: tbldepartment, tblcountry
为了产生tableorder.txt的内容,我需要一个算法,看起来这样的:
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
正如你想象的,这涉及到大量的递归。 我开始不知道是否是值得的? 如果有一些工具在那里? 什么工具(或算法)有生成为了执行不同的.sql表和视图考虑到依赖性列表? 它是更好的版本控制单独的.sql文件为每个表/视图或更好的版本控制整个数据库到一个.sql文件? 我欢迎任何回应,因为这已经采取了这么多天。 谢谢。
I do not use MySQL, but rather SQL Server, however, this is how I version my database:
(This is long, but in the end I hope the reasoning for me abandoning a simple schema dump as the primary way to handle database versioning is made apparent.)
I make a modification to the schema and apply it to a test database.
I generate delta change scripts and a dump of the schema after said scripts. (I use ApexSQL, but there are likely MySQL-specific tools to help.)
The delta change scripts know how to go from the current to target schema version: ALTER TABLE existing, CREATE TABLE new, DROP VIEW old .. Multiple operations can occur within the same .SQL file as the delta is of importance.
The dump of the schema is of the target schema version: CREATE TABLE a, CREATE VIEW b .. there is no "ALTER" or "DROP" here, because it is just a snapshot of the target schema. There is one .SQL file per database object as the schema is of importance.
I use RoundhousE to apply the delta change scripts. (I do not use the RoundhousE "anytime script" feature as this does not correctly handle relationships.)
I learned the hard way that applying database schema changes cannot be reliably done without a comprehensive step-by-step plan and, similarly (as noted in the question), the order of relationship dependencies are important. Just storing the "current" or "end" schema is not sufficient. There are many changes that cannot be retroactively applied A->C without knowing A->B->C and some changes B might involve migration logic or corrections. SQL schema change scripts can capture these changes and allow them to be "replayed".
However, at the same time just saving the delta scripts does not provide a "simple view" of the target schema. This is why I also dump all the schema as well as the change scripts and version both. The view dump could, in theory, be used to construct the database but due to relationship dependencies (the very kind noted in the question), it may take some work and I do not use it as part of an automated schema-restore approach: yet, keeping the schema dump part of the Hg version-control allows quick identification of changes and viewing the target schema at a particular version.
The change deltas thus move forward through the revisions while the schema dump provides a view at the current revision. Because the change deltas are incremental and forward-only it is important to keep the branch dealing with these changes "clean", which is easy to do with Hg.
In one of my projects I am currently at database change number 70 - and happy and productive! - after switching to this setup. (And these are deployed changes, not just development changes!)
Happy coding.
我不知道如何解答您的问题,但我倾向于只使用mysqldump(标准安装的一部分)。 这使我的SQL创建表,并对其进行填充,有效的序列化数据库。 例:
> mysqldump -u username -p yourdatabase > database_dump.sql
要加载从转储文件的SQL数据库:
MySQL的名-u用户名-p -e “源/path/to/database_dump.sql”
为了进一步回答你的问题,我会版本单独控制每一只如果有多人以这样的方式,冲突很可能与控制只是一个单一的转储是版本发生在数据库上工作。 我从来没有打一个项目,这样的话(数据库往往是在项目的初始阶段后的系统中最易挥发的部分之一),所以我只是版本控制的数据库转储作为一个整体,而不是每个表格中个别。
您可以使用sqitch 。 这里是一个用于MySQL的教程 ,但它实际上是数据库无关。
变动实现为原产于您所选择的数据库引擎...数据库更改脚本可能宣布其他的变化,甚至从其他Sqitch项目变化的相关性。 这将确保执行的正确顺序,甚至当你乱序...变更部署是通过保持一个计划文件管理,以您的VCS提交的修改。 因此,没有必要进行编号更改,但你可以,如果你想要的。 Sqitch并不很担心你如何命名您的更改...,直到你标记并释放你的应用程序,您可以根据需要随时修改变更部署脚本。 他们不锁在仅仅是因为他们一直致力于为您的VCS。 这使您可以采取迭代方法来开发数据库架构。 或者,更好的,你可以做测试驱动数据库开发。