Menu
▾
▴
[Everydevel-opers] SF.net SVN: everydevel: [956] trunk/ebase
|
From: <pau...@us...> - 2007-05-22 23:11:47
|
Revision: 956
http://svn.sourceforge.net/everydevel/?rev=956&view=rev
Author: paul_the_nomad
Date: 2007-05-22 16:11:44 -0700 (Tue, 22 May 2007)
Log Message:
-----------
Methods to return create table statements and associated tests
Modified Paths:
--------------
trunk/ebase/lib/Everything/DB/Pg.pm
trunk/ebase/lib/Everything/DB/Test/Pg.pm
trunk/ebase/lib/Everything/DB/Test/mysql.pm
trunk/ebase/lib/Everything/DB/Test/sqlite.pm
trunk/ebase/lib/Everything/DB/mysql.pm
trunk/ebase/lib/Everything/DB/sqlite.pm
Property Changed:
----------------
trunk/ebase/
Property changes on: trunk/ebase
___________________________________________________________________
Name: svk:merge
- 16c2b9cb-492b-4d64-9535-64d4e875048d:/wip/ebase:981
a6810612-c0f9-0310-9d3e-a9e4af8c5745:/ebase/offline:17930
+ 16c2b9cb-492b-4d64-9535-64d4e875048d:/wip/ebase:982
a6810612-c0f9-0310-9d3e-a9e4af8c5745:/ebase/offline:17930
Modified: trunk/ebase/lib/Everything/DB/Pg.pm
===================================================================
--- trunk/ebase/lib/Everything/DB/Pg.pm 2007-05-22 23:11:06 UTC (rev 955)
+++ trunk/ebase/lib/Everything/DB/Pg.pm 2007-05-22 23:11:44 UTC (rev 956)
@@ -429,4 +429,187 @@
}
+
+=head2 C<get_create_table>
+
+Returns the create table statements of the tables whose names were passed as arguments
+
+Returns a list if there is more than one table or a string if there is only one.
+
+=cut
+
+### Here we build the create statement manually. It should be OK with
+### the the current everything. However, it doesn't work if we start
+### using other features, such as foreign key constraints.
+
+## The code below has been copied from some php code found here
+## http://www.phpbbstyles.com/viewtopic.php?p=69590&highlight= That
+## code is a bit broken as it uses pg_relcheck, which is not current.
+## CHECK constraints are now handled by pg_constraint. Which is what
+## we'd use if we were going to beef up this method.
+
+sub get_create_table {
+
+ my ( $self, @tables ) = @_;
+
+ @tables = $self->list_tables unless @tables;
+ my %table_def;
+ my $dbh = $self->{dbh};
+
+ foreach (@tables) {
+
+ my $column_def = '';
+ ## First get columns:
+
+ my $sth = $dbh->prepare(
+'SELECT a.attnum, a.attname AS field, t.typname as type, a.attlen AS length, a.atttypmod as lengthvar, a.attnotnull as notnull
+ FROM pg_type t, pg_class c,
+ pg_attribute a
+
+ WHERE c.relname = ?
+ AND a.attnum > 0
+ AND a.attrelid = c.oid
+ AND a.atttypid = t.oid
+ ORDER BY a.attnum'
+ ) || die $DBI::errstr;
+ $sth->execute($_) || die $DBI::errstr;
+
+ my @col_def;
+ while ( my $result = $sth->fetchrow_hashref ) {
+
+ push @col_def,
+ {
+ field => $$result{'field'},
+ data_type => $$result{'type'},
+ notnull => $$result{'notnull'},
+ length => $$result{'length'},
+ lengthvar => $$result{'lengthvar'},
+ attnum => $$result{attnum}
+ };
+
+ }
+ $table_def{$_} = \@col_def;
+
+ }
+
+ ### Now get default values someone who is better at SQL than I
+ ### could do this in one statement with a proper use of LEFT JOIN
+ ### or UNION or something clever
+
+ my $sth = $dbh->prepare(
+ "SELECT d.adsrc AS rowdefault
+ FROM pg_attrdef d, pg_class c
+ WHERE (c.relname = ? )
+ AND (c.oid = d.adrelid)
+ AND d.adnum = ? "
+ ) || die $DBI::errstr;
+
+ foreach my $table_name ( keys %table_def ) {
+ foreach my $column ( @{ $table_def{$table_name} } ) {
+
+ $sth->execute( $table_name, $column->{attnum} ) || die $DBI::errstr;
+
+ while ( my $result = $sth->fetchrow_arrayref ) {
+ $column->{default} = $$result[0];
+ }
+
+ }
+
+ }
+
+ my @statements;
+ foreach my $table_name ( keys %table_def ) {
+ my $statement = "CREATE TABLE \"$table_name\" (\n";
+
+ my @col_defs;
+ foreach my $col ( @{ $table_def{$table_name} } ) {
+
+ my $col_name = $col->{field};
+ my $data_type = $col->{'data_type'};
+ my $data_len = $col->{'length'};
+ my $default = $col->{'default'};
+ my $lengthvar = $col->{'lengthvar'};
+
+ if ( $data_type eq 'bpchar' ) {
+ $data_type = 'char(' . ( $lengthvar - 4 ) . ')';
+ }
+
+ if ( $data_type eq 'int8' ) {
+ $data_type = 'bigint';
+ }
+
+ if ( $data_type eq 'int4' ) {
+ $data_type = 'integer';
+ }
+
+ if ( $default && $default =~ /nextval/ ) {
+ undef $default;
+ $data_type = 'serial';
+ }
+
+ $default =~ s/::(.*)$// if $default;
+
+ my $statement = "\t\"$col_name\" $data_type";
+ $statement .= " DEFAULT $default" if $default;
+ $statement .= ' NOT NULL' if $col->{'notnull'};
+ push @col_defs, $statement;
+ }
+
+ ## find keys
+
+ my $sth = $dbh->prepare(
+"SELECT ic.relname AS index_name, bc.relname AS tab_name, ta.attname AS column_name, i.indisunique AS unique_key, i.indisprimary AS primary_key
+ FROM pg_class bc, pg_class ic, pg_index i, pg_attribute ta, pg_attribute ia
+ WHERE (bc.oid = i.indrelid)
+ AND (ic.oid = i.indexrelid)
+ AND (ia.attrelid = i.indexrelid)
+ AND (ta.attrelid = bc.oid)
+ AND (bc.relname = ?)
+ AND (ta.attrelid = i.indrelid)
+ AND (ta.attnum = i.indkey[ia.attnum-1])
+ ORDER BY index_name, tab_name, column_name"
+ ) || die $DBI::errstr;
+
+ $sth->execute($table_name);
+
+ my %indices;
+ while ( my $result = $sth->fetchrow_hashref ) {
+
+ if ( $result->{primary_key} ) {
+ push @col_defs, "\tPRIMARY KEY (\"$$result{column_name}\")";
+ }
+ else {
+ if ( exists $indices{ $$result{index_name} } ) {
+ push @{ $indices{ $$result{index_name} }->{column_name} },
+ $$result{column_name};
+ }
+ else {
+ $indices{ $$result{index_name} } = {
+ unique => $$result{unique_key} ? ' UNIQUE' : '',
+ column_name => [ $$result{column_name} ],
+ table_name => $$result{tab_name}
+ };
+ }
+
+ }
+ }
+
+ $statement .= join ",\n", @col_defs;
+ $statement .= "\n);\n";
+ push @statements, $statement;
+
+ foreach ( keys %indices ) {
+ my %index = %{ $indices{$_} };
+ push @statements,
+ "CREATE$index{unique} INDEX \"$_\" ON \"$index{table_name}\" ("
+ . join( ', ', map { '"' . $_ . '"' } @{ $index{column_name} } )
+ . ");\n";
+ }
+
+ }
+ return $statements[0] if @statements == 1;
+ return @statements;
+}
+
+
1;
Modified: trunk/ebase/lib/Everything/DB/Test/Pg.pm
===================================================================
--- trunk/ebase/lib/Everything/DB/Test/Pg.pm 2007-05-22 23:11:06 UTC (rev 955)
+++ trunk/ebase/lib/Everything/DB/Test/Pg.pm 2007-05-22 23:11:44 UTC (rev 956)
@@ -655,4 +655,95 @@
local $TODO = "Unimplemented";
}
+
+sub test_get_create_table : Test(8) {
+ my $self = shift;
+
+ my $dbh = $self->{instance}->{dbh};
+ $dbh->clear;
+ my $var = 0;
+
+ my @returns;
+
+ my $renew_returns = sub {
+ @returns = ();
+ for ( 1 .. 3 ) {
+ push @returns,
+ {
+ field => $_,
+ type => $_,
+ notnull => $_ % 3,
+ length => $_,
+ lengthvar => $_,
+ attnum => $_
+ };
+ }
+ };
+
+ $renew_returns->();
+
+ $dbh->mock( 'fetchrow_hashref' => sub { shift @returns } );
+
+ my @frar_returns = ( ["default value"] );
+ $dbh->mock( fetchrow_arrayref => sub { shift @frar_returns } );
+
+ my @create = $self->{instance}->get_create_table('atable');
+
+ my ( $method, $args ) = $dbh->next_call;
+ is( $method, 'prepare', '...prepares statement' );
+ is(
+ $$args[1],
+"SELECT a.attnum, a.attname AS field, t.typname as type, a.attlen AS length, a.atttypmod as lengthvar, a.attnotnull as notnull
+ FROM pg_type t, pg_class c,
+ pg_attribute a
+
+ WHERE c.relname = ?
+ AND a.attnum > 0
+ AND a.attrelid = c.oid
+ AND a.atttypid = t.oid
+ ORDER BY a.attnum",
+ '...creates sql to retrieve attributes and data types.'
+ );
+ is_deeply(
+ \@create,
+ [
+ "CREATE TABLE \"atable\" (
+\t\"1\" 1 DEFAULT default value NOT NULL,
+\t\"2\" 2 NOT NULL,
+\t\"3\" 3
+);
+"
+ ],
+ '...and returns a list of create statements.'
+ );
+
+ $dbh->clear;
+
+ $renew_returns->();
+ @create =
+ $self->{instance}->get_create_table( 'btable', 'atable', 'ctable' );
+ ( $method, $args ) = $self->{instance}->{dbh}->next_call;
+ is( $method, 'prepare', '...prepares statement' );
+ like( $$args[1], qr/pg_class/, '...creates sql.' );
+ is( scalar @create, 3, '...and returns a list' );
+
+ $dbh->clear;
+ my @list = qw/ atable btable /;
+ $self->{instance}->{dbh}->mock(
+ 'fetchrow',
+ sub {
+ my $r = shift @list;
+ return () unless $r;
+ return ($r);
+ }
+ );
+
+ $renew_returns->();
+
+ @create = $self->{instance}->get_create_table();
+ like( $create[1], qr/CREATE TABLE "btable"/, '...returns all tables.' );
+ like( $create[0], qr/CREATE TABLE "atable"/, '...returns all tables.' );
+
+}
+
1;
Modified: trunk/ebase/lib/Everything/DB/Test/mysql.pm
===================================================================
--- trunk/ebase/lib/Everything/DB/Test/mysql.pm 2007-05-22 23:11:06 UTC (rev 955)
+++ trunk/ebase/lib/Everything/DB/Test/mysql.pm 2007-05-22 23:11:44 UTC (rev 956)
@@ -402,4 +402,48 @@
'2 - 1', '... makes a string from the arguments.' );
}
+
+sub test_get_create_table : Test(7) {
+ my $self = shift;
+
+ my $dbh = $self->{instance}->{dbh};
+ $dbh->clear;
+ my @returns = map { { 'Create Table' => $_ } } qw/one two three/;
+ $dbh->mock( 'fetchrow_hashref' => sub { shift @returns } );
+ my @create = $self->{instance}->get_create_table('atable');
+
+ my ( $method, $args ) = $self->{instance}->{dbh}->next_call;
+ is( $method, 'prepare', '...prepares statement' );
+ is( $$args[1], "show create table atable",
+ '...creates one sql statement/' );
+ is_deeply( \@create, [qw/one/], '...and returns a list' );
+
+ $dbh->clear;
+
+ @returns = map { { 'Create Table' => $_ } } qw/one two three/;
+ @create =
+ $self->{instance}->get_create_table( 'btable', 'atable', 'ctable' );
+ ( $method, $args ) = $self->{instance}->{dbh}->next_call;
+ is( $method, 'prepare', '...prepares statement' );
+ is( $$args[1], "show create table btable", '...creates sql.' );
+ is_deeply( \@create, [qw/one two three/], '...and returns a list' );
+
+ $dbh->clear;
+ my @list = qw/ atable btable /;
+ $self->{instance}->{dbh}->mock(
+ 'fetchrow',
+ sub {
+ my $r = shift @list;
+ return () unless $r;
+ return ($r);
+ }
+ );
+
+ @returns = map { { 'Create Table' => $_ } } qw/one two/;
+ @create = $self->{instance}->get_create_table();
+ is_deeply( \@create, [qw/ one two /], '...returns all tables.' );
+
+}
+
+
1;
Modified: trunk/ebase/lib/Everything/DB/Test/sqlite.pm
===================================================================
--- trunk/ebase/lib/Everything/DB/Test/sqlite.pm 2007-05-22 23:11:06 UTC (rev 955)
+++ trunk/ebase/lib/Everything/DB/Test/sqlite.pm 2007-05-22 23:11:44 UTC (rev 956)
@@ -417,4 +417,33 @@
'2 - 1', '... makes a string from the arguments.' );
}
+sub test_get_create_table : Test(6) {
+ my $self = shift;
+
+ my $dbh = $self->{instance}->{dbh};
+ $dbh->clear;
+ my @returns = map { [ $_ ] } qw/one two three/;
+ $dbh->mock('fetchrow_arrayref' => sub { shift @returns });
+ my @create = $self->{instance}->get_create_table('atable');
+
+ my ($method, $args) = $self->{instance}->{dbh}->next_call;
+ is ($method, 'prepare', '...prepares statement');
+ is ($$args[1], "select sql from sqlite_master where type = 'table' and name = 'atable'", '...creates sql with one where');
+ is_deeply (\@create, [ qw/one two three/ ], '...and returns a list');
+
+ $dbh->clear;
+
+ @create = $self->{instance}->get_create_table('atable', 'btable', 'ctable');
+ ($method, $args) = $self->{instance}->{dbh}->next_call;
+ is ($method, 'prepare', '...prepares statement');
+ is ($$args[1], "select sql from sqlite_master where type = 'table' and name = 'atable' or name = 'btable' or name = 'ctable'", '...creates sql with three wheres');
+
+ $dbh->clear;
+
+ @create = $self->{instance}->get_create_table();
+ ($method, $args) = $self->{instance}->{dbh}->next_call;
+ is ($$args[1], "select sql from sqlite_master where type = 'table'", '...creates sql with no where');
+
+}
+
1;
Modified: trunk/ebase/lib/Everything/DB/mysql.pm
===================================================================
--- trunk/ebase/lib/Everything/DB/mysql.pm 2007-05-22 23:11:06 UTC (rev 955)
+++ trunk/ebase/lib/Everything/DB/mysql.pm 2007-05-22 23:11:44 UTC (rev 956)
@@ -426,4 +426,32 @@
return $this->getDatabaseHandle()->last_insert_id(undef, undef, undef, undef);
}
+
+
+=head2 C<get_create_table>
+
+Returns the create table statements of the tables whose names were passed as arguments
+
+Returns a list if there is more than one table or a string if there is only one.
+
+=cut
+
+sub get_create_table {
+
+ my ( $self, @tables ) = @_;
+
+ @tables = $self->list_tables unless @tables;
+ my @statements = ();
+ my $dbh = $self->{dbh};
+
+ foreach ( @tables ) {
+ my $sth = $dbh->prepare("show create table $_") || die $DBI::errstr;
+ $sth->execute;
+ my $result = $sth->fetchrow_hashref;
+ push @statements, $result->{'Create Table'};
+ }
+ return $statements[0] if @statements == 1;
+ return @statements;
+}
+
1;
Modified: trunk/ebase/lib/Everything/DB/sqlite.pm
===================================================================
--- trunk/ebase/lib/Everything/DB/sqlite.pm 2007-05-22 23:11:06 UTC (rev 955)
+++ trunk/ebase/lib/Everything/DB/sqlite.pm 2007-05-22 23:11:44 UTC (rev 956)
@@ -390,6 +390,33 @@
return @tables;
}
+=head2 C<get_create_table>
+
+Returns the create table statements of the tables whose names were passed as arguments
+
+Returns a list if there is more than one table or a string if there is only one.
+
+=cut
+
+sub get_create_table {
+
+ my ( $self, @tables ) = @_;
+
+ my @statements = ();
+ my $dbh = $self->{dbh};
+
+ my $where = " where type = 'table'";
+ $where .= " and " . join ' or ', map { "name = '$_'" } @tables if @tables;
+ my $sth = $dbh->prepare( "select sql from sqlite_master" . $where) || die $DBI::errstr;
+ $sth->execute;
+ while (my $sql = $sth->fetchrow_arrayref) {
+ push @statements, @$sql;
+ }
+
+ return $statements[0] if @statements == 1;
+ return @statements;
+}
+
sub now { return "datetime('now')" }
sub timediff { "$_[1] - $_[2]" }
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
