Porting to MaxDB

[Migrated From Jforum.net]

I would like to port your implementation to the MaxDB open source database.

Could you please suggest the step needed to effectively do the port?

thanks in advance
[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

This is great !

I am currently working on a regression test suite and I think it will be much easier to do the port once the test suite is available.

I am also working on a port to hsqldb (version 1.7.2, someone else already did a port to 1.7.1). If you send me a private email with your email I will send you everything I have so far.
[originally posted on jforum.net by marc]

[Migrated From Jforum.net]

It's not very difficult, just a little hard-working. The text is a bit long, but don't be afraid ;)

First, take a look at WEB-INF/config/database/generic/generic_queries.sql. These are general queries that work on most databases, but, of course, some don't, because the SQL sintax or keywords used.

Based on that, you should create a specific SQL file for yout database implementation. In your cause, it should be WEB-INF/config/database/maxdb/maxdb.sql. Put in this file all queries related to maxdb. If you want to override some queries of generic_queries.sql, use the same key ( eg, using the key CategoryModel.addNew will override the query with the same key placed in generic_queries.sql ).

In this file ( maxdb.sql ), you can put also maxdb specific queries, if needed. An concrete example is the current postgresql's implementation: pgsql does not support the AUTO_INCREMENT feature of mysql, being necesasry to use SEQUENCEs to fetch the last generated key.
In the same way, the queries that insert records on tables with primary keys ( like jforum_categories, jforum_users etc etc ) should use the SEQUECE to insert the correct id. Using postgresql as example, the SQL statement to insert a new category is the one below:

INSERT INTO jforum_categories (title, display_order) VALUES (?, NEXTVAL('jforum_categories_order_seq'))

see the call to NEXTVAL('sequence_name'). Probably you will need to do something like that for maxdb ( or not, It's just a tought ;) ).

Also, if maxdb does not support AUTO_INCREMENT keys ( again, like postgresql and oracle, for example ), you should provide a query to get the last generated key. Using pgsql as example, in postgresql.sql there are queries like

CategoryModel.lastGeneratedCategoryId = SELECT CURRVAL('jforum_categories_seq')

which will retrieve the last sequence's value. This query will be used later. If maxdb does not have sequences, you can, for example, do something like

CategoryModel.lastGeneratedCategoryId = SELECT MAX(categories_id) FROM jforum_categories

Now let's assume that maxdb does not support auto_increment keys, eg, it depends of sequences ( just an assumption to use as example ). Open the file src/net/jforum/drivers/postgresql/CategoryModel.java. As we overwrite the addNew query, because pgsql need to use sequences, the addNew() method should be like

public int addNew(Category category) throws Exception
{
    this.setSupportAutoGeneratedKey(false);
    this.setAutoGeneratedKeysQuery(SystemGlobals.getSql("CategoryModel.lastGeneratedCategoryId"));
	
    return super.addNew(category);
}

Two important methods you should use: setSuppportAutoGeneratedKeys(boolean), and setAutoGeneratedKeysQuery(String), which are used to tell jfourm that the database does not support auto_increment and which query to use to retrieve the last id, respectively.

Now, because we changed the CategoryModel to use some specific database stuff, we need to extend net.jforum.drivers.generic.DataAccessDriver and register the modified Model classes. Just see the postgresql at src/net/jforum/drivers/postgresql/DataAccessDriver.java.


Most of the work you'll have is to write the maxdb specific SQL statements and register them with the correct driver ( in your cases, net.jforum.drivers.maxdb ). There are some other cases like the selectAll() methdo from UserModel.

Take a look in the postgresql implementation, including the queries at WEB-INF/config/database/postgresql[i] and the source files at [i]src/net/jforum/drivers/postgresql.

Any question, just ask
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

We can have some problems yes, but I assure you that all the code in the user request is executed using the same connection.

Ok, great, then I'm going to try implementing Oracle part, using current pattern. Aren't you going to change it, as you discussed in the thread i've mentioned?

//da
[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

Sorry for a dumb question, but how can I get sources? Can I checkout module as an anonymous CVS user or a have to register somewhere?

//da
[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

Anonymous wrote:

We can have some problems yes, but I assure you that all the code in the user request is executed using the same connection.

Ok, great, then I'm going to try implementing Oracle part, using current pattern. Aren't you going to change it, as you discussed in the thread i've mentioned?

//da

I'll, but not now. You can do the implementation without fear ;)

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

[Migrated From Jforum.net]

Anonymous wrote:Sorry for a dumb question, but how can I get sources? Can I checkout module as an anonymous CVS user or a have to register somewhere?

//da

The CVS is hosted by java.net .. go to http://jforum.dev.java.net/source/browse/jforum/ . There are instructions about how to get the sources.

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

[Migrated From Jforum.net]

Anonymous wrote:

We can have some problems yes, but I assure you that all the code in the user request is executed using the same connection.

Ok, great, then I'm going to try implementing Oracle part, using current pattern. Aren't you going to change it, as you discussed in the thread i've mentioned?

//da

i'm porting to MaxDb and is the same syntax as oracle
(if like me you use the maxdb oracle mode)
i'm done with the db creation and population.
i'm testing now.

my insert statement are like this for example

INSERT INTO jforum_groups (group_id, group_name, group_description, parent_id) select jforum_groups_seq.nextval ,?, ?, ? from dual

it seems to work, more later when i'm done with testing


[originally posted on jforum.net by sparklehorse]

[Migrated From Jforum.net]

Is there any sense for me to start implementing an Oracle version then? Maybe yours will suite both oracle and maxdb?

//da
[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

till know i've got the schema created on maxdb
and data loaded.
I've changed some sql statements and now i'm modifying the driver
to manage the fact that LIMIT clause is not supported in maxdb

i'm adding the driver code for maxdb...

Question: ' have to build my ant file or a default ant file is provided
somewhere?

thx
[originally posted on jforum.net by sparklehorse]

[Migrated From Jforum.net]

you find the build.xml in the cvs:

https://jforum.dev.java.net/source/browse/jforum/


as for the limit clause what about this :

SELECT a.* FROM
(select ROWNUM rn, b.* from
(select * from table_name order by pk) b) a
WHERE a.rn BETWEEN n and m;
[originally posted on jforum.net by marc]

[Migrated From Jforum.net]

thanks for the build.xml

unfortunately this will not work with maxdb
while it is working with oracle

maxdb does not accept order by clause in inner select statements
while oracle does.

still searching a solution.
The only one i see now is to fetch records and skip them until
a get to the range needed
[originally posted on jforum.net by sparklehorse]

[Migrated From Jforum.net]

i came into this problem while porting to maxdb:
column name value and time are keywords
for maxdb.

I have replaced them with a jvalue and jtime column name
and then i would like to override the behaviour of
SecurityCommon (for example) which specificly search
for a column named value.

I've create a net.jforum.drivers.maxdb.security.SecurityCommon.java
class compile it but it seems that this class is not taken into account.

I need to configure something particular?

thx
[originally posted on jforum.net by sparklehorse]

[Migrated From Jforum.net]

i know understand the way the code is done

i'm changing maxdb specific
GroupSecurityModel and UserSecurityModel
classes


[originally posted on jforum.net by sparklehorse]

[Migrated From Jforum.net]

Hi! I'm interested (like many other people) in using JForum with Oracle. I've read the post about porting to FirebirdSQL and about thread safe implementation here: https://coderanch.com/t/574594 ;

I've flicked through your code (PostgeSQL driver implementation) and I wonder: is current (RC5) PostgreSQL implementation thread-safe?

For example in net.jforum.drivers.generic.TopicModel method addNew:

First you call getStatementForAutoKeys, which gets the db connection and prepares 'insert' statement. In the case of PostgreSQL the statement is created without AUTOKEYS flag, so this call is equal to

p = JForum.getConnection().prepareStatement(SystemGlobals.getSql("TopicModel.addNew"));

Next, you set all the parameters and finally execute the query, using:
int topicId = executeAutoKeysQuery(p);

What I see in executeAutoKeysQuery method is that you first execute the 'insert' query (that is already prepared) and then you get a new DB connection to execute 'getAutoGeneratedKeysQuery' (which is "SELECT CURRVAL('jforum_topics_seq')" for PosgreSQL).

And here is the question: is it possible that before executing 'getAutoGeneratedKeysQuery' you recieve a different DB connection than the one you got in getStatementForAutoKeys call? I guess it is (when using database connection pooling).

In this case INSERT (and consequently 'SELECT NEXTVAL) is executed in one DB session, and 'SELECT CURRVAL' in another. And PostgeSQL documenation says that:
"Currval() returns the sequence number assigned by a prior nextval() call in the current session"

Theoretically, this issue can cause an incorrect topicId, returned by addNew().

I hope that i'm wrong, and it's guaranteed that both statements are executed in one DB session. If so, I guess i know the way of implementing a thread-safe version of
'SELECT my_sequence.curval FROM DUAL' in Oracle (using context vars).

//da




[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

Hi there,

Hi! I'm interested (like many other people) in using JForum with Oracle.
[...]
I've flicked through your code (PostgeSQL driver implementation) and I wonder: is current (RC5) PostgreSQL implementation thread-safe?

hhmm.. no, it's not. But this can be achieved by just synchronizing some pieces of code.. not difficult to do.

For example in net.jforum.drivers.generic.TopicModel method addNew:

What I see in executeAutoKeysQuery method is that you first execute the 'insert' query (that is already prepared) and then you get a new DB connection to execute 'getAutoGeneratedKeysQuery' (which is "SELECT CURRVAL('jforum_topics_seq')" for PosgreSQL).

It's the same db connection, so the same session.

And here is the question: is it possible that before executing 'getAutoGeneratedKeysQuery' you recieve a different DB connection than the one you got in getStatementForAutoKeys call? I guess it is (when using database connection pooling).

We can have some problems yes, but I assure you that all the code in the user request is executed using the same connection. The problem we can came up is taht kind of problem you have when you execute concurrent stuff without property sync.

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

[Migrated From Jforum.net]

i don't known how it will take to test everything.

the syntax is very similar and i think will perfectly work
with oracle but i'm doing my testing with maxdb
so something may be a little different

if you want to wait i can provide my implementation
when i'm done and you can go on from there

[originally posted on jforum.net by sparklehorse]