Gravatar.com en Invantive Vision
In het CRM deel van Invantive Vision tref je contactgegevens van je relaties aan. De contactgegevens zie je via het web en Microsoft Outlook. Het is prettig om in aanvulling op de namen ook de foto’s te zien.
Talrijke relaties registreren hun gewenste contactgegevens op gravatar.com. Als ze dan bijvoorbeeld reageren op een blog en hun email adres achterlaten, dan kan de naam en foto geautomatiseerd toegevoegd worden.
In dit voorbeeld bouwen we een koppeling in Oracle PL/SQL tussen Gravatar.com en Invantive Vision. Er zijn verschillende koppelingen denkbaar:
- Haal de foto op bij Gravatar.com als je een nieuw email adres bij een relatie opvoert.
- Haal massaal alle bekende foto’s op.
In dit voorbeeld zullen we alle bekende foto’s van relaties ophalen en verwerken in Invantive Vision.
Tabel met gravatars
Allereerst maken we een tabel waarin de foto’s (zogenaamde “gravatars”) terechtkomen. Op zich is het niet absoluut noodzakelijk om een tabel te gebruiken; de gravatars kunnen ook vanuit Oracle PL/SQL direct weggeschreven worden in Invantive Vision. Maar mogelijkerwijs wil je de gegevens op meerdere manieren inlezen en dan voorkom je dat je gravatar.com onnodig zwaar belast.
De tabel gravatar_gravats maak je aan in het Invantive Query Tool, SQL*Plus of TOAD met:
--
-- 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
)
Opvragen gravatars vanuit Oracle PL/SQL
Vervolgens lopen we door alle email adressen heen die bekend zijn in Invantive Vision. Hiervoor is een view beschikbaar voor gebruik in Oracle SQL: bubs_vda_email_r. In bubs_vda_email_r staan voor personen (vda_ref_tabel is ‘gbr’) en organisaties (vda_ref_tabel is ‘lvr’) de bekende email adressen.
De gravatar kun je opvragen bij gravatar.com met de MD5 hash van het email adres in kleine letters. Zet je daar http://gravatar.com/avatarvoor, dan krijg je een gravatar terug. Gebruik je echter dezelfde hash met http://gravatar.com, gevolgd door ‘.xml’, dan krijg je het profiel terug als XML. Het profiel wordt in dit voorbeeld wel opgehaald, maar niet gebruikt omdat er weinig gravatars met een profiel zijn.
Voor het ophalen van de gravatar en het profiel gebruiken we uit de bubs_http_util package, respectievelijk, bubs_http_util.get_content_as_blob en bubs_http_util.get_content_as_clob. Aan deze functies geef je een URL mee en het resultaat krijg je terug als functie resultaat in Oracle PL/SQL. De resultaten worden opgeslagen in de tabel 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;
<<vda>>
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;
Toevoegen gravatars in Invantive Vision
De gravatars staan na afloop van stap 2 als blob in de tabel gravatar_gravatars. Bij elke gravatar is vastgelegd op welk bedrijfsobject (persoon of organisatie) hij betrekking heeft in bubs_ref_tabel, inclusief de technisch unieke sleutel in bubs_ref_sleutel. In de laatste stap kijken we welke personen en organisaties nog geen avatar hebben. Voor die maken we een JPEG bestand aan met daarin de gravatar en die koppelen we aan de persoon of organisatie.
--
-- 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
<<gvr>>
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;
Hopelijk heb ik met je dit voorbeeld kunnen laten zien hoe je vanuit Oracle PL/SQL gegevens verwerkt in Invantive Vision.