Thomas Fjellstrom wrote:
I still wonder about the utility of storing binary data in the db itself.
Maybe it makes it more easy to distribute that way, but how often does one
distribute an entire database in a "vendor specific" format?
I'm quite interested in hearing people's reasoning for going the blob route,
when you have a perfectly good "database" format for "blobs" already (various
filesystems).
1)
We use the sqlite encryption extension. We want our blobs encrypted as
well as our database, so putting the blobs into the database makes
sense. We have a special table for the blobs that has a primary key (3
columns) and the blob. The rest of the data is contained in other tables.
2)
We don't need to worry about atomically deleting disk blobs and database
rows. We take advantage of the ACID nature of sqlite. This way we
don't have to code for contingencies where the user has managed to
delete or corrupt a blob, or a blob that our app can't delete even when
it deletes the database row.
3)
Having everything in one package. Makes tech support much easier if the
user only has to transmit a single file instead of an entire directory.
4)
We modify the blobs at runtime. ACIDness of sqlite is very nice here.
I don't want to try to re-implement this directly on the filesystem
(even if it becomes a simple rename operation).
Our blobs vary in size from 12K to 3M. Sqlite is not a performance
bottleneck for us... the client's internet connection is.
I have not done extensive performance tests on these settings, but these
are the settings that our app uses when it creates/opens the sqlite
database:
db.ExecuteImmediate("PRAGMA page_size=4096");
db.ExecuteImmediate("PRAGMA legacy_file_format=ON");
db.ExecuteImmediate("PRAGMA cache_size=8000");
db.ExecuteImmediate("PRAGMA synchronous=OFF");
db.ExecuteImmediate("PRAGMA temp_store=2");
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------