Creating the Quartz Database Structure

JobStores based on JDBC require a database for the Scheduler information to be persisted. Quartz requires 12 database tables to be created. The table names and description are listed in Table 6.1.

Table 6.1. Quartz Requires the Following Tables for All JDBC-persistent JobStores

Table Name	Description
QRTZ_CALENDARS	Stores Quartz calendar information as blobs
QRTZ_CRON_TRIGGERS	Stores cron triggers, including cron expression and time zone information
QRTZ_FIRED_TRIGGERS	Stores status information relating to triggers that have fired and the relevant execution information about the related job.
QRTZ_PAUSED_TRIGGER_GRPS	Stores the trigger groups that have been paused
QRTZ_SCHEDULER_STATE	Stores a few pieces of information about the state of the Scheduler and other Scheduler instances (if used within a cluster)
QRTZ_LOCKS	Stores pessimistic lock information for the application (if pessimistic locking is used)
QRTZ_JOB_DETAILS	Stores detailed information for every configured Job
QRTZ_JOB_LISTENERS	Stores information about configured JobListeners
QRTZ_SIMPLE_TRIGGERS	Stores simple triggers, including repeat count, internal, and number of times triggered
QRTZ_BLOB_TRIGGERS	Triggers stores as blobs (this is used when Quartz users create their own custom trigger typeswith JDBC, JobStore does not have specific knowledge about how to store instances)
QRTZ_TRIGGER_LISTENERS	Stores information about configured triggerListeners
QRTZ_TRIGGERS	Stores information about configured triggers

In Table 6.1, all tables start with the prefix QRTZ_. This is the default, but you can change it by providing an alternate prefix in the quartz.properties file. Changing the prefix is necessary if you use multiple table sets for different Scheduler instances. This would be the case if you need multiple nonclustered schedulers but want to use a single database instance.

Installing the Quartz Database Tables

Quartz includes SQL scripts for all the supported database platforms. You can find the SQL scripts in the /docs/dbTables directory, where is the directory where you unzipped the Quartz distribution.

About 18 different database platform scripts are available. This should cover just about any database that you can come up with. If yours is not included, you can use one of the existing ones and modify it for your database platform.

To install the required database tables, open the .sql file that is specifically designed for your database platform and run the commands using your preferred query tool. With MS SQL Server, for example, you need to run the commands in the file tables_sqlServer.sql using the Query Analyzer tool that comes with the database. With certain databases (including MS SQL Server), you need to create an empty database before running the commands. The SQL commands don't include creating the database. You should also pay careful attention to any notes at the top of the SQL file. Usually, you must follow a few instructions before executing the commands. For example, with the MS SQL Server SQL file, you need to modify this command at the top of the file with the name that you gave the empty database when you created it:

USE [enter_db_name_here]

The SQL files create the necessary table structures and also put some basic constraints and indexes on the tables. Later in this chapter, we talk about how you can improve performance by making some additional changes to the structure.