FYI on operator SEARCH

[steved]

This is a quote about the new SEARCH operator in Rel version 3.012:
"new built-in operator SEARCH(t TUPLE{*}, regex CHAR) RETURNS BOOLEAN, which returns
true if any CAST_AS_CHAR(a), where a is an attribute of t, matches regex."
Your first inclination may be to assume that a is referring to scalar attribute(s).
But what if relation t has tuples that include not only scalar value(s) but rva(s)
(relational valued attributes). What will SEARCH do when it encounters an rva?
To see what Rel does in this case execute the follow statement to create relvar TESTSEARCH
which has several rva's as well as a nested relation. It contains 4 tuples. The key is
attribute KEY# integer. KEY# ranges from 1 to 4. Please note that type Decimal is
required. If your database doesn't have this type you can load it from:
RelScripts\SampleJavaBasedTypes\TypeDecimal.rel.

Code: Select all

VAR TESTSEARCH BASE INIT(
RELATION {KEY# INTEGER, ID CHARACTER, R1 RELATION {M CHARACTER}, R2 RELATION {C CHARACTER, R3 RELATION {N INTEGER, D CHARACTER, G Decimal}}} {
	TUPLE {KEY# 1, ID "10", R1 RELATION {M CHARACTER} {
	TUPLE {M "H068"},
	TUPLE {M "H0273"},
	TUPLE {M "H0201"},
	TUPLE {M "H0118"}
}, R2 RELATION {C CHARACTER, R3 RELATION {N INTEGER, D CHARACTER, G Decimal}} {
	TUPLE {C "5Q72", R3 RELATION {N INTEGER, D CHARACTER, G Decimal} {
	TUPLE {N 10, D "KP0105.622", G Decimal(41.688, 3)},
	TUPLE {N 11, D "KP040.772", G Decimal(37.898, 3)},
	TUPLE {N 12, D "KP0312.842", G Decimal(34.74, 3)},
	TUPLE {N 13, D "KP045.54", G Decimal(32.067, 3)},
	TUPLE {N 14, D "KP084.479", G Decimal(29.777, 3)},
	TUPLE {N 15, D "KP042.699", G Decimal(27.792, 3)}
}}
}},
	TUPLE {KEY# 2, ID "14", R1 RELATION {M CHARACTER} {
	TUPLE {M "H0119"},
	TUPLE {M "H0152"},
	TUPLE {M "H0499"}
}, R2 RELATION {C CHARACTER, R3 RELATION {N INTEGER, D CHARACTER, G Decimal}} {
	TUPLE {C "5Q51", R3 RELATION {N INTEGER, D CHARACTER, G Decimal} {
	TUPLE {N 14, D "KP025.674", G Decimal(21.152, 3)},
	TUPLE {N 15, D "KP048.133", G Decimal(19.742, 3)},
	TUPLE {N 16, D "KP038.743", G Decimal(18.508, 3)},
	TUPLE {N 17, D "KP019.173", G Decimal(17.419, 3)},
	TUPLE {N 18, D "KP094.024", G Decimal(16.451, 3)},
	TUPLE {N 19, D "KP045.342", G Decimal(15.586, 3)}
}}
}},
	TUPLE {KEY# 3, ID "16", R1 RELATION {M CHARACTER} {
	TUPLE {M "H035"},
	TUPLE {M "H0418"},
	TUPLE {M "H0554"}
}, R2 RELATION {C CHARACTER, R3 RELATION {N INTEGER, D CHARACTER, G Decimal}} {
	TUPLE {C "4Q70", R3 RELATION {N INTEGER, D CHARACTER, G Decimal} {
	TUPLE {N 16, D "KP0194.876", G Decimal(20.93, 3)},
	TUPLE {N 17, D "KP046.131", G Decimal(19.699, 3)},
	TUPLE {N 18, D "KP032.775", G Decimal(18.604, 3)},
	TUPLE {N 19, D "KP062.651", G Decimal(17.625, 3)},
	TUPLE {N 20, D "KP0215.05", G Decimal(16.744, 3)}
}}
}},
	TUPLE {KEY# 4, ID "18", R1 RELATION {M CHARACTER} {
	TUPLE {M "H057"},
	TUPLE {M "H0351"},
	TUPLE {M "H0681"},
	TUPLE {M "H01033"},
	TUPLE {M "H01075"}
}, R2 RELATION {C CHARACTER, R3 RELATION {N INTEGER, D CHARACTER, G Decimal}} {
	TUPLE {C "9Q75", R3 RELATION {N INTEGER, D CHARACTER, G Decimal} {
	TUPLE {N 18, D "KP0894.048", G Decimal(40.896, 3)},
	TUPLE {N 19, D "KP055.853", G Decimal(38.743, 3)},
	TUPLE {N 20, D "KP053.574", G Decimal(36.806, 3)},
	TUPLE {N 21, D "KP095.333", G Decimal(35.054, 3)},
	TUPLE {N 22, D "KP0206.823", G Decimal(33.46, 3)},
	TUPLE {N 23, D "KP057.037", G Decimal(32.005, 3)},
	TUPLE {N 24, D "KP0120.177", G Decimal(30.672, 3)},
	TUPLE {N 25, D "KP030.5", G Decimal(29.445, 3)},
	TUPLE {N 26, D "KP069.576", G Decimal(28.313, 3)},
	TUPLE {N 27, D "KP036.981", G Decimal(27.264, 3)}
}}
}}
}
                 )KEY{KEY#} ;

View the relation in enhanced output to see the rva's clearly as well as all the scalar
attribute types and values. Now execute the following expression which adds attribute FOUND.
FOUND is type boolean. If SEARCH is working correctly then if FOUND is true SEARCH found
the string ("699") somewhere in the tuple. If FOUND is false the string wasn't found.

Code: Select all

TESTSEARCH EXTEND {FOUND:=SEARCH(TUP{*},".*699.*")}  

You should see FOUND as true for KEY# 1 (see nested rva R3 attr. D "KP042.699" and true for
KEY# 3 (see nested rva R3 attr. G Decimal(19.699,3)).
Next try the expression:

Code: Select all

TESTSEARCH EXTEND {FOUND:=SEARCH(TUP{*},".*72.*")} 

You should see FOUND as true for KEY# 1 (rva R2 attr. C "5Q72") and true for
KEY# 4 (nested rva R3 ATTR. G Decimal(30.672,3)).
You can, of course, use WHERE to restrict the returned tuples to those where SEARCH is true:

Code: Select all

TESTSEARCH WHERE SEARCH(TUP{*}, ".*72.*")

I prefer using EXTEND with SEARCH so all tuples can be returned (either true or false). I use MATCHING with
EXTEND for restricting tuples instead of using WHERE (but that's another post and I'll use WHERE here when restricting tuples).

If you want further evidence use these update example expressions to insert your own values
in relvar TESTSEARCH and use SEARCH with them. Note if you want to make the update(s) permanent
change the update expression to an update statement by adding a semicolon(;) at the end.
Use this update to change attribute M in relation R1 to another character value.

Code: Select all

UPDATE TESTSEARCH WHERE KEY#=2:{UPDATE R1 WHERE M="H0152" :{M:="AB015"}}

Use this update to change attribute C in relation R2 to another character value.

Code: Select all

UPDATE TESTSEARCH WHERE KEY#=4:{UPDATE R2:{C:="999Q55"}}

Use this update to change attribute G in nested relation R3 to another Decimal value.

Code: Select all

UPDATE TESTSEARCH WHERE KEY#=2:{UPDATE R2:{UPDATE R3 WHERE N=15:{G:=Decimal(15.999,3)}}}

What you have seen is that SEARCH has a scope that includes not only scalar attributes,
system types and user-defined types, but relational valued attributes as well. It essentially
will search everything no matter what it is and no matter where or how it's organized.
Note that using TUP{*} brings every scalar and every rva into the SEARCH scope.

Using an individual rva with SEARCH.

You can use the form: relvar WHERE SEARCH(TUPLE{arbitrary attribute name, rva}, regex CHAR)
For example SEARCH using rva R2. TUPLE attribute Y uses relation R2 which has attributes C and
nested rva R3.

Code: Select all

TESTSEARCH WHERE SEARCH(TUP{Y R2}, ".*KP036.981.*") 

You can use PROJECT with rva R2 to use just rva R3 for the search.

Code: Select all

TESTSEARCH WHERE SEARCH(TUP{Y R2{R3}}, ".*KP036.981.*") 

I find this use of SEARCH quite exceptional.

If you try to SEARCH just rva R3:

Code: Select all

TESTSEARCH WHERE SEARCH(TUP{Y R3}, ".*KP036.981.*") 

You get the error: RS0056: 'R3' has not been defined.
This is because the only way to get rva R3 in scope is thru rva R2 because it's a nested (in rva r2) rva.
It seems to me the scoping of rva's in SEARCH follow the same logic as rva scoping using EXTEND.
To duplicate the elegance of: TESTSEARCH WHERE SEARCH(TUP{Y R2{R3}}, ".*KP036.981.*")
using just rva R3 by itself in SEARCH requires multiple EXTEND operations in order to create an rva R3
like relation where SEARCH is applied to it. Notice that in this case the SEARCH restriction is not
applied to relvar TESTSEARCH but only to rva NEW.R3 and it takes another WHERE statement to restrict
relvar TESTSEARCH.

Code: Select all

((TESTSEARCH EXTEND {NEW.R2:=(R2 EXTEND {NEW.R3:=R3 WHERE SEARCH(TUP{*}, ".*KP036.981.*") }){NEW.R3}} UNGROUP NEW.R2) WHERE NOT IS_EMPTY(NEW.R3)){ALL BUT NEW.R3}

Here's the above query broken down using a WITH expression. It may be easier to follow step by step. Feel free to put
you own value for the SEARCH regex. (Note you could use the WITH definitions as input to the Rev visual query editor).

Code: Select all

WITH(T1:=TESTSEARCH EXTEND {NEW.R2:=R2 EXTEND {NEW.R3:=R3 WHERE SEARCH(TUP{*}, ".*KP036.981.*")}},
     T2:=T1 UNGROUP NEW.R2,
     T3:=T2 {ALL BUT C, R3}, //Attributes C and rva R3 in rva NEW.R2 are in a different scope from C and R3 in rva R2 so there's no conflict.
     T4:=T3 WHERE NOT IS_EMPTY(NEW.R3)  //Restrict relvar TESTSEARCH based on SEARCH applied to rva R3 by itself (rva NEW.R3).
     ):T4{ATTRIBUTES_OF(TESTSEARCH)}  /* Substitute expression T1, T2 OR T3 for T4{ATTRIBUTES_OF(TESTSEARCH)} to see the query at that particular point. */

Admittedly there are other solutions but none can be so simple as using SEARCH(TUP{Y R2{R3}}.".*KP036.981.*")

Operators like SEARCH is why its worth the investment to learn a relational dbms.
Especially a relational dbms like Rel.

P.S. I didn't write a query or create a relational literal by inputting data for relvar TESTSEARCH.
It was created/populated using an operator I named RAD. R(el)A(utomated)D(ata). It can generate
pretty much any kind of data structured in any way you can imagine. I struggle with documenting stuff
but if I ever get through documenting it I will post operator RAD

[Dave]

SEARCH(...) was developed as a an ad-hoc hack, purely to support the full-text search facility in the Rel user interface. If there are uses outside of that, that's excellent, but unintentional.