OFBIZ ENTITY ENGINE COOKBOOK
============================

* Keep your entity names less than 25 characters

  Oracle, for example, does not like tables with names longer than 30 characters, and OFBIZ will add a "_" between words, so it's best to keep them short. 

* How relationships work

  They are defined in entitymodel.xml files for an <entity> like this:

      <relation type="one" fk-name="PROD_CTGRY_PARENT" title="PrimaryParent" rel-entity-name="ProductCategory">
        <key-map field-name="primaryParentCategoryId" rel-field-name="productCategoryId"/>
      </relation>
      <relation type="many" title="PrimaryChild" rel-entity-name="ProductCategory">
        <key-map field-name="productCategoryId" rel-field-name="primaryParentCategoryId"/>
      </relation>

  type= defines the type of the relationship: "one" for one-to-one or "many" for one-to-many from this entity
  fk-name= is a foreign key name.  It is usually good practice to set your own foreign key name, although OFBIZ will auto-generate if it's not there.
  rel-entity-name= is the name of the related entity.
  title= is used to distinguish different relationships when there are many relationships between the same two entities.

  <key-map> defines the fields for the relationship. field-name= is for the field of this entity to use.  If the field on the related entity
  has a different field name, then rel-field-name= defines the field name in the related entity.  You can have many fields serve as part of
  a key-map. 

When you access a relationship, you would use .getRelated("") or .getRelatedOne("") with the title+entityName as parameter.  getRelated returns
a List and is appropriate when it is a "many" relation.  .getRelatedOne returns a single GenericVaue and is appropriate for "one" relations.

* A few things about view-entities

View-entities are very powerful and allow you to create join-like queries, even when your database doesn't support joins.

The configuration of your database's join syntax is in entityengine.xml under the join-style attribute of <datasource ..>

When you link two entities together using the <view-link ..> tag, remember
1.  The order of the entities you name is important.
2.  The default is an inner join (values must be present on both sides.)  To do an outer join, use rel-optional="true"

If several entities have the same field name, such as statusId, the one from the first entity will be used and the rest
tossed out.  So if you need the field from one, put its <alias-all> before the others.  Alternatively, use <alias ..> tag
to give the same field from different entities names, such as:
<alias entity="EntityOne" name="entityOneStatusId" field="statusId"/>
<alias entity="EntityTwo" name="entityTwoStatusId" field="statusId"/>

Another alternative is to use <exclude field=""> inside an <alias-all> as follows:
<alias-all entity-alias="EN">
  <exclude field="fieldNameToExclude1"/>
  <exclude field="fieldNameToExclude2"/>
</alias-all>
This top-down approach is more compact, which comes in handy for large tables.

Alternatively, you can specify one or more <exclude field="fieldName"> inside an <alias-all>.
 This top-down approach is more compact than specifying the alias for every field in a big
table.

If you need to do a query like this
SELECT count(visitId) FROM ... GROUP BY trackingCodeId WHERE fromDate > '2005-01-01'
include field visitId with function="count", trackingCodeId with group-by="true", and fromDate with group-by="false"

Then **VERY IMPORTANT** when you do your query, such as delegator.findByCondition, you must specify the fields to select,
and you must not specify the field fromDate, or you will get an error.  This is why these view-entities can't be viewed
from webtools.

For an example, look at the view entities at the bottom of applications/marketing/entitydef/entitymodel.xml and 
the BSH scripts in applications/marketing/webapp/marketing/WEB-INF/actions/reports.

* Do I have to define my view-entities in an entitymodel.xml file?

No, you can also define them dynamically.  A good example is the findParty method in org.ofbiz.party.party.PartyServices

* How to build conditions for expiration dates

There is a series of very helpful methods called EntityUtil.getFilterByDateExpr which return an EntityConditionList
to filter out search results by date.  

* How to work with large sets of data

If you need to select large sets of data, you should use the EntityListIterator instead of the List.  For example, if you did

List products = delegator.findAll("Product");

You may get yourself a "java.lang.OutOfMemoryError".  This is because findAll, findByAnd, findByCondition will try to retrieve all the records into
memory as a List.  In that case, re-write your query to do return an EntityListIterator then loop through it.  For example, this query can
be re-written as:

