pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedbytea or large object
7+ messages / 5 participants
[nested] [flat]
* bytea or large object
@ 2002-04-03 14:47 Jean-Christophe FABRE <jean-christophe.fabre@ensam.inra.fr>
0 siblings, 2 replies; 7+ messages in thread
From: Jean-Christophe FABRE @ 2002-04-03 14:47 UTC (permalink / raw)
To: pgsql-sql; pgsql-php@postgresql.org
Hi,
I would like to store .pdf files in a database, which is accessed through a
PHP interface.
which is the best way to store binary data?
- bytea? (with the tricky handling of escape chars)
- large objects?
thanks
JC
PS: I didn't found any example of scripts using bytea in PHP, does someone
has some?
_________________________________________________________
Jean-Christophe FABRE
INRA - UMR Sol & Environnement tel: +33 (0)4 99 61 23 51
2, place Viala fax: +33 (0)4 67 63 26 14
34060 MONTPELLIER cedex 1
FRANCE
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: bytea or large object
@ 2002-04-03 14:48 Timothy_maguire@hartehanks.com
0 siblings, 0 replies; 7+ messages in thread
From: Timothy_maguire@hartehanks.com @ 2002-04-03 14:48 UTC (permalink / raw)
To: Jean-Christophe FABRE <jean-christophe.fabre@ensam.inra.fr>; +Cc: pgsql-php@postgresql.org; pgsql-php-owner@postgresql.org; pgsql-sql
an alternative that I do for situations like this is store the file
location in the db, then when you want to call it, you can generate a link
on the web page from the db pointing to the file. The file is stored on
the server, not in the db.
Tim.
Timothy P. Maguire
Web Developer II
Harte-Hanks
978 436 3325
Jean-Christophe FABRE
<jean-christophe.fabre@ens To: pgsql-sql@postgresql.org, pgsql-php@postgresql.org
am.inra.fr> cc:
Sent by: Subject: bytea or large object
pgsql-php-owner@postgresql
.org
04/03/2002 09:47 AM
Hi,
I would like to store .pdf files in a database, which is accessed through a
PHP interface.
which is the best way to store binary data?
- bytea? (with the tricky handling of escape chars)
- large objects?
thanks
JC
PS: I didn't found any example of scripts using bytea in PHP, does someone
has some?
_________________________________________________________
Jean-Christophe FABRE
INRA - UMR Sol & Environnement tel: +33 (0)4 99 61 23 51
2, place Viala fax: +33 (0)4 67 63 26 14
34060 MONTPELLIER cedex 1
FRANCE
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
HARTE- HANKS PRIVILEGED AND CONFIDENTIAL INFORMATION- if you are not the
addressee or authorized to receive this for the addressee, you must not
use, copy, disclose or take any action based on this message or any
information herein and should delete this message.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: bytea or large object
@ 2002-04-03 16:12 Joe Conway <mail@joeconway.com>
parent: Jean-Christophe FABRE <jean-christophe.fabre@ensam.inra.fr>
1 sibling, 1 reply; 7+ messages in thread
From: Joe Conway @ 2002-04-03 16:12 UTC (permalink / raw)
To: Jean-Christophe FABRE <jean-christophe.fabre@ensam.inra.fr>; +Cc: pgsql-sql; pgsql-php@postgresql.org
Jean-Christophe FABRE wrote:
>
> Hi,
>
> I would like to store .pdf files in a database, which is accessed through
> a PHP interface. which is the best way to store binary data? -
> bytea? (with the tricky handling of escape chars) - large objects?
>
> thanks
>
> JC
>
> PS: I didn't found any example of scripts using bytea in PHP, does
someone
> has some?
Here's an escape script that was sent to me by Thomas T. Thai.
function escByteA($binData) {
/**
* \134 = 92 = backslash, \000 = 00 = NULL, \047 = 39 = Single Quote
*
* str_replace() replaces the searches array in order. We must
* process the 'backslash' character first. If we process it last,
* it'll replace all the escaped backslashes from the other searches
* that came before.
*/
$search = array(chr(92), chr(0), chr(39));
$replace = array('\\\134', '\\\000', '\\\047');
$binData = str_replace($search, $replace, $binData);
return $binData;
}
There is also a function built in to PHP 4.1.2, available if you're
using PostgreSQL 7.2, called pg_escape_bytea.
Read the pdf file into a string, then use either of these to escape the
string. The you can do a simple SQL insert. To retrieve the original
files, use stripcslashes() to restore the query result string.
Hope this helps,
Joe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] bytea or large object
@ 2002-04-04 07:00 Frank Joerdens <frank@joerdens.de>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 7+ messages in thread
From: Frank Joerdens @ 2002-04-04 07:00 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Jean-Christophe FABRE <jean-christophe.fabre@ensam.inra.fr>; pgsql-php@postgresql.org
On Wed, Apr 03, 2002 at 08:12:45AM -0800, Joe Conway wrote:
> Jean-Christophe FABRE wrote:
> >
> > Hi,
> >
> > I would like to store .pdf files in a database, which is accessed through
> > a PHP interface. which is the best way to store binary data? -
> > bytea? (with the tricky handling of escape chars) - large objects?
> >
> > thanks
> >
> > JC
> >
> > PS: I didn't found any example of scripts using bytea in PHP, does
> someone
> > has some?
>
> Here's an escape script that was sent to me by Thomas T. Thai.
>
> function escByteA($binData) {
> /**
> * \134 = 92 = backslash, \000 = 00 = NULL, \047 = 39 = Single Quote
> *
> * str_replace() replaces the searches array in order. We must
> * process the 'backslash' character first. If we process it last,
> * it'll replace all the escaped backslashes from the other searches
> * that came before.
> */
> $search = array(chr(92), chr(0), chr(39));
> $replace = array('\\\134', '\\\000', '\\\047');
> $binData = str_replace($search, $replace, $binData);
> return $binData;
> }
>
> There is also a function built in to PHP 4.1.2, available if you're
> using PostgreSQL 7.2, called pg_escape_bytea.
AFAIK you need PHP >= 4.2 to use pg_escape_bytea which is currently
available as rc1, i.e. not released. I tried pg_escape_bytea with rc1
and it segfaults (the process crashes). The function above works fine
though.
Regards, Frank
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: bytea or large object
@ 2002-04-04 09:40 Frank Joerdens <frank@joerdens.de>
parent: Jean-Christophe FABRE <jean-christophe.fabre@ensam.inra.fr>
1 sibling, 1 reply; 7+ messages in thread
From: Frank Joerdens @ 2002-04-04 09:40 UTC (permalink / raw)
To: Jean-Christophe FABRE <jean-christophe.fabre@ensam.inra.fr>; +Cc: pgsql-sql; linuxkurs@fas-art.com; adam@archi-me-des.de
On Wed, Apr 03, 2002 at 04:47:29PM +0200, Jean-Christophe FABRE wrote:
>
> Hi,
>
> I would like to store .pdf files in a database, which is accessed through a
> PHP interface.
> which is the best way to store binary data?
> - bytea? (with the tricky handling of escape chars)
> - large objects?
I asked the same question a week ago on pgsql-general. Here's my 2
cents: The Postgres large object interface is older and therefore more
tried and tested. It used to have a reputation for slowness, but the
code was overhauled in 7.1 to address this issue. The LO interface is a
little convoluted and takes some getting used to, but works well. For
Bytea you can use "normal" SQL syntax. Escaping the data is not
really difficult.
The Jury is still out on the perfomance issue. You might guesstimate though
that LO works better for really big chunks because with bytea,
everything needs to be read into memory on select/insert. This probably
also means that bytea is likely to be faster for smaller chunks (i.e.
those that easily fit into physical memory) because the server OS's and
PostgreSQL's cacheing mechanisms would come to the fore if the data is
small enough to be held in RAM a bit until it's written out to disk
(actually I don't have the faintest idea how large objects are handled
internally in Postres so this is more of a hunch on my part).
Last but not least it needs to be said that there is ample controversy
on the subject of whether to stuff binary data into the SQL database at
all - the alternative is to put them into the file system and store the
path to the file in an SQL table column. For web applications in
particular, the case _against_ putting e.g. images (think of a content
management app, for instance) into the database is pretty strong because
- especially on a loaded webserver - the overhead of retrieving images
from the database versus serving them straight from the file system is
pretty huge. Remember that web servers are designed to do this a quickly
as possible, whereas with transactional databases, the design focus is
elsewhere: data integrity and consistency, transactional functionality,
insert/update speeds, and then select speeds. You might see orders of
magnitude in performance difference.
However, if you do this - keep the big binary chunks out of the database
- then you partly compromise the very rationale for using a database in
the first place: namely data integrity, consistency, and transactional
functionality. What if someone accesses the files through the file
system directly rather than through your application which manipulates
filesystem and database simultaneously - your system would go out of
sync. What if 2 users try to manipulate the same set of data
simultaneously? How do you ensure transactional atomicity if you have
related data in 2 different places? Hence what I decided to do for the
next web project is this: Keeping the image data in the database, but
putting a copy of the image into the filesystem, as in a "permanent
image cache". The data in the database is then always considered
authoritative, and the image is only written out to the filesystem cache
once a transaction is successfully completed.
For a more philosophical, visionary slant on the subject matter, I would
recommend Hans Reiser's seminal paper on what he thinks ReiserFS might
be, or should be in the future:
http://www.namesys.com/whitepaper.html
He argues for a conversion between filesystems and databases and
approaches the problem from the filesystem end, i.e. he wants to make
filesystems more database-like.
I was thinking that it would be actually neat to have filesystem-like
functionality with an SQL database, and did some searching: There is a
little paper on how to do this with Postgres about something the author
calls PgFS (allows you to NFS mount a database), but as someone else on
some other list notes "code has not been sighted in years". There is
another approach called PerlFS which one might use for something
interesting with Posgres DBI, but the site where it used to be has been
down for months.
Regards, Frank
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: bytea or large object
@ 2002-04-04 10:36 Frank Joerdens <frank@joerdens.de>
parent: Frank Joerdens <frank@joerdens.de>
0 siblings, 0 replies; 7+ messages in thread
From: Frank Joerdens @ 2002-04-04 10:36 UTC (permalink / raw)
To: Frank Joerdens <frank@joerdens.de>; +Cc: Jean-Christophe FABRE <jean-christophe.fabre@ensam.inra.fr>; pgsql-sql; linuxkurs@fas-art.com; adam@archi-me-des.de
On Thu, Apr 04, 2002 at 11:40:51AM +0200, Frank Joerdens wrote:
> On Wed, Apr 03, 2002 at 04:47:29PM +0200, Jean-Christophe FABRE wrote:
> >
> > Hi,
> >
> > I would like to store .pdf files in a database, which is accessed through a
> > PHP interface.
> > which is the best way to store binary data?
> > - bytea? (with the tricky handling of escape chars)
> > - large objects?
>
> I asked the same question a week ago on pgsql-general. Here's my 2
> cents: The Postgres large object interface is older and therefore more
> tried and tested. It used to have a reputation for slowness, but the
> code was overhauled in 7.1 to address this issue. The LO interface is a
> little convoluted and takes some getting used to, but works well. For
> Bytea you can use "normal" SQL syntax. Escaping the data is not
> really difficult.
I forgot: A distinct advantage of bytea over large objects is that you
can still use pg_dumpall to back up your entire server in a single step.
It doesn't work with large objects because pg_dumpall can't create
binary output in an e.g. tar file. This means that if you use large
objects, you have to back up your databases one by one, using a binary
output format with pg_dump.
Regards, Frank
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] bytea or large object
@ 2002-04-04 11:08 Martín Marqués <martin@bugs.unl.edu.ar>
parent: Frank Joerdens <frank@joerdens.de>
0 siblings, 0 replies; 7+ messages in thread
From: Martín Marqués @ 2002-04-04 11:08 UTC (permalink / raw)
To: pgsql-php@postgresql.org
On Jue 04 Abr 2002 04:00, you wrote:
> >
> > There is also a function built in to PHP 4.1.2, available if you're
> > using PostgreSQL 7.2, called pg_escape_bytea.
>
> AFAIK you need PHP >= 4.2 to use pg_escape_bytea which is currently
> available as rc1, i.e. not released. I tried pg_escape_bytea with rc1
> and it segfaults (the process crashes). The function above works fine
> though.
PHP-4.2.0RC2 is out. :-)
--
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués | mmarques@unl.edu.ar
Programador, Administrador, DBA | Centro de Telematica
Universidad Nacional
del Litoral
-----------------------------------------------------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2002-04-04 11:08 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-03 14:47 bytea or large object Jean-Christophe FABRE <jean-christophe.fabre@ensam.inra.fr>
2002-04-03 16:12 ` Joe Conway <mail@joeconway.com>
2002-04-04 07:00 ` Frank Joerdens <frank@joerdens.de>
2002-04-04 11:08 ` Martín Marqués <martin@bugs.unl.edu.ar>
2002-04-04 09:40 ` Frank Joerdens <frank@joerdens.de>
2002-04-04 10:36 ` Frank Joerdens <frank@joerdens.de>
2002-04-03 14:48 Re: bytea or large object Timothy_maguire@hartehanks.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox