View source | View content page | Page history | Printable version   

Projects:DBSourceManager/Database sources management

Contents

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.

Tasks definition

To define the new database tasks, these tasks have to be declared at the beginning of the build file. Also the file dbmanager.jar that implements the database tasks must be available in the attribute classpath of every task definition.

  <path id="runtime-classpath">
    <fileset dir="./lib"> 
      <include name="**/*.jar" /> 
    </fileset> 
  </path> 

  <taskdef name="createdatabase"
           classname="org.openbravo.ddlutils.task.CreateDatabase">
    <classpath refid="runtime-classpath" /> 
  </taskdef> 
  <taskdef name="createscript" 
           classname="org.openbravo.ddlutils.task.CreateXML2SQL"> 
    <classpath refid="runtime-classpath" /> 
  </taskdef> 
  <taskdef name="alterdatabase" 
           classname="org.openbravo.ddlutils.task.AlterDatabase"> 
    <classpath refid="runtime-classpath" /> 
  </taskdef> 
  <taskdef name="alterscript" 
           classname="org.openbravo.ddlutils.task.AlterXML2SQL"> 
    <classpath refid="runtime-classpath" /> 
  </taskdef> 
  <taskdef name="exportdatabase"
           classname="org.openbravo.ddlutils.task.ExportDatabase">
    <classpath refid="runtime-classpath" />
  </taskdef>
  <taskdef name="importdata" 
           classname="org.openbravo.ddlutils.task.ImportDataXML"> 
    <classpath refid="runtime-classpath" /> 
  </taskdef> 
  <taskdef name="exportdata" 
           classname="org.openbravo.ddlutils.task.ExportDataXML"> 
    <classpath refid="runtime-classpath" /> 
  </taskdef> 

ANT tasks

Create database model task

This task connects to an empty database and creates all the database structures defined in the model attribute. To create all the database structures, the task first generates a database script from the input file with the syntax of the database defined in the connection attributes an after executes this script in the database.

Before creating all the structures defined in the model, the SQL script defined in the prescript attribute is executed. After all the structures are created, the SQL script defined in the postscript attribute is launched. These scripts are used to create all the structures needed by the model that are proprietary to the target database engine.

The attributes of this ant task are:

driver, url, user and password
define the properties of the JDBC connection to the database. The jar file that contains the database driver must be available in the java classpath.
model
defines the path to the folder that contains the database structures XML files to create.
object
defines the list of database objects (separated by comma) to create, the rest of database objects are ignored. If empty, all the database objects defined in the model are created.
prescript
defines the path to the SQL script to execute before creating the model. If empty then the default SQL script prescript-<database-engine-name>.sql; if one exists, it is executed.
postscript
defines the path to the SQL script execute after creating the model. If empty then the default SQL script postcript-<database-engine-name>.sql; if one exists, it is executed.
dropfirst
if true, the task first executes a script that drops all the structures defined in the input XML file.
failonerror
if true, and if a statement fails, the execution of the script aborts at that point.
verbosity
define the log level. The possible values are INFO, DEBUG. By default the level is INFO.

An example of this task is:

        <createdatabase 
            driver="org.postgresql.Driver"
            url="jdbc:postgresql://localhost:5432/openbravodb"
            user="tad"
            password="tad"
            model="/home/adrian/model"
            dropfirst="false"
            failonerror="false"/>  

Alter database model task

This task connects to an existing database and upgrades it to the structures defined in the model XML file. This assumes that the existing database has exactly the same structures as defined in the originalmodel file. To upgrade all the database structures, the task first generates a database script with the differences from the input file and target file with the syntax of the database defined in the connection attributes. After, it executes this script in the database.

If the database is empty then before creating all the structures defined in the model, the SQL script defined in the prescript attribute is launched. After all the structures are created, the SQL script defined in the postscript attribute is launched. These scripts are used to create all the structures needed by the model that are proprietary to the target database engine.

The attributes of this ant task are:

