Page 1 of 2

Help with database class design.

Posted: Mon Aug 21, 2006 10:51 pm
by daedalus__
I honestly thought I posted about this before. I went searching and only found crap that I wrote before.

I am about to start writing a database class but am in the process of designing it.

My goal with this project is to have one class for database connection and querying, and another class that returns results, with methods for iterating through them and using the data.

I already know a couple things that I want:
  • Connect and Disconnect methods
    Database selection and re-selection
    Support for multiple queries - I'm especially conf**kled about how to implement this
    Executing queries from a file
    Methods to aid in debugging
I was thinking about having separate methods for select and insert/update/delete so they can return the amount of rows selected or affected, since the idea is to return 'result' objects, the method used to execute the query can return the number of rows selected or affected, and a separate method can return the result object. I hope someon can follow that. lol

One of things I don't want this to do is aid in the writing of SQL statements. I think that SQL is a good thing to commit to memory and want to avoid having my class generating SQL statements as much as possible.

An important feature I wanted to focus on was debugging. I was thinking about having an array which will hold mysql_error()'s. I am curious about what other things I might implement in this class to help with debugging?

Also, I would like one of the classes in this project to handle the escaping/unescaping data. Maybe the database class will escape data and the result class will unescape data?

I have been looking at alot of other classes and have already ganked some ideas. The point of this thread is design help, I am confident that I can implement most of the features I want.

If nothing else I will just have it as a reference and then other people can gank ideas from it. ^^

Hope to hear from someone soon. :P I'll keep the thread updated.

EDIT:

I was also just thinking maybe I should create methods or another class (child/extender???) that would enable me to store data in a text file or some other useful storage medium. I may write a seperate class for xml stuff but would still like to hear ideas.

Posted: Mon Aug 21, 2006 11:00 pm
by Luke
This may help you:
viewtopic.php?t=52479&highlight=mysql+class

Also... Are you building this class as a learning experience? Why not just use a library like adodb or adodb lite?

Posted: Mon Aug 21, 2006 11:02 pm
by daedalus__
I guess it's a learning experience. I simply don't like using libraries. I don't want to use something I couldn't write myself. I've got quite a ways to go before I am a php-guru so I try to write everything myself.

Really, I'm going to use it in what I guess is a framework I'm writing.

P.S. I was looking for that thread! :P

I was also curious if there any design patterns I might want to look at?

Posted: Mon Aug 21, 2006 11:06 pm
by feyd
I'm noticing several design patterns directly related: Query Object (PoEAA), Result Set (PoEAA), and Iterator (GoF) come to mind quickly.
And several design patterns that might be used: Registry (PoEAA), Observer (GoF) and likely Factory Method (GoF)

PoEAA = Patterns of Enterprise Application Architecture by Martin Fowler.
GoF = Design Patterns by Gamma, Helm, Johnson and Vlissides (nicknamed the Gang of Four)

Posted: Mon Aug 21, 2006 11:08 pm
by Luke
Daedalus- wrote:I simply don't like using libraries.I don't want to use something I couldn't write myself.
Try using a few well-written libraries like simpletest, swiftmailer, adodb, smarty, etc. and get back to me about that one. I have a feeling your opinion will change.
Daedalus- wrote:I've got quite a ways to go before I am a php-guru so I try to write everything myself.
This, I can understand. I am building an entire framework and attempting to write code to deal with almost every aspect of php programming I can think of... just to learn, but this does not mean I rule out the use of libraries. Learn how the library works first, of course, but there is no need to re-invent the wheel. If a library is built well, and suits your project, use it.

Posted: Mon Aug 21, 2006 11:13 pm
by daedalus__
This is all for personal use. I'll use whatever rolls a project out fatest for work.

ChangeUser()

Posted: Wed Aug 23, 2006 10:13 pm
by daedalus__
I want to add a function to change the user that is logged into the mysql server.

It currently works a bit like this:

Code: Select all

function ChangeUser($user, $pass)
{
   $this->Disconnect();
   $this->SetUser();
   if ($pass != $old_pass)
   {
      $this->SetPass();
   }
   $this->Connect($new_info);
}
Is there a better way to do this? At all?

Would this even be useful? I would figure that having different accounts for one site would enable one to add beef up security by only granting needed priveliges at a certain point in the script.

I know PHP3 had a function for this but it was removed.

Posted: Wed Aug 23, 2006 10:18 pm
by feyd
There could be, but the likelihood of needing to change users in the middle of a script seems fairly low. Let's put it this way, it can't really hurt to have such a method. I just don't see many people using it.

Posted: Wed Aug 23, 2006 10:20 pm
by daedalus__
You know, I don't either but since I could see situations where it might be useful right off the top of my head, I decided to include it.

You don't know if there is a better way to accomplish this, do you?

Posted: Wed Aug 23, 2006 10:25 pm
by feyd
As far as I know, that's the way you'd have to do it if you needed to do it (and maintain only one connection). Personally, I wouldn't use the conditional on the password, I'd just call the set.

