~drizzle-trunk/drizzle/development

#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# 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 St, Fifth Floor, Boston, MA 02110-1301
# USA
#

package GenTest::SimPipe::DBObject;

require Exporter;
@ISA = qw(GenTest);
@EXPORT = qw(COLUMN_NAME COLUMN_TYPE COLUMN_COLLATION KEY_COLUMN DBOBJECT_NAME DBOBJECT_ENGINE);

use strict;
use DBI;
use GenTest;


use constant DBOBJECT_NAME	=> 0;
use constant DBOBJECT_COLUMNS	=> 1;
use constant DBOBJECT_KEYS	=> 2;
use constant DBOBJECT_DATA	=> 3;
use constant DBOBJECT_ENGINE	=> 4;

use constant COLUMN_NAME		=> 0;
use constant COLUMN_ORIG_NAME		=> 1;
use constant COLUMN_DEFAULT		=> 2;
use constant COLUMN_IS_NULLABLE		=> 3;
use constant COLUMN_TYPE		=> 4;
use constant COLUMN_COLLATION		=> 5;

use constant KEY_NAME	=> 0;
use constant KEY_COLUMN	=> 1;

my $dbname = 'test';


sub new {
	my $class = shift;

	my $dbobject = $class->SUPER::new({
		'name'		=> DBOBJECT_NAME,
		'columns'	=> DBOBJECT_COLUMNS,
		'keys'		=> DBOBJECT_KEYS,
		'data'		=> DBOBJECT_DATA,
		'engine'	=> DBOBJECT_ENGINE
	}, @_);
	
	return $dbobject;
}

sub newFromDSN {
	my ($class, $dsn, $table_name) = @_;

	my $dbh = DBI->connect($dsn);

	return $class->newFromDBH($dbh, $table_name);
}

sub newFromDBH {
	my ($class, $dbh, $table_name) = @_;
	
	my $table_info = $dbh->selectrow_hashref("
		SELECT * FROM INFORMATION_SCHEMA.TABLES
		WHERE TABLE_NAME = ?
		AND TABLE_SCHEMA = ?
	", undef, $table_name, $dbname);

	my $engine = $table_info->{ENGINE};

	my @columns;
	my $sth_columns = $dbh->prepare("
		SELECT * FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = ?
		AND TABLE_SCHEMA = ?
	");
	$sth_columns->execute($table_name, $dbname);

	while (my $column_info = $sth_columns->fetchrow_hashref()) {
		my $new_column;
		$new_column->[COLUMN_NAME] = $new_column->[COLUMN_ORIG_NAME] = $column_info->{'COLUMN_NAME'};
		$new_column->[COLUMN_DEFAULT] = $column_info->{'COLUMN_DEFAULT'};
		$new_column->[COLUMN_IS_NULLABLE] = $column_info->{'COLUMN_IS_NULLABLE'};
		$new_column->[COLUMN_TYPE] = $column_info->{'COLUMN_TYPE'};
		$new_column->[COLUMN_COLLATION] = $column_info->{'COLLATION_NAME'};
		push @columns, $new_column;
	}

	my @keys;
	my $sth_keys = $dbh->prepare("
		SELECT * FROM INFORMATION_SCHEMA.STATISTICS
		WHERE TABLE_NAME = ?
		AND TABLE_SCHEMA = ?
	");
	$sth_keys->execute($table_name, $dbname);

	while (my $key_info = $sth_keys->fetchrow_hashref()) {
		my $new_key;
		$new_key->[KEY_NAME] = $key_info->{'INDEX_NAME'};
		$new_key->[KEY_COLUMN] = $key_info->{'COLUMN_NAME'};
		push @keys, $new_key;
	}

	my @data;
	my $sth_data = $dbh->prepare("SELECT * FROM `$dbname`.`$table_name`");
	$sth_data->execute();
	while (my $row = $sth_data->fetchrow_hashref()) {
		push @data, $row;
	}

	my $dbobject = GenTest::SimPipe::DBObject->new(
		name	=> $table_name,
		engine	=> $engine,
		columns	=> \@columns,
		keys	=> \@keys,
		data	=> \@data
	);
}

sub toString {
	my $dbobject = shift;

	my @column_strings;
	foreach my $column (@{$dbobject->columns()}) {
		next if not defined $column;
		next if not defined $column->[COLUMN_NAME];

		my $column_string = $column->[COLUMN_NAME]." ".$column->[COLUMN_TYPE];
		$column_string .= " COLLATE ".$column->[COLUMN_COLLATION] if defined $column->[COLUMN_COLLATION];
		push @column_strings, $column_string;
	}

	return "" if ($#column_strings == -1);

	foreach my $key (@{$dbobject->keys()}) {
		next if not defined $key;
		my $underlying_column_exists = 0;
		foreach my $column (@{$dbobject->columns()}) {
			$underlying_column_exists = 1 if $column->[COLUMN_NAME] eq $key->[KEY_COLUMN];
		}
		next if !$underlying_column_exists;
		if ($key->[KEY_NAME] eq 'PRIMARY') {
			push @column_strings, "PRIMARY KEY (".$key->[KEY_COLUMN].")";
		} else {
			push @column_strings, "KEY (".$key->[KEY_COLUMN].")";
		}
	}

	my $create_string = "DROP TABLE IF EXISTS ".$dbobject->name().";\n";
	$create_string .= "CREATE TABLE ".$dbobject->name()." ( ".join(", ", @column_strings).") ";
	$create_string .= " ENGINE=".$dbobject->engine() if defined $dbobject->engine();
	$create_string .= " TRANSACTIONAL=0 " if $dbobject->engine() =~ m{Aria}sio;
	$create_string .= ";\n";

	my @rows_data;

	foreach my $row_id (0..$#{$dbobject->[DBOBJECT_DATA]}) {
		next if not defined $dbobject->[DBOBJECT_DATA]->[$row_id];
		my @row_data;
		foreach my $column (@{$dbobject->columns()}) {
			next if not defined $column;
			next if not defined $column->[COLUMN_NAME];
			my $cell = $dbobject->[DBOBJECT_DATA]->[$row_id]->{$column->[COLUMN_NAME]} || $dbobject->[DBOBJECT_DATA]->[$row_id]->{$column->[COLUMN_ORIG_NAME]};
			$cell =~ s{'}{\\'}sgio;
			push @row_data, defined $cell ? "'".$cell."'" : 'NULL';
		}
		push @rows_data, "(".join(',', @row_data).")";
	}

	print "Object ".$dbobject->name()." has ".($#rows_data + 1)." rows\n";

#	my $data_string = "ALTER TABLE ".$dbobject->name()." DISABLE KEYS;\n";
	my $data_string .= "INSERT IGNORE INTO ".$dbobject->name()." VALUES ".join(',', @rows_data).";\n" if $#rows_data > -1;
#	$data_string .= "ALTER TABLE ".$dbobject->name()." ENABLE KEYS;\n";

	return $create_string.$data_string;
	
}

sub name {
	return $_[0]->[DBOBJECT_NAME];
}

sub columns {
	return $_[0]->[DBOBJECT_COLUMNS];
}

sub keys {
	return $_[0]->[DBOBJECT_KEYS];
}

sub data {
	return $_[0]->[DBOBJECT_DATA];
}

sub engine {
	return $_[0]->[DBOBJECT_ENGINE];
}

1;