Hi Dave,
Sorry for a long delay in getting this to you -- I got busy at the
day job. At any rate, attached is the patch for Oracle compatibility
and db_oracle.properties file. Here are the two main changes I had to
make:
1) Updated createdb-raw.sql to change 'comment' table to
'blogcomment' and 'roller_audit_log.comment' column to 'comment_text'
column. The reason for these is that 'comment' is a reserved word in
Oracle 10g. I also updated XDoclet tags in the appropriate POJOs to
have the above cahnages reflected in Hibernate mapping files.
2) Added an Oracle "if" clause to the
HibernateRefererManagerImpl.getDaysPopularWebsites method which runs
the JDBC-based query.
There are also a few miscellaneous changes to the config files
(build.xml, hibernate.cfg.xml) for obvious reasons. Please review and
let me know if you have any questions.
I've tested the parts where I made changes against Oracle 10g Release
2 on Windows. We've also had the app based on Roller code running in
production against Oracle for some time now. However, it only uses a
subset of Roller functionality so I am not sure about more obscure
parts of the codebase.
Max
Index: /Users/mrudman/Projects/apache/roller/build.xml
===================================================================
--- /Users/mrudman/Projects/apache/roller/build.xml (revision 330942)
+++ /Users/mrudman/Projects/apache/roller/build.xml (working copy)
@@ -533,6 +533,19 @@
<mapper type="glob" from="*-raw.sql" to="*.sql" />
</copy>
+ <!-- Oracle -->
+ <copy toDir="${dbscripts}/oracle">
+ &custom-dbscripts;
+ <fileset dir="./metadata/database">
+ <include name="*.sql" />
+ <exclude name="droptables.sql" />
+ </fileset>
+ <filterset>
+ <filtersfile file="./metadata/database/db_oracle.properties"/>
+ </filterset>
+ <mapper type="glob" from="*-raw.sql" to="*.sql" />
+ </copy>
+
</target>
<!-- ********************************************************************* -->
Index:
/Users/mrudman/Projects/apache/roller/metadata/database/130-to-200-migration-raw.sql
===================================================================
---
/Users/mrudman/Projects/apache/roller/metadata/database/130-to-200-migration-raw.sql
(revision 330932)
+++
/Users/mrudman/Projects/apache/roller/metadata/database/130-to-200-migration-raw.sql
(working copy)
@@ -260,3 +260,10 @@
alter table pingcategory add constraint pc_categoryid_fk
foreign key (categoryid) references weblogcategory(id) @ADDL_FK_PARAMS@ ;
+
+-- Oracle compatability DDL
+alter table comment rename to blogcomment;
+alter table roller_audit_log add column comment_text varchar(255);
+update roller_audit_log set comment_text = comment;
+alter table roller_audit_log modify comment_text varchar(255) not null;
+alter table roller_audit_log drop column comment;
Index: /Users/mrudman/Projects/apache/roller/metadata/database/createdb-raw.sql
===================================================================
--- /Users/mrudman/Projects/apache/roller/metadata/database/createdb-raw.sql
(revision 330932)
+++ /Users/mrudman/Projects/apache/roller/metadata/database/createdb-raw.sql
(working copy)
@@ -5,6 +5,7 @@
-- * For MySQL run the script createdb.sql found in the mysql directory.
-- * For PostgreSQL run the script createdb.sql found in the postgresql
directory.
-- * For HSQLDB run the script createdb.sql found in the hsqldb directory.
+-- * For Oracle run the script createdb.sql found in the oracle directory.
--
-- For those who grabbed Roller source from CVS, don't try to run the script
-- named createdb-raw.sql, it is the source from which the above scripts are
@@ -57,7 +58,7 @@
user_id varchar(48) not null,
object_id varchar(48),
object_class varchar(255),
- comment varchar(255) not null,
+ comment_text varchar(255) not null,
change_time timestamp
);
@@ -204,7 +205,7 @@
create index nf_websiteid_idx on newsfeed( websiteid );
-create table comment (
+create table blogcomment (
id varchar(48) not null primary key,
entryid varchar(48) not null,
name varchar(255),
@@ -216,7 +217,7 @@
notify @BOOLEAN_SQL_TYPE_FALSE@ not null,
remotehost varchar(128)
);
-create index co_entryid_idx on comment( entryid );
+create index co_entryid_idx on blogcomment( entryid );
-- Ping Feature Tables
-- name: short descriptive name of the ping target
@@ -434,7 +435,7 @@
alter table weblogcategory add constraint wc_websiteid_fk
foreign key ( websiteid ) references website( id ) @ADDL_FK_PARAMS@ ;
-alter table comment add constraint co_entryid_fk
+alter table blogcomment add constraint co_entryid_fk
foreign key ( entryid ) references weblogentry( id ) @ADDL_FK_PARAMS@ ;
alter table entryattribute add constraint att_entryid_fk
Index: /Users/mrudman/Projects/apache/roller/metadata/database/droptables.sql
===================================================================
--- /Users/mrudman/Projects/apache/roller/metadata/database/droptables.sql
(revision 330932)
+++ /Users/mrudman/Projects/apache/roller/metadata/database/droptables.sql
(working copy)
@@ -6,7 +6,7 @@
drop table newsfeed;
drop table weblogcategoryassoc;
drop table bookmark;
-drop table comment;
+drop table blogcomment;
drop table entryattribute;
drop table weblogentry;
drop table weblogcategory;
Index:
/Users/mrudman/Projects/apache/roller/metadata/database/hibernate/hibernate.cfg.xml
===================================================================
---
/Users/mrudman/Projects/apache/roller/metadata/database/hibernate/hibernate.cfg.xml
(revision 330932)
+++
/Users/mrudman/Projects/apache/roller/metadata/database/hibernate/hibernate.cfg.xml
(working copy)
@@ -10,7 +10,7 @@
<property
name="connection.datasource">java:comp/env/jdbc/rollerdb</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<!--
- If you want to use HSQLDB, PostgreSQL, DB2 or Derby, then use the
right dialect
+ If you want to use HSQLDB, PostgreSQL, DB2 or Derby, Oracle then use
the right dialect
<property name="dialect">org.hibernate.dialect.HQLDBDialect</property>
<property
name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>
<property name="dialect">org.hibernate.dialect.DB2Dialect</property>
@@ -15,9 +15,16 @@
<property
name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>
<property name="dialect">org.hibernate.dialect.DB2Dialect</property>
<property name="dialect">org.hibernate.dialect.DerbyDialect</property>
+ <property
name="dialect">org.hibernate.dialect.Oracle9Dialect</property>
-->
<!--
+ Turning this on gets rid of ANTLR dependency;
+ See http://forum.hibernate.org/viewtopic.php?t=939468
+ <property
name="hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory</property>
+ -->
+
+ <!--
Examples for configuring Hibernate to use the JDBC Driver Manager
<property
name="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</property>
<property
name="hibernate.connection.url">jdbc:hsqldb:hsql://localhost:3219</property>
Index:
/Users/mrudman/Projects/apache/roller/src/org/roller/business/RefererManagerImpl.java
===================================================================
---
/Users/mrudman/Projects/apache/roller/src/org/roller/business/RefererManagerImpl.java
(revision 330932)
+++
/Users/mrudman/Projects/apache/roller/src/org/roller/business/RefererManagerImpl.java
(working copy)
@@ -366,6 +366,10 @@
private boolean checkForSpam(String refererUrl, WebsiteData website)
throws RollerException
{
String spamwords =
RollerRuntimeConfig.getProperty("spam.referers.ignorewords");
+ if (spamwords == null) {
+ // Oracle returns nulls instead of empty string so next
line would throw NPE.
+ spamwords = "";
+ }
LinkedList spamWords = new LinkedList(Arrays.asList(
StringUtils.split(StringUtils.deleteWhitespace(spamwords),
",")));
Index:
/Users/mrudman/Projects/apache/roller/src/org/roller/business/hibernate/HibernateRefererManagerImpl.java
===================================================================
---
/Users/mrudman/Projects/apache/roller/src/org/roller/business/hibernate/HibernateRefererManagerImpl.java
(revision 330932)
+++
/Users/mrudman/Projects/apache/roller/src/org/roller/business/hibernate/HibernateRefererManagerImpl.java
(working copy)
@@ -204,6 +204,14 @@
"group by u.username,w.name,w.id order by s desc fetch
first " +
Integer.toString(max) + " rows only");
stmt.setBoolean(1, true);
+ } else if (con.getMetaData().getDriverName().startsWith("Oracle"))
{
+ String sql = "select
u.username,w.name,w.handle,sum(r.dayhits) as s "+
+ "from rolleruser u, website w, referer r "+
+ "where r.websiteid=w.id and w.userid=u.id and w.isenabled= ?
and rownum <= ? " +
+ "group by u.username,w.name,w.handle order by s desc";
+ stmt = con.prepareStatement(sql);
+ stmt.setBoolean(1, true);
+ stmt.setInt(2, max );
} else {
stmt = con.prepareStatement(
"select w.id,w.name,w.handle,sum(r.dayhits) as s "+
Index:
/Users/mrudman/Projects/apache/roller/src/org/roller/pojos/CommentData.java
===================================================================
--- /Users/mrudman/Projects/apache/roller/src/org/roller/pojos/CommentData.java
(revision 330932)
+++ /Users/mrudman/Projects/apache/roller/src/org/roller/pojos/CommentData.java
(working copy)
@@ -10,7 +10,7 @@
* @ejb:bean name="CommentData"
* @struts.form include-all="true"
*
- * @hibernate.class lazy="false" table="comment"
+ * @hibernate.class lazy="false" table="blogcomment"
*/
public class CommentData extends org.roller.pojos.PersistentObject
implements java.io.Serializable
Index:
/Users/mrudman/Projects/apache/roller/src/org/roller/pojos/ObjectAuditData.java
===================================================================
---
/Users/mrudman/Projects/apache/roller/src/org/roller/pojos/ObjectAuditData.java
(revision 330932)
+++
/Users/mrudman/Projects/apache/roller/src/org/roller/pojos/ObjectAuditData.java
(working copy)
@@ -68,7 +68,7 @@
}
/**
* @ejb:persistent-field
- * @hibernate.property column="comment" non-null="true" unique="false"
+ * @hibernate.property column="comment_text" non-null="true" unique="false"
*/
public String getComment()
{
