use strict;
return 1;

=head1 Name

OSSTag - A Social Tagging Engine

Copyright (c) 2006 Open Source Development Labs, Inc.

=cut

package osstag_dbconnection;
use osstag_helper;

sub new($)
{
	my $class = shift;
	my $self = {};
	bless $self, $class;

	my ($dbh) = @_;
	$self->{dbh} = $dbh;

	return $self;
}

=head1 Functions

=head2 preventSQLInjectionForStrings

	Replaces "'" with "\'" because we use "'" as string-delimiter in our sql querys

=cut

sub preventSQLInjectionForStrings($)
{
	my $self = shift;
	my ($text) = @_;
	$text =~ s/\'/\\\'/g;
	return $text;
}

=head2 preventSQLInjectionForNumbers

	Deletes ";" as those have nothing to do in sql-numbers

=cut

sub preventSQLInjectionForNumbers($)
{
	my $self = shift;
	my ($text) = @_;
	$text =~ s/;//g;
	return $text;
}


=head2 doUpdate

	doUpdate is used to insert or update rows in the database

	Parameters:
	databaseconnection: DBI-Handler
	sqlstring: the sql-string to be executed

	Returns:
	in case of an insert: primary-key of the inserted row

=cut

sub doUpdate($)
{
	my $self = shift;
	my ($query) = @_;
	my $sth = $self->{dbh}->prepare($query);
	$sth->execute();
	return $self->{dbh}->{mysql_insertid};
}

=head2 executeQuery

	executeQuery is used to pass select-statements to the database

	Parameters: 
	databaseconnection: DBI-Handler
	sqlstring: the sql-string to be executed

	Returns:
	the result of the sql-query as array of arrays eachrow[eachcolumn[]] 

=cut

sub executeQuery($)
{
	my $self = shift;
	my ($query) = @_;
	my $sth = $self->{dbh}->prepare($query);
	$sth->execute();

	my @result;

	while( my @data = $sth->fetchrow_array())
	{
		push @result, \@data;
	}
	return @result;
}

sub selectPhymapByPhypointer($$)
{
	my $self = shift;
	my ($project, $phypointer) = @_;
	my $query = "select * from osstag_phymap where phy_pointer = '".$self->preventSQLInjectionForStrings($phypointer)."' and project = '".$self->preventSQLInjectionForStrings($project)."'";
	my @result = $self->executeQuery($query);
	return @result;
}

sub selectPhymapsByProject($)
{
	my $self = shift;
	my ($project) = @_;
	my $query = "select * from osstag_phymap where project = '".$self->preventSQLInjectionForStrings($project)."'";
	my @result = $self->executeQuery($query);
	return @result;
}

sub selectSemmapByPhymapFk($)
{
	my $self = shift;
	my ($phymap_fk) = @_;
	return $self->executeQuery("select sm.id, sm.phymap_fk, sm.sem_desc, asp.aspect from osstag_semmap as sm, osstag_aspects as asp where sm.phymap_fk = ".$self->preventSQLInjectionForNumbers($phymap_fk)." and sm.sem_aspect_fk = asp.id");
}

sub selectAssoziationsBySemmapFk($)
{
	my $self = shift;
	my ($semmap_fk) = @_;
	return $self->executeQuery("select * from osstag_assoziations where semmap_fk = ".$self->preventSQLInjectionForNumbers($semmap_fk));
}

sub insertPhymap($$)
{
	my $self = shift;
	my ($phypointer, $project) = @_;
	return $self->doUpdate("insert into osstag_phymap (phy_pointer, project) values ('".$self->preventSQLInjectionForStrings($phypointer)."', '".$self->preventSQLInjectionForStrings($project)."')");
}

sub insertSemmap($$$)
{
	my $self = shift;
	my ($phymap_fk, $semdesc, $semaspect) = @_;
	return $self->doUpdate("insert into osstag_semmap (phymap_fk, sem_desc, sem_aspect_fk) values (".$self->preventSQLInjectionForNumbers($phymap_fk).", '".$self->preventSQLInjectionForStrings($semdesc)."', $semaspect)");
}

sub insertAsssoziation($$$)
{
	my $self = shift;
	my ($semmap_fk, $original_tag, $normalized_tag) = @_;
	return $self->doUpdate("insert into osstag_assoziations (semmap_fk, original_tag, normalized_tag) values (".$self->preventSQLInjectionForNumbers($semmap_fk).", '".$self->preventSQLInjectionForStrings($original_tag)."', '".$self->preventSQLInjectionForStrings($normalized_tag)."')");
}

sub getAspects()
{
	my $self = shift;
	return $self->executeQuery("select id, aspect from osstag_aspects");
}

=head1 License

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License 
version 2 as published by the Free Software Foundation.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.

=head1 Version

Version: 0.2

Date:    07-JUN-2006

=head1 Author

Author:  Jan Kechel (jan@kechel.de)

=cut