BLOBs
Okay, I don’t know if any of you found my post on the other site but I am reposting here an idea…
As the name mentions, this is an idea for storage of data that would take the files off the file system and keep them in the db instead… It has its good points and its drawbacks. Security is easier in the db but inserting and deleting might not be completely straightforward especially with very big files… So I am dropping this on you gusy to see what you think. I can say that I am currently using CLOB’s (the oracle equivalent of TEXT) in a project at work and they work fine and allow alot of storage space. So here comes the blurb on them, same as the one I sent to Timmy as we were discussing it…
BLOB is Binary large object and CLOB is character large object. These are the actual names from oracle. In MYSQL they are known as Blob and Text types. Theyc ome sin four favours
TinyBlob/TinyText (255 bytes)
MediumBlob/MediumText (65535 bytes)
Blob/Text (16777215 bytes)
LongBlob/LongText (4294967295 bytes)
Blob’s are treated as pure binary strings so we can store files and whatnot in them. Text are treated as characters (just ots and lots of them). So basically we could store the templates and edited templates and such in the database on a seperate table and then have a table for attaching documents to a specific project. The attached documents would just be saved directly into the database (if xml into a text, if any other format then into a blob). The interesting thing being that we were mentioning the ability to upload files and whatnot that they could attach to their project, why store them on disk and risk collision when we can just bung them in the database.
This will also make security much easier as people will be unable to just access the files as on a file system (as they need to be kept somewhere that they can be accessed unless you make an expiring link and even then someone else can hijack it). In this case we can do the check as part of the db retrieval and basically if the person is not assigned to that project (or any other types of security we eventually setup) then they do not have acces..
As these blobs and text types allow us to store up to 4GB of data (the biggest ones) we would generally, if we are passing large amounts of data to the server, want to break it up into smaller more manageable packets or lumps of data so we would need a stored procedure on one end for the storing of data that will create, overwrite or append, and a function that will return a value in more manageable clumbs that can then be glued together via concatenation (assuming that we can actually have a 4gb xml file in memory 
). En tout cas…. These are just a couple of ideas and up for suggestion. Gimme a yell on what you think
Cheers
Chris
Explore posts in the same categories: Technology, Team, Programming