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
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.