Home > PostgreSQL > Transfer Binary from Photo File to Database Table

Transfer Binary from Photo File to Database Table

In many web application, personal photo is saved as [*.jpg] file in a folder in server’s disk. The relation with personal ID in database is established by set the filename accordingly in format: nnnnn.jpg, e.g: 00001.jpg, 00002.jpg and so on.
Once upon a time, I was in charge to move content of photo file into database’s field, integrated with personal identity table. All works in Linux environment and PostgreSQL 9.0.
I want to share the story….
Original setting: photo file is saved in folder: /home/webuser/photo/ and person table resides in persondb database with DDL as follows:

CREATE TABLE person
(
id serial NOT NULL,
fullname text NOT NULL,
addres text NOT NULL,
CONSTRAINT “person: id must be unique” PRIMARY KEY (id)
) WITH (OIDS=FALSE);

The table has been populated with hundreds of person records.

Part 1: Alter Table
Add a field to store photo bytearray:

ALTER TABLE person ADD COLUMN photo bytea;
Part 2: Create C Source Code
Create person.c:

#include <postgres.h>
#include <fmgr.h>
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
#include <lib/stringinfo.h>
#include <executor/spi.h>
#include <catalog/pg_type.h>
#include <utils/builtins.h>
#include <utils/bytea.h>
#include <catalog/pg_type.h>

PG_FUNCTION_INFO_V1(person_photo);
Datum person_photo(PG_FUNCTION_ARGS);

Datum
person_photo(PG_FUNCTION_ARGS)
{
int32 personId;
StringInfoData photoFile;
StringInfoData photoBytea;
const char* PHOTO_PATH=”/home/webuser/photo/”;
const char* PHOTO_EXT=”jpg”;
FILE* f;
const size_t BUFFER_SIZE=5*1024;
char buf[BUFFER_SIZE];
size_t byteReadCount;
const int32 SQL_PARAM_COUNT=2;
Oid argTypes[SQL_PARAM_COUNT];
Datum vals[SQL_PARAM_COUNT];

////get function argument:BEGIN

if(PG_ARGISNULL(0)) {
elog(ERROR,”person id can not be NULL”);
i}
personId=PG_GETARG_INT32(0);

////get function argument:END

////read photo file:BEGIN

photoBytea.data=0;
photoBytea.len=0;
initStringInfo(&photoFile);
appendStringInfo(&photoFile, “%s%05d.%s”, PHOTO_PATH, personId, PHOTO_EXT);
f=fopen(photoFile.data, “rb”);//open photo file
pfree(photoFile.data);
if(f){
initStringInfo(&photoBytea);
while(!feof(f)){//keep reading until end of file
byteReadCount=fread(buf, 1, BUFFER_SIZE, f);
appendBinaryStringInfo(&photoBytea, buf, byteReadCount);
}
fclose(f);
} else {
elog(NOTICE, “person with id %d does not have photo”, personId);
}

////read photo file:END

////save to database:BEGIN

if(photoBytea.len){
SPI_connect();

argTypes[0]=INT4OID;
argTypes[1]=BYTEAOID;
vals[0]=Int32GetDatum(personId);
vals[1]=DirectFunctionCall1(bytearecv, PointerGetDatum(&photoBytea));

SPI_execute_with_args(“UPDATE person SET photo=$2 WHERE id=$1″,
SQL_PARAM_COUNT, argTypes, vals, NULL, FALSE, 1);

SPI_finish();
}
if(photoBytea.data){
pfree(photoBytea.data);
}

////save to database:END

PG_RETURN_VOID();
}

Part 3: Create SQL to Install Our Custom Library
Create person.sql.in in the same folder as person.c:

CREATE OR REPLACE FUNCTION person_photo(person_id integer)
RETURNS VOID
AS ‘MODULE_PATHNAME’ LANGUAGE C VOLATILE;

Part 4: Create Makefile
Create Makefile in the same folder as person.c:

MODULE_big = person
OBJS = person.o
DATA_built = person.sql

PG_CPPFLAGS := $(shell pg_config –includedir-server)

PGXS := $(shell pg_config –pgxs)
include $(PGXS)

Part 5: Compile and Install
In the same folder as Makefile, run command make then make install. Make sure we have sufficient privilege to create file in PostgreSQL contrib and library folder. If everything goes fine then we will have two files:

  1. SQL file person.sql in PostgreSQL contrib directory.
  2. Library file person.so in PostgreSQL library directory.
Part 6: Install Stored Procedure
Assuming we are in the same folder as person.sql and want to install stored procedure in persondb database (where table person exists) at host server1:

psql -U postgres -h server1 -f person.sql -d persondb

Part 7: Move Photo From File to Table
All preparation has been set. It is time to login to PostgreSQL server and run the stored procedure (make sure folder /home/webuser/photo/ is accessible by postgres user):

psql -U postgres -h server1 persondb
Password for user postgres:[your password]
postgres=#SELECT person_photo(id) FROM person;

Wait a moment, let PostgreSQL read photo files and store the binaries into field photo.

My story ends….
About these ads
Categories: PostgreSQL
  1. chaitany
    December 26, 2010 at 6:14 pm

    good!

  2. Kaare Rasmussen
    December 27, 2010 at 6:11 am

    It would have been nice to know what was the impetus behind the change, and to see some hard facts about performance before and after.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: