We have three levels of code in our big and complex Web site:
- plain SQL (in fact Perl DBI),
- a primitive ORM (we wrote our own primitive ORM, because DBIx::Class appeared too slow in our environment),
- business objects (high level interface to the database, grouped by topics (not by tables and fields)).
- (well fourth, special code such as views)
Should such tasks as replacing an empty string with NULL be on level 2 or level 3?
6
First thing you have to decide is if you think it is important to distinguish (at least in certain cases) between empty strings and NULL in your business layer (BL), or not. I have seen systems where this difference was important, and other systems where the whole DB or its API was configured not to distinguish between those cases, because in the BL it made no difference. So before asking on which layer a mapping shall take place, clarify what you want to use here.
If you need to distinguish NULL/undef from the empty string in your BL, then the answer is simple: don’t do any automatic replacement. Map the empty string to an empty string, and “undef” to NULL (and vice versa). You have to write your business code so it can deal correctly with empty strings and “undef”ined values as well.
If you want to treat “undef” and “empty string” as equal in your BL for all tables and attributes, then it clearly makes sense to let the ORM do the mapping. If, however, you want this only sometimes, for some columns, and in other cases you need to distinguish between both values, you need to know how often you expect this situation. If the former is a rare situation you need only for approximately a half dozen attributes, you can implement the behaviour in the BL on a “by case” basis. If you have 100 or more attributes for which “undef” and “empty string” should be treated as equal, you should extend your ORM to let it handle this for certain columns in a generic manner.
For example: for certain, specificially tagged columns, the ORM could just map any NULL value to an empty string when it reads the data from the DB, and convert the empty string back to NULL when it writes the data again. Extending your ORM in this manner to provide a generic mechnanism will probably need some effort, and you have to decide if you can reuse this mechanism often enough to gain more benefit from it than the effort it takes to implement it.
After some thinking:
If we want a string field in DB to have NULL instead of empty string, then it is most likely a property of the business object that it should return NULL (undef in Perl) for this column and it should allow to store NULL (undef) into this field (one way to do this is to replace empty string with undef every time empty string is stored into this object).
After some more thinking:
The database design may change. Now we want an empty string to be stored as NULL, but we may change it to store empty string as empty string. This should not affect the functionality of the business object, which API may be written either with undef or empty string, as agreed on business object layer, not ORM layer.
Thus both arguments call to do this in business objects not in ORM.
1