blackmaster December 23, 2013 at 00:48

PostgreSQL interaction with external image storage service

Good day. When working with a database for a site, a situation sometimes arises when you have to choose how and where to store images. Among the possible options, as a rule, there are the following:

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

Although PostgreSQL provides the ability to store files in the database (directly in bytea fields or through large objects ), this is the least optimal option, both in terms of speed and memory consumption. Another common practice is the storage of images in the form of files on disk, the path to the image is formed for the site. Of the benefits - the ability to cache or use a specialized file system. And the third option - a separate service is allocated for images, which can be caching, scaling on the fly, changing the format. Let's try to implement the interaction of PostgreSQL with such a service.

Implementation

We outline a little picture of what is happening. We have an http service, like this , for images, supporting such commands:

image loading - sending a POST request with a form, in response comes JSON with some information about the image, among which the generated identifier
image acquisition - sending a GET request with the image identifier my.service.local / 1001
image deletion - sending a DELETE request with the image identifier my.service.local / 1001

Image identifiers will be stored in the database, in this case, you can embed tags of the form on the pages of the site:

On the part of the user, loading the image (as well as saving and deleting) should look like a call to the upload_image function (with the filename parameter ), which returns the image identifier in the service, which is then written to the table. Since it is impossible to access http requests directly from PostgreSQL , it is necessary to implement the required functionality on stored functions in C, and they already have a place to go around. For simplicity, let’s do with the curl and jansson libraries (the latter for working with JSON). We can start.

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

// 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:

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

Переменная last_error будет хранить последнюю ошибку, file — это указатель на файл, создаваемый при получении данных от сервиса, а в result будет сохранятся результат функций работы с сервисом.

Реализация функции barberry_error тривиальна — возврат last_error. Разберем подробно функцию barberry_upload_file.

Перед тем, как начать работу с библиотекой curl, необходимо проинициализировать окружение для неё (командой curl_gobal_init) и создать сессию (командой curl_easy_init, возращающей указатель на хэндл сессии). Далее, создаем submit-форму (через curl_formadd) и заполняем следующие опции:

CURLOPT_URL - the host we are working with
CURLOPT_HTTPPOST - form submitted by the POST method
CURLOPT_WRITEFUNCTION - CALLBACK function to respond from the host

Barberry_upload_file implementation :

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-function upload_response has prototype:

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

with parameters:

ptr - pointer to the received data
size * nmemb - their size
userdata - pointer to FILE *, optionally set via the CURLOPT_WRITEDATA option

The function should return the actual size of the processed data, i.e. size * nmemb. In this, in this function, it is necessary to parse the JSON transmitted in the response:

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

We will delegate this to another function in which we use jansson to parse the answer:

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 receive the answer {}, we will handle this case. If everything is in order, the file has been successfully uploaded, the answer will come in the form: {"id": 1001, "ext": "png" ...}. Only id is of interest, and we write it in result.

The function for saving the file is a little simpler - you just need to generate a GET request, get the response and write it to the file (after processing the situation when the file with the desired id was 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 on server not found");
		result = -1;
	}
	else
	{
		fwrite(ptr, size * nmemb, 1, file);
	}
	return size * nmemb;
}

Deleting a file in the service is a DELETE request (the request type for curl is set via 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 on to the PostgreSQL part, we will write a small console utility for testing our functions. In it, we check the passed parameters, if they correspond to the expected ones (example in print_help ), then we do the necessary actions:

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;
}

Putting this whole thing together (the paths in your OS to header files and libraries may vary) and testing:

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, you can go to the PostgreSQL part of our library (for more details on stored C functions in PostgreSQL, see [4] ).

Let's declare the functions exported for the database (with version 1):

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, a small function will help:

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;
}

The implementation of exported functions consists in delegating the previously written functionality, with the generation of 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();
}

We build a dynamic library and copy it to PostgreSQL (the paths in your OS to header files and libraries may vary):

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 created in the database look like:

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;

We will design a dynamic library and an SQL script as an extension to PostgreSQL (described in more detail in [5] ). To do this, you need the barberry.control control file :

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

The SQL script for our extension must be called barberry - 1.0.sql (according to the PostgreSQL documentation ). We copy these two files to where PostgreSQL stores its extensions.

Creating and using the extension is extremely simple:

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

Source files

The library above is a small utility, so it is not hosted on github. To facilitate assembly, a Makefile has been added for the purposes of barberry, barberry_test, clean, rebuild, install.

barberry_impl.h

#ifndef BARBERRY_IMPL_H
#define BARBERRY_IMPL_H
#include 
#include 
#include 
#include 
// 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 on server not found");
		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 
#include 
#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

#################################
# Makefile for barberry library #
#################################
# 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

since the dynamic library is loaded on behalf of postgres (the default user for the DBMS), it must also have access to the downloaded files and the right to create saved files
you can expand the idea by making an interface for accessing curl from PostgreSQL , screwing up the description of the form, headers and everything else in XML format, then parsing in C code and executing the corresponding commands in curl

List of references

Tags:

PostgreSQL interaction with external image storage service

Implementation

Source files

Notes

List of references

Also popular now: