Second Tutorial Part 7: Indices and Queries

Previous	Main	Next
<-- Part 6	Main	Part 8 -->

Richer Where-Clauses

In the previous section, we saw how indices are used to generate lookupBy and lookupWhere methods to access data. In this section, we look at more advanced methods of creating richer where clauses, i.e., ways to define a boolean expression to select one or more records.

• non-db Indices
• simple queries
• custom queries

In our experience, 90% of needs can be satisfied with just indices, while 9% can be satisfied with queries, and in the rare case where you need something more complex, the last 1%, you can use custom query code. Obviously, your mileage will vary a bit, but this is what we have experienced across several projects.

Indices

In the First Tutorial we saw how to define indices on tables, both for integrity purpose (unique indices representing natural identities for the table), and for performance purpose (regular indices for frequent patterns of data access that are being envisioned.

As per Tilda JSON Syntax: Object Indices, there is an extra element in the JSON syntax called "db" which can be set to true or false. This allows to create logical indices for the application that won't be created in the database, but which will generate lookupBy or lookupWhere methods. This is useful for example when creating an index over multiple columns but still want to be able to access the data with fewer columns. For example:

{ "name": "TestQuestionAnswer"
 ,"description": "Questions and answers for multiple-choice tests"
 ,"columns":[
     { "name":"type"         , "type":"STRING" , "size":  60, "nullable":false        , "description":"Form template type" }
    ,{ "name":"questionSeq"  , "type":"INTEGER"             , "nullable":false        , "description":"Sequence of the question for the test" }
    ,{ "name":"questionId"   , "type":"STRING" , "size":  60, "nullable":false        , "description":"Question id" }
    ,{ "name":"questionLabel", "type":"STRING" , "size": 256, "nullable":false        , "description":"Question label" }
    ,{ "name":"answerSeq"    , "type":"INTEGER"             , "nullable":false        , "description":"Sequence of the answer for the question in the test" }
    ,{ "name":"answerId"     , "type":"STRING" , "size":  60, "nullable":false        , "description":"Answer id" }
    ,{ "name":"answerLabel"  , "type":"STRING" , "size": 256, "nullable":false        , "description":"Answer label" }
    ,{ "name":"correct"      , "type":"BOOLEAN"             , "nullable":false        , "description":"Whether the answer is a correct one or not for that question (technically, there could be more than one)" }
   ]
 ,"primary": { "autogen": true, "keyBatch": 500 }
 ,"indices": [ 
     { "name":"FormAnswer", "columns":["type", "questionId", "answerSeq"] }
    ,{ "name":"Form"      , "columns":["type"]                          , "orderBy":["questionId", "answerSeq"], "db":false }
   ]
}

The object TestQuestionAnswer defines a unique index over type, questionId and answerSeq, but it's totally expected to allow the application to get a list of all question/answers for a single Test type, and so the second non-unique index is declared with "db":"false". This methods generated will be:

TestQuestionAnswer_Data lookupByFormAnswer(String type, String questionId, int answerSeq)
throws Exception;

List<TestQuestionAnswer_Data> lookupWhereForm(Connection C, String type, int Start, int Size)
throws Exception;

Queries

The next level of richness comes with defining queries with more flexible where-clauses. Indices with "db":"false" can easily be expressed as queries as well.

,"queries": [
    { "name"  :"Form",
      "description": "blah blah",
      "from"  : [], 
      "wheres": [
          { "db": "*", "clause": "((type=?() AND deleted is null AND lastUpdated >= ?(Start) AND lastUpdated < ?(End)" }
       ],
      "orderBy": ["questionId asc", "answerSeq asc" ]
    }
  ]

Here, we created a query that pretty much functions like the Form index, but with some extra logic:

• excluding deleted records
• returning records within a range for lastUpdated.

The syntax for queries describe the main elements. You can:

• Specify additional tables that would be joined as per the from-clause of an SQL statement, i.e., you have to specify all the join constraints in your where-clause.
• In the future, one will be able to specify where-clauses that would apply to specific database targets. a "db":"*" simply states the query would work across all databases targeted by the application.
• If you specify an orderBy, you'll bet a lookupWhere vs a lookupBy.
• The syntax for where-clauses is simple but has a few rules:
  • the ? character is used for parameter placing with a syntax (similar to regular SQL) followed by parentheses. These can be empty, as in ?(), or specify an additional name, as in ?(Start) which allows you to not only define multiple values for the same column, but also add extra semantics in the parameter defined for the generated method.
  • The ?() construct must be lexically close to the column it would bind to, i.e., separated by an operator. This limit for example the ability to create much more complex queries with in clauses, or calling more complex functions etc...

List<TestQuestionAnswer_Data> lookupWhereForm
    (Connection C, String type
                 , ZonedDateTime lastUpdatedStart
                 , ZonedDateTime lastUpdatedEnd
                 , int Start, int Size)
throws Exception;

🎈 NOTE: Always think hard about your names. Both indices and queries result in lookupBy and lookupWhere methods being generated. Tilda will check that the names are unique across the two sets of definitions, but your name should convey what each is doing clearly.

🎈 NOTE: Although Tilda uses a compiler to make sense of the where-clause and detect the bindings, it still cannot guarantee that the generated query will work as expected at runtime. Make sure you test your code.

Query Helper

The next level or richness for defining where-clauses involves essentially custom queries written in Java using the Query Helper facilities. Let's imagine that one builds a search UI with a text field. The idea is that we'd want to tokenize that text field and issue a like query. This cannot be done using the query facility outlined above because of the inherent dynamic nature of the scenario: the user may type 1 or more "tokens".

Although you can write such a piece of code pretty much anywhere you wish, the Tilda convention is to do it in the application-level Factory class, and follow the standard pattern of the lookupWhere methods. Here, we propose the following public static method in the TestQuestionAnswer_Factory class.

public static ListResults<TestQuestionAnswer_Data> 
              lookupWhereComplexQuery
              (Connection C, String typeSearchStr, int start, int size)
              throws Exception
  {
    SelectQuery Q = newSelectQuery(C);
    if (TextUtil.isNullOrEmpty(typeSearchStr) == false)
      {
        String[] tokens = typeSearchStr.split("\\s+");
        List<String> L = new ArrayList<String>();
        for (String t : tokens)
          if (TextUtil.isNullOrEmpty(t) == false)
            L.add("%" + t + "%");
        Q.like(COLS.TYPE, CollectionUtil.toStringArray(L), false, true);
      }
    Q.and()
     .isNull(COLS.DELETED);
    return runSelect(C, Q, start, size);
  }

A few notes:

• We use several utilities from the Tilda.utils library. You are free to use them but APIs may change over time. These are heavily used and tested as part of the Tilda project so they are stable though.
  • TextUtil.isNullOrEmpty() tests whether a string is null or empty (matching "\s*")
  • CollectionUtil.toStringArray() returns an array from a list (a simple shortcut to the more verbose toArray() facilities in Java).
• Every single column defined in the model is declared in the internal type COLS for every factory class. As Tilda strives to be transparent and enable iterative development, it creates compile-time artifacts for all its pieces. therefore, you create custom queries in your code that rely on the compiler's standard behavior to detect future schema changes that would affect your code.
• The SelectQuery object is smart enough to handle superfluous and's. In our case above, if the string passed in was empty, there won't be a like and the and() calls can detect that and not emit an actual and in the SQL query. Basically, if you call and or or right after a parenthesis, or at the beginning of a where-clause, they will not be emitted.
• newSelectQuery is a static method on the factory class that returns a SelectQuery that is set up for that object and is ready to accept the parameters of a where-clause.
• runSelect is another static method on the factory class that executes a Query and returns the list of objects retrieved form the database.

You can check the API for the SelectQuery class which provides extensive support for creating all sorts of queries, using a fluent and typed (as per your model definition) approach.

Previous	Main	Next
<-- Part 6	Main	Part 8 -->