Gravatar.com and Invantive Vision
In the CRM part of Invantive Vision you will find the contact data of your relations. The contact data can be seen through the web and Microsoft Outlook. It is nice to see photo’s in addition to the names.
Numerous relations register their desired contact data on gravatar.com. If they react to a blog for example, and leave their e-mail address, then the name and photo can be automatically added.
In this example we build a link in Oracle PL/SQL between Gravatar.com and Invantive Vision. Different links are conceivable:
Retrieve the photo at Gravatar.com if you enter a new e-mail address at a relation.
Retrieve all known photo’s en masse.
In this example we will retrieve all known photo’s of relations and process them in Invantive Vision.
Table with gravatars
First we create a table where the photo’s (so-called “gravatars”) end up. It is not absolutely necessary to use a table; the gravatars can also be saved directly from Oracle PL/SQL in Invantive Vision. But possibly you might want to import the data in multiple ways and then you prevent gravatar.com from an unneeded heavy load.
The table gravatar_gravats can be created in the Invantive Query Tool, SQL*Plus or TOAD with:
--
-- Part 1.
--
-- Set up table to contain the gravatars.
-- No meaningful primary key available.
--
create table gravatar_gravatars
( bubs_ref_tabel varchar2(3)
, bubs_ref_sleutel number(15, 0)
, bubs_adres varchar2(240)
, bubs_naam_persoon varchar2(240)
, bubs_naam_organisatie varchar2(240)
, gravatar_hash varchar2(240)
, gravatar_image blob
, gravatar_profile xmltype
)
Retrieving gravatars from Oracle PL/SQL
Next we run through all e-mail addresses that are known in Invantive Vision. For this a view is available for use in Oracle SQL: bubs_vda_email_r. In bubs_vda_email_r there are for persons (vda_ref_tabel is ‘gbr’) and organizations (vda_ref_tabel is ‘lvr’) the known e-mail addresses.
The gravatar can be retrieved at gravatar.com with the MD5 hash of the e-mail address in lowercase. If you put ‘http://gravatar.com/avatar’ in front of that, then you will get a gravatar in return. However, if you use the same hash with ‘http://gravatar.com’, followed by ‘.xml’, then you will receive the profile as XML. The profile is still retrieved in this example, but not used because there are few gravatars with a profile.
For the retrieval of the gravatar and the profile we use from the bubs_http_util package, respectively, bubs_http_util.get_content_as_blob and bubs_http_util.get_content_as_clob. You sent an URL along with these functions and the results will be returned as function result in Oracle PL/SQL. The results are stored in the table gravatar_gravatars.
--
-- Part 2.
--
-- Run all email addresses registered in Invantive Vision through gravatar.com.
-- Memorize images and profiles if found.
--
declare
g_url_prefix varchar2(240) := 'http://www.gravatar.com';
g_max_cnt number := 1e10;
--
l_url_gravatar varchar2(240);
l_url_profile varchar2(240);
l_hash gravatar_gravatars.gravatar_hash%type;
l_image gravatar_gravatars.gravatar_image%type;
l_profile gravatar_gravatars.gravatar_profile%type;
l_found_image boolean;
l_cnt pls_integer;
l_cnt_hits pls_integer;
begin
delete gravatar_gravatars;
l_cnt := 0;
l_cnt_hits := 0;
<>>
for r_vda
in
( select vda.vda_ref_tabel
, vda.vda_ref_sleutel
, vda.vda_naam_persoon
, vda.vda_naam_organisatie
, vda.vda_adres
from bubs_vda_email_r vda
where rownum <= g_max_cnt
and gvr.bubs_ref_tabel = 'gbr'
union all
select gvr.gravatar_image
, gvr.bubs_ref_sleutel
, gvr.bubs_ref_tabel
, null gbr_voornaam
, null gbr_achternaam
, lvr.lvr_naam
from gravatar_gravatars gvr
join bubs_leveranciers_v lvr
oon lvr.lvr_id = gvr.bubs_ref_sleutel
and lvr.lvr_logo_url is null
and gvr.bubs_ref_tabel = 'lvr'
order
by vda.vda_naam_persoon
)
loop
--
-- Generate hash for gravatar.com as described on:
-- http://gravatar.com/site/implement/hash
--
l_hash :=lower
( dbms_crypto.hash
( utl_raw.cast_to_raw(r_vda.vda_adres)
, dbms_crypto.hash_md5
)
)
;
--
-- Try to find gravatar image.
-- See http://gravatar.com/site/implement/images/
-- Gravatar is asked to return a 404 HTTP result when not found.
--
l_url_gravatar := g_url_prefix || '/avatar/' || l_hash || '.jpg?d=404';
l_image := null;
l_found_image := false;
begin
l_image := bubs_http_util.get_content_as_blob(l_url_gravatar, false);
l_found_image := true;
exception
when others
then
if bubs_error_handler.get_last_are_code = 'dalpre1058'
and bubs_error_handler.get_last_sqlerrm like '%ORA-29268% 404 %'
then
--
-- HTTP error 404, no problem. There is no known gravatar.
--
null;
else
raise;
end if;
end;
--
-- Try to find gravatar profile.
-- See http://gravatar.com/site/implement/profiles/
-- Only try this when an image was found.
--
if l_found_image
then
l_url_profile := g_url_prefix || '/' || l_hash || '.xml';
l_profile := null;
l_profile := xmltype(bubs_http_util.get_content_as_clob(l_url_profile, false));
end if;
--
-- Memorize results.
--
if l_found_image
then
insert into gravatar_gravatars
( bubs_ref_tabel
, bubs_ref_sleutel
, bubs_adres
, bubs_naam_persoon
, bubs_naam_organisatie
, gravatar_hash
, gravatar_image
, gravatar_profile
)
values
( r_vda.vda_ref_tabel
, r_vda.vda_ref_sleutel
, r_vda.vda_adres
, r_vda.vda_naam_persoon
, r_vda.vda_naam_organisatie
, l_hash
, l_image
, l_profile
)
;
l_cnt_hits := l_cnt_hits + 1;
end if;
l_cnt := l_cnt + 1;
end loop vda;
--
commit;
--
dbms_output.put_line('Results of Gravatar lookup:');
dbms_output.put_line('#Lookups: ' || to_char(l_cnt));
dbms_output.put_line('#Hits: ' || to_char(l_cnt_hits));
end;
Adding gravatars in Invantive Vision
The gravatars are listed as blob in the table gravatar_gravatars at the end of step 2. For every gravatar it is determined to which company object (person or organization) it relates to in bubs_ref_tabel, including the technically unique key in bubs_ref_sleutel. In the final step we check which persons and organization do not have an avatar yet. For those we create a JPEG file with the gravatar in it and we link that to the person or organization.
--
-- Part 3.
--
-- We will create public accessible images and relate to them as a URL.
-- You can alternatively create Documents in the document management system,
-- but every access then requires authentication from the middle tier.
--
create or replace directory somebubs_local_img as '/opt/prd/bubs/somebubs/local/img'
declare
g_directory varchar2(30) := 'SOMEBUBS_LOCAL_IMG';
--
l_filename varchar2(240);
begin
<>>
for r_gvr
in
( select gvr.gravatar_image
, gvr.bubs_ref_sleutel
, gvr.bubs_ref_tabel
, gbr.gbr_voornaam
, gbr.gbr_achternaam
, gbr.gbr_naam
from gravatar_gravatars gvr
join bubs_gebruikers_v gbr
on gbr.gbr_id = gvr.bubs_ref_sleutel
and gbr.gbr_pasfoto_url is null
)
loop
l_filename := 'pasfoto-'
|| r_gvr.bubs_ref_tabel
|| '-'
|| replace
( lower
( case
when r_gvr.gbr_achternaam is not null
and r_gvr.gbr_voornaam is not null
then r_gvr.gbr_voornaam || ' ' || r_gvr.gbr_achternaam
else r_gvr.gbr_naam
end
)
, ' '
, '-')
|| '.jpg'
;
--
-- Write blob to the filename established.
--
bubs#documenten.write_blob_to_file(g_directory, l_filename, r_gvr.gravatar_image);
--
-- Update person or organization to have their passport photo / logo reflect
-- the new image from gravatar.com.
--
if r_gvr.bubs_ref_tabel = 'gbr'
then
update bubs_gebruikers_v gbr
set gbr_pasfoto_url = 'local/img/' || l_filename
where gbr.gbr_id = r_gvr.bubs_ref_sleutel
;
else
update bubs_leveranciers_v lvr
set lvr_logo_url = 'local/img/' || l_filename
where lvr.lvr_id = r_gvr.bubs_ref_sleutel
;
end if;
end loop gvr;
commit;
end;
Hopefully I have shown you with this example how you can process data from Oracle PL/SQL in Invantive Vision.