Create a new parameter to allow QFJ to deal with the database schema name

[felipewind]

Is your feature request related to a problem? Please describe. In the database of my company, the user and the schema have different names, so I must inform the schema name explicitly at every database operation or the table naturally won't be found. When we acces the tables without informing the schema, the database assumes they have the same name of the user.

The parameters of the QFJ tables ( JdbcStoreMessagesTableName, JdbcStoreSessionsTableName, JdbcLogIncomingTable and JdbcLogOutgoingTable) don't work properly if we inform the schema and the table name concatenated as MY_SCHEMA.SESSION.

If I only inform the table name (without the schema), QFJ naturally won't find the table.

If I inform the schema and the table name, as MY_SCHEMA.SESSION , the quickfix.JdbcUtil.determineSessionIdSupport method don't work properly because the DatabaseMetaData.getColumns will receive the schema name concatenated with the table name (MY_SCHEMA.SESSION) and won't find the sendersubid column and QFJ won't understand that my table has the sendersubid column.

The workaround that I've found is to set the CURRENT SCHEMA at the start of my application, this way I can execute queries without informing the schema, in this way I don't need to inform the schema to QFJ:

ALTER SESSION SET CURRENT_SCHEMA=MY_SCHEMA

Here is the method of the quickfix.JdbcUtil class:

    static boolean determineSessionIdSupport(DataSource dataSource, String tableName) throws SQLException {
        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData metaData = connection.getMetaData();
            String columnName = "sendersubid";
            return isColumn(metaData, tableName.toUpperCase(), columnName.toUpperCase())
                    || isColumn(metaData, tableName, columnName);
        }
    }

    private static boolean isColumn(DatabaseMetaData metaData, String tableName, String columnName)
            throws SQLException {
        try (ResultSet columns = metaData.getColumns(null, null, tableName, columnName)) {
            return columns.next();
        }
    }

Describe the solution you'd like I'd like a way to inform the schema name of my tables to QFJ.

Describe alternatives you've considered I had thought about three alternatives.

But, perhaps that's an easier way to do this.

If this feature is authorized, I'd be happy to implement it :)

First - create the extendedSessionIdSupported parameter

Create a new parameter to inform if the tables have the sendersubid column and allow the user to inform the table parameters with the schema, like MY_SCHEMA.SESSION.

This way, the quickfix.JdbcStore could load the extendedSessionIdSupported from the SessionSettings parameter without executing the JdbcUtil.determineSessionIdSupport() method.

I've tested QFJ returning fixed true on the JdbcUtil.determineSessionIdSupport() and everything worked fine. In this test, I passed all table with the schemas like:

JdbcStoreMessagesTableName=broker.messages
JdbcStoreSessionsTableName=broker.sessions
JdbcLogHeartBeats=N
JdbcLogIncomingTable=broker.messages_log_incoming
JdbcLogOutgoingTable=broker.messages_log_outgoing
JdbcLogEventTable=broker.event_log
JdbcSessionIdDefaultPropertyValue=not_null

I like this alternative because it would be easy to implement and the user could even pass different schemas to the tables.

Second - create the JdbcSchema parameter

Create a new parameter to inform the schema of the database.

In this alternative, QFJ would need to concatenate the table name with the schema before executing the queries and execute the DatabaseMetaData.getColumns informing the schema name as:

    private static boolean isColumn(DatabaseMetaData metaData, String tableName, String columnName, String schemaName)
            throws SQLException {
        try (ResultSet columns = metaData.getColumns(null, schemaName, tableName, columnName)) {
            return columns.next();
        }
    }

I've tested this and worked. If we don't inform the schema in the getColumns method, the getColumns would search the tables of all the schemas and QFJ could get the wrong table.

Third - alter the method quickfix.JdbcUtil.isColumn to search for a dot (.)

In the isColumn() method, it would be possible to search a dot (.) on the tableName variable and split the first part as the schemaName and the second part as the tableName.

If the dot is found, the getColumns() will use the schemaName in the search as:

metaData.getColumns(null, schemaName, tableName, columnName))

It don't look very elegant, but would work. I'm only afraid of be missing something here and maybe broke someone application.

Additional context The error that I receive when I inform the schema and the table name together, as JdbcStoreSessionsTableName=MY_SCHEMA.SESSION:

	at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: java.lang.RuntimeException: org.h2.jdbc.JdbcSQLException: NULL not allowed for column "SENDERSUBID"; SQL statement:
INSERT INTO broker.sessions (beginstring,sendercompid,targetcompid,session_qualifier, creation_time,incoming_seqnum, outgoing_seqnum) VALUES (?,?,?,?,?,?,?) [23502-197]
	at quickfix.JdbcStoreFactory.create(JdbcStoreFactory.java:47)
	at quickfix.Session.<init>(Session.java:526)
	at quickfix.DefaultSessionFactory.create(DefaultSessionFactory.java:224)
	at quickfix.mina.SessionConnector.createSession(SessionConnector.java:182)
	at quickfix.mina.initiator.AbstractSocketInitiator.createSessions(AbstractSocketInitiator.java:220)
``

[felipewind]

About the third option I told, I guess it wouldn't be safe to use a dot to split the schema and the table name because it's not recommend, but it would be possible that someone has a table with a dot in the name.

I never tried to create a table with a dot in it's name, but according to this post it's possible.

[chrjohn]

Just my 2 cents: did you try to set the schema as property of your JDBC driver? This could then be part of the URL you pass to the JDBC driver. Unless you are not using an URL but configure the driver in another way.

[felipewind]

Just my 2 cents: did you try to set the schema as property of your JDBC driver? This could then be part of the URL you pass to the JDBC driver. Unless you are not using an URL but configure the driver in another way.

I can do this with PostgreSQL, but I can't do this with Oracle, which is the database used in my company.

I found the workaround to alter my default schema name when I was looking for this: https://stackoverflow.com/questions/2353594/default-schema-in-oracle-connection-url

But, as my workaround is working, this feature is not absolutely necessary to me.

[felipewind]

Just to update, today while making new tests in the application, I discovered that when I connect with an incoming seq num lower than the one of the counterparty something goes wrong with my workaround of changing the current schema name.

In this situation, QFJ sends automatically one ResendRequest and looks like there's a process running in some thread that don't see the result of my command:

ALTER SESSION SET CURRENT_SCHEMA=QFJ_REAL_SCHEMA

I tested this situation locally and I was able to fix this with a new workaround that is to create synonyms to the tables, like:

CREATE SYNONYM QFJ_DATABASE_USER.sessions for QFJ_REAL_SCHEMA.sessions;
CREATE SYNONYM QFJ_DATABASE_USER.messages for QFJ_REAL_SCHEMA.messages;
CREATE SYNONYM QFJ_DATABASE_USER.event_log FOR QFJ_REAL_SCHEMA.event_log;
CREATE SYNONYM QFJ_DATABASE_USER.messages_incoming FOR QFJ_REAL_SCHEMA.messages_incoming;
CREATE SYNONYM QFJ_DATABASE_USER.messages_outgoing FOR QFJ_REAL_SCHEMA.messages_outgoing;