Altova MobileTogether Designer

The goal of the search is to filter the display of records so that it displays only books where the search string occurs in any of a book's or an author's significant text fields. If the search string occurs in a field of an Authors record—as opposed to a field in a Books record—then all the books of that author should be displayed.

The screenshot below shows the results of a search for the string "dark". The search finds two books. In both cases the search string occurs in the book's Title field.

Local search versus DB search

The search can be carried out locally, on the mobile device, in the data that has been downloaded from the DB, or it can be carried out in the DB on the server. Each mechanism has relative advantages over the other, as explained below.

•If the search is carried out locally, all DB records must be loaded into memory and searched. This mechanism is faster than if the search is carried out in the DB, but it consumes more memory since all the records must be held in memory, especially if the amount of record data is large.

•If the search is carried out by sending an SQL request to the DB, the time taken for transactions with the DB will be more. However, since only, the searched records are returned, memory consumption for data storage will be lower.

You should weigh up the relative merits when deciding which mechanism is better suited for a particular case.

Mechanism

It is possible to design different approaches to the search. The mechanism we have used combines an SQL DB search with a local MobileTogether-specific approach, and it is outlined broadly below.

1.The search string is entered in an Edit Field.

2.Since the Edit Field is associated with the SearchText node of the $PERSISTENT page source, the search text will automatically be passed to this node as the search text is being entered in the Edit Field (see highlighted node in screenshot above)

3.On clicking the Find button, two actions are executed in sequence: (i) the $BookCatalog page source is reloaded with only those Authors records that contain the search string in the data of either the author or any book child (see Point 4 below); (ii) a Delete Node(s) action removes those books (of the selected authors) that do not contain the search string in any of its fields, leaving only the books of that author that contain the search string (see Point 5 below).

4.The reload of the page source triggers a search in the DB for the search string via an SQL statement. As a result, the $BookCatalog page source will be reloaded with only the relevant authors (where the search string occurs either in the author data or in the data of at least one of the author's books). See the section SQL Search of the DB below for details.

5.Since the SQL search of the DB returns authors with all their books, the books where the search string does not occur are deleted. See the section Removing Non-matched Books below for details.

The search string

An Edit Field has been created in which the user can enter the search string. The Edit Field.is associated with the $PERSISTENT/Root/SearchText node (by dragging-and-dropping the node onto the Edit Field). As a result, the search text will be stored in this node, from where it can be accessed subsequently for actions.

The Find button

The search is started when the Find button is clicked. The actions of the button's OnClicked event (screenshot below) are (i) a Reload of the $BookCatalog page source and (ii) a Delete Node(s) action. Both actions are described in detail below. For the moment, it is important to know that the Reload action for the $BookCatalog page source starts the search in the DB. See the next section, SQL Search of the DB. The Delete Node(s) action is described in the section Removing Non-matched Books below

SQL search of the DB

When the Reload action for $BookCatalog is triggered, an SQL statement—in the data-selection definition of the DB page source—selects every row of the Authors table that has any field containing the search text (either in the Authors record itself or in any of the record's related Books records). This is achieved through the following steps.

$BookCatalog page source configuration

The $BookCatalog page source was originally configured to select all the records of the Authors table, with each Authors record containing related Books records as children. Now we add a WHERE clause to the SQL statement in order to filter the Authors records to select only those records where the search term occurs. Since the WHERE clause needs to be constructed with a complex XPath expression, we will use a function to implement the XPath expression. The function is called DBSQLSearch(), as shown in the screenshot below. In the screenshot, you can read the SQL statement in the pane near the bottom of the window. In this SQL statement, the WHERE clause will be the return value of the DBSQLSearch() function.

Functions for the search

For the search, we create two functions: DBSQLSearch() and DBFieldsSearch(). The XPath Functions dialog to create these functions is accessed via the menu command Project | XPath/XQuery Functions and it is shown in the screenshot below.

DBSQLSearch() function

DBFieldsSearch() function

$SearchFields variable

The DBSQLSearch() function does the following:

•If the search text is not the empty string, then a SELECT statement is built using the DBFieldsSearch() function twice: the first time to search the fields of the Authors table, the second time to search the fields of the Books table.

•The DBFieldsSearch() function builds the WHERE clause to search each table (Authors and Books) by referencing the $SearchFields map of each DB table's columns. The $SearchFields map is stored as a user-defined variable that is accessed via the menu command Project | Global Variables.

•Else, as defined in the DBSQLSearch() function, if the search text is the empty string, then the WHERE clause of the SQL statement for $BookCatalog will be the empty string (see the DBSQLSearch() function above). Effectively, in this case, the SQL statement will have no WHERE clause, and all the Authors records will be returned.

The structure of the SELECT statement created when the search string is not empty is as follows:

SELECT <AuthorsFields-1 to AuthorsFields-Z> FROM "Authors"

WHERE AF1 LIKE '%SearchText%' OR AF2 LIKE '%SearchText%' ... OR AFZ LIKE '%SearchText%'

OR Author_ID IN (SELECT AuthorID FROM Books

WHERE BF1 LIKE '%SearchText%' OR BF2 LIKE '%SearchText%' ... OR BFZ LIKE '%SearchText%')

This expression will select Authors records that have either (i) any Authors filed that matches the search text or (ii) any related Books record that has a field that matches the search text. Note that if the search in the related Books records returns a match, then it is the parent Authors record that is selected.

It is important to note that, since, eventually, it is Authors records that are selected, these Authors records will be selected with all their related Books records—even if one or more of these Books records do not contain the search text. How to display only those books that contain the search text is described in the next section, Removing Non-matched Books.

Removing non-matched books

Since selected Authors records are returned with all their related (child) Books records (see discussion above), the following possibilities arise:

•The search text is found in the Authors table. In this case, we can show the author's details together with all the related Books records of that author.

•The search text is found in one of the related Books tables of an Authors record. In this case, we should show the author's details and details of the matched book/s only. One way to do this would be to remove the non-matched books from the $BookCatalog page source. In our example, we have done this by adding a Delete Node(s) action to the Find button after the page source has been reloaded (see screenshot below).

XPath expression of the Delete Nodes action

The XPath expression of the Delete Node(s) action works as follows:

•The $text variable contains a lower-cased variant of the search string. This will enable the search to be case-insensitive.

•The nodes that are returned for deletion will be selected from the descendant Books records of the current author.

•The books that are selected for deletion must not contain the search string in any of the significant Books fields. This is specified by building a sequence of predicate filters; each predicate is inside square brackets. All of the predicates must evaluate to true for the book to qualify for deletion. If one predicate evaluates to false (which would happen if the search string exists in the field being checked in that predicate), then the current Books record is not selected for deletion, and the next book is checked.

•Note that the predicate filters not only check the fields of the Books records but also the fields of the parent Authors record (see the last three predicates).

Clear the search string

After a search has been carried out, the $BookCatalog page source will contain only the Authors and Books records that were returned by the search mechanism described above. The Clear button (see first screenshot of this topic) clears the search string and reloads the $BookCatalog page source so that it contains all the Authors records. The actions of the Clear button are shown in the screenshot below.

The Update Node(s) action changes the $PERSISTENT/Root/SearchText node to contain the empty string. Since this node is associated with the Edit Field in which the search text is entered, the empty string value is displayed in the Edit Field, effectively clearing the Edit Field (see the section "The Search String" above). Reloading the $BookCatalog page source with the $PERSISTENT/Root/SearchText nodeset to the empty string loads all Authors records into the page source (see the description of the DBSQLSearch() function above).