Trabalhando com Large Objects no PostgreSQL

Quando trabalhamos com bancos de dados temos a nossa disposiç�o v�rios tipos de dados sendo utilizados como reposit�rios que s�o adequados para o armazenamento de valores de tipos �nicos. Isso quer dizer que os tipos de dados int armazenam valores num�ricos e os tipos de dados Varchar armazenam strings, por exemplo. Contudo, cada tipo de dados possui sua limitaç�o no que diz respeito ao tamanho e ao tipo para o devido armazenamento.

Devido a necessidade de guardar tipos de dados muito maiores, como v�deos e imagens, exigiu-se um tamanho muito maior de armazenamento, da� surgiram os tipos de dados considerados como Large Objects (LO) ou grandes objetos bin�rios, comumente conhecidos como arquivo de tipo BLOB.

Para lidarmos com os LO�s precisamos de mecanismos de armazenamento que possibilitem o acesso r�pido e eficiente dos dados em quest�o. No decorrer do artigo abordaremos com mais detalhes os Large Objects, onde os trataremos, inclusive sobre a biblioteca de interface cliente chamada libpq, que � utilizada para manipulaç�o de objetos com grandes dimens�es.

Large Objects

Um objeto composto por grandes dimens�es � armazenado de forma l�gica em uma coluna da tabela, mas armazenado fisicamente independente da coluna. Os large objects s�o armazenados em separado a partir da tabela porque normalmente armazenam uma grande quantidade de dados. De forma bem simples, podemos categorizar os objetos com grandes dimens�es baseando-se nos tipos de estruturas: simples ou complexas, semiestruturados ou sem estrutura.

O tipo de dados simples pode ser representado pelos tipos Byte e Text. O tipo byte, por exemplo, armazena qualquer tipo de dados bin�rios em um fluxo de bytes que tipicamente consiste de informaç�es digitalizadas, tais como planilhas, m�dulos de carregamento do programa, padr�es de voz digitalizada, etc.. J� o tipo Text armazena qualquer tipo de dados de texto.

O tipo de dados complexo suporta acesso aleat�rio aos dados, como os tipos BLOB e CLOB. O BLOB armazena qualquer tipo de dados bin�rios em pedaços de acesso aleat�rio, enquanto o CLOB, armazena qualquer tipo de dados de texto em blocos de acesso aleat�rio como XML, JSON ou mesmo HTML.

O tipo de dado semiestruturado � um dos mais utilizados pelos bancos e define esquemas irregulares ap�s a exist�ncia dos dados, como no XML.

No momento em que estamos realizando o armazenamento de imagens, �udio ou v�deos, estes n�o podem ser armazenados da mesma forma que um Varchar ou um int, pois eles n�o podem ser divididos em pequenas estruturas l�gicas. Sendo assim, eles s�o enquadrados nos tipos de dados n�o-estruturados, os quais precisam de um mecanismo diferente para que possamos lidar com eles. Com base nisso, o PostgreSQL nos auxilia com este problema apresentando um recurso de armazenamento de Large Objects de forma consider�vel, no que diz respeito a facilidade no momento de executar as consultas ou inserç�o dos dados, utilizando refer�ncias a uma tabela padr�o do PostgreSQL.

Large Objects no PostgreSQL

Curiosamente, o PostgreSQL oferece duas formas de armazenar grandes objetos com relaç�o a cada requisito que tenhamos que atender: o BYTEA e o armazenamento de Large Objects.

A implementaç�o BYTEA � utilizada para armazenamento de objetos com grandes dimens�es. � bastante semelhante ao VARCHAR, mas com caracter�sticas bem distintas, como o armazenamento de dados brutos ou n�o-estruturados, al�m de permitir o armazenamento de valores nulos ou at� 1 GB de dados.

J� o Large Objects permite o armazenamento de at� 4 TB, al�m de oferece funç�es que ajudam melhor e mais facilmente na manipulaç�o de dados externos. Por n�o ser um tipo de dado, mas uma entrada, os LOs s�o armazenados na tabela de sistema chamada pg_largeobject e s�o quebrados em pedaços de tamanho padr�o e armazenados como linhas na base de dados. Estes pedaços s�o indexados em B-tree, o que garante pesquisas mais r�pidas no momento das operaç�es de leitura e escrita no banco, j� que s�o �rvores de busca desenvolvidas para agrupar informaç�es de tem algum nexo entre elas.

Como os Large Objects n�o s�o armazenados nas tabelas do usu�rio, � criado um valor OID (Object Identifier) para ser armazenado, ou seja, no momento em que precisarmos acessar esses dados devemos referenciar o OID que aponta para os registros na tabela pg_largeobject.

Operando com os Large Objects na pr�tica

A partir deste momento passaremos a trabalhar com algumas funç�es espec�ficas de manipulaç�o dos Large Objects. Para que possamos ver melhor as funç�es de servidor que temos dispon�veis para as operaç�es faremos uma consulta simples, como mostra a Listagem 1.

Listagem 1. Retornando as funç�es para trabalhar com Large Objects.


    SELECT n.nspname as "Schema", p.proname as "funç�o",
       pg_catalog.pg_get_function_result(p.oid) as "tipo de dados",   
       pg_catalog.pg_get_function_arguments(p.oid) as "tipo de argumento"
       FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
       WHERE p.proname ~ '^(lo_.*)$'AND pg_catalog.pg_function_is_visible(p.oid)
     ORDER BY 1, 2, 4;

Na Figura 1 temos a exibiç�o do Schema com as funç�es armazenadas, os tipos de dados e os poss�veis argumentos utilizados.

Agora criaremos uma tabela para armazenar imagens digitais chamada largeObjects_Devmedia, usando o c�digo que est� na Listagem 2.

Listagem 2. Criaç�o da tabela largeObjects_Devmedia.


    CREATE TABLE largeObjects_Devmedia
     (
       cod_imagem INTEGER,
       nome_imagem VARCHAR(30),
       local_imagem oid,
       CONSTRAINT pk_cod_imagem PRIMARY KEY(cod_imagem)
     );

Como ainda n�o temos nenhuma imagem salva consultaremos a tabela do sistema pg_largeobject para que possamos ver o seu estado atual. No momento que inserirmos um registro teremos uma chamada a funç�o que afetar� tamb�m a tabela de sistema. Para verificarmos esta nesse momento utilizaremos a seguinte instruç�o de consulta:

SELECT * FROM pg_largeobject;

O resultado da consulta pode ser visto na Figura 2, onde constatamos que a tabela se encontra vazia.

Figura 2. Consulta na tabela pg_largeobject.

Agora realizaremos a inserç�o de um registro na tabela que criamos, como mostra a seguinte instruç�o:


    INSERT INTO public.largeobjects_devmedia(cod_imagem, nome_imagem, local_imagem)
     VALUES (1, 'naruto_shippuden', lo_import('D:/imagens/naruto_shippuden.jpg'));

Caso os dados n�o sejam inseridos na tabela, como mostra a Figura 3, � necess�rio atribuir as devidas permiss�es no banco de dados, usando a seguinte instruç�o:

GRANT SELECT, INSERT, UPDATE ON pg_largeobject TO PUBLIC;

Figura 3. Inserç�o de registro.

Com as devidas permiss�es poderemos realizar a operaç�o de inserç�o novamente. Em seguida verificaremos se o registro foi inserido com sucesso usando a instruç�o SELECT a seguir e o resultado na Figura 4:

select * from public.largeobjects_devmedia;

Figura 4. Inserç�o dos dados na tabela.

Percebam que no momento de inserç�o dos dados na tabela utilizamos a funç�o espec�fica lo_import(), que � utilizada para carregar imagens para a tabela de sistema pg_largeobjects. Ao observarmos o resultado trazido pela Figura 4 vemos que o local_imagem n�o foi o caminho que especificamos, mas sim, o valor do OID de localizaç�o utilizado pela tabela do sistema. Verifiquemos ent�o o registro na tabela pg_largeobjects, como mostram as Figuras 5 e 6, com base nas seguintes instruç�es:


    SELECT loid FROM pg_largeobject;
    SELECT oid FROM pg_largeobject_metadata;

Figura 5. Resultado da tabela de sistema.

Figura 6. Resultado dos metadados da tabela.

Na Figura 5 percebemos que o OID se repete, pois este � o endereçamento das partes de registro da imagem que salvamos. De forma similar a importaç�o da imagem para a base de dados, podemos tamb�m export�-la para a nossa m�quina utilizando a funç�o lo_export() com as informaç�es de OID e o local no qual ser� armazenada a imagem como par�metros , de acordo com a seguinte instruç�o:

