Projects:DBSourceManager

Introduction

One important part of the Openbravo ERP sources is the database. Common areas here include database structures (tables, views, functions, triggers, and sequences), and database data. Up until now we provided these sources as a database dump, one for each database engine supported: Oracle and PostgreSql. Now these sources are an organized collection of XML files with a syntax that is independent of the database server making them very easy to browse and inspect. This is a great enhancement that offers a long list of benefits for the community of users and developers.

To manage this new representation of the database, there is the tool DBSourceManager that is based on Apache DdlUtils that facilitates the manipulation of the database source files. And this tool contains a collection of ant tasks to access its functionality.

This project will facilitate the creation and update processes of the Openbravo database, and the export process of the changes made to the database.

Documentation

• DBSourceManager Schema
• DBSourceManager Architecture
• Database sources management

Project Plan

Currently, there are three main objectives in this project: first, we want to stabilize the code. This means that we want to make sure that all the operations are permitted in all the Openbravo-compatible platforms (and specifically, this means that DBSourceManager should be able to export a PostgreSQL database to source files, something that currently is not possible), and that code should be standard and completely platform-agnostic, and should not be changed by DBSourceManager if possible.

Secondly, we want to make it easier and more efficient for developers to use the tool. This mainly means we aim to improve performance, and to give more and better information of the execution process.

Thirdly, we want to correct and improve several details, and "clean" the implementation a little bit.

This three objectives are divided in the following tasks.

Project tasks

• Code stabilization
  • Export process will also work on PostgreSQL database: When working with a PostgreSQL database, this will make possible to export the complete database model in a standard format and all the data to xml files, that can be version-controlled or better managed.
  • Additional filter to export data which is useful for clients but that is not medatada strictly speaking: Currently, there are two kinds of data, sourcedata, which is the Openbravo application dictionary data, and sampledata, the rest of the data. We've found that there actually is a third kind of data, that we will call masterdata, which is data that is not part of the Openbravo application dictionary, but is very useful to a lot of users, such as the currency data, or the country data.
  • It will be possible to write the columns default value inside a CDATA section.
  • Openbravo Oracle-specific procedures will be moved from the model to the Oracle postscript. Currently, there are a few Oracle-specific procedures that do not work in PostgreSQL (and shouldn't anyway, that's why they are Oracle-specific). We will remove this procedures from the model, and put them inside an Oracle postscript, that is executed immediately after a database creation or update process.
  • onCreatedDefault attribute. Currently, when performing several kinds of updates to the model, there are problems related to the inconsistencies appeared when trying to use old data with a new model. As an example, if anyone tries to add a not nullable column to an existing table with data, there will be an error as the old data doesn't have values for the new column. We are going to implement a new attribute that will allow the user to specify what kind of update should be done to the data in order to make it new model compliant.
• Making the tool easier to use and more efficient
  • Implementation of a security mechanism to prevent misuse of the utility. Right now, if you use the tool in combination with Subversion, and you don't follow the standard procedure (steps: update the source code in Subversion -> update the database -> work with the database -> export the database with DBSourceManager -> commit the changes to Subversion repository), and specifically, if you forget to update the database after you've updated the repository, you can accidentally revert changes made by other people to the repository. We will implement a security mechanism to prevent this.
  • Current strategy to insert data into the database (UPDATE followed by an INSERT if no rows have been updated) will be changed (an INSERT will be followed by an UPDATE on failure) to improve performance.
  • If an SQL statement fails when activated in the database, DBSourceManager will show it for debugging purposes.
  • Several messages shown when prescripts and postscripts are executed will be hidden. There are several messages shown when prescripts and postscripts are executed that appear to be failures, but they actually are not. These messages will be hidden.
• Minor details
  • All the relevant code will be placed inside the same folder in the Openbravo project
  • Several targets inside the build.xml will be deleted, because they are no longer needed.

Project schedule

This is the planned schedule for the project.

Releases Plan

We plan to release two versions of DBSourceManager in the next month. There will be a first release when we finish the "CDATA in default" task. This release should produce standard xml code that should not be changed in the future, and we will start to use it in the Openbravo trunk as soon as it is available. A second release will be launched once we complete the rest of the tasks.

Automated Testing Task

Once we launch the first release, we will start to use an specific automated testing task. This task will test that code produced by DBSourceManager is stable, and doesn't change if the database hasn't changed. It will also check that the database produced by the same sources is the same.