new DB support

[Migrated From Jforum.net]

Great!!! . Send it me NOW .

I've zipped the files I changed. You can find them here: http://users.tvcablenet.be/personal/tvcn7863/changes.zip

Sorry, I have no time to prepare a real patch so you'll have to diff the files yourself ;-)
If needed, I can help you understand my mess.

I hope I left no mistakes by the way...

I already handle that using the "AutoGeneratedKeys" class. Please see the Postgresql implementation. It consists on creating a query that will get the last generated value of a sequence.

I had a look at it. If I understood correctly, you are running a query (SELECT CURRVAL('sequence')) to fetch the generated PK value. There is no such TX-safe equivalent in Firebird nor Oracle. And for Mysql, you are using the JDBC3 feature (prepareStatement(stmt, Statement.RETURN_GENERATED_KEYS)) which is not supported by many JDBC drivers, like the Firebird and the Oracle ones.

Weird isn't it ?

Nice. What problems you've found?

All of them are related to the admin control panel:
- In Users panel, the Insert button is broken
- In Groups panel, creating a group that does not have Top Level Group as parent breaks the screen
- there is no proper way to exit the admin panel

Apart from those, you've done a fantastic work ! Thanks for it.
[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

Ooops... I've forgotten to log in.

I'm the guy with the patch.

[originally posted on jforum.net by lorban]

[Migrated From Jforum.net]

I had a look at it. If I understood correctly, you are running a query (SELECT CURRVAL('sequence')) to fetch the generated PK value. There is no such TX-safe equivalent in Firebird nor Oracle.

hmm.. in Oracle

SELECT your_sequence.currval FROM dual

doesn't works? ( of course is not 100% "safe", but we can always sync that ).

And for Mysql, you are using the JDBC3 feature (prepareStatement(stmt, Statement.RETURN_GENERATED_KEYS)) which is not supported by many JDBC drivers, like the Firebird and the Oracle ones.

Ok, so that's the reason I made AutoGeneratedKeys support.. if the driver doesn't supports that kind of statement ( postgresql does not suport too ), then my workaround should work.

All of them are related to the admin control panel:
- In Users panel, the Insert button is broken
- In Groups panel, creating a group that does not have Top Level Group as parent breaks the screen
- there is no proper way to exit the admin panel

Ok, I'll check that. Thanks.

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

hmm.. in Oracle

SELECT your_sequence.currval FROM dual

doesn't works? ( of course is not 100% "safe", but we can always sync that ).

Problem is that it's not safe: if another SELECT your_sequence.nextval is called before your call to SELECT your_sequence.currval, you're catched.

How can you sync that ?
[originally posted on jforum.net by lorban]

[Migrated From Jforum.net]

lorban wrote:
Problem is that it's not safe: if another SELECT your_sequence.nextval is called before your call to SELECT your_sequence.currval, you're catched.

yeah, that's the problem

lorban wrote:
How can you sync that ?

Doing some refactoring to the code. For example, the addXxx method could be entire synchronized, or just the part that does the call to executeUpdate() and the select that gets the current sequence's value. I guess it works..

What do you think?

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

Hi,

I just changed JForum to support FirebirdSQL.

everything should work (more or less) fine and I can provide a patch to be applied on RC4.

Unfortunately I encountered some design flaws in your persistence engine that will cause problem with firebird and other databases that don't support getting generated PK after insertion, like Oracle.

My workaround will do it but at the expense of breaking the TX isolation which is quite unacceptable. Too bad I thought about it AFTER I finished the port...

There are also quite some bugs that I've found here and there.

Interested ? Just let me know.
[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

Hi,

I just changed JForum to support FirebirdSQL.

everything should work (more or less) fine and I can provide a patch to be applied on RC4.

Great!!! . Send it me NOW .

Unfortunately I encountered some design flaws in your persistence engine that will cause problem with firebird and other databases that don't support getting generated PK after insertion, like Oracle.

I already handle that using the "AutoGeneratedKeys" class. Please see the Postgresql implementation. It consists on creating a query that will get the last generated value of a sequence.

There are also quite some bugs that I've found here and there.

Interested ? Just let me know.

Nice. What problems you've found?

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

What do you think?

This is a quickfix that is fast and easy to implement so I think this is the solution to go. On the other hand, the database cannot be shared anymore and that will impact performances on a loaded forum.

I think that these two problems are not too important, there is no reason to share the DB and it will take a highly loaded forum for the synchronization blocks to become a real problem.

Real solution would be to implement a pattern that can fetch the PK after or before the insertion:

* After is what you are doing. First you insert your row then you get the generated key.

* Before is first fetch the key then use it to perform the insertion.

I don't know any database that support both approaches so implementing both is the best solution to get maximal portability.

This solution is cleaner but would require much more refactoring work. I'm even wondering if refactoring the code to use an O/R mapper would not be a better solution in that case, I mean if you plan to support as many DB as you can.

These are all the options I can think of !
[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

O/R mappsing was one of my first options when I started coding jforum. But in that time Hibernate was just starting to get known, and I also wanted to test my ideas, get experience with some patterns..

Now I consider using hibernate, but only in a way that we don't get too dependet of it ( i mean, we must have choices ).

But, anyway, O/R mapping it not priority. I'll try to put the sync code block in the CVS asap.

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

BTW guys, all tis problems with sequencies will solved with adding no autocommited transactions. ))) Which will be as soon as Refael approved Dmitiriy's patch
[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

link http://users.tvcablenet.be/personal/tvcn7863/changes.zip don't work.


[originally posted on jforum.net by michel]

[Migrated From Jforum.net]

What I usually do is generate the keys myself, instead of letting the db generate it, so you know the key in advance and you don't depend on the support of the DB for sequences, autonumerics or retrieving the last generated key.

XDoclet has a unique ID generator that can be used for that, which generates 32char IDs. They don't look as good, meaning simple, as counters, but they are easier to manage.

Just an option.
[originally posted on jforum.net by GreenEyed]

[Migrated From Jforum.net]

GreenEyed wrote:What I usually do is generate the keys myself, instead of letting the db generate it, so you know the key in advance and you don't depend on the support of the DB for sequences, autonumerics or retrieving the last generated key.

XDoclet has a unique ID generator that can be used for that, which generates 32char IDs. They don't look as good, meaning simple, as counters, but they are easier to manage.

Just an option.

This often the best approach (but using XDoclet is not a very good idea)
Typical good unique ID generation is the following:
create SQL table unique_id with ID field.
and then make a synchronized java method
getUniqueID()
which would
do SQL update unique_id.ID by 1
return last updated value.

This returned ID will be global ID, across all objects.
The thread /board/user ID will not go one by one now,
but it does not matter. They will go in increasing order number, so all sorting /data extracting will work.


[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

What I meant is that I borrowed the technique that is implemented in XDoclet, not that I use XDoclet for that ;). I use it for other things though.

I've been using it for years and so far I have never come across a single conflict.

The technique you explain is interesting, even though I'm not sure I like creating a table and making it the bottleneck of the creation of all database objects.
But using the generated ID for sorting is something I would not do. The best PKs are usually the ones that have absolutely no meaning but just being an ID.
[originally posted on jforum.net by GreenEyed]

[Migrated From Jforum.net]

GreenEyed wrote:What I meant is that I borrowed the technique that is implemented in XDoclet, not that I use XDoclet for that ;). I use it for other things though.

I've been using it for years and so far I have never come across a single conflict.

The technique you explain is interesting, even though I'm not sure I like creating a table and making it the bottleneck of the creation of all database objects.

This is not a bottleneck. Imagine - how many simultaneous new user/posts you can have?
Very unlikely more than 1-2 a second.
Iven if it will be 10000 new users/post a second (you will never get such traffic) this technique would work OK.

GreenEyed wrote:
But using the generated ID for sorting is something I would not do. The best PKs are usually the ones that have absolutely no meaning but just being an ID.

I am not sure, but as far as I remember JForum uses id for something like I mentioned.
[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

Anonymous wrote:
This is not a bottleneck. Imagine - how many simultaneous new user/posts you can have?
Very unlikely more than 1-2 a second.
Iven if it will be 10000 new users/post a second (you will never get such traffic) this technique would work OK.

I'm not saying it wouln't work, just that you are synchronising all creation of objects in a single point -> bottleneck. If you don't have enough traffic that this bottleneck becomes a problem, fine. Again, I'm not saying it won't work or that it would cause problems for JForum. I'm just saying that as a general technique, it might cause performance problems when you have many tables depending on that and many objects are created simultaneously.

Another option would be to use Max(id) + 1 on the table where you want to create the new object. This way you would just be synchronising at the table level and you wouldn't need an extra table just for IDs.

I didn't mean it as a critique and I apologise if it sounded as one; I just wanted to point out the drawbacks.

Anonymous wrote:I am not sure, but as far as I remember JForum uses id for something like I mentioned.

Again, I'm not saying JForum does not do it. Just that it is usually not a good idea. That changing the PK generation implementation might cause a problem with some sorting algorithms is a clear example why this is not a good idea ;).
[originally posted on jforum.net by GreenEyed]