Back to subdatabases, with tools


Our last query

? select find_record(i) from unid_node where a = -145 limit 2

was fast enough in reaching records containing a certain type of property, provided we added a generic partial symbol (-145) targeting this type. Now we must notice that it’s exactly what does a subdatabase. No need to describe our properties for the sake of speed if we work with subdatabases then. They offer additionally quick means of checking which kinds of properties a record has. Of course – due to the non-coercion principle – Unidatab lets put whatever in a subrecord, and it probably won’t disturb much when we don’t respect our subdatabase schema, unless we work in scientific context. In this case we should perhaps create these special symbols with no property and a type (like ‘has x’) each time we add some type x of property to a subrecord whose template didn’t define x. We could also do the same for each subrecord that lacks some type y of property (‘lacks y’). For it is interesting to store properties only as long as we can extract them with some ease. Unidatab offers several functions designed to help in this task. Let’s see first how we can refer to the records of one subdatabase, for it’s a task we’re going to execute often.

? select * from unid_subdbs
i       sdb                  n    a    t    u        :
-185    #note template       -186  57  1479316217    admin    
-101    #Unid link template  -102  30  1479316216    admin    
-24     #Unid table template  -25  22  1479316214    admin    
? select * from unid_formats where a = 57
i    for            a  t          u :
-184 #note template 57 1479316217 admin 
? select find_record(i) from unid_node where a = -184
find_record(i) :
-185 
-177 

In this set of three queries, we first identified our subdb by id. Then found the id of the corresponding format (-184) which – by the way is also a symbol – and then only we could issue the query that isolated the desired set (records members of subdb ‘note template’). But how could have we done that quicker, or in lesser steps?

The nature of formats implies a blank property, set to 0 and a special string beginning with a sign that can’t be typed directly, in order to distinguish and normalize the manipulation of subdatabases.

? select find_record(i) from unid_node where a = (select sym.i from unid_symbols as sym where sym.a like get_string(get_chars('UNID_CODE_SUBDB')||"note template") and n = 0 and d = 1 limit 1)
find_record(i)        :
-185    
-177

However fast, this query clashes with Unidatab’s creative retrieval behaviour: if the string didn’t exist, get_string() would create it leaving useless traces of our typo (erased at the next administrator’s remove_junks()) and of course crashing the request.

We’d like to use get_symbol() to retrieve the format id, but it would fail too, for get_symbol() protects special cases by not allowing a 0-value (at least not in the same sense as subdbs do).

The ‘subdbs’ view does not provide directly the id of the format symbol either. But the ‘formats’ view contains the desired information:

? select find_record(i) from unid_node where a = (select for.i from unid_formats as for where for.for like get_chars('UNID_CODE_SUBDB')||'note template' limit 1) 
find_record(i)        :
-185    
-177

The same thing can be done using only the a-value of the subdbs view, for a subdb’s title equals its format. This kind of access to subrecords is perfect to script some high level function in an application using Unidatab, but lacks handiness. The best means left to us at this stage to recover our subdatabase format without difficulty, is to create an alias of our format symbol:

? select set_alias(-184,'note_sdb')
set_alias(-184,'note_sdb')        :
184
? select find_record(i) from node where a = -(get_id('note_sdb'))
find_record(i)        :
-185    
-177    

Unlike records, aliased symbols are stored as absolute values (here 184) and need to be converted back to the negative.

We could stop here and leave search functions for a later post, but as a teaser, let’s review a pair of utilities that sum up the kind of queries mentionned above.

locate_node()

does in lesser words what our last search did, and above all, as other search functions, locate_node() stores its results so we can later use them in other queries without needing to create temporary tables.

? select locate_node("a = -(get_id('note_sdb'))")
SELECT i,a,d,n,t,u FROM node WHERE (a = -(get_id('note_sdb')));locate_node("a = -(get_id('note_sdb'))")        :
-186    -184    -185    -187    1479316217    admin    
-178    -184    -187    -179    1479316217    admin        
? select last_out('select find_record(:unid-item:)',1)
last_out('select find_record(:unid-item:)',1) :
-185 
-177 

[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