Restructured verb frame schema to be more queryable

Groups are much less significant now, and they no longer have a database
table, nor are they considered a top level object anymore. Instead of
containing their own role data, that data is folded into the frames so
that it's easier to query; as a result, each group has its own copy of
the frames that it contains. Additionally, parts are considered top
level objects now, and you can query for frames based on attributes of
their indexed parts. Synrestrs are also contained in their own table
now, so that parts can be filtered against their synrestrs; they are
however not considered top level objects.

Created a new type of field, the "join where" or "condition join" field,
which is a normal join field that has a built in condition on a
specified field. This is used to allow creating multiple distinct join
fields from one object to another. This is required for the lemma::form
and frame::part joins, because filters for forms of separate inflections
should not be coalesced; similarly, filters on differently indexed frame
parts should not be coalesced.

Queries can now be ordered, ascending or descending, by a field, in
addition to randomly as before. This is necessary for accessing the
parts of a verb frame in the correct order, but may be useful to an end
user as well.

Fixed a bug with statement generation in that condition groups were not
being surrounded in parentheses, which made mixing OR groups and AND
groups generate inaccurate statements. This has been fixed;
additionally, parentheses are not placed around the top level condition,
and nested condition groups with the same logic type are coalesced, to
make query strings as easy to read as possible.

Also simplified the form::lemma field; it no longer conditions on the
inflection of the form like the lemma::form field does.

Also added a debug flag to statement::getQueryString that makes it
return a query string with all of the bindings filled in, for debug use
only.

1 files changed, 145 insertions, 19 deletions

@@ -5,11 +5,12 @@
 #include "util.h"
 #include "notion.h"
 #include "word.h"
-#include "group.h"
 #include "frame.h"
+#include "part.h"
 #include "lemma.h"
 #include "form.h"
 #include "pronunciation.h"
