8

The Three Laws of Robotic Vacuum Cleaners and the ANYDATA Datatype | The Anti-Ky...

 2 years ago
source link: https://mikesmithers.wordpress.com/2021/10/06/the-three-laws-of-robotic-vacuum-cleaners-and-the-anydata-datatype/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

The Three Laws of Robotic Vacuum Cleaners and the ANYDATA Datatype

Posted on October 6, 2021

After some observation of my recently acquired robotic vacuum cleaner, I’ve concluded that Asimov’s Three Laws of Robotics require some revision :

First Law – A robot may not traverse stairs. If you want it to do the whole house then move to a bungalow.

Second Law – A robot must stop in the most inaccessible place possible, such as under a bed or a sofa, unless doing so would conflict with the First Law

Third Law – A robot must seek out and entangle itself in any loose wires/pet toys/socks it can find unless doing so would conflict with the First or Second Laws.

Mind you, these Laws are unlikely to be immutable. I mean, The Daleks never used to be able to climb stairs…until they discovered CGI…

The Application Parameters Table

If we were to capture these settings in an application, we’d probably use some kind of config file. After all, we’d like some flexibility to change the robot’s behaviour if the laws change, without having to rewrite the software. If the application was running on Oracle, then we’d be more likely to use a table like this :

create table vacuum_params(
parameter_name varchar2(100),
varchar_value varchar2(4000),
number_value number,
date_value date,
datatype varchar2(10) not null,
description varchar2(4000) not null,
constraint vacuum_params_pk primary key (parameter_name),
constraint vacuum_params_datatype_chk check (datatype in ('VARCHAR2', 'NUMBER', 'DATE')))
/   

With most tables of this sort, it’s fairly safe to assume that any code reading it will “know” the datatype of the parameter it’s looking up. However, that’s not necessarily the case with any DML statements. Therefore, we have to do a bit of work to ensure that :

  • there is one value per parameter, but no more
  • the value is of the correct type
create or replace trigger vacuum_params_biu
before insert or update on vacuum_params
for each row
declare
v_param_count pls_integer;
begin
-- there must be one, and only one, value set for a parameter
v_param_count :=
case when :new.varchar_value is null then 0 else 1 end +
case when :new.number_value is null then 0 else 1 end +
case when :new.date_value is null then 0 else 1 end;
if v_param_count = 0 then
raise_application_error(-20990, 'A value must be specified for a parameter');
elsif v_param_count > 1 then
raise_application_error(-20991, 'Only one value can be specified for a parameter');
end if;
if
:new.varchar_value is not null and :new.datatype != 'VARCHAR2' or
:new.number_value is not null and :new.datatype != 'NUMBER' or
:new.date_value is not null and :new.datatype != 'DATE'
then
raise_application_error(-20992, 'The parameter must be of the type specified in DATATYPE');
end if;
end;
/

This trigger does what we need so we can now safely add some parameters.

insert into vacuum_params( parameter_name, varchar_value, datatype, description)
values('DALEK_MODE', 'Y', 'VARCHAR2', q'[If set to 'Y' then do not ascend or descend stairs. If 'N' then clean stairs.]');
insert into vacuum_params( parameter_name, number_value, datatype, description)
values('TRAILING_WIRES_FOUND', 38, 'NUMBER', 'A count of the trailing wires that the robot has got tangled in');
insert into vacuum_params(parameter_name, date_value, datatype, description)
values('DATE_LAST_STUCK', date '2021-10-02', 'DATE', 'The date on which the robot last played hide-and-seek under the sofa');
commit;

The ANYDATA Datatype

Using Oracle’s ANYDATA datatype, we can regain some of the “for-free” data validation functionality offered by a more conventional table structure :

drop table vacuum_params;
create table vacuum_params(
parameter_name varchar2(100),
param_value anydata not null,
datatype varchar2(10) not null,
description varchar2(4000),
constraint vacuum_params_datatype_chk check( datatype in ( 'VARCHAR2', 'NUMBER', 'DATE')),
CONSTRAINT vacuum_params_pk primary key( parameter_name))
/

As we now have only one value column, we can make the value mandatory by simply adding a NOT NULL constraint. However, a trigger is still required to ensure that each parameter has values of the correct type…

create or replace trigger vacuum_params_biu
before insert or update on vacuum_params
for each row
begin
-- No need to count the number of values passed in because there is only one.
-- Also that column now has a not null constraint.
-- However, if we try to use the anydata method on a null argument, we run into
-- ORA-30625: method dispatch on NULL SELF argument is disallowed.
-- So, if the param_value is null then just get out of the way and let the not null constraint
-- do it's thing.
if :new.param_value is null then
return;
end if;
if
anydata.getTypeName(:new.param_value) = 'SYS.VARCHAR2' and :new.datatype != 'VARCHAR2' or
anydata.getTypeName(:new.param_value) = 'SYS.NUMBER' and :new.datatype != 'NUMBER' or
anydata.getTypeName(:new.param_value) = 'SYS.DATE' and :new.datatype != 'DATE'
then   
raise_application_error(-20992, 'The parameter must be of the type specified in DATATYPE');
elsif anydata.getTypeName(:new.param_value) not in ('SYS.VARCHAR2', 'SYS.NUMBER', 'SYS.DATE')
then
raise_application_error(-20993, 'Parameter values must be of type VARCHAR2, NUMBER or DATE');
end if;   
end;
/

Being an object type, we now need to use ANYDATA’s methods when performing DML on the PARAM_VALUE column. :

insert into vacuum_params( parameter_name, param_value, datatype, description)
values(
'DALEK_MODE',
anydata.convertVarchar2('Y'),
'VARCHAR2',
q'[If set to 'Y' then do not ascend or descend stairs. If 'N' then clean stairs.]');
insert into vacuum_params( parameter_name, param_value, datatype, description)
values(
'TRAILING_WIRES_FOUND',
anydata.convertNumber(38),
'NUMBER',
'A count of the trailing wires that the robot has got tangled in');
insert into vacuum_params( parameter_name, param_value, datatype, description)
values(
'DATE_LAST_STUCK',
anydata.convertDate(date '2021-10-02'),
'DATE',
'The date on which the robot last played hide-and-seek under the sofa');

Using the native types directly in the insert tends to generate rather unhelpful error messages :

insert into vacuum_params( parameter_name, param_value, datatype, description)
values(
'ROBOT_NAME',
'HUSBAND 2.0',
'VARCHAR2',
'More reliable than the original');
SQL Error: ORA-00932: inconsistent datatypes: expected CHAR got CHAR
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:   
*Action:

Querying from the table is also a bit different

select parameter_name,
case anydata.getTypeName(param_value)
when 'SYS.VARCHAR2' then anydata.accessVarchar2(param_value)
when 'SYS.NUMBER' then to_char(anydata.accessNumber(param_value))
when 'SYS.DATE' then to_char(anydata.accessDate(param_value), 'DD-MON-YYYY')
end as param_value,
datatype,
description
from vacuum_params;

…but the result is a bit neater…

Whilst ANYDATA may not be the ideal solution, it is worth considering if you like to keep you’re table structures nice and clean with minimal effort…just like my floors.

Advertisements
Report this ad

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK