Just a quick note about how to debug and compile Pentaho PDI

1. Get the source from svn e.g. to get the branch 3.1.0 use the following command
   svn co svn://source.pentaho.org/svnkettleroot/Kettle/branches/3.1.0 Kettle3.1.0
2. Make sure you have ant, I have 1.7.x installed and in the path
3. Load the project as an existing Java project in Eclipse, modify source at your will
   
4. Look at the build.xml file at the top level directory where you checked out the source
   e.g. Kettle3.1.0
5. Run the default target or if you changed source file in one of the sub packages such as core or ui etc., there are separate targets to build just those lib

Pentaho PDI - Running multiple SQL statements

As you may already know, you can use Exec SQL step to execute multiple SQL statements either once during the entire transformation run or once per row.

But...

I was bitten by this issue and ended up wasting couple of hours trying to debug the cause. The reason... Error is really amgious, if you have multiple "syntactically correct" SQL statements separated by commas and try to execute it with default settings on the Connector/J and MySQL as DB you get
"You have an error in your SQL statement, Check the syntax..." error.
Checked my SQL statements several times and individually in SQL Query browser to make sure they were correct.

There are a couple of things you should note
1. In the debug log you will see an old log saying executing DDL statement when it actually can execute both DDL and DML statements. So you will need to ignore that log
2. Not all the drivers/databases allow running multiple comma separated SQL statements due to SQL Injection attach. Check the driver/db to make sure it allows execution of multiple SQL statements separated by commas in one shot.

For MySQL and Connector/J by default does not allow this, so to enable it you will have to add an option in your Database Connections dialog -> Options tab and add the parameter allowMultiQueries with the value of true.

3. Also make sure that you surround ?'s in your SQL statements with single quotes if you are passing in string values. If you are like me, you tend to think of the statement as your typical prepared statement in JDBC and using JDBC api to set the values so you dont need to worry about wrapping strings with quotes.

Hope that helps.

Using Ofbiz and Wicket - Part I

This article is the first article and probably more to follow on how to use Wicket with Ofbiz.

Both Wicket and Ofbiz are from ASF (Apache Software Foundation) but unfortunately not many ppl seem to have used them together.

Primary reason for me to use them together was complexity and amount of configuration required to use existing Ofbiz frontend frameworks such as screen widget and learning additional technologies such as freemarker, beanshell etc. That lead me to look for a framework which would replace that and provide more - hence Apache Wicket.

Apache Wicket falls into already crowded MVC UI Frameworks consisting of struts, seam amongst others. You can get an entire list from Wicket website :). Yes, it has AJAX support builtin in the framework and yes it has rich set of components both AJAX and otherwise as Wicket extensions and at WicketStuff.org with integration with popular Javascript AJAX libraries DOJO, YUI and JQuery and few others.

Now that we have some background, lets see what you need to get started and use Wicket "inside" Apache Ofbiz.

• Ofbiz comes with embedded Tomcat at the time of writing Ofbiz 4.0 came with Tomcat 5.5.23 and fortunately Wicket can be deployed in any standard web container
  
