PhpWiki / Bugs / #557 Oracle Database Not working

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'"

Oracle Database Not working

Group

Searches

Help

#557 Oracle Database Not working

Discussion