Tools

Free and Open Source Tools.

Function	Tools
VB.NET IDE	Visual Basic Express Edition SharpDevelop
Reports for VB.NET applications	SQL Server Express with Advanced Services (to design report) + Report Viewer redistributable/control (to display report in .NET application)
Report designer and runtime for VB6 applications	Report Manager Designer and ActiveX (http://reportman.sourceforge.net/)
Project management	ProjectLibre
Dictionary [offline]	Wordweb
Office automation	LibreOffice
Setup authoring	NSIS with HM NIS Edit for a GUI Advanced installer free edition Excelsior Installer WiX
.NET Obfuscator	Eazfuscator.NET
SQLite GUI	SQLiteStudio Sqliteman SQLite2009 Pro Enterprise Manager
CD Writing	CDBurnerXP
Download manager/accelerator	DownThemAll firefox add-on
Firewall	Comodo Internet Security
Spyware remover	Spybot search & destroy
Web server	Apache HTTP server
Database	PostgreSQL SQL Server Express edition MySQL CUBRID (Web application needs) HSQLDB (Embedded + Java needs) SQLite (Embedded needs)
MySQL GUI	HeidiSQL Toad for MySQL Freeware MySQL Workbench Adminer PhpMyAdmin
Disk space viewer	WizTree TreeSize Free
Image resizing	Picture Resize Genius Wiseval Photophant
Telnet client	PuTTY
Music player	Winamp free edition
Text editor	Notepad++
Disk Encryption [On-The-Fly Encryption]	FreeOTFE FreeOTFE Explorer TrueCrypt
Virtualization	VirtualBox
CD Drive emulator	VirtualCloneDrive
Startup programs manager	Autoruns
Convert/Save documents or web pages as PDF	PDF Creator
Asymmetric encryption	GPG Gpg4win
File comparison	Winmerge
File compression	7-zip
FTP Client	Filezilla
FTP Server	Filezilla server
Screen capture	Screenhunter free
Perl interpreter	ActivePerl
MD5/SHA-1 hash verifier	HashTab DigestIT 2004
Email server	hMailServer
Password storage	KeePass Password Safe
Website copier	WinHTTrack
Software licencing	ActiveLock (http://www.activelocksoftware.com/)
Regex editor	Rad regex designer (http://www.radsoftware.com.au/regexdesigner/)
Password generator	PasswordGen (http://www.sdean12.org/PasswordGen.htm) PC tools password utilities (http://www.pctools.com/guides/password/) GRC ultra high security password generator (https://www.grc.com/passwords.htm) Xyzzy
Email client	Thunderbird Windows live mail
XML Editor	foxe - FirstObject Xml Editor (http://www.firstobject.com/dn_editor.htm) Notepad++
Universal database GUI. Use to manage any DMBS.	Squirrel SQL client
Enterprise reporting [report deployment, report distribution, web-based reporting, business intelligence]	ART (http://art.sourceforge.net) Pentaho BI suite Community Edition Jaspersoft BI suite Community Edition
Bug tracking	MantisBT
Convert music/video files to different formats	VLC media player
Java decompiler	JD-GUI
ETL	Pentaho Data Integration [Kettle]
OLAP cube viewer and MDX generator	Rex (http://sourceforge.net/projects/whex)
Web based IMAP client [webmail, webmail client]	SquirrelMail Roundcube AtMail open Horde IMP
LDAP server	Apache Directory Server - ApacheDS
LDAP browser [ldap client]	Apache Directory Studio
Difference in database schemas	SQL Power Architect Community Edition (http://www.sqlpower.ca/page/products OR http://code.google.com/p/power-architect/) MySQLDiff (http://www.mysqldiff.org/)
Data generator	Spawner (http://sourceforge.net/projects/spawner/) Dategenerator (http://www.generatedata.com)
Data warehousing database	Infinidb Community Edition
Sms library	Gsmcomm SMSLib
.NET library to read or write excel files without having MS office installed	NPOI (http://npoi.codeplex.com/)
Generate .NET program documentation from xml comments	ImmDoc.Net (http://immdocnet.codeplex.com/)
Profiler for SQL Server Express editions	AnjLab sql profiler (http://sites.google.com/site/sqlprofiler/)
Review resource usage by processes	Process Explorer (http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx)
Manage ssl certificates available to java	Portecle
Reminder to take breaks	Workrave FadeTop
Document Management System	OpenKM
Merge text files	TXTcollector (http://bluefive.pair.com/txtcollector.htm)
Software testing management	TestLink
Determine who is using which port	TCPView
Manage frequent ip address changes	NetSetMan
Unlock files that can’t be deleted because they are locked by some process	Unlocker

Using JasperReports

JasperReports is a java report library that can be used to add reporting capability to an application.

Using JasperReports in a Java EE application


Prerequisistes
JRE
Servlet engine
RDBMS + JDBC driver for a database
Apache Ant

Versions used
JRE 1.6.0_21
Apache Tomcat 6.0.29
MySQL 5.1.50
MySQL connector/J 5.1.13
JasperReports 3.7.0
Apache Ant 1.8.1

Steps

• Download the jasperreports project zip file from sourceforge, http://sourceforge.net/projects/jasperreports/
• Extract the zip file to a temporary location e.g. C:\temp
• Open a command prompt
• Navigate to c:\temp\jasperreports-3.7.0-project\jasperreports-3.7.0\demo\samples\webapp
• Type the command ant javac [the path to the ant bin folder needs to be in the system path]
• Create a folder in the tomcat webapps directory e.g. jasper
• Copy the contents of the webapp directory to the webapps\jasper directory. This sample web application can serve as a guide on how to integrate jasperreports into your own web application.

Using in a custom application

• Copy the following files from the webapps\jasper\web-inf\lib\ directory to your application's web-inf\lib directory. commons-beanutils-1.8.0.jar, commons-collections-2.1.1.jar, commons-digester-1.7.jar, commons-logging-1.0.4.jar, jasperreports-3.7.0.jar, iText-2.1.0.jar (for pdf export), poi-3.2-FINAL-20081019.jar (for xls export). For generating charts you'll need jfreechart and jcommon.

Generating a report

import net.sf.jasperreports.engine.*; //for main jasper objects
import net.sf.jasperreports.engine.export.*; //for exporters

JasperReport jasperReport;
JasperPrint jasperPrint;
JRResultSetDataSource ds;

ServletContext context = this.getServletConfig().getServletContext();  
String fileBase="report1";

String fileName=context.getRealPath("/reports/"+fileBase+".jasper");

File reportFile = new File(context.getRealPath("/reports/"+fileBase+".jasper"));
if (!reportFile.exists()) {
//compile file
fileName=JasperCompileManager.compileReportToFile(context.getRealPath("/reports/"+fileBase+".jrxml"));   
}

//rs is a resultset generated by your application. You can also pass a connection instead of a resultset if you want to use the query as it is in the repoort template    
ds = new JRResultSetDataSource(rs);

HashMap parameters=new HashMap(); //use a map to pass report parameters  
jasperPrint = JasperFillManager.fillReport(fileName, parameters,ds);

//export to pdf   
JasperExportManager.exportReportToPdfFile(jasperPrint,fullFileName);

//export to html
JRXhtmlExporter exporter = new JRXhtmlExporter();
exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, fullFileName);
exporter.exportReport();

//export to xls
JRXlsExporter exporter = new JRXlsExporter();    
exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, fullFileName);
exporter.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.FALSE);
exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
exporter.exportReport();

Passing parameters

• If passing parameters, the parameter value data type in the parameters map must match the data type of the parameter as declared in the report template.

Example template query using a single value parameter

select * from customers where customer_id = $P{customer_id_param}

Example template query using a multi value parameter

select * from products where $X{IN, category, categories_param}

select * from products where $X{NOTIN, category, categories_param}

For multi value parameters, define the parameter class in the jrxml file as java.util.List. Pass values using an array list e.g

List categories = new ArrayList();
categories.add("Laptop");
categories.add("PC");
categories.add("Printer");
parameters.put("categories_param", categories);

Using virtualizers

• A virtualizer can be used to enable large reports to be generated successfully, without resulting in out of memory errors. Using a virtualizer doesn't guarantee filling of arbitrarily large reports. Heap memory is still needed but memory requirements are reduced.
• Also, once a report is filled, whether it can be exported successfully depends on the export format and library. e.g. Filling may be successful, generating a jasper print object but exporting to excel may fail because poi puts all the data in memory before generating the final file. Adding available heap memory may help to have a successful export.
• If a report contains images, this may also be a cause of out of memory errors. In such a case, the virtualizer doesn't help until you set the image's properties "isUsingCache" to false and "isLazy" to true in the report template.

import net.sf.jasperreports.engine.fill.*; //for virtualizers
import net.sf.jasperreports.engine.util.*; //for jrswapfile

//use virtualizer if required
JRAbstractLRUVirtualizer virtualizer=null;

if(!props.getProperty(VIRTUALIZER).equals("none")){
if(props.getProperty(VIRTUALIZER).equals("file")){
int maxSize=Integer.parseInt(props.getProperty(FILE_MAX_SIZE));
virtualizer=new JRFileVirtualizer(maxSize,System.getProperty("java.io.tmpdir"));
params.put(JRParameter.REPORT_VIRTUALIZER, virtualizer);
} else if(props.getProperty(VIRTUALIZER).equals("gzip")){
int maxSize=Integer.parseInt(props.getProperty(GZIP_MAX_SIZE));
virtualizer=new JRGzipVirtualizer(maxSize);
params.put(JRParameter.REPORT_VIRTUALIZER, virtualizer);
} else {
//use swap virtualizer by default
int maxSize=Integer.parseInt(props.getProperty(SWAP_MAX_SIZE));
int blockSize=Integer.parseInt(props.getProperty(SWAP_BLOCK_SIZE));
int minGrowCount=Integer.parseInt(props.getProperty(SWAP_MIN_GROW_COUNT));

JRSwapFile swapFile=new JRSwapFile(System.getProperty("java.io.tmpdir"),blockSize,minGrowCount);
virtualizer=new JRSwapFileVirtualizer(maxSize,swapFile);
params.put(JRParameter.REPORT_VIRTUALIZER, virtualizer);
}
}

//fill report with data
JasperPrint jasperPrint;
if(rs==null){
//use template query
connQuery = ArtDBCP.getConnection(datasourceId);      
jasperPrint = JasperFillManager.fillReport(jasperFileName, params,connQuery);
} else {
//use recordset based on art query 
JRResultSetDataSource ds;
ds = new JRResultSetDataSource(rs);
jasperPrint = JasperFillManager.fillReport(jasperFileName, params,ds);
}

//set virtualizer read only to optimize performance. must be set after print object has been generated
if(virtualizer!=null){
virtualizer.setReadOnly(true);
}

//export report
...

//clean up
if(virtualizer!=null){
virtualizer.cleanup();
}

Notes

• If the resultset is null, by default, the generated report will consist of a completely blank page. To display other report sections and only have the data section blank, if using iReport, change the report properties "when no data" option to "all sections, no detail".
• If the report contains images, the image files should be located in the same directory as the jrxml file
• If a parameter is used in a query and is not provided, it will be ignored, as if the condition didn't exist

Using Mondrian

Using mondrian with Jpivot in a Java EE application

Versions used
Mondrian 3.2.1.13885
JPivot 1.8

Required files

• Deploy the mondrian war
• Copy the folders webapps\mondrian\jpivot and webapps\mondrian\wcf to the root of your application e.g. webapps\myapp
• Copy the folders webapps\mondrian\web-inf\jpivot and webapps\mondrian\web-inf\wcf to the web-inf folder of your application e.g. webapps\myapp\web-inf
• Copy the libraries in the mondrian\web-inf\lib folder to your applications web-inf\lib folder
• Copy the file mondrian\web-inf\mondrian.properties to the myapp\web-inf\classes folder
• Edit this mondrian.properties file to have contents like the following

# Allow the use of aggregates
mondrian.rolap.aggregates.Use=true
mondrian.rolap.aggregates.Read=true
mondrian.native.topcount.enable=true
mondrian.native.filter.enable=true

# result set limit
mondrian.result.limit=50000

# format sql if logging is configured to output sql or mdx
mondrian.rolap.generate.formatted.sql=true

# don't automatically load any drivers
mondrian.jdbcDrivers=

• Create a file log4j.xml in the myapps\web-inf\classes folder with contents like the following

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">


  
    
    
      
    
  
  

  
        
 

   
        
 

   
        
 

 



 



  
    
    
  
  

• Create a file named userconfig.xml in myapps\web-inf\jpivot\print with the following contents

<!-- empty configuration file to avoid error being logged because file is missing -->

web.xml modifications

• Copy and adapt contents of mondrian\web-inf\web.xml to your application's web.xml file
• Files that will launch pivot tables should be contained in the JpivotController filter mapping e.g.

 
    JPivotController
    /user/showAnalysis.jsp
  

• You can change the location of the error and busy pages e.g.

 
    JPivotController
    com.tonbeller.wcf.controller.RequestFilter
    
      errorJSP      /user/jpivotError.jsp      URI of error page
    
    
      busyJSP      /user/jpivotBusy.jsp      This page is displayed if a the user clicks
        on a query before the previous query has finished
    
      

File displaying pivot table

• Use the mondrian\testpage.jsp file as a template for the file that displays pivot tables. Remove the <wcf:include add="" and="" for="" li="" logic="" mdx="" own="" query<="" section="" the="" your="">

if(request.getParameter("action")==null && request.getParameter("null")==null){
...
 %>

<jp:mondrianquery jdbcUrl="<%=databaseUrl%>" jdbcUser="<%=databaseUser%>" jdbcPassword="<%=databasePassword%>" catalogUri="<%=schemaFile%>">
<%=query%>

<% } %>
...



...

• If you want to have a title for the pivot table, store it as a session attribute and retrieve it when you want to display it.

Alternative for getting Required files

• Download the mondrian zip package from sourceforge. http://sourceforge.net/projects/mondrian/files/
• Extract the mondrian zip file to a temporary folder e.g. C:\temp
• Download the jpivot zip package from the jpivot website, jpivot.sourceforge.net
• Extract the jpivot zip file to a temporary folder e.g. C:\temp
• Copy all the jar files from the mondrian lib directory e.g. C:\temp\mondrian-3.2.0.13661\lib to the application's web-inf\lib directory
• Rename the jpivot war file c:\temp\jpivot-1.8.0\jpivot.war to jpivot.zip and unzip the file
• Copy the wcf folder to the application's root e.g. webapps\myapp
• Copy the jpivot folder to the application's root
• Copy the web-inf\wcf folder to the application's web-inf folder e.g. webapps\myapp\web-inf
• Copy the web-inf\jpivot folder to the application's web-inf folder
• Copy the files in the web-inf\lib folder to the application's web-inf\lib folder
• Add the contents of the jpivot web.xml file to your application's web.xml file

Printing

• To ensure is done correctly regardless of the path of the file displaying the pivot table change the wcf toolbar tags that do the printing to

Modifying pdf output header and footer

• Printing to pdf generates a pdf file with default header and footer text. To modify this, edit the file myapp\web-inf\jpivot\table\fo_mdxtable.xsl

Maintaining scroll position

• When you expand a dimension, the page is refreshed and the scroll position goes back to the top of the page. You then have to manually scroll to the element you just expanded. To maintain scroll position have the page that displays the pivot table to have code like the following

<jp:table id="table01" query="#{query01}">
...

Get the current mdx

• If you want to get the mdx query for the currently displayed pivot table view, add code to the file that displays the pivot table similar to the following

<%@ page import="com.tonbeller.jpivot.table.TableComponent,com.tonbeller.jpivot.olap.model.*,com.tonbeller.jpivot.tags.OlapModelProxy" %>
<%@ page import="com.tonbeller.jpivot.olap.query.MdxOlapModel" %>

<%
//get the current mdx
TableComponent table = (TableComponent) session.getAttribute("table01"); 
//assuming table has id of table01 e.g. <jp:table String mdx="";
if( table != null ) {
    OlapModel olapModel = table.getOlapModel();
    while( olapModel != null ) {
        if( olapModel instanceof OlapModelProxy ) {
            OlapModelProxy proxy = (OlapModelProxy) olapModel;
            olapModel = proxy.getDelegate();
        }
        if( olapModel instanceof OlapModelDecorator) {
            OlapModelDecorator decorator = (OlapModelDecorator) olapModel;
            olapModel = decorator.getDelegate();
        }
        if( olapModel instanceof MdxOlapModel) {
            MdxOlapModel model = (MdxOlapModel) olapModel;
            mdx = model.getCurrentMdx();
            olapModel = null;
        }
    }
}
%>

Clear the mondrian cache

• Mondrian uses a cache held in memory to make analysis faster. Every unique mondrian connection [connection string] has a cache. If the underlying database changes and you need to clear the cache, you can use a jsp page like the following

<%@ page contentType="text/html; charset=UTF-8" %>

<%
//clear all mondrian caches
java.util.Iterator<mondrian.rolap.rolapschema> schemaIterator =  mondrian.rolap.RolapSchema.getRolapSchemas();
while(schemaIterator.hasNext()){
    mondrian.rolap.RolapSchema schema = schemaIterator.next();
    mondrian.olap.CacheControl cacheControl = schema.getInternalConnection().getCacheControl(null);
        
    cacheControl.flushSchemaCache();  
}
  
%>

Errors and possible causes

• 404 - Invalid url
• No metadata for catalog - Misspelt catalog name. Names are case sensitive
• XMLA connection datasource not found - Misspelt datasource name. Names are case sensitive
• XMLA Discover unparse results error - Definition file in catalog section of datasources.xml does not exist. If change made to datasources.xml, app has to be redeployed for the changes to take effect

Sample project
The ART reporting tool, http://art.sourceforge.net, makes use of mondrian + jpivot for OLAP queries (pivot tables). It's source code can be used as a reference. It uses custom jpivot.jar, wcf.jar and tbutils-wcf.jar files.

Upgrading Apache HTTP server

• Save apache folder [just in case]
• Uninstall existing installation from add/remove programs
• Run new installer
• Existing sites and configuration e.g. httpd.conf will not be deleted/overwritten

Installing OpenKM 5.1.7

OpenKM is an open source Document Management System

Prerequisites
JDK

Versions used
OpenKM 5.1.7
Oracle JDK 1.6.0_21

Steps

• Download the zip package from the openkm website, openkm.com
• Extract the zip file to a directory of your choice e.g. c:\openkm
• Create a file in the C:\openkm\jboss-4.2.3.GA\bin directory named start.bat with the following contents

run.bat -b 0.0.0.0

• Create a file in the C:\openkm\jboss-4.2.3.GA\bin directory named stop.bat with the following contents

shutdown.bat -S

• Run the start.bat file in the C:\openkm\jboss-4.2.3.GA\bin directory
• Wait for jboss to finish starting (until "server started in ..." is displayed)
• Using a browser, navigate to localhost:8080/OpenKM
• Use the username/password combination of okmAdmin/admin to log in. The username is case sensitive.
• Use the File | Exit menu to logout
• Edit the C:\openkm\jboss-4.2.3.GA\OpenKM.cfg file, changing the hibernate.hbm2ddl line from create to none so that it looks as follows

hibernate.hbm2ddl=none

• Stop openkm by running the stop.bat file in the C:\openkm\jboss-4.2.3.GA\bin directory
• Restart openkm by running the start.bat file in the C:\openkm\jboss-4.2.3.GA\bin directory
• Installation is complete

Increasing permgen space

• Edit the run.bat file in the C:\openkm\jboss-4.2.3.GA\bin directory. Edit the line

set JAVA_OPTS=%JAVA_OPTS% -Xms128m -Xmx512m

So that it looks something like this

set JAVA_OPTS=%JAVA_OPTS% -Xms128m -Xmx512m -XX:MaxPermSize=256m