Get jBilling

Database Guide

Database Guide

By default, the jBilling binary distribution runs on Hypersonic, a simple database written entirely in Java. Hypersonic provides a good example of a fully functional jBilling installation, but if you're planning to run a more demanding environment, you'll want to configure jBilling to use a different database vendor.

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 standard ANSI SQL statements combined with Hibernate's SQL dialect feature.

To configure jBilling to work with a different vendor, you'll need to do two things:

  1. Tell Hibernate what kind of database it has to work with.
  2. Initialize a new database with the jBilling schema (tables, indexes, keys, and base data).

Configuring Hibernate

In your jBilling-community-3.x/jbilling/ directory you'll find the jBilling-DataSource.groovy configuration file. Edit this file and enter in your database connection information. Typically you'll need the user name, password, server host name, and database instance name.

The exact syntax of the URL property depends on the JDBC driver and the vendor you obtained it from. Please refer to your vendors JDBC driver's documentation for information on how to specify the JDBC connection URL to your database.

Also in the jBilling-DataSource.groovy file, you'll find the Hibernate dialect property. The dialect tells Hibernate how to talk to your database, as well as the supported statements and syntax that are allowed. By default, jBilling is configured to use the Hypersonic dialect, 'org.hibernate.dialect.HSQLDialect.' You'll need to change the dialect to a value that matches your database vendor (see the Hibernate Core documentation for a complete list).

jbilling-DataSource.groovy
    dialect = "org.hibernate.dialect.PostgreSQLDialect"
    driverClassName = "org.postgresql.Driver"
    username = "jbilling"
    password = ""
    url = "jdbc:postgresql://localhost:5432/jbilling_test"

Last but not least, you'll need to provide the JDBC driver .jar file to the application server (Tomcat 6). This is done by copying the jar file to the lib/ folder. JBilling 3.x already comes packaged with drivers for Hypersonic and PostgreSQL; for other database vendors, you'll need to get the driver file from the particular vendor you choose.

Database Initialization

To initialize your database, you must run one of the scripts in the jbilling-community-3.x/jbilling/resources/db-samples/ folder. This of course, assumes that you have your database up and running with a user and target database instance created.

In the db-samples/ folder, you'll find a set of files named jbilling-schema*.sql and another file named jbilling-data.sql. There is one schema creation script per supported database engine. If you open any of these files in a text editor you'll see a series of DDL (data definition language) commands that will create the jBilling schema, as well as all the indexes, foreign keys, and constraints.

jBilling's base data resides in the jbilling-data.sql file and must be inserted after the schema has been created, but before all the foreign keys and constraints are created. To get this to work we'll need to separate the schema creation from the constraint creation statements and run them separately.

Open up the file jbilling-schema-postgresql.sql and look for the first foreign key constraint statement for the ACH table. This statement should appear about two thirds of the way into the schema creation script. Take all of the statements below (and including) the ACH constraints and cut-and-paste them into a new file named jbilling-schema-postgresql-constraints.sql.

Now that you have separated the schema creation statements from the constraints, you can execute the scripts using your databases client utility. To initialize your database, run the scripts in the following order:

run jbilling-schema-postgresql.sql
run jbilling-data.sql
run jbilling-schema-postgresql-constraints.sql

When you're done initializing your database, start up jBilling and browse to http://localhost:8080/jbilling/signup to create your first company and administration user.

Database Compatibility

There are many factors to consider when selecting which database engine to use for a critical application such as a billing system. You'll need performance, scalability, and security. Although jBilling will run on a wide variety of engines, it doesn't mean you can switch from one to another easily. This is mostly a one-time decision you have to make before you install jBilling, but remember that once you're up and running on your chosen database, switching to another can be very difficult.

Please note that jBilling has not been equally tested on all supported database vendors and engines. We “support” all of the following vendors, but that only means that we provide initialization files to get jBilling up and running, not that we have fully tested the systems, or that we give any kind of guarantee of performance or stability.

We need your help to keep this chart updated. Let us know about your experience with any of these databases, whether good or bad. For additional notes on a particular engine, see the bottom of this page.

Database Vendor Compatibility Comments
DB2

 

Red jBilling's table names may exceed the maximum length supported by DB2.
Hypersonic

 

Red This is the default engine, but is meant only as an example. Hypersonic does not support “read-committed” transaction isolation and therefore reliably cannot handle concurrent access.
Microsoft SQL Server Yellow There are some companies using MS SQL Server with success. We are not very familiar with it here at jBilling, which makes it harder for us to evaluate its compatibility.
MySQL Yellow

There are large deployments of jBilling powered by this database, however MySQL does not support nested transactions. Failure of MySQL to fully rollback a nested transaction can leave jBilling in an inconsistent state after a crash or run-time exception.

For more information, please read the MySQL Database Guide.

Oracle Green

A great choice if budget is not an issue. Companies can easily scale up to meet growing needs.

PostgreSQL

 

Green Free, open-source and very stable. jBilling has been implemented using PostgreSQL with companies of all sizes without any issues.
Sybase Red  

 

Red = We are not aware of any production installations using this database.

Yellow = Some instances of jBilling are known to be running using this database, but not for long enough or in a big enough context for us to be fully confident of its compatibility. May also indicate some known issues with this database vendor (see comments above).

Green = Thoroughly tested, known to perform well in demanding scenarios.

Additional Notes

MySQL

For more information, please read the MySQL Database Guide.

Oracle

Allow use of "&" when loading jbilling-data.sql

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

sqlplus> set define off;
Use NUMBER(1,0) instead of BIT

Edit the jbilling-schema-oracle.sql schema script and change all instances of BIT to NUMBER(1,0)