JDBC SQL Server 数据库迁移
我正在开发一个 Java Web 应用程序（Adobe Flex 前端、JPA/Hibernate/BlazeDS/Spring MVC 后端），并且很快就会达到无法再擦除数据库并重新生成它的地步。
处理数据库模式更改的最佳方法是什么？生产和测试数据库是 SQL Server 2005，开发人员使用 MySQL，单元测试针对 HSQLDB 内存数据库运行。我很乐意让开发机器继续使用 Hibernate 从示例数据中擦除和重新加载数据库来重新生成表。然而，对于生产部署，DBA 希望有一个可以手动执行的 DDL 脚本。
因此，我理想的解决方案是我可以编写 Rails 风格的迁移，在测试服务器上执行它们，并在验证它们工作后能够编写 DBA 可以在生产服务器上执行的 SQL Server DDL（以及已经被验证可以在测试服务器上工作）。
有什么好的工具可以做到这一点？我应该手动编写 DDL（并让开发机器使用 Hibernate 重新生成数据库）吗？我可以使用 migrate4j 这样的工具（它似乎对 SQL Server 的支持有限，如果有的话）？
我还希望将数据库操作脚本集成到此过程中（例如，通过拆分所有现有字符串的 JDBC 脚本将“名称”字段转换为“名字”、“姓氏”字段）。
任何建议将不胜感激！
I'm working on a Java web application (Adobe Flex front-end, JPA/Hibernate/BlazeDS/Spring MVC backend) and will soon reach the point where I can no longer wipe the database and regenerate it.
What's the best approach for handling changes to the DB schema? The production and test databases are SQL Server 2005, dev's use MySQL, and unit tests run against an HSQLDB in-memory database. I'm fine with having dev machines continue to wipe and reload the DB from sample data using Hibernate to regenerate the tables. However, for a production deploy the DBA would like to have a DDL script that he can manually execute.
So, my ideal solution would be one where I can write Rails-style migrations, execute them against the test servers, and after verifying that they work be able to write out SQL Server DDL that the DBA can execute on the production servers (and which has already been validated to work agains the test servers).
What's a good tool for this? Should I be writing the DDL manually (and just let dev machines use Hibernate to regenerate the DB)? Can I use a tool like migrate4j (which seems to have limited support for SQL Server, if at all)?
I'm also looking to integrate DB manipulation scripts into this process (for example, converting a "Name" field into a "First Name", "Last Name" field via a JDBC script that splits all the existing strings).
Any suggestions would be much appreciated!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
幂等更改脚本 带有版本表（以及应用所有编号大于当前存储在版本表中的版本的更改脚本的工具）。另请查看提到的帖子使用 INFORMATION_SCHEMA 视图的 Bulletproof Sql 更改脚本。
要实现这一点，您可以推出自己的解决方案或使用现有工具，例如 DbUpdater（在更改脚本的评论)、LiquiBase 或 数据库部署。后者有我的偏好。
Idempotent change scripts with a version table (and a tool to apply all the change scripts with a number greater than the version currently stored in the version table). Also check the mentioned post Bulletproof Sql Change Scripts Using INFORMATION_SCHEMA Views.
To implement this, you could roll out your own solutions or use existing tools like DbUpdater (mentioned in the comments of change scripts), LiquiBase or dbdeploy. The later has my preference.
我依靠 hibernate 来创建生产服务器上所需的任何内容。不存在丢失数据的风险，因为它永远不会删除任何内容：它只会添加丢失的内容。
在当前项目中，我们建立了一个约定，任何需要更改数据库（架构或数据）的功能都需要提供自己的 DDL/DML 片段，这意味着我们需要做的就是将片段聚合到一个脚本并执行它以使生产保持最新。这些在大规模上都不起作用（片段的顺序变得至关重要，并非每个人都遵循约定等），但在小团队和迭代过程中它工作得很好。
I depend on hibernate to create whatever it needs on the production server. There's no risk of losing data because it never removes anything: it only adds what is missing.
On the current project, we have established a convention by which any feature which requires a change in the database (schema or data) is required to provide it's own DDL/DML snippets, meaning that all we need to do is to aggregate the snippets into a single script and execute it to get production up to date. None of this works on a very large scale (order of snippets becomes critical, not everyone follows the convention etc.), but in a small team and an iterative process it works just fine.