<?php

/*
 *  W-AGORA 4.0
 *  -----------
 *  $Id: postgres65.php,v 1.30 2005/06/15 15:15:04 mdruilhe Exp $
 *  Usage:              include file - Database access functions (PostgreSQL >= v6.5)
 *  Author:             Marc Druilhe <mdruilhe@w-agora.com>
 *  Initial file by:    Ron Friedman <friedman@filmmaker.com>
 */

if (!defined('_GLOBALS')) {
	die('Hacking attempt');
}

if (!defined("_PG65_ACCESS")) {
 define("_PG65_ACCESS", 1);

if (!defined("_DBACCESS")) {
	include ("$inc_dir/dbaccess.$ext");
}


class postgres65_access extends DBaccess {

/* private: link and query handles */
var	$Host     = "";
var	$Database = "";
var	$User     = "";
var	$Password = "";

var	$dblink    = 0;
var	$Query_ID;
var	$Auto_Free = 0;     ## Set to 1 for automatic mysql_free_result()

/**
 * Constructor.
 * @return	void
 */
function postgres65_access () {
	$this->dbtype = "postgreSQL";
} // end func


/**
 * Description
 * @var
 * @since     1.0
 * @access    private
 */
var	$sequence_table = "";

#	------------------------------------------------------------------
#				General Database access implementation (cf  : phplib)
#				Copyright (c) 1998,1999 SH Online Dienst GmbH
#					 Boris Erdmann, Kristian Koehntopp
#	------------------------------------------------------------------

/* public: perform a query */
function query($Query_String) {

	$this->Error = "";
	$this->Errno = 0;
	$this->Row   = 0;

	// rewrite the 'LIMIT' (limit #,# not supported)
	if (preg_match("/ LIMIT (.*)/mi", $Query_String, $match)) {
		$limit = trim($match[1]);
		if (ereg(',', $limit) ) {
			list($offset,$rows) = split(',', $limit);
			$Query_String = str_replace($match[1], "$rows OFFSET $offset", $Query_String);			
		}
	}

	$ti = explode(" ", microtime());
	$start = (float)$ti[0]+(float)$ti[1];

	$this->Query_ID = @pg_Exec($this->dblink, $Query_String);

	$this->query_count++;
	$ti = explode(" ", microtime());
	$stop = (float)$ti[0]+(float)$ti[1];
	$elapsed = $stop - $start;
	$this->sql_time += $elapsed;
	

	if (!$this->Query_ID) {
		$this->Error = pg_ErrorMessage($this->dblink);
		$this->Errno = ($this->Error == "")?0:1;
		$this->halt("Invalid SQL: ".$Query_String);
		return ERR_ACCESS;
	}

	if ($this->debug) {
		printf("<tt>Debug: [query %d] %s (exec. time = %.3f ms)</tt><br />\n", $this->query_count, $Query_String, $elapsed*1000);
		flush();
	}

	return $this->Query_ID;
}

/* public: walk result set */
function next_record() {

	$ti = explode(" ", microtime());
	$start = (float)$ti[0]+(float)$ti[1];

	$this->Record = @pg_fetch_array($this->Query_ID, $this->Row++);

	$stat = is_array($this->Record);
	if (!$stat && $this->Auto_Free) {
		pg_freeresult($this->Query_ID);
		$this->Query_ID = 0;
	}

	$ti = explode(" ", microtime());
	$stop = (float)$ti[0]+(float)$ti[1];
	$this->sql_time += ($stop - $start);

	return $stat;
}

/* public: position in result set */
function seek($pos = 0) {
	$this->Row = $pos;
}

function affected_rows() {
	return pg_cmdtuples($this->Query_ID);
}

function num_rows() {
	return pg_numrows($this->Query_ID);
}

function num_fields() {
	return pg_numfields($this->Query_ID);
}

#	------------------------------------------------------------------
#				End of PHPLIB copyright
#	------------------------------------------------------------------

#	------------------------------------------------------------------
#				General database access functions
#	------------------------------------------------------------------

function nextId($seqname) {

	$sequence = $seqname . "_seq";
	$result = @pg_exec($this->dblink, "SELECT NEXTVAL('$sequence') AS nextid");

	if (!$result) {
		$result = @pg_exec($this->dblink, "CREATE SEQUENCE $sequence");
		if (!$result) {
			$this->halt("<BR> nextid() function - unable to create sequence<br> : " . pg_ErrorMessage() );
		}
		$result = @pg_exec($this->dblink, "SELECT NEXTVAL('$sequence') AS nextid");
		if (!$result) {
			$this->halt("<BR> nextid() function - unable to create sequence<br> : " . pg_ErrorMessage() );
		}
	}

	$row = @pg_fetch_array($result, 0);
	$this->autoID[$seqname] = $row["nextid"];
	return $this->autoID[$seqname];
}

function _openDB ($dbhost="", $dbport="", $dbuser="", $dbpassword="", $dbname="", $persistent=0) {

	if (!function_exists('pg_connect') ) {
		$this->Error = sprintf (ERROR_DB_NOT_SUPPORTED, 'postgreSQL');
		$this->halt("pg_connect()");
		return ERR_ACCESS;
	}

	$login_string = "dbname=$dbname";

	if (!empty($dbhost)) {
		$this->Host = $dbhost;
		$login_string .= " host=$dbhost";
	}

	if (!empty($dbport)) {
		$this->Port = $dbport;
		$login_string .= " port=$dbport";
	}

	if (!empty($dbuser)) {
		$this->User = $dbuser;
		$login_string .= " user=$dbuser";
	}

	if (!empty($dbpassword)) {
		$this->Password = $dbpassword;
		$login_string .= " password=$dbpassword";
	}

	if ($persistent == 1) {
		$this->dblink = pg_pconnect ($login_string);
	} else {
		$this->dblink = pg_connect ($login_string);
	}

	if (empty($this->dblink)) {
		return ERR_ACCESS;
	}

	if (!pg_dbname($this->dblink)) {
		$this->Error = pg_errormessage($this->dblink);
		$this->halt("pg_dbname()");
		return ERR_ACCESS;
	}

	return 0;

}

function _closeDB () {
	return @pg_close($this->dblink);
}

function createTable ($table, $field_defs, $pk="") {

	if (!is_array ($field_defs)) {
		echo "warning! invalid argument in createTable(): field_defs is not an array";
		return ERR_BADARG;
	}

	$tmp = $this->Halt_On_Error;
	$this->Halt_On_Error = "report";

# Check if table exists
# ---------------------
	$result = @pg_exec($this->dblink, "select relname from pg_class where relname not like 'pg_%'");
	if (!$result) {
		echo "Postgres error: ".pg_errormessage($this->dblink)."<BR>";
		return ERR_ACCESS;
	}
	$count = pg_NumRows ($result);
	$i=0;
	while ($i < $count) {
		if ($table == pg_result ($result, $i, 0)) {
			$this->Halt_On_Error = $tmp;
			return ERR_EXISTS;	//  table already exist
		}
		$i++;
	}

# Create Table
# ------------
	foreach ($field_defs as $col=>$size) {
		if (ereg ("^[0-9+]+$", $size) ) {
			$desc .= ", $col VARCHAR($size) NOT NULL DEFAULT ''";
		} else {
			$type = strtoupper($size);
			switch ($type) {
				case "INT":
					$desc .= ", $col INT NOT NULL DEFAULT 0";
					break;
				case "AUTO":
					$desc .= ", $col INT";
					break;
				case "TIMESTAMP":
					$desc .= ", $col TIMESTAMP DEFAULT NOW()";
					break;
				case "DATETIME":
				case "DATETIME NULL":
					$desc .= ", $col TIMESTAMP NULL";
					break;
				case "DATETIME NOT NULL":
					$desc .= ", $col TIMESTAMP NOT NULL";
					break;
				default:
					$desc .= ", $col $type";
					break;
			}
		}
	}

	if(!empty($pk)) {
		$pkname = $table. '_pk';
		$desc .= ", CONSTRAINT $pkname PRIMARY KEY ($pk)";
	}

	$desc = ereg_replace ("^, ", "", $desc);

	$this->Halt_On_Error = $tmp;
	return $this->query ("CREATE TABLE $table ($desc)");
}

function updateTable ($table, &$fields, $where="") {

	if (!is_array ($fields) ) {
		echo "warning: updateTable(): item to update is not an array";
		return -3;
	}

 #  gets fields name and type from table
 #  ------------------------------------
	$result = pg_exec ($this->dblink,
	 "select a.attname, t.typname from pg_class c, pg_attribute a, pg_type t where c.relname = '$table' and a.attnum > 0 and	a.attrelid = c.oid and a.atttypid = t.oid");
	if (!$result) { /* access problem */
		$this->halt("could not list fields from $table");
		return ERR_ACCESS;
	}

	$i=pg_numrows($result);
if ($this->debug) echo "numfields: $i <br>";

#	build columns/values list (set all matching column names)
#	---------------------------------------------------------
	while ($i >= 0) {
		$colname = @pg_result($result, $i, 0);
		$coltype = @pg_result($result, $i, 1);
		if (!empty($colname) && (isset($fields[$colname])) ) {
			if ($coltype == "int4") {
				$values .= ", $colname=" . intVal($fields[$colname]);
			} else {
				$val = addSlashes($fields[$colname]);
				$values .= ", $colname='$val'";
			}
		}
		$i--;
	}
	$values = ereg_replace ("^, ", "", $values);

	if (empty($values) ) {
		echo "warning: updateTable(): no value to update";
		return ERR_ACCESS;
	}

	$q = "UPDATE $table SET $values";
	if (!empty($where) ) {
		$q .= " WHERE $where";
	}

if ($this->debug) printf("Debug: query = %s<br>\n", $q);

	$this->Query_ID = pg_Exec($this->dblink, $q);
	if (!$this->Query_ID) {
		echo "Postgres error: ".pg_errormessage()."<BR>";
		return -3;
	}

	return 0;
}

function insertRow ($table, &$fields) {

	if (!is_array ($fields) ) {
		echo "warning: insertRow(): item to insert is not an array";
		return -3;
	}

 #  gets fields name and type from table
 #  ------------------------------------
	$result = pg_exec ($this->dblink, "select a.attname, t.typname from pg_class c, pg_attribute a, pg_type t where c.relname = '$table' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid");

	if (!$result) { /* access problem */
		$this->halt("could not list fields from $table");
		return -3;
	}

	$i=pg_numrows($result);
	if ($this->debug) echo "numfields: $i <br>";

#	build columns/values list (set all matching column names)
#	---------------------------------------------------------
	$cols = "";
	$values = "";
	while ($i >= 0) {
		$colname = @pg_result($result, $i, 0);
		$coltype = @pg_result($result, $i, 1);
		if (!empty($colname) && (isset($fields[$colname])) ) {
			$cols .= ", $colname";
			if ($coltype == "int4") {
				$values .= ", " . IntVal($fields[$colname]);
			} else {
				$val = addSlashes($fields[$colname]);
				$values .= ", '$val'";
			}
		}
		$i--;
	}

	if (empty($values) ) {
		echo "warning: insertRow(): no value to update";
		return -3;
	}

	$cols = ereg_replace ("^, ", "", $cols);
	$values = ereg_replace ("^, ", "", $values);

	$q = "INSERT INTO $table ($cols) VALUES($values)";

if ($this->debug) printf("Debug: query = %s<br>\n", $q);

	$this->Query_ID = pg_Exec($this->dblink, $q);
	if (!$this->Query_ID) {
		echo "Postgres error: ".pg_errormessage()."<BR>";
		return -3;
	}

	return 0;
}

#	------------------------------------------------------------------
#				Forum management functions
#	------------------------------------------------------------------

function getSiteStats ($site, $show_all=false) {
	global $auth;

	$from = "$site S";
	$where = "S.category=0";

/* Currently not enabled (OUTER JOIN not supported before 7.3)
	if (! $show_all) {
		if ( empty($auth->userid) ) {
			$where .= " AND S.state != '0' AND S.rank != 0  AND S.type != 'priv'";
		} elseif ($auth->level < ADMIN) {
			$userid = $this->preserveQuotes($auth->userid);
			$from .= " LEFT OUTER JOIN ${site}_userforum UF ON (S.bn_name = UF.bn_name AND UF.userid= '$userid')";
			$where .= " AND S.state != '0' AND S.rank != 0 AND (S.type != 'priv' OR UF.listpriv='1')";
		}
	}
*/
	$result = $this->query ("SELECT count(S.bn_name) as tforums, sum(S.totalthreads) as tthreads, sum(S.totalnotes) as tnotes, sum(S.att_count) as tfiles, sum(S.att_size) as tsize, max(S.lastnote) as mlast FROM $from WHERE $where");
	if ($this->next_record() ) {
		$stats['totalforums']   = (int) $this->f('tforums');
		$stats['totalthreads']  = (int) $this->f('tthreads');
		$stats['totalnotes']    = (int) $this->f('tnotes');
		$stats['totalfiles']    = (int) $this->f('tfiles');
		$stats['totalfilesize'] = (int) $this->f('tsize');
		$stats['lastnote'] = (int) $this->f('mlast');
	}

	$result = $this->query ("SELECT count(userid) as totalusers FROM ${site}_users WHERE lastlogin>0");
	if ($this->next_record() ) {
		$stats['totalusers'] = $this->f('totalusers');
	}

	return $stats;
}


# gets info about forum $name in site $site
# Postgres 7.0 doesn't support OUTER JOIN
# -----------------------------------------------
function getForum ($site, $name) {
	$halt = $this->Halt_On_Error;
	$this->Halt_On_Error = "no";

	$this->query ("SELECT parent AS cat FROM $site WHERE bn_name='$name'");
	if ($this->next_record() ) {
	    $cat = $this->Record["cat"];
		$this->query ("SELECT bn_title FROM $site WHERE cle='$cat'");
		if ($this->next_record() ) {
			$cat_title = $this->Record["bn_title"];
		}
	}
	$result = $this->query ("SELECT S.*, U.username as ownername, U.useraddress as owneraddress FROM $site S, ${site}_users U WHERE S.bn_name='$name' AND U.userid = S.owner");

	$this->Halt_On_Error = $halt;
	if ($this->next_record() ) {
		$this->Record["cat_id"] = $this->Record["parent"];
		$this->Record["cat_title"] = $cat_title;
		return $this->Record;
	} else {
		return -3;
	}
}

function listForums ($site, $moder="", $sort="", $hide_inactive=0, $cat='') {
	global $auth;
	
	if ($site=="agora") {
		$query = "SELECT S.*, 1 as catorder, U.username as ownername, U.useraddress as owneraddress FROM $site S, ${site}_users U WHERE U.userid = S.owner";
	} elseif (empty($moder)) {
		$query = "SELECT S.*, C.rank AS catorder, U.username as ownername, U.useraddress as owneraddress FROM $site S, $site C, ${site}_users U WHERE S.parent=C.cle AND S.category!=1 AND U.userid = S.owner";
	} else {
# gets all forums for wich $moder is moderator
		$query = "SELECT S.*, C.rank AS catorder, U.username as ownername, U.useraddress as owneraddress FROM $site S, $site C, ${site}_users U, ${site}_userforum UF WHERE S.parent=C.cle AND S.category!=1 AND S.bn_name=UF.bn_name AND UF.userid = '" . $this->preserveQuotes($moder)."' AND UF.modpriv=1";
	}

	if ($hide_inactive) {
		$query .= " AND S.state != '0' AND S.rank != '0'";
	}

	if ($cat != '') {
	    $query .= " AND S.parent='$cat'";
	}

	if (empty($sort)) {
		$order = ",S.bn_title";
	}else {
	    $a=explode (",",$sort);
		while ( list(,$s)=each ($a) ) {
		    $order .= ", S.".trim($s);
		}
	}
	$query .= " ORDER BY catorder, S.rank". $order;
	$result = $this->query ($query);
	reset ($this->entries);
	while ($this->next_record() ) {
		$name=ereg_replace ("^${site}_", "", $this->Record["bn_name"]);
		$forums[$name] = $this->Record;
	}

	if (!is_array($forums)) {
	    return false;
	}

# Hide private forums for unauthorized users
	$showpriv = false;
	if ($site=="agora") {
		$showpriv =true;
	} elseif (empty($auth->userid) ) {
		$list_access =array();
	} elseif ($auth->level < ADMIN) {
		$u = $this->preserveQuotes($auth->userid);
		$r = $this->query ("SELECT bn_name FROM ${site}_userforum WHERE userid='$u' AND listpriv='1'");
		while ($this->next_record() ) {
			$n=$this->Record["bn_name"];
			$list_access[$n]=1;
		}
	} else {
		$showpriv =true;
	}

# Gets all forums that the user can list 
	unset ($this->entries);
	reset ($forums);
	while (list($n,$f) = each($forums) ) {
		$bn = $f["bn_name"];
		if ( ($f["type"]!="priv") || $showpriv || isset($list_access[$bn]) ) {
			$key = $f["cle"];
			$this->entries[$key] = $f;
			$this->children[0][] = $key;
		} else {
			unset ($forums[$n]);
		}
	}
	return $forums;
}


#	------------------------------------------------------------------
#				Messages (notes) management functions
#	------------------------------------------------------------------

function listNotes ($forum, $first=0, $last=0, $sort="", $where="", $limit) {
	global $list_var;

	if (empty($forum)) {
		echo "DBaccess error: listNotes() : forum not defined";
		return -3;
	}

	settype ($first, "integer");
	settype ($last, "integer");
	settype ($limit, "integer");

#	1) gets total count # of notes satisfying the query
#	---------------------------------------------------
	// skip administrative note (old format)
	// don't skip hidden notes (listed in moderate_notes.php)
	$whereClause = "cle<>0";
	if (!empty($where)) {
		$whereClause .= " AND $where";
	}
	$result = @pg_exec ($this->dblink, "SELECT count(*) as total FROM $forum WHERE $whereClause");
	if (!$result) {
		echo "Postgres error: ".pg_errormessage()."<BR>";
		return -3;
	}

	$ret["total"] = @pg_result ($result, 0, "total");
	if ($ret["total"] == 0) {
		return $ret;
	}
	pg_FreeResult($result);

#	2) gets all notes sorted by $sort
#	---------------------------------
	$whereClause = "WHERE cle>0"; // skip administrative record
	if (!empty($where)) {
		$whereClause .= " AND $where";
	}

	$orderClause = (empty ($sort)) ? "ORDER BY unixdate DESC" : "ORDER BY $sort";

# Set first thread and # of threads to be returned
# ------------------------------------------------
	if ($limit>0) {
		if ($last > 0) {
			$start = ($last-$limit)+1;
			$start = ($start<0) ? 0 : $start;
		} else {
			$start = $first;
		}
		$limitClause = "limit $start, $limit";
	} else {
		$start=0;
		$limitClause="";
	}

	$cols = "cle,parent,childs,thread,newest";
	reset($list_var);
	while (list($var,$val) = each($list_var)) {
		if ($val==1) $cols.=",$var";
	}

	$query = "SELECT $cols FROM $forum $whereClause $orderClause";
	$result = @pg_exec ($this->dblink, $query);
	if (!$result) {
		echo "Postgres error: ".pg_errormessage()."<BR>";
		return (-3);
	}

	reset ($this->entries);
	reset ($this->children);
	$tcount=0;
	while ($row = @pg_fetch_array($result, $tcount)) {
		$key = $row["cle"];
		$row["parent"]=0;
		$this->entries[$key] = $row;
		$this->children[0][] = $key;
		$tcount++;
	}
	pg_FreeResult($result);

	$ret["notes"] = $tcount;
	$ret["threads"] = $tcount;
	$ret["first"] = $start;
	$ret["last"] = $start + $tcount - 1;

#	3) set next/previous (1 if TRUE)
#	---------------------------------
	$ret["next"] = ($ret["total"] > $ret["last"]+1) ? 1 : 0;
	$ret["prev"] = ($ret["first"] > 1) ? 1 : 0;

	return $ret;
}

}; // end class

} // defined _PG65_ACCESS

?>
