Menu
▾
▴
[Everydevel-opers] SF.net SVN: everydevel: [942] trunk/ebase
|
From: <pau...@us...> - 2007-03-18 12:36:25
|
Revision: 942
http://svn.sourceforge.net/everydevel/?rev=942&view=rev
Author: paul_the_nomad
Date: 2007-03-16 17:28:00 -0700 (Fri, 16 Mar 2007)
Log Message:
-----------
Fixes for SQL LIMIT syntax. Quoting of table names to allow case sensitive
field names. Changed "lastValue" select on pg's sequence rather than
DBI::last_insert_id. Plus other sundry fixes.
Modified Paths:
--------------
trunk/ebase/lib/Everything/DB/Pg.pm
trunk/ebase/lib/Everything/DB/Test/Pg.pm
trunk/ebase/t/DB/Pg.t
Property Changed:
----------------
trunk/ebase/
Property changes on: trunk/ebase
___________________________________________________________________
Name: svk:merge
- 16c2b9cb-492b-4d64-9535-64d4e875048d:/wip/ebase:948
a6810612-c0f9-0310-9d3e-a9e4af8c5745:/ebase/offline:17930
+ 16c2b9cb-492b-4d64-9535-64d4e875048d:/wip/ebase:949
a6810612-c0f9-0310-9d3e-a9e4af8c5745:/ebase/offline:17930
Modified: trunk/ebase/lib/Everything/DB/Pg.pm
===================================================================
--- trunk/ebase/lib/Everything/DB/Pg.pm 2007-03-17 00:27:32 UTC (rev 941)
+++ trunk/ebase/lib/Everything/DB/Pg.pm 2007-03-17 00:28:00 UTC (rev 942)
@@ -384,7 +384,7 @@
$offset ||= 0;
- return "LIMIT $limit, $offset";
+ return "LIMIT $limit OFFSET $offset";
}
sub genTableName
@@ -394,4 +394,39 @@
return '"' . $table . '"';
}
+
+sub lastValue
+{
+ my ( $this, $table, $field ) = @_;
+
+ return $this->getDatabaseHandle()->selectrow_array("SELECT currval('${table}_${field}_seq')");
+}
+
+sub list_tables {
+
+ my ($this) = @_;
+ my $sth = $this->{dbh}->prepare("select c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid)");
+
+ $sth->execute();
+
+ my @tables;
+ while ( my ($table) = $sth->fetchrow() )
+ {
+ push @tables, $table;
+ }
+
+ return @tables;
+}
+
+sub now { return 'now()' }
+
+sub _quoteData {
+
+ my $self = shift;
+ my ($names, $values, $bound) = $self->SUPER( @_ );
+ my @quoted_names = map { '"' . $_ .'"' } @$names;
+ return \@quoted_names, $values, $bound;
+
+}
+
1;
Modified: trunk/ebase/lib/Everything/DB/Test/Pg.pm
===================================================================
--- trunk/ebase/lib/Everything/DB/Test/Pg.pm 2007-03-17 00:27:32 UTC (rev 941)
+++ trunk/ebase/lib/Everything/DB/Test/Pg.pm 2007-03-17 00:28:00 UTC (rev 942)
@@ -148,16 +148,6 @@
$self->SUPER;
}
-sub test_get_node_cursor : Test(+0) {
- my $self = shift;
-
- $self->add_expected_sql(
-q|SELECT fieldname FROM "node" LEFT JOIN "lions" ON node_id=lions_id LEFT JOIN "serpents" ON node_id=serpents_id WHERE foo='bar' AND type_nodetype=8888 ORDER BY title LIMIT 1, 2|,
- );
-
- $self->SUPER;
-}
-
sub test_table_exists : Test(6) {
my $self = shift;
@@ -433,15 +423,7 @@
$self->SUPER;
}
-sub test_select_node_where : Test(+0) {
- my $self = shift;
- $self->add_expected_sql(
-q|SELECT node_id FROM "node" LEFT JOIN "sylph" ON node_id=sylph_id LEFT JOIN "dryad" ON node_id=dryad_id WHERE medusa='arachne' AND type_nodetype=8888 ORDER BY title LIMIT 1, 2|
- );
- $self->SUPER;
-}
-
sub test_sql_delete : Test(+0) {
my $self = shift;
my $value = 'a value';
@@ -468,7 +450,7 @@
my $self = shift;
$self->add_expected_sql(
- qr/INSERT INTO "atable" \((?:one|foo), (?:one|foo)\) VALUES\(\?, \?\)/);
+ qr/INSERT INTO "atable" \((?:"one"|"foo"), (?:"one"|"foo")\) VALUES\(\?, \?\)/);
$self->SUPER;
}
@@ -511,7 +493,7 @@
my $self = shift;
$self->add_expected_sql(
- qr/UPDATE "atable" SET foo = \?\s+WHERE title = \?/ms);
+ qr/UPDATE "atable" SET "foo" = \?\s+WHERE title = \?/ms);
$self->SUPER;
}
@@ -565,9 +547,9 @@
can_ok( $self->{class}, 'genLimitString' );
is( $self->{class}->genLimitString( 10, 20 ),
- 'LIMIT 20, 10', 'genLimitString() should return a valid limit' );
+ 'LIMIT 20 OFFSET 10', 'genLimitString() should return a valid limit' );
is( $self->{class}->genLimitString( undef, 20 ),
- 'LIMIT 20, 0', '... defaulting to an offset of zero' );
+ 'LIMIT 20 OFFSET 0', '... defaulting to an offset of zero' );
## opposite from mysql :)
}
@@ -587,15 +569,88 @@
}
-sub test_list_tables : Test(0) {
- local $TODO = "Unimplemented";
+sub test_get_node_cursor : Test(+0) {
+ my $self = shift;
+ $self->add_expected_sql( q|SELECT fieldname FROM "node" LEFT JOIN "lions" ON node_id=lions_id LEFT JOIN "serpents" ON node_id=serpents_id WHERE foo='bar' AND type_nodetype=8888 ORDER BY title LIMIT 1 OFFSET 2|);
+ $self->SUPER;
}
-sub test_now : Test(0) {
- local $TODO = "Unimplemented";
+sub test_select_node_where : Test(+0) {
+ my $self = shift;
+ $self->add_expected_sql( q|SELECT node_id FROM "node" LEFT JOIN "sylph" ON node_id=sylph_id LEFT JOIN "dryad" ON node_id=dryad_id WHERE medusa='arachne' AND type_nodetype=8888 ORDER BY title LIMIT 1 OFFSET 2|);
+ $self->SUPER;
+
}
+sub test_list_tables : Test(2) {
+ my $self = shift;
+ can_ok( $self->{class}, 'list_tables' ) || return;
+ my @list = (qw/auxo charis hegemone phaenna pasithea/);
+ my @expected = @list;
+ $self->{instance}->{dbh}->mock(
+ 'fetchrow',
+ sub {
+ my $r = shift @list;
+ return () unless $r;
+ return ($r);
+ }
+ );
+
+ is_deeply( [ $self->{instance}->list_tables ],
+ \@expected, '...returns all the tables in the DB.' );
+
+}
+
+sub test_now : Test(2) {
+ my $self = shift;
+ can_ok( $self->{class}, 'now' ) || return;
+ is( $self->{instance}->now,
+ 'now()',
+ '... should return the DB function that returns current time/date' );
+}
+
+
+sub test_quote_data : Test(6) {
+ my $self = shift;
+ my $data = { foo => ' bar', good => 'day', -to => 'you' };
+ my $bound = { '"foo"' => '?', '"good"' => '?', '"to"' => 'you' };
+ my $value = { '"foo"' => ' bar', '"good"' => 'day', -to => undef };
+ my @rv = $self->{instance}->_quoteData($data);
+ my $index = 0;
+ foreach ( 0 .. $#{ $rv[0] } ) {
+ my $name = $rv[0]->[$_];
+
+ #bound
+ is( $rv[1]->[$_], $$bound{ $name },
+ '_quoteData must correctly return the bound variable' );
+
+ #value
+ is( $rv[2]->[$_], $$value{$name},
+ '_quoteData correctly returns the value' );
+
+ }
+
+}
+
+sub test_last_value : Test(3) {
+ my $self = shift;
+
+ ## This finds the last insert id. In theory it is supposed to just
+ ## call last_insert_id on the database handle. In practice, that
+ ## didn't work, so we have to examine the relevant pg sequence.
+
+ $self->{instance}->{dbh}->set_always( selectrow_array => 555 );
+ is( $self->{instance}->lastValue('table', 'field'),
+ 555, 'lastValue should return the last insert id' );
+
+ my ($method, $args) = $self->{instance}->{dbh}->next_call;
+
+ is( $method, 'selectrow_array', '...with a select call.');
+ is( $args->[1], "SELECT currval('table_field_seq')", '...with the currval call.');
+
+}
+
sub test_timediff : Test(0) {
local $TODO = "Unimplemented";
}
Modified: trunk/ebase/t/DB/Pg.t
===================================================================
--- trunk/ebase/t/DB/Pg.t 2007-03-17 00:27:32 UTC (rev 941)
+++ trunk/ebase/t/DB/Pg.t 2007-03-17 00:28:00 UTC (rev 942)
@@ -1,5 +1,5 @@
#! perl
use Everything::DB::Test::Pg;
-Test::Class->runtests;
+Everything::DB::Test::Pg->runtests;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
