~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;