Discussions

News: Automate your database maintenance with DbMaintain

  1. Maintenance of databases can impose quite some overhead. Most projects have several development and test databases, or multiple production databases at different customer sites, and the version of these database instances differs. Recently, a new open source tool has been released called DbMaintain. DbMaintain automates execution of scripts to a relational database. It enables bringing database scripts into version control just like regular source code and can be used to transparently deploy databases from development to production. DbMaintain automates the part of the deployment which is most difficult to automate, opening the door for fully automated deployments. The tool works as follows: Database scripts are organized in folder tree, like demonstrated in following example: scripts/01_v1.0/01_products_and_orders.sql 02_users.sql 02_v1.1/01_add_barcode_column.sql 02_drop_itemcode_column.sql Folder and script names must start with an index number followed by an underscore and a description. The index indicates the sequence of the scripts or script folders. The database keeps track of all executed scripts in the table DBMAINTAIN_SCRIPTS: FILE_NAME CHECKSUM SUCCEEDED 01_v1.0/01_products_and_orders.sql q34dxg37ds97ze97sdfz 1 01_v1.0/02_users.sql fXrZ45FsGs6SDhd4zs2h 1 02_v1.1/01_add_barcode_column.sql rz56eR1x78qePMr67q45 1 02_v1.1/02_drop_itemcode_column.sql 7dR3dg634g8kUfxcX9oW 1 Suppose you add a new script 02_v1.1/03_add_productid_sequence.sql. The next time you call the updateDatabase operation, the database is updated incrementally by executing this new script. DbMaintain is not the first project trying to automate database deployments, comparable projects exist such as dbmigrate, migratedb, liquibase and dbdeploy. DbMaintain however goes a lot further than these projects in terms of the features that it offers:
    • The tool doesn't just 'suppose' that you don't modify existing scripts or add scripts out-of-sequence, it actually verifies it. For each script, a checksum is stored. If an existing script changed, an error is given and the database is not updated.
    • A from-scratch mode is supported: if used, all script manipulations are allowed: If you modify an existing scripts or add one out-of-sequence, all database objects are dropped and the database is recreated from-scratch. The from-scratch option is ideal for development or test databases or any database in which preservation of data is not a requirement.
    • Support is offered for repeatable scripts, that have to be written such that they can be executed multiple times. They can always be changed: if so they are simply re-executed.
    • Postprocessing scripts can be defined that are executed each time some script was executed.
    • Multiple target databases or database users are supported.
    • Support for oracle stored procedures.
    • A script archive (a jar file) can be created and used for deployment. Such an archive can be published as a project artifact, just like you would publish a regular jar or war file.
    • Support for script renames: Scripts can be renamed and reorganized, as long as the relative sequence of the scripts doesn't change.
    DbMaintain originates from the project unitils, a library offering various utilities for unit/integration testing. The 'dbmaintainer' feature of unitils was created to automatically maintain a database that is used to execute automatic tests, but as features were added the dbmaintainer became more generally usable. That's why we decided to split it off into a new project.

    Threaded Messages (4)

  2. This is similar to LiquiBase ?
  3. Hi, Has it a feature for updating a DEV Database which is MYSQL, POSTGRES from a PRODUCTION Database which runs on Oracle ? I am looking for a product which updates (or even erase my dev DB's) my dev DB's with the content of the Production Database. But my production DB runs on Oracle and my DEV DB's run on postgres, mysql. Any suggestions ? Georges
  4. Don't bother[ Go to top ]

    Flip doesn't even answer questions here. Just use Liquibase.
  5. Re: Don't bother[ Go to top ]

    You're right blaming me for not answering these questions, I'm sorry for that. I'll try to make up for it a little bit with these late answers:
    This is similar to LiquiBase <http://www.liquibase.org/>?
    They are similar, and liquibase offers a number of features dbmaintain doesn't support: e.g. (partly-automatic) rollback of database changes and the possibility to encode preconditions that are automatically checked before running updates. Dbmaintain on the other hand has following advantages: - You simply use SQL scripts. This makes it easy to get started with existing SQL scripts and execute the same scripts in other tools. - A from-scratch option is offered, which is very useful during development. While developing, you often want to update SQL scripts that were already executed on a test database because of errors, because you forgot something or if you want to review scripts created by other team members. Due to the incremental nature of databases you can't always simply fix the script and perform the database update again. With the from-scratch feature enabled, when DbMaintain notices that an existing incremental script changed, all database objects are dropped and the database is recreated from-scratch. Of course you can switch off this option (for any database in which runtime data must be preserved), in this case the database update fails if the system detects that an existing script was changed.
    Has it a feature for updating a DEV Database which is MYSQL, POSTGRES from a PRODUCTION Database which runs on Oracle ? I am looking for a product which updates (or even erase my dev DB's) my dev DB's with the content of the Production Database. But my production DB runs on Oracle and my DEV DB's run on postgres, mysql.
    Such a feature is not supported by DbMaintain, it's not intended to be a database comparison tool or a tool that performs automatically updates on a database by analyzing the current structure of the tables.