Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Archive /

Our old forums are still available as a read-only archive.

Moderators: martimiz, Sean, biapar, Willr, Ingo, simon_w

DEFAULT values and NOT NULL in db/build


Reply


7 Posts   2965 Views

Avatar
maetl

Community Member, 15 Posts

9 July 2008 at 1:59pm

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

Avatar
dio5

Community Member, 501 Posts

9 July 2008 at 9:09pm

Hi,

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

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

Avatar
maetl

Community Member, 15 Posts

10 July 2008 at 9:52am

Edited: 10/07/2008 9:54am

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.

Avatar
maetl

Community Member, 15 Posts

10 July 2008 at 10:27am

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.

Avatar
Sam

Administrator, 685 Posts

14 July 2008 at 5:14pm

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);


which is roughly the same as:

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 ;-)

Avatar
maetl

Community Member, 15 Posts

14 July 2008 at 8:58pm

Edited: 14/07/2008 8:59pm

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!

Avatar
Sam

Administrator, 685 Posts

18 July 2008 at 3:32pm

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...