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