PostgreSQL interaction with external services to store images

Good time of day. When working with a database for a website sometimes a situation arises when you have to choose how and where to store the image. Among the possible options usually include:
the

images are entirely in the database
images are in the file system, stored in the database file name
images are stored in external specialized service

Though PostgreSQL and provides the possibility of storing in a DB file (in bytea the fields, or through large objects), this is the least best option in terms of speed and memory consumption. Another common practice is to store images as files on a disk that site is the path to the image. Of the benefits — the possibility of caching or use a specialized file system. And the third option for images a separate service, which can be caching, masturbirovala on the fly, change the format. Try to interact PostgreSQL with this service.

Implementation of

Outline a little picture. We have a http-service, type this, for images that support the following commands:
the

upload the image — sending a POST request with the form, the JSON response comes with some information about the image including the generated ID
receipt image — sending GET request with image ID my.service.local/1001
removal of an image — sending a DELETE-request with ID my.service.local/1001

In the database to store the IDs of images in this case, pages of the website can be vetravati tags:
the

<img src="http://my.service.local/1001"/>

User upload the image (as well as saving and deleting) should look like a function call upload_image (filename), which returns the identifier of the image in the service zapisany then to the table. As it directly from PostgreSQL it dostupatsya to http requests necessary to implement the required functionality on stored functions in C, and is where Razgulyai. For simplicity, we'll manage libraries curl and jansson (the latter is for working with JSON). Can start.

Define in the header file barberry_impl.h our function prototypes:
the

// get last error
char* barberry_error();

// upload file to BarBerry''s service and return ID
int barberry_upload_file(const char *host, const char *filename);

// download file from BarBerry''s service by ID
int barberry_download_file(const char *host, int id, const char *filename);

// delete file from BarBerry''s service by ID
int barberry_delete_file(const char *host, int id);

In the source file barberry_impl.c put the following global variables:
the

char last_error[1024];
FILE *file = NULL;
int result = 0;

Variable last_error will store the last error, the file is a pointer to the file that is generated when data is received from the service, and in result will persist the result of the function service.

The implementation of the function barberry_error is trivial, but the return of the last_error. Let us examine in detail the function barberry_upload_file.

Before you start working with the library curl, you must initialize the environment for it (with curl_gobal_init) and create session (with the command curl_easy_init, masraweya a pointer to the handle of the session). Next, create a submit form (via curl_formadd) and fill in the following options:
the

CURLOPT_URL host that we work with
CURLOPT_HTTPPOST — form sent by the POST method
CURLOPT_WRITEFUNCTION CALLBACK function for response from host

Implementation of barberry_upload_file:
the

int barberry_upload_file(const char *host, const char *filename)
{
result = -1;

curl_global_init(CURL_GLOBAL_ALL);

CURL *curl = curl_easy_init();

if (curl)
{
curl_easy_setopt(curl, CURLOPT_URL, host);

struct curl_httppost *httppost = NULL;
struct curl_httppost *last_ptr = NULL;

curl_formadd(&httppost, &last_ptr, CURLFORM_COPYNAME, "sendfile", CURLFORM_FILE, filename, CURLFORM_END);
curl_formadd(&httppost, &last_ptr, CURLFORM_COPYNAME, "submit", CURLFORM_COPYCONTENTS, "send", CURLFORM_END);

curl_easy_setopt(curl, CURLOPT_WRITEFUNCTION, upload_response);
curl_easy_setopt(curl, CURLOPT_HTTPPOST, httppost);

CURLcode res = curl_easy_perform(curl);

if (res != CURLE_OK)
{
sprintf(last_error, "%s", curl_easy_strerror(res));
}

curl_easy_cleanup(curl);
curl_formfree(httppost);
}

return result;
}

CALLBACK-the function upload_response has the prototype:
the

size_t function(char *ptr, size_t size, size_t nmemb, void *userdata);

with parameters:
the

ptr — pointer to get the data
size * nmemb is the size of them
userdata — pointer to FILE* that is optionally installed via the option CURLOPT_WRITEDATA

