PureBasic Forums - English

At the moment, my recipe manager imports text data into a database and strips pictures during import into a subdirectory and just links the relative path.

Is it a good (better) idea to store all the pictures into a blob field into a separate linked table?

I want to avoid the possibility of thousands of files on the hard disk, fragmentation etc.

How tot he big knobs do it? Like Amazon, eBay etc?)

No, you should store that many pictures outside of a DB. I wouldn't worry too much about
fragmentation and stuff. That's exactly what operating systems and storage devices are designed for,
to deal with large numbers of files.

I think the only real reason where I'd store large pictures in a DB directly is if the picture is
transactionally coupled to the data, which is most often not the case.

Of course, the true answer always depends on the individual project...

Store the filenamepath$ and filefingerprint in the db. Put the actual images on a server.

Shield wrote:I think the only real reason where I'd store large pictures in a DB directly is if the picture is
transactionally coupled to the data, which is most often not the case.

Transactionally? The only coupling between the picture and recipe data is when you display the recipe in a list, the picture for it is displayed to the right. Also in edit/add new mode.

Nothing more fancy than that.

skywalk wrote:Store the filenamepath$ and filefingerprint in the db.

I do that already.

Put the actual images on a server.

There is no server involved, it is all local, portable.

Portability is another reason I wanted to know why putting the pictures in a table was a good/bad idea or not as I don't fancy copying 38,000 pictures (and possibly more as I add more recipes) to a USB key at USB 3/2 speeds (shudders).

The last experiment had the database at 580 megabytes for 780,000 (My figures are not accurate, I forget exactly) and then there were the externally linked pictures.

Fangbeast wrote:Transactionally?

Transactionally here means that it is guaranteed that all changes to your images are part of your transactions.
For example, if you store file paths in the DB and an error occurs that causes the image file not to be written down,
your DB won't know of this fact and you'll have data inconsistency (which can be dealt with in this case),
since your picture data is not part of your record.

This problem doesn't occur if you store pictures in your DB, as the entire insertion will be rolled back if something happens.


Since you are doing a portable app you have to outweigh the pros and cons. How often will your records change? Especially,
how often will you add and remove pictures? If the pictures don't often change it might be a good idea after all to store
them directly in your DB for this specific use case.

Especially, how often will you add and remove pictures? If the pictures don't often change it might be a good idea after all to store them directly in your DB for this specific use case.

Thanks for the explanation, you are a good help.

The pictures won't change at all. They are provided (Along with the recipe data) from the Yahoo Living CookBook group and I want to keep them as they are.

IMHO - don't think there's a right or wrong way (sorry Fangbeast, I know that's not very helpful!). I've been using picture Blobs for years, although not reached the high numbers discussed here. I guess the decision has to be based on a balance of factors.

There must be a practical limit on record numbers or size for a Blob table, but haven't reached it yet myself. However, I have done real-world testing to over 12000 pics as Blobs with no problems at all. That was with moderate size pictures, but I also tested with bigger pictures so that the table size got to more than 2Gb. My personal everyday work copy of the database runs with 9000 embedded pics, which is about 450Mb.

I'd be interested in comments from anyone with real experience of much bigger numbers of Blob records.

My own program can store pictures both ways, as Blobs or path links - customers can choose when they add new pictures. The pictures supplied with the program, currently just under 6000, are all embedded Blobs. This keeps installation simple and avoids them getting in a mess with file paths (support nightmare!). The application can be run as fully portable. Some users don't change records at all, while others add and amend things a lot. Interestingly, most of them choose the option to store "in the database" rather than link files.

An important bit for me - all pictures are stored in separate database files, which are then Attached to the main "data" database at run-time. Some time ago when first moving to Sqlite, I did some unscientific experiments and found this gave the best overall performance. It was especially significant on low power machines. This testing was not with PureBasic, but I suspect it's still true. Adding a big Blob table to the main database file appeared to slow down data searches a bit, whereas in separate files they don't seem affected. The performance of the PureBasic/Sqlite edition has always been really good even on very old, slow PCs.

In other situations, such as MySQL web database, I concluded that it was better to use only file links. But for the desktop program there has been so far no disadvantage using Blobs and no headaches from handling thousands of separate files in the installer.

IMHO - don't think there's a right or wrong way (sorry Fangbeast, I know that's not very helpful!).

No, you are right, everyone has to discover for themselves which is the best method but it's good to ask so as not to necessarily reinvent the wheel.

I've been using picture Blobs for years, although not reached the high numbers discussed here.

Eventually, the database has to be capable of at least 500,000 recipes (or more) but I have been having trouble with startup times and search times on a database of that size, with or without pictures and it has frustrated me for years. This was a textual database with links to the pictures.

Adding a big Blob table to the main database file appeared to slow down data searches a bit, whereas in separate files they don't seem affected.

Yes, I had the same problem, whether I had indexes or not. Had a separate table for the blobs, in the same data, indexed on the blob record number for the blobs. Still was woeful.

In other situations, such as MySQL web database, I concluded that it was better to use only file links. But for the desktop program there has been so far no disadvantage using Blobs and no headaches from handling thousands of separate files in the installer.[/quote]