#557 Oracle Database Not working

Database
open
Reini Urban
5
2012-10-11
2006-10-02
Anonymous
No

I had to made the following changes to get phpwiki to
work with Oracle:

  1. Modified PearDB-pgsql.php Chnaged to
    "define("USE_BYTEA", FALSE) to eliminate Fatal error:
    Call to undefined function: pg_escape_bytea().

  2. Modified Requests.php code as follows (this may not
    be the "best" way - I was just hacking away)

Added: $tstamp = date('d-M-Y H:i:s');
$fmt = 'dd-mon-yyyy hh24:mi:ss';

Modified the $dbh->genericSqlQuery sprintf statement:
- Changed to
VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%d,%d,%s,%s,%F)",
- note esp the %F to get the duration in the proper
format. See "//" just above insert statement in
original code.

Changed the two dates to:
"to_date('$tstamp','$fmt')",

  1. Modified phpwiki_accesslog
    Increased the size of the remote_host to 100 (I
    got an error when the value being inserted was longer
    that the original 50)
    Increased the size of the bytes_sent to NUMBER(9)
    because the bytes sent value exceeded 9999 (NUMBER(4))

Just for reference, Oracle has a datatype of TIMESTAMP
(with the optional WITH LOCAL TIMEZONE) which might
have been a better choice that DATA for time_stamp, and
request_time.


Now if I could only figure out why it is coming out in
German...


-Bob Peele (bob.peele@oracle.com)

