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 :)