AUGCIDB: Advanced Search Syntax

Every object in the database is made up out of a set of attribute-value pairs. The attributes are usually named by taking the labels in the GUI and replacing the spaces with underscores (so "Contact Details" becomes "Contact_Details" when convered to an attribute name). There are other attributes like "Created_At", "Owner", "Updated_At" and "Updated_By" which most objects have even if they aren't necessarily displayed on-screen. And every object has an attribute called "tag" which contains the object's ID.

Here is an example object:

   tag   | attribute  |            value             |  numeric   
 100152Z | Parent     | 100132T                      |     100132
 100152Z | tag        | 100152Z                      |     100152
 100152Z | Updated_At | Mon Mar 31 10:14:45 2003     | 1049071485
 100152Z | Date       | Mon Mar 31 10:14:06 2003     | 1049071446
 100152Z | Comment    |                              |          0
 100152Z | Module     | Rating                       |          0
 100152Z | Name       | Level 1 Independent Operator |          0
 100152Z | Updated_By | newton                       |          0

You can search for objects by saying, "tell me which tags have attribute XXX set to value YYY" by typing "XXX=YYY" in the search box on the front page. So if you type Name=Igor you get all the objects which mention "Igor" in the "Name" field. If you type a search string which doesn't have an = sign in it, it's rewritten to "*=search-string" before processing (i.e., "tell me which OIDs have any attribute at all set to value `search-string'")

You can also do logical-AND by stringing search terms together with semicolons. So typing Name=Igor;Approach_And_Landing=Solo narrows down the search to objects which fit both criteria. When you type a complex query like that there's an expression evaluator in the back-end which converts what you've typed into a series of optimized SQL queries which give you whatever you've asked for.

Finally, you can do logical-OR by stringing values after the = sign together with | symbols. For example, searching for Name=newton|conway will retrieve all the objects in the database which mention "newton" or "conway" in their Name attribute.

The key to performing useful searches is understanding what all the attributes are. So how can you find out what they are?

Determining attribute names

Answer: You can ask the database to tell you by searching for objects with an attribute called "Template" which is set to the object type you're interested in. Each object type has a "Template" object in the database which describes the object. You can query those template objects with the following searches:

The result of each of these searches is a "Template" object. One of the fields in that object is marked "Attributes", and it lists all the special-case attributes that object is supposed to have, and tells you whether the attribute is mandatory or optional.

The displayed attributes include several attributes which all objects in the database always have, including Updated_At (containing the date and time of the last update to the object), Updated_By (containing the username of the person who made the last change to the object), Module (containing the object's type), and tag (containing the object's unique ID).

Inequality searches

Starting 4 May 2005, you can perform searches for numeric inequality as well as string eqality.

There are some quiet conversions, e.g., an object ID such as 100160R is silently equivalent to a numeric value of 100160. Generally speaking, any textual value which starts with a number is treated like that number in a numeric conversion, so a value like "80 flights" can be compared with "80" in searches.

In addition those conversions, dates are converted to numbers too. Dates in the format "Dow Mon dd HH:MM:SS YYYY" (i.e., UNIX-style ctime) or "dd/mm/yy" or "dd/mm/yyyy" are implicitly converted to epoch-time (number of seconds from midnight Jan 1 1970). If a two-digit year is provided it's treated as yy+2000 (i.e., 21st century -- the database didn't exist in the 20th century, so I don't see that as a problem).

That means you can do comparisons like Updated_At>1/1/2005 to find all the records which have been modified since new years day 2005.

Another example: Module=Rating;Date>1/1/2005 to show all the ratings which have been created this year. Or perhaps Module=Conversion;Name=Pik;Date>1/8/2004 to see all the Pik conversions added to the DB since August 2004.

Regular Expressions

If you understand the arcane machinations of UNIX "regular expressions" (as used by "grep" and "perl") then it might benefit you to know that the bits on either side of the equals sign, greater-than sign and less-than sign are actually regular expressions.

Regex parsing is a bit beyond the scope of this document, so I'm not going to go into depth about how to use it. If you don't know how already you'll probably never need to know, but if you do know how to use regular expressions then you should be able to incorporate that knowledge into your use of the database.