PostgreSQL interaction with an external image storage service

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

the images are entirely in the database
the 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 to store images in the form of files on a 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, in which there can be caching, scaling on the fly, format change. Let's try to implement the interaction of PostgreSQL with such a service.

Implementation

Outline a little picture of what is happening. We have an http service, like this , for images that supports these commands:

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

Image identifiers will be stored in the database, in this case, it will be possible to insert tags like this on the pages of the site:

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

From the user’s side, image loading (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 the stored functions in C, and they already have some fun in them. For simplicity, let's do the curl and jansson libraries (the last one for working with JSON). We can start.

Define our function prototypes in the header file barberry_impl.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);

In the source file barberry_impl.c we place the following global variables:

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

The variable last_error will store the last error, file is a pointer to the file created when receiving data from the service, and the result will preserve the result of the functions of working with the service.

The implementation of the barberry_error function is trivial - return last_error . Let us examine in detail the function barberry_upload_file .
')
Before you start working with curl , you need to initialize the environment for it (with the curl_gobal_init command) and create a session (with the curl_easy_init command that returns a pointer to the session handle). Next, create a submit form (via curl_formadd ) and fill in the following options:

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

Implementation barberry_upload_file :

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

The upload_response CALLBACK function has a 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 *, if necessary, set via the CURLOPT_WRITEDATA option

The function must 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; }

Assign 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 process this case. If everything is in order, the file has been successfully uploaded. The answer will come in the form: {"id": 1001, "ext": "png" ...}. Interests only id, and write it to result.

The function to save the file is a bit simpler - you just need to form a GET request, get an answer and write it to a file (after handling the situation when the file with the required id is 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, let's write a small console utility to test our functions. In it, we check the passed parameters, if they are as expected (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 it all together (paths in your OS to header files and libraries may differ) and test:

 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 proceed to the PostgreSQL part of our library (for more information about stored C functions in PostgreSQL, see [4] ).

Let's announce 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 the exported functions consists in the delegation of the previously written functional, 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 compile a dynamic library and copy it to PostgreSQL (paths in your OS to header files and libraries may differ):

 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

The SQL functions created in the database are:

 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;

Let's create a dynamic library and SQL script as an extension to PostgreSQL (described in more detail in [5] ). This will require the control file barberry.control :

 # 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 named as barberry - 1.0.sql (according to the PostgreSQL documentation). Copy these two files to where PostgreSQL stores its extensions.

Creating and using an 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

Your library is a small utility, so it is not hosted on github. To facilitate the assembly, a Makefile has been added with barberry, barberry_test, clean, rebuild, install goals.

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

 ################################# # 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 extend the idea by making an interface for accessing curl from PostgreSQL , screwing up a description of the form, headers and everything else in XML format, then parsing it in C code and executing the corresponding commands in curl

Bibliography

Source: https://habr.com/ru/post/206576/

All Articles

PostgreSQL interaction with an external image storage service

Implementation

Source files

Notes

Bibliography

More articles: