Data Formatting - where?

PHP
1

I just wrote this sql function


CREATE FUNCTION `FORMAT_BYTES`(b INT, p INT) RETURNS varchar(200) CHARSET latin1
    NO SQL
    DETERMINISTIC
BEGIN

  IF b > 1024*1024*1204
    THEN RETURN CONCAT( ROUND( b / (1024*1024*1024 ), p ), ' GB');
  ELSEIF b > 1024*1024
    THEN RETURN CONCAT( ROUND( b / (1024*1024 ), p ), ' MB');
  ELSEIF b > 1024
    THEN RETURN CONCAT( ROUND( b / 1024, p ), ' KB');
  ELSE
    RETURN CONCAT( b, ' bytes');
  END IF;

END

The function transforms an integer to a byte count (An attachments table stores the byte count of the attachments placed in its blob fields). The function is a stopgap measure because the legacy system I’m using smarty and honestly I’d rather use a SQL function to format things than write a new smarty function for the task (my hatred of all things smarty well documented on these forums).

Smarty aside, is this such a bad thing? After all, SQL already has a few formatting functions inbuilt (most notably DATE_FORMAT). A couple months ago I made a thread on the uses of stored procedures and functions, and the results of that thread where inconclusive, but it might be worth picking up.

Aside: If there’s an inbuilt function that returns the size of a blob field’s data that would be even more efficient than my current approach.

2

Ignoring smarty, I’m not doing this kind of operation in the database is a particularly good idea. You lose re-usability. Sure, it’s efficient if you’re measuring values from the database. However, what if you want to display the size of a file or arbitrary string? You either need an unnecessary database query or duplicated code.

This is after all, presentation logic. I really don’t think it belongs in the database. I think your issue is with smarty rather than separation of concerns.

Oh and LENGTH() should give you what you want.

3

I have considered this many times in the past. I have to say formatting is best done in the model layer, preferably by a locale layer.

  1. You lose reusability if you were to swtch to a NoSQL solution or another RDBMS potentially.

  2. Formatting logic is business logic and should therefore be located within the model. Besides it should also be unit tested and validated. More difficult in SQL - in fact I have never even attempted it. :stuck_out_tongue:

  3. Formatting is something that is/should be (ideally) user controlled. I store all dates as a unix timestamp, which must be translated into a timestamp from any given date format (input) according to locale setting. The reverse (formatting) is also done according to locale. I suppose you could possibly inform RDBMS as to the locale to use but when done in PHP it becomes slightly more portable.

I’d rather seen a framework support eh above, so it’s independent of the RDBMS - but I suppose if your a hardcore DBA and know you will only use a single system…it’s OK.

Cheers,
Alex