Hey fullas...

I need to add a new property to an existing data object that is optimized in the database with a NOT NULL constraint and a DEFAULT value.

Clearly, this can't be done in the actual property of the $db hash. I get a syntax error from db/build when I try the following:

function extraDBFields() {
return array("db" => array("TestField" => "Varchar(4) NOT NULL DEFAULT 'test'"));
}

The following works as a baseline, but always creates a row with NULL values, which is what I don't want:

function extraDBFields() {
return array("db" => array("TestField" => "Varchar(4)"));
}

Is there a recommended way to solve this within Sapphire? Am I missing something obvious? As I would like to be able to migrate these changes to multiple servers using just db/build, rather than having to manually change the columns.

Thanks,
Mark

Hi,

isn't the $defaults array what you are looking for?

http://doc.silverstripe.com/doku.php?id=datamodel&s=static%20defaults#default_values

Thanks. I was under the impression that the $defaults array applies to write operations on the data object itself, and doesn't have any affect on the table schema itself.

After percolating on this for a while, I think the best way (am testing this now) is to use the augmentDatabase() method, to manually apply an ALTER TABLE query during the db/build process.

Using:

augmentDatabase() { DB::query('ALTER TABLE ... etc'); }

It seems to work looking at the generated table definitions, but it crashes the db/build process in a strange way.

The short answer is that the framework isn't really set up to deal with this situation ;-). So any "good" solution is going to require patches on the core.

function extraDBFields() {
return array("db" => array("TestField" => "Varchar(4) NOT NULL DEFAULT 'test'"));
}

The reason that fails is that Varchar(4) is actually a request to construct a new Varchar PHP object. Specifically, it will instantiate a varchar object using

Object::create('Varchar', 'TestField', 4);

new Varchar('TestField', 4);

A few thoughts:

* You could update the requireTable() system to populate the fields' default values based on the $defaults array. This would be a handy addition to core.
* For things other than defaults, you could add additional arguments to the Varchar constructor. For example, a boolean flag indicating whether nulls are allowed.
* We're trying to work towards making the whole system DB-abstracted, so putting too many MySQL-specific things into the Varchar constructor would be a bad idea. Obviously "are nulls allowed?' isn't MySQL specific ;-)

I think you're right, that it's probably not a good idea to complicate the field constructors if at all possible - since (in some cases) as mentioned, corresponding information does exist in the $defaults array.

I guess, inititally I got confused because of the field type string defaulting to not need parentheses... Varchar, vs Varchar(50)...

One question, is whether there is a reason for the generated tables to default to NULL rather than NOT NULL. This is a controversial topic in SQL:

http://en.wikipedia.org/wiki/Null_(SQL)#Common_mistakes

I find null values of columns can cause weirdness in comparison operators and ordering, and my preference is to try and avoid them where possible. However, I don't mind whichever way is default, as long as there *is* an explanation or rationale somewhere!

To be honest I can't remember the rationale for using nulls on strings. We certainly avoided them for numeric values! I'm inclined to agree that disallowing nulls might make things simpler.

My only potential objection for changing this setting in the core would be the potential for backward compatibility issues...