The availability and perusability of frighteningly verbose illustrations and
explanations of the intricacies and inner operations of FreeBSD's
SoftUpdates, and Snapshots can be found at the following locations:



http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/snapshots.html

http://www.mckusick.com/softdep/index.html



But, I'd still keep the files out on disk, rather than inside the database.

My recent digital asset archive (somewhat simplified) looks like this:

CREATE TABLE tbl_file (

Int_fileid BIGINT NOT NULL,

str_filename VARCHAR(256) NOT NULL,

str_ext VARCHAR(5), /* *.doc files are encrypted as well */

str_desc VARCHAR(2048),

.any other fields you wish.

PRIMARY KEY(int_fileid)

);

CREATE GENERATOR gen_tbl_file;



I then create directories from 000 to 999, and inside, I store files called
[n].dat, left padded out to three digits(so file 1 becomes 001.dat), which
are gzipped, and some files are encrypted as well (*.doc, *.xls)

File are stored in the relevant folder (001, 3001, and 24001) are all stored
in the 001 folder, to keep directory index speeds up.



Simple, scales well, and syncing the repository to another host is easy,
again, rsync is used to walk the repository tree, and copy the index
database.



Nige.



Nigel Weeks

Tech Support and Systems Developer

Rural Press Tasmania

The Examiner Newspaper

Ph. 03 6336 7234

Mob. 0408 133 738

Email. <mailto:nweeks@...> nweeks@...

_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
Sent: Monday, 7 May 2007 4:30 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Database for images

> You must remember, we use FreeBSD - a very different beast to

Windows and

> Linux. We can take a filesystem snapshot, and spend as much time as

we like

> doing a filesystem copy of the .fdb from the server to anywhere, and
> Firebird can happily continue on it's version of the filesystem.

That would be a key to doing it that way. I do not know enough about
BSD filesystems, but for that to work the filesystem must be
internally doing its own version of MGA (maintaining a delta file or
something like that). Otherwise you will end up with all sorts of
funny business. Records in the data page written by transactions that
don't exist yet in the TIP and other fun things.

On file systems like NTFS, you would be lucky to have a successful backup.

As Glebas points out, a database with a table that size would need FB
2, and NBackup allows you to put the database into a backup mode,
where any deltas are written to another file to be merged later. You
could conceivably put the database in this mode, use rsync to send the
deltas (which should be relatively few) and then put it back into
normal mode. The added advantage is that it would work on any platform
which supports nbackup.

I would still prefer the approach I suggested of keeping images out of
the database if for no other reason that it allows other future
applications to process the images without having to write some
complicated interface. If access control is the only issue, it is
easily resolved by file system permissions that allow fbserver to see
what the user can not under their own account.

Adam





[Non-text portions of this message have been removed]