~danieru-dressler/gsoc2011danieldressler/sqllayer

package sqlLayer;

use strict;
use warnings;
use Data::Dumper;

use DBI;
use phonebook;


sub new( $ $ $ ) {
	my ($class, $dbHandle, $dbType, $version) = @_;
	my $this = {};
	bless $this, $class;
	
	$this->{'dbh'} = $dbHandle;
	
	$this->{'type'} = $dbType;
	
	
	#default to 0, perfect compatablity with C primary xml parsing
	# 1 = compatibility of C combo parsing
	# 2 = multilingual support and printers_byname in drivers
	$this->{'version'} = 0;
	$this->{'version'} = $version if(defined($version));
	
	my $phonebook = phonebook->new($this->{'version'});
	$this->{'printerPhonebook'} = $phonebook->printer();
	$this->{'driverPhonebook'}  = $phonebook->driver();
	$this->{'optionPhonebook'}  = $phonebook->option();
	$this->{'schemaPhonebook'}  = $phonebook->schema();
	
	
	return $this;
}

#TODO: Move this and the version in xmlParse to a helper lib
sub getCleanId {
	my ($id) = @_;
	$id =~ s/^[^\/]*\///;
	#remove everything before the leading slash
	return $id;
}

sub getSchema {
	my ($this, $table) = @_;
	
	return $this->{'schemaPhonebook'}{$table};
}

sub initDatabase {
	my ($this) = @_;
	
	#Create the Tables
	foreach my $table (keys %{$this->{'schemaPhonebook'}}) {
		my $schema = $this->getSchema($table);
		my $sth = $this->{'dbh'}->prepare($schema);
		
		$sth->execute();
	}
	$this->{'dbh'}->commit();
}

sub getSortedKeys {
	my ($this, $data) = @_;
	
	my @keys;
	foreach my $key (keys %{$data}) {
		push(@keys, $key);
	}
	
	@keys = sort(@keys);
	
	#create a string that sums the contents of @keys, this allows us to identify 
	#similar sets of keys
	my $sumString;
	foreach my $key (@keys) {
		$sumString .= $key;
	}
	
	return (\@keys, $sumString);
}

sub getPreparedStatement {
	my ($this, $wantedTable, $data) = @_;

	my ($sortedKeys, $summedKeys) = $this->getSortedKeys($data);
	
	#use cached copy if present
	if (defined($this->{'cache'}{'preparedStatements'}{$wantedTable}{$summedKeys})) {
		return $this->{'cache'}{'preparedStatements'}{$wantedTable}{$summedKeys};
	}
	
	#Create the SQL
	my $statementBegining = "INSERT INTO ".$wantedTable."(";
	
	my $tableKeys = join(",", @{$sortedKeys});
	
	my $statementMiddle = ") VALUES (";
	
	my $bindValues;
	for(my $i = 0; $i < @{$sortedKeys}; $i++) {
		$bindValues .= ',' if ($i > 0);
		$bindValues .= '?';
	}
	
	my $statementEnd = ");";
	
	my $statementSql = $statementBegining . $tableKeys . 
	                      $statementMiddle . $bindValues . $statementEnd;
	
	my $sth = $this->{'dbh'}->prepare($statementSql);
	
	#add the sth to our cache
	$this->{'cache'}{'preparedStatements'}{$wantedTable}{$summedKeys} = $sth;
	
	return $sth;
}

sub setPreparedData {
	my ($this, $data, $phonebook) = @_;
	
	#compute the namespace from the phonebook
	my $nodePath = $phonebook->[0][0];#the nodepath of the first element
	$nodePath =~ m!^/([^/]*)/!;
	my $namespace = $1;
		
	#We sort the data by table and rename if needed.
	foreach my $element (@{$phonebook}) {
		my ($ignore, $source, $mainGroup, $table, $destination, $minorGroup) = @{$element};
		$destination = $source if (!$destination);
		
		#Only sort if the table is defined and if the destination key has not 
		#been set, does not work for complex types 
		if($table && !exists $data->{'prepared'}{$table}{$destination}) {
			
			if($minorGroup == 4) {
				if($namespace eq 'option') {
					$data->{'prepared'}{$table}{$destination} = getCleanId($data->{$source});
				}
			
			} elsif($minorGroup == 7) {#multilingual comments
				foreach my $lang (keys %{$data->{$source}}) {
					if($namespace eq 'printer') {
						my %preparedComment;
						$preparedComment{'id'} = $data->{'id'};
						$preparedComment{'lang'} = $lang;
						$preparedComment{'comments'} = $data->{'comments'}{$lang};
						push(@{$data->{'prepared'}{$table}{$destination}}, \%preparedComment);
					} elsif ($namespace eq 'option') {
						my %preparedComment;
						$preparedComment{'id'} = getCleanId($data->{'idx'});
						$preparedComment{'lang'} = $lang;
						$preparedComment{'longname'} = $data->{'comments'}{$lang};
						push(@{$data->{'prepared'}{$table}{$destination}}, \%preparedComment);
					}
				}
				
			} elsif($mainGroup == 13) { #Languages
				if($namespace eq 'printer') {
					# A complex data key must not be undef
					# since there is no direct key in the table for it
					next() if !defined($data->{$source});
					
					foreach my $lang (@{$data->{$source}}) {
						my $name = $lang->{'name'};
						$data->{'prepared'}{$table}{$name}  = 1;
						
						if(defined($lang->{'level'}) && !($name eq 'proprietary') ) {
							$data->{'prepared'}{$table}{$name.'_level'} = $lang->{'level'};
						}
					}
				}
			} elsif( defined($data->{$source})) {
				$data->{'prepared'}{$table}{$destination} = $data->{$source};
			}
		}
	}
}

sub insertData {
	my ($this, $data, $table) = @_;
	
	my $sth = $this->getPreparedStatement($table, $data);
	
	#Sort data alphabetically, required for prepared statements
	my ($sortedKeys) = $this->getSortedKeys($data);
	my @sortedData;
	foreach my $key (@{$sortedKeys}) {
		push(@sortedData, $data->{$key});
	}
	
	
	if( !$sth->execute(@sortedData) ) {
		
		print Dumper($data);
		die $this->{'dbh'}->errstr . "\n------\n";
		
	}
}

sub insertTranslationData {
	my ($this, $data, $table) = @_;
	foreach my $comment (@{ $data->{'prepared'}{$table}{'comments'} }) {
		$this->insertData($comment, $table);
	}
}

sub pushOption {
	my ($this, $option) = @_;
	$this->setPreparedData($option, $this->{'optionPhonebook'});
	
	$this->insertData($option->{'prepared'}{'options'},'options');
	$this->insertTranslationData($option, 'options_translation');
	
	$this->{'dbh'}->commit;
}

sub pushPrinter {
	my ($this, $printer) = @_;
	
	$this->setPreparedData($printer, $this->{'printerPhonebook'});
	
	$this->insertData($printer->{'prepared'}{'printer'},'printer');
	$this->insertTranslationData($printer, 'printer_translation');
	
	$this->{'dbh'}->commit;
}

1;