Support for databases that have "time" as a reserved word

Openfire Openfire Dev
1

Hi. We’ve created an OpenFire schema definition for Blackfish SQL so we can use our preferred database for our chat services on the CodeGear Developer Network and we’ve run into a minor snag.

Blackfish SQL treats “time” as a reserved word, so in order to retain that field name we’ve had to quote it. However, that means a) all queries need to quote the time field, which means lots of query changes, b) that the time field becomes case sensitive in the queries, and c) that we have to modify the OpenFire source code to support Blackfish SQL.

The other option, to avoid case sensitivity issues and quoting syntax, is to rename the time field to something else. This change still results in c) above.

I’m surprised to find that “time” has not been an issue with other SQL servers. We’d like to provide a schema for Blackfish SQL support, particularly because the liberal license we provide for deploying it could mean easy adoption for open source projects like OpenFire. Does anyone have suggestions for ways to support Blackfish SQL with OpenFire that minimize the impact or changes to the source? Has anyone else reported issues with “time” being a reserved word for their database backend?

Thanks for any advice you can offer.

2

Hey John,

Welcome to Openfire! I’m certainly interested in adding Blackfish SQL support to Openfire. I haven’t used Blackfish SQL before so I will need some basic information from you.

  1. Is there a free version of Blackfish SQL that I can download? I would like to have one for my local test environment.

  2. Where can I get the JDBC drivers?

  3. What’s the format of the URL of the JDBC driver?

  4. I’m using DbVisualizer as my DB client to execute/test my SQL statements. Do you know if I can use that client with your DB? If not which db client do you recommend using?

BTW, I filed JM-1153 for 3.4.0 and right now I’m testing the renaming of the time column.

Thanks,

– Gato

3

Gato,

Thanks for your quick reply. We’ve been busy writing our own custom plug-in for Openfire and enhancing JWChat. While that work has been under way, I’m also trying to figure out the easiest way to get your Blackfish SQL. Blackfish SQL is actually the .NET implementation of what was previously called JDataStore. We have not yet released Blackish SQL for Java, but the JDBC drivers for JDataStore work just fine with Blackfish SQL for .NET.

So, I’m trying to figure out the best way to get you a freely available version of Blackfish SQL (nee JDataStore) for Java so you can use it for your testing and dev purposes. It may be a beta version of the latest Blackfish SQL in its Java form, or it may be a copy of the previous version of JDataStore, which should be compatible for Openfire purposes.

A good background on how we support both Java and .NET with the database can be found on the architect’s blog at http://blogs.codegear.com/steveshaughnessy/archive/2007/09/06/38776.aspx.More technical information on Blackfish SQL can be found at http://dn.codegear.com/blackfish

I’ll get you an answer regarding Blackfish SQL as soon as I can. We have not “gone dark” with Openfire by any means, and in fact have been rapidly making customizations to it, and JWChat, that members of the CodeGear Developer Network will really appreciate. I’ve been documenting what we’ve done as we go about it, and as soon as the public beta starts I’ll post information about our changes.

Thanks again for your prompt reply and good support.

– John

4

Hey John,

We are going to release Openfire 3.4.0 next Wednesday so you will probably be able to test it with your DB in your local environment. If you want to give it a try now you can use Openfire 3.4.0 beta 2 which is available in the download section (beta section).

Thanks,

– Gato

5

Sorry, I didn’t answer your other questions:

> Is there a free version of Blackfish SQL that I can download? I would like to have one for my local test environment.

> Where can I get the JDBC drivers?

(Just answered in previous reply)

> What’s the format of the URL of the JDBC driver?

jdbc:borland:dsremote://<server>/<path to jds file>

> I’m using DbVisualizer as my DB client to execute/test my SQL

statements. Do you know if I can use that client with your DB? If not

which db client do you recommend using?

DbVisualizer works with previous versions of JDataStore, so it should still work with Blackfish SQL.

6

Hey John,

Thanks for the info. If you are able to test Openfire 3.4.0 (beta2 or custom built from SVN trunk) with your database I can add Blackfish SQL in the list of supported DBs and in the setup wizard as an option. I just need to know that it works fine with Openfire 3.4.0.

Thanks,

– Gato

7

We are going to release Openfire 3.4.0 next Wednesday so you will

probably be able to test it with your DB in your local environment.

Thanks for the quick response.

We have tested the beta, and confirm success. So yes, Blackfish SQL is now supported without any code changes required in Openfire.