This post assumes that you know the basics of Pilog (PicoLisp Prolog). If you don't, consider to read this article series first.
All Pilog access to database objects is done via the two predicates db/3 and select/3 (/3 means that these predicates take 3 arguments).
db/3corresponds to the Lisp-level functionsdbandcollect, as it derives its data from a single relation. It can be used for simple database queries.select/3provides for self-optimizing parallel access to an arbitrary number of relations. There is also a Lisp front-end functionselectfor easy access in debug mode, which will be explained in the next post.
The db/3 predicate
Let's open our family library from the previous tasks. You can use any of our predefined scripts, for example this one and start it with:
$ pil family-original-tutorial.l -family~main +
family:
It starts directly in the family namespace. The db predicate expects a relation of the type +index, the class and a variable for the unification. For example, the following query returns all +Person objects in the database that are indexed by the attribute nm:
family: (? (db nm +Person @P))
@P={A64}
@P={A127}
@P={A115}
@P={A47}
@P={A113}
...
In order to expand @P to the full record, we can use the predicate show/1:
family: (? (db nm +Person @P) (show @P))
{A64} (+Man)
ma {A57}
fin 711698
dat 688253
nm "Adalbert Ferdinand Berengar Viktor of Prussia"
pa {A55}
@P={A64}
...
We can add another argument to specify the name. This query returns all entries for a +Person that is indexed by "Edward":
family: (? (db nm +Person "Edward" @P))
@P={A13}
@P={A35}
@P={A115}
...
Instead of a name, we can also use a cons-pair to define a range, such as:
: (? nr +Item 2 @Item) # get 2nd item
: (? (db nr +Item (2 . 5) @Item) # get item 2 through 5
Obviously, these examples don't work for our family database as we don't have a class +Item defined.
The val/3 predicate
In order to form more complex queries, we can use the val predicate to return the value of an object's attribute. In the simplest form, it takes three arguments: the attribute value, the object and the attribute key.
This query returns the "job" of all objects which can be indexed by the name "Edward":
family: (? (db nm +Person "Edward" @P) (val @Job @P job))
@P={A13} @Job="Prince"
@P={A35} @Job="Duke of Kent"
@P={A115} @Job="Prince"
However, it is not only restricted to object @P. For example, let's return the names of all partners of @P. We can do this by replacing @P with @P mate:
family: (? (db nm +Person "Edward" @P) (val @Partner @P mate nm))
@P={A115} @Partner="Victoria I"
@P={A126} @Partner="Victoria Mary Louisa"
@P={A46} @Partner="Alexandra of Denmark"
Or we can query specifically for the value. Let's return all persons with "Edward" in their name who are married to somebody with occupation "Queen":
family: (? (db nm +Person "Edward" @P) (val "Queen" @P mate job) (show @P))
{A115} (+Man)
mate {A53}
job "Prince"
kids ({A116} {A117} {A120} {A121} {A122} {A123} {A124} {A54} {A46})
nm "Albert Edward"
fin 680370
dat 664554
@P={A115}
There is only one result.
The lst predicate
If the value of an attribute is a list (like the kids attribute in our example), we can use the lst predicate, which returns every item of a list.
family: (? (db nm +Person "Edward" @P) (lst @K @P kids))
@P={A115} @K={A116}
@P={A115} @K={A117}
@P={A115} @K={A120}
@P={A115} @K={A121}
@P={A115} @K={A122}
@P={A115} @K={A123}
@P={A115} @K={A124}
@P={A115} @K={A54}
@P={A115} @K={A46}
@P={A126} @K={A53}
...
As you can see, each list item is unified with the variable @K. So additionally, we can access @K and print out the name using val:
family: (? (db nm +Person "Edward" @P) (lst @K @P kids) (val @Kid @K nm))
@P={A115} @K={A116} @Kid="Beatrice Mary Victoria"
@P={A115} @K={A117} @Kid="Leopold George Duncan"
The select/3 predicate
db/3 can do a direct index access only for a single attribute (like nm of +Person in the previous examples). To search for several criteria at the same time, select/3 has to be used.
: (?
(select (@P)
((nm +Person "Edward") (nm +Person "Augusta" pa)) # Generator clauses
(tolr "Edward" @P nm) # Filter clauses
(tolr "Augusta" @P kids nm) )
(show @P) )
{2-1B} (+Man)
kids ({2-1C} {2-1D} {2-1E} {2-1F} {2-1G} {2-1H} {2-1I} {2-g} {2-a})
mate {2-f}
nm "Albert Edward"
job "Prince"
fin 680370
dat 664554
@P={2-1B}
-> NIL
In the above examples, the "generators" are:
(nm +Person "Edward") to generate persons with "Edward" in their names, and "(nm +Person "Augusta" pa) to find persons with "Augusta" in their names and generate persons using the pa ("father") attribute.
Then these results are filtered using the following criteria:
- match "Edward" in their name:
(tolr "Edward" @P nm), and - match "Augusta" in one of their kids' names:
(tolr "Augusta" @P kids nm)exy wheretolr/3is a pre-defined predicate for tolerant string searches (according to the "soundex"-algorithm if defined, or substring matches).
The query is resolved in two steps: All persons generated are possible candidates for our selection. The nm index tree of +Person is traversed twice in parallel, optimizing the search in such a way that successful hits get higher priority in the search, depending on the filter clauses. The process will stop as soon as any one of the generators is exhausted. (Note that this is different from the standard Prolog search algorithm!)
In principle, db/3 can be seen as a special case of select/3. The following two queries are equivalent:
family: (? (select (@P) ((nm +Person "Edward"))))
@P={A13}
@P={A35}
@P={A115}
@P={A126}
@P={A46}
@P={A37}
-> NIL
family: (? (db nm +Person "Edward" @P))
@P={A13}
@P={A35}
@P={A115}
@P={A126}
@P={A46}
@P={A37}
-> NIL
In the next posts we will see several examples of the select and db predicates in action.