• Download wicket binaries from Apache Wicket site, copy them over to your Ofbiz application lib directory where you have your application and other 3rd party jars. I chose to put it under hot-deploy/myapp/webapp/myapp/WEB-INF/lib
• Add WicketFilter entry to your web.xml file (Note: Both WicketFilter and Ofbiz ContextFilter will be listening on /* requests - so initially there will be chaining of those filters, but for this first article I will ignore that) - Make sure to put WicketFilter entry before ContextFilter so WicketFilter gets called first
  
• Write your first Hello World in Wicket by creating your WebApplication and WebPage classes - access the URL (http://localhost:8080/yourappcontext) and see it in action, where yourappcontext is whatever you configured it to be in your ofbiz-component.xml file

In the following articles I will try to cover things like using Ofbiz Entity Engine, Service Engine from wicket and may be how to translate Ofbiz ecommerce application using entirely Wicket. On the way will try to quickly cover some cool UI components both SEO friendly and otherwise.

How to resolve channel 1: open failed: administratively prohibited: open failed

There are a few reasons why you could get

channel 1: open failed: administratively prohibited: open failed

But essentially it means the port you are trying to tunnel into on the remote machine cannot be opened

To debug the issue

• If you have access to the remote machine check /var/log/secure to get more information on error
• You could try ssh tunnel in verbose mode with -v option
• Try with a different port number on the remote host just to test - some ports are reserved

To resolve the issue - Depending on what the error was from debugging you might have to

• If the error in the secure log was cannot resolve host name - it means you need to restart your network service or interfaces - it could be due to dns server issues
• restart sshd on the remote server - make sure port forwarding is enabled in your ssh config - there are plenty of resources on the net that will explain this
  

How to package Wicket jars quickly

Wicket is buildable only using maven. Using maven install is not bad if you have to just build Wicket once, but when you have to build it multiple times, its takes its toll :) - on my 2.4GHz, 4Gb RAM dell laptop it takes atleast 8-12 mins - thats painful.

If you are not Maven guru and would like to get this done quickly - here is what I tried and it worked. I could not find any documentation that shows exactly these steps so...

Depending on the wicket jar that you want to build - cd to that subdirectory under wicket and use

mvn -Dmaven.test.skip=true compile jar:jar install:install

or any combination of the options above which are self explanatory, but I will explain them anyway, just in case-
compile - compile phase of maven which will compile all
jar:jar - is the jar goal of package phase if you specify the package instead of jar:jar it will also try to generate javadocs which will take forever
install:install - deploy phase goal

Hope that helped - would save atleast 30-45 mins if you didnt know maven

Main entities and their relationships

Following are the main entities to get the basic e-commerce store up and running and their relationships

• Website -> Store -> Catalog -> Categories
• Store -> Payment, Email, Facilities
• Categories -> Product

You will find most of the convenience methods to extract information out for these entities (for e-commerce) are in *Worker files. e.g. CategoryWorker has all category convenience methods, CatalogWorker for catalog and so on.

Not so obvious about Ofbiz Categories and Catalog

Assumption:You should already be familiar with Ofbiz Category/Catalog data model. If you are not, please refer to the database tables and understand the relationship before proceeding further.

PRODUCT_CATEGORY should contain all the categories that you ever need.
PROD_CATALOG_CATEGORY contains a subset of the categories from the above table. Why the subset you ask? ... because PROD_CATALOG_CATEGORY table contains categories which are directly linked to a given catalog. Under normal circumstances, you would need a handful of categories (which are of type identified by the type in PROD_CATALOG_CATEGORY_TYPE). e.g. Browse Root, Quick Add, Search and Promotions and may be Whats new and Most popular. Most categories in the PRODUCT_CATEGORY table should be direct or indirect child of Browse Root category and should be of type CATALOG_CATEGORY. Only a handful of other categories (e.g. Quick Add, Search, Promotions and any other custom ones that you define) are standalone categories.

• Quick Add category is used to group all the products which can be added together in one shot
• All child (immediate or otherwise) categories of Browse Root categories are used for Navigation on ecommerce site (e.g. left nav on the Ofbiz ecommerce demo).
• Promotion category is used to show promotional products
• Search categories are used for product searches.

Refer to Ofbiz ecommerce demo and demo data under ecommerce data/demo*.xml that you installed to see the relationship and how its used/displayed

How to Hot Deploy your application in Ofbiz

Ofbiz uses embedded tomcat container as a web/application server. Ofbiz comes with hot-deploy directory exactly for that purpose - to be able to hot-deploy your application. This comes in handy especially during development, since Ofbiz startup takes a while, restarting several times during the development can be painful.

hot-deploy in Ofbiz doesnt work out of the box, there are a couple of minor config changes that you need to do before you can use it.

1. Copy you Ofbiz application (web application) under hot-deploy directory. Make sure all the directory structure of your ofbiz application is correct.
2. Create (or copy from other ofbiz applications) component-load.xml file and add your applications load-component element tag
3. In your ofbiz_home/framework/base/config/ofbiz-containers.xml file change the property setting app-contact-reloadable to true e.g. <property name="apps-context-reloadable" value="false"/>
4. Restart ofbiz for this change to take effect.
5. To test if its working, modify one of the java files of your application and recompile it. You will see messages on your console and/or log file indicating the web app context being reloaded.
   Important: I use build.xml in my application to copy my application jar file under my applications ofbiz_home/hot-deploy/myapp/webapp/myapp/WEB-INF/lib directory. I have not tried choosing a different deploy location so not sure if it will work.
   

Setting up product database and seed data for Ofbiz

This post follows the main document on Ofbiz docs site and contains information that are not included in that document or information that is "in between lines".
Ofbiz Setup Guide

1. Make sure the default delegator in framework/entity/config/entityengine.xml is pointing to the correct database
2. For production db setup from the ofbiz home run ant run-install-seed , this will install only the seed data which is the bare minimum data required for Ofbiz to come up in the most basic meaningful way and then you can enter your custom data
3. Create your business specific data by looking at Demo*.xml files in ecommerce, accounting and other applications that comes with Ofbiz. In order to run and customize e-commerce store, you will need at least Accounting/data/DemoOrgnization e-commerce/data/DemoProduct.xml.
4. Pay close attention to the "id" fields in the data file, these are the primary keys for your data. In order to avoid conflicts with existing data choose alpha numeric keys which begin with a string which identifies your company or business.
5. Once these data files are created, they can be imported through the WebTools/Import functionality. If the files are successfully imported, you will see message towards teh bottom indicating how many entities were successfully imported. Note: import process is atomic and is part of 1 transaction - so if you have lot of data and if for any reason import fails, none of the data from that import will be saved. So you can import the data in its entirity.
   

Using ZK with Ofbiz/Opentaps

1. Download ZK from zkoss.org, unzip and copy the lib directory and its subdirectories anywhere inside your Ofbiz component. e.g. you could put it under OFBIZ_HOME/applications/yourapp/webapp/yourapp/WEB-INF/lib. ZK directories for 3.0.2 are lib, lib/ext.
2. Add classpath entries for these libraries in ofbiz-component.xml
3. Add appropriate entries for ZK in web.xml of your component. See (http://www.zkoss.org/doc/quickstart/ch07.html) for details
4. Create Hello World zul/zhtml page and put it in your webapp directory
5. Change web.xml for your component, if it has ContextFilter entry, add /zkau to the allowed path variable otherwise both javascript and css wont be loaded when you view the page

Key things to note:

Disclaimer: All the statements here are to the best of my knowledge, there may be other bigger and better ways to do things so do your own readings and research.

• How to add custom stylesheets to zk pages?There are 3 ways to add stylesheets

1. If you writing your own tags in the zhtml or zul page, put tag with reference to the stylesheet
2. In ZUL pages only, you can add PI
3. If you want to add site wide stylesheet (theme), you can add it in APPROOT/WEB-INF/zk.xml file containing the following tags
<zk>
<desktop-configgt;
<theme-urigt;/static/css/style.csslt;/theme-urigt;
</desktop-configgt;
</zk>

• How to add meta tags to zk pages

Meta tags are important from SEO and other perspective. Meta tags can be added using the PI in ZUL pages

• Included zhml pages at runtime complain about undefined standard entities like nbsp, copyright etc.

To fix this, add to each of the included pages.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

Ofbiz - Opentaps cheatsheet

1. Download Ofbiz 2. Setup database (http://www.opentaps.org/index.php?option=com_content&task=view&id=37&Itemid=62) 3. run ant run-install (to install both demo and seed data). In the production env do not install demo data. Just seed data should be fine Writing your first entity def and service def IMPORTANT!! All the services property declared are loaded and available to run by all the means service can be run. In other words services are globally accessible. The way to run them is described below. Also all the available services can be seen from webtools->services tab in opentaps. 1. Look at the documentation on ofbiz wiki about Entity Engine guide and Service Engine guide, also see tutorials from Si Chen 2. Once you have ofbiz component either existing or newly created - make sure entitydef and servicedef directories have related files (entity_group.xml and entitymodel_*.xml in entitydef directory and *services.xml in servicedef directory. Also make sure the entries for these 3 files exist in ofbiz_component.xml otherwise they wont be picked up automatically. 3. Once you define your service and entity defs, you can see them listed under webtools. If the entity names in your entitymodel_*.xml conflicts with the existing ones (duplicate names) whichever component is loaded last will overwrite the entity definition. You can check the definition from webtools. 4. To run the service a. From webtools - click Service Reference link - find your service and on the top left click run service. It will ask you for input params corresponding to the ones declared in services.xml file in servicedef directory and upon successful execution will show the return arguments. b. From beanshell - easiest way would be to use http interface of the beanshell if the port is not blocked by the firewall - the default port is 9989 - URL is http://localhost:9989/remote/jconsole.html. Download and run bshcontainer.bsh (http://www.opensourcestrategies.com/ofbiz/hello_world3.php) script so you have access to delegator, dispatcher and other ofbiz specific classes. Then call delegator.runSync("service_name", params) c. From telnet session to beanshell - same as described in b except step b uses http interface d. From any of the existing bsh scripts. Shipping configuration Modify shipment.properties file under product/config - add all the carrier specific information Make sure you link your Product Store with the facility. Also make sure Facility has Postal Address which is tagged as atleast ship from address. Note: it is strange but the product doesnt have to be linked to the facility for this product store, even though there is facility tab for product!! To configure UPS rate service to get the shipping charges, in your product store you need to add a shipping method (carrier method) with the service name field on the page set to upsRateEstimate. You can also add various ShippingEstimate values, the final shipping charge will be calculated based on the shipping rate using the UPS rate service and the formula displayed on the shipping estimate tab of product store. Payment setup: 1. For CC and Paypal payments modify applications/accounting/config/payment.properties file with correct settings 2. From the catalog manager, Setup paypal payment as external service and setup authorize.net capture and authorize services with the service names specified in the production setup guide Data Import: 1. Take a look at DemoProduct.xml file for product, category, facility, product store and website creation data. 2. Since the Id fields for PK are varchar - to avoid potential conflict with existing data, try using alpha numeric ids in the seed data or use delegator.getNextSeqId() with the sequence name. Create new or use existing sequence from SEQUENCE_VALUE_ITEM table. Word about sequence ids... For each sequence name, a bank of 10 sequence ids are created, once those are exhausted another set of 10 are created. Server restart will cause these "banked" ids to be lost and next set of bank will be created. To override default sequence id creation - take a look at SequenceUtil.java, GenericDelegator.java and other related classes. 3. If the products to be imported has SKU, ISBN, Mfg Ids use GoodIdentificationTypeIds - look at GoodIdentification and GoodIdentificationType tables. Tax payment setup: For CA tax Generic Delegator Important notes 1. Generic Delegators are defined and created from the entityengine.xml file. There is a default delegator defined out of the box. 2. delegator.makeValue() is used when you absolutely know that all the fields passed belong to the entity vs. makeValidValue() is forgiving and will only take the values corresponding to the fields of the entity and ignore the rest