1 files changed, 520 insertions, 0 deletions

diff --git a/it_db_record.class b/it_db_record.class
new file mode 100644
index 0000000..a716117
--- /dev/null
+++ b/it_db_record.class
@@ -0,0 +1,520 @@
+<?php
+/*
+**	$Id$
+**
+**	it_db_record.class - A database record based on it_db_table and it_db
+**
+**	ITools - the Internet Tools Library
+**
+**	Copyright (C) 1995-2003 by the ITools Authors.
+**	This program is free software; you can redistribute it and/or
+**	modify it under the terms of either the GNU General Public License
+**	or the GNU Lesser General Public License, as published by the Free
+**	Software Foundation. See http://www.gnu.org/licenses/ for details.
+*/
+
+/**
+ * Functions to handle MySQL records
+ * @see it_db, it_db_table
+ */
+class it_db_record
+{
+	var $table;		/* Table object */
+	var $handle;		/* SQL handle for select() / fetch_next() */
+	var $keyfieldname;	/* Name of primary key field */
+	var $key;		/* Key of loaded record or empty if no record is loaded */
+	var $data = array();	/* Data fields */
+
+
+/**
+ * Initialize internal data (table object and name of key field)
+ * @param $table Underlying table object (class it_db_table)
+ * @param $keyfieldname Optional name of primary key field, defaults to 'ID'
+ */
+function it_db_record(&$table, $keyfieldname='ID')
+{
+	$this->table = &$table;
+	$this->keyfieldname = $keyfieldname;
+}
+
+
+/**
+ * Change active key field
+ * @param $keyfieldname Optional name of primary key field, defaults to 'ID'
+ */
+function set_key_field($keyfieldname='ID')
+{
+	$this->keyfieldname = $keyfieldname;
+	$this->key = isset($this->data[$keyfieldname]) ? $this->data[$keyfieldname] : '';
+}
+
+
+/**
+ * Select a set of records of this table according to an array of selection
+ * and sorting criteria.
+ * Use fetch_next() to iterate through the result set<br>
+ * Example:<br>
+ * $record->select(array('Type' => 'bar', 'Date >=' => '1999-01-01',
+ *    '-Date <' => 'NOW()', 'User NI' => 'chris'), 'ORDER BY Date');
+ *
+ * @param $params optional array of fieldname => value tupels.
+ * These are ANDed to form a WHERE clause.
+ * fieldname can contain an operator (separated by space), the
+ * default operator is '='. The special operator 'NI' specifies
+ * that the argument must be contained in a comma-separated list.
+ *
+ * @param $sql Optional SQL addendum added after $params (ORDER BY etc.)
+ * @param $fields Optional field list to select (defaults to '*')
+ * @return Number of rows that matched this query
+ * @see it_db_record::fetch_next, it_db_table::construct_sql_clause
+ *
+ */
+function select($params='', $sql='', $fields='*')
+{
+	$this->handle = $this->table->safe_sql_select($this->table->construct_sql_clause($params, $sql), $fields);
+	return $this->table->db->num_rows($this->handle);
+}
+
+
+/**
+ * Post-process data after reading a record.
+ * This is a stub-function that can be overloaded.
+ */
+function _read_post_process()
+{
+}
+
+
+/**
+ * Pre-process data before writing a record.
+ * This is a stub-function that can be overloaded.
+ * @param $tags Reference to update/create tags, can be modified as needed
+ */
+function _write_pre_process(&$tags)
+{
+}
+
+
+/**
+ * Fetch the next record of a select() query
+ * @return true if a record was successfully fetched, false otherwise
+ * @see it_db_record::select
+ */
+function fetch_next()
+{
+	return $this->fetch($this->handle);
+}
+
+
+/**
+ * Fetch the next record from an SQL result into this object.
+ * If no more records are pending, clear the object's data.
+ * @param $sqlresult Result of table::sql_select() or similar
+ * @return 1 if a record was successfully fetched, 0 otherwise
+ */
+function fetch(&$sqlresult)
+{
+	$result = 0;
+
+	if ($this->data = $this->table->db->fetch_assoc($sqlresult))
+	{
+		$this->key = $this->data[$this->keyfieldname];
+		$result = 1;
+	}
+	else
+	{
+		$this->key = "";	/* "No record is loaded" */
+		$this->data = array();	/* Clear local record data */
+	}
+
+	$this->_read_post_process();
+
+	return  $result;
+}
+
+
+/**
+ * Read a record from table
+ * @param $key key field value of the desired row
+ * @return 1 on success, 0 otherwise
+ */
+function read($key)
+{
+	$result = 0;
+
+	if ($key != "")
+	{
+		$result = $this->fetch($this->table->safe_sql_select("WHERE $this->keyfieldname='" . mysql_real_escape_string($key) . "'"));
+	}
+	else
+	{
+		$this->key = "";	/* "No record is loaded" */
+		$this->data = array();	/* Clear local record data */
+	}
+
+	$this->_read_post_process();
+
+	return $result;
+}
+
+
+/**
+ * Create a new record in the table
+ * @see it_db_record::record_create, it_db_record::safe_create
+ * @param $tags array of Fieldname/Value pairs
+ * @param $replace use REPLACE (1) instead of INSERT (otherwise)
+ * @return MySQL result (0 means failure)
+ */
+function create($tags, $replace=0)
+{
+	/* Pre-processing, $tags is passed by reference and may be modified here */
+	$this->_write_pre_process($tags);
+
+	$sql = $replace ? "REPLACE" : "INSERT";
+	$sql .= " INTO " . $this->table->name . " (";
+	$sql2 = '';
+
+	unset($this->data);
+	for ($sep=""; $ti = each($tags); $sep=",")
+	{
+		/* If the field name starts with '-', the value is taken as raw, no escaping
+		   is done and no quotes are put around it. */
+		if (substr($ti[0], 0, 1) == '-')
+		{
+			$ti[0] = substr($ti[0], 1);	/* Strip that '-' sign */
+			$val = $ti[1];
+		}
+		else
+			$val = isset($ti[1]) ? "'".mysql_real_escape_string($ti[1])."'" : 'NULL';
+
+		$this->data[$ti[0]] = $ti[1];	/* Store data for later use */
+
+		$sql .= $sep . $ti[0];
+		$sql2 .= "$sep$val";
+	}
+
+	$sql .= ") VALUES ($sql2)";
+
+	if ($result = $this->table->db->sql_query($sql))
+	{
+		/* If the key element was not given, it means it's an AUTO_INCREMENT thingy. */
+		if (($this->key = $this->data[$this->keyfieldname]) == "")
+		{
+			$result = $this->table->db->safe_sql_query("SELECT LAST_INSERT_ID()");
+			$this->key = mysql_result($result, 0, 0);
+		}
+
+		/* We need all fields from record, not only those we initialised */
+		$this->read($this->key);
+	}
+
+	return $result;
+}
+
+
+/**
+ * Safe version of create (doesn't return in case of failure)
+ * @see it_db_record::create, it_db_record::record_create
+ * @param $tags array of Fieldname/Value pairs
+ * @param $replace use REPLACE (1) instead of INSERT (otherwise)
+ * @return MySQL result (0 means failure)
+ */
+function safe_create($tags, $replace=0)
+{
+	if ($result = $this->create($tags, $replace))
+		return $result;
+	else
+		fail("it_db_record::safe_create() failed: " . $this->table->db->error());
+}
+
+
+/**
+ * Update a record in the table
+ * @param $tags array of Fieldname/Value pairs
+ * @return MySQL result (0 means failure)
+ */
+function update($tags)
+{
+	/* Pre-processing, $tags is passed by reference and may be modified here */
+	$this->_write_pre_process($tags);
+
+	if ($this->key == "")
+		it::fatal("it_db_record::update(): can't update undefined record");
+
+	/* If we have nothing to do, return instead of performing an invalid SQL query */
+	if (!count($tags))
+		return true;
+
+	$sql = "UPDATE " . $this->table->name . " SET ";
+
+	for ($sep="", $raw=0; $ti = each($tags); $sep=",")
+	{
+		/* If the field name starts with '-', the value is taken as raw, no escaping
+		   is done and no quotes are put around it. */
+		if (substr($ti[0], 0, 1) == '-')
+		{
+			$ti[0] = substr($ti[0], 1);	/* Strip that '-' sign */
+			$val = $ti[1];
+			$raw++;
+		}
+		else
+			$val = "'".mysql_real_escape_string($ti[1])."'";
+
+		$sql .= $sep.$ti[0].'='.$val;
+
+		if ($ti[0] == $this->key)
+		{
+			if ($this->data[$this->keyfieldname] != $ti[0])
+				it::fatal("$sql: trying to change key from $this->key.");
+		}
+
+		$this->data[$ti[0]] = $ti[1];
+	}
+
+	$sql .= " WHERE " . $this->keyfieldname . "='" . mysql_real_escape_string($this->key) . "'";
+
+	/* debug("it_db_record::update(): $sql"); */
+	$ret = $this->table->db->safe_sql_query($sql);
+
+	/* Only re-read record if necessary (for performance), but always do post-processing. */
+	if ($raw)
+		$this->read($this->key);
+	else
+		$this->_read_post_process();
+
+	return $ret;
+}
+
+
+/**
+ * Delete the current record from the database
+ * @param $key keyfield value of the record to be deleted. If this is missing, the current record is deleted. 
+ * @return MySQL result (0 means failure)
+ */
+function delete($key="")
+{
+	if (!$key) $key = $this->key;
+
+	if (($this->table->name == "") || ($this->keyfieldname == "") || ($key == ""))
+		it::fatal("it_db_record::delete(): no record\n");
+
+	$sql = "DELETE FROM " . $this->table->name . " WHERE ". $this->keyfieldname . "='" . mysql_real_escape_string($key) . "'";
+	if ($result = $this->table->db->sql_query($sql))
+	{
+		$this->key = "";
+		$this->data = array();
+	}
+	return $result;
+}
+
+
+/**
+ * Return a field of a record.
+ * @param $field field name
+ * @return field value. If the field does not exists, issue an
+ * error message and terminate program execution.
+ */
+function safe_get_field($field)
+{
+	if ($field)
+	{
+		if (isset($this->data[$field]))
+			return $this->data[$field];
+		else it::fatal("it_db_record::safe_get_field(): field \"$field\" not present in record.");
+	}
+	else it::fatal("it_db_record::safe_get_field(): empty field name");
+}
+
+
+/**
+ * Output all fields of a record in (ugly) html format
+ */
+function dump_html()
+{
+	echo "<br>Dump of table " . $this->table->name . ", record " . $this->keyfieldname . " = \"" . $this->key . "\":<br><pre>\n";
+	reset($this->data); next($this->data);
+	while(list($key, $value) = each($this->data))
+	{
+		if ($key > 0) continue;	/* $$$ ugly */
+		echo $key . " = \"" . htmlspecialchars($value) . "\"\n";
+	}
+	echo "</pre><br>\n";
+}
+
+
+/**
+ * Edit a record of a table. Create a new record if necessary.<br>
+ * Example:<br>
+ * $record->edit($id, array('ID' => $id));
+ *
+ * @param $key key value of the row to edit
+ * @param $fixfields Array of fields not editable (with fixed values)
+ * @return 1=changed, 2=created, -1=deleted, 0=nothing changed
+ */
+function edit($key, $fixfields = array())
+{
+	$returnresult = 0;
+	$numfields = $this->table->num_fields();
+
+	if ($_REQUEST['_COMMAND'])
+	{
+		/*
+		**  If the key field is changed, $key will carry a wrong value, so we
+		**  take the correct one from $_RECORD_KEY_VALUE
+		*/
+		$key = $_REQUEST['_RECORD_KEY_VALUE'];
+
+		switch($_REQUEST['_COMMAND'])
+		{
+		case "EDIT":
+			$this->read($key);
+			$newdata = array();
+			$changes = 0;
+			for ($i=0; $i < $numfields; ++$i)
+			{
+				$fieldname = $this->table->fieldnames[$i];
+
+				/* Assumes field order is fixed */
+				if (isset($fixfields[$fieldname]))
+					$newvalue = $fixfields[$fieldname];
+				else if (is_array($_REQUEST["_RECORD_$fieldname"]))
+					$newvalue = join(',', $_REQUEST["_RECORD_$fieldname"]);
+				else
+					$newvalue = $_REQUEST["_RECORD_$fieldname"];
+	
+				if ($this->data[$fieldname] != $newvalue)
+				{
+					$newdata[$fieldname] = $newvalue;
+					$changes = 1;
+				}
+			}
+
+			if ($changes)
+			{
+				if ($this->update($newdata))
+				{
+					echo "<b>Änderung erfolgreich durchgef&uuml;hrt.</b><br>\n";
+					$returnresult = 1;
+				}
+				else
+					fail("Update failed.");
+			}
+			else echo "<b>Keine Änderungen.</b><br>\n";
+			break;
+
+		case "CREATE":
+			$newdata = array();
+			for ($i=0; $i < $numfields; ++$i)
+			{
+				$fieldname = $this->table->fieldnames[$i];
+				
+				if (isset($fixfields[$fieldname]))
+					$newvalue = $fixfields[$fieldname];
+				else if (is_array($_REQUEST["_RECORD_$fieldname"]))
+					$newvalue = join(',', $_REQUEST["_RECORD_$fieldname"]);
+				else
+					$newvalue = $_REQUEST["_RECORD_$fieldname"];
+
+				$newdata[$fieldname] = $newvalue;
+			}
+
+			if ($this->create($newdata))
+			{
+				$key = $this->key;
+				echo "<b>Datensatz erzeugt.</b><br>\n";
+				$returnresult = 2;
+			}
+			else
+				fail("Datensatz konnte nicht erzeugt werden.");
+			break;
+
+		case "DELETE":
+			if ($this->delete($key))
+			{
+				echo "<b>Datensatz gelöscht.</b><br>\n";
+				$returnresult = -1;
+			}
+			else
+				fail("Datensatz konnte nicht gelöscht werden.");
+			break;
+
+		default:
+			fail("Unknown _COMMAND \"{$_REQUEST['_COMMAND']}\"");
+			/* NOT REACHED */
+		}
+	}
+
+	if ($key != "")
+	{
+		$this->read($key);
+		$cmd = "EDIT";
+		$buttontext = "Änderungen übernehmen";
+	}
+	else
+	{
+		$cmd = "CREATE";
+		$buttontext = "Datensatz erzeugen";
+	}
+
+	echo "<form action=\"\" method=\"post\"><input type=\"hidden\" name=\"_COMMAND\" value=\"$cmd\"><input type=\"hidden\" name=\"_RECORD_KEY_NAME\" value=\"$this->keyfieldname\"><input type=\"hidden\" name=\"_RECORD_KEY_VALUE\" value=\"$this->key\">\n";
+	echo "<table border=1 cellspacing=0 cellpadding=4>\n";
+	for ($i=0; $i < $numfields; ++$i)
+	{
+		$fieldname = $this->table->fieldnames[$i];
+
+		/* Do not display fix fields */
+		if (isset($fixfields[$fieldname]))
+			continue;
+
+		$fieldflags = $this->table->fieldflags[$i];
+		$fieldlen  = $this->table->fieldlengths[$i];
+		$size = min($fieldlen, 65);
+
+		if ($fieldname == $this->keyfieldname)
+			echo "<tr><th align=right>$fieldname</th>";
+		else
+			echo "<tr><td align=right>$fieldname</td>";
+
+		if (strstr($fieldflags, "blob"))
+		{
+			if (strlen($this->data["$fieldname"]) < 256)
+				echo "<td><textarea name=\"_RECORD_$fieldname\" rows=4 cols=56 wrap=\"physical\">" . htmlspecialchars($this->data["$fieldname"]) . "</textarea></td></tr>\n";
+			else
+				echo "<td><textarea name=\"_RECORD_$fieldname\" rows=10 cols=75 wrap=\"physical\">" . htmlspecialchars($this->data["$fieldname"]) . "</textarea></td></tr>\n";
+		}
+		else if (strstr($fieldflags, "enum"))
+		{
+			$result = $this->table->db->safe_sql_query("SHOW columns FROM ". $this->table->name . " LIKE '$fieldname'");
+			$options = split("'", mysql_result($result, 0, 1));
+
+			$vals = explode(',', $this->data[$fieldname]);
+
+			for ($j = 0; $j < count($vals); $j++)
+				$values[$vals[$j]] = 1;
+
+			if (strstr($options[0], 'set'))
+				$attr = 'multiple';
+			else
+				$attr = 'set="1"';
+
+			echo "<td><select name=\"_RECORD_${fieldname}[]\" $attr>";
+			for ($j=1; $options[$j]; $j+=2)
+			{
+				$selected = $values[$options[$j]] ? " selected" : "";
+				echo "<option$selected>", $options[$j], "</option>";
+			}
+			echo "</select></td></tr>\n";
+		}
+		else
+			echo "<td><input type=\"text\" size=$size name=\"_RECORD_$fieldname\" maxlength=\"$fieldlen\" value=\"" . htmlspecialchars($this->data["$fieldname"]) . "\"></td></tr>\n";
+	}
+	echo "</table><br><input type=\"submit\" value=\"$buttontext\"></form>\n";
+
+	if ($this->key != "")
+		echo "<form action=\"\" method=\"post\"><input type=\"hidden\" name=\"_COMMAND\" value=\"DELETE\"><input type=\"hidden\" name=\"_RECORD_KEY_NAME\" value=\"$this->keyfieldname\"><input type=\"hidden\" name=\"_RECORD_KEY_VALUE\" value=\"$this->key\"><input type=\"submit\" value=\"Datensatz löschen\"></form>\n";
+
+	return $returnresult;
+}
+
+} /* End class it_db_record */
+?>