skip to main |
skip to sidebar
I've been playing around with storing image/media data in the database for the Beavervision Scoreboard - partly so I could just ship a whole database backup around without having to remember a whole heap of additional content files, and partly to test out BLOB handling through ADO.NET Entity Frameworks.
Firstly, file upload and reading/writing to the database. A doddle, read the incoming file into a byte array and stick it into the db object e.g:
HttpPostedFileBase v_HPFB = (HttpPostedFileBase)Request.Files[v_File];
if (v_HPFB.ContentLength != 0)
{
tbl_Media v_Media = new tbl_Media();
byte[] v_Buffer = new byte[v_HPFB.ContentLength];
v_HPFB.InputStream.Read(v_Buffer, 0, v_HPFB.ContentLength);
v_Media.value = v_Buffer;
}
Obviously for larger files you might want to stream rather than reading all into memory.
And then equally reading out from the column is also a doddle as its exposed as the byte array - obviously take care including the table holding the BLOB as you don't really want it to come back every time you do a simple query. I created a separate media table (cunningly called tbl_Media (which has just an identity column and a value image column - as above), and then have link tables for each area of the system that can have media, and only go to the BLOB on actual request for it.
So then we need to get this image out and use it somehow. I cracked out MVC and found the FileContentResult and FileStreamResult which you can attach to an action to essentially return non-HTML-view data. Thus to fetch an item out of the media table by ID I created a generic action, e.g
public FileContentResult Get(int id)
{
BeavervisionEntities v_Ent = new BeavervisionEntities();
tbl_Media v_Media = (from m in v_Ent.tbl_Media.Include("tbl_MediaType") where m.id == id select m).First();
return File(v_Media.value, v_Media.tbl_MediaType.type);
}
The Media Type table being the associated mime-type of the media stored in a normalised fashion.
Of course, once you wander into processing the BLOB, rather that just serving it up from a static location, you can start to muck around with it mid-stream...
So (hang on to your hats now), I know that I have a list of countries taking part, they all have their own flags, and I want to put them on different parts of the site in different sizes (e.g. a small one for a list, a mid-size one for view)... so I'll create a number of facade actions in the controller to neatly wrap a more complex action, and just upload and store a high-quality large transparent PNG (which I use as the source for all my images anyway), I then knocked-up a quick static html page to call these, and the output looks like:
- A is the original image as from the /Media/Get/1 source.
- B is the same image, unprocessed from /Media/Flag/Albania - querying through to find the relevant country name and pulling the associated flag image back.
- C is through the /Media/SmallFlag/Albania facade action to provide a 50x28 PNG.
- D is through the /Media/GetFlag/Albania?height=84&width=150 action, which outputs an 150x84 PNG (the facade in C is actually passing through to this action).
- E are two re-encoded images (JPEG & GIF respectively) used by appending &format=image/jpeg (or image/gif) to the GetFlag action.
All very nice and pretty (and pretty ugly too), and quick for me using the site on my own - but what can we do to try and reduce server & database overhead? How about some output caching? Well MVC nicely supports this with OutputCache attribute, so before the Flag & SmallFlag actions I told it to cache (for 2 weeks) with: [OutputCache(Duration = 1209600, NoStore = false, Location = System.Web.UI.OutputCacheLocation.Any, VaryByParam = "id")]Now to test this, I decided to upload a flag for a different country - but wouldn't you know it, me being the dunce who wants to test caching I deliberately uploaded the wrong image and bring up the static page designed to query it (i.e. changing Albania to Andorra), duh me. OK, so then I bin that from the database and upload the correct image - then let's see what came out for that:
So, using the same lettering from above I told the actions called in B & C to cache with the wrong image, and lo it doth return the cached image, whereas A, D & E haven't been told to cache and do return the correct image.
Neat.
Now I can abuse this to within an inch of its life.
One of the artefacts of moving from IIS to Apache to host the html was caching becoming a problem, and due to the limited way I have control over the hosting server - I can only hope the few tweaks I've been able to make will help. But I'll have to keep my eye on it.
The only other problems I've thus far noticed was the score graph had the incorrect start point, and the sort order of the races on the X axis was wrong.
- I decided that rather than using the database race id it would be better to normalise the sequence myself - this meant flot (the graphing tool) saw a discrete procession from 1 to 20 rather than say 36, and then maybe 37 to 56. This made it easier to tie the value to a point in the graph too and overall a bit neater.
- Sorting the X axis was a little more involved as I needed to extend the DAL's static method for retrieving the season to include the individual round (i.e. race or qualifying) so that I could sort on the date. Then through the power of LINQ I was able to sort in the view and just trawl through the output var - which worked, although you end up with some hideous looking code at times, ala:
- var v_Query = (from r in v_Season.tbl_Race select r).OrderBy(r => r.tbl_RaceRound.Max(rr => rr.date));
That's all I've noticed so far, which is fairly good I think.
Time to start looking at the Beavervision scoreboard - I think the biggest conclusions I've drawn from the Sweepstake is that having a really well designed database really makes using things like ADO.Net Entity Framework & ASP.Net MVC much cleaner and simpler - and building maintenance parts of applications becomes almost cookie-cutter in its simplicity - generally only 6 lines of code are needed to retrieve data for list/view/edit, and the views are pretty much identical, just bound to the specific object - which in turn is really just a database object.
The more complex side of development then is what the application does itself, but even then, getting and setting data through these frameworks is so simple, it allows you to concentrate on the more interesting elements rather than worrying about minutiae. Which of course means you can then abuse it all within an inch of its life.
Simples!
After a good few days of being able to catchup with a lot of the outstanding F1 Sweepstake work, the website is now ready to go live, I've put it up here where I'm sure the eagle-eyed viewer will notice it has the 2008 season data rather than 2009 - this is just my reference work as it's slightly easier to muck about with 2008 (less competitors, and less fuss if I get it wrong - which I have).
After the late abandonment of the MS Cloud route, and not wanting to shell out this year for any .net hosting (the timing all kind of clashed from my LAMP & Shell/POP/DNS renewal), I'm utilising the handy nature of MVC to serialize the html out from my dynamically produced site via wget, then uploading the contents to the hosting site. This seems to have worked fine for the html output, although some of the javascript and stuff in the Content directory seems to get mangled by wget - I assume this is some localisation/culture issue - although this is easily dealt with not letting wget fetch anything from the Content directory and just managing any changes (for which there should be virtually none) manually. All I need to do now is knock up a script on my Linux box to process the wget commands and upload to the hosting site.
Moving on, when I do sort out some .net hosting, I can just transfer to the dynamic site, and there should be no differences to the user. In theory.
I've added a few fiddly UI sprinkles for vertical resizing, and it seems mostly ok... I'll look at doing a few upgrades through the season, as on the face of it there's been no new functionality added (this has mostly been a refactoring exercise).
Next up, I need to use SQL Server Reporting Services to produce the PDF we mail round, and stick up at the bar - this shouldn't take too long to fix-up as the required data models for the document are the same as those for the website, so I should be able to re-use those.
Very nearly there, and with a week to go. Might even afford some time to test at this rate.
