MySQL blocked because of many connection errors

Suddenly Tomcat was sending back errors to the clients, restarting fixed it but only for a while, inspecting the logs the cause was apparently not enough connections available:

"Host 'myhost.com' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"

Issuing the command would be the next measure and even applying brute force a script doing this every so often. Wait a minute we know better. There must be an (intentionally or not) attack.

The cluprit

Monit was configured to monitor mysql like:

if failed host 192.168.0.161 port 3306 then restart

This is a problem as all it does is opening a socket and then dropping the connection without any SQL handshaking.

Solution

Specifying mysql protocol could solve the problem but mysql is so mature and stable that it would be enough to check just the socket file:

if failed unixsocket /var/lib/mysql/mysql.sock with timeout 5 seconds then restart

SFTP with OpenSSH User Setup made easy

I have explained before how to get SFTP working using OpenSSH.

Let me go an extra mile now and share a simple bash script that creates a user, assigns a password, sets a maximum number of files (and allowed size) and allows *just* SFTP access. Here is how you do so from a single command line (I tested this time in Ubuntu / Debian):

sudo  /path/to/addSftpUser.sh 'testUser' 'testPassword'

Here is the script code:

#!/bin/bash
#
# @fileName: addSftpUser.sh:
# @description: Creates an SFTP user
# @author: Nestor Urquiza
# @date: Dec 22, 2010
#

#
# Constants
#
ALLOWED_KB=100000
ALLOWED_FILES=1000

#
# Functions
#
function usage {
  echo "Usage - $0 user password"
  exit 1
}