productsELI = delegator.findListIteratorByCondition("Product", new EntityExpr("productId", EntityOperator.NOT_EQUAL, null), UtilMisc.toList("productId"), null);

Note that the only method for finding an EntityListIterator is a by condition, so you would have to re-write your conditions as EntityExpr (in this case, a dummy
one which just says that productId is not null, which should apply to all Product entities, since productId is a not-null primary key field) or EntityConditionList.
This method also asks you to fill in the fields to select (in this case just productId) and order by (which I didn't specify with the last null.) 

You can pass a null EntityCondition to grab all records.  However, this does not work across all databases!  Beware the use of avanced functionality such as the EntityListIterator with maxdb and other odd databases.

* How to use an EntityListIterator 

When iterating through the EntityListIterator, this is the preferred form:

while ((nextProduct = productsELI.next()) != null) {
....
    // operations on nextProduct
}

Using the .hasNext() method on EntityListIterator is considered wasteful.

When you are done, don't forget to close it:

productsELI.close();

* How to do a select distinct

The only way we know of to do it with the entity engine is to find a list iterator and use EntityFindOptions to specify it, like this:
    listIt = delegator.findListIteratorByCondition(entityName, findConditions, 
            null, // havingEntityConditions
            fieldsToSelectList, 
            fieldsToOrderByList,  
            
            // This is the key part.  The first true here is for "specifyTypeAndConcur"
            // the second true is for a distinct select.  Apparently this is the only way the entity engine can do a distinct query
            new EntityFindOptions(true, EntityFindOptions.TYPE_SCROLL_INSENSITIVE, EntityFindOptions.CONCUR_READ_ONLY, true));

In minilang, curiously, it is much easier:
  <entity-condition entity-name="${entityName}" list-name="${resultList}" distinct="true">
     <select field="${fieldName}"/>
     ....

* How to do a case-insensitive search

You would need to make the find expressions convert both sides to uppercase, like this:
andExprs.add(new EntityExpr("lastName", true, EntityOperator.LIKE, "%"+lastName+"%", true));

(from org.ofbiz.party.party.PartyServices)

* How to go from EntityListIterator to just List

  Use the EntityListIterator.getCompleteList() and getPartialList methods

* How to get the next ID value automatically

  Use either <sequence-id-to-env ...> in minilang or delegator.getNextSeqId(...) in Java.  The id sequence numbers are stored in an
  entity SequenceValueItem.

* A word of warning about ID values

  DO NOT CREATE SEED OR DEMO DATA WITH ID 10000!  When the system tries to create more values automatically, it will also try to use 10000,
  resulting in a key clash.

* How to get a sequence ID of an item
  
  Some entities, like InvoiceItem and OrderItem, have an itemSeqId as well.  This can also be generated automatically for you by first making
  a GenericValue of the item, then asking the delegator to generate the item seq ID:

  GenericValue orderItem = delegator.makeValue("OrderItem", orderItemValues);
  delegator.setNextSubSeqId(orderItem, "orderItemSeqId", ORDER_ITEM_PADDING, 1);

* How to set up an alias that does the equivalent of SELECT SUM(QUANTITY - CANCEL_QUANTITY) AS QUANTITY

  <alias entity-alias="OI" name="quantity" function="sum">
      <complex-alias operator="-">
          <complex-alias-field entity-alias="OI" field="quantity" default-value="0"/>
          <complex-alias-field entity-alias="OI" field="cancelQuantity" default-value="0"/>
      </complex-alias>
  </alias> 

  This results in SELECT SUM(COALESCE(OI.QUANTITY,'0') - COALESCE(0I.CANCEL_QUANTITY)) AS QUANTITY
  Including a default-value is a good habit, otherwise the result of the subtraction will be null if
  any of its fields are null.

  The operator can be any of the supported SQL operations of your database, such as arithmetic with
  +, -, * and / or concatenation of strings with ||.

  You can add a function="" to perform min, max, sum, avg, count, count-distinct, upper and lower
  on the complex-alias-field.  For example, an alternative way to express the query above is to do:

  <alias entity-alias="OI" name="quantity">
      <complex-alias operator="-">
          <complex-alias-field entity-alias="OI" field="quantity" default-value="0" function="sum"/>
          <complex-alias-field entity-alias="OI" field="cancelQuantity" default-value="0" function="sum"/>
      </complex-alias>
  </alias>

  Which results in SELECT (SUM(COALESCE(OI.QUANTITY,'0')) - SUM(COALESCE(OI.CANCEL_QUANTITY,'0'))) AS QUANTITY

* I hate the @#!$^_ OFBIZ Entity Engine.  Give me a JDBC connection!

  Ok, here's how you can get and use a JDBC connection:

import org.ofbiz.entity.jdbc.ConnectionFactory;

String helperName = delegator.getGroupHelperName("org.ofbiz");    // gets the helper (localderby, localmysql, localpostgres, etc.) for your entity group org.ofbiz
Connection conn = ConnectionFactory.getConnection(helperName); 
Statement statement = conn.createStatement();
statement.execute("SELECT * FROM PARTY");
ResultSet results = statement.getResultSet();

// ... work with it as normal JDBC results

Alternatively, you can use the SQLProcessor like this:

SQLProcessor sqlproc = new SQLProcessor(helperName);
sqlproc.prepareStatement("SELECT * FROM PARTY");
ResultSet rs1 = sqlproc.executeQuery();

ResultSet rs2 = sqlproc.executeQuery("SELECT * FROM PRODUCT");

For an example, see framework/webtools/webapp/webtools/WEB-INF/actions/entity/EntitySQLProcessor.bsh

For javadoc please see:
http://www.opentaps.org/javadocs/version-1.0/framework/api/org/ofbiz/entity/jdbc/SQLProcessor.html
http://www.opentaps.org/javadocs/version-1.0/framework/api/org/ofbiz/entity/jdbc/ConnectionFactory.html

  *** THINK ABOUT THIS FIRST: You're giving up database independence and in some ways cutting yourself off 
from the rest of the framework and the other applications.  Are you SURE you want to do this?

For a better way to do this, see http://www.opentaps.org/docs/index.php/Using_the_Query_Tool

* WARNING ABOUT COMPARING TIMESTAMPS

It seems that when you do a GREATER_THAN operation on a Timestamp, you will get the same Timestamp:

delegator.findByAnd("XXX", UtilMisc.toList(new EntityExpr("fromDate", EntityOperator.GREATER_THAN, "2007-12-31 23:59:59.998")));

will also get records with fromDate of 2007-12-31 23:59:59.998.  (This happened on PostgreSQL 8.1 and the GenericDAO class was generating 'FROM_DATE > ' so I'm not sure why
this happens.)  To be safe, add 1 second to the timestamp and use GREATER_THAN_EQUAL_TO

delegator.findByAnd("XXX", UtilMisc.toList(new EntityExpr("fromDate", EntityOperator.GREATER_THAN_EQUAL_TO, "2008-01-01 00:00:00.998")));

* WARNING: EntityOperator.IN on empty Lists

Be careful that your lists are not empty for EntityOperator.IN, or you might get a Syntax Error: FALSE from Derby and possibly other cryptic messages from other databases.
Always perform a UtilValidate.isNotEmpty on a retrieved List before using it in EntityOperator.IN

* WARNING: delegator.getNextSubSeqId does not guarantee uniqueness

There are many entities with composite or cyclic primary keys.  For example, OrderItem's primary key fields are orderId and orderItemSeqId.  InventoryItemDetail's primary key
fields are inventoryItemId and inventoryItemSeqId.  Usually, delegator.getNextSubSeqId is used to get the seq-id value, BUT IT MAY NOT GUARANTEE UNIQUENESS AGAINST MULTI-THREADED
ACCESS.  This issue is documented in http://issues.apache.org/jira/browse/OFBIZ-1636.

For now, if it is possible that several threads might try to write to an entity with composite keys, then use delegator.getNextSeqId instead of getNextSubSeqId.  (This is not the 
case with OrderItem, where an order is typically written by just one thread, but it may happen with InventoryItemDetail, where multiple threads could create orders that try to 
reserve inventory.)
 
 
applies to on