Otmetrics Compatibility Issue: Reserved Keyword "COMMENT" Used as Column Name in Oracle SQL

[AshishVirdi]

Issue Description:

During the creation of the Otmetrics benchmark database in Oracle Database 19c/23c, an error occurs due to the usage of the reserved keyword "COMMENT" as a column name. The execution of the benchmarks/smallbank/ddl-generic.sql script results in an ORA-03050: invalid identifier: "COMMENT" is a reserved word error. The error log is given as follows :

[INFO ] 2023-07-13 04:40:57,267 [main]  com.oltpbenchmark.DBWorkload main (181) - ======================================================================
[INFO ] 2023-07-13 04:40:57,338 [main]  com.oltpbenchmark.DBWorkload main (406) - Creating new OTMETRICS database...
[ERROR] 2023-07-13 04:41:03,955 [main]  com.oltpbenchmark.DBWorkload main (411) - Unexpected error when creating benchmark database tables.
java.sql.SQLException: ORA-03050: invalid identifier: "COMMENT" is a reserved word

	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 : 3050, Position : 91, Sql = CREATE TABLE sources (     id INTEGER NOT NULL,     name VARCHAR(128) NOT NULL UNIQUE,     comment varchar(256) DEFAULT NULL,     created_time TIMESTAMP NOT NULL,     PRIMARY KEY (id) ), OriginalSql = CREATE TABLE sources (     id INTEGER NOT NULL,     name VARCHAR(128) NOT NULL UNIQUE,     comment varchar(256) DEFAULT NULL,     created_time TIMESTAMP NOT NULL,     PRIMARY KEY (id) ), Error Msg = ORA-03050: invalid identifier: "COMMENT" is a reserved word

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

Description:

To address this compatibility issue, a dedicated Oracle-specific SQL file, benchmarks/otmetrics/ddl-oracle.sql , is introduced. In this file, the column name "COMMENT" is enclosed in double quotes to escape it and differentiate it from the reserved keyword. The updated SQL statements in the ddl-oracle.sql file ensure compatibility with Oracle SQL and resolve the "COMMENT" column conflict. After this load and execute steps works properly without an changes.

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

DROP TABLE IF EXISTS observations;
DROP TABLE IF exists types;
DROP TABLE IF EXISTS sessions;
DROP TABLE IF exists sources;

CREATE TABLE sources (
    id INTEGER NOT NULL,
    name VARCHAR(128) NOT NULL UNIQUE,
    "comment" varchar(256) DEFAULT NULL,
    created_time TIMESTAMP NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE types (
    id INTEGER NOT NULL,
    category INTEGER NOT NULL,
    value_type INTEGER NOT NULL,
    name VARCHAR(64) NOT NULL,
    "comment" varchar(256) DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE (category, name)
);

CREATE TABLE sessions (
    id INTEGER NOT NULL,
    source_id INTEGER NOT NULL REFERENCES sources (id),
    agent VARCHAR(32) NOT NULL,
    created_time TIMESTAMP NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE observations (
  source_id INTEGER NOT NULL REFERENCES sources (id),
  session_id INTEGER NOT NULL REFERENCES sessions (id),
  type_id INTEGER NOT NULL REFERENCES types (id),
  value DOUBLE PRECISION NOT NULL,
  created_time TIMESTAMP NOT NULL
);
CREATE INDEX idx_observations_source_session ON observations (source_id, session_id, type_id);

[apavlo]

@AshishVirdi For this one, let's just rename the column to something else. There is no reason it has to be comment. We can use description instead.

[bpkroth]

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