cvs: pear /Forum/lib/classes/db PearDB.php PearDBStandard.php

mog Mon Oct 29 14:43:46 2001 EDT Added files: /pear/Forum/lib/classes/db PearDB.php PearDBStandard.php Log: Re-added pear db backend with correct names.

Index: pear/Forum/lib/classes/db/PearDB.php +++ pear/Forum/lib/classes/db/PearDB.php <?PHP /** * Pear DB class, contaings inherited functions for other classes using Pear DB. * * @author Tomas V.V.Cox <cox@idecnet.com> */ require_once 'DB.php'; function pear_error($error) { global $FORUM; if (PEAR::isError($error)) { $errorstr = $error->getMessage(); if ($FORUM['DEBUG']) { $errorstr .= '<br>' . $error->getDebugInfo(); } trigger_error('Error: ' . $errorstr, E_USER_ERROR); } } class ForumDB { var $link = false; /** * Connect to SQL server. * * @return resource $link Database link */ function connect() { global $FORUM; if (empty($this->dbh)){ $dsn = array( 'phptype' => $FORUM["PEARDB"], 'dbsyntax' => $FORUM["PEARDB"], // XXXX Fixme 'protocol' => false, 'hostspec' => $FORUM["DBSERVER"], 'database' => $FORUM["DBNAME"], 'username' => $FORUM["DBUSER"], 'password' => $FORUM["DBPASS"] ); $dbh = DB::connect($dsn); if (DB::isError($dbh)) { pear_error($dbh); } $dbh->setErrorHandling(PEAR_ERROR_CALLBACK, 'pear_error'); $this->dbh =& $dbh; } return true; } /** * Used internally to query database and show any error as E_USER_WARNING. * * @param string $SQL SQL query * @return resource $result MySQL result */ function query(&$SQL) { global $FORUM; if ($this->connect()) { $res = $this->dbh->query($SQL); if ($FORUM['DEBUG']) { trigger_error("Query ran: <PRE>\"$SQL\"</PRE>", E_USER_NOTICE); } } return $res; } function insert_id() { return mysql_insert_id($this->link); } /*** * Get a single row, make sure to use LIMIT 1 in queries used with this function. * Returns empty array on false. * * @param string $SQL SQL query * @return array $row Array containing row returned. */ function get_row(&$SQL) { if (empty($this->dbh)) { $this->connect(); } $row = $this->dbh->getRow($SQL, DB_FETCHMODE_ASSOC); // if row is false the query didn't return any rows, // so we return an empty array. if(!$row) return array(); return $row; } function get_col($SQL, $from = null, $to = null) { if (empty($this->dbh)) { $this->connect(); } $cols = array(); $res = $this->query($SQL); if ($from && $to) { for($i = $from; $i <= $to; $i++) { $row = $res->fetchrow(DB_FETCHMODE_ORDERED, $i); $cols[] = $row[0]; } } else { while($row = $res->fetchrow(DB_FETCHMODE_ORDERED)) { $cols[] = $row[0]; } } $res->free(); return $cols; } /** * Get rows by $field * * @param string $SQL SQL Query. * @param string $field Field to use as key in $rows[$field] */ function get_rows_by_field(&$SQL, $field, $from = null, $to = null) { $rows=array(); $res = $this->query($SQL); if ($from && $to) { for($i = $from; $i <= $to; $i++) { $row = $res->fetchrow(DB_FETCHMODE_ASSOC, $i); $rows[$row[$field]] = $row; } } else { while($row = $res->fetchrow(DB_FETCHMODE_ASSOC)) { $rows[$row[$field]] = $row; } } $res->free(); return $rows; } /** * Get rows using $field as key and $field as value * * @param string $SQL SQL query * @param string $field Field to use as key in $rows[$field] * @return array $rows rows * @author Morgan Christiansson <mog@linux.nu> */ function get_vals_by_field(&$SQL, $field) { $rows=array(); $res = $this->query($SQL); while($row = $res->fetchrow(DB_FETCHMODE_ASSOC)) { $rows[$row[$field]] = $row[$field]; } $res->free(); return $rows; } function build_set_query($array) { $SQL = ''; $ret = array(); foreach($array as $key => $val) { if ($val === null) { $add = " $key IS NULL"; } else { $add = " $key = " . $this->dbh->quote($val); } $ret[] = "$add\n"; } return implode($ret, ','); } function build_insert_query($fields, $data) { if (empty($this->dbh)) { $this->connect(); } $fields = explode(' ', $fields); foreach($fields as $field) { $inserts[] = $field; $values[] = $this->dbh->quote($data[$field]); } return ' (' . implode(',', $inserts) . ') VALUES'. ' (' . implode(',', $values) . ')'; } function bool2yn($bool) { if(is_bool($bool)) { return $bool ? "Y" : "N"; } if(!is_string($bool)) { trigger_error("\$bool $bool is a ".gettype($bool)." and not a bool or a string",E_USER_WARNING); return ""; } else { $bool = strtoupper($bool); if($bool == "Y" || $bool == "N") { return $bool; } else { triger_error("\$bool wasn't 'Y' or 'N' although it should, it was $bool",E_USER_WARNING); return ""; } } /* !is_string() */ } } ?> Index: pear/Forum/lib/classes/db/PearDBStandard.php +++ pear/Forum/lib/classes/db/PearDBStandard.php <?PHP if(!class_exists('ForumDB')) { require_once $FORUM['LIB'] . '/classes/db/PearDB.php'; } /** * @author Morgan Christiansson <mog@phorum.org> * @since Phorum 4 Alpha * @access public * @package DB MySQL */ class ForumDBStandard extends ForumDB { /** * Proccess headers * * Adding "replies" and "replies_left" to all parent id's * and create the superparent with id 0. * * Should be used by PhorumList and PhorumRead, isn't currently but this should be implemented some day. * * @author Morgan Christiansson <mog@linux.nu> * @param array &$rows Array with rows to be proccessed, usually what is returned from get_headers() */ function process_headers($rows) { $rows[0] = array( "replies_left" => 0, "message_id" => 0, "parent_id" => 0, ); foreach($rows as $row) { if(!isset($rows[$row["parent_id"]])) { //TODO: better explanation of how to fix if problem occurs. trigger_error("Orphan message (id: $row[message_id]), parent (id: $row[parent_id]) does not exist", E_USER_WARNING); } $rows[$row["parent_id"]]["replies"][]=$row["message_id"]; $rows[$row["parent_id"]]["replies_left"] += 1; } return($rows); } /** * Searches tables for resulst, parsing text is done internally * since different DB backend's have different capabilities. * * @since Phorum 4 Alpha 2 * @author Morgan Christiasson <mog@phorum.org> * @return array $rows */ function search() { //$this->query } /** * Sets approved='N' WHERE forum_id=$forum_id AND thread_id=$thread_id * * @param mixed $forum_id forum id * @param mixed $thread_id thread id * @param string $status New status to set of messages, Y or N * @return bool true|false * @since Phorum 4 Alpha 2 * @author Morgan Christiansson <mog@linux.nu> */ function approve_thread($forum_id, $thread_id, $status="Y") { $this->get_forum_settings($forum_id); $SQL="UPDATE $this->forums[$forum_id] SET approved='$status' WHERE forum_id='$forum_id' AND thread_id='$thread_id'"; return $this->query($SQL) ? TRUE : FALSE; } /** * Sets approved='N' WHERE forum_id=$forum_id AND message_id=$thread_id * * @param mixed $forum_id forum id * @param mixed $message_id message id * @param string $status New status to set of messages, Y or N * @return bool TRUE * @since Phorum 4 Alpha 2 * @author Morgan Christiansson <mog@linux.nu> */ function approve_message($forum_id, $message_id, $status="Y") { $this->get_forum_settings($forum_id); $SQL = "UPDATE $this->forums[$forum_id] SET approved='$status' WHERE forum_id='$forum_id' AND thread_id='$thread_id'"; return $this->query($SQL) ? TRUE : FALSE; } /** * Get settings for $forum_id * * Note: This functions caches all forum data in the $this->forums[] array. * * @param mixed $forum_id Forum ID. * @return array $forum * @author Morgan Christiansson <mog@linux.nu> */ function get_forum_settings($forum_id){ global $FORUM; if(isset($this->forums[$forum_id])) return $this->forums[$forum_id]; $arr =& $this->forums[$forum_id]; $SQL = "SELECT * FROM $FORUM[FORUM_TABLE] WHERE forum_id='$forum_id'"; $arr = $this->get_row($SQL); if(sizeof($arr) == 0) trigger_error("No forum data for forum_id='$forum_id'",E_USER_ERROR); $arr["headers"] = $arr["table_name"]."_headers"; $arr["bodies"] = $arr["table_name"]."_bodies"; return is_array($arr) ? $arr : array(); } /** * Retrieves headers and returns them. * * @param $forum_id mixed * @param $from int Get messages starting with message no. $from * @param $to int Get $to messages. * @param $whattoget int What to get. constant: FORUM_THREAD|FORUM_THREAD_FLOAT|FORUM_FLAT|FORUM_FLAT_FLOAT * @return $rows array Array with row's using "message_id" as key. * @since Phorum 4 Alpha */ function get_headers($forum_id, $from, $to, $whattoget) { $this->get_forum_settings($forum_id); global $FORUM; switch($whattoget) { case FORUM_THREAD: $SQL = "SELECT thread_id FROM ".$this->forums[$forum_id]["headers"]. " WHERE forum_id='$forum_id' AND parent_id=0 AND approved='Y'". " ORDER BY timestamp limit $from,$to"; $threads = implode(",", $this->get_vals_by_field($SQL, "thread_id")); if($threads == "") { return array(); } $SQL = "SELECT *,0 as replies_left FROM ".$this->forums[$forum_id]["headers"]. " WHERE thread_id in ($threads) $cutoff ORDER BY timestamp"; return $this->process_headers($this->get_rows_by_field($SQL,"message_id")); case FORUM_THREAD_FLOAT: //get the $count thread_id's ordered by thread_updated $SQL = "SELECT distinct thread_id, thread_updated FROM ".$this->forums[$forum_id]["headers"]. " WHERE forum_id='$forum_id' AND parent_id=0". " ORDER BY thread_updated DESC"; $ids = implode(',', $this->get_col($SQL)); if (empty($ids)) { return array(); } //get $SQL = "SELECT *, 0 as replies_left FROM ".$this->forums[$forum_id]["headers"]. " WHERE thread_id in ($ids)"; return $this->process_headers($this->get_rows_by_field($SQL,"message_id")); case FORUM_FLAT: $SQL = "SELECT *,0 as replies_left FROM ".$this->forums[$forum_id]["headers"]. " WHERE forum_id='$forum_id' AND parent_id=0". " ORDER BY timestamp limit $from,$to"; return $this->get_rows_by_field($SQL,"message_id"); case FORUM_FLAT_FLOAT: $SQL = "SELECT *,0 as replies_left FROM ".$this->forums[$forum_id]["headers"]. " WHERE forum_id='$forum_id' AND parent_id=0". " ORDER BY thread_updated DESC limit $from,$to"; return $this->get_rows_by_field($SQL,"message_id"); default: trigger_error("Unhandled \$whattoget: $whattoget",E_USER_ERROR); return FALSE; } } /** * Get whole thread * * @param mixed $forum_id * @param mixed $thread_id * @param int $whattoget FORUM_ALL or FORUM_HEADERS, default FORUM_ALL * @return array $rows Array with rows returned * @author Morgan Christiansson <mog@phorum.org> * @since Phorum 4 Alpha */ function get_thread($forum_id, $thread_id, $whattoget=FORUM_ALL) { GLOBAL $FORUM; $messages=array(); switch($whattoget) { case FORUM_ALL: $SQL="SELECT headers.*,bodies.body,0 as replies_left FROM ".$this->forums[$forum_id]["headers"]." AS headers,".$this->forums[$forum_id]["bodies"]." as bodies WHERE headers.thread_id='$thread_id' AND headers.message_id = bodies.message_id AND approved='Y'"; return $this->get_rows_by_field($SQL,"message_id"); case FORUM_HEADERS: $SQL="SELECT * FROM $FORUM[HEADERS] WHERE thread_id = $thread_id AND approved='Y'"; return $this->get_rows_by_field($SQL,"message_id"); default: trigger_error("Unhandled \$whattoget: $whattoget",E_USER_ERROR); } } /** * Used by PhorumPost to retrieve header information for a single message. * Not currently used anywhere else as of Phorum 4 Alpha. * * @param $forum_id int forum_id * @param $message_id mixed id of message to return * @param $whattoget int FORUM_ALL|FORUM_HEADERS|FORUM_BODIES * @author Morgan Christiansson <mog@phorum.org> * @since Phorum 4 Alpha */ function get_message($forum_id, $message_id, $whattoget=0){ GLOBAL $FORUM; $messages=array(); switch ($whattoget) { case FORUM_ALL: $SQL="SELECT $FORUM[HEADERS].*,$FORUM[BODIES].body FROM $FORUM[HEADERS],$FORUM[BODIES] WHERE $FORUM[HEADERS] = $message_id AND $FORUM[HEADERS].message_id = $FORUM[BODIES].message_id"; break; case FORUM_HEADERS: $SQL="SELECT * FROM ".$this->forums[$forum_id]["headers"]." WHERE message_id = $message_id"; break; case FORUM_BODIES: $SQL="SELECT * FROM $FORUM[BODIES] WHERE message_id = $message_id"; break; default: trigger_error("get_message: Unknown \$whattoget: $whattoget",E_USER_ERROR); break; } $row = $this->get_row($SQL); if(sizeof($row) == 0) trigger_error("get_message: \$this->get_row returned 0 rows, unknown message_id!",E_USER_WARNING); return $row; } /** * @author Morgan Christiansson <mog@phorum.org> * @since Phorum 4 Alpha */ function get_forums($parent_id=1) { global $FORUM; $SQL="SELECT * FROM $FORUM[FORUM_TABLE] WHERE parent_id=$parent_id AND active='Y'"; return $this->get_rows_by_field($SQL, "forum_id"); } /** * @author Morgan Christiansson <mog@phorum.org> * @since Phorum 4 Alpha 2 */ function get_all_forums() { global $FORUM; $SQL="SELECT * FROM $FORUM[FORUM_TABLE]"; return $this->get_rows_by_field($SQL, "forum_id"); } /** * @author Morgan Christiansson <mog@phorum.org> * @since Phorum 4 Alpha 2 */ function get_all_folders() { global $FORUM; $SQL="SELECT * FROM $FORUM[FORUM_TABLE] WHERE is_folder='Y'"; $arr = $this->get_rows_by_field($SQL, "forum_id"); return is_array($arr) ? $arr : array(); } /** * Inserts message to DB. * * @author Morgan Christiansson <mog@phorum.org> * @since Phorum 4 Alpha * @param $message array Array containing fields to be inserted. */ function insert_message($message) { $this->connect(); $id = $this->dbh->nextID('sequence'); $SQL = "INSERT INTO ".$this->forums[$message["forum_id"]]["bodies"]."\n". "(message_id, body) ". "VALUES ($id, " . $this->dbh->quote($message['body']) . ")"; $this->query($SQL); $message["message_id"] = $id; if ($message["thread_id"] == 0) { $message["thread_id"] =& $message["message_id"]; $message["parent_id"] = 0; } $message["email_reply"] = $this->bool2yn($message["email_reply"]); $message["approved"] = $this->bool2yn($message["approved"]); if(!isset($message["timestamp"])) { $message["timestamp"] = mktime(); } $fields = "message_id forum_id thread_id parent_id timestamp author user_id subject email host email_reply approved"; $SQL = "INSERT INTO ".$this->forums[$message["forum_id"]]["headers"]. $this->build_insert_query($fields, $message); $this->query($SQL); /* query failed, delete body XXXX Fixme error handler $SQL="DELETE FROM ".$this->forums[$message["forum_id"]]["bodies"]." WHERE message_id=$message[message_id]"; $this->query($SQL); trigger_error("insert canceled",E_USER_WARNING); */ if ($message["thread_id"] != $message["message_id"]) { $SQL="UPDATE " . $this->forums[$message["forum_id"]]["headers"] . " SET thread_updated = $message[timestamp]". " WHERE thread_id = $message[thread_id]"; $this->query($SQL); } /*** UPDATE STATISTICS ***/ $SQL = sprintf("SELECT COUNT(message_id) as count,". "MAX(message_id) as max ". "FROM ". $this->forums[$message["forum_id"]]["headers"] . " WHERE forum_id=%d GROUP BY forum_id" , $message["forum_id"]); $row = $this->get_row($SQL); $SQL = "UPDATE phorum4 ". "SET count=$row[count], max_id=$row[max] ". "WHERE forum_id=$message[forum_id]"; $this->query($SQL); return true; } /** * @author Brian Moon <brian@phorum.org> * @since Phorum 4 Alpha */ function get_server_vars(){ $vars[]=array("name"=>"DBSERVER", "description"=>"server name", "type"=>"string"); $vars[]=array("name"=>"DBNAME", "description"=>"database name", "type"=>"string"); $vars[]=array("name"=>"DBUSER", "description"=>"user name", "type"=>"string"); $vars[]=array("name"=>"DBPASS", "description"=>"password", "type"=>"string"); $vars[]=array("name"=>"DBCACHE", "description"=>"cache results", "type"=>"bool"); return $vars; } /** * * * @author Morgan Christiansson <mog@linux.nu> */ function modify_forum_settings($forum_id, $settings) { global $FORUM; $SQL="UPDATE $FORUM[FORUM_TABLE] SET\n". $this->build_set_query($settings). "WHERE forum_id='$forum_id'"; return($this->query($SQL)); } function create_folders_table() { $SQL = " CREATE TABLE $FORUM[TABLE_NAME] ( forum_id SMALLINT() unsigned NOT NULL PRIMARY KEY auto_increment, parent_id SMALLINT() unsigned NOT NULL, name VARCHAR(50) NOT NULL, description VARCHAR(255) NOT NULL, table_name VARCHAR(100) NOT NULL DEFAULT 'phorum4', default_skin VARCHAR(100) NOT NULL DEFAULT 'simple', active enum('Y','N') NOT NULL, is_folder ENUM('Y','N') DEFAULT 'N', count INT() UNSIGNED NOT NULL, last_post_author CHAR(37) NOT NULL, last_post_timestamp INT() UNSIGNED NOT NULL, KEY parent_id (parent_id), KEY active (active), ) "; $this->query($SQL); } /** * @author Morgan Christiansson <mog@linux.nu> */ function create_forum($settings) { global $FORUM; $SQL="INSERT INTO $FORUM[FORUM_TABLE] SET\n". $this->build_set_query($settings); my_var_dump($SQL); $SQL="CREATE TABLE $settings[table_name]_headers IF NOT EXISTS ( message_id INT() unsigned NOT NULL PRIMARY KEY auto_increment, forum_id SMALLINT() unsigned NOT NULL, thread_id INT() unsigned NOT NULL, parent_id INT() unsigned NOT NULL, thread_updated INT() unsigned NULL, timestamp INT() unsigned NOT NULL, user_id INT() unsigned NOT NULL, author CHAR(37) NOT NULL, subject CHAR(40) NOT NULL, email CHAR(50) NOT NULL, host CHAR(50) NOT NULL, approved ENUM('N','Y') NOT NULL, ) "; $SQL="CREATE TABLE $settings[table_name]_bodies ( message_id INT() unsigned NOT NULL PRIMARY KEY auto_increment, body text NOT NULL, ) "; } } // end class ?>