Thursday, July 15, 2010

Using html2ps

html2ps is a perl script available from http://user.it.uu.se/~jan/html2ps.html that can be used to convert html to postscript [and then you can convert the postscript to pdf].

Prerequisites
Perl
Ghostscript
GSView [for viewing postscript files]
ImageMagick

Versions used
html2ps – 1.0 beta7
ActivePerl – 5.8.8.820
Ghostscript – 8.71
GSView – 4.9
ImageMagick - ImageMagick-6.6.2-3-Q16-windows-dll


Installation
  • Download the zip package from http://user.it.uu.se/~jan/html2ps.html
  • Extract the zip package to c:\
  • Rename the extracted folder to c:\html2ps
  • Download ghostscript from http://pages.cs.wisc.edu/~ghost/
  • Run the ghostscript installation file
  • Add ghostscript to the system path. Add both the bin and lib directories
  • Download the windows binary release exe of InstallMagick from http://www.imagemagick.org/script/index.php
  • Install InstallMagick and specify that the imagemagick installation path should be included in the system path
  • Ensure the perl bin directory is in the system path
  • Open a command prompt window
cd c:\html2ps
perl install
  • Accept the installation script defaults. When asked to enter the name of this directory, type c:\html2ps
  • Once the install script is finished, edit the file c:\html2ps\html2ps. Replace the line $tmpname=$posix?POSIX::tmpnam():"h2p_$$"; with
$tmpname=$posix?POSIX::tmpnam():"h2p_$$";
if($^O =~ m/win/i) {
$tmpname="h2p_$$";}
  • Ghostscript and ImageMagick aren't required for html2ps to work, but some configuration parameters and documents may need them, or other additonal libraries.


Converting a html file to postscript
Example converting the html2ps user guide
cd c:\html2ps
perl html2ps -d -D -f sample -o test.ps html2ps.html


Converting the postscript file to pdf
Use ps2pdf that comes with the ghostscript installation
ps2pdf test.ps test.pdf


Locating the table of contents at the beginning of the document
You can modify many aspects of the postscript file generated by html2ps. This would involve creating and modifying a configuration file. The file "sample" is one such configuration file. You can make a copy of it and add your own modifications to customize the file generated. Review the html2ps user guide html document for configuration options. As an example, you can set the table of contents to be generated at the start of the document instead of the end. Modify the file sample, editing the toc line to the following