#
# Main program
#
if [ $# -lt 2 ]
then
        usage
fi
USER=$1
PASSWORD=$2
useradd -d /home/$USER -s /bin/false -m $USER
usermod -g sftponly $USER
sudo usermod -p `mkpasswd $PASSWORD` $USER
chown root:root /home/$USER
chmod 755 /home/$USER
mkdir /home/$USER/$USER
chown $USER:$USER /home/$USER/$USER
chmod 755 /home/$USER/$USER
#Quotas: Feel free to remove if you do not need to limit uploads
setquota -u $USER $ALLOWED_KB $ALLOWED_KB $ALLOWED_FILES $ALLOWED_FILES -a /

You must be sure the user cannot SSH into the box:

$ ssh testUser@192.168.3.161
testUser@192.168.3.161's password: 
This service allows sftp connections only.
Connection to 192.168.0.161 closed.
$ 

You want to be sure the user can use SFTP

$ sftp testUser@192.168.3.161
Connecting to 192.168.3.161...
testUser@192.168.3.161's password: 
sftp> exit

Rendering CSV as ERT from BHUB with Spring

As I showed for Excel we can create a custom View to render tabular data in CSV format instead.

Note that this example uses Spring and a ControllerContext class that I use to pass information about different layers (Context-Object pattern). You can of course get the fundamental idea in case you do not use a ControllerContext in your design.

The unique dependency:

<dependency>
            <groupId>net.sf.opencsv</groupId>
            <artifactId>opencsv</artifactId>
            <version>2.0</version>
</dependency>

The Controller is very similar to the one I presented for the ExcelView post. Just a little modification so actually we can render either Excel or CSV:

package com.nestorurquiza.spring.web;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.nestorurquiza.spring.web.ExcelView;
import com.nestorurquiza.spring.web.CsvView;

@Controller
public class TabularController extends RootController {

    @RequestMapping("/board")
    public ModelAndView welcomeHandler(HttpServletRequest request,
            HttpServletResponse response) {
        //Initialize the context (mandatory)
        ControllerContext ctx = new ControllerContext(request, response);
        init(ctx);
        
        DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
        Map<String, List<Map<String, Object>>> excelWorkbookViewMap = new HashMap<String, List<Map<String, Object>>>();
        List<Map<String, Object>> excelRows = new ArrayList<Map<String, Object>>();
        try {
            Map<String, Object> excelRow = new HashMap<String, Object>(); 
            excelRow.put("Name", "Gregory");
            excelRow.put("Age", 33);
            excelRow.put("Salary", 33000.55);
            excelRow.put("DOB", df.parse("2/1/1980"));
            excelRow.put("Graduated", false);
            excelRow.put("Comments", "He is our \"report designer\"");
            excelRows.add(excelRow);
            excelRow = new HashMap<String, Object>(); 
            excelRow.put("Name", "Mark");
            excelRow.put("Age", 41);
            excelRow.put("Salary", 33000.55);
            excelRow.put("DOB", df.parse("20/12/1975"));
            excelRow.put("Graduated", true);
            excelRow.put("Comments", "He is our \"web designer\"");
            excelRows.add(excelRow);
            excelWorkbookViewMap.put("First Sheet", excelRows);
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 
        String ert = ctx.getParameter("ert");
        String baseFilename = "board";
        if("csv".equals(ert)) {
            Map<String, List<Map<String, Object>>> csvViewMap = new HashMap<String, List<Map<String, Object>>>();
            String fileName = baseFilename + ".csv";
            csvViewMap.put(fileName, excelRows);
            return new ModelAndView(new CsvView(ctx, fileName, ','), csvViewMap);
        } else {
            String fileName = baseFilename + ".xls";
            return new ModelAndView(new ExcelView(ctx, fileName), excelWorkbookViewMap);
        }
    }
}

The CsvView:

package com.nestorurquiza.spring.web;

import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.web.servlet.view.AbstractView;

import au.com.bytecode.opencsv.CSVWriter;

import com.nestorurquiza.utils.Utils;
import com.nestorurquiza.web.ControllerContext;


public class CsvView extends AbstractView {
    private static final String CONTENT_TYPE = "text/csv";

    public CsvView(ControllerContext ctx, String fileName, char fieldSeparator) {
        super();
        if(Utils.isEmpty(fileName)) {
            fileName = "fileName";
        }
        this.fileName = fileName;
        this.fieldSeparator = fieldSeparator;
        this.ctx = ctx;
        setContentType(CONTENT_TYPE);
    }
    
    private String fileName;
    private char fieldSeparator;
    private ControllerContext ctx; 

    /**
     * 
     * model: Map<String, List<Map, Object>>
     * This view returns back a CSV stream
     * The model must come with an entry for fileName key
     * Each list entry (the list map) corresponds to a row
     * The headers for each row are the list map keys
     * The comma separated values are the list map values
     * 
     * @author nestor
     *
     */
    protected void renderMergedOutputModel(Map model, HttpServletRequest request, HttpServletResponse response) throws Exception {
        response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
        CSVWriter writer = new CSVWriter(response.getWriter(), fieldSeparator);

        if (Utils.isEmpty(model)) {
            writer.writeNext("error.empty.model".split(""));
        } else {
            Set<Map.Entry<String, List<Map<String, Object>>>> set = model.entrySet();
            for (Map.Entry<String, List<Map<String, Object>>> entry : set) {
                String key = entry.getKey();
                if (fileName.equals(key)) {
                    List<Map<String, Object>> content = entry.getValue();
                    int rowCount = 0;
                    for (Map<String, Object> row : content) {
                        if (rowCount == 0) {
                            String[] tokens = new String[row.size()];
                            int i = 0;
                            for( String cellName : row.keySet() ) {
                                tokens[i] = cellName;
                                i++;
                            }
                            writer.writeNext(tokens);
                        }
                        String[] tokens = new String[row.size()];
                        int i = 0;
                        for( String cellName : row.keySet() ) {
                            Object cellValue = row.get(cellName);
                            tokens[i] = cellValue.toString();
                            i++;
                        }
                        writer.writeNext(tokens);
                        rowCount++;
                    }

                }
            }
        }
        writer.flush();
    }
}

Rendering Excel as ERT from BHUB with Spring POI

Why Excel is not suitable as expected response type (ERT) for all possible BHUB methods is obviously a consequence of the fact that websites render hierarchical data and not just tabular data.

When Excel is needed to present some tabular data like for example when too many columns are to be presented then I provide a specific controller to manage the rendering. One could argue that with so many DHTML data grids components it should not be a big deal to still use HTML instead of Excel and I agree that is the case especially when a front end developer is on board. Still even the best grid component out there will not allow for real post processing, multiple sheets, formulas: Excel sometimes is simply "the tool".

Spring has a View that wraps the POI API. The only extra dependency to include is shown below:

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.6</version>
</dependency>

Here is a Spring Controller using a custom ExcelView. Note that the custom View will acccept simple Map List to contain cells, rows and sheets:

package com.nestorurquiza.spring.web;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.nestorurquiza.spring.web.ExcelView;


@Controller
public class ExcelController extends RootController {

    @RequestMapping("/excel/sample")
    public ModelAndView welcomeHandler(HttpServletRequest request,
            HttpServletResponse response) {
        //Initialize the context (mandatory)
        ControllerContext ctx = new ControllerContext(request, response);
        init(ctx);
        
        DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
        Map<String, List<Map<String, Object>>> excelWorkbookViewMap = new HashMap<String, List<Map<String, Object>>>();
        try {
            List<Map<String, Object>> excelRows = new ArrayList<Map<String, Object>>();
            Map<String, Object> excelRow = new HashMap<String, Object>(); 
            excelRow.put("Name", "Gregory");
            excelRow.put("Age", 33);
            excelRow.put("Salary", 33000.55);
            excelRow.put("DOB", df.parse("2/1/1980"));
            excelRow.put("Graduated", false);
            excelRows.add(excelRow);
            excelRow = new HashMap<String, Object>(); 
            excelRow.put("Name", "Mark");
            excelRow.put("Age", 41);
            excelRow.put("Salary", 33000.55);
            excelRow.put("DOB", df.parse("20/12/1975"));
            excelRow.put("Graduated", true);
            excelRows.add(excelRow);
            excelWorkbookViewMap.put("First Sheet", excelRows);
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 
        return new ModelAndView(new ExcelView(ctx, "sample.xls"), excelWorkbookViewMap);
    }
}

Finally the custom ExcelView:

package com.nestorurquiza.web;

import java.util.Date;
import java.util.Map;
import java.util.List;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.nestorurquiza.utils.Utils;
import com.nestorurquiza.web.ControllerContext;


public class ExcelView extends AbstractExcelView{
    public ExcelView(ControllerContext ctx, String fileName) {
        super();
        this.fileName = fileName;
        this.ctx = ctx;
    }

    private String fileName;
    private ControllerContext ctx; 
 
    /**
     * 
     * model: Map<String, List<Map, Object>>
     * This view returns back an Excel stream
     * The sheets are determined by the amount of parent model map keys
     * The content of the sheets are determined by the value of the model map key (a List of Maps)
     * Each list list entry (the list map) corresponds to a row
     * The headers for each row are the list map keys
     * The content of the cell are the list map values
     * 
     * @author nestor
     *
     */
    @Override
    protected void buildExcelDocument(Map model, HSSFWorkbook workbook,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
        if(Utils.isEmpty(model)) {
            HSSFSheet sheet = workbook.createSheet(ctx.getString("error.empty.model"));
        } else {
            Set<Map.Entry<String, List<Map<String, Object>>>> set = model.entrySet();
            for (Map.Entry<String, List<Map<String, Object>>> entry : set) {
              String sheetName = entry.getKey();
              HSSFSheet sheet = workbook.createSheet(sheetName);
              List<Map<String, Object>> sheetContent = entry.getValue();
              HSSFRow header = sheet.createRow(0);
              HSSFRow excelRow = header;
              int rowCount = 0;
              for( Map<String, Object> row : sheetContent ) {
                  
                  int i = 0;
                  if( rowCount == 0 ){
                      
                      for( String cellName : row.keySet() ) {
                          Object cellValue = row.get(cellName);
                          header.createCell(i++).setCellValue(cellName);
                      }
                      rowCount++;
                      i = 0;
                  }
                  excelRow = sheet.createRow(rowCount);
                  for( String cellName : row.keySet() ) {
                      Object cellValue = row.get(cellName);
                      HSSFCell cell = excelRow.createCell(i++);
                      
                      //CELL_TYPE_NUMERIC, CELL_TYPE_STRING, CELL_TYPE_BOOLEAN
                      if (cellValue instanceof Integer) {
                          cell.setCellValue((Integer) cellValue);
                          cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                      } else if(cellValue instanceof Float) {
                          cell.setCellValue((Float) cellValue);
                          cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                      } else if(cellValue instanceof Double) {
                          cell.setCellValue((Double) cellValue);
                          cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                      } else if (cellValue instanceof Boolean) {
                          cell.setCellValue((Boolean) cellValue);
                          cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
                      } else if (cellValue instanceof Date) {
                          cell.setCellValue((Date) cellValue);
                          cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                          HSSFCellStyle style = workbook.createCellStyle();
                          HSSFDataFormat dataFormat = workbook.createDataFormat();
                          style.setDataFormat(dataFormat.getFormat("dd/MM/yyyy"));
                          cell.setCellStyle(style);
                      } else {
                          cell.setCellValue(cellValue.toString());
                          cell.setCellType(HSSFCell.CELL_TYPE_STRING);   
                      }
                  }
                  rowCount++;
              }
            }
        }
    }
}

Liferay The requested resource is not available

For some reason Tomcat kept deploying my portlet without complaint but Liferay was showing the below content inside the portlet:

The requested resource (/myWar/myPortlet/invoke) is not available

Solution

Undeploying the whole exploded war from tomcat and then redeploying it back fixed the issue. To undeploy just delete the whole exploded directory from "webapps" folder.

Form submit IE6 problems

There are at least three points to take into consideration when submitting forms with IE6:

1. IE6 has a limitation on how long the query string can be so use POST instead of GET (for debugging purposes GET is good) in your production systems.
2. IE6 has limitations in terms of rendering and processing javascript in parallel. A setTimeout() can be handy to resolve related problems.
3. IE6 is less forgiven than other browsers (which in my opinion is not exactly wrong) so pay attention to the correctness of the document. Always use a validator for your markup.

And I have to say it again and again, if you can afford it make sure you have a dedicated developer for front end. There is simply too much to hack, fragmentation keeps on being an issue and there is no solver bullet.

I still do not understand why I need to be writing about Internet Explorer version 6 in almost year 2011. It reminds me some dictatorships in the world: everybody knows it is evil, do not work fine, it is a waste of time, it is *not* efficient, and the list goes on. Still like some dictatorship for some reason millions of people still want to live oppressed apparently (read: use a FREE mozilla browser, FREE Chrome, FREE Opera).

Life is complicated and I cannot propose to ban IE6 as that will make me a dictator.

Developers are like doctors: no matter what the person is the doctor must save that life. We as developers must fix IE6 issues to save IE6 slaves.

Hopefully this will explain why that form of yours sometimes submits and sometimes simply it does not.

Interesting enough I have seen myself IE6 not submitting when receiving the form from the server however the very same form gets submitted if tested from a saved HTML file. But seriously I have no time to deal with it ...

VINE Remote access for Mac OSX with VNC

As I have to do this every so often I rather document it instead of copying and pasting emails (DRY).

1. In the target MAC run Vine Server
2. Choose a password
3. Share password, external IP address, port (default 5900) and display (default 0) with the client
4. Open the VNC client from a different machine (In OSX use "Chicken of the VNC". In Windows use RealVNC). Use the provided the IP. If the port is not 5900 and the display is not 0 then use "ip:port+display"
5. When done stop and quit Vine Server

Troubleshooting

Most of the time firewall rules are responsible for problems so be sure you have port 5900 accessible in your server side. You can configure a different port from the Preferences window of Vine. Share that port with the client. If you use a different than 0 display share it as well with the client.

SFTP access only with OpenSSH

SFTP or secure FTP is preferred over plain FTP. If you just need to configure some users that have SFTP access only and you can live with a directory per user then you can use the method described below.

Nowadays most of the out-of-the-box pre-installed openssh versions will be greater than 4.9. For those setting up SFTP access is easy I have tested the below in Ubuntu 9.04 (Jaunty) with OOpenSSH_5.6p1, OpenSSL 0.9.8g.

The information below is the result of some research I have performed visiting a dozen of websites and trying things like RSSH and scponly. I found this to be the quickest and simple way to get SFTP working.

1. Find out you are running version > 4.9
   

   $ ssh -v
   

2. Create sftponly group
   

$ groupadd sftponly

3. Configure SFTP-access-only for group sftponly. Note I have commented out the ForceCommand line. In my Ubuntu with that line the server will authenticate the user but the user will get a "Connection closed" message right away.
   

   $ vi /etc/ssh/sshd_config
   #Subsystem sftp /usr/lib/openssh/sftp-server
   Subsystem sftp internal-sftp
   
   #The below must terminate the file
   Match Group sftponly
       ChrootDirectory %h
       AllowTCPForwarding no
       X11Forwarding no
       ForceCommand internal-sftp
   

4. Add a user for example "report". The home directory should be /home/ and the shell must be set to a false shell.
   

   $ useradd -d /home/report -s /bin/false -m report
   

5. Alternatively modify an existing user
   

   $ usermod -d /home/report -s /bin/false report
   

6. Assign user to group
   

   $ usermod -g sftponly report
   

7. Assign a password to the user
   

   $ passwd report
   

8. Modify ownership and permissions to the home directory
   

   $ chown root:root /home/report
   $ chmod 755 /home/report
   

9. Create a folder and assign permissions for the user. Within this folder the user will be able to add/remove folders and files. Of course permissions can vary depending on what you want to achieve.
   

   $ mkdir /home/report/reports
   $ chown report:report /home/report/reports
   $ chmod 755 /home/report/reports
   

10. New users. Just repeat steps 4-9.

If you liked this then it is time for you to check how to simplify sftp user creation.

Some useful resources

http://www.minstrel.org.uk/papers/sftp/builtin/
http://www.cyberciti.biz/tips/rhel-centos-linux-install-configure-rssh-shell.html
http://blog.markvdb.be/2009/01/sftp-on-ubuntu-and-debian-in-9-easy.html

IReport No suitable driver found

I knew I had checked for everything and still some of my Jasper reports were unable to run from Jasper:

...
Caused by: java.sql.SQLException: No suitable driver found for jdbc:...
...

In my case it turned out to be a class loader problem. For some reason (custom class loaders?) the Netbeans GUI is not able to find the JDBC driver. Interesting enough this does not happen for all reports and probably there are situations (specific xml tags inside jrxml?) where the bug shows up.

Some proposed solutions

Sometimes it just works copying your diver jar file into the iReport lib folder. Below are examples in OSX and Windows for my version of iReport (Your folders might differ slightly, that is why I am providing a folder list). The specific driver failing in my case was the sqlite driver.

In Mac OSX

Nestor-Urquizas-MacBook-Pro:~ nestor$ ls -l /Applications/iReport.app/Contents/Resources/ireport/platform9/lib 
total 7240
-rw-r--r--  1 nestor  admin   255928 Jul 20 11:29 boot.jar
-rwxr-xr-x  1 nestor  admin    14731 Jul 20 11:29 nbexec
-rw-r--r--  1 nestor  admin    93696 Jul 20 11:29 nbexec.exe
-rw-r--r--  1 nestor  admin    23345 Jul 20 11:29 org-openide-modules.jar
-rw-r--r--  1 nestor  admin   626203 Jul 20 11:29 org-openide-util.jar
-rw-r--r--  1 nestor  admin  2684154 Oct 28 20:32 sqlitejdbc-3.6.14.2.jar

In Windows

C:\>dir "C:\Program Files\Jaspersoft\iReport-3.7.4\platform9\lib"
 Volume in drive C has no label.
 Volume Serial Number is DC32-F12F

 Directory of C:\Program Files\Jaspersoft\iReport-3.7.4\platform9\lib

11/09/2010  03:03 PM    <DIR>          .
11/09/2010  03:03 PM    <DIR>          ..
07/20/2010  10:37 AM           255,928 boot.jar
07/20/2010  10:37 AM            14,731 nbexec
07/20/2010  10:37 AM            97,792 nbexec.exe
07/20/2010  10:37 AM            93,696 nbexec.exe_original
07/20/2010  10:37 AM            23,345 org-openide-modules.jar
07/20/2010  10:37 AM           626,203 org-openide-util.jar
10/28/2010  07:32 PM         2,684,154 sqlitejdbc-3.6.14.2.jar

But sometimes it does not. Some people (especially on Widnows and linux) report dropping the jar in the $JAVA_HOME/lib/ext/

A perhaps better solution

Why these hacks? What is really going on?

The answer to this question will need to be found in the specific environment and the way iReport (netbeans) work. If you are running a Unix/Linux/OSX syste you can use lsof to find out what is going on and get the issue resolved.

My test case would be a report which uses for example sqlserver and a subreport which uses sqlite. You might notice that the subreport cannot render and sometimes iReport will complain about no suitable driver while others simply the report comes back blank (which is even worst we agree). Follow the below steps and let me know if it does work for you.

1. Remove from preferences classpath any driver jar file
2. Run the master report
3. Check with "lsof|grep sqlite" that ireport did not open a sqlite file (you need to look for the java entries or you could use option -p to get a faster response just for the ireport process). If it lists any sqlite file open just delete the file. In OSX iReport opens a temp file like /private/var/folders/_6/_1d763x107sffygx2l8rr3vw0000gn/T/libsqlitejdbc-1619728097364591810.lib so be aware you are not looking only for jar extension. Even though that file is temporary it might have a caching impact. Sometimes I could even see previous classpath definitions that were already removed which is clearly coming from some internal caching. It is very important that lsof shows no sqlite files open and that you get an error close to the below:

   java.sql.SQLException: No suitable driver found for jdbc:sqlite:/path/to/mysql.db

4. Now that we know we have a system with no caching restart ireport
5. Go to preferences and in the classpath add the jar file
6. The result of lsof command will be showing the newly added jar and then you think you got it: if the jar is referenced iReport should have it in the classpath indeed. Not so fast my friend, classloaders work in misterious ways ;-) Unfortunately it will fail again.
7. Delete the main report jasper file and restart iReport.
8. Edit the main report with something like:

9. Now when you run it you get the driver loaded indeed. What did we do? We just forced the loading of the driver needed by the subreport from the main report. Note that in OSX as I said before you get something like /private/var/folders/_6/_1d763x107sffygx2l8rr3vw0000gn/T/sqlite-3.7.2-libsqlitejdbc.jnilib from lsof instead of the jar driver. Optimizations? Who knows but that caching game really takes the life out of any mortal

XSS and CSRF protection in Spring MVC Framework

UPDATE: Use UUID.randomUUID().toString() instead of trying to build the token yourself. No need to explain what these vulnerabilities are about. As developers it is important to understand them and to protect the software we create against them.

At the time of this writing Spring MVC Framework is still not providing an out-of-the-box protection

XSS protection can be achieved sanitizing the requests and the responses. In Java world you use a filter for the first. For the second XML must be escaped. JSTL has broad support for it as Spring tags has. If you are using scriptlets be sure to come up with your own ecaping mechanism (I love scriptlets but certainly you are safer using taglibs. Beware if you use taglibs to sanitize your resulting-from-request-responses.

There are several samples of XSS filters out there. I have used some variations of Stripes XSSFilter in the past. There are also several libraries that provide XSS protection but they will commonly push for you to use their own taglibs. I prefer to do it myself while I wait for native Spring support.

CSRF protection can be achieved (or at least mitigated) through the use of the Synchronizer Token Pattern. While again a filter or even a listener could be used I prefer to rely on simple Controller logic. In reality I prefer to protect my BHUB single entry points.

So I have a RootController from where I inherit (I am excluding my internal wrappers around getting and setting in session and request attributes but the below should be self explanatory):

@Controller
public class RootController {
...
    protected void init(ControllerContext ctx) {
        ctx.setRequestAttribute("module", ctx.getModuleNameFromCurrentUrl());
        setAdvancedSearchAvailable(ctx, false);
        initializeCsfrToken(ctx);
    }

    private void initializeCsfrToken(ControllerContext ctx) {
       String csrfToken = ctx.getSessionAttribute(ControllerContext.CSRF_TOKEN, "");
       if(Utils.isEmpty(csrfToken)) {
           ctx.setSessionAttribute(ctx, ControllerContext.CSRF_TOKEN, generateCsrfToken(ctx));
       }
       ctx.setRequestAttribute(ControllerContext.CSRF_TOKEN, csrfToken);
    }
    
    private String generateCsrfToken(ControllerContext ctx) {
        //long seed = System.currentTimeMillis(); 
        //Random r = new Random();
        //r.setSeed(seed);
        //return Long.toString(seed) + Long.toString(Math.abs(r.nextLong()));
        return java.util.UUID.randomUUID.toString();
    }
    
    protected boolean isValidCsrfToken(ControllerContext ctx) {
        String csrfParamToken = ctx.getParameter(ControllerContext.CSRF_TOKEN);
        String csrfSessionToken = ctx.getSessionAttribute(ControllerContext.CSRF_TOKEN, "");
        if(!Utils.isEmpty(csrfParamToken) && !Utils.isEmpty(csrfSessionToken) && csrfParamToken.equals(csrfSessionToken)) {
            return true;
        } else {
            //Log this as this can be a security threat
            Log.warn("Invalid security Token. Supplied token: " + csrfParamToken + ". Session token: " + csrfSessionToken + ". IP: " + ctx.request.getRemoteAddr());
            return false;
        }
    }
...

From individual Controllers methods (I am excluding my wrapper around applicationContext#getMessage()) we have:

...
@RequestMapping("/list")
public ModelAndView list(HttpServletRequest request,
           HttpServletResponse response,
           //This is a hack just to provide an uniform way to present global errors in the front end. @ModelAttribute should never be used in this method
           @ModelAttribute("employee") Employee employee,
           BindingResult result) throws ServletException, IOException {

       ControllerContext ctx = new ControllerContext(request, response);
       init(ctx);
       if (!isValidCsrfToken(ctx)) {
           result.addError(new ObjectError("employee", getMessage("error.invalidCsrfToken")));
           return getModelAndView(ctx, "employee/list");
       }
...

Some JSP samples:

...
<div id="global_error"><form:errors path="employee"
             cssClass="errors" /></div>
...
<input type="hidden" name="ctoken" id="ctoken" value="${ctoken}"/>
...
<li><a href="<spring:url value="/employee/list?ctoken=${ctoken}"/>" class="navLink"><span>Employee</span></a></li>

In file message.properties (for internationalization):

error.invalidCsrfToken=Invalid Security Token!

Note that this solution creates a token for the whole session duration. I do not like breaking the back button functionality so I take especial care when submitting so the user lands in a different than submission-page. In addition the ctoken is gotten in JSP from an attribute instead of a param. It is assumed that the param was sanitized before.

Again it would be ideal to have this protection provided out of the box by Spring. A combination of the form tag with annotations in a per Controller method basis (with the help of AOP) is certainly possible. While I wait for that I keep on going the old school way instead of adding just another framework to protect my apps from CSRF attacks.
Finally it is better to use a header instead of a param in the case you must use GET protection. Of course this demands an AJAX driven app (AKA SPA).

Data Encryption with Jasypt Spring JPA and Hibernate

If you are storing private user data you must encrypt it to protect your users and also to avoid consequences (see HIPAA and California/Massachusetts Privacy Laws as an example).

Before continuing let me state that this is not a post about if you should store private data or not, that depends on the real necessities and your professional judgment. This is not a post about how to store your keyring the right way neither a security-for-pros post.

Jasypt is simple and yet tested by security experts that do the job for you so do not rely on obscurity to protect your data, use a Library like Jasypt.

Spring allows to clean your code especially if you use annotations and JPA facilitates persistance supporting annotations. JPA unfortunately is missing still so many features that I forgot a while ago about changing my persistence provider in the future. Hibernate provides a really clean (at least in comparison with others I had to implement in the past) solution.

I am sharing a typical configuration to get a JUnit test up and running but exactly the same applies to a container managed (Web) application.
So in your pom.xml

<!-- Encryption / Decryption -->
        <dependency>
            <groupId>org.jasypt</groupId>
            <artifactId>jasypt</artifactId>
            <version>1.7</version>
            <scope>compile</scope>
        </dependency>

In spring config file:

<context:property-placeholder location="test.properties" ignore-unresolvable="true"/>
<bean id="hibernateStringEncryptor" class="org.jasypt.hibernate.encryptor.HibernatePBEStringEncryptor">
        <property name="registeredName">
            <value>hibernateStringEncryptor</value>
        </property>
        <property name="password">
            <value>${jasypt.password}</value>
        </property>
    </bean>

In your test.properties (Which of course will use a different password than the one you use in development/integration, staging and production environments ):

#Holder for all general test properties
jasypt.password=jasypt

In JPA Entities the proposed annotation on top of the getter did not work for me. I had to annotate the field itself which makes sense BTW:

@TypeDef(
        name="encryptedString", 
        typeClass=EncryptedStringType.class, 
        parameters={@Parameter(name="encryptorRegisteredName",
                               value="hibernateStringEncryptor")}
)

@Type(type="encryptedString")
private String ssn;   

Acceptance Test Driven Development ATDD with Selenium

The User Interface defines how the application works. Automated testing from the user interface is therefore a must-do. And since Front End Engineers are in charge of developing the user interface the best investment you can do for Quality Assurance ( QA ) is to have Front End Engineers responsible for testing the application.

No need to mention how important is to have automated tests as part of any SDLC. Unfortunately for many projects it is prohibited to do any kind of Test Driven Development (TDD). Whatever the reason is (and yes, there are reasons that are sometimes beyond the IT team control) still Tests are necessary.

The tests you cannot forget about are those resulting from existing bugs (Test Driven Bug Fix or TDBF). When you correct something you should write a test that asserts that part of the application works as expected. The reason is that you want to be sure the very same problem does not hit you back. It is embarrassing to hear from Business side "This problem is happening again", period.

So you might not be able to afford TDD but at least you should be able to provide Development with Automated Tests (Let us call it DAT as these days we are supposed to remember abbreviations for almost anything :-)

While U-TDD (Unit TDD) is in reality invisible for the stake holders A-TDD (Acceptance TDD) is not.

User Stories have proven to be an effective mechanism to allow both, business and developers talk the same language. Plain English constructs "Given, When, Then" are a good starting point to build automated acceptance tests. There are multiple testing frameworks supporting code assertions for user stories. Selenium is one that I think is leading the race.

Your Test team (also called QA) can use any language regardless of the language being used for your real website. The reality is that small and medium size companies commonly cannot afford paying for dedicated people for this matter (Even though this is the ideal way to go either for Agile or Waterfall approaches). But as an experienced software architect you know that separation of concerns is the most important principle of successful enterprise software development so even if you decide to use the same language and keep it maintained by the same team of programmers you should:

1. Convince stake holders they must agree on User Stories.
2. Put the acceptance tests that assert the user stories in an acceptance tests project (Do not use your application project to host acceptance tests).
3. Externalize any configurations as URLs and credentials.
4. Schedule (at least daily) automated acceptance tests. Continuum Integration is a must-have. I have built it myself in the past but do not waste your time there are really good tools in the market for this.

The development team must be focused on what the customer really wants. Your customer (stake holders) must be in control to evaluate if the software you are building meets the requirements. There is then, as I have written before, a need for business people to write effective user stories.

Here is how the User Story 1 should be written by our stake holders. Note that we could divide this User Story in more than just one but for the purpose of showing the use of differenct methods in the final Java class I prefer to include all related scenarios:

User Story 1:
------------
1.1 
Given a user is not logged in 
When any page is hit
Then the user should be asked to authenticate

1.2 
Given a user with admin role is logged in 
When an admin page is hit
Then the user should see the page

1.3 
Given a user with just regular user role is logged in 
When an admin page is hit
Then the user should see an error page

In the Java world I prefer Maven as building tool. Below you can find what a POM file will look like to use Selenium 2 for Acceptance tests.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.nestorurquiza</groupId>
    <artifactId>acceptance-tests</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>acceptance-tests</name>
    <packaging>jar</packaging>
    <description>Acceptance tests for my web application</description>
    <properties>
        <maven.compiler.source>1.6</maven.compiler.source>
        <maven.compiler.target>1.6</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
    
    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.8.1</version>
            <scope>test</scope>
        </dependency>
        <dependency>
           <groupId>net.sourceforge.htmlunit</groupId>
           <artifactId>htmlunit</artifactId>
           <version>2.8</version>
           <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>selenium-java</groupId>
            <artifactId>selenium-java</artifactId>
            <version>2.0a6</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <defaultGoal>install</defaultGoal>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>${maven.compiler.source}</source>
                    <target>${maven.compiler.target}</target>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-site-plugin</artifactId>
                <configuration>
                    <source>${maven.compiler.source}</source>
                    <target>${maven.compiler.target}</target>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>
                <configuration>
                    <source>${maven.compiler.source}</source>
                    <target>${maven.compiler.target}</target>
                    <archive>
                        <addMavenDescriptor>false</addMavenDescriptor>
                    </archive>
                </configuration>
            </plugin>
            <plugin>
                <artifactId>maven-release-plugin</artifactId>
                <version>2.0-beta-7</version>
                <configuration>
                    <tagBase>
                        http://my.svn.repo/acceptance-tests/tags/
                    </tagBase>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-eclipse-plugin</artifactId>
                <version>2.7</version>
                <configuration>
                    <ajdtVersion>2.0</ajdtVersion>
                    <wtpversion>2.0</wtpversion>
                </configuration>
            </plugin>
        </plugins>
    </build>
    <distributionManagement>
        <repository>
            <id>central</id>
            <name>libs-releases-local</name>
            <url>
                http://my.artifacts.repo/libs-releases-local
            </url>
            <uniqueVersion>false</uniqueVersion>
        </repository>
    </distributionManagement>
    <scm>
        <connection>scm:svn:http://my.svn.repo/acceptance-tests/tags/trunk/</connection>
    </scm>
</project>

Below is an example of a properties file to hold the target application URL, the WebDriver to use (HTMLUnit driver in this case) and user names and passwords to test:

#acceptanceEnvironment.propeties
baseUrl=http://localhost:8080
webDriverClass=org.openqa.selenium.htmlunit.HtmlUnitDriver
adminUserName=admin@nestorurquiza.com
adminUserPassword=test
regularUserName=user@nestorurquiza.com
regularUserPassword=test

Here is a class you can use to get the properties from the file above in you acceptance tests:

package com.nestorurquiza.acceptance;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

import org.openqa.selenium.WebDriver;
import org.openqa.selenium.htmlunit.HtmlUnitDriver;

/**
 * A VM argument providing the properties path parameter is needed.
 * for example: -DacceptanceEnvironment.properties=/Users/nestor/projects/config/acceptanceEnvironment.properties
 * 
 * Here is an example of such a property file
 * 
* #acceptanceEnvironment.propeties
 * baseUrl=http://localhost:8080
 * webDriverClass=org.openqa.selenium.htmlunit.HtmlUnitDriver
 * adminUserName=admin@nestorurquiza.com
 * adminUserPassword=test
 * regularUserName=user@nestorurquiza.com
 * regularUserPassword=test
 * 
* 
 */
public class EnvironmentProperties {
    private static final String ACCEPTANCE_ENVIRONMENT_PROPERTIES = "acceptanceEnvironment.properties";
    private static final EnvironmentProperties INSTANCE = new EnvironmentProperties();
    private static Properties properties = null;
    
    private EnvironmentProperties() {
    }

    public static Properties getInstance() throws FileNotFoundException {
        if(properties == null) {
            properties = new Properties();
            //Use -DacceptanceEnvironment.properties=/Users/nestor/projects/config/acceptanceEnvironment.properties
            String acceptanceEnvironmentPropertiesPath = System.getProperty(ACCEPTANCE_ENVIRONMENT_PROPERTIES);
            if(acceptanceEnvironmentPropertiesPath != null && acceptanceEnvironmentPropertiesPath.trim().length() > 0){
                InputStream in = new FileInputStream(acceptanceEnvironmentPropertiesPath);
                if(in != null) {
                    try {
                        properties.load(in);
                        in.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                } 
            }
        }
        return properties;
    }
    
    public static WebDriver getWebDriver() throws FileNotFoundException {
        //The simplest not environment specific Driver
        WebDriver webDriver = new HtmlUnitDriver();
        Class c;
        try {
            c = Class.forName(EnvironmentProperties.getInstance().getProperty("webDriverClass"));
            webDriver = (WebDriver) c.newInstance();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (InstantiationException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return webDriver;
    }
    
    public static String getBaseUrl() throws FileNotFoundException {
        return EnvironmentProperties.getInstance().getProperty("baseUrl");
    }
    
    public static String getAdminUserName() throws FileNotFoundException {
        return EnvironmentProperties.getInstance().getProperty("adminUserName");
    }
    
    public static String getAdminUserPassword() throws FileNotFoundException {
        return EnvironmentProperties.getInstance().getProperty("adminUserPassword");
    }
    
    public static String getRegularUserName() throws FileNotFoundException {
        return EnvironmentProperties.getInstance().getProperty("regularUserName");
    }
    
    public static String getRegularUserPassword() throws FileNotFoundException {
        return EnvironmentProperties.getInstance().getProperty("regularUserPassword");
    }
}

Finally here is a sample acceptance test that makes several assertions, all of them included in our User Story 1. Note that the test below asserts a site protected with Spring Security:

package com.nestorurquiza.acceptance;

import static org.hamcrest.CoreMatchers.*;
import static org.junit.Assert.assertThat;

import java.io.FileNotFoundException;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;


/**
 * Verifying behavior of UserStory1.
 */
public class UserStory1Test {

    private WebDriver driver = null;
    
    @Before
    public void aTearUpMethodForEachTest() throws FileNotFoundException {
        driver = EnvironmentProperties.getWebDriver();
    }

    @After
    public void aTearDownMethodForEachTest() {
        driver.close();
    }

    @Test
    public void givenNotLoggedInWhenAnyPageIsHitThenShouldBeAskedToAuthenticate() throws FileNotFoundException {

        String url = EnvironmentProperties.getBaseUrl() + "/client/list";
        driver.get(url);
        assertThat(driver.findElement(By.name("loginForm")).getAttribute("action"), is("j_spring_security_check"));
    }
    
    @Test
    public void givenLoggedInAndAdminUserRoleWhenAnAdminPageIsHitThenShouldSeeThePage() throws FileNotFoundException {
        String url = EnvironmentProperties.getBaseUrl() + "/";
        driver.get(url);
        WebElement loginForm = driver.findElement(By.name("loginForm"));
        WebElement userName = loginForm.findElement(By.name("j_username"));
        userName.sendKeys(EnvironmentProperties.getAdminUserName());
        WebElement password = loginForm.findElement(By.name("j_password"));
        password.sendKeys(EnvironmentProperties.getAdminUserPassword());
        loginForm.submit();
        url = EnvironmentProperties.getBaseUrl() + "/client/list";
        driver.get(url);
        
        //Not supported by HtmlUnit
        //assertThat(driver.findElement(By.cssSelector(".form-navigation")), is(notNullValue()));
        
        assertThat(driver.findElement(By.className("form-navigation")), is(notNullValue()));
    }
    
    @Test
    public void givenLoggedInAndRegularUserRoleWhenAnAdminPageIsHitThenShouldSeeAnErrorPage() throws FileNotFoundException {
        String url = EnvironmentProperties.getBaseUrl() + "/";
        driver.get(url);
        WebElement loginForm = driver.findElement(By.name("loginForm"));
        WebElement userName = loginForm.findElement(By.name("j_username"));
        userName.sendKeys(EnvironmentProperties.getRegularUserName());
        WebElement password = loginForm.findElement(By.name("j_password"));
        password.sendKeys(EnvironmentProperties.getRegularUserPassword());
        loginForm.submit();
        url = EnvironmentProperties.getBaseUrl() + "/client/list";
        driver.get(url);
        
        //Not supported by HtmlUnit
        //assertThat(driver.findElement(By.cssSelector(".form-navigation")), is(nullValue()));
        
        assertThat(driver.findElement(By.className("errors")), is(notNullValue()));
    } 
}

Below is how to run our Acceptance Tests from Maven:

mvn test -DargLine="-DacceptanceEnvironment.properties=/Users/nestor/projects/config/acceptanceEnvironment.properties"

To run the Acceptance Tests from an IDE you will need to configure a VM argument like

-DacceptanceEnvironment.properties=/Users/nestor/projects/config/acceptanceEnvironment.properties

You can of course change the driver so you use different browsers. As the available drivers[1] support javascript from a front end perspective you can come up with very complete test assertions that later can be scheduled so you keep checking the website functionality in a regular basis.

I have to say though that to test Front End development you better wait for the preference of your UI Engineer. Java is most likely not his preferred language.

While I here provide guidance to ATDD from a Java perspective I still think that given the fact that ATDD is closer to front end development or User experience (UX) it should be better to study other possibilities in the case you have the luxury of having a UX engineer on board.

Bibliography

1. http://seleniumhq.org/docs/09_webdriver.html
2. http://thinkinginsoftware.blogspot.com/2010/08/writing-agile-specifications.html

CIFS VFS No username specified cifs_mount failed code = -22

I wanted to use a credentials file instead of plain text user and password in fstab. I did that before in Ubuntu but for some reason in one of my servers I was getting the below:

# mount /mnt/mymount
mount: wrong fs type, bad option, bad superblock on \\windowsbox\shared,
       missing codepage or helper program, or other error
       (for several filesystems (e.g. nfs, cifs) you might
       need a /sbin/mount. helper program)
       In some cases useful info is found in syslog - try
       dmesg | tail  or so

From system logs:

# dmesg|tail
[8628151.305806]  CIFS VFS: No username specified
[8628151.306141]  CIFS VFS: cifs_mount failed w/return code = -22

Solution

This was related to smbs not being installed:

# apt-get install cifs-utils
# which mount.cifs

monit action failed There is no service by that name

It came to my attention a google search for "monit: action failed -- There is no service by that name" did not return any hits.

At list in RHEL 5.5 (Tikanga) if you add new lines to monitrc you have to stop monit. In my case monit runs in inittab and so all I did was kill monit process.

Google Reader Notifier for Android

You should be automatically redirected to this url

Android RNotifier: RSS Google Reader Notifier

Google RSS Reader Notifier (RNotifier) allows you to receive notifications about unread RSS feeds configured in Google reader (Please note you must provide the complete email address and not just the user Id) and password to get notified.
* It checks Google reader feeds every 5 minutes.
* Just click the alert and land in Google reader mobile.
* Once installed it will start automatically every time you restart your phone.
* To stop checking for news just leave email and password empty.

Installation

Go to Google market and download Google RSS Reader Notifier or if navigating this page from your android device click here for a list of my applications. Then select from there.

Support

Use this page for support, questions, enhancements and feature requests. Post any issues here or drop me an email. I will be glad to help making this application better.

* If the application does not work as expected please check you actually have unread feeds in your Google Reader web interface from a Desktop/Laptop computer. Then be sure you have configured your correct email and password.
* If you suspect RNotifier is responsible for any performance issues you can install "TaskPanel" and kill "RNotifier".
* If you find out any problems (bugs) please install "Log Collector" and send me the content by email.

Note for IPhone customers

If there is enough demand I will make it available to the IPhone community as well. So drop me an email if interested.

Manually recover Liferay admin password

I forgot liferay admin password for a brand new production configuration. That is not big deal if the server has accessible SMTP but in my case the hosting provider hadn't enabled it so I knew my only option was to touch the database directly.

Password string "test" after encryption becomes 'qUqP5cyxm6YcTAhz05Hph5gvu9M=' so the below statement will take care of reseting the password to test (Of course in production you must never have the original test@liferay.com user so change that by the real admin email in your system)

UPDATE User_ 
SET password_='qUqP5cyxm6YcTAhz05Hph5gvu9M=' 
WHERE emailAddress='test@liferay.com';

Specify liferay.home or else

First time it happens to me perhaps because I had used Debian in production before but at least in a fresh Red Hat Enterprise 5 I was getting the below error

java.lang.ArrayIndexOutOfBoundsException: 0
 com.liferay.portal.util.PortalInstances._getDefaultCompanyId(PortalInstances.java:232) 

This was solved as suggested in Liferay Forums

Add the below line in portal-ext.properties (change the path according to your specifics) then restart Liferay:

liferay.home=/opt/liferay-portal-5.2.3 

Android HNotifier: A Hotmail Notifier

Hotmail Email Notifier (HNotifier) allows you to configure a hotmail/live email (Please note you must provide the complete email address and not just the user Id) and password to get notified about new and unread emails.
* It checks Hotmail every 5 minutes.
* Just click the alert and land in hotmail mobile to check, reply or send new emails.
* Once installed it will start automatically every time you restart your phone.
* To stop checking emails just leave email and password empty.
* It won’t bother you with the same unread emails alert if you do not perform any actions after clicking the alert and no new emails are received.
* For support go to http://thinkinginsoftware.blogspot.com/hnotifier

Installation

Go to Google market and download Hnotifier or if navigating this page from your android device click here to get any of my applications

Support

Use this page for support, questions, enhancements and feature requests. Post any issues here or drop me an email. I will be glad to help making this application better.

* If the application does not work as expected please check you actually have unread emails in your hotmail web interface from a Desktop/Laptop computer. Then be sure you have configured your correct email and password.
* If you suspect HNotifier is responsible for any performance issues you can install "TaskPanel" and kill "YNotifier".
* If you find out any problems (bugs) please install "Log Collector" and send me the content by email.

Important Notice

At the moment I am not supporting the new security changes Microsoft has added to Hotmail. You can change the settings though. Just go to https://account.live.com/ManageSSL and deactivate SSL from there.

Note for IPhone customers

If there is enough demand I will make it available to the IPhone community as well. So drop me an email if interested.


* It checks Hotmail every 5 minutes.
* Click the alert and land in hotmail email mobile.
* Automatically restarts every time you turn on your phone.
* To stop checking emails just leave email and password empty.

Pdf extraction: Split by bookmarks and merge back

Here is the task: Extract from a PDF those pages bookmarked as "you name it" in a new file keeping the original bookmarks related to the extracted pages.

Solution

1. Download pdfsam
2. Extract the content and execute run-console command (x permission is needed in unix/linux/osx).
3. Run the below to split the original file. Look how you can split at any bookmark level. In this case we split the file using the first level of bookmarks. There are other options available (for more info run ./run-console.sh -h split)

./run-console.sh -f /Users/nestor/Downloads/pdf/20100722_dailystm.pdf -o /Users/nestor/Downloads/pdf/pdfsam_out -s BLEVEL -bl 1 -p [BOOKMARK_NAME] split

4. Now let us say that you want to join only two files (you can join a whole directory if you want: for more info run ./run-console.sh -h concat). We just provide the input file paths and the output file path:

./run-console.sh -f /Users/nestor/Downloads/pdf/pdfsam_out/F-111-11111.pdf -f /Users/nestor/Downloads/pdf/pdfsam_out/F-444-AA33D.pdf -o /Users/nestor/Downloads/pdf/pdfsam_out/merged.pdf concat

5. In my case I had to rebuild the project from the sources as the resulting jar file (pdfsam-console-2.3.0e.jar) failed to correctly merge the files back. Basically files were containing first level bookmarks, all pointing to page 1.

Below are the changes I did to the project so I could build it.

Nestor-Urquizas-MacBook-Pro:trunk nestor$ svn diff ./ant/build.properties
Index: ant/build.properties
===================================================================
--- ant/build.properties (revision 1128)
+++ ant/build.properties (working copy)
@@ -1,8 +1,8 @@
 #where classes are compiled, jars distributed, javadocs created and release created
-build.dir=f:/build2
+build.dir=dist
 
 #libraries
-libs.dir=F:/pdfsam/workspace-enhanced/libraries
+libs.dir=/Users/nestor/Downloads/pdfsam-2.2.0-out/lib


Nestor-Urquizas-MacBook-Pro:trunk nestor$ svn diff ./bin/run-console.sh
Index: bin/run-console.sh
===================================================================
--- bin/run-console.sh (revision 1128)
+++ bin/run-console.sh (working copy)
@@ -15,8 +15,8 @@
 }
 
 
-DIRNAME="../lib/"
-CONSOLEJAR="$DIRNAME/pdfsam-console-2.0.5e.jar"
+#DIRNAME="../lib/"
+CONSOLEJAR="/Users/nestor/pdf/pdfsam/pdfsam-enhanced/pdfsam-console/trunk/dist/pdfsam-console/dist/pdfsam-console-2.3.0e.jar"

Below is the output of the ant command that generates the output jar which you will need to replace in the lib folder of your downloaded binaries (step 1)

Nestor-Urquizas-MacBook-Pro:trunk nestor$ ant -f ant/build.xml 
Buildfile: ant/build.xml

init:

make-structure:

compile:

build-jars:
      [jar] Building jar: /Users/nestor/pdf/pdfsam/pdfsam-enhanced/pdfsam-console/trunk/dist/pdfsam-console/dist/pdfsam-console-2.3.0e.jar

BUILD SUCCESSFUL
Total time: 1 second

Schedule Excel Macros with Parameters

I had this task assigned today and I thought about documenting where I found the pieces of the puzzle.

Of course we are talking about Windows OS here and so it makes sense to use just the Task Scheduler to set when the script should be run.

There is an excellent post that helped me start building the script. The only thing I added to it is the possibility to accept parameters that are injected into an Excel Subroutine that accepts parameters. As Excel will not show such subroutine as a valid macro if you want to call it from inside Excel you will need to use a non parametrized Sub that calls with test parameters the first one.

Below is the code for both of the Subroutines in Excel:

Option Explicit

' This is the routine that can accept parameters
Sub RunMacroFromParameters(param1 As String, param2 As String,  param3 As String)
     Range("E7").Value = param1
     Range("E8").Value = param2
     Range("J8").Value = param3
     MyMacroNeedingE7E8J8CellsAsInputParameters
End Sub

'Use this to test 
Sub ParametersTest()    
    RunMacroFromParameters "Miami", "1/1/2010", "3"
End Sub

Below is the code resulting from modifications on the original post

'''''''''''''''''''''''''''''''''''''''''''''''
'
' @Author: Modified from http://krgreenlee.blogspot.com/2006/04/excel-running-excel-on-windows-task.html#c4023873001264863808
' @Created: 10/01/2010
'
'
' @Description: Runs an Excel Macro and saves the result in a file adding the time stamp
'
' @Parameters (The first parameter if exists is added to the file name):
' 1. complete Path to the excel book
' 2. Name of the Macro
' 3. Extra parameters to be accepted by the Macro procedure. 
'  
'  
'
'
'
''''''''''''''''''''''''''''''''''''''''''''''''

'Create a WshShell to get the current directory

Dim WshShell
Set WshShell = CreateObject("WScript.Shell")

If (Wscript.Arguments.Count < 2) Then
Wscript.Echo "Runexcel.vbs - Required Parameter missing"
Wscript.Quit
End If


'retrieve the arguments

Dim strWorkerWB
strWorkerWB = Wscript.Arguments(0)


Dim strMacroName
strMacroName = Wscript.Arguments(1)

Dim firstExtraParameter
firstExtraParameter = Wscript.Arguments(2)

Dim strMacroParams
If (Wscript.Arguments.Count > 2) Then
    For i = 2 To Wscript.Arguments.Count - 1
        strMacroParams = strMacroParams & " ,""" & Wscript.Arguments(i)    & """"    
    Next    
End If


' Create an Excel instance
Dim myExcelWorker
Set myExcelWorker = CreateObject("Excel.Application")

' Disable Excel UI elements
myExcelWorker.DisplayAlerts = False
myExcelWorker.AskToUpdateLinks = False
myExcelWorker.AlertBeforeOverwriting = False
myExcelWorker.FeatureInstall = msoFeatureInstallNone


Dim StrPathNameNew

' Open the Workbook specified on the command-line
Dim oWorkBook

'it opens the file readonly
Set oWorkBook = myExcelWorker.Workbooks.Open (strWorkerWB,,True)

'on error resume next
' Run the calculation macro
strCommand = "myExcelWorker.Run " & """" & strMacroName & """" & strMacroParams
'Wscript.Echo "Runexcel.vbs - strCommand=" & strCommand 
Execute(strCommand )


if err.number <> 0 Then
' Error occurred - just close it down.
End If
err.clear
on error goto 0

Dim optionalToken
If IsNull(firstExtraParameter) or IsEmpty(firstExtraParameter) Then
    optionalToken = "_" & firstExtraParameter
End If

StrPathNameNew = replace(ucase(strWorkerWB),".XLS","") & optionalToken & "_" & year(Date()) & right("0" & month(date()),2) & right("0" & day(date()),2) & "_" & right("0" & hour(now()),2) & right("0" & minute(now()),2) & right("0" & second(now()),2) & ".XLS"
oWorkBook.SaveAs StrPathNameNew
oWorkBook.Close

' Clean up and shut down
Set oWorkBook = Nothing

' Don’t Quit() Excel if there are other Excel instances
' running, Quit() will shut those down also
if myExcelWorker.Workbooks.Count = 0 Then
myExcelWorker.Quit
End If

Set myExcelWorker = Nothing
Set WshShell = Nothing

Now you can run the command from the scheduler

C:\>"c:\RunExcel.vbs" "c:\myExcelWithMacro.xls" "RunMacroFromParameters" "Fort Lauderdale" "8/1/2010" "10"

Note that the script is generic enough to allow for automation of any Excel Macro whether it expects parameters or not. BTW the file is opened readonly as a new file is created everytime the script is run.

ApacheDS LDAP from Spring Security and Liferay

There are so many ways you can integrate LDAP with Liferay that I think a whole book could be filled with examples. Clearly this is a consequence of the many different scenarios you might face in your company.


Showcase
The showcase I am presenting is:
1. ApacheDS hosts users and groups (roles and groups are the same on the LDAP side in other words we do not have the diferentiation Liferay does have)
2. ApacheDS is accessible from spring security
3. ApacheDS is accessible from Liferay 5.2.3 LDAP implementation following the below user stories.

I have documented already the ApacheDS setup. Of you have not setup ApacheDS read here.

I have also documented on the same link how to get Spring security working with ApacheDS.

User Stories
Liferay LDAP authentication user stories:
1. When a user is set in LDAP, Then the user can login with his credentials when accessing Liferay even if the user has never been set in Liferay
2. When a user is assigned to a group/role in LDAP, Then after user login the new group and the user-group association will be created in Liferay
3. When a user is detached from a group/role in LDAP, Then after user login the user-group association will be removed.

The above guarrantees that we can handle the setup of users for both applications (Portlets and Servlets applications) in just one LDAP server.


Implementation
I thought this was going to be an easy plumbing but it turned to be not that easy. I posted the issue and continued investigating to arrive to the following solution.

1. Spring Security will work only if group contains users (uniqueMember attribute in the group cn)
2. Liferay can work as expected only if user contains groups (any attribute that points to a valid group cn)
3. I have added then an extra attribute to users (ou) which basically closes a cyclic reference between users and groups.
4. See below for the configuration in liferay. Note that I do not include the groups section as in Liferay you must decide either to import users or groups. If you import groups you will not be able to login in version 5.2.3 as I posted in the issues link. I do not show the import/export section as I do not import nor export users and roles. As said before this showcase is precisely about leaving those tasks to LDAP alone. Performance wise this is a good decision BTW.

5. Of course we need to build an application that handles this cyclical reference.

ETL: Importing data with Talend

ETL is used for Operational Data Stores, Data Warehouse and Data Mart. ETL tools can also be handy for simple importing into existing application databases as well.

Importing data into your application is as important as reporting out of your application. Importing can be achieved while distributing the workload through several people.

On one end you want a BA to decide when to run an import process, which components to assembly, which data sources to use, the mappings.

There is an existing model, datasources representing feeds that need to get to your model and a mapping to make that transformation happen. ETL processes are commonly used for this task. You can do something as simple as manual SQL scripting, something more elaborated using a Rules Engine or even more polished like using an ETL tool.

Talend has a very good tool for ETL (which JasperETL uses as well). The fact that latest JasperETL 3.2.3 does not work that well on MAC OSX even after tweaking made me decide to stick to Talend. These tools can be used for:

1. Construct a Business model with a graphical interface that allows BA to drop the general blocks for example an Excel source file to be used in conjunction with a web service output to fill out records in an existing database.
2. Design a Job to implement the business model blocks.
3. Schedule Jobs.

This tutorial is about designing a Job with Talend/Jasper ETL. I am not interested here in covering points 1 and 3 as they are really not needed for our task: Importing data from Excel into an application database.

For the impatient

Create a local MySQL DB and name it myapp. Use the model from http://thinkinginsoftware.blogspot.com/2010/09/jasper-real-time-report-services.html

Download Talend ( I used TOS-All-r48998-V4.1.0RC3.zip ) and uncompress in c:\TOS-All-r48998-V4.1.0RC3

1. Create folder c:\projects\talend to be used as workspace
2. Open Talend (use the non-wpf exe file for example TalendOpenStudio-win32-x86.exe ) and create a project named DbImport. Use as workspace folder c:\projects\talend
3. After Talend is done updating the project close it.
4. Checkout http://nestorurquiza.googlecode.com/svn/trunk/talend/DbImport/ in a temporary directory
5. Copy all files from the temporary directory to the workspace DbImport folder
6. I have included a sample Excel employees.xls with the project. Create folder c:\projects\DbImport and drop the excel there
7. Start Talend and open project DbImport
8. Run PopulateAll job and confirm offices, departments and employees have been added to “myapp” database
9. Note: Even if no changes are made after closing the IDE you will get differences in some projects files. It is good idea to always update from svn before starting to work on a project as other developers might commit their local project files to the repository.

Let us review in detail what I have done in this simple project.

Showcase

1. Go ahead and create a mySQL database named “myapp”. We are going to use the simple model we created on http://thinkinginsoftware.blogspot.com/2010/09/jasper-real-time-report-services.html
2. We want to import employees from an existing Excel spreadsheet into our new database.
3. Our Excel import file contains a de-normalized data we need to import into our normalized tables. The columns are:

first_name
last_name
office_name
department_name

Using Talend / Jasper ETL

1. Install Talend (I am using version 4.1.0) or Jasper ETL (I have tested version 3.2.3 which at least in MAC OSX needs a little tweak). Install it near the File System root. I will describe here everything for Windows OS but you should be able to to the same in other OS. The important is to keep paths reusable through your team. So install Talend then in “c:\talend”
2. Start the program (If using Windows XP use the executable, for example TalendOpenStudio-win32-x86.exe. The Eclipse -wpf- version flicks).Set up a connection. Click on the button near “Repository” (In Talend is “Email” button), provide your email (this will end up stamped in many files from now on so use a real personal or work email). For the workspace folder use a common folder that other users can later use as well in their own machines for example “C:\projects\talend”. You will need to hit “restart” if using Talend and changing the workspace.
3. Select “Create a new Local project” from “Project” section and click on “Go”. Use as project name “DbImport” and as language generation “Java”. Pick the new project from the last dropdown. Click on “Open”.
4. After you close the welcome windows you should see the “Window|Perspective|Design Workspace”. Right click on Job Designs on the left and create the first Job called “PopulateLookup” with Purpose “Import from Excel to lookup MySQL tables”. This is a job that will populate department and office tables. We need office_id and department_id for the employee table, that is why we must be sure the department and office exist in the DB before.
5. On the repository View (left panel by default) right click on Metadata/Db Connection and create a MySQL connection to the database containing the tables employee, department and office tables. Right click on the connection and “retrieve schema” for the three tables.
6. Create an Excel file named c:\projects\DbImport\employees.xls containing the data in the appendix.
7. Right click on Metadata/File Excel and point to a local Excel file (Use Path button to point to the file)
8. Select the sheet, click next, select “Set heading row as column names”, click next and select as name “employee”
9. Drag and drop one by one (into the job area) the department and office metadata (when prompted select tMySQLOutput)
10. Look for the Palette (Components Library). If it is not showing up use “Window|Show View|General|Palette.”
11. Drag and drop a tMap component from the palette “Processing” section.
12. Right click on the employee inner square (you must select the inner square otherwise the option will not be available) and select “Row|Main” a line will be started and will end wherever you click as final destination component. In this case click on the tMap component.
13. Right click on tMap, select “Row|New Output” and drop the line into department and name it outputDepartment. Do the same for Office. When prompted to get the schema from the target respond yes as that helps to see the available destination fields.
14. Double click on the tMap and drag and drop the fields from the input to the output
15. Run the Job from the Run tab. If there are problems the specific component will be red and double clicking on it will show up a description of the problem. You might notice that is the situation as we have specified office_name instead of just name as the destination field in the tMap, so correct that and rerun.
16. Save your job and create a second job named “PopulateEmployee” purpose “Populate table employee”
17. Drop department and office boxes into the working area. Be sure to select type tMySQLInput
18. Drop the employee Excel
19. Drop a tMap
20. Drop the employee MySQL as tMySQLOutput
21. Create input and output connections as explained before. Use naming conventions for example inputEmployee, inputOffice, inputDepartment and outputEmployee
22. Open the tMap and in the input panel drag and drop the inputOffice.name to inputEmployee.office_name and inputDepartment.name to inputEmployee.department_name. Here you are defining the necessary joins from input sources.
23. Drag and drop inputOffice.id, inputDepartment.id, inputEmployee.first_name and inputEmployee.last_name into the output panel left colum right next to the destination field.
24. Run the project to get the data imported. Check the data from the mySQL tables
25. Of course both jobs are related. We want to run PopulateLookup and then later PopulateEmployee. That is why we need to create a third job now. Name it “PopulateAll”
26. Drop two components type “tRunJob” from the palette. From the Component tab select for the first “PopulateLookup” and for the second “PopulateEmployee”
27. Right click on the first and select “Row|Main”. Drop the line into the second sub job.
28. Cleanup the records from the database so you can see all recreated.
29. 
    
    

    delete from office;
    delete from employee;
    delete from office;
    

30. Run “populateAll” job and your data will be in the destination.

Sharing the project

Talend and so JasperETL are designed in a way that they have version control through a server. To avoid using an extra sever you could use export/import (but that would be limiting):
1. To export: Right click on “Business Models” and export “all” to the root folder (in our case C:\projects\) that is shared let us say on a subversion repository. This will create/update “c:\projects\DbImport”. Now you can share that on SVN.

2. To import: Checkout from SVN. Go to Talend and import.

As a better option you can (at least in Talend version 4.1.0) share the whole project (which does not include any binaries) Below is the list of all files for the project in this tutorial:

|-- TDQ_Data Profiling
|   |-- Analyses
|   `-- Reports
|-- TDQ_Libraries
|   |-- Indicators
|   |-- JRXML Template
|   |-- Patterns
|   `-- Rules
|-- businessProcess
|-- businessProcessSVG
|-- code
|   |-- jobscripts
|   |-- routines
|   |   `-- system
|   |       |-- DataOperation_0.1.item
|   |       |-- DataOperation_0.1.properties
|   |       |-- Mathematical_0.1.item
|   |       |-- Mathematical_0.1.properties
|   |       |-- Numeric_0.1.item
|   |       |-- Numeric_0.1.properties
|   |       |-- Relational_0.1.item
|   |       |-- Relational_0.1.properties
|   |       |-- StringHandling_0.1.item
|   |       |-- StringHandling_0.1.properties
|   |       |-- TalendDataGenerator_0.1.item
|   |       |-- TalendDataGenerator_0.1.properties
|   |       |-- TalendDate_0.1.item
|   |       |-- TalendDate_0.1.properties
|   |       |-- TalendString_0.1.item
|   |       `-- TalendString_0.1.properties
|   `-- snippets
|-- components
|-- context
|-- documentations
|-- images
|   |-- job_outlines
|   `-- joblet_outlines
|-- joblets
|-- libs
|-- metadata
|   |-- BRMSconnections
|   |-- FTPconnections
|   |-- LDAPSchema
|   |-- MDMconnections
|   |-- SalesforceSchema
|   |-- WSDLSchema
|   |-- connections
|   |   |-- myapp_0.1.item
|   |   `-- myapp_0.1.properties
|   |-- fileDelimited
|   |-- fileEBCDIC
|   |-- fileExcel
|   |   |-- employee_0.1.item
|   |   `-- employee_0.1.properties
|   |-- fileHL7
|   |-- fileLdif
|   |-- filePositional
|   |-- fileRegex
|   |-- fileXml
|   |-- genericSchema
|   |-- header_footer
|   |-- rules
|   `-- sapconnections
|-- process
|   |-- PopulateEmployee_0.1.item
|   |-- PopulateEmployee_0.1.properties
|   |-- PopulateLookup_0.1.item
|   |-- PopulateLookup_0.1.properties
|   |-- populateAll_0.1.item
|   `-- populateAll_0.1.properties
|-- sqlPatterns
|   |-- Generic
|   |   |-- UserDefined
|   |   `-- system
|   |       |-- Aggregate_0.1.item
|   |       |-- Aggregate_0.1.properties
|   |       |-- Commit_0.1.item
|   |       |-- Commit_0.1.properties
|   |       |-- DropSourceTable_0.1.item
|   |       |-- DropSourceTable_0.1.properties
|   |       |-- DropTargetTable_0.1.item
|   |       |-- DropTargetTable_0.1.properties
|   |       |-- FilterColumns_0.1.item
|   |       |-- FilterColumns_0.1.properties
|   |       |-- FilterRow_0.1.item
|   |       |-- FilterRow_0.1.properties
|   |       |-- MergeInsert_0.1.item
|   |       |-- MergeInsert_0.1.properties
|   |       |-- MergeUpdate_0.1.item
|   |       |-- MergeUpdate_0.1.properties
|   |       |-- Rollback_0.1.item
|   |       `-- Rollback_0.1.properties
|   |-- Hive
|   |   |-- UserDefined
|   |   `-- system
|   |       |-- HiveAggregate_0.1.item
|   |       |-- HiveAggregate_0.1.properties
|   |       |-- HiveCreateSourceTable_0.1.item
|   |       |-- HiveCreateSourceTable_0.1.properties
|   |       |-- HiveCreateTargetTable_0.1.item
|   |       |-- HiveCreateTargetTable_0.1.properties
|   |       |-- HiveDropSourceTable_0.1.item
|   |       |-- HiveDropSourceTable_0.1.properties
|   |       |-- HiveDropTargetTable_0.1.item
|   |       |-- HiveDropTargetTable_0.1.properties
|   |       |-- HiveFilterColumns_0.1.item
|   |       |-- HiveFilterColumns_0.1.properties
|   |       |-- HiveFilterRow_0.1.item
|   |       `-- HiveFilterRow_0.1.properties
|   |-- MySQL
|   |   |-- UserDefined
|   |   `-- system
|   |       |-- MySQLAggregate_0.1.item
|   |       |-- MySQLAggregate_0.1.properties
|   |       |-- MySQLCreateSourceTable_0.1.item
|   |       |-- MySQLCreateSourceTable_0.1.properties
|   |       |-- MySQLCreateTargetTable_0.1.item
|   |       |-- MySQLCreateTargetTable_0.1.properties
|   |       |-- MySQLDropSourceTable_0.1.item
|   |       |-- MySQLDropSourceTable_0.1.properties
|   |       |-- MySQLDropTargetTable_0.1.item
|   |       |-- MySQLDropTargetTable_0.1.properties
|   |       |-- MySQLFilterColumns_0.1.item
|   |       |-- MySQLFilterColumns_0.1.properties
|   |       |-- MySQLFilterRow_0.1.item
|   |       `-- MySQLFilterRow_0.1.properties
|   |-- Netezza
|   |   |-- UserDefined
|   |   `-- system
|   |       |-- NetezzaAggregate_0.1.item
|   |       |-- NetezzaAggregate_0.1.properties
|   |       |-- NetezzaCreateSourceTable_0.1.item
|   |       |-- NetezzaCreateSourceTable_0.1.properties
|   |       |-- NetezzaCreateTargetTable_0.1.item
|   |       |-- NetezzaCreateTargetTable_0.1.properties
|   |       |-- NetezzaDropSourceTable_0.1.item
|   |       |-- NetezzaDropSourceTable_0.1.properties
|   |       |-- NetezzaDropTargetTable_0.1.item
|   |       |-- NetezzaDropTargetTable_0.1.properties
|   |       |-- NetezzaFilterColumns_0.1.item
|   |       |-- NetezzaFilterColumns_0.1.properties
|   |       |-- NetezzaFilterRow_0.1.item
|   |       `-- NetezzaFilterRow_0.1.properties
|   |-- Oracle
|   |   |-- UserDefined
|   |   `-- system
|   |       |-- OracleAggregate_0.1.item
|   |       |-- OracleAggregate_0.1.properties
|   |       |-- OracleCreateSourceTable_0.1.item
|   |       |-- OracleCreateSourceTable_0.1.properties
|   |       |-- OracleCreateTargetTable_0.1.item
|   |       |-- OracleCreateTargetTable_0.1.properties
|   |       |-- OracleDropSourceTable_0.1.item
|   |       |-- OracleDropSourceTable_0.1.properties
|   |       |-- OracleDropTargetTable_0.1.item
|   |       |-- OracleDropTargetTable_0.1.properties
|   |       |-- OracleFilterColumns_0.1.item
|   |       |-- OracleFilterColumns_0.1.properties
|   |       |-- OracleFilterRow_0.1.item
|   |       |-- OracleFilterRow_0.1.properties
|   |       |-- OracleMerge_0.1.item
|   |       `-- OracleMerge_0.1.properties
|   |-- ParAccel
|   |   |-- UserDefined
|   |   `-- system
|   |       |-- ParAccelAggregate_0.1.item
|   |       |-- ParAccelAggregate_0.1.properties
|   |       |-- ParAccelCommit_0.1.item
|   |       |-- ParAccelCommit_0.1.properties
|   |       |-- ParAccelDropSourceTable_0.1.item
|   |       |-- ParAccelDropSourceTable_0.1.properties
|   |       |-- ParAccelDropTargetTable_0.1.item
|   |       |-- ParAccelDropTargetTable_0.1.properties
|   |       |-- ParAccelFilterColumns_0.1.item
|   |       |-- ParAccelFilterColumns_0.1.properties
|   |       |-- ParAccelFilterRow_0.1.item
|   |       |-- ParAccelFilterRow_0.1.properties
|   |       |-- ParAccelRollback_0.1.item
|   |       `-- ParAccelRollback_0.1.properties
|   `-- Teradata
|       |-- UserDefined
|       `-- system
|           |-- TeradataAggregate_0.1.item
|           |-- TeradataAggregate_0.1.properties
|           |-- TeradataColumnList_0.1.item
|           |-- TeradataColumnList_0.1.properties
|           |-- TeradataCreateSourceTable_0.1.item
|           |-- TeradataCreateSourceTable_0.1.properties
|           |-- TeradataCreateTargetTable_0.1.item
|           |-- TeradataCreateTargetTable_0.1.properties
|           |-- TeradataDropSourceTable_0.1.item
|           |-- TeradataDropSourceTable_0.1.properties
|           |-- TeradataDropTargetTable_0.1.item
|           |-- TeradataDropTargetTable_0.1.properties
|           |-- TeradataFilterColumns_0.1.item
|           |-- TeradataFilterColumns_0.1.properties
|           |-- TeradataFilterRow_0.1.item
|           |-- TeradataFilterRow_0.1.properties
|           |-- TeradataTableList_0.1.item
|           `-- TeradataTableList_0.1.properties
|-- talend.project
`-- temp

Unfortunately SVN support is not included in the IDE. Following some steps though you can still share the project.

Commit the project to SVN

1. Create “DbImport” project as explained before.
2. Delete temp directory
3. Import in your SVN
4. Checkout the project from SVN
5. Add svn:ignore for the temp directory (svn propset svn:ignore "temp" .)
6. Commit the project.

Check out the project from SVN

1. Create a new local “DbImport” project. Close the IDE.
2. Outside the workspace folder checkout “DbImport” from SVN.
3. Replace the content of the workspace “DbImport” directory with the checked from SVN files.
4. Open the IDE and modify the project as you wish.
5. Close the IDE and use svn update and/or commit commands as you need.

Documentation

1. http://sourceforge.net/projects/jasperetl/files/
2. http://talend.dreamhosters.com/tos/user-guide-download/V402/DocumentationSet_UG&RG_40b_EN.zip
3. Help from the GUI

Appendix

first_name	last_name	office_name	department_name
John	Smith	London	Legal
Mathew	Parker	USA	Marketing
Andrea	Polini	Rome	Sales