<?php

/*
 *	W-AGORA 4.0
 *	-----------
 *	$Id: mysql.php,v 1.42 2005/03/25 18:23:07 mdruilhe Exp $
 *	Usage:	include file - Database access functions (mySQL)
 *	Author:	Marc Druilhe <mdruilhe@w-agora.com>
 */

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

if (defined("_MYSQL_ACCESS")) return;
define('_MYSQL_ACCESS', 1);

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

class mysql_access extends DBaccess {

/* private: link and query handles */
var $dblink;
var $dbname;
var $Query_ID = 0;

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


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


#	------------------------------------------------------------------
#	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) {

	if ($this->Query_ID) {
		@mysql_free_result($this->Query_ID);
		$this->Query_ID = 0;
	}

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

	$this->Query_ID = @mysql_query($Query_String, $this->dblink);
	$this->Row   = 0;

	$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->Errno = @mysql_errno($this->dblink);
		$this->Error = @mysql_error($this->dblink);
		$this->halt("Invalid SQL: ".$Query_String);
	}

	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 = @mysql_fetch_array($this->Query_ID, MYSQL_ASSOC);
	$this->Row   += 1;
	$this->Errno  = mysql_errno();
	$this->Error  = mysql_error();

	$stat = is_array($this->Record);
	if (!$stat && $this->Auto_Free) {
		@mysql_free_result($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) {
	$status = @mysql_data_seek($this->Query_ID, $pos);
	if ($status)
		$this->Row = $pos;
	else
		$this->halt("seek($pos) failed: result has ".$this->num_rows()." rows");

	return;
}

function affected_rows() {
	return @mysql_affected_rows($this->dblink);
}

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

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

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

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


// mysql uses auto-increment fields
function nextId ($seqname, $table="") {
	return 0;
}

/**
 * return the auto-generated ID returned by the last INSERT query performed
 * @returns		the id of the last autoincremented field
 */
function lastId($seqname, $table="") {
	return mysql_insert_id ($this->dblink);
}

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

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

	if (!empty ($dbhost)) {
		$host = $dbhost;
		if (!empty ($dbport)) {
			$host .= ":$dbport";
		}
	}

	if ($persistent == 1) {
		$this->dblink = mysql_pconnect ($host, $dbuser, $dbpassword);
	} else {
		$this->dblink = mysql_connect ($host, $dbuser, $dbpassword);
	}

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

	if (!mysql_select_db ($dbname, $this->dblink)) {
		$this->Error = mysql_errno().": ".mysql_error();
		$this->halt("mysql_select_db()");
		return ERR_ACCESS;
	}

	$this->dbname = $dbname;

	return 0;
}

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

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

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

# Check if table exists
# ---------------------
	$result = @mysql_List_Tables ($this->dbname);
	if (!$result) {
		echo "mySQL error: ".mysql_errno().": ".mysql_error()."<BR>";
		$this->Halt_On_Error = $tmp;
		return -3;
	}

	$count = mysql_NumRows ($result);
	$i=0;
	while ($i < $count) {
		if ($table == mysql_TableName ($result, $i)) {
			$this->Halt_On_Error = $tmp;
			return -1;	/*  table already exist */
		}
		$i++;
	}

# Create Table
# ------------
	if (!is_array ($field_defs)) {
		$this->Halt_On_Error = $tmp;
		return -4;
	}

	foreach($field_defs as $col=>$size) {
		if (ereg ("^[0-9+]+$", $size) ) {
			if ($size>255) {
				$desc .= ", $col TEXT";
			} else {
				$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":
					if($col == $pk) {
						$key_def = 'PRIMARY KEY '.$col.'_pk';
						$pk = "";
					} else {
						$key_def = 'KEY '.$col.'_idx';
					}
					$desc .= ", $col INT NOT NULL AUTO_INCREMENT, $key_def($col)";
					break;
				default:
					$desc .= ", $col $type";
					break;
			}
		}
	}

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

	$desc = ereg_replace ("^, ", "", $desc);
	$ret = $this->query ("CREATE TABLE $table ($desc)");
	if (!$ret) {
		$this->Halt_On_Error = $tmp;
		return -3;
	}

	$this->Halt_On_Error = $tmp;
	return 0;
}

/**
 * create an index on the specified table
 *
 * Overwritten although the CREATE INDEX syntax is supported, some ISPs (ovh.fr) don't allow it!!
 * 
 * @param   string  $table		the table 
 * @param   string  $cols		the columns ('col1[,col2,...]') on which to set the index 
 * @param   string  $unique		unique index or not
 * @param   string  $indexname	the optional name of the index
 * @since   4.2
 * @access  public
 * @see     dropIndex
 * @return  void
 */
function createIndex ($table, $cols, $unique=false, $indexname="") {

	if (empty($indexname)) {
		$indexname = "$table".'_'. str_replace(',', '_', $cols) . '_idx';
	}

	if ($unique) {
		$ret = $this->query ("ALTER TABLE $table ADD UNIQUE $indexname ($cols)");	
	} else {
		$ret = $this->query ("ALTER TABLE $table ADD INDEX $indexname ($cols)");	
	}

	if (!$ret) {
		return -3;
	}
	return 0;
}


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 = mysql_List_fields($this->dbname, $table, $this->dblink);
	if (!$result) { /* access problem */
		$this->halt("could not list fields from $table");
		return -3;
	}

	mysql_field_seek($result, 0);

#	build columns/values list (set all matching column names)
#	---------------------------------------------------------
	while ($db_field = mysql_fetch_field($result)) {
		$colname = $db_field->name;
		$coltype = $db_field->type;
		if (isset($fields[$colname])) {
			if ($coltype == "int") {
				$values .= ", $colname=" . intVal($fields[$colname]);
			} else {
				$val = addSlashes($fields[$colname]);
				$values .= ", $colname='$val'";
			}
		}
	}

	@mysql_free_result($result);

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

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

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

	$result = $this->query($q);
	if (!$result) {
		return -3;
	}
	return 0;

}

/**
 * drop a column (field) from the specified table
 *
 * @param	string	$table	the table where the field must be deleted
 * @param	string	$col	the name of the field
 * @since	4.1
 * @access	public
 * @see		addField
 * @return	void
 */
function dropField ($table, $name) {
	$this->query ("ALTER TABLE $table DROP COLUMN $col");
}

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 = mysql_List_fields($this->dbname, $table, $this->dblink);
	if (!$result) { /* access problem */
		$this->halt("could not list fields from $table");
		return -3;
	}

	mysql_field_seek($result, 0);

#	build columns/values list (set all matching column names)
#	---------------------------------------------------------
	$cols = "";
	$values = "";
	while ($db_field = mysql_fetch_field($result)) {
		$colname = $db_field->name;
		$coltype = $db_field->type;
		if (isset($fields[$colname])) {
			$cols .= ", $colname";
			if ($coltype == "int") {
				$values .= ", " . IntVal($fields[$colname]);
			} else {
				$val = addSlashes($fields[$colname]);
				$values .= ", '$val'";
			}
		}
	}

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

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

	$result = $this->query("INSERT INTO $table ($cols) VALUES($values)");
	if (!$result) {
		return -3;
	}
	return 0;
}


#	------------------------------------------------------------------
#				User management functions
#	------------------------------------------------------------------

/**
 * returns the latest registered user from the site user database
 *
 * @param	string 	$site		The site where to find the user
 * @returns	array	"userid"	The user's id
 *			"username"	The user's name
 *			"useraddress"	The user's email address
 */
function getNewestUser($site) {
	$result = $this->query("SELECT userid, username, useraddress  FROM ${site}_users WHERE lastlogin>0 ORDER BY unixdate DESC limit 1");
	if (!$this->next_record() ) {
		return -3;
	}
	return $this->Record;
}


#	------------------------------------------------------------------
#				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 = "WHERE cle<>0";
	if (!empty($where)) {
		$whereClause .= " AND $where";
	}

	$result = $this->query("SELECT count(*) as total FROM $forum $whereClause");
	if (!$this->next_record() ) {
		return -3;
	}

	$ret["total"] = (int) $this->f('total');

	if ($ret["total"] == 0) {
		return $ret;
	}

#	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";
	}

	$result = $this->query("SELECT $cols FROM $forum $whereClause $orderClause $limitClause");
	if (!$result) {
		return -3;
	}

	reset ($this->entries);
	reset ($this->children);
	$tcount=0;
	while ($this->next_record() ) {
		$key = $this->Record["cle"];
		$this->Record["parent"]=0;
		$this->entries[$key] = $this->Record;
		$this->children[0][] = $key;
		$tcount++;
	}

	$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

?>
