literally saving a file inside a database

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: literally saving a file inside a database by Thilosophy (Curate) on Feb 18, 2005 at 04:35 UTC
WHY would someone do this in real life? What's a reason someone would save the file into the database instead of to their hard drive or server and making a simple reference to it? Does mysql dish out files quicker? I'd say it is a tradeoff between convenient access (especially if the data is updated from time to time) and performance (which will suffer -- mysql does not dish out files quicker than you can read it directly). For example you can access data in a database from clients on a different server, whereas a file on a local disk can only be accessed locally (setting up a network share is of course an alternative, too).	[reply]
Re: literally saving a file inside a database by NetWallah (Canon) on Feb 18, 2005 at 05:16 UTC
WHY would someone do this in real life? One possible reason : VERSIONING. The database makes it possible to store multiple instances of the same (or modified) object. I have a perl app that takes weekly snapshots of all my network device configs. It compares the checksum of the current config to one in the database, and, if different, it stores the new config. This way, I can see when and how configs changed. By the way, the perl scripts that retrieve the config info are also stored in a database table - this allows me to use a master script to call a specific script for each specific device configured in the database (Yes, device name, IP etc is also stored in the database) ..."I don't know what the facts are but somebody's certainly going to sit down with him and find out what he knows that they may not know, and make sure he knows what they know that he may not know, and that's a good thing. I think it's a very constructive exchange," --Donald Rumsfeld	[reply]
Re: literally saving a file inside a database by prowler (Friar) on Feb 18, 2005 at 06:21 UTC
Another possible reason to save the content is to utilise the database's searching ability (have a fulltext index on the field and you've got a quick route to a search routine - mysql even has a relevance score system). Prowler - Spelling is a demanding task that requies you full attention.	[reply]
Re: literally saving a file inside a database by hubb0r (Pilgrim) on Feb 18, 2005 at 04:55 UTC
As far as how to do it, for binary or text you make a blob or text type field in mysql. The storage requirements and maximum size are listed as: `TINYBLOB, TINYTEXT L+1 bytes, where L < 2^8 BLOB, TEXT L+2 bytes, where L < 2^16 MEDIUMBLOB, MEDIUMTEXT L+3 bytes, where L < 2^24 LONGBLOB, LONGTEXT L+4 bytes, where L < 2^32` [download] From the Mysql dev manual. As far as putting the file into it, just store the data in a string and do an insert: `my $file = <FILE>; $dbh->do("insert into file_table set data = '$file'");` [download] Of course using $dbh->do() is not the best way to accomplish this... but it's fast and to the point. I've used a database for storing files in one or two situations... it has to be just the right situation for it to be a good thing. Something to take note of, and I mention it because it was something that bit me and took me a while to figure out why. Make sure that your max_allowed_packet is set to a sufficiently high number on both the client and server side if using mysql or else you'll have strange failures without much in the way of reasonable error codes to figure out why it was failing. I can't tell you what caveats there may be for other db's because I haven't used them, but for mysql, this one got me!	[reply] [d/l] [select]
Re: literally saving a file inside a database by dragonchild (Archbishop) on Feb 18, 2005 at 13:24 UTC
Yet another reason is to allow for serving of these files to the web. For example, a wiki might want to take advantage of this. Or, looked at another way, SharePoint (by Microsoft) is a fancy wrapper around storing files in a database. So is Subversion or CVS or RCS, for that matter. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]