Search functions


Introduction

In this post we’ll explore ways of selecting records by comparison of properties. Designing even the simplest scientific examples implied too many decisions on the theoretical level for us to risk here. Instead, we’re going to fancy we need holidays. Let’s see how we can retrieve properties of some hotels we have in memory. First we are going to create two subdatabases, one for hotels and the other for facilities. The following examples make use of the multiline input feature of unid-adm (an ‘§‘ at end of line provides a new input line).
? select get_subject('hotel','hotel',§
  ':unid-protyp-sep:facilities:unid-params-sep:§
  :unid-protyp-sep:view:unid-params-sep:§
  :unid-protyp-sep:price','hotels','normal')
-206
Now let’s check that the subdb exists and is made correctly:
? select * from unid_subdbs
i          sdb               n      a           t       u :
-206  hotel                 -202    63   1479827203    admin 
-185  note template         -186    57   1479827165    admin 
-101  Unid link template    -102    30   1479827163    admin 
-24   Unid table template   -25     22   1479827162    admin
? select send_record('hotels',1)
send_record('hotels',1)        :
 Type    Property    objid    typid    propid    unicity    nodeid    Nesting
    hotel             63                                      -206        R0
  hotel             -204       63         0          1        -202    
  facilities        -201       64         3          0        -207     
  view              -208       65         3          0        -209    
  price             -210       66         3          0        -211

As everything seems ok, we can then make the ‘facilities’ subdb:

? select get_subject('facilities','facilities',§
 ':unid-protyp-sep:dancing:unid-params-sep:§
 :unid-protyp-sep:fitness:unid-params-sep:§
 :unid-protyp-sep:ping-pong:unid-params-sep:§
 :unid-protyp-sep:spa:unid-params-sep:§
 :unid-protyp-sep:swimming-pool:unid-params-sep:§
 :unid-protyp-sep:tennis:unid-params-sep:§
 :unid-protyp-sep:trekking:unid-params-sep:§
 :unid-protyp-sep:wifi','facilities','normal')
-212

? select send_record('facilities',1)
send_record('facilities',1)        :
Type    Property    objid    typid    propid    unicity    nodeid    Nesting
    facilities                68                            -212       R0
facilities           -213     68       0         1          -214    
dancing              -215     69       3         0          -216    
fitness              -217     70       3         0          -218    
ping-pong            -219     71       3         0          -220    
spa                  -221     72       3         0          -222    
swimming-pool        -223     73       3         0          -224    
tennis               -225     74       3         0          -226    
trekking             -227     75       3         0          -228    
wifi                 -229     76       3         0          -230

These are empty templates we are going to examplify in subrecords with properties:

? select examplify('hotel','Hotel 1''center:unid-protyp-sep:view:unid-params-sep:§
wifi,swimming-pool,ping-pong:unid-protyp-sep:facilities:unid-params-sep:§
120:unid-protyp-sep:price','hotel1') 
-231
? select examplify('hotel','Hotel 2',§
'sea:unid-protyp-sep:view:unid-params-sep:§
 wifi,spa,tennis:unid-protyp-sep:facilities:unid-params-sep:§
 110:unid-protyp-sep:price','hotel2')
 -254

We could as well have used get_subject() to create a subrecord, as in:

? select get_subject('Facilities C','facilities',§
 '0:unid-protyp-sep:wifi:unid-params-sep:§
 1:unid-protyp-sep:spa:unid-params-sep:§
 1:unid-protyp-sep:trekking:unid-params-sep:§
 0:unid-protyp-sep:swimming-pool:unid-params-sep:§
 0:unid-protyp-sep:ping-pong:unid-params-sep:§
 0:unid-protyp-sep:fitness:unid-params-sep:§
 1:unid-protyp-sep:dancing','cfacil','normal')
 -238

? select send_record(-238,1)
send_record(-238,1)        :
 Type        Property    objid    typid    propid    unicity    nodeid    Nesting
 Facilities C                       80                            -238        R0
 facilities             -213        68        0         1         -239
 wifi           0       -240        76       81         0         -241
 spa            1       -242        72       82         0         -243
 trekking       1       -244        75       82         0         -245
 swimming-pool  0       -246        73       81         0         -247
 ping-pong      0       -248        71       81         0         -249
 fitness        0       -250        70       81         0         -251
 dancing        1       -252        69       82         0         -253
 
? select get_subject('Hotel 3','hotel',§
 'mountain:unid-protyp-sep:view:unid-params-sep:||get_chars('UNID_CODE_SUBDB')||§
 '-238:unid-protyp-sep:facilities:unid-params-sep:§
 90:unid-protyp-sep:price','hotel3','normal') 
-261
Note the special use of UNID_CODE_SUBDB to insert a record-property. Correct quoting required. This feature is used by make_full_record() which is called by get_subject(); thus a similar syntax is valid for make_full_record() and should be valid for examplify().
? select send_record('hotel1',1)
send_record('hotel1',1)        :
Type    Property    objid    typid    propid    unicity    nodeid    Nesting
    Hotel 1                   77                            -231       R0
hotel               -204      63        0         1         -232    
view    center      -233      65       78         0         -234    
facilities    wifi,swimming-pool,ping-pong    
                    -268      64       88         0         -235    
price     120       -236      67       79         0         -237    

? select send_record('hotel2',1)
send_record('hotel2',1)        :
Type    Property    objid    typid    propid    unicity    nodeid    Nesting
    Hotel 2                    83                            -254      R0
hotel               -204       63       0          1         -255    
view       sea      -270       65      90          0         -257    
facilities wifi,tennis,fitness 
                    -269       64      89          0         -259    
price      110      -271       67      91          1         -260    

? select send_record('hotel3',1)
send_record('hotel3',1)        :
Type    Property    objid    typid    propid    unicity    nodeid    Nesting
    Hotel 3            86                                   -261       R0
hotel                -204      63        0         1        -262    
view        mountain -256      65       84         0        -263    
facilities  Facilities C
                     -264      64      -238        0        -265    
price       90       -266      67        87        0        -267

These three records all have a type of property called ‘facilities’, but in the third this property links to a full record with predefined binary facilities, while the others have it in one string. The question we are facing is : how can we retrieve and compare properties when they are not stored the same way. Before starting, let’s say that this case shouldn’t happen; in a well managed db, the UnidSql type of a property should always be known before searching. But we also know that a database rarely remains in a finished state.

list_form()

? select list_form(get_id('hotel1'),0)
list_form(get_id('hotel1'),0) :
4 -- STEP 1 
In the previous post we tried to get the members of a subdatabase and opted for a satisfying solution with symbol aliases. Here we disclose a function specially dedicated to this task. The function stores the list and returns its size. list_form() wants the id of any record to gather all the records sharing its format. So there are three records to explore, plus -206, which is the template. We can continue with last_out(), as long as we don’t forget to place the item we want to work on in first position of our selection.
Let’s search for hotels where the price is smaller than 120.-
? select last_out("§
 select i,rec from unid_records where i=:unid-item:and §
 search_record(:unid-item:,'price', 1)+0<120",1)
 -261    Hotel 3
 -254    Hotel 2
 -206    hotel -- STEP 2
We get only two valid hotels, the third being the template – as usual. search_record() returned the value of type ‘price’. Remark the ‘+0‘ form: this comparison only works that way on my version of Sqlite3. Let’s check if they both have wifi:

search_record()

? select last_out(§
"select i,rec from unid_records where i=:unid-item: and §
regex(search_record(:unid-item:,'facilities',1),'.*wifi.*',0) = 1",1)
-254 Hotel 2 -- STEP 3
search_record() extracts different kinds of information according to its mode, here it is a property (the full data of type ‘facilities’) which is then passed to regex(), that in mode 0, does in turn only confirm the string contains substring ‘wifi’ or not.
Ok, but as we have records that link to other records (for a checklist of facilities) instead of providing a combination of facilities in a string,we have to use another tool to bear this typological constraint.

look_up()

-- restarting from STEP 2:
? select last_out(§
 "select i,rec from unid_records where i=:unid-item:and §
 (regex(search_record(:unid-item:,'facilities',1),'.*wifi.*',0) = 1 or §
 look_up('1','wifi',:unid-item:,4)=1)",1)
-254 Hotel 2 -- STEP 3
In spite of extending the search, this command confirms our previous result. The string ‘wifi’ is mentionned once as type and once as property in this request. We could have asked another combination of facilities, for instance either ‘wifi’ or ‘dancing’:
-- restarting from STEP 2:
? select last_out(§
 "select i,rec from unid_records where i=:unid-item:and §
 (regex(search_record(:unid-item:,'facilities',1),'.*wifi.*',0) = 1 or §
 look_up('1','dancing',:unid-item:,4)=1)",1)
-261    Hotel 3    
-254    Hotel 2 -- STEP 3

list_occs()

list_occs() is for the ignorant and the lazy, for neither does it require knowledge of the typology in use nor special skills in SQL. list_occs() searches the symbol table for symbols showing the parametrized properties without considering subdatabases or formats specifically. This command has to be followed by a locate_node() for each of the output symbols in order to get back to records:
? select list_occs('%wifi%','facilities','protyp')
2    
? select last_out('',0)
-269
-268

? select locate_node("a=-269 or a=-268")
SELECT i,a,d,n,t,u FROM node WHERE (a=-269 or a=-268);locate_node("a=-269 or a=-268")        :
-259    -269    -257    -260    1479977360    admin    
-235    -268    -260    -237    1479975548    admin
? select last_out("select find_record(:unid-item:)",1)
last_out("select find_record(:unid-item:)",1) :
-254 
-231
Using list_occs() engages in more manipulations and will not help to isolate records from one subdatabase, however, allowing partial search content as above (for ‘%‘ matches any character in any quantity), it is powerful to get information back from an unsound db. Caveat: allowing too indeterminate searches can yield very long output – list_occs(‘%’,’%’,’protyp’) would output all the symbols in the base.

Conclusion

The commands we have seen in this post can be combined in several ways without guarantee that they are compatible, since many of them store their data in datares, which is the source array for last_out(). At least, last_out() refuses to be used recursively; other functions haven’t been tested in depth and surely fail in several scenarios. For some functions like search_record(), can produce different kinds of output according to their mode, and it is up to the programmer to verify the compatibility of her request. look_up() is recursive and thus the time of execution depends on the typological complexity of the investigated database. An efficient database will certainly try to limit the amount of recursive levels in information. Whether a property has to be encoded in a string as above or requires a descriptive object (a record) is an important decision in the design of a database.
A last good thing to know, is that everything that can be done in several steps with the help of last_out(), can be done in one step and more effort in writing the request. But often it will be more informative to use last_out(), for while you search step by step for a certain combination of properties, you can see which ones exclude or include many records and such information is useful. Having the request done at once only extracts the wanted information if any.
[2018 edit]
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s