This isn't really a Metabase question, but I'm hoping that with your experience you can give me some direction.
I've used Metabase to put a BLOB into a MySQL database and to retrieve it. Worked like a charm! Thanks for an easy-to-use tool.
However I wonder if putting my church photos in a database is worthwhile. Since I apparently have to retrieve a BLOB to a disk file before doing anything with it, why not leave it on the hard disk and just store its file name? Or is there some way to go directly from the hard disk to a Web page without first writing the photo to a disk?
Manuel Lemos - 2006-10-14 20:44:41 - In reply to message 1 from Harvey P. Morgan
There are reasons in favour or against putting files in database BLOBs fields. Those are the same for using SQL/relational databases versus using flat file databases.
Storing and retrieving BLOBs is not a fast operation, so it may slow down your application, unless you use some sort of cache that avoids retrieving the BLOB fields repeatedly.
The same can be said about generating pages with content retrieved from the database using many or slow queries. To avoid the overhead of generating pages that require a slow database access, you can used a caching technique.
The PHPClasses uses a generic file cache class for that purpose. The benefits have been tremendous in terms of performance. most of the accesses to pages with database driven content no longer requires any database accesses until the content is changed.
Keeping content on disk, may solve the problem for single server Web sites. For sites served by clusters that becomes problematic.
The solution that guarantees data consistency is to concentrate the caching in a single server and all clustered servers retrieve the cached content from there.
That would be slower but it would preserve scalability. To reduce the networked content access overhead for busy sites, you may also consider local server caching for short periods (say a few seconds or minutes).
Other than that, storing BLOBs in a database can be assured to be consistent using transactions for updates that require more than one query.
Storing BLOBs in a database also simplifies the backup of your application data. A single procedure can backup and restore all your database tables, including those with BLOB fields.
Other than that, keep in mind that not all databases are suited for storing and retrieving large BLOBs. The problem is that they require to load the whole BLOB data in memory when you are storing or retrieving it from a database.
Metabase provides an API with prepared queries that lets you stream data that is being stored or retrieved from a BLOB field. However, the internal implementation of some drivers requires loading the whole BLOB data in strings to execute INSERT, UPDATE and SELECT queries.
This means that if you accessing BLOBs with more than a few megabytes, it may exceed the default PHP memory limit which is 8MB.
Only the databases that support native prepared queries that stream BLOB data instead of sending whole BLOBs at once.
Metabase drivers for Oracle and PostgreSQL for instance, support native prepared queries, thus streaming BLOB data.
The Metabase MySQL driver that uses the old MySQL API do not support native prepared queries.
I have not yet used the MySQLi API that supports native prepared queries. So, I am not sure if it provides all the necessary means to stream BLOB data.