Configuring jBilling for various databases

jBilling, by default in the binary distribution, runs on Hypersonic, a simple database written entirely on Java. This is pretty good as an example, but if you plan to be running jBilling in a more demanding environment, or you simply are more comfortable running other database brand, then you have to follow a few simple steps to configure jBilling:

jBilling has been designed and built to be database vendor agnostic: it doesn't matter what database engine you run it on. This is done by using very standard SQL statements combined with Hibernate. In any case, you still need to do two things:

1. Tell Hibernate what kind of database it has to work with,

2. Initialize you database with the jBilling schema. This is the tables, indexes, foreign key constraints and the initial data.

Let's start with the first point:

1. Go to jbilling\conf directory . Edit the file jbilling-database.xml and enter the database connection information; typically server name, instance name, user name, password ...

2. In this same file (jbilling-database.xml), change the 'dialect' property. It is by default set to use Hypersonic, you need to change that. There are some popular values as comments int he file. You can find all possible values here.

3. Make sure you have the JDBC driver needed for your database available for the application server (Tomcat). This is done by copying the jar file to jbilling\lib. In that directory you'll see that there is already the jar file for Hypersonic. Beside this driver, jBilling doesn't come with any other JDBC driver. You'll have to find the right driver for your database from your database vendor.

Now the database initialization. Of course, you need to have your database up and running, with an instance for jBilling already created.

1. Go to jbilling\resources\db-samples. You'll find a set of files named 'jbilling-schema-*.sql'. There is one of these files per supported database engine. If you open any of these text files you will see a series of DDL commands to create the jBilling schema. So far so good. The problem is that after the creation of the tables, you have all the foreign keys as well. We need to separate these two parts and put them in different files. For example, for jbilling-schema-oracle.sql at about two thirds of the file you have the last table creation with its primary key, and then the first foreign key creation for the table 'ACH'. So we need all the foreign keys to be created in its own file, so we split this file in two with the tables/primary keys and indexes all remaining in jbilling-schema-oracle.sql and the foreign keys in a new file jbilling-schema-oracle2.sql. You can see as an example of this file splitting the files for hypersonic. There is the standard jbilling-schema-hypersonic.sql and then the split files jbilling-schema-hypersonic1.sql with the table definitions and jbilling-schema-hypersonic2.sql with the constrains

2. Now run jbilling-schema-oracle.sql using your database's client utility. Now we have all the tables created.

3. Now run jbilling-data.sql. Now we have the tables populated with the initialization data.

4. Now run jbilling-schema-oracle2.sql. The foreign keys are now there too. You are done, start-up jBilling and create your first company.

Database compatibility

There are many factors to consider when selecting which database engine to use for a critical application such a billing system. You need performance, scalability and security. Although jBilling will run on any of these engines, it doesn't mean you can switch from one to another one easily. This is mostly a one-time decision you have to make before installing jBilling. Once you are running in one of them, switching can be hard.

jBilling has not been tested equally in all the supported engines. By 'supported' I mean that you have the initialization files to get jBilling running on that engine. It doesn't mean that we have fully tested or that we give any kind of guarantee on that engine's performance.

This is the current status for all these database engines. We need your help to keep this chart updated. Let us know of your experience in any of these engines (either good or bad). For additional notes on a particular engine, see the bottom of this page. In some cases, following these notes is key to get jBilling running.

References:

Red: I am not aware of production installations using this engine. Please let me know if you do.

Yellow: I do know that some are running jBilling in this engine, but not for long enough and in a big enough context to be fully confident.

Green: Thoroughly tested including demanding performance scenarios.

MySQL

There is an additional parameter for MySQL so it returns NULL for null values, instead of 0: zeroDateTimeBehavior=convertToNull
Follow this syntax for the JDBC url to include the above parameter: jdbc:mysql://[host:port],[host:port].../[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]... The database schema scripts create tables using the 'TIMESTAMP' data type of MySQL. This data type has some default behavior that will cause trouble with jBilling, since jBilling needs to be able to have a NULL values.
Instead of TIMESTAMP, we need those fields to be DATETIME. There are two ways to fix this.
Use MAXDB mode.
According to the MySQL documentation, if you create the schema while in 'MaxDB' mode all the TIMESTAPS will be considered 'DATETIME' types.
To enable MAXDB mode, set the server SQL mode to MAXDB at startup using the --sql-mode=MAXDB server option or by setting the global sql_mode variable at runtime:
mysql> SET GLOBAL sql_mode=MAXDB;

Changing the jbilling-schema-mysql.sql script
Simple edit the file, and do a global search and replace, to convert all those TIMESTAMP to DATETIME.

Oracle

The data to initialize the database includes '&' characters, which is a reserved symbol for sqlplus. Use 'set define off' to skip prompting for '&' values.