Mapping boolean operators to SQL

Oracle's REGEXP_LIKE() function has the same syntax and semantics as Dataphor's System.iMatches() operator. I try to map iMatches() to Oracle like so:

alter device MyOracleDevice {
create operator iMatches(String, String)
class "SQLDevice.SQLCallOperator" attributes { "OperatorName" = "regexp_like" }
};

But queries like "select Baz where Foo matches '.*'" are then translated to SQL as "select T1.Foo as Foo from Baz T1 where (regexp_like(T1.Foo, '.*') = 1)", which doesn't work, because regexp_like() returns a boolean, not an integer.

Likewise, this mapping doesn't work either:

alter device MyOracleDevice {
create operator iMatches(String, String)
tags { Storage.TranslationString = "regexp_like({0}, {1})" }
};

---the TranslationString has to be something like "case when regexp_like({0}, {1}) then 1 else 0 end". Why is this so convoluted?

Speaking of operator mapping: What are the advantages of SQLCallOperator mapping as opposed to TranslationString mapping? The documentation presents TranslationString almost like a last resort if you can't create operators in the target system, but I prefer not to create SQL functions if I can help it. Is SQLCallOperator mapping faster or safer in any way?

Also, I've been trying to map IsSpecial()-type operators using both the techniques above, but this corrupts the catalog to the extent that I have to delete the DAECatalog.sdf file. Is this just another symptom of the bugginess of IsSpecial(), or must the mapping be special in some way, too? I think I've seen the tag DAE.IsSystem used in connection with operator mapping (but I don't remember where); does that have anything to do with it?

And are the following operator mappings equivalent? Is one a shorthand for the other?

create operator iMatches(String, String)
tags { Storage.TranslationString = "regexp_like({0}, {1})" }

create operator iMatches(String, String)
class "SQLDevice.SQLUserOperator" attributes { "TranslationString" = "regexp_like({0}, {1})" }

Operator Mappings

Hi Jon,

---the TranslationString has to be something like "case when regexp_like({0}, {1}) then 1 else 0 end". Why is this so convoluted?

Good question. The reason is the fact that SQL is syntactically opposed to boolean expressions. Most SQL dialects have a boolean type nowadays, but they still have trouble actually representing boolean-valued expressions. If I have a boolean column B, for example, I should be able to say:

select * from T where B

But in SQL I have to say:

select * from T where B = true

To further complicate the issue, the Dataphor SQL translator has to cater to the lowest common denominator, so even though most dialects do have boolean types, the Dataphor translator still uses integers and comparisons to produce boolean-valued expressions. If the Oracle syntax allows this construction:

select * from T where regexp_like(StringColumn, 'Blah%')

Then you can use a SQLCallOperator and specify that it is a truth-valued operator:

alter device MyOracleDevice {
create operator iMatches(String, String)
class "SQLDevice.SQLCallOperator" attributes { "OperatorName" = "regexp_like", IsTruthValued = "true" }
};

Otherwise, you will need to continue to use the TranslationString as you have it.

As far as the difference between SQLCallOperator and SQLUserOperator, the only difference is that SQLCallOperator is still participating at the abstract syntax tree level, where the SQLUserOperator is embedding directly in the target dialect. For operator calls, this makes very little difference because all the dialects have operator invocation in common. As it is, SQLCallOperator is just a shorthand for SQLUserOperator when the invocation is simple.

As far as mappings for the IsSpecial operators, when you map a type into a device, the system does attempt to map the IsSpecial operators automatically. However, it sounds like this mechanism is not working as expected, which doesn't surprise me because specials have not received as much coverage as the other aspects of the translation. Can you post a specific error you are getting in connection with the mapping of types with specials defined?

As far as the use of the Storage.TranslationString tag, yes, it is just a shorthand for the equivalent SQLUserOperator mapping.

Regards,
Bryn Rhodes
Database Consulting Group LLC

Operator Mappings

Thanks; "IsTruthValued" = "true" did the trick, and makes the code a little cleaner. :)

As for IsSpecial(), I can't now reproduce the catalog corruption problem. But IsSpecial() is not mapped automatically. Like I reported in a different thread, I can't test "select T where C.IsSpecial()", since it doesn't compile, but I can create a reference from a column with a special value. When doing so, I get a warning that there is no operator map for IsSpecial(). However, I have now managed to map it successfully using a Storage.TranslationString tag (looking e.g. like this: "case when {0} = '0000' then 1 else 0 end"---is it possible to specify that this is also truth-valued, so that "{0} = '0000'" is sufficient?).

Is there still a tag by the name of DAE.IsSystem? What is it for? I use it on my mapping of the standard deviation aggregate function, which exists in Oracle but not in Dataphor, and I believe I did it based on an example in the Dataphor docs that I can't find anymore.

DAE.IsSystem

Hi Jon,

Yes, you can also use the IsTruthValued attribute in combination with the IsTruthValued attribute:


create operator iMatches(String, String)
class "SQLDevice.SQLUserOperator" attributes { "TranslationString" = "regexp_like({0}, {1})", "IsTruthValued" = "true" }

As far as the DAE.IsSystem tag, for device scalar types and operators, it is only used to determine whether or not the objects should be scripted as part of a call to ScriptObject (used by the Emit menu items in Dataphoria). It used to be used as part of the persistence mechanism, but that is no longer the case. You should not set it on user-created maps, but doing so really doesn't hurt anything, it just makes the map show up in the system-created list when it really shouldn't.

Regards,
Bryn Rhodes
Database Consulting Group LLC