search

quarkusio / quarkus

Quarkus: Supersonic Subatomic Java.
https://quarkus.io
Apache License 2.0
13.73k stars 2.67k forks source link

[Quartz][Oracle] Unable to store job details when using Quarkus Quartz Scheduler with Oracle #43720

Open dcdh opened 2 weeks ago

dcdh commented 2 weeks ago

Describe the bug

While doing a migration from an old application using quartz 2 (from my memory) to the last version of quarkus, I notice an issue when the quartz job is stored in the database.

It failed because in qrtz_job_details boolean likes IS_DURABLE is stored using one varchar (0 or 1) type and it expected 5 varchar type for TRUE or FALSE.

Expected behavior

Following the reproducer associated with this issue:

Actual behavior

The reproducer fails to start because at startup the quartz job definition is stored and an sql issue is thrown regarding varchar length too small to store boolean value representation.

How to Reproduce?

  1. git clone https://github.com/dcdh/oracle-quartz-reproducer.git
  2. run SampleTaskTest
  3. It will fail with this kind of stacktrace 
    Caused by: org.quartz.JobPersistenceException: Couldn't store job: ORA-12899: valeur trop grande pour la colonne "QUARKUS"."QRTZ_JOB_DETAILS"."IS_DURABLE" (réelle : 5, maximum : 1)

    FYI, the init script is coming from tables_oracle.sql provided by quartz dependency. I've just commented the delete and drop table blocs because it was failing at startup.

Output of uname -a or ver

Linux 2a02-8428-dff8-c601-234b-8c10-a3c4-2308.rev.sfr.net 6.10.10-200.fc40.x86_64 #1 SMP PREEMPT_DYNAMIC Thu Sep 12 18:26:09 UTC 2024 x86_64 GNU/Linux

Output of java -version

openjdk version "21.0.4" 2024-07-16 OpenJDK Runtime Environment (Red_Hat-21.0.4.0.7-2) (build 21.0.4+7) OpenJDK 64-Bit Server VM (Red_Hat-21.0.4.0.7-2) (build 21.0.4+7, mixed mode, sharing)

Quarkus version or git rev

3.15.1

Build tool (ie. output of mvnw --version or gradlew --version)

Apache Maven 3.9.6 (Red Hat 3.9.6-6)

Additional information

It failed to store the job because it expect a boolean column definition using a 5 varchar length to store TRUE or FALSE as string. The oracle table definition has not changed from many years and it used one varchar to store boolean value. We should keep this definition.

When using an oracle db-kind, the QuarkusStdJDBCDelegate is used. I guess this issue is coming from it. Maybe we should provide a custom implementation for oracle (which is not the case).

I guess to fix it, in QuartzProcressor the buildstep guessDriver should be updated from

    private String guessDriver(Optional<JdbcDataSourceBuildItem> jdbcDataSource) {
        if (!jdbcDataSource.isPresent()) {
            return QuarkusStdJDBCDelegate.class.getName();
        }

        String dataSourceKind = jdbcDataSource.get().getDbKind();
        if (DatabaseKind.isPostgreSQL(dataSourceKind)) {
            return QuarkusPostgreSQLDelegate.class.getName();
        }
        if (DatabaseKind.isH2(dataSourceKind)) {
            return QuarkusHSQLDBDelegate.class.getName();
        }
        if (DatabaseKind.isMsSQL(dataSourceKind)) {
            return QuarkusMSSQLDelegate.class.getName();
        }
        if (DatabaseKind.isDB2(dataSourceKind)) {
            return QuarkusDBv8Delegate.class.getName();
        }

        return QuarkusStdJDBCDelegate.class.getName();
    }

to

    private String guessDriver(Optional<JdbcDataSourceBuildItem> jdbcDataSource) {
        if (!jdbcDataSource.isPresent()) {
            return QuarkusStdJDBCDelegate.class.getName();
        }

        String dataSourceKind = jdbcDataSource.get().getDbKind();
        if (DatabaseKind.isPostgreSQL(dataSourceKind)) {
            return QuarkusPostgreSQLDelegate.class.getName();
        }
        if (DatabaseKind.isH2(dataSourceKind)) {
            return QuarkusHSQLDBDelegate.class.getName();
        }
        if (DatabaseKind.isMsSQL(dataSourceKind)) {
            return QuarkusMSSQLDelegate.class.getName();
        }
        if (DatabaseKind.isDB2(dataSourceKind)) {
            return QuarkusDBv8Delegate.class.getName();
        }
        if (DatabaseKind.isOracle(dataSourceKind)) {
            return QuarkusOracleDelegate.class.getName();
        }

        return QuarkusStdJDBCDelegate.class.getName();
    }

particular code

        if (DatabaseKind.isOracle(dataSourceKind)) {
            return QuarkusOracleDelegate.class.getName();
        }

with QuarkusOracleDelegate having the same beahvior than other QuarkusdatasourceKindDelegate

quarkus-bot[bot] commented 2 weeks ago

/cc @machi1990 (quartz), @manovotn (quartz,scheduler), @mkouba (quartz,scheduler)

manovotn commented 2 weeks ago

I suppose this is an issue on my end but as of now I am unable to run the reproducer - namely the docker image refuses to start 🤔

dcdh commented 2 weeks ago

@manovotn just run the test SampleTaskTest - I guess you've got an issue with oracle devservice ?

mkouba commented 2 weeks ago

I think that it makes sense to introduce the QuarkusOracleDelegate that extends org.quartz.impl.jdbcjobstore.oracle.OracleDelegate. Actually, I'm quite surprised we don't support it. It might be that (1) OracleDelegate is located in a separate package (org.quartz.impl.jdbcjobstore.oracle vs org.quartz.impl.jdbcjobstore used for other DB types) so we simply overlooked it and (2) there was no Oracle user yet?

@dcdh Would you care to send a pull request?

manovotn commented 2 weeks ago

@manovotn just run the test SampleTaskTest - I guess you've got an issue with oracle devservice ?

Well, the container is started with the test; I guess it's the devservice timing out, yea.

Anyway, I agree with Martin that this looks like we initially overlooked the oracle delegate. If you were to send a PR, we'd be happy to review it :)

dcdh commented 1 week ago

Ok, I will provide a PR tonight. Keep you in touch.

dcdh commented 1 week ago

I think that it makes sense to introduce the QuarkusOracleDelegate that extends org.quartz.impl.jdbcjobstore.oracle.OracleDelegate. Actually, I'm quite surprised we don't support it. It might be that (1) OracleDelegate is located in a separate package (org.quartz.impl.jdbcjobstore.oracle vs org.quartz.impl.jdbcjobstore used for other DB types) so we simply overlooked it and (2) there was no Oracle user yet?

@dcdh Would you care to send a pull request?

OracleDelegate is declared inside a subpackage of org.quartz.impl.jdbcjobstore : org.quartz.impl.jdbcjobstore.oracle

mkouba commented 1 week ago

I think that it makes sense to introduce the QuarkusOracleDelegate that extends org.quartz.impl.jdbcjobstore.oracle.OracleDelegate. Actually, I'm quite surprised we don't support it. It might be that (1) OracleDelegate is located in a separate package (org.quartz.impl.jdbcjobstore.oracle vs org.quartz.impl.jdbcjobstore used for other DB types) so we simply overlooked it and (2) there was no Oracle user yet? @dcdh Would you care to send a pull request?

OracleDelegate is declared inside a subpackage of org.quartz.impl.jdbcjobstore : org.quartz.impl.jdbcjobstore.oracle

Yes, that's what I meant when I said "org.quartz.impl.jdbcjobstore.oracle vs org.quartz.impl.jdbcjobstore"...

dcdh commented 1 week ago

I think that it makes sense to introduce the QuarkusOracleDelegate that extends org.quartz.impl.jdbcjobstore.oracle.OracleDelegate. Actually, I'm quite surprised we don't support it. It might be that (1) OracleDelegate is located in a separate package (org.quartz.impl.jdbcjobstore.oracle vs org.quartz.impl.jdbcjobstore used for other DB types) so we simply overlooked it and (2) there was no Oracle user yet? @dcdh Would you care to send a pull request?

OracleDelegate is declared inside a subpackage of org.quartz.impl.jdbcjobstore : org.quartz.impl.jdbcjobstore.oracle

Yes, that's what I meant when I said "org.quartz.impl.jdbcjobstore.oracle vs org.quartz.impl.jdbcjobstore"...

Sorry Martin, monday is hard today

