Una base de datos distribuida (BDD) es un conjunto
de múltiples bases de datos lógicamente relacionadas las cuales se
encuentran distribuidas
en diferentes espacios lógicos (pej. un servidor corriendo
2 máquinas virtuales) e interconectados
por una red de comunicaciones.
Dichas BDD tienen la capacidad de realizar procesamiento autónomo, esto
permite realizar operaciones locales o distribuidas.
Un sistema de Bases de Datos Distribuida (SBDD) es un sistema en el
cual múltiples sitios de bases de datos están ligados por un sistema de
comunicaciones de tal forma que, un usuario en
cualquier sitio puede acceder los datos en cualquier parte de la red
exactamente como si estos fueran accedidos de forma local.
Un sistema distribuido de bases de datos se almacenan en
varias computadoras. Los principales factores que distinguen un SBDD de
un sistema centralizado son los siguientes:
Hay múltiples computadores, llamados sitios o nodos.
Estos sitios deben de estar comunicados por medio de algún
tipo de red de comunicaciones para transmitir datos y
órdenes entre los sitios.
Desde cualquier BD se pueden recuperar datos de cualquier otra base de
datos PostgresSQL (Naturalmente, es necesario identificarse con perfil de un usuario con acceso a los mismos) usando dblink.
DBLink son funciones preparadas por los autores de postgres que añaden
la funcionalidad de acceso a datos de forma remota.
No se trata de conectarse
remotamente con un servidor de bases de datos, sino acceder desde una base da datos a
elementos de otra base de datos a través de una linea de
comunicaciones, combinando ambas
Instalar las funciones de dblink
Es un paso que hay que ejecutar SOLO UNA VEZ y que permite instalar las
funciones necesarias para proceder a la comunicación con bases de datos
remotas.
En la base de datos desde la que pretendemos conectarnos en remoto se ejecuta el
SQL que creará las funciones de dblink. El script está en:
Para las versiones 9.1 y posteriores, para generar las funciones de
dblink basta ejecutar en SQL
CREATE
EXTENSION dblink;
Ejecutar comandos de SQL
Comandos del tipo SELECT:
Los campos de las tablas del nodo remoto se “traen” al nodo local con el predicado FROM
añadiendo los parámetros de conexión proporcionados por dblink
SELECT lista_de_campos FROM
dblink('dbname=baseDatos port=puerto host=ordenadorRemoto user=usuario
password=contraseña','SQL') AS Alias(campo1 tipo1,campo2 tipo2,…) WHERE condiciones ORDER BY criterios_de_ordenacion;
Es necesario indicar en el Alias qué tipo de campos vienen desde el
ordenador remoto, pues se desconoce. Este extremo se define
en AS …
Comandos de mantenimiento de tablas (INSERT/DELETE/UPDATE):
En este caso se realiza mediante la ejecución de función dblink_exec(conexion,sentencia) Por ejemplo, si queremos insertar valores en la tabla gente (codigo int PK, nombre varchar, sueldo double): select dblink_exec('dbname=remota port=5432 host=yoquese.com user=postgres password=contrasena', 'INSERT INTO gente (codigo,nombre,sueldo) VALUES (33,\'Pepito\',1234.45));
Nota: \' sirve para incluir el carácter especial de comilla simple en la sentencia a ejecutar
Ejercicio 1.- Mostrar datos de otra base de datos residente
en el mismo equipo:
SELECT
p.nombre FROM
dblink('dbname=futbol user=postgres password=root','SELECT nombre FROM
equipos')AS
p(nombre varchar(100));
Ejercicio 2.- Mostrar los nombres de los empleados que
comienzan por E en una tabla llamada templa de la base de datos
empleado que está en el servidor del alumno 'pepito' (IP=192.168.32.44):
SELECT
p.nombre,p.apellido FROM
dblink('dbname=empleado
port=5432
host=192.168.32.44
user=postgres
password=root',
'SELECT nombre,apellido FROM
templa')
AS p(nombre
varchar(100),apellido varchar(100)) WHERE
p.nombre LIKE 'E%';
Ejercicio 3.- Listado de apellidos y departamentos donde
trabajan los empleados de la misma base de datos anterior. En este caso
las 2 tablas son remotas.
SELECT
a.b,d.f FROM
dblink(
'dbname=ejemplo1 port=5432 host=192.168.32.44 user=postgres
password=root' , 'SELECT
apellido,dept FROM templa') AS a(b varchar(100),c varchar(100)) , dblink(
'dbname=ejemplo1 port=5432 host=192.168.32.44 user=postgres
password=root', 'SELECT
numdep,nomdep FROM tdepta') AS d(e varchar(100),f varchar(100)) WHERE
a.c=d.e;
Ejercicio 4.- Mezcla de datos remotos y locales. Esta vez se
hará en varios pasos
b) Sentencia SQL (mezclo una tabla remota con una local)
SELECT
r.nombre,a.texto FROM
dblink('SELECT nuempl,nombre FROM
templa') AS r(nuempl varchar,nombre varchar), tabla_local
AS a WHERE
r.nuempl=a.codigo;
c) Cierre de la conexión remota
SELECT
dblink_disconnect();
Ejercicio 5.- Realización de una copia de una tabla remota (o
parte de ella)
SELECT
dblink_connect('dbname=empleado port=5432 host=192.168.32.44
user=postgres password=root'); SELECT
* into copia FROM dblink('SELECT nuempl,nombre FROM templa') AS
r(nuempl varchar,nombre varchar); SELECT
dblink_disconnect();
Problemas a tener en
cuenta:
Desaparece la clave primaria
Se debería alterar la tabla para
crearla
Es necesario conocer los tipos de los campos que
se copian
Práctica de acceso a bases de datos distribuidas:
Se va a realizar una simulación del valor de una cartera de valores.
Para realizarla será necesario que 4 alumnos creen en sus equipos (192.168.32.1 a 192.168.32.4) las
siguientes bases de datos, tablas, registros y campos, es decir, la información
se haya distribuida en 4 bases de datos remotas:
Equipo
Base
de datos
Tabla
Contenido
192.168.32.1
ibex35
cotizaciones
192.168.32.2
ftse
quote
192.168.32.3
dax30
zitat
192.168.32.4
downjones
quote
change
En cada ordenador del aula disponemos de una base de datos llamada
'mipasata'
con una tabla llamada 'cartera'
con nuestra cartera de
valores como la siguiente
Realizar una función llamada cuanto() a la que se
le pase un tic y un mercado y devuelva el valor correspondiente.
Ejemplo cuanto(‘BBVA’,’ibex35’) devolverá 5.555
Realizar una función llamada que() a la que se
pasa un tic y un mercado y devuelve la moneda en la que se
cotiza.
Ejemplo que(‘BBVA’,’ibex35’) devolverá ’E’
Realizar una función llamada cambio() a la que se
le pasa dos codigos de monedas y devuelve el cambio
correspondiente.
Ejemplo cambio(‘E’,‘D’) devolverá 1.3344
Con las funciones anteriores, realizar un listado del valor de
nuestra cartera de valores
Solución.: Todas las funciones reciben 2 parámetros llamados
a y b
cuanto(varchar,varchar)
que(varchar,varchar)
cambio(char,char)
declare
respuesta double precision;
basedatos varchar(100);
puerto varchar(4);
ordenador varchar(100);
usuario varchar(100);
contrasena varchar(100);
tabla varchar(100);
begin
if (b='ibex35') then
basedatos='ibex35';
puerto='5432';
ordenador='192.168.32.1';
usuario='postgres';
contrasena='root';
tabla='cotizaciones';
end if;
if (b='ftse') then
basedatos='ftse';
puerto='5432';
ordenador='192.168.32.2';
usuario='postgres';
contrasena='root';
tabla='quote';
end if;
if (b='dax30') then
basedatos='dax30';
puerto='5432';
ordenador='192.168.32.3';
usuario='postgres';
contrasena='root';
tabla='zitat';
end if;
if (b='downjones') then
basedatos='downjones';
puerto='5432';
ordenador='192.168.32.4';
usuario='postgres';
contrasena='root';
tabla='quote';
end if;
select into respuesta p.valor
from dblink('dbname=' || basedatos ||
' port=' || puerto ||
' host=' || ordenador ||
' user=' || usuario ||
' password=' || contrasena ,
'select * from ' || tabla)
AS p(tic varchar(10),valor double precision,moneda varchar)
where p.tic=a;
return respuesta;
end;
declare
respuesta character;
basedatos varchar(100);
puerto varchar(4);
ordenador varchar(100);
usuario varchar(100);
contrasena varchar(100);
tabla varchar(100);
begin
if (b='ibex35') then
basedatos='ibex35';
puerto='5432';
ordenador='192.168.32.1';
usuario='postgres';
contrasena='root';
tabla='cotizaciones';
end if;
if (b='ftse') then
basedatos='ftse';
puerto='5432';
ordenador='192.168.32.2';
usuario='postgres';
contrasena='root';
tabla='quote';
end if;
if (b='dax30') then
basedatos='dax30';
puerto='5432';
ordenador='192.168.32.3';
usuario='postgres';
contrasena='root';
tabla='zitat';
end if;
if (b='downjones') then
basedatos='downjones';
puerto='5432';
ordenador='192.168.32.4';
usuario='postgres';
contrasena='root';
tabla='quote';
end if;
select into respuesta p.moneda
from dblink(' dbname=' || basedatos ||
' port=' || puerto ||
' host=' || ordenador ||
' user=' || usuario ||
' password=' || contrasena ,
'select * from ' || tabla)
AS p(tic varchar(10),valor double precision,moneda varchar)
where p.tic=a;
return respuesta;
end;
declare
respuesta double precision;
begin
if(a=b) then
return 1;
else
select into respuesta p.r3
from dblink(' dbname=downjones
port=5432
host=192.168.32.4
user=postgres
password=root' ,
'select * from change')
AS p(r1 varchar,r2 varchar,r3 double precision)
where p.r1=a
and p.r2=b;
if(respuesta>0) then
return respuesta;
else
select into respuesta p.r3
from dblink(' dbname=downjones
port=5432
host=192.168.32.44
user=postgres
password=root' ,
'select * from change')
AS p(r1 varchar,r2 varchar,r3 double precision)
where p.r1=b
and p.r2=a;
return (1/respuesta);
end if;
end if;
end;
Para realizar un listado de la cartera de valores haremos la consulta:
select
cantidad, tic, mercado, cuanto(tic,mercado), que(tic,mercado), cambio(que(tic,mercado),'E')*cantidad*cuanto(tic,mercado) from
cartera;
que dará como como resultado:
Ejercicio de bases de datos distribuidas:
El ordenador remoto lamerced.no-ip.org dispone un servidor
postgreSQL con una base de datos llamada staples. Se ha creado un usuario llamado 'vendedor' con la contraseña 'venta' que puede acceder a las siguientes vistas
entradas_partido con 3 campos:
fila
(entero):
fila del estadio
asiento
(entero): asiento de la fila correspondiente
ocupado
(bolean):
si pone false indica que está disponible para la venta
precios_partido con 2 campos:
fila
(entero): fila del estadio
precio
(double precision): importe de una entrada
de esa fila
1.- Realizar una consulta sql que permita obtener las
entradas disponibles y el precio de las mismas
select * from dblink('dbname=staples port=5432
host=lamerced.no-ip.org user=vendedor password=venta','select * from
entradas_partido') AS e(fila integer,asiento integer,ocupado boolean), dblink('dbname=staples
port=5432 host=lamerced.no-ip.org user=vendedor password=venta','select * from
precios_partido') AS p(fila integer,importe double precision) where e.fila=p.fila and
e.ocupado=false;
2.- Crear en nuestro servidor local una tabla llamada
comisiones con los siguientes campos:
Tabla: comisiones
fila
(entero):fila de la que sacamos la entrada
comision
(double precision): comisión que nos llevamos por la venta
En esta tabla pondremos que la comisión por vender una
entrada de la fila 1 es el 20% (0.2) y para la fila 2 nos llevamos el 10% (0.1)
3.- Realizar una función llamada vendiendo en nuestro servidor con las siguientes características:
La función
se llamará vendiendo.
Se le pasa como parámetro una fila y un asiento.
Si está
disponible:
La debe vender (cambia el valor de ocupado a true)
Calcula el
importe a cobrar (su precio incrementado en el porcentaje correspondiente)
Devuelve el mensaje: ‘VENTA EFECTUADA. Fila X. Asiento Y. Importe: Z’
Si no está disponible:
Devuelve el mensaje ‘ENTRADA
NO DISPONIBLE’
declare r
boolean; pr
double precision; sentencia
character varying; nada
character varying; begin --Veamos si
la entrada está disponible select into
r e.ocupado from
dblink('dbname=staples port=5432 host=lamerced.no-ip.org user=vendedor
password=venta','select * from entradas_partido') AS e(fila integer,asiento
integer,ocupado boolean) where e.ocupado=false and e.fila=f and
e.asiento=a; --Dependiendo
de la instruccion anterior r puede ser false (disponible, true (ya vendida) o null (no existe esa entrada)
if(r=false)
then --Calculamos el importe de la
entrada select into
pr p.importe*(1+c.comision) from dblink('dbname=staples port=5432
host=lamerced.no-ip.org user=vendedor password=venta','select * from
precios_partido') AS p(fila integer,importe double precision), comisiones
c where
c.fila=f and p.fila=f; --Cambiamos ocupado de false a true sentencia='update entradas_partido
set ocupado=true where fila='||f||' and asiento='||a; select into nada
dblink_exec('dbname=staples port=5432 host=lamerced.no-ip.org user=vendedor
password=venta',sentencia); --Devolvemos el mensaje de la venta return ('VENTA EFECTUADA. Fila:
'||f||'. Asiento: '||a||'. Importe: '||pr); else --Esa entrada o no existe o no esta
disponible return('ENTRADA
NO DSIPONIBLE'); end if; end;
