CVS Version and Oracle

OCS development discussion, enhancement requests, third-party patches and plug-ins.

Moderators: jmacgreg, michael

Forum rules
The Public Knowledge Project Support Forum is moving to

This forum will be maintained permanently as an archived historical resource, but all new questions should be added to the new forum. Questions will no longer be monitored on this old forum after March 30, 2015.
Posts: 1
Joined: Tue Oct 09, 2007 6:20 am

CVS Version and Oracle

Postby han013 » Fri Oct 12, 2007 1:05 am

Warning: LOOOONG posting :-)

I tried to install the CVS version of OCS using oracle as DB-backend.
I made a checkout of the cvs-repository and followed step 1 to 4 of the installation instructions in
docs/README (doing a Manual install).

When I tried to open the location of the installation in a browser I stumbled over a a typo in "classes/install/" line 154 which was easy to fix (see patch inline:)

Code: Select all

--- ocs2-cvs.orig/classes/install/       2007-09-20 20:02:18.000000000 +0200
+++ ocs2-cvs/classes/install/    2007-10-10 13:54:44.000000000 +0200
@@ -151,7 +151,7 @@
                        // Setup page 5
                        // Setup page 6
                        'searchDescription' => 'searchDescription',
-                       'searchKeywords' = 'searchKeywords',
+                       'searchKeywords' => 'searchKeywords',
                        'customHeaders' => 'customHeaders'

After that I could see the installation-page, filled out the necessary values (I chose UTF-8 for all charset-choices and "oracle" for the DB). But when I tried to execute the provided SQL-statements I found many problems:
1. In oracle-DBs identifiers are limited to max. 30 characters.
2. Oracle does not have a datatype "NVARCHAR". This has to be "NVARCHAR2".
3. "current" and "access" are reserved words in Oracle-DBs.

To solve those issues and make me able to at least create the tables I had to
* shorten "seqPrefix" and "trigPrefix"
* shorten some names for indices
* change "nvarchar" to "nvarchar2"

This results in the following patches to lib/adodb/datadict/

Code: Select all

--- ocs2-cvs.orig/lib/adodb/datadict/      2006-12-17 20:55:22.000000000 +0100
+++ ocs2-cvs/lib/adodb/datadict/   2007-10-10 15:07:47.000000000 +0200
@@ -17,9 +17,9 @@
        var $databaseType = 'oci8';
        var $seqField = false;
-       var $seqPrefix = 'SEQ_';
+       var $seqPrefix = 'S_';
        var $dropTable = "DROP TABLE %s CASCADE CONSTRAINTS";
-       var $trigPrefix = 'TRIG_';
+       var $trigPrefix = 'T_';
        var $alterCol = ' MODIFY ';
        var $typeX = 'VARCHAR(4000)';
        var $typeXL = 'CLOB';
@@ -75,8 +75,8 @@
                case 'X': return $this->typeX;
                case 'XL': return $this->typeXL;
-               case 'C2': return 'NVARCHAR';
-               case 'X2': return 'NVARCHAR(2000)';
+               case 'C2': return 'NVARCHAR2';
+               case 'X2': return 'NVARCHAR2(2000)';
                case 'B': return 'BLOB';

and dbscripts/xml/ocs_schema.xml

Code: Select all

--- ocs2-cvs.orig/dbscripts/xml/ocs_schema.xml  2007-09-25 17:52:56.000000000 +0200
+++ ocs2-cvs/dbscripts/xml/ocs_schema.xml       2007-10-10 16:23:56.000000000 +0200
@@ -39,7 +39,7 @@
                <field name="date_installed" type="T">
-               <field name="current" type="I1">
+               <field name="current_version" type="I1">
                        <DEFAULT VALUE="0"/>
@@ -313,7 +313,7 @@
                <descr>Conference settings.</descr>
-               <index name="conference_settings_conference_id">
+               <index name="conference_settings_conf_id">
@@ -374,7 +374,7 @@
                <descr>Scheduled conference settings.</descr>
-               <index name="sched_conf_settings_sched_conf_id">
+               <index name="sched_conf_settings_conf_id">
@@ -408,10 +408,10 @@
                <field name="assoc_id" type="I8"/>
                <field name="message" type="X"/>
                <descr>A log of all events associated with a conference or scheduled conference.</descr>
-               <index name="conference_event_log_sched_conf_id">
+               <index name="conference_event_log_s_conf_id">
-               <index name="conference_event_log_conference_id">
+               <index name="conference_event_log_conf_id">
@@ -708,7 +708,7 @@
                <index name="published_papers_sched_conf_id">
-               <index name="published_papers_public_paper_id">
+               <index name="published_papers_pub_paper_id">
@@ -810,7 +810,7 @@
                <descr>Paper presenter settings.</descr>
