Monitor plugin | oracle | querries and ddl not working properly

Openfire Plugins Community Plugins
1

There seems to be a problem with the ddl of the monitoring plugin for oracle in general and a problem with the query statements for oracle 11g and later versions.

In the DDL of the monitoring plugin for oracle, there are defined two “LONG” columns in the table “ofMessageArchive”. Oracle Databases do not allow this, there is permitted only one “LONG” column per table (ORA-01754).

When converting the columns to “CLOB” all select statements that contain a distinct modifier will break as oracle cannot apply distinct modifier to “LOB” columns.

For oracle 11g there is another problem with a select statement when querrying messages. As “LIMIT” and “OFFSET” are not available in oracle 11g this will not work here.

Please find attached a patch where i tried to solve those issues. I did not adjust all statements but those necessary for our needs. A also added an option to store complete stanzas for mucs. (md5sum 604b0b17ac9e2897d964eb7b49d9b648)
monitoring.patch.zip (7202 Bytes)

2

I just tested the patch with my Oracle 11g. Now I can ditch the MySQL database :-). Thanks!

It would be awesome if this could be applied to the next Openfire version.

3

Would you kindly consider making a Github Pull Request with this suggested change?

4

Pull Request created - Adjust monitoring plugin for use with oracle database by Hammington · Pull Request #664 · igniterealtime/Openfire · GitH…