phpBB with MySQL

Recently I was looking to set up a forum. Plenty of them listed here in wikipedia. I went with phpBB.

Atfirst, the Quick Start guide seemed little lengthy. But once started reading, I was able to setup and run the forum in minutes though there was a significant problem I faced while installation. It was setting up the database. I used MySQL with the default storage engine as INNODB. The phpBB installation schema for MySQL created errors if I use the INNODB engine. Tables were not created.

After doing some research, I had to change the storage engine to MYISAM. Then the installation went through and the forum started running good. So if you need to install phpBB with MySQL then you might need to change the storage engine to MYISAM.

If you want the MySQL to use MYISAM all the time, you can set the storage engine in the my.ini file. Command might look like below.

default-storage-engine=MYISAM

Otherwise you can choose to use the MYISAM just for the phpBB installation. And once the installation is over, you can start using the INNODB engine. For this, you can edit the MySQL schema files bundled with phpBB and add the below line as the first line of the schema.

SET storage_engine=MYISAM;

This way the tables will be created without any problem. But I'm not sure if this solves the problem completely. Will post my findings.

Multi database connections

I was using JGrinder as ORM tool for quite some time. Till now, I connected to a single database using JGrinder. And I believed that the JGrinder can connect only one database per JVM. Recently I got a requirement to connect multiple databases in a single JVM. I was not sure it would be possible to do it in JGrinder. So dug it little deeper. Main draw back with JGrinder is that there are not much help documents or forums to talk about it. But after looking at little deeper, I found a way to connect to more than one database in a single JVM using JGrinder. Below is a brief explanation followed by the source code I wrote for it.

JGrinder uses one broker object for each database connection. By default when the following code is executed, a new broker is created with the database details mentioned in the 'fileName' argument.

com.objectwave.appSupport.StartupRoutine.loadDefaults(caller, fileName);

The new broker created will be the default broker by the JGrinder. If more databases need to connected on the fly, broker for each database need to be created and added to the BrokerFactory of JGrinder. When adding the additional brokers, it is necessary to pass a brooker name / tag for the broker, which can be used to identify the broker later to connect to the desired database. The way I used to add additional brokers, is by reading the additional jdbc.ini files. Below is the source code (IniLoader.java) I wrote to load multiple jdbc.ini files. I hope the source code can explain the rest. You can use and distribute this source for free, as long as you abide by the JGrinder licensing terms for its usage.

/*
 * ========================================================================
 *                                   IniLoader
 * ========================================================================
 *
 */

package com.pinetree.utils.io;

import com.objectwave.persist.BrokerFactory;
import com.objectwave.persist.broker.RDBBroker;
import com.objectwave.persist.properties.BrokerPropertyDetail;
import com.objectwave.persist.properties.BrokerPropertySource;
import java.io.BufferedInputStream;
import java.io.InputStream;
import java.util.Properties;

/**
 * IniLoader is used to load multiple jdbc.ini files into the BrokerFactory of
 * JGrinder. Loading multiple jdbc.ini files will allow the developer to use
 * any of the database defined in the loaded jdbc.ini files from the same JVM.
 *
 * Here is the sample usage of loading multiple jdbc.ini files.
 * 
 * IniLoader.addDefaultIni(this, "dev_ecpis_jdbc.ini");
 * IniLoader.addIni(this, "epics_jdbc.ini", "epics");
 * 
 * Here is an example of connecting to the default database
 * 
 * try {
 *      ApplicationContainer ac = new ApplicationContainer();
 *      ac.setAppNo(1234);
 *      SQLQuery sqlQuery = new SQLQuery(ac);
 *      Vector  resVec = sqlQuery.find();
 *      if (resVec != null) {
 *          ac = resVec.get(0);
 *          System.out.println("Insured Name : " + ac.getInsuredName());
 *      }
 * } catch (Exception e) {
 *      e.printStackTrace();
 * }
 * 
 * Here is an example of connecting to a database other than the default one.
 * All needs to be done is to set the broker name to the tag used to add the
 * corresponding jdbc.ini file. For example we added epics_jdbc.ini file with a
 * epics tag. So to connect to the epics database, it is necessary to set the
 * broker name to "epics", as below, before querying it using the SQLQuery object.
 * 
 * try {
 *      ApplicationContainer ac = new ApplicationContainer();
 *      ac.setAppNo(1234);
 *      ac.setBrokerName("epics");
 *      SQLQuery sqlQuery = new SQLQuery(ac);
 *      Vector  resVec = sqlQuery.find();
 *      if (resVec != null) {
 *          ac = resVec.get(0);
 *          System.out.println("Insured Name : " + ac.getInsuredName());
 *      }
 * } catch (Exception e) {
 *      e.printStackTrace();
 * }
 * 
 *
 * @author jerald
 */

