I have a database that I am trying to move into SilverStripe's ORM. Some of the fields store numeric data, but use null to signify that a value was not available. Can I do this within SilverStripe?

I have created a model for the object and a form to enter data, but when I try to store the data, my null values are being altered. I have tried PHP's NULL constant and the string "null".

FYI, the form data is only partial, and the remaining values are calculated based on the form data. The fields that may be null are all calculated fields, so I am not entering "null" in a text box, it is being added programmatically to the array of data. That array is passed at instantiation, and then written to the database (pseudocode):

$calculated_data = calculations($form_data);
$snapshot_data = array_merge($form_data, $calculated_data);
$snapshot = new Snapshot($snapshot_data);
$snapshot->write();

I did find that setting the field default to NULL (using phpMyAdmin) solves part of my problem, as I am then able to pass "" or NULL and the resulting field is saved as NULL. But... /dev/build changes the field to NOT NULL and my data is altered.

How can I make this work? Perhaps more importantly, is using NULL in the database for this purpose bad design (if so, what should I do about it)?

Thanks,
David

Anyone?

Any suggestions?

-David

Okay, I think I've got this solved.

SilverStripe has field "specs" mostly wired up in methods where they may be of use, but the data type methods of the MySQLDatabase class overwrite these with a default spec right before the field is created. I worked around this by extending the class. I will show the changes required to make integers accept a null value. I welcome any feedback on this approach.

DoublePlusGoodMySQLDatabase.php

<?php

class DoublePlusGoodMySQLDatabase extends MySQLDatabase {

	public function int($values){
		return 'int(' . $values['precision'] . ') ' . $values['null'] . ' default ' . $values['default'];
	}
}

?>

That's the only change that needs be made to the database class, but the data type classes themselves aren't written with null values in mind. In this example, I've also made NULL the default value.

NullInt.php

<?php

class NullInt extends Int {

	function __construct($name, $defaultVal = 'NULL') {
		parent::__construct($name);
		$this->defaultVal = (is_int($defaultVal) || is_null($defaultVal) || 'null' == strtolower($defaultVal)) ? $defaultVal : 'NULL';
	}

	function nullValue() {
		return NULL;
	}

	function requireField() {
		$parts=Array('datatype'=>'int', 'precision'=>11, 'null'=>'null', 'default'=>$this->defaultVal, 'arrayValue'=>$this->arrayValue);
		$values=Array('type'=>'int', 'parts'=>$parts);
		DB::requireField($this->tableName, $this->name, $values);
	}
	
	function prepValueForDB($value) {
		if($value === true) {
			return 1;
		}
		
		if($value === NULL || 'null' === strtolower($value)) {
			return 'NULL';
		}
		
		if(!$value || !is_numeric($value)) {
			if(strpos($value, '[')===false)
				return '0';
			else
				return addslashes($value);
		} else {
			return addslashes($value);
		}
	}

}

?>

Now, just make sure you're using your new database class and data type class:

_config.php (partial)

$databaseConfig = array(
	"type" => "DoublePlusGoodMySQLDatabase",
	[...]

MyObject.php

<?php

class MyObject extends DataObject
{
	public static $db = array(
		'number_but_might_be_null' => 'NullInt'
	);
}

?>

This is not at all thoroughly tested (though it's going into a production website soon). I'll update here if I find any necessary changes.

-David

Hi David,

Your solution is what exactly what I'd recommend you wanted to keep the NULLs in your DB. If possible NULL should never appear in a database and should definitely not be used as described. It probably isn't too much of an issue but there are much better, cleaner, ways for storing info the the db... I've learnt that small/smallish website are very different from my previous stuff that was large scale financial systems... so this is only advice... but a good principle is a good principle.. for example you could...

*store an enum status that indicates what the NULL meant
*put a has_one in and if that is not set it means that the data item doesn't exist

Barry

Barry,

> If possible NULL should never appear in a database and should definitely not be used as described. [...] a good principle is a good principle [...]

Thanks for your input. I am certainly no DBA. Perhaps you would take a minute to help me understand what principle you are referring to.

Let me start by saying, I don't use nulls unless they have a purpose. Most fields in most tables I create are NOT NULL. In my example, I have a table with roughly 20 numeric fields that contain government data which originates on paper forms. Sometimes no value is entered in the paper form. I use NULL to identify these. It seems that the alternative would be to store a 0 for the field and identify (as you said) using an enum that there is no value for the field. That seems cumbersome (I could never use the value of that field without checking another field first) and creates the potential for errors (the value is *not* 0, despite what the database says). Thoughts?

Thank you,
David

Well I'll have to admit that people disagree on this issue as they rightly say that the database contains a value which indicates it is 'unknown'. And yes it is cumbersome to represent data without using it if the system was previously designed to use NULL.

In practice though it is very corrupting in large scale systems - simply because NULL can mean - not set by default, intentionally not set and not set unintentionally. It can cause all kinds of trouble with SQL statements like...

drop table test;
create table test (a char, b int);
insert into test(a)values('y');
insert into test(b)values(3);
insert into test(a)values('n');

select * from test where a='y'
union
select * from test where a='n';

the second insert doesn't exist because it allow for nothing to be set, when there should be a value. In ss it seems that a has one relationship that is not set contains '0' for the ID. The system is coded to intentionally show that no relationship is set, with 0. My main concern would be for the depth of code and SQL that you'll never see that might handle NULL's in an unexpected way!

I recently migrated a database with tons of null values and then the second the data migrated I opened a ModelAdmin and the validation failed on saving a record and I needed to provide a value - something I went back and upgraded the migration with rather than letting the user decide later on.

NULLs are part of db functionality and can hardly always be avoided - they should be kept way down in the hole though to enable people to fully understand the data that is in the database.

Barry

p.s. I am no DBA myself, but I've been around a few and I am passing on their advice

EDIT just went to that bastion of knowledge wikipedia - http://en.wikipedia.org/wiki/Null_(SQL)

Barry,

Thanks for taking the time. Some good food for thought there.

> My main concern would be for the depth of code and SQL that you'll never see that might handle NULL's in an unexpected way!

A very good point!

> they should be kept way down in the hole though to enable people to fully understand the data that is in the database.

Agreed.

That seems like a very lucid and informative Wikipedia page. You can always tell the pages that draw a lot of interest...

Cheers,
David