option {
toc: hb;

Run html2ps and ps2pdf to confirm that the table of contents is now at the beginning of the document.

perl html2ps -d -D -f sample -o test.ps html2ps.html
ps2pdf test.ps test.pdf


Correcting display of euro signs in pdf bookmarks
If generating a table of contents, after converting the postscript file to pdf the pdf bookmarks may be displayed with two euro signs at the beginning of the bookmark text. To correct this, edit the file html2ps. Replace the line $dh.="/h$nhd [($hind\\240\\240)($htxt)] D\n"; with

$dh.="/h$nhd [($hind\\56\\40)($htxt)] D\n";

Replace the line $toc.="$hv NH le{$nref($hind\\240\\240)$hv C($htxt)$nref 1 TN()EA()BN}if\n"; with

$toc.="$hv NH le{$nref($hind\\56\\40)$hv C($htxt)$nref 1 TN()EA()BN}if\n";


Processing a file with images
If an html file contains links to images held locally and referenced with relative links e.g. src="images/sample.png", use the base option when calling html2ps providing the base url to be appended to all relative links for images.

perl html2ps -d -D -b file:///c:/some/path/ -f sample -o example.ps c:\some\path\example.html


Changing the default look of hyperlinks
By default, text for all links, both to internal document sections and to external web locations, are rendered in a final pdf surrounded by boxes. To have them rendered without the boxes, add a definition to the style sheet definitions in the configuration file used.

A:link { color: blue }
The color must be something other than black. In addition, when running html2ps, you'll need to add the -U parameter.

perl html2ps -d -D -U -f myconfig.txt -o example.ps example.html


Left aligning H1 elements
The example configuration file "sample" provided specifies that H1 elements are centred. If left aligning is required, remove the text-align: center portion of the H1 style rule.


Undesired blank pages
By default, an extra (empty) page is printed, when necessary, to ensure that the title page, the table of contents, and the document itself will start on odd pages. This is typically desirable for double sided printing. If this is not desired, add the extrapage flag to the @html2ps block of the configuration file, setting it to 0.

@html2ps {
extrapage: 0;


Starting new pages
You can have a page break inserted anywhere in the html text, e.g. before H1 elements. To do this, modify the source html and insert <!--NewPage-->


Using CSS
html2ps ignores css contained in the html document. You can define styles in the configuration file. Only a subset of css is supported by html2ps. This subset is outlined in the user guide, in the CSS2 blocks section.


Using custom colours
By default html2ps only recognizes 16 colours. These are defined in the colour block of the html2ps file. To use additional colours, e.g. in css rules, edit your configuration file. In the @html2ps block, add a colour block with the custom colours you use in the style rules. e.g.
Colour{
brown: A52A2A;
}


The user guide that comes with html2ps has explanations for all possible options for modifying the look of the generated postscript, and possibly eventual pdf document.

Tuesday, May 11, 2010

Using Quartz

Quartz is an open source job scheduling library that can be used within a Java application.

Using quartz in a Java EE application

Prerequisistes
JRE
Servlet engine
RDBMS + JDBC driver for storing jobs in a database

Versions used
JRE 1.6.0_20
Apache Tomcat 6.0.20
MySQL 5.0.45
MySQL connector/J 5.1.10
Quartz 1.8.0

Steps
  • Download the package from the quartz website, http://www.quartz-scheduler.org/
  • Unzip the package to a temporary location e.g. C:\temp
  • Add the quartz tables to your application's database schema. The scripts with the quartz table schemas will be in c:\temp\quartz-1.8.0\docs\dbtables.
C:\> mysql -h localhost --database=mydb --user=dbuser --password=dbpassword
mysql> \.  c:\temp\quartz-1.8.0\docs\dbtables\tables_mysql_innodb.sql
mysql> quit
  • Create indexes on the quartz tables just created by running the following additional script
create index idx_qrtz_t_next_fire_time on qrtz_triggers(NEXT_FIRE_TIME);
create index idx_qrtz_t_state on qrtz_triggers(TRIGGER_STATE);
create index idx_qrtz_t_nf_st on qrtz_triggers(TRIGGER_STATE,NEXT_FIRE_TIME);
create index idx_qrtz_ft_trig_name on qrtz_fired_triggers(TRIGGER_NAME);
create index idx_qrtz_ft_trig_group on qrtz_fired_triggers(TRIGGER_GROUP);
create index idx_qrtz_ft_trig_n_g on qrtz_fired_triggers(TRIGGER_NAME,TRIGGER_GROUP);
create index idx_qrtz_ft_trig_inst_name on qrtz_fired_triggers(INSTANCE_NAME);
create index idx_qrtz_ft_job_name on qrtz_fired_triggers(JOB_NAME);
create index idx_qrtz_ft_job_group on qrtz_fired_triggers(JOB_GROUP);
  • Copy the file c:\temp\quartz-1.8.0\quartz-all-1.8.0.jar to your application's web-inf\lib folder e.g. tomcat\webapps\myapp\web-inf\lib
  • Copy all the jar files in c:\temp\quartz-1.8.0\lib to tomcat\webapps\myapp\web-inf\lib
  • Create a file named quartz.properties in myapp\web-\classes with the following details
# quartz configuration

# jobstore
org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreTX

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate

# datasource
org.quartz.jobStore.dataSource = anyString

org.quartz.dataSource.anyString.driver = com.mysql.jdbc.Driver
org.quartz.dataSource.anyString.URL = jdbc:mysql://localhost/mydb
org.quartz.dataSource.anyString.user = dbuser
org.quartz.dataSource.anyString.password = dbpassword
org.quartz.dataSource.anyString.validationQuery=select 1

# thread pool
org.quartz.threadPool.class = org.quartz.simpl.SimpleThreadPool
org.quartz.threadPool.threadCount = 5

# disable quartz version update check
org.quartz.scheduler.skipUpdateCheck=true
  • Create a file named log4j.xml in myapp\web-\classes with the following details
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">

<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">

  <appender name="default" class="org.apache.log4j.ConsoleAppender">
    <param name="target" value="System.out"/>
    <layout class="org.apache.log4j.PatternLayout">
      <param name="ConversionPattern" value="[%p] %d{dd MMM yyyy HH:mm:ss.SSS} %t [%c]%n%m%n%n"/>
    </layout>
  </appender>
    
 <logger name="org.quartz">
   <level value="info" />     
 </logger>

  <root>
    <level value="warn" />
    <appender-ref ref="default" />
  </root>
  
</log4j:configuration>
  • Create a servlet class to be running the scheduler
package my.app;

import javax.servlet.*;
import javax.servlet.http.*;

import org.quartz.impl.StdSchedulerFactory;
import org.quartz.utils.*;

import org.quartz.*;

public class TestScheduler extends HttpServlet
{
Scheduler myscheduler;
public void init(ServletConfig config) throws ServletException
{
try
{
//start scheduler and run a test job
// Initiate a Schedule Factory
SchedulerFactory schedulerFactory = new StdSchedulerFactory();
        // Retrieve a scheduler from schedule factory
        myscheduler = schedulerFactory.getScheduler();
                        
        // Initiate JobDetail with job name, job group, and executable job class
        JobDetail job1 = new JobDetail("myjobDetail", "myjobDetailGroup", MyJob.class);
        // Initiate SimpleTrigger with its name and group name
        SimpleTrigger simpleTrigger = new SimpleTrigger("mysimpleTrigger", "mytriggerGroup");
        
//example setting int parameter for the job
job1.getJobDataMap().put("int-parameter-name",5);

//schedule the job and start the scheduler
myscheduler.scheduleJob(job1, simpleTrigger);
        
        // start the scheduler
        myscheduler.start();
}
catch(Exception e)
{
System.err.println(e);
}
}

public void destroy()
{
//shut down the scheduler
try
{
myscheduler.shutdown(true);
}
catch(Exception e)
      {
      System.err.println(e);
      }
}
}
  • Include the scheduler class in the application's web.xml file so that it runs on startup
 <servlet>
  <servlet-name>TestScheduler</servlet-name>
  <servlet-class>my.app.TestScheduler</servlet-class>
  <!-- Load this servlet at server startup time. Number not special. Just indicates the sequence of loading servlets -->
  <load-on-startup>3</load-on-startup>
 </servlet>
  • Create a class that will be doing the work. The job class. This class needs to implement the org.quartz.job interface
package my.app;

import org.quartz.*;

public class MyJob implements Job
{

//no-argument public constructor
public MyJob()
{
}

//execute method of job interface that does the work
public void execute (JobExecutionContext context) throws JobExecutionException
{
//do anything here.

//you can take parameters passed by the scheduler and use them e.g
JobDataMap dataMap=context.getMergedJobDataMap();
int myIntVariable;
myIntVariable=dataMap.getInt("int-parameter-name");

if (myIntVariable==1)
{ 
//do something
}
else
{
//do something else
}
}
}
  • Instead of creating your own class to start the scheduler, you can use one provided by quartz. Modify the web.xml to have the following
<servlet>
    <servlet-name>
        QuartzInitializer
 </servlet-name>
    <display-name>
        Quartz Initializer Servlet
 </display-name>
    <servlet-class>
        org.quartz.ee.servlet.QuartzInitializerServlet
 </servlet-class>
    <load-on-startup>3</load-on-startup>    
</servlet>
  • A default scheduler instance will now be automatically created and started when the application starts, and automatically shut down when the application is stopped.

  • To access this scheduler within the application e.g. In a jsp page, you can retrieve the scheduler instance from the servlet context. You can have the following
<%@ page import="org.quartz.*,org.quartz.impl.*,org.quartz.utils.*,org.quartz.ee.servlet.QuartzInitializerServlet" %>

<%
  StdSchedulerFactory factory = (StdSchedulerFactory) getServletConfig().getServletContext().getAttribute(QuartzInitializerServlet.QUARTZ_FACTORY_KEY);
  Scheduler scheduler=factory.getScheduler();
  
  // Initiate JobDetail with job name, job group, and executable job class
        JobDetail job1 = new JobDetail("myjobDetail", "myjobDetailGroup", MyJob.class);
        // Initiate SimpleTrigger that will fire immediately
        SimpleTrigger simpleTrigger = new SimpleTrigger("mysimpleTrigger", "mytriggerGroup");        
        job1.getJobDataMap().put("int-parameter-name",5);                
        
        scheduler.scheduleJob(job1, simpleTrigger);
  %>
  • You can create jobs and triggers according to the application's logic and user inteface components used and then schedule using the scheduler object.


Deleting jobs and triggers
You can't add a trigger or job if another one with a similar name and group exists. Use methods of the scheduler object to do the deletion. An exception is not raised if the job or trigger doesn't exist.
scheduler.deleteJob("job name","job group");
scheduler.unscheduleJob("trigger name","trigger group");

Checking if a cron expression is valid
If using a cron trigger, and the expression provided isn't valid, an exception will be raised when creating the trigger. To avoid this you can check whether the expression is valid before creating the trigger object. There's a static method in the CronExpression class for this.
if (CronExpression.isValidExpression(myCronString)){

Determining next run job run time
You can determine the next time a job will run using the trigger's getFireTimeAfter method
java.util.Date nextRunDate=myTrigger.getFireTimeAfter(new java.util.Date())

Or within the job implementation's execute method,
public void execute(JobExecutionContext context) throws JobExecutionException {

java.util.Date nextRunDate=context.getTrigger().getFireTimeAfter(new java.util.Date());

}

Setting job end date
You can set the date on which a job should start or end using the associated trigger's setStartTime and setEndTime methods. By default, a trigger's start time is the time the object is instantiated with no end date. One can set the end date without specifying the start date and vice versa. The end date can't be before the start date, else an exception will be thrown.

Setting quarz properties in code
Instead of having the quartz configuration properties residing in a properties file, you can create a scheduler instance with the properties defined from code. For instance if you don't want to have the database username/password in clear text in the properties file. You'll need to create a java.util.Properties object, populate it with all the relevant quartz properties and then pass the properties object to the StdSchedulerFactory constructor e.g.
import java.util.*;
import org.quartz.*;
import org.quartz.impl.*; 

props=new Properties();
props.setProperty("org.quartz.threadPool.threadCount","10");
//...set other properties

//create scheduler instance 
SchedulerFactory schedulerFactory = new StdSchedulerFactory(props);      
org.quartz.Scheduler scheduler = schedulerFactory.getScheduler();
scheduler.start(); 

//if doing this from a servlet that's loaded on startup, you can put the scheduler instance in the servlet context so that you can access it from anywhere within the application

//save scheduler in the servlet context, to make it accessible throughout the application
getServletConfig().getServletContext().setAttribute("myscheduler",scheduler);

//to access the scheduler elsewhere in the application e.g. to schedule new jobs
Scheduler scheduler=(Scheduler) getServletConfig().getServletContext().getAttribute("myscheduler");
scheduler.scheduleJob(someJobObject, someTriggerObject);

//make sure to call the scheduler's shutdown method in the servlet's destroy method
If creating the scheduler instance like this, you won't need the QuartzInitializerServlet entry in the web.xml file.

Friday, April 16, 2010

Installing Open Source Job Scheduler 1.3.6

Open Source Job Scheduler is an open source application for scheduling and monitoring tasks.

Prerequisites
JRE
RDBMS + JDBC driver
Web server with PHP configured
PHP

Versions used
Apache 2.2.8
MySQL 5.0.45
MySQL Connector/J 5.1.10
PHP 5.2.8
JRE 1.6.0_18

Steps
  • Download the zip package from the job scheduler website, http://jobscheduler.sourceforge.net/
  • Create a temporary directory e.g c:\temp\scheduler
  • Unzip the file to the temporary directory e.g. C:\temp\scheduler
  • Open a command prompt
  • Type mysql -h localhost -u root -p
  • Enter the root password
mysql> create database scheduler;
mysql> grant all on scheduler.* to scheduler@localhost identified by "scheduler";
mysql> quit
cd c:\temp\scheduler
java -jar scheduler_win32.jar
  • The setup program will start
  • Change the installation path from program files e.g. to c:\scheduler
  • Be sure to include the managed jobs, database and web interface components when installing
  • Enter other required details and create a copy of the install script at the end.
  • Edit the file apache\conf\httpd.conf and add the following
Alias /scheduler/logs "c:/scheduler/logs"
Alias /scheduler "c:/scheduler/web"

<Directory "c:/scheduler/web">
    AllowOverride None    
    Options Indexes FollowSymLinks ExecCGI
    Order allow,deny
    Allow from all
</Directory>

<Directory "c:/scheduler/logs">
    AllowOverride None    
    Options Indexes FollowSymLinks
    Order allow,deny
    Allow from all
</Directory>
  • Restart apache
  • Using a browser, navigate to localhost/scheduler/index.htm
  • In the login screen enter sos for the unit, admin for username and leave the password blank
  • Review the documents in the c:\scheduler\doc\en folder for information on how to create and manage jobs. The quickstart is a good place to start.

If you get an error after logging in e.g. Error while connecting to the scheduler... or a message like "It could not commit any Job Scheduler to this site for monitoring", try the following.
  • Edit the file c:\scheduler\web\packages\scheduler\sos_scheduler_network.inc.php. Modify the normalize_host function to look as follows.
  function normalize_host( $host ) {
    return gethostbyname($host);
    /*
    $ip = gethostbyname($host);
    if( ereg( '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$', $ip ) ) {
      return gethostbyname(gethostbyaddr($ip));
    } else {
      return '';
    }
    */
  }
  • In the same file also edit the line that sets the ip host to
$this->network_schedulers[$ip_port]['host']= $this->normalize_host(APP_SCHEDULER_HOST);
  • Edit the file c:\scheduler\web\scheduler_monitor.php. Edit the line that sets the ip to
$ip=gethostbyname($spooler_host);


Scheduling Pentaho Data Integration (Kettle) jobs
If scheduling kettle transformations or jobs, the scheduler will think the job has encountered an error because some text is written to stderr and a warning email is sent as a result. To avoid this, when configuring the job, after settting the command parameter, set the ignore stderr parameter to Yes.

Another way to avoid the warning email would be to modify the batch file that runs the job or transformation to redirect stderr to the same file as stdout [using 2>&1]. e.g.
cd C:\Kettle
kitchen.bat /file:"C:\path\job.kjb" > "C:\path\job.log" 2>&1

Installing OpenReports 3.2.0

OpenReports is an open source web reporting solution that supports a variety of open source reporting engines, including JasperReports, JXLS, and Eclipse BIRT. You create reports externally using these tools and then OpenReports can be used to run, schedule and deliver them. OpenReports also supports QueryReports and ChartReports which enable you to easily create reports and charts by only specifying the required SQL. No report design is required for these.

OpenReports 3.2.0 supports JasperReports 3.5.1, Eclipse BIRT 2.3.2 and JXLS 0.9.8.

Prerequisites
JRE or JDK 1.6

Versions used
JRE – 1.6.0.17

Steps
  • Download the zip file from SourceForge, http://sourceforge.net/projects/oreports/files/
  • Unzip the file to C:\. Don't rename the extracted folder, but leave the extracted path as C:\openreports-tomcat.
  • Go to Start > Control Panel > System > Advanced System Settings. Select the Advanced tab and click on the Environment Variables button. Click on the new button and create a variable named JRE_HOME with the value set to the path of the JRE directory e.g. C:\Program Files\Java\jre6. If you have JDK installed, create a variable named JAVA_HOME instead with the value set to the path of the JDK directory.
  • Edit the file c:\openreports-tomcat\startup.bat to have the following contents
cd database
start start-database.bat
cd ..\tomcat\bin
call startup
  • Create a new file in c:\openreports-tomcat named shutdown.bat with the following contents
cd database
start stop-database.bat
cd ..\tomcat\bin
call shutdown
  • Create a new file in c:\openreports-tomcat\database named stop-database.bat with the following contents
@echo off

FOR %%b IN (openreports,sample) DO call :runCommand %%b
goto :end

:runCommand
java -cp hsql/hsqldb-1.7.3.jar org.hsqldb.util.ShutdownServer -url "jdbc:hsqldb:hsql://localhost/%1" -user "SA" -password ""

:end
  • Double-click on the file c:\openreports-tomcat\startup.bat. This will start the HSQLDB database OpenReports uses internally and the tomcat server bundled with OpenReports.
  • Wait for the tomcat server to finish starting up. When finished the original command window will display something like INFO: Server startup in 78676 ms
  • Open a browser window and navigate to http://localhost:8080/openreports
  • If port 8080 is already in use, for instance by another web server, modify the file C:\openreports-tomcat\tomcat\conf\server.xml. Search for 8080 and replace the value for the HTTP/1.1 connector with another port number that's not in use and that's greater than 1024. Restart the server and include the new port number in the URL you navigate to.
  • Login using the username admin and password admin.
  • You can now select and view the sample reports that come with the installation.
  • To add new reports, create the reports using the reporting engines supported by OpenReports e.g. JasperReports and deploy the report files to the OpenReports server. Add and configure them from OpenReports to make them available.
  • Copy the JDBC drivers for the databases you'll be reporting from to the C:\openreports-tomcat\tomcat\common\lib folder.
  • Copy the JDBC drivers for the databases used by BIRT reports to the folder C:\openreports-tomcat\tomcat\webapps\openreports\WEB-INF\platform\plugins\org.eclipse.birt.report.data.oda.jdbc_2.3.2.r232_v20090212\drivers
  • To have emails sent to a mail server with SMTP authentication turned on, add the following to the file C:\openreports-tomcat\tomcat\webapps\openreports\WEB-INF\applicationContext.xml. Setting the smtp authentication details in the OpenReports administration panel doesn't work.
<bean id="mailAuthenticator" class="org.efs.openreports.util.SMTPAuthenticator">
 <constructor-arg index="0" value="email account here" />
 <constructor-arg index="1" value="password here" />
 </bean>

 <bean id="mailSession" class="javax.mail.Session" factory-method="getInstance">
 <constructor-arg index="0">
 <props>
 <prop key="mail.smtp.host">smtp server hostname or IP here</prop> 
<prop key="mail.smtp.auth">true</prop> 
 </props>
 </constructor-arg>
 <constructor-arg index="1" ref="mailAuthenticator" />
</bean>
  • To get OLAP report samples to work when using JRE 1.6+, delete the file xalan-2.6.0.jar from the WEB-INF\lib folder and then restart tomcat.
  • To stop OpenReports, double-click on the file C:\openreports-tomcat\shutdown.bat
  • Installation is complete


Setting up OpenReports to use an independent Tomcat server and MySQL for its database
OpenReports comes bundled with a tomcat server and preconfigured HSQLDB database. To use a pre-existing tomcat server and MySQL for the database, use the following additional steps.

Prerequisites
Tomcat
MySQL
Apache Ant
JDK 1.6
MySQL JDBC driver

Versions used
Tomcat 6.0.20
MySQL 5.0.45
JDK 1.6.0_17
Apache Ant 1.8.0RC1
MySQL JDBC driver 5.1.10

Steps
  • Download Apache Ant from http://ant.apache.org/ if you don't already have it. Download the binary distribution zip archive.
  • Unzip the file to c:\
  • Edit the PATH environment variable and add ;C:\apache-ant-1.8.0RC1\bin at the end.
  • Copy the MySQL JDBC driver [jar file] to the tomcat\lib folder of the tomcat installation
  • Ensure you have a JAVA_HOME environment variable that points to the JDK folder e.g. C:\Program Files\Java\jdk1.6.0_17
  • Edit the file C:\openreports-tomcat\database\schema\or_ddl_mysql.sql. Add semicolons at the end of each command, and add the following lines in addition.
INSERT INTO REPORT_USER (NAME,PASSWORD,PDF_EXPORT_TYPE) VALUES('admin','admin',0);
INSERT INTO USER_SECURITY(USER_ID,ROLE_NAME) VALUES (1,'ROOT_ADMIN_ROLE');
  • Open a command prompt window and type mysql -h localhost -u root -p [assuming mysql\bin is included in the PATH environment variable]
  • Supply the root password in the mysql window that comes up
mysql> CREATE DATABASE openreports;
mysql> GRANT ALL ON openreports.* TO oreports@localhost IDENTIFIED BY 'oreports';
mysql> use openreports
mysql> \. C:\openreports-tomcat\database\schema\or_ddl_mysql.sql
mysql> \. C:\openreports-tomcat\database\schema\quartz\tables_mysql.sql
mysql> quit
  • Edit the file C:\openreports-tomcat\openreports\src\openreports.properties to have the following contents
# properties used by the Spring configuration

hibernate.dialect=org.hibernate.dialect.MySQLDialect

hibernate.jdbc.driver=com.mysql.jdbc.Driver
hibernate.jdbc.url=jdbc:mysql://localhost:3306/openreports
hibernate.jdbc.username=oreports
hibernate.jdbc.password=oreports
hibernate.jdbc.maxIdle=2
hibernate.jdbc.maxActive=5

quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate  

# use the following line for HSQLDB demo Quartz databases
#quartz.jobStore.selectWithLockSQL = SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?

# use this for all other Quartz databases
quartz.jobStore.selectWithLockSQL = SELECT * FROM {0}LOCKS WHERE LOCK_NAME = ? FOR UPDATE
   
quartz.jdbc.driver = com.mysql.jdbc.Driver
quartz.jdbc.url = jdbc:mysql://localhost:3306/openreports
quartz.jdbc.username=oreports
quartz.jdbc.password=oreports
quartz.jdbc.maxIdle=2
quartz.jdbc.maxActive=5

#number of threads that are available for concurrent execution of jobs
org.quartz.threadPool.threadCount = 5
  • Edit the file C:\openreports-tomcat\openreports\WebRoot\WEB-INF\applicationContext.xml and add the following items to enable sending mail with SMTP authentication.
<bean id="mailAuthenticator" class="org.efs.openreports.util.SMTPAuthenticator">
 <constructor-arg index="0" value="email account here" />
 <constructor-arg index="1" value="password here" />
 </bean>

 <bean id="mailSession" class="javax.mail.Session" factory-method="getInstance">
 <constructor-arg index="0">
 <props>
 <prop key="mail.smtp.host">server hostname or IP here</prop> 
<prop key="mail.smtp.auth">true</prop> 
 </props>
 </constructor-arg>
 <constructor-arg index="1" ref="mailAuthenticator" />
</bean>
  • Edit the file C:\openreports-tomcat\reports\datasources.xml and delete the sample mondrian datasource so that the file has the following contents.
<?xml version="1.0"?>
<DataSources>

</DataSources>
  • Delete the following files from C:\openreports-tomcat\openreports\WebRoot\WEB-INF\lib\.xalan-2.6.0.jar, xercesImpl-2.0.2.jar and xml-apis-2.0.2. These will be replaced with files from the xalan 2.7.1 distribution.
  • Download the binary distribution of xalan 2.7.1 from http://xml.apache.org/xalan-j/downloads.html
  • Unzip the package to a temporary folder e.g. c:\temp
  • Copy the files xalan.jar, xercesImpl.jar, xml-apis.jar and serializer.jar from c:\temp\xalan-j_2_7_1 to the folder C:\openreports-tomcat\openreports\WebRoot\WEB-INF\lib\
  • If you'll be using a jasper reports version other than 3.5.1, delete the files jasperreports-3.5.1.jar and jasperreports-3.5.1-javaflow.jar from C:\openreports-tomcat\openreports\WebRoot\WEB-INF\lib\. Place the files for the required jasper engine in the folder instead e.g. jasperreports-3.7.0.jar and jasperreports-3.7.0-javaflow.jar.
  • Put the mysql JDBC driver in the folder C:\openreports-tomcat\openreports\WebRoot\WEB-INF\lib. You can skip this step if a copy of the driver is available in the tomcat\lib folder
  • If you are using tomcat 6 and JRE 1.6, put a copy of the olap4j jar file in C:\openreports-tomcat\openreports\WebRoot\WEB-INF\lib. You can download this file from http://sourceforge.net/projects/olap4j/files/. This step is important for the environment described. If omitted, the application will deploy successfully on tomcat 5.5 but fail on tomcat 6 with an error “cannot create jdbc driver...java.lang.NoClassDefFoundError: org/olap4j/OlapWrapper...”.
  • Edit the C:\openreports-tomcat\openreports\WebRoot\WEB-INF\applicationContext.xml so that the environment bean section looks something like this
<bean id="environment"
    class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
    lazy-init="false">
    <property name="ignoreResourceNotFound" value="true" />
    <property name="locations">
      <list>        
            <value>/WEB-INF/openreports.properties</value>        
      </list>
    </property>
  </bean> 
  • Move the file openreports.properties from C:\openreports-tomcat\openreports\src to C:\openreports-tomcat\openreports\WebRoot\WEB-INF.
  • Edit the file C:\openreports-tomcat\openreports\build.xml to change the deployment path of the openreports.properties file. Add an entry to the “war” target fileset for this file
<target name="war" depends="jar">
 <war destfile="${deploy}/openreports.war" webxml="${web-inf}/web.xml">
  <fileset dir="${WebRoot}">
   <include name="**/openreports.properties" />
  • These last 3 steps will allow you to change the openreports database at a later time, without having to recompile the application.
  • Open a command prompt window and navigate to C:\openreports-tomcat\openreports
  • Type ant war and hit enter.
  • Wait for the task to complete with a BUILD SUCCESSFUL message
  • Start the tomcat service
  • Navigate to localhost:8080/ and login to the web application manager.
  • Deploy the updated war file found at C:\openreports-tomcat\tomcat\webapps\openreports.war
  • Nagivate to localhost:8080/openreports
  • Login using username admin and password admin.
  • Click on the Administration button, then the Settings link and set the following properties
Base directory – C:\openreports-tomcat\reports [where jasper or birt report files will reside]
Report generation directory – C:\openreports-tomcat\reports\generated
Temp directory – C:\openreports-tomcat\temp
Query report max rows – 1000
  • If you would like to use an independent tomcat server with the sample hsqldb database that comes with openreports, make sure that the version of the hsqldb driver referenced in the C:\openreports-tomcat\database\start-database.bat file used to start up the database is the same one used in the tomcat installation e.g. in the tomcat\lib folder. Otherwise you may get a “connection is broken...” error when starting the application if the openreports.properties file points to the sample database.


Using BIRT reports
OpenReports 3.2.0 supports BIRT 2.3.2.
  • Copy the JDBC drivers for the databases used by the BIRT reports to the folder tomcat\webapps\openreports\WEB-INF\platform\plugins\org.eclipse.birt.report.data.oda.jdbc_2.3.2.r232_v20090212\drivers
  • When creating a new report in OpenReports, when adding a report template, add the .rptdesign report file.
  • To design birt reports, use the BIRT RCP designer 2.3.2. You can download it from http://download.eclipse.org/birt/downloads/index2.3.2.php

When exporting to xls, BIRT uses Excel 2003 xml format [birt uses office 2003 xml for all formats but OpenReports doesn't have an option for exporting to doc or ppt]. This doesn't open properly by default in OpenOffice. To generate binary xls files, you can use the Tribix xls emitter for BIRT.
  • Download xls-emitter-bin_2.3.1.zip from http://sourceforge.net/projects/tribix/files/
  • Download the Apache POI library. Download the file poi-bin-3.2-FINAL-20081019.zip from http://archive.apache.org/dist/poi/release/bin/
  • Extract xls-emitter-bin_2.3.1.zip to a temporary location e.g. C:\temp
  • Extract poi-bin-3.2-FINAL-20081019.zip to a temporary location e.g. C:\temp
  • To use the tribix emitter in the Eclipse RCP Designer, copy the contents of c:\temp\xls-emitter-bin_2.3.1\plugins to birt-rcp-report-designer-2_3_2\plugins. [a jar file and a folder]
  • Copy c:\temp\poi-3.2-FINAL\poi-3.2-FINAL-20081019.jar to birt-rcp-report-designer-2_3_2\plugins\org.uguess.birt.report.engine.emitter.xls_2.3.1.200812291511\lib
  • Rename the file birt-rcp-report-designer-2_3_2\plugins\org.eclipse.birt.report.engine.emitter.prototype.excel_2.3.2.r232_v20090601.jar e.g to not-used-org.eclipse.birt.report.engine.emitter.prototype.excel_2.3.2.r232_v20090601.jar
  • Open the BIRT designer and run a report as XLS.
 
  • To use the tribix emitter in OpenReports, stop tomcat and copy the contents of c:\temp\xls-emitter-bin_2.3.1\plugins to tomcat\webapps\openreports\WEB-INF\platform\plugins.
  • Copy c:\temp\poi-3.2-FINAL\poi-3.2-FINAL-20081019.jar to tomcat\webapps\openreports\WEB-INF\platform\plugins\org.uguess.birt.report.engine.emitter.xls_2.3.1.200812291511\lib
  • Rename the file tomcat\webapps\openreports\WEB-INF\platform\plugins\org.eclipse.birt.report.engine.emitter.prototype.excel_2.3.2.r232_20090202.jar e.g. to not-used-org.eclipse.birt.report.engine.emitter.prototype.excel_2.3.2.r232_20090202.jar
  • Restart tomcat
  • When you export BIRT reports to XLS, they will now use the tribix xls emitter.


Using JasperReports
OpenReports 3.2.0 supports jasperreports 3.5.1 by default.
  • To design reports using the jasperreports engine, install and use iReport. You can download it from http://sourceforge.net/projects/ireport/files/ 
  • If you use an iReport version later than 3.5.1 to design the reports, reports you deploy to OpenReports may not display any data. This is because the same version of jasperreports used to compile a report should be used to run it.
To use a later version of jasperreports e.g. 3.7.0, use the following additional steps.
  • Download the files jasperreports-3.7.0.jar and jasperreports-javaflow-3.7.0.jar from http://sourceforge.net/projects/jasperreports/files/
  • Copy these two files to the tomcat\webapps\openreports\WEB-INF\lib folder
  • Delete the files jasperreports-3.5.1.jar and jasperreports-3.5.1-javaflow.jar from tomcat\webapps\openreports\WEB-INF\lib. Stop tomcat if the files are in use and can't be deleted.
  • When creating a new report in OpenReports, when adding a report template, add the .jasper report file rather than the .jrxml file. To get a .jasper file, preview the report from within iReport.
If you need to compile a jrxml file to a particular jasper version, e.g. a report to be used with the jasperreports 3.5.1 engine in OpenReports, download and use the iReport version corresponding to the required jasper version.

Installing OpenI 2.0 RC2

OpenI is an open source business intelligence application. It's main emphasis is on providing OLAP analysis, although it can also do regular reporting by allowing publishing of jasper report files.

Prerequisites
Tomcat 5.5+ [with JDBC driver in the tomcat\lib directory]
JRE 1.5+
OLAP server
RDBMS + JDBC driver

Versions used
Tomcat 6.0.20
JRE 1.6.0_18
Mondrian 3.1.5
MySQL 5.0.45
MySQL Connector/J 5.1.10

Steps
  • Download the openi-tomcat package from sourceforge, http://sourceforge.net/projects/openi/files/
  • Extract the zip package to a temporary folder e.g. C:\temp
  • Edit the file tomcat\conf\tomcat-users.xml file and add some details for openi users.
<role rolename="openi"/>
<role rolename="app_admin"/>
<role rolename="foodmart_user"/><role rolename="foodmart_admin"/>
<user username="openiadmin" password="password" roles="openi,app_admin"/>
<user username="foodmartadmin" password="password" roles="openi,foodmart_admin"/>
<user username="foodmartuser" password="password" roles="openi,foodmart_user"/> 
  • Edit the tomcat\webapps\mondrian\web-inf\datasources.xml file. Modify the default datasource [with datasourcename of Provider=Mondrian;DataSource=MondrianFoodMart;].
  • Edit the DatasourceInfo element to put the appropriate details for the foodmart sample database i.e database url, username and password. e.g.
<DataSourceInfo>Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart;JdbcUser=foodmart;JdbcPassword=foodmart;JdbcDrivers=com.mysql.jdbc.Driver;</DataSourceInfo>
  • Start tomcat
  • Copy the file c:\temp\openi\openi.war to the tomcat\webapps folder. This will cause the application to be deployed
  • Using a browser, navigate to localhost:8080/openi
  • Login with username/password of openiadmin/password
  • Openi needs a folder in which to place project details. You'll be prompted to create one e.g .in c:\openi-projects. Click on the create sample project button to do this.
  • This will create the folder with a sample project that's made for the foodmart sample database.
  • Click on the Logout button
  • Edit the file c:\openi-projects\foodmart\project.xml and modify the jdbc_default string entry with the details for the sample foodmart database e.g. Put the correct database url, username and password. The details for the mondrian data source entry should match those in the tomcat\webapps\mondrian\web-inf\datasources.xml file, if this wasn't changed when installing the mondrian web application.
  • Create a new environment variable called PROJECTS_DIR with the value c:\\openi-projects.
  • Using a browser, navigate to localhost:8080/openi
  • Login with username/password of openiadmin/password
  • You can now view the analyses and reports of the sample foodmart project.

Installing Mondrian 3.1.5

Mondrian is an open source ROLAP server. It uses the ROLAP scheme of storing data i.e. It doesn't have a data storage engine of its own. The data is stored in an external relational database.

Prerequisites
JRE 1.4+
Tomcat 5.5+ [with JDBC driver in the tomcat\lib directory]
RDMBS + JDBC driver

Versions used
JRE 1.6.0_18
Tomcat 6.0.20
MySQL 5.0.45
MySQL Connector/J 5.1.10

Steps
mysql> create database foodmart;
mysql> grant all on foodmart.* to foodmart@localhost identified by "foodmart";
mysql> use foodmart;
mysql> \. c:\temp\foodmart_mysql.sql
mysql> quit
  • Start tomcat
  • Copy c:\temp\mondrian-3.1.5.13307\lib\mondrian.war to the tomcat\webapps directory. This will cause the mondrian application to be deployed.
  • Edit the the fourhier.jsp, mondrian.jsp, colors.jsp and arrows.jsp files in the tomcat\webapps\mondrian\web-inf\queries directory. Edit the following line
<jp:mondrianQuery id="query01" jdbcDriver="sun.jdbc.odbc.JdbcOdbcDriver" jdbcUrl="jdbc:odbc:MondrianFoodMart" catalogUri="/WEB-INF/queries/FoodMart.xml">
  • Replace it with details of the mysql foodmart database you've just created
<jp:mondrianQuery id="query01" jdbcDriver="com.mysql.jdbc.Driver" jdbcUrl="jdbc:mysql://localhost/foodmart?user=foodmart&password=foodmart" catalogUri="/WEB-INF/queries/FoodMart.xml"> 
  • Edit the the testrole.jsp files in the tomcat\webapps\mondrian\web-inf\queries directory. Edit the following line
<jp:mondrianQuery id="query01" jdbcDriver="sun.jdbc.odbc.JdbcOdbcDriver" jdbcUrl="jdbc:odbc:MondrianFoodMart" catalogUri="/WEB-INF/queries/FoodMart.xml" role="California manager">
  • Replace it with details of the mysql foodmart database you've just created
<jp:mondrianQuery id="query01" jdbcDriver="com.mysql.jdbc.Driver" jdbcUrl="jdbc:mysql://localhost/foodmart?user=foodmart&password=foodmart" catalogUri="/WEB-INF/queries/FoodMart.xml" role="California manager">
  • Edit the tomcat\webapps\mondrian\web-inf\datasources.xml file. Edit the datasourceinfo tag of the default data source to have the details of the sample foodmart database. e.g.
<DataSourceInfo>Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart;JdbcUser=foodmart;JdbcPassword=foodmart;JdbcDrivers=com.mysql.jdbc.Driver;</DataSourceInfo>
  • Edit the tomcat\webapps\mondrian\web-inf\mondrian.properties file. Edit the connectionstring property to have the details of the sample foodmart database. e.g.
mondrian.test.connectString=Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart;JdbcUser=foodmart;JdbcPassword=foodmart;JdbcDrivers=com.mysql.jdbc.Driver;Catalog=/WEB-INF/queries/FoodMart.xml;
  • Edit the tomcat\webapps\mondrian\web-inf\web.xml file. Modify all occurrences of the string @mondrian.webapp.connectString@, replacing it with details for connecting to the sample foodmart database. This will be required for the connectString context parameter and the MDXQueryServlet servlet sections of the file e.g
<context-param>
    <param-name>connectString</param-name>
    <param-value>Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart;JdbcUser=foodmart;JdbcPassword=foodmart;Catalog=/WEB-INF/queries/FoodMart.xml;JdbcDrivers=com.mysql.jdbc.Driver;</param-value>
  </context-param>

  <servlet>
    <servlet-name>MDXQueryServlet</servlet-name>
    <servlet-class>mondrian.web.servlet.MdxQueryServlet</servlet-class>
    <init-param>
      <param-name>connectString</param-name>
      <param-value>Provider=mondrian;Jdbc=jdbc:mysql://localhost/foodmart;JdbcUser=foodmart;JdbcPassword=foodmart;JdbcDrivers=com.mysql.jdbc.Driver;Catalog=/WEB-INF/queries/FoodMart.xml;</param-value>
    </init-param>
  </servlet>
  • Stop tomcat
  • Download the binary distribution of xalan 2.7.1 from http://xml.apache.org/xalan-j/downloads.html
  • Unzip the package to a temporary folder e.g. c:\temp
  • Delete the file tomcat\webapps\mondrian\web-inf\lib\xalan.jar
  • Copy the files xalan.jar, xercesImpl.jar, xml-apis.jar and serializer.jar from c:\temp\xalan-j_2_7_1 to the folder tomcat\webapps\mondrian\WEB-INF\lib\
  • Start tomcat
  • Using your browser, navigate to localhost:8080/mondrian
  • You can now get a feel of the examples using the links provided.


Using the schema workbench
In order to create OLAP cubes, you can use the mondrian schema workbench. The schema workbench is a Java desktop application allows you to
  • Visually create and test Mondrian OLAP cube schemas, validating the schema against the cube database
  • Run example MDX queries using the schema and the database
  • Browse the cube database
Steps
  1. Download the schema workbench zip package from sourceforge, http://sourceforge.net/projects/mondrian/files/
  2. Extract the zip file to c:\
  3. Place jdbc drivers to connect to the source database in the c:\schema-workbench\drivers folder
  4. Double-click the file c:\schema-workbench\workbench.bat to start the schema workbench. You'll need to have defined a JAVA_HOME or JRE_HOME environment variable pointing to the java location.
  5. Click on the Tools | Connection menu
  6. Enter the connection details for the database
  7. You can then open the xml file that contains a mondrian schema definition for editing, or create a new schema definition.

Installing Pentaho BI Server 3.5.0

The Pentaho BI Suite Community Edition [CE] is an open source business intelligence package that includes ETL, analysis, metadata, and reporting capabilities. The suite is composed of server components and client tools. The server components [also referred to as the BI Platform] consist of the BI Server, the administration console and the user console. The BI server is responsible for management and delivery of reports.

Some of the Pentaho client tools include:
  • Report Designer: Used to create formatted reports. Report Designer offers far more flexibility and functionality
    than the ad hoc reporting capabilities of the Pentaho User Console.
  • Design Studio: An Eclipse-based tool that enables you to hand-edit a report or analysis view
    xaction file. Generally, people use Design Studio to add modifications to an existing report that
    cannot be added with Report Designer. Design Studio is also required to enable report scheduling, e-mail distribution, dynamic and static prompting, and more.
  • Metadata Editor: Enables you to add a custom metadata layer to an existing data source.
    Usually you would do this for a data source that you intend to use for analysis or reporting; it's
    not required, but it makes it easier for business users to parse the database when building a
    query.
  • Pentaho Data Integration: Also known as Kettle. An extract, transform, and load (ETL) tool, which enables
    you to access and prepare data sources for analysis, data mining, or reporting.
  • Schema Workbench: A graphical tool that helps you create ROLAP schemas for analysis.

These steps are for setting up the server components.

Prerequisites
JRE or JDK 1.5+

Versions used
Pentaho BI Server 3.5.0
Pentaho Report Designer 3.5.0
JRE – 1.6.0.17

Steps
  • Download the business intelligence server zip file from SourceForge, http://sourceforge.net/projects/pentaho/files/
  • Download the report designer zip file from the same SourceForge location
  • Create a folder c:\pentaho\server and extract the BI server zip file here.
  • Create a folder c:\pentaho\design-tools and extract the report designer zip file here.
  • Go to Start > Control Panel > System > Advanced System Settings. Select the Advanced tab and click on the Environment Variables button. Click on the new button and create a variable named JRE_HOME with the value set to the path of the JRE directory e.g. C:\Program Files\Java\jre6. If you have JDK installed, create a variable named JAVA_HOME instead with the value set to the path of the JDK directory.
  • Note that the BI server package seems to come with it's own JRE which will be located in C:\Pentaho\server\biserver-ce\jre.
  • Edit the file C:\Pentaho\server\biserver-ce\tomcat\bin\setclasspath.bat. Modify the first four lines of the gotJreHome label so that they appear as follows. [tools.jar comes with JDK, not JRE]
:gotJreHome
if not exist "%JRE_HOME%\bin\java.exe" goto noJavaHome
if not exist "%JRE_HOME%\bin\javaw.exe" goto noJavaHome
REM if not exist "%JRE_HOME%\lib\tools.jar" goto noJreToolsJar
REM set CLASSPATH=%JRE_HOME%\lib\tools.jar
  • Edit the files start-pentaho.bat and stop-pentaho.bat located in C:\Pentaho\server\biserver-ce\. Comment out the line setting the value of the JAVA_HOME variable so that it looks as below
REM set JAVA_HOME=%_PENTAHO_JAVA_HOME%
  • The preceding two steps are only required if you only have JRE and don't have JDK
  • Edit the file C:\Pentaho\server\biserver-ce\pentaho-solutions\system\publisher_config.xml. Enter a password [in clear text] in the element. This is the password that will be used when publishing reports from the report designer to the BI server. Don't leave it blank.
  • Open a command prompt window and navigate to c:\Pentaho\server\biserver-ce. Type start-pentaho and hit enter. This will start the HSQLDB database the BI server uses internally in a new command prompt window and start the tomcat server bundled with the BI server also in a new command window.
  • Wait for the tomcat server to finish starting up. When finished the tomcat command window will display something like INFO: Server startup in 77980 ms
  • Open a command prompt window and navigate to c:\Pentaho\server\administration-console. Type start-pac and hit enter. This will start the pentaho administration console.
  • Open a browser window and navigate to http://localhost:8099 to access the pentaho administration console. If prompted for login credentials, use “admin” for the username and “password” for the password.
  • Open a browser window and navigate to http://localhost:8080/pentaho to access the pentaho user console
  • If port 8080 is already in use, for instance by another web server, modify the file C:\Pentaho\server\biserver-ce\tomcat\conf\server.xml. Search for 8080 and replace the value for the HTTP/1.1 connector with another port number that's not in use. Restart the server and include the new port number in the URL to access the user console.
  • Click on the login button in the user console and select the sample user Joe (admin) to login. The username and password are automatically filled in the form. This user has a username of “Joe” and a password of “password”.
  • If you'll need to access the user console from different machines, edit the file C:\Pentaho\server\biserver-ce\tomcat\webapps\pentaho\WEB-INF\web.xml. Change the base-url parameter, replacing “localhost” with the server's hostname or IP address.
  • To start the report designer, open a command prompt window and navigate to C:\Pentaho\design-tools\report-designer. Type report-designer and hit enter.
  • To stop the BI server, open a command prompt window and navigate to c:\Pentaho\server\biserver-ce. Type stop-pentaho and hit enter. OR double click on the stop-pentaho.bat file.
  • To stop the administration console, double click the file c:\Pentaho\server\administration-console\stop-pentaho.bat file.
  • Note that if you use IE8 for the browser, intranet settings may not be enabled for localhost, and some features of the user console will not work properly. To correct this, select the Tools | Internet Options menu. If the main menu [File, Edit etc] isn't displayed on IE, press the alt key to get it to display. Select the security tab, then the Local intranet zone and click on the sites button. Uncheck the Automatically detect intranet network box and click on the Advanced button. Type in http://localhost and click on the Add button.
  • To enable emailing of reports, edit the file C:\Pentaho\server\biserver-ce\pentaho-solutions\system\smtp-email\email_config.xml, adding the details of the email server.

Using Apache

Disable caching

  • Uncomment the line #LoadModule expires_module modules/mod_expires.so in the httpd.conf file
  • Create a file named .htaccess under the relevant website directory and add the text

# enable expiration
ExpiresActive On

#expire all pages to prevent caching
ExpiresDefault "access plus 1 second"
  • Restart Apache

Use folder structure not in the document root
  • Add an alias in the httpd.conf file e.g.
Alias /mysite "c:/somefolder/mysite"


    AllowOverride None    
    Options Indexes FollowSymLinks ExecCGI
    Order allow,deny
    Allow from all

Using PostgreSQL

Creating a database
createdb -U postgre_user database_name
[assumes postgre bin location has been added to the system path]

Starting the command line client
psql -U postgre_user

Changing database within psql
postgres=# \c mydatabase

Using MySQL

Using keywords as column names or in queries
Enclose keywords around backticks e.g. Select `desc` from mytable

Using Activelock

Activelock is an open source software licensing and copy protection component for windows applications. Its source is available in VB6 and VB.NET.

Prerequisites
Visual Studio 6 with SP6
MSXML 4
Visual Studio 2008
IIS with ASP.NET configured (only for testing web integration)

Version used
Activelock 3.6.0.3
Visual Studio 6 with SP6
.NET framework 3.5
Visual Studio 2008 Express edition
IIS on Windows XP (IIS 5.1)


Installation
  1. Download the VB6 and VB2008 core dlls and source code packages from the activelock website, activelocksoftware.com
  2. Run the setup programs to install the activelock files

Customizing the VB6 dll
  1. Copy the contents of the dll source folder C:\Program Files\Activelock_VB6_3.6\Activelock3.6 for VB6 to your own folder e.g. C:\MyLock
  2. Copy the contents of the test application folder C:\Program Files\Activelock_VB6_3.6\ALTestApp3.6 for VB6 to your own folder e.g. C:\MyLock TestApp
  3. Using the code in the C:\MyLock folder, open the file ActiveLock3.vbp
  4. Save the project with a different name e.g. MyLock.vbp
  5. Change the name of the project under project properties e.g. MyLock
  6. Right click on the project and click on the properties menu
  7. Clear the help file text box
  8. Change the project description text box e.g. MyLock
  9. In the make tab, change the version number e.g. 1.0.0
  10. Change the application title e.g MyLock
  11. Change all the items under version information as desired
  12. Open a code window and replace all occurrences of the string ActiveLock3 with e.g. MyLock. Ensure the Find whole word only option is selected.
  13. In activelock.cls, change the value of the AL_REGISTRY_HIVE$ constant
  14. In activelock.cls, modify the ValidateShortKey procedure. Comment out the section referring to alcrypto, only leaving the automatic registration code. If you don't want the short key to be tied to a specific computer, comment out the line fprint = modHardware.GetFingerprint()
  15. In activelock.cls, modify the IactiveLock_InstallationCode Get procedure. Don't pass the fingerprint to the GenerateShortSerial function. This is also only if the short key isn't required to be tied to a specific computer
  16. Make any other customizations as required
  17. Add a reference to the Microsoft WMI Scripting V1.1 Library
  18. If you don't have MDAC 2.8, remove the existing reference and select the reference for the highest version of Microsoft ActiveX Data Objects Library you have.
  19. Compile the dll, giving it a different name e.g MyLock.dll
  20. Select the project properties, and under the component tab, set version compatibility to binary compatibility, referencing the dll just compiled.
  21. Compile the dll again.
  22. Close the project.

Customizing the key generator
  1. Using the code in the C:\MyLock folder, open the file Alugen.vbp
  2. Save the project with a different name e.g. MyGenerator.vbp
  3. Change the name of the project e.g. MyGenerator
  4. Open a code window and replace all occurrences of the string ActiveLock3 with the same name used in the dll e.g. MyLock
  5. Under project references, remove the reference to the ActiveLock Object Library 3.6 and add a reference to the modified dll you've compiled e.g MyLock.dll.
  6. Build the exe, giving it a different name e.g. MyGenerator.exe
  7. Close the project

Customizing the test application
  1. Using the code in the C:\MyLock TestApp folder, open the file ALVB6Sample.vbp.
  2. Save the project with a different name e.g. MyLockTestApp.vbp
  3. Open a code window and replace all occurrences of the string ActiveLock3 with the same name used in the dll e.g. MyLock
  4. Under project references, remove the reference to the ActiveLock Object Library 3.6 and add a reference to the modified dll you've compiled.
  5. In the form_load procedure of frmMain, comment out the line with CheckForResources
  6. In the form_load procedure of frmMain, modify the software password e.g. .SoftwarePassword="pass"
  7. In the form_load procedure of frmMain, modify the licence key type to .LicenseKeyType = alsShortKeyMD5. Only if a short key will be used
  8. Build the exe, giving it a different name e.g. MyLockTestApp.exe
  9. Close the project

Generating an installation code
  • Create a new project and add two text boxes to it, txtUser and txtInstallCode
  • Add a reference to the modified dll, MyLock.dll
  • Open a code window and paste the following code
Option Explicit

Private ActiveLock As DVTransManager.IActiveLock
Private WithEvents ActiveLockEventSink As ActiveLockEventNotifier

Private Sub Form_Load()
    InitializeActiveLock
End Sub

Private Sub txtUser_Change()
    txtInstallCode.Text = ActiveLock.InstallationCode(txtUser.Text)
    
    On Error GoTo 0
    Exit Sub
End Sub

Private Sub InitializeActiveLock()
    Set ActiveLock = DVTransManager.NewInstance()
    
    Set ActiveLockEventSink = ActiveLock.EventNotifier
    
    With ActiveLock
        .SoftwareName = "ALVB6Sample"
        .SoftwareVersion = "3.6"
        .SoftwarePassword = "pass"
        .SoftwareCode = "RSA1024BgIAAAAkAABSU0ExAAQAAAEAAQCBcwKp9p1rkQhZyxTeREh9EM273wBqpODS+KLkeu/xn/Q0+w8uhBQfZq8f/sdRfL+S5LIBItOv0okG42mKcaNk0mRoSoJkUPMrRp43j8nAKVCmRrD7pZ1Do3uHM4SjydLY0omeK8vOCyZ2WldYy0IxwgQjNqMHLuG1rCg1DR4+5g=="
        
        .LicenseKeyType = alsShortKeyMD5
        .LockType = lockNone
    End With
   
    ActiveLock.Init
End Sub

  • Run the program and in the txtUser text box, type in "test"
  • Copy the installation code displayed

Generate and test a licence key
  1. Run MyGenerator.exe
  2. In the licence keygen tab, make sure the product is ALVB6Sample – 3.6
  3. Paste the installation code in the installation code text box
  4. In the user name text box, type in "user"
  5. Click on the Generate button to generate the licence key
  6. Run MyLockTestApp.exe
  7. In the user name text box, type in "user"
  8. In the installation code text box, paste the installation code used in the key generator
  9. In the liberation key text box, paste the licence key generated by the key generator
  10. Click on the Register button
  11. Registration should be successful, indicating that you have successfully created a licence key and had it verified.

Using with a new product
  1. For your own custom application, you start by creating a new product in the key generator.
  2. Type in a name and version for your product and click on the Generate button to generate the product codes. The codes are a public/private key-pair with the Vcode being the public key, and the Gcode the private key. The codes are specific to a particular product name and version combination
  3. Click on Add to product list to add the new product to the list of known products
  4. The source code in the test application can be used as a guide for your own windows application. Further customization would be required e.g to include different product details, and apply custom stealth measures if desired.


Customizing the VB.NET version
To customize the VB.NET version of Activelock, download and install the corresponding VB.NET package and make code changes in the same places as the VB6 version.
The product codes, installation codes and licence keys [liberation keys] generated by the VB.NET and VB6 versions are not the same. This means a licence key generated by the VB.NET key generator will not be valid when an attempt to register the licence is made from the VB6 component and vice versa.


Web integration for the VB6 version
One may require to automate licence key generation and distribution through a website. If your windows application is using the VB6 component to verify licence keys, you'll need to generate keys using the VB6 key generator.
  1. Download and install the VB6 version of Activelock
  2. Copy the following files from C:\Program Files\Activelock_VB6_3.6\Activelock3.6 for VB6 to your own folder e.g. C:\MyWebGenerator. modActiveLock.bas, modALUGEN.bas and modWindowsVersion.bas.
  3. Open the VB6 IDE and create a new ActiveX DLL project
  4. Give the project a name e.g. MyWebGenerator and change the name of the default class e.g WebGenerator and save the project to the folder you created e.g. C:\MyWebGenerator
  5. Add the module files to the project
  6. Open the project file Alugen.vbp from C:\Program Files\Activelock_VB6_3.6\Activelock3.6 for VB6
  7. Create a public function in you class e.g. GenerateLicenceKey and use code from the cmdKeyGen_Click procedure of the Alugen project to implement the licence key generation as per your requirements.
  8. Declare a private class variable for the Activelock object and use the InitActiveLock procedure from frmMain in the Class_Initialize method.
  9. Add a project reference to the VB6 Activelock dll, perhaps one which you've modified and compiled.
  10. Compile your new dll e.g. MyWebGenerator.dll

Using the VB6 key generation dll in an ASP.NET website
  1. Set up an IIS server that can serve ASP.NET pages
  2. Copy MyWebGenerator.dll to the web server, if it's on a different machine, e.g to a folder C:\Components
  3. Register MyWebGenerator.dll on the web server
  4. Use the tlbimp.exe utility that comes with the .NET framework to create a runtime callable wrapper for the ActiveX dll you've just compiled. This is to allow you to use the functions of the dll from a .NET development environment just like it was a .NET component.
  5. Search for the file tlbimp.exe on the machine and copy it to C:\Components
  6. Open a command prompt window and navigate to C:\Components
  7. Run tlbimp giving a new name for the .NET wrapper. The syntax to use would be something like tlbimp MyWebGenerator.dll /out:MyWebGeneratorRCW.dll /verbose
  8. Create a new website project e.g using Visual Web Developer Express edition
  9. Under the Website | Add Reference menu, browse and add a reference to the wrapper dll MyWebGeneratorRCW.dll
  10. Use the ildasm.exe utility that comes with the .NET framework to confirm the name of the namespace, class and methods in the wrapper dll you've just created. Run ildasm and open the MyWebGeneratorRCW.dll. The namespace is the name of the node directly under the MANIFEST node. Classes are blue nodes and interfaces are blue nodes with an I.
  11. Add a line in your webpage to import the wrapper namespace as indicated by ildasm e.g <%@ Import Namespace="MyWebGeneratorRCW" %>
  12. Instantiate and use the methods in your dll as you would any .NET component in ASP.NET.


Web integration for the VB.NET version
Create a .NET dll using code from the VB.NET ALUGEN, just like is done for the VB6 version. Since ASP.NET will use the .NET dlls without having to jump any hoops, add a reference to this dll and use as required.

Installing Bugzilla 3.2

Bugzilla is an open source, web-based bug-tracking application.



Prerequisites
Web server configured to process CGI requests
Perl 5.8.1 and above
MySQL 4.2.1 and above
Internet connection

Versions used
Apache 2.2.8
MySQL 5.0.45
ActivePerl 5.8.8.820

Steps
  • Download the package [tarball] from the bugzilla website, bugzilla.org.
  • Unzip the package to a folder in the web server's document root, e.g. Bugzilla
  • Create a database on the MySQL server e.g.
mysql> create database bugs;
mysql> grant all on bugs.* to bugs@localhost' identified by 'password';
mysql> quit;
  • Confirm/enable CGI support in Apache by including the following line in the section of the httpd.conf file
AddHandler cgi-script .cgi
  • Allow running of .cgi scripts by adding the ExecCGI option to the section of the the httpd.conf file
Options Indexes FollowSymLinks ExecCGI
  • Allow Bugzilla's .htaccess file to restrict access to sensitive documents by allowing it to override the defaults. This involves changing AllowOverride None to AllowOverride All
  • To tell Apache to use Perl to execute .cgi files, use the ScriptInterpreterSource directive. Add ScriptInterpreterSource Registry-Strict to the httpd.conf file. In order for ScriptInterpreterSource Registry-Strict to work, you also need to add an entry to the Registry. Create a key HKEY_CLASSES_ROOT\.cgi\Shell\ExecCGI\Command with the default value of the full path of perl.exe with a -T parameter e.g. C:\Perl\bin\perl.exe -T . This will cause this path to be used to run the cgi scripts rather than the path specified in the first line of the scripts themselves. Bugzilla's cgi scripts specify the path of the perl executable as /usr/bin/perl
  • Add index.cgi to the DirectoryIndex list in the httpd.conf file
  • Restart Apache
  • To confirm that perl cgi scripts process OK, create a file test.cgi in the document root folder with the following contents and then navigate to localhost/test.cgi. If Hello, World is displayed on the browser, cgi configuration was successful
#!/perl/bin/perl
# test cgi execution of perl script
print "Content-type: text/html\r\n\r\n";
print "Hello, World.";
  • Open a command prompt window and run checksetup.pl in the bugzilla folder. This checks for perl modules required for bugzilla and guides on which ones to install if they aren't present
cd \apache\htdocs\bugzilla
perl checksetup.pl
  • With an internet connection available, run the commands given to install the relevant perl packages/modules using PPM [Perl Package Manager]. Add the suggested repository and install at least the mandatory packages. Alternatively download and unzip the individual packages from the suggested website e.g. theoryx5.uwinnipeg.ca/ppms for perl 5.8 packages or ppm.activestate.com/PPMPackages/zips/, and then use PPM to install them offline. e.g. ppm install c:\tmp\TimeDate.ppd
  • After all mandatory packages are installed and the script has read the ./localconfig file, edit the file C:\apache\htdocs\bugzilla\localconfig, updating the database variables, $db_driver, $db_name, $db_user, $db_pass, with details bugzilla will use to connect to the database created.
  • Run checksetup.pl again. This time it will build your database tables and initialise Bugzilla. Enter the hostname of the SMTP server to use [or localhost to continue installation]. Also enter the admin email, name and password. Installation is now complete.
  • Navigate to yourserver/bugzilla/ using your browser and login using the administrator account created. Use the email address as the login.
  • Click on the parameters link to update some required settings. Set at least the maintainer email address and urlbase. This is the final configuration step and after this bugzilla is ready for use.
 
  • Add a Scheduled Task to run collectstats.pl nightly
  • Add a Scheduled Task to run whineatnews.pl nightly
  • Add a Scheduled Task to run whine.pl nightly. whine.pl needs to run in Perl's Taint mode. The command line needs to include the -T option e.g. C:\Perl\bin\perl.exe -T C:\apache\htdocs\bugzilla\whine.pl
  • Backup the entire Bugzilla directory
  • Backup the Bugzilla Database. It contains all the Bugs, Users, Attachments etc. Schedule the mysqldump utility to create the database dump [backup file], and add the resulting file to your backup media 

Note
If the main bugzilla page doesn't load, check that the web server is properly configured by running the perl script testserver.pl in the bugzilla folder to get an idea of what may be wrong e.g.
cd \apache\htdocs\bugzilla
perl testserver.pl http://localhost/bugzilla

Installing PHP 5.2.8

PHP stands for PHP: Hypertext Preprocessor. It refers to a scripting language mainly used for server-side scripting and the parser/engine/compiler that processes PHP code. It runs on a web server, taking PHP code as input and returning web pages/HTML as output.

Prerequisites
Web server

Versions used
Apache HTTP server 2.2.8

Steps
  • Download the zip package from PHP website, http://www.php.net/downloads.php. The installer package as at version 5.2.6 doesn't seem to install correctly on Vista.
  • Create a folder C:\PHP
  • Unzip the contents of the zip package to this folder.
  • Add C:\php to the path system variable by going to Start → Settings → Control panel → System → Advanced System Settings → Advanced tab → Environment variables
  • Restart the computer
  • Make a copy of the file php.ini-recommended in C:\PHP and rename the copy as php.ini
  • Update the doc_root directive in php.ini file to the DocumentRoot path set in the Apache httpd.conf file e.g. doc_root = C:\Apache\htdocs
  • Update the extension_dir directive in php.ini file to extension_dir = C:\PHP\ext
  • Update the display_errors directive in php.ini file to display_errors = on
  • Update the date.timezone directive in php.ini e.g. date.timezone = Africa/Nairobi
  • Update the Apache httpd.conf file by adding the following lines
LoadModule php5_module "C:/php/php5apache2_2.dll" 
AddType application/x-httpd-php .php 
PHPIniDir "C:/php"
  • Add index.php to the DirectoryIndex setting of the httpd.conf file i.e. DirectoryIndex index.html index.php
  • Restart Apache by going to Start → Programs → Apache HTTP Server 2.2 → Control Apache Server → Restart
  • Create a text file named test.php in the Apache document root path. Add the following to the file. <?php phpinfo(); ?>
  • Open your browser and navigate to http://localhost/test.php. If a page with PHP configuration information is displayed, the PHP installation and configuration was successful.

Installing Subversion 1.5.3

Subversion [svn] is a version control system. A class of applications also referred to as Software Configuration Management (SCM).

Prerequisites
None

Versions used
VisualSVN 1.5.3
TortoiseSVN 1.5.3

Steps
  1. Download visualsvn server from the visualsvn website. This is a free GUI for the subversion server. It also comes with svn command line tools e.g. svn.exe
  2. Download tortoisesvn from the tortoisesvn website. This is a free GUI client for subversion. It is a windows explorer extension
  3. Install tortoisesvn. This will require a restart
  4. Install visualsvn server. You can specify to use http on port 81 for connections using a browser. Identify the folder where the svn server will keep it's database. Use svn authentication and create a user and password.

Using Subversion

Creating a repository
  • Create a new repository using visualsvn server

  • In windows explorer, right click on the root directory of items you want to add to source control and select TortoiseSVN > Import.Type the path to parent folder to create in your repository e.g. file:///C:/svn/myrepository/myfolder [don't specify the repository root path e.g. svn/myrepository]. Click on OK. This will add all files and subfolders under that folder to source control.
  • Right click on the folder again and click on SVN Checkout. Note the repository URL and checkout directory and amend as appropriate.
  • Right click on the folder and select SVN Commit

  • To add only selected files or folders, in windows explorer, from any directory, right click and select TortoiseSVN > Repo-browser. Type the URL of the repository just created.
  • Create the directory structure you want for your repository structure. Right-click in the left pane of the repository browser and select Create folder as appropriate.
  • Click on OK to close the repository browser.
  • Right click on the folder to use as the working directory and select SVN Checkout.
  • Set the repository URL e.g. file:///C:/svn/myrepository/myfolder and the checkout directory and click on OK
  • Accept any warning message that the target folder is not empty. Already existing files in the folder won't be deleted
  • To add files to source control, right click on the file and select TortoiseSVN > Add. Click on OK to mark the file for adding to source control.
  • When finished adding files, right click on the working folder and select SVN Commit. Click on OK to add the files to source control.
  • Items are now under version control

Daily use
  • To check in items, right click and select SVN Commit
  • To check out, no special action is needed. SVN by default uses an update-and-merge technique rather than lock-update-check-in, so that multiple people can update the same file at the same time. If you require to lock the file so that you are the only one who can modify it, right click and select TortoiseSVN > Get lock. Use TortoiseSVN > Release lock when finished.
  • To get latest, right click and select SVN update
  • To remove a file from version control, shift+right click on the file while viewing it from the right hand pane of windows explorer and choose the TortoiseSVN > Delete (keep local) menu. This will mark it for deletion from svn but keep the local copy. Right click on the file and select SVN Commit to effect the change. The normal TortoiseSVN > Delete command will immediately delete a file from the working folder. For a folder, it's marked for deletion and actually deleted when you commit. You can get a file back by browsing the repository and copying it from a previous revision, before it was deleted. Use TortoiseSVN > Revert to unmark an item previously marked for deletion.
  • To remove a folder from version control, right-drag the working copy onto itself. Windows explorer doesn't allow dragging items onto themselves in the same window though, so you must either use a second explorer window or drag onto the tree view. On the pop-up menu provided select SVN Export to here and accept to unversion the folder.
  • To export a folder, such that a copy is made to a different location without the .svn directories, right click on the folder and select TortoiseSVN > Export.
  • To create a tag or branch, right click on the folder, select TortoiseSVN > Branch/tag and in the To path: section, enter the path of the new branch/tag e.g. /tags/version2.2. The base directory e.g. tags in this case, needs to exist already.

Right-drag
In Windows explorer, it is possible to move/copy files not only by dragging them with the left mouse button, but also with the right mouse button. The difference is that the left-drag executes the operation immediately, and the right-drag will first show you a context menu where you can choose the operation. TortoiseSVN adds some of its commands to that right-drag context menu.

To add the files/folders to the working copy, right-drag unversioned files/folders on to a versioned folder and select SVN Add files to this WC.

To export items, right-drag versioned files/folders from your working copy to an unversioned folder and select one of these two commands
  • SVN Export to here: exports the dragged files/folders to the target location, i.e. creates a copy without the .svn folders
  • SVN Export all to here: exports the dragged files/folders to the target location, including unversioned files.

To move and copy your files and folders around in your working copy, right-drag files/folders inside your working copy and select one of these four commands
  • SVN Move versioned files here: moves the files to the drop location. If a file already exists, it will ask you what to do (rename the file, overwrite it or cancel).
  • SVN Move and rename versioned files here: the same as 'SVN Move versioned files here', except that it will ask for a new name for every dropped file.
  • SVN Copy versioned files here: the same as 'SVN Move versioned files here' but it leaves the original file, i.e. the file is copied not moved
  • SVN Copy and rename versioned files here: the same as 'SVN Move and rename versioned files here' but it leaves the original file, i.e. the files are copied not moved

Installing JasperServer 3.7.0

JasperServer is at the heart of the Jaspersoft BI suite, enabling management and distribution of reports.

Prerequisites
JRE [installer comes with bundled JRE]
Tomcat [installer comes with bundled tomcat]
MySQL [installer comes with bundled MySQL]

Versions used
JRE 1.6.0_18
Tomcat 6.0.20
MySQL 5.0.45

Steps
  1. Download the jasperserver installer package from sourceforge, http://sourceforge.net/projects/jasperserver/files/JasperServer/
  2. Make sure the mysql service is running
  3. If you have a databases on your server named sugarcrm or foodmart, export them to different databases. The sample databases that come with jasperserver are stored  in these databases
  4. Run the installer
  5. Install to C:\jasperserver-ce-3.7.0
  6. Specify to use an existing tomcat and an existing mysql
  7. Start tomcat
  8. Navigate to localhost:8080/jasperserver
  9. Login with the username/password combination of jasperadmin/jasperadmin
  10. Installation is complete

Note that the installer package comes bundled with iReport which is a report designer for jasperreports. You need not download iReport separately.

If you require to use a different application server or DBMS, download the WAR package rather than the installer. Download the jasperserver-ce-install-guide.pdf for instructions on how to set it up.

Installing TikiWiki 4.2

TikiWiki is an open source web-based application that does many things including collaboration and bug tracking.

Prerequisites
Web server configured to process PHP pages
PHP
MySQL

Versions used
PHP 5.2.8
Apache 2.2.8
MySQL 5.0.45

Steps
  • Download the zip package from the tiki website, tikiwiki.org
  • Unzip the zip file to the webserver document root e.g c:\apache\htdocs
  • Rename the folder created e.g. to tiki
  • Open a command prompt window
  • Type mysql -h localhost -u root -p [assuming mysql is in the system path]
  • Enter the root password
mysql> create database tiki;
mysql> grant all on tiki.* to tiki@localhost identified by “tiki”;
mysql>quit
  • Using a browser, navigate to localhost/tiki
  • Follow the instructions of the installer

Installing Palo BI Suite 3.0

Palo is an open source MOLAP server. It's primarily designed to work with excel, coming with an excel add-in. There's also an openoffice extension, palo for openoffice add-in, that can be used for integration with openoffice calc.

Prerequisites
.NET framework 2.0
MS Excel or OpenOffice calc

Versions used
Palo 3.0
OpenOffice calc 3.1.0
Palo for openoffice addin 2.0.1

Steps
  1. Download the Palo BI suite from the palo website, jedox.com
  2. Run the setup file
  3. Install to c:\Palo
  4. Download the palo for openoffice addin from http://extensions.services.openoffice.org/en/project/PaloOpenOffice
  5. Start openoffice calc
  6. Select the Tools | Extension manager menu
  7. Click on the Add button and select the PalOOCa.oxt file downloaded.
  8. Close calc after the addin has installed
  9. Open calc
  10. You'll have a new menu in the menu bar named Palo
  11. Click on the Palo | Modeler menuClick on the edit connection icon to edit the details for the localhost connection. Set the port to 7921, username to admin and password to admin.
  12. Click on the connect to server icon to connect to the server on localhost.

To connect to a mondrian schema, and use OpenOffice to browse mondrian olap cubes,
  • Click on the Palo | Modeler menu
  • Click on the create connection icon. Fill the following for the host and port, or similar values corresponding to the mondrian server setup
Host – localhost:8080
Port – mondrian/xmla
  • Click on the connect to server icon to connect to the mondrian server
  • Click on close once the connection is established
  • Click on the Palo | Insert View menu
  • Select the appropriate database and dimensions to view then click on the create button

Upgrading Mantis

Based on the upgrading section of the mantis manual.
http://www.mantisbt.org/manual/manual.installation.upgrading.php

Versions used
Upgrade from 1.1.6 to 1.2.0

Steps
  1. Backup the mantis database
  2. Backup the mantis directory on the web server
  3. Rename the file c:\apache\htdocs\mantis\mantis_offline.php.sample to mantis_offline.php
  4. Unzip the latest mantis package to a temporary folder e.g. C:\temp
  5. Copy the contents of c:\temp\mantisbt-1.2.0 to the web server e.g. C:\apache\htdocs\mantis, replacing all existing files and folders
  6. Delete the file c:\apache\htdocs\mantis\core\my_view_inc.php
  7. Using a browser, navigate to localhost/mantis/admin/install.php
  8. Supply a database username and password to use to upgrade the database
  9. Click on the install/upgrade database button
  10. Installation should be successful
  11. Confirm the installation status by navigating to localhost/mantis/admin/check.php
  12. Delete the file c:\apache\htdocs\mantis\mantis_offline.php. It may be appropriate to delete all old files from the previous version (those which haven't been replaced because they no longer exist in the new version)
  13. Delete the folder c:\apache\htdocs\mantis\admin
  14. Using a browser, navigate to localhost/mantis to log in and use the latest version of mantis

Tuesday, March 23, 2010

Installing SquirrelMail 1.4.20 RC2

SquirrelMail is an application that provides web-based access to POP or IMAP email servers.

Prerequsites
Web server enabled to server php pages
PHP
Email server
Perl

Versions used
Apache 2.2.8
PHP 5.2.8
Hmailserver 5.3
Perl 2.6

Steps
  1. Download the zip package from the website, squirrelmail.org
  2. Extract the zip file to a temporary location e.g. C:\temp
  3. Create a new folder named squirrelmail in the webroot folder of the web server e.g. In c:\apache\htdocs
  4. Copy the contents of c:\temp\squirrelmail-1.4.20-RC2 to c:\apache\htdocs\squirrelmail
  5. Create the folders c:\squirrelmail\data and c:\squirrelmail\attachments
  6. Open a command prompt window and change directory to c:\apache\htdocs\squirrelmail\config
  7. Type perl conf.pl
  8. A menu driven command interface is displayed
  9. Review the different options. Modify the server settings [domain name, imap server, smtp server [use IP e.g. 127.0.0.1 for the last two]] and general settings [data directory, attachment directory. Set them to the directories created above]
  10. Save data when finished and quit
  11. This will create a new file, c:\apache\htdocs\squirrelmail\config\config.php with the appropriate config information
  12. Using a browser, navigate to localhost/squirrelmail
  13. To log in, use the credentials of an available email account on the configured mail server

Installing Roundcube 0.3.1

Roundcube is an application that provides web-based access to IMAP email servers.

Prerequisites
Web server with php configured
PHP
Email server
RDBMS

Versions used
Apache 2.2.8
PHP 5.2.8
Hmailserver 5.3
MySQL 5.0.45

Steps
  • Download the roundcube tar.gzip package from the roundcube website, roundcube.net
  • Unzip the zip and tar to a temporary location e.g. C:\temp
  • Create a new folder named roundcubemail in the webroot folder of the web server e.g. In c:\apache\htdocs
  • copy the contents of c:\temp\roundcubemail-0.3.1 to c:\apache\htdocs\roundcubemail
  • Open a command prompt window
  • Type mysql -u root -p
  • Enter the password for the mysql root user
mysql> create database roundcube;
mysql> grant all privileges on roundcube.* to roundcube@localhost identified by "roundcube";
> quit
  • Using a browser, navigate to localhost/roundcubemail/installer/
  • Click on the start installation button
  • Click on the next button
  • Disable spell checking, set details for the database and IMAP server
  • Click on the create config button
  • Create a new file named main.inc.php in c:\apache\htdocs\roundcubemail\config and paste the contents of the main.inc.php section to it
  • Edit this file, main.inc.php, setting the smtp authentication details [for servers that require smtp authentication].
// SMTP username (if required) if you use %u as the username RoundCube
// will use the current username for login
$rcmail_config['smtp_user'] = 'valid user account';

// SMTP password (if required) if you use %p as the password RoundCube
// will use the current user's password for login
$rcmail_config['smtp_pass'] = 'valid password';

// SMTP AUTH type (DIGEST-MD5, CRAM-MD5, LOGIN, PLAIN or empty to use
// best server supported one)
$rcmail_config['smtp_auth_type'] = 'LOGIN';
  • Create a new file named db.inc.php in c:\apache\htdocs\roundcubemail\config and paste the contents of the db.inc.php section to it
  • Click on the continue button
  • Click on the initialize database button
  • Fill in appropriate details for testing smtp settings and click on the send test mail button
  • Fill in appropriate details for testing the imap configuration and click on the check login button
  • Open a new browser window and navigate to localhost/roundcubemail
  • Log in with details of a valid email account

Installing phpMyAdmin 3.1.1

phpMyAdmin is a web-based application used to administer MySQL databases.

Prerequisites
Web server configured to process PHP pages
PHP

Versions used
PHP 5.2.8
Apache  2.2.8

Steps
  1. Download the package from the phpMyAdmin website, phpmyadmin.net.
  2. Unzip the package to a folder in the web server's document root, e.g. phpMyAdmin
  3. Create a folder named config in this home folder
  4. Navigate to http://yourserver/phpmyadmin/setup/ to set up the config.inc.php file in the config folder
  5. Click on the New Server to specify the details of the MySQL server to administer. Specify the hostname for the server. Other options can be left as default. Save these options.
  6. Save the new configuration
  7. Edit the newly created file ./config/config.inc.php. Change the blowfish_secret value to a different string that must contain numbers, letters and special characters e.g. my2blowfish$ecret.
  8. Move the file to the root of the phpmyadmin directory.
  9. The installation is complete and you can navigate to yourwebserver/phpmyadmin to start using it. Login with a valid MySQL login to the server configured for administration.
  10. If you are upgrading your installation, copy the config.inc.php file to your new installation/folder.

Installing Mantis 1.1.6

Mantis is an open source web-based bug tracking system.

Prerequisites
Web server configured to process PHP pages
PHP
MySQL

Versions used
PHP – 5.2.8
Apache -  2.2.8
Mantis – 1.1.6

Steps

  • Download the package from the mantis website, mantisbt.org
  • Unzip the package to a folder in the web server's document root, e.g. Mantis
  • Create a database on the MySQL server e.g.
mysql> create database mantis;
mysql> grant all on mantis.* to mantis@localhost identified by 'password';
mysql> flush privileges;
mysql> quit;
  • Open your browser and navigate to yourserver/mantis/admin/install.php. This will run the installer. Provide database connection details
  • Installation is complete. Login as the admin user with the username administrator and password root. Change the default password once logged in.
  • Delete the \mantis\admin folder so that install scripts are not re-run by mistake.
Configuring mantis
To configure mantis e.g. to send email, edit the c:\apache\mantis\config_inc.php file. Add the following.
// Setting up the mail 
    $g_phpMailer_method='2'; //smtp
    $g_smtp_host='smtp.server.address';

//for smtp authentication. If not required, set to ''
    $g_smtp_username='email address';
    $g_smtp_password='password';

// Configure email addresses system 
        $g_administrator_email='email address';
        $g_webmaster_email=$g_administrator_email; //shown on mantis pages
        $g_from_email='email address'; //from address for emails generated by mantis
$g_from_name= 'Mantis Bug Tracker';
    $g_return_path_email='email address';

// Setting the language and date format
        $g_default_language='english';
        $g_short_date_format='d-m-Y';
        $g_normal_date_format='d-m-Y H:i';
        $g_complete_date_format='d-m-Y H:i';
// Customizing Mantis
        $g_window_title='My Mantis';