Apache OfBiz Cookbook

Over 60 simple but incredibly effective recipes for taking control of OFBiz Optimize your OFBiz experience and save hours of frustration with this timesaving collection of practical recipes covering a wide range of OFBiz topics. Get answers to the most commonly asked OFBiz questions in an easy-to-digest reference style of presentation. Discover insights into OFBiz design, implementation, and best practices by exploring real-life solutions. Each recipe in this Cookbook is crafted to describe not only “how” to accomplish a specific task, but also “why” the technique works to ensure you get the most out of your OFBiz implementation.

        Read more about this book      

(For more resources on Apache, see here.)

Introduction

Secure and reliable data storage is the key business driver behind any data management strategy. That OFBiz takes data management seriously and does not leave all the tedious and error-prone data management tasks to the application developer or the integrator is evident from the visionary design and implementation of the Entity Engine.

The Entity Engine is a database agnostic application development and deployment framework seamlessly integrated into the OFBiz project code. It handles all the day-to-day data management tasks necessary to securely and reliably operate an enterprise. These tasks include, but are not limited to support for:

• Simultaneously connecting to an unlimited number of databases
• Managing an unlimited number of database connection pools
• Overseeing database transactions
• Handling database error conditions

The true power of the Entity Engine is that it provides OFBiz Applications with all the tools, utilities, and an Application Programming Interface (API) necessary to easily read and write data to all configured data sources in a consistent and predictable manner without concern for database connections, the physical location of the data, or the underlying data type.

To best understand how to effectively use the Entity Engine to meet all your data storage needs, a quick review of Relational Database Management Systems (RDBMS) is in order:

• RDBMS tables are the basic organizational structure of a relational database. An OFBiz entity is a model of a database table. As a model, entities describe a table’s structure, content format, and any applicable associations a table may have with other tables.
• Database tables are further broken down into one or more columns. Table columns have data type and format characteristics constrained by the underlying RDBMS and assigned to them as part of a table’s definition. The entity model describes a mapping of table columns to entity fields.
• Physically, data is stored in tables as one or more rows. A record is a unique instance of the content within a table’s row. Users access table records by reading and writing one or more rows as mapped by an entity’s model. In OFBiz, records are called entity values.
• Keys are a special type of field. Although there are several types of keys, OFBiz is primarily concerned with primary keys and foreign keys. A table’s primary key is a column or group of columns that uniquely identifies a row within a table. The value of the primary key uniquely identifies a table’s row throughout the entire database.
• A foreign key is a key used in one table to represent the value of a primary key in a related table. Foreign keys are used to establish unique and referentially correct relationships between one or more tables.
• Relationships are any associations that tables may have with one another.
• Views are “virtual” tables composed of columns from one or more tables in the database. OFBiz has a similar construct (although it differs from the traditional RDBMS definition of a “view”) in the view-entity.

Note: while this discussion has focused on RDMS, there is nothing to preclude you from using the Entity Engine in conjunction with any other types of data source(s).

The Entity Engine provides all the tools and utilities necessary to effectively and securely access an unlimited number of databases regardless of the physical location of the data source, as shown in the following figure:

Changing the default database