Discussion

  • C.J.
    C.J.
    2006-11-16

    Logged In: YES
    user_id=1646527
    Originator: NO

    Here are patches based on the above notes for phpwiki-1.3.12p3.

    These hacks will get phpwiki working with Oracle. Oracle users should
    follow the steps in doc/INSTALL.oci8 after applying the patches. I've
    included a "diff" of the config.ini file from the distributed config
    file for reference.

    Full testing has not been performed. Basic testing was done with Zend
    Core for Oracle 1.5 (aka PHP 5.0.5), Oracle XE 10.2 and apache 2.0.58
    on Linux.

    --- /home/cjbj/src/phpwiki-1.3.12p3/lib/Request.php 2006-06-18 04:19:24.000000000 -0700
    +++ lib/Request.php 2006-11-16 12:58:05.000000000 -0800
    @@ -1139,9 +1139,9 @@
    sprintf("INSERT INTO $log_tbl"
    . " (time_stamp,remote_host,remote_user,request_method,request_line,request_uri,"
    . "request_args,request_time,status,bytes_sent,referer,agent,request_duration)"
    - . " VALUES(%d,%s,%s,%s,%s,%s,%s,%s,%d,%d,%s,%s,'%s')",
    - $this->time,
    + . " VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%d,%d,%s,%s,%F)",
    + "to_date('".date('d-M-Y H:i:s', $this->time)."', 'DD-Mon-YYYY HH24:MI:SS')",
    $dbh->quote($this->host), $dbh->quote($this->user),
    $dbh->quote($request->get('REQUEST_METHOD')), $dbh->quote($this->request),
    $dbh->quote($request->get('REQUEST_URI')), $dbh->quote($this->request_args),
    --- /home/cjbj/src/phpwiki-1.3.12p3/lib/WikiDB/backend/PearDB_oci8.php 2006-06-18 04:19:27.000000000 -0700
    +++ lib/WikiDB/backend/PearDB_oci8.php 2006-11-16 13:10:56.000000000 -0800
    @@ -63,4 +63,14 @@
    $dbh->query("SET TRANSACTION READ ONLY");
    }
    }
    +
    + function _quote($s) {
    + return base64_encode($s);
    + }
    +
    + function _unquote($s) {
    + return base64_decode($s);
    + }
    +
    +
    };

    class WikiDB_backend_PearDB_oci8_search
    --- /home/cjbj/src/phpwiki-1.3.12p3/schemas/oci8-initialize.sql 2006-06-18 04:19:45.000000000 -0700
    +++ schemas/oci8-initialize.sql 2006-11-16 12:53:33.000000000 -0800
    @@ -193,17 +193,17 @@
    CREATE TABLE &accesslog_tbl (
    -- for OCI 9i+ use: time_stamp TIMESTAMP,
    time_stamp DATE,
    - remote_host VARCHAR2(50),
    + remote_host VARCHAR2(100),
    remote_user VARCHAR2(50),
    request_method VARCHAR2(10),
    request_line VARCHAR2(255),
    request_args VARCHAR2(255),
    request_file VARCHAR2(255),
    request_uri VARCHAR2(255),
    - request_time DATE,
    + request_time VARCHAR2(28),
    status NUMBER(4),
    - bytes_sent NUMBER(4),
    + bytes_sent NUMBER,
    referer VARCHAR(255),
    agent VARCHAR(255),
    request_duration FLOAT
    --- /home/cjbj/src/phpwiki-1.3.12p3/config/config-dist.ini 2006-06-18 04:19:16.000000000 -0700
    +++ config/config.ini 2006-11-16 12:34:26.000000000 -0800
    @@ -56,0 +56,0 @@

    ; Check for links to blocked external tld domains in new edits, against
    ; multi.surbl.org and bl.spamcop.net.
    -;ENABLE_SPAMBLOCKLIST = true
    +ENABLE_SPAMBLOCKLIST = true

    ; If more than this number of external links appear on non-authenticated
    ; edits it will be rejected as spam.
    @@ -138,4 +138,4 @@
    ; functions. On all other occasions you should simply log in with your
    ; regular WikiName.
    ; If your password contains special chars like ";" or ":" better quote it in double-quotes.
    -;ADMIN_USER =
    +ADMIN_USER = cj

    ; You must set this! Username and password of the administrator.
    ; ADMIN_PASSWD is ignored on HttpAuth
    -;ADMIN_PASSWD =
    +ADMIN_PASSWD = cj

    ; It is recommended that you use encrypted passwords to be stored in the
    ; config.ini and the users homepages metadata.
    @@ -358,4 +358,4 @@
    ; dba: use one of the standard UNIX dbm libraries. Use BerkeleyDB (db3,4) (fastest)
    ; file: use a flat file database. (easiest)
    ; cvs: use a CVS server to store everything. (experimental, not recommended)
    -DATABASE_TYPE = dba
    +DATABASE_TYPE = SQL

    ; Prefix for filenames or table names
    ;
    @@ -367,4 +367,4 @@
    ; during the installation.
    ; Note: This prefix is NOT prepended to the default DBAUTH_
    ; tables user, pref and member!
    -;DATABASE_PREFIX = phpwiki_
    +DATABASE_PREFIX = phpwiki_

    ; For SQL based backends, specify the database as a DSN (Data Source Name),
    ; a kind of URL for databases.
    @@ -386,4 +386,4 @@
    ;
    ; Valid values for dbtype are mysql, pgsql, or sqlite.
    ;
    -DATABASE_DSN = "mysql://guest@unix(/var/lib/mysql/mysql.sock)/test"
    +DATABASE_DSN = "oci8://phpwiki:phpwiki@//localhost/XE"

    ; Keep persistent connections: (mysql_pconnect, ...)
    ; Recommended is false for bigger servers, and true for small servers
    @@ -726,4 +726,4 @@
    ; USER/PASSWORD queries
    ;
    ; For USER_AUTH_POLICY=strict and the Db method this is required:
    -;DBAUTH_AUTH_USER_EXISTS = "SELECT userid FROM pref WHERE userid='$userid'"
    +DBAUTH_AUTH_USER_EXISTS = "SELECT userid FROM phpwiki_pref WHERE userid='$userid'"

    ; Check to see if the supplied username/password pair is OK
    ;
    ; Plaintext Passwords:
    -; DBAUTH_AUTH_CHECK = "SELECT IF(passwd='$password',1,0) AS ok FROM pref WHERE userid='$userid'"
    +DBAUTH_AUTH_CHECK = "SELECT IF(passwd='$password',1,0) AS ok FROM phpwiki_pref WHERE userid='$userid'"
    ;
    ; Database-hashed passwords (more secure):
    ;DBAUTH_AUTH_CHECK = "SELECT IF(passwd=PASSWORD('$password'),1,0) AS ok FROM pref WHERE userid='$userid'"
    @@ -740,5 +740,5 @@
    ; If you want to use Unix crypt()ed passwords, you can use DBAUTH_AUTH_CHECK
    ; to get the password out of the database with a simple SELECT query, and
    ; specify DBAUTH_AUTH_USER_EXISTS and DBAUTH_AUTH_CRYPT_METHOD:
    -;DBAUTH_AUTH_CHECK = "SELECT passwd FROM pref where userid='$userid'"
    +DBAUTH_AUTH_CHECK = "SELECT passwd FROM phpwiki_pref where userid='$userid'"
    ; DBAUTH_AUTH_CRYPT_METHOD = crypt

    ; Update the user's authentication credential. If this is not defined but
    @@ -750,4 +750,4 @@
    ; Database-hashed passwords:
    ; DBAUTH_AUTH_UPDATE = "UPDATE pref SET passwd=PASSWORD('$password') WHERE userid='$userid'"
    ; Plaintext passwords:
    -;DBAUTH_AUTH_UPDATE = "UPDATE pref SET passwd='$password' WHERE userid='$userid'"
    +DBAUTH_AUTH_UPDATE = "UPDATE phpwiki_pref SET passwd='$password' WHERE userid='$userid'"

    ; Allow users to create their own account.
    ; with CRYPT_METHOD=crypt e.g:
    ; DBAUTH_AUTH_CREATE = "INSERT INTO pref (passwd,userid) VALUES ('$password','$userid')"
    ; with CRYPT_METHOD=plain:
    -;DBAUTH_AUTH_CREATE = "INSERT INTO pref (passwd,userid) VALUES (PASSWORD('$password'),'$userid')"
    +DBAUTH_AUTH_CREATE = "INSERT INTO phpwiki_pref (passwd,userid) VALUES (PASSWORD('$password'),'$userid')"

    ; USER/PREFERENCE queries
    ;
    @@ -767,4 +767,4 @@
    ;
    ; The prefs field stores the serialized form of the user's preferences array,
    ; to ease the complication of storage.
    -;DBAUTH_PREF_SELECT = "SELECT prefs FROM pref WHERE userid='$userid'"
    +DBAUTH_PREF_SELECT = "SELECT prefs FROM phpwiki_pref WHERE userid='$userid'"

    ; Update the user's preferences
    -;DBAUTH_PREF_UPDATE = "UPDATE pref SET prefs='$pref_blob' WHERE userid='$userid'"
    -;DBAUTH_PREF_INSERT = "INSERT INTO pref (userid,prefs) VALUES ('$userid','$pref_blob')"
    +DBAUTH_PREF_UPDATE = "UPDATE phpwiki_pref SET prefs='$pref_blob' WHERE userid='$userid'"
    +DBAUTH_PREF_INSERT = "INSERT INTO phpwiki_pref (userid,prefs) VALUES ('$userid','$pref_blob')"

    ; USERS/GROUPS queries
    ;
    @@ -779,9 +779,9 @@
    ;
    ; Sample configurations:
    ; Only one group per user - 1:n: (Default)
    -; DBAUTH_IS_MEMBER = "SELECT userid FROM pref WHERE userid='$userid' AND groupname='$groupname'"
    -; DBAUTH_GROUP_MEMBERS = "SELECT userid FROM pref WHERE groupname='$groupname'"
    -; DBAUTH_USER_GROUPS = "SELECT groupname FROM pref WHERE userid='$userid'"
    + DBAUTH_IS_MEMBER = "SELECT userid FROM phpwiki_pref WHERE userid='$userid' AND groupname='$groupname'"
    + DBAUTH_GROUP_MEMBERS = "SELECT userid FROM phpwiki_pref WHERE groupname='$groupname'"
    + DBAUTH_USER_GROUPS = "SELECT groupname FROM phpwiki_pref WHERE userid='$userid'"
    ; Multiple groups per user - n:m:
    ; DBAUTH_IS_MEMBER = "SELECT userid FROM member WHERE userid='$userid' AND groupname='$groupname'"
    ; DBAUTH_GROUP_MEMBERS = "SELECT DISTINCT userid FROM member WHERE groupname='$groupname'"

     
  • Reini Urban
    Reini Urban
    2006-11-17

    Logged In: YES
    user_id=13755
    Originator: NO

    Thanks for the patch and hints. I'll add it ASAP.

     
  • C.J.
    C.J.
    2006-11-20

    Logged In: YES
    user_id=1646527
    Originator: NO

    The CVS change to Request.php unfortunately still fails with Oracle because (i) the text of the date is not quoted (ii) after adding quotes the format of the date is not Oracle's default so the value will not recognized as a date.

    The use of Oracle's to_date() function in my patch works regardless of the users default date format, which can vary in different countries and regions.

    Can a function retuning the date in the DB-specific format be added in somewhere?