[svn:p5ee] r7946 - p5ee/trunk/App-Repository/lib/App/ValueDomain

Author: spadkins
Date: Mon Oct 16 10:58:12 2006
New Revision: 7946

Modified:
   p5ee/trunk/App-Repository/lib/App/ValueDomain/Repository.pm

Log:
add direct SQL with substitutions as another way to get the values/labels from a Repository

Modified: p5ee/trunk/App-Repository/lib/App/ValueDomain/Repository.pm
==============================================================================
--- p5ee/trunk/App-Repository/lib/App/ValueDomain/Repository.pm	(original)
+++ p5ee/trunk/App-Repository/lib/App/ValueDomain/Repository.pm	Mon Oct 16 10:58:12 2006
@@ -101,6 +101,7 @@
 
     if ($needs_loading) {
         my $context     = $self->{context};
+
         my $rep         = $context->repository($repository);
         my $table       = $self->{table};
         my $valuecolumn = $self->{valuecolumn};
@@ -108,6 +109,9 @@
         $labelcolumn = "" if ($labelcolumn eq $valuecolumn);
         my $params      = $self->{params} || {};
         my %params      = %$params;
+        my $sql         = $self->{sql};
+
+        # TODO. I might want to use [$params = $self->substitute($params);] here
         my ($key, $keyvalue, $wname, $wvalue);
         foreach my $key (keys %params) {
             $keyvalue = $params{$key};
@@ -177,6 +181,45 @@
             $self->{time} = $time;
             $self->{values_string} = $values_string;
         }
+
+        if ($sql) {
+            # TODO. I might want to use [$params = $self->substitute($params);] here
+            while ($sql =~ /\{([A-Za-z0-9\._-]+)\}/g) {
+                $wname = $1;
+                $wvalue = $context->so_get($wname);
+                if (defined $wvalue) {
+                    $sql =~ s/\{$wname\}/$wvalue/g;
+                }
+                else {
+                    $sql =~ s/\{$wname\}/NULL/g;
+                }
+            }
+            my $dbh = $rep->{dbh};
+            my $sth = $dbh->prepare($sql);
+            return("Prepare error: $DBI::errstr\n") if ($DBI::err);
+            
+            my $rc =  $sth->execute;
+            return("Execute error: $DBI::errstr\n") if ($DBI::err);
+
+            while (1) {
+                my @row = $sth->fetchrow;
+                return("Fetch error: $DBI::errstr\n") if ($DBI::err);
+                if ($#row < 0) {
+                   $sth->finish;
+                   return("Finish error: $DBI::errstr\n") if ($DBI::err);
+                   last;
+                }
+                my ($val, $lbl) = @row;
+                
+                push(@$values, $val);
+                $labels->{$val} = $lbl;
+            }
+
+            $self->{values} = $values;
+            $self->{labels} = $labels;
+            $time = time();
+            $self->{time} = $time;
+        } 
     }
 
     $values = [] if (! defined $values);