public final class IniLoader {

    /**
     * loads an jdbc.ini file. Any read or write done on the container without
     * setting the broker name will use this jdbc.ini file details to connect
     * to the database.
     *
     * @param caller reference of the object that called this method
     * @param fileName name of the jdbc.ini file
     *
     */

    public static void addDefaultIni(Object caller, String fileName) {
        com.objectwave.appSupport.StartupRoutine.loadDefaults(caller, fileName);
        BrokerFactory.useDatabase();
    }

    /**
     * loads an jdbc.ini file specified in the arguement with the tag name as
     * passed in the argument. When the broker name of a container is set to
     * the tag passed in this argument, any read or write done on the container
     * will happen on the database defined in the jdbc.ini file as passed in the
     * argument.
     *
     * @param caller reference of the object that called this method
     * @param fileName name of the jdbc.ini file
     * @param tag tag that to be associated with the jdbc.ini file
     */

    public static void addIni(Object caller, String fileName, String tag) {

        final String URL_TOKEN = "ow.connectUrl";
        final String USERNAME_TOKEN = "ow.persistUser";
        final String PASSWORD_TOKEN = "ow.persistPassword";
        final String DRIVERNAME_TOKEN = "ow.persistDriver";
        final String DBIMPL_TOKEN = "ow.databaseImpl";

        if (tag == null) tag = fileName;

        InputStream inputStream = null;
        Properties props = null;
        try {
            inputStream = ClassLoader.getSystemResourceAsStream(fileName);
        } catch (Exception  e) {}

        if (inputStream == null) {
            if (caller instanceof Class) {
                inputStream = ((Class) caller).getResourceAsStream(fileName);
            } else {
                inputStream = caller.getClass().getResourceAsStream(fileName);
            }
        }

        try {
            if (inputStream != null) {
                BufferedInputStream bis = new BufferedInputStream(inputStream);
                props = new Properties();
                props.load(bis);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        if (props != null) {
            String connectUrl = props.getProperty(URL_TOKEN);
            String userName = props.getProperty(USERNAME_TOKEN);
            String password = props.getProperty(PASSWORD_TOKEN);
            String driverName = props.getProperty(DRIVERNAME_TOKEN);
            String dbImpl = props.getProperty(DBIMPL_TOKEN);

            if (connectUrl != null && userName != null &&
                    password != null && driverName != null && dbImpl != null) {
                try {
                    RDBBroker broker  = new RDBBroker();
                    BrokerPropertyDetail bpd = new BrokerPropertyDetail();

                    bpd.setConnectUrl(connectUrl);
                    bpd.setPersistUser(userName);
                    bpd.setPersistPassword(password);
                    bpd.setPersistDriver(driverName);
                    bpd.setDatabaseImpl(dbImpl);
                    BrokerPropertySource bps = new BrokerPropertySource(bpd);
                    broker.setBrokerPropertySource(bps);
                    broker.initialize();
                    BrokerFactory.addStaticBroker(tag, broker);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }

    }

}