-               <index name="presenter_settings_presenter_id">
+               <index name="presenter_settings_presntr_id">
@@ -892,13 +892,13 @@
                        <DEFAULT VALUE="0"/>
                <descr>Supplementary files attached to papers.</descr>
-               <index name="paper_supplementary_files_file_id">
+               <index name="paper_supplementary_files_f_id">
-               <index name="paper_supplementary_files_paper_id">
+               <index name="paper_supplementary_files_p_id">
-               <index name="paper_supplementary_files_public_issue_id">
+               <index name="paper_supplementary_files_pbid">
@@ -924,7 +924,7 @@
                <descr>Paper supplementary file metadata.</descr>
-               <index name="paper_supp_file_settings_supp_id">
+               <index name="paper_supp_file_settings_s_id">
@@ -1324,7 +1324,7 @@
                <field name="from_role_id" type="I8"/>
                <field name="to_role_id" type="I8"/>
                <descr>Default conference email templates.</descr>
-               <index name="email_templates_default_email_key">
+               <index name="email_templates_default_e_key">
@@ -1478,7 +1478,7 @@
                        <descr>Word position of the keyword in the object.</descr>
                <descr>Keyword occurrences for each indexed object.</descr>
-               <index name="paper_search_object_keywords_keyword_id">
+               <index name="paper_search_object_kwds_kid">
@@ -1641,7 +1641,7 @@
                <field name="expiry_date" type="D" />
-               <field name="access" type="I2">
+               <field name="access_to" type="I2">
                <field name="institutional" type="I1">
@@ -1681,7 +1681,7 @@
                <descr>Locale-specific registration type data</descr>
-               <index name="registration_type_settings_type_id">
+               <index name="registration_type_settings_tid">
@@ -1741,7 +1741,7 @@
                <descr>Conference announcement types.</descr>
-               <index name="announcement_types_conference_id">
+               <index name="announcement_types_conf_id">
@@ -1766,7 +1766,7 @@
                <descr>Locale-specific announcement type data</descr>
-               <index name="announcement_type_settings_type_id">
+               <index name="announcement_type_settings_tid">
@@ -1821,7 +1821,7 @@
                <descr>Locale-specific announcement data</descr>
-               <index name="announcement_settings_announcement_id">
+               <index name="announcement_settings_ann_id">

After that I tried to do all the data-insertion statements. Some I could do, some not:
Those worked flawlessly:
* All "INSERT INTO email_templates_default"
* The one "INSERT INTO users"

To make the "INSERT INTO email_templates_default_data" statements work I had to substitute \' with '' (s/\\\'/\'\'/g). In oracle a ' is used to escape a ' :-)

"INSERT INTO currencies " did not work, because there was no table "currencies" created previously.

"INSERT INTO site" did not work because the columnnames used are not all defined.

INSERT INTO roles (conference_id, user_id, role_id) VALUES (0, 1, 1);
worked after changing it to
INSERT INTO roles (conference_id,sched_conf_id, user_id, role_id) VALUES (0, 0, 1, 1);

INSERT INTO versions (major, minor, revision, build, date_installed, current) VALUES (2, 1, 0, 0, NOW(), 1);
failed because I had to rename current to current_version earlier. Plus there is no "NOW()" function in oracle.

I would be pleased if you could give me some help on how to resolve the remaining issues of failing insert statements...

With my configuration (apache 1.3.x, phh 5.2.4 as CGI) I am still tackling to get any response from the application. There seems to be something wrong with the session-managment. But that's a different story...


Posts: 10015
Joined: Wed Aug 10, 2005 12:56 pm

Re: CVS Version and Oracle

Postby asmecher » Fri Oct 12, 2007 12:05 pm

Hi Hermann,

I've applied the fix you suggested for classes/install/; thanks.

Oracle isn't on our list of supported DBMSs, and we haven't done any testing with it; I suspect you'll have to address a number of issues in OCS to do with the length limit on database entities. You'll need to address these both in the descriptor XML (which you've done), and in the related DAOs (e.g. for the site table, you'll need to update classes/site/ to reflect the column renaming). Getting OCS working with Oracle will take a little legwork, as we don't have the resources to do this testing ourselves.

The CVS version of OCS is currently unstable, as many functions have been partially back-ported from OJS without testing. I'd suggest working against the 2.0 release for the moment; we'll be shifting our attention back to OCS in the beginning of November, after which point the CVS version should stabilize again.

FYI, several of the Oracle problems you mention, e.g. the quote escaping, sound like they may be problems with the ADODB library. If you have patches to ADODB, I'd suggest submitting them to the ADODB team (see for potential inclusion in that distribution.

Alec Smecher
Public Knowledge Project Team

Return to “OCS Development”

Who is online

Users browsing this forum: No registered users and 0 guests