Thread: Storing jpgs
Dear All, What's the best way to store jpgs in postgresql to use in a web page? I tried to use large objects, but how would you extract them from a table to be viewed in a web-page without having to write them to a scratch file somewhere first? Thanks Colin _________________________________________________________________ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger
I won't say what is right or wrong to do. But some will say to store the
file location in a field such as /mydocs/mypictures/myimage.jpg and then
have your php or what ever open that file. by using a select statement.
Not sure how you would store them in the database and pull it righ out. I
would prefer your method for many reasons. For one backing up the database
also backups up all your image files. Good luck.
|---------+---------------------------------->
| | "C G" |
| | <csgcsg39@hotmail.com> |
| | Sent by: |
| | pgsql-general-owner@pos|
| | tgresql.org |
| | |
| | |
| | 04/05/2004 11:20 AM |
| | |
|---------+---------------------------------->
>------------------------------------------------------------------------------------------------------------------------------|
|
|
| To: pgsql-general@postgresql.org
|
| cc:
|
| Subject: [GENERAL] Storing jpgs
|
>------------------------------------------------------------------------------------------------------------------------------|
Dear All,
What's the best way to store jpgs in postgresql to use in a web page?
I tried to use large objects, but how would you extract them from a table
to
be viewed in a web-page without having to write them to a scratch file
somewhere first?
Thanks
Colin
_________________________________________________________________
Stay in touch with absent friends - get MSN Messenger
http://www.msn.co.uk/messenger
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
*************************************************************************
PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may
containproprietary, confidential and/or privileged information. If you are not the intended recipient, any use,
copying,disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient,
pleasenotify the sender immediately by return e-mail, delete this communication and destroy all copies.
*************************************************************************
I've found bytea works better for me than large objects. As far as how to retrieve and display, that depends. What scripting language are you using? > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of C G > Sent: Monday, April 05, 2004 12:20 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Storing jpgs > > > Dear All, > > What's the best way to store jpgs in postgresql to use in a web page? > > I tried to use large objects, but how would you extract them from > a table to > be viewed in a web-page without having to write them to a scratch file > somewhere first? > > Thanks > > Colin > > _________________________________________________________________ > Stay in touch with absent friends - get MSN Messenger > http://www.msn.co.uk/messenger > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
On Mon, 5 Apr 2004, C G wrote: > Dear All, > > What's the best way to store jpgs in postgresql to use in a web page? > > I tried to use large objects, but how would you extract them from a table to > be viewed in a web-page without having to write them to a scratch file > somewhere first? There are several ways to skin this cat, and your choice depends largely on what type of system you'll be deploying. Will you have more than one front end server? If so, will they be able to share a network file system mount for the files? Then the preferred method for many people is to store the jpg in the file system with a path in the database. If you can't mount the same point from multiple servers (or don't want to) then you'll need to store them in the database. However, maybe you want to be able to update multiple jpegs at the same time in a transaction? then storing them in either a bytea field or base64 encoded in a text field will work well. Storing them as base64 or as a path with a file system is likely more portable than using large objects. Also, you have to dump large objects seperately, so your backup process may be more complicated than you want. As for displaying them whether you store them as bytea, base64 encoded text, or large objects, most languages will allow you to build and deliver an image without having to write it to some temporary place. Here's a simple example from PHPBuilder on doing it with the file path in the database, and using a directory of files that may lie outside the documentroot of apache: http://www.phpbuilder.com/board/showthread.php?s=&postid=10497815#post10497815
I'm one for using base64 encoded text in a text field. It's easy to deal with queries, it's easy to deal with spitting out images, and it's easy to back up. Others do it differently, there are many ways to skin a cat, and each person who skins cats most likely thinks their way is best. Gavin C G wrote: > Dear All, > > What's the best way to store jpgs in postgresql to use in a web page? > > I tried to use large objects, but how would you extract them from a > table to be viewed in a web-page without having to write them to a > scratch file somewhere first? > > Thanks > > Colin > > _________________________________________________________________ > Stay in touch with absent friends - get MSN Messenger > http://www.msn.co.uk/messenger > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > What's the best way to store jpgs in postgresql to use in a web page? > > Dear All, Thanks for all your responses. Basically, we're building a web application using python/spyce which will allow the user to run numerical algothrims and then visualise the results as a graph. The user may (or may not) which to store the graphs. Hence, I think that using base 64/bytea method maybe the best bet. Thanks again Colin _________________________________________________________________ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger
Would anyone have some example code they could share using libpq to encode an image into a text field? Right now, I'm converting my image into a hexadecimal string representation in my SQL statement. I'm sure there must be a better (faster) way. The hex encodeing/decoding slows things down for me since my app deals with a lot of images. On Apr 5, 2004, at 2:03 PM, Gavin M. Roy wrote: > I'm one for using base64 encoded text in a text field. It's easy to > deal with queries, it's easy to deal with spitting out images, and > it's easy to back up. Others do it differently, there are many ways > to skin a cat, and each person who skins cats most likely thinks their > way is best. > Gavin > > C G wrote: > >> Dear All, >> >> What's the best way to store jpgs in postgresql to use in a web page?
What language? Here's a quick example in php that expects a HTML Form
that has a input type=file name=userfile:
<?php
// assumes pg_Connect has been called and the connection is $conn
// check to make sure it's an uploaded file
|if (is_uploaded_file(|$_FILES['userfile']['tmp_name']|))
{
// Get Image Information for JPEG Validation
$imgInfo = getimagesize(|$_FILES['userfile']['tmp_name']);
// Make sure it's a JPEG before moving on
if ( $imgInfo[2] == 2 )
{
|// Read the file in to a variable
$fp = fopen(|$_FILES['userfile']['tmp_name']|, "rb");
$textData = base64_encode(fread($fp,
filesize(|$_FILES['userfile']['tmp_name'])));
fclose($fp);
/* Insert into into a table called media with the following schema:
filename varchar(30) not null primary key
mimetype varchar(30) not null default 'image/jpeg'
filedata text
*/
pg_Query($conn, "INSERT INTO media VALUES('" .
$_FILES['userfile']['name'] . "','image/jpeg', '$textData');
} else {
echo "Uploaded file isn't a valid JPEG.\n";
}
} else {
echo "Invalid file upload.\n";
}
}
|
?>
|
And to send it:
<?php
// assumes a GET variable called image
(http://yoursite.com/displayImage.php?image=picture.jpg and
// pg_Connect already called with $conn as connection
$result = pg_Query($conn, "SELECT mimetype, filedata FROM media WHERE
filename = '" . $_GET['image'] . "';");
if ( pg_NumRows($result) > 0 )
{
$data = pg_Fetch_Object($result, 0);
Header("Content-type: $data->mimetype");
echo base64_decode($data->filedata);
exit();
} else {
echo "404: File Not Found.";
}
?>
Hope this helps, I've not tested it but it should work ok and at best it
illustrates the principles.
Gavin
Joe Lester wrote:
> Would anyone have some example code they could share using libpq to
> encode an image into a text field? Right now, I'm converting my image
> into a hexadecimal string representation in my SQL statement. I'm sure
> there must be a better (faster) way. The hex encodeing/decoding slows
> things down for me since my app deals with a lot of images.
>
> On Apr 5, 2004, at 2:03 PM, Gavin M. Roy wrote:
>
>> I'm one for using base64 encoded text in a text field. It's easy to
>> deal with queries, it's easy to deal with spitting out images, and
>> it's easy to back up. Others do it differently, there are many ways
>> to skin a cat, and each person who skins cats most likely thinks
>> their way is best.
>> Gavin
>>
>> C G wrote:
>>
>>> Dear All,
>>>
>>> What's the best way to store jpgs in postgresql to use in a web page?
>>
>
>
Joe Lester wrote: > Would anyone have some example code they could share using libpq to > encode an image into a text field? Right now, I'm converting my image > into a hexadecimal string representation in my SQL statement. I'm sure > there must be a better (faster) way. The hex encodeing/decoding slows > things down for me since my app deals with a lot of images. Is this what you're looking for?: http://www.postgresql.org/docs/7.4/static/libpq-exec.html#LIBPQ-EXEC-ESCAPE-BYTEA I don't have actual numbers to support it, but I would expect that using Postgres' escape function will be faster since it only escapes what absolutely must be escaped. HTH -- Bill Moran Potential Technologies http://www.potentialtech.com
Rod K wrote: > I've found bytea works better for me than large objects. Speaking of BYTEA, what is the recommended maximum sizes for data to be put in there. I've played with megabytes-sized BYTEA and the memory requirement is pretty huge... Is it possible for BYTEA to be efficient and supporting chunk processing in the future? Is BYTEA and TEXT the future of all lobs? -- dave
On Apr 5, 2004, at 2:03 PM, Gavin M. Roy wrote: > I'm one for using base64 encoded text in a text field. It's easy to > deal with queries, it's easy to deal with spitting out images, and > it's easy to back up. Others do it differently, there are many ways to > skin a cat, and each person who skins cats most likely thinks their > way is best. Gavin Anyone knows what about performance of that kind of solution. Right now I'm storing all multimedia files on the disc, but I would be great if all files could be stored in DB (automatic remove, portability etc...). But the problem is with the size of all files - today it is about 5GB, so I belive that this can affect for overall DB performance. Any experiences ? PS. My DB have 3 schemes where about 1 million different data are stored. I thought that it will be the best to create new scheme for storing files ? Right ? How about perfomance in this case ?
Marek Lewczuk <newsy@lewczuk.com> writes: > On Apr 5, 2004, at 2:03 PM, Gavin M. Roy wrote: > > > I'm one for using base64 encoded text in a text field. It's easy to > > deal with queries, it's easy to deal with spitting out images, and > > it's easy to back up. Others do it differently, there are many ways to > > skin a cat, and each person who skins cats most likely thinks their > > way is best. Gavin > > Anyone knows what about performance of that kind of solution. Right now I'm > storing all multimedia files on the disc, but I would be great if all files > could be stored in DB (automatic remove, portability etc...). But the problem > is with the size of all files - today it is about 5GB, so I belive that this > can affect for overall DB performance. Any experiences ? Well just having them in the database won't affect performance directly. But there are indirect effects: 1) If you're accessing them regularly then the disk data will be cached by the kernel just like other tables, and will contend with the much denser data from your data tables. 2) Backups and restores will have to slog through that data as well and take correspondingly longer. > PS. My DB have 3 schemes where about 1 million different data are stored. I > thought that it will be the best to create new scheme for storing files ? > Right ? How about perfomance in this case ? Schemas don't affect performance at all. If you're debating between storing in the same table versus new identical tables there could be pros or cons, but if it complicates your SQL it's probably not worth it in any case. -- greg
> Well just having them in the database won't affect performance directly. > > But there are indirect effects: > > 1) If you're accessing them regularly then the disk data will be cached by the > kernel just like other tables, and will contend with the much denser data > from your data tables. > > 2) Backups and restores will have to slog through that data as well and take > correspondingly longer. Right. One thing to consider is whether your files will change often or be mostly static. I store them in the file system because the files I'm dealing with right now never get updated. If they were in the database then my simple database backup (pg_dumpall) would copy them all every time. By keeping them in the file system it's easy to use utilities which do incremental backup, and thus only new files need be copied. -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 665-7007 voice