C-squares now in use at AquaMaps, Mapping Tools, FishBase, CephBase, OBIS, MarLIN and inhouse here at CSIRO (CAAB and satellite data mapping)
Views
- State: published
validator-plsql.txt
Document Actions
File contents
-------------------------------------------------------------------------------------------------
-- function: valid_csq --
-- written by: Tony Rees, CSIRO Australia (Tony.Rees@csiro.au) --
-- date created: April, 2005 --
-- purpose: determine if a supplied single c-square code is valid --
-- input: csquare code as csq --
-- output: 'Y' if valid or error message if not valid --
-- remarks: (1) will fail if supplied with more than Oracle varchar2 limit (32767 chars) --
-- (2) will reject codes in excess of 100 characters (likely to be erroneous) --
-------------------------------------------------------------------------------------------------
function valid_csq (csq varchar2 := null) return varchar2 is
csq_string varchar2(100);
bad_chars_string varchar2(100);
err_msg varchar2(100) := null;
asterisk_start_reached varchar2(1) := 'N';
begin
if csq is null then
err_msg := 'missing c-square code';
goto exit_sub;
elsif length(csq) >100 then
err_msg := 'c-square code exceeds 100 characters (probable error)';
goto exit_sub;
end if;
--first check for illegal characters - ^ (random illegal character) added as there must be
-- at least one character translated into a non null character with this Oracle function
-- (Note, this operation replaces all the "permitted" characters 0123456789:* with nulls)
bad_chars_string := translate(csq, '^0123456789:*','^');
if length(bad_chars_string) >0 then -- something remains after replacement, not a permitted character
err_msg := 'illegal character "'||substr(bad_chars_string,1,1)||'" at position '||
to_char(instr(csq,substr(bad_chars_string,1,1),1));
goto exit_sub;
-- valid c-square code must be minimum 4 characters
elsif length(csq) <4 then
err_msg := 'c-square code is incomplete';
goto exit_sub;
-- test length of first cycle (to initial colon), reject if other than 4 characters
-- (add a trailing colon in case none exists)
elsif instr((csq||':'),':',1)-1 <4 then
err_msg := 'initial cycle contains less than 4 digits';
goto exit_sub;
elsif instr((csq||':'),':',1)-1 >4 then
err_msg := 'initial cycle contains more than 4 digits';
goto exit_sub;
else -- check the initial cycle
-- first cycle is 4 characters
-- now test they are all digits (no asterisks allowed)
bad_chars_string := translate(substr(csq,1,4), '^0123456789','^');
if length(bad_chars_string) >0 then -- a character has been entered not in the set 0-9
err_msg := 'illegal character found at "'||substr(bad_chars_string,1,1)||'"';
goto exit_sub;
-- check global quadrant (first character) for validity - must be 1, 3, 5 or 7
elsif substr(csq,1,1) not in ('1','3','5','7') then
err_msg := 'illegal global quadrant value at '||substr(csq, 1,1)||'xxx';
goto exit_sub;
-- check tens of degrees of latitude and longitude for validity
-- minimum absolute latitude: character 2 (cannot be more than 89.9999999...)
elsif to_number(substr(csq,2,1)) not between 0 and 8 then
err_msg := 'illegal latitude value at x'||substr(csq, 2,1)||'xx';
goto exit_sub;
-- minimum absolute longitude: characters 3 and 4 (cannot be more than 179.999999...)
elsif to_number(substr(csq,3,2)) not between 0 and 17 then
err_msg := 'illegal longitude value at xx'||substr(csq, 3,2);
goto exit_sub;
end if;
end if;
if err_msg is null and length(csq) >4 then -- more than one cycle is present
csq_string := csq;
-- First test for asterisks. If present, check correct form, then delete any valid cycles with asterisks
-- before further processing
if csq_string like '%*%' then -- asterisk/s present
-- delete any final "truncated" cycle (colon + single asterisk)
if csq_string like '%:*' then
csq_string := substr(csq_string, 1, length(csq_string)-2);
end if;
-- remaining cycles (apart from the first) must be three characters, 5 legal options if
-- asterisks present. Test backwards from the end (since asterisks must always be in a chain)
while csq_string like '%*' and length(csq_string) >4 and err_msg is null
and asterisk_start_reached != 'Y' loop
if csq_string like '%:***' then
-- current last cycle is valid and all asterisks,
-- strip the cycle before testing the previous one
csq_string := substr(csq_string, 1, length(csq_string)-4);
elsif csq_string like '%:1**' or csq_string like '%:2**'
or csq_string like '%:3**' or csq_string like '%:4**' then
-- current last cycle is valid, strip the cycle but don't test previous one,
-- since the start of the legal asterisk block has been reached
csq_string := substr(csq_string, 1, length(csq_string)-4);
asterisk_start_reached := 'Y';
else
err_msg := 'illegal triplet at '||substr(csq_string,length(csq_string)-3);
goto exit_sub;
end if;
end loop;
end if;
-- detect any valid remaining final "truncated" cycle (colon + single digit 1-4) and delete,
-- flag if invalid value present
if err_msg is null then
if csq_string like '%:1' or csq_string like '%:2' or csq_string like '%:3' or
csq_string like '%:4' then
csq_string := substr(csq_string, 1, length(csq_string)-2);
elsif csq_string like '%:5' or csq_string like '%:6' or csq_string like '%:7' or
csq_string like '%:8' or csq_string like '%:9' then
err_msg := 'illegal final intermediate quadrant value';
goto exit_sub;
elsif csq_string like '%:' then
err_msg := 'code cannot terminate with a colon';
goto exit_sub;
end if;
end if;
-- now examine each cycle in turn, following the initial 4 digits
-- (any valid final "truncated" cycle has already been stripped,
-- also any valid segments containing asterisks, so any remaining
-- ones should be 3 digits, with no asterisks)
-- remove initial 4 digits, leaving colon at start of next cycle
csq_string := substr(csq_string,5);
while length(csq_string) > 0 and err_msg is null loop
-- cycle must be a colon plus 3 digits (plus additional cycles if present)
if length(csq_string) <4 then
err_msg := 'incomplete cycle after colon character';
goto exit_sub;
-- now check length of the cycle - to next colon character
-- (trailing colon added in case not present)
elsif instr((csq_string||':'),':',2) >5 then
err_msg := 'cycle '||substr(csq_string,1,5)||'... contains more than 3 digits';
goto exit_sub;
-- check for valid intermediate quadrant digit
elsif substr(csq_string,2,1) not in ('1','2','3','4') then
err_msg := 'illegal intermediate quadrant value at '||substr(csq_string,1,4);
goto exit_sub;
-- if three digits are correctly present in cycle, second and third must agree
-- with the designated intermediate quadrant
(
substr(csq_string,2,1) = '1' and
(to_number(substr(csq_string,3,1)) not between 0 and 4
or to_number(substr(csq_string,4,1)) not between 0 and 4)
)
or
(
substr(csq_string,2,1) = '2' and
(to_number(substr(csq_string,3,1)) not between 0 and 4
or to_number(substr(csq_string,4,1)) not between 5 and 9)
)
or
(
substr(csq_string,2,1) = '3' and
(to_number(substr(csq_string,3,1)) not between 5 and 9
or to_number(substr(csq_string,4,1)) not between 0 and 4)
)
or
(
substr(csq_string,2,1) = '4' and
(to_number(substr(csq_string,3,1)) not between 5 and 9
or to_number(substr(csq_string,4,1)) not between 5 and 9)
)
then
err_msg := 'illegal triplet at '||substr(csq_string,1,4);
goto exit_sub;
end if;
-- move to the next cycle (strip the one just tested)
csq_string := substr(csq_string, 5);
end loop;
end if;
<<exit_sub>>
if err_msg is not null then
return err_msg;
else
return 'Y';
end if;
end valid_csq;
-------------------------------------------------------------------------------------------------
-- function: valid_csq_string --
-- written by: Tony Rees, CSIRO Australia (Tony.Rees@csiro.au) --
-- date created: April, 2005 --
-- purpose: determine if a supplied c-squares string is valid --
-- input: csq_string as csq_str --
-- output: 'Y' if valid or error message if not valid --
-- remarks: will fail if supplied with more than Oracle varchar2 limit (32767 chars) --
-------------------------------------------------------------------------------------------------
function valid_csq_string (csq_str varchar2 := null) return varchar2 is
csq_string varchar2(32767);
bad_chars_string varchar2(32767);
this_csq varchar2(100);
this_result varchar2(200);
code_count integer :=0;
prev_code_length integer;
this_code_length integer;
err_msg varchar2(100) := null;
begin
if csq_str is null then
err_msg := 'no c-squares string supplied';
goto exit_sub;
end if;
--first check for illegal characters - ^ (random illegal character) added as there must be
-- at least one character translated into a non null character with this Oracle function
-- (Note, this operation replaces all the "permitted" characters 0123456789:*| with nulls)
bad_chars_string := translate(csq_str, '^0123456789:*|','^');
if length(bad_chars_string) >0 then -- something remains after replacement, not a permitted character
err_msg := 'illegal character "'||substr(bad_chars_string,1,1)||'" found at position '||
to_char(instr(csq_str,substr(bad_chars_string,1,1),1));
goto exit_sub;
-- valid c-squares string (minimum 1 code) must be minimum 4 characters
elsif length(csq_str) <4 then
err_msg := 'c-square code is incomplete';
goto exit_sub;
elsif csq_str like '%|' then
err_msg := 'c-square string cannot terminate with separator character';
goto exit_sub;
elsif csq_str like '|%' then
err_msg := 'c-square string cannot start with separator character';
goto exit_sub;
end if;
if err_msg is null then
-- add a final separator character (otherwise will loop for ever)
csq_string := csq_str||'|';
while length(csq_string) >0 and err_msg is null loop
code_count := code_count+1;
prev_code_length := this_code_length;
this_csq := substr(csq_string,1,instr(csq_string,'|',1)-1);
this_result := valid_csq(this_csq);
this_code_length := length(this_csq);
if this_result != 'Y' then
err_msg := this_result||' (at code count='||to_char(code_count)||')';
goto exit_sub;
end if;
-- not permitted to mix resolutions (code lengths) within the same string
if code_count >1 and this_code_length != prev_code_length then
err_msg := ' inconsistent code length (at code count='||to_char(code_count)||')';
goto exit_sub;
end if;
-- move to next code in string (as available)
csq_string := substr(csq_string,instr(csq_string,'|',1)+1);
end loop;
end if;
<<exit_sub>>
if err_msg is not null then
return err_msg;
else
return 'Y';
end if;
end valid_csq_string;
Created by
admin
Last modified 2006-03-23 08:45
Last modified 2006-03-23 08:45