Introduction

Presented on this page are patches for getting phpBB to work with Oracle as described in the phpBB Oracle Howto.
Please post any queries or comments to the phpBB discussion group for Oracle, where I and others will try and assist.
Note that I do not support any of these patches by email.

For problems setting up Oracle OCI with PHP try our configuration checking script which will check the basic settings and environment for Unix based systems (tested on Solaris, Linux, Mac OS X).

Special note for version 2.0.15: If you are upgrading from an earlier version of phpBB to 2.0.15 you must run this SQL script on your database.
Special note for versions 2.0.14 and above: The developers of phpBB have chosen to remove db/oracle.php from the distribution (reason unknown at this time), so the patches now include the file as of 2.0.13 from our patch kit.

The patches

• STABLE: Vanilla 2.0.17 to Oracle Patch (plain text) - This patch also requires this SQL script to be run on the target schema, after the schema script from the Howto has been run (also available here)
  Or you can copy these files over your installation, if that is easier for you.
  
  Use this patch set if you have a freshly downloaded phpBB 2.0.17 installation unpacked on your webserver and want the latest Oracle specific fixes (October 2003 - see history below). These have been running on my own service without issue.
  
  This patch set is known to work with Oracle Enterprise Edition versions 8.1.7.4, 9.2.0.4, 9.2.0.5, 9.2.0.6 and 10.1.0.3.
  
  
• EXPERIMENTAL: Vanilla 2.0.17 to Oracle with Text Patch (plain text) - This patch also requires this SQL script and this extra SQL script to be run on the target schema, after the schema script from the Howto has been run (also available here)
  Or you can copy these files over your installation, if that is easier for you.
  
  This change should be considered completely experimental - test thoroughly before deploying.
  
  Use this patch set if you have a freshly downloaded phpBB 2.0.17 installation unpacked on your webserver and want the latest Oracle specific fixes (October 2003 - see history below), and want to use Oracle Text (aka ConText) full text indexes instead of the platform neutral implementation in phpBB.
  It is strongly advised that you only use this if you already have some experience of using Oracle Text Context indexes and understand the behaviour and limitations of them. Additionally, your DBA will need to grant CTXAPP role and EXECUTE on CTXSYS.CTX_DDL to the Oracle user used by phpBB.
  The Context indexes are not self maintaining - altering data in the underlying columns will not result in changes being made to the index (thus excluding the altered data from searches) until the maintenance procedure has been run. In the patch set there is a new parameter in includes/constants.php called ORACLE_CTX_UPDATE_ASAP that is set by default to 0. In this mode you are expected to provide some external facility for updating the indexes on a schedule such as DBMS_JOB. This is recommended for all but the lightest used boards. Setting the configuration value to 1 will cause the synchronisation procedure to be called after every insert or update of a post. While this is still very fast it does cause table level locks to be placed on the tables used by the indexes very briefly so may cause some serialisation. Additionaly, running the index sync after every post will cause fragmentation in the text index token structures, so you will need to run the CTX_DDL optimisation process more frequently.
  
  The framework for this enhancement is taken from this mod for MySQL.
  
  This patch set is known to work with Oracle Enterprise Edition version 10.1.0.3.

Change History

• 2.0.17: Merge of latest baseline patches.
  

• 2.0.16: Merge of latest baseline patches.
  Also adds bind variable support to Forum names and Polls to prevent character escape problems.

• 2.0.15: Merge of latest baseline patches - Special note: An additional column is required in the database as well as some data changes - the script to apply these is available for those upgrading from an earlier version.

• 2.0.14: Merge of latest baseline patches - Special note: The developers of phpBB have chosen to remove db/oracle.php from the distribution (reason unknown at this time), so the patches now include the file as of 2.0.13 from our patch kit.

• 2.0.13: phpBB can now be configured to use Oracle Text Context indexes instead of the search tables. This modification is currently being maintained in a separate branch so the existing method will continue to be supported. The prime reasons for this enhancement are to reduce the time taken to build the search words (even though we've already sped this up lots using Oracle features rather than PHP), and to reduce the disproportionate amount of Redo being generated by the search word maintenance.

• 2.0.13: Merge of latest baseline patches

• 2.0.11: Merge of latest baseline patches

• 2.0.10: Merge of latest baseline patches

• 2.0.8a: Final (hopefully) fix for the ' character in posts and PMs

• 2.0.8a: Enabled multiple bind variables to be sent to the query (now passes arrays to sql_query)
• 2.0.8a: phpbb_post_text.post_subject now passed in bind variable to protect special chars (like ")
• 2.0.8a: sql_fetchrowset changed to more database efficient mechanism (i.e. no longer requires the query to be rerun)

• Private Messages now use the same mechanism as posts (CLOB field and bind variable) for saving their text. So large messages (though why you would want to create a > 2k PM...) are now possible, but more importantly, the use of quote marks and other special characters now no longer breaks the message, or appear/dissappear unexpectedly. Requires new table from this SQL script

• Database size in the administrator control panel now returns the allocated sizes of all the objects owned by the user (as we expect most Oracle phpBB installations to have a separate user/schema for each board).

• Posts cannot exceed 2000 characters. This limit is caused by several factors: firstly, the field in phpbb_posts_text that stores the text is defined as VARCHAR2(2000) (the largest 8i supports). While 9i supports 4000 character wide fields, the Oracle Net8 client still only allows literal strings to a maximum of 2000 characters. This limit can be overcome through use of CLOB fields and bind variables.
• Various problems with single and double quote characters either being double escaped, repeated or dropped. This has been fixed as a side benefit of the bind variable changes required to get large posts saved, as we never have to assemble a PHP/OCI compatible string - it is all contained in variables. This is particularly noticable when posts are quoted with an attribution name; all the formatting of the forum is mangled by the mistakenly surfaced escaped quotes.
• Using the word "limit" in a post caused the search words generation to fail. The Oracle emulation of the MySQL "LIMIT" clause was being too loose in parsing queries for genuine limit clauses. The redundant eregi has now been merged into an improved (i.e. safer) preg_match parse.
• Generation of search word and search word matches is now able to exceed 1000 items (the limit of discrete options in an Oracle "IN" clause). This is done by using a table to temporarily store the matches, and by replacing the calls of "IN ({literal list of words})" with "IN ({select words from temp_table})".
  An additional benefit of this change is that we can use the Oracle "MINUS" and compound "IN" to generate and store the new words/matches lists on the database server, instead of the old method of pulling every search word to the client and parsing them there for new words.
• Pruning of old posts now works when more than 1000 posts or topics are to be pruned - the reason is the same as for the search words problem; an "IN" with more than 1000 literals is illegal. The solution is very similar. It could have been done with no temporary tables, but for the interdependancy of the queries to derive the list of posts to remove: posts depends on topics, and topics depends on posts. Hence deleting one, leaves orphans in the other when the query is reused. So in my implementation the dependancy is broken by storing the list of topics to prune in a temporary table.

Applying the patches using "patch"

To apply the chosen patch (for example to update a standard phpBB 2.0.6 distribution to include all the Oracle 8i patches):

tar zxvf phpBB-2.0.6.tar.gz
cd phpBB
patch --backup --verbose -p1 < ../vanilla-to-oracle8i.patch

Hmm...  Looks like a unified diff to me...
The text leading up to this was:
--------------------------
|diff -ur vanilla/admin/index.php oracle8i/admin/index.php
|--- vanilla/admin/index.php    Wed Sep 10 18:15:47 2003
|+++ oracle8i/admin/index.php   Wed Sep 10 18:03:52 2003
--------------------------
Patching file admin/index.php using Plan A...
Hunk #1 succeeded at 287.
Hmm...  The next patch looks like a unified diff to me...
The text leading up to this was:
--------------------------
|diff -ur vanilla/db/oracle.php oracle8i/db/oracle.php
|--- vanilla/db/oracle.php      Wed Sep 10 18:09:03 2003
|+++ oracle8i/db/oracle.php     Wed Sep 10 18:03:53 2003
--------------------------
Patching file db/oracle.php using Plan A...
Hunk #1 succeeded at 56.
Hunk #2 succeeded at 97.
Hunk #3 succeeded at 110.
Hunk #4 succeeded at 124.
Hunk #5 succeeded at 270.
Hunk #6 succeeded at 313.
Hunk #7 succeeded at 391.
Hunk #8 succeeded at 416.

...

--------------------------
|diff -ur vanilla/search.php oracle8i/search.php
|--- vanilla/search.php Wed Sep 10 18:12:59 2003
|+++ oracle8i/search.php        Wed Sep 10 18:04:03 2003
--------------------------
Patching file search.php using Plan A...
Hunk #1 succeeded at 711.
Hunk #2 succeeded at 721.
done

Credits

These patches combine the work done by Shurik, sufehmi, and amannering on the phpBB Discussion group