I suggest using a continuous (or at least nightly) build strategy. Rebuild the database on every checkin, or at least once per day. Also once per day, run unit tests to exercise each bit of code, be it in a stored procedur, a trigger or a data access layer.
There is a great cost to writing stored procs, but this will identify breaks immediately. Once you know where the break is, you can fix it.
I'd be interested to hear other people's experiences with this strategy applied to database changes.
当数据库发生更改时，我们在 EA 中进行生成 SQL，并将其签入我们的版本控制 (svn)。 我们使用 Hudson 进行构建，当它看到您修改了签入的sql。
We use Enterprise Architect for our DB definitions. We include stored procedures, triggers, and all table definitions defined in UML. The three brilliant features of the program are:
Import UML Diagrams from an ODBC Connection.
Generate SQL Scripts (DDL) for the entire DB at once
Generate Custom Templated Documentation of your DB.
I've never been more impressed with any other tool in my 10+ years as a developer. EA supports Oracle, MySQL, SQL Server (multiple versions), PostGreSQL, Interbase, DB2, and Access in one fell swoop. Any time I've had problems, their forums have answered my problems promptly. Highly recommended!!
When DB changes come in, we make then in EA, generate the SQL, and check it into our version control (svn). We use Hudson for building, and it auto-builds the database from scripts when it sees you've modified the checked-in sql.
My advice would be to get rid of stored procedures and instead use inline SQL, maybe maintained in text/xml files. I find SProcs are far more annoying and time consuming to maintain. Once the query plan is generated (first time the query is executed) you'll notice negligible difference in performance. Plus you'll be able to version control your entire DB scripts...
Try to get rid of the least used functionality. Question the features that are not used all the time. Each feature in an application has several levels of costs associated with it (maintaining, support, regression testing, code complexity, etc.).
Stay away from Stored procedures, unless there is absolutely no way to do it efficiently and in a scalable manner in the code.
Introduce an ORM solution gradually (using refactoring to move from JDBC to ORM) to reduce the amount of code and code complexity in CRUD operations
Build functional, integration and unit tests as and when you fix a bug and incorporate those tests in to the Continuous integration system. Automate your regression testing as much as possible to identify problems as soon as it is introduced by a check-in.
In general, whenever you fix a bug, use that opportunity to refactor to decouple the implementations/code modules.
发布评论
评论（5）
您可能想看看重构数据库：进化数据库设计这本书。
You might want to checkout this book on Refactoring Databases: Evolutionary Database Design.
我建议使用连续（或至少每晚）构建策略。
每次签入时重建数据库，或至少每天一次。
另外，每天运行一次单元测试来测试每一位代码，无论是在存储过程、触发器还是数据访问层中。
编写存储过程的成本很高，但这会立即识别中断。
一旦知道断点在哪里，就可以修复它。
我很想听听其他人将此策略应用于数据库更改的经验。
I suggest using a continuous (or at least nightly) build strategy.
Rebuild the database on every checkin, or at least once per day.
Also once per day, run unit tests to exercise each bit of code, be it in a stored procedur, a trigger or a data access layer.
There is a great cost to writing stored procs, but this will identify breaks immediately.
Once you know where the break is, you can fix it.
I'd be interested to hear other people's experiences with this strategy applied to database changes.
我们使用 Enterprise Architect 进行数据库定义。 我们包括存储过程、触发器以及 UML 中定义的所有表定义。 该程序的三个出色功能是：
在我作为开发人员 10 多年的时间里，从来没有任何其他工具给我留下过如此深刻的印象。 EA 一次性支持 Oracle、MySQL、SQL Server（多个版本）、PostGreSQL、Interbase、DB2 和 Access。 每当我遇到问题时，他们的论坛都会及时回答我的问题。 强烈推荐！！
当数据库发生更改时，我们在 EA 中进行生成 SQL，并将其签入我们的版本控制 (svn)。 我们使用 Hudson 进行构建，当它看到您修改了签入的sql。
We use Enterprise Architect for our DB definitions. We include stored procedures, triggers, and all table definitions defined in UML. The three brilliant features of the program are:
I've never been more impressed with any other tool in my 10+ years as a developer. EA supports Oracle, MySQL, SQL Server (multiple versions), PostGreSQL, Interbase, DB2, and Access in one fell swoop. Any time I've had problems, their forums have answered my problems promptly. Highly recommended!!
When DB changes come in, we make then in EA, generate the SQL, and check it into our version control (svn). We use Hudson for building, and it auto-builds the database from scripts when it sees you've modified the checked-in sql.
我的建议是摆脱存储过程，转而使用内联 SQL，可能在文本/xml 文件中维护。 我发现 SPrc 更烦人，而且维护起来更耗时。 生成查询计划后（第一次执行查询），您会注意到性能差异可以忽略不计。 另外，您将能够对整个数据库脚本进行版本控制......
My advice would be to get rid of stored procedures and instead use inline SQL, maybe maintained in text/xml files. I find SProcs are far more annoying and time consuming to maintain. Once the query plan is generated (first time the query is executed) you'll notice negligible difference in performance. Plus you'll be able to version control your entire DB scripts...
以下是我的建议：
如果您对数据库迁移问题有疑问，这可能会有所帮助：http:// /shashivelur.com/blog/2008/07/hibernate-db-migration/
Here are my suggestions:
If you have have questions about Database migration problems, this might help: http://shashivelur.com/blog/2008/07/hibernate-db-migration/