0

Has anyone ever saved files against database record but stores them in a directory on PC?

So software which allows you to add files against database record but at the point of adding it, it creates a directory and takes a copy of the file and puts it in there. Still allowing the user to go into the database record and view the file from within the software.

Any help would be much appreciated. Thanks,

Sharpie
  • 373
  • 2
  • 15
  • 34
  • 1
    What is the point of doing that? Why do you care how the db stores its data? What motivates you to prefer one storage option over others? – David Heffernan May 28 '15 at 08:25
  • Why not save the path and file name in the database and the file itself to a folder on the disk. Then you can access the files as if they were entered into the database? I would not advise you to do this. – Val Marinov May 28 '15 at 08:34
  • Getting a database to transparently bring back the files as you do a `SELECT * FROM table` query is only possible if you use a view and it will be painfully slow. I would defer loading the files until you actually need the contents, and you would only know which row(s) to retrieve the contents for by inspecting other columns on the same row, as such I would build code into the application to do this and not try to force the database to do this. Or, depending on the size of the files etc. I would simply store the contents as blobs. Why is that not an option? – Lasse V. Karlsen May 28 '15 at 08:38
  • Hi all, it was just a thought that came to my mind so was jsut asking if anyone else has done this before. – Sharpie May 28 '15 at 08:48
  • All sorts of problems arise if you keep data outside the database. One wonders what specific problem you have. – David Heffernan May 28 '15 at 08:56
  • Hi @DavidHeffernan one thing that came to mind was if the database was lost, then if files/documents were stored locally in a directory they would be retrievable. Thanks – Sharpie May 28 '15 at 10:05
  • And what if the files got lost? Do you have an actual problem? – David Heffernan May 28 '15 at 10:09
  • Well then I'd be screwed if I didn't backup the PC. I have no problem I was just asking a question. – Sharpie May 28 '15 at 10:23
  • So you backup the files but not the db? – David Heffernan May 28 '15 at 11:14
  • backup both to be extra safe – Sharpie May 28 '15 at 11:45
  • Yes, I'm quite certain that someone has done this before. Does that help? It answers the question you asked here. – Ken White May 28 '15 at 13:11
  • I still see no reason for any of this. – David Heffernan May 28 '15 at 13:28

2 Answers2

1

You can store files in a folder and save the path in the database record but that would make the files available only to the software running on the same computer. You could instead use a FTP server and store files on it, giving them names equal to a unique field in the database record, for example the autogenerated/autoincremented ID field. That way it would be simple for client software running on any computer to retrieve the file that corresponds to the database record.

Milos
  • 63
  • 1
  • 9
  • 1
    Ad *that would make the files available only to the users using the same computer*, the access to the database as well as the access to those files should be the business logic tier responsibility. – TLama May 28 '15 at 08:55
  • Hi @Milos can you give example to your answer about storing files in a folder and save path in db? thanks and much appreciated – Sharpie May 28 '15 at 10:08
  • Simplest way would be to put all files in same predefined folder and name files using the database record ID value (e.g. 9876.dat for record with ID=9876), if it is autoincremented then you know it will be unique. – Milos May 28 '15 at 10:19
0

I actually use such approach in one of my programs.

Now do note that I have a custom made database and database engine for this.

I really doubt this could be made with common database engines without making some changes to them. Why?

In order for this to work you need to modify record preparations routines. What are record preparation routines?

Record preparation routines are number of functions which read data from multiple database tables (relational database connections) and then join them into a single structured record which is finally sent to the client.

It is these routines that control how and from where is some data being read.

Why have I decided to implement this?

Well that was many years ago when I only owned old Win98 based computer. And because Win98 does not support NTFS file system but only FAT32 I was limited by 3 GB file size which could be quickly exceeded when saving bunch of files directly into the database which is normally stored in single file or several different files (one for each database table) in some databases.

Because todays computers file systems no longer have this limitations there is no need for such solution any more.

But I still decided to keep this functionality. Why?

Most files that I store are textual files. So in order to reduce the space requirements on the database server even more I went and implemented word compression.

So all files are compressed but when client requests certain record that contain one of these files the file gets uncompressed and sent as such to the client (to maintain backward compatibility with older client software).

In newer client software I transfer these files in compressed format along with the word dictionary needed for decompression. So the files are decompressed on the client side now.

As for non text files that I also store I'm planning to implement a custom file server so these won't even be transmitted through the database engine itself as this is causing some slowdowns especially in case of larger files.

SilverWarior
  • 7,372
  • 2
  • 16
  • 22