Searching a module

A program accesses an EMu module (or table, the terms are used interchangeably) using the Module class. The name of the table to be accessed is passed to the Module constructor. For example:

C#

using IMu;

// ⋯

Module parties = new Module("eparties", session);

VB

Dim parties = New IMu.Module("eparties", session)

Note: The IMu class name Module conflicts with a Visual Basic reserved word and it is therefore necessary to use the fully qualified name IMu.Module.

This code assumes that a Session object called session has already been created. If a Session object is not passed to the Module constructor, a session will be created automatically using the DefaultHost and DefaultPort class properties. See Connecting to an IMu server for details.

Once a Module object has been created, it can be used to search the specified module and retrieve records.

Any one of the following methods can be used to search for records within a module:

The FindKey method searches for a single record by its key. The key is a long integer (i.e. long in C#, Long in VB).

For example, the following code searches for a record with a key of 42 in the Parties module:

C#

using IMu;

// ⋯

Module parties = new Module("eparties", session);

long hits = parties.FindKey(42);

VB

Dim parties = New IMu.Module("eparties", session)

Dim hits = parties.FindKey(42)

The method returns the number of matches found, which is either 1 if the record exists or 0 if it does not.

The FindKeys method searches for a set of key values. The keys are passed as an array of long integers.

C#

using IMu;

// ⋯

Module parties = new Module("eparties", session);

long[] keys = { 52, 42, 17 };

long hits = parties.FindKeys(keys);

VB

Dim parties = New IMu.Module("eparties", session)

Dim keys() As Long = {52, 42, 17}

Dim hits = parties.FindKeys(keys)

or as a List:

C#

Module parties = new Module("eparties", session);

List<long> keys = new List<long>();

keys.Add(52);

keys.Add(42);

keys.Add(17);

long hits = parties.FindKeys(keys);

VB

Dim parties = New IMu.Module("eparties", session)

Dim keys New List(Of Long)

keys.Add(1)

keys.Add(2)

keys.Add(3)

Dim hits = parties.FindKeys(keys)

The method returns the number of records found.

The FindTerms method is the most flexible and powerful way to search for records within a module. It can be used to run simple single term queries or complex multi-term searches.

The terms are specified using a Terms object. Once a Terms object has been created, add specific terms to it (using the Add method) and then pass the Terms object to the FindTerms method. For example, to specify a Parties search for records that contain a first name of John and a last name of Smith:

C#

Terms search = new Terms();

search.Add("NamFirst", "John");

search.Add("NamLast", "Smith");

// ⋯

long hits = parties.FindTerms(search);

VB

Dim search = New Terms

search.Add("NamFirst", "John")

search.Add("NamLast", "Smith")

' ⋯

Dim hits = parties.FindTerms(search)

There are several points to note:

  1. The first argument passed to the Add method element contains the name of the column or an alias in the module to be searched.

  2. An alias associates a supplied value with one or more actual columns. Aliases are created using the AddSearchAlias or AddSearchAliases methods.

  3. The second argument contains the value to search for.

  4. Optionally, a comparison operator can be supplied as a third argument (see example here). The operator specifies how the value supplied as the second argument should be matched. Operators are the same as those used in TexQL (See Texpress Query Language Guide for details). If not supplied, the operator defaults to "matches". This is not a real TexQL operator, but is translated by the search engine as the most "natural" operator for the type of column being searched. For example, for text columns "matches" is translated as the contains TexQL operator and for integer columns it is translated as the = TexQL operator.

Note: Unless it is really necessary to specify an operator, consider using the matches operator, or better still supplying no operator at all as this allows the server to determine the best type of search.

Examples

  1. To search for the name Smith in the last name field of the Parties module, the following term can be used:

    C#

    Terms search = new Terms();

    search.Add("NamLast", "Smith");

    VB

    Dim search = New Terms

    search.Add("NamLast", "Smith")

  2. Specifying search terms for other types of columns is straightforward. For example, to search for records inserted on April 4, 2011:

    C#

    Terms search = new Terms();

    search.Add("AdmDateInserted", "Apr 4 2011");

    VB

    Dim search = New Terms

    search.Add("AdmDateInserted", "Apr 4 2011")

  3. To search for records inserted before April 4, 2011, it is necessary to add an operator:

    C#

    Terms search = new Terms();

    search.Add("AdmDateInserted", "Apr 4 2011", "<");

    VB

    Dim search = New Terms

    search.Add("AdmDateInserted", "Apr 4 2011", "<")

  4. By default, the relationship between the terms is a Boolean AND. This means that to find records which match both a first name containing John and a last name containing Smith the Terms object can be created as follows:

    C#

    Terms search = new Terms();

    search.Add("NamFirst", "John");

    search.Add("NamLast", "Smith");

    VB

    Dim search = New Terms

    search.Add("NamFirst", "John")

    search.Add("NamLast", "Smith")

  5. A Terms object where the relationship between the terms is a Boolean OR can be created by passing the enumeration value TermsKind.OR to the Terms constructor:

    C#

    Terms search = new Terms(TermsKind.OR);

    search.Add("NamFirst", "John");

    search.Add("NamLast", "Smith");

    VB

    Dim search = New Terms(TermsKind.OR)

    search.Add("NamFirst", "John")

    search.Add("NamLast", "Smith")

    This specifies a search for records where either the first name contains John or the last name contains Smith.

  6. Combinations of AND and OR search terms can be created. The AddAnd method adds a new set of AND terms to the original Terms object. Similarly, the AddOr method adds a new set of OR terms. For example, to restrict the search for a first name of John and a last name of Smith to matching records inserted before April 4, 2011 or on May 1, 2011, specify:

    C#

    Terms search = new Terms();

    search.Add("NamFirst", "John");

    search.Add("NamLast", "Smith");

    Terms dates = search.AddOr();

    dates.Add("AdmDateInserted", "Apr 4 2011", "<");

    dates.Add("AdmDateInserted", "May 1 2011");

    VB

    Dim search = New Terms

    search.Add("NamFirst", "John")

    search.Add("NamLast", "Smith")

    Dim dates = search.AddOr()

    dates.Add("AdmDateInserted", "Apr 4 2011", "<")

    dates.Add("AdmDateInserted", "May 1 2011")

  7. To run a search, pass the Terms object to the FindTerms method:

    C#

    Module parties = new Module("eparties", session);

    Terms search = new Terms();

    search.Add("NamLast", "Smith");

    long hits = parties.FindTerms(search);

    VB

    Dim parties = New IMu.Module("eparties", session)

    Dim search = New Terms

    search.Add("NamFirst", "John")

    Dim hits = parties.FindTerms(search)

    As with other find methods, the return value contains the estimated number of matches.

  8. To use a search alias, call the AddSearchAlias method to associate the alias with one or more real column names before calling FindTerms. Suppose we want to allow a user to search the Catalogue module for keywords. Our definition of a keywords search is to search the SummaryData, CatSubjects_tab and NotNotes columns. We could do this by building an OR search:

    C#

    string keyword = "⋯";

    // ⋯

    Terms search = new Terms(TermsKind.OR);

    search.Add("SummaryData", keyword);

    search.Add("CatSubjects_tab", keyword);

    search.Add("NotNotes", keyword);

    VB

    Dim keyword As String = "⋯"

    ' ⋯

    Dim search = New Terms(TermsKind.OR)

    search.Add("SummaryData", keyword)

    search.Add("CatSubjects_tab", keyword)

    search.Add("NotNotes", keyword)

    Another way of doing this is to register the association between the name keywords and the three columns we are interested in and then pass the name keywords as the column to be searched:

    C#

    string keyword = "⋯";

    // ⋯

    Module catalogue = new Module("ecatalogue", session);

    string[] columns =

    {

    "SummaryData",

    "CatSubjects_tab",

    "NotNotes"

    };

    catalogue.AddSearchAlias("keywords", columns);

    // ⋯

    Terms search = new Terms();

    search.Add("keywords", keyword);

    catalogue.FindTerms(search);

    VB

    Dim keyword As String = "⋯"

    ' ⋯

    Dim catalogue = New IMu.Module("ecatalogue", session)

    Dim columns() As String =

    {

    "SummaryData",

    "NamRoles_tab",

    "NotNotes"

    }

    catalogue.AddSearchAlias("keywords", columns)

    ' ⋯

    Dim search = New Terms

    search.Add("keywords", keyword)

    catalogue.FindTerms(search)

    An alternative to passing the columns as an array of strings is to pass a single string, with the column names separated by semi-colons:

    C#

    string keyword = "⋯";

    // ⋯

    Module catalogue = new Module("ecatalogue", session);

    string columns = "SummaryData;CatSubjects_tab;NotNotes";

    catalogue.AddSearchAlias("keywords", columns);

    // ⋯

    Terms search = new Terms();

    search.Add("keywords", keyword);

    catalogue.FindTerms(search);

    VB

    Dim keyword As String = "⋯"

    ' ⋯

    Dim catalogue = Neww IMu.Module("ecatalogue", session)

    Dim columns = "SummaryData;CatSubjects_tab;NotNotes"

    catalogue.AddSearchAlias("keywords", columns)

    ' ⋯

    Dim search = New Terms

    search.Add("keywords", keyword)

    catalogue.FindTerms(search)

    The advantage of using a search alias is that once the alias is registered a simple name can be used to specify a more complex OR search.

  9. To add more than one alias at a time, use the IMu Map class to build an associative array of names and columns and call the AddSearchAliases method:

    C#

    Map aliases = new Map();

    aliases.Add("keywords", "SummaryData;CatSubjects_tab;NotNotes");

    aliases.Add("title", "SummaryData;TitMainTitle");

    catalogue.AddSearchAliases(aliases);

    VB

    Dim aliases = New Map

    aliases.Add("keywords", "SummaryData;CatSubjects_tab;NotNotes")

    aliases.Add("title", "SummaryData;TitMainTitle")

    catalogue.AddSearchAliases(aliases)

With the FindWhere method it is possible to submit a complete TexQL where clause.

C#

Module parties = new Module("eparties", session);

string where = "NamLast contains 'Smith'";

long hits = parties.FindWhere(where);

VB

Dim parties = New IMu.Module("eparties", session)

Dim where = "NamLast contains 'Smith'"

Dim hits = parties.FindWhere(where)

Although this method provides complete control over exactly how a search is run, it is generally better to use FindTerms to submit a search rather than building a where clause. There are (at least) two reasons to prefer FindTerms over FindWhere:

  1. Building the where clause requires the code to have detailed knowledge of the data type and structure of each column. The FindTerms method leaves this task to the server. For example, specifying the term to search for a particular value in a nested table is straightforward. To find Parties records where the Roles nested table contains Artist, FindTerms simply requires:

    search.Add("NamRoles_tab", "Artist");

    On the other hand, the equivalent TexQL clause is:

    exists(NamRoles_tab where NamRoles contains 'Artist')

    The TexQL for double nested tables is even more complex.

  2. More importantly, FindTerms is more secure.

    With FindTerms a set of terms is submitted to the server which then builds the TexQL where clause. This makes it much easier for the server to check for terms which may contain SQL-injection style attacks and to avoid them. If your code builds a where clause from user-entered data so it can be run using FindWhere, it is much more difficult, if not impossible, for the server to check and avoid SQL-injection. The responsibility for checking for SQL-injection becomes yours.

All of the find methods return the number of matches found by the search. For FindKey and FindKeys this number is always the exact number of matches found. The number returned by FindTerms and FindWhere is best thought of as an estimate. This estimate is almost always correct but because of the nature of the indexing used by the server's data engine (Texpress) the number can sometimes be an over-estimate of the real number of matches. This is similar to the estimated number of hits returned by a Google search.