Item14419: support n-ary AND and OR nodes, now produced by the query …

…parser
foswiki · Jun 9, 2017 · 8208a4a · 8208a4a
1 parent b5a81d1
commit 8208a4a
Show file tree

Hide file tree

Showing 6 changed files with 50 additions and 30 deletions.
diff --git a/data/System/DBIStoreContrib.txt b/data/System/DBIStoreContrib.txt
@@ -1,4 +1,4 @@
-META:TOPICINFO{author="ProjectContributor" comment="save topic" date="1401385201" format="1.1" version="1"}%
+%META:TOPICINFO{author="ProjectContributor" date="1497033049" format="1.1" version="1"}%
 ---+!! !DBIStoreContrib
 <!--
    * Set SHORTDESCRIPTION = %$SHORTDESCRIPTION%
@@ -11,7 +11,7 @@ This extension supports fast queries and searches over the
 content of topics and attachments by cacheing wiki topics in
 an SQL database.
 
-Works as a mirror of an existing store (e.g. PlainFile, RCS)
+Works as a mirror of an existing store (e.g. !PlainFile, RCS)
    1. With all searching operations performed using SQL _or_
    2. With just structured queries performed using SQL _or_
    3. Simply to support other clients querying data using SQL.
@@ -32,7 +32,7 @@ matched against the original query to give a final set of matching
 topics. This is fine when you have relatively few queries, but is
 not terribly efficient if you make a lot of use of query searches.
 
-The DBIStoreContrib does things differently. When you select
+The !DBIStoreContrib does things differently. When you select
 !DBIStoreContrib for the query algorithm, query searches are
 automatically transformed into SQL queries, which are very
 fast. Further, you can optionally choose to use the database for
