Chbenchmark Database Creation Issue: Missing CONSTRAINT Keyword in DDL for Oracle Sql

[AshishVirdi]

Issue Description:

During the creation of the Chbenchmark database in Oracle Database 19c/23c, we encounter the following error on using benchmarks/chbenchmark/ddl-generic.sql :

[INFO ] 2023-07-13 05:24:41,463 [main]  com.oltpbenchmark.DBWorkload main (181) - ======================================================================
[INFO ] 2023-07-13 05:24:41,468 [main]  com.oltpbenchmark.DBWorkload main (406) - Creating new CHBENCHMARK database...
[ERROR] 2023-07-13 05:24:46,475 [main]  com.oltpbenchmark.DBWorkload main (411) - Unexpected error when creating benchmark database tables.
java.sql.SQLSyntaxErrorException: ORA-02000: missing CONSTRAINT keyword

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:629)
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:563)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1230)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:511)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:122)
	at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1199)
	at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1819)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1471)
	at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2504)
	at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2459)
	at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:327)
	at com.oltpbenchmark.util.ScriptRunner.runScript(ScriptRunner.java:125)
	at com.oltpbenchmark.util.ScriptRunner.runScript(ScriptRunner.java:82)
	at com.oltpbenchmark.util.ScriptRunner.runScript(ScriptRunner.java:71)
	at com.oltpbenchmark.api.BenchmarkModule.createDatabase(BenchmarkModule.java:234)
	at com.oltpbenchmark.api.BenchmarkModule.createDatabase(BenchmarkModule.java:213)
	at com.oltpbenchmark.DBWorkload.runCreator(DBWorkload.java:628)
	at com.oltpbenchmark.DBWorkload.main(DBWorkload.java:407)
Caused by: Error : 2000, Position : 35, Sql = DROP TABLE IF EXISTS region CASCADE, OriginalSql = DROP TABLE IF EXISTS region CASCADE, Error Msg = ORA-02000: missing CONSTRAINT keyword

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:636)
	... 19 more

Description:

To address this issue, a dedicated Oracle-specific SQL file, benchmarks/chbenchmark/ddl-oracle.sql, is introduced. We use the below mentioned sql file where we add the missing CONSTRAINT keyword and provide necessary error handling while dropping the tables. Rest of the Benchmark runs fine i.e. Load and execute step work properly without any changes.

-- (benchmarks/chbenchmark/ddl-oracle.sql)

-- Drop tables if they exist
BEGIN EXECUTE IMMEDIATE 'DROP TABLE region CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;;
BEGIN EXECUTE IMMEDIATE 'DROP TABLE nation CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;;
BEGIN EXECUTE IMMEDIATE 'DROP TABLE supplier CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;;

create table region
(
    r_regionkey int       not null,
    r_name      char(55)  not null,
    r_comment   char(152) not null,
    PRIMARY KEY (r_regionkey)
);

create table nation
(
    n_nationkey int    not null,
   n_name char(25) not null,
   n_regionkey int not null references region(r_regionkey) ON DELETE CASCADE,
   n_comment char(152) not null,
   PRIMARY KEY ( n_nationkey )
);

create table supplier (
   su_suppkey int not null,
   su_name char(25) not null,
   su_address varchar(40) not null,
   su_nationkey int not null references nation(n_nationkey)  ON DELETE CASCADE,
   su_phone char(15) not null,
   su_acctbal numeric(12,2) not null,
   su_comment char(101) not null,
   PRIMARY KEY ( su_suppkey )
);

[apavlo]

@AshishVirdi Please send a PR.

[bpkroth]

@AshishVirdi can you please check to see if this is still relevant after #379 got merged? Thanks!