Jump to:

17452 Posts in 4473 Topics by 1971 members

Archive

SilverStripe Forums » Archive » DEFAULT values and NOT NULL in db/build

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

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

Page: 1
Go to End
Author Topic: 2757 Views
  • maetl
    Avatar
    Community Member
    15 Posts

    DEFAULT values and NOT NULL in db/build Link to this post

    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

  • dio5
    Avatar
    Community Member
    501 Posts
  • maetl
    Avatar
    Community Member
    15 Posts

    Re: DEFAULT values and NOT NULL in db/build Link to this post

    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.

  • maetl
    Avatar
    Community Member
    15 Posts

    Re: DEFAULT values and NOT NULL in db/build Link to this post

    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.

  • Sam
    Avatar
    Administrator
    679 Posts

    Re: DEFAULT values and NOT NULL in db/build Link to this post

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

  • maetl
    Avatar
    Community Member
    15 Posts

    Re: DEFAULT values and NOT NULL in db/build Link to this post

    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!

  • Sam
    Avatar
    Administrator
    679 Posts

    Re: DEFAULT values and NOT NULL in db/build Link to this post

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

    2757 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.