driver, url, user and password
define the properties of the JDBC connection to the database. The jar file that contains the database driver must be available in the java classpath.
excludeobjects
defines the java class name that filters the database objects read from the JDBC connection. By default, all database objects are processed.
originalmodel
defines the path to the folder that contains the database structures XML files base of the differences. If empty it reads the database model of the database.
model
defines the path to the database structures XML file to upgrade.
object
defines the list of database objects (separated by comma) to modify the rest of database objects are ignored. If empty, all the database objects defined in the model are processed.
prescript
defines the path to the SQL script to execute before creating the model. If empty then the default SQL script prescript-<database-engine-name>.sql; if one exists, it is executed.
postscript
defines the path to the SQL script execute after creating the model. If empty then the default SQL script postcript-<database-engine-name>.sql; if one exists, it is executed.
failonerror
if true and if a statement fails, the execution of the script aborts at that point.
verbosity
define the log level. The possible values are INFO, DEBUG. By default the level is INFO.

The following example upgrades a database to the model selected:

        <alterdatabase 
            driver="org.postgresql.Driver"
            url="jdbc:postgresql://localhost:5432/openbravodb"
            user="tad"
            password="tad"
            model="/home/adrian/model"
            failonerror="false"/>  

Export database model task

This task reads the model from an existing database and stores it as XML files. This task is the complementary of Create database model.

The attributes of this ant task are:

driver, url, user and password
define the properties of the JDBC connection to the database. The jar file that contains the database driver must be available in the java classpath.
excludeobjects
defines the java class name that filters the database objects read from the JDBC connection. By default, all database objects are processed.
model
defines the path to store the database structures XML files read from the database.
verbosity
define the log level. The possible values are INFO, DEBUG. By default the level is INFO.

The following example exports a database model to the selected path:

        <exportdatabase 
            driver="org.postgresql.Driver"
            url="jdbc:postgresql://localhost:5432/openbravodb"
            user="tad"
            password="tad"
            model="/home/adrian/model"/>  

Create script task

This task creates a database script with all the database structures defined in the model attribute and based on the syntax defined in the platform attribute.

The attributes of this ant task are:

platform
defines the platform of the target database and the syntax of the script created. Oracle and PostgreSql are supported.
model
defines the path to the database structures XML to create.
object
defines the list of database objects (separated by comma) to create, the rest of database objects are ignored. If empty, all the database objects defined in the model are created.
output
defines the path to the database scripts file to generate.
dropfirst
if true, the task first executes a script that drops all the structures definied in the input XML file.
verbosity
define the log level. The possible values are INFO, DEBUG. By default the level is INFO.

An example of this task is:

        <createscript 
            platform="PostgreSql"
            model="/home/adrian/model"
            output="/home/adrian/openbravo231model-postgre.sql"
            dropfirst="false"

Alter script task

This task creates a database script that upgrades the structures defined in the original model attribute up to the structures defined in the model attribute and based on the syntax defined in the platform attribute.

The attributes of this ant task are:

platform
defines the platform of the target database and the syntax of the script created. Oracle and PostgreSql are supported.
originalmodel
defines the path to the database structures base of the differences.
model
defines the path to the database structures to upgrade.
object
defines the list of database objects separated by comma to modify the rest of database objects are ignored. If empty all the database objects defined in the model are processed.
output
defines the path to the database scripts file to generate.
verbosity
define the log level. The possible values are INFO, DEBUG. By default the level is INFO.

An example of this task is:

        <alterscript 
            platform="PostgreSql"
            originalmodel="/home/adrian/originalmodel”
            model="/home/adrian/model"
            output="/home/adrian/openbravo-upgrade-postgre.sql"/>  

Import data task

This task imports all the data contained in a collection of XML files to an existing database. When importing each record the task first executes an update sentence, and if the record does not exists then it executes an insert sentence.

Before executing the import process, all the triggers and foreign keys of the database are disabled, and after the execution, they are enabled again.

