[horde] short summary on IMP & HORDE with interbase
Alexander Knauer
Here are my results on what to take care when using Interbase-SQL as
DB-backend for HORDE & IMP (current cvs versions):
1. here are the SQL-Statement for creating the tables "auth",
"horde_prefs" and "log_table";
/* Table: AUTH, Owner: SYSDBA */
CREATE TABLE "AUTH"
(
"USER_ID" VARCHAR(32) NOT NULL,
"USER_PASS" VARCHAR(32) NOT NULL,
PRIMARY KEY ("USER_ID")
);
/* Table: HORDE_PREFS, Owner: SYSDBA */
CREATE TABLE "HORDE_PREFS"
(
"UID" VARCHAR(32) NOT NULL,
"SCOPE" VARCHAR(16) default '' NOT NULL,
"PREF_NAME" VARCHAR(32) NOT NULL,
"PREF_VALUE" VARCHAR(1000),
PRIMARY KEY ("UID", "SCOPE", "PREF_NAME")
);
/* Table: LOG_TABLE, Owner: SYSDBA */
CREATE TABLE "LOG_TABLE"
(
"UNIXTIME" INTEGER NOT NULL,
"IDENT" CHAR(16) NOT NULL,
"PRIORITY" INTEGER,
"message" VARCHAR(200),
PRIMARY KEY ("UNIXTIME", "IDENT")
);
GRANT SELECT,INSERT ON log_table TO hordemgr;
GRANT ALL ON horde_prefs TO hordemgr;
GRANT ALL ON auth TO hordemgr;
2. in the PEAR file ibase.php (normally found in your php-install-dir
..../php/DB/ibase.php) you probably need to make two changes:
- the function simpleQuery should look like this:
################################################################
function simpleQuery($query)
{
$ismanip = DB::isManip($query); /*
line newly added */
$this->last_query = $query;
$query = $this->modifyQuery($query);
$result = @ibase_query($this->connection, $query);
if (!$result) {
return $this->raiseError();
}
if ($this->autocommit && $ismanip) { /*
$ismanip condition added /*
ibase_commit($this->connection);
}
// Determine which queries that should return data, and which
// should return an error code only.
return DB::isManip($query) ? DB_OK : $result;
}
##################################################################
- in "function connect" there may be a small writing error:
in the function header the second parameter is spelled
"$persistant"
but in the 16th line of the function this parameter is spelled
"$persistent"
both should be spelled same. This maybe already corrected in
future versions of ibase.php
3. the following database-parameters in the horde/imp config-files are
good for interbase:
HORDE-authentication in /horde/config/horde.php:
$conf['auth']['params'] = array(
"phptype" => "ibase",
"username" => "hordemgr",
"password" => "yourpassword",
"database" => "home/mydbs/horde.gdb",
"hostspec" => "localhost",
"table" => "auth"
);
IMPORTANT!!: the database MUST NOT start with a /
can't tell exactly why, but seems to be a problem of the parser.
HORDE-Preference System Settings in /horde/config/horde.php:
$conf['prefs']['params'] = array(
"phptype" => "ibase",
"username" => "hordemgr",
"password" => "yourpassword",
"database" => "home/mydbs/horde.gdb",
"hostspec" => "localhost",
"table" => "horde_prefs"
);
IMP-Logging and statistics in /horde/imp/config/conf.php:
$conf['log']['conf'] = array(
"dsn" =>
"ibase://hordemgr:yourpassword@localhost/home/mydbs/mydb.gdb"
);
4. in /horde/lib/Prefs/sql.php you need a small modification in order
not to run into an endless-loop:
in "function retrieve" you will find a while-loop which originally
looks like
while ($row && !DB::isError($row)) {
change it to:
while (count($row) > 1 && !DB::isError($row)) {
WHY THIS modification is needed:
the interbase-module for PHP does not know a function
"ibase_fetch_array" which normally fetches a record
into an array, instead of this function, in PEARS ibase.php the
construct (array)ibase_fetch_object is used,
but this construct always returns an array, even when there are no
records anymore in the query, so that the
above while-loop never ends. I know that my solution is ugly, but it
works and I am not this PHP-guru. Maybe
someone will find a better solution. For me it seems to be a
type-casting-problem from object to array...
5. in /horde/lib/Prefs/sql.php you will find the following code-parts
which you have to change
$row['pref_name'] change it to $row['PREF_NAME']
$row['pref_value'] change it to $row['PREF_VALUE']
in /horde/lib/Perms/sql.php you will find the following code-parts
which you have to change
$row['perm'] change it to $row['PERM']
these changes are needed because interbase returns the field-names
in upper-cases when doing a
(array)ibase_fetch_object. Sure you could create your tables with
delimited identifiers (e.g. "pref_name")
so that interbase returns field-names in lower-cases, but then you
would have to change all
SELECT,INSERT,UPDATE statements and put the field-names of these
statments between ' ' so that interbase
recognizes them as delimited identifiers.
This would be much more changes, so take the above ones.
Point 4 & 5 are the only modifications currently which will "break" your
installed sources from the CVS-sources of HORDE/IMP. Point 2 will
probably be included/changed in PEAR for the future.
And I can tell, IT WILL RUN with interbase :)
--
Horde mailing list: http://horde.org/horde/
Frequently Asked Questions: http://horde.org/faq/
To unsubscribe, mail: horde-un...@lists.horde.org