# this is a perl module for the reviews project.
# it pretty much just handles direct database interaction
# written by loren jan wilson, october 2003
#

use strict;
use DBI;

sub make_db_handle {
	my $dbh = DBI->connect("DBI:mysql:database=reviews", 
			"**Username**", "**Password**",
			{'RaiseError' => 1});
	return $dbh;
}

sub make_db_handle_cgi {
	my $dbh = DBI->connect("DBI:mysql:database=reviews;host=solar", 
			"**Username**", "**Password**",
			{'RaiseError' => 1});
	return $dbh;
}

sub dbquery {
	my $dbh = $_[0]; # database handle
	my $query = $_[1]; # query string, like "SELECT * FROM foo"
	#warn qq/$query/;
	my $return; # value we will return
	my $sth = $dbh->prepare($query);
	# make sure the handle is valid at all...
	if (!$sth) {
		warn "Error:" . $dbh->errstr . "\n" if $main::DEBUG;
		return undef;
	}
	# execute the query and make sure there wasn't an error
	if (!$sth->execute) {
		warn "Error:" . $sth->errstr . "\n" if $main::DEBUG;
		return undef;
	}

	# get names of fields
	#my $names = $sth->{'NAME'};
	# get number of fields (we need this to create the data structure)
	my $numfields = $sth->{'NUM_OF_FIELDS'};
	# get number of rows returned
	my $numrows = $sth->rows;

	# if we didn't get any rows, the query didn't return results...
	if ($numrows == 0) {
		$return = undef;
	}
	# if the number of fields is 1 AND number of rows is 1,
	# just return a string.
	elsif (($numrows == 1) && ($numfields == 1)) {
		my $ref = $sth->fetchrow_arrayref;
		$return = $$ref[0];
	}
	# if the number of rows is one but there are many fields, 
	# return an array reference to a simple array.
	elsif (($numrows == 1) && ($numfields > 1)) {
		my $ref = $sth->fetchrow_arrayref;
		$return = $ref;
	}
	# conversely, if there are lots of rows but one field, 
	# return an array reference to a simple array that we build.
	elsif (($numrows > 1) && ($numfields == 1)) {
		my @rows;
		while (my $ref = $sth->fetchrow_arrayref) {
			push @rows, $$ref[0];
		}
		my $ref = \@rows;
		$return = $ref;
	}
	# finally, if there are multiple rows and fields,
	# return an array reference to an array(rows) of arrays(fields).
	elsif (($numrows > 1) && ($numfields > 1)) {
		my @rows;
		while (my $ref = $sth->fetchrow_arrayref) {
			# this line is tricky but necessary...$ref keeps 
			# changing so we can't just push that.	
			push @rows, [ @$ref ];
		}
		my $ref = \@rows;
		$return = $ref;
	}
	# if there are multiple rows but no fields,
	# we made an update request or insert request. return undef.
	elsif (($numrows >= 1) && ($numfields == 0)) {
		$return = undef;
	}
	# every possibility should be accounted for above.
	else {
		die "this can't happen! numrows $numrows numfields $numfields";
	}
	$sth->finish;
	return $return;
}

1;

__END__