Re: Storing jpgs

От:

"Gavin M. Roy" <gmr@ehpg.net>

Дата:

5 апреля 2004 г. в 21:27:45

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)

Re: Storing jpgs

От:

"Gavin M. Roy" <gmr@ehpg.net>

Дата:

6 апреля 2004 г. в 16:02:41

What language?  Here's a quick example in php that expects a HTML Form 
that has a input type=file name=userfile:

 
|
And to send it:

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

Re: Storing jpgs

От:

Bill Moran <wmoran@potentialtech.com>

Дата:

6 апреля 2004 г. в 16:11:37

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

BYTEA maximum sizes (Re: Storing jpgs)

От:

David Garamond <lists@zara.6.isreserved.com>

Дата:

7 апреля 2004 г. в 01:58:44

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

Re: Storing jpgs

От:

Marek Lewczuk <newsy@lewczuk.com>

Дата:

7 апреля 2004 г. в 15:46:55

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 ?

Re: Storing jpgs

От:

Joe Lester <joe_lester@sweetwater.com>

Дата:

6 апреля 2004 г. в 15:02:06

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?

Re: Storing jpgs

От:

Greg Stark <gsstark@mit.edu>

Дата:

8 апреля 2004 г. в 12:24:09

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

Re: Storing jpgs

От:

"C G" <csgcsg39@hotmail.com>

Дата:

6 апреля 2004 г. в 13:47:42

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

Storing jpgs

От:

"C G" <csgcsg39@hotmail.com>

Дата:

5 апреля 2004 г. в 13:22:38

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

Re: Storing jpgs

От:

Scott Ribe <scott_ribe@killerbytes.com>

Дата:

9 апреля 2004 г. в 13:49:18

> 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

Re: Storing jpgs

От:

"Rod K" <rod@23net.net>

Дата:

5 апреля 2004 г. в 13:57:18

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

Re: Storing jpgs

От:

Bob.Henkel@hartfordlife.com

Дата:

5 апреля 2004 г. в 13:27:55






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"                  |
|         |            |
|         |           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 contain proprietary, 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, please notify the sender immediately by return e-mail, delete this communication and destroy all copies.
*************************************************************************

Re: Storing jpgs

От:

"scott.marlowe" <scott.marlowe@ihs.com>

Дата:

5 апреля 2004 г. в 14:05:53

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