oracle aide

December 23, 2008

How to eyeball GUIDs (or any long string of hex numbers)

Filed under: Advanced Queueing — Tags: , , , — oracleaide @ 3:27 pm

GUIDs are not human friendly.
Anyone who dealt with Advanced Queueing and had to look for messages in queue tables knows that.

I have seen many database applications naively relying on uniqueness of GUIDs (or UUIDs).
Pity those poor souls who maintain and troubleshoot those apps.

Why? Just try to read a GUID over the phone.
Or try to find differences between the following two strings:



select sys_guid() from dual

5EB8C2EBEEF2C00EE043C0A868F5C00E

select sys_guid() from dual

5EB8C2EBEEF3C00EE043C0A868F5C00E

(Aha! I see 2 and 3 in the 12th position!)

There is hope. For new designs we could use Tags:

“…The tag algorithm lets people mint — create — identifiers that no one else using the same algorithm could ever mint. It is simple enough to do in your head, and the resulting identifiers can be easy to read, write, and remember. The identifiers conform to the URI (URL) Syntax…”

What should we do with the old, 20th century applications?

Of course, we can compare two GUIDs:


select
case
when '5EB8C2EBEEF2C00EE043C0A868F5C00E' = '5EB8C2EBEEF3C00EE043C0A868F5C00E' then
'true'
else
'false'
end
as are_guids_the_same
from dual

Or we could verbalize them:

select verbalize_guid('5EB8C2EBEEF2C00EE043C0A868F5C00E') from dual
union all
select verbalize_guid('5EB8C2EBEEF3C00EE043C0A868F5C00E') from dual

alliterations-cocooning-Nassau-and-Jane-Pusey-Kronecker-Gilligan-Earp-collided-Kronecker-Jerusalem-Anselmo-Aztlan-Kronecker-Gilligan
alliterations-cocooning-Nassau-and-Jane-collocating-Kronecker-Gilligan-Earp-collided-Kronecker-Jerusalem-Anselmo-Aztlan-Kronecker-Gilligan

Isn’t this easier for our poor tired eyes? The difference is much easier to see where Pusey != collocating.

I am using the same principle as the NATO phonetic alphabet.

A sample verbalization function follows.
For the simplicity sake I hardcoded codewords.
Feel free to store them in a table. Or pick better words.


CREATE OR REPLACE function verbalize_guid(guid in varchar2)
return varchar2 as
type t_words is table of varchar2(30) index by varchar2(2);
words t_words;
i pls_integer;
len pls_integer;
n pls_integer;
rv varchar2(4000) := null;
w guid_verb_words.word%type;
c char(2);
begin
-- init
words('00') := 'Bella';
words('01') := 'carrousel';
words('02') := 'Polynesia';
words('03') := 'codifications';
words('04') := 'Kathryn';
words('05') := 'brochure';
words('06') := 'Moldavia';
words('07') := 'changeling';
words('08') := 'Rollerblade';
words('09') := 'comported';
words('0A') := 'Utopias';
words('0B') := 'Bertha';
words('0C') := 'Coleridge';
words('0D') := 'arisen';
words('0E') := 'Gilligan';
words('0F') := 'bereavements';
words('10') := 'Kristen';
words('11') := 'bullshit';
words('12') := 'Naples';
words('13') := 'chickadees';
words('14') := 'Sandinista';
words('15') := 'confiding';
words('16') := 'Warren';
words('17') := 'Branch';
words('18') := 'amaranth';
words('19') := 'asserting';
words('1A') := 'Guy';
words('1B') := 'biology';
words('1C') := 'Leoncavallo';
words('1D') := 'bypass';
words('1E') := 'Nukualofa';
words('1F') := 'churchman';
words('20') := 'Seychelles';
words('21') := 'Abernathy';
words('22') := 'Xenakis';
words('23') := 'Caldwell';
words('24') := 'angers';
words('25') := 'Ethel';
words('26') := 'Hermitage';
words('27') := 'blindside';
words('28') := 'Luciano';
words('29') := 'camshaft';
words('2A') := 'Palestrina';
words('2B') := 'cleanliness';
words('2C') := 'Sophoclean';
words('2D') := 'Almohad';
words('2E') := 'ability';
words('2F') := 'Celts';
words('30') := 'apathetic';
words('31') := 'Fourier';
words('32') := 'beagling';
words('33') := 'Kaifeng';
words('34') := 'Mani';
words('35') := 'careen';
words('36') := 'Pickwick';
words('37') := 'coarsen';
words('38') := 'Swahili';
words('39') := 'Arabian';
words('3A') := 'accumulation';
words('3B') := 'Claudio';
words('3C') := 'archangel';
words('3D') := 'Georges';
words('3E') := 'belly';
words('3F') := 'Knesset';
words('40') := 'buggy';
words('41') := 'catcalls';
words('42') := 'Purana';
words('43') := 'collided';
words('44') := 'Theron';
words('45') := 'Ayyubid';
words('46') := 'admiralty';
words('47') := 'Craft';
words('48') := 'askance';
words('49') := 'Grinch';
words('4A') := 'bikinis';
words('4B') := 'Leander';
words('4C') := 'busybody';
words('4D') := 'Nobel';
words('4E') := 'Rickover';
words('4F') := 'companions';
words('50') := 'Turing';
words('51') := 'Belarus';
words('52') := 'agency';
words('53') := 'Delano';
words('54') := 'attestation';
words('55') := 'Hegelian';
words('56') := 'blasphemously';
words('57') := 'Longstreet';
words('58') := 'caloric';
words('59') := 'Oscar';
words('5A') := 'clappering';
words('5B') := 'concubine';
words('5C') := 'Vincent';
words('5D') := 'Bolshevist';
words('5E') := 'alliterations';
words('5F') := 'Dristan';
words('60') := 'axis';
words('61') := 'Horthy';
words('62') := 'boardinghouses';
words('63') := 'Majorca';
words('64') := 'caption';
words('65') := 'Persepolis';
words('66') := 'clove';
words('67') := 'Sue';
words('68') := 'Anselmo';
words('69') := 'Buffy';
words('6A') := 'amuses';
words('6B') := 'Englishwoman';
words('6C') := 'balsas';
words('6D') := 'Issachar';
words('6E') := 'boredom';
words('6F') := 'Mathis';
words('70') := 'caste';
words('71') := 'Priestley';
words('72') := 'coincides';
words('73') := 'Teotihuacan';
words('74') := 'Aubrey';
words('75') := 'adenoid';
words('76') := 'anthologist';
words('77') := 'Fermat';
words('78') := 'bassoons';
words('79') := 'Jolene';
words('7A') := 'breakfasted';
words('7B') := 'Mexico';
words('7C') := 'censors';
words('7D') := 'Reinhardt';
words('7E') := 'commissariat';
words('7F') := 'Tricia';
words('80') := 'Bauer';
words('81') := 'affirmatives';
words('82') := 'Darwin';
words('83') := 'Galibi';
words('84') := 'befits';
words('85') := 'Kerri';
words('86') := 'bruised';
words('87') := 'Morgan';
words('88') := 'charter';
words('89') := 'Roumania';
words('8A') := 'conceive';
words('8B') := 'Venn';
words('8C') := 'Blackstone';
words('8D') := 'algebra';
words('8E') := 'Dona';
words('8F') := 'avians';
words('90') := 'betted';
words('91') := 'Lamb';
words('92') := 'bureaucrat';
words('93') := 'Neptune';
words('94') := 'chintz';
words('95') := 'Scaramouch';
words('96') := 'congressman';
words('97') := 'Wheatstone';
words('98') := 'Britten';
words('99') := 'amnesia';
words('9A') := 'Elizabethan';
words('9B') := 'baking';
words('9C') := 'Ionian';
words('9D') := 'Lilliput';
words('9E') := 'caesura';
words('9F') := 'Oklahomans';
words('A0') := 'circumflex';
words('A1') := 'Shijiazhuang';
words('A2') := 'Afghanistan';
words('A3') := 'Youngstown';
words('A4') := 'Capek';
words('A5') := 'annual';
words('A6') := 'Fagin';
words('A7') := 'barren';
words('A8') := 'Jerusalem';
words('A9') := 'brandied';
words('AA') := 'canonisation';
words('AB') := 'Patrica';
words('AC') := 'clinch';
words('AD') := 'Stalinist';
words('AE') := 'Amman';
words('AF') := 'absent';
words('B0') := 'Chattanooga';
words('B1') := 'appellant';
words('B2') := 'Frisbee';
words('B3') := 'bedding';
words('B4') := 'Kauai';
words('B5') := 'broker';
words('B6') := 'Mombasa';
words('B7') := 'Poitier';
words('B8') := 'cocooning';
words('B9') := 'Talley';
words('BA') := 'Armstrong';
words('BB') := 'acquits';
words('BC') := 'Cologne';
words('BD') := 'armada';
words('BE') := 'Giorgione';
words('BF') := 'berserk';
words('C0') := 'Kronecker';
words('C1') := 'bumblebees';
words('C2') := 'Nassau';
words('C3') := 'chid';
words('C4') := 'combatted';
words('C5') := 'Titania';
words('C6') := 'Baltimore';
words('C7') := 'adventitious';
words('C8') := 'Curt';
words('C9') := 'assessors';
words('CA') := 'G?del';
words('CB') := 'biotechnology';
words('CC') := 'Lepus';
words('CD') := 'bywords';
words('CE') := 'Nyerere';
words('CF') := 'chute';
words('D0') := 'Shanghai';
words('D1') := 'Uranus';
words('D2') := 'Bern';
words('D3') := 'ailing';
words('D4') := 'DiCaprio';
words('D5') := 'aural';
words('D6') := 'Hersey';
words('D7') := 'blips';
words('D8') := 'Lucknow';
words('D9') := 'cancan';
words('DA') := 'Pam';
words('DB') := 'cleared';
words('DC') := 'Southeast';
words('DD') := 'Alphonso';
words('DE') := 'Bradshaw';
words('DF') := 'alto';
words('E0') := 'Earp';
words('E1') := 'backlogged';
words('E2') := 'Hyundai';
words('E3') := 'bola';
words('E4') := 'Mansfield';
words('E5') := 'carelessness';
words('E6') := 'Pilgrim';
words('E7') := 'coasts';
words('E8') := 'Swedenborg';
words('E9') := 'Arapaho';
words('EA') := 'accused';
words('EB') := 'and';
words('EC') := 'Esq';
words('ED') := 'banqueting';
words('EE') := 'Jane';
words('EF') := 'bourgeoisie';
words('F0') := 'McDonald';
words('F1') := 'catechise';
words('F2') := 'Pusey';
words('F3') := 'collocating';
words('F4') := 'Thompson';
words('F5') := 'Aztlan';
words('F6') := 'admittance';
words('F7') := 'Creator';
words('F8') := 'Foley';
words('F9') := 'bayou';
words('FA') := 'Jungfrau';
words('FB') := 'bridgeheads';
words('FC') := 'Mindoro';
words('FD') := 'cervix';
words('FE') := 'Rigel';
words('FF') := 'Tuscarora';
-- split the guid in two char tokens
len := length(guid);
-- each hex number takes 2 positions
if mod(len, 2) = 0 then
n := len / 2;
for i in 0..n - 1 loop
c := substr(guid, i * 2 + 1, 2);
w := words(c);
rv := rv || '-' || w;
end loop;
-- remove the first dash
rv := substr(rv, 2, length(rv) - 1);
end if;
return rv;
end;
/

It turned out that generating a list of random words is a separate, rather exciting story.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: