(c) Software Lab. Alexander Burger
The search function allows to search the database for a combination of search criteria.
It finds all objects - directly from the criteria or after traversing all associations - which fulfill all given search criteria, and returns them one at a time.
The examples in this document will use the demo application in "app/*.l" (in demoApp.tgz).
To get an interactive prompt, start it as:
$ pil app/main.l -ap~main + :
As ever, you can terminate the interpreter by hitting Ctrl-D.
search is called in two different forms.
The first form initializes a query. It takes two or more arguments, and returns a query structure (a list).
The second form can then be called repeatedly with that structure, and will
subsequently return the next resulting object, or NIL if no more
results can be found.
To start a new query, search is called with an arbitrary number
of argument pairs, each consisting of a search criterion and a list of relation
specifications, and an optional extraction function which filters and possibly
modifies the results.
For example, to find the item with the number 2:
ap: (search 2 '((nr +Item))) -> (NIL ...
The first argument 2 is a search criterion (the key to
look for), and ((nr +Item)) is a list with a single relation
specification (the nr index of the +Item class).
The returned query structure is abbreviated here, because it is big and not relevant. It can now be used to fetch the result:
ap: (search @)
-> {B2}
ap: (show @)
{B2} (+Item)
nr 2
pr 1250
inv 100
sup {C2}
nm "Spare Part"
-> {B2}
There are no further results, because nr is a unique key:
ap: (search @@@) # '@@@' refers to the third-last REPL result, the query -> NIL
Each criterion is an attribute of a database object (like name, e-mail, address etc.), or some given database object. It may be used to find objects directly, or as a starting point for a recursive search for other objects reachable by this object.
For every search criterion which is NIL, no search is performed,
and the following relation specification is ignored. If, however, all
search criteria are NIL, a full search over the last relation
specification is forced.
If the search criterion is numeric (including derived types like date or time), it can be atomic for an exact search, or a cons pair for searching a range of numbers.
Extending the above example, we may search for all items with a number between 2 and (including) 6:
ap: (search (2 . 6) '((nr +Item))) -> (NIL ...
We may use a for loop to retrieve all results:
ap: (for
(Q
(search (2 . 6) '((nr +Item)))
(search Q) )
(printsp @) )
{B2} {B3} {B4} {B5} {B6}
If the search criterion is a string (transient symbol) or an internal symbol, or a cons pair of those, the exact behavior depends on the relation type. It includes all cases where it matches the heads of the result attributes (string prefixes), but may also match substrings and/or tolerant (folded or soundex-encoded) searches.
ap: (for
(Q
(search "Api" '((em +CuSu)))
(search Q) )
(println (; @ em)) )
"info@api.tld"
ap: (for
(Q
(search "part" '((nm +Item)))
(search Q) )
(with @
(println (: nr) (: nm)) ) )
1 "Main Part"
2 "Spare Part"
Or, combined with a number range search:
ap: (for
(Q
(search
(2 . 6) '((nr +Item))
"part" '((nm +Item)) )
(search Q) )
(with @
(println (: nr) (: nm)) ) )
2 "Spare Part"
A database object can also be used as a search criterion. A cons pair (i.e. a range) of objects makes no sense, because objects by themselves are not ordered.
Searching for all items from a given supplier:
ap: (for
(Q
(search '{C1} '((sup +Item)))
(search Q) )
(printsp @) )
{B1} {B3} {B5}
or for all positions in a given order:
ap: (for
(Q
(search '{B7} '((ord . pos)))
(search Q) )
(printsp @) )
{A1} {A2} {A3}
Every second argument to search is a list of relation
specifications. In typical use cases, a relation specification is either
(var cls [hook]) for an index relation, or
(sym . sym) for a +Joint relation
For general cases, the first specification in the list may be replaced by two custom functions: A generator function and a filter function. This allows to start the search from arbitrary resources like remote databases or coroutines.
If a specification is (var cls) but var is not an
index of cls, a brute force search through the objects in the
database file of cls will be performed. This should only be done
for small files with ideally all objects of type cls.
The rest of the list contains associations (which are also relation
specifications) to recursively search through associated objects. They are
typically (+Joint), (+List +Joint),
or (+Ref +Link) relations.
Look for example at the choOrd ("choose Order") function in the
demo application. You can access it directly in the REPL with (vi
'choOrd). The search call is
(search *OrdCus '((nm +CuSu) (cus +Ord)) *OrdOrt '((ort +CuSu) (cus +Ord)) *OrdItem '((nm +Item) (itm +Pos) (pos . ord)) *OrdSup '((nm +CuSu) (sup +Item) (itm +Pos) (pos . ord)) (and *OrdNr (cons @)) '((nr +Ord)) (and *OrdDat (cons @)) '((dat +Ord)) )
The global variables *OrdCus through *OrdDat hold
the search criteria from the search fields in the dialog GUI.
The line with the longest chain of associations is:
*OrdSup '((nm +CuSu) (sup +Item) (itm +Pos) (pos . ord))
This means:
Testing this line stand-alone, searching orders only by supplier name:
ap: (for
(Q
(search
"Seven Oaks"
(quote
(nm +CuSu)
(sup +Item)
(itm +Pos)
(pos . ord) ) )
(search Q) )
(printsp @) )
{B7}
If the list of relation specifications does not start with an index relation
(var cls) or a joint relation (sym . sym), but instead
with a function like ((X) (foo)), the first two elements of
the list are taken as generator and filter functions, respectively.
We could rewrite the last example in slightly a simplified form, but with custom functions:
ap: (for
(Q
(search
"Seven Oaks"
(quote
((X) # Generator function
(iter> (meta '(+CuSu) 'nm)
"Seven Oaks"
'(nm +CuSu) ) )
((This X) # Filter function
(pre? "Seven Oaks" (: nm)) )
(sup +Item)
(itm +Pos)
(pos . ord) ) )
(search Q) )
(printsp @) )
{B7}
The iter> method implements the mechanisms to produce the
internal query structures for individual relations. There is a convenience
function relQ for this. It can be used to simplify such standard
generators. Instead of:
((X) # Generator function
(iter> (meta '(+CuSu) 'nm)
"Seven Oaks"
'(nm +CuSu) ) )
we can write:
((X) # Generator function
(relQ "Seven Oaks" '(nm +CuSu)) )
While relQ is normally not used directly by application
programs, because its functionality is provided by the standard relation
specification syntax, there is a function relQs which is indeed
useful.
relQs produces proper generator and filter functions which can
search multiple indexes for a singe search criterion.
The general syntax is:
(relQs '((var1 +Cls1) (var2 +Cls2) ..) (sym1 ..) (sym2 ..)..)
to search first the index (var1 +Cls1), then (var2
+Cls2) etc., and then follow the optional associations (sym1
..), (sym2 ..) etc.
A typical use case is searching for a telephone number in both the landline
and mobile attributes. You find an example in the choCuSu ("choose
Customer/Supplier") function in the demo application, in the line:
*CuSuTel (relQs '((tel +CuSu) (mob +CuSu)))
Note that (relQs ..) must not be quoted, because it needs
to be evaluate to produce the right functions and query structure.
Sometimes it is necessary to to do further checks on a search result, which may not be covered by the standard matching of combined search criteria.
An example can be found in the tut.tgz tutorial in the file
"db/family.l", in the contemporaries report. It searches for people
living roughly at the same time as the given person:
'(let @Fin
(or
(: home obj fin)
(+ (: home obj dat) 36525) )
(search
(cons (- (: home obj dat) 36525) @Fin) '((dat +Person))
(curry (@Fin) (Obj)
(and
(n== Obj (: home obj))
(>= (; Obj fin) (: home obj dat))
(>= @Fin (; Obj dat))
Obj ) ) ) )
@Fin is set to either the date when the given person died, or to
the birth date plus ten years if not known. Then all persons born in the range
of ten years before the given person and @Fin are searched.
curry builds the filter function, taking an
object and doing range checks to see if that person died after the given person
was born, and that he or she was born before @Fin.
If those conditions are not met, the function returns NIL, and
search continues to search for the next result.
The extraction function may also - instead of returning the object or
NIL, return some other value as appropriate for the application.
In general, the values returned by search are not sorted when
multiple search criteria are given. This is because the indexes are iterated
over in an unpredictable order.
If, however, only a single search criterion is given, or only one of the
search criteria is non-NIL, then the results will be returned in
sorted order according to the given index.
If all search criteria are NIL, and thus the last
relation specification is used (see above under Search
Criteria), then the results will turn up in increasing order.