SELECT lo_export(32784, 'D:/imagens/naruto_shippuden.jpg');

Temos tamb�m a funç�o lo_unlink(), que � utilizada para realizar a remoç�o do objeto, como podemos observar na instruç�o a seguir:

SELECT lo_unlink(32784);

Como podemos observar nas instruç�es apresentadas, o uso de OID � utilizado como uma refer�ncia para exportaç�o e desvinculaç�o dos Large Objects. No momento que desvinculamos a imagem, perdemos apenas a refer�ncia a tabela pg_largeobjects, mas o registro permanece na nossa tabela.

Se executarmos a instruç�o de SELECT utilizando a funç�o lo_unlink() obteremos uma mensagem de que o objeto n�o existe, como podemos ver na Figura 7.

Figura 7. Mensagem de erro.

Manipulando os objetos com a biblioteca libpq

Para facilitar o nosso trabalho, o PostgreSQL nos fornece diversas maneiras de armazenar e acessar os Large Objects. Um destes recursos � a biblioteca de interface cliente chamada libpq, que nos ajuda a acessar estes objetos com uma maior facilidade e efici�ncia. Ela realiza a comunicaç�o com o servidor do PostgreSQL atrav�s de algumas funç�es que veremos mais � frente.

Para os exemplos desta etapa foi utilizada a linguagem C para a sua criaç�o. O intuito principal aqui � apresentar as funcionalidades de forma mais pr�tica, mas n�o fugindo ao foco do artigo que � o banco de dados PostgreSQL.

Primeiramente conectamos o banco de dados PostgreSQL e em seguida importamos o arquivo recebe_img.jpg com o aux�lio da funç�o lo_import, e por fim, utilizamos a funç�o lo_export() chamado de exporta_img.jpg usando o OID retornado, como mostram as instruç�es presentes na Listagem 3.

Listagem 3. Exemplo de criaç�o, importaç�o e exportaç�o de objetos.


    #include "libpq-fe.h"
    #include "libpq/libpq-fs.h"
    #define ARQUIVO_EXPORTA "exporta_img.jpg"
    #define ARQUIVO_IMPORTAR "recebe_img.jpg"
    int main(int argc, char **argv)
    {
      PGconn *conecta;
      PGresult *resultado;
      int lo_oid;
      /* Conectando ao banco de dados */
      conecta = PQsetdb(NULL, NULL, NULL, NULL, "testesDevmedia");
      /* Momento da importaç�o da imagem para o banco de dados */
      lo_oid = lo_import(conecta, ARQUIVO_IMPORTAR);
      if (lo_export(conecta, lo_oid, ARQUIVO_EXPORTA) < 0)
      {
        fprintf(stderr, "%s\n", PQerrorMessage(conecta));
        PQfinish(conecta);
        return -1;
      }
      return lo_oid;
    }

A funç�o lo_unlink() pode ser utilizada para remover do banco de dados os registros dos Large Objects usando a seguinte sintaxe:

int lo_unlink(PGconn *conecta, Oid large_object_oid);

O argumento large_object_oid especifica o OID do objeto que deve ser removido.

Agora observe o exemplo da Listagem 4.

Listagem 4. Utilizando as funç�es lo_write, lo_read.

1. /*Definindo as constantes e bibliotecas*/ 2. #define EXP_ARQUIVO "exporta_img.jpg" 3. #define FILE_TO_IMPORT "recebe_img.jpg" 4. int main(int argc, char **argv) 5. { 6. PGconn *conecta; 7. PGresult *resultado; 8. int lo_oid; 9. int loTeste, lo_loTeste; 10. int n; 11. char buffer[1024]; 12. int ler_img; 13. /* conex�o com o banco de dados*/ 15. conecta = PQsetdb(NULL, NULL, NULL, NULL, "testeDevmedia"); 16. 17. /* Executando o comando BEGIN */ 18. resultado = PQexec(conecta, "BEGIN"); 19. PQclear(resultado); 20. loTeste = open(FILE_TO_IMPORT, O_RDONLY, 0666); 21. lo_oid = lo_create(conecta, INV_READ | INV_WRITE); 22. lo_loTeste = lo_open(conecta, lo_oid, INV_WRITE); 23. n = read(loTeste, buffer, 1024); 24. if (n <= 0) 25. break; 26. ler_img = lo_write(conecta, lo_loTeste, buffer, n); 27. lo_close(conecta, lo_loTeste); 28. loTeste = open(EXP_ARQUIVO, O_CREATE | O_WRONLY, 0666); 29. lo_loTeste = lo_open(conecta, lo_oid, INV_READ); 30. loTeste = open(EXP_ARQUIVO, O_CREATE | O_WRONLY, 0666); 31. n = lo_read(conecta, lo_loTeste, buffer, 1024); 32. ler_img = write(loTeste, buffer, n); 33. lo_close(conecta, lo_loTeste);

A funç�o lo_create() da linha 21 realiza a conex�o do projeto ao banco de dados, usando como argumento primeiro a conex�o seguida de uma constante presente na linguagem C. Com isso obtemos como resultado o OID.

Outra funç�o ainda n�o apresentada � a lo_open(), que � utilizada para abrir um objeto com grandes dimens�es para leitura ou escrita. A sintaxe de sua utilizaç�o � a seguinte:

int lo_open(PGconn *conecta, Oid large_object_oid, int testeDev);

Esta funç�o retorna um descritor de objetos que pode ser utilizado nas funç�es como lo_read, lo_write, dentre outras funç�es, assim como vimos nas linhas 22 e 29.

J� a funç�o lo_write() da linha 26 escreve o tamanho, representado pelo �tam�, em bytes do buffer, que deve ter, pelo menos, o mesmo tamanho que o �tam� para o descritor loTeste do objeto. O n�mero de bytes que foi escrito � ent�o retornado e � sempre igual a �tam�.

J� a funç�o lo_read() tem a sua sintaxe apresentada a seguir:

int lo_read(PGconn *conecta, int loTeste, char *buffer, size_t tam);

Ela opera de forma diferente a funç�o lo_write, mesmo ambas tento a sintaxe parecida. Neste momento, ela realiza a leitura do tamanho (�tam�) em bytes do objeto representado pelo descritor loTeste no buffer de tamanho �tam�. O argumento loTeste deve ser retornado pela funç�o lo_open(). O n�mero de bytes realmente lidos � ent�o devolvido e ser� um pouco menor do que o tamanho final do objeto, como vimos na linha 31.

Por fim, tivemos a apresentaç�o da funç�o lo_close(), a qual tem por finalidade fechar o descritor do objeto, assim como vimos nas linhas 27 e 33, onde o loTeste � um descritor de objeto retornado pela funç�o lo_open e, em caso de sucesso, o lo_close retornar� 0.

Funç�es da biblioteca libpq

Podemos ter em nossas aplicaç�es v�rias conex�es com o banco de dados abertas ao mesmo tempo, inclusive acessando mais de um banco tamb�m.

Como vimos na linha 6 da �ltima listagem, a conex�o � representada pelo objeto PGconn, que pode ser obtido a partir de uma das m�ltiplas funç�es que a biblioteca nos fornece.

Elas sempre v�o retornar um objeto n�o nulo, a menos que haja pouca mem�ria para alocar o PGconn.

A seguir veremos os tipos mais usados

Funç�o PQconnectdb

Esta funç�o � a mais b�sica para realizarmos a conex�o ao PostgreSQL: ela precisa apenas da string de conex�o como par�metro, como podemos ver na sintaxe a seguir:

PGconn *PQconnectdb (const char *conninfo);

A string conninfo � uma cadeia delimitada por espaço que cont�m os pares de valores de palavra-chave, sendo os mais importantes apresentados a seguir

o hostaddr, que � o endereço ip do servidor PostgreSQL;
o host � nome do servidor;
port, que � a porta de entrada;
dbname, que � o nome do banco de dados;
user, que � o usu�rio configurado;
password, que � a senha configurada;
e a connect_timeout, que � o per�odo de conex�o.

Funç�o PQconnectdbParams

A variaç�o da funç�o apresentada anteriormente � a PQconnectdbParams, que recebe uma matriz bidimensional de chaves e valores ao inv�s de um �nico valor-chave de cadeia dupla. Para ela, a sintaxe utilizada � a seguinte:

PGconn *PQconnectdbParams(const char **keywords, const char **values, int expand_dbname);

Funç�o PQsetdbLogin

Outra forma de conex�o � utilizando a funç�o PQsetdbLogin, que tem um n�mero limitado de par�metros, como mostra a sintaxe a seguir:

PGconn *PQsetdbLogin (const char *pghost, const char *pgport, const char *pgoptions, const char *pgtty, const char *dbName, const char *login, const char *pwd);

Funç�o PQsetdb

Essa funç�o chama a PQsetdbLogin com o nome de usu�rio e senha padr�o, como mostra a sintaxe a seguir:

PGconn *PQsetdb(char *pghost, char *pgport, char *pgoptions, char *pgtty, char *dbName);

Para melhor entender a forma que realizamos a conex�o ao banco de dados PostgreSQL, vejamos um exemplo constru�do em C, de acordo com a Listagem 5.

Listagem 5. Criando uma classe de conex�o em C.

#include<stdio.h> #include<sys/types.h> #include<stdlib.h> #include<limits.h> #include "libpq-fe.h" #include "libpq/libpq-fs.h" charconninfo[] = "hostaddr = '127.0.0.1' port = '5432' dbname ='testeDevmedia'"; char *keyword[] = {"hostaddr", "port", "dbname"}; char *value[] = {"127.0.0.1", "5432", "testeDevmedia"}; int main(int argc, char **argv) { /* conex�o */ PGconn *conecta; /* Forma 1: usando o PQsetdb para criar a conex�o ao banco*/ conecta = PQsetdb(NULL, NULL, NULL, NULL, "testeDevmedia"); if (PQstatus(conecta) == CONNECTION_BAD) { fprintf(stderr, "A conex�o falhou!"); fprintf(stderr, "%s", PQerrorMessage(conecta)); return -1; } fprintf(stdout, "A conex�o foi realizada com sucesso!!\n"); PQfinish(conecta); /* Forma 2: Usando o PQconnectdb */ conecta = PQconnectdb(conninfo); if (PQstatus(conecta) == CONNECTION_BAD) { fprintf(stderr, "A conex�o falhou!\n"); fprintf(stderr, "%s", PQerrorMessage(conecta)); return -1; } fprintf(stdout, "conex�o realizada com sucesso!!\n"); PQfinish(conecta); /* Forma 3: Usando o PQconnectdbParams */ conecta = PQconnectdbParams((const char **)keyword, (const char**)value, 1); if (PQstatus(conecta) == CONNECTION_BAD) { fprintf(stderr, "conex�o falhou!!\n"); fprintf(stderr, "%s", PQerrorMessage(conecta)); return -1; } fprintf(stdout, "Conex�o realizada com sucesso!!\n"); PQfinish(conecta); /* Forma 4: Usando o PQsetdbLogin */ conecta = PQsetdbLogin("127.0.0.1", "5432", NULL, NULL, "testeDevmedia", "postgres", "123456"); if (PQstatus(conecta) == CONNECTION_BAD) { fprintf(stderr, "conex�o ao banco de dados falhou!\n"); fprintf(stderr, "%s", PQerrorMessage(conecta)); return -1; } fprintf(stdout, "Conex�o realizada com sucesso!\n"); PQfinish(conecta); return 0; }

Repare que no c�digo utilizamos a funç�o PQfinish, que � utilizada para fecharmos a conex�o com o servidor utilizado o ponteiro PGconn da conex�o com o objeto retornado pelas funç�es PQconnectdb, PQsetdbLogin ou PQsetdb. A sintaxe do PQfinish � a seguinte:

Void PQfinish(PGconn *conn);

Com isso finalizamos este artigo, onde vimos que quando temos a necessidade de armazenar textos que ultrapassam o limite permitido para uma string ou quando temos a necessidade de armazenar objetos bin�rios em nossa base de dados, torna-se necess�ria a utilizaç�o do Large Objects, a qual pode ser realizada com o aux�lio de bibliotecas espec�ficas do PostgreSQL, como � o caso da libpq.

Esperamos que tenham gostado!

Links

Documentaç�o do Large Objects
http://www.postgresql.org/docs/9.4/static/largeobjects.html

Documentaç�o das interfaces LOs
http://www.postgresql.org/docs/9.4/static/lo-interfaces.html