The function must return the actual size of the processed data, i.e., size * nmemb. In this, in this function, you must parse the JSON passed in the response.
the

size_t upload_response(char *ptr, size_t size, size_t nmemb, void *userdata)
{
(void)userdata;

parse_upload_response(ptr);

return size * nmemb;
}

Leave it to another function which use a jansson to parse the response:
the

void parse_upload_response(const char *text)
{
if (!strcmp(text, "{}"))
{
sprintf(last_error, "%s", "Empty file");

return;
}

json_error_t error;

json_t *root = json_loads(text, 0, &error);

if (!root)
{
sprintf(last_error, "%s", text);

return;
}

json_t *id = json_object_get(root, "id");

if(!json_is_integer(id))
{
sprintf(last_error, "%s", text);

json_decref(root);

return;
}

result = json_integer_value(id);

json_decref(root);
}

In the case of an empty file, we will come to answer the {} will process this case. If everything is in order, the file was loaded successfully the answer will come in the form of: { "id":1001, "ext":"png",...}. Only interested in id, and written to the result.

A function to save the file a bit easier — you just have to create a GET request, get response and write it to file (after processing the situation when the file with the correct id not found):

barberry_download_file

int barberry_download_file(const char *host, int id, const char *filename)
{
result = 0;

file = fopen(filename, "wb");

if (!file)
{
sprintf(last_error, "%s", "Can't create file");

return -1;
}

curl_global_init(CURL_GLOBAL_ALL);

CURL *curl = curl_easy_init();

if (curl)
{
char buffer[1024];

sprintf(buffer, "%s/%d", host, id);

curl_easy_setopt(curl, CURLOPT_URL, buffer);
curl_easy_setopt(curl, CURLOPT_WRITEFUNCTION, download_response);

CURLcode res = curl_easy_perform(curl);

if (res != CURLE_OK)
{
sprintf(last_error, "%s", curl_easy_strerror(res));

result = -1;
}

curl_easy_cleanup(curl);
}

fclose(file);

return result;
}

download_response

size_t download_response(char *ptr, size_t size, size_t nmemb, void *userdata)
{
(void)userdata;

if (!strcmp(ptr, "{}"))
{
sprintf(last_error, "%s" "File not found on server");

result = -1;
}
else
{
fwrite(ptr, size * nmemb, 1, file);
}

return size * nmemb;
}

Delete the file in the service — this is the DELETE query (query type to curl is installed through the CURLOPT_CUSTOMREQUEST option):

barberry_delete_file

int barberry_delete_file(const char *host, int id)
{
result = 0;

curl_global_init(CURL_GLOBAL_ALL);

CURL *curl = curl_easy_init();

if (curl)
{
char buffer[1024];

sprintf(buffer, "%s/%d", host, id);

curl_easy_setopt(curl, CURLOPT_URL, buffer);
curl_easy_setopt(curl, CURLOPT_CUSTOMREQUEST, "DELETE");
curl_easy_setopt(curl, CURLOPT_WRITEFUNCTION, delete_response);

CURLcode res = curl_easy_perform(curl);

if (res != CURLE_OK)
{
sprintf(last_error, "%s", curl_easy_strerror(res));

result = -1;
}

curl_easy_cleanup(curl);
}

return result;
}

delete_response

size_t delete_response(char *ptr, size_t size, size_t nmemb, void *userdata)
{
(void)ptr;
(void)userdata;

return size * nmemb;
}

Before moving to PostgreSQL will write the small console utility for testing our functions. It checks the parameters if they match your expectations (for example in print_help), then do the desired action:

barberry_test.c

#include "barberry_impl.h"

void print_help()
{
fprintf(stdout, "Usage:\n");
fprintf(stdout, " bbtest upload my.service.local /home/username/image1000.png\n");
fprintf(stdout, " bbtest download my.service.local 1000 /home/username/image1000.png\n");
fprintf(stdout, " bbtest delete my.service.local 1000\n\n");
}

int main(int argc, char *argv[])
{
(void)argc;
(void)argv;

if (argc <= 2)
{
print_help();

return 0;
}

if (!strcmp(argv[1], "upload"))
{
if (argc != 4)
{
print_help();

return 0;
}

int id = barberry_upload_file(argv[2], argv[3]);

if (id != -1)
{
fprintf(stdout, "File uploaded with id %d\n", id);
}
else
{
fprintf(stderr, "%s\n", barberry_error());
}
}
else if (!strcmp(argv[1], "download"))
{
if (argc != 5)
{
print_help();

return 0;
}

int result = barberry_download_file(argv[2], atoi(argv[3]), argv[4]);

if (result != -1)
{
fprintf(stdout, "%s\n", "File downloaded");
}
else
{
fprintf(stderr, "%s\n", barberry_error());
}
}
else if (!strcmp(argv[1], "delete"))

if (argc != 4)
{
print_help();

return 0;
}

int result = barberry_delete_file(argv[2], atoi(argv[3]));

if (result != -1)
{
fprintf(stdout, "%s\n", "File deleted");
}
else
{
fprintf(stderr, "%s\n", barberry_error());
}
}
else
{
print_help();
}

return 0;
}

Collect the whole thing (paths in Your OS to the header files and libraries may differ) and test:
the

cc-c barberry_impl.c
cc-c barberry_test.c
cc-L/usr/lib -lcurl -ljansson -o bbtest barberry_test.o barberry_impl.o
./bbtest upload my.service.local ~/picture01.png
File uploaded with id 1017

If everything is in order, proceeds to the PostgreSQL part of our library (more information about stored functions in C in PostgreSQL is described in [4]).

Declare exported to database functions (version 1):
the

PG_FUNCTION_INFO_V1(bb_upload_file);
PG_FUNCTION_INFO_V1(bb_download_file);
PG_FUNCTION_INFO_V1(bb_delete_file);

To convert from text (type in PostgreSQL) to c-string will help a small function:
the

char* text_to_string(text *txt)
{
size_t size = VARSIZE(txt) - VARHDRSZ;

char *buffer = (char*)palloc(size + 1);

memcpy(buffer, VARDATA(txt), size);

buffer[size] = '\0';

return buffer;
}

Implementation of exported functions is to delegate the previously written functions, throwing an error if something went wrong:

bb_upload_file

Datum bb_upload_file(PG_FUNCTION_ARGS)
{
char *host = text_to_string(PG_GETARG_TEXT_P(0));
char *filename = text_to_string(PG_GETARG_TEXT_P(1));

int result = barberry_upload_file(host, filename);

if (result == -1)
{
elog(ERROR, "%s", barberry_error());
}

pfree(host);
pfree(filename);

PG_RETURN_INT32(result);
}

bb_download_file

Datum bb_download_file(PG_FUNCTION_ARGS)
{
char *host = text_to_string(PG_GETARG_TEXT_P(0));
int id = PG_GETARG_INT32(1);
char *filename = text_to_string(PG_GETARG_TEXT_P(2));

int result = barberry_download_file(host, id, filename);

if (result == -1)
{
elog(ERROR, "%s", barberry_error());
}

pfree(host);
pfree(filename);

PG_RETURN_VOID();
}

bb_delete_file

Datum bb_delete_file(PG_FUNCTION_ARGS)
{
char *host = text_to_string(PG_GETARG_TEXT_P(0));
int id = PG_GETARG_INT32(1);

int result = barberry_delete_file(host, id);

if (result == -1)
{
elog(ERROR, "%s", barberry_error());
}

pfree(host);

PG_RETURN_VOID();
}

Collect dynamic library and copy it to the PostgreSQL (paths in Your OS to the header files and libraries may differ):
the

rm-rf *.o
cc-I/usr/include/postgresql/server -fpic -c barberry.c
cc-I/usr/include/postgresql/server -fpic -c barberry_impl.c
cc-L/usr/lib -lpq -lcurl -ljansson -shared-o barberry.so barberry.o barberry_impl.o
cp *.so /usr/lib/postgresql

SQL functions are created in the database have the form:
the

CREATE OR REPLACE FUNCTION public.bb_upload_file ( p_host text, p_filename text )
RETURNS integer AS
'barberry', 'bb_upload_file'
LANGUAGE c VOLATILE STRICT;

CREATE OR REPLACE FUNCTION public.bb_download_file ( p_host text, p_id integer, p_filename text )
RETURNS void AS
'barberry', 'bb_download_file'
LANGUAGE c VOLATILE STRICT;

CREATE OR REPLACE FUNCTION public.bb_delete_file ( p_host text, p_id integer )
RETURNS void AS
'barberry', 'bb_delete_file'
LANGUAGE c VOLATILE STRICT;

Make a dynamic library and SQL script in the form of an extension to PostgreSQL (more details are described in [5]). This will require managing the file barberry.control:
the

# BarBerry image service
comment = 'BarBerry image service'
default_version = '1.0'
module_pathname = '$libdir/barberry'
relocatable = true

SQL-script to our extension must be named as barberry--1.0.sql (according to documentation, PostgreSQL). Copy these two files to where the PostgreSQL to store their extensions.

Creating and using extensions is very simple:
the

CREATE EXTENSION barberry;
UPDATE avatar SET image = bb_upload_file ( 'my.service.local', 'images/avatar_admin.png' ) WHERE name = 'admin';

Source files

Library your as a small utility, so it is not posted on github. To facilitate Assembly, added Makefile with targets barberry, barberry_test, clean, rebuild, and install.

barberry_impl.h

#ifndef BARBERRY_IMPL_H
#define BARBERRY_IMPL_H

#include <stdio.h>
#include <string.h>
#include <curl/curl.h>
#include <jansson.h>

// get last error
char* barberry_error();

// upload file to BarBerry''s service and return ID
int barberry_upload_file(const char *host, const char *filename);

// download file from BarBerry''s service by ID
int barberry_download_file(const char *host, int id, const char *filename);

// delete file from BarBerry''s service by ID
int barberry_delete_file(const char *host, int id);

#endif // BARBERRY_IMPL_H

barberry_impl.c

#include "barberry_impl.h"

char last_error[1024];
FILE *file = NULL;
int result = 0;