mkouba commented 1 week ago

I think that it makes sense to introduce the QuarkusOracleDelegate that extends org.quartz.impl.jdbcjobstore.oracle.OracleDelegate. Actually, I'm quite surprised we don't support it. It might be that (1) OracleDelegate is located in a separate package (org.quartz.impl.jdbcjobstore.oracle vs org.quartz.impl.jdbcjobstore used for other DB types) so we simply overlooked it and (2) there was no Oracle user yet? @dcdh Would you care to send a pull request?

OracleDelegate is declared inside a subpackage of org.quartz.impl.jdbcjobstore : org.quartz.impl.jdbcjobstore.oracle

Yes, that's what I meant when I said "org.quartz.impl.jdbcjobstore.oracle vs org.quartz.impl.jdbcjobstore"...

Sorry Martin, monday is hard today

No problem. It's the same for me! :D

dcdh commented 1 week ago

I made the changes, but I do not thinks it is the root cause no matter the use of QuarkusStdJDBCDelegate or QuarkusOracleDelegate.

But, the new way Oracle is handling boolean representation between Oracle23 and version below.

In my reproducer using the default oracle devservice gvenzl/oracle-free:23-slim-faststart will fail for both delegates meanwhile using this version gvenzl/oracle-xe:21-slim-faststart will work on both delegates.

I am unable to find a documentation related on Boolean new type and breaking changes :/

Could you validate it on your side too, to validate what I am saying.

That mind complex ... what should we do ?

dcdh commented 1 week ago

Ok I found it from the release note available here https://www.oracle.com/fr/database/technologies/appdev/jdbc-downloads.html for Oracle Database 23ai (23.5.0.24.07) JDBC Driver & UCP Downloads

https://download.oracle.com/otn-pub/otn_software/jdbc/23c/JDBC-UCP-ReleaseNotes-23ai.txt?AuthParam=1728332561_fe1f349d3bea85fc969e271fc9fc098b

- New connection property sendBooleanAsNativeBoolean to restore the old behavior:
  JDBC 23.4 provides a compatibility property "oracle.jdbc.sendBooleanAsNativeBoolean", 
  when set to false (the default is true), will restore the old behavior of sending 
  integer values (0/1) for boolean data type.

The worst part of this sentence is the fact that it will not check the database type used to store the boolean ... breaking change :/

dcdh commented 1 week ago

Multiple axes of resolution:

  1. ask for Quartz maintainer to update the script for a specific version of oracle by replacing VARCHAR2(1) to BOOLEAN;
  2. use VARCHAR2(5);
  3. define the property oracle.jdbc.sendBooleanAsNativeBoolean to false to be backward compatible but in this case it should not be possible to use BOOLEAN type on new code (I do not know how Hibernate handle it).

On my side I maintain a legacy infrastructure which is not using Oracle 23c and I guess it should be the case for a lot of organizations.

What should we do ?

mkouba commented 1 week ago

What should we do ?

We should definitely file a new issue in the Quartz repo. Unfortunately, the community was not very active until recently. So we will see what happens next.

If I understand it correctly it will work once you change the type of the boolean columns from VARCHAR2(1) to BOOLEAN, right? I'd choose this way.

Also pls send the pull request with the QuarkusOracleDelegate. We should merge it anyway.

dcdh commented 1 week ago

@mkouba I confirm that remplacing VARCHAR2(1) to BOOLEAN is working with gvenzl/oracle-free:23-slim-faststart

ok I will raise an issue on Quartz Repo.

I was thinking, meanwhile, what do you think, if we add the specific property to deactivate the feature this way quarkus.datasource.NAME.jdbc.additional-jdbc-properties."oracle.jdbc.sendBooleanAsNativeBoolean"=false when Oracle capability is present and Quartz is used on an oracle datasource. I can do it using the RunTimeConfigurationDefaultBuildItem.

And also add a log and update the guide on Oracle and Quarkus Quartz.

Because, even if we merge the PR, it is a partial fixed. No one will be able to test or run in dev mode with Quartz and Oracle together without specifying a lower version of Oracle.

Or maybe another way: downgrade the version of Oracle used by the dev service. I do not know the legal implication to go from an oracle-free to oracle-xe ?

Please let me know.

mkouba commented 1 week ago