Before importing the data the SQL script defined in the prescript attribute is launched. After importing, the SQL script defined in the postscript attribute is launched. These scripts are used to prepare the database data before and after the import process.

The attributes of this ant task are:

driver, url, user and password
define the properties of the JDBC connection to the database. The jar file that contains the database driver must be available in the java classpath.
excludeobjects
defines the java class name that filters the database objects read from the JDBC connection. By default, all database objects are processed.
model
defines the path to the database model expected. If empty it reads the database model of the database.
prescript
defines the path to the SQL script to execute before creating the model. If empty then the default SQL script prescript-<database-engine-name>.sql; if exists, it is executed.
postscript
defines the path to the SQL script execute after creating the model. If empty then the default SQL script postcript-<database-engine-name>.sql; if exists, it is executed.
filter
defines the class name used to filter the tables and data to import. This is used to delete all the data defined in the filter class before executing the import process. By default is empty i.e. It does not delete any data of the database and in this case it merges all the data.
input
defines the path of the folder where are the XML files that contains the data to import.
encoding
defines the character encoding of the input XML files. By default is UTF-8.
verbosity
define the log level. The possible values are INFO, DEBUG. By default the level is INFO.

An example of this task is:

        <importdata 
            driver="org.postgresql.Driver"
            url="jdbc:postgresql://localhost:5432/openbravo"
            user="tad"
            password="tad"
            input="/home/adrian/data”/> 

Export data task

This task exports all the data of an existing database to a collection of XML files.

Before exporting the data the SQL script defined in the prescript attribute is launched. After exporting, the SQL script defined in the postscript attribute is launched. These scripts are used to prepare the database data before and after the export process.

The attributes of this ant task are:

driver, url, user and password
define the properties of the JDBC connection to the database. The jar file that contains the database driver must be available in the java classpath.
excludeobjects
defines the java class name that filters the database objects read from the JDBC connection. By default, all database objects are processed.
model
defines the path to the database model expected. If empty it reads the database model of the database.
prescript
defines the path to the SQL script to execute before creating the model. If empty then the default SQL script prescript-<database-engine-name>.sql; if exists, it is executed.
postscript
defines the path to the SQL script execute after creating the model. If empty then the default SQL script postcript-<database-engine-name>.sql; if exists, it is executed.
filter
defines the class name used to filter the tables and data to export. By default it exports all the database data defined in the model.
output
defines the path to store the XML files with the database data.
encoding
defines the character encoding of the output XML files. By default is UTF-8.
verbosity
define the log level. The possible values are INFO, DEBUG. By default the level is INFO.

An example of this task that exports all the Openbravo ERP metadata of a database is:

        <exportdata 
            driver="org.postgresql.Driver"
            url="jdbc:postgresql://localhost:5432/openbravodb"
            user="tad"
            password="tad"
            filter="org.openbravo.ddlutils.task.OpenbravoMetadataFilter"
            output="/home/openbravo/metadata"/>

Database model schema

This is the definition of the database schema that DBSourceManager interprets.

Database model

database

The database element. This element contains the definition of all the database structure objects.

attributes

name
The name of the database

children

table 
any number.
sequence 
any number.
view
any number.
function
any number.
trigger
any number.

table

This element describes a database table.

attributes

name 
The name of the table.
primaryKey 
The name of the primary key constraint.

children

column 
any number.
foreign-key 
any number.
index 
any number.
unique 
any number.
check 
any number.

column

This element describes a table column.

attributes

name 
The name of this column.
primaryKey 
Whether this is a primary key column.
required 
Whether this is a required column, i.e. a column that is not allowed to contain NULL values. Note that primary key columns are not automatically required.
type 
The JDBC type of the column. This will be automatically translated to a type native to the database.
size 
The JDBC size specification of the column. This is type specific. For types that have a length (such as VARCHAR), specify a single number specifying this length. For types that have precision and scale (such as NUMERIC), specify a pair of values "precision,scale".
default 
The default value of the column if any. Do not specify this attribute unless the column shall have a default value as an empty attribute value will be passed as an empty string (not NULL) to the database. This value is passed as is to the database, though you don't need to quote it for e.g. string values. This value may also be a database function e.g. SYSDATE.
autoIncrement 
Whether this column is an identity value, i.e. it's value is automatically defined by the database
description 
The column description

foreign-key

This element describes a foreign key of this table.

attributes

name 
The name of this foreign key:
onUpdate 
Specifies the action to perform when the value in the referenced column in the foreign table is changed: cascade Change the value of the local column accordingly. setnull Set the local column to NULL which effectively removes this specific foreign key relationship. restrict Different databases may interpret this value differently, but usually it is synonymous with none. none The value of the local column remains unchanged.
onDelete 
Specifies the action to perform when the referenced row in the foreign table is deleted: cascade Delete the local row. setnull Set the local column to NULL which effectively removes this specific foreign key relationship. restrict Different databases may interpret this value differently, but usually it is synonymous with none. none The value of the local column remains unchanged.

children

reference 
any number.

reference

This element describes a foreign key reference.

attributes

local 
The name of the column in the current table.
foreign 
The name of the column in the foreign table.

index

This element describes an index of this table.

attributes

name 
The name of this index.
unique 
Whether this index is unique.

children

index-column 
any number.

index-column

This element describes an index column.

attributes

name 
The name of the column in the current table.

unique

This element describes an unique constraint of this table.

attributes

name 
The name of this unique constraint.

children

unique-column 
any number.

unique-column

This element describes an unique column.

attributes

name 
The name of the column in the current table.

check

This element describes a check constraint of the current table.

attributes

name 
The name of this check constraint.
condition 
The condition expression that this table must satisfy. Deprecated, use the element text.

text

The condition expression that this table must satisfy.

sequence

This element describes a database sequence.

attributes

name 
The name of this sequence.
start 
The number from this sequence starts.
increment 
The number that this sequence increments every request.

view

This element describes a database view.

attributes

name 
The name of this view.

text

The SQL sentence that defines the columns and the data that this view request from the database. The SQL syntax used is the Oracle SQL syntax.

function

This element describes a database stored function or procedure.

attributes

name 
The name of this function of procedure.
type 
The JDBC type that this function returns. This will be automatically translated to a type native to the database. if type is NULL it is a procedure that does not return any value.

children

parameter 
any number.
body 
The text of this element defines the PL/SQL code of this function or procedure. The PL/SQL syntax used is the Oracle PL/SQL syntax.

parameter

This element describes a function parameter.

attributes

name 
The name of this parameter.
type 
The JDBC type of this parameter. This will be automatically translated to a type native to the database.
mode 
The mode of this parameter: in for input parameters, out for output parameters.
default 
The default value of the parameter if any. Do not specify this attribute unless the parameter shall have a default value when an invocation to this function does not specify this parameter value. This value is passed as is to the database, though you don't need to quote it for e.g. string values. This value may also be a database function e.g. SYSDATE.

trigger

This element describes a database trigger.

attributes

name 
The name of this trigger.
table 
The name of the table of the current database this trigger is associated.
fires 
The event that fires the execution of this trigger: before the trigger is executed before the table associated is modified, after the trigger is executed after the table associated is modified.
insert 
Whether this trigger is executed when inserting records in the associated table.
update 
Whether this trigger is executed when updating records in the associated table.
delete 
Whether this trigger is executed when deleting records in the associated table.
foreach 
Defines how many times this trigger is executed: row the trigger is executed for each record that is modified of the associated table. statement the trigger is executed for each SQL statement that modifies the associated table.

children

body 
The text of this element defines the PL/SQL code of this trigger. The PL/SQL syntax used is the Oracle PL/SQL syntax.

Retrieved from "http://wiki.openbravo.com/wiki/Projects:DBSourceManager/Database_sources_management"

This page has been accessed 19,091 times. This page was last modified on 8 June 2012, at 05:27. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.