+#include "order.h"
 
 namespace verbly {
 
@@ -20,7 +21,7 @@ namespace verbly {
   {
   }
 
-  std::string statement::getQueryString(std::list<std::string> select, bool random, int limit) const
+  std::string statement::getQueryString(std::list<std::string> select, order sortOrder, int limit, bool debug) const
   {
     std::stringstream queryStream;
 
@@ -49,7 +50,7 @@ namespace verbly {
         if (cte.getCondition().getType() != condition::type::empty)
         {
           cteStream << " WHERE ";
-          cteStream << cte.getCondition().toSql();
+          cteStream << cte.getCondition().flatten().toSql(true, debug);
         }
 
         if (cte.isRecursive())
@@ -101,12 +102,28 @@ namespace verbly {
     if (topCondition_.getType() != condition::type::empty)
     {
       queryStream << " WHERE ";
-      queryStream << topCondition_.toSql();
+      queryStream << topCondition_.flatten().toSql(true, debug);
     }
-
-    if (random)
+    
+    queryStream << " ORDER BY ";
+    
+    switch (sortOrder.getType())
     {
-      queryStream << " ORDER BY RANDOM()";
+      case order::type::random:
+      {
+        queryStream << "RANDOM()";
+        
+        break;
+      }
+      
+      case order::type::field:
+      {
+        queryStream << topTable_;
+        queryStream << ".";
+        queryStream << sortOrder.getSortField().getColumn();
+        
+        break;
+      }
     }
 
     if (limit > 0)
@@ -260,6 +277,7 @@ namespace verbly {
           }
 
           case field::type::join:
+          case field::type::join_where:
           {
             // First, figure out what table we need to join against.
             std::string joinTableName;
@@ -269,13 +287,22 @@ namespace verbly {
             } else {
               joinTableName = getTableForContext(clause.getField().getJoinObject());
             }
+            
+            filter joinCondition = clause.getJoinCondition();
+            
+            // If this is a condition join, we need to add the field join
+            // condition to the clause.
+            if (clause.getField().getType() == field::type::join_where)
+            {
+              joinCondition &= (clause.getField().getConditionField() == clause.getField().getConditionValue());
+            }
 
             // Recursively parse the subquery, and therefore obtain an
             // instantiated table to join against, as well as any joins or CTEs
             // that the subquery may require to function.
             statement joinStmt(
               joinTableName,
-              clause.getJoinCondition().normalize(clause.getField().getJoinObject()),
+              std::move(joinCondition).normalize(clause.getField().getJoinObject()),
               nextTableId_,
               nextWithId_);
 
@@ -801,7 +828,7 @@ namespace verbly {
     new(&singleton_.value_) binding(std::move(value));
   }
 
-  std::string statement::condition::toSql() const
+  std::string statement::condition::toSql(bool toplevel, bool debug) const
   {
     switch (type_)
     {
@@ -816,42 +843,92 @@ namespace verbly {
         {
           case comparison::equals:
           {
-            return singleton_.table_ + "." + singleton_.column_ + " = ?";
+            if (debug)
+            {
+              if (singleton_.value_.getType() == binding::type::string)
+              {
+                return singleton_.table_ + "." + singleton_.column_ + " = \"" + singleton_.value_.getString() + "\"";
+              } else {
+                return singleton_.table_ + "." + singleton_.column_ + " = " + std::to_string(singleton_.value_.getInteger());
+              }
+            } else {
+              return singleton_.table_ + "." + singleton_.column_ + " = ?";
+            }
           }
 
           case comparison::does_not_equal:
           {
-            return singleton_.table_ + "." + singleton_.column_ + " != ?";
+            if (debug)
+            {
+              if (singleton_.value_.getType() == binding::type::string)
+              {
+                return singleton_.table_ + "." + singleton_.column_ + " != \"" + singleton_.value_.getString() + "\"";
+              } else {
+                return singleton_.table_ + "." + singleton_.column_ + " != " + std::to_string(singleton_.value_.getInteger());
+              }
+            } else {
+              return singleton_.table_ + "." + singleton_.column_ + " != ?";
+            }
           }
 
           case comparison::is_greater_than:
           {
-            return singleton_.table_ + "." + singleton_.column_ + " > ?";
+            if (debug)
+            {
+              return singleton_.table_ + "." + singleton_.column_ + " > " + std::to_string(singleton_.value_.getInteger());
+            } else {
+              return singleton_.table_ + "." + singleton_.column_ + " > ?";
+            }
           }
 
           case comparison::is_at_most:
           {
-            return singleton_.table_ + "." + singleton_.column_ + " <= ?";
+            if (debug)
+            {
+              return singleton_.table_ + "." + singleton_.column_ + " <= " + std::to_string(singleton_.value_.getInteger());
+            } else {
+              return singleton_.table_ + "." + singleton_.column_ + " <= ?";
+            }
           }
 
           case comparison::is_less_than:
           {
-            return singleton_.table_ + "." + singleton_.column_ + " < ?";
+            if (debug)
+            {
+              return singleton_.table_ + "." + singleton_.column_ + " < " + std::to_string(singleton_.value_.getInteger());
+            } else {
+              return singleton_.table_ + "." + singleton_.column_ + " < ?";
+            }
           }
 
           case comparison::is_at_least:
           {
-            return singleton_.table_ + "." + singleton_.column_ + " >= ?";
+            if (debug)
+            {
+              return singleton_.table_ + "." + singleton_.column_ + " >= " + std::to_string(singleton_.value_.getInteger());
+            } else {
+              return singleton_.table_ + "." + singleton_.column_ + " >= ?";
+            }
           }
 
           case comparison::is_like:
           {
-            return singleton_.table_ + "." + singleton_.column_ + " LIKE ?";
+            if (debug)
+            {
+              return singleton_.table_ + "." + singleton_.column_ + " LIKE \"" + singleton_.value_.getString() + "\"";
+            } else {
+              return singleton_.table_ + "." + singleton_.column_ + " LIKE ?";
+            }
           }
 
           case comparison::is_not_like:
           {
-            return singleton_.table_ + "." + singleton_.column_ + " NOT LIKE ?";
+            if (debug)
+            {
+              return singleton_.table_ + "." + singleton_.column_ + " NOT LIKE \"" + singleton_.value_.getString() + "\"";
+            } else {
+              return singleton_.table_ + "." + singleton_.column_ + " NOT LIKE ?";
+            }
           }
 
           case comparison::is_not_null:
@@ -871,10 +948,25 @@ namespace verbly {
         std::list<std::string> clauses;
         for (const condition& cond : group_.children_)
         {
-          clauses.push_back(cond.toSql());
+          clauses.push_back(cond.toSql(false, debug));
         }
 
-        return implode(std::begin(clauses), std::end(clauses), group_.orlogic_ ? " OR " : " AND ");
+        if (clauses.empty())
+        {
+          return "";
+        } else if (clauses.size() == 1)
+        {
+          return clauses.front();
+        } else {
+          std::string result = implode(std::begin(clauses), std::end(clauses), group_.orlogic_ ? " OR " : " AND ");
+          
+          if (toplevel)
+          {
+            return result;
+          } else {
+            return "(" + result + ")";
+          }
+        }
       }
     }
   }
@@ -988,5 +1080,39 @@ namespace verbly {
       throw std::domain_error("Cannot get children of non-group condition");
     }
   }
+  
+  statement::condition statement::condition::flatten() const
+  {
+    switch (type_)
+    {
+      case type::empty:
+      case type::singleton:
+      {
+        return *this;
+      }
+      
+      case type::group:
+      {
+        condition result(group_.orlogic_);
+        
+        for (const condition& child : group_.children_)
+        {
+          condition newChild = child.flatten();
+          
+          if ((newChild.type_ == type::group) && (newChild.group_.orlogic_ == group_.orlogic_))
+          {
+            for (condition subChild : std::move(newChild.group_.children_))
+            {
+              result += std::move(subChild);
+            }
+          } else {
+            result += std::move(newChild);
+          }
+        }
+        
+        return result;
+      }
+    }
+  }
 
 };