Database Abstraction


Related tickets: #171
 


As I use ADODB on other projects, I did a little hacking session with my Wikka.
The very big downside of changing to ADODB is that all the Wikka project must be reviewed to do the appropriate changes where MySQL calls are made. Some SQL calls are also to be revised for optimisation or compatibility fix.
ADODB will also add an extra load but I doubt it would be such a big thing considering the... up side.

The up side is that wikka could work on different DB types like postgresql, oracle, MSSQL, etc...

Beware though that I'm no expert at all and probably won't be able to answer much questions about it. I only tested this because I was accustomed to another syntax used on other projects I am involved.

Application


As I am quite lazy, I took the adodb folder from the PostNuke cvs (as it was already on my computer anyway) and copied it to my Wikka/3rdparty/core/adodb.

Structure :

Next was to add some parameters in the wikka.config.php...

//..... under the last values...
  "geshi_tab width" => "4",
  "wakka_version"   => "1.1.6.1",
  "adodb_temp"      => "Temp", // This assumes that you have a temporary folder for caching
  "encoded"         => 0, // See note below
  "dbtype"          => "mysql",
  "pconnect"        => 0
  );


Note: The encoded parameter is used in many CMS. Although I use base64 later like in many CMS and that base64 is not necessarily secure, Wikka stores the user and pass in plain text wich is even less secure.

Then the big change comes...

Added functions : DBInit inside the Wakka class
  function DBInit($config)
  {

      $this->config = $config;
      // ADODB configuration
      global $ADODB_CACHE_DIR;
      $ADODB_CACHE_DIR = realpath($this->config["adodb_temp"] . '/adodb');
      if (!defined('ADODB_DIR')) {
          define('ADODB_DIR', '3rdparty/core/adodb');
      }
      include ADODB_DIR.'/adodb.inc.php';

      // ADODB Error handle
      if ($this->GetConfigValue("sql_debugging")) {
          include ADODB_DIR.'/adodb-errorhandler.inc.php';
      }

      // Decode encoded DB parameters
      if ($this->config["encoded"]) {
          $this->config["mysql_user"]     = base64_decode($this->config["mysql_user"]);
          $this->config["mysql_password"] = base64_decode($this->config["mysql_password"]);
          $this->config["encoded"] = 0;
      }

      $dbtype   = $this->config["dbtype"];
      $dbhost   = $this->config["mysql_host"];
      $dbname   = $this->config["mysql_database"];
      $dbuname  = $this->config["mysql_user"];
      $dbpass   = $this->config["mysql_password"];
      $pconnect = $this->config["pconnect"];

      $this->wikkadb =& ADONewConnection($dbtype);

      if ($pconnect) {
          $dbh = $this->wikkadb->PConnect($dbhost, $dbuname, $dbpass, $dbname);
      } else {
          // itevo: /Go; It's more safe to use NConnect instead of Connect because of the following:
          // If you create two connections, but both use the same userid and password, PHP will share the same connection.
          // This can cause problems if the connections are meant to different databases.
          // The solution is to always use different userid's for different databases, or use NConnect().
          // NConnect: Always force a new connection. In contrast, PHP sometimes reuses connections when you use Connect() or PConnect().
          // Currently works only on mysql (PHP 4.3.0 or later), postgresql and oci8-derived drivers.
          // For other drivers, NConnect() works like Connect().
          $dbh = $this->wikkadb->NConnect($dbhost, $dbuname, $dbpass, $dbname);
      }

      global $ADODB_FETCH_MODE;
      $ADODB_FETCH_MODE = ADODB_FETCH_NUM;

      // force oracle to a consistent date format for comparison methods later on
      if (strcmp($dbtype, 'oci8') == 0) {
          $this->wikkadb->Execute("alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'");
      }

      return true;
  }


Modified functions : Wakka
  // constructor
  function Wakka($config)
  {

    $this->config  = $config; // Might be unnecessary and redundant
    $this->dblink  = $this->DBInit($config);
    $this->VERSION = WAKKA_VERSION;

  }


function Query
  // DATABASE
  function Query($query)
  {
      $start = $this->GetMicroTime();

      // Get a reference to the DB Setup
      $wikkadb =& $this->wikkadb;

      // Execute query
      $result = $wikkadb->Execute($query);

      // On error, show the ADODB ErrorMsg()
      if ($wikkadb->ErrorNo() != 0) {
          die("Query failed: ".$query." (".$wikkadb->ErrorMsg().")");
      }

      if ($this->GetConfigValue("sql_debugging"))
      {
          $time = $this->GetMicroTime() - $start;
          $this->queryLog[] = array("query" => $query,
                                    "time"  => $time);
      }

      // Return result
      return $result;

  }


Load ALL
function LoadAll($query)
{
    $data = array();
   
    if ($result = $this->Query($query)) {
        // Put items into result array.
        for (; !$result->EOF; $result->MoveNext()) {
            $data[] = $result->GetRowAssoc(2);
        }
        // All successful database queries produce a result set, and that result
        // set should be closed when it has been finished with.
        $result->Close();
    }
   
    return $data;
}


Modified action to reflect the use of the new db calls...
highscores.php
<?php
    # highscores.php {{HighScores}}
    # by Chris Tessmer
    # 19 Dec 2002
    # license: GPL

    $prefix   = $this->config["table_prefix"];
    $userstbl = $prefix . 'users';
    $pagestbl = $prefix . 'pages';
   
    $str = "SELECT   Count(*) AS cnt, name
            FROM     $userstbl, $pagestbl
            WHERE    name   = owner
            AND      latest = 'Y'
            GROUP BY name
            ORDER BY cnt DESC";

    $rankQuery =& $this->Query( $str );

    $str = "SELECT COUNT(*) AS total FROM $pagestbl WHERE latest = 'Y'";
    $totalQuery = $this->Query( $str );
    $total  = $totalQuery->fields[0];//mysql_result($totalQuery, 0);

    print( "<blockquote><table>" );

    $i = 0;
    for(; !$rankQuery->EOF; $rankQuery->MoveNext()) //mysql_fetch_array($rankQuery) )
    {
        list($cnt, $name) = $rankQuery->fields;
        $i++;
        $str  = '<tr>';
        $str .= '<td>'.$i.' </td>';
        $str .= '<td>'. $this->Format( $name ) .'</td>';
        $str .= '<td> </td>';
        $str .= '<td>    </td>';
        $str .= '<td>'.$cnt.'</td>';
        $str .= '<td>    </td>';
        $str .= '<td>'.round( ($cnt/$total)*100, 2).'% </td>';
        //$str .= '<td>'.$total.'</td>';
        $str .= '</tr>';
        print( $str );
    }
    // All successful database queries produce a result set, and that result
    // set should be closed when it has been finished with.
    $rankquery->Close();
   
    print( "</table></blockquote>" );
?>


countowned.php
<?php
/**
 * Print number of pages owned by the current user.
 */
$str = 'SELECT COUNT(*) FROM '.$this->config["table_prefix"].'pages WHERE `owner` ';
$str .= "= '" . $this->GetUserName() . "' AND `latest` = 'Y'";
$countquery = $this->Query($str);
$count  = $countquery->fields[0]; //mysql_result($countquery, 0);
echo $this->Link('MyPages', '', $count,'','','Display a list of the pages you currently own');

?>


This won't work on the full setup of course as I only did the minimum to be able to see if wikka was showing something after my changes and it was made on an already installed wikka. But it might help the Wikka dev team see where it can go.

Frank Chestnut. 25-02-2006

CategoryDevelopment
CategoryDevelopmentDiscussion
There are 3 comments on this page. [Show comments]
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki