ordersprinter/spider/php/database/database.php

<?php
require_once (__DIR__. '/../dbutils.php');


class Database {
	
	/**
	 * Open a connection to the data base.
	 * Don't use the DbUtils function to be sure that not the previous config.php but the new parameters
	 * are used for the connection!
	 *
	 * @param unknown $host
	 * @param unknown $dbname
	 * @param unknown $user
	 * @param unknown $pass
	 * @return NULL|PDO: null if no connection could be established, otherwise the PDO instance
	 */
	public static function openDbAndReturnPdo($host,$dbname,$user,$pass) {
		$dsn = 'mysql:host=' . $host . ';dbname=' . $dbname;
		$user = $user;
		$password = $pass;
		$pdo = null;
		try {
			$pdo = new PDO($dsn, $user, $password);
			$pdo ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		}
		catch (PDOException $e) {
			return null;
		}
		return $pdo;
	}
	
	static function doSQL($pdo,$sql) {
		$stmt = $pdo->prepare(DbUtils::substTableAlias($sql));
		$stmt->execute();
	}
	
	/**
	 * Execute the SQL command, but in case of errors catch and ignore them.
	 * This method is intended to be used during installation when the existence of
	 * database tables can not be guaranteed.
	 * 
	 * @param unknown $pdo
	 * @param unknown $sql the command to be executed
	 */
	static function doSqlSuppressError($pdo,$sql) {
		try {
			$stmt = $pdo->prepare(DbUtils::substTableAlias($sql));
			$stmt->execute();
		} catch (Exception $e) {
			// nothing - table not present or whatever...
		}
	}

	/**
	 * To clean up the database before a fresh installation drop the
	 * tables that will be recreated for the instance. If the tables
	 * do not exist (first installation) then errors are suppressed.
	 * @param unknown $pdo
	 */
	static function dropTables($pdo) {
		self::doSqlSuppressError($pdo, "drop TABLE `%config%`");
		self::doSqlSuppressError($pdo, "drop TABLE `%clients%`");
	}
	
	static function createConfigTable($pdo,$prefix) {
		$sql = "
			CREATE TABLE `%config%` (
			`id` INT (10) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
			`name` VARCHAR ( 1000 ) ,
			`setting` VARCHAR ( 10000 )
			) CHARACTER SET latin1 COLLATE latin1_german1_ci ENGINE = InnoDb ;
			";
		$stmt = $pdo->prepare(DbUtils::substTableAliasWithPrefix($sql,$prefix));
		$stmt->execute();
	}
	
	static function createClientsTable($pdo,$prefix) {
		$sql = "
			CREATE TABLE `%clients%` (
				`id` INT (10) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
				`name` VARCHAR ( 100 ) ,
				`url` VARCHAR ( 200 ) ,
				`remoteaccesscode` VARCHAR ( 200 ),
				`basicauthuser` VARCHAR ( 50 ),
				`basicauthpass` VARCHAR ( 50 ),
				`remark` VARCHAR ( 300 )
			) CHARACTER SET latin1 COLLATE latin1_german1_ci ENGINE = InnoDb ;
			";
		$stmt = $pdo->prepare(DbUtils::substTableAliasWithPrefix($sql,$prefix));
		$stmt->execute();
	}

	/**
	 * Create empty tables for the installation.
	 * @param unknown $pdo
	 * @param unknown $prefix
	 */
	static function createEmptyTables($pdo,$prefix)
	{
		self::createConfigTable($pdo, $prefix);
		self::createClientsTable($pdo, $prefix);
	}
	
	/**
	 * Set the version as parameter into the config table. This version
	 * can be used for the update procedure to check from which source
	 * version the software needs to be updated.
	 * 
	 * @param unknown $pdo
	 * @param unknown $prefix
	 * @param unknown $version
	 */
	static function setVersion($pdo,$prefix,$version) {
		self::changeOneConfigItem($pdo, "version", $version, $prefix);
	}
	
	/**
	 * Set the access password that is needed to use spider
	 * 
	 * @param unknown $pdo
	 * @param unknown $prefix
	 * @param unknown $password
	 */
	static function setAccessPassword($pdo,$prefix,$password) {
		self::changeOneConfigItem($pdo, "accesspassword", $password, $prefix);
	}
	
	static function setRefreshRate($pdo,$prefix,$rate) {
		if (is_null($prefix)) {
			// is not part of installer procedure -> use prefix in config
			$prefix = TAB_PREFIX;
		}
		self::changeOneConfigItem($pdo, "refreshrate", $rate, $prefix);
	}
	
	/**
	 * Change only one item in the config table. If the item does not exist
	 * at the moment it will be created.
	 * The method expects that there is only one set with the given parameter,
	 * it does not check for duplicates!
	 * 
	 * @param unknown $pdo
	 * @param unknown $theItem the name of the parameter
	 * @param unknown $theValue the value that shall be assigned to the parameter
	 * @param unknown $prefix table prefix
	 */
	static private function changeOneConfigItem($pdo,$theItem,$theValue,$prefix) {
		// is the value already there, or has it to be created?
		$sql = "SELECT setting from %config% WHERE name=?";
	
		$stmt = $pdo->prepare(DbUtils::substTableAliasWithPrefix($sql,$prefix));
		$stmt->execute(array($theItem));
		$row = $stmt->fetchObject();
	
		if ($stmt->rowCount() > 0) {
			$sql = "UPDATE %config% SET setting=? WHERE name=?";
			$stmt = $pdo->prepare(DbUtils::substTableAliasWithPrefix($sql,$prefix));
			$stmt->execute(array($theValue,$theItem));
		} else {
			$sql = "INSERT INTO %config% (`id` , `name`,`setting`) VALUES (NULL , ? , ?)";
			$stmt = $pdo->prepare(DbUtils::substTableAliasWithPrefix($sql,$prefix));
			$stmt->execute(array($theItem,$theValue));
		}
	}
	
	/**
	 * Get a setting from the config table as a string value. If the item does not
	 * exist an empty string ("") will be returned.
	 * 
	 * @param unknown $pdo
	 * @param unknown $key
	 * @return string|unknown
	 */
	static public function getConfigItem($pdo,$key) {
		$sql = "SELECT setting FROM %config% WHERE name=?";
	
		if (is_null($pdo)) {
			return "";
		}
		$stmt = $pdo->prepare(DbUtils::substTableAlias($sql));
		$stmt->execute(array($key));
		$row = $stmt->fetchObject();
	
		if ($stmt->rowCount() == 0) {
			return "";
		}
	
		$theValue = $row->setting;
	
		if (is_null($theValue)) {
			return "";
		} else {
			return $theValue;
		}
	}
	
}

?>