Projects:DBSourceManager/Database sources management

Contents 1 Introduction 2 Tasks definition 3 ANT tasks 3.1 Create database model task 3.2 Alter database model task 3.3 Export database model task 3.4 Create script task 3.5 Alter script task 3.6 Import data task 3.7 Export data task 4 Database model schema 4.1 database 4.2 table 4.3 column 4.4 foreign-key 4.5 reference 4.6 index 4.7 index-column 4.8 unique 4.9 unique-column 4.10 check 4.11 sequence 4.12 view 4.13 function 4.14 parameter 4.15 trigger

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

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.

Categories: Templates | Development ERP