void parse_upload_response(const char *text)
{
if (!strcmp(text, "{}"))
{
sprintf(last_error, "%s", "Empty file");

return;
}

json_error_t error;

json_t *root = json_loads(text, 0, &error);

if (!root)
{
sprintf(last_error, "%s", text);

return;


json_t *id = json_object_get(root, "id");

if(!json_is_integer(id))
{
sprintf(last_error, "%s", text);

json_decref(root);

return;
}

result = json_integer_value(id);

json_decref(root);
}

size_t upload_response(char *ptr, size_t size, size_t nmemb, void *userdata)
{
(void)userdata;

parse_upload_response(ptr);

return size * nmemb;
}

size_t download_response(char *ptr, size_t size, size_t nmemb, void *userdata)
{
(void)userdata;

if (!strcmp(ptr, "{}"))
{
sprintf(last_error, "%s" "File not found on server");

result = -1;
}
else
{
fwrite(ptr, size * nmemb, 1, file);
}

return size * nmemb;
}

size_t delete_response(char *ptr, size_t size, size_t nmemb, void *userdata)
{
(void)ptr;
(void)userdata;

return size * nmemb;
}


char* barberry_error()
{
return last_error;
}

int barberry_upload_file(const char *host, const char *filename)
{
result = -1;

curl_global_init(CURL_GLOBAL_ALL);

CURL *curl = curl_easy_init();

if (curl)
{
curl_easy_setopt(curl, CURLOPT_URL, host);

struct curl_httppost *httppost = NULL;
struct curl_httppost *last_ptr = NULL;

curl_formadd(&httppost, &last_ptr, CURLFORM_COPYNAME, "sendfile", CURLFORM_FILE, filename, CURLFORM_END);
curl_formadd(&httppost, &last_ptr, CURLFORM_COPYNAME, "submit", CURLFORM_COPYCONTENTS, "send", CURLFORM_END);

curl_easy_setopt(curl, CURLOPT_WRITEFUNCTION, upload_response);
curl_easy_setopt(curl, CURLOPT_HTTPPOST, httppost);

CURLcode res = curl_easy_perform(curl);

if (res != CURLE_OK)
{
sprintf(last_error, "%s", curl_easy_strerror(res));
}

curl_easy_cleanup(curl);
curl_formfree(httppost);
}

return result;
}

int barberry_download_file(const char *host, int id, const char *filename)
{
result = 0;

file = fopen(filename, "wb");

if (!file)
{
sprintf(last_error, "%s", "Can't create file");

return -1;
}

curl_global_init(CURL_GLOBAL_ALL);

CURL *curl = curl_easy_init();

if (curl)
{
char buffer[1024];

sprintf(buffer, "%s/%d", host, id);

curl_easy_setopt(curl, CURLOPT_URL, buffer);
curl_easy_setopt(curl, CURLOPT_WRITEFUNCTION, download_response);

CURLcode res = curl_easy_perform(curl);

if (res != CURLE_OK)
{
sprintf(last_error, "%s", curl_easy_strerror(res));

result = -1;
}

curl_easy_cleanup(curl);
}

fclose(file);

return result;
}

int barberry_delete_file(const char *host, int id)
{
result = 0;

curl_global_init(CURL_GLOBAL_ALL);

CURL *curl = curl_easy_init();

if (curl)
{
char buffer[1024];

sprintf(buffer, "%s/%d", host, id);

curl_easy_setopt(curl, CURLOPT_URL, buffer);
curl_easy_setopt(curl, CURLOPT_CUSTOMREQUEST, "DELETE");
curl_easy_setopt(curl, CURLOPT_WRITEFUNCTION, delete_response);

CURLcode res = curl_easy_perform(curl);

if (res != CURLE_OK)
{
sprintf(last_error, "%s", curl_easy_strerror(res));

result = -1;
}

curl_easy_cleanup(curl);
}

return result;
}

barberry.c

#include <postgres.h>
#include <fmgr.h>

#include "barberry_impl.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(bb_upload_file);
PG_FUNCTION_INFO_V1(bb_download_file);
PG_FUNCTION_INFO_V1(bb_delete_file);

char* text_to_string(text *txt)
{
size_t size = VARSIZE(txt) - VARHDRSZ;

char *buffer = (char*)palloc(size + 1);

memcpy(buffer, VARDATA(txt), size);

buffer[size] = '\0';

return buffer;
}

Datum bb_upload_file(PG_FUNCTION_ARGS)
{
char *host = text_to_string(PG_GETARG_TEXT_P(0));
char *filename = text_to_string(PG_GETARG_TEXT_P(1));

int result = barberry_upload_file(host, filename);

if (result == -1)
{
elog(ERROR, "%s", barberry_error());
}

pfree(host);
pfree(filename);

PG_RETURN_INT32(result);
}

Datum bb_download_file(PG_FUNCTION_ARGS)
{
char *host = text_to_string(PG_GETARG_TEXT_P(0));
int id = PG_GETARG_INT32(1);
char *filename = text_to_string(PG_GETARG_TEXT_P(2));

int result = barberry_download_file(host, id, filename);

if (result == -1)
{
elog(ERROR, "%s", barberry_error());
}

pfree(host);
pfree(filename);

PG_RETURN_VOID();
}

Datum bb_delete_file(PG_FUNCTION_ARGS)
{
char *host = text_to_string(PG_GETARG_TEXT_P(0));
int id = PG_GETARG_INT32(1);

int result = barberry_delete_file(host, id);

if (result == -1)
{
elog(ERROR, "%s", barberry_error());
}

pfree(host);

PG_RETURN_VOID();
}

barberry_test.c

#include "barberry_impl.h"

void print_help()
{
fprintf(stdout, "Usage:\n");
fprintf(stdout, " bbtest upload my.service.local /home/username/image1000.png\n");
fprintf(stdout, " bbtest download my.service.local 1000 /home/username/image1000.png\n");
fprintf(stdout, " bbtest delete my.service.local 1000\n\n");
}

int main(int argc, char *argv[])
{
(void)argc;
(void)argv;

if (argc <= 2)
{
print_help();

return 0;
}

if (!strcmp(argv[1], "upload"))
{
if (argc != 4)
{
print_help();

return 0;
}

int id = barberry_upload_file(argv[2], argv[3]);

if (id != -1)
{
fprintf(stdout, "File uploaded with id %d\n", id);
}
else
{
fprintf(stderr, "%s\n", barberry_error());
}
}
else if (!strcmp(argv[1], "download"))
{
if (argc != 5)
{
print_help();

return 0;
}

int result = barberry_download_file(argv[2], atoi(argv[3]), argv[4]);

if (result != -1)
{
fprintf(stdout, "%s\n", "File downloaded");
}
else
{
fprintf(stderr, "%s\n", barberry_error());
}
}
else if (!strcmp(argv[1], "delete"))
{
if (argc != 4)
{
print_help();

return 0;
}

int result = barberry_delete_file(argv[2], atoi(argv[3]));

if (result != -1)
{
fprintf(stdout, "%s\n", "File deleted");
}
else
{
fprintf(stderr, "%s\n", barberry_error());
}
}
else
{
print_help();
}

return 0;
}

barberry--1.0.sql

CREATE OR REPLACE FUNCTION public.bb_upload_file ( p_host text, p_filename text )
RETURNS integer AS
'barberry', 'bb_upload_file'
LANGUAGE c VOLATILE STRICT;

CREATE OR REPLACE FUNCTION public.bb_download_file ( p_host text, p_id integer, p_filename text )
RETURNS void AS
'barberry', 'bb_download_file'
LANGUAGE c VOLATILE STRICT;

CREATE OR REPLACE FUNCTION public.bb_delete_file ( p_host text, p_id integer )
RETURNS void AS
'barberry', 'bb_delete_file'
LANGUAGE c VOLATILE STRICT

barberry.control

# BarBerry image service
comment = 'BarBerry image service'
default_version = '1.0'
module_pathname = '$libdir/barberry'
relocatable = true

Makefile

#################################

#################################

# options

CC=cc
CFLAGS=-fpic -c
INCLUDEPATH=-I/usr/include/postgresql/server
LIBS=-L/usr/lib -lpq -lcurl -ljansson

# targets

all: barberry barberry_test

barberry: barberry.o barberry_impl.o
$(CC) $(LIBS) -shared-o barberry.so barberry.o barberry_impl.o

barberry_test: barberry_test.o barberry_impl.o
$(CC) $(LIBS) -o bbtest barberry_test.o barberry_impl.o

barberry.o:
$(CC) $(INCLUDEPATH) $(CFLAGS) barberry.c

barberry_impl.o:
$(CC) $(INCLUDEPATH) $(CFLAGS) barberry_impl.c

barberry_test.o:
$(CC) $(INCLUDEPATH) $(CFLAGS) barberry_test.c

clean:
rm-rf *.o *.so bbtest

rebuild: clean all

install:
cp *.so /usr/lib/postgresql
cp *.control /usr/share/postgresql/extension
cp *.sql /usr/share/postgresql/extension

notes

the

as a dynamic library is loaded on behalf of postgres (the default user for the DBMS), it shall have access to the downloadable files and the right to create backup file
you can extend the idea by making the interface to access curl of PostgreSQL unit description forms, headings and other things in XML format, responsiva then in C-code, and executing the appropriate commands in the curl

bibliography

Article based on information from habrahabr.ru

Поиск по этому блогу

computer express