OJS OCS OMP OHS

You are viewing the PKP Support Forum | PKP Home Wiki



CVS Version and Oracle

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

Moderators: jmacgreg, michael

Forum rules
Developer Resources:

Documentation: The OJS Technical Reference and the OJS API Reference are both available from the OJS Documentation page. While these are OJS-specific, the OCS codebase is similar enough to OJS they should be of help. There is also an [url=http://pkp.sfu.ca/ocs_documentation[/url]OCS Documentation[/url] page with some more general documentation that might also be of interest.

Git: You can access our public Git Repository here. Comprehensive Git usage instructions are available on the wiki.

Bugzilla: You can access our Bugzilla report tracker here.

Search: You can use our Google Custom Search to search across our main website, the support forum, and Bugzilla.

Questions and discussion are welcome, but if you have a workflow or usability question you should probably post to the OCS Conference Support and Discussion subforum; if you have a technical support question, try the OCS Technical Support subforum.

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/Upgrade.inc.php" line 154 which was easy to fix (see patch inline:)
Code: Select all
--- ocs2-cvs.orig/classes/install/Upgrade.inc.php       2007-09-20 20:02:18.000000000 +0200
+++ ocs2-cvs/classes/install/Upgrade.inc.php    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/datadict-oci8.inc.php:
Code: Select all
--- ocs2-cvs.orig/lib/adodb/datadict/datadict-oci8.inc.php      2006-12-17 20:55:22.000000000 +0100
+++ ocs2-cvs/lib/adodb/datadict/datadict-oci8.inc.php   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">
                        <NOTNULL/>
                </field>
-               <field name="current" type="I1">
+               <field name="current_version" type="I1">
                        <NOTNULL/>
                        <DEFAULT VALUE="0"/>
                </field>
@@ -313,7 +313,7 @@
                        <descr>(bool|int|float|string|object)</descr>
                </field>
                <descr>Conference settings.</descr>
-               <index name="conference_settings_conference_id">
+               <index name="conference_settings_conf_id">
                        <col>conference_id</col>
                </index>
        </table>
@@ -374,7 +374,7 @@
                        <descr>(bool|int|float|string|object)</descr>
                </field>
                <descr>Scheduled conference settings.</descr>
-               <index name="sched_conf_settings_sched_conf_id">
+               <index name="sched_conf_settings_conf_id">
                        <col>sched_conf_id</col>
                </index>
        </table>
@@ -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">
                        <col>sched_conf_id</col>
                </index>
-               <index name="conference_event_log_conference_id">
+               <index name="conference_event_log_conf_id">
                        <col>conference_id</col>
                </index>
        </table>
@@ -708,7 +708,7 @@
                <index name="published_papers_sched_conf_id">
                        <col>sched_conf_id</col>
                </index>
-               <index name="published_papers_public_paper_id">
+               <index name="published_papers_pub_paper_id">
                        <col>public_paper_id</col>
                </index>
        </table>
@@ -810,7 +810,7 @@
                        <descr>(bool|int|float|string|object)</descr>
                </field>
                <descr>Paper presenter settings.</descr>
-               <index name="presenter_settings_presenter_id">
+               <index name="presenter_settings_presntr_id">
                        <col>presenter_id</col>
                </index>
        </table>
@@ -892,13 +892,13 @@
                        <DEFAULT VALUE="0"/>
                </field>
                <descr>Supplementary files attached to papers.</descr>
-               <index name="paper_supplementary_files_file_id">
+               <index name="paper_supplementary_files_f_id">
                        <col>file_id</col>
                </index>
-               <index name="paper_supplementary_files_paper_id">
+               <index name="paper_supplementary_files_p_id">
                        <col>paper_id</col>
                </index>
-               <index name="paper_supplementary_files_public_issue_id">
+               <index name="paper_supplementary_files_pbid">
                        <col>public_supp_file_id</col>
                </index>
        </table>
@@ -924,7 +924,7 @@
                        <descr>(bool|int|float|string|object|date)</descr>
                </field>
                <descr>Paper supplementary file metadata.</descr>
-               <index name="paper_supp_file_settings_supp_id">
+               <index name="paper_supp_file_settings_s_id">
                        <col>supp_id</col>
                </index>
        </table>
@@ -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">
                        <col>email_key</col>
                        <UNIQUE/>
                </index>
@@ -1478,7 +1478,7 @@
                        <descr>Word position of the keyword in the object.</descr>
                </field>
                <descr>Keyword occurrences for each indexed object.</descr>
-               <index name="paper_search_object_keywords_keyword_id">
+               <index name="paper_search_object_kwds_kid">
                        <col>keyword_id</col>
                </index>
        </table>
@@ -1641,7 +1641,7 @@
                        <NOTNULL/>
                </field>
                <field name="expiry_date" type="D" />
-               <field name="access" type="I2">
+               <field name="access_to" type="I2">
                        <NOTNULL/>
                </field>
                <field name="institutional" type="I1">
@@ -1681,7 +1681,7 @@
                        <NOTNULL/>
                </field>
                <descr>Locale-specific registration type data</descr>
-               <index name="registration_type_settings_type_id">
+               <index name="registration_type_settings_tid">
                        <col>type_id</col>
                </index>
        </table>
@@ -1741,7 +1741,7 @@
                        <NOTNULL/>
                </field>
                <descr>Conference announcement types.</descr>
-               <index name="announcement_types_conference_id">
+               <index name="announcement_types_conf_id">
                        <col>conference_id</col>
                </index>
        </table>
@@ -1766,7 +1766,7 @@
                        <NOTNULL/>
                </field>
                <descr>Locale-specific announcement type data</descr>
-               <index name="announcement_type_settings_type_id">
+               <index name="announcement_type_settings_tid">
                        <col>type_id</col>
                </index>
        </table>
@@ -1821,7 +1821,7 @@
                        <NOTNULL/>
                </field>
                <descr>Locale-specific announcement data</descr>
-               <index name="announcement_settings_announcement_id">
+               <index name="announcement_settings_ann_id">
                        <col>announcement_id</col>
                </index>
        </table>



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...

hermann
han013
 
Posts: 1
Joined: Tue Oct 09, 2007 6:20 am

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/Upgrade.inc.php; 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/SiteDAO.inc.php 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 http://adodb.sourceforge.net) for potential inclusion in that distribution.

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 8910
Joined: Wed Aug 10, 2005 12:56 pm


Return to OCS Development

Who is online

Users browsing this forum: No registered users and 0 guests