Changeset 324

Timestamp:

08/10/09 23:03:51 (15 years ago)

Author:

nanardon

Message:

• add an optimized version of search() in SQL base

File:

• LATMOS-Accounts/lib/LATMOS/Accounts/Bases/Sql/objects.pm (modified) (1 diff)

LATMOS-Accounts/lib/LATMOS/Accounts/Bases/Sql/objects.pm

@@ -364 +364 @@
 }
 
+
+sub search {
+    my ($class, $base, @filter) = @_;
+
+    if ($class->has_extended_attributes) {
+        my @sqlintersec;
+        my @bind;
+        while (my $item = shift(@filter)) {
+            # attr=foo => no extra white space !
+            # \W is false, it is possible to have two char
+            my ($attr, $mode, $val) = $item =~ /^(\w+)(?:(\W)(.+))?$/ or next;
+            if (!$mode) {
+                $mode = '~';
+                $val = shift(@filter);
+            }
+            push(@sqlintersec,
+                sprintf(q{select okey from %s where attr=? %s},
+                    $base->db->quote_identifier($class->object_table .
+                        '_attributes'),
+                    sprintf(q{and lower(value) = ?}),
+                )
+            );
+            push(@bind, $base->get_field_name($class->type, $attr, 'r'),
+                lc($val)); 
+        }
+        my $sth = $base->db->prepare(
+            sprintf(q{
+                select name from %s where ikey in (
+                %s
+                )
+                },
+                $base->db->quote_identifier($class->object_table),
+                join("\n intersect\n", @sqlintersec),
+            )
+        );
+        $sth->execute(@bind);
+        my @results;
+        while (my $res = $sth->fetchrow_hashref) {
+            push(@results, $res->{name});
+        }
+        return(@results);
+    } else {
+        my @bind;
+        my @where;
+        while (my $item = shift(@filter)) {
+            # attr=foo => no extra white space !
+            # \W is false, it is possible to have two char
+            my ($attr, $mode, $val) = $item =~ /^(\w+)(?:(\W)(.+))?$/ or next;
+            if (!$mode) {
+                $mode = '~';
+                $val = shift(@filter);
+            }
+            push(@where, sprintf("lower(%s) = ?",
+                   $base->db->quote_identifier($base->get_field_name($class->type,
+                           $attr, 'r'))
+               ));
+            push(@bind, lc($val)); 
+        }
+        my $sth = $base->db->prepare(sprintf(q{select name from %s where %s},
+                $base->db->quote_identifier($class->object_table),
+                join(' and ', @where),
+            ));
+        $sth->execute(@bind);
+        my @results;
+        while (my $res = $sth->fetchrow_hashref) {
+            push(@results, $res->{name});
+        }
+        return(@results);
+    }
+}
+
 1;