Out-of-the-box, OFBiz is integrated with the Apache Derby database system (http://db.apache.org/derby). While Derby is sufficient to handle OFBiz during software development, evaluation, and functional testing, it is not recommended for environments that experience high transaction volumes. In particular, it is not recommended for use in production environments.

Getting ready

Before configuring an external database, the following few steps have to be ensured:

1. Before changing the OFBiz Entity Engine configuration to use a remote data source, you must first create the remote database; the remote database must exist.

Note: if you are not going to install the OFBiz schema and/or seed data on the remote database, but rather intend to use it as is, you will not need to create a database. You will need, however, to define entities for each remote database table you wish to access, and assign those entities to one or more entity groups.

1. Add a user/owner for the remote database. OFBiz will access the database as this user. Make sure the user has all necessary privileges to create and remove database tables.
2. Add a user/owner password (if desired or necessary) to the remote database.
3. Ensure that the IP port the database is listening on for remote connections is open and clear of any firewall obstructions (for example, by default, PostgreSQL listens for connections on port 5432).
4. Add the appropriate database driver to the ~framework/entity/lib/jdbc directory.

For example, if you are using PostgreSQL version 8.3, download the postgresql-8.3-605.jdbc2.jar driver from the PostgreSQL website (http://jdbc.postgresql.org/download.html).

How to do it…

To configure another external database, follow these few steps:

1. Open the Entity Engine’s configuration file located at:~framework/entity/config/entityengine.xml
2. Within the entityengine.xml file, configure the remote database’s usage settings. A suggested method for doing this is to take an existing datasource element entry and modify that to reflect the necessary settings for a remote database. There are examples provided for most of the commonly used databases.For example, to configure a remote PostgreSQL database with the name of myofbiz_db, with a username ofbiz and password of ofbiz, edit the localpostnew configuration entry as shown here:

   <datasource name=”localpostnew”
   helper-class=
   “org.ofbiz.entity.datasource.GenericHelperDAO”
   schema-name=”public”
   field-type-name=”postnew”
   check-on-start=”true”
   add-missing-on-start=”true”
   use-fk-initially-deferred=”false”
   alias-view-columns=”false”
   join-style=”ansi”
   result-fetch-size=”50″
   use-binary-type-for-blob=”true”>
   <read-data reader-name=”seed”/>
   <read-data reader-name=”seed-initial”/>
   <read-data reader-name=”demo”/>
   <read-data reader-name=”ext”/>
   <inline-jdbc jdbc-driver=”org.postgresql.Driver”
   jdbc-uri=”jdbc:postgresql://127.0.0.1/myofbiz_db”
   jdbc-username=”ofbiz”
   jdbc-password=”ofbiz”
   isolation-level=”ReadCommitted”
   pool-minsize=”2″
   pool-maxsize=”250″/>
   </datasource>

3. Configure the default delegator for this data source:

   <delegator name=”default” entity-model-reader=”main”
   entity-group-reader=”main” entity-eca-reader=”main”
   distributed-cache-clear-enabled=”false”>
   <group-map group-name=”org.ofbiz”
   datasource-name=”localpostnew”/>
   <group-map group-name=”org.ofbiz.olap”
   datasource-name=”localderbyolap”/>
   </delegator>

4. Save and close the entityengine.xml file.
5. From the OFBiz install directory, rebuild OFBiz by running the ant run-install command.
6. Start OFBiz.
7. Test by observing that the database was created and populated. You may use the WebTools entity reference page (https://localhost:8443/webtools/control/entityref) to search for your newly created entities, or a third-party tool designed to work with your specific database.

How it works…

The Entity Engine is configured using the entityengine.xml file. Whenever OFBiz is restarted, the Entity Engine initializes itself by first referencing this file, and then building and testing all the designated database connections. In this way, an unlimited number of data source connections, database types, and even low-level driver combinations may be applied at runtime without affecting the higher-level database access logic.

By abstracting the connection using one or more delegators, OFBiz further offloads lowlevel database connection management from the developer, and handles all connection maintenance, data mappings, and the default transaction configuration for an unlimited number of target databases.

To configure one or more database connections, add datasource element declarations with settings as shown here:

To specify that the Entity Engine should be connected to a database using a JDBC driver and to configure the specific connection parameters to pass, set the inline-jdbc element attributes as detailed here:

Connecting to a remote database

A “remote” database is any data source that is not the default Derby database. A remote database may be network connected and/or installed on the local server. The Entity Engine supports simultaneous connections to an unlimited number of remote databases in addition to, or as a replacement for, the default instance of Derby.

Each remote database connection requires a datasource element entry in the entityengine.xml file. Adding and removing database connections may be performed at any time; however, entityengine.xml file changes are only effective upon OFBiz restart.