# This Perl class is a utility class for easy use of MySQL databases.
# It was written by Christoph Haas (email@christoph-haas.de) and can
# be used under the terms of the GNU general public license.

package sqlutil;
require 5.005;

use strict;
use Carp;
use DBI;
use Fcntl ':flock';	# for flock constants
use Time::Local;	# for function 'timelocal'

sub new
{
	my ($pkg, $dbfile) = @_;

	# Provide an anonymous hash to contain the main object
	my $self = { };
	bless $self, $pkg;

	my $DATABASE = 'provider';
	my $DATABASEHOST = 'localhost';
	my $DATABASEUSER = 'sqladmin';
	my $DATABASEPW = 'secret';
	$self->{db} = DBI->connect("DBI:mysql:database=$DATABASE;hostname=$DATABASEHOST;port=3306",$DATABASEUSER,$DATABASEPW)
		or croak "FATAL ($0): Error opening database: ".$DBI::errstr;

	return $self;
}

# do a simple SQL query
sub sql_query
{
	my @args = @_;
	my $self = shift @args;
	my $query = shift @args or confess "Parameter for query missing";

	my $sth = $self->{db}->prepare($query);
	$self->{rows_changed} = $sth->execute(@args) or confess("Couldn't execute query '$query'. Database error: ".$self->{db}->errstr);

	return $sth;	# Return the query handle and the number of changed rows
}

# get the result of the last SQL query
sub sql_result
{
	my $self = shift;
	my $sth = shift;

	#my $sth = shift or confess "Parameter for query_handle missing";

	my $result = $sth->fetchrow_hashref or return undef;
	confess ("Couldn't fetch result: ".$sth->err) if $sth->err;

	return $result;	# Return the hash containing the tuple
}

# get the results of a scalar SQL select query
sub sql_scalar
{
	my @args = @_;
	my $self = shift @args;
	my $query = shift @args or confess "Parameter for query missing";

	my $sth = $self->{db}->prepare($query);
	$self->{rows_changed} = $sth->execute(@args) or confess("Couldn't execute query '$query'. Database error: ".$self->{db}->errstr);

	if (my @rows = $sth->fetchrow_array)
	{
		if ($rows[0])
		{
			$sth->finish;
			return $rows[0];
		}
	}
	else
	{
		$sth->finish;
		return undef;
	}
}

# execute a blind sql query
sub sql_doit
{
	my @args = @_;
	my $self = shift @args;
	my $query = shift @args or confess "Missing query for sql_doit()";

	my $sth = $self->{db}->prepare($query);
	$sth->execute(@args) or confess("Couldn't execute query '$query'. Database: ".$self->{db}->errstr);
	$sth->finish;
}

1;