@mkouba I confirm that remplacing VARCHAR2(1) to BOOLEAN is working with gvenzl/oracle-free:23-slim-faststart

+1

ok I will raise an issue on Quartz Repo.

+1

I was thinking, meanwhile, what do you think, if we add the specific property to deactivate the feature this way quarkus.datasource.NAME.jdbc.additional-jdbc-properties."oracle.jdbc.sendBooleanAsNativeBoolean"=false when Oracle capability is present and Quartz is used on an oracle datasource. I can do it using the RunTimeConfigurationDefaultBuildItem.

Have you verified that oracle.jdbc.sendBooleanAsNativeBoolean=false does not break things like Hibernate?

I wonder if we shouldn't log a warning and instruct the user to use BOOLEAN instead?

And also add a log and update the guide on Oracle and Quarkus Quartz.

Hm, the guide is postgres-based. So I'm not quite sure where to put this note.

Because, even if we merge the PR, it is a partial fixed. No one will be able to test or run in dev mode with Quartz and Oracle together without specifying a lower version of Oracle.

Or maybe another way: downgrade the version of Oracle used by the dev service. I do not know the legal implication to go from an oracle-free to oracle-xe ?

I have no idea. By the way I've just notice that quarkus-jdbc-oracle still has the "preview" status.

CC @yrodiere

dcdh commented 1 week ago

What should we do ?

We should definitely file a new issue in the Quartz repo. Unfortunately, the community was not very active until recently. So we will see what happens next.

If I understand it correctly it will work once you change the type of the boolean columns from VARCHAR2(1) to BOOLEAN, right? I'd choose this way.

Also pls send the pull request with the QuarkusOracleDelegate. We should merge it anyway.

By changing VARCHAR2(1) to BOOLEAN we will breaks all clients (organizations) which are not using Oracle 23 databases (I am on this case).

mkouba commented 1 week ago

@dcdh You can use the quarkus.datasource.devservices.image-name config property to specify the image name, or?

dcdh commented 1 week ago

It is doable.

Because this property is a build time property, I can raise a ValidationErrorBuildItem if a linked quartz oracle datasource is not using gvenzl/oracle-xe:21-slim-faststart image. Wrong idea, I should communicate inside my organization to select a compatible image

dcdh commented 1 week ago

Waiting for @yrodiere feedbacks

mkouba commented 1 week ago

Let me sum up so that we're sure on the same page ;-)

Workarounds:

Action items

dcdh commented 1 week ago

@mkouba is it the responsibility to the developer to import by himself the quartz tables ? I guess it is the case, because I did not found any way from Quarkus to create all tables if not present, but I may be wrong - could you confirm please ?

So in this case we can do nothings programmatically to check the column format used for Oracle Boolean representation. Thus, we can only update the Quarkus Oracle guide and put a reference to it from the Quarkus Quartz guide.

What do you think about it ?

mkouba commented 1 week ago

@mkouba is it the responsibility to the developer to import by himself the quartz tables ? I guess it is the case, because I did not found any way from Quarkus to create all tables if not present, but I may be wrong - could you confirm please ?

Yes, it is.

So in this case we can do nothings programmatically to check the column format used for Oracle Boolean representation. Thus, we can only update the Quarkus Oracle guide and put a reference to it from the Quarkus Quartz guide.

+1

dcdh commented 1 week ago

Ok so I will just update the guide. I'll do it tonight.

Keep you in touch

yrodiere commented 1 week ago

I was thinking, meanwhile, what do you think, if we add the specific property to deactivate the feature this way quarkus.datasource.NAME.jdbc.additional-jdbc-properties."oracle.jdbc.sendBooleanAsNativeBoolean"=false when Oracle capability is present and Quartz is used on an oracle datasource. I can do it using the RunTimeConfigurationDefaultBuildItem.

Have you verified that oracle.jdbc.sendBooleanAsNativeBoolean=false does not break things like Hibernate?

I wonder if we shouldn't log a warning and instruct the user to use BOOLEAN instead?

-1 to change default behavior of JDBC drivers, regardless of how Hibernate ORM behaves. It would just be confusing as documentation on the Oracle JDBC driver would advertise one behavior, and in Quarkus we'd have another one.

+1 to document the breaking change in the Oracle JDBC driver when using Oracle 23+.

FWIW before using BOOLEAN, Hibernate ORM was using BIT for booleans in Oracle, not VARCHAR; the switch from BIT to BOOLEAN was made without an entry in the migration guide, so I suspect you can work with BOOLEAN in the JDBC driver, while still using BIT in your DB schema, and Oracle (on the DB side) will just convert implicitly (1 <=> true, 0 <=> false). Actually I'm sure that's the case, I just checked. All that to say: people might be able to support both Oracle 23+ and 22- by using number(1, 0) (BIT in JDBC) in their DDL script instead of VARCHAR.

I have no idea. By the way I've just notice that quarkus-jdbc-oracle still has the "preview" status.

See https://github.com/quarkusio/quarkus/pull/43462

dcdh commented 1 week ago

@yrodiere thanks. I will made a PR on the guideline this weekend.

dcdh commented 1 week ago

I need to updated two guides

How, where to do it regarding oracle ?

yrodiere commented 6 days ago

How, where to do it regarding oracle ?

We don't have a specific guide for Oracle, just one for all datasources. Which does include subsections about Oracle, but well... not sure this is the right place.

Since we're talking about a breaking change, maybe an entry in the relevant migration guide would make sense?

melloware commented 3 days ago

@dcdh another note I am using Quarkus Quartz with Oracle 23c and using Quarkus Liquibase and it works fine.

Liquibase properly creates the table for Oracle 23C at least I have not seen any errors.

Image

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <property name="table_prefix" value="QRTZ_"/>

    <property name="blob_type" value="BYTEA" dbms="postgresql"/>
    <property name="blob_type" value="BLOB"/>

    <changeSet id="quartz-init" author="quartz">

        <createTable tableName="${table_prefix}LOCKS">
            <column name="SCHED_NAME" type="VARCHAR(120)">
                <constraints nullable="false"/>
            </column>
            <column name="LOCK_NAME" type="VARCHAR(40)">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <addPrimaryKey columnNames="SCHED_NAME, LOCK_NAME" tableName="${table_prefix}LOCKS"/>

        <createTable tableName="${table_prefix}FIRED_TRIGGERS">
            <column name="SCHED_NAME" type="VARCHAR(120)">
                <constraints nullable="false"/>
            </column>
            <column name="ENTRY_ID" type="VARCHAR(95)">
                <constraints nullable="false"/>
            </column>
            <column name="TRIGGER_NAME" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="TRIGGER_GROUP" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="INSTANCE_NAME" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="FIRED_TIME" type="BIGINT">
                <constraints nullable="false"/>
            </column>
            <column name="SCHED_TIME" type="BIGINT">
                <constraints nullable="false"/>
            </column>
            <column name="PRIORITY" type="INTEGER">
                <constraints nullable="false"/>
            </column>
            <column name="STATE" type="VARCHAR(16)">
                <constraints nullable="false"/>
            </column>
            <column name="JOB_NAME" type="VARCHAR(200)"/>
            <column name="JOB_GROUP" type="VARCHAR(200)"/>
            <column name="IS_NONCONCURRENT" type="BOOLEAN"/>
            <column name="REQUESTS_RECOVERY" type="BOOLEAN"/>
        </createTable>
        <addPrimaryKey columnNames="SCHED_NAME, ENTRY_ID" tableName="${table_prefix}FIRED_TRIGGERS"/>

        <createIndex tableName="${table_prefix}FIRED_TRIGGERS" indexName="IDX_${table_prefix}FT_INST_JOB_REQ_RCVRY">
            <column name="SCHED_NAME"/>
            <column name="INSTANCE_NAME"/>
            <column name="REQUESTS_RECOVERY"/>
        </createIndex>

        <createIndex tableName="${table_prefix}FIRED_TRIGGERS" indexName="IDX_${table_prefix}FT_J_G">
            <column name="SCHED_NAME"/>
            <column name="JOB_NAME"/>
            <column name="JOB_GROUP"/>
        </createIndex>

        <createIndex tableName="${table_prefix}FIRED_TRIGGERS" indexName="IDX_${table_prefix}FT_JG">
            <column name="SCHED_NAME"/>
            <column name="JOB_GROUP"/>
        </createIndex>

        <createIndex tableName="${table_prefix}FIRED_TRIGGERS" indexName="IDX_${table_prefix}FT_T_G">
            <column name="SCHED_NAME"/>
            <column name="TRIGGER_NAME"/>
            <column name="TRIGGER_GROUP"/>
        </createIndex>

        <createIndex tableName="${table_prefix}FIRED_TRIGGERS" indexName="IDX_${table_prefix}FT_TG">
            <column name="SCHED_NAME"/>
            <column name="TRIGGER_GROUP"/>
        </createIndex>

        <createIndex tableName="${table_prefix}FIRED_TRIGGERS" indexName="IDX_${table_prefix}FT_TRIG_INST_NAME">
            <column name="SCHED_NAME"/>
            <column name="INSTANCE_NAME"/>
        </createIndex>

        <createTable tableName="${table_prefix}CALENDARS">
            <column name="SCHED_NAME" type="VARCHAR(120)">
                <constraints nullable="false"/>
            </column>
            <column name="CALENDAR_NAME" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="CALENDAR" type="${blob_type}">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <addPrimaryKey columnNames="SCHED_NAME, CALENDAR_NAME" tableName="${table_prefix}CALENDARS"/>

        <createTable tableName="${table_prefix}PAUSED_TRIGGER_GRPS">
            <column name="SCHED_NAME" type="VARCHAR(120)">
                <constraints nullable="false"/>
            </column>
            <column name="TRIGGER_GROUP" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <addPrimaryKey columnNames="SCHED_NAME, TRIGGER_GROUP" tableName="${table_prefix}PAUSED_TRIGGER_GRPS"/>

        <createTable tableName="${table_prefix}SCHEDULER_STATE">
            <column name="SCHED_NAME" type="VARCHAR(120)">
                <constraints nullable="false"/>
            </column>
            <column name="INSTANCE_NAME" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="LAST_CHECKIN_TIME" type="BIGINT">
                <constraints nullable="false"/>
            </column>
            <column name="CHECKIN_INTERVAL" type="BIGINT">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <addPrimaryKey columnNames="SCHED_NAME, INSTANCE_NAME" tableName="${table_prefix}SCHEDULER_STATE"/>

        <createTable tableName="${table_prefix}JOB_DETAILS">
            <column name="SCHED_NAME" type="VARCHAR(120)">
                <constraints nullable="false"/>
            </column>
            <column name="JOB_NAME" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="JOB_GROUP" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="DESCRIPTION" type="VARCHAR(250)"/>
            <column name="JOB_CLASS_NAME" type="VARCHAR(250)">
                <constraints nullable="false"/>
            </column>
            <column name="IS_DURABLE" type="BOOLEAN">
                <constraints nullable="false"/>
            </column>
            <column name="IS_NONCONCURRENT" type="BOOLEAN">
                <constraints nullable="false"/>
            </column>
            <column name="IS_UPDATE_DATA" type="BOOLEAN">
                <constraints nullable="false"/>
            </column>
            <column name="REQUESTS_RECOVERY" type="BOOLEAN">
                <constraints nullable="false"/>
            </column>
            <column name="JOB_DATA" type="${blob_type}"/>
        </createTable>
        <addPrimaryKey columnNames="SCHED_NAME, JOB_NAME, JOB_GROUP" tableName="${table_prefix}JOB_DETAILS"/>

        <createIndex tableName="${table_prefix}JOB_DETAILS" indexName="IDX_${table_prefix}J_GRP">
            <column name="SCHED_NAME"/>
            <column name="JOB_GROUP"/>
        </createIndex>

        <createIndex tableName="${table_prefix}JOB_DETAILS" indexName="IDX_${table_prefix}J_REQ_RECOVERY">
            <column name="SCHED_NAME"/>
            <column name="REQUESTS_RECOVERY"/>
        </createIndex>

        <createTable tableName="${table_prefix}TRIGGERS">
            <column name="SCHED_NAME" type="VARCHAR(120)">
                <constraints nullable="false"/>
            </column>
            <column name="TRIGGER_NAME" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="TRIGGER_GROUP" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="JOB_NAME" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="JOB_GROUP" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="DESCRIPTION" type="VARCHAR(250)"/>
            <column name="NEXT_FIRE_TIME" type="BIGINT"/>
            <column name="PREV_FIRE_TIME" type="BIGINT"/>
            <column name="PRIORITY" type="INTEGER"/>
            <column name="TRIGGER_STATE" type="VARCHAR(16)">
                <constraints nullable="false"/>
            </column>
            <column name="TRIGGER_TYPE" type="VARCHAR(8)">
                <constraints nullable="false"/>
            </column>
            <column name="START_TIME" type="BIGINT">
                <constraints nullable="false"/>
            </column>
            <column name="END_TIME" type="BIGINT"/>
            <column name="CALENDAR_NAME" type="VARCHAR(200)"/>
            <column name="MISFIRE_INSTR" type="smallint"/>
            <column name="JOB_DATA" type="${blob_type}"/>
        </createTable>
        <addPrimaryKey columnNames="SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP" tableName="${table_prefix}TRIGGERS"/>

        <createIndex tableName="${table_prefix}TRIGGERS" indexName="IDX_${table_prefix}T_C">
            <column name="SCHED_NAME"/>
            <column name="CALENDAR_NAME"/>
        </createIndex>

        <createIndex tableName="${table_prefix}TRIGGERS" indexName="IDX_${table_prefix}T_G">
            <column name="SCHED_NAME"/>
            <column name="TRIGGER_GROUP"/>
        </createIndex>

        <createIndex tableName="${table_prefix}TRIGGERS" indexName="IDX_${table_prefix}T_JG">
            <column name="SCHED_NAME"/>
            <column name="JOB_GROUP"/>
        </createIndex>

        <createIndex tableName="${table_prefix}TRIGGERS" indexName="IDX_${table_prefix}T_N_G_STATE">
            <column name="SCHED_NAME"/>
            <column name="TRIGGER_GROUP"/>
            <column name="TRIGGER_STATE"/>
        </createIndex>

        <createIndex tableName="${table_prefix}TRIGGERS" indexName="IDX_${table_prefix}T_N_STATE">
            <column name="SCHED_NAME"/>
            <column name="TRIGGER_NAME"/>
            <column name="TRIGGER_GROUP"/>
            <column name="TRIGGER_STATE"/>
        </createIndex>

        <createIndex tableName="${table_prefix}TRIGGERS" indexName="IDX_${table_prefix}T_NEXT_FIRE_TIME">
            <column name="SCHED_NAME"/>
            <column name="NEXT_FIRE_TIME"/>
        </createIndex>

        <createIndex tableName="${table_prefix}TRIGGERS" indexName="IDX_${table_prefix}T_NFT_MISFIRE">
            <column name="SCHED_NAME"/>
            <column name="MISFIRE_INSTR"/>
            <column name="NEXT_FIRE_TIME"/>
        </createIndex>

        <createIndex tableName="${table_prefix}TRIGGERS" indexName="IDX_${table_prefix}T_NFT_ST">
            <column name="SCHED_NAME"/>
            <column name="TRIGGER_STATE"/>
            <column name="NEXT_FIRE_TIME"/>
        </createIndex>

        <createIndex tableName="${table_prefix}TRIGGERS" indexName="IDX_${table_prefix}T_NFT_ST_MISFIRE">
            <column name="SCHED_NAME"/>
            <column name="MISFIRE_INSTR"/>
            <column name="NEXT_FIRE_TIME"/>
            <column name="TRIGGER_STATE"/>
        </createIndex>

        <createIndex tableName="${table_prefix}TRIGGERS" indexName="IDX_${table_prefix}T_NFT_ST_MISFIRE_GRP">
            <column name="SCHED_NAME"/>
            <column name="MISFIRE_INSTR"/>
            <column name="NEXT_FIRE_TIME"/>
            <column name="TRIGGER_GROUP"/>
            <column name="TRIGGER_STATE"/>
        </createIndex>

        <createIndex tableName="${table_prefix}TRIGGERS" indexName="IDX_${table_prefix}T_STATE">
            <column name="SCHED_NAME"/>
            <column name="TRIGGER_STATE"/>
        </createIndex>

        <createTable tableName="${table_prefix}BLOB_TRIGGERS">
            <column name="SCHED_NAME" type="VARCHAR(120)">
                <constraints nullable="false"/>
            </column>
            <column name="TRIGGER_NAME" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="TRIGGER_GROUP" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="BLOB_DATA" type="${blob_type}"/>
        </createTable>
        <addPrimaryKey columnNames="SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP" tableName="${table_prefix}BLOB_TRIGGERS"/>

        <createTable tableName="${table_prefix}SIMPROP_TRIGGERS">
            <column name="SCHED_NAME" type="VARCHAR(120)">
                <constraints nullable="false"/>
            </column>
            <column name="TRIGGER_NAME" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="TRIGGER_GROUP" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="STR_PROP_1" type="VARCHAR(512)"/>
            <column name="STR_PROP_2" type="VARCHAR(512)"/>
            <column name="STR_PROP_3" type="VARCHAR(512)"/>
            <column name="INT_PROP_1" type="INTEGER"/>
            <column name="INT_PROP_2" type="INTEGER"/>
            <column name="LONG_PROP_1" type="BIGINT"/>
            <column name="LONG_PROP_2" type="BIGINT"/>
            <column name="DEC_PROP_1" type="NUMERIC(13,4)"/>
            <column name="DEC_PROP_2" type="NUMERIC(13,4)"/>
            <column name="BOOL_PROP_1" type="BOOLEAN"/>
            <column name="BOOL_PROP_2" type="BOOLEAN"/>
        </createTable>
        <addPrimaryKey columnNames="SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP" tableName="${table_prefix}SIMPROP_TRIGGERS"/>

        <createTable tableName="${table_prefix}CRON_TRIGGERS">
            <column name="SCHED_NAME" type="VARCHAR(120)">
                <constraints nullable="false"/>
            </column>
            <column name="TRIGGER_NAME" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="TRIGGER_GROUP" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="CRON_EXPRESSION" type="VARCHAR(120)">
                <constraints nullable="false"/>
            </column>
            <column name="TIME_ZONE_ID" type="VARCHAR(80)"/>
        </createTable>
        <addPrimaryKey columnNames="SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP" tableName="${table_prefix}CRON_TRIGGERS"/>

        <createTable tableName="${table_prefix}SIMPLE_TRIGGERS">
            <column name="SCHED_NAME" type="VARCHAR(120)">
                <constraints nullable="false"/>
            </column>
            <column name="TRIGGER_NAME" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="TRIGGER_GROUP" type="VARCHAR(200)">
                <constraints nullable="false"/>
            </column>
            <column name="REPEAT_COUNT" type="BIGINT">
                <constraints nullable="false"/>
            </column>
            <column name="REPEAT_INTERVAL" type="BIGINT">
                <constraints nullable="false"/>
            </column>
            <column name="TIMES_TRIGGERED" type="BIGINT">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <addPrimaryKey columnNames="SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP" tableName="${table_prefix}SIMPLE_TRIGGERS"/>

        <addForeignKeyConstraint baseTableName="${table_prefix}TRIGGERS" constraintName="${table_prefix}TRIGGERS_SCHED_NAME_FKEY" baseColumnNames="SCHED_NAME, JOB_NAME, JOB_GROUP" referencedTableName="${table_prefix}JOB_DETAILS" referencedColumnNames="SCHED_NAME, JOB_NAME, JOB_GROUP"/>

        <addForeignKeyConstraint baseTableName="${table_prefix}SIMPLE_TRIGGERS" constraintName="${table_prefix}SIMPLE_TRIGGERS_SCHED_NAME_FKEY" baseColumnNames="SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP" referencedTableName="${table_prefix}TRIGGERS" referencedColumnNames="SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP"/>

        <addForeignKeyConstraint baseTableName="${table_prefix}CRON_TRIGGERS" constraintName="${table_prefix}CRON_TRIGGERS_SCHED_NAME_FKEY" baseColumnNames="SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP" referencedTableName="${table_prefix}TRIGGERS" referencedColumnNames="SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP"/>

        <addForeignKeyConstraint baseTableName="${table_prefix}SIMPROP_TRIGGERS" constraintName="${table_prefix}SIMPROP_TRIGGERS_SCHED_NAME_FKEY" baseColumnNames="SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP" referencedTableName="${table_prefix}TRIGGERS" referencedColumnNames="SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP"/>

        <addForeignKeyConstraint baseTableName="${table_prefix}BLOB_TRIGGERS" constraintName="${table_prefix}BLOB_TRIGGERS_SCHED_NAME_FKEY" baseColumnNames="SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP" referencedTableName="${table_prefix}TRIGGERS" referencedColumnNames="SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP"/>
    </changeSet>
</databaseChangeLog>