Accessing Large Objects from libpq
The facilities Red Hat Database provides to access large objects in the frontend as part of an application using the libpq interface are described in this section.
 | All large object manipulation must take place within an SQL transaction. This requirement is strictly enforced. |
The Red Hat Database libpq large object interface is modeled after the Unix file system interface, with analogs of open(), read(), write(), lseek(), etc. User functions call these routines to retrieve only the data of interest from a large object. For example, if a large object type called mugshot stored photographs of faces, then a function called beard could be declared on mugshot data. The beard() function could look at the lower third of a photograph, and determine the color of the beard that appeared there, if any. The entire large object value need not be buffered, or even examined, by the beard() function. Large objects may be accessed from dynamically-loaded C functions or database client programs that link the library. Red Hat Database provides a set of routines that support opening, reading, writing, closing, and seeking on large objects.
Frontend applications which use the large object interface in libpq should include the header file libpq/libpq-fs.h and link with the libpq library.
Creating or Removing a Large Object
lo_creat
lo_creat creates a new large object.
Oid lo_creat(PGconn *conn, int mode) |
mode is a bitmask describing several different attributes of the new object. The symbolic constants listed here are defined in PGROOT/src/backend/libpq/libpq-fs.h The access type (read, write, or both) is controlled by OR-ing together the bits INV_READ and INV_WRITE. If the large object should be archived -- that is, if historical versions of it should be moved periodically to a special archive relation -- then the INV_ARCHIVE bit should be set. The low-order 16 bits of mask are the storage manager number on which the large object should reside. These bits should always be zero. The commands below create an (Inversion) large object:
inv_oid = lo_creat(INV_READ|INV_WRITE|INV_ARCHIVE); |
lo_unlink
lo_unlink removes a large object from the database.
Oid lo_unlink(PGconn *conn, Oid lobjId) |
The lobjId argument specifies the Oid of the large object to remove.
Importing and Exporting a Large Object
lo_import
lo_import imports a Linux file as a large object.
Oid lo_import(PGconn *conn, const char *filename) |
filename specifies the Linux pathname of the file to be imported as a large object. A new large object is automatically created.
lo_export
lo_export export a large object into a Linux file.
int lo_export(PGconn *conn, Oid lobjId, const char *filename) |
The lobjId argument specifies the Oid of the large object to export and the filename argument specifies the pathname of the file.
Accessing an Existing Large Object
lo_open
lo_open opens an existing large object.
int lo_open(PGconn *conn, Oid lobjId, int mode) |
The lobjId argument specifies the Oid of the large object to open. The mode bits control whether the object is opened for reading (INV_READ), writing (INV_WRITE), or both. A large object cannot be opened before it is created. lo_open returns a large object descriptor for later use in lo_read, lo_write, lo_lseek, lo_tell, and lo_close.
lo_write
lo_write writes len bytes from buf to large object fd.
int lo_write(PGconn *conn, int fd, const char *buf, size_t len) |
The fd argument must have been returned by a previous lo_open. The number of bytes actually written is returned. In the event of an error, the return value is negative.
lo_read
lo_read reads len bytes from large object fd into buf.
int lo_read(PGconn *conn, int fd, char *buf, size_t len) |
The fd argument must have been returned by a previous lo_open. The number of bytes actually read is returned. In the event of an error, the return value is negative.
lo_lseek
lo_lseek changes the current read or write location on a large object.
int lo_lseek(PGconn *conn, int fd, int offset, int whence) |
This routine moves the current location pointer for the large object, described by fd, to the new location specified by offset. The valid values for whence are SEEK_SET, SEEK_CUR, and SEEK_END.
lo_close
lo_close closes a large object.
int lo_close(PGconn *conn, int fd) |
This routine closes the current location pointer for the large object, described by fd, the large object descriptor returned by lo_open. On success, lo_close returns zero. On error, the return value is negative.
Large Objects Sample Program
Below is a sample program which shows how the large object interface in libpq can be used. Parts of the program are commented out but are left in the source for the reader's benefit. This program can be found in src/test/examples
/*-------------------------------------------------------------------------
*
* testlo.c
* test using large objects with libpq
*
* Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
*
*
* IDENTIFICATION
* $Header:
/home/projects/pgsql/cvsroot/pgsql/src/test/examples/testlo.c,v 1.18
2001/03/22 04:01:43 momjian Exp $
*
*-------------------------------------------------------------------------
*/
#include <stdio.h>
#include <stdlib.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <fcntl.h>
#include <unistd.h>
#include "libpq-fe.h"
#include "libpq/libpq-fs.h"
#define BUFSIZE 1024
/*
* importFile -
* import file "in_filename" into database as large object "lobjOid"
*
*/
static Oid
importFile(PGconn *conn, char *filename)
{
Oid lobjId;
int lobj_fd;
char buf[BUFSIZE];
int nbytes,
tmp;
int fd;
/*
* open the file to be read in
*/
fd = open(filename, O_RDONLY, 0666);
if (fd < 0)
{ /* error */
fprintf(stderr, "can't open unix file\"%s\"\n", filename);
}
/*
* create the large object
*/
lobjId = lo_creat(conn, INV_READ | INV_WRITE);
if (lobjId == 0)
fprintf(stderr, "can't create large object");
lobj_fd = lo_open(conn, lobjId, INV_WRITE);
/*
* read in from the Unix file and write to the inversion file
*/
while ((nbytes = read(fd, buf, BUFSIZE)) > 0)
{
tmp = lo_write(conn, lobj_fd, buf, nbytes);
if (tmp < nbytes)
fprintf(stderr, "error while reading \"%s\"", filename);
}
close(fd);
lo_close(conn, lobj_fd);
return lobjId;
}
static void
pickout(PGconn *conn, Oid lobjId, int start, int len)
{
int lobj_fd;
char *buf;
int nbytes;
int nread;
lobj_fd = lo_open(conn, lobjId, INV_READ);
if (lobj_fd < 0)
fprintf(stderr, "can't open large object %u", lobjId);
lo_lseek(conn, lobj_fd, start, SEEK_SET);
buf = malloc(len + 1);
nread = 0;
while (len - nread > 0)
{
nbytes = lo_read(conn, lobj_fd, buf, len - nread);
buf[nbytes] = '\0';
fprintf(stderr, ">>> %s", buf);
nread += nbytes;
if (nbytes <= 0)
break; /* no more data? */
}
fprintf(stderr, "\n");
lo_close(conn, lobj_fd);
}
static void
overwrite(PGconn *conn, Oid lobjId, int start, int len)
{
int lobj_fd;
char *buf;
int nbytes;
int nwritten;
int i;
lobj_fd = lo_open(conn, lobjId, INV_READ);
if (lobj_fd < 0)
fprintf(stderr, "can't open large object %u", lobjId);
lo_lseek(conn, lobj_fd, start, SEEK_SET);
buf = malloc(len + 1);
for (i = 0; i < len; i++)
buf[i] = 'X';
buf[i] = '\0';
nwritten = 0;
while (len - nwritten > 0)
{
nbytes = lo_write(conn, lobj_fd, buf + nwritten, len - nwritten);
nwritten += nbytes;
if (nbytes <= 0)
{
fprintf(stderr, "\nWRITE FAILED!\n");
break;
}
}
fprintf(stderr, "\n");
lo_close(conn, lobj_fd);
}
/*
* exportFile -
* export large object "lobjOid" to file "out_filename"
*
*/
static void
exportFile(PGconn *conn, Oid lobjId, char *filename)
{
int lobj_fd;
char buf[BUFSIZE];
int nbytes,
tmp;
int fd;
/*
* create an inversion "object"
*/
lobj_fd = lo_open(conn, lobjId, INV_READ);
if (lobj_fd < 0)
fprintf(stderr, "can't open large object %u", lobjId);
/*
* open the file to be written to
*/
fd = open(filename, O_CREAT | O_WRONLY | O_TRUNC, 0666);
if (fd < 0)
{ /* error */
fprintf(stderr, "can't open unix file\"%s\"",
filename);
}
/*
* read in from the Unix file and write to the inversion file
*/
while ((nbytes = lo_read(conn, lobj_fd, buf, BUFSIZE)) > 0)
{
tmp = write(fd, buf, nbytes);
if (tmp < nbytes)
{
fprintf(stderr, "error while writing \"%s\"",
filename);
}
}
lo_close(conn, lobj_fd);
close(fd);
return;
}
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
int
main(int argc, char **argv)
{
char *in_filename,
*out_filename;
char *database;
Oid lobjOid;
PGconn *conn;
PGresult *res;
if (argc != 4)
{
fprintf(stderr, "Usage: %s database_name in_filename out_filename\n",
argv[0]);
exit(1);
}
database = argv[1];
in_filename = argv[2];
out_filename = argv[3];
/*
* set up the connection
*/
conn = PQsetdb(NULL, NULL, NULL, NULL, database);
/* check to see that the backend connection was successfully made */
if (PQstatus(conn) == CONNECTION_BAD)
{
fprintf(stderr, "Connection to database '%s' failed.\n", database);
fprintf(stderr, "%s", PQerrorMessage(conn));
exit_nicely(conn);
}
res = PQexec(conn, "begin");
PQclear(res);
printf("importing file \"%s\" ...\n", in_filename);
/* lobjOid = importFile(conn, in_filename); */
lobjOid = lo_import(conn, in_filename);
if (lobjOid == 0)
fprintf(stderr, "%s\n", PQerrorMessage(conn));
else
{
printf("\tas large object %u.\n", lobjOid);
printf("picking out bytes 1000-2000 of the large object\n");
pickout(conn, lobjOid, 1000, 1000);
printf("overwriting bytes 1000-2000 of the large object with X's\n");
overwrite(conn, lobjOid, 1000, 1000);
printf("exporting large object to file \"%s\" ...\n", out_filename);
/* exportFile(conn, lobjOid, out_filename); */
if (!lo_export(conn, lobjOid, out_filename))
fprintf(stderr, "%s\n", PQerrorMessage(conn));
}
res = PQexec(conn, "end");
PQclear(res);
PQfinish(conn);
return 0;
} |