Posted: Wed Aug 23, 2006 10:26 pm
by daedalus__
I forgot to mention that password is an optional argument

Posted: Wed Aug 23, 2006 10:41 pm
by feyd
Okay, so check if it's the default value, not the current password. It may be better to check if the new user is the same, although someone may just want to cycle the connection for whatever reason. :roll:

Posted: Wed Aug 23, 2006 11:04 pm
by daedalus__
I'll fix it up. I was just wondering if there was some SQL I could use to do that instead of having to reconnect. bleh


:P

Posted: Wed Aug 23, 2006 11:59 pm
by daedalus__
I'm afraid to say anything about this. I like it alot but it could be crap.

Tear it apart and tell me what you think.

It is unfinished, obviously, but I thought I would check with the community for a minute and try gather thoughts and opinions.

I like setters and getters so shush any nonsense about getting rid of them.

:D:D:D:D

EDIT: Also thought I should mention I'm too tired to start explaining any of it but am glad to answer questions tomorrow.

Code: Select all

<?php
class DbLayer
{
	private $results;
	private $links;
	private $errors;
	private $dbhost;
	private $dbuser;
	private $dbpass;
	private $dbname;

	public function __construct($host, $user, $pass, $new_link = null)
	{
		$this->SetDbHost($host);
		$this->SetDbUser($user);
		$this->SetDbPass($pass);
		if (! is_null($new_link) )
		{
			$this->Connect();
		}
	}

	public function Info()
	{
		// Yet to be implemented
	}

	public function Status($link_id = null)
	{
		if ( is_null($link_id) )
		{
			if (! empty($this->links) )
			{
				$status = explode('  ', mysql_stat());
			} else throw new Exception('Cannot check status: Not connected to a server!', 0x00);
		}
		else 
		{
			$status = explode('  ', mysql_stat($this->links[$link_id]));
		}
		print '<pre>';
		print_r($status);
		print '</pre>';
	}

	public function Connect()
	{
		if ($link = mysql_connect($this->GetDbHost(), $this->GetDbUser(), $this->GetDbPass()))
		{
			$this->RegisterEntry('link', $link);
			return $link;
		} else $this->RegisterEntry('error', mysql_error());
	}

	public function UseDb($database)
	{
		$this->SetDbName($database);
		mysql_select_db($this->GetDbName);
	}

	/**
	 * I would figure they (especially me) aren't going to call this unless they need it. I decided that checking the username would just be fluff
	 */
	public function ChangeUser($username, $password = null)
	{
		$this->Disconnect();
		$this->SetDbUser($username);
		if (! is_null($password) )
		{
			$this->SetDbPass($password);
		}
		$this->Connect();
	}

	public function Query($sql, $args)
	{
		// Yet to be implemented
	}

	public function ExecSQL($filename)
	{
		// Yet to be implemented
	}

	/**
	 * $this->RegisterEntry('error', mysql_error());
	 * $this->RegisterEntry('result', $this->Query());
	 * $this->RegisterEntry('link', $this->Connect());
	 */
	public function RegisterEntry($entry_type, $entry)
	{
		switch ($entry_type)
		{
			case 'error':
				$this->errors[count($this->errors)] = $entry;
				return count($this->errors)-1;
				break;
			case 'result':
				$this->results[count($this->results)] = $entry;
				return count($this->results)-1;
				break;
			case 'link':
				$this->links[count($this->links)] = $entry;
				return count($this->links)-1;
				break;
			default:
				throw new Exception('Tried to register unknown or unallowed object!', 0x0f);
				break;
		}
	}

	/**
	 * Returns the specified entry
	 *
	 */
	public function ReturnEntry($registry, $key)
	{
		return $this->$registry[$key];
	}

	public function Disconnect()
	{
		// Yet to be implemened
	}

	public function __destruct()
	{
		// Still designing
	}

	// Setters
	private function SetDbHost($url)
	{
		if ( is_string($url) )
		{
			$this->dbhost = $url;
		} else throw new Exception('Host address must be a string and must be passed as a string!', 0x01);
	}

	private function SetDbUser($username)
	{
		if ( is_string($username) )
		{
			$this->dbuser = $username;
		} else throw new Exception('Username must be a string and also must be passed as a string!', 0x01);
	}

	private function SetDbPass($password)
	{
		if ( is_string($password) )
		{
			$this->dbpass = $password;
			return 1;
		} else throw new Exception('Password must be a string and also must be passed as a string!', 0x01);
	}
	
	private function SetDbName($database)
	{
		if ( is_string($database) )
		{
			$this->dbname = $database;
			return 1;
		}
		else throw new Exception('Database name must be string!', 0x01);
	}
	
	// Getters
	private function GetDbHost()
	{
		return $this->dbhost;
	}

	private function GetDbUser()
	{
		return $this->dbuser;
	}

	private function GetDbPass()
	{
		return $this->dbpass;
	}

	private function GetDbName()
	{
		return $this->dbname;
	}
}
?>

Posted: Thu Aug 24, 2006 12:03 am
by feyd
Why are your setters and getters all private? I can marginally understand the getters, but why the setters?