17488 Posts in 4473 Topics by 1978 members
|
Page:
1
|
Go to End | |
| Author | Topic: | 2449 Views |
-
DEFAULT values and NOT NULL in db/build
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 -
Re: DEFAULT values and NOT NULL in db/build
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
-
Re: DEFAULT values and NOT NULL in db/build
10 July 2008 at 9:52am Last edited: 10 July 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.
-
Re: DEFAULT values and NOT NULL in db/build
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.
-
Re: DEFAULT values and NOT NULL in db/build
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 ;-) -
Re: DEFAULT values and NOT NULL in db/build
14 July 2008 at 8:58pm Last edited: 14 July 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!
-
Re: DEFAULT values and NOT NULL in db/build
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...
| 2449 Views | ||
|
Page:
1
|
Go to Top |
