Re: Picture with Postgres and Delphi

Quoting Andrew Ayers <aayers@eldocomp.com>:

> Network Administrator wrote:
> > I'd actually like to get some comments on this too because for
> compatibility and
> > throughput issues, I would think that storing the file path in the
> database
> > instead of the actually file would be "better".  I've done one application
> like
> > this in the past that very worked well.  I'm getting ready to do the final
> > e-commerce integration on a new site and if there is an advantage to
> storing the
> > files in the database (in this case about 300 jpeg images for a t-shirt
> site)
> > I'll try that out.  I'll have to research that base64 encoding part because
> I'll
> > only every do text dumps.
>
> Storing the file path on the database can work well, but with modern
> databases, there shouldn't be any issue with performance with storing
> BLOB data. If you find there is an issue, then you should just set up a
> table holding this information along with a key field. This key would be
> a foreign key on your table holding the other information (in the case
> of the t-shirt site, perhaps the other table holds information relating
> to the style, size, etc).
>
> In effect, what you are doing by storing the path is the path is your
> "foreign key", to the filesystem "database". That is, you are using the
> filesystem as a database.

The issue I would be referring to might be a very narrow on in scope as a I
think about this.  In order to do this in the most portable way, I would need to
encode the images so that I can maintain my currrent procedures for database
disaster recovery, maintanance, etc.  In the previous site I did, the html pages
were all dynamically generally by mpl's (mod_perl scripts).  In the case of that
site, the image's size could be controlled by the users.  So if you wanted to
scale and image to 800x600 or 320x240, the script simple read in the file
(pointed to by the database) and then scaled then and wrote they to the screen.
 If the case of storing the images as BLOBS what I am worried about generally
speaking is the conversation coding time "penalty" associated with each image
that would be displayed.  This really has nothing to do with the database since
the obvious things to do is to NOT store the data in an encoded form.  The
penalty now is in the arena of our company procedures.

Of course the other piece of this you nail on the head.  Related information for
the shirts, (size, color, price) are part of another table.

Looking at the docs there is also a question of escaping certain characters.
Now I'm wondering just how exactly do I get the images into the database?  I
would have thought I could just copy them from STDIN or something along those
lines (I haven't even considered this until now).  In seems like managementwise
this might be more difficult for two reasons. One, just how to I get 250 images
or ANY arbitrary number of images into the database?  I'm guessing I could
script it but then two, what about the escaping issues?  With the storing the
file path the only thing I had to do is run a "recatalog" script we wrote which
would write to the database all the file paths.  That made it real easy for the
admins do the FTP or HTTP upload and then run that script from the admin page we
built them.  The application code, included the ability to detect these updates
and to recognize when a file was actually not of the system.

> One of the issues with storing the path on the database, instead of
> storing the BLOB, is that the data on the filesystem can change or be
> moved, without the database knowing about it. You then have "broken
> keys", keys that refer to data that is no longer the same as it was when
> the data was inserted into the database (the data is different, or it
> doesn't exist, or it is corrupted in some manner). There is also the
> issue of backups and restoring the state of the database. If you take a
> snapshot of the database at any point in time for a backup, you need to
> do the same with the pictures as well, and when you restore, remember to
> restore both. If you kept the data in the database, you just need to
> restore it alone.

The issue of backup/restore as you stated above is more procedural than
technical (simply make sure you backup client databases with their website data)
for exactly the scenario you gave.  You could still have human errors (i.e.
upload file, don't recatalog, crash, recover, I get a call- "where are my
files?") but that is acceptable compared having to dump in a non-portable,
non-editable (binary?) format.

> I know there are other reasons as well - hopefully others on here will
> point them out, as my memory is a bit fuzzy right now...
>
> Andrew Ayers
> Phoenix, Arizona

You're fuzzy- I'm perplexed- a fork just appeared in my road!  If anyone has
strategies for loading large amount of files into the database, I'd love to
heard them.  I'm going to have to play around with this.  I just don't think I
have the time to do it before this project is due.  I *love* when that
happens...  *snicker* :)

> -- CONFIDENTIALITY NOTICE --
>
> This message is intended for the sole use of the individual and entity to
> whom it is addressed, and may contain information that is privileged,
> confidential and exempt from disclosure under applicable law. If you are not
> the intended addressee, nor authorized to receive for the intended addressee,
> you are hereby notified that you may not use, copy, disclose or distribute to
> anyone the message or any information contained in the message. If you have
> received this message in error, please immediately advise the sender by reply
> email, and delete the message. Thank you.
>


--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com