committers/resources/scripts/sql.php

<?php 
/*******************************************************************************
 * Copyright (c) 2006 Eclipse Foundation and others.
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the Eclipse Public License v1.0
 * which accompanies this distribution, and is available at
 * http://www.eclipse.org/legal/epl-v10.html
 *
 * Contributors:
 *    Wayne Beaton (Eclipse Foundation)- initial API and implementation
 *******************************************************************************/

	#*****************************************************************************
	#
	# sql.php
	#
	# Author: 		Wayne Beaton
	# Date:			February 16, 2006
	#
	# Description:
	#    This file contains function that interact with the database.
	#****************************************************************************

class ResourceBroker {
	var $messages = '';
	var $error;

	private function add_message($message) {
		$this->messages .= "$message<br/>";
	}
	
	private function begin_transaction() {
		$this->add_message("Begin transaction");
		mysql_query("begin");
	}

	private function commit_transaction() {
		$this->add_message("Commit transaction");
		mysql_query("commit");
	}

	function rollback_transaction() {
		$this->add_message("Rollback transaction");
		mysql_query("rollback");
	}

	function create_resource($title, $description, $type, $image_path, $categories) {
		$this->begin_transaction();
		
		$this->add_message("Creating resource...");
		try {
			$id = $this->primitive_create_resource($title, $description, $type, $image_path);
			$this->primitive_update_categories($id, $categories);
			$this->commit_transaction();
			return $id;
		} catch (Exception $e) {
			$this->add_message($e->getMessage());
			$this->error = $e->getMessage();
			$this->rollback_transaction();
		}
	}
	
	function delete_resource($id) {
		$this->begin_transaction();
		try {
			$this->primitive_delete_resource($id);
			$this->primitive_delete_resource_category_mappings($id);
			$this->primitive_delete_resource_links($id);
			$this->commit_transaction();
		} catch (Exception $e) {
			$this->add_message($e->getMessage());
			$this->error = $e->getMessage();
			$this->rollback_transaction();
		}
	}
	
	function update_resource($id, $title, $description, $type, $image_path, $categories) {
		$this->begin_transaction();
		$this->add_message("Updating resource $id");
		try {
			$this->primitive_update_resource($id, $title, $description, $type, $image_path);
			$this->primitive_update_categories($id, $categories);
			$this->commit_transaction();
		} catch (Exception $e) {
			$this->add_message($e->getMessage());
			$this->error = $e->getMessage();
			$this->rollback_transaction();
		}
		$this->add_message("Resource updated");
	}
	
	function add_categories($id, $category_ids) {
	 	if (!is_array($category_ids)) return;
		if (count($category_ids) == 0) return;
		$this->begin_transaction();
		foreach($category_ids as $category_id) {
			$this->add_message("Adding reference to category '$category_id'.");
			$result = mysql_query("select * from resource_category where category_id=$category_id and resource_id=$id");
			if (mysql_fetch_array($result)) {
				$this->add_message("The category is already referenced.");
				continue;
			}
			mysql_query("insert into resource_category (resource_id, category_id) values ($id, $category_id)");
			if ($error = mysql_error()) {
				$this->add_message("An error occurred while adding reference to category '$category_id'.");
				$this->rollback_transaction();
				return;
			}
		}
		$this->commit_transaction();
	}
	
	function create_link($id, $title, $language, $date, $path, $type, $authors) {
		$this->begin_transaction();
		$this->add_message("Creating link for resource $id");
		try {
			$link_id = $this->primitive_create_link($id, $title, $language, $date, $path, $type);
			$this->primitive_update_link_authors($link_id, $authors);
			$this->add_message("Link $link_id created.");
			$this->commit_transaction();
			return $link_id;
		} catch (Exception $e) {
			$this->add_message($e->getMessage());
			$this->error = $e;
			$this->rollback_transaction();
		}			
	}
		
	function update_link($id, $link_id, $title, $language, $date, $path, $type, $authors) {
		$this->begin_transaction();
		$this->add_message("Updating link $link_id");
		try {
			$this->primitive_update_link($id, $link_id, $title, $language, $date, $path, $type);
			$this->primitive_update_link_authors($link_id, $authors);
			$this->commit_transaction();
			$this->add_message("Link updated");
		} catch (Exception $e) {
			$this->add_message($e->getMessage());
			$this->error = $e->getMessage();
			$this->rollback_transaction();
		}			
	}
	
	function delete_link($link_id) {
		$this->begin_transaction();
		try {
			$this->primitive_delete_resource_link($link_id);
			$this->commit_transaction();
		} catch (Exception $e) {
			$this->add_message($e->getMessage());
			$this->error = $e->getMessage();
			$this->rollback_transaction();
		}
	}
	
	function get_type_suggestions_string() {
		$result = mysql_query("select distinct type from link");
		$type_suggestions = '';
		$separator = '';
		while ($row = mysql_fetch_array($result)) {
			$item = $row[0];
			$item = trim($item);
			if (!$item) continue;
			$type_suggestions .= $separator.$item;
			$separator = ', ';
		}
		return $type_suggestions;
	}
	
	function get_language_suggestions_string() {
		$result = mysql_query("select distinct language from link");
		$language_suggestions = '';
		$separator = '';
		while ($row = mysql_fetch_array($result)) {
			$item = $row[0];
			$item = trim($item);
			if (!$item) continue;
			$language_suggestions .= $separator.$item;
			$separator = ', ';
		}
		return $language_suggestions;
	}
	
	function get_link_authors_string($link_id) {
		$result = mysql_query("select name from author, link_author where link_author.link_id=$link_id and link_author.author_id=author.id");
		$link_authors = '';
		$separator = '';
		while ($row = mysql_fetch_array($result)) {
			$link_authors .= $separator.$row[0];
			$separator = ', ';
		}
		return $link_authors;
	}
	
	function get_resource_categories_string($id) {
		$result = mysql_query("select name from category, resource_category where resource_category.resource_id=$id and resource_category.category_id = category.id");
		$categories = '';
		$separator = '';
		while ($row = mysql_fetch_array($result)) {
			$categories .= $separator.$row[0];
			$separator = ', ';
		}
		return $categories;
	}
	
	function update_author($id, $name, $email, $company, $url) {
		$this->add_message("Updating author $id");
		$result = mysql_query("update author set name='$name', email='$email', company='$company', link='$url' where id=$id");
		if ($error = mysql_error()) {
			add_message("Error updating authors: $error");
			return;
		}
		$this->add_message("Author $id updated.");
	}
	
	function delete_author($id) {	
		$this->add_message("Deleting author $id");
		$this->begin_transaction();
		try {
			$this->primitive_delete_author_mappings($id);
			$this->primitive_delete_author($id);
			$this->commit_transaction();
		} catch (Exception $e) {
			$this->add_message($e->getMessage());
			$this->rollback_transaction();
		}			
		$this->add_message("Author $id deleted.");
	}
	
	function update_category($id, $name) {
		$this->add_message("Updating category $id");
		//$name = mysql_escape_string($name);
		$result = mysql_query("update category set name='$name' where id=$id");
		if ($error = mysql_error()) {
			add_message("Error updating category: $error");
			return;
		}
		$this->add_message("Category $id updated.");
	}
		
	function delete_category($id) {	
		$this->add_message("Deleting category $id");
		$this->begin_transaction();
		try {
			$this->primitive_delete_category_mappings($id);
			$this->primitive_delete_category($id);
			$this->commit_transaction();
		} catch (Exception $e) {
			$this->add_message($e->getMessage());
			$this->rollback_transaction();
		}			
		$this->add_message("Category $id deleted.");
	}
	
	function primitive_delete_author($id) {	
		$result = mysql_query("delete from author where id=$id");
		if ($error = mysql_error()) {
			throw new Exception("Error deleting author: $error");
		}
	}	
	
	function primitive_update_link($id, $link_id, $title, $language, $date, $path, $type) {
		mysql_query("update link set title='$title', language='$language', create_date='$date', path='$path', type='$type' where id=$link_id");
		if ($error = mysql_error()) {
			throw new Exception($error);
		}
	}
	
	function primitive_update_resource($id, $title, $description, $type, $image_path) {
		mysql_query("update resource set title='$title', description='$description', type='$type', image_path='$image_path' where id=$id");
		if ($error = mysql_error()) {
			throw new Exception($error);
		}
	}
	
	function primitive_delete_resource($id) {
		$this->add_message("Deleting resource $id");
		mysql_query("delete from resource where id=$id");
		if ($error = mysql_error()) {
			throw new Exception($error);
		}
		
		$this->add_message("Resource $id deleted");
	}
	
	function primitive_delete_resource_category_mappings($id) {
		$this->add_message("Deleting resource/category mappings for $id");
		mysql_query("delete from resource_category where resource_id=$id");
		if ($error = mysql_error()) {
			throw new Exception($error);
		} 
	
		$this->add_message("Resource/category mappings deleted");
	}
	
	function primitive_delete_resource_link($link_id) {
		$this->add_message("Deleting link $link_id.");
		$this->primitive_delete_link_author_mapping($link_id);
		
		$result = mysql_query("delete from link where id=$link_id");
		if ($error = mysql_error()) {
			throw new Exception($error);
		} 
		$this->add_message("Link deleted.");
	}
	
	function primitive_delete_resource_links($id) {
		$this->add_message("Deleting links for resource $id");
		$result = mysql_query("select id from link where resource_id=$id");
		if ($error = mysql_error()) {
			throw new Exception($error);
		} 
		
		while ($row = mysql_fetch_array($result)) {
			$link_id = $row[0];
			$this->primitive_delete_resource_link($link_id);
		}
		
		$this->add_message("Links deleted");
	}
	
	/*
	 * This function deletes all mappings to from the link
	 * with the given $id to its authors.
	 */
	function primitive_delete_link_author_mapping($id) {
		$this->add_message("Deleting link/author mappings for link $id");
		mysql_query("delete from link_author where link_id=$id");
		if ($error = mysql_error()) {
			throw new Exception($error);
		}
		
		$this->add_message("Link/author mappings deleted");
	}
	
	/*
	 * This function deletes all mappings to the author with
	 * the given $id.
	 */
	function primitive_delete_author_mappings($id) {
		$result = mysql_query("delete from link_author where author_id=$id");
		if ($error = mysql_error()) {
			throw new Exception("Error deleting links to author: $error");
		}
		$this->add_message("References to author $id in links deleted.");
	}	
	
	function primitive_delete_category($id) {	
		$result = mysql_query("delete from category where id=$id");
		if ($error = mysql_error()) {
			throw new Exception("Error deleting category: $error");
		}
	}	
	
	/*
	 * This function deletes all mappings to the category with
	 * the given $id.
	 */
	function primitive_delete_category_mappings($id) {
		$result = mysql_query("delete from resource_category where category_id=$id");
		if ($error = mysql_error()) {
			throw new Exception("Error deleting links to author: $error");
		}
		$this->add_message("References to category $id deleted.");
	}
	
	function primitive_create_link($id, $title, $language, $date, $path, $type) {
		mysql_query("insert into link (resource_id, title, language, create_date, path, type) values ($id, '$title', '$language', '$date', '$path', '$type')");
		if ($error = mysql_error()) {
			throw new Exception($error);
		}
		return mysql_insert_id();
	}
	
	function primitive_update_link_authors($link_id, $authors) {
		if (!$authors) return;
		$this->add_message("Updating link authors...");
		$this->primitive_delete_link_author_mapping($link_id);
		
		$authors = split(',', $authors);
		foreach($authors as $author_name) {
			$author_name = trim($author_name);
			if (!$author_name) continue;
			$this->add_message("Searching for author '$author_name'...");
			$result = mysql_query("select id from author where name='$author_name'");
			if ($error = mysql_error()) {
				throw new Exception("Error querying for existing author '$author_name': $error");
			}
			$row = mysql_fetch_row($result);
			if ($row) {
				$author_id = $row[0];
				$this->add_message("Found author with id $author_id...");
			} else {
				$this->add_message("Creating new author record...");
				mysql_query("insert into author (name, email, company, link) values ('$author_name', '', '', '')");
				if ($error = mysql_error()) {
					throw new Exception("Error inserting author '$author_name': $error");;
				}
				$author_id = mysql_insert_id();
			}
			$this->add_message("Inserting reference to author $author_id...");
			mysql_query("insert into link_author (link_id, author_id) values ($link_id, $author_id)");
			if ($error = mysql_error()) {
				throw new Exception("Error inserting mapping to author $author_id: $error");
			}
		}
	}
	
	function primitive_create_resource($title, $description, $type, $image_path) {
		mysql_query("insert into resource (type, title, description, image_path) values ('$type', '$title', '$description', '$image_path')");
		if ($error = mysql_error()) {
			throw new Exception("Error inserting resource: $error");
		}
		return mysql_insert_id();
	}
	
	function primitive_update_categories($id, $categories) {
		$this->primitive_delete_resource_category_mappings($id);
		$categories = split(',', $categories);
		foreach($categories as $category_name) {
			$category_name = trim($category_name);
			if (!$category_name) continue;
			$this->add_message("Searching for category '$category_name'.");
			$result = mysql_query("select id from category where name='$category_name'");
			if ($error = mysql_error()) {
				throw new Exception ("Error querying for existing category '$category_name': $error");
			}
			$row = mysql_fetch_row($result);
			if ($row) {
				$category_id = $row[0];
				$this->add_message("Found category with id $category_id.");
			} else {
				$this->add_message("Creating category $category_name.");
				mysql_query("insert into category (name) values ('$category_name')");
				if ($error = mysql_error()) {
					throw new Exception($error);
				}
				$category_id = mysql_insert_id();
			}
			$this->add_message("Inserting reference to category $category_id.");
			mysql_query("insert into resource_category (resource_id, category_id) values ($id, $category_id)");
			if ($error = mysql_error()) {
				throw new Exception($error);
			}
		}
	}
}
?>