@@ -52,14 +52,13 @@ It has "personality modules" that support the following SQL implementations:
    * [[http://www.sqlite.org/][SQLite]]
    * [[http://www.mysql.com/][MySQL]]
    * [[http://www.postgresql.org/][Postgresql]]
-   * [[www.microsoft.com/SQL][Microsoft SQL Server (Transact-SQL)]]
+   * [[http://www.microsoft.com/SQL][Microsoft SQL Server (Transact-SQL)]]
 
 Note that there are many different versions of these databases and
 full compatibility with any given version cannot be guaranteed.
 
-The release package includes the DBIStorePlugin, which can be optionally
-enabled. This plugin hooks into the Foswiki save operations to
-incrementally update the database.
+The release package includes the !DBIStorePlugin, which hooks into the
+Foswiki save operations to incrementally update the database.
 
 Alternatively, you can use the included =dbistore_manage.pl= program
 to create cron (or iwatch) jobs to update the database offline.
@@ -109,7 +108,7 @@ database, you can define an optional prefix to be used on table names
 in =configure=.
 
 If the =FILEATTACHMENT= table in the schema has a field called 'text',
-and the StringifierContrib is installed, then the attachment will
+and the [[http://foswiki.org/Extensions/StringifierContrib][StringifierContrib]] is installed, then the attachment will
 automatically be serialised (if necessary) and stored in text form in
 the database. This pseudo-field can be searched using standard SEARCH
 queries, for example:
@@ -149,8 +148,8 @@ Columns are defined in a Perl hash containing attributes:
      this type on (local attributes override those in the base type)
    * =truncate_to= if set to a length, then the value of that field
     stored in the DB will be truncated to that length. This only
-    affects searching. If =truncate=_to is not set, then trying to
-    store a value longer than the field accepts will be an error.
+    affects searching. If =truncate_to= is not set, then trying to
+    store a value longer than the field accepts will be an SQL error.
    * =default= can be used to provide a default for the column. If you
      don't give a =default=, one will automatically be chosen from
      the column type.
@@ -161,7 +160,13 @@ Columns are defined in a Perl hash containing attributes:
     column.
    * =index= if true, then an index will be created for the column
 
-For example,
+For example, the WORKFLOW table might 
+<verbatim>
+       tid => { type => 'INT', primary => 1 }
+       name => { type => 'TEXT', truncate_to => 30 }
+</verbatim>
+
+Using a =basetype=
 <verbatim>
   _INDEXED => { type => 'INT', index => 1, unique => 'smatter' },
   ...
@@ -228,7 +233,7 @@ different to what base Foswiki would return.
 
 ---+++ Date comparison
 Date conversion using the =d2n= operator is not supported with
-Postgresql and !MySQL.
+!PostgreSQL and !MySQL.
 
 ---+++ Row indexes
 Integer indexes are not supported. Use queries instead.
@@ -261,8 +266,8 @@ from your {Site}{CharSet} to UTF-8 before saving in the database.
       1 Set a DSN in the 'Extensions' section (the default is for
       sqlite3). Example DSNs:
          * =dbi:ODBC:wiki= - generic ODBC
-         * =dbi:Pg:dbname=foswiki= - PostgreSQL
-         * =dbi:mysql:database=wiki;host=mysqlserver;port=1234= - MySQL
+         * =dbi:Pg:dbname=foswiki= - !PostgreSQL
+         * =dbi:mysql:database=wiki;host=mysqlserver;port=1234= - !MySQL
       1 Select a suitable personality module for your database type.
       1 Select =Foswiki::Store::QueryAlgorithms::DBIStoreContrib= for
       the ={Store}{QueryAlgorithm}= *EXPERT* setting 1 Select
@@ -321,8 +326,6 @@ UTF-8.
 ---+++ Microsoft SQL Server Notes
 SQL Server is supported via ODBC. See "ODBC Notes" for more.
 
-%X% *IF YOU WANT FULL SUPPORT FOR UNICODE CHARACTERS, DON'T USE SQL SERVER*
-
 If you are using the !FreeTDS driver, make sure your =odbc.ini= is
 selecting the highest possible protocol version that your server
 supports. See
@@ -368,6 +371,8 @@ Without this, upper and lower case strings will be compared
 case-insensitively, and the DB will be unable to distinguish
 =ThisTopicName= and =THISTOPICNAME=.
 
+%X% *IF YOU WANT FULL SUPPORT FOR UNICODE CHARACTERS, DON'T USE SQL SERVER*
+
 ---++++ Authentication
 If you are using Windows authentication for access to the database
 server, then the simplest thing to do is to use the ODBC driver and

diff --git a/lib/Foswiki/Contrib/DBIStoreContrib.pm b/lib/Foswiki/Contrib/DBIStoreContrib.pm
@@ -23,8 +23,8 @@ use DBI           ();
 use Encode        ();
 use File::Temp;
 
-our $VERSION = '2.0';          # plugin version is also locked to this
-our $RELEASE = '17 Apr 2017';
+our $VERSION = '2.1';          # plugin version is also locked to this
+our $RELEASE = '9 Jun 2017';
 
 # Global options, used to control tracing etc throughout the module
 our %TRACE = (
@@ -565,6 +565,7 @@ sub load {
     while ( $wit->hasNext() ) {
 
         my $w = ( $wo->web ? $wo->web . '/' : '' ) . $wit->next();
+        next unless !defined($wre) || $w =~ /^$wre$/;
 
         # Load subweb
         my $swo = Foswiki::Meta->load( $wo->session, $w );

diff --git a/lib/Foswiki/Contrib/DBIStoreContrib/HoistSQL.pm b/lib/Foswiki/Contrib/DBIStoreContrib/HoistSQL.pm
@@ -371,8 +371,8 @@ sub _hoist {
     # is still 'topic'
     trace( "HOIST ", $node ) if $TRACE{hoist};
 
-    my $op    = $node->{op}->{name}  if ref( $node->{op} );
-    my $arity = $node->{op}->{arity} if ref( $node->{op} );
+    my $op    = $node->{op}->{name}         if ref( $node->{op} );
+    my $arity = scalar @{ $node->{params} } if ref( $node->{op} );
 
     my %result;
     $result{type} = UNKNOWN;
@@ -567,7 +567,6 @@ sub _hoist {
         $result{ignore_tid} = 1;
     }
     elsif ( $arity == 2 && defined $bop_map{$op} ) {
-
         my $lhs = $node->{params}[0];
         my %lhs = _hoist( $lhs, $in_table );
 
@@ -827,7 +826,21 @@ sub _clarifyFWQuery {
     $before = recreate($node) if $TRACE{hoist};
     my $lineNo = __LINE__;
 
-    my $op = $node->{op};
+    my $op    = $node->{op};
+    my $arity = scalar @{ $node->{params} };
+
+    # We only handle at most binary ops, so must unflatten n-ary ops, such
+    # as and and or. Do this by taking params 0 and 1 and creating a clone
+    # of this node
+    if ( ref($op) ) {
+        while ( $arity > 2 ) {
+            my $p0 = shift @{ $node->{params} };
+            $node->{params}[0] =
+              Foswiki::Infix::Node->newNode( $node->{op}, $p0,
+                $node->{params}[0] );
+            $arity--;
+        }
+    }
 
     if ( !ref($op) ) {
         if ( $op == NAME ) {
@@ -968,12 +981,11 @@ sub _clarifyFWQuery {
         $node->{is_table} = 1;
     }
     else {
-        for ( my $i = 0 ; $i < $op->{arity} ; $i++ ) {
+        for ( my $i = 0 ; $i < $arity ; $i++ ) {
             my $nn =
               _clarifyFWQuery( $node->{params}[$i], $context, "$indent " );
             $node->{params}[$i] = $nn;
         }
-        my $nop = $op->{name};
     }
 
     trace( $indent, $lineNo, ': Clarified FWQ ',
@@ -1067,7 +1079,8 @@ sub recreate {
 
     if ( ref( $node->{op} ) ) {
         my @oa;
-        for ( my $i = 0 ; $i < $node->{op}->{arity} ; $i++ ) {
+        my $arity = scalar @{ $node->{params} };
+        for ( my $i = 0 ; $i < $arity ; $i++ ) {
             my $nprec = $node->{op}->{prec};
             $nprec++ if $i > 0;
             $nprec = 0 if $node->{op}->{close};

diff --git a/lib/Foswiki/Contrib/DBIStoreContrib/Personality.pm b/lib/Foswiki/Contrib/DBIStoreContrib/Personality.pm
@@ -121,7 +121,7 @@ sub sql {
     };
     if ($@) {
         require Carp;
-        Carp::confess($@);
+        Carp::confess( $@, $sql );
     }
     return $sth;
 }

diff --git a/lib/Foswiki/Contrib/DBIStoreContrib/Personality/PostgreSQL.pm b/lib/Foswiki/Contrib/DBIStoreContrib/Personality/PostgreSQL.pm
@@ -73,7 +73,7 @@ sub startup {
     $this->{dbh}->{AutoCommit} = 1;
 
     #    $this->sql('do', '\\set ON_ERROR_ROLLBACK true');
-    $this->sql("SET client_min_messages = 'warning'");
+    $this->{dbh}->do("SET client_min_messages to warning");
     $this->sql(<<'DO');
 CREATE OR REPLACE FUNCTION make_number(TEXT) RETURNS NUMERIC AS $$
 DECLARE i NUMERIC;

diff --git a/lib/Foswiki/Contrib/DBIStoreContrib/Personality/TransactSQL.pm b/lib/Foswiki/Contrib/DBIStoreContrib/Personality/TransactSQL.pm
@@ -133,9 +133,10 @@ sub to_db {
     return Encode::encode_utf8( $_[1] );
 }
 
-sub from_db {
-    return Encode::decode_utf8( $_[1] );
-}
+# Commented out otherwise it doesn't work at Micralyne
+#sub from_db {
+#    return Encode::decode_utf8( $_[1] );
+#}
 
 sub is_true {
     my ( $this, $type, $sql ) = @_;