Changeset 1788 for trunk/LATMOS-Accounts/lib/LATMOS/Accounts

Timestamp:

06/24/16 07:01:18 (8 years ago)

Author:

nanardon

Message:

Merge branch 'bettersearch'

Location:

trunk/LATMOS-Accounts/lib/LATMOS/Accounts/Bases/Sql

Files:

• Group.pm (modified) (1 diff)
• objects.pm (modified) (7 diffs)

trunk/LATMOS-Accounts/lib/LATMOS/Accounts/Bases/Sql/Group.pm

@@ -87 +87 @@
                 multiple => 1, 
                 delayed => 1,
+                iname => 'memberUID',
                 can_values => sub { $base->list_objects('user') },
                 ro => sub {

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

@@ -736 +736 @@
     my ($class, $base, @filter) = @_;
 
-    my %attrsql;
-    my %attrbind;
+    # Results groups by attr (OR filter)
+    # foo=1 foo=1 => foo = 1 or foo = 2
+    # foo=1 bar=1 => foo =1 and bar = 2
+    my $results = {};
 
     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 = '~';
+        my ($attr, $attrref, $operator, $val) = $item =~ /^(\w+)(?:\.([\.\w]+))?(?:([^\w*]+)(.+))?$/ or next;
+        if (!$operator) {
+            $operator = '~';
             $val = shift(@filter);
         }
@@ -753 +755 @@
         defined($val) or $val =  '';
 
+        $base->log(LA_DEBUG, "Search for %s %s (ref %s) %s %s", $class->type, $attr, $attrref || '(none)', $operator || '', $val);
+
         # Invalid filter due to impossible value:
-        if ($mode ne '~' && !($mode eq '=' && $val eq 'NULL')) {
+        if ($operator ne '~' && !($operator eq '=' && $val eq 'NULL')) {
             if (!$attribute->checkinputformat($val)) {
                 $base->log(LA_ERR, "Invalid format value $val for attribute $attr");
@@ -761 +765 @@
         }
 
-        $val = $attribute->input($val) unless($mode eq '=' && $val eq 'NULL');
+        if ($attrref) {
+            my $otype = $attribute->reference or do {
+                $base->log(LA_ERR, "Attribute $attr do not refer to another object");
+                return;
+            };
+
+            my @results = $base->search_objects($otype, "$attrref$operator$val");
+            $base->log(LA_DEBUG, "Sub search %s res: %s", $otype, join(', ', @results));
+
+            if (!@results) {
+                $results->{$attr} ||= {};
+                next;
+            }
+
+            ($operator, $val) = ('=', join('||', @results));
+        }
+
+        my @results = $class->_search_uniq_filter($base, $attr, $operator, $val);
+
+        $results->{$attr}{$_} = 1 foreach (@results) 
+    }
+
+    # Merging filter result
+    my ($attrRef) = keys %{ $results } or return;
+
+    my %mresults = %{ $results->{$attrRef} };
+
+    foreach my $attr (keys %{ $results }) {
+        my @values = keys %mresults;
+        foreach (@values) {
+            $results->{$attr}{$_} or delete($mresults{$_});
+        }
+    }
+
+    return(sort keys %mresults);
+}
+
+
+sub _search_uniq_filter {
+    my ($class, $base, $attr, $operator, $value) = @_;
+
+    my @attrsql;
+    my @attrbind;
+
+    my $attribute = $base->attribute($class->type, $attr) or do {
+        $base->log(LA_ERR, "Unknown attribute $attr");
+        return;
+    };
+
+    my @values = split(/([\|\&]+)/, $value);
+    
+    # We detect if we can do a very quick search:
+    my $forRef = $operator eq '=' && ! grep { $_ eq '*' or $_ eq  '&&' or $_ eq 'NULL' } @values;
+    if ($forRef) {
+        # Improv perf
+        if ($attribute->{inline}) {
+            my $sql = sprintf(
+                q{select ikey from %s where %s = ANY (?)},
+                $base->db->quote_identifier($class->_object_table),
+                $base->db->quote_identifier($attribute->iname),
+            );
+            push(@attrsql, $sql);
+        } else {
+            my $sql = sprintf(
+                q{select okey from %s where attr = ? and "value" = ANY (?) },
+                    $base->db->quote_identifier($class->_object_table . '_attributes'),
+            );
+            push(@attrbind, $attribute->iname);
+            push(@attrsql, $sql);
+        }
+        push(@attrbind, [ grep { $_ ne '||' } @values ]);
+    } else {
+
+    # No optimisation possible:
+    while (defined(my $val = shift(@values))) {
+
+        if ($val eq '&&') {
+            push(@attrsql, 'intersect');
+            next;
+        }
+        if ($val eq '||') {
+            push(@attrsql, 'union');
+            next;
+        }
+
+        $val = $attribute->input($val) unless($operator eq '=' && $val eq 'NULL');
 
         my $sql;
@@ -777 +866 @@
                 $base->db->quote_identifier($class->_object_table),
                 $base->db->quote_identifier($attribute->iname),
-                ($mode eq '~' ? '::text' : ''),
-                $mode eq '=' && $val eq '*'
+                ($operator eq '~' ? '::text' : ''),
+                $operator eq '=' && $val eq '*'
                     ? 'is not NULL'
-                    : $mode eq '=' && $val eq 'NULL'
+                    : $operator eq '=' && $val eq 'NULL'
                     ? 'is NULL'
-                    : $mode eq '~'
+                    : $operator eq '~'
                         ? 'ILIKE ?'
-                        : "$mode ?"
+                        : "$operator ?"
             );
-            push(@{$attrbind{$attr}}, $mode eq '~' ? '%' . $val . '%' : $val) unless($mode eq '=' && ($val eq '*' || $val eq 'NULL'));
+            push(@attrbind, $operator eq '~' ? '%' . $val . '%' : $val) unless($operator eq '=' && ($val eq '*' || $val eq 'NULL'));
         } else {
-            if ($mode eq '=' && $val eq 'NULL') {
+            if ($operator eq '=' && $val eq 'NULL') {
                 $sql = sprintf(q{ select ikey from %s where ikey
                           not in (select okey from %s where attr = ? and ("value" is NOT NULL and value != '')) },
@@ -796 +885 @@
                           ),
                 );
-                push(@{$attrbind{$attr}}, $attribute->iname);
+                push(@attrbind, $attribute->iname);
             } else {
                 $sql = sprintf(
@@ -805 +894 @@
                     $val eq '*'
                         ? ''
-                        : $mode eq '~'
+                        : $operator eq '~'
                             ? q{and value::text ILIKE ?}
-                            : qq{and value $mode ?}
+                            : qq{and value $operator ?}
 
                 );
-                push(@{$attrbind{$attr}}, $attribute->iname);
-                push(@{$attrbind{$attr}}, $mode eq '~' ? '%' . $val . '%' : $val) unless($val eq '*');
+                push(@attrbind, $attribute->iname);
+                push(@attrbind, $operator eq '~' ? '%' . $val . '%' : $val) unless($val eq '*');
             }
         }
 
-        push(@{ $attrsql{$attr} }, $sql);
-    }
+        push(@attrsql, $sql);
+    }
+
+    } # Perf
 
     # building the query
-    my @sqlintersec;
     if (!$base->{wexported}) {
-        push(@sqlintersec, sprintf(
+        push(@attrsql, 'intersect', sprintf(
                 q{select ikey from %s where exported = true},
                 $base->db->quote_identifier($class->_object_table)
             )
         );
-    }
-    my @bind;
-    foreach (keys %attrsql) {
-        push(@sqlintersec, '(' . join(" union ", @{$attrsql{$_}}) . ")\n");
-        push(@bind, @{$attrbind{$_} || []});
     }
     my $sth = $base->db->prepare(
@@ -839 +924 @@
             },
             $base->db->quote_identifier($class->_object_table),
-            @sqlintersec 
-            ? "where ikey in (\n" . join("\n intersect\n", @sqlintersec) . ")\n"
+            @attrsql
+            ? "where ikey in (\n" . join(" ", @attrsql) . ")\n"
             : '',
         )
     );
-    $sth->execute(@bind);
+    $sth->execute(@attrbind);
     my @results;
     while (my $res = $sth->fetchrow_hashref) {